Ted Holt
-
Let’s Start Over from the Beginning
September 23, 2009 Hey, Ted
… Read moreWe have RPG programs that use SQL cursors to sequentially retrieve data. If a program cancels and I call it again, the program resumes processing a cursor where it left off. I have to sign off and back on in order to restart from the top. Why doesn’t the program start over from the beginning of the returned data set?
–Lynne
The behavior you’re witnessing comes from three contributing factors, Lynne. First, your program was compiled to close the cursor when the activation group is destroyed. Second, your program is running in the default activation group. Third, you are not
-
Use the Dup Key in Subfiles
September 2, 2009 Hey, Ted
… Read moreI hope you don’t mind another question about the “old” stuff. How do I program for the Dup key in a data entry subfile?
–David
This is a good technique to know, David, and it’s not hard to do.
Let’s start with a simple subfile of 10 records per page. There are three input fields: a date; a customer number; and an amount. Here’s the DDS:
A DSPSIZ(24 80 *DS3) A INDARA A R SFL SFL A SDATE 6S 0I 8 6DUP(31) A SCUST 8A I 8 16DUP(32) A SAMOUNT 7Y 3I 8 30DUP(33) A R CTL SFLCTL(SFL) A SFLSIZ(12)
-
Using Free-Format Calcs with Cycle Programs
August 5, 2009 Hey, Ted
… Read moreI use free-format calculations for new development and for existing cycle-less RPG programs. However, I have responsibility for some old programs that use the RPG cycle. Is it possible for me to use free-format calcs in those programs?
–Bill
Yes, Bill. In the detail calculations (calcs with no level indicator in columns 7 and 8), use the /FREE and /END-FREE directives as you normally would. In the following example, I have added free-format calcs that are to be executed at L1 detail time (before a new control group) and at detail time (for each input record).
C L1 MOVE *ZERO
-
Subqueries vs. Joins
July 8, 2009 Hey, Ted
… Read morePlease settle a disagreement. Another developer claims that it is bad to use an SQL subquery if the same results can be achieved with a join. He says that database management systems can optimize joins better than they can optimize subqueries, which means that joins run faster than subqueries. Is this true? I seem to have heard somewhere that the query engine converts a subquery to a join when possible.
–Dee
If the other developer is experienced with database management systems other than DB2, than he may be correct. However, when it comes to DB2 for i, you are correct.
-
Faster Fetching
May 20, 2009 Hey, Ted
… Read moreWhen using embedded SQL to read data, I have the option of retrieving one row or multiple rows with one fetch. Does fetching more than one row at a time improve program performance?
–Neil
I’d like to think it does, Neil. It seems to me it should. Here are the comments of John, a reader who claims that a multiple-row fetch is appreciably faster.
I have used fetch with multiple-occurrence data structures in order to read multiple records at once and minimize the use of FETCH in the program. The execution of a FETCH is quite resource intensive and slow.
-
A Not-Quite-As-Sleepy RPG Program
May 13, 2009 Hey, Ted
… Read moreThanks for the information about the sleep API. On a recent project, we had a requirement to ensure that two operations were at least a microsecond apart, but a full second proved to be far too long. (Seconds add up quickly!) We created a DLYJOBSML (Delay Job for Small Duration) command and invoked the usleep API in the CPP.
–Blair
Like sleep, usleep also delays a job. The difference is that the usleep parameter denotes milliseconds, rather than whole seconds. You might say that sleep is like going to bed at night, whereas usleep only takes a nap.
Here’s the
-
A Sleepy RPG Program
April 1, 2009 Hey, Ted
… Read moreDoes RPG have anything equivalent to CL’s Delay Job (DLYJOB) command? I am working on an RF application, and I need to code a delay of a few seconds into my program. Besides using a do loop with a final value of 1,000,000,000,000, how can I cause a timed interval within an RPG program?
–David
Thanks to the wonders of the Integrated Language Environment, RPG has a way to delay a job a specific number of seconds. It’s called sleep. Sleep requires one 4-byte unsigned integer parameter–the number of seconds to delay–which must be passed by value.
Here’s an example:
-
Mismatched Record Name Formats? No Problem!
February 18, 2009 Hey, Ted
… Read moreWe have two sets of order files–one current and one history–with the same fields but different record format names. (Who knows why?) The two inquiry programs use the same display file; and yours truly forgets to make changes in the history program when he changes the current program. I get the big brain idea to use the same program for both, but how do I override the history file to the current file when the record format names do not match?
–David
Let’s suppose your two files are called CURYRSLS (current year sales) and SLSHIST (sales history). The record formats
-
Redundant Join Criteria: Good or Bad Idea?
January 21, 2009 Hey, Ted
… Read moreSuppose I have four tables that I commonly join. Is there any benefit to adding redundant criteria to the join? Or to the “where” clause? That is, will redundant criteria or selection expressions improve performance?
–Philip
Philip’s four tables are keyed as follows:
SITE ITEM PROCESS STRUCTURE ------ ------ -------- --------- SiteID SiteID SiteID SiteID ItemID ItemID ItemID Revision Revision Revision StructIDHere’s a join without redundant join criteria:
select whatever from site as s join item as i on s.siteid = i.siteid join process as p on i.siteid = p.siteid and i.itemid = p.itemid and i.rev = p.rev join
-
Bypassing a Locked Record, Take Two
January 7, 2009 Hey, Ted
… Read moreI have a slightly different solution to the locked record problem your friends tackled in Bypassing a Locked Record. Like them, I have RPG programs that sequentially read a file and update some of the records. When a program tries to read a record that another job has locked, I sometimes bypass the locked record. Here’s how.
Notice the READ operation in the second calculation of this example:
FSomeFile UF E K Disk Prefix(SR_) D Forever S N inz(*On) D Open C Const(' ') D Closed C Const('Z') /Free DoW Forever; Read(E) SomeRec; If %Eof(SomeFile); Leave; EndIf; If %Error();