DB2 - Problem description
Problem IT24737 | Status: Closed |
AUTOMATIC ONLINE REORG FAILS WITH SQL0270N BECAUSE THE CLAUSE ALLOW WRITE ACCESS IS GENERATED AND NOT SUPPORTED ON MDC TABLE. (R | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem 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 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher * **************************************************************** | |
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. | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 16.04.2018 27.11.2018 27.11.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |