IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022