Ted Holt
-
No Truncate Table? No Problem!
March 23, 2011 Hey, Ted
… Read moreI am working on a project using DB2 for i, but my experience is with other database management systems. I can’t find the SQL TRUNCATE TABLE statement. Does DB2 cover this functionality some other way?
–Brad
Even in 7.1, the latest release, IBM does not implement the TRUNCATE TABLE statement. However, since this statement is included in other DB2 products, as well as in Informix, I expect we’ll see it eventually as part of our world.
For the benefit of readers who are not familiar with TRUNCATE TABLE, this statement removes all rows of a table (records of a physical
-
Circumventing Integer Division
March 16, 2011 Hey, Ted
… Read moreMy computer seems to have forgotten how to divide. When I divide one value by another, using SQL, the answer is always exactly one or zero. What gives?
–RPG Professional
This took a little while, but we finally found his problem. First, the background.
Assume a database file with three fields: customer ID, the number of orders from that customer, and the number of shipments to the customer.
select CustID, Orders, Shipments from Summary CUSTID ORDERS SHIPMENTS 1 75 25 2 10 16 3 20 0 4 8 8Now let’s add a ratio to the query to gauge order
-
Avoid Division by Zero in Query/400
February 23, 2011 Hey, Ted
… Read moreI want to build a temporary database file to send to a user, but records with a zero divisor do not get written to the output file. Can I prevent Query for i from dividing by zero?
–Bill
Yes, Bill, and it’s not difficult. Here’s how it’s done. But first, some test data.
create table mylib/divbyzero (Extended dec(5,2), Qty dec(3,0)) insert into mylib/divbyzero values (500, 2), (0, 12), (6, 0), (0, 0), (100, 3)
Now I have five rows of data to play with, two of which have zero in the Qty field. Here’s my first attempt to create an
-
OUTQ vs. PRTDEV
February 16, 2011 Hey, Ted
… Read moreSeveral CL commands–Create User Profile (CRTUSRPRF) is a good example–have two parameters that control the destination of printed output. Where is the need for both print device and output queue? Can you explain the role of these parameters in determining the destination of output?
-Tim
I know of no need to use both print device and output queue (“outq”) parameters. I have always managed fine with outq’s only. The System/38, one of the predecessors of the AS/400, used outq’s exclusively. I understand that IBM added the print device option to support software that had been developed for the AS/400’s other
-
Cleaning Up RSE Detritus
January 19, 2011 Hey, Ted
… Read moreNote: The code accompanying this article is available for download here.
Hey, Ted:
While doing some year-end house (disk) cleaning, I ran across the temporary source files that CRTSQLRPGI uses when run from RSE (WDSC or RDx), which are named EVFTEMPF01 and EVFTEMPF02. I knew about these files but never paid much attention to them. Today I discovered that there were 20 files (10 of each type) with total size of 45MB.
I wrote the attached clean-up program and a command interface. I scheduled the command to run every weekend.
I didn’t clear the file at the end so
-
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,