29
Fri, Nov
0 New Articles

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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';

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions - Figure 1

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;

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions - Figure 2

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;

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions - Figure 3 

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;

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions - Figure 4 

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());

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions - Figure 5 

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());

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions - Figure 6 

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.

Sarah Mackenzie is a Db2 for i Senior Software Engineer. She has worked on the IBM i development team since she joined IBM in 2012. During that time, she has focused on both database and query and has worked on the design, development, and support of new enhancements for IBM i such as Temporal Tables, Db2 Mirror, and Geospatial Analytics with Watson. Sarah is also a speaker at many industry events.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: