ims-java-lab

This lab's goal is to show a Java developer how to access an IMS database through the IMS Universal JDBC driver.

Getting started

This lab is designed specifically for coding Java in an Eclipse IDE. It is meant to be instructor led where the laptops are already provided pre-configured for you but you should be able to run this on your own machine as well. If you get lost at any step, feel free to reference the solution provided in the com.ibm.ims.lab.solutions.MyIMSJavaApplication.java file

Pre-requisites

Software:

• Eclipse Neon or later
• Java 7 or later

Skills:

• Java programming - Beginner level
• SQL programming - Beginner level
• IMS - Beginner level

Download the ims-java-lab project from GitHub

Download the ims-java-lab project by click on the Clone or Download button and then select Download ZIP.

Importing the Eclipse project

The ims-java-lab project is designed as an Eclipse project and will need to get imported into your Eclipse development environment.

1. Open up Eclipse
2. Accept the default workspace or specify your own (e.g. c:\IMSJavaLab)
3. In the top menu, select File->Import
4. Choose the Existing Projects into Workspace option in the popup wizard and click Next
5. Click the Browse button next to the Select root directory radio button and navigate to where you downloaded the ims-java-lab project or Select archive file radio button and specify the zip file name.

Opening up the lab files

1. Once your project has been imported, you should have a ims-java-lab project in the Project Explorer.
2. Expand out the following folders: ims-java-lab->src->com.ibm.ims.lab
3. Double click on the MyIMSJavaApplication.java file. The majority of your work will be done in this file.

Writing a distributed Java application

The first part of the lab is to develop a distributed Java application. In this case when we say distributed, we're specifically talking about any non z/OS environment that supports Java.

Connections to IMS resources on the mainframe from a distributed environment requires a TCP/IP connection through an IMS Connect TCP/IP gateway. For our distributed application, we will be connecting through a pre-configured IMS Connect that resides on a public demo system.

We will cover two different query languages in this lab. Exercises 1 through 7 will cover use the IMS JDBC driver to issue SQL queries and Exercises 8 through 10 will cover using the IMS Java DL/I API to issue DL/I queries.

Exercise 1: Creating a Type-4 JDBC connection to an IMS database

The following information is required to connect to an IMS database from an external environment

1. Hostname/IP address of the IMS Connect
2. Port number for the IMS Connect
3. Username to authenticate against the system's resource access control facility (RACF)
4. Password to authenticate against RACF
5. The IMS Program specification block (PSB) name that the user will access

In MyIMSJavaApplication.java, go ahead and uncomment the line under Exercise 1 in the main() method by removing the comments ('//').

createAnImsConnection(4).close();

Now navigate to the createAnImsConnection() method and create your connection underneath the Exercise 1 section.

To create your connection first create an IMSDataSource object. We will use an IMSDataSource to create our connection but you could also alternatively create it using the standard JDBC DriverManager interface.

IMSDataSource ds = new IMSDataSource();

Now use the appropriate setters on your IMSDataSource object to set the following parameters:

1. host: To be provided by the lab instructor
2. port number: To be provided by the lab instructor
3. driver type: 4
4. user: To be provided by the lab instructor
5. password: To be provided by the lab instructor
6. database name: DFSIVP1 <-- This is actually the PSB name

Setter example for host:

ds.setHost("yourHost");
ds.setPortNumber(7013);
ds.setUser("YOURID");
ds.setPassword("YOURPWD");
ds.setDriverType(4);
ds.setDatabaseName("DFSIVP1");

Once you've set all of the connection information, you can create a connection by calling the getConnection() method on your IMSDataSource object.

connection = ds.getConnection();

You can now run your application by selecting Run->Run As->Java Application from the top Eclipse menu. The console should show the following output:

Apr 16, 2018 1:52:18 PM com.ibm.ims.drda.t4.T4ConnectionReply checkServerCompatibility
INFO: Server IMS Connect DDM level:  1
Apr 16, 2018 1:52:18 PM com.ibm.ims.drda.t4.T4ConnectionReply checkServerCompatibility
INFO: Client IMS Connect DDM level:  1
Apr 16, 2018 1:52:18 PM com.ibm.ims.drda.t4.T4ConnectionReply checkServerCompatibility
INFO: Server ODBM DDM level:  1 2 3 4 5 6
Apr 16, 2018 1:52:18 PM com.ibm.ims.drda.t4.T4ConnectionReply checkServerCompatibility
INFO: Client ODBM DDM level:  1 2 3 4 5 6 7
Apr 16, 2018 1:52:18 PM com.ibm.ims.drda.t4.T4ConnectionReply checkServerCompatibility
INFO: ODBM DDM level is backlevel with respect to the Universal driver client. Some functionality in the driver will be disabled. Suggest upgrading ODBM to latest service level.
Apr 16, 2018 1:52:18 PM com.ibm.ims.dli.PSBInternalFactory createPSB
INFO: IMS Universal Drivers build number: 14066

The output shows that we created a connection to the system and validated functional levels between the server and the client.

Let's disable Excercise 1 before moving on by adding the comments back in the main() method

//createAnImsConnection(4).close();

Exercise 2: Discovering the database metadata

Now that we have a connection to IMS, the next step is to discover what databases are available for access through the PSB (IVPDB1) defined in the connection from Exercise 1. This database metadata information would be stored in the IMS catalog which is IMS' trusted source for information. This information has been mapped to standard JDBC DatabaseMetadata discovery which many JDBC based tools use.

The following is a mapping of terms from IMS to the relational model that the JDBC interface uses:

• Program Control Block (PCB) == Schemas
• Database Segments == Database Tables
• Database Fields == Database Columns
• Database Records == Database Rows

Similar to Exercise 1, we will want to uncomment the following line in the main() method:

displayMetadata();

Now navigate to the displayMetadata() implementation, you'll notice that we are first establishing a connection to the IMS system by taking advantage of the code we wrote in Exercise 1.

Let's take that connection object and retrieve a queryable DatabaseMetaData object from that.

DatabaseMetaData dbmd = connection.getMetaData();

The DatabaseMetaData class contains several methods for discovery which typically returns back a ResultSet object. Let's discover what PCBs are available by using the getSchemas() method. Remember that PCBs have a one to one mapping with schemas. The following code will show how to invoke the getSchemas() method and display the output.

// Display IMS PCB information
ResultSet rs = dbmd.getSchemas("DFSIVP1", null);
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
		
System.out.println("Displaying IMS PCB metadata");
while (rs.next()) {
  for (int i = 1; i <= colCount; i++) {
    System.out.println(rsmd.getColumnName(i) + ": " + rs.getString(i));
  }
}

In addition to using DatabaseMetaData for discovery of the database, we also used ResultSetMetaData above to identify information on the ResultSet returned by the getSchemas() call. We will be used ResultSetMetaData in most of the following exercises in order to display a readable output like the following:

TABLE_SCHEM: PCB01
TABLE_CATALOG: DFSIVP1
PCB_PROCESSING_OPTIONS: A
DBD_NAME: IVPDB1
DBD_TIMESTAMP: 1620715292519

We can dig even further into the database segments and fields with the following query. Use the same format as above to process the ResultSet object:

// Display IMS segment information
rs = dbmd.getTables("DFSIVP1", "PCB01", null, null);
// Display IMS field information
rs = dbmd.getColumns("DFSIVP1", "PCB01", "A1111111", null);

That completes Exercise 2. Let's go ahead and disable the following line in the main() method by commenting it out:

//displayMetadata();

Exercise 3: Querying the database

Now that we have a good understanding of what our database looks like. We can go ahead and start building queries against the database. Let's start by uncommenting the following line in the main() method.

executeAndDisplaySqlQuery();

Let's now navigate to the executeAndDisplaySqlQuery() method and write our SQL SELECT statement to issue a read request against the database.

An initial query has already been written SELECT * FROM PCB01.A1111111. This is based off of our database metadata discovery where we know the PSB DFSIVP1 contains a PCB PCB01 which has a segment A111111 that contains fields related to a phonebook.

The way we would execute a read query is through the Statement.executeQuery() method. We can get a Statement object off of the Connection. The following code shows how to do that.

Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(sql);

You can process the ResultSet in a similar manner to what we did in Exercise 2. You should see output similar to the following:

A1111111: LAST1

Exercise 4: Looking at how IMS breaks down SQL queries

The native query language for an IMS database is DL/I. In order for IMS to process SQL queries, those queries will need to be translated into the DL/I equivalent. Sometimes, it's useful for debugging or tuning purposes to look at how a SQL query is broken down.

So where is this translation being done? In this case, the IMS JDBC driver handles all of the translation. It exposes the translation through the Connection.nativeSql() method

Let's start by uncommenting the following line in the main() method.

displayDliTranslationForSqlQuery()

Let's take a look at the the translation for the previous SQL query by adding the following code snippet to the displayDliTranslationForSqlQuery() method:

String sql = "SELECT * FROM PCB01.A1111111";
System.out.println("DL/I translation for '" + sql + "' is:");
System.out.println(connection.nativeSQL(sql));

You should see the following output in your console:

DL/I translation for 'SELECT * FROM PCB01.A1111111' is:
GU   A1111111 

(LOOP)
GN   A1111111 

NOTE: GU/GN VALID only if not overruled by CONCUR_UPDATABLE ResultSet concurrency

The SQL SELECT query which can be considered a batch retrieve, is translated into a series of singleton DL/I calls. The first call is to a GET UNIQUE which retrieves the first record to match a qualifier. The IMS JDBC driver will then repeatedly call GET NEXT until it retrieves all records from the database that match the qualifier.

Congratulation you have completed the lab!