Setting up a JDBC provider for type 4 connections

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

mqsireportproperties broker_name -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:

• 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.

mqsireportproperties broker_name -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 DB2® is defined with the following content:

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

  The pattern is used and completed by the broker at run time when it connects to the database. The values in brackets, for example [serverName], are substituted by the broker 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 broker 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 broker 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 broker is running in. If the code pages do not match, the connectionUrlFormat string pattern that you define might not be recognized correctly by the broker.

• 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 broker 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, DB2.
• databaseVersion. The database version; for example, 9.1.
• description. An optional property to describe the data source definition.
• environmentParms. For 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 broker 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.

• 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 broker registry lookup to find an entry under the broker 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 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 DB2, or specify oracle.jdbc.OracleDriver for Oracle.
• jdbcProviderXASupport. An optional property that controls whether the broker 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 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.

mqsicreateconfigurableservice broker_name -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.