Pages

Wednesday, July 17, 2024

List all object types using SQL

Have you ever wondered how many different types of objects there in IBM i? And what the types of objects are?

The latest Technology Refreshes give us a SQL View that contains that information, and we can query it to answer those questions.

The SQL View is called SYSTEM_OBJECT_TYPES, and it is found in the QSYS2 library. It has three columns:

  1. OBJECT_TYPE:  Object type
  2. TEXT_DESCRIPTION:  Description of what the object type is
  3. CATEGORY:  There are two categories:
    IFS - object is in the IFS
    LIBRARY - object resides in a library (not an IFS object)

I recommend you use the following statement just to see what results are returned. Don't worry it won't take long as there are not too many types of objects.

01  SELECT * 
02    FROM QSYS2.SYSTEM_OBJECT_TYPES 
03   ORDER BY CATEGORY

I am not going to show you all of the results. These are the first four results for each of the categories.

OBJECT_
TYPE     TEXT_DESCRIPTION            CATEGORY
-------  --------------------------  --------
*BLKSF   Block special file          IFS
*CHRSF   Character special file      IFS
*DDIR    Distributed file directory  IFS
*DIR     Directory                   IFS

*ALRTBL  Alert table                 LIBRARY
*AUTL    Authorization list          LIBRARY
*BNDDIR  Binding directory           LIBRARY
*CFGL    Configuration list          LIBRARY

How can I answer the question I posed at the top of the article: how many different types of objects are there? I can use the following statement to answer.

01  SELECT CATEGORY,COUNT(*) AS "No. object types"
02    FROM QSYS2.SYSTEM_OBJECT_TYPES 
03   GROUP BY CATEGORY
04   ORDER BY CATEGORY

And the answer is:

CATEGORY   No. object types
--------   ----------------
IFS                       9
LIBRARY                  92

It is a coincident that the week before the TRs were released I was looking for a way to validate several object types. Now I can use this View in a RPG program:

01  **free
02  ctl-opt dftactgrp(*no) ;

03  dcl-s Valid ind ;

04  Valid = Check('*LIB') ;
05  dsply ('*LIB ' + Valid) ;

06  Valid = Check('*LIBX') ;
07  dsply ('*LIBX ' + Valid) ;

08  Valid = Check('*DDIR') ;
09  dsply ('*DDIR ' + Valid) ;

10  *inlr = *on ;

Line 1: In 2024 your RPG needs to be free.

Line 2: As a subprocedure is called this program cannot run in the default activation group.

Line 3: Defining the indicator variable that will contain the result of whether this object type is valid or not.

Line 4: Here I call a subprocedure, Check, passing to it a object type. The subprocedure will return a value that will be placed in the variable Valid.

Line 5: I use the Display operation code, DSPLY, to show the contents of the variable Valid.

Line 6: This is the same as line 4, just for another object type. *LIBX is not a valid object type.

Line 7: Display Valid.

Line 8: This time I am passing to the subprocedure an IFS object type.

Line 9: Result of the subprocedure will be displayed.

Below is the subprocedure's source code:

11  dcl-proc Check ;
12    dcl-pi *n ind ;
13      ObjectType varchar(10) const ;
14    end-pi ;

15    dcl-s Indicator ind ;

16    exec sql SELECT '1' INTO :Indicator
17               FROM QSYS2.SYSTEM_OBJECT_TYPES
18              WHERE OBJECT_TYPE = :ObjectType
19              LIMIT 1 ;

20    return Indicator ;
21  end-proc ;

Lines 12 – 14: This the procedure's interface. It has one parameter passed to it, that I have called ObjectType. I have defined it as a constant, CONST, as I am passing strings to this subprocedure. The sub procedure returns an indicator data type indicated by the IND on line 12.

Line 15: Defining an indicator variable.

Lines 16 - 19: This is the Select statement that checks if the object type passed is in the SYSTEM_OBJECT_TYPES View. If it is a character value of "1" is placed into the variable Indicator.

Line 20: I return the value in Indicator to whatever called this subprocedure.

When I run the program the following is displayed:

DSPLY  *LIB 1
DSPLY  *LIBX 0
DSPLY  *DDIR 1

"*LIB" and "*DDIR" are valid object types. "*LIBX" is not.

 

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

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.

5 comments:

  1. I love the comment of the line 1 ;)
    ...to be free? You know me, I'm Georges Kopp.
    This is one of the reasons to name the "free" as RPG V !
    Is more modern and respectable and is true, is another language!
    Thank you 👍

    ReplyDelete
  2. Buenas, tengo version 7.4 y al querer realizar la consulta me da un error que no encuentra dicha tabla en el qsys2

    ReplyDelete
    Replies
    1. 1. Check that you are qualifying the View with the correct library name, QSYS2.

      2. Check if you have the latest PTFs installed for Database.

      Delete
  3. maybe given the low count, it would be worth as an exercise to load all in a shot into a *AUTO array and then test the assertion with the IN operator or similar lookups

    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.