You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Exploring Recent SQL Discoveries

DB2 for i Directions prompts exploration into date-conversion tables and SQL performance

DB2 for i Directions prompts exploration into date-conversion tables and SQL performance

In a recent blog post, we mentioned two interesting notions we picked up at the recent DB2 for i Directions event. We promised to explore these in more detail and report back, so here we go.

Date-Conversion Table

First, the topic that intrigued us most—the idea of creating and using a date-conversion table. We first heard about this technique from Gene Cobb from IBM Rochester who referenced it during his DB2 Web Query sessions. It was featured in the Getting Started With DB2 Web Query for i Redbooks publication. The date-conversion table was used to make querying data in formats not directly supported by SQL faster and easier. We thought it could equally make RPG applications easier to code. While we only recently heard about this technique, we’ve apparently been sleeping because it’s not a new idea for many IBM i shops—as comments on our blog post and a recent discussion on pointed out.

For those of you unfamiliar with the concept of date-conversion (or calendar) tables, here’s the idea. Simply create a table (aka file) that consists of rows containing commonly used formats and related information for every date within the range of dates that makes sense for your applications. In the Redbooks publication, they created rows (aka records) for every date from 1900 to 2030. In each row, the date value and its constituent parts appear in various formats and combinations, for example, MDY, YMD, DMY, CYMD, Julian, Julian day only, JDE Julian, YY, YYYY, MM, DD, etc. Furthermore, other application-relevant information about each date is also included in each row, such as the day of the week (both numeric form and English text form, e.g., “Monday”), whether it is a weekend day, what quarter (fiscal and/or calendar) the date is in, etc.

Obviously each shop will have its own variant of this table depending on its application needs. For example, whether a date is a holiday will likely be important for many applications, but, of course, the specific holidays to include will vary from company to company. For some companies, it’ll be important to include the season or even some very specific dates, such as Super Bowl Sunday or the dates the local school year begins or ends.

The Redbooks publication also has an appendix with some SQL code to create a sample date-conversion table. We’re using a variant of that table in our examples here, which includes more date formats and more “RPG friendly” non-null capable columns (aka fields). The code we used to create and populate the table is pure SQL. Obviously, you could populate the table with RPG or use/modify the SQL statements from the Redbooks publication to populate your own table. (Note: The Redbooks SQL logic has a bug in populating the Weekend flag; if you use that code as a base, make sure you modify that part.)

What kinds of things might you do with such a table? You could use it as an easier/faster way to convert date information for displaying/reporting or for comparisons. Think about, for example, how you might locate the orders in a date range, but the date is in *MDY format? You could create a logical file joining the orders table with the date-conversion table on the *MDY format column and key the logical on a more appropriate version of the date, such as either a date data type or a numeric YYYYMMDD format. Using that logical file, you can now easily use CHAIN or SETLL and READ in RPG to process the date range. Alternatively, you could use embedded SQL in RPG to query the tables joined on the *MDY column and perhaps include other selection and ordering logic in the SQL to simplify your RPG logic for processing the data. If you use embedded SQL, it would be a good idea to create an SQL view joining the two tables to simplify your future SQL logic as well.

The table itself—even without joining it to other tables—can also prove useful for your applications. In RPG, we can easily use %Diff to calculate the number of days between two dates or use %Days to calculate a new date x days in the future as in estimated ship date for an order, for example. However, those are calendar days—not business days. With a date-conversion table, you could use an embedded SQL statement to more accurately calculate the ship date, taking weekends and holidays into account. If your logic calculates the order processing to take n days (represented by :n using embedded SQL), the following statement (one of many SQL options) could be used:

SELECT DC_Date From Date_Conv D1
WHERE D1.dc_Weekend = 'N' and
(Select Count(*) from Date_Conv D2
Where D2.dc_Date >= Current Date
And D2.dc_Date <= D1.dc_Date
And D2.dc_Weekend = 'N') = :n

If you’re not into using embedded SQL (or if SubSelects like the one shown make your head hurt), you could create an SQL index or LF keyed on the DC_Date column, then SETLL into the date-conversion table at the current date and read/count rows until you reach n business days, not counting any rows marked as weekend or holiday. Of course, you may not want to begin counting with today’s date—perhaps tomorrow or find the next business day after today. Another good use for the date-conversion table!

If you do this kind of “business day” logic frequently, a better solution still would be to populate a sequential “business day of year” column in the table or alternatively even a business day from the beginning date of the table. That would dramatically improve performance of finding that ship date, whether using SQL or native RPG I/O.

The idea of a date-conversion table is a simple solution to many issues. Of course, in some ways, it seems to fly in the face of good database design. What we mean is that there’s nothing in this table that couldn’t be derived from other data. It therefore technically duplicates data—a definite no-no in database design. However, it provides a simple and fast way to accomplish things that would otherwise require a lot of RPG and/or SQL code. And reducing and simplifying code seems a great tradeoff for database design purity!

We’ve only scratched the surface of ways to use a table like this. If we come up with more ideas, we’ll write a follow-on article or include our findings in our blog. If you have ideas or experience with this to offer, please leave a comment on the blog post.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters