Introduction

This article is intended for developers, administrators, or Independent Software Vendors (ISVs) who:

• Have database applications that support non-IBM databases (such as Oracle Server)
• Have customers who wish to migrate their Oracle application to IBM DB2 on a distributed platform — DB2 for Linux, UNIX, and Windows (referred to in this article as DB2)

Many companies and business partners who want to migrate their Oracle applications to support DB2 ask the following question:

I have existing customers running on an Oracle server who wish to run their applications on DB2. What changes do I need to make so that the application will support DB2 data access?

The task of porting your Oracle application to the DB2 platform is made easy if you follow this article step by step. It points out the most common issues that you may encounter while migrating an Oracle application to DB2, and also lets you know the steps you can take to overcome these problems. This article is based on my experience with successfully completing a proof-of-concept migration of an Oracle application having 100 million lines of source code.

Here are some of the assumptions for this particular sample migration:

• The Oracle application for the example is written the C/C++ programming language.
• The porting was done on DB2 Version V8.2 on a UNIX (AIX® 5.2) platform. Most of the issues discussed are the same for DB2 9.

Here are the issues described in this article:

• The DB2 precompiler does not recognize the user-defined datatypes (typefef, #define macros) in the declare section, that is, between EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION statements.
• Applications developed using Oracle user-defined functions (UDFs) (such as || , rawtohex, hextoraw, and so on) are not compiled on DB2 because of different types of parameters.
• Oracle applications using the DECODE function do not get compiled correctly on DB2.
• You need to achieve similar behavior in DB2 for Oracle SQL statements containing NOWAIT.
• You need to handle host variables in DB2 whose size exceeds 32672.
• You need to take care of Oracle's "Select for update" statements when porting to DB2.

This article breaks down the process into major tasks, and then looks at the issues involved with each task.

Back to top

Task 1: Identify embedded SQL (.sqC) programs

For this task, you need to identify all Pro C (SQL + C/C++ program combination) programs within your application. The first focus should be to convert these Pro C programs to embedded SQL programs that DB2 can interpret with its precompiler.

• SQL with C programs: Extension is .sqc (on UNIX)
• SQL with C++ programs: Extension is .sqC (on UNIX)

You may want to review examples of embedded SQL programs developed in C/C++. You can find those samples in the following paths under your DB2 instance directory:

• C programs: sqllib/samples/c
• C++ programs: sqllib/samples/cpp

These embedded SQL programs are compiled using DB2's precompiler (db2 prep).

Issue 1

The DB2 precompiler faces problems while compiling these programs, if you have user-defined data types (for example "typedef" in C/C++) and macros (#define) used between the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION statements.

Therefore, when a file (.sqC) containing embedded SQL statements is passed to the db2 precompile command, it gives errors while compiling. This is illustrated in Listing 1.

Consider the test1.sqC file as follows:

#include <stdio.h>

#define LONGBIG long

/* 
Or we can have a definition like:
typedef long LONGBIG
*/

EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
	LONGBIG col1_val;
EXEC SQL END DECLARE SECTION;

int main()
{
	EXEC SQL SELECT DEPT 
	INTO :col1_val 
	FROM TEST_TABLE;
	
	return 0;
}      

When you try to compile the test1.sqC file using db2 prep, you get the following errors:

db2 prep test1.sqC bindfile 

LINE    MESSAGES FOR test1.sqC
------  --------------------------------------------------------------------
        SQL0060W  The "C++" precompiler is in progress.
   11   SQL0008N  The token "LONGBIG" found in a host variable
                  declaration is not valid.
   18   SQL4942N  The statement selects an incompatible data type
                  into host variable ":col1_val".  SQLSTATE=42806
        SQL0095N  No bind file was created because of previous
                  errors.
        SQL0091W  Precompilation or binding was ended with "3"
                  errors and "0" warnings.

[db2inst1]/users/ganesh_gosavi/mig1/issues/NULL_issue>
      

These errors are raised because the DB2 precompiler cannot resolve the typedefs and macros that are used within the declaration section defined by the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION statements.

Issue 2

The second issue occurs with widely-used type definitions (typedef structures, typedef within nested structures, user defined data types, and typedefs declared within various nested header files) that are used within the declaration section of code.

The sample source code in Listing 3 illustrates this issue.

				
#include <stdio.h>
#include <string.h>
.
.

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
#include "extrndef.h"
	EXEC SQL INCLUDE 'UPR_ELEMENTS.H';

	EXTERN varchar b2k_amount_host_str[50][35];
	static varchar cur_time[20];
	static varchar cur_user[16];


	static char cur_time[20];
	static char cur_user[16];
EXEC SQL END DECLARE SECTION; 
	

Solutions to issues 1 and 2

One general solution to the issues described above is to avoid having typedefs and macros within the EXEC SQL declaration section. But if you have many such files, and the manual effort to do this could potentially be prohibitive, you will want to automate the task. Here we try to reduce your some of your burden by providing a Perl script (esql_prep), which helps you to make this task much easier.

Note: The primary developer of this script is Knut Stolze (stolze@de.ibm.com). Please take precautions when using this script and, as usual, test its proper functioning first before applying it to your source code. It is possible that you have some special situations that are not yet covered by the script, causing it to produce incorrect results.

The syntax for using this Perl script is as follows:

	
./esql_prep xxxxxxx.SQX "gcc -E -I<path>"

Here,

• "path" is either the relative or absolute path for your header (.h) include files.
• "xxxxxxx.SQX" is a file that has problems as described in Issue 1 and Issue 2.
• The "gcc -E" option tells the C++ compiler to pre-process the "xxxxxxx.SQX" file.

The above command pre-processes the file xxxxxxx.SQX and generates a new file, xxxxxxx.SQC. In this xxxxxxx.SQC file, you will see the replacement performed for your predefined macros and user-defined types. The xxxxxxx.SQC file can then be passed to the db2 prep precompiler for further execution.

Issue 3

When you pass your embedded SQL program (for example, test3.sqc or test3.sqC) to the db2 prep precompiler, the output of this process is two files:

• A modified C or C++ program (test.c or test.C)
• A bind file (test.bnd)

When you pass test3.c or test3.C to the language compiler (C/C++), sometimes it returns an error similar to the following:

"test3.i",line 6043.38:1540-0274 (S)The name lookup for "NULL" did not find a declaration.
	

This error occurs because the precompiler inserts statements similar to the following when it creates the C files:

.
.
sql_setdlist[0].sqldata = (void*)&col1_val;
#line 6043 "test3.i"
      sql_setdlist[0].sqlind = 0L;
#line 6043 "test3.i"
      sqlasetdata(3,0,1,sql_setdlist,NULL,0L);
    }	

What happens here is:

1. The esql_prep above runs the source code (still with embedded SQL statements) through the C/C++ precompiler. As a result, all #include directives are resolved, including the one for <stdio.h>.
2. The DB2 precompiler inserts the statements, as shown in the listing above, including the "NULL."
3. Now the regular C/C++ compiler kicks in. It runs a regular C/C++ precompile. But that precompilation does not do anything because everything was already done in step 1. In particular, there are no #include directives. As a result, the "NULL" remains unchanged in the code. The C/C++ compiler finds that "NULL" and complains about it because it does not know what to do at that point. After all, the "#define NULL (void *)0" is not there, because no #include <stdio.h> is found.
4. Therefore, it is necessary to provide a regular #define for NULL.

Hence, db2 prep precompiler returns with the error in Listing 4.

Solution to Issue 3

In order to eliminate this error, use the -D option of the language compiler.

For example:

	
/usr/vacpp/bin/xlC  -c   -o test3.o -DNULL=0   -I./  -I/db2/db2inst1/sqllib/include

Back to top

Task 2: Achieving Oracle UDF behavior in DB2 for Linux, UNIX, and Windows

This is the major challenge you face while migrating your Oracle application to DB2 for Linux, UNIX, and Windows. Your application makes calls to various Oracle supported UDFs. It is very difficult to go each and every place in potentially millions of lines of source code spread across various files and directories. Going and finding each location and then changing the source code accordingly so that your application supports DB2 can be a tedious and cumbersome, error-prone job.

The download provided with this article, OracleToDB2UDFs.zip, provides you with many of the Oracle equivalent UDFs. You can just register these under a schema of your choice. You can then start using these UDFs without modifying your application source code.

Here are the provided UDFs :

• || or CONCAT
  This UDF accepts various types of parameters, including:
  
  • ||(varchar, varchar)
  • ||(varchar, int)
  • ||(int,int)
  • ||(int, varchar)
  • ||(decimal,varchar)
  • ||(varchar, int)
  • ||(varchar, timestamp)
  • ||(timestamp , varchar)
  
  

  You can register this UDF either with symbolic name || or with the function name concat or both, depending on what is being used in your application source code.

  The registration process is illustrated below only for this function. The same process must be followed for the other UDFs as well.

  The functions for this UDF are provided in the file (concat.db2). If you wanted to have additional support of different parameters, you can add that function with the appropriate parameters in the CONCAT.db2 file.

  Assume that you wanted to register the UDFs under the schema or user TBAADM. You would enter these commands:

  1. db2 connect to sample user TBAADM using PASSWORD
  2. db2 -td/ -vf concat.db2

  Note: Here, '/' is used as a delimiter inside the CONCAT.db2 file to separate the functions. (Using / as a delimiter is unusual in DB2. In DB2, most often the delimeters '!', '@', '#', and ';' are used as statement terminators. But since Oracle uses '/', I have taken here '/' as a statement terminator in the examples and scripts).

  This step registers all the functions (|| with various parameters shown above) under the schema TBAADM. Then the user application starts using these newly registered functions with appropriate parameters, provided that you connected with the proper user or you set the correct CURRENT SCHEMA to use).

  
  
  Listing 8. Example 1: || with input parameters (int, int)
  

  >db2 values "1234 || 4567" 1 ------------------------------ 12344567 1 record(s) selected.

  
  
  
  Listing 9. Example 2: || with input parameters (int, varchar)
  

  >db2 "values 1234 || ' Ganesh'" 1 ------------------------------ 1234 Ganesh 1 record(s) selected.

  
  
  
  Listing 10. Example 3: || with input parameters (varchar, varchar)
  

  >db2 "values 'ganesh_gosavi' || '@yahoo.com'" 1 ----------------------- ganesh_gosavi@yahoo.com 1 record(s) selected.

  
  

  The same process has to be followed for registering the below mentioned Oracle equivalent functions under DB2 so that your application starts using them without modifying the single source code statement that makes calls to these Oracle functions.

  Note: The IBM Migration Toolkit (MTK) also provides these functions. The reason for providing these functions here is to support different types of parameters and also different combinations of parameters that a function can accept. An appropriate function gets called depending upon the number of parameters and the type of parameters.

