Is it possible to connect to a IBM DB2 database without using ODBC?

[#1]

Hello,

     We use an IBM DB2 database through an SSL connection. The OCBC connection to the DB works well, but is quite complex to setup for the majority of the users. If a software can use a "type 4 db2 connection", odbc is not required. Only a "connection string" is sent directly to the remote server. The parameters of this string are usually configured directly in the software. A connection configuration is created in the software with the proper parameters, which is really simple to document by IT and to execute by the users.

     I feel that JMP doesn't support a "type 4 connection" directly, but R and python do. And it's also possible to extract data in a JSL script, like the example below. Could we create a small python script that creates a database connection that would then be available to JSL or to the query builder?

// JSL Script to extract some IBM DB2 data

New SQL Query(
	Connection(
		"ODBC:DSN=DBPROD1;MODE=SHARE;PWD=%_PWD_%;SECURITY=SSL;SSLCLIENTKEYSTASH=C:\BRMDB2\ssl\truststore.sth;SSLCLIENTKEYSTOREDB=C:\BRMDB2\ssl\truststore.kdb;SSLSERVERCERTIFICATE=C:\BRMDB2\ssl\carootcert.der;UID=%_UID_%;"
	),
	QueryName( "Extract exemple" ),
	CustomSQL(
		"SELECT NAME
        	   ,EMPLOYEE_NB 
        	   ,DEPARTMENT
        	   ,SALARY 
        FROM 
           EMPLOYEE_TABLE
        WHERE 
           DEPARTMENT = 'MFG02'"
        )
) << Run

     Thanks for your help!

[#2]

Based on this Data Connectors  you would have to utilize ODBC. Other option would be to utilize Python Integration to make the queries. Write a function which takes in for example the query and inside it runs Python script to perform the query and returns the result as data table.

[#3]

Hi,

This is somewhat what I had in mind, but I was hoping that the connection could be an object created independently and made available for SQL queries. I will test your suggestion and provide feedback here. I assume I can write a Python script "template" that my colleagues could paste into their JMP, adding the SQL code according to their needs. I’m convinced it should work. However, I will check if this solution is functional and usable by my colleagues. If it is, I’ll provide feedback here and accept the response as an acceptable solution. It depends on how good they are with SQL and the size of the tables.

Thank you for the response,

[#4]

I'm not sure if this type of connectors can be done in JMP18 as this add-in has minimum version of JMP19 EA 3, S3 Data Connector (JMP Marketplace), but it seems to be using Python as connector for AWS S3. If it can be used in JMP18, it might give some ideas of what you could do. You should be able to download the add-in and then extract it by using 7zip for example.

[#5]

Hello,

For oracle and postgre database, we can send only the connection string.

I think that this method should be possible for db2 too without setup the odbc with a name for each computer.

I could plan to try it this month or next month.

Regards,

Guillaume

[#6]

Hi,

Sorry for my late answer.

You can do like this (tested on my side).

db2_connect =
	"DRIVER={IBM DB2 ODBC DRIVER};
	DATABASE= strDatabase;
	HOSTNAME= strHostname;
	PORT= strPort;
	PROTOCOL=TCPIP;
	UID= strUser;
	PWD= strPassword;";
	
OpenDatabase(db2_connect, "SELECT * FROM SYSIBM.SYSDUMMY1;", "sysdummy1");

You have to define the DRIVER and all other varaibles strDatabase, strHostname, strPort, strUser, strPassword

Using SSL, you could try this (not tested):

"DRIVER={IBM DB2 ODBC DRIVER};
DATABASE=strDatabase;
HOSTNAME=strHostname;
PORT=strPort;
PROTOCOL=TCPIP;
SECURITY=SSL;
UID=strUser;
PWD=strPassword;
SSLCLIENTKEYSTOREDB=/path/to/client/keystore.kdb;
SSLCLIENTKEYSTASH=/path/to/client/keystore.sth;
SSLCLIENTKEYPASSWORD=keystore_password;"

Regards,

Guillaume

[#7]

you maybe have to use more connection parameters for ssl connection.