*********************************************************************

*   CE801.T13 DB-EZTest Tech Note                    September 1998 *

*                                                                   *

*   KEYWORDS: DB-EZTest NATIVE SQL VIRTUAL DATA AGING               *

*                                                                   *

*********************************************************************

*                                                                   *

*   This Tech Note describes how to set an SRL table to allow       *

*   virtual aging of a IDMS SQL defined database.                   *

*                                                                   *

*********************************************************************



SQL syntax similar to the following can be used to extract the

infomation, from the SQL catalog, required to manually build an SRL 

table.



SET SESSION CURRENT SCHEMA SYSTEM;         

SELECT TABLE.NAME,                         

       COLUMN.NAME,                        

       COLUMN.TYPE,                        

       COLUMN.VOFFSET,                     

       COLUMN.VLENGTH                      

  FROM TABLE, COLUMN                       

  WHERE TABLE.SCHEMA = 'DEMOEMPL'          

    AND TABLE.NAME = COLUMN.TABLE          

    AND TABLE.TYPE = 'T'                   

    AND COLUMN.NAME LIKE '%DATE%'          

  ORDER BY TABLE.NAME, COLUMN.VOFFSET;





The resultant output is similar to:



NAME            NAME                        TYPE      VOFFSET  VLENGTH

                                                                                

BENEFITS        LAST_REVIEW_DATE            DATE           35        8       

BENEFITS        PROMO_DATE                  DATE           39        8  

COVERAGE        SELECTION_DATE              DATE            7        8 

COVERAGE        TERMINATION_DATE            DATE           16        8  

EMPLOYEE        START_DATE                  DATE          145        8

EMPLOYEE        TERMINATION_DATE            DATE          154        8

EMPLOYEE        BIRTH_DATE                  DATE          163        8  

INSURANCE_PLAN  EFF_DATE                    DATE          152        8  

JOB             EFF_DATE                    DATE           45        8  

POSITION        START_DATE                  DATE            8        8 

POSITION        FINISH_DATE                 DATE           17        8  

     

Information from this output can be used to create and assemble an SRL

table using the #SRLTAB macro. The following example builds an SRL table

for the EMPLOYEE SQL table in schema DEMOEMPL.



SQLSRL   #SRLTAB ENTRY=FIRST,FILE=DEMOEMPL                              

         #SRLTAB RECORD=EMPLOYEE                                        

         #SRLTAB DATENAM=START_DATE,DATEOFF=145,DATELEN=8,DFC=SQLDATE   

         #SRLTAB DATENAM=TERMINATION_DATE,                             X

               DATEOFF=145,DATELEN=8,DFC=SQLDATE                        

         #SRLTAB DATENAM=BIRTH_DATE,DATEOFF=163,DATELEN=8,DFC=SQLDATE   

         #SRLTAB ENTRY=LAST                                             



The DATEOFF and DATELEN are taken from the VOFFSET and VLENGTH columns

in the SQL query above. Additional tables/columns can be defined as

needed. 



The additional query:



SELECT NAME,                                         

       TABLE,                                        

       COLUMN                                        

  FROM INDEXKEY                                      

  WHERE SCHEMA = 'DEMOEMPL' AND COLUMN LIKE '%DATE%';



can be used to determine whether the additional parameters SORT=YES and 

CALC=YES are required on the column definitions in the SRL. CALC=YES

indicates that the column participates in a hash key(NAME=HASH from the

above query) and SORT=YES indicates that the column is part of an index

key(NAME not equal to HASH).