AS/400 Links Partner with Rochester Institute Contact Us
Training Catalog Free Downloads Privacy / Usage


FREE AS/400, iSeries, and System i / iSeries Downloads
HOME >> FREE AS/400, iSeries, and System i / iSeries Downloads >> Sizing up a downsizing task



Sizing up a downsizing task
Author: Christopher F. Burns, Sr.


Imagine you have an ERP database with many different companies loaded: AR files, GL files and inventory files.  They coexist nicely because your software was designed to be company number driven.  Then one day, your CEO announces - "We're closing company #3 because of poor performance and market pressures".   Days later, the CFO approaches you and says - "We need to get all company #3 data out of the database immediately after we close this fiscal year.  But we should keep a copy of it just in case we're audited".

Consider that for a moment.  Data spread out over hundreds, perhaps thousands of files, each with a different record format, needs to be removed from the system, but kept handy at the same time.  Sounds like a whole bunch of CPYF's, RPG programs and/or SQL's that will keep us busy for the next three months, doesn't it?

Not quite.  If your ERP database uses a prudent field naming standard, chances are all or most company number fields will contain a common series of characters.  For example, the standard might be to name all such fields XXCONO, where XX is a prefix that identifies the file in question.  Having said that, a good place to start might be to identify all files that have a company number field.  One approach would be to run DSPFFD over your entire library, and use the outfile in conjunction with a Query or SQL to seek out field names containing "CONO", for example.  But there's an even better way.

A little know logical file in the QSYS2 library known as SYSCOLUMNS contains the names of every  column (field) in every table (file) for the entire system.  A second logical called SYSTABLES contains a record for every database file on the system, including an indicator of file type (physical or logical).  By using these two in tandem, we can easily identify every physical file for a library which contains a company number field, as well as the name of that field.

 SELECT C.TABLE_NAME, C.COLUMN_NAME                               
  FROM SYSCOLUMNS C JOIN SYSTABLES T ON C.SYS_TNAME = T.TABLE_NAME
WHERE C.SYS_DNAME = 'APLUS6FGI'                                  
  AND SUBSTR(C.SYS_CNAME,3,4) = 'CONO'                           
  AND T.TABLE_TYPE = 'P'                                         
ORDER BY 1                               

Figure 1

This SQL would yield a result set with every company number field name, by file, within library APLUS6FGI.  So what next?  Dump it into a file?  Perhaps.  Use it to drive a C/L program with many CRTDUPOBJ and CPYF commands?  Maybe.  But there is a better way.

In SQL, a little known AS clause on the CREATE TABLE statement allows us to construct a new physical file with the same record layout as another, AND perform a SELECT statement to extract the data we want load to that new file.  In essence, it performs the equivalent of both a CRTDUPOBJ and CPYF in one step.

 CREATE TABLE ARCCMP03/CUSMS                          
  AS (SELECT * FROM APLUS6FGI/CUSMS WHERE CMCONO = 3)
  WITH DATA                                          

Figure 2

This SQL creates a duplicate CUSMS file in library ARCCMP03, and by virtue of the AS and WITH DATA clauses, loads the data yielded by the sub-select, which in this case is all company 3 customers.
However, all the identifiers in this statement (field, file, library) are hard coded, so the next step is to construct a means of performing the CREATE TABLE statement in Figure 2 dynamically in a loop, using the result set from Figure 1.

One very effective way is with SQL embedded inside RPG.  Figure 3 illustrates a simple RPG program which accepts three parameters - company number to archive, the name of the live data library, and the name of the archive data library.  In this example, the company number was passed as alpha, even though it is numeric in the database. 

To make the process a little simpler, and somewhat foolproof, we've chosen to wrap a CL program around this process to do some of the housekeeping.  A command definition ARCCMPDTA has also been added.  The source code for these two objects is in Figures 4 and 5, respectively.

The CL program creates a library called ARCCMPXX, where XX is the company number, where the new file objects will be created.  Note how the process submits itself to batch, since this could be a long running operation.  When the ARCCMPDTA command is run, and the user provides a company number and live data library, the end product will be a library full of physical files containing only records for the specified company.  This library can easily be moved off to tape for permanent storage.

One issue we have not yet addressed is how to get the company 3 records out of the live library.  I purposely did not build that into the primary RPG program so that the user has an opportunity to verify that all desired data has been archived.  The process can be rerun if necessary without consequence.

To facilitate removal of the company 3 records, simply clone the RPG program, replacing the assignment of variable SQLSTM with an expression like that in Figure 6.  The archive data library parameter would not be required by this clone.

To merge the archived records back into the live data library, create another clone and replace the assignment of variable SQLSTM with an expression like that in Figure 7.  All three parameters are used by this program.

