TRUNCATE work file!!

Gov

Guest
Hello,

Is there a way where we can truncate a table from JDE? I see delete all rows BSFN B8000002 uses JDB_Deletetable API which i guess generates the Delete SQL statement rather than truncate statement.

Any idea please!!

Thanks in advance.
 
Using Business Function B8000002 to Delete All Records from aTable
Abstract: Document describes a business function (B8000002) that can be used to delete all records from a table.
Overview

The B8000007 and the B8000002 business functions must be used together to delete all the rows in a table. They will not delete the table specifications or the physical table in the database. B8000007 must be called first to get the environment handle, with the handle only then B8000002 is able to delete all records from the specified table. B8000007 must be called again to free the handle.

The B8000007 Get Input Output Env Handles is used to get the environment handle where the table can be found.

The B8000002 Delete All Rows From Table is used to delete all the rows in a selected table.

The B8000007 Free Input Output Env Handles is used to free the environment handles.

B8000007 - Get Input Output Env Handles

To use the B8000007, you will first need to create event rule variables, one for the Input Handle and one for the Output Handle. These variables need to be an ID type. It would be best to use the Data Dictionary alias HANDLE.

Unless the B8000007 is being used in a table conversion there are no input and output environments. In the example below it is assumed that B8000007 is not being used in a table conversion, so all environments are filled with the system variable SL LoginEnvironment. In the example below Input_HANDLE and the Output_HANDLE are event rule variables created by using the Data Dictionary alias HANDLE.

SL LoginEnvironment -> szInputEnvironment

Input_HANDLE <- idInputEnvHandle

SL LoginEnvironment -> szOutputEnvironment

Output_Handle <- idOutputEnvHandle

SL LoginEnvionment -> szLoginEnvrionment

B8000002 - Delete All Rows From Table

The business function B8000002 will delete all the records in a table. It does not delete the table specifications or the physical table in the database.

The business function has two arguments. The first argument is of type string. This argument must pass in the Object Name of the table. Example: F550101. The second argument is of type ID and should be the environment handle value returned by B8000007. The value from the B8000007 to be used is idOutputEnvHandle. In the example above the handle was returned to the event rule variable Output_HANDLE.

B8000007 (Free Input Output Env Handles)

After the B8000002 business function has been used to delete the records, the Input and Output Environment handles need to be freed or removed from memory. The business function B8000007 Free Input Output Env Handles is used for this purpose. The inputs and outputs for this business function will be the same except that the Input and Output Handles should be bi-directional. See the example below.

SL LoginEnvironment -> szInputEnvironment

Input_HANDLE <-> idInputEnvHandle

SL LoginEnvironment -> szOutputEnvironment

Output_Handle <-> idOutputEnvHandle

SL LoginEnvionment -> szLoginEnvrionment
 
Tom,

You are exactly correct. Thats how we use BSFN B8000002 while deleting records from a table.

But, my query was on truncating a table. Truncate is different with Delete. Truncate gives much performance than 'delete' statement.

so question is, can we truncate a table from JDE? using C- APIs or any other means.

any idea.

Thanks in advance!!
 
Gov,

I don't believe there is a JDE database API to do a truncate, but you could certainly use the SQL api's to issue any sql statement you'd like.

For an example of how to use them, see Whipping Boy's website.

Good luck
 
Gov,

there are issues in attempting to use truncate with an Oracle database.
Basically Oracle does not allow a user to truncate a table unless the user:
1. is the table owner
or
2. is a dba
or
3. has the DROP ANY TABLE system level privilege

Its bad practice in secure production environments for the JDE oracle account to have these privileges.

An alternative is to create a stored procedure that truncates the table in question and which is granted DBA role or DROP ANY TABLE system level privilege. In turn, grant execute permissions on this procedure to the database user(s), who need it.
How you execute this procedure is up to you. You can try the 'C' BSFN approach, or trigger it by a DML, or . . .

Just something to consider.
 
Hi Larry,

I found no problem for this case, I am using two step of works:

1. If u r using TC, the Oracle will create problem. As you said some time work some time can not work.
2. Do it from UBE, delete (truncate) the table (B8000002 will submite TRUNCATE syntax), and execute the TC program. And yippy all become smooth. Don't forget to remove the delete from TC.

Have a nice try.
 
Back
Top