Bharath's Tech Blog

YTD growth trends analysis of DASD, in z/OS

DASD growth analysis on z/OS is usually done by Capacity Planning team or Storage admin team. And, I noticed that they perform the analysis at Volume Group level, rather than at Database or Tablespace/Index level.

There can be exceptional situations where DBA team has to monitor and analyze the Database growth. My team is in such situation. We support an ERP application, and Database growth has to be monitored for the purpose of Application updates/patches.

In our existing process, multiple jobs are submitted to get Dataset and Storage information at Volume Group level. Then, this information is manually copied into Spread sheets for analysis. This process is cumbersome and usually takes at least 2 hours for completion.

I designed the new process to replace existing manual process, and takes approx. 30 minutes for completion. The new process has some new features like YTD growth trends analysis. This process is implemented by execution of a monthly scheduled job on Mainframes. The JCL of the job has the following steps.

Step 1: Get the list of Databases, for which we want the Storage details.

SELECT

   DISTINCT(CHAR(DBNAME))

FROM SYSIBM.SYSTABLEPART

WHERE DBNAME LIKE 'LD%'

WITH UR;

Note: I’m querying SYSTABLEPART rather than SYSDATABASE as there can be some empty Databases.

Step 2: Generate the LISTCAT statements for the Databases.

SELECT

   CHAR(CONCAT(CONCAT(CONCAT(CONCAT(' LISTCAT LEVEL(',

     STRIP(VCATNAME,B)),'.DSNDBD.'),STRIP(DBNAME,B)),') ALL'))

FROM SYSIBM.SYSTABLEPART

WHERE DBNAME LIKE 'LD%'

GROUP BY

   DBNAME

  ,VCATNAME

ORDER BY DBNAME

WITH UR;

Step 3: Executing the LISTCAT statements generated in Step 2.

Below JCL can be used for executing the LISTCAT statements.

//STEP030  EXEC PGM=IDCAMS

//SYSIN    DD   DSN=&&LSTLVL,DISP=SHR

//SYSPRINT DD   DSN=&&LSTOUT,

//            DISP=(NEW,CATLG,DELETE),UNIT=DISK,

//            RECFM=VB,SPACE=(CYL,(500,300),RLSE)

//*

Step 4: REXX routine to get Object-level Storage details from the LISTCAT output

I coded REXX routine that can parse the LISTCAT output, and get the Allocated & Used Storage details for Tablespaces & Indexes. These details are stored in GDG Dataset.

Step 5: Delete already existing Database Storage details for the current month, from the DB2 Table.

I created a DB2 Tablespace structure to store the Database Storage details. Whenever the job is executed, the current month’s Database Storage details (if any) are deleted from the Table. 

I’m using the below query to delete the current month’s details.

DELETE FROM DBDASDST.DBTRENDS

WHERE

YEAR(ROW_ENTRY_TS)  = YEAR(CURRENT TIMESTAMP) AND

MONTH(ROW_ENTRY_TS) = MONTH(CURRENT TIMESTAMP)

And, below is the Tablespace structure created for Database Storage details.

CREATE TABLESPACE DBTRENDS

  IN DBDASDST

  USING STOGROUP LAGTD000

    PRIQTY -1     SECQTY -1   ERASE NO

  FREEPAGE 0

  PCTFREE 5

  GBPCACHE CHANGED

  TRACKMOD YES

  COMPRESS YES

  DEFINE YES

  LOGGED

  DSSIZE 4 G

  SEGSIZE 32

  BUFFERPOOL BP1

  CCSID ASCII

  CLOSE YES

  LOCKMAX SYSTEM

  LOCKSIZE PAGE

  MAXROWS 1

  MAXPARTITIONS 10;

CREATE TABLE DBDASDST.DBTRENDS (

  DB_NAME         CHARACTER(8)   NOT NULL    WITH DEFAULT,

  ALLOC_SPACE     DECIMAL(10, 2) NOT NULL    WITH DEFAULT,

  USED_SPACE      DECIMAL(10, 2) NOT NULL    WITH DEFAULT,

  ROW_ENTRY_TS    TIMESTAMP      NOT NULL    WITH DEFAULT

  )

  PARTITION BY SIZE EVERY 4G

  IN DBDASDST.DBTRENDS

  AUDIT NONE

  DATA CAPTURE NONE

  NOT VOLATILE

  APPEND NO;

CREATE UNIQUE INDEX DBDASDST.DBTRND1

  ON DBDASDST.DBTRENDS

    ( DB_NAME ASC,

      ROW_ENTRY_TS ASC )

  USING STOGROUP LAGTX000

    PRIQTY -1     SECQTY -1   ERASE NO

  FREEPAGE 0

  PCTFREE 10

  GBPCACHE CHANGED

  DEFINE YES

  COMPRESS NO

  CLUSTER

  BUFFERPOOL BP2

  CLOSE YES

  PIECESIZE 2G

  COPY YES;

