IBM Support

IT36690: QUERY WITH INDEX SCAN ON A COLUMNAR TABLE WHICH IS UNDER A UNIONMAY GET INCORRECT RESULT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • QUERY WITH INDEX SCAN ON A COLUMNAR TABLE WHICH IS UNDER A UNION
    MAY GET INCORRECT RESULT.
    Union leg with the CDE index fetch needs to be more complex -
    for example, have NLJN between CDE index scan and Union.
    
    Example of the plan:
    Plan Graph:
    
                                   Rows
                                 Operator
                                   (ID)
                                   Cost
    
                                    2
                                 RETURN
                                  ( 1)
                                 12.0948
                                   |
                                    2
                                  SCAN
                                  ( 2)
                                 12.0948
                                   |
                                    2
                                  SORT
                                  ( 3)
                                 12.0133
                                   |
                                    2
                                  UNION
                                  ( 4)
                                 11.8146
                                /       \
                           1                1
                         NLJN             *SCAN*
                         ( 5)              ( 9)
                        11.8098           0.0048
                       /       \            |
                  1               1     TFunc:
                FETCH     *SCAN*   SYSIBM
                ( 6)             ( 8)    GENROW
               2.60333     9.20648
              /       \            |
         1            1           1
       ISCAN   Columnar Table:  Table:
       ( 7)    Schema            ELENA
      0.20428  Table_CDE    Table_ROW
        |      <2,8>            <2,6>
        1
     Index:
     Schema
     Index_name
     <2,8,2>
    

Local fix

  • db2set  DB2_REDUCED_OPTIMIZATION="COL_IXSCAN_ALL_V1133 OFF"
    -im option can be used, BUT Static sql will need to be rebound
    and already compiled dynamic sql re-compiled as well.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * BLU                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2_v111m4fp7 or later                            *
    ****************************************************************
    

Problem conclusion

  • Upgrade to db2_v111m4fp7 or later
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT36690

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-04-23

  • Closed date

    2021-07-01

  • Last modified date

    2022-04-20

  • APAR is sysrouted FROM one or more of the following:

    IT36682

  • 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

  • RB10 PSY

       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:
03 May 2022