• CHR
  

  This UDF accepts integer, float types of parameters:
  

  • CHR(float)

• HEXTORAW
  

  This UDF accepts a single varchar parameter:

  • HEXTORAW (varchar)
• RAWTOHEX
  

  This UDF accepts a single parameter:

  • RAWTOHEX (varchar for bit data)
• INSTR
  

  This UDF accepts following types of parameters:

  • INSTR (varchar, varchar, integer, integer)
  • INSTR (varchar, varchar, integer)
  • INSTR (varchar, varchar)
• LAST_DAY
  

  This UDF accepts following two types of parameters:

  • LAST_DAY (date)
  • LAST_DAY (timestamp)
• RPAD and LPAD
  

  This UDF accepts following types of parameters:

  • RPAD (integer, integer)
  • RPAD (integer, integer, varchar)
  • RPAD (varchar, integer)
  • RPAD (varchar, integer, varchar)
  • LPAD (integer, integer)
  • LPAD (integer, integer, varchar)
  • LPAD (varchar, integer)
  • LPAD (varchar, integer, varchar)
• TRIM
  

  This UDF accepts following types of parameters:

  • TRIM (varchar, varchar, varchar)
  • TRIM (varchar, varchar)
  • TRIM (varchar)
• RTRIM and LTRIM
  

  This UDF accepts following types of parameters:

  • LTRIM (varchar, varchar)
  • RTRIM (varchar, varchar)
