Making non-SQL date and time fields into SQL columns

Products

• IDMS
• IDMS - Database

Issue/Introduction

Non-SQL defined date and time fields are simply PIC 9(8) or PIC 9(6) fields with no special type. They are still numeric. It would be nice to make them have an SQL datatype of DATE, TIME or TIMESTAMP so that front end applications can take advantage of that fact.

Environment

Release: All supported releases.

Resolution

In the following example, NAVSCHM is a non-SQL defined schema, SNSSCHM is the SQL schema defined for non-SQL schema NAVSCHM, and SQLSCHM is a native SQL defined schema.
Consider a network record defined in schema NAVSCHM as follows:

ADD
RECORD NAME IS NAVDTS VERSION IS 1
.
02 ID
    PICTURE IS  9(4)
    USAGE IS DISPLAY
    .
02 NAVDATE
    PICTURE IS  9(8)
    USAGE IS DISPLAY
    .
02 NAVTIME
    PICTURE IS  9(6)
    USAGE IS DISPLAY
    .

An SQL schema for this non-SQL schema can be defined as follows:

CREATE SCHEMA SNSSCHM
    FOR NONSQL SCHEMA APPLDICT.NAVSCHM VERSION 1
    ;

But when data is selected from this record, they are still just numeric fields of a particular length:

SELECT * FROM SNSSCHM.NAVDTS;
*+
*+     ID     NAVDATE   NAVTIME
*+     --     -------   -------
*+      1    20091203    141234
*+      2    19991203     80607
*+
*+ 2 rows processed

They are only DATE and TIME fields because the application treats them as such.

By using SQL functions and optionally a VIEW definition, it is possible to create SELECT statements where the returned data will be of the correct data type.

For example:

SELECT ID,
       DATE(CAST(INSERT(INSERT(CAST(NAVDATE AS CHAR(8)),
         7, 0, '-'), 5, 0, '-') AS CHAR(10))) AS SQLDATE,
       TIME(CAST(INSERT(INSERT(SUBSTR(CAST(NAVTIME+1000000
         AS CHAR(7)), 2, 6), 5, 0, ':'),  3, 0, ':')
         AS CHAR(8))) AS SQLTIME
  FROM SNSSCHM.NAVDTS;
*+
*+     ID  SQLDATE     SQLTIME
*+     --  -------     -------
*+      1  2009-12-03  14.12.34
*+      2  1999-12-03  08.06.07
*+
*+ 2 rows processed 

The use of the +1000000 and SUBSTR() function call for the NAVTIME column is required to ensure that a time prior to 10.00 will still result in a CHAR(6) field which can still be processed by the outer INSERT function calls. This would not be necessary if the TIME field was defined as PIC X(6).

An easier way to extract the DATE is to use the TIMESTAMP function first because it will accept a string of 14 characters with no formatting in between to interpret the timestamp:

SELECT ID,
       DATE(TIMESTAMP(CAST(NAVDATE*1000000 AS CHAR(14))))
         AS SQLDATE
  FROM SNSSCHM.NAVDTS;
*+
*+     ID  SQLDATE
*+     --  -------
*+      1  2009-12-03
*+      2  1999-12-03
*+
*+ 2 rows processed

The two columns can also be combined to generate the full timestamp value in one field:

SELECT ID,
       TIMESTAMP(CAST(NAVDATE*1000000+NAVTIME AS CHAR(14)))
         AS SQLTIMESTAMP
  FROM SNSSCHM.NAVDTS;
*+
*+     ID  SQLTIMESTAMP
*+     --  ------------
*+      1  2009-12-03-14.12.34.000000
*+      2  1999-12-03-08.06.07.000000
*+
*+ 2 rows processed

Finally by defining this column in a view, it becomes much easier for the application program to extract the individual date and time:

CREATE VIEW SQLSCHM.SQLDTS  ( ID, SQLTIMESTAMP )    AS
SELECT ID,
       TIMESTAMP(CAST(NAVDATE*1000000+NAVTIME AS CHAR(14)))
         AS SQLTIMESTAMP
  FROM SNSSCHM.NAVDTS;

using a simple select:

SELECT ID,
       DATE(SQLTIMESTAMP) AS SQLDATE,
       TIME(SQLTIMESTAMP) AS SQLTIME
  FROM SQLSCHM.SQLDTS;
*+
*+     ID  SQLDATE     SQLTIME
*+     --  -------     -------
*+      1  2009-12-03  14.12.34
*+      2  1999-12-03  08.06.07
*+
*+ 2 rows processed