You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > RPG

Overcoming the Challenges of Embedding SQL into RPG Programs


 

Note: This is part two of a two-part series. Part one appeared in the August issue.

 

In last month's article, "Bringing the Power of SQL to Your RPG Program," we examined the basics of embedding SQL into RPG programs. Here, we look at how to overcome some challenges that can arise when embedding SQL.

 

The Challenge of New Compile Parameters

We concluded the previous article with the compile step using the Create SQL ILE RPG Object (CRTSQLRPGI) command. Some of you may have tried that command and noticed some extra parameters that you don't have when compiling "ordinary" RPGLE source members. You may also have noticed that some parameters you were expecting to see aren't present with CRTSQLRPGI, such as Default Activation Group (DFTACTGRP) and Fix Numeric.

 

Let's first examine some of the new parameters for CRTSQLxxxx. (We discussed the important Commitment Control and Compile Type parameters last month.) Of the Precompiler Options (OPTION), perhaps the most significant is the one that specifies the naming convention for your SQL statements. A value of *SYS, the default value, means that if you library qualify file names in your SQL statements, youll use the typical slash (/) separator, while *SQL, the other option, means you use a period (.). The naming convention also controls how unqualified database file objects will be located. The library list will only be searched when the *SYS option is used. This option is also important because some other parameters on the command have as their default value *NAMING, which means the behavior is determined by whether *SYS or *SQL naming is in effect.

 

Include File (INCFILE) names the source file that contains any source members youve specified using the SQL INCLUDE statement. SQL INCLUDE works much like the /COPY or /INCLUDE directives in RPG, but is required if you want to copy SQL statements into your program at compile time.

 

A few parameters are primarily targeted to help provide good performance for your SQL programs. The shipped default values for these commands are typically set for the best performance option. For example, the *OPTIMIZE value for Allow Copy Data (ALWCPYDTA) allows the SQL optimizer to decide whether to use a copy of the database data if it achieves better performance. In cases where its critical that your program see only "live" data rather than a copy, you may use *NO (never). 

 

The Close SQL Cursor (CLOSQLCSR) parameter controls when SQL cursors are implicitly closed if they aren't explicitly closed in your program. The *ENDACTGRP (when the Activation Group ends) default value typically provides better performance than the alternative *ENDMOD (when the logic exits the Module) because it often allows the cursors to stay open longer. Its best to open a cursor as seldom as possible as doing so impacts performance.

 

 

A few parameters are primarily targeted to help provide good performance for your SQL programs. The shipped default values for these commands are typically set for the best performance option.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters