IBM Support

How to query Informix by using Db2 Federation through Informix native wrapper?

How To


Summary

This document tells steps of querying Informix by using Db2 Federation through Informix native wrapper

Environment

Db2 in linuxamd64 platform

Steps

1. Download Informix Client SDK and put it in the db2 server, for example,
(db2inst1@sunny) /home/db2inst1
$ ls /home/db2inst1/dsclient/informix
bin  demo  doc  etc  gls  gskit  incl  jvm  lib  msg  properties  release  run  sdk_license  tmp  uninstall
2. Prepare the Informix configuration file, for example,
(db2inst1@sunny) /home/db2inst1
$ cat /home/db2inst1/sqllib/cfg/sqlhosts
# dbserver   nettype    hostname               servicename
# --------   --------   ------------------     -----------
ol_informix1410 onsoctcp test1.fyre.ibm.com 18687
3. Test the connection to the Informix server.
(db2inst1@sunny) /home/db2inst1
$ telnet test1.fyre.ibm.com 18687
Trying 9.30.123.111...
Connected to test1.fyre.ibm.com.
Escape character is '^]'.
4. Set the required environment variables.
INFORMIXDIR - the client installation path.
INFORMIXSERVER - the name of the default Informix server.
INFORMIXSQLHOSTS - - the path of the sqlhosts file.
For example,
INFORMIXDIR=/home/db2inst1/dsclient/informix
INFORMIXSERVER=ol_informix1410
INFORMIXSQLHOSTS=/home/db2inst1/sqllib/cfg/sqlhosts
Add these 3 variables into ~/sqllib/cfg/db2dj.ini
  
$ cat /home/db2inst1/sqllib/cfg/db2dj.ini
DJX_ODBC_LIBRARY_PATH=/home/db2inst1/sqllib/federation/odbc/lib
DB2_FED_LIBPATH=/home/db2inst1/sqllib/federation/odbc/lib:/home/db2inst1/sqllib/federation/netezza/lib64
ODBCINST=/home/db2inst1/sqllib/cfg/odbcinst.ini
NZ_ODBC_INI_PATH=/home/db2inst1/sqllib/cfg

INFORMIXDIR=/home/db2inst1/dsclient/informix
INFORMIXSERVER=ol_informix1410
INFORMIXSQLHOSTS=/home/db2inst1/sqllib/cfg/sqlhosts
5. Run the djxlinkInformix script in ~/sqllib/bin to generate libdb2informixF.so library.
  
$ export INFORMIXDIR=/home/db2inst1/dsclient/informix

(db2inst1@sunny) /home/db2inst1/sqllib/bin
$ ls djxlinkInformix
djxlinkInformix

(db2inst1@sunny) /home/db2inst1/sqllib/bin
$ ./djxlinkInformix
INFORMIXDIR = /home/db2inst1/dsclient/informix
IBM/Informix-Client SDK Version 4.10.FC10
Copyright (C) 1991-2017 IBM
Currently installed version: 4.10.FC10
Library "libdb2informixF.so" was built successfully.
6. create wrapper, server, user mapping and nickname, then query the nickname.
  
db2 => create wrapper informix options(DB2_FENCED 'Y')
DB20000I  The SQL command completed successfully.

db2 => create server server_native_informix type informix version 14 wrapper informix authorization "informix" password "xxx" options(node 'ol_informix1410', dbname 'inf141db1')
DB20000I  The SQL command completed successfully.

db2 => create user mapping for user server server_native_informix options(remote_authid 'informix', remote_password 'xxx')
DB20000I  The SQL command completed successfully.

db2 => create or replace nickname nick_department for server_native_informix."informix"."department"
DB20000I  The SQL command completed successfully.

db2 => describe table nick_department
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNO                          SYSIBM    VARCHAR                      3     0 Yes
DEPTNAME                        SYSIBM    VARCHAR                     36     0 Yes
MGRNO                           SYSIBM    VARCHAR                      6     0 Yes
ADMRDEPT                        SYSIBM    VARCHAR                      3     0 Yes
  4 record(s) selected.

db2 => select * from nick_department
DEPTNO DEPTNAME                             MGRNO  ADMRDEPT
------ ------------------------------------ ------ --------
A00    SPIFFY COMPUTER SERVICE DIV.         000010 A00
B01    PLANNING                             000020 A00
C01    INFORMATION CENTER                   000030 A00
D01    DEVELOPMENT CENTER                   -      A00
D11    MANUFACTURING SYSTEMS                000060 D01
D21    ADMINISTRATION SYSTEMS               000070 D01
E01    SUPPORT SERVICES                     000050 A00
E11    OPERATIONS                           000090 E01
E21    SOFTWARE SUPPORT                     000100 E01
  9 record(s) selected.
For more information, please refer to Configuring access to Informix data sources

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFHEG","label":"DB2 Enterprise Server Edition"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Connectivity-\u003EFederation"}],"ARM Case Number":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"11.5.0"}]

Document Information

Modified date:
18 March 2024

UID

ibm17139620

Page Feedback