-
Notifications
You must be signed in to change notification settings - Fork 17
DB2 Client
It is assumed you have been able to source a legal copy of IBM DB2 Client. How to source that is not subject to this article. By the time of writing this article Client v11.1.4fp7_linuxx64_client.tar, the client archive file, was used. The installation was done on Oracle Linux 8.
- download the client archive file to your home directory on your linux system.
- untar the archive file
tar xvf <filename>
- change to the client directory in your home directory after untaring the archive
- Locate the db2_install.sh script
- run
sudo ./db2_install.shand answer the subsequent questions with yes, as we did, or chose other answers that fit your situation better
You should be able to locate the client code in /opt/ibm/db2/V11.1, if you chose the default directory at installation.
- navigate to
/opt/ibm/db2/V11.1/instance - run command
sudo ./db2icrt-s client postgres, which will create an instance in the user postgres home directory (/var/lib/pgsql) You should be able to locate a folder /var/lib/pgsql/sqllib as well as a file /var/lib/pgsql/.profile, now. - ensure .profile gets executed when logging on to user postgres, wich has been done by adding it to .bash_profile When .profile gets executed it will establish the environment for the user postgres to use db2 command line utility
- For further configuration (e.g. issue #23 "decimal separator") of the DB2 client please check IBM manuals:
-
to ensure postgres database engine is able to locate required shared object libraries and knows some crutial environment variables, we copied
/usr/lib/systemd/system/postgresql-xx.serviceto/etc/systemd/systemand modified it -
add the following lines to the
/etc/systemd/system/postgresql-xx.servicefileEnvironment=LD_LIBRARY_PATH=/usr/pgsql-xx/lib:/var/lib/pgsql/sqllib/lib64:/var/lib/pgsql/sqllib/lib64/gskit:/var/lib/pgsql/sqllib/lib32 Environment=DB2INSTANCE=postgres Environment=DB2_HOME=/var/lib/pgsql/sqllib Environment=DB2LIB=/var/lib/pgsql/sqllib/lib Environment=PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/pgsql-xx/bin:/var/lib/pgsql/sqllib/bin:/var/lib/pgsql/sqllib/adm:/var/lib/pgsql/sqllib/misc:/var/lib/pgsql/sqllib/gskit/bin Environment=DB2CLIINIPATH=/var/lib/pgsql/sqllib/cfg/db2cli.ini -
do not forget to save the change and invoke
sudo systemctl daemon-reloadto activate those changes With that when you start postgres the database will have access to db2 client shared object libraries and the clients environment, when it needs to load the db2_fdw shared object library later.
While logged on to user postgres on your system, run these commands to prepare access to a remote DB2 database. In our case we used a DB2 V12.1 on Oracle Linux 8.
db2 catalog tcpip node <nodename> remote <ip or hostname> server <portnumber>
db2 catalog database <dbname> at node <nodename>
For example
db2 catalog tcpip node DB2 remote db2.mydomain.org server 25010
db2 catalog database SAMPLE at node DB2
Check your node by issuing db2 list node directory and the output should look like:
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = DB2
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = db2.mydomain.org
Service name = 25010
Check your database by issuing db2 db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Node name = DB2
Database release level = 14.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
Now connect to that database db2 connect to sample user db2inst1 using db2inst1 and you should see
db2 => connect to sample user db2inst1 using db2inst1
Database Connection Information
Database server = DB2/LINUXX8664 12.1.1.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
Issue db2 list tables to have the system present you all tables there are in that database:
db2 => list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ACT DB2INST1 T 2025-04-21-16.43.15.865102
ADEFUSR DB2INST1 S 2025-04-21-16.43.16.479839
BINTYPES DB2INST1 T 2025-09-29-18.53.45.691633
CATALOG DB2INST1 T 2025-04-21-16.43.17.388287
CL_SCHED DB2INST1 T 2025-04-21-16.43.15.628162
CUSTOMER DB2INST1 T 2025-04-21-16.43.17.319329
DEPARTMENT DB2INST1 T 2025-04-21-16.43.15.645780
DEPT DB2INST1 A 2025-04-21-16.43.15.680002
EMP DB2INST1 A 2025-04-21-16.43.15.715224
EMPACT DB2INST1 A 2025-04-21-16.43.15.864138
EMPLOYEE DB2INST1 T 2025-04-21-16.43.15.680549
EMPMDC DB2INST1 T 2025-04-21-16.43.16.677861
EMPPROJACT DB2INST1 T 2025-04-21-16.43.15.851979
EMP_ACT DB2INST1 A 2025-04-21-16.43.15.864675
EMP_PHOTO DB2INST1 T 2025-04-21-16.43.15.715682
EMP_RESUME DB2INST1 T 2025-04-21-16.43.15.755737
INVENTORY DB2INST1 T 2025-04-21-16.43.17.302194
IN_TRAY DB2INST1 T 2025-04-21-16.43.15.892459
NUMERICTYPES DB2INST1 T 2025-09-18-16.45.47.173716
ORG DB2INST1 T 2025-04-21-16.43.15.900086
PRODUCT DB2INST1 T 2025-04-21-16.43.17.265086
PRODUCTSUPPLIER DB2INST1 T 2025-04-21-16.43.17.448112
PROJ DB2INST1 A 2025-04-21-16.43.15.827885
PROJACT DB2INST1 T 2025-04-21-16.43.15.828373
PROJECT DB2INST1 T 2025-04-21-16.43.15.792766
PURCHASEORDER DB2INST1 T 2025-04-21-16.43.17.353509
SALES DB2INST1 T 2025-04-21-16.43.15.914631
STAFF DB2INST1 T 2025-04-21-16.43.15.908331
STAFFG DB2INST1 T 2025-04-21-16.43.16.391198
SUPPLIERS DB2INST1 T 2025-04-21-16.43.17.419644
TIMETYPES DB2INST1 T 2025-09-19-17.20.02.874667
TM2ACCT DB2INST1 T 2025-10-23-14.04.37.346307
VACT DB2INST1 V 2025-04-21-16.43.15.931844
VASTRDE1 DB2INST1 V 2025-04-21-16.43.15.940904
VASTRDE2 DB2INST1 V 2025-04-21-16.43.15.943446
VDEPMG1 DB2INST1 V 2025-04-21-16.43.15.936272
VDEPT DB2INST1 V 2025-04-21-16.43.15.922698
VEMP DB2INST1 V 2025-04-21-16.43.15.930033
VEMPDPT1 DB2INST1 V 2025-04-21-16.43.15.938659
VEMPLP DB2INST1 V 2025-04-21-16.43.15.956721
VEMPPROJACT DB2INST1 V 2025-04-21-16.43.15.934442
VFORPLA DB2INST1 V 2025-04-21-16.43.15.951618
VHDEPT DB2INST1 V 2025-04-21-16.43.15.929177
VM2ACCT DB2INST1 V 2025-10-23-14.05.03.618399
VPHONE DB2INST1 V 2025-04-21-16.43.15.955629
VPROJ DB2INST1 V 2025-04-21-16.43.15.930843
VPROJACT DB2INST1 V 2025-04-21-16.43.15.933088
VPROJRE1 DB2INST1 V 2025-04-21-16.43.15.945428
VPSTRDE1 DB2INST1 V 2025-04-21-16.43.15.948198
VPSTRDE2 DB2INST1 V 2025-04-21-16.43.15.950096
VSTAFAC1 DB2INST1 V 2025-04-21-16.43.15.952872
VSTAFAC2 DB2INST1 V 2025-04-21-16.43.15.954137
52 record(s) selected.
You may notice less tables on your system. That is because we have already added a few more to cover some otherwise not used data types. The system now is ready to use db2_fdw with postgres.
Depending on your locale definitions in your DB2 database and within PostgreSQL, you may encounter issues, particularly in the representation of decimal numbers. PostgreSQL will expect the decimal separator to be either a decimal point or komma, depending on its locale configuration. Your DB2 may be configured differently. There is a way to force the decimal separator to be a decimal point within the db2cli.ini. If you need the decimal separator to be a decimal point set Patch2 to 15 in db2cli.ini.
; Common settings applicable to all connections
[COMMON]
Trace=1
TraceFlushOnError=1
TraceFlush=1
TracePathName=/var/lib/pgsql/18/data/log
TraceFileName=/var/lib/pgsql/18/data/log/db2cli.log
TraceTime=1
TraceTimestamp=3
Patch2="15"
; Database-specific settings
[SAMPLE]
Database=SAMPLE
Protocol=TCPIP
Hostname=db2.mydomain.org
Port=25010