Wed, Jul
0 New Articles

Creating SQL Functions with RPG IV Subprocedures

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

Unless you live in a cave, I'm sure you've noticed that SQL is becoming more popular in iSeries shops. IBM provides a large number of built-in functions for use with SQL. These functions provide the ability to translate, convert, or in some way operate against the raw data from the files.

A function is a prewritten set of instructions that modify the data being processed, such as SUM(), which totals a column, and CHAR(), which converts certain values to character data. Sometimes, however, you come up with a need that IBM didn't cover. For example, perhaps you need a function that works as a counter, returning 1 for the first row, 2 for the second, and so on. Such a function would enable you to create sequence numbers within an SQL statement. IBM provides no such function to does this, but it does provide an example of how to write one. Unfortunately, the example is written in C. Not being a C programmer, I wanted to figure out how to do the same thing in RPG IV. I thought I'd share the code and what I learned along the way.

How Do I Create a Function?

SQL provides three ways to create functions--written in SQL; written in other host languages, such as RPG IV or COBOL; or simply modeled on an existing "source" function. Operations Navigator provides a wizard to assist in creating functions, but all you need is the ability to execute an SQL statement.

In this article, I'll show you how to create an SQL function that calls RPG IV subprocedures. The goal is to recreate one of IBM's example functions that is written in C (see the manual DB2 Universal Database for iSeries SQL Programming Concepts at the iSeries Information Center). The function is called "Counter," and it receives two parameters: the starting value of the counter and the amount by which to increment the counter for each row in the result table.

Create (or rather, "register") this function on your iSeries (see Figure 1).

(inout INT, inout INT)

Figure 1: Use this CREATE FUNCTION SQL statement to register Counter.

This type of function is referred to as an "external" function, because the code being run is written not in SQL, but in some other language of the host system. When creating an external function, the SQL CREATE FUNCTION statement does not create a new object on the system. It simply adds an entry to a control table (QSYS2/SYSFUNCS). In this case, the executable code is stored within an RPG IV service program. Therefore, this function cannot be used until the RPG IV program is created.

The CREATE FUNCTION statement contains a range of parameters that are not at all intuitive! These parameters can make using functions challenging until you understand what they do. Let's break the statement down and see what each one does.

  • CREATE FUNCTION mylib/COUNTER(int,int) defines the name of the new function as "Counter" and shows that it receives two integers as input parameters.
  • RETURNS INT indicates that the function returns an integer value.
  • EXTERNAL NAME 'MYLIB/COUNTER(COUNTER1)' identifies the service program that contains the executable code. The subprocedure being called as a function is named within the parentheses.
  • LANGUAGE RPGLE specifies that the program is written in RPG IV.
  • PARAMETER STYLE DB2SQL determines what set of parameters to pass to the subprocedure.
  • NO SQL simply indicates that the subprocedure does not contain additional SQL code.
  • NOT DETERMINISTIC indicates that the function can return different results each time it is run, even if the input parameters remain the same.
  • SCRATCHPAD 20 allocates 20 bytes of space for each instance of the function within an SQL statement.
  • FINAL CALL sends an additional parameter to the subprocedure, identifying the first and last calls to the subprocedure for each instance in an SQL statement.
  • DISALLOW PARALLEL requires the function calls to be performed consecutively; processing two or more simultaneously is not allowed.

Is that clear? If not, don't worry. I'll review the CREATE command again later.

What About the RPG IV Code?

The service program contains a subprocedure to perform the work of the function (see Figure 2). Additional subprocedures could be used to redefine the same functions with different parameters being passed.

P Counter         B                   EXPORT             
D                 PI                                     
D Start                         10I 0                    
D Incr                          10I 0                    
D OutCtr                        10I 0                    
D InNull1                        5I 0                    
D InNull2                        5I 0                    
D OutNull                        5I 0                    
D SqlState                       5                       
D FuncName                     139A   VARYING            
D SpecName                     128A   VARYING            
D MesgText                      70A   VARYING            
D InStruct                      20                       
D Flag                           5I 0                    
D Struct          DS            20                         
D InLen                         10I 0                      
D InCtr                         10I 0                      
C                   MOVEL     InStruct      Struct         
C                   IF        FLAG = -1                    
C                   Z-ADD     Start         InCtr          
C                   ELSE                                   
C                   IF        Incr <> 0                    
C                   ADD       Incr          InCtr          
C                   ELSE                                   
C                   ADD       1             InCtr    
C                   ENDIF                            
C                   ENDIF                            
C                   Z-ADD     InCtr         OutCtr   
C                   MOVEL     Struct        InStruct 
C                   RETURN                           
P                 E                                  

