Month: March 2023

If you’re on the very latest TR of #IBMi you can now use the IBM-supplied BINDING_DIRECTORY_INFO Table Function to get the same information as you can from the DSPBNDDIR CL command.

In my #SQLTools, I’ve had the BNDDIR_ENTRIES Table function that produces the identical information for several years. There is no API to access Binding Directory objects so my version uses the DSPBNDDIR OUTFILE support to accomplish this task.

As mentioned, the IBM-supplied BINDING_DIRECTORY_INFO is available on the latest TR of the operating system. If you are on V7R3 or later, see if you have it. If so, you don’t really need to continue reading this article, but feel free to continue.

BNDDIR_ENTRIES for #IBMi V7R2 and Later

In SQL Tools we ship the SQL User-Defined Table Function (UDTF) named BNDDIR_ENTRIES which was written to be compatible with IBMi V7R2 and later, with the exception of one line of code, which I’ll cover later in this article.

The syntax for this function follows:

SELECT * FROM TABLE( BNDDIR_ENTRIES( ‘QSYS2’, ‘QUSAPIBD’));

If you run this statement using IBM ACS or our SQL iQuery product, you will see the results for the QUSAPIBD. This binding directory is automatically included by RPG IV and C compilers. Years ago, working with IBM Rochester, we got all the entries from QC2LE included in QUSAPIBD which is why BNDDIR(‘QC2LE’) became unnecessary in RPG IV starting with IBM i V6R1.

The output from the above statement would look similar to the following.

If you’re an IBM i shop that takes advantage of Binding Directories, you probably need to know which binding directories contain certain Service Programs or Modules. the BNDDIR_ENTRIES Table Function will help with that.

Suppose you wanted to know which *SRVPGM and *MODULE objects are in the QC2LE as well as QUSAPIBD binding directores? A simple JOIN statement can be used to create this dataset:

SELECT c2.
  FROM TABLE (
         sqltools.BNDDIR_ENTRIES(‘QSYS’, ‘QC2LE’)
       ) C2
       INNER JOIN (
           SELECT *
             FROM TABLE (
                 sqltools.BNDDIR_ENTRIES(‘QSYS’, ‘QUSAPIBD’)
               ) API
         ) API
         ON (C2.OBJNAME, C2.OBJLIB) = (API.OBJNAME, API.OBJLIB)

The results from this SQL statement look like this:

Now you know which entries appear in both Binding Directories.

Binding Directories do not have an API themselves. So the only exposed way to get to those entries is via the DSPBNDDIR CL command. The BNDDIR object is a SPACE object, similar to a User Space object. Space objects are used for a variety of IBM i objects, such as *CMD, *USRSPC, and several others. For *BNDDIR objects the data is stored as fixed length segments within the space and repeats for each entry. In the old days, I would’ve written an MI program, but today I would choose the C language to get to the *BNDDIR Entries. However, today objects are locked down with System Domain and System State flags, so user code, even written in C, cannot resolve a pointer to those objects associated space. You would actually have to do a DMPSYSOBJ to a SPOOLED file, read that SPOOLED file data and extract the Entries.

The source code for the BNDDIR_ENTRIES Table function is shipped with our SQL Tools product. We include a source file named QUSRSRC (User Source) that contains functions that we deem less compelling and thus ship the source code for them.

Optional Statement

In order to perform the JOIN operation I illustrated, the BNDDIR_ENTRIES table function needs to connect one set of data with another. However, the DSPBNDDIR CL command output to a named output file. That OUTFILE can be a different name every time the command is run. Coming up with unique names can be challenging. To solve that problem SQL Tools ships with the TEMPNAME Function. This function returns a system-wide unique name. That name can be the name of a file in QTEMP or an IFS file name. To have it generate a name in QTEMP, pass in the name QTEMP as the first parameter, as follows:

DECLARE OUTFILE_NAME VARCHAR(21) NOT NULL DEFAULT ‘QTEMP/Z_BNDDIRE’;
set OUTFILE_NAME = sqlTools.tempname(‘QTEMP’);

Calling TEMPNAME() generates a name in QTEMP that is unique. This allows you to use this function in a JOIN operation as illustrate above. If you do NOT have SQL Tools, then this function does not exist on your system. If you want to use BNDDIR_ENTRIES anyway, simply comment out that one line of code that sets the OUTFILE_NAME variable. Also avoid using it in a JOIN operation with another BNDDIR_ENTRIES table function.

To learn more about any of our SQL Tools functions or obtain the source code for BNDDIR_ENTIES visit our website, download the latest SQL Tools V2R3 (announced 3 March 2023), and install them onto your IBM i system. You’ll receive a 2-month no-charge trial license automatically upon installation.

Visit: http://www.SQLiQuery.com/SQLTools for more details.

That’s all there is to it.