CA RC/Update for DB2 for Z/OS : How to show the used length of a varchar column when browsing data?

Products

• RC/Update for DB2 for z/OS

Issue/Introduction

How to show the used length of a varchar column when browsing data? It can be difficult to see how much space of a varchar column has actually been used as opposed to the maximum length of the column.

Environment

DB2 for Z/OS

Resolution

When browsing a table using RC/Update, there is a command called EXPLODE which can show the used length of the data in a column.

Consider CA Database Management for DB2 for Z/OS product table  PTI.PTGL500_HISTORY

This table contains VARCHAR columns one of which is : PUT_CMD_TEXT VARCHAR   with a max length of 29,000 characters. 

As an example , when browsing this table PF11 to the right to find this column on the screen.

For this example this screen can be seen:

RUBROWC  20.0   --------- RC/Browse: Column Mode --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> EXPLODE                                          SCROLL ===> CSR

For Table   => PTI.PTGL500_HISTORY             > Row number=> 5 OF 22102
Browse Mode => C                                 Max Char  => 070
SSID: ssid ----------FETCH STATUS: ABEND U0068----------------------- authid1
      PUT_CMD_TEXT
      RECOVER-TOOL ESTIMATE-RECOVERY.TABLESPACE QCDBK01.QCTSKR03.TABLESPACE
      RECOVER-TOOL ESTIMATE-RECOVERY.TABLESPACE QCDBK01.QCTSK*.
      RECOVER-TOOL ESTIMATE-RECOVERY.TABLESPACE QCDBK01.QCTSK*.
      RECOVER-TOOL ESTIMATE-RECOVERY.TABLESPACE FRDBVK01.FRTSVK04.TABLESPACE
      RECOVER-TOOL ESTIMATE-RECOVERY.TABLESPACE FRDBVK01.FRTSVK08.TABLESPACE
      OLIST DROP BNRTST01.TSBASE.OLIST DROP BNRTST01."TS_ALL".OLIST DROP BNR
      OLIST DROP BNRTST01.TSBASE.OLIST DROP BNRTST01."TS_ALL".OLIST DROP BNR
      OLIST DROP BNRTST01.TSBASE.OLIST DROP BNRTST01."TS_ALL".OLIST DROP BNR


For this sample we want to look at the line above in red.

In the COMMAND line enter the EXPLODE command(as above) and position the cursor on the line that will be examined.

This screen is displayed: 

RUVIEWE  20.0   -------- RC/Edit View Explode Facility ------- yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ==

Table Name   => PTGL500_HISTORY     > Column Name     => PUT_CMD_TEXT
Table Creator=> PTI       >           Column Type     => VARCHAR(29000)
DB2 SSID     => ssid                  Column Length/50=> 580

            ----+----1----+----2----+----3----+----4----+----5
     ****** ****************** Top of Data *******************
     000001 OLIST DROP BNRTST01.TSBASE OLIST DROP BNRTST01."TS
            44455245452445555332554454044455245452445555332255
            FC934042F002E243401E432135DFC934042F002E243401E243
     --------------------------------------------------------
     000051 _ALL" OLIST DROP BNRTST01."TSXML" OLIST DROP BNRTS
            54442044455245452445555332255544204445524545244555
            F1CC2DFC934042F002E243401E2438DC2DFC934042F002E243
     --------------------------------------------------------
.
.
.

The screen is filled up with data so the column is rather long....VARCHAR(29000)

The data is displayed in 50 byte groups with a start of line number seen on the left so the sample above goes up to 100 bytes.

For a very long column like this type in the ispf "M" in the command line and PF8 to go to the end of the column display...

Down at the end this screen can be seen:

RUVIEWE  20.0   -------- RC/Edit View Explode Facility ------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ==

Table Name   => PTGL500_HISTORY     > Column Name     => PUT_CMD_TEXT
Table Creator=> PTI       >           Column Type     => VARCHAR(29000)
DB2 SSID     => ssid                  Column Length/50=> 580

            ----+----1----+----2----+----3----+----4----+----5
     014951  LIST BNRTST01."WNXPARTNCY" ALL OLIST LIST BNRTST0
            24455244555533225455455445224440444552445524455553
            0C93402E243401E27E80124E39201CCDFC9340C93402E24340
     --------------------------------------------------------
     015001 1."WNXPARTECN" ALL OLIST LIST BNRTST01."WNXPARTNCN
            32254554554442244404445524455244555533225455455444
            1E27E8012453E201CCDFC9340C93402E243401E27E80124E3E
     --------------------------------------------------------
     015051 " ALL OLIST LIST BNRTST01."IXNPI" ALL
            22444044455244552445555332245454224440222222222222
            201CCDFC9340C93402E243401E298E09201CCD000000000000
     --------------------------------------------------------
     ****** ***************** Bottom of Data *****************

So now above on the last line the start column is at 15051 ending on column 38 which gives a total length of 15088 including the x'0d' character Carriage return at the end of the row. 

By SQL this can be executed to get the same result using an SQL engine like CA Batch Processor:

SELECT  LENGTH(RTRIM(PUT_CMD_TEXT))
   FROM PTI.PTGL500_HISTORY
  WHERE ( PUT_JOBNAME = 'BN#QCT1#'
    AND PUT_JOBID = 'JOB18930'
    AND PUT_END_DATETIME = '2020-10-05-05.00.55.000000' )
    WITH UR

Result, there are three records that match the predicate above based on the initial record listing shown above.

3 ROWS RETRIEVED
       #1
    15088
    15088
    15088

Additional Information

Expand and View Data for Long Character Columns