Listing Tables or Views using RC/Query and RC/Update using wildcards
search cancel

Listing Tables or Views using RC/Query and RC/Update using wildcards

book

Article ID: 65724

calendar_today

Updated On:

Products

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

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
;