IMAGE/SQL Date Mapping Enhancement

CAUTION: The DATE MAPPING enhancement for IMAGE/SQL will be available in the first IMAGE/SQL patch released on MPE/iX version 6.5. This enhancement requires migration of the ATCINFO file since the ATCINFO file format has been changed to keep track of additional date related information.

• In TurboIMAGE, dates may be held in numeric or ascii fields. If no data exists in a data field, it will hold binary zeroes, and will be interpreted by SQL as zero rather than null. Any attempt to CAST this data into a DATE format will fail with an error, and the SELECT can not continue. Similarly, a failure in the validation of dates in the 3GL code will result in the same error, and the SELECT statement fails.

• Both FA3000 and MM3000 will be handling year 2000 and beyond by incrementing the year to 'A0' for the year 2000, 'B0' for year 2010 etc. This allows their applications to be able to distinguish the year 2000 and beyond with a 'YYMMDD' format. The users of FA3000 and MM3000 need a way for IMAGE/SQL to convert the above format year to SQL DATE and vise versa. To alleviate the problems mentioned above, IMAGE/SQL has been enhanced to allow some numeric or ascii fields to be mapped to DATE columns, and the users can specify what DATE format the data is in. The date formats supported are the same as the formats supported by MPE. Following is a description of the enhanced SPLIT and UPDATE TYPE commands in the IMAGESQL program.

This enhancement also has a dependency on the Business basic patch BBRDXU7 for the new Business Basic conversion routines.

SP(LIT)

Syntax

  SP[LIT] MappedTable.MappedColumn INTO NewMappedColumn:SourceType
          [FORMAT=FormatType[,lowvalue[,highvalue]]]
          [:MappedType [SIGNED  ]
                       [UNSIGNED]] [,...]

Example

  (i)    SPLIT TABLE1.COLUMN5 INTO NEWCOLUMN1:I4:CHAR(8),&
                                   NEWCOLUMN2:X20,&
                                   NEWCOLUMN3:K3:DECIMAL(15,0)
  (ii)   SPLIT TABLE2.COLUMN3 INTO NEWCOL1:I4:CHAR(8),&
                                   NEWCOL2:X20,&
                                   NEWCOL3:Z4:DECIMAL(4,0) UNSIGNED
  (iii)  SPLIT TABLE2.MYDATE INTO &
           MYDATE1:X6 FORMAT=DT35,'000000','999999':DATE, &
           MYDATE2:X6 FORMAT=DT36,'******','&&&&&&':DATE
  (iv)   SPLIT TABLE2.DDATE INTO &
           DDATE1:X6 FORMAT=DT26,'     0','    99':DATE,&
           DDATE2:X6 FORMAT=DT25,'      ','------':DATE
  (vi)   SPLIT TABLE2.MYDATE2 INTO &
           MYDATE2_1:I2 FORMAT=DT2,0,-1:DATE, &
           MYDATE2_2:I2 FORMAT=DT2,0,-1:DATE
  (vii)  SPLIT TABLE1.COLUMN5 INTO NEWCOLUMN1:I4:CHAR(8),&
                                   NEWCOUMNL2:X20,&
                                   NEWCOLUMN3:K4:FORMAT=BB2:FLOAT
  (viii) SPLIT TABLE2.MYBBDECS INTO &
           MYBBDEC_1:K2 FORMAT=BB1:FLOAT, &
           MYBBDEC_1:K4 FORMAT=BB2:FLOAT

U[PDATE] TYPE

Syntax

  U[PDATE] TYPE {SourceType IN {*          }
                               {MappedTable}
                 [FORMAT=FormatType[,lowvalue[,highvalue]]]}
                 {IN MappedTable.Col
                     [FORMAT=FormatType[,lowvalue[,highvalue]]]}
           [TO NewMappedType [SIGNED  ]
                             [UNSIGNED]]

Examples

  (i)    UPDATE TYPE I4 IN COMPOSER
  (ii)   UPDATE TYPE IN COMPOSER.BIRTHDATE TO CHAR(18)
  (iii)  UPDATE TYPE IN TABLE1.UNSIGNEDZ4 TO DECIMAL(4,0) UNSIGNED
  (iv)   UPDATE TYPE IN TABLE2.MYDATE FORMAT=DT35,'000000','999999' &
           TO DATE
  (v)    UPDATE TYPE X6 IN TABLE2 FORMAT=DT26,'     0','    99' &
           TO DATE
  (vi)   UPDATE TYPE IN TABLE2.MYDATE2 FORMAT=DT2,0,-1 TO DATE
  (vii)  UPDATE TYPE IN TABLE2.MYP8 FORMAT=DT15,0,-1 TO DATE UNSIGNED
  (viii) UPDATE TYPE IN TABLE3.MYZ6 FORMAT=DT26,'******','999999' &
           TO DATE SIGNED
  (ix)   UPDATE TYPE IN TABLE4.MYZ8 FORMAT=DT38,'--------', &
           '########' TO DATE UNSIGNED
  (x)    UPDATE TYPE K2 IN TABLE2 FORMAT=BB1 TO FLOAT
  (xi)   UPDATE TYPE IN TABLE2.MYBBDEC FORMAT=BB2 TO FLOAT(53)

