6. Database Tailoring and Retrofitting Techniques › 6.3 Retrofitting Guidelines › 6.3.2 Retrofitting Database Files › 6.3.2.3 Value Change With Order and Granularity Changes

6.3.2.3 Value Change With Order and Granularity Changes

Assume that there is NOT a one-to-one mapping of the old key
data element value to the new value.  This situation may
occur under several conditions:

    -  A key data element is being added to the file.

    -  A key data element is being deleted from the file.

    -  The contents of an existing key data element must be
       changed in other than a one-to-one mapping of values.

    -  The length of an existing key data element must be
       changed, and the added or deleted material may change
       file granularity.

For example, here is a sample table showing old and new key
data element values and file observation order:

    Old     Old     New     New
    Value   Order   Value   Order
    -----   -----   -----   -----
    AA01    1       AA      1
    AA02    2       AA      1
    BA01    3       BA      2

Not only must the data values be corrected, but the file must
be re-sorted AND resummarized.  The template for such a
generalized recovery job becomes:

    %LET   R_BY    = %fffSEQ(TS=sssss) ;
    %LET   R_BREAK = %fffSEQ(TS=sssss,OP=BREAK) ;
    DATA TEMP (KEEP= %fffKEEP(TS=sssss) );
      SET  &diiit..iiifffnn;
      (correction logic)
    RUN;
    PROC SORT DATA=TEMP;
      BY &R_BY ;
    RUN;
    DATA &diiit..iiifffnn (%fffFILE(TS=sssss,OP=FILEOPTS));
      %fffLEN(TS=sssss,TYPE=BOTH);
      %fffFMT(TS=sssss);
      SET  TEMP;
      %fffSUM(SUMBY=&R_BY , SUMBREAK=&R_BREAK );
    RUN;

where fff   is the file identifier
      sssss is the timespan name (DETAIL, DAYS, etc.)
      d     is the database unit identifier
      nn    is the file cycle number
      iii   is the information area identifier
      t     is the timespan identifier (X, D, etc.)

Putting the macro treatment method into practice can be done
by a method similar to that used in the previous example.
This example illustrates the method applied to the example
NPA component.

    //xxx JOB (your job card)
    //STEP1 EXEC MICSDBS      (assume unit identifier "S")
    //SYSIN DD *
    %INCLUDE USOURCE($BASMSTR);
    %MACRO RETFIT(DDN,III,FFF,CYC,TSPAN);
      %LOCAL FILEID R_BY R_BREAK;
      %LET   FILEID  = &III&FFF&CYC;
      %LET   R_BY    = %&FFF.SEQ(TS=&TSPAN);
      %LET   R_BREAK = %&FFF.SEQ(TS=&TSPAN,OP=BREAK);
      DATA TEMP;
        %&FFF.LEN(TS=&TSPAN,TYPE=BOTH);
        %&FFF.FMT(TS=&TSPAN);
        %&FFF.LBL;
        SET  &DDN..&FILEID;
        (correction logic)
      RUN;
      PROC SORT DATA=TEMP; BY &R_BY; RUN;
      DATA &DDN..&FILEID (%&FFF.FILE(TS=&TSPAN,OP=FILEOPTS)
                         );;;;
        %&FFF.LEN(TS=&TSPAN,TYPE=BOTH);
        %&FFF.FMT(TS=&TSPAN);
        %&FFF.LBL;
        SET  TEMP;
        %&FFF.SUM(SUMBY=&R_BY,SUMBREAK=&R_BREAK);
      RUN;
    %MEND RETFIT;
     /* RETROFIT DETAIL FILES */
    %RETFIT(&SNPAX,NPA,NCP,01,DETAIL); /*RETROFIT 1ST CYCLE*/
     ...
    %RETFIT(&SNPAX,NPA,NCP,nn,DETAIL); /*RETROFIT LAST CYCL*/
     /* RETROFIT MONTHS FILES */
    %RETFIT(&SNPAM,NPA,NCP,01,MONTHS); /*RETROFIT 1ST CYCLE*/
     ...
    %RETFIT(&SNPAM,NPA,NCP,nn,MONTHS); /*RETROFIT LAST CYCL*/

Again, this database retrofit should be done at the same
time as permanent corrections to CA MICS logic or user exit
code.  This assures that a single form of the key element
value exists at a given time.

Here is a sample table showing the opposite granularity
problem.  New key data element values have a finer
granularity than the old:

    Old     Old     New     New
    Value   Order   Value   Order
    -----   -----   -----   -----
    AA01    1       AA011   1
                    AA012   2
                    AA013   3
    AA02    2       AA021   4
                    AA022   5
    BA01    3       BA011   6


The retrofitting of key data element values at the DETAIL
timespan can be done easily, because no summarization is
necessary.  However, higher levels of summarization have
already taken place.  Data in the database in DAYS, WEEKS,
MONTHS and YEARS has been summarized to a higher level of
granularity than is desired, and splitting the data back into
its component parts can be difficult.  In fact, an accurate
method of doing this often involves reprocessing input source
data.  This may be done using a special database.  (Special
databases are described in the Planning, Installation,
Operation, and Maintenance Guide.)

It is possible to avoid such situations by doing a complete
analysis of key data element requirements before the files
are structured.  But special requirements often present
themselves after the database has been in production for
some time.

It may be more advantageous to treat the database to
accommodate the new data elements or adjusted lengths, so the
key structures of existing observations are compatible with
the new method, but leave old values.

Take the example of adding a new data element to the key.  If
there is no source for calculating the value of the new data
element from summarized data, add the data element to higher
timespans with a blank value (zero for numeric data
elements).  This gives you the ability to extract data by the
new key for all data added to the database after the key
change, and to access all the data on the database by the
higher (old) granularity as well.

Contact CA MICS Product Support for more information on
performing this difficult retrofit process.