ims-java-jdbc

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

Getting started

This example is designed around using Visual Studio Code, but feel free to use your IDE of choice. If you get lost at any step, feel free to reference the solution provided in the MyIMSJavaApplicationSolutions.java file.

Pre-requisites

Software:

• Java JDK 17 or later
• Visual Studio Code
• Required Extensions: Project Manager for Java, Java Extension Pack

Skills:

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

Download the ims-java-jdbc project from GitHub

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

Ensure that the Project Manager for Java and Java Extension Pack extension is installed

This can be downloaded from the Extensions Marketplace which can be opened by selecting the Extensions logo on the left sidebar

Importing the project

The ims-java-jdbc project is designed as a Visual Studio Code project and will need to get imported into your Visual Studio Code development environment.

1. Open up Visual Studio Code
2. In the top menu, select File->Open Folder and import the unzipped project files.
3. Expand out the following folders: ims-java->src
4. Double click on the MyIMSJavaApplication.java file. The solution file ('MyIMSApplicationSolutions.java') is also in this folder.
5. The Project Manager for Java extension should automatically set up your project.
6. Ensure that your "src" file with your Java code is linked under the Java Projects menu on the bottom left.
7. Ensure that your IMS Universal Driver (imsudb.jar) is linked under the Referenced Libraries folder on the bottom left.
8. You can now run your project by selecting the play button on the top right corner to test that your Java Environment has been properly set up.
9. If your project compiles successfully, you are now ready to create your program!

Writing a distributed Java application

In the first part of the sample, you will develop a distributed Java application. This application will be able to access and manipulate IMS data while running on your personal system.

Connections to IMS resources on the mainframe from a distributed environment requires a TCP/IP connection through an IMS Connect TCP/IP gateway. You will need to determine your IMS Connect DB Port Number in order for your application to create a connection.

Adding Field Metadata to the Parts Database

In order to see the "non-key" fields that are shown in the proceeding examples you will need to add several fields to the DI21PART database. The DDL accomplish this can be found under src->resources->DI21PART.ddl. This code can be ran using yout IMS SQL executor of choice (IMS Explorer for VsCode, IMS Explorer for Development, IMS SPUFI). A Catalog Import will need to be ran after executing the DDL. This can be done by running this IMS Type-2 command.

IMPORT DEFN SOURCE(CATALOG)

Exercises 1 through 7 will cover how to use the IMS JDBC driver to issue and work with SQL 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. z/OS hostname/IP address
2. Port number for IMS Connect DRDA
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: z/OS hostname/IP Address
2. port number: IMS Connect DB Port Number
3. driver type: 4
4. user: Your User ID
5. password: Your Password
6. database name: DFSSAM09" <-- This is the PSB name for the parts database

This lab is designed around a version of this DFSSAM09 with its field metadata already imported into the catalog. If your version of this PSB has not had its metadata imported into the catalog you will only be able to see the key fields in each segment. If you have a created a separate parts database PSB with populated metadata you may use that PSB as well.

Setter example for host:

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

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();

For this sample program you will be working with the PSB - DFSSAM09

This PSB defines the PCB - PCB01 which connects to the database DI21PART

DI21PART is a simple database that represents a parts inventory. Refer to the image below to see the segments and fields in this database.

You can now run your application. 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: 15166

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

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 you have a connection to IMS, the next step is to discover what databases are available for access. This is determined by the PSB that was connected to in the last exercise. This database metadata information is stored in the IMS catalog, and has been mapped to standard JDBC Database Metadata 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, uncomment the following line in the main() method:

displayMetadata();

Now navigate to the displayMetadata() implementation, you'll notice a connection to the IMS system is established 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("DFSSAM09", 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, ResultSetMetaData was also used to identify information on the ResultSet returned by the getSchemas() call. ResultSetMetaData will be used in most of the following exercises in order to display a readable output like the following:

Displaying IMS PCB metadata
TABLE_SCHEM: PCB01
TABLE_CATALOG: DFSSAM09
PCB_PROCESSING_OPTIONS: AP
DBD_NAME: DI21PART
DBD_TIMESTAMP: 2222110340239

You can dig even further into the database segments and fields with the following query. Use the same method as above to print the ResultsSet object

If you supply the PCB name you can view the segments/tables contained within the database

// Display IMS segment information
rs = dbmd.getTables("DFSSAM09", "PCB01", null, null);

Your output will display all of the segments/tables in the database. If you supply a table name you can view the fields in a table. Select a "TABLE_NAME" from the previous output and execute the getColumns() function.

// Display IMS field information
rs = dbmd.getColumns("DFSSAM09", "PCB01", "_______", null);

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

//displayMetadata();

Exercise 3: Querying the database

Now that you have a good understanding of what your database looks like. You can go ahead and start building queries against the database. Start by uncommenting the following line in the main() method.

executeAndDisplaySqlQuery();

Navigate to the executeAndDisplaySqlQuery() method and write a SQL SELECT statement to issue a read request against the database.

An initial query has already been written SELECT * FROM PCB01.PARTROOT. From the database metadata discovery, it is know that the PSB DFSSAM09 contains a PCB PCB01 which has a segment PARTROOT.

The way you would execute a read query is through the Statement.executeQuery() method. You 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 was done in Exercise 2. You should see output similar to the following:

