Connecting to IBM DB2 using JDBC

How do I correctly connect to my IBM DB2 Database using JDBC connection in MATLAB?

Answer 1

[MathWorks Support Team]

For more information on the following steps, please refer to the following link.

https://github.com/IBMProjectEventStore/db2eventstore-IoT-Analytics/tree/master/AdvancedApplications/JDBCApplication

 

1. Make sure you have the Database toolbox that provides the Database Explorer in MATLAB.
2. First go to the MATLAB editor and type: "edit javaclasspath.txt" in the Command Window, and in that file add the file path to the DB2 JDBC client driver downloaded from the https://www.ibm.com/support/pages/download-initial-version-115-clients-and-drivers :
   1. E.g. /<path where the JDBC jar was placed>/db2jcc4.jar
3. (AFTER UPDATING javaclasspath.txt) restart MATLAB or look at https://www.mathworks.com/matlabcentral/answers/103763-why-does-the-contents-of-javaclasspath-txt-not-get-added-to-the-static-class-path-when-i-double-clic
4. Go to the Apps tab in MATLAB and select Database Explorer
5. Select "Configure Data Source" and select "Configure JDBC Data Source" from the drop down menu
6. Download the SSL keystore and password through the REST API using the instructions documented in the https://www.ibm.com/docs/en/db2-event-store/2.0.0?topic=store-rest-api , this is necessary because DB2 EventStore is configured with SSL with a default keystore out of the box (this may change if you configure it your own keystore after installation).
7. In the JDBC Data Source Configuration Editor window do the following:
   • Enter any new name for the data source, e.g. EventStoreJDBC
   • Enter OTHER as the vendor
   • For Driver enter: com.ibm.db2.jcc.DB2Driver
   • In URL, add in the following: jdbc:db2://<Your Db2 Event Store Cluster VIP>:18729/EVENTDB:sslConnection=true;sslTrustStoreLocation=<path to clientkeystore downloaded from cluster>;sslKeyStoreLocation=<path to clientkeystore downloaded from cluster>;sslKeyStorePassword=<password for clientkeystore retrieved from cluster>;sslTrustStorePassword=<password for clientkeystore retrieved from cluster>;securityMechanism=15;pluginName=IBMPrivateCloudAuth;
   •  e.g. jdbc:db2://9.30.192.111:18729/EVENTDB:sslConnection=true;sslTrustStoreLocation=/Users/cmgarcia/Documents/MATLAB/eventstore/clientkeystore;sslKeyStoreLocation=/Users/cmgarcia/Documents/MATLAB/eventstore/clientkeystore;sslKeyStorePassword=Cc2cZ8TxdhWf;sslTrustStorePassword=Cc2cZ8TxdhWf;securityMechanism=15;pluginName=IBMPrivateCloudAuth;
   • Then click on any part of the window and the TEST button will pop up
   • Select 'TEST' and you will be asked for a user ID and password, where you can enter the EventStore user ID and password for your system (e.g. admin and password)
   • Then you close the window. 
8. If you select and expand the default schema you will see the tables that exist in DB2 EventStore. You can expand on any of the tables and see the columns for each table. If you select the checkbox for one of the tables, a 'select * from default.<tablename>' will show up in the query editor view, and if you select a subset of the columns for that table then the query will automatically only select those columns. In both cases, the query result will be displayed. If after you select a table, you can then select the "Join" icon at the top of the Database Explorer view, and in that view you can select another table and the Join Columns to view the Join Query and the query result.
9. Then in the Database Explore window, select the "New Query" icon which will ask for the Data Source, Username and Password. Enter the "Name" used from the JDBC window above (where it should be displayed in a drop down menu under Data Source, e.g. EventStoreJDBC), and enter the EventStore user ID and password. Note that in this New Query Editor view, one can optionally use or not use the default schema name for the EventStore table(s).