Listing Tables or Views using RC/Query and RC/Update using wildcards
Article ID: 65724
Updated On: 10-25-2023
Products
Issue/Introduction
Underscores (_) can be used in view or table names as a real character and NOT a wildcard to list tables or views in RC/Query for Db2 for z/OS (RCQ)
and/or RC/Update for Db2 for z/OS (RCU). Poor response times could be experienced because the underlying SQL will utilize the LIKE predicate which
does a tablespace scan against the Db2 catalog tables.
RQTL 20.0 --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => N
Table Name ===> table_% > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 4 >
CMD TABLE NAME CREATOR DATABASE TBLSPACE COL_CNT NUMBER OF ROWS
________ table_1 authid1 dbname tsname01 4 N/A
________ table_2 authid1 dbname tsname02 2 N/A
________ table_3 authid1 dbname tsname03 5 N/A
________ table_4 authid1 dbname tsname04 3 N/A
******************************* BOTTOM OF DATA ********************************
The SQL that is used is :
Current SQL Statement:
SELECT A.NAME , A.CREATOR , A.DBNAME , A.TSNAME , CASE WHEN A.CARDF >
9223372036854775807 THEN 9223372036854775807 ELSE BIGINT ( A.CARDF )
END , A.COLCOUNT , A.TYPE , A.DBID , A.OBID , A.CLUSTERTYPE , A.EDPROC
, A.VALPROC , A.PCTPAGES , A.PARENTS , A.CHILDREN , A.KEYCOLUMNS ,
A.RECLENGTH , A.STATUS , A.KEYOBID , A.CHECKFLAG , A.AUDITING ,
A.CREATEDBY , A.LOCATION , A.TBCREATOR , A.TBNAME , A.CREATEDTS ,
A.ALTEREDTS , A.DATACAPTURE , A.RBA1 , A.RBA2 , A.PCTROWCOMP ,
A.STATSTIME , A.CHECKS , A.CHECKRID5B , A.ENCODING_SCHEME ,
A.TABLESTATUS , A.NPAGESF , A.SPACEF , A.AVGROWLEN , A.RELCREATED ,
A.NUM_DEP_MQTS , A.VERSION , A.PARTKEYCOLNUM , A.SPLIT_ROWS ,
A.SECURITY_LABEL , A.OWNER , A.APPEND , A.OWNERTYPE , A.CONTROL ,
A.VERSIONING_SCHEMA , A.VERSIONING_TABLE , A.HASHKEYCOLUMNS , A.LABEL
, A.REMARKS , A.CHECKRID , A.CLUSTERRID , A.ARCHIVING_SCHEMA ,
A.ARCHIVING_TABLE , A.STATS_FEEDBACK , A.REGENERATETS
FROM SYSIBM.SYSTABLES A
WHERE A.TYPE IN ( 'T' , 'G' , 'X' , 'M' , 'C' , 'P' , 'H' , 'R' , 'D' )
AND A.CREATOR = 'authid1'
AND A.NAME LIKE 'table_%'
ORDER BY A.NAME , A.CREATOR WITH UR
******************************* BOTTOM OF DATA ********************************
Resolution
In order to get a better response time, consider creating an additional index on SYSTABLES or/and SYSVIEWS, using columns (NAME, CREATOR) followed by a REBIND.
This should improve performance significantly. In general, there is a much better filter factor on NAME compared to CREATOR, particularly in ERP environments,
where it is common to use a limited number of creators, but have thousands of different tables names.
(Optional) Create Catalog Indexes Index Summary by Product Name
A DDL skeleton in member CATDTX06 can be used to create an index to improve performance when generating reports on objects that have an underscore as part of the name or when CREATOR is not specified or has a low cardinality.
See: Skeleton DDL in hlq.CDBASAMP(CATDTX06) and follow the instructions
CREATE INDEX &CREATOR.CATDTX06
ON SYSIBM.SYSTABLES
(NAME ASC
,CREATOR ASC
)
USING STOGROUP &STOGP
PRIQTY &PQTY
SECQTY &SQTY
ERASE NO
FREEPAGE &FPAGE
PCTFREE &PCFREE
BUFFERPOOL &BPOOL
;
Feedback
