IBM Support

IC93735: AUTOMATIC REORG RETURNS SQL2220W ON REORG OF EMPTY COMPRESSED TABLE AND DICTIONARYOPTION="REBUILD"

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Automatic reorg will return following errors in the db2diag.log:
    
    2013-06-26-01.00.01.149619+120 I3779E669           LEVEL: Error
    PID     : 4132                 TID  : 139923044009728PROC :
    db2acd 0
    INSTANCE: db2v977              NODE : 000
    FUNCTION: DB2 UDB, Automatic Table Maintenance,
    db2AutoReorgExec, probe:190
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 2220   sqlerrml: 0
     sqlerrmc:
     sqlerrp : db2Reorg
     sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
    0x00000000
               (4) 0x00000000      (5) 0x00000000      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
    
    2013-06-26-01.00.01.149779+120 I4449E342           LEVEL: Event
    PID     : 4132                 TID  : 139923044009728PROC :
    db2acd 0
    INSTANCE: db2v977              NODE : 000
    FUNCTION: DB2 UDB, Automatic Table Maintenance,
    db2AutoReorgExec, probe:10
    STOP    : Automatic reorg has completed with errors on table
    SAMPLE  ."DB2V977 "."TESTTAB"
    
    
    To hit the above errors following conditions need to be met:
    
    - table is a compressed table (CREATE TABLE ... COMPRESS YES)
    - automatic reorg policy uses option dictionaryOption="Rebuild"
    - table data has been deleted
    
    With the above conditions being true the table will be marked
    for a reorg. DB2 automatic reorg will attempt to reorganize the
    table and starts a REORG.
    Due to table being empty the REORG will fail to build a new
    compression dictionary and return warning SQL2220. The table
    reorganization as such will be successful only the dictionary
    rebuilt could not be performed.
    
    Automatic reorg does treat the warning as error and prints the
    db2diag.log messages.
    

Local fix

  • You should execute a manual runstats against such a table to
    reflect the current state after the messages are seen in the
    db2diag.log. This will ensure the table is no longer in need for
    reorg and prevent it from being considered in the next automatic
    reorg window.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7 Fix Pack 9                        *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 version 9.7 Fix Pack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC93735

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-06-27

  • Closed date

    2013-12-23

  • Last modified date

    2013-12-23

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

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

    IC93787 IC93788

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 December 2013