• MONTHS_BETWEEN
  

  This UDF accepts following types of parameters:

  • MONTHS_BETWEEN (date, date)
  • MONTHS_BETWEEN (timestamp, timestamp)
• NEXT_DAY
  

  This UDF accepts following types of parameters:

  • NEXT_DAY (date, varchar)
  • NEXT_DAY (timestamp, varchar)
• NVL
  

  This UDF accepts following types of parameters:

  • NVL (varchar, varchar)
  • NVL (bigint, bigint)
  • NVL (varchar, bigint)
  • NVL (bigint, varchar)
  • NVL (decimal, varchar)
  • NVL (decimal, decimal)
  • NVL (float, float)
  • NVL (timestamp, timestamp)

  Note: DB2 Viper 2 (DB2 9.5) introduces this NVL functionality as well.

• TO_CHAR
  

  This UDF accepts following types of parameters:

  • TO_CHAR (decimal, varchar)
  • TO_CHAR (float)
• TO_DATE
  

  This UDF accepts following types of parameters:

  • TO_DATE (varchar, varchar)
  • TO_DATE (varchar)
  • TO_DATE (varchar,varchar, timestamp)
• TO_NUMBER
  

  This UDF accepts following types of parameters:

  • TO_NUMBER (varchar, varchar)
  • TO_NUMBER (varchar)