Displaying query results
PARTKEY: 02AN960C10       
PARTNAME:          WAS
PARTDESC:      WASHER          

PARTKEY: 02CK05CW181K     
PARTNAME:          CAP
PARTDESC:      CAPACITOR    

Now try another query! You will be selecting the BACKORDR table which is a child of PARTROOT

SELECT * FROM DBPCB01.BACKORDR WHERE PARTROOT_PARTKEY = '027618032P101'

You can use a key from the parent table to find all children under the key. Note: The parent key variable will always take the form of "(TABLE NAME)_(TABLE KEY NAME)"

Lastly, select some fields from the bottom of the table.

Using the information previously covered in this exercise, can you write a query to find all entries in the BACKORDR table with parent keys: PARTKEY = '027618032P101' and STOCKEY =' 0025900326'?

That completes Exercise 3. Go ahead and disable the following line in the main() method by commenting it out:

//executeAndDisplaySqlQuery

Exercise 4: Inserting a record into the database

Now that you have a base understanding of what the IMS database looks like and what data resides in that database, go ahead and insert in a new record into our parts database. Let's start off by uncommenting the following lines in the main() method

executeASqlInsert();
executeAndDisplaySqlQuery();

Then navigate to the executeASqlInsert() method. For this exercise you will be focusing on the PARTROOT table, you will need to provide a unique PARTKEY as well as a unique PARTNAME and PARTDESC. The format for a SQL INSERT statement can be found here. Similar to what was done for a SQL SELECT, you will be using a Statement object to issue the SQL statement. However instead of using the executeQuery() method which is for database reads, you will use the executeUpdate() method for database inserts, updates and deletes. The following code snippet will insert a record into the database. Make sure to modify the values for the entry you want to add.

sql = "INSERT INTO PCB01.PARTROOT (PARTKEY, PARTNAME, PARTDESC) VALUES ('027618032P202', '', ' CAT 5 ETHERNET CABLE')";
Statement st = connection.createStatement();
System.out.println("Inserted " + st.executeUpdate(sql) + " record");

Note: PARTNAME is derived from the field information in PARTDESC so this field is left blank in this insert statement

Run the Java application and verify that your new record was inserted properly. You should see something like the following in your output:

Inserted 1 record
PARTKEY: 027618032P202    
PARTNAME:      CAT 5 E
PARTDESC:  CAT 5 ETHERNET CABLE    

What happens if you try to insert the same record again? An error would be expected as you cannot have two records with the same unique key. Try running your application again. You should see the following error message

com.ibm.ims.drda.base.DrdaException: An error occurred processing the database DFSSAM09. AIB return code: 0x900. AIB reason code: 0x0. AIB error code extension: 0x0. DBPCB status code: II.

You'll notice that you gan an AIB return and reason code in addition to a DBPCB status code. This error information is actually returned by the IMS database as a result of attempting to execute the translated DL/I query. Looking at the IMS knowledge center, You can see that the II status code is returned on a DL/I ISRT call when a record already exists in the database. Before moving on to the next exercise, make sure you comment out any code we added to the executeASqlInsertOrUpdate() method.

Make sure to clean up your application by going back into the main() method and commenting out the following lines:

//executeASqlInsert();
//executeAndDisplaySqlQuery();

Exercise 5: Updating a record in the database

Take the record you inserted in the previous exercise and update it using a SQL UPDATE statement. The format for a SQL UPDATE can be found here.

Uncomment the following lines in the main() method.

executeASqlUpdate();
executeAndDisplaySqlQuery();

Then navigate to the executeASqlUpdated() method.

Make sure to only update the record you inserted earlier. This can be done by qualifying on the PARTKEY field which is a unique field. The following code snippet shows how to issue a SQL UPDATE query, make sure to modify the fields and qualifier as necessary.

sql = "UPDATE PCB01.PARTROOT SET PARTDESC=' CAT 6 ETHERNET CABLE' WHERE PARTKEY='027618032P202'";
Statement st = connection.createStatement();
System.out.println("Updated " + st.executeUpdate(sql) + " record(s)");

After running your application, you should see similar output in your console:

Updated 1 record(s)
Displaying query results
PARTKEY: 027618032P202    
PARTNAME:      CAT 6 E
PARTDESC:  CAT 6 ETHERNET CABLE

Make sure to clean up your application by going back into the main() method and commenting out the following lines:

//executeASqlUpdate();
//executeAndDisplaySqlQuery();

Exercise 6: Delete a record in the database

Let's clean up our database!

Let's start off by uncommenting the following lines in the main() method

executeASqlDelete();
executeAndDisplaySqlQuery();

Then navigate to the executeASqlDelete() method.

Issue a SQL DELETE against the database to remove the record you just inserted/updated. If you get stuck you can use the previous exercises as a reference.

sql = "DELETE FROM ______ WHERE _____________";

Make sure to clean up your application by going back into the main() method and commenting out the following lines:

//executeASqlDelete();
//executeAndDisplaySqlQuery();

Exercise 7: 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.PARTROOT";
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 DBPCB01.PARTROOT' is:
GU   PARTROOT 

(LOOP)
GN   PARTROOT 

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.

Feel free to take a look at the DL/I translations for the other queries used in this lab. You should notice how much easier it is to use SQL to access this database over DL/I calls.

Congratulations you have completed all of the excersises!