Or, instead of cloning the RPG, you might just choose to add a fourth parameter to govern which action is taken on each pass through the SQL result set.

 

      *  Program ARCCMPDTAR                                                                       
      *                                                                                           
      *  Replicates physical files from a specified live data library to a specified archive data 
      *  library, and populates the files with records associated with a specified company number.
      *  Only physical files are copied.                                                          

     h  dftactgrp(*no) actgrp('ARCCMPDTA')

     d  FilesWithCmp   ds                                                       files with comp num
     d    filnam                     10                                         file name
     d    fldnam                     10                                         field name

     d sqlstm          s            256    varying                              sql statement
     d sqlcodsv        s                   like(sqlcod)                         sql return code

     c     *entry        plist                                                  entry parameters
     c                   parm                    cmparc            2            company to archive
     c                   parm                    livlib           10            live library
     c                   parm                    arclib           10            archive library

     c/exec sql
     c+ declare c1 cursor for
     c+ select c.table_name, c.column_name
     c+   from syscolumns c join systables t on c.sys_tname = t.table_name
     c+ where c.sys_dname = :livlib and substr(c.sys_cname,3,4) = 'CONO'
     c+   and t.table_type = 'P'
     c+ order by 1
     c/end-exec

     c/exec sql
     c+ open c1
     c/end-exec

      /free
       dou sqlcodsv = 100;                                                   // until no more rows
      /end-free

     c/exec sql
     c+ fetch from c1 into :FilesWithCmp
     c/end-exec

      /free

        sqlcodsv = sqlcod;

        if sqlcodsv <> 100;

        sqlstm = 'create table ' + %trim(arclib) + '/' + %trim(filnam) + ' as ('
               + 'select * from ' + %trim(livlib) + '/' + %trim(filnam) + ' '
               + 'where ' + %trim(fldnam) + ' = ' + cmparc + ') with data';

      /end-free

     c/exec sql
     c+ prepare s1 from :sqlstm
     c/end-exec

     c/exec sql
     c+ execute s1
     c/end-exec

      /free

       endif;
       enddo;

       *inlr = *on;

      /end-free

     c/exec sql
     c+ close c1
     c/end-exec

Figure 3

/*  ARCCMPDTA - ARCHIVE COMPANY DATA                                          */
/*                                                                            */
/*  THIS PROGRAM SIPHONS OFF ANY DATA SPECIFIC TO A COMPANY NUMBER AND PUTS   */
/*  IT IN ITS OWN LIBRARY.  ONLY PHYSICAL FILES ARE COPIED.                   */

             PGM        PARM(&CMPNUM &LIVLIB)

             DCL        VAR(&CMPNUM) TYPE(*DEC)  LEN(15 5)
             DCL        VAR(&CMPINT) TYPE(*DEC)  LEN(02 0)
             DCL        VAR(&LIVLIB) TYPE(*CHAR) LEN(10)
             DCL        VAR(&CMPCHR) TYPE(*CHAR) LEN(2)
             DCL        VAR(&ARCLIB) TYPE(*CHAR) LEN(10)
             DCL        VAR(&JOBTYP) TYPE(*CHAR) LEN(1)
             DCL        VAR(&MSGTXT) TYPE(*CHAR) LEN(256)

             RTVJOBA    TYPE(&JOBTYP)

/*  INTERACTIVE PORTION */

             IF         COND(&JOBTYP *EQ '1') THEN(DO)

             CHGVAR     VAR(&CMPINT) VALUE(&CMPNUM)
             CHGVAR     VAR(&CMPCHR) VALUE(&CMPINT)
             CHGVAR     VAR(&ARCLIB) VALUE('ARCCMP' *TCAT &CMPCHR)

             CHGVAR     VAR(&MSGTXT) VALUE('Company' *BCAT &CMPCHR +
                          *BCAT 'Data Archival Submitted.')

             SBMJOB     CMD(CALL PGM(ARCCMPDTA) PARM(&CMPNUM +
                          &LIVLIB)) JOB(&ARCLIB) LOG(4 0 *SECLVL) +
                          LOGCLPGM(*YES)

             SNDPGMMSG  MSG(&MSGTXT)

             GOTO       CMDLBL(EXIT)

             ENDDO

/*  BATCH PORTION */

             CHGVAR     VAR(&CMPINT) VALUE(&CMPNUM)
             CHGVAR     VAR(&CMPCHR) VALUE(&CMPINT)
             CHGVAR     VAR(&ARCLIB) VALUE('ARCCMP' *TCAT &CMPCHR)

             CRTLIB     LIB(&ARCLIB)

                        MONMSG     MSGID(CPF2111) EXEC(CLRLIB LIB(&ARCLIB))

             CALL       PGM(ARCCMPDTAR) PARM(&CMPCHR &LIVLIB &ARCLIB)

 EXIT:       ENDPGM

            Figure 4

 

 

/*  ARCCMPDTA - ARCHIVE COMPANY DATA                                          */
/*                                                                            */
/*  THIS PROGRAM SIPHONS OFF ANY DATA SPECIFIC TO A COMPANY NUMBER AND PUTS   */
/*  IT IN ITS OWN LIBRARY.  ONLY PHYSICAL FILES ARE COPIED.                   */

             CMD        PROMPT('Archive Company Data')

             PARM       KWD(CMPNUM) TYPE(*DEC) LEN(15 5) RANGE(1 99) +
                          PROMPT('Company Number To Archive')

             PARM       KWD(LIVLIB) TYPE(*CHAR) LEN(10) MIN(1) +
                          PROMPT('Live Data Library')

Figure 5

        sqlstm = 'delete from ' + %trim(livlib) + '/' + %trim(filnam) + ' '
               + 'where ' + %trim(fldnam) + ' = ' + cmparc;                

Figure 6

        sqlstm = 'insert into ' + %trim(livlib) + '/' + %trim(filnam) + ' '  
               + 'select * from ' + %trim(arclib) + '/' + %trim(filnam) + ' '
               + 'where ' + %trim(fldnam) + ' = ' + cmparc;                  

Figure 7

Should your company take downsizing or consolidation steps in the future, this may prove to be a valuable tool to help your database keep up with the changes.  Hopefully, your position with the company will still be there so that you can reap the benefits of this process.

If you are interested in learning more about advanced RPG and SQL topics visit www.gemko.com.