esqlconv

esqlconv is a tool to convert the COBOL/DB2 E/SQL to COBOL/Oracle and COBOL/MS SQL Server compatible sources. It performs several types of manipulation (AKA process) on the code: each manipulation process may be separately activated with the command line options.

Some processes require the XML description of DB2 tables used in the processed programs. This files may be obtained with xddlconv.

Target selection

The target database for the code conversion must be selected. The currently supported database types:

The target must be selected with -target option specifying one of the following values:

Process description

SQLCODE process

This process handles the SQLCODE comparisons, replacing the DB2 SQLCODEs with the corresponding Oracle one. This process may be propagated to all the fields "infected" by SQLCODE: when SQLCODE is move on another field, this one is processed as SQLCODE, and therefore also comparisons on this are managed.

Example

IF SQLCODE = 100
  ...
END-IF

becomes

IF SQLCODE = 1403
  ...
END-IF

Note: the SQLCODE values conversion table may be externally provided, using option -s. Furthermore, using option -create, ESQLCONV, may generate this file this table, in order to provide you a skeleton file to customize with your desired values. I.e.

esqlconv -creare -s <sqlcodes_file_to_generate> -target mssql

TIMESTAMP process

It replaces the DB2 special register CURRENT TIMESTAMP with LOCALTIMESTAMP, and the system table SYSIBM.SYSDUMMY1 with DUAL.

SETTSTAMP process

It replaces the SET :host-var = TIMESTAMP statements with corresponding Oracle or SQL Server ones, and it replaces the system table SYSIBM.SYSDUMMY1 by DUAL for Oracle only.

Example

EXEC SQL
  SET :HOST-VAR = CURRENT TIMESTAMP
END-EXEC

becomes (for Oracle)

EXEC SQL
   SELECT CURRENT_TIMESTAMP FROM DUAL INTO :HOST-VAR
END-EXEC

becomes (for SQL Server)

EXEC SQL
   SELECT CURRENT_TIMESTAMP INTO :HOST-VAR
END-EXEC

RMSCHEMA process

It removes the scheme identifier from table names.

Example

EXEC SQL
      SELECT *
      ...
      FROM SCH01.TAB1
      ...
END-EXEC

becomes

EXEC SQL
      SELECT *
      ...
      FROM TAB1
      ...
END-EXEC

CURSOR process

It replaces the dash (-) character with underscore (_) in the cursor names.

Example

EXEC SQL DECLARE CUR-SELCS CURSOR FOR
    ...
END-EXEC

becomes

EXEC SQL DECLARE CUR_SELCS CURSOR FOR
    ...
END-EXEC

DATE process

Handles the DB2 DATE fields, to allow their correct management with the target database. Please note that DB2 automatically recognizes the format of dates contained in the host variables, while Oracle and SQL Server require an explicit date format qualification (i.e. TO_DATE). Whenever necessary, the process inserts the the HTTODATE PL/SQL function that emulates the DB2 behaviour. If an indicator is defined, the function HTTODATE2 is used.

It processes the E/SQL statements replacing:

Example

EXEC SQL
  ...
  WHERE MY_DATE = :D-SEARCH
END-EXEC

becomes

EXEC SQL
  ...
  WHERE MY_DATE = HTTODATE(:D-SEARCH)
END-EXEC

Process the E/SQL statements replacing:

Furthermore, it is possible to change the behaviour of this process, providing the options -noHTTODATE (which inhibits the usage of HTTODATE) e -convDate, which enables the conversion in CHAR format of the output dates (for SELECT and WHERE) using the function TO_CHAR (for Oracle) and CONVERT (for SQL Server).

Of course, this process is based on the identification of DATE fields for the processed tables, whose DDL must be correctly parsed by xddlconv and provided to ESQLCONV (option -l).

Example 1 (HTTODATE)

EXEC SQL
  ...
  WHERE MY_DATE = :D-SEARCH
END-EXEC

becomes

EXEC SQL
  ...
  WHERE MY_DATE = HTTODATE(:D-SEARCH)
END-EXEC

Example 2 (DATE sum)

EXEC SQL
    SELECT DATE(:WSX-DATE) + (TD-DAY - 1) DAYS
    ...
END-EXEC

becomes

EXEC SQL
    SELECT HTTODATE(:WSX-DATE, :ESQL-DATE-FMT) +
     NUMTODSINTERVAL((TD-DAY - 1), 'DAY')
  ...
END-EXEC

TIME process

Process the E/SQL statements managing fields previously defined as TIME in DB2. It applies the following changes:

Depending on the conversion options provided to xddlconv, TIME fields may be converted into DATE or CHAR(8) datatypes. Therefore, the behaviour of this process varies depending on the conversion strategy applied by xddlconv. By default, esqlconv manages TIME fields as CHAR(8). Providing the "-td" option on command line, it handles TIME fields as DATE (TIME to DATE).

Note: All the operands used in WHERE clauses, are always managed as CHAR.

Example 1 - SELECT with -td (TIME to DATE)

EXEC SQL
   SELECT C_CHAR
        , C_TIME
     INTO :C-CHAR
        , :C-TIME:C-TIME-I
     FROM T_TABLE
    WHERE C_CHAR   = 'rec1'
      AND C_TIME BETWEEN '00:00:01' AND CURRENT TIME
END-EXEC

becomes

EXEC SQL
   SELECT C_CHAR
        , TO_CHAR(C_TIME, :ESQL-TIME-FMT)
     INTO :C-CHAR
        , :C-TIME:C-TIME-I
     FROM T_TABLE
    WHERE C_CHAR   = 'rec1'
      AND TO_CHAR(C_TIME, :ESQL-TIME-FMT) BETWEEN
          '00:00:01' AND TO_CHAR(CURRENT_DATE, :ESQL-TIME-FMT)
