Hey, Ted:

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.

-- Ted

SPONSORED BY HELP/SYSTEMS NOW MANAGE LOTUS DOMINO FROM YOUR ISERIES! New Robot/DOMINO streamlines tasks such as executing agents; archiving, deleting, and compacting databases; & deleting attachments from database docs. Pair with Robot/SCHEDULE, the job scheduling package, to automate Domino management! For FREE info visit: http://www.helpsystems.com/ad_in.cgi?ad_id=50

Hey, Ted:

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.

-- Teresa

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.

-- Ted

SPONSORED BY PROFOUND LOGIC Why do thousands of RPG Programmers choose RPG-ALIVE for their Development Environment? Want to know? Click http://www.RPGAlive.com/now TRY IT FREE FOR 30 DAYS and you too will be convinced! RPG-Alive - It's Your RPG Editing and Analysis Tool! Implementation Time: 1 Minute Savings: Tens of thousands of dollars in gained productivity See our Return on Investment Chart at http://www.rpgalive.com/roi.html RPG-Alive Clients include: Computer Associates, GE Aircraft Engines, Help/Systems, Caterpillar, Honda, IBS, Sara Lee, Volvo, Warner Brothers, Wells Fargo, Waste Management, Harley-Davidson, State of Minnesota, Avery Dennison, AAA... Profound Logic Software, Inc. * sales@rpgalive.com * (937) 439-7925 *

Hey, Ted:

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 ...

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.

-- Ted

If you have a tough problem, our gurus can probably help. Their mailboxes are always open.

* Email Ted Holt at tholt@itjungle.com