• TRUNC
  

  This UDF accepts following types of parameters:

  • TRUNC (timestamp, varchar)
  • TRUNC (timestamp)

Back to top

Task 3: Achieving Oracle DECODE behavior in DB2 for Linux, UNIX, and Windows

Oracle's DECODE() function has the functionality of an IF-THEN-ELSE statement.

DB2 does not support the DECODE function as it is. DB2 has the CASE expression, which is equivalent to Oracle's DECODE function. So if your application (embedded SQL programs) uses the DECODE function, you need to convert all your DECODE statements to the equivalent (syntactically correct) DB2 CASE structure. Once you do this, your programs and application will support DB2.

You must be worried now, thinking about the millions of lines of source code spread across multiple files and then under multiple directories. How can you go to each and every individual file and start replacing all such DECODE function calls with DB2's equivalent CASE expressions? Too much manual effort, right?

Don't worry! We have broken down this task to help reduce your effort and make your life simple by providing you a Perl script, replace_decode.

This Perl script (replace_decode) takes a source code (especially C++) file or directory (assuming .PXX or .CXX or .SQL which is an embedded SQL program). You can modify this Perl script to support the desired extension of your choice. This Perl script discovers all the DECODE function calls in your source code and automatically replaces it with DB2's equivalent CASE expressions.

For example:

SELECT DECODE(MAX(TO_NUMBER(SRL_NUM)),NULL,0,MAX(TO_NUMBER(SRL_NUM))) FROM DSACH WHERE 
DSA_ID = '%s' AND PRODUCT_CODE = '%s' AND CRNCY_CODE = '%s' AND COMM_EVENT_ID = '%s'

SELECT CASE when MAX(TO_NUMBER(SRL_NUM)) is NULL THEN 0 ELSE MAX(TO_NUMBER(SRL_NUM))  END
FROM DSACH WHERE DSA_ID = '%s' AND PRODUCT_CODE = '%s' AND CRNCY_CODE = '%s' AND 
COMM_EVENT_ID = '%s'

Using replace_decode.pl

Invoke this script using the following syntax:

	
perl replace_decode.pl --dir <source dir> --file <source file> --help

Note:

• Either --dir or --file should be specified.
• Both dir and file can be specified together. The - .cxx & .pxx files in the dir and the files mentioned will be processed.
• If both --dir and --file are skipped, or if --help is specified, usage will be displayed.
• You can specify multiple files by repeating the --file option:
  
  Listing 14. Repeating the --file option
  

  perl replace_decode.pl --dir dir1 --file src_file1 --file src_file2 --file src_file3

  
  
• The directory and file names can be specified with absolute paths or relative to the current directory.

Note: The primary developer of this script is Renu Pinky Sumam (renusuma@in.ibm.com). Please take precautions when using this script and, as usual, test its proper functioning first before applying it to your source code. It is possible that you have some special situations that are not yet covered by the script, and therefore may produce incorrect results.

Back to top

Task 4: Converting Oracle SQL statements containing NOWAIT

The ORACLE select-for-update statement allows a NOWAIT clause to prevent the application from being blocked if it cannot obtain the locks required.

For example, when the keyword NOWAIT is used to the FOR UPDATE clause, it tells Oracle not to wait if the table has been locked by another user.

DB2 does not support the NOWAIT clause because all read access (except for uncommitted read transactions) in DB2 result in acquiring a shared lock on the selected rows. DB2 also has a deadlock detector, and if any deadlock occurs, it automatically selects a transaction for rollback, and allows the other transaction to proceed.

