Using SQL to get information about ASP capacity

Someone asked me if there was an easy way to monitor the available storage space in their Auxiliary Storage Pools (ASP)? I have to admit this is something I have not been asked before, and not something I worried about. I knew we were getting close to critical storage when I would receive the message from the system operator asking what could be deleted from the system before that was reached.

Fortunately in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.2 TR7, a new Db2 for i View was added to return information about the ASP.

The new View, ASP_INFO, is one of a pair of additions to Db2 for for ASP monitoring. Like all the recent additions to Db2 for i this View is found in the library QSYS2. I am not going to list all the columns here, as I will only using a few in this example. If you want a list of them all you can go to IBM's KnowledgeCenter here.

I was thinking what kind of information would I want to have to determine if my ASP was getting to "critical storage":

1. How quickly is the ASP is filling up?
2. Did this happen suddenly or was this a gradual process?
3. What libraries, in the ASP, are using up the most space?

As I am going to need to compare the current space for the ASP with previous I am going to need to save this data to tables. This time I did not say "files", as I am going to insert this data into DDL tables.

What information am I going to want about the ASP?

• ASP number
• ASP status
• Total amount of space in ASP
• Amount of unused space in ASP
• Percentage used

My table is going to be like this:

01 CREATE OR REPLACE TABLE MYLIB.ASPLIST ( 02 RUN_TIME TIMESTAMP NOT NULL, 03 ASP_NUMBER NUMERIC(2,0), 04 ASP_STATE VARCHAR(10), 05 TOTAL_CAP INTEGER, 06 UNUSED_CAP INTEGER, 07 PERCT_USED DECIMAL(6,3), 08 PRIMARY KEY(ASP_NUMBER,RUN_TIME) 09 ) ; 10 LABEL ON COLUMN ASPLIST ( 11 RUN_TIME IS 'Run time', 12 ASP_NUMBER IS 'ASP No.', 13 ASP_STATE IS 'ASP status', 14 TOTAL_CAP IS 'Total capacity', 15 UNUSED_CAP IS 'Unused capacity', 16 PERCT_USED IS 'Percent used' 17 ) ; 18 LABEL ON COLUMN ASPLIST ( 19 RUN_TIME TEXT IS 'Run time', 20 ASP_NUMBER TEXT IS 'ASP number', 21 ASP_STATE TEXT IS 'ASP status', 22 TOTAL_CAP TEXT IS 'Total megabytes in ASP', 23 UNUSED_CAP TEXT IS 'Unused megabytes in ASP', 24 PERCT_USED TEXT IS 'Percent used' 25 ) ; 26 LABEL ON TABLE ASPLIST IS 'List of ASPs' ;

Lines 1 – 9: This part defines the columns that will be in the table.

Line 2: I decided to add the Run Time, to know when this row was created and to differentiate it from earlier rows.

Line 3: I am defining the ASP number as Decimal, which is the same as Signed in a DDS file.

Line 4: I have defined the ASP state column to be VARCHAR. This will reduce the total size of my file.

Line 5 and 6: The Total Capacity and Unused Capacity columns are defined as just integer columns, in a DDS file these would be nine long binary fields.

Line 7: There is no column for the percentage used in the View. I will calculate this at the time rows are inserted.

Line 8: Gives this table a unique key.

Lines 10 - 17: This adds the equivalent of column headings to the columns. And yes you really do need those spaces.

Line 18 – 25: This adds the equivalent of the field text.

Line 26: This adds the text to the table, which is displayed when you look at the table in things like the WRKOBJPDM command.

I don't need much information about the libraries within the ASP.

• Run time, so I can link the ASP row to the library rows
• ASP number
• Library name
• Size of library

Getting the ASP number proved harder than I thought. The obvious choice was to go to the View SYSSCHEMAS, but that contains the IASP, Independent Auxiliary Storage Pool, not the ASP. After looking at various other Db2 for i Views and Table Functions I could not find what I needed, the ASP number. I came to the conclusion I would have to use the Display Object Description command, DSPOBJD, and use the fields from its output file.

DSPOBJD OBJ(*ALL) OBJTYPE(*LIB) OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPOBJD) SELECT A.ODOBNM, A.ODASP, B.IASPNUMBER FROM QTEMP.@DSPOBJD A JOIN QSYS2.SYSSCHEMAS B ON A.ODOBNM = B.SYS_NAME Object ASP IASPNUMBER Number QBRM 1 0 QCAEXP 1 0 QCA400W 1 0 QCBLLE 1 0 QCBLLEP 1 0

When I compare SYSCHEMA's IASP to DSPOBJD ASP they are different.

My libraries table looks like:

01 CREATE OR REPLACE TABLE MYLIB.LIBASPLIST ( 02 RUN_TIME TIMESTAMP NOT NULL, 03 LIBRARY VARCHAR(10) NOT NULL, 04 ASP_NUMBER NUMERIC(2,0), 05 LIB_SIZE DECIMAL(15,0), 06 PRIMARY KEY(ASP_NUMBER,LIBRARY,RUN_TIME) 07 ) ; 08 LABEL ON COLUMN LIBASPLIST ( 09 RUN_TIME IS 'Run time', 10 LIBRARY IS 'Library', 11 ASP_NUMBER IS 'ASP No.', 12 LIB_SIZE IS 'Library size' 13 ) ; 14 LABEL ON COLUMN LIBASPLIST ( 15 RUN_TIME TEXT IS 'Run time', 16 LIBRARY TEXT IS 'Library', 17 ASP_NUMBER TEXT IS 'ASP number', 18 LIB_SIZE TEXT IS 'Library size in bytes' 19 ) ; 20 LABEL ON TABLE LIBASPLIST IS 'List of libraries with size & ASP' ;

