About this task
When you include a
DatabaseRetrieve,
DatabaseRoute,
JavaCompute,
Mapping, or Java™ user-defined node in a message flow, and
interact with a database in that node, the integration node must
establish a connection with the database to fulfill the operations
that are performed by the node. You must define a JDBCProvider
configurable service to provide the integration node with the information
that it needs to complete the connection.
Important: When naming your JDBCProvider service, consider
the following requirements:
- If you want to use your JDBCProvider service with a JavaCompute node, or with a Java user-defined node, the name
of your JDBCProvider service must match the datasourceName parameter
in the getJDBCType4Connection() API call from the
node.
- If you want to use your JDBCProvider service with a Mapping node, the name of your
JDBCProvider service must match the database name that is used
by the database transforms in your Graphical Data Map. For each
database transform, the database name is determined by the database
definition (.dbm file) in the Data Design
project that was used to create the map.
- If you want to use your JDBCProvider service with a DatabaseRetrieve node, or with
a DatabaseRoute node, the
name of your JDBCProvider service must match the value of the Data
source name property of the node.
A JDBCProvider configurable service supports connections
to one database only; you must create a service for each database
that your nodes or Java applications
connect to.
To set up a JDBC provider for type 4 connections
by using the web user interface, see Creating configurable services.
To
set up a JDBC provider for type 4 connections by using the mqsicreateconfigurableservice or
mqsichangeproperties commands,
complete the following steps:
- 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.
- 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:
- 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.
- 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 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:
- 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.
- 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.
- Third, the values are platform-specific:
On Windows:
The integration node service ID and password that you specified on
the mqsicreatebroker command.

On Linux and UNIX: The user ID mqsiUser and
password ******** (these values are fixed).
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,
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 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.
- 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 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 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.
- 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.
- 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.
- 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.
What to do next
If required, set up security for the JDBC connection, set
up the environment to include the JDBCProvider service in globally
coordinated transactions, or both.