Connecting to an IMS database by using the JDBC DriverManager interface

A JDBC application can establish a connection to a data source using the JDBC DriverManager interface, which is part of the java.sql package.

The Java™ application first loads the JDBC driver by invoking the Class.forName method. After the application loads the driver, it connects to a database server by invoking the DriverManager.getConnection method. For example: 
Connection conn = DriverManager.getConnection(url);
To connect to an IMS™ database by using the DriverManager interface in your IMS Universal JDBC driver application:
  1. Load the IMS Universal JDBC driver with the DriverManager interface by invoking the Class.forName method with the argument com.ibm.ims.jdbc.IMSDriver.
  2. Connect to the IMS database by invoking the DriverManager.getConnection method. The URL represents a data source, and indicates what type of JDBC connectivity you are using.
    • For type-4 connectivity, specify the URL in the following form: 
      Read syntax diagramSkip visual syntax diagram
>>-jdbc:ims:--//--DatastoreServer------------------------------->

>--+---------------+--/--DatabaseName--+------------------------------+-><
   '-:--PortNumber-'                   |    .-----------------------. |   
                                       |    V                       | |   
                                       '-:----property--=--value--;-+-'   
    • For type-2 connectivity, specify the URL in the following form: 
      Read syntax diagramSkip visual syntax diagram