Step 6: REXX routine to get the Database Storage details and insert them into the DB2 Table (explained in Step 5)

I coded a REXX routine which will take the Databases list (generated in Step 1) and Object-level Storage details (generated in Step 4) as inputs, parse them, and generates and inserts the Database Storage details (Database Name, Allocated Space, Used Space) into the DB2 Table.

Step 7: Get the Database Storage details for YTD comparison into a Dataset.

This is the new feature developed in the process (which is not present in existing cumbersome process). Instead of comparing the current month’s Databases’ Storage details with the last month’s Storage details, current month’s details will be compared on year-to-date (YTD) basis.

E.g., if the job starts executing from May, every month’s Storage details until May 2017 will be compared to this May’s Storage details, and all details from Jun 2017 till May 2018 will be compared with details of May 2017 and this continues on. This YTD approach is very helpful and effective in Trends analysis.

I’m using the below query to get the month’s details for YTD comparison with current month’s details.

SELECT

   DB_NAME

  ,CHAR(ALLOC_SPACE)

  ,CHAR(USED_SPACE)

FROM DBDASDST.DBTRENDS

WHERE

MONTH(ROW_ENTRY_TS) = 5 AND

YEAR(ROW_ENTRY_TS)  =

  CASE

   WHEN YEAR(CURRENT TIMESTAMP) = 2016 THEN 2016

   WHEN YEAR(CURRENT TIMESTAMP) > 2016 AND MONTH(CURRENT TIMESTAMP) <= 5

              THEN YEAR(CURRENT TIMESTAMP) - 1

   WHEN YEAR(CURRENT TIMESTAMP) > 2016 AND MONTH(CURRENT TIMESTAMP) > 5

              THEN YEAR(CURRENT TIMESTAMP)

  END

WITH UR;

  

  

Step 8: Check whether the Dataset created in Step 7 is empty or not.

Below JCL can used be to check that.

//STEP075  EXEC PGM=IDCAMS

//SYSPRINT DD SYSOUT=*

//INFILE   DD DSN=&&DBSTOYTD,DISP=SHR

//SYSIN    DD *

 PRINT INFILE(INFILE) CHARACTER COUNT(1)

/*

//*

Step 9: REXX routine to generate Database Storage growth Trends details.

I coded the REXX routine that will take outputs of Step 6, Step 7 and will generate the Trends details.

This step will be executed only based on the RC from Step 8.

Step 10: Create Report for Database Storage growth Trends.

This step will be executed only based on the RC from Step 8. Below JCL can be used for that, and the output will saved in a GDG Dataset.

//STEP090  EXEC PGM=ICETOOL,

//         COND=(4,EQ,STEP075)

//TOOLMSG  DD SYSOUT=*

//DFSMSG   DD SYSOUT=*

//TOOLIN   DD DISP=SHR,DSN=DSNDBA.EGP1.PARMLIB(DBM990P3)

//INPUT1   DD DSN=&&TRENDEGP,DISP=SHR

//OUTPUT   DD DSN=DSNDBA.EGP1.DBMN0990.DBTRNDS(+1),

//            DISP=(NEW,CATLG,DELETE),UNIT=DISK,

//            RECFM=FB,SPACE=(CYL,(50,50),RLSE)

//*

Report format will be

DISPLAY FROM(INPUT1) LIST(OUTPUT) -

TITLE('*** YTD DASD GROWTH TRENDS IN EGT1 ***') PAGE -

HEADER('DBNAME')            ON(1,8,CH)           -

HEADER('CURR_MONTH_ALLOC')  ON(12,10,CH)         -

HEADER('YTD_MONTH_ALLOC')   ON(23,10,CH)         -

HEADER('ALLOC_INCR_PCT')    ON(34,3,CH)          -

HEADER('ALLOC_DECR_PCT')    ON(38,3,CH)          -

HEADER('CURR_MONTH_USED')   ON(42,10,CH)         -

HEADER('YTD_MONTH_USED')    ON(53,10,CH)         -

HEADER('USED_INCR_PCT')     ON(64,3,CH)          -

HEADER('USED_DECR_PCT')     ON(68,3,CH)

Step 11: REXX routine to build the SMTP card.

I coded the REXX routine that will build the SMTP card, needed for emailing the Database Storage growth Trends details.

This step will be executed only based on the RC from Step 8.

Step 12: Execute the SMTP card, for emailing the Database Storage growth Trends details.