Date Check Y2000 Brute Force Utility

Aside from the examination of coding practices for Y2000 issues, we had to examine actual database contents. For instance:

How many price lists are going to expire on 12/31/99?
What is going to happen September 9, 1999 when all the sales orders that Customer Service entered with a Due Date of 9/9/99 (because the customer was on credit hold, something wasn't ready yet, etc, etc) get released?
How many part BOM's are effective through 12/31/99?
How many other goofy dates have users entered that we don't know about?
If I set the -yy parameter to 1920, will the Payroll Application puke on the birthday of someone that retired ten years ago?

This involves many different application systems. We concluded that we needed to:

Make a brute force evaluation to see how bad it was.
Correct the existing data.
Educate the users to enter correct data.
Make sure the users are not slipping in bogus dates again.
We felt this should be done NOW, while we could be reasonable sure that aside from Fixed Assets dates, any date field with a value in next year is certainly suspect. Rather than write a whole ton of code by hand, we elected to write a Progress utility to read the dictionary schema and write the inquiries we wanted. It looks like this:

+--------------------------- Date Check Parameters ---------------------------+
|                                                                             |
| Copyright 1998 Neal Rhodes/MNOP Ltd - All Right Reserved                    |
|                                                                             |
| This program will generate a program for each file in the primary connected |
| datebase which will examine every date field.  For every date field beyond  |
| the cutoff date, it will write a line to the file "bumdates.log" with       |
| the file, the field, the contents of the field, the RECID, and the contents |
| of the fields which make up the primary index for that record.              |
|                                                                             |
|                                                                             |
|Starting DB File: pal_mstr                                                   |
|Ending DB File: pj_mstr                                                      |
|Starting Cutoff Date: 1/1/1920                                               |
|Ending Cutoff Date: 12/31/98                                                 |
+-----------------------------------------------------------------------------+

It then goes through the dictionary and generates a program for each file, which (simplified) looks something like this:

for each pc_mstr                          no-lock:
      if pc_expire > 12/31/98 then do:
         put stream bum-dates " pc_mstr pc_expire "
                   pc_expire format "99/99/9999" " "
                   RECID(pc_mstr) format "ZZZZZZZZZZZZ9" " "
                   pc_list "|"
                   pc_curr "|"
                   pc_prod_line "|"
                   pc_part "|"
                   pc_um "|"
                   pc_start format "99/99/9999" "|"
                 skip.
              bumCnt = bumCnt + 1.
              end.
      end.

It runs each program, producing a running account like this, along with a summary file.

+------------- Summary --------------+ +----------------------------------+
|File-Name       Recs Read  Bum Count| |Elapsed Secs  Recs Read  Reads/Sec|
|------------ ------------ ----------| |------------ ---------- ----------|
|pal_mstr     no date flds           | |        4.00      1,900     475.00|
|pcd_det                 0          0| |        5.00      2,000     400.00|
|pck_det                 0          0| |        5.00      2,100     420.00|
|pc_mstr            15,080     14,955| |        5.00      2,200     440.00|
|pfc_det                 0          0| |        5.00      2,300     460.00|
|pfd_det                 0          0| |        6.00      2,400     400.00|
|pgc_ctrl                1          0| |        7.00      2,500     357.14|
|pgh_hist                0          0| |        7.00      2,600     371.43|
|pgm_det                 7          0| |        7.00      2,700     385.71|
|pj_mstr                 1          0| |        8.00      2,800     350.00|
|pk_det                 12         12| |                                  |
|pld_det                12          0| |                                  |
|                                    | |                                  |
|                                    | |                                  |
|                                    | |                                  |
|                                    | |                                  |
|                                    | |                                  |
|                                    | |                                  |
+------------------------------------+ +----------------------------------+

The summary file contains enough information to locate the offending records, either through your application, or by using the RECID.

   File    Field    Value     RECID   Contents of Primary Index Fields
 ======= ========= ========  ======= ====================================
 pc_mstr pc_expire 01/01/1999  6027046 1989 |usd|    |R2342Z  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027047 1989 |usd|    |R2436C  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027048 1989 |usd|    |R2436Z  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027072 1989 |usd|    |R2442C  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027073 1989 |usd|    |R2442Z  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027074 1989 |usd|    |R2536C  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027075 1989 |usd|    |R2536Z  |  |01/01/1989|
 pc_mstr pc_expire 01/01/1999  6027076 1989 |usd|    |R2542C  |  |01/01/1989|
 pc_mstr pc_start  01/01/1901  6021318 1989 |usd|    |10002508|  |01/01/1901|

Thus we can cut this file up and:

Hand some pages to users to correct by hand
Do brute force corrections like: for each pc_mstr: pc_expire = 12/31/19.
Read in portions into another Progress program to correct.
This seems to be a pretty useful tool. Writing Progress programs to write Progress programs is a bit tedious and it took us several hours to knock this out. We are providing it free to non-profit (501-C) organizations. For the rest of us, the cost is $150US for source code. For US and Canadian corporations fax a PO, and indicate an Email address.

Michelle, Neal or Pat Rhodes, neal@mnopltd.com
4737 Habersham Ridge
Lilburn, GA 30247
(770) 972-5430

Progress is a registered trademark of Progress Software Corporation. Powered by Progress is a trademark of Progress Software Corporation. All other registered trademarks and trademarks are the property of their respective manufacturers. File names above are the property of QAD and are only used as examples.