>>-jdbc:ims:--DatabaseName--+------------------------------+---><
                            |    .-----------------------. |   
                            |    V                       | |   
                            '-:----property--=--value--;-+-'   

    The parts of the URL have the following meaning:

    jdbc:ims:
    Indicates that the connection is to an IMS database.
    PortNumber
    The TCP/IP server port number to be used to communicate with IMS Connect. The port number is defined using the DRDAPORT parameter on the ODACCESS statement in the IMS Connect configuration PROCLIB member. The default port number is 8888. Do not set this property when using type-2 connectivity.
    MetadataURL

    The location of the database metadata representing the target IMS database.

    This property is deprecated. Use DatabaseName instead.

    The MetadataURL property is the fully qualified name of the Java metadata class generated by the IMS Enterprise Suite Explorer for Development. The URL must be prefixed with class:// (for example, class://com.foo.BMP255DatabaseView).

    In a J2C Connection Factory environment, the MetadataURL property can be overridden for an individual connection without affecting the default value specified for the resource adapter.

    DatabaseName

    The location of the database metadata representing the target IMS database.

    The DatabaseName property can be specified in one of two ways, depending on whether the metadata is stored in the IMS catalog or as a static metadata class generated by the IMS Enterprise Suite Explorer for Development:

    • If your IMS system uses the IMS catalog, the DatabaseName property is the name of the PSB that your application uses to access the target IMS database.
    • If you are using the IMS Explorer for Development, the databaseName property is the fully qualified name of the Java metadata class generated by the IMS Explorer for Development. The URL must be prefixed with class:// (for example, class://com.foo.BMP255DatabaseView).

    In a J2C Connection Factory environment, the DatabaseName property can be overridden for an individual connection without affecting the default value specified for the resource adapter.

    DatastoreServer
    The name or IP address of the data store server (IMS Connect). You can provide either the host name (for example, dev123.svl.ibm.com) or the IP address (for example, 192.166.0.2). Do not set this property when using type-2 connectivity.
    property
    One of the following connection properties:
    Start of changeallMetadataEnd of change
    Start of change

    Optional. When this property is set to true, the DatabaseMetadata interface returns information for all resources in the IMS catalog. When the property is set to false, the DatabaseMetadata interface returns information for the allocated PSB. The default value for this property is false.

    IMS 13 APAR PI62580 (PTF UI39459) is required for this property.

    End of change
    datastoreName

    Optional. The name of the IMS data store to access.

    • When using type-4 connectivity, the DatastoreName property must match either the name of the data store defined to ODBM or be blank. The data store name is defined in the ODBM CSLDCxxx PROCLIB member using either the DATASTORE(NAME=name) or DATASTORE(NAME=name, ALIAS(NAME=aliasname)) parameter. If an alias is specified, you must specify the aliasname as the value of the datastoreName property. If the DatastoreName value is left blank (or not supplied), IMS Connect connects to any available instance of ODBM as it is assumed that data sharing is enabled among all datastores defined to ODBM.
    • When using type-2 connectivity, set the DatastoreName property to the IMS subsystem alias. This is not required to be set for the Java Dependent Region run time.
    loginTimeout
    Optional. Specifies the number of seconds that the driver waits for a response from the server before timing out a connection initialization or server request. Set this property to a non-negative integer for the number of seconds. Set this property to 0 for an infinite timeout length. Do not set this property when using type-2 connectivity.
    password
    The password for the connection to IMS Connect provided by your RACF® administrator. Do not set this property when using type-2 connectivity.
    sslConnection
    Optional. Indicates if this connection uses Secure Sockets Layer (SSL) for data encryption. Set this property to true to enable SSL, or to false otherwise. Do not set this property when using type-2 connectivity.
    Start of changeStart of changesslKeyStoreTypeEnd of changeEnd of change
    Start of changeStart of changeOptional. Specifies the format of the file that contains cryptographic objects needed to establish a secure socket connection. The valid values are JKS and PKCS12. This value is only used when sslConnection is set to true and sslKeyStoreType is not specified. The sslKeyStoreType parameter defaults to JKS. End of changeEnd of change
    Start of changeStart of changesslSecureSocketProtocolEnd of changeEnd of change
    Start of changeStart of changeOptional. Specifies the cryptographic communication protocol for the new connection. Specify a protocol that is supported by the server and provides the highest level of security. The valid values are SSL, SSLv3, TLSv1.1, and TLSv1.2. This value is only used when sslConnection is set to true. If sslConnection is set to true and sslSecureSocketProtocol is not specified, a default protocol will be determined at runtime by the JRE and the server.End of changeEnd of change
    Start of changesslTrustStoreLocationEnd of change
    Start of changeOptional. Specifies the location of the cryptographic trust store file for the new connection. This value is only used when sslConnection is set to true.End of change
    Start of changesslTrustStorePasswordEnd of change
    Start of changeOptional. Specifies the password to access the cryptographic trust store file. This value is only used when sslConnection is set to true.End of change
    Start of changesslKeyStoreLocationEnd of change
    Start of changeOptional. Specifies the location of the cryptographic key store file for the new connection. This value is only used when sslConnection is set to true.End of change
    Start of changesslKeyStorePasswordEnd of change
    Start of changeOptional. Specifies the password to access the cryptographic key store file. This value is only used when sslConnection is set to true.End of change
    user
    The user name for the connection to IMS Connect provided by your RACF administrator. Do not set this property when using type-2 connectivity.
    Start of changesignedCompareEnd of change
    Start of changeOptional. When this property is set to true, special SSAs are generated to support ranged queries over signed data types. If the property is set to false, standard binary comparisons are performed based on the binary representation of the data type value. Setting the value to false can increase performance but might result in incorrect results. The default value for this property is true.End of change
    Start of changeflattenTablesEnd of change
    Start of changeStart of changeOptional.End of change Indicates whether to produce a flattened view of the database tables. A value of true exposes the sub-elements of a STRUCT or an ARRAY as additional columns of the table. The default value is false.
    • IMS Explorer flattens the copybook structures when you import the copybook. Although the copybook itself remains unchanged in the IMS catalog, the information about the structure of each table is altered for that particular connection.
    • The the flattenTables property allows you to query the fields in complex structures directly. For more information about support for flattening complex structures, see Support for flattening complex structures.
    Restriction: The flattenTables connection property supports static arrays and structures only. Dynamic arrays are not altered.
    End of change
    Start of changet2OutputBufferSizeEnd of change
    Start of changeOptional. The size of the output buffer in bytes for the results from a SELECT operation for a type-2 connection.

    The minimum value for t2OutputBufferSize is 500000. If any value less than 500000 is set, this property value will be adjusted to 500000. There is no maximum bound. The default value is 1280000.

    End of change
    Start of changetreatInvalidDecimalAsNullEnd of change
    Start of changeOptional. Indicates whether to interpret certain Decimal values that appear invalid in Java applications (such as PACKEDDECIMAL and ZONEDDECIMAL with invalid sign bits) as null. By default, this property is false, and a conversion exception is thrown when the Java applications are processing invalid values. End of change
    currentSchema
    Optional. Specifies the default schema name that is used to qualify unqualified database objects in dynamically prepared SQL statements.
    dbViewLocation
    Optional. Specifies the fully qualified path to a databaseView metadata class. You can use this property to include a metadata class that is not located in your project path.
    dpsbOnCommit
    Optional. Set this property to true to deallocate the PSB when a commit occurs.
    Recommendation: Do not set this property to true except in a managed environment with integrated connection pooling.
    fetchSize
    Optional. Gives the client a hint about the number of rows to get from the database when more rows are needed. The number specified for this property only affects data retrieved with the current connection. If the value specified is 0, all of the applicable rows are returned.

    The default value for this property is 0 for both managed and unmanaged connections.

    llField
    Optional. Setting this property to true exposes the LL field data as a normal column in the result set. You can modify the LL field value to change the length of a variable length segment instance.
    maxRows
    Optional. Specifies the maximum number of rows to return in a query result set. The default value is 0, which returns all of the applicable rows in the result set.
    traceFile
    Optional. Specifies the name of the trace file for the connection.
    traceFileAppend
    Optional. If the specified trace file exists, setting this property to true specifies that the trace data for the new connection must be appended to the existing trace file instead of overwriting it.

    This property is ignored if no value is specified for traceFile.

    traceDirectory
    Optional. Specifies the file system directory where the trace file is located. By default, this path is the directory where the application is executed.

    This property is ignored if no value is specified for traceFile.

    traceLevel
    Optional. Specifies which traces are enabled for the connection. The valid values for this property are defined in the Java API documentation for the IMSDataSource class.

    By default, all traces are disabled.

    This property is ignored if no value is specified for traceFile.

    Trace level traceLevel package value traceLevel constant field in IMSDataSource traceLevel demical value
    All com.ibm.ims.db.opendb.* TRACE_ALL -1
    DL/I com.ibm.ims.db.opendb.dli.* TRACE_DLI 28
    DRDA com.ibm.ims.db.opendb.drda.* TRACE_DRDA 1
    JDBC com.ibm.ims.db.opendb.jdbc.* TRACE_JDBC 32
    Java EE com.ibm.ims.opendb.spi.*
    com.ibm.ims.db.opendb.cci.*    		 TRACE_JEE 192
    value
    A valid value for the connection property.
    To set the sslConnection and loginTimeout properties, use a java.util.Properties object. For example, the following sample code shows how to enable SSL and set the timeout value to 10 seconds:
    Properties props = new Properties();