The NOWAIT clause can be simulated in DB2 by setting the database configuration parameter LOCKTIMEOUT to 0. LOCKTIMEOUT is the interval before the deadlock detector wakes up to check for deadlocks in the system. By doing so, DB2 returns immediately to the application with an sqlcode -911 and sqlstate 40001 if the locking resource is blocked. Note that LOCKTIMEOUT affects all applications connected to the database.

At the database level, you can implement it as follows:

	
DB2 UPDATE DB CONFIGURATION FOR <DBNAME> USING LOCKTIMEOUT 0

At the transaction connection level, you can implement it as follows. Use one of the following commands:

	
SET CURRENT LOCK TIMEOUT NOT WAIT

or

	
SET CURRENT LOCK TIMEOUT <time in seconds>

DB2 waits at most for 30 seconds to obtain a lock. If the lock cannot be acquired in that time, SQLCODE -911 is raised to tell the user or application to try again.

Back to top

Task 5: Modification of host variables having size > 32672

If your application declares any CHAR or VARCHAR host variable whose size exceeds 32672, the db2 prep precompiler does not compile the embedded SQL program and returns an error.

For example, consider the following test4.sqc program

#include  <stdio.h>

EXEC SQL INCLUDE SQLCA;

int test_ArraySize(void)
{
        EXEC SQL BEGIN declare SECTION;
        char ResumeData[65533];
        EXEC SQL END declare SECTION;

        EXEC SQL select RESUME
        INTO    :ResumeData
        FROM EMP_RESUME
         WHERE  RESUME_FORMAT = 'ascii';

        return SUCCESS;
}

If you try to compile this program with db2 prep precompiler as follows, it will return an error.

db2 prep test4.sqc

LINE    MESSAGES FOR test4.sqc
------  --------------------------------------------------------------------
        SQL0060W  The "C" precompiler is in progress.
    8   SQL0314N  The host variable "ResumeData" is incorrectly
                  declared.
        SQL0092N  No package was created because of previous
                  errors.
        SQL0091W  Precompilation or binding was ended with "2"
                  errors and "0" warnings.

The error occurs because DB2 string literals must not be longer than 32672 bytes. Everything longer must be represented as a CLOB data type.

In order to resolve the above error, you need to use the following syntax for host variables having a size larger than 32672:

EXEC SQL BEGIN declare SECTION;
 	SQL TYPE IS CLOB (65533) ResumeData;
EXEC SQL END declare SECTION;

This allows you to proceed further as follows:

db2 prep test4.sqc

LINE    MESSAGES FOR test4.sqc
------  --------------------------------------------------------------------
        SQL0060W  The "C" precompiler is in progress.
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "0" warnings.

Caution: While the DB2 precompilation succeeds now, you will get compile problems because the initial char *ResumeData has been converted to struct { int length; char *data; } ResumeData;. You have to manage the data and length attributes, and that most likely requires code changes.

Back to top

Task 6: Modification of SQL statements having SELECT FOR UPDATE clauses

If your application contains SQL statements that use the SELECT FOR UPDATE clause, you may receive an error:

	
SQL0511N The FOR UPDATE clause is not allowed because the table specified by the cursor
 cannot be modified.  
	SQLSTATE=42829

For example, consider the following SQL statement:

EXEC SQL declare MY_CUR CURSOR for select
	col1_main_system,
 	col2_sub_system,
 	TO_CHAR(col3_system_date, 'DD-MM-YYYY HH24:MI:SS'),
 	col4_mainclass_user,
  	col5_subclass_user,
  	TO_CHAR(col6_userclass_date, 'DD-MM-YYYY HH24:MI:SS'),
  	col7_chnageUserId,
  	TO_CHAR(col8_Change_Time, 'DD-MM-YYYY HH24:MI:SS'),
  	col9_create_userid,
  	TO_CHAR(col10_create_time, 'DD-MM-YYYY HH24:MI:SS'),
  	NVL(col11_count,0),
  	col12_reasoncode,
  	col13_createflag,
  	col14_deleteflag,
  	col15_maxSerialNo,
  	col16_training_type,
  	col17_training_id
 FROM 	CLASS_DETAIL_TABLE
 WHERE 	col18_type = :HIS_type 
  		AND col19_id = :HIS_id 
 for update;

