Thanks for the Comments!

We're supposed to be on vacation so instead of coming up with a new topic for this week, we thought we'd highlight a few of the recent comments on posts and continue some of those discussions.

Our "Jon and Susan Get Educated" post sparked a number of comments. One in particular made us realize that we had erred in our tests. While reading J Taylor's comment on his own SQL vs. Native tests we realized that we had in fact given the Native options an unintentional edge. We had not included the open/close of the file within the timing loop. Those tasks had been left to the RPG cycle logic so the comparison was very slightly slanted against SQL. "J" did not mention how many iterations of the loop he performed, but from his results we wonder if it may have only been only a single iteration. If that is the case, then SQL would never have stood a chance -- the initial overhead would have been too great to overcome.

Jeanne F offered an alternative possibility using the SQL COUNT feature. We did try that option, Jeanne, and the results were not pretty.

Darren commented that, in such circumstances, adding FETCH FIRST ROW ONLY would boost performance in cases where the lookup field was not unique. In our case, the lookup key was unique, but we tried it anyway and in this case it had a slight negative impact. In other tests we ran though, where the key was not unique, it did result in a significant performance boost. Darren also suggested a completely different approach to our "existence testing" example which would be a better solution in many circumstances. In our case, we were trying to retrofit an SQL solution into a situation originally designed for record level I/O so it was tough to rework the logic to fit. 

Susan especially appreciates all the advice and support in comments on "Susan's Adventures in Mac-Land." We particularly enjoyed the thought of Rick's contrast between the gestures used with iPad (and Mac) and gestures used with the PC! Rick -- an overdue response to your question re: Parallels. First, I never even considered Bootcamp because I don't want to re-start my system to switch between Mac and Windows applications. I do it too often. Even though RDP is the only Windows application I use on a daily basis, I do switch back and forth a lot. In choosing between Parallels and VMware, Parallels seems to allow the two worlds to coexist a bit more seamlessly, as Aaron alluded. 

After our last post on the "New Release Cycle," IBM's Gina Whitney kindly posted details of how the DB2 team is making its announcements.Rather than repeat that information here, we'll simply point you to Gina's comment.

Thanks for the interactions -- keep those virtual cards and letters coming it!

The New Release Cycle

As some of you may have noticed, IBM has changed the game in terms of the way that new features and functionality arrive on the system.It wasn't so very long ago that "new functionality" and "new release" were effectively synonymous.If you wanted a new function or feature you had to wait for the new release to be installed. Now that's no longer the case. New features are being added regularly via PTFs, and perhaps more importantly are no longer available only on the latest release. In fact, many of the recent additions have been made available on V6 prior to being made available on V7. Our fellow blogger, Dawn May, announced on her blog some recent updates for Systems Director Navigator. Other parts of IBM are announcing new support through variety of vehicles. As we come upon new ones, we'll let you know. If you already know of some other official sources, please let us know via the blog comments. 

Changes have included two enhancements for RPG's XML support, both of which we have previously covered in detail in the articles "XML-INTO Revisited" and "New and Improved XML-INTO". Last week, IBM's Barbara Morris announced, via the RPG Cafe's Blog, the latest update in our favorite language. This one is not perhaps quite so exciting, but nevertheless an important change. The Blog appears to be THE place where the RPG team are announcing such enhancements.

The purpose of the new support is to improve the performance of date handling. By this we mean "real" dates (i.e., the D data type) not just numeric or alpha fields that you think of as dates. In order to understand how the new support works, it's perhaps helpful to understand how things work today. When a date field is moved from one date field to another, the generated code validates the date while converting it to the internal binary form** - once converted, it's then converted to the required target format. Obviously this all takes time. More importantly, since the system will never allow an invalid date to be stored on disk, it's a complete waste of time if the field originated from the database!

The new support speeds this process up by handling the date as a character field whenever possible thereby completely avoiding the validation/conversion process. IBM warns that this support should be used with caution because it is conceivable that you've done something "odd" that resulted in an invalid date field, but we suspect that in practice this will be a very rare event.

We intend to do some testing of this support when we have a chance and will report back on our findings. In the meantime, you can find the details, including the required PTF numbers, here. 

For now though, it's back to training a group of mainframe RPG programmers on the joys of IBM i, RDP and RPG IV. We'll let you know how that's working out next week.

 

