JDBCProviders configurable service

Select the objects and properties that you want to change for the JDBCProviders configurable service.

For JDBCProviders configurable services, you must stop and start the integration server for a change of property value to take effect.

Supplied configurable services that are created for each integration node Properties for each configurable service that is defined Description of properties
DB2®
Informix®
Informix_With_Date_Format
Microsoft_SQL_Server  
Oracle
Sybase_JConnect6_05
MySQL

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 fixed content:
jdbc:db2://[serverName]:[portNumber]/[databaseName]
:user=[user];password=[password];

Do not use the mqsichangeproperties command to change the pattern itself; changes made to the pattern might cause unpredictable results. This situation is due to the behavior at run time when the integration node substitutes any parts of the URL found between square brackets (such as [serverName] or [portNumber]) with the corresponding values found in a configurable service property that matches the name of that URL part.

The connectionUrlFormat [user] and [password] parameters are a special case. These parameters are substituted with user identity and encrypted password values derived using the securityIdentity property.

For information about using substitution strings for special characters, see Substitution strings for special characters.
connectionUrlFormatAttr1
connectionUrlFormatAttr2
connectionUrlFormatAttr3
connectionUrlFormatAttr4
connectionUrlFormatAttr5

If the specified URL format contains non-standard JDBC data source properties, such as a server identifier, specify one of five general-purpose connection attributes to define these additional properties.

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.

For information about using substitution strings for special characters, see Substitution strings for special characters.

databaseName

The name of the database to which the data source entry enables connections; for example, employees.

databaseSchemaNames

The name of the database schema to include 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.

Valid values are:
  • An empty string. This is the default value, and indicates that your nodes do not include a database schema name in the SQL statements that they create.
  • The name of a single database schema. Your nodes include this schema name in all SQL statements that they create.
  • One or more database table and database schema name pairs, in the form: 
    tableA:schema01;tableB:schema02
    . If a table has a table:schema name pair defined in this way, your nodes include the corresponding database schema name in the SQL statements that they create. If a table does not have a table:schema pair defined, your nodes do not include a database schema name in the SQL statements that they create.
  • The name of a database schema to use as a default, followed by one or more database table and database schema name pairs, in the form: 
    defaultschema;tableA:schema01;tableB:schema02
    For tables that have a table:schema pair, your nodes include the corresponding database schema name in the SQL statements that they create. If a table does not have a table:schema pair defined, your nodes include the default database schema name in the SQL statements that it creates.
  • The string useProvidedSchemaNames, to use the schema name provided in the map (the schema name used at design time).

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

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.

portNumber

The port number on which the database server is listening; for example, 50000.

securityIdentity

A unique key to identify a DSN entry, which provides the user ID and password credentials that are required to connect to the database system. Use the mqsisetdbparms command to create a DSN entry, as described in Securing database connections. For example, set "JdbcSecurityIdentity" to use the credentials that are set by running the following command:
mqsisetdbparms IB10NODE -n jdbc::JdbcSecurityIdentity -u userid -p pwd

At run time, the integration node uses the security credentials to substitute for the strings [user] and [password] that might occur in the connectionUrlFormat, connectionUrlFormatAttr or environmentParms properties.

There are cases where an alternative authentication method is required. For instance, when a database server takes the user identity and password from an SSL certificate to obtain a secure JDBC connection. In this case, the security key must be set to jdbc::none, which disables connection logon authentication.

serverName

The name of the server; for example, host1.

type4DatasourceClassName

The name of the JDBC type 4 data source class name that is used to establish a type 4 connection to a remote database and for coordinated transaction support; for example, com.ibm.db2.jcc.DB2XADataSource.

type4DriverClassName

The name of the JDBC driver class name that is used to establish a connection; for example, com.ibm.db2.jcc.DB2Driver.

jdbcProviderXASupport

This property is optional. Setting this property to true indicates that the selected JDBC provider supports XA coordinated transactions, and the database server is enabled to use the XA transaction protocol. The JDBC type 4 data source class that is specified in the type4DatasourceClassName property is used to establish the connection.

Setting this property to false indicates that the selected JDBC provider either does not support XA coordinated transactions, or the database server is not enabled to use the XA transaction protocol. The JDBC type 4 driver class that is specified in the type4DriverClassName property is used to establish a connection. Specify a value of false if you prefer to use the JDBC driver class rather than the data source class.

The default value for this property is true.

If you set this property to true, and the selected JDBC provider does not support XA transactions, an exception is raised.

If you set this property to false, but the Coordinated Transaction message flow property is selected, a non-XA JDBC connection is created as a locally coordinated resource, and not as part of the global transaction.
 

maxConnectionPoolSize

Connection pooling is switched on for a JDBCProviders configurable service when the value for this attribute is non-zero and within the range 1 - 100000.

The connection pool limits the number of connections for that configurable service for each integration server. Note that, after you have set a value for this attribute, you must stop and restart the integration server for the changes to take effect.

The default value for this attribute is zero and this value turns connection pooling off for the integration server.

All message flows in an integration server that use the same JDBCProviders configurable service share the same connection pool. Multiple JDBCProviders configurable services that refer to the same database have their own pool of independently controlled connections to that database
 

useDeployedJars

Setting this property to true means that 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.

Substitution strings for special characters

Special characters such as a quotation mark, or ampersand, can be embedded in the JDBC connection URL template, or in its related connectionUrlFormatAttr properties. Such special characters need to be escaped in the command syntax by surrounding them with a backslash. As an alternative to escaping the special characters, you can use substitution strings instead. The table below lists the supported substitutions strings and the values that that are required in the connectionUrlFormat value or its related connectionUrlFormatAttr properties:
Substitution string Value required in connectionUrlFormat or connectionUrlFormatAttr property
" "
& &
> >
&lt; <
&apos; '
&tilde; ~
For example, to embed double quotation marks around the password box in the value for a connectionUrlFormat property, use the followng syntax:
mqsichangeproperties IBNODE -c JDBCProviders -o Oracle -n connectionUrlFormat -v "jdbc:oracle:thin:[user]/&quot;[password]&quot;@[serverName]:[portNumber]:[connectionUrlFormatAttr1]"
Note that the value specified in the -v parameter must be enclosed within double quotation marks.
In this example for an Oracle JDBC data source, the required connectionUrlFormat for use at runtime is: 
jdbc:oracle:thin:[user]/"[password]";@[serverName]:[portNumber]:[connectionUrlFormatAttr1]