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
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
29 April 2013