24
Sun, Nov
1 New Articles

Reuse Your RPG Code with SQL User-Defined Functions

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

What? You haven't used a UDF before? No worries. There's nothing to it.

 

Recently, one of our programmers came to me with a question about retrieving some information from our iSeries. He was using a business intelligence (BI) tool to analyze picking errors across several warehouses, and he needed a way of retrieving the warehouse area for a specific warehouse and item location. Unfortunately, that information was not in a table in our system, but rather was calculated by an RPGLE program and returned as a parameter when the program was called with the warehouse ID and the item location. Because the BI tool used SQL to retrieve information from our iSeries, I suggested that he consider creating an SQL User-Defined Function (UDF) to return the warehouse area. With this method, the warehouse area can be returned as a value by any high-level language, such as PHP or Java, or any tool that uses SQL to access data on the system.

 

If you're primarily an RPG programmer and haven't created or used an SQL UDF before, it might be a little intimidating the first time you look at the SQL CREATE FUNCTION command. That's because SQL is a standard database language that can be used with many different operating systems, high-level languages, and database implementations, so the CREATE FUNCTION command has a number of parameters to accommodate them. For the purposes of this article, however, we're going to assume that we have an RPG or RPGLE program that's called to perform a specific task and that it returns a single value. In SQL UDF terminology, we are creating an external UDF that returns a scalar value.

 

Once you have identified an RPG program that returns a single value, what steps do you need to take to create an SQL UDF? That depends on whether you're starting with a program or service program and the type of interface you want to create. If you're starting with a service program, then you have a choice of the type of interface you want to use when the program is called, or in SQL terminology, the PARAMETER STYLE. The two basic types I'll discuss here for RPG programs are GENERAL and SQL. Ironically, GENERAL refers to a very specific program implementation, which is an RPGLE Service Program containing a RETURN (result) statement, while SQL refers to a more general interface that can be used with both programs and service programs. Let's look at the GENERAL style first, since it's the easiest to implement.

 

In the example above, where we're trying to find the warehouse area based on a warehouse number and bin location, the service program would look like this:

 

     H NOMAIN

     ****************************************************************

     * Program.....: AREACHEK                                       *

     * Purpose.....: Determine warehouse area                       *

     ****************************************************************    

        

     D getarea       pr            3

     D whse_                      2   const

     D binloc_                      5   const

    

     P getarea        B                   Export

 

     D getarea       pi            3

     D whse                      2   const

     D binloc                      5   const

    

     D whsearea     s             3  

 

     /free

 

     // Do some work here to take the warehouse number and bin

     // location and determine the warehouse area

 

      return (whsearea);

 

     /end-free

     P                 E

 

The good thing about starting with the preceding service program that returns a value is that no programming changes are required to define an interface for SQL to call the UDF, and the CREATE FUNCTION statement can refer directly to the service program. But before we move on to the CREATE FUNCTION statement, let's look at what needs to be done in the case of an RPGLE program.

 

Instead of a service program, let's assume you have an RPGLE program similar to the one shown below that returns the warehouse area as a parameter:

 

     ****************************************************************

   * Program.....: AREACHK                                       *

     * Purpose.....: Determine warehouse area                       *

     ****************************************************************

 

     D areacheck     pr                 EXTPGM('AREACHEK')

     D whse_                      2

     D binloc_                      5

     D whsearea_                  3

 

     D areacheck     pi

     D whse                       2

     D binloc                    5

     D whsearea                  3

 

     /free

 

     // Do some work here to take the warehouse number and bin

     // location and determine the warehouse area

     // Set whsearea = warehouse area

 

      *inlr = *on;

 

     /end-free

 

Again you have a choice. Either the program can be converted to a service program as in the first example with a RETURN (value), or the SQL parameter style interface can be used. Since we've discussed the service program interface, let's look at what needs to be done to this program to be called using the SQL interface. The SQL interface consists of the following required parameters in this order:

 

  1. Input parameters: One or more to be specified for the function
  2. A result parameter
  3. One or more indicator variables for the input parameters (one for each input parameter). These are set by DB2 and are used to indicate null (-1) or not null (0).
  4. An indicator variable for the result parameter. A negative value indicates a null result.
  5. A CHAR(5) output parameter for SQLSTATE
  6. A VARCHAR(517) input parameter for the fully qualified function name
  7. A VARCHAR(128) input parameter for the specific name of the function
  8. A VARCHAR(1000) output parameter for the message text

 

