Refacing Your Database, Part 3

In the preceding two articles, we saw how to extract, analyze and correct table and column definitions. In this, the final article on refacing your database, we look at some more options for re-representing data and, finally, generating a script to create the required views.

Handling Numeric Date Fields

One of the issues we need to handle is the representation of dates in the SASALHST table. Figure 1 shows how the purchase date is stored as a numeric field, the order date is stored as a year, month and day and the delivery date is stored as a century, year, month and day. And there is no guarantee that these columns will always contain a valid date.

Figure 1: Contends of the SASALHST Table.

There are two ways of handling these date problems:

1. Write a user-defined function to convert the numeric fields to a proper date field.

2. Have a “date translation” table that contains a row for every date, the row containing the date in all required formats. We can join to this table to get the required date value.

Let’s start with the user-defined function solution. The SQLFACE library contains the overloaded user-defined function SETDATEFROMNUM, which returns a proper ISO date for numeric values passed. We could use it in a select statement as follows:

select setDateFromNum(SHPURDAT) as purchase_date,
       setDateFromNum(SHORYY, SHORMM, SHORDD) as order_date,
       setDateFromNum(SHDLC, SHDLYY, SHDLMM, SHDLDD) as delivery_date
from sasalhst;

How do we reflect this in our translation tables?

The first step is to identify the three columns that have the SETDATEFROMNUM user defined function applied, as follows:

update column_translation
       set use_function_name = 'SETDATEFROMNUM' 
       where current_column in ('SHPURDAT', 'SHORYY', 'SHDLC');

Then we identify the extra parameters required for the Order Date and the Delivery Date, as follows:

update column_translation
       set extra_parameters = ', SHORMM, SHORDD',
           long_column = 'ORDER_DATE'
       where current_column = 'SHORYY';

update column_translation
       set extra_parameters = ', SHDLYY, SHDLMM, SHDLDD', 
           long_column = 'DELIVERY_DATE'
       where current_column = 'SHDLC'; 

Finally, we need to ensure that those fields that are included as extra parameters are not also included in the new view. We do this by changing their long name to *NONE, as follows:

update column_translation
       set long_column = '*NONE' 
       where current_column in ('SHORMM', 'SHORDD',
                                'SHDLYY', 'SHDLMM', 
                                'SHDLDD');

Dropping Columns

As we just saw, setting the LONG_COLUMN to *NONE means that the column will not be included in the new view. This is useful if you have superfluous columns (e.g., unused columns or columns used for internal controls, such as record locking) that you do not need to carry over.

Generate A Script

We use the command GENSCRIPT, as shown in Figure 2 below, to generate an SQL script that can later be run with the Run SQL Statement (RUNSQLSTM) command. The parameters are:

• The name of the file for which we want to create a script. This can be an individual file or *ALL for all files in the translation tables.
• The name of the source file and member to contain the generated script.
• The system name prefix to be used on short table names when the long name of the view exceeds 10 characters. For example, for SASALHST, the name of the view will be SALES_HISTORY and the system name will be VSASALHST.
• Whether the create statement should be CREATE OR REPLACE VIEW or simply CREATE VIEW.

Figure 2: Prompting the GENSCRIPT command.

The GENSCRIPT command calls the RPG program GENSCRIPTR. This program processes each requested table and creates the required CREATE VIEW statement. We are not going to go through the gruesome detail of the code, but feel free to peruse it at your leisure.

The following piece of code shows the generated script for the SASALHST table.

CREATE OR REPLACE VIEW SALES_HISTORY
 FOR SYSTEM NAME VSASALHST (
       CUSTOMER_ID FOR COLUMN SHCUSNO,
       PRODUCT_CODE FOR COLUMN SHPRODCD,
       QUANTITY_VOLUME FOR COLUMN SHQTY,
       AMOUNT FOR COLUMN SHAMOUNT,
       PURCHASE_DATE FOR COLUMN SHPURDAT,
       ORDER_DATE FOR COLUMN SHORYY,
       DELIVERY_DATE FOR COLUMN SHDLC) AS
