How To
Summary
The page tells how to use Db2 and Oracle Instant Client for Microsoft Windows (x64) 64-bit connect to remote Oracle database and query tables.
Environment
Db2 and Oracle Instant Client for Microsoft Windows (x64) 64-bit
Steps
Step1: Install Oracle Instant Client on Windows
1.1 Download the Oracle Instant Client:
Go to the Oracle Instant Client download page. Locate the version 23.4 for Windows x64 and download
Basic package (instantclient-basic-windows.x64-23.4.zip),
SQL*Plus Package (instantclient-sqlplus-windows.x64-23.4.0.24.05.zip).
Basic package (instantclient-basic-windows.x64-23.4.zip),
SQL*Plus Package (instantclient-sqlplus-windows.x64-23.4.0.24.05.zip).
1.2 Extract the Zip File:
Once the download is complete, extract the zip file to a directory of your choice (e.g., C:\oracle\instantclient_23_4).
1.3 Install Visual C++ Redistributable:
The Oracle Instant Client requires the Microsoft Visual C++ Redistributable. If you don’t have it installed, download and install it from the Microsoft website.
1.4 Configure tnsnames.ora
Locate or Create the Network Admin Directory:
By default, tnsnames.ora should be located in the network/admin directory under your Oracle client installation directory.
For example, if you installed the Oracle Instant Client in C:\oracle\instantclient_23_4,
By default, tnsnames.ora should be located in the network/admin directory under your Oracle client installation directory.
For example, if you installed the Oracle Instant Client in C:\oracle\instantclient_23_4,
the tnsnames.ora file should be in C:\oracle\instantclient_23_4\network\admin. If this directory does not exist, create it.
Create/Edit tnsnames.ora:
Open or create tnsnames.ora in a text editor and add the connection details for your Oracle database. Here’s an example configuration:
ORACLEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sunny1.xxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = FREEPDB1)
)
)
1.5 Set Environment Variables
Set TNS_ADMIN Environment Variable:
The TNS_ADMIN environment variable tells Oracle clients where to find the tnsnames.ora file.
Open the Start Menu, search for "Environment Variables," and select "Edit the system environment variables."
In the System Properties window, click on the "Environment Variables" button.
Under "System variables," click "New" and add:
Variable name: TNS_ADMIN
Variable value: C:\oracle\instantclient_23_4\network\admin (adjust the path to your actual installation directory)
Click "OK" to close all windows.
Ensure PATH Environment Variable Includes Oracle Instant Client Directory:
Ensure the directory where you installed the Oracle Instant Client is included in the PATH environment variable.
In the "System variables" section, find the "Path" variable and click "Edit."
Click "New" and add the path to the directory where you extracted the Oracle Instant Client (e.g., C:\oracle\instantclient_23_4).
Click "OK" to close all windows.
Set Oracle Home (optional but recommended for some configurations):
In the "System variables" section, click "New."
Set the "Variable name" to ORACLE_HOME.
Set the "Variable value" to the path of the Instant Client directory (e.g., C:\oracle\instantclient_23_4).
Click "OK."
1.6 Test the Connection
Open a Command Prompt:
Open a new Command Prompt window (important to get the updated environment variables).
Open a new Command Prompt window (important to get the updated environment variables).
Use SQL*Plus to Test the Connection:
sqlplus J15USER1/J15USER1@ORACLEDB
If everything is set up correctly, you should be connected to the Oracle database.
Step2: Add a new folder lib in C:\oracle\instantclient_23_4, copy all files in instantclient_23_4 into lib.
C:\oracle\instantclient_23_4\lib
If we don't do this it will report following error.
db2 => create or replace nickname nk1_oracle for server1."J15USER1".alltype_test
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1822N Unexpected error code "-1" received from data source "SERVER1".
Associated text and tokens are "Do not have permission to read Oracle Client
Libraries". SQLSTATE=560BD
Step3: Modify file C:\Program Files\IBM\SQLLIB\cfg\db2dj.ini add following 4 parameters.
ORACLE_HOME=C:\oracle\instantclient_23_4\
DB2LIBPATH=C:\oracle\instantclient_23_4\lib
TNS_ADMIN=C:\oracle\instantclient_23_4\network\admin
NLS_LANG=American_America.UTF8
Step4: Restart Db2 and run create wrapper, server, user mapping and nickname commands
C:\Program Files\IBM\SQLLIB\BIN>db2stop force
07/16/2024 18:09:06 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
C:\Program Files\IBM\SQLLIB\BIN>db2start
07/16/2024 18:09:16 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
C:\Program Files\IBM\SQLLIB\BIN>db2
db2 => connect to sample
Database Connection Information
Database server = DB2/NT64 11.5.8.0
SQL authorization ID = ADMINIST...
Local database alias = SAMPLE
db2 => drop wrapper net8
db2 => create wrapper net8
db2 => create server server1 type oracle version 19 wrapper net8 authorization "J15USER1" password "J15USER1" options (node 'ORACLEDB')
db2 => create user mapping for user server SERVER1 options ( REMOTE_AUTHID 'J15USER1', REMOTE_PASSWORD 'J15USER1' );
db2 => set passthru server1
db2 => drop table alltype_test
db2 => create table alltype_test(col1_int integer, col2_smallint smallint, col3_double double precision, col4_char char(30), col5_varchar varchar(30), col6_date date, col7_time timestamp, col8_timestamp timestamp, col9_decimal numeric(10, 5))
db2 => insert into alltype_test values(1, 2, 3.1, 'hello4 h', 'hello world5 h', to_date('2020-02-14','yyyy-mm-dd'),'2020-09-17 10:44:27', '2020-09-17 10:44:28', 12.39)
db2 => insert into alltype_test values(1, 2, 3.1, 'hello24 abh', 'hello world25 ah', to_date('2020-02-14','yyyy-mm-dd'),'2020-09-17 10:44:27', '2020-09-17 10:44:28', 12.39)
db2 => CREATE INDEX idx_lower_name ON alltype_test (LOWER(col4_char))
db2 => CREATE INDEX idx_oracle ON alltype_test (col4_char)
db2 => set passthru reset
db2 => create or replace nickname nk1_oracle for server1."J15USER1".alltype_test
db2 => select count(*) from nk1_oracle
1
-----------
2
1 record(s) selected.
db2 => describe table nk1_oracle
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1_INT SYSIBM DOUBLE 8 0 Yes
COL2_SMALLINT SYSIBM DOUBLE 8 0 Yes
COL3_DOUBLE SYSIBM DOUBLE 8 0 Yes
COL4_CHAR SYSIBM CHARACTER 30 0 Yes
COL5_VARCHAR SYSIBM VARCHAR 30 0 Yes
COL6_DATE SYSIBM TIMESTAMP 10 6 Yes
COL7_TIME SYSIBM TIMESTAMP 10 6 Yes
COL8_TIMESTAMP SYSIBM TIMESTAMP 10 6 Yes
COL9_DECIMAL SYSIBM DECIMAL 10 5 Yes
9 record(s) selected.
db2 =>
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSFHEG","label":"DB2 Enterprise Server Edition"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Connectivity-\u003EFederation"}],"ARM Case Number":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
17 July 2024
UID
ibm17160468