Ted Holt
-
Don’t Let SQL Name Your Baby
February 6, 2008 Hey, Ted
… Read moreWe have started using SQL Data Definition Language (DDL) to define our physical files. One feature we really like is that we can use column (field) names longer than 10 characters. One feature we don’t like is that SQL creates ugly alternate names of 10 characters that we end up using in our RPG programs. Is there any way we can rename these ugly names?
–Paul
I know what you mean, Paul. If a column name is more than 10 characters long, SQL creates a shorter column name from the first five characters of the field name and a five-digit
-
More About Blocking
January 23, 2008 Hey, Ted
… Read moreDon’t Disable Blocking was a good article. I would like to offer two suggestions for improvement.
First RPG ILE provides a BLOCK(*YES) file level keyword, which allows compiler-generated blocking when a random I/O operation is issued to a file processed sequentially using a READ. The most common scenario is “Keylist SETxx SomeFile” followed by READ. Note that READE is not treated as a sequential operation.
Second, I am a little disappointed that after your breakthrough OPNQRYF to SQL article using a SPECIAL device, you didn’t recommend the same approach for this situation. SQL automatically generates efficient blocking instructions for
-
Stuff I Didn’t Publish This Year
December 12, 2007 Ted Holt
… Read moreNote: The code accompanying this article is available for download here.
My wife and I recently visited our local Cracker Barrel, which is one of our favorite restaurants. While we were waiting for our food, I picked up the peg game that unfailingly occupies patrons at the tables of that establishment and, for the umpteenth time, failed to solve it.
The Peg Game
In case you’re not familiar with a peg game, here’s how it works. The board has 15 holes arranged in a triangle and 14 of the holes are occupied by pegs. To
-
Create Multiple Directory Levels in One Swell Foop
December 5, 2007 Hey, Ted
… Read moreMaybe I’m getting old, but I seem to remember being able to create several levels of directory structure with one command. I currently have such a need, in an application that creates XML files in the IFS. Yet CL’s MAKE DIRECTORY (MKDIR) command fails with error message CPFA0A9. Am I imaging things, or has something changed?
–Lynn
My guess is that you’re remembering MS-DOS’s md command. The following example contains a command that creates from one to five directories as needed.
C:>md salesreports2007 1RegionC
CL’s MKDIR command can only create one directory level at a time. However, Qshell’s mkdir command can
-
Update Only One of Duplicate Records
December 5, 2007 Hey, Ted
… Read moreWe have a file that has no unique key. This means that we sometimes get duplicate records in the file. This does not cause us problems in RPG programs because we can read a record, make changes to it, and update it, whether we’re using native I/O or an SQL cursor. However, having duplicate rows is a problem when we use SQL outside of programs. If two or more records are identical, there is no way to code a WHERE clause that will select only one record. Or is there?
–David
It is ugly, but possible, David. Assume table MYDATA,
-
ON vs. WHERE
November 14, 2007 Hey, Ted
… Read moreI need your help with an SQL query. I have two database files: a header file and a detail file. There may be many detail records for each header record, but I only want to see certain detail records. Here’s the rub. I want to retrieve all header records, even the headers for which I don’t select any corresponding detail. It seems to me this should be an easy query to write, but my efforts so far produce only the header records for the selected details.
–Wayne
There’s more to this query than meets the eye, Wayne. It is simple,
-
Copy Message Descriptions
November 7, 2007 Hey, Ted
… Read moreThe Work with Message Descriptions (WRKSMSGD) panel gives me options 2, 4, 5, and 6 to change, delete, display, and print message descriptions. But there is no option to allow me to copy a message description. Such an option would be helpful when I need to create a new message description that is similar to an existing one, or when I copy a message description to another message file. Am I overlooking an easy way to copy message descriptions?
–Ron
To the best of my knowledge, no. (Buddy, I hope I’m wrong on this one.)
Let’s take the second case
-
Converting LF to CRLF in IFS Files
October 17, 2007 Hey, Ted
… Read moreI had a situation where I wanted to change the end-of-line character from linefeed to carriage-return/linefeed to make a JavaScript file on the Integrated File System more readable in Notepad. I stumbled upon EDIT FILE (EDTF), F15, option 5. Then, after a little searching, I found your articles. Talk about reinventing the wheel! I was surprised that changing end-of-line characters was not part of the CHANGE ATTRIBUTE (CHGATR) command (or some other command). Is there any chance you could share a Qshell solution that converts LF to CRLF? I love using Qshell when I can, but
-
Weird SQL UNION Results
September 26, 2007 Hey, Ted
… Read moreI was intrigued by your article Have Your Cake and Eat It, Too. I decided to try your tip with some of my own data. The resulting data set had an anomaly in it. The system omitted a row of input, yet the total was still correct. Why?
–Wyatt
It’s my bad, Wyatt, and I should have known better. (Note to self: Leave instructions for my survivors to put those last five words on my tombstone. It seems to accurately describe my life so far! 😉
The problem was that the example used the word UNION, but it should
-
Reuse Deleted Records? *YES!
September 12, 2007 Hey, Ted
… Read moreHow does the iSeries find a space to insert a new record over a deleted record when REUSEDLT(*YES) is specified for a file? An associate says that the file is scanned sequentially for a deleted record when writing a new record to the file and as a result there is a significant performance penalty.
–Millard
I sent your question to Kent Milligan of IBM. According to him, “A table scan is not performed. A bitmap-like structure is used to identify available deleted rows within the table.”
So, Millard, there are no performance-related reasons not to reuse deleted records. This topic