IBM Support

IT24737: AUTOMATIC ONLINE REORG FAILS WITH SQL0270N BECAUSE THE CLAUSE ALLOW WRITE ACCESS IS GENERATED AND NOT SUPPORTED ON MDC TABLE. (R

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The auto reorg fails with SQL0270 because the clause ALLOW WRITE
    ACCESS is generated and not supported on MDC on ONLINE job.
    Online index reorganization is supported on multi dimensionally
    clustered (MDC) or insert time clustering (ITC) tables in ALLOW
    WRITE mode only when the CLEANUP option or RECLAIM EXTENTS
    option is specified.
    
    
    2018-04-14-19.31.55.106467-240 I2836E695             LEVEL:
    Error
    PID     : 19935                TID : 140436493494016 KTID : 863
    PROC    : db2acd
    INSTANCE: juntang              NODE : 000
    HOSTNAME: hotellnx116
    FUNCTION: DB2 UDB, Health Monitor, db2AutoReorgExec, probe:190
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -270   sqlerrml: 2
    sqlerrmc: 57
    sqlerrp : db2Reorg
    sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 0x00000000
               (4) 0x00000000      (5) 0xFFFFF9F2      (6)
    0x00000000
    sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
    sqlstate: 42997
    
    
    7214917    entry DB2 UDB global services sqlzeSqlCode cei
    (1.3.26.45.2)
        pid 2760 tid 3504 cpid 4700 node 0
        eduid 3504 eduname db2agent
        bytes 50
    
        Data1     (PD_TYPE_ECFID,4) Function:
        DB2 UDB, relation data serv, sqlrreorg_sendinfo
        Data2     (PD_TYPE_SINT,4) signed integer:
        -270
        Data3     (PD_TYPE_UINT,8) unsigned integer:
        1550
        Data4     (PD_TYPE_UINT,2) unsigned integer:
        1
    
    7224669    data DB2 UDB Health Monitor db2AutoReorgExec fnc
    (3.3.82.709.0.190)
        pid 4700 tid 3104 cpid 64976 node 0 probe 190
        bytes 78
    
        Data1     (PD_TYPE_DEFAULT,70) Hexdump:
        5245 4F52 4720 494E 4445 5845 5320 414C    REORG INDEXES AL
        4C4F 5720 5752 4954 4520 4F4E 2044 4154    LOW WRITE ON DAT
        4120 5041 5254 4954 494F 4E3B 2054 4F4B    A PARTITION; TOK
        313A 5041 5254 3231 373B 2045 4E44 3B20    1:PART217; END;
        5351 4C32 3730                             SQL270
    
    8234531    data DB2 UDB Health Monitor db2AutoReorgExec fnc
    (3.3.82.709.0.400)
        pid 4700 tid 3104 cpid 65016 node 0 probe 400
        bytes 101
    
        Data1     (PD_TYPE_MSG_TOKEN,93) MRI message tokens:
        REORG INDEXES."SCH1"."TAB1".IX1
    .-270.2018-02-14-10.30.27.2018-02-14-10.31.00..
    
    8234532    data DB2 UDB DRDA Application Requester
    sqljrParseSqlcaGrp fnc (3.3.55.273.0.179)
        pid 4700 tid 5780 cpid 65016 node 0 probe 179
        bytes 144
    
        Data1     (PD_DB2_TYPE_SQLCA,136) SQLCA:
    sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 0   sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQL11012
    sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
               (4) 0x00000000      (5) 0x00000000      (6)
    0x00000000
    sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
    sqlstate: 00000
    

Local fix

  • As for the "workaround", customer may use filter clause (
    FilterClause of ReorgTableScope tag in their reorg policy xml
    file  ) to skip the MDC table they are having issue with, so
    they wouldn't even try to evaluate them for reorg.  It doesn't
    solve the real problem, just let these error message goes away
    as it skips evaluating these tables.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT24737

  • 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

    2018-04-16

  • Closed date

    2018-11-27

  • Last modified date

    2018-11-27

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

  • 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 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":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
27 November 2018