IBM Support

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

Subscribe

You can track all active APARs for this component.

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 number

    JR37942

  • Reported component name

    WIS DATASTAGE

  • Reported component ID

    5724Q36DS

  • Reported release

    810

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-10-07

  • Closed date

    2013-04-29

  • Last modified date

    2013-04-29

  • 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



Document information

More support for: InfoSphere DataStage

Software version: 8.1

Reference #: JR37942

Modified date: 29 April 2013