Wednesday, December 13, 2017

Using SQL to get information about ASP capacity

sql view asp_info get data about asp

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.

7 comments:

  1. excellent Mr Smithers (Simpsons)

    ReplyDelete
  2. Thanks, Simon. And thanks, IBM for adding these useful views and tables. I've used the API QWCRSSTS to retrieve disk info. Simon, at line 15, couldn't you have just done EXEC SQL DROP TABLE qtemp.@DSPOBJD ?

    ReplyDelete
    Replies
    1. I could have used the DROP TABLE instead, but I wanted this to be an example of how you can delete tables using the DLTF command too.

      Delete
  3. Yet another idea.
    ... creating an UDTF that includes all information. Instead of executing multiple steps manually, a single SELECT-Statement must be performed.

    Something like this:
    CREATE OR REPLACE FUNCTION HSCOMMON10/UDTF_ASPINFO ( )
    RETURNS TABLE (RUNTMPSTP TIMESTAMP ,
    ODOBNM VARCHAR(10) ,
    ODASP DECIMAL(3, 0) ,
    ODOBSZ DECIMAL(11, 0),
    ASP_NUM INTEGER ,
    ASP_STATE VARCHAR(10) ,
    TOTCAP BIGINT ,
    TOTCAPA BIGINT ,
    PERCENT DECIMAL(11, 2) )
    LANGUAGE SQL
    SPECIFIC HSCOMMON10/UDTF_ASPINFO
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    SET OPTION COMMIT = *NONE ,
    DBGVIEW = *SOURCE
    BEGIN
    CALL QCMDEXC ( Trim('DSPOBJD OBJ(*ALL) ' concat
    'OBJTYPE(*LIB) ' Concat
    'OUTPUT(*OUTFILE) ' Concat
    'OUTFILE(QTEMP/XDSPOBJD) ' Concat
    'OUTMBR(*FIRST *REPLACE)') ) ;

    RETURN SELECT CURRENT_TIMESTAMP , Trim(ODOBNM), ODOASP , ODOBSZ ,
    ASP_NUM , ASP_STATE , TOTAL_CAPACITY , TOTAL_CAPACITY_AVAILABLE ,
    100,00 * ( TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE ) / TOTAL_CAPACITY AS PERCENT
    FROM XDSPOBJD JOIN ASP_INFO ON ODASP = ASP_NUM ;
    END ;

    The UDTF can be called as follows:
    Select * from Table(UDTF_ASPINFO()) x;

    ReplyDelete
  4. Every weekend rtvdskinf runs on our system. We archive the qaezdisk output file and generate reports comparing current qaezdisk to last week's. This keeps us in tune to storage issues on our system.

    ReplyDelete
  5. Thanks alot, Simon. This really helped me alot for monitoring. I have tried with basic query as per my understanding which resulting 0 and after using CAST, showed up the required result.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.