End of Year Miscellanea
December 15, 2010 Hey, Distinguished Professional
The year’s almost up, believe it or not. What’s even harder for me to believe is that we’ve been putting out this newsletter for almost 10 years. It’s been wonderful to hear how you’ve benefited from Four Hundred Guru. Let’s end 2010 with some feedback from readers of this august publication.
Thank you very, very, very, very much for the extremely helpful tip showing how to edit spool files. Under big time pressure, I couldn’t fully test mandatory payroll changes before payroll ran them. In the middle of the check it would jump to the next check and continue on the correct line. Big Ooops!
I thought I’d have to write a program to read the spooled file after it was copied to a database file. The program would figure out, based on the printer control characters, what line I had, and create a new output database file. Then I would download that and do a mail merge into Word. I figured this process would take me until at least 3 o’clock the next morning. Because of your great article, I went home at 9:45 p.m.
Many, many thanks once again for making this available on the Web. Twenty minutes of research for a possible better solution paid off big-time!
I’m glad it helped you, Neil. Feedback like yours is what makes putting this newsletter together so rewarding. You made my day.
I used your article on global temporary tables to solve an SQL problem that was driving me nuts! The goal was to show containers that had hazardous materials first, then show containers without hazardous materials. I considered adding a field to the container table, but didn’t want to trust users to set it correctly (and you just don’t mess around with hazardous shipments). I easily solved the problem by creating a temporary table, joining it to other tables, and ordering by a coalesced count. Thanks for the help!!!
I’m not sure which article Tim was referring to, and didn’t think to ask at the time. Here’s more about global temporary tables.
You can skip the unnecessary step of dropping the temporary table by adding the WITH REPLACE clause.
exec sql declare global temporary table Temp1 as (select * from sales where companyno = :inCompany and customerno = :inCustomer) with data WITH REPLACE;
If the table already exists, the system will blow it away prior to creating it.
Robert wrote in response to Global Temporary Tables and Host Variables. I had forgotten about WITH REPLACE and was thankful for the reminder.
I saw Justin’s tip regarding updating spreadsheet data fed by SQL statements. There’s a much easier way to do things. MS Query will feed an SQL statement into the spreadsheet and will allow the user to refresh at any time. It also allows parameters in the statement (but the statement has to be in the graphical format, they’re not allowed in the SQL format). Parameters greatly expand the usability of this feature for the users.
Using JDBC you can specify remarks=sql on the connection string and that will get you the SQL label instead of the field name. That can also be set in the GUI properties using iSeries Navigator.
Coy’s comment is in response to Every Bucket Deserves a Descriptive Name.
With every article I read about Qshell or Unix commands, I’m more convinced than ever that, though they were probably (partially) born out of the necessity of simplicity (in the sense of miserly use of resources–definitely not in the sense of making sense), they’ve stayed cryptic all these years for just one reason: By the time a person masters their use they’re too proud of their accomplishment to want to negate that accomplishment by upgrading the “language” to something that resembles a 21st century (or late 20th century) language. After all, why make it simpler for those who haven’t endured the learning curve themselves?
You’re not the only person who’s unimpressed with Unix. Read the next correspondence.
“There’s two things that came out of Berkley in the 60s: LSD and Unix and that’s not a coincidence.”
I had never heard this quotation. I found out it’s attributed to Jeremy S. Anderson and is probably not true. Here’s one Web site that deals with it.
We need something to help these one-man shops that don’t have the time and resources to get out and learn new things that apply to everyday programming. I know there are things I am doing today that could be done easier or faster, but having the time to learn them is not an option so I have to pick up pieces wherever I see them. The articles in the Four Hundred Guru are fantastic, and I have picked up many tips on how to do things in different and better ways. Thanks, Ted. There are a lot of us out here who really appreciate what you are doing.
I’ve been a one-man shop too, Duane. I know where you’re coming from.
Thanks to everybody who took time to write.
That does it for this year. I plan to enjoy Christmas, and hope that you also enjoy the holidays. May 2011 be our best year ever.
Every Bucket Deserves a Descriptive Name
Global Temporary Tables and Host Variables