Several other optional parameters can be used but will not be discussed here. Now let's look at the program after the interface modifications have been made.

 

     ****************************************************************

     * Program.....: AREACHK                                       *

     * Purpose.....: Determine warehouse area                       *

     ****************************************************************

 

     D areacheck     pr                 EXTPGM('AREACHEK')

     D whse_                      2   const

     D binloc_                    5   const

     D whsearea_                  3

     D in_whse_                  5i 0 const

     D in_binloc_                5i 0 const

     D in_whsearea_              5i 0

     D sqlsterr_                  5

     D fctname_                517   varying

     D spcname_                128   varying

     D msgtext_                1000   varying

 

     D areacheck     pi

     D inwhse                    2   const

     D inbinloc                  5   const

     D whsearea                  3

     D in_whse                    5i 0 const

     D in_binloc                  5i 0 const

     D in_whsearea                5i 0

     D sqlsterr                  5

     D fctname                  517   varying

     D spcname                  128   varying

     D msgtext                  1000   varying

    

     /free

 

     // Do some work here to take the warehouse number and bin

     // location and determine the warehouse area

     // Set whsearea = warehouse area

 

      if notfound;

         sqlsterr = '59999';

         msgtest = 'Warehouse area not found';

      ENDIF;

 

      *inlr = *on;

 

     /end-free

 

When faced with making changes to the program interface to support the SQL parameter style, you might say it's just as easy to create a service program instead, and most of the time I would agree with that view. However, there are a couple of benefits when using the SQL parameter interface, and one is that a user-defined error code can be returned in the sqlsterr parameter, as well as a user-defined message in the msgtext. I've included an example here where the warehouse area could not be determined. This is one reason that you might consider using the SQL parameter style instead of the GENERAL one. Another reason is that your database supports null values, and you might pass or return a null value as part of the function arguments and want to be able to identify them in your program. The SQL interface provides indicator variables to identify null values.

 

Once we've determined the parameter style, it's time to create the SQL function. This can be done by using the CREATE FUNCTION SQL statement in STRSQL, creating a source module with the CREATE FUNCTION statement in it and using the RUNSQLSTMT command, or using the wizard in the System i Navigator. For our examples above, let's compare a simple create function statement for the service program using the GENERAL parameter style to the create function statement for the RPGLE program using the SQL parameter style. For the service program, our create statement is:

 

 

Create function mylib/getarea ( whse char(2), binloc char(5) )

       returns char(3)

       language rpgle

       external name 'MYLIB/AREACHECK(getarea)'

       parameter style general

       program type sub

        

 

Reading the above statement is straightforward. We're creating the SQL function getarea in the library MYLIB and passing two parameters, both consisting of character data. The function returns a three-character result. The language that the service program is written in is RPGLE, the program is located in MYLIB/AREACHECK with procedure getarea, and the program type is a procedure in a service program. Note that the external name parameter specifying the library name and program name (MYLIB/AREACHECK) must be in uppercase.

 

For the RPGLE program with the SQL parameter style, we have the following:

 

Create function mylib/getarea (whse char(2), binloc char(5) )

       returns char(3)

       language rpgle

       external name 'MYLIB/AREACHECK'

       parameter style sql

       program type main  

 

 

The only differences are referencing the program name on the external name statement instead of the procedure name for the service program, the difference in the parameter style, and the program type MAIN instead of SUB. It's important to remember that the create function statement is not creating a program object in your library, but is instead registering information about the interface of the program object in the database for SQL to use to call it.

Other Keywords on the Create Function

