Services on Parade: LIBRARY_LIST_INFO

Recenty, my teammate Mr. Jim Denton was sharing a common issue (or maybe it was a complaint) that some of our DB2 for i users have; namely, how to figure out which database object actually got opened when you have unqualified references to a table or view. He also came up with a clever way to determine the actual object referenced.  I've asked him to share with us here.  Thanks Jim!

__________ 

DB2 for i is clearly on a roll with each release and technology refresh containing new services that help expose and integrate traditional IBM i functionality through SQL functions, procedures, and views.  This article talks about using LIBRARY_LIST_INFO to solve a programming problem.

SQL supports two naming conventions for resolving unqualified object references.  The first is called system naming (*SYS) and means that the library list will be used to find unqualified objects like files, tables, and views.  (Please note that other database objects like procedures and functions have their own rules based on the SQL path.)  This is very familiar to IBM i users and is reflected in the high level languages.  Interestingly enough, system naming is supported for consistency with IBM i in general but is not standard SQL.  The second naming convention is called SQL naming (*SQL) is standard and means that there is a default schema which is used to resolve unqualified object references.  It defaults to a schema with the same name as the current user.  The naming option is exposed on many interfaces including System i Navigator Run SQL Scripts and the high level language precompilers.  Your choice used to be evident in the SQL statement itself.  System naming used the ‘/’ character for qualified references such as schema1/table1 while  SQL naming used the ‘.’ character such as schema1.table1.  Starting with some PTF’s a while back in IBM i 7.1 and in IBM i 7.2, you can also use the ‘.’ qualifier in system naming. 

A client that was using system naming asked me if there was SQL syntax to determine the library in which a file was found on a SELECT statement with an unqualified reference like “SELECT * FROM filename”.  Note that in traditional record level access, there is something called the Open Feedback area which provides this kind of information.  So there’s good news and bad news.  The bad news is that there isn’t SQL syntax for determining the library for a file.  The good news is that it is easy to build one using one of the services provided by DB2 for i – the LIBRARY_LIST_INFO view. 

If you just specify SELECT * FROM qsys2.library_list_info, you’ll see it presents a concise version of the library list that you could also see by doing a Display Library List (DSPLIBL) CL command.  It has columns named ORDINAL_POSITION, SCHEMA_NAME, SYSTEM_SCHEMA_NAME, “TYPE", IASP_NUMBER, and TEXT_DESCRIPTION.  For purposes of this query, we only need to use ORDINAL_POSITION and SCHEMA_NAME.  We could alternatively use SYSTEM_SCHEMA_NAME if we wanted to be sure of getting the ten character name versus the potentially 128 character SQL schema name.

Let’s say our RPG program is opening a table named XYZ under system naming and wanted to know programmatically which instance of XYZ is being opened.  To make this work, we just have to join the catalog SYSTABLES which contains all the instances of the XYZ file to the LIBRARY_LIST_INFO to see which one(s) are in our library list and which one would be found first.  The query isn’t complicated.  This would show you all the instances of XYZ in the library list based on the order they appear in the library list:

                SELECT table_name, table_schema, ordinal_position
                FROM qsys2.library_list_info INNER JOIN qsys2.systables
                                ON schema_name = table_schema
                WHERE table_name = 'XYZ'  
                ORDER BY ordinal_position  ;

We love modular programming and this seems like something we might want to write once and then use in many places.  Let’s put a function wrapper around it.  Here’s a scalar function called SchemaOfTable which will return the single value indicating the schema of the first instance of XYZ in the library list.  If it doesn’t find one, it returns the null value.

              CREATE OR REPLACE FUNCTION

                  SchemaOfTable (i_tablename VARCHAR(128))
              RETURNS VARCHAR(128)
              SPECIFIC SchemOfTbl
              LANGUAGE SQL
              RETURN
              (SELECT table_schema
                              FROM qsys2.library_list_info INNER JOIN qsys2.systables
                                              ON schema_name = table_schema
                              WHERE table_name = i_tablename  
                              ORDER BY ordinal_position FETCH FIRST 1 ROW ONLY) ;

We can invoke this function as part of another SELECT.  Here’s the most basic example to just retrieve the value.

              SELECT SchemaOfTable (‘XYZ’) FROM sysibm.sysdummy1;

There are a few things we could do to optimize the performance of this function before putting it into production but we’ll save that discussion for another time. 

You’ve probably heard this many times from the DB2 for i team – we are continuously enhancing our support and there’s a lot of function provided that could make your life simpler.  The system-supplied services provide an open-ended opportunity to expose and integrate IBM i functions using SQL techniques and best practices.   And it’s fun!