MAINFRAME > Administrator > IMS

Accessing IMS Data Through the JDBC API


IMS provides a JDBC driver so that application developers can write SQL queries in Java against data stored in an IMS database. While JDBC technology has been around for a long time, IMS is the first non-relational database to leverage that technology. By allowing JDBC access, IMS data is now easily accessible to the next generation of developers that are both Java and JDBC savvy.

Because the JDBC API is originally designed for relational databases, there are some considerations that a non-IMS knowledgeable Java developer will want to watch out for. But before delving into the technical details of the implementation, let’s first answer the question of why people would prefer to access IMS through JDBC versus another relational database such as DB2.

IMS, due to its hierarchical nature, is better suited for database queries on a known key. IMS is heavily used in most of the top finance and insurance companies where keyed searches are the norm. For a finance institution, a common key would be the account number, while for insurance institutions it could be a policy or claim number. A relational database would be better suited for queries relating multiple tables or for nested queries.

The IMS Relational Model

In order for IMS to fit into the relational model that JDBC is meant for, the IMS databases hierarchical structure has to be interpreted as a relational one. Most of the basic database concepts map on a one-to-one basis between the hierarchical model and the relational model, and some common terms can be used interchangeably (see Table 1).

In most company environments, the application developers will reference the relational definition, while the database administrators and system programmers will use the hierarchical variation.

As for preserving the hierarchical structure, IMS maintains parent-to-child relationships among its tables by leveraging the primary and foreign key constraints in the relational model. The key column in each table becomes the primary key for that table. The primary keys of a parent table then become the foreign key for its child tables (see Figure 1).

One of the main differences in how IMS stores key values can be seen in Figure 1 concerning the names of the foreign keys. In relational databases, the foreign key physically exists in the child table. This is not the case with IMS. The foreign key values for a given row are actually stored within a key feedback area (see Figure 2).

Because the foreign keys do not physically exists in the child table, IMS will generate the name for the foreign keys with the following convention to avoid potential name bashing with other columns in a table:

	ForeignKeyName = <parenttablename>_<parentkeyname> </parentkeyname></parenttablename>

As mentioned in the previous section, hierarchical databases are not built for relating data between tables. The IMS JDBC driver only supports INNER JOINS for tables that fall along the same hierarchical path. If there is a need to relate tables that are not along the same hierarchical path, there is another method available. IMS allows for logical databases that can define logical relationships between unrelated tables. A database administrator is required to create the logical database. Once the logical database is created, then an application developer can query as if it was a standard database without the need of any JOIN syntax.

IMS Data Storage Considerations

An IMS row can be considered as a huge byte buffer of data and allows for columns to be defined anywhere within the buffer. IMS also allows for multiple columns to be defined in the same buffer area (see Figure 3). It is important for developers to find out which columns overlap as an update to one column will affect the value of the overlapping columns.

IMS also allows for dynamic overlays of that data buffer. This is done by having a control field defined where, depending on the value of the control field, a specific overlay is applied. In Figure 4, the policy data can be interpreted as either a house or a car policy. In this scenario, when a specific overlay is applied, such as the house policy, the fields related to the car policy are interpreted as null data.

IMS Null Value Considerations

IMS does not have a native notion of null data, meaning that a value of null cannot be stored within the database. However in certain scenarios, the IMS JDBC driver will interpret data as null. This was the case in the previous scenario related to dynamic overlays. The other scenario where the IMS JDBC driver will interpret data as null is related to variable length tables.

In IMS it is possible to have a table where each row can vary in size. The size of the row is determined by a two-byte length value that is stored in the first two bytes of the byte buffer. While columns may be defined anywhere within the byte buffer, it is possible for a given row to have a length value that defines a buffer size which do not encapsulate some of the column definitions. In Figure 5, the COMMENTS column does not exist for that given row and is returned back as a null to the application.

IMS Data Type Support

In most IMS shops, table overlays are defined by COBOL or PL/I data structures. The IMS JDBC driver has to convert these data structures to Java for the application to be able to use it. While most data conversions are generally straightforward, IMS does allow for complex structures and arrays that can be nested many levels deep. These data structures are quite common in COBOL and PL/I. While IMS does follow the JDBC specification for ARRAY and STRUCT data types, it also allows for alternative methods to manage these structures.

For the structure defined in Figure 6, the standard JDBC method of reading assumes that the application knows the layout of the structure (see Code Sample 1). IMS provides an alternative method to read data (see Code Sample 2) that provides a more intuitive lookup for the nested columns. To instantiate the structure, the JDBC specification follows a bottom-up approach (see Code Sample 3). IMS allows for a top-down definition (see Code Sample 4).

Similarly for array structures as defined in Figure 7, the standard JDBC method to read and instantiate can be seen in Code Sample 5 and Code Sample 6. The IMS JDBC driver provides a DBArrayElementSet interface to easily navigate an array’s elements similar to how JDBC manages result sets. The IMS alternative method for reading and instantiating an array is shown in Code Sample 7 and Code Sample 8.

IMS also allows support for custom data types. The need for custom data types arose from IMS’ rich history of more than 46 years. Over that time, many IMS shops came up with various ways of defining data in IMS. A common scenario for custom types revolves around date values. In Java, dates are stored as the number of milliseconds since the epoch date of January 1, 1970. Obviously, since IMS has been around before 1970, that is not a common date storage mechanism for IMS data. IMS can store data as the number of days since a different epoch date or even as a packed decimal value. For example, January 1, 1950, could be stored as a packed decimal value 19500101 which would appear in hex as 0x19500101c.

Custom data type converters can be written in Java by extending the com.ibm.ims.dli.types.BaseConverter class. The readObject() method is used for SQL SELECT queries and the writeObject() method is used for SQL INSERT and UPDATE calls.

In order to help customers write new custom converters, IMS provides a ConverterFactory class that can be used to instantiate basic converters, which the custom converter can leverage. The IMS JDBC drivers ships with a sample converter (com.ibm.ims.dli.types.PackedDateConverter) that customers can use as a reference for writing their own converter. The readObject() implementation is provided in Code Sample 9.

In the sample, the ConverterFactory is used to instantiate a PackedDecimalTypeConverter that converts the packed decimal value to a BigDecimal data type. This was done to illustrate how the ConverterFactory can help reduce the amount of code needed in the custom converters.

In order to deploy a custom converter, the DBA needs to update the catalog to reference the custom converter class for a given column. The IMS JDBC driver will automatically find this information in the IMS catalog and will invoke the converter behind the scenes. The converter themselves will need to be compiled and deployed in a location where it will be loaded by the same Java class loader that loads the IMS JDBC driver.

It should be noted that both the complex structure and customer data type support are only available in IMS V12, as both features require the IMS catalog.

Speeding up IMS JDBC Application Development

While IMS has provided JDBC support as of IMS V7, that is with the IMS Classic JDBC driver. It is recommended that all JDBC application development be done with the IMS Universal JDBC driver that was made available in IMS V11, which has been optimized to leverage the latest IMS features. With these programming considerations in mind, a Java application developer will know enough about IMS to quickly code applications that access IMS data.

Richard Tran is an IBM software engineer working as the IMS Open Database team lead. He is involved in integrating IMS with other business solutions such as business analytics and reporting, content discovery and web enablement.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.


comments powered by Disqus

Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Bringing IMS and SOA Together With IMS Connect

New requirements for IMS Connect functionality could make implementing an SOA environment with IMS easier and more flexible.

Celebrating 40 Successful Years

IMS version 10 supports synchronous and asynchronous callout

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters