• Finding the Last Transaction for a Customer

  January 31, 2007 Hey, Howard

  I’ve been studying your book, SQL at Work. I was hoping you would answer a question for me. We have a historical transaction file sorted on a descending date field. I’d like to only select the latest record for a specific ID with the most current date. I don’t know the value of this date and I don’t want it to return any other records. Is this possible? Thanks! –Mary This kind of query can be done using a subquery or a join operation. Let’s create a sample table, load it with data, and try a few queries that

  …

  Read more

• Sorting Arrays and Subfiles with a User Index

  January 31, 2007 Hey, Ted

  I’d like to suggest another method Jennifer can use for sorting arrays. Why not use a user index? I use user indexes so the user may sort subfiles umpteen different ways. Using a user index eliminates the need to be concerned with size (unless you have more than 4 GB of data). Maybe size would not be an issue in Jennifer’s daily run shipping application. –Drew at BIC Graphic USA Note: the code from this article is available for download Drew sent me some illustrative code and a short write-up about the three API’s he uses–Create a User Index

  …

  Read more

• When Fix Central Won’t Let You Download PTFs

  January 31, 2007 Hey, Joe

  When I went to IBM‘s Fix Central Web site to download some i5 PTFs, the site wouldn’t let me complete my order after I entered my contact and shipping information. Fix Central wouldn’t let me advance past the contact screen, and I couldn’t download the PTFs I wanted. Is there something wrong with my browser settings or do I need to adjust something else on my system? –Bob This same problem recently happened to me and I discovered that this issue occurred because I needed to update my i5/OS V5R3 partition’s service contact information. For service-related issues, IBM stores

  …

  Read more

• Using the SQL SET OPTION Statement

  January 24, 2007 Michael Sansoterra

  SQL’s SET OPTION statement is a powerful way to control the parameters of the DB2 execution environment in which an SQL program runs. Using it properly will allow SQL code to execute consistently between different environments–including those thorny differences that often arise between green screen and client/server environments. Read on to find out why this statement is essential to embedded SQL programs and persistent stored modules (SQL based triggers, procedures and functions). First of all, SET OPTION is a statement that is evaluated at “compile time.” It never actually gets executed. Therefore SET OPTION can only be specified once in

  …

  Read more

• Legible PATH and CLASSPATH

  January 24, 2007 Ted Holt

  Dear Professional: One of the things I don’t like about the Integrated File System (IFS) is its directory-based structure of untyped files. I consider the library system of strongly typed objects a much more robust architecture. Nevertheless, the IFS is reality and I have to deal with it. Here’s a short and simple tip for reading those ugly, almost indecipherable PATH and CLASSPATH variables we have to put up with. The PATH and CLASSPATH variables contain lists of directories, separated with colon characters. They function like library lists, in that the system searches the lists when looking for certain things.

  …

  Read more

• Admin Alert: Updating Your System i5 for Daylight Saving Time Changes

  January 24, 2007 Joe Hertvik

  In the Energy Policy Act of 2005, Congress changed Daylight Saving Time (DST) in the United States so that starting this year (2007) DST begins three weeks earlier than it did last year, on the second Sunday of March. Conversely, 2007 will also see Daylight Saving Time end one week later than last year, on the first Sunday in November. This week, I’ll talk about how this change affects i5 shops. The new DST starting and ending dates are causing some minor concerns for system i5 shops because, like the rest of the computing world, i5/OS needs to be patched

  …

  Read more

• Using APIs to Send Impromptu Messages, Take Two

  January 17, 2007 Hey, Ted

  The code for this story is available for download here. In your article, Using APIs to Send Impromptu Messages, you show how to use a message subfile to display impromptu error messages from an RPG program. The logic works great if you call the message-handling APIs from the main logic. However, when I move the API calls into subprocedures, your technique breaks down. What do I need to do? –Chris The problem is that you are now sending messages from a different call stack entry. (I hesitate to use the terms “higher” and “lower” when talking about call

  …

  Read more

• Gotcha Lurking in Datalink File Manager for DB2/400

  January 17, 2007 Hey, Ted

  I’ve grown quite fond of the convenience of the DB2 SQL support in QShell, and especially the ease with which it can be invoked within a CL program, by simply calling the underlying Datalink File Manager DB2 program, QZDFMDB2. Having said that, I must also admit to getting burned by a “gotcha” lurking in this otherwise handy tool. Although I’ve never experienced a problem when providing QZDFMDB2 with an SQL statement in the form of a constant, I did walk straight into a real head-scratcher recently when building the parameter string for this program from variable data. I kept getting

  …

  Read more

• Admin Alert: Ending Subsystems Properly

  January 17, 2007 Joe Hertvik

  It’s unavoidable that system i5 administrators occasionally have to end a subsystem by using the End Subsystem command (ENDSBS). Killing jobs can wreak havoc with system processing because jobs may not have time to cleanly end before the subsystem terminates, which can result in partially updated data and potentially damaged objects. Fortunately, there are some simple things you can do to cushion but not totally soften the blow as your jobs end. Why Interactive Subsystems Must End Killing a subsystem is a fairly common event. It’s necessary in several situations, including: If there’s a power outage and your machine’s UPS

  …

  Read more

• Exporting DB2/400 Dates to Excel

  January 10, 2007 Jeff Haddix

  In order for Microsoft‘s Excel spreadsheet to recognize the set of values for a column as dates, those values must be stored as a number of days. For example, to export the date December 26, 2006, to an Excel spreadsheet, you should export the number 37,066, not a date value like 12/26/2006 or 2006-12-26. However, due to an acknowledged defect in Excel, be aware of the following. Even though Excel claims that 01/01/1900 equates to Day 1, for most purposes it is necessary to actually use 12/31/1899 as Day 1. Otherwise, a one-day discrepancy will occur when converting

  …

  Read more

Previous Articles Next Articles