IBM® Integration Bus can access databases
that are set up on the local computer or on a remote server, subject
to restrictions.
IBM Integration Bus supports the
databases that are listed in
IBM Integration Bus Requirements (SOE).
Data types considerations for mapping database
content during development
During the development phase,
you must configure a database before you can access the data in
a map.
To configure a database, you define a database definition
file. For more information, see Creating a database definition (.dbm file) by using the New Database Definition File wizard.
A
database definition file holds the physical data model that details
all the database resources, such as the schema, the tables, and
other resources, that you need access to.
Note: A
database definition file in the IBM® Integration Toolkit is not automatically
updated. If you modify your database, you must recreate the database
definition file describing the connection to the database.
For each database transform in your message map, the Graphical
Data Mapping editor uses the database
definition file (.dbm file) to determine
the name and structure of the database that you want to access.
When
you map a database table in a message map,
the data types of the database columns are provided by the database
definition file.
You can use the xs:type cast
transform or custom transforms, such as Custom XPath,
to ensure that data from elements mapped to the database columns
are of the correct type.
Data types considerations for mapping database
content at run time
At run time, a JDBC Provider configurable
service is used to determine the database to connect to. You must
enable the JDBC connection to the database before you execute
a map that requires data from a database. See Enabling
JDBC connections to databases.
Note: The
name of the JDBCProvider service must be identical to the name of
the database.
When you use a
Mapping node that includes
a map with database transforms, you must consider the database
server behavior:
- If your database server can provide at run time table parameter
meta data, the Mapping node
validates the input data to a database transform in a map and
performs any allowed implicit type casting, before sending the
SQL statement to the database for execution.
Note: The Mapping node applies
any required xs:type cast transforms before
passing data in SQL statements. If there is no valid type cast
between the type of the presented value and the type defined by the
database meta data, a run time exception is
thrown by the Mapping node
that is executing the map.
- If your database server cannot provide at run time table parameter
meta data, during development, you must define the xs:type cast
transform to ensure that all data values that will be passed to the
database, as parameters to Where clauses or to populate a column
in a table, match the data type requirements of the database
server.
Note: Not all database servers supported by
IBM Integration Bus provide querying of table
meta data in a way
IBM Integration Bus can
currently process.
IBM Integration Bus cannot
currently obtain table meta data when connected to the following
database server types:
- Microsoft_SQL_Server
- Oracle
- Sybase_JConnect6_05
- solidDB®
When a database system cannot provide table
meta data at run time, the Mapping node cannot perform
validation and implicit type casting. The data element values are
passed to the database server in the type they are presented, without
any casting being performed. This can result in the database system
rejecting the value and throwing a database exception.
Behavior when a database server can provide at
run time table meta data information
The resulting
data type of values that are set to the database systems are determined
depending on how the input element is wired to the database transform:
- Column values set via Move transforms
from an message tree element are passed as its given type when
it is a base SQL type. For example: Integer, otherwise as character
string formatted as per the IBM Integration Bus getValueAsString() MbElement
method.
- Column values set via Custom XPath, Custom
Java or Custom ESQL functions
are passed as the type returned by the function.
- Column values set via Assign transform
will always be passed as character string. If you require a
specific type to be assigned, you must use a Cast transform
of the appropriate xs type constructor. For
example, to assign the value 1 to an Integer type column, use
the xs:int() Cast transform
and set a value of '1' instead of an Assign transform.
When using values in Where clauses
for Select, Update and
Delete, the types are determined as follows:
- Literal values are typed according to standard SQL syntax, such
as quote character strings, unquoted numbers and so on.
- Values set via XPath expressions to a message tree element are
passed as its given type when it is a base SQL type. For example:
Integer, otherwise as character string formatted as per the IBM Integration Bus getValueAsString() MbElement
method.
Behavior when a database server cannot provide
at run time table meta data information
When a database
system cannot provide table meta data at run time, the Mapping node cannot perform
validation and implicit type casting. The data element values
are passed to the database server in the type they are presented,
without any casting being performed. This can result in the database
system rejecting the value and throwing a database exception.
The database server raises a SQL invalid type exception.
To
resolve this error, you must manually add explicit type casting in
the map. Use the xs:type transform in the
XPath expression of the Where clause
when you set a value in a target database column or when you pass
a value for a stored procedure parameter.
Working with database servers that are not listed
in the product SOE for IBM Integration Bus Version
10
This
section only applies if your database server is not listed under
IBM Integration Bus Requirements (SOE)
By
default, the Mapping node
queries table meta data by calling java.sql.PreparedStatement.getParameterMetaData().
If the database server you are connecting to does not fully implement
this JDBC interface method, the mapping can fail due to an SQL
exception from the database server. For example, the
database server might respond by returning the exception java.sql.SQLFeatureNotSupportedException.
You
can set the environment variable MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT to
control whether the Mapping node
queries the database server for table meta data at run time.
The
scope of the environment variable is an integration node.
To set this
environment variable, consider the following behavior:
- MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = true:
When you set the value to true, all the Mapping nodes within flows
running in the same integration node will
validate and perform implicit data casts.
- MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = false:
When you set the value to false, all the Mapping nodes within flows
running in the same integration node will
not validate and perform implicit data casts.
- MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = DatasourceName1:true
DatasourceName2:false When you set the value to a
list of database data source names, you enable the integration node to interact differently
with each data source. In this example, you enable Mapping nodes to interact
with the database associated to DataSourceName1 and
obtain meta data. You disable Mapping nodes to
interact with the database associated to DataSourceName2 so
no meta data is available.
Note: Use a space to separate
data source names.
To set the environment variable, complete the following
steps:
- Create a file in the following directory:
- On Windows:
work_path\Common\profiles
- On Linux and UNIX:
work_path/common/profiles
where
work_path is the machine-wide
IBM Integration Bus working directory.
Note: To
verify the machine-wide
IBM Integration Bus
working directory, enter the following command in a command
console:
echo %MQSI_WORKPATH%
- Edit the file to set the environment variable. for example, you
can enter MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = DatasourceName1:true
DatasourceName2:false.
- Set the environment variable. For more information, see Setting
up a command environment.