Ted Holt
-
OPNQRYF Has No “If” But You Can Fake It
November 5, 2008 Hey, Ted
… Read moreWe recently started adding Web customers to our customer master file, in which a customer ID is a field of seven characters. The ID’s of Web customers consist of the letter “W” followed by six digits. Is there any way using OPNQRYF to change only the customer IDs that start with “W” to the customer number “5000000” for reporting purposes? Doing so would allow us to keep a lot of existing programs running without a lot of modification.
–John
OPNQRYF does not have conditional logic, John, but in your case, you can fake it out. Here’s the type of OPNQRYF
-
Reader Feedback on Database Modernization Still Unknown Territory
August 18, 2008 Hey, Ted
… Read moreI seem to have missed something on the DDS versus DDL topic, which Dan Burger wrote about in Database Modernization Still Unknown Territory. Explain why and how DDL is faster if they both run on the same box. The speed of the disk drives is the same. If there is some “super algorithm” for DDL, why can’t it be applied to DDS? Is there some design feature of DDS that just makes it slow compared to DDL, or is DDL really just something new just because IBM thought it was time for a change, kinda like the i versus
-
Print Part of an IFS File
July 23, 2008 Hey, Ted
… Read moreLet’s say I have an IFS file with several thousand lines in it. Let’s say I want to print a few lines of that file. My current method is to copy the IFS file to another file, use Edit File (EDTF) with the copy to delete the lines I don’t want, and use EDTF’s Print command to get the report. Please tell me there’s an easier way.
–Bob
I recommend two Qshell commands. Sed, the stream editor, can select the lines you want to print. The Rfile utility can send them to a printer.
Now, how do you want
-
API Fun Time
July 16, 2008 Hey, Ted
… Read moreWe have a job that is designed to run periodically to collect XML files from a third party via FTP. Rather than have a program that is constantly active, it was decided that the very first action of the program would be to re-submit itself to a single entry job queue with a scheduled run time derived from a maintainable value held on a file. The benefit of this approach is that if for some reason the program fails there is no need to worry about re-starting the process–unless of course the program failed at the self-submit stage!
When an
-
A Handy SQL Timestamp Function
July 9, 2008 Hey, Ted
… Read moreI have found a great tool for working with timestamp data. I have used it for performance tracking and analyzing data intervals. It’s SQL’s Timestampdiff function, and I think you should tell readers of Four Hundred Guru about it.
Timestampdiff returns the difference between two timestamps in the interval of your choice. The first parameter for the timestampdiff function is the interval to use. Valid values for this parameter are:
We use SQL’s CREATE TABLE command to make each user his own copy of a file. Although the original file is keyed, the copy is not. Do you have any ideas on how to keep the key?
–Mary Jo
Mary Jo provided the following commands by way of example:
create table Temp as (select * from PATTERN) with no data rename table Temp to FileForJoeSmith
So, what’s Mary Jo to do?
The first thought I had was to create the index by hand, like this:
create unique index FileForJoeSmithIndex1 on FileForJoeSmith (SomeField)
But that was not a solution to the
-
Replace the Contents of a Physical File That Has Triggers
May 7, 2008 Hey, Ted
… Read moreI need to copy the contents of a physical file from our production system to its counterpart in our development system, which is a separate logical partition. I have several ways to copy the file from the production system to the development system. However, error messages I get say that I cannot replace the data because the database file has triggers over it. Help!
–Tricia
You need to disable the triggers. Disabling triggers is most easily done if all of the triggers are active. Use Change Physical File (CHGPF) to disable the triggers.
CHGPFTRG FILE(MYLIB/MYFILE) TRG(*ALL) STATE(*DISABLED)
Then copy the
-
Multiformat SQL Data Sets
April 30, 2008 Hey, Ted
… Read moreDDS-defined logical files can have multiple record formats, each one of them coming from different physical files of different types of data. I would like to do the same sort of thing in SQL. That is, I want to retrieve all the records from one file followed by all the records from a second file, grouped by one or more common key fields. This is not a join, and it doesn’t seem like a union either, because the two data sets are so different. Am I trying to do the impossible?
–David
What you’re doing may be unusual, but it’s
-
Build Pivot Tables over DB2 Data
April 30, 2008 Hey, Ted
… Read moreIf you already know about these, then just hit the ol’ delete key on the message. I learned how to do this today. SQL is great for going “down the page.” It’s when they want data summed across that it gets to be a real kludge! Pivot tables are the answer.
It started with your article Load a Spreadsheet from a DB2/400 Database. I got it working! Sweet! Miracles never cease! Thanks a bunch!
Once the data is loaded into the spreadsheet via the SQL statement, make sure the column headings have decent labels. Open the Data menu and
-
A Recycle Bin for the IFS (Sort Of)
April 23, 2008 Hey, Ted
… Read moreWe inadvertently deleted an IFS file that was created today and therefore was not on the previous night’s backup. What I wouldn’t give for an IFS recycle bin! We can recreate the file, but I wonder, short of backing everything up every minute, if there is anything that I could have done to prepare for such a situation?
-Chris
My sympathy, Chris. I hate it when that happens. As you point out, the IFS has no recycle bin, but there is a way you can delete a file from a directory without deleting it from disk. I’ll show you the