Get a List of IBM i Object Types

Ever have a situation where you could swear a feature was not only available but you thought you must have been using it for more than 30 years? When I added the ability to Prompt a field in SQL iQuery Script a couple years ago, the first few prompts I wanted to create included a list of library names and a list of Object Types.

To generate a list of library names, that was easy; object types... not so much. Let's look at how to do each of these lists, starting with a list of library names.

with libraries (lib_name, row_num) as 
(????? 
select objname, row_number() OVER (order by objname)? as ROW_NUM? ? 
from table(QSYS2.object_statistics('*ALLSIMPLE','*LIB'))?
 where substr(objname,1,1) NOT IN ('#','"','Q')
)?
select listAgg( cast( schema_name as varchar(16000)), ' ')??
? within group(order by row_num)??????????????????????????????????? ?
?INTO &Libs?
FROM libraries;
        

This CTE generates a list of all library names using the IBM Object_Statistics Table Function. I could've specified QSYS or *ALLUSR as the library name, but since I only wanted the name itself, *ALLSIMPLE produces the list faster.

Next the SELECT LISTAGG() statement moves that library name list into a string of blank-separated library names. I then read that into the &LIBS host variable. This is SQL iQuery Script in case you're unfamiliar with it. The next thing I do is use that variable in the VALUES parameter of the DSPVAR SQL iQuery Script command like this:

?while isEmpty(&lib);???????????????? 
 ?dspwin chginpdft(UC),"List SQL Stmts Used in Programs";
?dspvar &lib,3,2,name(10), values(&LIBS),"Library (F4=Select from list)"; 
?EXFMT;???????????????? 
?if fKey(f3);
   return;
?endif;
endwhile;????????????????????????????????????????????????????????????????        

Basically this prompts the end-user for the library name. If they press F4 while the cursor is in the LIB field, the list of library names is shown and they can select one by placing the cursor on that name and pressing Enter. Here's what the prompter looks like in action. Note the list of libraries where I've paged down to the C's.

The ability to retrieve a list of object or library names is pretty easy--object_statistics and our own #SQLTools OBJECT_LIST() table functions do a great job of that, and with the #SQLTools MBR_LIST() Table Function, retrieving a list of Member names is just as easy--no APIs required! But what if you need a list of IBM i Object Types?

Object Type List

This is where I thought we always had a way to do this, but apparently we do not.

The ability to list the IBM i object types suffers from there being no API, no MI instruction and no SQL interface to do this. No database contains them and their number can change with each PTF level and Version/Release update. So you can't really hard-code them unless you only need a few of them where it won't matter anyway.

I need them so I can write more SQL iQuery Script macros and new #SQLTools Functions that can utilize them. For example, if you wanted to use SQL to retrieve information about a certain type of object, for example a *PGM or *SRVPGM you can easily specify VALUES(*PGM *SRVPGM) on the DSPVAR command in SQL iQuery Script, or if you need it in an RPG program, you can hard-code it there. That's not a challenge, but what is a challenge it getting a list of all of them.

I invented two methods that can be used to retrieve a list of object types. They both automatically update whenever IBM changes (adds to) the list of object types. One uses SQL Regular Expressions, and one uses a Table Function we built for #SQLTools.

SQL Object Types List using RegEx

If you have the IBM licensed program object 5770-SS1 Option 13 (Openness Includes) installed, you can use the regular expression approach. If not, skip down to the #SQLTools solution.

The Source File Member name MIOBJTYP in QSYSINC contains a set of conversion macros used in C/C++ programs with the QLICVTTP API to convert between the 2-byte internal object type, and the human-facing object type. So I put together a little SQL Regular Expression that can not only read that source member, but parse the object types and return a nice OBJTYPE column.

cl: ovrdbf mih tofile(qsysinc/mih) mbr( MIOBJTYP ) ovrscope(*JOB);
select
??? regexp_replace( regexp_substr(srcdta,'(\bWLI_)\S+'), '(\bWLI_)','*') 
??? as OBJTYPE
from QSYSINC.MIH??????????????????????????????????????????????????? ?
where regexp_like(srcdta,'(\bWLI_)');        

Note the use of the "CL:" directive on the first line. This causes the processor to run the statement using QCMDEXC in ACS Scripts and QCAPCMD in SQL iQuery instead of the SQL engine. The SQL statement itself contains 3 regular expression functions utilizing two distinct regular expressions. The RegEx functions pull the Object Type out of the source member and add an asterick prefix. The result is returned as the OBJTYPE column. I suppose you should CAST the result to VARCHAR(10) for good measure but that isn't done in this example.

Here's what it looks like when I run it in IBM ACS, but it works in iQuery Script as well, which I've illustrated in the 2nd image below.

#SQLTools To Retrieve a List of Object Types

Another way to do it is with the #SQLTools RTVCMDPARM table function. This function returns a list of CL Command Parameters and their options for any CL command on the system. Using it, I put together a simple SQL statement to retrieve the object type from the DSPOBJD CL command. DSPOBJD seems to have most of the non-IFS related object types. There are other IFS-related commands you can use for IFS object types however. A list that appears when I do an exception join over my two techniques produces the IFS object type list.

Using RTVCMDPARM there are no overrides, no dependencies on QSYSINC, and it is automatically updated with each new release or PTF update to your system. More info on the RTVCMDPARM table function can be found at the #SQLTools webpage. That example is below:

Using the CTE technique or the RTVCMDPARM technique I can easily integrate object types into into my code as you can into your own user-written applications. For an example of the iQuery Object Type Prompter, see the main/top photo associated with this article.

One more thing: To get a list of the IFS Object Types, the WRKLNK CL command may be used with our RTVCMDPARM table function. However that command also supports nearly all IBM i object types so is it complete and perhaps should be used in stead of DSPOBJD? The short answer is no. It lacks a couple of IBM object types. I put together an SQL JOIN operation to show the differences between the OBJTYPE parameter of the DSPOBJD and WRKLNK commands. Since SQL doesn't really have a "FULL OUTER JOIN" it is really more of a merge or full join instead, I used the WHERE clause with IS NULL to fake it. Here are the results:

Note the *PDFMAP and *NWSCFG objects from DSPOBJD that are not included in WRKLNK. This is clearly just an oversight by IBM since WRKLNK will show you *PDFMAP objects when you specify *ALL for OBJTYPE. Note that I also ran the above join between DSPOBJD and WRKOBJ and there are no differences so those two are in sync.

So there you have it; two techniques to list the type of Objects available to the IBM i operating system. One is available on most systems, the other requires #SQLTools which should be on everyone's system.

SQL Tools picks up where the "IBM Services" leaves off. With over 170 functions in SQL Tools virtually every API or other similar interfaces has been provided in SQL Function form for you to use without the need to setup a program or call an API. The IBM Services is a great base, but SQL Tools completes the implementation. And at just $695 OTC, you really should've already ordered it for your employer.

See http:/www.SQLiQuery.com/SQLTools for more information.

That's all there is to it.