APAR status
Closed as program error.
Error description
DESCRIBE via ADMIN_CMD can sometimes give wrong column order: $ db2 "CALL sysproc.ADMIN_CMD('DESCRIBE output > SELECT CORPORATE_NR ,SOLD_TO_NR ,CUST_ITEM_NR ,ITEM_REVISION_NR > ,ITEM_NR ,ITEM_REV_TYPE_CD ,ITEM_REV_DOFU_DT ,ITEM_REV_EXP_DT > ,ITEM_REV_STAT_CD ,CREATE_USER_ID ,CREATE_TS ,CHANGE_USER_ID > ,CHANGE_TS FROM SCHEMA.TABLE > WHERE ( CORPORATE_NR = ( CAST ( ? AS CHAR ( 10 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 10 ) ) ) = ( CAST ( ? AS CHAR ( 10 ) ) ) ) > AND ( SOLD_TO_NR = ( CAST ( ? AS CHAR ( 5 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 5 ) ) ) = (CAST ( ? AS CHAR ( 5 ) ) ) ) > AND ( CUST_ITEM_NR = ( CAST ( ? AS CHAR ( 30 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 30 ) ) ) = ( CAST ( ? AS CHAR ( 30 ) ) ) ) > AND ( ITEM_NR = ( CAST ( ? AS CHAR ( 15 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 15 ) ) ) = ( CAST (? AS CHAR ( 15 ) ) ) ) > ORDER BY CORPORATE_NR ,SOLD_TO_NR ,CUST_ITEM_NR ,ITEM_REVISION_NR WITH UR' ) > > " Result set 1 -------------- SQLTYPE_ID SQLTYPE SQLLENGTH SQLSCALE SQLNAME_DATA SQLNAME_LENGTH SQLDATATYPE_NAME_DATA SQLDATATYPE_NAME_LENGTH ---------- ---------- ----------- -------- ---------------- -------------- --------------------- ----------------------- 452 CHARACTER 10 0 CORPORATE_NR 12 0 452 CHARACTER 5 0 SOLD_TO_NR 10 0 452 CHARACTER 30 0 CUST_ITEM_NR 12 0 452 CHARACTER 10 0 ITEM_REVISION_NR 16 0 452 CHARACTER 15 0 ITEM_NR 7 0 452 CHARACTER 2 0 ITEM_REV_TYPE_CD 16 0 384 DATE 10 0 ITEM_REV_DOFU_DT 16 0 384 DATE 10 0 ITEM_REV_EXP_DT 15 0 452 CHARACTER 2 0 ITEM_REV_STAT_CD 16 0 452 CHARACTER 8 0 CREATE_USER_ID 14 0 392 TIMESTAMP 26 0 CREATE_TS 9 0 452 CHARACTER 8 0 CHANGE_USER_ID 14 0 392 TIMESTAMP 26 0 CHANGE_TS 9 0 13 record(s) selected. Return Status = 0 $ db2 "CALL sysproc.ADMIN_CMD('DESCRIBE output > SELECT CORPORATE_NR ,SOLD_TO_NR ,CUST_ITEM_NR ,ITEM_REVISION_NR > ,ITEM_NR ,ITEM_REV_TYPE_CD ,ITEM_REV_DOFU_DT ,ITEM_REV_EXP_DT > ,ITEM_REV_STAT_CD ,CREATE_USER_ID ,CREATE_TS ,CHANGE_USER_ID > ,CHANGE_TS FROM SCHEMA.TABLE > WHERE ( CORPORATE_NR = ( CAST ( ? AS CHAR ( 10 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 10 ) ) ) = ( CAST ( ? AS CHAR ( 10 ) ) ) ) > AND ( SOLD_TO_NR = ( CAST ( ? AS CHAR ( 5 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 5 ) ) ) = (CAST ( ? AS CHAR ( 5 ) ) ) ) > AND ( CUST_ITEM_NR = ( CAST ( ? AS CHAR ( 30 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 30 ) ) ) = ( CAST ( ? AS CHAR ( 30 ) ) ) ) > AND ( ITEM_NR = ( CAST ( ? AS CHAR ( 15 ) ) ) > OR ( CAST ( '' '' AS CHAR ( 15 ) ) ) = ( CAST (? AS CHAR ( 15 ) ) ) ) > ORDER BY CORPORATE_NR ,SOLD_TO_NR ,CUST_ITEM_NR ,ITEM_REVISION_NR WITH UR' ) > > " Result set 1 -------------- SQLTYPE_ID SQLTYPE SQLLENGTH SQLSCALE SQLNAME_DATA SQLNAME_LENGTH SQLDATATYPE_NAME_DATA SQLDATATYPE_NAME_LENGTH ---------- ---------- ----------- -------- ----------------- -------------- ---------------------- ----------------------- 452 CHARACTER 5 0 SOLD_TO_NR 10 0 452 CHARACTER 10 0 ITEM_REVISION_NR 16 0 452 CHARACTER 2 0 ITEM_REV_TYPE_CD 16 0 384 DATE 10 0 ITEM_REV_EXP_DT 15 0 452 CHARACTER 2 0 ITEM_REV_STAT_CD 16 0 392 TIMESTAMP 26 0 CREATE_TS 9 0 452 CHARACTER 8 0 CHANGE_USER_ID 14 0 452 CHARACTER 10 0 CORPORATE_NR 12 0 452 CHARACTER 30 0 CUST_ITEM_NR 12 0 452 CHARACTER 15 0 ITEM_NR 7 0 384 DATE 10 0 ITEM_REV_DOFU_DT 16 0 452 CHARACTER 8 0 CREATE_USER_ID 14 0 392 TIMESTAMP 26 0 CHANGE_TS 9 0 13 record(s) selected. Return Status = 0 We can see column order is different in 2nd execution. The reason it doesn't always repro is because of a missing ORDER BY resulting in indeterminate order. Sometimes it will be okay, sometimes not. This does not happen when you run the describe command directly on command line.
Local fix
The workaround is to issue DESCRIBE from the CLP directly without ADMIN_CMD Stored procedure.
Problem summary
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 version 11.1.4.6 * ****************************************************************
Problem conclusion
First fixed in Db2 version 11.1.4.6
Temporary fix
Comments
APAR Information
APAR number
IT30272
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-09-12
Closed date
2021-03-22
Last modified date
2021-03-22
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
DB2 FOR LUW
Fixed component ID
DB2FORLUW
Applicable component levels
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]
Document Information
Modified date:
23 March 2021