13
Thu, Jun
4 New Articles

V6R1 SQL ILE RPG Precompiler Enhancements

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

Variable scoping support, the ability to take a source stream file as input, increased LOB limits, and so much more!

 

If you have been using the SQL ILE RPG precompiler for many releases, you probably know that it does not fully support all the features that the compiler offers. In V5R3, enhancements were made to the precompiler to support commonly used compiler features. These enhancements were the beginning of a precompiler revolution. The gap between the precompiler and the compiler is narrowing, embedding SQL into programs is becoming more seamless, and attitudes about the precompiler are changing.

 

The precompiler revolution continues in V6R1, with the biggest release ever for the SQL ILE RPG precompiler. Not only did the precompiler catch up on some missing function, it also added support for the new compiler enhancements. The precompiler added variable scoping support, the ability to take a source stream file as input, the ability to do a LIKE on the SQLCA variables, increased LOB limits, and several new file enhancements. Now let's take a closer look at each enhancement.

Variable Scoping

This is something that a new user of the precompiler would have assumed is supported. Veterans know that this is not the case, since you probably have gotten an error or two and spent some time trying to figure out what is wrong with the variable. I'm happy to say that variables are finally scoped to procedures and work as you would expect. Now you don't have to remember to use unique names or remember what the rules are for the precompiler to accept the duplicated name if the names are not unique. The great news about this is that you can precompile to a previous release, and it will still allow the duplicated names. The precompiler however still does write its internally used generated variables globally.

 

As you know, there were lots of changes with how the precompiler handles variables in past releases. Here is an example of code that precompiled cleanly in V5R2, failed in V5R3 because of the stricter rules for duplicate names, and now precompiles cleanly in V6R1.

 

In the following source, ResultSet is defined twice, but with a different subfield definition in each of the procedures.

 

HNoMain                                                         

DSubProc1         Pr                                           

DSubProc2         Pr                                           

PSubProc1         B                                            

D                 PI                                           

D Count           S              5I 0 Inz(1)                   

D ResultSet       ds                  occurs(1) Inz            

D  Fld1                         10A                            

c/exec SQL                                                      

c+ Set Result Sets  WITH RETURN TO CLIENT                      

c+                 Array :ResultSet for :Count rows            

c/end-exec                                                     

P                 E                                            

PSubProc2         B                                            

D                 PI                                           

D Count           S              5I 0  Inz(1)                  

D ResultSet       ds                  occurs(1)                

D  Fld2                          7A                            

c/exec SQL                                                        

c+ Set Result Sets  WITH RETURN TO CLIENT                          

c+                 Array :ResultSet for :Count rows               

c/end-exec                                                        

C                   Return                                        

P                 E                                                

 

On V5R2, the precompiler defined the variables this way:

 

Data Names                    Define    Reference                         

COUNT                            16       SMALL INTEGER PRECISION(4,0) 

FLD1                             8        CHARACTER(10)                   

FLD2                             18       CHARACTER(7) IN RESULTSET       

RESULTSET                        17       ARRAY(1) STRUCTURE               

 

For the SQL statement in SubProc1, the precompiler was using an array that had a subfield of character length 7.

 

On V5R3, the precompiler defined the variables like this:

 

Data Names                    Define    Reference                                   

COUNT                            6        SMALL INTEGER PRECISION(4,0) 

                                          9 19                                      

FLD1                             8        CHARACTER(10) IN RESULTSET                

FLD2                             18       CHARACTER(7) IN RESULTSET                 

RESULTSET                        7        ARRAY(1) STRUCTURE                        

RESULTSET                        17       ARRAY(1) STRUCTURE        

SQL0314  35      11  Position 32 Host variable RESULTSET not unique.            

SQL5011  30      11  Position 32 Host structure array RESULTSET not defined          

                     or not usable.                                             

SQL0314  35      21  Position 32 Host variable RESULTSET not unique.            

SQL5011  30      21  Position 32 Host structure array RESULTSET not defined          

                     or not usable.

 

On V6R1, the precompiler defines the variables like so:

 

Data Names                    Define    Reference                                    

COUNT                            6        SMALL INTEGER PRECISION(4,0) IN RPG             

                                          PROCEDURE SUBPROC1                              

                                          9    

COUNT                            16       SMALL INTEGER PRECISION(4,0) IN RPG

                                          PROCEDURE SUBPROC2                              

                                          19                                                                          

FLD1                             8        CHARACTER(10) IN RESULTSET IN RPG

                                          PROCEDURE SUBPROC1                

FLD2                             18       CHARACTER(7) IN RESULTSET IN RPG

                                          PROCEDURE SUBPROC2                 

RESULTSET                        7        ARRAY(1) STRUCTURE IN RPG PROCEDURE           

                                          SUBPROC1                       

RESULTSET                        17       ARRAY(1) STRUCTURE IN RPG PROCEDURE

                                          SUBPROC2        

SUBPROC1                         4        RPG PROCEDURE

SUBPROC2                         14       RPG PROCEDURE

 

The correct definition of RESULTSET is used in each procedure.

IFS

The precompiler is now able to take a source stream file as input. The source stream file (SRCSTMF) and SQL include directory (INCDIR) parameters were added to all the ILE precompilers. The SRCSTMF parameter takes the absolute or relative path name for the source. The precompiler will handle a path of up to 5000 characters. The parameter INCDIR is used only for SQL include statements. This is the IFS version of the include file (INCFILE) parameter. If you want an INCDIR parameter to be used on the compiler command, the compiler options (COMPILEOPT) parameter needs to be used. If the SQL INCLUDE statement has an absolute path, the precompiler will ignore the INCDIR value. If a relative path is specified, the precompiler searches for an include file in the directories in this order: the current directory, the path on the INCDIR parameter, and finally the directory where the input source is found. Unfortunately, the output file generated by the precompiler still needs to be a source member. If OPTION(*GEN) is specified, the precompiler will call the compiler with the SRCSTMF parameter, specifying the output file in IFS form, for example /QSYS.LIB/QTEMP.LIB/QSQLTEMP1.FILE/MYMBR.MBR.

Variables LIKE SQLCA Variables

Have you ever wanted to define a variable like one that is declared in the SQLCA data structure? It can be a hassle to find the exact definition. Now all you have to do is use the LIKE keyword for the variable. You don't need to remember how the SQLCA variable is defined, and your code will look pretty slick. Since the SQLCA is a global variable, you can do the LIKE at any scope. If a SET OPTION SQLCA = *NO statement is found, only LIKE of the variables SQLCODE and SQLSTATE is allowed, since there will not be an SQLCA structure.

 

D SaveSQLCODE     S                    Like(SQLCODE)                       

Larger LOBs

In RPG, the size of a LOB is limited to the maximum size of a string variable allowed by the RPG compiler. The compiler has increased this limit to have lengths up to the non-teraspace storage system limit of 16,773,104. So, for CLOB and BLOB, the new maximum size is 16,773,100, and for DBCLOB, it is 8,386,550. SQL database limits have not changed, so character and graphic string limits are still at 32766 and 16383, respectively. The increased LOB limit is not supported when compiling to a previous release.

LEN Keyword and VARYING Parameter

In order to support the longer string-variable limits, the compiler introduced the keyword LEN and a parameter for the VARYING keyword. The LEN keyword allows you to specify the length of a data structure or string data type. The precompiler will define a host variable if the length is within the SQL limit. The parameter for the VARYING keyword indicates the number of bytes used for the prefix that indicates the actual length. The precompiler will accept VARYING(2) as long as the length is within the SQL limit. It will not recognize a variable for use by SQL if it is defined with VARYING(4), since that does not match SQL types.

 

D VAR1            S               A   LEN(3326)                            

D VAR2            S               A   LEN(234) VARYING(2)       

D VAR3            S               G   LEN( 60243)  VARYING(2)

D VAR4            S               C   LEN(434) VARYING(4)           

D VARDS           DS                                                       

D VAR5                         10A   VARYING(4)                        

D VAR6                          5A   VARYING(2) 

 

VAR1, VAR2, and VAR6 are all acceptable host variables for the SQL. VAR3 has a length that is over the SQL limit. VAR4 and VAR5 have VARYING(4), which is not accepted in SQL. Note that VARDS contains VAR5, which SQL does not support, and the VARDS structure cannot be used in SQL.

EXTNAME Enhancement

The compiler made an enhancement to the EXTNAME keyword to allow the parameter to be a character literal. The rules for the literal are the same as the rules for the EXTFILE keyword. The nice part of this enhancement is that the file can now be library qualified.

 

D EXTSTRUCT     E DS                     EXTNAME('MYLIB/MYFILE')

D EXTSTRUCT2    E DS                     EXTNAME('"ginalib"/"myTabLe"')

 

Note that EXTSTRUCT2 is using delimited names. If the name is not delimited, as in EXTSTRUCT, the names must be capitalized.

QUALIFIED Files

The QUALIFIED keyword is now allowed on the F specification. This requires the record name to be qualified by the file name. The precompiler will take this into account when resolving the LIKEREC keyword.

 

FMYFILE    o    E             DISK      QUALIFIED                            

F                                       RENAME(TEST1FILE:X)                 

D TEST            DS                    LIKEREC(MYFILE.X)                  

LIKEFILE Keyword

The LIKEFILE keyword is used to define a file like another file. It also allows a file to be passed as a parameter to a procedure.

EXTDESC Keyword

The EXTDESC keyword allows you to specify the exact file name to be used at compile time. The parameter is a literal that is the file name or the library-qualified file name.

 

FMYFILE    o    E             DISK        EXTDESC('MYLIB/FILE2')         

EVENTF Enhancement

If you use the events file, you probably have seen errors reported by the precompiler. In WDSc, if you tried to edit the intermediate source, which is the output generated by the precompiler, the changes were not added to the original source. Now, the events file has been enhanced and the error will be inserted in the original source, thus no more editing the wrong file. The precompiler also fixed the problems when there were SQL errors in the /copy file.

A Precompiler Revolution

As you can see, there are a lot of changes for the precompiler in V6R1. I'm excited about the precompiler revolution, and I hope that you are also and that you are eager take advantage of some of these enhancements.

Gina Whitney

Gina Whitney is a staff software engineer at IBM in SQL development for DB2 for i5/OS. She is responsible for the SQL precompilers. Gina is located in Rochester, Minnesota and can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

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: