Can’t find the right IBM i service for your needs? Learn how to create your own services to automate and simplify tasks using SQL and user-defined table functions.

By Sarah Mackenzie

IBM i services allow users to automate complex tasks using SQL. The number and scope of services available grows every year. One of the most common questions that I am asked is “Is there an IBM i service that does [some specific task]?” Many times, the answer is yes! But sometimes there isn’t a service that does exactly what a user is looking for. When you find that there isn’t a service that fits your needs, you may be able to write your own.

For example, I was recently asked if there was an IBM i service the user could run that would return the same information as the Display Problems (DSPPRB) command. The DSPPRB command allows you to print service information in order to perform hardware or software maintenance. In this particular instance, the user wanted to retrieve the problem information from a remote system and run queries against the information retrieved. There is no IBM i service today that will provide this information; however, by using different SQL features, we can create our own.

For this example, we will be creating our own service as a user-defined table function (UDTF). A UDTF is a function that you define that takes parameters as input, executes a series of SQL statements in a routine body, and then returns a results table that can be referenced in the FROM clause of a selection statement. Many IBM i services are UDTFs. UDTFs allow you to take complex multi-step processes and simplify them down into a single SQL query. For this example, we will create a UDTF that:

• runs a command (DSPPRB),
• creates an outfile populated by the output from the command,
• parses and interprets the information in the outfile,
• and returns the results as a table that can be easily queried.

Before we build the UDTF, let’s first look at the DSPPRB command. The command has many parameters. We will use the default value for most of the parameters. This will allow us to capture information about all problems, all statuses, and all severity. The parameters that we will specify are the OUTPUT and OUTFILE parameters. Instead of just displaying the problem information, we will use the OUTFILE parm to direct the output of the command into a database table. The DSPPRB command that we will run in the UDTF is:

DSPPRB OUTPUT(*OUTFILE) OUTFILE(QTEMP/MY_DSPPRB) TYPE(*BASIC)

This command will create a database table in the QTEMP library called MY_DSPPRB that contains the information returned from the command. This table has 95 columns that provide a vast amount of information. We will focus on only a subset of these columns. To get the column names and column descriptions for the outfile, either call the Display File Field Description (DSPFFD) command or query QSYS2.SYSCOLUMNS. Since SYSCOLUMNS does not return information about objects in QTEMP, you will need to re-run the command specifying a different outfile in a schema. For example, re-run the command, specifying MY_LIB/MY_DSPPRB as the output file. The following SYSCOLUMSNS query returns information about the columns in the output table:

SELECT COLUMN_NAME, COLUMN_HEADING, COLUMN_TEXT, DATA_TYPE, LENGTH

FROM QSYS2.SYSCOLUMNS

WHERE SYSTEM_TABLE_NAME = 'MY_DSPPRB'

   AND SYSTEM_TABLE_SCHEMA = 'MY_LIB';

Figure 1: Results of SYSCOLUMNS query

Once we understand the format of the outfile, we can build a query. The following SELECT statement returns a subset of the data from the table created when we ran the DSPPRB command.

SELECT PBSYSN, PBID, PBDESC, PBISOS, PBTYPE, PBDATO, PBTIMO

FROM QTEMP.MY_DSPPRB;

Figure 2: Results of outfile query

The values returned from this query are not formatted into an easily readable format. However, we can rewrite our query to return the data in a format that is easier to read and understand. In order to do that, let’s use several built-in functions (BIFs).

The first BIF we will use is the INTERPRET function. This function interprets the operand as the data type that you specify. In the SYSCOLUMNS query, we can see the data type and length of each column. We can use this to interpret each value into the correct data type and length. This allows me to, for example, interpret the problem description as a CHAR(7).

SELECT INTERPRET(PBDESC AS CHAR(7)) AS PROBLEM_DESCRIPTION

FROM QTEMP.MY_DSPPRB;

Figure 3: Results of using the INTERPRET function

