search cancel

RC/Query for Db2 for Z/OS : How to quickly find out which tablespaces have pending changes on them.

book

Article ID: 242032

calendar_today

Updated On:

Products

RC/Query for DB2 for z/OS

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: DT32  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. 

Additional Information

Tablespace Reports

PC (Tablespace Pending Changes Report)
Displays the tablespace objects that are pending definition changes.