Format(TI)Type StorageType #Bytes Explanation

Notes

1. MM3000 dates are represented as in the MM3000 product which uses the ASCII "A" through "Z" for decades starting with the year 2000.

2. lowvalue will be displayed as '9900-01-01',
   highvalue will be displayed as '9999-12-31', and
   invalid value will be displayed as '9901-12-31'
   There will be no default values for lowvalue and highvalue. If lowvalue
   and/or highvalue are not provided, then the code checking for
   lowvalue/highvalue will not be exercised.

3. For integer DT types (DT1 - DT18), the lowvalue and the highvalue must be set to integer values.

4. For ASCII DT types (DT25 - DT38), the lowvalue and the highvalue must be set to ascii values (single quoted), 0 (for COBOL LOWVALUES), or -1 (for COBOL HIGHVALUES). Single quote character cannot be included in the lowvalue or highvalue ascii string. No padding will be done for the user. The user must provide ascii values with the correct length. For example, for DT25, the lowvalue and highvalue provided must be 6 characters long.

5. If the TURBO data type is P8, Z6 or Z8, and it is being mapped to DATE, then [UNSIGNED/SIGNED] keyword can be used to indicate whether positive data should be SIGNED or UNSIGNED when inserting data via IMAGE/SQL. The default is SIGNED.

Examples

1. Use IMAGESQL:

   
     UPDATE TYPE IN mytable.mydate FORMAT=DT26,'******','$$$$$$' &
       TO DATE
   
   

2. Use ISQL

   
     INSERT INTO mytable VALUES ('9900-01-01');
       This statement will PUT '******' into the TURBO field mydate.
     INSERT INTO mytable VALUES ('9999-12-31');
       This statement will PUT '$$$$$$' into the TURBO field mydate.
     INSERT INTO date2.d2 VALUES ('9901-12-31','9901-12-31', &
                                               'bad date');
       Invalid DATE for TurboIMAGE: set 1, item 0, column 2,
       record 0.  (DBERR 13512)
       Number of rows processed is 0
     SELECT mydate FROM mytable where mydate is null;
       returns "Number of rows selected is 0"
       i.e. nothing qualifies.
     SELECT mydate FROM mytable WHERE mydate = '9900-01-01';
       returns all records with TURBO value = '******'
       (if index exist on the item, mydate, then CHAIN READ is used)
     SELECT mydate FROM mytable WHERE mydate = '9999-12-31';
       returns all records with TURBO value = '$$$$$$'
       (if index exist on the item, mydate, then CHAIN READ is used)
     SELECT mydate FROM mytable WHERE mydate = '9901-12-31';
       returns all records with TURBO value is not a valid date.
       (SERIAL READ is always used in this case)
   
   

3. How do you migrate your ATCINFO file?

   NOTE: Before migrating your ATCINFO file of your DBE, make sure you backup your existing ATCINFO file and the matching DBE.

   
     :run imagesql.pub.sys
     > dbe yourdbe
     > migrate
   
   

4. How do you know that your ATCINFO file needs migration?

   
     (1) :imagesql
         >> dbe mydbe
         >> base yourdb
         >> display turbodb
         Incompatible ATCINFO file version number, migration
         is needed   (ATCERR 2101).
     (2) isql=> connect to 'mydbe';
         ATCINFO file contains incompatible version number,
         migration is needed.  (DBERR 13516)
   
   

5. How about going back to an older version?

   Possible scenario: You restored the migrated database onto a system that is running an older version of IMAGE/SQL.

   Symptoms:

   
     (1) :imagesql
         >>dbe mydbe
         >>base yourdb
         >>display turbodb
         ATCINFO procedure error (ATCERR 32421,ATCSTAT 2101,
         FSERR 0).
     (2) isql=> connect to 'mydbe';
         IMAGE/SQL internal error 141, 2101, 0, 0.  (DBERR 13554)
   
   

   What to do:

   Restore the ATCINFO file and the matching DBE from a backup before the migration.