Transfering Data from DB2 to EXASOL Part 1/2

You want to transfer data from DB2 LUW or DB2 zOS to your EXASOL Database?

 

There are 2 Options

  • Using Connections from within Exasol using DB2's JDBC Driver (requires License)
  • Using DB2 Export to del and using Exasol Import Statement.

In the first part I will focus on Connections.

 

Exasol Connection

You got a licence for "Generic JDBC connections". Great! 

(to be sure have a look in EXAoperation Software -> Licence)

 

This should be a piece of cake.

 


Configure the DB2 JDBC Driver

Login into EXAOperation and switch to Software -> JDBC

  • Click "Add" and fill in the necessary information
  • For both DB2 zOS and DB2 LUW the information is the same
  • Driver Class is com.ibm.db2.DB2Driver
  • Prefix is jdbc:db2
  • Click "Add"

Now Upload the driver jar files.

 

Select the newly created ID from the list (should be jdbc1) and click upload

 

Upload these files

  • db2jcc4.jar contains the Driver 

    If you have a Client Installation it's in the Installation Directory/java/db2jcc4.jar

  •  

    For DB2 LUW add db2jcc_license_cisuz.jar (License)
    It's in the same directory as the db2jcc4.jar

  • For DB2 zOS add db2jcc_license_cu.jar.
    This File is only available with a DB2 Connect Licence which you probably own if you have a mainframe 

 

Now it should look like this:

 


You only have to do this once. Now your DB2 Driver is ready to be used in any number of connections.

 

Now we will create the connection


Creating Connection to DB2

Log into EXAPlus or DBeaver.

 

Now you have to create a connection object in EXASOL.

 

I provide a sample for DB2 LUW and DB2 zOS.

 


--Mainframe
create or replace connection DSN1 to
'jdbc:db2://dsn1.prdplex1:540/DSN1:clientUser=EXASOL;clientWorkstation=TEST_DB;clientProgramName=EXASOL_IMPORT;' 
user 'hostuid' identified by 'hostpassword';
--DB2 LUW
create or replace connection DB2LUW to 
'jdbc:db2://db2admin:60000/SAMPLE:clientUser=EXASOL;clientWorkstation=TEST_DB;clientProgramName=EXASOL_IMPORT;' 
user 'luwuid' identified by 'luwpassword';

This is an advanced configuration as we provide extra information for the target systems by using the user accounting fields.

 

In our shop we use these fields for resource accounting (zOS) and workload management (LUW).

 

The "user" and "identified by" field are optional. If you don't provide a user and password you will have to provide this information on every import. 

 

If you use "Password Encrypt" (or another DB2 Authentication Mechanism) in your DB2 configuration you have to provide securityMechanism with the correct value.

 

Example with SecurityMechanism ( and a minimum set of options)

  • create or replace connection DB2LUW to 'jdbc:db2://db2admin:60000/SAMPLE:securityMechanism=13' ;

It's rather hard to get a mapping of securityMechanism Names to numbers but here you are

  • USER_ONLY_SECURITY = 4
  • CLEAR_TEXT_PASSWORD_SECURITY = 3
  • ENCRYPTED_USER_ONLY_SECURITY = 16
  • ENCRYPTED_PASSWORD_SECURITY = 7
  • ENCRYPTED_USER_AND_PASSWORD_SECURITY = 9
  • KERBEROS_SECURITY = 11
  • ENCRYPTED_USER_AND_DATA_SECURITY = 12
  • ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY = 13
  • PLUGIN_SECURITY = 15
  • TLS_CLIENT_CERTIFICATE_SECURITY = 18

If nothing is provided then CLEAR_TEXT_PASSWORD_SECURITY is enabled which is also the default for every installation.

 

I really hope you are not using DATA_ENCRYPT. It's a outdated encryption algorithm which mainly only works with the IBM JRE.

 

Now we have connection Objects and we are ready for import


Import from a Connection

All examples are referring to the connection names created earlier (DB2LUW and DSN1). If you have other names change your statement accordingly.

 

First we test our connection by using the EXASOL import statement as nested table expression to select some rows from the db2 catalog tables

--Mainframe 
select * from ( import from jdbc at DSN1 
statement 'select creator,name from sysibm.systables where creator = ''SYSIBM'' fetch first 10 rows only' ) ;

--DB2 LUW 
select * from ( import from jdbc at DB2LUW 
statement 'select tabschema,tabname from syscat.tables where tabschema = ''SYSCAT'' fetch first 10 rows only' ) ;

If you didn't make any mistakes you should receive 10 rows form the db2 catalog tables.

 

Please keep in mind that EXASOL does not support Binary nor Lob Objects. So you might receive an "JDBC SQL Type x is not known" error.

 

Now you are ready to import the data

Known Limitations

  • Exasol does not support Binary Data (BLOB, CHAR FOR BIT DATA) - don't try to import such columns
  • Exasol has no TIME Data Time. You will have to cast it to CHAR(8) in the SELECT Statement
  • Exasol Timestamp precision is different to DB2. Exasol has 3 (milliseconds) and DB2 6 (microseconds)
    You might loose data or uniqueness. Workaround: cast to CHAR(26)
  • The Connector does not provide correct meta data in the SELECT Statement (as of Version 5.0.16)
    You might get the idea to just create a table using an import:
    CREATE TABLE AS SELECT * FROM (import from jdbc at DB2LUW statement 'SELECT ....')
    Don't. Every Column Except Date and Timestamp will be defined as VARCHAR(2000000). This is a defect in the Connector that will be fixed with Exasol V6 (should be released in 2016)

Write a comment

Comments: 0