This returns the following error code:

23819   SQL0511N  The FOR UPDATE clause is not allowed because the
                  table specified by the cursor cannot be modified.
                  SQLSTATE=42829

DB2 does not know how to deal with the function calls in the SELECT list and cannot determine what to do if you want to update columns in the result set. In such cases, you may want to revise your SQL statement as follows:

EXEC SQL declare MY_CUR CURSOR for select
	col1_main_system,
 	col2_sub_system,
 	TO_CHAR(col3_system_date, 'DD-MM-YYYY HH24:MI:SS'),
 	col4_mainclass_user,
  	col5_subclass_user,
  	TO_CHAR(col6_userclass_date, 'DD-MM-YYYY HH24:MI:SS'),
  	col7_chnageUserId,
  	TO_CHAR(col8_Change_Time, 'DD-MM-YYYY HH24:MI:SS'),
  	col9_create_userid,
  	TO_CHAR(col10_create_time, 'DD-MM-YYYY HH24:MI:SS'),
  	NVL(col11_count,0),
  	col12_reasoncode,
  	col13_createflag,
  	col14_deleteflag,
  	col15_maxSerialNo,
  	col16_training_type,
  	col17_training_id
 FROM 	CLASS_DETAIL_TABLE
 WHERE 	col18_type = :HIS_type 
  		AND col19_id = :HIS_id 
 for update of
	col1_main_system,
 	col2_sub_system,
 	col3_system_date,
 	col4_mainclass_user,
  	col5_subclass_user,
  	col6_userclass_date,
  	col7_chnageUserId,
  	col8_Change_Time,
  	col9_create_userid,
  	col10_create_time, 
  	col11_count,
  	col12_reasoncode,
  	col13_createflag,
  	col14_deleteflag,
  	col15_maxSerialNo,
  	col16_training_type,
  	col17_training_id;

Back to top

Task 7: Command line utilities to move data from Oracle objects to DB2 objects

In the download OracleToDB2_DataMigrationScripts.zip, command line utilities and scripts are provided that you can use to migrate your Oracle data to DB2.

This tool allows you to move the table data from the Oracle database to the DB2 database. Therefore, you can test your DB2 ported application by running it against equivalent DB2 database tables containing valid data. This tool mainly provides you two utilities:

• geninput: This tool or script is run against the Oracle database and extracts the object (tables) names and prepares a list of object names. This list is later used by the unload utility to extract the data from the objects that are specified in the object list.
• unload: This script extracts the data from the specified database for the objects that are mentioned in the object list (This object list is prepared by the first utility — geninput).

Since this tool uses the JDBC universal driver, it can be used for any other database, provided it is tested with that database. For this article, it was successfully tested with an Oracle database. For more information about this tool, refer to Migrate from MySQL or PostgreSQL to DB2 Express-C (developerWorks, June 2006).

Briefly, here are the steps for using this tool:

1. Make sure that IBMExtract.jar and ojdbc14.jar (Oracle JDBC driver) is in your classpath. For example (on UNIX):
   
   
   Listing 26. Classpath
   

   export CLASSPATH=/home/db2inst1/java/IBMExtract.jar:/home/db2inst1/ java/ojdbc14.jar:$CLASSPATH

   
   
2. Make sure that you use the correct version of IBM JRE for either 1.4.2 or 1.5 for the target machine, and export its PATH so that the tool knows how to locate Java™.
   
   
   Listing 27. Exporting the path
   

   export PATH=/opt/IBMJava2-142/jre/bin:$PATH

   
   
3. There are two steps in running the tool. The first step (run geninput) is to create a file that is an input to the tool to unload the data for the tables that you need. This file has entries for all tables from your Oracle database, so go ahead and manually delete those entries that you do not want for the migration. If you do not do this, you will migrate all tables, including the Oracle system tables.
   
   Listing 28. Creating the input for migration
   

   [db2inst1@student java]$ cat geninput #!/bin/bash if [ "$1" = "" ] ; then echo Usage : geninput dbname exit 1 fi DBVENDOR=oracle DB2SCHEMA=$1 SERVER=192.69.79.92 DATABASE=$1 PORT=1521 DBUID=ilmsdb DBPWD=pass java -DINPUT_DIR=/work/java -cp $CLASSPATH ibm.GenInput $DBVENDOR $DB2SCHEMA $SERVER $DATABASE $PORT $DBUID $DBPWD

   
   
