How to configure Sybase ETL to access data from Oracle 10g and insert into a Sybase IQ server

This paper demonstrates how to insert data into IQ from Oracle Server, using ETL. It shows how to set up the ETL environment using ETL Development running on a Windows PC and ETL Server running on a Linux box.

ETL will be using two components; one to define the source as  DB Data Provider - Full Load and one for the target as  DB Bulk Load - Sybase IQ .

Software Requirements:  

The following software will be used in setting up the configuration to access data from Oracle and load into IQ Server via use of ETL:

- Sybase ETL v 4.9  - The ETL program has two components; ETL Development is used to define the GUI project and ETL Server is the execution engine to access the data from the source database, apply any transformations, and load into a target database.

-  Sybase IQ v15.1  -  An IQ database will be used as a target to store the data that comes from Oracle.

- Oracle Server  10g  -

- Oracle Client  v.11 - will be used to access Oracle natively as a data source.

- Sybase ASA v 11  - An ASA database will be the repository for the  ETL project .

The components are distributed in the following environments :

a.) On the Windows PC   :  

 - ETL Development  4.9

- Oracle Server 10g

- Oracle client - - will be used to access ORACLE natively

-  DSN entry to access IQ via ODBC using SYBASE IQ driver, called candil_iq_iqdemo.

b.) On the Linux system:

-  ETL Server : v 4.9 

-  IQ  v15.1  -

 -  ASA repository  v 11.-

- Oracle Client - will be used to access ORACLE natively

- DSN entry to access IQ via ODBC using SYBASE IQ driver, called candil_iq_iqdemo

Prerequisites :

Oracle Client :

 In this configuration, as the Oracle database will be accessed using the Oracle native driver, it is assumed that the Oracle client already was installed on both the Windows and Linux side.

 In this set up, the  Oracle client was installed on a Windows box in the directory   :

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

On Linux , it was installed in the directory : /c11574411/mcandil/product/11.1.0/db_1

Make sure that the tnsnames.ora file exists in the Oracle client installation under the Network/Admin directory  like this :

-          on Windows : C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

-          on Linux : : /c11574411/mcandil/product/11.1.0/db_1/network/admin

[ Sample of definition of tnsnames.ora file ]

# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\NETWORK

\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 158.78.41.143)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

Where HOST = 158.78.41.143 is the IP address of the box where Oracle Server is installed and  PORT = 1521  indicates the port number to access Oracle Server.

As target database, it will use IQ v15.1 ( 64 bits) that will be accessed via SYBASE ODBC driver.

DSN entry for ODBC must be defined on Windows and on Linux  box.

On the Linux box, it will be necessary to create an .odbc.ini File.

It should be created in the $ HOME_user  directory,  which for the sample is :  /usr/u/mcandil

[ Sample of definition  of .odbc.ini file ]

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

; This is only a sample .odbc.ini file

; IT WILL NOT WORK AS IS.

; You need to make a copy of this file

; into the user's home directory and modify

; the DSNs to your need:

;

; cp odbc.ini.sample ~/.odbc.ini

; vi ~/.odbc.ini

;

; You also need to modified the default

; value of your ODBCINI environment variable.

; It should be set to point to your local

; copy of the .odbc.ini file:

; export ODBCINI=~/.odbc.ini

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[ODBC Data Sources]

candil_iq_iqdemo=SQLAnywhere 11.0

[candil_iq_iqdemo]

Driver=libdbodbc11.so

uid=DBA

pwd=sql

EngineName=candil_iq_iqdemo

CommLinks=tcpip(host=redsun4;port=7777)

AutoStop=no

DatabaseName=iqdemo

;[ODBC]

;Trace=0

;TraceFile=odbctrace.out

;TraceDll=/c11574411/SQLAnywhere11/lib32/odbctrac.so

;InstallDir=/opt/sybase/SYBSsa8/drivers

;ConversionTableLocation=/opt/sybase/SYBSsa8/drivers/tables

To configure an entry in DSN for ODBC driver on Windows use this sequence of screens as a guide :

Setting up the others components of this environment :

The next steps are to set the environment variables and start each component of this configuration.

(a) To start  ASA db which is used as a repository of ETL project :

Install ASA v 11 which is shipped with ETL Server for Linux.

( a.1.) under ASA installation directory (/c11574411/SQLAnywhere11/), choose the bin32 programs to start the ASA db

(a.2) execute the script :  sa_config script to set the environment variables

/c11574411/SQLAnywhere11/bin32 : source sa_config.csh

 [mcandil@redsun4 bin32]$: dbsrv11 @rep.cfg demo_rep.db

[ASA console log ]:

EVALUATION SOFTWARE: SQL Anywhere Version 11.0

Copyright (c) 2001-2008, iAnywhere Solutions, Inc.

Portions copyright 1988-2008, Sybase, Inc. All rights reserved.

This software is licensed for evaluation purposes only pursuant to

the terms of the IANYWHERE NO-CHARGE 60 DAY SOFTWARE

EVALUATION LICENSE AGREEMENT (the 'Evaluation License'). The text

of this license is contained in the 'license.txt' file located

in the installation directory.

For more information about SQL Anywhere:

Internet:                 http://www.ianywhere.com

North America Toll Free:  1-800-8SYBASE

Please confirm your acceptance of these license terms:

I accept and agree to abide by the terms and conditions of the

"IANYWHERE NO-CHARGE 60 DAY SOFTWARE EVALUATION LICENSE AGREEMENT".

Enter 'Y' for Yes and 'N' for No:

Asynchronous IO disabled due to lack of proper OS support

SQL Anywhere Network Server Version 11.0.0.1264

Copyright (c) 2001-2008, iAnywhere Solutions, Inc.

Portions copyright (c) 1988-2008, Sybase, Inc. All rights reserved.

Use of this software is governed by the Sybase License Agreement. Refer to http:

//www.sybase.com/softwarelicenses

SQL Anywhere Evaluation Edition

4 logical processor(s) on 2 physical processor(s) detected.

Networked Seat (per-seat) model. Access to the server is limited to 3 seat(s).

This server is licensed to:

    Evaluation Copy

    Restricted Use

Running Linux 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:32:02 EDT 2006 on X86_64

Server built for X86 processor architecture

8192K of memory used for caching

Minimum cache size: 8192K, maximum cache size: 1834876K

Using a maximum page size of 4096 bytes

Starting database "demo_rep" (/c11574411/SQLAnywhere11/bin32/demo_rep.db) at Wed

 Jul 07 2010 10:42

Starting checkpoint of "demo_rep" (demo_rep.db) at Wed Jul 07 2010 10:42

Finished checkpoint of "demo_rep" (demo_rep.db) at Wed Jul 07 2010 10:42

Database "demo_rep" (demo_rep.db) started at Wed Jul 07 2010 10:42

Database server started at Wed Jul 07 2010 10:42

Trying to start SharedMemory link ...

    SharedMemory link started successfully

Trying to start TCPIP link ...

    TCPIP link started successfully

Now accepting requests

Press 'q' to shut down the database server

(b) to start IQ :

(b.1) execute the script : source /redsun41/candil/SYBASE.csh

 (b.2) start the IQ engine : start_iq @iqdemo.cfg iqdemo.db

[IQ console log ]

Starting server candil_iq_iqdemo on redsun4 at port 7777 (07/07 10:49:29)

Run Directory       : /redsun41/candil/IQ-15_1/demo

Server Executable   : /redsun41/candil/IQ-15_1/bin64/iqsrv15

Server Output Log   : /redsun41/candil/IQ-15_1/logfiles/candil_iq_iqdemo.0014.

vlog

Server Version      : 15.1.0.5034/ESD 2

Open Client Version : 15.0/P-EBF16082 ESD #15

User Parameters     : '@iqdemo.cfg' 'iqdemo.db'

Default Parameters  : -ti 4400 -gn 25

I. 07/07 10:49:32.      Sybase IQ

I. 07/07 10:49:32.       Version 15.1

I. 07/07 10:49:32.        (64bit mode)

I. 07/07 10:49:32. Copyright 1992-2009 by Sybase, Inc. All rights reserved

I. 07/07 10:49:32.

I. 07/07 10:49:32. 4 logical processor(s) on 2 physical processor(s) detected.

I. 07/07 10:49:32. Maximum number of physical processors the server will use:

I. 07/07 10:49:32. Running Linux 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:32:02 EDT

006 on X86_64

I. 07/07 10:49:32. Server built for X86_64 processor architecture

I. 07/07 10:49:32. 49152K of memory used for caching

I. 07/07 10:49:32. Minimum cache size: 49152K, maximum cache size: 262144K

I. 07/07 10:49:32. Using a maximum page size of 4096 bytes

I. 07/07 10:49:32. Starting database "iqdemo" (/redsun41/candil/IQ-15_1/demo/i

emo.db) at Wed Jul 07 2010 10:49

I. 07/07 10:49:33. Transaction log: iqdemo.log

=============================================================

