Resolving an Object's Library Name #IBMi
I'm always writing code that needs to support *LIBL for objects, but sometimes interfaces require the object to have a hard-code library name qualifier, while others don't support all of the *xxxxLIBL symbolic names.
Find My Object
For example, suppose you need to locate a data area named THX1138 anywhere on the system. The QUSROBJD API and RTVOBJD CL command support *CURLIB and *LIBL but not *ALLUSR or *USRLIBL, and certainly not *ALL. So they are of no use. The DSPOBJD CL command and the QGYOLOBJ API support the following library list symbolic names, so they'd be able to help. But DSPOBJD is more of a CL Programmer's tool and have you ever read the QGYOLOBJ API documentation and said "Oh, sure I can do that, no problem"?
On V7R2 systems, our SQLTools product includes an SQL Table function named RESOLVER that allows you to pass in the object and type and it finds that object based on the optional library parameter. The library may be one of the 5 symbolic names. RESOLVER returns to you the library name where the object was found along with a few other attributes. You can then pass that resolved library name to another interface. Other information returned includes: owner, creator, and the object's creation and last-used date. The Resolver Table function in SQL Tools returns just one row (record) so the "first" object if find is what you get. This was done on purpose.
With the latest technology refresh, IBM updated the OBJECT_STATISTICS Service ("Service" being the term IBM uses to classify the collection of SQL Functions and Procedures that they developer and ship with IBM i). It now accepts an object name as the 3rd input parameter, this makes the resultSet return much faster than older versions. Previously you had to specify a WHERE clause to select the object names in which you were interested. That means the function had to produce the entire list (every object in the library or libraries) and then you had to omit those you didn't want--very slow to get the data you needed. For example
select objlib,objname,objtype
from table(QSYS2.Object_Statistics('*ALLUSR','*PGM')) OS
WHERE OBJNAME = 'PICKLES';
This older version returned every *PGM object in every user library, and then the WHERE clause would throw away the stuff you didn't want. That is, the objects not named 'PICKLES'. Very lengthy. But today, we can pass in the object name as the 3rd parameter and it scans the object index for the library and only pulls those that match, something like this:
领英推荐
select objlib,objname,objtype
from table(QSYS2.Object_Statistics('*ALLUSR','*PGM','PICKLES')) OS;
Of course the down-side of the progressive implementation is that we now have parameters that are in a very unexpected order, but at least now you can pass in parameter 3 and bam! it returns very fast.
Our SQLTools OBJECT_LIST Table Function is similar to OBJECT_STATISTICS but accepts the object name regardless of what IBM i release you have installed, and even works on V7R2. It has similar performance properties to OBJECT_STATISTICS, plus has the added advantage of ignoring upper/lower case differences in the input library, object and object type parameters. In addition the parameters sequence is more natual (library, object, object-type). That in itself is worth the relatively cheap $700 license fee for the over 100 SQL Functions included in SQLTools.
But hey, I'm a nice guy, so I've written a V7R4 version of our RESOLVER that is named RESOLVE (no trailing 'R') that uses OBJECT_STATISTICS. I've cleaned up the awkward parameter list of OBJECT_STATISTICS and enabled case-insensitivity for the input parameters. Since RESOLVE only needs one Object Type entry, I've limited that parameter to VARCHAR(10) but you can easily modify it to handle more. The only reason I can see specifying multiple object types is if you wanted to control the results to "only one of this types" to be returned, perhaps *PGM,*SRVPGM would be practical? IDK.
The code for the RESOLVE Table Function appears below in its entirety. Simply cut/paste it into ACS and run it once. That'll create it. At that point you can start using it to resolve the library name for objects. In RPG you can use it like this:
dcl-s objlib varchar(10);
EXEC SQL SELECT RTNLIB INTO :OBJLIB
FROM Table(sqlTools.Resolve('*allusr','orders','*dtaara'));
Here's the source code for the RESOLVE Table Function. If you want to learn how to write SQL User Defined Table Functions, you can play with this code and make it a full-wrapper for OBJECT_STATISTICS and solve that issue you probably run into where you're typing the library, object, and object type parameters in the wrong order, or forgetting to press CAPS-LOCK. I find I want to throw my computer out the window much less often using our SQL Tools case-agnostic functions vs the native IBM Services, but to each their own.
CREATE or REPLACE FUNCTION Resolve(
LIBRARY_NAME varchar(128) default '*LIBL',
??????????????????????object_name? varchar(128),
??????????????????????objType????? varchar(10)? default NULL
????????????????????????????????? )
-- (c) R.Cozzi, Jr. All right reserved.
-- Written 8-Dec-2021 for IBM i v7r3/r4 at the latest TR
?????? RETURNS table (
??????????? objLib??? varchar(10),????
??????????? objName?? varchar(10),????
??????????? OBJTYPE?? varchar(10),????
??????????? OBJATTR?? varchar(10),
??????????? rtnLib??? varchar(10),????
??????????? OBJOWNER?? varchar(10),???
??????????? OBJCREATOR varchar(10),???
??????????? crtsysname?? varchar(8),??
??????????? objcrtdate?? date,????????
??????????? lastUsedDate date,????????
??????????? OBJTEXT????? varchar(50)??
?????????????????????? )
?????? LANGUAGE SQL
?????? READS SQL DATA
?????? SPECIFIC ST_RESOLVE
?????? NOT DETERMINISTIC
?????? CARDINALITY 1
??? -- Date Format ISO is required for dates prior to 1940.
??? set option datfmt = *ISO, commit=*NONE
?R: BEGIN
???? DECLARE objName?? VARCHAR(128) not null default '';
???? DECLARE objLib??? VARCHAR(128) not null default '';
???? DECLARE OBJ_TYPE? VARCHAR(10) not null default '';
???? if ( LIBRARY_NAME is NOT NULL and LIBRARY_NAME <> '') THEN
?????? set R.objLib = upper(LIBRARY_NAME);
???? else
?????? set R.objLib = '*LIBL';
???? end if;
???? if (R.OBJLIB = '*ALLUSER') THEN?? -- xlate common mispelling
?????? set R.OBJLIB = '*ALLUSR';
???? end if;
???? if (object_name is not null and object_name <> '') THEN
?????? set R.objName = upper(object_name);
???? end if;
???? if (OBJTYPE is NOT NULL and OBJTYPE <> '') THEN
?????? set R.OBJ_TYPE = upper( OBJTYPE );
???? end if;
???? if (length(trim(R.OBJ_TYPE)) = 0) THEN
????? set R.OBJ_TYPE = '*ALL';
???? end if;
???? if (SUBSTR(R.OBJ_TYPE, 1, 1) <> '*')? THEN
????? set R.OBJ_TYPE = '*' concat trim(R.OBJ_TYPE);
???? end if;
??? if R.OBJ_TYPE = '*SAVF' THEN???? -- *SAVFs are *FILES
????? set R.OBJ_TYPE = '*FILE';
??? elseif R.OBJ_TYPE = '*SCHEMA' THEN?? -- SCHEMAs are *LIBs
????? set R.OBJ_TYPE = '*LIB';
??? end if;
??? if (R.OBJ_TYPE = '*LIB') THEN??? -- libs are only in QSYS
????? set R.objLib = 'QSYS';
??? end if;
??? set? R.obj_type = trim(R.obj_type);
??? set? R.objName? = trim(R.objname);
??? set? R.objLib?? = trim(R.objLib);
??? return? SELECT
?????? R.objlib,?????? -- User input value
?????? objname,
?????? objtype,
?????? objattribute AS OBJATTR,
?????? RESOLVED.OBJLIB,??? -- Found In library name
?????? objowner,
?????? objdefiner AS objcreator,
?????? created_system AS CRTSYSNAME,
?????? CAST(objcreated AS DATE) AS CRTDATE,
?????? CAST(last_used_timestamp AS DATE) AS Last_Used_Date,
?????? objtext
??? FROM TABLE (
????? object_statistics(R.OBJLIB, R.OBJ_TYPE, R.OBJNAME) ) RESOLVED
??? LIMIT 1;
?END;
That's all there is to it.