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?