I faced a problem for quite a few days connecting to DB2 Database on IBM Cloud using RJDBC. Ultimately, I found a solution yesterday.
The Code to connect to DB2 Database using RJDBC
I refer to this code a CODE1.
if("rJava" %in% rownames(installed.packages()) == FALSE) {install.packages("rJava")}
library(rJava)
if("DBI" %in% rownames(installed.packages()) == FALSE) {install.packages("DBI")}
library(DBI)
if("RJDBC" %in% rownames(installed.packages()) == FALSE) {install.packages("RJDBC")}
library(RJDBC)
#Values for you database connection
dsn_driver = "com.ibm.db2.jcc.DB2Driver"
dsn_database = "BLUDB" # e.g. "BLUDB"
dsn_hostname = "dashdb-entry-yp-lon02-01.services.eu-gb.bluemix.net" # e.g. replace <yourhostname> with your hostname, e.g., "Db2 Warehouse01.datascientstworkbench.com"
dsn_port = "50000" # e.g. "50000"
dsn_protocol = "TCPIP" # i.e. "TCPIP"
dsn_uid = "<your UID>" # e.g. userid
dsn_pwd = "<your password>" # e.g. password
#Connect to the Database
jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar");
jdbc_path = paste("jdbc:db2://", dsn_hostname, ":", dsn_port, "/", dsn_database, sep="");
conn = dbConnect(jcc, jdbc_path, user=dsn_uid, password=dsn_pwd)
The Issue
When I ran this code, the program gave a error stating that rJava cannot be loaded. This happened every time I ran the program.
The Initial Solution
The initial solution was to load the dynamic library using the following code.
dyn.load('/Library/Java/JavaVirtualMachines/jdk-9.0.4.jdk/Contents/Home/lib/server/libjvm.dylib')
if("rJava" %in% rownames(installed.packages()) == FALSE) {install.packages("rJava")}
library(rJava)
The solution works properly after using this code. However, there is a problem. The issue is that with this code, the application in Shiny cannot be deployed in the Shiny Server. This is because the path to the file libjvm.dylib is an absolute path.
So, I copied the file libjvm.dylib to the application directory and changed the code as follows.
dyn.load('libjvm.dylib')
if("rJava" %in% rownames(installed.packages()) == FALSE) {install.packages("rJava")}
library(rJava)
When I run this code, R raises a fatal error and the environment is aborted. On investigating I found that this line of code does not produce the error. However, the following line of code does.
#Connect to the Database
jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar");
So, under the application path, I created a directory called “Drivers” and copied the db2jcc4.jar in that directory. So, the code became as follows.
#Connect to the Database
jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "Drivers/db2jcc4.jar");
Now, what happens is that when I run the program once providing the complete path for libjvm.dylib as dyn.load(‘/Library/Java/JavaVirtualMachines/jdk-9.0.4.jdk/Contents/Home/lib/server/libjvm.dylib’), the program works perfectly. After running the program once, if I change the code to dyn.load(‘Drivers/libjvm.dylib’), the program works perfectly till the R environment is restarted. However, I needed to load the shared library using full path once the environment was restarted.
When I deployed the Shiny Application on the Shiny Server without the absolute path, the application produced error when it was run. The error was as follows.
Error in value[[3L]](cond) : unable to load shared object '/srv/connect/apps/ExpenseAnalysis/Drivers/libjvm.dylib': /srv/connect/apps/ExpenseAnalysis/Drivers/libjvm.dylib: invalid ELF header Calls: local ... tryCatch -> tryCatchList -> tryCatchOne -> <Anonymous> Execution halted
This error is produced as binary image cannot be uploaded to the Shiny Server while deploying an application.
So, this was not a solution. However, it gave 2 aspects.
- I needed a directory called Drivers under the application directory.
- In this directory, I needed the file db2jcc4.jar.
The Final Solution
After searching through the Internet, I got a few aspects and tried the following. This solved the problem.
The steps need to be followed in the same sequence as provided below.
On the shell, run this command.
R CMD javareconf
Then, on the R Console, run this command.
install.packages("rJava")
After these 2 steps have been followed, the program containing the code as provided in CODE1 can be run without any error. This code can be deployed on Shiny Server and will run without any error.
Experiences of Partha Majumdar 