Read applications are supported on a reads on standby database. At some times, thought it might seem that the query is read only it fails to execute and an error is returned. I was asked the below question..
<Question> HADR ROS is enabled on the standby server. When I run a read only query on the standby, the query fails with error SQL1773 RC5. In the db2diag.log I notice the following messages
2012-06-12-11.10.06.782777-240 I1749E551 LEVEL: Error
PID : 23655 TID : 47079342729536PROC : db2sysc 0
INSTANCE: hdrinst1 NODE : 000 DB : HADRDB71
APPHDL : 0-609 APPID: 169.193.146.11.37629.120612150952
AUTHID : kkchinta
EDUID : 46 EDUNAME: db2agent (HADRDB71) 0
FUNCTION: DB2 UDB, Common Trace API, sqlbfix, probe:123
MESSAGE : ZRC=0x80100469=-2146433943=SQLP_HDRS_READ_ONLY
"The operation that attempted to modify the contents of the database failed"
2012-06-12-11.10.06.784560-240 I2301E1262 LEVEL: Error
PID : 23655 TID : 47079342729536PROC : db2sysc 0
INSTANCE: hdrinst1 NODE : 000 DB : HADRDB71
APPHDL : 0-609 APPID: 169.193.146.11.37629.120612150952
AUTHID : kkchinta
EDUID : 46 EDUNAME: db2agent (HADRDB71) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbFixAndSearchOneSMPPage, probe:800
MESSAGE : ZRC=0x80100469=-2146433943=SQLP_HDRS_READ_ONLY
"The operation that attempted to modify the contents of the database failed"
DATA #1 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes
64
DATA #2 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes
Obj: {pool:25;obj:2;type:128} Parent={25;2}
lifeLSN: 7726780610111206
tid: 0 0 0
extentAnchor: 0
initEmpPages: 0
poolPage0: 0
poolflags: 6112
objectState: 45
lastSMP: 0
pageSize: 32768
extentSize: 64
bufferPoolID: 13
partialHash: 2147614745
bufferPool: 0x00002ad1a226ce20
pdef: 0x00002ad3438a6840
DATA #3 : Codepath, 8 bytes
60:62
<Question> HADR ROS is enabled on the standby server. When I run a read only query on the standby, the query fails with error SQL1773 RC5. In the db2diag.log I notice the following messages
2012-06-12-11.10.06.782777-240 I1749E551 LEVEL: Error
PID : 23655 TID : 47079342729536PROC : db2sysc 0
INSTANCE: hdrinst1 NODE : 000 DB : HADRDB71
APPHDL : 0-609 APPID: 169.193.146.11.37629.120612150952
AUTHID : kkchinta
EDUID : 46 EDUNAME: db2agent (HADRDB71) 0
FUNCTION: DB2 UDB, Common Trace API, sqlbfix, probe:123
MESSAGE : ZRC=0x80100469=-2146433943=SQLP_HDRS_READ_ONLY
"The operation that attempted to modify the contents of the database failed"
2012-06-12-11.10.06.784560-240 I2301E1262 LEVEL: Error
PID : 23655 TID : 47079342729536PROC : db2sysc 0
INSTANCE: hdrinst1 NODE : 000 DB : HADRDB71
APPHDL : 0-609 APPID: 169.193.146.11.37629.120612150952
AUTHID : kkchinta
EDUID : 46 EDUNAME: db2agent (HADRDB71) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbFixAndSearchOneSMPPage, probe:800
MESSAGE : ZRC=0x80100469=-2146433943=SQLP_HDRS_READ_ONLY
"The operation that attempted to modify the contents of the database failed"
DATA #1 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes
64
DATA #2 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes
Obj: {pool:25;obj:2;type:128} Parent={25;2}
lifeLSN: 7726780610111206
tid: 0 0 0
extentAnchor: 0
initEmpPages: 0
poolPage0: 0
poolflags: 6112
objectState: 45
lastSMP: 0
pageSize: 32768
extentSize: 64
bufferPoolID: 13
partialHash: 2147614745
bufferPool: 0x00002ad1a226ce20
pdef: 0x00002ad3438a6840
DATA #3 : Codepath, 8 bytes
60:62
<Answer> To explain the above failure on a high level -- Queries on the standby database can only make use of SMS system temporary table spaces. A query executed on the standby database that uses DMS system temporary table spaces may result in an error (SQL1773N Reason Code 5). You can read this on the IBM doc's here.
You can confirm if the table space used by the temp table is a DMS temp table space by looking at the Pool ID dumped in the diaglog. In the above diaglog, the pool ID dumped is 25. You can run a list tablespaces command to see if the table space 25 is a DMS temporary table space or not. </Answer>
3 comments:
Hi Kiran-
We are also getting the sql 1773 RC 5 error but we have no DMS temp tablespaces and the query that we are running is very straight forward.
select distinct(page_region) from page_media with ur
SQL1773N The statement or command requires functionality that is not
supported on a read-enabled HADR standby database. Reason code = "5".
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PAGE_MEDIA_ID SYSIBM INTEGER 4 0 No
PAGE_ID SYSIBM VARCHAR 30 0 No
PAGE_DOMAIN SYSIBM VARCHAR 30 0 No
PAGE_REGION SYSIBM VARCHAR 254 0 No
MEDIA_KEY SYSIBM INTEGER 4 0 Yes
Any, ideas as to why this could be happening?
Thanks,
AB
Does the table page_media has indexes defined on any of the column. If the index on the table is bad, the read query will try to recreate the index which is a write operation. Looking at the db2diag.log for any related messages might help track down the issue.
I looked in the diag the only thing it shows there is the "The operation that attempted to modify the contents of the database failed" message. No index rebuilds seen in the diag or the nfy log. Besides the indexrec is set to "restart". We thought this may be a sort overflow causing temp table creation which may be a write operation but changing sort related parms. didn't help either.
Thanks,
AB
Post a Comment