** In case you didn't realize it, on the disk dates are actually stored as a binary integer which is a count of the number of days since ... well it doesn't really matter and we can't be bothered to look it up but the start of the Gregorian calendar sounds about right.  

 

Susan's Adventures in Mac-land

Susan wrote this week's post

I love it! Can't believe it took me so long to make the switch from PC to Mac. After all, it was on my new year's resolution list -- for 2010! As our regular readers have no doubt already surmised, I'm a little slow to accept change and try new things. Jon is definitely the company (and family) guinea pig.

But better late than never! I've been working on my Mac now for a few months and I'm pretty close to reaching Macaholic status. I had to work on Windows again recently when setting up for a hands-on class and I couldn't wait to get back to my Mac.

What's so great about Mac for me? There are dozens of small things, but here are a few of the bigger ones.

1) Jon beat me to this one but the Mac Finder function is just magic! If you could see the mess of stuff on my physical desktop (and I'm very glad you can't!), you would understand why having Finder is so important to me. Filing is not my strong suit. While Mac's Finder can't help me find thing on my physical desktop, it can at least help me find things on my virtual Mac desk, which is equally disorganized -- searching for files or folders by content or file name, filtering by folder if I want -- is not only simple, but incredibly fast. Searching for emails -- by keywords, names, from/to/subject line or entire email content -- is equally fast and easy and done using exactly the same interface.

And there are the special filters in Finder to find things created/changed Today or Yesterday or Past Week. The amount of time that feature alone has saved me is incredible.

Yes, Windows and Outlook had many search capabilities but in all the years I was on Windows, I never felt like I mastered searching there and it was very slow. With Mac, there's a little Search box that works the same for searching email, files/folders, PDFs and ... a simple, ubiquitous, consistent interface regardless of what I'm looking for -- reminds me another system I like to use . . .

2) Ease of application installation and removal. Drag a folder into the Applications folder. Drag an Application from the Applications folder to the Trash. No registry. Need I say more?

3) Runs Windows way better than my PC ever did. Yes, I still run a few Windows apps -- the primary one being RD Power (I've jumped on the "I want RDP running on a Mac" bandwagon with both feet). Strangely, my Windows virtual machine has less memory than my PC had but it seems to be far more stable and way faster on my Mac using the Parallels virtual machine.

4) Touch pad gestures. I'm not a fan of touch pads. If there is one thing I have to confess I still miss from my ThinkPad, it's my TrackPoint -- you know, that little red button in the middle of the keyboard. A lot of people don't like them, but I was very attached to my TrackPoint. If my Mac had one, I'd be even happier.

But what my Macbook lacks in TrackPoint, it more than makes up for with touch pad gestures -- most importantly the two-finger scroll. Reading a document, PDF, a web page or whatever, I can just rest my fingers on the touch pad and gradually scroll up or down -- or just as easily "speed slide" back and forth through a document looking for the part I want. Think scrolling mouse, but (to me, anyway) infinitely easier to control. There are many other gestures that sound way cooler still, but so far I haven't found that much use for them yet -- like using two fingers to rotate a picture or PDF document and the two-finger pinch open and close (if you have an iPhone or iPod touch, you've no doubt mastered this one). These are great, but on my Mac I personally just haven't found that much occasion to use them.

But then, there's the screen zoom. I am (well) over 50 after all. Zooming comes in very handy when my old eyesight meets a web page or document created by some clever kid! Using the touch pad, it's very easy to control the degree of zooming.

5) It's simple. I'll have to confess that I still have to ask for help from my resident Mac expert (Thanks, Jon!) from time to time, but much of the time, it turns out that I was having trouble doing something because I was trying to make it harder than it really was. Unlearning old Windows ways of working takes a while.

It's just easier than Windows. It's very hard to explain exactly what I mean by "easier." Almost any specific example I can think of, you could say "but my x application on Windows does that too." True, but on Mac, it's mostly about consistency of interface, even for Mac applications not written by Apple. It's not 100 percent consistent, but so much more so than I was accustomed to on my PC. And things are integrated -- between applications and with the operating system. I actually hardly ever feel like I'm interacting with the OS -- it's just there in the background making it easier for me to do my job.

The most amazing and endearing thing about my Mac? It is to my desktop/laptop computing world what IBM i is to my host server world. Simple, integrated, consistent, elegant, unassuming.

Mac -- it's the IBM i of the workstation world!

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?