How To
Summary
How to use Db2 Federation query SAP Sybase tables
Environment
Db2 for Linux and Db2 for AIX
Steps
Step1: Download SAP Sybase client and put it into the Db2 server that the Db2 instance user can access it.
search "SAP Adaptive Server Enterprise SDK",
NOTICE: Download the SDK file according to your Db2 server platform.
For example, if Db2 server is Linux on x86_64 64bit, you need to download 51047974.ZIP.
If Db2 server is AIX 64bit, you need to download 51047965.ZIP.
Unzip and install the SAP Adaptive Server Enterprise SDK
For example,
[root@tautog1 sap_sybase]# unzip 51047974.ZIP
Archive: 51047974.ZIP
[root@tautog1 sap_sybase]# ls
51047974.ZIP CDLABEL.ASC COPY_TM.HTM LABEL.ASC MD5FILE.DAT setup.bin ThirdPartyLegal VERSION.EBC
archives CDLABEL.EBC COPY_TM.TXT LABEL.EBC sample_response.txt SHAFILE.DAT VERSION.ASC
[root@tautog1 sap_sybase]# ./setup.bin
Where would you like to install?
Default Install Folder: /opt/sap
ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT:
The directory /opt/sap does not exist. Do you want to create it?
(Y/N): Y
Choose Install Set
------------------
Please choose the Install Set to be installed by this installer.
->1- Typical
2- Full
3- Customize...
ENTER THE NUMBER FOR THE INSTALL SET, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
: 1
Install Folder:
/opt/sap
Product Features:
Open Client,
DB-Library,
SAP ASE ODBC Driver,
SAP jConnect 16.0 for JDBC,
ASE Extension Module for Python,
ASE Database Driver for PERL,
ASE Extension Module for PHP,
Interactive SQL
Installation Complete
---------------------
Congratulations. SDK for SAP Adaptive Server Enterprise has been successfully
installed to:
/opt/sap
[root@tautog1 sap]# ls
charsets config DataAccess64 jConnect-16_0 jutils-3_0 log shared SYBASE.env SYBASE.sh
collate DataAccess DBISQL-16_0 jre64 locales OCS-16_0 SYBASE.csh Sybase_Install_Registry sybuninstall
[root@tautog1 sap]#
Step2: [Optional] Try to connect to Sybase database by using isql, this step is not a must for Db2 federation.
For example,
2.1 Prepare sybase interface
[db2inst2@tautog1 sybase]$ pwd
/home/db2inst2/sybase
[db2inst2@tautog1 sybase]$ cat interfaces
SYBASE16
master tcp ether test1.fyre.ibm.com 50000
query tcp ether test1.fyre.ibm.com 50000
2.2 Export the required environment variables
[db2inst2@tautog1 sybase]$ export SYBASE=/opt/sap
[db2inst2@tautog1 sybase]$ export SYBASE_OCS=OCS-16_0
2.3 use isql connect to the sybase database
[db2inst2@tautog1 ~]$ cd /opt/sap/OCS-16_0/bin
[db2inst2@tautog1 bin]$ ls
bcp bcp_r cobpre cobpre_r cpre cpre_r defncopy dscp ExportPrivateKey.jar isql64 isql_r64 sybhelp
bcp64 bcp_r64 cobpre64 cobpre_r64 cpre64 cpre_r64 defncopy_r dsedit isql isql_r pwdcrypt
[db2inst2@tautog1 bin]$ ./isql -S SYBASE16 -U j15user3 -P j15user3 -D sybase16db3
1>
Step3: Prepare Sybase configuration file interface and Db2 federation configuration file db2dj.ini,
put these files into /home/db2inst_path/sqllib/cfg
For example,
[db2inst2@tautog1 cfg]$ cat /home/db2inst2/sqllib/cfg/interfaces
SYBASE16
master tcp ether test1.fyre.ibm.com 50000
query tcp ether test1.fyre.ibm.com 50000
[db2inst2@tautog1 cfg]$ cat /home/db2inst2/sqllib/cfg/db2dj.ini
DB2LIBPATH=/opt/sap/OCS-16_0/lib
SYBASE=/opt/sap
SYBASE_OCS=OCS-16_0
Step4: Restart Db2 to load the federation configuration file db2dj.ini
[db2inst2@tautog1 ~]$ db2stop force
04/16/2024 08:35:16 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst2@tautog1 ~]$ db2start
04/16/2024 08:35:20 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst2@tautog1 ~]$
Step5: Create wrapper, server, user mapping, nickname and query the nickname.
connect to DEDB
Database Connection Information
Database server = DB2/LINUXX8664 11.5.8.0
SQL authorization ID = DB2INST2
Local database alias = SAMPLE
CREATE WRAPPER CTLIB
DB20000I The SQL command completed successfully.
CREATE SERVER server1 TYPE SYBASE VERSION '16.0' WRAPPER CTLIB AUTHORIZATION "j15user3" PASSWORD "xxxxx" OPTIONS (NODE 'SYBASE16',DBNAME 'sybase16db3',PUSHDOWN 'Y' ,DB2_MAXIMAL_PUSHDOWN 'Y')
DB20000I The SQL command completed successfully.
CREATE USER MAPPING FOR user SERVER server1 OPTIONS (REMOTE_AUTHID 'j15user3' ,REMOTE_PASSWORD 'xxxxx')
DB20000I The SQL command completed successfully.
set passthru server1
DB20000I The SQL command completed successfully.
drop table test1
DB20000I The SQL command completed successfully.
create table test1 (col1 int, col2 numeric(5,2), col3 char(20), col4 varchar(20))
DB20000I The SQL command completed successfully.
insert into test1 values(1, 2.1, 'CharData1', 'VarCharData2')
DB20000I The SQL command completed successfully.
insert into test1 values(2, 3.1, 'CharData2', 'VarCharData3')
DB20000I The SQL command completed successfully.
set passthru reset
DB20000I The SQL command completed successfully.
create or replace nickname nk_test1 for server1."j15user3"."test1"
DB20000I The SQL command completed successfully.
describe table nk_test1
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1 SYSIBM INTEGER 4 0 Yes
COL2 SYSIBM DECIMAL 5 2 Yes
COL3 SYSIBM VARCHAR 20 0 Yes
COL4 SYSIBM VARCHAR 20 0 Yes
4 record(s) selected.
select * from nk_test1
COL1 COL2 COL3 COL4
----------- ------- -------------------- --------------------
1 2.10 CharData1 VarCharData2
2 3.10 CharData2 VarCharData3
2 record(s) selected.
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":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
17 April 2024
UID
ibm17148362