JR37942: TRUNCATE TABLE STATEMENT IS NOT SUPPORTED AS A USER DEFINED TRUNCATE STATEMENT IN DB2 COMMON CONNECTOR FOR DB2 9.7

APAR status

• Closed as program error.

Error description

• Job with DB2 Common Connector failed when TRUNCATE TABLE
  statement was used for the user defined truncate statement.
  
  Error message in job log was:
     Item #: 41
     Event ID: 407
     Timestamp: 2010-09-14 16:22:16
     Type: Info
     User Name: dsadm
     Message Id: IIS-CONN-DB2-00695
     Message: DB2_Connector_10: DB2 reported: SQLSTATE = 25001:
  Native Error Code = -428: Msg = [IBM][CLI Driver][DB2/AIX64]
  SQL0428N  The SQL statement is only allowed as the first
  statement in a unit of work.  SQLSTATE=25001
  
     Item #: 42
     Event ID: 408
     Timestamp: 2010-09-14 16:22:16
     Type: Fatal
     User Name: dsadm
     Message Id: IIS-CONN-DB2-00028
     Message: main_program: Fatal Error: An error occurred when
  truncating the table. Statement = TRUNCATE TABLE
  KSJ.TEST1 IMMEDIATE
  

Local fix

• Use Generated truncate statement or use "ALTER TABLE tablename
  ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE" or "CALL
  SYSPROC.ADMIN_CMD('IMPORT FROM /dev/null OF DEL REPLACE INTO
  tablename')" as the user defined truncate statement.
  

Problem summary

• If the user set the DB2Connector's Table action property to
  Truncate and supplied a user-defined TRUNCATE TABLE
  statement, the job failed with the  following error:
  
  DB2 reported: SQLSTATE = 25001: Native Error Code =
   -428: Msg = [IBM][CLI Driver][DB2/AIX64] SQL0428N  The SQL
  statement is onlyallowed as the first statement in a unit of
  work. SQLSTATE=25001
  

Problem conclusion

• DB2 9.7 introduced a TRUNCATE TABLE statement, but it must be
  the first statement in the transaction. If the user supplies a
  truncate statement, the DB2 Connector now commits the current
  transaction before executing the TRUNCATE TABLE statement.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R810 PSY

     UP