Wednesday, April 17, 2019

Discover size of library using SQL

get library size using sql

Someone asked me if there was a way, using SQL, to determine the size of a library?

This is a trick question as few people realize that the size of a library and the total size of all the objects within are different.

The questioner clarified that they wanted to know the total size of all of the objects in the library. But I gave him examples showing the difference between the two.

He was using the Display Library command, DSPLIB and was trying to find a way to get the same total from this command in a way it could be retrieved programmatically.

In the days before all the cool Db2 for i views, table functions, etc. I would have used the Display Object Description command, DSPOBJD. To get the size of the library I would have used the following:

DSPOBJD OBJ(MYLIB) OBJTYPE(*LIB) OUTPUT(*OUTFILE) 
          OUTFILE(QTEMP/@DSPOBJD1)

I am going to "cheat" here and display the size using a SQL statement. If I was not able to use SQL I would have written an RPG program to read the output file instead.

SELECT ODOBNM,ODOBSZ FROM QTEMP.@DSPOBJD1

Object    Object
            Size
MYLIB    196,608

And what if I wanted to know the total size of all the objects in that library. I would have used the DSPOBJD command again:

DSPOBJD OBJ(MYLIB/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) 
          OUTFILE(QTEMP/@DSPOBJD2)

This time is looking for all the objects in the library MYLIB.

This SQL statement gives me the total, SUM, of the size of all the objects.

SELECT SUM(ODOBSZ) FROM QTEMP.@DSPOBJD2

SUM ( ODOBSZ )
    10,215,424

That is a big difference in the numbers. Remember that the number for the library is just the library's size, which does not include the objects within the library.

DSPOBJD make this a two-step process, what I want is a single step method. For the size of the library I could use the Db2 for i SCHEMAS view:

SELECT SYSTEM_SCHEMA_NAME,SCHEMA_SIZE 
         FROM QSYS2.SYSSCHEMAS 
        WHERE SYSTEM_SCHEMA_NAME = 'MYLIB'

SYSTEM_SCHEMA_NAME   SCHEMA_SIZE
    MYLIB               196,608

I found it interesting that in the IBM documentation they do not recommend using this view for the size of the library, or schema in SQL parlance. They recommend using the OBJECT_STATISTICS table function instead. Notice how I pass the name of the library as the first parameter, and LIB to denote I only want the statistics for the library with this name.

SELECT OBJNAME,OBJSIZE 
  FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','LIB')) AS A

OBJNAME   OBJSIZE
MYLIB     196,608

For the total size of the objects in the library again I use the SUM function. The parameters passed to the table function are a bit counter to what I did in the previous statement just of the library's data. This time I want the details for all the objects, hence the ALL in the second parameter, and the library these objects are in has to be the first parameter.

SELECT SUM(OBJSIZE) 
  FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','ALL')) AS A

SUM ( OBJSIZE )
     10,215,424

If I wanted to I could put the last statement in a RPG program, and make the library name a parameter that would be passed to the program.

01  **free
02  ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ;

03  dcl-pr Main extpgm('TESTRPG') ;
04    *n char(10) ;  //Library
05    *n uns(10) ;   //Total size
06  end-pr ;

07  dcl-proc Main ;
08    dcl-pi *n ;
09      Library char(10) ;
10      TotalSize uns(10) ;
11    end-pi ;

12    exec sql SELECT SUM(OBJSIZE) INTO :TotalSize
13               FROM TABLE
14                 (QSYS2.OBJECT_STATISTICS(:Library,'ALL'))
15               AS A ;

16    dsply ('Size = ' + %triml(%editc(TotalSize:'J')) ) ;
17  end-proc ;

Line 1: In 2019 I have to use totally free RPG.

Line 2: These control options mean the following:

  • main(Main)  the program is not using the RPG cycle
  • option(*srcstmt)  when the program is compiled it will use source statement line numbers, rather than the system generated ones
  • dftactgrp(*no)  as there is a procedure in the program it cannot run in the default activation group

Lines 3 – 6: The program prototype for this program. The program name must be in the EXTPGM keyword. This program has two parameters, which I never bother to name in the prototype, but I do have to give their data types.

Line 7: Start of the Main procedure.

Lines 8 – 11: The procedure interface for the Main procedure. I never bother to name the interface, but I must the parameters their names'.

Lines 12 – 15: This is the same as the last SQL statement. I am taking to total of the sizes of all the objects and putting in the variable TotalSales. And in the table function's parameters the hard coded library name has been replaced with the parameter passed to this program, Library.

Line 16: This line is only here as this is a test program, so I can show value returned from the SQL statement. I have used the %EDTIC built in function to format the number nicely with "thousand separators", and the %TRIML removes the leading spaces in the transformed number.

Line 17: End of the Main procedure.

When I call this program, passing MYLIB to it, the following is displayed:

DSPLY  Size = 10,215,424

 

This article was written for IBM i 7.3, and should work for some earlier releases too.

8 comments:

  1. Thanks Simon! Cool stuff.
    Whats up with RNF1324 "Keywords DFTACTGRP, ACTGRP, or USRPRF are not allowed." on the ctl-opt line when compiling with CRTRPGMOD?

    ReplyDelete
    Replies
    1. Those keywords are not allowed for a module. See IBM's documentation here.

      Delete
  2. Very good

    ReplyDelete
  3. dftactgrp(*no) as there is a procedure in the program it cannot run in the default activation group

    Programs with procedures in it CAN run in the default activation group and since you called this program from the command line, it did.

    Dftactgrp(*no) continues to be one of the most confusing and misunderstood keywords out there. *NO does not mean the program can not or will not run in the default activation group. It means that the program will not automatically run in the default activation group like OPM programs do. In other words, it will not default to the default activation group, pun intended. Instead, it will run in the activation group you specify with the ACTGRP keyword. If you do not specify ACTGRP on your Dcl-opt, the compiler will take the ACTGRP value from the compile command and the default for that is *CALLER. Which means that if you run this interactively from a command line, your program will in fact be running in the default activation group because the command line itself runs in the default activation group. Just start a new session, do DSPJOB, 11, F11 and you'll see it.

    I hasten to add that I am not recommending ILE programs be run in the default activation group lest I get an earful from Jon about that. I am merely clarifying that ILE programs and programs with procedures or that call external procedures CAN run in the default activation group and nobody has ever successfully convinced me that programs run faster in a named activation group. Arguments in favor of not running programs in the default activation group mostly revolve around error handling and clean-up.

    This particular program however uses embedded SQL, has source type SQLRPGLE and requires compile command CRTSQLRPGI. That compile command could care less about Dftactgrp because it's not even a keyword on that compile command. Perhaps IBM wised up after CRTBNDRPG and realized how incredibly confusing and useless and almost redundant that whole Dftactgrp keyword was and did away with it when they invented CRTSQLRPGI.

    01 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no);
    DSPPGM shows Activation group attribute *CALLER

    02 ctl-opt main(Main) option(*srcstmt) ;
    Compiles just fine and DSPPGM shows Activation group attribute *CALLER

    03 ctl-opt main(Main) option(*srcstmt) dftactgrp(*yes);
    Compiles just fine and DSPPGM shows Activation group attribute *CALLER

    ReplyDelete
  4. So how can you control the activation group this program will actually run in then?

    Option 1:
    ctl-opt main(Main) option(*srcstmt) dftactgrp(*yes) ACTGRP('SIMON');

    DSPPGM shows Activation group attribute SIMON
    Since Dftactgrp is not even a keyword on the compile command for this program, you can and should just omit it

    Option 3: Specify it on the compile command:
    CRTSQLRPGI OBJ(TESTRPG) COMPILEOPT('ACTGRP(DAN)')

    DSPPGM shows Activation group attribute SIMON
    Say what? I compiled it to run in DAN, why does it say SIMON? Because this line of code is still in the program:
    ctl-opt main(Main) option(*srcstmt) Actgrp('SIMON');
    The ctl-opt takes precedence

    Option 3:
    ctl-opt main(Main) option(*srcstmt); or
    ctl-opt main(Main) option(*srcstmt) ACTGRP(*CALLER);
    and then call it from another program that is compiled to run in a named activation group

    Option 4:
    Change the CRTSQLRPGI compile command default for keyword ACTGRP from *CALLER to a name. I don't recommend this option.


    What is the purpose of Dftactgrp(*no) then?
    Nothing other than saying "it's not *yes.". I can't think of too many cases where I'd deliberately specify *yes but it was invented to convert OPM programs to ILE and, provided they do not use procedures or need to bind to anything or do other ILE type things, they can be compiled with *yes which will cause them to always run in the default activation group, just like when they were still OPM programs. Basically dftactgrp was a vehicle to transition to ILE without subjecting your program to the way ILE programs are handled in named activation groups with respect to override scoping, reclaim resources etc.


    When do I need to put Dftactgrp(*no) in my program then?
    -Never, if the compile command required for your program does not even have that keyword
    -Never, if you specify ACTGRP because as soon as you do that, dftactgrp(*no) is assumed and it overrides whatever value the compile command may have for dftactgrp
    -Never, if you convince your system administrator that dftactgrp(*yes) is a suboptimal default and have him change it to *no. (And script it to do that after every OS upgrade.)
    -If your compile command has the dftactgrp keyword and has value *yes and your program uses procedures.
    -Always, if you just don't want to think about it, use the old no-harm no-foul approach


    If the compile command calls for DftActgrp and I specify Dftactgrp(*no) on the H spec of Ctl-opt, how do I explain in one line why that's there?
    Because the program uses ILE features such as procedures, it can not be defaulted to run in the default activation group like an OPM program. Because nobody will get that without a whole dissertation to explain Aggrevation Groups, I would probably just skirt around it and simply say "ILE programs that use procedures won't compile without dftactgrp(*no).

    ReplyDelete
  5. If one has the coreutils-gnu rpm package installed, this works quite nicely in a PASE shell to find the size of all the objects in a library:

    du -hs /qsys.lib/mylib.lib

    ReplyDelete
    Replies
    1. It seems to want a capital H for that switch, not h. What does the result of that command represent though?

      du -Hs /qsys.lib/TESTLIB.lib
      215,328,952 /qsys.lib/TESTLIB.lib (0.2 GB)


      The object_statistics() UDTF returns:
      SUM ( OBJSIZE )
      110,245,285,888 (110 GB)

      summing up the results of DSPOBJD returns
      110,245,285,888 (110 GB)

      DSPOBJD TESTLIB *LIB returns
      Object Type Attribute Size
      TESTLIB *LIB PROD 3,137,536

      which as Simon explained is the size of the library object itself, not the total size of everything in it.

      I'll stick with the UDTF because it's easy to do all libraries in one shot and write the results to a file to monitor growth trends.

      Delete
    2. If one uses GNU du from the coreutils-gnu package, it will recognize the lower case -h parameter, which displays the size in human readable format. Also, it looks like GNU du reports the total size of all objects in the library, where /QOpenSys/bin/du doesn't. I just tried both on an example library. GNU du reported its size as 66GB, where /QOpenSys/bin/du reported its size as 137,446,160/

      Delete

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.