IBM Support

IT30272: DESCRIBE VIA ADMIN_CMD CAN SOMETIMES GIVE WRONG COLUMN ORDER.

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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:

    IT35064

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