DB2 to Oracle

Datatypes

DATE

To normalize it, the NLS_DATE_FORMAT parameter must be changed in one of the following ways

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'

Both XCICS and SQLLOADER (XRUN) support the setting of the parameter.

XCICS uses the configuration parameter rdbms_date_format in xcics.conf.

I.e.

set rdbms_date_format='YYYY/MM/DD';

SQLLOADER (XRUN) uses the environment XRUN_SQL_DATE_FORMAT.

I.e.

export XRUN_SQL_DATE_FORMAT="YYYY/MM/DD"

No changement to user program is required

TIMESTAMP (on Oracle 9i)

To normalize it, the NLS_TIMESTAMP_FORMAT parameter must be changed in one of the following ways

ALTER SESSION SET NLS_TIMESTAMO_FORMAT = 'YYYY-MM-DD HH24.MI.SSXFF'

Both XCICS and SQLLOADER (XRUN) support the setting of the parameter.

XCICS uses the configuration parameter rdbms_timestamo_format in xcics.conf.

I.e.

set rdbms_timestamp_format='YYYY-MM-DD HH24.MI.SSXFF';

SQLLOADER (XRUN) uses the environment XRUN_SQL_TIMESTAMP_FORMAT.

I.e.

export XRUN_SQL_TIMESTAMP_FORMAT="YYYY-MM-DD HH24.MI.SSXFF"

User programs changements

The DB2 CURRENT TIMESTAMP must become LOCALTIMESTAMP-

I.e.

Please note the LOCALTIMESTAMP must be used intead of using CURRENT_TIMESTAMP as it is a TIMESTAMP WITH TIME ZONE, while LOCALTIMESTAMP is a simple TIMESTAMP.

Please note also that SYSTIMESTAMP is not sensible at TIME ZONE changes.

TIME ZONE management

It is possible to establish the time zione for a session with the command:

ALTER SESSION SET TIME_ZONE = '-5:0'

TIMESTAMP (on Oracle 8i)

The TIMESTAMP field may be simulated on Oracle8 with a user function.

User programs changes

The DB2 CURRENT TIMESTAMP must become LOCALTIMESTAMP-

I.e.

Generating user function

The user function LOCALTIMESTAMP may be written using:

Generating with PL/SQL

This simple script generates the function:

CREATE OR REPLACE FUNCTION LOCALTIMESTAMP
RETURN CHAR IS RC CHAR(26);
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24-MI-SS')
          INTO RC
          FROM DUAL;
   RETURN(RC);
END;

Generating the function in PL/SQL has a problem: no milliseconds field is returned (I.e. [2002/08/08 19:44:21:      ]).

Generating with Java

To generate in Java proceed as follow:

/**
* LocalTimeStamp.java
*/
import java.util.*;
import java.text.*;
public class LocalTimeStamp {
public static String getTimeStamp() {
     SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.SSS");
     return sdf.format(new Date());
}
}

# javac LocalTimeStamp.java
# loadjava -user userid/password LocalTimeStamp.class

CREATE OR REPLACE FUNCTION LOCALTIMESTAMP
    RETURN VARCHAR2
    AS LANGUAGE JAVA
    NAME ?LocalTimeStamp.getTimeStamp() return java.lang.String?;
/

Using this method the first 3 digit of milliseconds may be obtained (I.e. [2002/08/08 19:44:21:206   ])

Moreover Java code does not manages the oracle Time Zone therefore Java Locales shoudl be used.

EBCDIC sequence

Using Oracle ordering and matches may be done according to EBCDIC sequence on different levels:

Ordering the single query

A single query may be ordered according to the EBCDIC collating sequence:

EXEC SQL SELECT CODICE
               COGNOME,
               NOME,
        INTO :CD, :CO, :NO
        ORDER BY NLSSORT(CODICE, 'NLS_SORT=EBCDIC')
END-EXEC

Be carefull using the WHERE clause as it uses the standard ASCII sequence.

Ordering for the session

To set up the session seems to be the best solution, if necessary. To set up the session two runtime parameter must be set:

ALTER SESSION SET NLS_SORT = 'EBCDIC';

ALTER SESSION SET NLS_COMP = 'ANSI';

The first statement sets EBCDIC for ordering, and the second one forces matches to work against the EBCDIC sequence.

Column indexing using EBCDIC

It is possible to generate EBCDIC ordered indexes using function based indexes.

I.E.

CREATE INDEX EBCIDX ON TABLE1 (NLSSORT(FIELD1, 'NLS_SORT=EBCDIC'));

In order to create this kind of indexes, user must have granted for query rewrite. I.e.

GRANT QUERY REWRITE TO USER1;

SQLCODE

Following tables describe the code changes between DB2 and Oracle.