Identify the LOB objects that are contained in a Database, Tablespace or Table.
Article ID: 282111
Updated On: 04-16-2024
Products
Issue/Introduction
How to obtain the details of LOB objects on a Database , Tablespace or Table in order to identify the auxiliary object names.
Environment
DB2 for Z/OS
Resolution
On the RC/Query DB/L , TS/L and T/L reports there is a line command called "LR"
On each object level the report is based on that object as the primary and so the report is different.
At Database level , the Database Lob Relationship report displays Lob Relationship information for all the LOB objects associated with the selected database including the
Tablespace and table containing the LOB along with LOB column name, the auxiliary table and auxiliary index name.
RQDBL ------------- RC/Q Data Base List ------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> DB Option ===> L Where => N
Data Base ===> PLANDB > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: SSID ----------authid1 - LINE 1 OF 1 >
CMD DATABASE CREATOR STOGROUP CREATEDB DBID BPOOL INDEXBP IMP REL
LR______ PLANDB authid1 SYSDEFLT authid1 2313 BP0 BP0 N Q
******************************* BOTTOM OF DATA ********************************
.
.
.
RQDBLR ------ RC/Q Data Base Lob Relationships ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> DB Option ===> LR Where => N
Data Base ===> PLANDB > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: SSID LVL: 01 -authid1 - LINE 1 OF 3 >
CMD DATABASE CREATOR TSNAME PART TBNAME TBOWNER
________ PLANDB authid1
________ DSNR1JYT 1 DSN_STATEMENT_CACH> authid1
________ DSNRUSER 1 DSN_USERQUERY_TABLE authid1 <==== More columns to the right!
PF11 to see the other columns of the report.
At Tablespace level, the Tablespace Lob Relationship report displays Lob Relationship information for LOB objects associated with the selected tablespace including the
table name,column name, the auxiliary table and auxiliary index name.
RQTSL ------------ RC/Q Table Space List ------------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> L Where => N
Table Space ===> DSNRUSER > Creator ===> authid1 >
Data Base ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: SSID ----------authid1 - LINE 1 OF 1 >
CMD NAME CREATOR DATABASE PART BPOOL DBID OBID PSID
LR______ DSNRUSER authid1 PLANDB 1 BP0 2313 14 15
******************************* BOTTOM OF DATA ********************************
.
.
.
RQTSLR ----- RC/Q Table Space Lob Relationships ------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> LR Where => N
Table Space ===> DSNRUSER > Creator ===> authid1 >
Data Base ===> PLANDB > N/A ===> * >
Loc: LOCAL ---------- SSID: SSID LVL: 01 -authid1 - LINE 1 OF 2 >
CMD NAME CREATOR DATABASE PART TBNAME TBOWNER
________ DSNRUSER authid1 PLANDB
________ LVSFTH7H authid1 1 DSN_USERQUERY_TABLE authid1 <==== LVSFTH7H in this case is the DB2 genned auxiliary LOB
******************************* BOTTOM OF DATA ******************************* tablespace for the QUERY_TEXT column in table DSN_USERQUERY_TABLE
which is a CLOB. More columns to the right!
PF11 to see the other columns of the report.
At Table level, the Table Lob Relationship report displays lob relationship information for LOB objects associated with the selected table including the tablespace name,
Database name , the column name and the auxiliary index name.
RQTL --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => N
Table Name ===> DSN_USERQUERY_TABLE > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: SSID ----------authid1 - LINE 1 OF 1 >
CMD TABLE NAME CREATOR DATABASE TBLSPACE COL_CNT NUMBER OF ROWS
LR______ DSN_USERQUERY_TAB> authid1 PLANDB DSNRUSER 20 N/A
******************************* BOTTOM OF DATA ********************************
.
.
.
RQTLR -------- RC/Q Table Lob Relationships --------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> LR Where => N
Table Name ===> DSN_USERQUERY_TABLE > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: SSID LVL: 01 -authid1 - LINE 1 OF 2 >
CMD NAME CREATOR TSNAME LOBTS DBNAME PART
________ DSN_USERQUERY_TABLE authid1
________ DSN_UQUERYVSFTN0LE authid1 DSNRUSER LVSFTH7H PLANDB 1 <=== DSN_UQUERYVSFTN0LE in this case is the DB2 genned auxiliary
******************************* BOTTOM OF DATA ******************************** tablename for the QUERY_TEXT column in table DSN_USERQUERY_TABLE
which is a CLOB. More columns to the right!
PF11 to see the other columns of the report.
The reports are also able to be run in batch with the Batch Reporting function of RC/Query.
Additional Information
Feedback