END-EXEC

Example 2 - INSERT with -td (TIME to DATE)

EXEC SQL
   INSERT INTO T_TABLE
        ( C_CHAR
        , C_TIME
        , C_TIME2
        , C_TIME3
        )
    VALUES
        ( :C-CHAR
        , :C-TIME:C-TIME-I
        , CURRENT TIME
        , '13:45:58'
        )
END-EXEC

becomes

EXEC SQL
   INSERT INTO T_TABLE
        ( C_CHAR
        , C_TIME
        , C_TIME2
        , C_TIME3
        )
    VALUES
        ( :C-CHAR
        , TO_DATE(:C-TIME:C-TIME-I, :ESQL-TIME-FMT)
        , CURRENT_DATE
        , TO_DATE('13:45:58', :ESQL-TIME-FMT)
        )
END-EXEC

Example 3 - UPDATE without -td (TIME to CHAR(8))

EXEC SQL
   UPDATE T_TABLE
      SET C_TIME   = :C-TIME :C-TIME-I
        , C_TIME2  = CURRENT TIME
        , C_TIME3  = '13:45:58'
    WHERE C_CHAR   = 'rec1'
      AND C_TIME BETWEEN '00:00:01' AND CURRENT TIME
END-EXEC

becomes

EXEC SQL
   UPDATE T_TABLE
      SET C_TIME   = :C-TIME :C-TIME-I
        , C_TIME2  = TO_CHAR(CURRENT_DATE, :ESQL-TIME-FMT)
        , C_TIME3  = '13:45:58'
    WHERE C_CHAR   = 'rec1'
      AND C_TIME BETWEEN '00:00:01' AND
       TO_CHAR(CURRENT_DATE, :ESQL-TIME-FMT)
END-EXEC

Of course, this process is based on the identification of DATE fields for the processed tables, whose DDL must be correctly parsed by xddlconv and provided to ESQLCONV (option -l).

TIMESTAMP process

Process the E/SQL statements managing fields previously defined as TIMESTAMP in DB2. It applies the following changes:

For Oracle, the standard behaviour of this process is based on the management of DB2 TIMESTAMP types as Oracle TIMESTAMP types.

For SQL Server, this is meaningless because, on this target DB, TIMESTAMP is always converted as CHAR(26).

OPTIMIZE process

Replaces the OPTIMIZE FOR clause with /*+ FIRST ROWS(n) */

EXEC SQL
    DECLARE CURFUN CURSOR FOR
    SELECT TFUN_CNCNLIDA,
           TFUN_CNVFORSA
      FROM CNDVFUN0
     WHERE TFUN_CNCNLIDA = :VFUN0-CNCNLIDA
       FOR FETCH ONLY
       OPTIMIZE FOR 250 ROWS
END-EXEC

becomes

EXEC SQL
    DECLARE CURFUN /*+ FIRST_ROWS(250) */ CURSOR FOR
    SELECT TFUN_CNCNLIDA,
           TFUN_CNVFORSA
      FROM CNDVFUN0
     WHERE TFUN_CNCNLIDA = :VFUN0-CNCNLIDA
       FOR FETCH ONLY
END-EXEC

RMFETCHONLY process

Removes the FOR FETCH ONLY and FOR READ ONLY clauses.

ALL process

Activates all processes available.

Output structure

The source programs are only read, and the modified version is stored in the output repository, defined with the -o option. The output repository structure depends on the source input: each file processed is stored in the output repository in a directory with the same name of the one from which the object has been loaded.

Output files

esqlconv processes the programs: coybooks and includes are handles as program components, and therefore they can not be managed standalone. esqlconv generates the following files:

Modified program

It is the converted program, and It has the same name of the source one.

Modified copybooks and includes

Each copybook and include file processed.

Listing

It is stored in the same directory of the modified program, with extension .lst. It contains the result of copybook/include expansion.

Log file

It is stored in the same directory of the modified program, with extension .log. It contains a summary of operation performed, including:

Each row/column coordinate refers to the listing.

Options

Date/Time conversion format

The format specification fields (i.e. ESQL-TIME-FMT, ESQl-DATE-FMT) are provided by the host variables defined in the include file ESQLINC0.cpy. This include must be created by the user.

Example (ESQLINC0.cpy for Oracle)

01  ESQLINC0.
   02  ESQL-DATE-FMT         PIC X(10) VALUE "YYYY-MM-DD".
   02  ESQL-TIME-FMT         PIC X(10) VALUE "HH24:MI:SS".
   02  ESQL-TIMESTAMP-FMT    PIC X(21) VALUE "YYYY-MM-DD HH24:MI:SS".

Example (ESQLINC0.cpy for SQL Server)

01  ESQLINCO.
   02  ESQL-DATE-FMT         PIC S9(4) COMP-5 VALUE 102.
   02  ESQL-TIME-FMT         PIC X(10) COMP-5 VALUE 108.
   02  ESQL-TIMESTAMP-FMT    PIC X(21) COMP-5 VALUE 121.

A sample ANT target

      <target name="ol_db2">
               <mkdir  dir="${home}/src/db2"/>
               <apply executable="esqlconv" dest="${home}/src/db2/tp">
                       <arg value="-x all"/>
                       <arg value="-c ${pwd}/cpy"/>
                       <arg value="-c ${pwd}/include"/>
                       <arg value="-e CPY"/>
                       <arg value="-l ${home}/db2/xml"/>
                       <arg value="-o ${home}/src/db2"/>
                       <arg value="-p"/>
                       <srcfile/>
                       <fileset dir="src/tp" includes="*.pre"/>
                       <mapper type="glob" from="*.pre" to="*.pre"/>
               </apply>
       </target>