Figure 2: Code for the COUNTER subprocedure.

This function uses the parameter style DB2SQL. Because FINAL CALL is specified on the CREATE statement, the input parameter FLAG is set to –1 the first time the function is called for each instance and then set to 1 on a final call to allow "clean-up" work to be done, if necessary. If FINAL CALL is not specified when creating the function, this parameter will not be passed.

On the first call, the subprocedure initializes the data in the scratchpad (INSTRUCT), and on subsequent calls, it adds the increment amount to it. The calculation specifications are rather simple. The scratchpad data is moved into a data structure. If this is the first call, the input parameter, START, is loaded into the counter field; otherwise, the input parameter INCR is added to the previous value. The new value is loaded into the output parameter and back into the scratchpad data. To call this version of the function, simply code COUNTER(x,y) in an SQL statement.

So What Does This Thing Do?

Once the service program is compiled, the function can be tested. The SQL statement Select Counter(1,1), cname from cust might yield the results shown in Figure 3. As you can see, a unique sequential number is assigned to each row of the result table.

COUNTER (1 , 1 )    CUSTOMER NAME                              
      1    Joe's Banana Shack                
      2     Patty's Pineapple Palace          
      3    Franky's house of Fruit           
      4     The Kiwi Castle                   

Figure 3: SQL Select with 1 counter yields these results.

What other features could be added to improve this function? How about being able to assign just a starting value like COUNTER(101) and let the increment default to 1? Or simply let both parameters default to 1 with something like COUNTER()? That would cause the function to start at 1 and increment by 1 for each row in the result set. It is possible to configure a function to accept different parameter lists while still using the same function name. Each unique set of parameters requires another subprocedure to process it.

Because scratchpad is specified, this function can be used multiple times within the same statement, and each instance will have its own counter. A more complex SQL statement--Select Counter(1,1),cname,counter(10,10) from cust--yields the result shown in Figure 4.

COUNTER (1 , 1 )    CUSTOMER NAME               COUNTER ( 10 , 10 )   

       1    Joe's Banana Shack                  10    
       2    Patty's Pineapple Palace            20    
       3    Franky's house of Fruit             30    
       4     The Kiwi Castle                     40    

Figure 4: SQL Select with 2 counters yields these results.

The first three columns use the counter function, but the counter number is determined separately for each column.

And there you have it--a working example of an RPG IV service program that is being called as a function out of SQL. Pretty cool, huh?

How Can the Same Function Accept Different Parameters?

SQL identifies functions not only by their names, but by their names and parameter lists. So each time a new set of parameters is registered, a new entry is created in the system file SYSFUNCS. To register a different parameter list for the same function, simply issue another CREATE FUNCTION statement and have it point to a different subprocedure (probably within the same service program)

Let's Review the Create Statement More Closely

A few of those odd-looking parameters on the create statement are of great importance:

SCRATCHPAD is vital to allowing this function to work. In Figure 4, the function was used more than once within the same SQL statement. How does the subprocedure know which column it's working with when it gets called? Since the data being manipulated is the data from the previous row in the result table, some mechanism is needed to keep the data accurate for each column. Scratchpad provides a block of memory for each instance of a function within an SQL statement. Each time the function is called, the appropriate memory block is passed to the subprocedure. Any data that needs to be persistent between calls, such as a counter value or a running total, can be stored within the scratchpad data. If scratchpad is not used, then the InStruct parameter must be removed from the prototypes and procedure interfaces. Scratchpad can only be used with parameter style DB2SQL.

FINAL CALL clearly identifies when the initial value of the column should be set. The FLAG parameter identifies the first call for an instance when it's value is - 1. After all the processing for that column is complete, the function is called again. This time, FLAG is set to 1. This allows the function to do any necessary clean-up work. On all other calls, FLAG is set to 0. If FINAL CALL is not used, the FLAG parameter must be removed from the prototypes and interfaces. FINAL CALL can only be used with PARAMETER STYLE DB2SQL.

PARAMETER STYLE DB2SQL identifies the format for passing parameters to the procedure. The Create command supports five parameter styles, each one with its own layout of the prototype and interface (see Figure 5).

