RC/Query & RC/Update: Troubleshooting when an error is found by Db2 when editing a table.

book

Article ID: 242515

calendar_today

Updated On:

Products

RC/Query for DB2 for z/OS Unicenter RC/Update

Issue/Introduction

RC/Query and RC/Update are able to work together seamlessly when performing tasks. This sample scenario involves correcting a structural problem found on the DB2 database when attempting to edit table data.

Cause

An index on a table is missing.

Environment

DB2 for Z/OS

Release : R20

Resolution

Sample scenario:

Start an EDIT on table AUTHID1.TBTASK via RC/Query Tablespace/Dependancy report(TS/O) using the EDIT line command which transfers control to RC/Update to do the edit.

RQOD   20.0   ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> O    Where => N
Table Space ===> TSTASK                > Creator ===> AUTHID1                >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 01 -AUTHID1 -          LINE 1 OF 4    >
CMD        TABLESPACE   DATABASE TABLENAME           TBCREATR
________  TSTASK       DBCORP
edit____                         TBTASK              AUTHID1
________
________
******************************* BOTTOM OF DATA ********************************


This error is returned when the edit session starts in RC/Update:

********************************* TOP OF DATA *********************************
DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN
         UNAVAILABLE RESOURCE. REASON 00C9008C, TYPE OF RESOURCE
         00000200, AND RESOURCE NAME DBCORP  .TSTASK
DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXI15 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 190  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'000000BE'  X'00000000'  X'00000000'
         X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC
         INFORMATION
******************************** BOTTOM OF DATA *******************************

Using the IBM MVS/QuickRef command at the command line while positioning the cursor on the reason code : COMMAND ===> qw

reveals the reason for the problem...

00C9008C

Explanation
An attempt was made to allocate or bind to a partitioned table space for
which no partitioning key was defined.

System action
The bind or allocation process is not allowed.

User response
Define the partitioning key or partitioning index for the partitioned table
space, and rerun the job.

Problem determination
The requested operation is not performed. An SQLCODE -904 is issued, and/or
message DSNT501I is issued.

Looking at the indexes on this table....PF11 to go to the right hand screen while on the TS/O report....

RQOD   20.0   ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> O    Where => N
Table Space ===> TSTASK                > Creator ===> AUTHID1                >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 01 -AUTHID1 -          LINE 1 OF 3  <
CMD        TABLESPACE  INDEXNAME               IXCREATR
________  TSTASK
________
________               TBTASKX                 AUTHID1
******************************* BOTTOM OF DATA ********************************

There is one index and it is a NPI. There is no partitioning index on the table.

So, a partitioning index is required.

Using the "D" line command on the tablespace to get a DETAIL report from RC/Query:

RQOD   20.0   ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> O    Where => N
Table Space ===> TSTASK                > Creator ===> AUTHID1                >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 01 -AUTHID1 -          LINE 1 OF 3    >
CMD        TABLESPACE   DATABASE TABLENAME           TBCREATR
D_______  TSTASK       DBCORP
________                         TBTASK              AUTHID1
________
******************************* BOTTOM OF DATA ********************************

and then on the Tablespace Detail Page a PF11 to go to the right hand screen to get to the partition information...

RQTSD  20.0   ----------- RC/Q Table Space Detail ----------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> D    Where => N
Table Space ===> TSTASK                > Creator ===> *                      >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 02 -AUTHID1 -       FRAME 01 OF 44

LAST REORG OR LOAD REPLACE: 0001/01/01 / 00:00:00
  VCATNAME: DSNDTGP
LOGIC PART:    1  OF  44
PARTITION :    1  OF  44   PRIQTY  : 12K          N.ROWS   : N/A

It shows us that it has 44 partitions. So a new index on the table with 44 partitions is required.

Using the TEMPLATE command , copy the existing index and change the details to create a new partitioned index.

RQOD   20.0   ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> O    Where => N
Table Space ===> TSTASK                > Creator ===> AUTHID1                >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 01 -AUTHID1 -          LINE 1 OF 4  <
CMD        TABLESPACE  INDEXNAME               IXCREATR
________  TSTASK
________
TEMPLATE               TBTASKX                 AUTHID1
******************************* BOTTOM OF DATA ********************************

Change the index name , set Partitioned to Yes, and add the partitions and the limit keys as usual in this case 44 of them with the limit keys using the LIMITS command.


ROPITMP --------------- CA - Index Template -------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

Option      => T                     Object  => I           Mode  => O ONLINE
Item Name   => TBTASKP             > Creator => AUTHID1   > Where => N
SSID: SSID ---------------------------------------------------------- AUTHID1
Index Name  => TBTASKP             > Creator => AUTHID1   > Comment      => N
Table Name  => TBTASK              > Creator => AUTHID1   > Unique Rule  => YES
Buffer Pool => BP0                 Cluster    => YES       Close Dataset => YES
Partitioned => YES (TS Parts: 44)  GBP Cache  => CHANGED   Defer Build   => NO
Piecesize   =>                     Padded     => DEFAULT   Index Type    => 2
Copy        => NO                  Compress   => NO        Define        => YES
                                                           BTWOO         => NO
CMD SEQ# PS KEY-COLUMN-NAME    ORDER COLTYPE            SRCTYPE  SIZE       N
___ 1       TASK_ID            ASC   CHAR                        6          N
******************************* BOTTOM OF DATA ********************************

         EXPLICIT/IMPLICIT---------------+
CMD PART VCAT     STOGROUP PRIQTY  SECQTY  ERASE  FRPAGE  %FR
___ 1    ________ SYSDEFLT DEFAULT DEFAULT NO     0       10
___ 2    ________ SYSDEFLT DEFAULT DEFAULT NO     0       10
___ 3    ________ SYSDEFLT DEFAULT DEFAULT NO     0       10
.
.
.
         EXPLICIT/IMPLICIT---------------+
CMD PART VCAT     STOGROUP PRIQTY  SECQTY  ERASE  FRPAGE  %FR
___ 42   ________ SYSDEFLT DEFAULT DEFAULT NO     0       10
___ 43   ________ SYSDEFLT DEFAULT DEFAULT NO     0       10
___ 44   ________ SYSDEFLT DEFAULT DEFAULT NO     0       10

Create the Index and after that is created this should be the result....a new partitioned index TBTASKP.

RQOD   20.0   ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> O    Where => N
Table Space ===> TSTASK                > Creator ===> AUTHID1                >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 01 -AUTHID1 -          LINE 1 OF 4  <
CMD        TABLESPACE  INDEXNAME               IXCREATR
________  TSTASK
________
________               TBTASKP                 AUTHID1
________               TBTASKX                 AUTHID1
******************************* BOTTOM OF DATA ********************************

So then when we EDIT the table again :

RQOD   20.0   ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> TS                      Option  ===> O    Where => N
Table Space ===> TSTASK                > Creator ===> AUTHID1                >
  Data Base ===> DBCORP                > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: SSID  LVL: 01 -AUTHID1 -          LINE 1 OF 4    >
CMD        TABLESPACE   DATABASE TABLENAME           TBCREATR
________  TSTASK       DBCORP
edit____                         TBTASK              AUTHID1
________
________
******************************* BOTTOM OF DATA ********************************

The editor goes in and starts up this time.

RUEDITC  20.0   ----- RC/Edit: Positional Column Mode ---- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

For Table   => AUTHID1.TBTASK                  > Row number=> 1 OF 1
Edit Mode   => C                                 Max Char  => 070
SSID: SSID ---------------------------------------------------------- AUTHID1
OPT S TASK_ID TASK_DESC                                          TASK_DATE_FROM
___ I                                                            2022-05-25
******************************* BOTTOM OF DATA ********************************

Changing the screen display by using the FORM mode(F) edit:

Enter a new record into the table....


RUEDITF  20.0   ------ RC/Edit: Positional Form Mode ----- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

For Table => AUTHID1.TBTASK                  >   Row number=> 1 OF 1
Edit Mode => F                                   Max Char  => 256
Option    =>                                     Status    => INSERTED
SSID: SSID ---------------------------------------------------------- AUTHID1
##.COLUMN NAME        NULL  DATA FOR ROW # 1
A1.TASK_ID                  1000
A2.TASK_DESC                TASK 1000
A3.TASK_DATE_FROM           2022-05-25
A4.TASK_DATE_TO             2022-05-25
A5.TASK_PROJ_NO             8000
A6.TASK_COST                2,000
A7.TASK_CODE                C
******************************* BOTTOM OF DATA ********************************

Submit the edit to DB2 with ENTER.


RUEDITT  20.0   ---------- RC/Edit Trace Summary --------- yyyy/mm/dd hh:mm


   DB2 I/O Trace Statistics Summarization                          SSID: SSID
         Number of successfully processed FETCHES: 0
         Number of successfully processed UPDATES: 0
         Number of successfully processed DELETES: 0
         Number of successfully processed INSERTS: 1

         Statistics are cumulative for current edit session.


                       Press ENTER to continue

and the record is there:

RUEDITF  20.0   ------ RC/Edit: Positional Form Mode ----- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

For Table => AUTHID1.TBTASK                  >   Row number=> 1 OF 1
Edit Mode => F                                   Max Char  => 256
Option    =>                                     Status    =>
SSID: SSID ---------------------------------------------------------- AUTHID1
##.COLUMN NAME        NULL  DATA FOR ROW # 1
A1.TASK_ID                  1000
A2.TASK_DESC                TASK 1000
A3.TASK_DATE_FROM           2022-05-25
A4.TASK_DATE_TO             2022-05-25
A5.TASK_PROJ_NO             8000
A6.TASK_COST                2,000
A7.TASK_CODE                C
******************************* BOTTOM OF DATA ********************************

In this sample, these are the functions used:

  • EDIT of a table with RC/Update(subcomponent RC/EDIT)
  • QW - IBM MVS/QuickRef can be called from inside RC/Query or RC/Update to check the reason code of the -904.
  • "D" line command via RC/Query on the tablespace to see how many partitions are defined.
  • TEMPLATE line command via Rc/Update on an Index to make a new copy of the index that is partitioned on the same table.

Additional Information

RC/Query Line Commands

Tablespace Reports

RC/Edit Component

Index Template Option