IQ server starting with:

     10 connections         (       -gm )

     18 cmd resources       ( -iqgovern )

    267 threads             (     -iqmt )

    512 Kb thread stack size   (   -iqtss  )

  136704 Kb thread memory size ( -iqmt * -iqtss )

      4 IQ number of cpus  ( -iqnumbercpus )

      0 MB maximum size of IQMSG file ( -iqmsgsz )

      0 copies of IQMSG file archives ( -iqmsgnum )

=============================================================

I. 07/07 10:49:33. Starting checkpoint of "iqdemo" (iqdemo.db) at Wed Jul 07 2

0 10:49

I. 07/07 10:49:33. Finished checkpoint of "iqdemo" (iqdemo.db) at Wed Jul 07 2

0 10:49

I. 07/07 10:49:34. Database "iqdemo" (iqdemo.db) started at Wed Jul 07 2010 10

9

I. 07/07 10:49:34. IQ Server candil_iq_iqdemo.

I. 07/07 10:49:36. Database server started at Wed Jul 07 2010 10:49

I. 07/07 10:49:36. Trying to start SharedMemory link ...

I. 07/07 10:49:36.     SharedMemory link started successfully

I. 07/07 10:49:36. Trying to start TCPIP link ...

I. 07/07 10:49:41.     TCPIP link started successfully

I. 07/07 10:49:41. Now accepting requests

New process id is 27049

Server started successfully

(c) for ETL Server start  : v 4.9:

(c.1.) set the ORACLE_HOME variable which points to the ORACLE Client :

 setenv ORACLE_HOME /c11574411/mcandil/product/11.1.0/db_1

(c.2) set the LD_LIBRARY_PATH variable which point to the Oracle libraries :

 setenv LD_LIBRARY_PATH /c11574411/mcandil/product/11.1.0/db_1/lib32

*Note*: if ORACLE_HOME was not set when the ETL Server was installed you should set it and re-run the configure.sh script found in the ETL directory.

(c3) start the ETL Server: [mcandil@redsun4 etl]$

./GridNode.sh

[ETL server log ]

GridNode (4.9.0.29699)

GridNode

INFO  : Application user data path has been set to '/usr/u/mcandil/SYBASE/Sybase

 ETL Server/4.9'.

INFO  : Copying file '/c11574411/etl/etc/components_grid.ini' to '/usr/u/mcandil

/SYBASE/Sybase ETL Server/4.9/etc/components_grid.ini'.

INFO  : Sybase ETL Server V4.9.0.29699

INFO  : Copyright (c) 2003 - 2009 Sybase, Inc.  All rights reserved. Unpublished

 rights reserved under U.S. copyright laws. This product is the confidential and

 trade secret information of Sybase, Inc.  Use, duplication or disclosure of the

 software and documentation by the U.S. Government is subject to restrictions se

t forth in a license agreement between the Government and Sybase, Inc. or other

written agreement specifying the Government's rights to use the software and any

 applicable FAR provisions, for example, FAR 52.227-19.

INFO  : Running on Linux (x86_64) 2.6.9-42.ELsmp

Using ETL Development :

Use ETL Development to create the new project that will access Oracle natively and store the data into IQ via the ODBC driver.

On Windows, start ETL Development :

Open the Repository which is stored on Linux : here it is redsun4

 Define the ETL Server that is running on the Linux box. Here it is called  redsun4.

 Define a DB Data Provider Full Load ( source)  to access Oracle natively ;

Define the DB Bulk Load Sybase ID (destination) using the ODBC driver :

Executing the project :

Checking the result in the IQ database via DBISQL after Executing the ETL project : 

Using DBISQL access the IQ db, to check the data inserted by ETL -

The ETL logs are stored in :

[mcandil@redsun4 log]$ pwd

/usr/u/mcandil/SYBASE/Sybase ETL Server/4.9/log

Checking the ETL Server logs after executing the project  :

[ETL SERVER execution log ]

"2010-07-12 04:47:10";"Info";"EXEC-E19B6C93-6835-A648-8DCB-5A64266FD0C7";"PROJ-A

36700CD-6CA7-448F-AD01-BFF2D0E82A81";"";"Job 'ETLS_repositorio_linux ' started."

"2010-07-12 04:47:11";"Info";"C5819F0B-3E2F-934A-9837-D998071BB9C0";"";"PROJ-A36

700CD-6CA7-448F-AD01-BFF2D0E82A81";"Project 'ETLS_repositorio_linux ' started."