* Parameter Style = DB2SQL
P Counter         B                   EXPORT             
D                 PI                                     
D Start                         10I 0                    
D Incr                          10I 0                    
D OutCtr                        10I 0                    
D InNull1                        5I 0                    
D InNull2                        5I 0                    
D OutNull                        5I 0                    
D SqlState                       5                       
D FuncName                     139A   VARYING            
D SpecName                     128A   VARYING            
D MesgText                      70A   VARYING 
D Scratchpad                     20                       
D Flag                           5I 0  
D DBINFO                              LIKE(DBINFODS)

D DBINFODS        DS                               
D Server                       128A   VARYING      
D AUTID                        128A   VARYING      
D CCSIDDATA                           LIKE(CCSID)  
D TCScheme                     128A   Varying      
D TCTable                      128A   Varying      
D TCColumn                     128A   Varying      
D VerRel                         8A     

D CCSID           DS                      
D SBCSIDASCII                   10U 0     
D DBCSIDASCII                   10U 0     
D MXCSIDASCII                   10U 0     
D SBCSIDEBCDIC                  10U 0     
D DBCSIDEBCDIC                  10U 0     
D MXCSIDEBCDIC                  10U 0     
D SBCSIDUNICODE                 10U 0     
D DBCSIDUNICODE                 10U 0     
D MXCSIDUNICODE                 10U 0     
D SelCSID                       10U 0     
D Reserved                       8A   

Figure 5: The Create Function command supports alternative parameter styles.

DB2SQL offers the most flexibility for use with RPG IV, and it includes optional parameters. These optional parameters may or may not be passed, depending on which keywords you use when creating the function. DB2SQL is the only format that supports the SCRATCHPAD, FINAL CALL, and DBINFO options. See the manual DB2 Universal Database for iSeries SQL Reference at the iSeries Information Center for more information.

NOT DETERMINISTIC indicates that the results of the function can change even when the input parameters are the same. The function UPPER(), which translates lowercase characters to uppercase, is an example of a deterministic function. If the function gets called multiple times with the same data, identical results are returned for each call. For example, UPER('abc') always returns a value of 'ABC'. While GETONHAND(PART) might return a different on-hand quantity each time it is called. Defining the function as deterministic allows SQL to reuse results that may still be in cache from a previous call, thereby improving performance. For example, if the Sales History file has 100,000 records in it and you print the cost of each part with the statement SELECT PART, GETCOST(PART) FROM SALESHIST, the function GETCOST will be run 100,000 times if it is defined as NOT DETERMINISTIC. But, if it is defined as DETERMINISTIC, it might get called fewer times due to the fact that previous results for the same part could be found in the system cache. This option should only be used if the results are always the same.

What About the Service Program's Activation Group?

The service program should be compiled into either activation group *CALLER or a named activation group.

What Does All This Mean?

SQL is a fantastic tool for database manipulation. Its greatest weakness, in my opinion, is that it lacks the same level of granularity that can be found in high-level languages (HLLs) such as RPG IV. Being able to create your own SQL functions in RPG IV will enable you to provide your developers with powerful tools that greatly simplify their SQL code, or in some cases, add completely new functionality that may have been virtually impossible with the standard SQL instruction set. Add this to your tool belt and leverage the best of both SQL and RPG IV. You could be the proud author of your own SQL functions to perform tasks such as these:

  • Converting dates from legacy formats to date data types
  • Calculating costs
  • Calculating prices
  • Retrieving on-hand balances
  • And many more...

You may have already learned how to embed SQL with RPG. Now, you know how to embed RPG within SQL!

Kevin Forsythe has over 18 years of experience working with the iSeries platform and its predecessors. He has been a member of the DMC team for the past nine years. Kevin's primary responsibility is providing iSeries education, but he also provides customers with project management, system design, analysis, and technical construction. In addition to his technical skills (RPG IV, CL, OS/400, SQL, FTP, Query, VB, Net.Data), Kevin possesses the ability to communicate new and complex concepts to his students. He has been the primary instructor for DMC's iSeries-based AS/Credentials training courses since 1997 and has authored courses such as Advanced ILE, SQL, Embedded SQL, Operations Navigator, and Intro to WebSphere Studio. An award-winning speaker, he has spoken at every COMMON Conference since the spring of 2000.



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: