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.