Lines 1 – 7: Define the table's columns.

Line 2: Run time is there to be part of the key to establish the relationship to the ASP rows.

Line 3: Library is define as variable character, which will reduce the size of the table.

Line 4: ASP number is defined as decimal, as it was in the ASP table.

Line 5: Size of the library in bytes.

line 6: Establishes a unique key for this table.

Lines 8 – 13: Give my columns column headings.

Lines 14 – 19: Gives the columns the equivalent of field test.

Lines 20: Gives the table a description.

Now I have the tables, what about the program to load data into them?

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-s RunTimestamp timestamp ; 04 dcl-s LongString varchar(200) ; 05 exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD ; 06 RunTimestamp = %timestamp() ; 07 LongString = 'DSPOBJD OBJ(*ALL) + OBJTYPE(*LIB) + OUTPUT(*OUTFILE) + OUTFILE(QTEMP/@DSPOBJD)' ; 08 exec sql CALL QSYS2.QCMDEXC(:LongString) ; 09 exec sql INSERT INTO MYLIB.LIBASPLIST 10 (SELECT :RunTimestamp, 11 ODOBNM, 12 ODASP, 13 ODOBSZ 14 FROM QTEMP.@DSPOBJD) ; 15 exec sql CALL QSYS2.QCMDEXC('DLTF FILE(QTEMP/@DSPOBJD)') ; 16 exec sql INSERT INTO MYLIB.ASPLIST 17 (SELECT :RunTimestamp, 18 ASP_NUM, 19 ASP_STATE, 20 TOTCAP, 21 TOTCAPA, 22 CAST(CAST((TOTCAP - TOTCAPA) AS DECIMAL(15,5)) 23 / CAST(TOTCAP AS DECIMAL(10)) 24 * 100 AS DECIMAL(6,3)) 25 FROM QSYS2.ASP_INFO) ; 26 *inlr = *on ;

Line 1: For those of you who are regular readers of this blog, you know I am only going to write in totally free RPG.

Line 2: My favorite control options

Lines 3 and 4: Variable definitions.

Line 5: Setting my SQL options, so I don't have to remember to change them when using the create command.

Line 6: I toyed about how to move the current timestamp into this variable. I could have initialized this variable with the timestamp when I defined it. But I decided against that. I have done it here as this program could be modified to have a Do loop that would loop periodically, and I would need a new timestamp for each loop.

Line 7: I am initializing this variable with the string I will be using for the DSPOBJD command to get the information about the libraries.

Line 8: I am using the Db2 for i QCMDEXC procedure to execute the command in the variable.

Lines 9 – 14: This is where I am taking the data from the DSPOBJD output file and inserting it into the Library table. The fields I am using are:

• :RunTime:  the RPG variable
• ODOBNM:  object name, which will be the library names
• ODASP:  ASP number
• OBOBSZ:  object size

As the Insert statement contains a select all of the eligible records from the output file are Inserted into the table in "one go". Much faster than doing multiple RPG write operations.

Line 15: I always delete the output files when I have finished with them.

Lines 16 – 25: Here I am retrieving the row(s) from ASP_INFO and inserting them into my table. The rows I using are:

• :RunTime:  the RPG variable
• ASP_NUM:  ASP number
• ASP_STATE:  ASP state (status)
• TOTCAP:  total capacity of ASP
• TOTCAP:  total available capacity of ASP, in other words unused space
• Percentage used

Calculating the percentage turned out to be more difficult than I first thought. A simple:

((TOTCAP – TOTCAPA) / (TOTCAP) * 100)

Returned zero. I determined that the calculation could not be performed as the columns were integers. Therefore, I would need to convert them to decimal using CAST.

CAST(CAST((TOTCAP - TOTCAPA) AS DECIMAL(15,5)) / CAST(TOTCAP AS DECIMAL(10)) * 100 AS DECIMAL(6,3))

I need to CAST the dividend part:  CAST((TOTCAP – TOTCAPA) AS DECIMAL(15,5))

And CAST the divisor independently:  CAST(TOTCAP AS DECIMAL(10))

Then finally CAST the result from the multiplication to get a column that is useful for a percentage:  CAST( ... ) * 100 AS DECIMAL(6,3))

So what does this look like? Before I show I need to explain that all the IBM i I have access to only have one ASP. Therefore, my ASP table would only contain one row.

RUN ASP ASP TIME NO. STATUS 2017-12-13-19.25.59.853000 1 NONE TOTAL UNUSED PERCENT CAPACITY CAPACITY USED 195,740 129,178 34.005

The list of libraries is long. I am only going to show the first few.

RUN LIBRARY ASP LIBRARY TIME NO. SIZE 2017-12-13-03.43.53.379000 #CGULIB 1 65,536 2017-12-13-03.43.53.379000 #COBLIB 1 114,688 2017-12-13-03.43.53.379000 #DFULIB 1 65,536 2017-12-13-03.43.53.379000 #DSULIB 1 65,536 2017-12-13-03.43.53.379000 #LIBRARY 1 73,728 2017-12-13-03.43.53.379000 #RPGLIB 1 98,304 2017-12-13-03.43.53.379000 #SEULIB 1 65,536

The next time I run this program, whether it is tomorrow or an hour's time, I can compare that ASP row to the previous one. If there is a big change in the percentage used I have the information, in the Library file, to work out what used all that available space.

 

You can learn more about the ASP_INFO View from the IBM website here.

 

This article was written for IBM i 7.3 TR3 and 7.2 TR7.