HCOSS Conversion Rules

The HCOSS DB2 to SQL Server conversion process includes the following:

• Converts DB2 functions and special registers to SQL Server functions and special registers according to a set of the defaults that best match one to the other. Customizable.
• Converts DB2 data types to SQL Server data types according to a set of defaults that best match one to the other. Customizable.
• Converts DB2 SQLCODE values to equivalent SQL Server SQLCODE values. Customizable.
• Depending on the setting of the DETECTDATE directive, converts DB2-formatted date and time string values in character fields as follows:
  • DETECTDATE is turned on, HCOSS converts to ODBC format before sending the data to the server. ODBC format is:

    yyyy-mm-dd hh:mm:ss.n

  • DETECTDATE is turned off, HCOSS converts to a DB2-compatible format. The DB2-compatible format is:

    yyyy-mm-mm-hh.mm.ss.nnnnnn

• Converts the DB2 CONCAT function and operator into an HCOSS-supplied dbo.CONCAT call, such that SQL Server character, numeric, and datetime data are concatenated according to DB2 rules.
• Provides the CONCAT_BINARY function and operator to concatenate SQL Server SQL TYPE BINARY and SQL TYPE VARBINARY data according to DB2 rules. You should manually convert applications that concatenate z/OS DB2 BINARY or VARBINARY values to use CONCAT_BINARY when migrating to SQL Server. CONCAT_BINARY is converted to the HCOSS-supplied dbo.CONCAT_BINARY call.
• Converts the following statements:

  SET host-variableAssignment
  VALUES INTO host-variableAssignment

  Where the value of host-variableAssignment is not CURRENT PACKAGESET or CURRENT PACKAGE PATH, into equivalent SQL Server SELECT statements.
• Converts LOCK TABLE statements to equivalent SQL Server select statements with appropriate TABLOCK or TABLOCKX and HOLDLOCK table hints.
• Converts labeled durations to equivalent calls of the SQL Server DATEADD functions.
• Converts isolation clauses in cursor selects to corresponding SQL Server table hints.
• Converts SKIP LOCKED DATA clauses in cursor selects to corresponding READPAST table hints.
• Converts the following following DB2 functions to corresponding SQL Server DATEPART functions:
  • DAY
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFWEEK ISO
  • DAYOFYEAR
  • HOUR
  • MICROSECOND
  • MINUTE
  • MONTH
  • SECOND
  • WEEK
  • WEEK ISO
  • YEAR
• Converts the following DB2 type conversion functions to corresponding SQL Server CONVERT functions:
  • DATE
  • TIME
  • TIMESTAMP
  • TIMESTAMP ISO
• Converts the DB2 DAYS function to the corresponding SQL Server DATEDIFF function.
• Allows optimization of DB2 SQL statements by adding SQL Server query hints, such as OPTION(RECOMPILE), to select, insert, update, and delete statements.
• For SQL Server 2012 SP2 and later, executes converted DECLARE GLOBAL TEMPORARY statements at compile time. These enable subsequent statements that reference the temporary table to be checked using SQL(CHECK).