Ted Holt
-
Closing the Gaps
January 12, 2011 Hey, Ted
… Read moreWe have a database file to which records are added throughout the day. The key is a five-digit packed decimal whole number, and the program that writes to the file increments this number when adding a new record to the file. During the day, another program processes and deletes records throughout the file, leaving gaps between sequence numbers. At the end of each day, we would like to renumber the remaining records, beginning again at sequence number one, to remove the gaps and be prepared for a fresh start the next day. Can we use an SQL UPDATE statement to
-
How To Rename Your Local Database
January 5, 2011 Hey, Ted
… Read moreWhen I start up an interactive SQL session I see “Current connection is to relational database Sxxxxxxx.” Sxxxxxxx is our serial number. Is there a way to change the relational database name to something a little more meaningful?
–David
Use the Work with Relational Database Directory Entry (WRKRDBDIRE) command. The entry you need to change is for remote location *LOCAL. For more information about WRKRDBDIRE, see Using the WRKRDBDIRE command at IBM‘s Info Center.
–Ted
That solved the problem. It was little scary because I couldn’t change the name, but had to delete the existing one
-
How To Rename Your Local Database
January 5, 2011 Hey, Ted
… Read moreWhen I start up an interactive SQL session I see “Current connection is to relational database Sxxxxxxx.” Sxxxxxxx is our serial number. Is there a way to change the relational database name to something a little more meaningful?
–David
Use the Work with Relational Database Directory Entry (WRKRDBDIRE) command. The entry you need to change is for remote location *LOCAL. For more information about WRKRDBDIRE, see Using the WRKRDBDIRE command at IBM‘s Info Center.
–Ted
That solved the problem. It was little scary because I couldn’t change the name, but had to delete the existing one
-
Find Time and Date Durations in RPG
December 8, 2010 Hey, Ted
… Read moreTime and date calculations fall short in ILE RPG. I have seen many examples of handling date or time math on IT Jungle and other Web sites. All of them calculate one unit of time only–either years, days, hours, minutes, etc. In the application I am working on, I need to find an interval in days, hours and minutes, but I have found nothing that subtracts one date from another and gets a true answer in mixed units.
–Al
RPG does not have date and time durations, as SQL and Query do. (For more information about durations, see the articles
-
Case-Sensitive SQL Identifiers
December 1, 2010 Hey, Ted
… Read moreLike most people who use SQL, I have never bothered worrying about which case a field name is written in. Case never seemed to matter. However, while performing a data fix, I discovered an instance where it does. (Fortunately, I was using test data!)
I had a number of files from which records were to be deleted. The key values of the records to be deleted were held in a spreadsheet. There were three fields, one of them called VERSION, in the files I was deleting from.
I tried to create a table to store the contents of the spreadsheet,
-
USA Time Format in Query for i, Redux
October 6, 2010 Hey, Ted
… Read moreI am having trouble converting my time format from military time. That is, the time fields in my system are six-digit numeric fields. I want to print them in the common AM-PM format we use in the United States.
–Ronnie
I’ve covered this topic twice that I know of. Both tips ran in 2003. (See Date and Time Formats and Query/400 and Miscellaneous Query Tips. I’ll cover it again today, because some readers who might like to know about this technique didn’t see it then and don’t know it’s out there, and because I have a slight improvement on
-
CASE Simplifies SQL Update
September 29, 2010 Hey, Ted
… Read moreI need to run a series of similar updates against 12 groups in a very large physical file (millions of records). Is there a way I can combine the updates into one SQL update command? I’d rather have the system read the file once, not 12 times.
–Jim
Here’s an example that illustrates Jim’s update. Set the minimum order quantity to 12 for all items in class A1.
update items set minordqty = 12 where class = 'A1'
If Jim has 12 class values, he has to run 12 update commands. However, if he were to use a CASE structure,
-
How Did I Do That?
August 11, 2010 Hey, Ted
… Read moreYour tip for building spreadsheets from SQL statements is spot on. The problem I used to have is that people would come back to me days, weeks, or even months later, asking for me to rerun my query to give them current data. I no longer have that problem, and here’s why.
After pasting the SQL data into the first sheet of my Excel file, I copy my SQL command(s) into the second sheet. When the user asks me for a data refresh, I open the spreadsheet and retrieve the SQL. No more time wasted looking through my SQL history.
-
Spaces, Braces, and Semicolons
June 30, 2010 Hey, Ted
… Read moreMy Qshell script would work perfectly if I could make it count correctly. It thinks I have more objects in library QGPL than I really do have. Can you help?
–Hugo
Today is a spectacular day to improve our Qshell skills, thus improving our Unix and Linux shell skills and beefing up our résumés.
Here is Hugo’s Qshell command:
find /qsys.lib/qgpl.lib/ -name '*' | wc -l
The “find” utility produces a list of everything (-name ‘*’) in QGPL. The list is passed along to the “wc” utility, which counts the number of lines (-l) in the list. The problem is
-
SQL Performance: IN vs. EXISTS
June 16, 2010 Hey, Ted
… Read moreConcerning your article Update One File Based on Another File, I would agree that the IN is more intuitive than the EXISTS. When looking at the volume of data, possible number of rows to update, and the rows returned for the IN, is there a preferred method if considering performance? Does the IN or EXISTS result in better performance under certain conditions?
–Sarah
The prevailing wisdom is that EXISTS currently tends to outperform IN. Here are comments from two readers that support that position.
A nice consequence of going the IN route (as opposed to using EXISTS) is that,