SELECT SHCUSNO,
       SHPRODCD,
       SHQTY,
       SHAMOUNT,
       SETDATEFROMNUM(SHPURDAT),
       SETDATEFROMNUM(SHORYY, SHORMM, SHORDD),
       SETDATEFROMNUM(SHDLC, SHDLYY, SHDLMM, SHDLDD)
 FROM SASALHST ;
LABEL ON TABLE SALES_HISTORY IS
'Sales/-History Table';

With the SQLFACE library and the library containing the tables (SQLFACETST) in our library list, we can now run this script using the RUNSQLSTM command, as follows:

RUNSQLSTM SRCFILE(SQLFACE/TESTSQL)
          SRCMBR(TEST1)
          COMMIT(*NONE)
          DFTRDBCOL(SQLFACETST)

Note that the name of the library in which the views are to be created must be specified on the Default Collection (DFTRDBCOL) parameter.

Of course, we could make further changes to the generated script before running it. For example, we might use a CASE statement to provide a more meaningful representation of a code. Or, instead of using a user-defined function to convert numeric fields to date fields, we might use a date translation table instead.

Handling Numeric Date Fields Again

The table TRANSDATES is provided in the SQLFACE library along with the stored procedure FILL_DATES, which can be used to populate the table. Figure 3 gives you an idea of what is in TRANSDATES.

Figure 3: Contents of the TRANSDATES table.

When calling the FILL_DATES stored procedure you provide a starting date, the number of years for which dates are to be added and whether or not rows should be added for a 0 date value (represents the date 0001-01-01) and a date of all 9s (represents the date 9999-12-31)

call fill_dates('2013-01-01', 4, 'Y');

To make use of the TRANSDATES table, as opposed to the SETDATEFROMNUM user defined function, we would change the generated script as shown in the next piece of code. Outer joins are made to TRANSDATES for each of the different dates. The date retrieved from TRANSDATES is what is presented by the view. The COALESCE function is required in case there is an invalid “date” that does not have a match.

CREATE OR REPLACE VIEW SALES_HISTORY
 FOR SYSTEM NAME VSASALHST (
       CUSTOMER_ID FOR COLUMN SHCUSNO,
       PRODUCT_CODE FOR COLUMN SHPRODCD,
       QUANTITY_VOLUME FOR COLUMN SHQTY,
       AMOUNT FOR COLUMN SHAMOUNT,
       PURCHASE_DATE FOR COLUMN SHPURDAT,
       ORDER_DATE FOR COLUMN SHORYY,
       DELIVERY_DATE FOR COLUMN SHDLC) AS
SELECT SHCUSNO,
       SHPRODCD,
       SHQTY,
       SHAMOUNT,
       COALESCE(B.ISODATE, '9000-01-01'),
       COALESCE(C.ISODATE, '9000-01-01'),
       COALESCE(D.ISODATE, '9000-01-01')
  FROM SASALHST A
       LEFT OUTER JOIN SQLFACE/TRANSDATES B ON
                  SHPURDAT = B.NUMDATE 
       LEFT OUTER JOIN SQLFACE/TRANSDATES C ON
                  (SHORYY, SHORMM, SHORDD) =
                  (C.YYYY, C.MM, C.DD)
       LEFT OUTER JOIN SQLFACE/TRANSDATES D ON
                  (SHDLC, SHDLYY, SHDLMM, SHDLDD) =
                  (D.CENTURY, D.YY, D.MM, D.DD) ;
LABEL ON TABLE SALES_HISTORY IS
'Sales/-History Table';

The End Result

We have now achieved our aim of refacing the representation of our database from the naming used in Figure 4 to that used in Figure 5.

Figure 4: Results of SELECT on traditional physical files.

Figure 5: Results of SELECT on DDL named views.

As I said at the outset of the first article, I think this is a great project to give to one or more interns. So now might be a good time to reach out to a local college or university.

And remember, if you come up with any enhancements or modifications to this process, please let me know!

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

RELATED STORIES

Refacing Your Database, Part 2

Refacing Your Database, Part 1