RC/Query & RC/Update: Troubleshooting when an error is found by Db2 when editing a table.
Article ID: 242515
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.
Environment
DB2 for Z/OS
Release : R20
Cause
An index on a table is missing.
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
Feedback
Yes
No
Powered by
