Ted Holt
-
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(); -
Where’s the Service Program?
December 10, 2008 Hey, Ted
… Read moreWe have two environments: a homegrown system for financials and a canned ERP system for manufacturing. We have written a service program containing subprocedures that access manufacturing data. This service program is stored in a library that is in the manufacturing system’s library list, but is not in the financial system’s library list. Now we need a financial program to use a routine from this service program.
We add manufacturing libraries to the end of the finance library list when we create the finance program so the compiler finds the service program and binds to it. It would be nice
-
There’s Power in Edit Words
December 3, 2008 Hey, Ted
… Read moreI am trying to use an edit word to insert hyphens into an account number. I must be doing something wrong, because leading zeros don’t show up. I’ve used edit codes for years, but rarely edit words. Can you help?
–Laurie
You’re not doing anything wrong. That’s the way edit words work, Laurie. The system blanks out everything before the first non-zero digit, including editing characters. The good news is there’s a way around it.
Let’s illustrate with an example that many of your fellow readers will be familiar with: those nine-digit Social Security numbers without which Americans can’t do
-
SQL and Conversion Strategies
December 3, 2008 Hey, Ted
… Read moreI just read your article Common Table Expressions Ease System Conversion. Instead of changing each SQL statement to add a common table expression, I’d suggest you create permanent views that join the new files. Give the views the same names and the same column names as the original file. Because a view doesn’t have a key, you can have as many views as you want without any performance decrease. A permanent view can also be used for Query/400. In this way queries do not have to be changed.
–Birgitta Hauser
I am always delighted to find mail from Birgitta