For each of the columns that we want to return, we can use INTERPRET to correctly interpret the returned data type and length. For some columns, we can do even better by using a CASE statement to turn a number into meaningful text. We can get even more advanced and use the BIF TIMESTAMP_FORMAT to combine into a single timestamp column the two columns indicating the date and the time the problem occurred. Here is the query that will return the data formatted:

SELECT INTERPRET(PBSYSN   AS CHAR(8)) AS SYSTEM_NAME,

       INTERPRET(PBID     AS CHAR(10)) AS PROBLEM_ID,

      CASE INTERPRET(PBTYPE AS CHAR(1))

         WHEN('1') THEN 'Machine-detected'

         WHEN('2') THEN 'User-perceived'

         WHEN('3') THEN 'PTF order'

         ELSE NULL

       END AS PROBLEM_TYPE,

       INTERPRET(PBDESC AS CHAR(7)) AS PROBLEM_DESCRIPTION,

       CASE INTERPRET(PBISOS AS CHAR(1))

         WHEN('1') THEN

           'Problem analysis was successful, the problem still exists'

         WHEN('2') THEN

           'Problem analysis was successful, the problem has been resolved'

         WHEN('3') THEN

           'Problem analysis was not successful'

         WHEN('4') THEN

           'Problem analysis was not completed'

         WHEN('5') THEN

           'Problem analysis was partially completed'

         ELSE NULL

       END AS ANALYSIS_RESULT,

       CASE

         WHEN PBDATO = X'404040404040' THEN NULL

         ELSE TIMESTAMP_FORMAT(INTERPRET(PBDATO AS CHAR(6)) CONCAT

                               INTERPRET(PBTIMO AS CHAR(6)),

                               'YYMMDDHH24MISS')

       END AS DATE_OPENED

FROM QTEMP.MY_DSPPRB;

Figure 4: Results of formatted query

Now that we understand how to use the DSPPRB command and how to query the outfile, we can build the UDTF. We will name our table function DISPLAY_PROBLEM and create it in MY_LIB. Since this is a UDTF, we are returning a table and we must define the columns we are going to return. These columns should match the columns returned from the query we built earlier. Therefore, our UDTF is declared like so:

CREATE OR REPLACE FUNCTION MY_LIB.DISPLAY_PROBLEM()

RETURNS TABLE (

   System_Name       CHAR(8),

   Problem_ID         CHAR(10),

   Problem_Type       VARCHAR(16),

   Problem_Msg       CHAR(7),

   Analysis_Result   VARCHAR(63),

   Timestamp_Opened   TIMESTAMP)

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

-- UDTF logic written in SQL PL

END;

The language used to write the routine body for SQL table functions is the SQL Procedural Language (SQL PL). We will use SQL PL to define the logic that will run the DSPPRB command and then query and format the results.

We cannot directly run a command in our table function. Instead, we will run our command using an SQL statement. The SQL procedure QCMDEXC can be used to run a command in the UDTF routine body. The QCMDEXC procedure takes a CL command string as input and runs the command. In order to run the DSPPRB command, we will run the following SQL statement:

CALL qsys2.qcmdexc(''DSPPRB OUTPUT(*OUTFILE) OUTFILE(QTEMP/QDSPPRB) TYPE(*BASIC)'')

Because this is a non-SELECT statement, we can use SQL PL’s EXECUTE IMMEDIATE to both prepare and execute the SQL QCMDEXC statement. First, we declare a variable to hold our SQL statement string. Next, we set the SQL statement string to the CALL QCMDEXC SQL. Finally, we call EXECUTE IMMEDIATE, passing the SQL statement string we just set. Therefore, our table function’s routing body will look like this:

DECLARE STMT VARCHAR(1000);

SET STMT = 'CALL qsys2.qcmdexc(''DSPPRB OUTPUT(*OUTFILE) OUTFILE(QTEMP/QDSPPRB) TYPE(*BASIC)'')';