4. The second step is to actually run the tool (run unload).
   
   
   Listing 29. Running the unload
   

   [db2inst1@student java]$ cat unload #!/bin/bash if [ "$1" = "" ] ; then echo Usage : unload dbname exit 1 fi TABLES=/work/java/input/$1.tables COLSEP=\~ DBVENDOR=oracle NUM_THREADS=5 SERVER=192.69.79.92 DATABASE=$1 PORT=1521 DBUID=ilmsdb DBPWD=pass GENDDL=true UNLOAD=true FETCHSIZE=100 java -DOUTPUT_DIR=/work/java/output/$1 -cp $CLASSPATH ibm.GenerateExtract $TABLES $COLSEP $DBVENDOR $NUM_THREADS $SERVER $DATABASE $PORT $DBUID $DBPWD $GENDDL $UNLOAD $FETCHSIZE

   
   

After you unload the data, you can go to the output directory and see all the scripts that it creates for you, including a shell script that you can run to do the data migration.

This tool is intended only for the data migration and not the migration of other objects such as stored procedures and triggers. For that purpose, you should use the Migration Toolkit as usual. This tool is not a substitute for the Migration Toolkit.

Note: This is not an IBM supported tool, so please do not expect to get a full or dedicated support for this tool.

You can run the above tool on any platform provided you have IBM Java for that platform and use proper shell scripts to run the tool. The example script given here was for Linux. For Windows, see the link for the windows shell scripts.

Back to top

Summary

This article provided you with an overview of some of the technical challenges you can encounter when migrating an Oracle-based application to support DB2 for Linux, UNIX, and Windows, and gave you tools for resolving them. The UDFs provided are not tested against the performance in real production scenarios. These UDFs can be written in C/C++ language to potentially improve performance. Ideally, you should be using these UDFs for running a proof-of-concept, for your application to work with DB2. You can add more functions to the respective provided UDF files, in order to support various input and output parameters based on your needs.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Back to top

Downloads

Information about download methods

Resources

Learn

• "DB2 Application Development Guide": Find information on developing applications for DB2 for Linux, UNIX, and Windows.
  
  
• "Oracle to DB2 Conversion Guide for Linux, UNIX, and Windows": Get step-by-step guidance on migrating from Oracle to DB2.
  
  
• "Migrate from MySQL or PostgreSQL to DB2 Express-C" (developerWorks, June 2006): Find scripts for data migration that can also be used to migrate from Oracle.
  
  
• "DB2 Viper 2 compatibility features" (developerWorks, July 2007): Read about the compatibility between Oracle and DB2 9.5.
  
  
• developerWorks resource page for DB2 for Linux, UNIX, and Windows: Read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• IBM Migration Toolkit: Migrate your data from a wide variety of source databases to either DB2 or Informix® Dynamic Server.
  
  
• Download a free trial version of DB2 9.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Ganesh R Gosavi has a broad range of experience in C, C++, UNIX, VC++, and DB2 for Linux, UNIX, and Windows. He started his work with DB2 when he joined IBM Software Labs, India as a Senior DB Consultant (Staff Software Engineer), working on DB2 and supported all Asia Pacific customers and business partners. He works with business partners and specializes in providing mission-critical DB2 for Linux, UNIX, and Windows solutions, architecting the solutions, application performance tuning, migration, application porting, and development. Prior to working for IBM, Ganesh was recognised for his contribution in BMC Software's SmartDBA Performance Solution for DB2 UDB, V3.0 release.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Task 1: Identify embedded SQL (.sqC) programs
• Task 2: Achieving Oracle UDF behavior in DB2 for Linux, UNIX, and Windows
• Task 3: Achieving Oracle DECODE behavior in DB2 for Linux, UNIX, and Windows
• Task 4: Converting Oracle SQL statements containing NOWAIT
• Task 5: Modification of host variables having size > 32672
• Task 6: Modification of SQL statements having SELECT FOR UPDATE clauses
• Task 7: Command line utilities to move data from Oracle objects to DB2 objects
• Summary
• Disclaimer
• Downloads
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers