|Editors:||Ted Holt||Managing Editor:||Mari Barrett|
|Howard Arner||Technical Editor:||David Morris|
In Part I of this saga (Midrange Guru, OS/400 Edition, Volume 1 Number 16), I explained how to define the entry parameters of an RPG IV program in D specs instead of C specs. In this episode, I explain how prototyping allows you to do some things you can't do when you define parameters in C specs.
Here's the example I used in Part I. The program is PARMX2, and it has two parameters.
C *ENTRY PLIST C PARM CUST 70 C PARM ACTION 5
Both CUST and ACTION are modifiable. That is, PARMX2 can change their values, and the calling program will receive the changed values.
But sometimes it is desirable that a parameter's value not be changed. If you define the parameters with C specs, there's no stopping someone from accidentally or ignorantly changing a parameter's value.
However, you can prevent this from happening by using the CONST keyword in the D specs. Here's the prototype, which should be placed in its own source member.
D ParmX2 pr extpgm('PARMX2') D customer 7p 0 const D action 5
Program PARMX2 includes this definition.
* *ENTRY parameter list /copy qprotosrc,parmx2 D ParmX2 pi D customer 7p 0 const D action 5
Now, if Junior J. Programmer tries to change the value of customer, perhaps through an EVAL operation, he won't be able to compile the program. He'll get an error such as RNF5346 (Result of EVAL operation must not be a field that cannot be modified.) But Junior J. can change the value of ACTION, if he wants to.
Using the CONST keyword also allows calling programs to pass literals, expressions, and variables of other sizes as customer numbers to PARMX2.
Here's a call using a literal. In this example, the literal 25 is passed to PARMX2 as customer number.
/copy qprotosrc,parmx2 D yAct s 5 C eval yAct = 'GGGGG' C callp parmx2 (25 : yAct)
Here's a call using a variable of a different size and format. The compiler generates the appropriate instructions to convert a five-digit zoned-decimal value to a seven-digit packed-decimal value.
/copy qprotosrc,parmx2 D yWork s 5s 0 inz(300) D yAct s 5 C eval yAct = 'GGGGG' C callp parmx2 (yWork : yAct)
If yWork were defined with more than seven decimal positions, and the value in yWork were greater than 9,999,999, there would be a runtime error, RNQ0103 (The target for a numeric operation is too small to hold the result).
Here's a call using an expression. RPG multiplies the value in yWork by one fourth. PARMX2 sees a customer number of 75.
/copy qprotosrc,parmx2 D yWork s 5s 0 inz(300) D yAct s 5 C eval yAct = 'GGGGG' C callp parmx2 (yWork * 0.25 : yAct)
In all three cases, using a CALL with PARM operations would require you to create an appropriately defined variable for the customer parameter.
Just a couple more points. First, even though the VALUE keyword is similar to CONST, you can't use VALUE for a program entry point parameter. Second, a prototyped call to a program cannot return a value.
I hope these two brief presentations have been helpful for those of you who are trying to master new features of RPG.
To unsubscribe, change your email address, or sign up for any of Guild Companies, Inc's free email newsletters, visit http://www.itjungle.com. Hit the SUBSCRIBE button on the homepage and it will lead you to our online subscription system.
When you sign up for one of our e-newsletters, you can be assured that your e-mail address will NEVER be sold to an outside company.
Please see our advertising opportunities and pricing at
Or contact Timothy Prickett Morgan at
Phone: 212 942 5818
I've written an RPGLE program using SQL for I/O. That is, the member type is SQLRPGLE. One cursor in this program combines our Order Header file with the Order Detail file, selecting a header schedule#, then grouping by the detail schedule#, style, piece-totaling detail order qty.
When I run this SQL statement in interactive SQL, it not only groups by the detail schedule, but also orders by detail schedule, which is what I want the embedded SQL to do. The embedded SQL isn't ordered by style/piece as the interactive one is.
The problem is that you've specified the GROUP BY clause but not the ORDER BY clause in your SQL query. If you do not specify ORDER BY, you give the database manager permission to return the data in any sequence it likes. That may or may not be in the sequence of the grouping fields.
To make sure that I was correct, I emailed Joe Celko, a true SQL guru whose column in Intelligent Enterprise magazine I always read. Joe shed more light on this subject, so I'm passing his remarks along to you.
"Single-processor products tend to use a sort to form their groups, so the results tend to come back sorted. It is not required, nor is it true in many of the newer versions. Sorting algorithms have been replaced by grouping algorithms that run faster. If you want to see that in action, the best product is NCR's Teradata because it uses hashing to get the groups and works on parallel processors. The first group completed is the first group returned, so group size and how many groups fell into one bucket determines the results. Most optimizers are smart enough to ignore the ORDER BY if it is redundant."
As your example demonstrates, Teradata is not the only product that optimizes SQL queries. There is an easy way to find out more about the optimization process applied to your query; first try running the query in debug (STRDBG). You don't need to specify a program name. Next, look at the low-level messages in the job log returned by the query optimizer. The information in the job log should help you understand the choices made by the optimizer.
For and even more information you might try running the Print SQL Information (PRTSQLINF) command specifying your SQL program's name. This command prints a summary of the SQL statements used in a program. The summary includes information about the access plan and optimization choices.
Finally, if you are trying to solve a performance problem or understand in detail what the SQL query optimizer is doing, and are on OS/400 V4R5 or V5R1, you should investigate the Visual Explain product that is part of Operations Navigator. To use Visual Explain, start Operations Navigator, expand your system, and then right-click on the database tab. Select Run SQL Scripts. If this is the first time you have used Run SQL Scripts, select JDBC setup from the connection drop down and change the naming convention to *SYS on the Format tab. Now run Visual Explain by selecting Explain, or Run and Explain, from the Visual Explain drop down. That should open a window that graphically depicts the SQL query optimizer's choices.
In the October 5 issue of Midrange Guru, you showed how to keep the output of RUNSQLSTM from printing by redirecting it to a garbage output queue. I know another method, which I would like to share with your readers.
With an undescribed printer file, it's quite easy to redirect the spooled output to a database file.
CRTPF FILE(QTEMP/SINK) RCDLEN(1) OVRDBF FILE(QSYSPRT) TOFILE(QTEMP/SINK) RUNSQLSTM ...This method works with OS/400 V5R1. I can't vouch for other releases.
Keep up the good work. Love those tips; they keep the little grey cells stimulated.
-- Brian Johnson, Help/Systems
Thanks for the tip, Brian. It works on OS/400 V4R5, too. Thanks also for the encouragement.
If you have a tough problem, our gurus can probably help. Their mailboxes are always open.
* Email Ted Holt at email@example.com
* Email Howard Arner at firstname.lastname@example.org
This document may be redistributed freely and enthusiastically by email, but only in its unedited form. Thanks for your cooperation.
Midrange Guru is a registered trademark of Guild Companies, Inc. IBM, AS/400, iSeries, OS/400, and eServer registered trademarks of International Business Machines Corp. All other product names are trademarked or copyrighted by their respective holders.