At last year's
, I learned more about two recently announced JDBC tools that had piqued my curiousity: -
InfoSphere Federation Server 9.7's "JDBC wrapper"
IMS 11's Universal JDBC driver
Why so curious? It's simple. The new JDBC wrapper lets Federation Server access a database through the database's JDBC driver. Therefore, if IMS now has a JDBC driver, it seemed logical to me that I should be able to combine the two and then join IMS and DB2 data in a single query. So, back at the Lab, I read the documentation and decided to give it a try. With a little help from the IMS and Federation Server teams, I was successful. What follows are the 8 basic steps I took and my conclusion.
1. Install the Federation Server
Before you get started, be aware that you may need a fix from the Federation Server team. I was using 9.7 with fix pack 1 applied. There was a bug in the JDBC wrapper that caused column numbers to be incorrect on queries over nicknames. I received a small fix that resolved the issue. The substeps you need to take at this point are as follows: a. Install Federation Server 9.7 and make sure you select the JDBC wrapper. b. Download and install
the latest fix pack
. c. Update your Federation Server instance's database manager configuration to have
. (An instance is created during install.)
2. Get the IMS JDBC files and IMS access from your local IMS person
If you're like me, you know DB2 and JDBC, but little to nothing about IMS. That's okay. The IMS Universal JDBC Driver gives us access to IMS data without making us learn IMS first. Therefore, I think it's reasonable that we let our local IMS person handle any necessary IMS configuration and provide us with the IMS JDBC parts we need. (Me? I got fantastic help... straight from the IMS development team at IBM's Silicon Valley Lab ;) You'll need the following from your IMS person: - a user and password for accessing the IMS database - the host name and port for your IMS subsystem - a list of 'tables' that can be accessed through the JDBC driver - imsudb.jar (this is the JDBC driver) - a 'metadata' class file (more below) JDBC applications need access to metadata. For example, with DB2, they access metadata found in catalog tables such as sysibm.systables. However, IMS does not have a runtime metadata catalog. Therefore, metadata needs to be provided another way. This is done through a class file that's based on information stored in IMS files. Your IMS person can generate this metadata class file using IMS's
. The metadata file's name can be anything. The one I'm using from the SVL IMS team is called: BMP255DatabaseView.class The list of IMS 'tables' I can access from this are:
I don't know if these tables are available as a sample for IMS customers. If not, then you'll need to adjust my instructions to fit what you have.
3. Add the metadata class file to the JDBC driver jar file
I don't think the IMS driver requires this for user-written applications, but I found I had to do it so that the driver could find the metadata when the driver was used by the Federation Server. This is a very simple process. In my case, the metadata class expected to be in a package called testcases.jdbo. Therefore, to use this file, I had to do the following: 1. create subdirectories called IMS\testcases\jdbo. 2. copy the metadata file into the jdbo directory. 3. copy imsudb.jar in to the IMS directory. 4. add the class file to the jar file with: C:\Progra~1\IBM\SQLLIB\java\jdk\bin\jar -uvf imsudb.jar testcases/ Notice I used the jar command found in the Federation Server instance's Java directories. Also, to make sure I didn't overwrite the original JDBC driver, I renamed the updated jar file to imsudb.updated.jar.
4. Optionally, copy the updated driver into your Federation Server instance
I wanted the updated driver out of my work directories and somewhere easy to find in instance's directories. Therefore, I created a subdirectory called IMS under my instance's sqllib. Whatever you do, save the name because you'll find you'll need it for subsequent steps.
5. Identify the JDBC driver in the Federation Server ini file
This file is called
. Create it in your Federation Server instance's cfg subdirectory. For example, in my case, this directory was: C:\Progra~1\IBM\SQLLIB\cfg You'll need only one line in this file. It sets the CLASSPATH variable to the directory identified in step 4. The Federation Server will set this value internally when the instance is started. In my case, the line was: CLASSPATH=C:\Program Files\IBM\sqllib\IMS
6. Start the instance and make sure you have a Sample database
If your instance is already running, then you need to restart it. To restart, issue the db2stop command followed by the db2start command. The rest of these steps assume the use of a
database. If you don't have one, create it now.
7. Create a DB2 SQL file using the Sample Script at the end of this post
The sample script contains all the SQL needed to access IMS along with queries to access IMS data through the Federation Server. The last query is a join of DB2 and IMS data. Copy and paste it into your own file. You'll need to customize the script based on your environment. The sample script contains comments that identify what needs to be changed.
8. Run your copy of the script through the DB2 command line processor
For example, let's say you named your file joinDB2andIMS.sql and you named your sample databae warehaus. You would need to issue the following two commands: a. db2 connect to warehaus b. db2 -vtf joinDB2andIMS.sql I recommend redirecting output to a file. A successful run creates everything you need to access IMS through the Federation Server and joins data from DB2 with data from IMS.
I think it takes a little bit of skill and patience to combine these two, but, really, the biggest challenge is the lack of documentation. That's the point of this post :) Also, as with anything new, I'd like to spend more time with these two to understand any considerations and limitations beyond what's currently in the official documentation. --
-- -- -- Copy this sample SQL into a file. -- -- Search for the word 'Changes' to find lines that need to be -- customized for your environment. -- drop wrapper jdbc; create wrapper jdbc; -- -- Changes: You need to update the Create Server statement: -- 1. Set DRIVER_PACKAGE to the full path of your IMS JDBC driver. -- 2. Set three values for URL -- The host for the IMS subsystem (e.g., ecat07.svldev.sv..ibm.com) -- The listener port for IMS (e.g., 5555) -- The metadata class (e.g., testcases.jdbo.BMP255DatabaseView) -- drop server myims; CREATE SERVER myims TYPE JDBC VERSION 3.0 WRAPPER JDBC OPTIONS ( DRIVER_PACKAGE 'C:\Program Files\IBM\sqllib\IMS\imsudb.updated.jar', DRIVER_CLASS 'com.ibm.ims.jdbc.IMSDriver', URL 'jdbc:ims://ecat07.svldev.svl.ibm.com:5555/class://testcases.jdbo.BMP255DatabaseView', db2_max_corr_name_len '0', DB2_IUD_ENABLE 'Y', db2_char_blankpadded_comparison 'Y', db2_varchar_blankpadded_comparison 'Y', VARCHAR_NO_TRAILING_BLANKS 'Y', JDBC_LOG 'Y'); -- -- Changes: You need to update the Create User statement: -- 1. Change 'tolleson' to your DB2 user name. -- 2. Change 'usrx001' to your IMS user ID. -- 3. Change 'passw0rd' to your IMS user password. -- CREATE USER MAPPING FOR tolleson SERVER myims OPTIONS ( REMOTE_AUTHID 'usrx001', REMOTE_PASSWORD 'passw0rd'); Commit; -- -- Changes: If your IMS 'tables' are different from my, you'll need to make -- considerable changes to the script from here on. However, you -- should be able to get a good feel for what you need to do based -- on what's here. -- set passthru myims; select count(hospll) from pcb01.hospital; select hospll,hospcode,hospname from pcb01.hospital; set passthru reset; -- -- Try one nickname. -- create nickname hospital for myims.PCB01.HOSPITAL; commit; select count(hospll) from hospital; select hospll,hospcode,hospname from hospital; -- -- Create the reset of the databases nicknames -- create nickname ward for myims.PCB01.WARD; create nickname patient for myims.PCB01.PATIENT; create nickname illness for myims.PCB01.ILLNESS; create nickname treatmnt for myims.PCB01.TREATMNT; create nickname doctor for myims.PCB01.DOCTOR; create nickname payments for myims.PCB01.PAYMENTS; create nickname billing for myims.PCB01.BILLING; commit; -- -- Select from individual nicknames -- select HOSPITAL_HOSPCODE ,WARDLL ,WARDNO ,WARDNAME ,PATCOUNT ,NURCOUNT ,DOCCOUNT from WARD; select HOSPITAL_HOSPCODE ,PATIENT_PATNUM ,WARD_WARDNO ,DOCLL ,DOCTNO ,DOCNAME from DOCTOR; -- -- Join an IMS table with a DB2 table. -- Changes: For this query to work, you need to -- insert a row into the DB2 Sample database's -- employee table. The empno needs to end with -- the IMS DOCTNO found in the IMS DOCTOR tabe. -- select MyDoc.HOSPITAL_HOSPCODE ,MyEmp.WORKDEPT ,MyDoc.DOCTNO ,MyDoc.DOCNAME ,MyDoc.PATIENT_PATNUM ,MyDoc.WARD_WARDNO from DOCTOR MyDoc, EMPLOYEE MyEmp where MyDoc.DOCTNO=substr(MyEmp.empno,3,4); commit;