There are many other keywords on the Create Function command, and without discussing all of them, I will mention here a couple that could apply to an RPG or RPGLE program. The first is the DETERMINISTIC or NOT DETERMINISTIC keyword. Using the DETERMINISTIC keyword tells SQL that the same input parameters will always return the same result. This allows an SQL function to cache the result on a call and, if called again with the same parameters, use the result without executing the program. An example is a function that returns the hire date based on passing the function an employee number. Similarly, a NOT DETERMINISTIC (the default) function would be one that returns the vacation days remaining for an employee when passed the employee number. Without this keyword specified, SQL will assume the function is NOT DETERMINISTIC, so specifying DETERMINISTIC if it applies could improve performance.

 

Another keyword indicates whether SQL is used in the RPG or RPGLE program that makes up the function and how it is being used. In this case the options are these:

 

  • No SQLThe program does not use SQL.
  • Contains SQLThe program contains a limited number of SQL statements, none of which read or update data files.
  • Reads SQLThe program reads data using, for example, an SQL Select statement (Default).
  • Modifies SQL DataThe program can read and modify data using SQL.

 

There are a number of other keywords available, so this is just a start.

 

After we've executed the CREATE FUNCTION statement above, let's put our new function to use. In our system, the table INVENT contains our locations and inventory for each item in each of our warehouses, so the SQL statement

 

SELECT prodno, size, casloc, getarea ( char('01'), casloc )

FROM invent WHERE casloc <> ' '  

 

returns the warehouse area for warehouse 1 for each of the items with a case location that is not blank, as shown below.

 

112112STATONFIG1                  

Figure 1: The display shows the warehouse area and case location for each item.

 

While I've used the STRSQL command line interface and the green-screen to show the getarea function, it's important to point out that the System i Navigator has a nice interface to create, delete, and view SQL UDFs. For example, if we take a look at the getarea function that we created in my test library JBSTEST, it looks like this:

 

112112STATONFIG2

Figure 2: This is the getarea function created in test library JBSTEST.

 

By selecting the function and right-clicking your mouse, you get a dialog box with one of the choices being "Explain SQL". Choosing this option displays information about the function, including the CREATE statement.

 

112112STATONFIG3
Figure 3: Get information about the chosen function.

 

Summary

While I haven't covered every aspect of creating an SQL UDF scalar function in this brief article, I hope I've given you a good starting point for creating your own functions. So the next time one someone asks for information that is contained in the business logic of an RPG or RPGLE program, at least consider whether it makes sense to provide it through an SQL UDF. It's a great way to share your RPG business logic with programmers writing in Java, PHP, or any other language that uses SQL.

 

On a final note, I'd like to leave you with two other SQL statements that will be useful to you as you begin experimenting with the Create Function statement. The first is the Drop Function statement, which allows you to remove an SQL function you created from the data base registry, and, as of V6R1, a new Alter Function statement that allows you to modify an existing function.

 

For more on SQL User-Defined Functions, consider the IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries or the DB2 for i SQL Reference at the IBM Infocenter website.

 

 

 

Jim Staton

Jim Staton is Vice President for Information Technology at Mutual Distributing Company, the largest alcohol beverage distributing company in North Carolina.

 

While Jim never worked with IBM midrange systems at IBM, one of his first programming jobs was developing software for an IBM System/3 Model 6 for his family's business. He continued to develop RPG applications for the business in his spare time on a System/32, System/34, System/36, and eventually the AS/400.

 

In 1978, Jim joined IBM at the Research Triangle Park in North Carolina, where he worked with computer communications and protocol standardization. As a manager at IBM, he was responsible for IBM's work with the IEEE 802.x committees to complete the standards for the 802.2-5 protocols for local area networks. In 1985, Jim joined the IBM European Networking Center in Heidelberg, Germany, where he managed the development of IBM's first prototype implementing the CCITT X400 Standard, which were the rules governing the first universal email system. While at IBM, Jim was awarded two patents in communication protocols as well as an Outstanding Technical Achievement Award for his work with X.400 Message Handling System. He also coauthored several articles on computer communications for the IBM Systems Journal.

 

Jim has been a speaker at a number of conferences on a variety of topics, including communications protocols, supply chain management, mobile applications, and business intelligence.

 

Jim graduated from Ohio State University in 1978 with an MS degree in Computer Science.

 

 

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: