«

»

Sep 10 2009

Using BSFN to Delete All Records from a Table

VN:F [1.9.22_1171]
Rating: 4.6/5 (5 votes cast)

We all do work tables during our architectural design and development. To Clear the work table, we then rely on some bogus field, or say something like PID, to delete off the records.
Such deletion of records takes up space on the Database side, as it just deletes the data and does not reclaim the space from the table space, even when there’s no records. Delete Records statement, uses more transaction logs, . TRUNCATE Table is the SQL that has to be used in these scenarios.

BSFN – B8000002 does something similar to Truncate table, though the SQL generated is "Delete From TableName".
Note: Sometimes this BSFN does not fire Delete Table triggers. I dunno in what cases it does.

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

Source: Oracle/Metalink3
Also Read at www.DeepeshMD.com

Published under the license.

VN:F [1.9.22_1171]
Rating: 4.6/5 (5 votes cast)
Using BSFN to Delete All Records from a Table, 4.6 out of 5 based on 5 ratings
(Visited 1,109 times, 1 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.

7 comments

Skip to comment form

  1. Mitchell Vanwieren

    Many thanks for discussing such an topical article with all of us. I’ve bookmarked your blog will come back for a re-read again. Keep up the very good work.

    VA:F [1.9.22_1171]
    Rating: +2 (from 2 votes)
  2. JDEuser

    Hi,

    How to create the id variable. I cant map any event variable as “Input_Handle”. Please advice. I want to delete all rows in my table before entering data in to the table.

    Thank You,

    VN:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    1. Deepesh M Divakaran

      Hi JDEuser,
      You can use any DD Item of the Data type – ID (Identifier).
      for e.g. GENLNG

      Regards
      Deepesh

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  3. JDEuser

    Hi Deepesh,

    Thank you for your answer its working now.

    VN:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  4. zaki

    Is there any bsfn/way to TRUNCATE table in JDE instead of DELETE? DELETE seems to leave HWM in Oracle and reduce performance in Work Files

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  5. Raj

    We have Input Environment — Oneworld XE — DB2/400,Output Environment E9.1 — Oracle.I need to Left outer Join with F0101lected records in the tables on input side migrate data from XE ( F0101,F0115,F0116,F0150,F0006) to F0101Z2 on E9.1.

    can it be possible using Table conversions?,If so can you pls elaborate the possibility.

    Can i use handles here?

    Thanks .
    Raj

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    1. Deepesh M Divakaran

      Hi Raj,

      You may try table conversions, by having a View as the input and single table as output.

      Best option is to use Database SQL’s to do migration of data where you have Left outer joins with more than 3 tables. JDE does not support having more than 3 tables when using complex joins.

      Regards
      Deepesh

      VN:F [1.9.22_1171]
      Rating: +2 (from 2 votes)

Leave a Reply