Setting up a JDBC provider for type 4 connections

Use the mqsicreateconfigurableservice or the mqsichangeproperties command to configure a JDBC provider service.

1. Identify the type of database for which you require a JDBCProvider service.

   Supported JDBC drivers and databases are shown in IBM Integration Bus system requirements; support for globally coordinated (XA) transactions is restricted on some platforms and for some databases.

2. Run the mqsireportproperties command to view the list of available JDBCProvider services.
   Substitute the name of your integration node in place of integrationNodeName.

   mqsireportproperties integrationNodeName -c JDBCProviders -a -o AllReportableEntityNames

   The command response lists all the JDBCProvider configurable services that are defined. If you have not created your own definitions, the following list of default supplied services is shown:

   • IBM Db2
   • Informix®
   • Informix_With_Date_Format
   • Microsoft_SQL_Server
   • Oracle
   • Sybase_JConnect6_05

   If you are connecting to an Informix database:

   • Use Informix_With_Date_Format for compatibility with client applications that are dependent on the date format connection attribute that was used by earlier versions of Informix servers.
   • Use Informix for client applications that are not dependent on the date format attribute.
3. View the contents of the relevant JDBCProvider service definition.
   For example, run the following command to display the supplied Oracle definition:

   mqsireportproperties integrationNodeName -c JDBCProviders -o Oracle -r

   The command response lists all the properties for the Oracle definition. If you have not changed this definition, the properties are set to initial values, some of which you must change to create a viable definition. For example, the property databaseName is set to default_Database_Name, and you must change it to identify the specific database that you want to connect to.

   A JDBCProvider service has the following properties:

   • connectionUrlFormat. A pattern that represents the connection URL definition, which is specific to a particular database type. For example, the pattern for IBM DB2® is defined with the following content:

     jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];

     The pattern is used and completed by the integration node at run time when it connects to the database. The values in brackets, for example [serverName], are substituted by the integration node into the pattern by using the values that you have specified on the mqsicreateconfigurableservice, mqsichangeproperties, or mqsisetdbparms commands.

     When the values in square brackets are [user] and [password], they are substituted with user identity and encrypted password values derived using the securityIdentity property.

     The following values and order of preference are used by the integration node to substitute the user ID and password in the pattern:

     1. First, on all platforms: The user ID and password that you have set for the specific database, by using the mqsisetdbparms and specifying the database in the -n parameter.
     2. Second, on all platforms: The user ID and password that you have set for all other databases, by using the mqsisetdbparms and specifying jdbc::JDBC in the -n parameter.
     3. Third, the values are platform-specific:
        1. On Windows: The integration node service ID and password that you specified on the mqsicreatebroker command.
        2. On Linux® and UNIX: The user ID mqsiUser and password ******** (these values are fixed).
        3. On z/OS®: The user ID "" and password "".

     If you are using one of the supplied JDBCProvider services, do not use the mqsichangeproperties command to change the pattern itself; changes made to the pattern might cause unpredictable results.

     If you use the mqsicreateconfigurableservice command to define your own JDBCProvider service, use the mqsireportproperties command to check that the content of the connectionUrlFormat string exactly matches the default supplied provider for the database type that you are using.

     In addition, if you are working on z/OS, and are using the JCL files BIPCRCS and BIPCHPR to define your JDBCProvider service, ensure that your 3270 emulator is configured to use the same code page that the integration node is running in. If the code pages do not match, the connectionUrlFormat string pattern that you define might not be recognized correctly by the integration node.

   • connectionUrlFormat Attr1-5. If the defined URL pattern for a database contains non-standard JDBC data source properties, such as a server identifier, specify these properties in addition to the standard attributes by using one of five general-purpose connection URL attributes. For example:
     • If connectionURLFormat = jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1], connectionUrlFormatAttr1 must contain an Oracle server identifier, which you must supply by defining the value for the property connectionUrlFormatAttr1 on the mqsicreateconfigurableservice or mqsichangeproperties command. The integration node can then substitute all the required values into the required pattern.
     • If connectionURLFormat = jdbc:informix-sqli://[serverName]:[portNumber]/[databaseName]:informixserver=[connectionUrlFormatAttr1]; user=[user];password=[password], connectionUrlFormatAttr1 must contain the name of the Informix instance on the server (typically specified by the INFORMIXSERVER environment variable). This value is case-sensitive.
   • databaseName. The name of the database to which the data source entry enables connections; for example, employees.
   • databaseSchemaNames. Optionally override the name of the database schema used in SQL statements created by message flow nodes. This property is used only by the Mapping node, and only when calling a graphical data map that contains a database transform. For more information, see JDBCProviders configurable service.
   • databaseType. The database type; for example, IBM Db2.
   • databaseVersion. The database version; for example, 9.1.
   • description. An optional property to describe the data source definition.
   • environmentParms. For IBM Db2 and Informix only. An optional property specifying a list of data source properties of the form name=value each separated by a semicolon.
   • jarsURL. The local directory path, on the system on which the integration node is running, where the JAR file that contains the type 4 driver class is located.

     In addition, a storage area network disk can be used for the directory path, but a mapped network drive to a remote computer cannot be used.

     Note: On operating systems that support symbolic links, jarsURL can be set to a directory path that contains the symbolic links to files that are held in another location provided that the operating system implementation of symbolic links is transparent to Java file processing facilities. This method is helpful in scenarios where multiple versions of the JDBC driver exist in the same directory. Creating a symbolic link to the required JDBC driver JAR file can ensure that the type 4 driver class is loaded from the required version of the JDBC driver.
   • jdbcProviderXASupport. An optional property that controls whether the integration node connects to a database server using the XA Protocol. By default this property is set to true. If the database server is not enabled for XA Support, or globally coordinated transactions are not required, set the value to false. In which case the type 4 driver specified using the type4DriverClassName property is used, instead of the type 4 datasource specified in the type4DatasourceClassName property.
   • maxConnectionPoolSize. Optionally set this property to create a JDBC connection pool. For more information, see Using a JDBC connection pool to manage database resources used by an integration server.

   • portNumber. The port number on which the database server is listening; for example, 50000.
   • securityIdentity. A unique security key to perform a second integration node registry lookup to find an entry under the integration node security identities, which store the encrypted password for the user on the associated host system; for example, mysecurityIdentity.

     Create a security identity by using the mqsisetdbparms command, as described in Securing database connections. The value of securityIdentity (for example, mysecurityIdentity) must match the value that you specify following the prefix jdbc:: for the parameter -n on that command.

     The security identity provides a user ID and password value pair, which are used to access the specified data source defined for a particular JDBCProvider service entry. This property is ignored if the connection URL does not contain both a user ID and password pair, which require property values to be substituted for such inserts.

     The default values, which you can set by specifying a ResourceName of jdbc::JDBC on the mqsisetdbparms command, are used under the following conditions:

     • If the securityIdentity is blank, or if you have not changed it from the default value default_User@default_Server, but the identity is required for the connection URL pattern.
     • If you have entered a valid unique security identity key, but it cannot be found under the DSN key.
   • serverName. The name of the server; for example, host1.
   • type4DatasourceClassName. The name of the JDBC data source class name that is used to establish a type 4 connection to a remote database, and to coordinate transaction support. For example, specify com.ibm.db2.jcc.DB2XADataSource for IBM Db2, or specify oracle.jdbc.xa.client.OracleXADataSource for Oracle. You must specify the XA class name when using the getJDBCType4Connection() API call for coordinated transactions. If the database server does not support XA transactions, or you do not want to use the XA protocol, this property is optional and you must set the jdbcProviderXASupport property to false.
   • type4DriverClassName. The name of the JDBC type 4 driver class name that is used to establish a connection. For example, specify com.ibm.db2.jcc.DB2Driver for IBM Db2, or specify oracle.jdbc.OracleDriver for Oracle.
   • useDeployedJars. A property that specifies whether the classloader that contains deployed classes is searched when an attempt is made to load the JDBC client JAR files. By default, this property is set to false.
4. If you want to use the provided definition, run the mqsichangeproperties command to replace default values with the values specific to your database and environment.
   If you are in any doubt about the required values, consult your database administrator, or check the documentation that is provided with your chosen database. Some values depend on how and where you have installed the database product; for example, the property jarsURL identifies the location of the JAR files supplied and installed by the database provider.
5. If you want to create a new configurable service, perhaps because you want to retain the supplied service as a template for future definitions, run the mqsicreateconfigurableservice command to create the definition.

   mqsicreateconfigurableservice integrationNodeName -c JDBCProviders -o provider_name 
   -n list of properties -v list of values

   Enter the command on a single line; the example is split to enhance readability.

   Specify all the properties that are required by the database provider that you have chosen. To specify a list of properties and values, separate the items after each flag with a comma. For example, -n databaseName,databaseType -v EmployeeDB,DB2. If you do not specify all the properties on the mqsicreateconfigurableservice command, you can update them later with the mqsichangeproperties command.

6. When you have set up or modified your JDBCProvider service, you must reload any integration servers which currently use, or intend to use, the JDBCProvider service.