IBM Support

IY85488: INDEX SCAN CAN NOT PICK UP START/STOP KEY DUE TO MISSING ONCE-ONLY COMPUTATION FOR STRING CASTINGS IN SQL COMPILER

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Index scan can not pick up start/stop key due to missing
    once-only computation for string castings in SQL compiler
    
    This results in a poor query plan and poor performance of these
    types of queries.
    
    Reproduction:
    
     CREATE TABLE "DSC_FILE"  (
                      "FILE_ID" CHAR(17) FOR BIT DATA NOT NULL ,
                      "OBJECT_ID" CHAR(17) FOR BIT DATA NOT NULL ,
                      "PATH" VARCHAR(64) ,
                      "FORMAT" VARCHAR(64) ,
                      "FLAGS" SMALLINT NOT NULL )   ;
    
    ALTER TABLE "DSC_FILE"
            ADD PRIMARY KEY
                    ("FILE_ID");
    
    
    generate 1000 rows
    generate runstats
    db2 "select format from dsc_file where file_id = CAST( CAST ( ?
    AS CLOB(17) ) AS CHAR ) "
    
    Reproduction:
    
    
     CREATE TABLE "DSC_FILE"  (
                      "FILE_ID" CHAR(17) FOR BIT DATA NOT NULL ,
                      "OBJECT_ID" CHAR(17) FOR BIT DATA NOT NULL ,
                      "PATH" VARCHAR(64) ,
                      "FORMAT" VARCHAR(64) ,
                      "FLAGS" SMALLINT NOT NULL )   ;
    
    ALTER TABLE "DSC_FILE"
            ADD PRIMARY KEY
                    ("FILE_ID");
    
    
    generate 1000 rows
    generate runstats
    db2 "select format from dsc_file where file_id = CAST( CAST ( ?
    AS CLOB(17) ) AS CHAR ) "
    

Local fix

  • N/A
    

Problem summary

  • INDEX SCAN CAN NOT PICK UP START/STOP KEY DUE TO MISSING
    ONCE-ONLY COMPUTATION FOR STRING CASTINGS IN SQL COMPILER
    

Problem conclusion

  • First fixed in DB2 UPD Version 8.2, FixPak 14.  The performance
    enhancement is not enabled by default due to the potential of
    getting erroneous runtime errors (APAR JR22061).  To enable this
    performance enhancement, set the following registry value:
    
    db2set db2compopt=33882629
    

Temporary fix

  • N/A
    

Comments

APAR Information

  • APAR number

    IY85488

  • Reported component name

    DB2 UDB ESE SOL

  • Reported component ID

    5765F4102

  • Reported release

    820

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2006-05-30

  • Closed date

    2008-03-13

  • Last modified date

    2008-03-13

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

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

    IZ13887

Fix information

  • Fixed component name

    DB2 UDB ESE SOL

  • Fixed component ID

    5765F4102

Applicable component levels

  • R820 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"820","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
13 March 2008