Operating system: Apple Mac OS X
TOC
- Install unixODBC library to access databases
- Download the IBM DB2 ODBC CLI Driver
- Configure odbcinst.ini
- Configure odbc.ini
- Test the database connection
- Bonus : Install IBM I Access Client GUI
Install unixODBC library to access databases
To get ODBC working we need to install the unixODBC package
The unixODBC package can be used to access databases through the ODBC
drivers.
Install unixODBC package:
brew install unixodbc
To verify whether unixODBC is installed, execute the following commands:
which odbcinst
which isql
Replace the odbc driver on the mac with the UnixOdbc driver
Read more about it here.
In order to do this we need to temporarily turn off System Integrity Protection (SIP):
Click on the (Apple Logo) at the far left of your Mac's Menubar.
Click on Restart.
Hold down CMD + R during reboot to enter Recovery Mode.
Click on the Utilities Menu.
Launch Terminal.
csrutil disable
Restart your Mac again.
rename the existing libiodbc.dylib file
sudo mv /usr/lib/libiodbc.dylib /usr/lib/libiodbc.IODBC.dylib
Automatically create a new symbolic link to the unixODBC libodbc.2.dylib file
we can try to find the libodbc.2.dylib file automatically if this command does not work, you will have to manually create it
tmpp=$(find /usr/local/Cellar/unixodbc -name libodbc.2.dylib)
echo "sudo ln -s $tmpp /usr/lib/libiodbc.dylib"
sudo ln -s $tmpp /usr/lib/libiodbc.dylib
Manually create a new symbolic link to the unixODBC libodbc.2.dylib file
The unixodbc is installed in a directory that has a specific version so we need to create the command below. Make sure to replace with the correct information found, for example, 2.3.7
# sudo ln -s /usr/local/Cellar/unixodbc//lib/libodbc.2.dylib /usr/lib/libiodbc.dylib
cd /usr/local/Cellar/unixodbc
ls
Click on the (Apple Logo) at the far left of your Mac's Menubar.
Click on Restart.
Hold down CMD + R during reboot to enter Recovery Mode.
Click on the Utilities Menu.
Launch Terminal.
Type
csrutil enable
Restart your Mac again.
Download the IBM DB2 ODBC CLI Driver
Extract file:
Create the directory:
mkdir /usr/local/share/odbc_db2
untar the file into the newly created directory
tar xvzf macos64_odbc_cli.tar.gz -C /usr/local/share/odbc_db2
The IBM DB2 Client libraries (libdb2.dylib) needs to be in the macOS library search path
So create a shortcut(a symbolic link to the libdb2.dylib file):
- Navigate to the new directory
cd /usr/local/share/odbc_db2/clidriver/lib/
- Create a shortcut(symbolic link)
from: /usr/local/share/odbc_db2/clidriver/lib/libdb2.dylib
to : /usr/local/lib/libdb2.dylib
ln -s $(pwd)/libdb2.dylib /usr/local/lib
configure odbcinst.ini
Verify the driver data stored in odbcinst.ini
file location: /usr/local/etc/odbcinst.ini
The location of this file can also be found by running
odbcinst -j
The odbcinst.ini file maps all of the files needed for the driver to operate properly. The odbcinst.ini usually looks like this. If your copy is missing information about IBM go ahead and add it
odbcinst.ini
[IBM i Access ODBC Driver]
Description=IBM i Access for Mac ODBC Driver
Driver=/usr/local/lib/libdb2.dylib
Threading=0
DontDLClose=1
Configure odbc.ini
create ODBC.ini
File location for the SYSTEM DATA SOURCES:
*/usr/local/etc/odbc.ini *
The name of the DSN should be placed within the [] brackets
The name of the driver specified in the Driver keyword must match the name of a driver defined in the odbcinst.ini.
vi /usr/local/etc/odbc.ini
Copy and paste the details below.
Note : To save the file, press the Esc key, then type :wq
ODBC.ini
[usrProd]
Description = IBM i Access for Mac ODBC data source
Driver = IBM i Access ODBC Driver
System = 192.168.1.223
Naming = 0
DefaultLibraries = usrsadbtst
TrueAutoCommit = 1
UserID = username1
Password = password1
Edit ODBC Data Source file
The db2cli also needs the ODBC DSN entry so make s, if a well defined db2cli.ini file exists at the drivers config dir. Please create the file /usr/local/share/odbc_db2/clidriver/cfg/db2cli.ini with this content:
vi /usr/local/share/odbc_db2/clidriver/cfg/db2cli.ini
Copy and paste the details below.
Note : To save the file, press the Esc key, then type :wq
db2cli.ini
[usrProd]
Protocol= TCPIP
Hostname= 192.168.1.223
ServiceName= 50000
uid= username1
pwd= password1
Add the clidriver's bin directory to the environment path
Just open the Terminal and run the command below to create/open a bash profile file
vi ~/.bash_profile
Copy and paste the details below.
Note : To save the file, press the Esc key, then type :wq
export PATH=$PATH:/usr/local/share/odbc_db2/clidriver/bin/
export DB2CLIINIPATH=/usr/local/share/odbc_db2/clidriver/cfg/
export DB2DSDRIVER_CFG_PATH=/usr/local/share/odbc_db2/clidriver/cfg/
NOTE: the current shell session needs to be restarted for changes to take effect.
This can be done by closing and reopening the terminal or by typing
source ~/.bash_profile
Verify that the changes to the path environment variable is being read by the terminal. Type the command below to list all the environment variables currently set.
printenv
Test the database connection
verify the driver and datasource
odbcinst -q -d
odbcinst -q -s
Test the new configuration with the UnixODBC isql tool:
isql usrProd username1 password1
Test the new configuration with db2cli:
db2cli validate -dsn usrProd -connect -user username1 -passwd password1
Create node application
mkdir -p /var/www/node-apps/obdc_test
cd /var/www/node-apps/obdc_test
npm init -y
Install node ODBC package
npm install odbc
vi /var/www/node-apps/obdc_test/app.js
Copy and paste the details below.
Note : To save the file, press the Esc key, then type :wq
const odbc = require("odbc");
const cn = "DSN=usrProd;UID=username1;PWD=password1";
odbc.connect(cn, (error, connection) => {
connection.query(
"SELECT * FROM QIWS.QCUSTCDT FETCH FIRST 6 ROWS ONLY",
(error, result) => {
if (error) {
throw error;
}
console.log(result);
}
);
});
Run node application:
node /var/www/node-apps/obdc_test/app.js
Bonus : Install IBM I Access Client GUI
Install instructions taken from here: https://github.com/IBM/ibmi-oss-examples/blob/master/odbc/odbc.md
To get the driver, visit the IBM i Access - Client Solutions page and select Downloads for IBM i Access Client Solutions. After logging in and redirected to the IBM I Access Client Solutions Download page, select the Download using http tab then scroll down and download the IBM i Access Client Solutions (IBMiAccess_v1r1).
Install Java:
In the terminal type the command below, if java is not installed follow the instructions on the screen to download and install it
# java -version
unzip IBMiAccessT_v1r1.zip
locate the ** Mac_Application **
Click install_acs to install the software, doing this should create the IBM i Access Client Solutions.app in the Applications directory
Top comments (0)