Ted Holt
-
Customize the PC5250 Popup Keypad
July 18, 2007 Hey, Ted
… Read moreI just found out that I can change the function of the keys on the Personal Communications Popup Keypad, similar to the way I can map the keyboard. I created a new keypad with six functions on it. Here’s how I did it.
From the PC5250 session within iSeries Access (Client Access), pull down the Edit menu, then select Preferences and Popup Keypad. (Editor’s note: In some versions of Personal Communications, use the Assist menu and select Popup Keypad Setup.) Select the User-Defined keypad, rather than the IBM Default, and click on Customize.
At this point, you can expand the
-
Comment Your Binder Language
July 11, 2007 Hey, Ted
… Read moreBecause of Sarbanes-Oxley, we have a new requirement in our shop that all source members must include comments that tell the usual stuff–who did what, when and why they did it, etc. While we have always done that sort of thing (well, most of the time), there are certain types of source members we never bothered to comment. A good example is ILE binder language. I’ve looked for, but never found, a document that tells how to comment binder language. Do you know?
–Pat
IBM didn’t make it easy for you, Pat. I wondered the same thing myself a while
-
Conditional Counting with Open Query File
June 20, 2007 Hey, Ted
… Read moreIs there a way to make Open Query File (OPNQRYF) count records that meet certain criteria? I want to write a summary query, using OPNQRYF, that counts all records, and that also counts only the records that have a positive value in one of the numeric fields. Can OPNQRYF do anything like that?
–Dave
When I got Dave’s question, I told him that I thought it could be done, but I’d have to play with it to figure it out. A little while later, Dave had his query and I had another technique to share with you.
It would be
-
Joining on Mismatched Values
June 13, 2007 Hey, Ted
… Read moreLike many shops, we have a mixture of database files that we have acquired from different sources due to mergers and acquisitions over the years. Getting things to match up is challenging, to put it mildly. I have two files that contain warehouse code fields, but the codes do not match exactly. In one file, a certain warehouse is indicated by the letter T. In the other file, the same warehouse is the character 1 (one). All other warehouses have the same codes in both files.
I need to join these files, but I don’t know what to do about
-
Special Files Can Do It All, Part 2
June 6, 2007 Hey, Ted
… Read moreI just wanted to tell you how much I appreciate your real world ideas and examples. They are the kinds of ideas that can really help smaller shops do the practical, bulletproof kinds of jobs we need to do. I have used your special files technique to replace some old System/36 #GSORT sales analysis programs. I know, it takes us a long time to convert, but some of these programs have not changed in 15 years.
We have S/36 OCL procedures that display a screen to ask for parameters, use #GSORT to create an address-out file using the parameters for
-
Load a Spreadsheet from a DB2/400 Database
June 6, 2007 Hey, Ted
… Read moreI know from my reading that it is possible to make an Excel spreadsheet read from an iSeries database. I’ve been an RPG programmer for years, and I don’t know where to start. Can you point me in the right direction?
–“D”
I’ll do my best. I have some Visual Basic code that I’ve used for years that I can share with you. I wish I could give credit to the original author, but I don’t remember where I got it.
First, crank up a new Excel spreadsheet. Pull down the Tools menu, select Macro, then Visual Basic Editor. The
-
Send a Spool File from AS/400 with a Specific Subject Line and Message Body
May 23, 2007 Hey, Ted
… Read moreNote: The code accompanying this article is available for download here.
A while back, I was working on a project that required an e-mail to be sent with a resulting spool file as an attachment and with a specific subject line and specific message body. I had no trouble figuring out how to send the spool file as a document and how to create the message body. The hard part was customizing the subject line of the e-mail. This was important because the subject line would have provided the users a hint about the urgency of the task they
-
WHERE Versus HAVING
May 9, 2007 Hey, Ted
… Read moreWhat is the purpose of SQL’s HAVING operator? It seems to me to be unnecessary, since the WHERE operator can choose which records are to be included in the query. What am I missing?
–Rich
Don’t sweat it, Rich. This is a very common point of confusion. Let me try to straighten it out for you, and in the process I’ll add a tip that some of the more experienced readers may not have seen before.
Sometimes you can get the same result either WHERE or HAVING. The following two SQL commands produce the same result set. That is, both
-
Monitor for Specific Messages in RPG
May 2, 2007 Hey, Ted
… Read moreI have a CL program that calls another CL program and monitors for specific escape messages. No big deal. I put multiple Monitor Message (MONMSG) commands after the CALL. Suppose I want to call the second CL program from an RPG program. How do I monitor for the different errors?
–Ray
OK, Ray, let’s set this up for the readers. Let’s suppose your program, which we’ll call SOMEPGM, can return escape messages MYM2101 and MYM2105. A CL caller monitors for those messages like this:
CALL PGM(SOMEPGM) PARM(&whatever) MONMSG MSGID(MYM2101) EXEC(DO) /* do something */ ENDDO MONMSG MSGID(MYM2105) EXEC(DO) /* do
-
Missing In Action: The Full Outer Join
April 11, 2007 Hey, Ted
… Read moreAs far as I know, you haven’t covered my situation in Four Hundred Guru. I need to join two physical files using SQL. It’s possible that some records in the first file won’t have matches in the second file. It’s also possible that some records in the second file won’t have matches in the first file. What type of join do I use?
–Chris
You need a full outer join, Chris, and DB2/400 (or whatever they call it these days) won’t handle it–yet. I expect IBM to add the full outer join any release now.
To simulate the full