While most mainframe DB2 applications use the default format for DB2 DATE, TIME, and TIMESTAMP PIC X character host variables, some do not. Mainframe DB2 applications support alternative formats as well as the defaults. Similarly, most SQL Server applications use the default date, time, and datetime2 formats for PIC X character host variables. Unfortunately, default DB2 data type formats do not match the default SQL Server data type formats. To ensure that your mainframe DB2 DATE, TIME, and TIMESTAMP formatting is properly converted and recognized by your SQL Server application, use HCOSS to customize the conversion.
For example, the default Data Type formats for character host variables are:
| Mainframe Data Type | Mainframe DB2 format | SQL Server Data Type | SQL Server format |
|---|---|---|---|
| DATE | yyyy-mm-dd | date | yyyy-mm-dd |
| TIME | hh.mm.ss | time | hh:mm:ss |
| TIMESTAMP | yyyy-mm-dd-hh.mm.ss.ffffff | datetime2 | yyyy-mm-dd hh:mm:ss.ffffffff |
The character host variable default formats for the DATE data type for mainframe DB2 and the date data type for SQL Server match; therefore, SQL Server recognizes the DB2 format and no conversion is required. However, the default data type formats for TIME/time and TIMESTAMP/datetime2 do not match. Therefore, if your application uses default DB2 TIME and/or TIMESTAMP formats for your character host variables, and you want to use these for your SQL Server time and datetime2 data types respectively, use HCOSS to convert the mainframe DB2 formats such that they are recognized by SQL Server.
In cases where the DB2 application being converted uses an input host variable format that is not supported by SQL Server by default, use the DETECTDATE HCOSS compiler directive option to convert the DB2 format to a SQL Server-compatible format. DETECTDATE instructs HCOSS to examine the contents of SQL Server character input host variables, looking for data that matches the mainframe format specified for DATE, TIME and TIMESTAMP data.
With respect to DATE and TIME data types processed using only the DETECTDATE directive to address date and time conversion, HCOSS examines the contents of SQL Server character input host variables and identifies fields that match based on the default DATE and TIME DB2 mainframe formats (yyyy-mm-dd and hh.mm.ss respectively).
If you specify the DATE and/or TIME HCOSS directives in addition to DETECTDATE, HCOSS examines the contents of SQL Server character input host variables and identifies fields that match the date and time formats specified by the DATE and TIME directives.
When it finds a match, HCOSS translates the data in the input host variable into a format SQL Server understands.
| Scenario | DETECTDATE option |
|---|---|
| My DB2 application uses DATE, TIME, and TIMESTAMP values in PIC X input host variables, but I only use those values in date, time or datetime2 columns in SQL Server. | CLIENT |
| My DB2 application uses DATE, TIME, and TIMESTAMP values in PIC X input host variables, but I only use those values in character columns in SQL Server. Also, my SQL does not use either implicit or explicit characters for date, time or datetime2 data types. | Not required. Do not use DETECTDATE, DATE, or TIME HCOSS SQL compiler directive options. |
| I only use SQLTYPE host variables with date, time and datetime2 columns in SQL Server, and never use PIC X host variables with date, time or datetime2 columns. | Not required.1 |
| My DB2 application uses DATE, TIME, and TIMESTAMP values in PIC X input host variables, and I use those values both in character columns and in date, time and datetime2 columns in SQL Server, and my character columns might use data in formats that could be confused with the formats for DATE, TIME, or TIMESTAMP values. | SERVER |
| My application uses DB2 mainframe DATE and/or TIME formats that match the default SQL Server formats, and I use those values in both character columns and/or date columns in SQL Server. | Not required. Do not use DETECTDATE, DATE, or TIME HCOSS SQL compiler directive options. |
| 1Optionally, you can use alternative datetime HCOSS SQL compiler directive options. | |
Simply by enabling HCOSS using the DIALECT=MAINFRAME directive, by default HCOSS returns SQL Server datetime and datetime2 data types in the mainframe default format as shown below:
| Mainframe Data Type | SQL Server Data Type | Mainframe Default Format |
|---|---|---|
| DATE | date | yyyy-mm-dd |
| TIME | time | hh.mm.ss |
| TIMESTAMP | datetime2 | yyyy-mm-dd-hh.mm.ss.ffffff |
HCOSS also provides support for literals inside of SQL statements. To enable this functionality, code any of the following SQL comments into your DB2 mainframe application, positioned before a literal declaration. These comments tell HCOSS whether the literal value is used with a DATE, TIME, TIMESTAMP or CHAR column in your DB2 database:
In particular, the CHAR literal specification can be very helpful when you do not want HCOSS to translate the literal.