EXECUTE IMMEDIATE STMT;

The last step is to return the table with the data from our outfile. In order to return data, use the SQL PL operation RETURN using the query we built earlier.

RETURN (the SQL query);

Put together, the UDTF as a whole is defined like so:

CREATE OR REPLACE FUNCTION MY_LIB.DISPLAY_PROBLEM()

RETURNS TABLE (

   System_Name     CHAR(8),

   Problem_ID       CHAR(10),

   Problem_Type     VARCHAR(16),

   Problem_Msg     CHAR(7),

   Analysis_Result VARCHAR(63),

   Timestamp_Opened TIMESTAMP)

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

DECLARE STMT VARCHAR(1000);

SET STMT = 'CALL qsys2.qcmdexc(''DSPPRB OUTPUT(*OUTFILE) OUTFILE(QTEMP/MY_DSPPRB) TYPE(*BASIC)'')';

EXECUTE IMMEDIATE STMT;

RETURN (SELECT

         INTERPRET(PBSYSN   AS CHAR(8)),

         INTERPRET(PBID     AS CHAR(10)),

             CASE INTERPRET(PBTYPE AS CHAR(1))

             WHEN('1') THEN 'Machine-detected'

             WHEN('2') THEN 'User-perceived'

             WHEN('3') THEN 'PTF order'

             ELSE NULL

           END,

           INTERPRET(PBDESC AS CHAR(7)),

           CASE INTERPRET(PBISOS AS CHAR(1))

             WHEN('1') THEN

               'Problem analysis was successful, the problem still exists'

             WHEN('2') THEN

               'Problem analysis was successful, the problem has been resolved'

             WHEN('3') THEN

               'Problem analysis was not successful'

             WHEN('4') THEN

               'Problem analysis was not completed'

             WHEN('5') THEN

               'Problem analysis was partially completed'

             ELSE NULL

           END,

           CASE

             WHEN PBDATO = X'404040404040' THEN NULL

             ELSE TIMESTAMP_FORMAT(INTERPRET(PBDATO AS CHAR(6)) CONCAT

                                   INTERPRET(PBTIMO AS CHAR(6)),

                                   'YYMMDDHH24MISS')

           END

         FROM QTEMP.MY_DSPPRB);

END;

The UDTF can now be called with a simple SELECT statement. The UDTF is referenced in the FROM TABLE clause.

SELECT * FROM TABLE(MY_LIB.DISPLAY_PROBLEM());

Figure 5: Results from running DISPLAY_PROBLEM table function

This DISPLAY_PROBLEM UDTF has hidden the complexity of the command, the outfile, and the formatting of the returned data. The power of SQL can now be used to query this data to answer your questions and take action against problems that may need attention.

Bonus

This topic originally started with the question “How can I get the problem information from a different Db2 for i database?” Up to this point, we’ve only been gathering data from the current node. To access data from a remote node, we can use the FROM REMOTE TABLE syntax on the SELECT statement. The REMOTE TABLE support allows a user to query a UDTF that exists on a different Db2 for i database.

First, we must create the UDTF on the remote node that we wish to retrieve problem information from. In the example below, the UDTF must first be created on the system named DB740.

Next, we must make sure that we have a relational database directory entry for the node we are trying to connect to. Use the Work with Relational Database Directory Entries (WRKRDBDIRE) CL command to add an RDB entry for the remote location used in the query.

Once these two step are completed, we can run the REMOTE query. From the source node, we use the REMOTE syntax and three-part naming to specify the remote Db2 for i database we wish to get the problem information from. In this example, we are querying the DB740 system from the DB750 system.

-- Run from DB750 retrieving data from DB740

SELECT * FROM REMOTE TABLE(DB740.MY_LIB.DISPLAY_PROBLEM());

Figure 6: Retrieving data from a remote system

This will run the UDTF on the remote node, causing the command to be called and the data interpreted on the remote node. The results will be sent back to the source node.