End-Of-Year Odds And Ends
December 12, 2012 Ted Holt
|
Dear Esteemed Colleagues: We made it through another year! Thanks to you, along with our writers and advertisers, this august publication is 11 years old! Who’da thunk it? Let’s wind up 2012 with various and sundry items that you sent my way. –Ted Hey, Ted: I am using SQL to query an index, similar to what I do for a logical file. The system is responding with error SQL7011 (SOMEINDEX in SOMELIB not table, view, or physical file.) Could you please shed some light on why it is happening? –Jabir What you’re trying to do makes sense from a native point of view, Jabir, but not from an SQL point of view. When you query with SQL, you ask the system for rows and columns. The SQL object types that contain rows and columns are tables and views. A SQL index’s only purpose in life is to help the system retrieve data more quickly by eliminating the need for table scans. Because of the unique features of DB2 for i, you can use native interfaces, such as Query and RPG, to access the data through indexes. But SQL–on IBM i or any other platform–wants a table or a view. Hey, Ted: I have a minor nit to pick with your article Alternatives To SQL Literals. A good programming rule is to use multiplication instead of division whenever possible. Instead of dividing by 2.2046, multiply by 0.453592. –David I agree, David. Normally I do multiply rather than divide. This idea has been around a long time. Computers generally multiply much faster than they divide, so if nothing else, you get a performance improvement by multiplying. Hey, Ted: Looking through some of your previous articles, I find the examples often contain variable names that begin with ‘SQL’. These names are reserved by IBM. Please fix your examples, if possible. This can help promote better programming practice in our community. –Warren Warren is correct. Here’s what IBM says: “Do not use host variable names or external entry names that begin with the characters SQ, SQL, RDI, or DSN. These names are reserved for the database manager.” The SQL precompiler generates names of its own, and even if the current release doesn’t use any of the names I’ve come up, that’s not to say that a future release won’t. Hey, Ted: For the past two days I have been scouring the Internet looking at RowID and RRn solutions for a way to renumber records in a work file. I found no answer. I was about to resort to good old reliable RPG renumber the records when, as if it were Christmas morning, I discovered in my inbox the Four Hundred Guru and your article Use SQL To Update A Sequence Number. I immediately implemented the technique you described in my application and it works wonderfully. Thanks much for your answer and for your timing. –Tim I’d call that fortuitous, Tim. I’m glad your problem was solved. Hey, Ted: Thanks for teaching this old dog a new trick. A day I learn something new is a good day. Your article on SQL sequence numbers makes this day a good day. –Wyatt I’m glad I could do something nice for you, Wyatt. It’s the least I could do to repay you for that delicious frozen custard you treated me to at Kopp’s in Milwaukee. Hey, Ted: I read your article on ugly (with a capital “UG”) code. Understanding that indeed there is a lesson within the article, it should be pointed out that not only is the code in question not the ugliest code on the planet, it doesn’t even come close. Compared to the vast myriad of monstrosities mired in my shop, your code example is downright handsome! It would be an easy task for me to find a dozen examples of the downright nasty evil vicious cacophonous code that I have to abide and endure. Great article, but the code ain’t ugly by comparison. –Dave P.S. I am fighting the good fight to modernize my shop’s S/36 code. It’s not even decent S/36 code! Thanks, Dave. I learned years ago that a bad programmer can–and will–write bad code with a good language. I wouldn’t be surprised if the folks who wrote the code you “abide and endure” were the kind who code before they think, a description that I’m certain does not apply to readers of this august publication. Hey, Ted: Regarding *NOMAX Does Not Mean Infinite Capacity and NOMAX? No Way!, I set the file size to *NOMAX for 95 percent of my database files. I created a job on the system that sends me a text message if disk usage goes up or down 2 percent. If I get those several times, I get on the system to check it out. –Steve Good solution, Steve! I am honored that so many smart people read this newsletter. I learn so much when they email me. Hey, Ted: Thank you for the Convert Job Log utility. I needed to compare two job logs that have almost 1,500 pages apiece. I was able to convert them in a snap. I have a better way to compare them now. –Roland I’m glad you found the utility useful, Roland. It has saved me a lot of time and effort. Hey, Ted: I’m writing to warn others about a “gotcha”. For the first time, I added an implicitly hidden column to a table in the database. All seemed to be working fine, so this past weekend, I added it to 10 more tables. There was a business need to have an automatic timestamp on these tables. Anyway, what got me into trouble was my SQLRPGLE code. I know that the description of implicitly hidden said it didn’t work the same in non-SQL interfaces, but I didn’t really know what gotchas to look for. Well, one bit me big time. Luckily we discovered it within a day. In my SQLRPGLE program, I have a data structure defined for an external file. Then, my SQL fetch statements fetch into this data structure. That is, I do a select * into the external data structure. After adding implicitly hidden fields, the number of columns in the data structure no longer matched the number of columns in the select clause, so nothing was brought back to the program. A colleague suggested an elegant, and probably correct, solution. He said if I create a view over the table and reference that view in the data structure (instead of the table name), I can control what is brought into the program without having to worry about it changing, plus it retains the simplicity I have now of using select * and an external data structure. I just hadn’t thought of using a view on a data structure before. –Cheryl I discourage using SELECT * in an application program, but if you’re going to do so, it’s certainly better to access a view rather than a table. The more views and fewer tables you can use in your SQL commands, the more flexible your database will be. Hey, Ted: I have started to implement the earlier CL error handling with the two commands mentioned in CL Error-Handling with APIs. It works great and requires less code. Now, when I am in a CL program with the old error trapping code, I replace it with the new method. –Doug You’re not the only one who liked Brian Rusch’s code, Doug. When I submitted that article for publication, I forgot to send the downloadable code to the editor, and many readers quickly let me know about it. Hey, Ted: What a lifesaver! I found that tip Converting XML Attributes to Elements. It worked perfectly! –Snowblownmind We published that tip in 2003. I’m pleased that we can leave all the old content on the web so people can find it. Hey, Ted: Raz-Lee Security’s Action module, which provides pro-active reaction to situations on the system includes a quite capable CL interpreter. This feature accepts a set of CL commands with replacement variables that come from the event itself, and supports labels, GOTO and MONMSG EXEC(GOTO label). It is not the “full thing”, but it is certainly comprehensive. Raz-Lee charges for the Action module, but the price is not very high. –Eli Spitz Eli wrote in regard to two articles we ran about interpreted CL. Here are the links to those articles, along with an article that Alex Woodie wrote for Four Hundred Stuff.
Hey, Ted: Thanks for another great article and for telling me the name of the type of join I recently did. I had a small project that I had to match up vehicle GPS points of time with service calls made. The times reported between the GPS data and the service call data did not match exactly so I used a +/- on the time of the service call to find the corresponding (at least most) of the vehicle positions. As always, you add to my knowledge, Ted. Thanks. –Russ You’re welcome, Russ. This is off the subject, but because of that tip, I read Animal Farm again in November. I hadn’t read it in several years. I thoroughly enjoyed it! Thanks for all of you who took time from your busy lives to let me know that something we did was helpful to you. Merry Christmas to all of you. May 2013 be our best year ever! –Ted
RELATED STORIES Use SQL To Update A Sequence Number Eliminate The Legitimate Use Of GOTO *NOMAX Does Not Mean Infinite Capacity A Better Way To Read a Job Log Converting XML Attributes to Elements
|