"2010-07-12 04:47:18";"Success";"C5819F0B-3E2F-934A-9837-D998071BB9C0";"";"PROJ-

A36700CD-6CA7-448F-AD01-BFF2D0E82A81";"Project 'ETLS_repositorio_linux ' execute

d successfully."

"2010-07-12 04:47:19";"Success";"EXEC-E19B6C93-6835-A648-8DCB-5A64266FD0C7";"PRO

J-A36700CD-6CA7-448F-AD01-BFF2D0E82A81";"";"Job 'ETLS_repositorio_linux ' executed successfully."

[ETL Server system log]

"2010-07-12 04:47:10";"Info";"EXEC-E19B6C93-6835-A648-8DCB-5A64266FD0C7";"PROJ-A

36700CD-6CA7-448F-AD01-BFF2D0E82A81";"";"Job 'ETLS_repositorio_linux ' started."

"2010-07-12 04:47:11";"Info";"C5819F0B-3E2F-934A-9837-D998071BB9C0";"";"PROJ-A36

700CD-6CA7-448F-AD01-BFF2D0E82A81";"Project 'ETLS_repositorio_linux ' started."

"2010-07-12 04:47:18";"Success";"C5819F0B-3E2F-934A-9837-D998071BB9C0";"";"PROJ-

A36700CD-6CA7-448F-AD01-BFF2D0E82A81";"Project 'ETLS_repositorio_linux ' execute

d successfully."

"2010-07-12 04:47:19";"Success";"EXEC-E19B6C93-6835-A648-8DCB-5A64266FD0C7";"PRO

J-A36700CD-6CA7-448F-AD01-BFF2D0E82A81";"";"Job 'ETLS_repositorio_linux ' execut

ed successfully."

[sybase@redsun4 log]$ ls

alert_2.log  execution_2.log  execution.old  system.log

alert.log    execution.log    system_2.log   system.old

[sybase@redsun4 log]$ tail system.log

2010-07-12 04:27:10 [15] INFO  (DispatchUdpDgram.cpp:182): UDP listener (IPv6) s

tarted on port 5124

2010-07-12 04:27:10 [16] INFO  (DispatchUdpDgram.cpp:178): UDP listener (IPv4) s

tarted on port 5124

2010-07-12 04:27:10 [17] INFO  (Dispatcher.cpp:140): Dispatcher service running

under 'svc'. Serving 4 protocols to 4 transports.

2010-07-12 04:27:10 [18] INFO  (NodeService.cpp:231): Node service running on 'r

edsun4'.

2010-07-12 04:27:10 [19] INFO  (NodeService.cpp:237): Announcing on all interfac

es with 1s interval.

2010-07-12 04:47:10 [20] INFO  (IQMultiplex.cpp:51): IQ Multiplex: engine guid =

 CE56257E-2281-2445-92D9-78A25881D452

2010-07-12 04:47:11 [21] INFO  (LocalProjectSession.cpp:81): Starting project 'E

TLS_repositorio_linux '.

2010-07-12 04:47:11 [22] INFO  (LocalProjectSession.cpp:100): Initializing proje

ct 'ETLS_repositorio_linux '.

2010-07-12 04:47:14 [23] INFO  (dboracle.cpp:332): Using conversions

2010-07-12 04:47:18 [24] INFO  (LocalProjectSession.cpp:233): Execution was successful for project 'ETLS_repositorio_linux '.

[sybase@redsun4 log]$

How to print the ETL project in HTML format :

This the HTML format of the ETL Project : which is stored in :

C:\Program Files\Sybase\ETLDevelop49\report

Sybase ETL Development - Project Documentation

ETLS_repositorio_linux

Generated: 2010-7-7 17:19:27.044

Overview

List of Components

DB Data Provider Full Load

DB Bulk Load Sybase IQ

DB Data Provider - Full Load

DB Data Provider - Full Load

DB Data Provider Full Load

List of Ports

This is an output port for query results

Properties

Oracle

ORCL

system

*****

select * from testoracle

1000

1

1

Port Details

OUT (OUT)

Connected to: DB Bulk Load - Sybase IQ - IN
Mapping: Default

DB Bulk Load - Sybase IQ

DB Bulk Load - Sybase IQ

DB Bulk Load Sybase IQ

List of Ports

This is an input port for data

Properties

ODBC

candil_iq_iqdemo

DBA

*****

0

DBA.receiveoracle

Insert

0

pipe://mypipe

0

1

0

00:00:00.000

0

1

Port Details

IN (IN)

Connected to: DB Data Provider - Full Load - OUT
Mapping: Default

List of Connections