HCOPG enables you to keep your mainframe DB2 datetime data type formats intact, including DATE, TIME, and TIMESTAMP formats in PIC X character host variables, while accessing corresponding PostgreSQL date, time, and timestamp columns, rather than using PostgreSQL default formats for these data types. PostgreSQL character columns can also store data with mainframe DB2 formats intact.
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 PostgreSQL applications use the default date, time, and timestamp formats for PIC X character host variables. Unfortunately, default DB2 data type formats do not match the default PostgreSQL data type formats. To ensure that your mainframe DB2 DATE, TIME, and TIMESTAMP formatting is properly converted and recognized by your PostgreSQL application, use HCOPG to customize the conversion.
For example, the default Data Type formats for character host variables are:
| Mainframe Data Type | Mainframe DB2 format | PostgreSQL Data Type | PostgreSQL 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 | timestamp | 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 PostgreSQL match; therefore, PostgreSQL recognizes the DB2 format and no conversion is required. However, the default data type formats for TIME/time and TIMESTAMP/timestamp 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 PostgreSQL time and timestamp data types respectively, use HCOPG to convert the mainframe DB2 formats such that they are recognized by PostgreSQL.
In cases where the DB2 application being converted uses an input host variable format that is not supported by PostgreSQL by default, use the DETECTDATE HCOPG compiler directive option to convert the DB2 format to a PostgreSQL-compatible format. DETECTDATE instructs HCOPG to examine the contents of PostgreSQL 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, HCOPG examines the contents of PostgreSQL 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 HCOPG directives in addition to DETECTDATE, HCOPG examines the contents of PostgreSQL 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, HCOPG translates the data in the input host variable into a format PostgreSQL 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 timestamp columns in PostgreSQL. | 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 PostgreSQL. Also, MySQL does not use either implicit or explicit characters for date, time, or timestamp data types. | Not required. Do not use DETECTDATE, DATE, or TIME HCOPG SQL compiler directive options. |
| I only use SQLTYPE host variables with date, time and timestamp columns in PostgreSQL, and never use PIC X host variables with date, time, or timestamp 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 timestamp columns in PostgreSQL, 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 PostgreSQL formats, and I use those values in both character columns and/or date columns in PostgreSQL. | Not required. Do not use DETECTDATE, DATE, or TIME HCOPG SQL compiler directive options. |
| 1Optionally, you can use alternative datetime HCOPG SQL compiler directive options. | |
Simply by enabling HCOPG using the DIALECT=MAINFRAME and TARGETDB=POSTGRESQL directive, by default HCOPG returns PostgreSQL datetime and timestamp data types in the mainframe default format as shown below:
| Mainframe Data Type | PostgreSQL Data Type | Mainframe Default Format |
|---|---|---|
| DATE | date | yyyy-mm-dd |
| TIME | time | hh.mm.ss |
| TIMESTAMP | timestamp | yyyy-mm-dd-hh.mm.ss.ffffff |
HCOPG 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 HCOPG 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 HCOPG to translate the literal.