IBM Support

ADMIN_CMD('load from ...') using JDBC in XA transaction gets SQL30090N

Question & Answer


Question

Why does ADMIN_CMD('load from ...') using JDBC in XA transaction get SQL30090N?

Cause

It is an known current limitation of DB2.

Answer

Here is an example message from a jdbctrace.log
-------------------
[jcc] BEGIN TRACE_DIAGNOSTICS
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03] java.sql.SQLWarning
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] DB2 SQLCA from server
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlCode = 20397
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlErrd = { 0, 2, 0, 0, 0, 0 }
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlErrmc = SYSPROC.ADMIN_CMD;SQL30090;^@
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlErrmcTokens = { SYSPROC.ADMIN_CMD, SQL30090, ^@ }
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlErrp = DB2LOAD
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlState = 01H52
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03][Sqlca@f3177864] SqlWarn =
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03] SQL state = 01H52
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03] Error code = 20397
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03] Tokens = SYSPROC.ADMIN_CMD;SQL30090;^@
[jcc][Thread:WebContainer : 0][SQLWarning@2cab0b03] Stack trace follows
com.ibm.db2.jcc.am.SqlWarning: DB2 SQL Warning: SQLCODE=20397, SQLSTATE=01H52,
SQLERRMC=SYSPROC.ADMIN_CMD;SQL30090;^@, DRIVER=4.19.49
-------------------

Symptoms:
- It does not throw any exception and the application finished without any problem but
the data is not actually loaded into the table.
- If the same operation without XA transaction, it is succeeded and loaded data.
- From jdbctrace, SQL20397W and SQL30090N are logged.

The reason why:
The cause of the problem is LOAD does internal commit near the beginning of
operation. The load internal commit got back SQL30090 error is standard
behavior in XA environment. So ADMIN_CMD + LOAD does not work on XA.

Workaround:
Unfortunately, there is no way to do ADMIN_CMD + LOAD on XA.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Movement - Load","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5;11.1","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21988939

Page Feedback