Jon and Susan Get Educated

Some of you may remember that a few weeks back we mentioned that we would be heading to Boston for the inaugural DB2 for i Directions conference, and that we were looking forward to being students for a change. Well, it was a great event and we learned a lot -- our brains hurt!

One of the non-DB2 things we learned was to have more sympathy for our own students -- sitting in class and learning, learning, learning for days on end is tough. If we were a little closer in time to our school days (we wish!), perhaps it would have been a little easier. It made us even more determined to improve our own teaching skills to ensure that our students don't become overwhelmed. We'll be even more on the lookout for that "glazed over" look on our students' faces! We also learned that "Naked Fish" is a great restaurant that could use a few lessons in customer service, and that the Boston area has some really great brew pubs. 

On the DB2/SQL front, we learned far too much to be able to share it all with you here, but we will be covering a couple of items in the next edition of EXTRA so make sure that you're signed up to receive that when it comes out later this month. But just to give you a flavor of some of the things that came out during the course of the event ...

Handling Dates in SQL
For years we've watched as Internet discussions covered the topic of handling date fields in SQL -- for example when the DB2 table contains a "real" date field but the field in your program is an 8-digit numeric in YYMD form. This often leads to examples that include code such as this:

Select Date(Digits(MyNumDate) concat '000000') From MyTable ...

And that's a simple example. When the fake date is in character form things can get really ugly really quickly. The technique that we were introduced to during Directions came courtesy of a session on Web Query and is based on populating a table that has a row for each individual date in the range that you want to cover and a column for each replacement or comparison form you want of that date. For example for the date of February 1st, 2011, you might have a column containing 02/01/01, one containing 2001/02/01, one containing "Tuesday February the 1st, 2001", one containing 32 (the Julian day number), and ... well, you get the picture. It was kind of a "why didn't I think of that" moment. Rather than come up with a nasty convoluted SQL statement, a simple lookup can be incorporated in the statement which compares directly against the required date format. If you can't wait for EXTRA to come out to see how this all works, you can find the details in an appendix of the Web Query Redbooks publication. Does this approach perform well? According to the Redbooks, it outperforms direct usage of the SQL functions that would otherwise be required. We'll let you know what we find as we explore this further. 

CHAIN vs SELECT
During performance discussions with IBM's Mike Cain on the use of SQL for single row (as opposed to set-at-a-time) retrieval, Mike pointed out something that we hadn't thought about. Namely that if the SQL references only those columns that form part of the key, then the system is smart enough to know that it does not need to access the table itself, but rather can determine the found/not found status simply by referencing the index. Although we haven't had time to test this yet, Mike is convinced that it would perform as fast or faster than a CHAIN or SETLL used simply to determine if a given part number, customer number, or whatever, is valid. When we get a chance to test this out, we'll let you know the results.

We learned in Boston that it can be fun -- and tiring -- to be students. We came back with many good ideas on using SQL in our RPG programs. Not to worry -- we're still a long way from the "SQL is the answer -- now what's the question?" camp. Besides, we all know that RPG is the answer -- with or without SQL -- no matter the question, right? 

 

 

Comments

[J Taylor]

Re CHAIN vs SELECT:
That's great news, although I doubt SQL could beat a CHAIN, a tie would be good enough for me. Did he mention if that applied to specific releases? I'm one of those (many) on the trailing edge when it comes to OS version.

[Roger H.]

Two comments.
1) We've used the date table approach for years and I love it. We need a table for fiscal periods anyway and we have several variations of the date form (verbose, short & long julian, etc) in there too.
2) Laughed out loud at the comment on "SQL is the answer.....". Had a programmer a few years ago who was a "Jim Jones" type of believer in that philosophy. Ugh....

[J Taylor]

I ran my own little CHAIN vs SELECT test, and the results were not good for SELECT.

I used the same uniquely-keyed, DDS-described PF in SQLRPGLE under v5r4. I measured the elapsed time with RPGLE TIMESTAMP fields. A CHAIN using all the key fields registered 0.000000s. A SELECT INTO using all the key fields in the WHERE clause took 0.500000s (yes, 1/2 second).

I'll be sticking with CHAIN for now.

[Jeanne F]

Just finished reading the article in EXTRA and found the performance test in 'Existence Testing via SQL' well covered. Since our shop is moving away from native I/O, I am always looking for SQL alternatives to boost performance. Thank you for providing several options.

Here is yet another approach:
Exec SQL
With ROWS as
(Select Count(*) as RowCount
from OrderNum where Order_Num = :charKey)
Select '1' into :ValidRow
from ROWS where RowCount=1;

[Darren]

One tip for performance, use "FETCH FIRST ROW ONLY" at the end of your statement. This prevents the SQL processor from looking for additional records, when you're really only looking for one.

Also, for performance reasons, I think you should avoid use the SELECT INTO command in a loop where it will be hit so many times. Its better to build it into whatever data set you're reading through to compare. A subselect with a FETCH FIRST ROW ONLY is pretty powerful.

[Rick]

Jon and Susan - a good series of articles.

As a Manufacturing firm that's used the calendar table concept since the System/38, I'd be tempted to say we invented it but I'm sure others would challenge that assertion. We used our calender due to the 4-4-5 accounting year the company adopted. Each quarter had 13-weeks with all months ending on a Saturday and the last month of the quarter having 5-weeks. That extra week helped our monthly shipping plan! We were able to lookup accounting Year, Quarter, Month for any date as well as working and non-working days and day names. We'd publish the calendar, first on green screen or IPDS printers and later as a web page. A revision to the very same calendar also contained the date in native date format, when that became available in OS/400, helping us to remediate the "Y2K problem" very effectively.