RC/Query : How to quickly find out which tablespaces have pending changes on them.
Article ID: 242032
Updated On:
Products
Issue/Introduction
When structural changes are made to DB2 objects there is often the requirement for a REORG utility to be run against it in order to clear the AREOR status that DB2 places on such tablespaces.
"REORG-pending (AREOR) advisory status: Indicates that the object should be reorganized to apply pending definition changes.
AREOR status must be reset for all adjacent logical partitions that are in AREOR status.
The affected objects are not restricted and can be accessed by both readers and writers."
Environment
DB2 for Z/OS
Release R20
Resolution
Pending changes are supported only for database objects in Universal table spaces.
Pending changes are validated at execution time as usual but the alteration is not actually implemented. The alteration is stored in the Db2 Catalog in table SYSIBM.SYSPENDINGDDL.
When the alteration is submitted, the object is placed into advisory state "AREOR". The ALTER statement returns SQLCODE +610 reporting that the object has been placed into a ADVISORY REORG PENDING state.
ALTER TABLESPACE DBCORPC.TSROLE MAXPARTITIONS 10
;
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT
DBCORPC.TSROLE HAS PLACED OBJECT IN ADVISORY REORG PENDING
The object is still available for use by applications.
If an ALTER is attempted now on the tablespace after AREOR is in place using RC/Update, it will display this warning message:
RO866 RO866W Tablespace has pending changes. Object is shown with pending
changes omitted. Press HELP for more information.
If an additional ALTERATION is made to the same tablespace such as:
ALTER TABLESPACE DBCORPC.TSROLE
BUFFERPOOL BP1
;
The additional alteration will be stored also in catalog table SYSIBM.SYSPENDINGDDL in timestamp order.
RC/Query has a TABLESPACE report called PC: TS/PC from the main RC/Query menu.
PC (Tablespace Pending Changes Report)
Displays the tablespace objects that are pending definition changes.
The first page of the PC report lists the objects found with AREOR status:
RQTSPC 20.0 ------ RC/Q Table Space Pending Changes ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> PC Where => N
Table Space ===> TSR% > Creator ===> AUTHID1 >
Data Base ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 3 >
CMD TABLESPACE DATABASE OBJNAME OBJSCHEMA OBJTYPE
________ TSROLE DBCORP1 TSROLE AUTHID1 S
________ TSROLE DBCORPC TSROLE AUTHID1 S
________ TSROLE DBCORPC TSROLE AUTHID1 S
The next page to the right(PF11) shows the timestamp and the statement used for the alteration:
RQTSPC 20.0 ------ RC/Q Table Space Pending Changes ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> PC Where => N
Table Space ===> TSR% > Creator ===> AUTHID1 >
Data Base ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 3 < >
CMD TABLESPACE PENDING TIMESTAMP STATEMENT_TYPE
________ TSROLE yyyy-mm-dd-20.30.14.664732692382 ALTER
________ TSROLE yyyy-mm-dd-01.03.12.152700367187 ALTER
________ TSROLE yyyy-mm-dd-01.29.25.026095525390 ALTER
******************************* BOTTOM OF DATA ********************************
The last page to the right shows what was altered:
RQTSPC 20.0 ------ RC/Q Table Space Pending Changes ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> PC Where => N
Table Space ===> TSR% > Creator ===> AUTHID1 >
Data Base ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 3 <
CMD TABLESPACE OPTION_KEYWORD OPTION_VALUE OPTION_SEQNO
________ TSROLE MAXPARTITIONS 10 1
________ TSROLE MAXPARTITIONS 10 1
PE______ TSROLE BUFFERPOOL BP1 1
******************************* BOTTOM OF DATA ********************************
If the PE line command is used on the third tablespace listed, more details are displayed
about the alteration:
RQTSPE 20.0 ------ RC/Q Table Space Pending Details ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> PE Where => N
Table Space ===> TSROLE > Creator ===> * >
Data Base ===> DBCORPC > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -AUTHID1 - FRAME 2 OF 2
CMD: ________
TABLESPACE : TSROLE
:
DATABASE : DBCORPC
:
OBJSCHEMA : AUTHID1
OBJNAME : TSROLE
OBJTYPE : TABLESPACE STMTTYPE: ALTER OPT_SEQ_NUM: 1
OPT_KEYWORD: BUFFERPOOL
OPT_VALUE : BP1
PENDING TIMESTAMP: 2022-05-19-01.29.25.026095525390
ALTER DDL : ALTER TABLESPACE DBCORPC.TSROLE BUFFERPOOL BP1
******************************** BOTTOM OF DATA *******************************
The pending alterations are implemented when the REORG utility is executed on the object using SHRLEVEL CHANGE or REFERENCE.
The records on table SYSIBM.SYSPENDINGDDL are no longer on the table after the object in question is REORGed.
Also, when on the the TS/PC screen, the SDPEND command can be used to generate DDL that drops the pending changes selectively.
RQTSPC ------ RC/Q Table Space Pending Changes ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> PC Where => N
Table Space ===> TSR% > Creator ===> * >
Data Base ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 3 >
CMD TABLESPACE DATABASE OBJNAME OBJTYPE
________ TSROLE DBCORP1 TSROLE S
________ TSROLE DBCORPC TSROLE S
SDPEND__ TSROLE DBCORPC TSROLE S
******************************* BOTTOM OF DATA ********************************
.CONNECT ssid
SET CURRENT SQLID ='AUTHID1';
ALTER TABLESPACE DBCORPC.TSROLE
DROP PENDING CHANGES;
ALTER TABLESPACE DBCORPC.TSROLE MAXPARTITIONS 10
;
Additional Information
Tablespace Reports
PC (Tablespace Pending Changes Report)
Displays the tablespace objects that are pending definition changes.
Use SDPEND to Selectively Remove Pending Changes
Feedback