props.put("sslConnection", "true");
props.put("timeout", "10");
  3. For type-4 connectivity, you must specify a user ID and password in one of the following ways: through the connection URL, through parameters, or through a java.util.Properties object. To set the user ID and password for the connection through parameters, use the form of the getConnection method that specifies user and password. For example:
    String url = 
"jdbc:ims://tst.svl.ibm.com:8888/class://BMP2.BMP2DatabaseView";
String user = "MyUserID";
String password = "MyPassword";
Connection conn = DriverManager.getConnection(url, user, password);
    To set the user ID and password for the connection through a java.util.Properties object, use the form of the getConnection method that specifies a java.util.Properties object. For example:
    Properties props = new Properties();    
props.put( "user", "MyUserID" );        
props.put( "password", "MyPassword" );  
String url = 
"jdbc:ims://tst.svl.ibm.com:8888/class://BMP2.BMP2DatabaseView";
Connection conn = DriverManager.getConnection(url, props);
  4. After your application has finished with the connection, close the connection using the close method on the Connection interface.
The following code example shows how to create a type-4 connection to an IMS database from your IMS Universal JDBC driver application using the DriverManager interface:
Connection conn = null;

// Create Properties object
Properties props = new Properties();    

// Enable SSL for connection
props.put("sslConnection", "true");

// Set datastoreName for connection
props.put( "datastoreName", "IMS1" );  

// Set timeout for connection
props.put("loginTimeout", "10");

// Set user ID for connection
props.put( "user", "myUserID" );        

// Set password for connection
props.put( "password", "myPassword" );  

// Set URL for the data source
Class.forName("com.ibm.ims.jdbc.IMSDriver");

// Create connection
conn = DriverManager.getConnection
("jdbc:ims://tst.svl.ibm.com:8888/class://BMP2.BMP2DatabaseView", 
props);
Alternatively, you can specify the connection properties in the URL. For example: 
String url=""jdbc:ims://tst.svl.ibm.com:8888/class://"
   + "BMP2.BMP2DatabaseView:datastoreName=IMS1;"
   + "loginTimeout=10;sslConnection=true;user=myUserID;password=myPassword;";
Connection conn = DriverManager.getConnection(url);
Parent topic: Connecting to IMS using the IMS Universal JDBC driver
Related tasks:
Configuring the IMS Universal drivers for SSL support
Related information:
https://imsinsiders.wordpress.com/2016/04/18/how-to-enable-the-ims-jdbc-trace