In the Graphical Data Mapping editor, you can access a database and manipulate your business data.

– You can use database content as addition input data for a graphical data map transform.
– You can use database transforms to modify database content.
– You can use a database transform to invoke a database stored procedure.

Data type handling using the Graphical Data Mapping editor to read or modify data in a database table requires consideration of the type of Database server that will be connected to from the run time. The connection to the Database system from the IBM Integration Bus runtime is made via JDBC. At run time, a JDBC Provider configurable service is used to define the configuration of the connection. You must enable the JDBC connection to the database before you execute the map. See knowledge center topic Setting up a JDBC provider for type 4 connections. Note: The name of the JDBCProvider service must be identical to the name of the database definition you used during map design in the Integration Toolkit.

IBM Integration Bus supports the JDBC drivers that are listed in the release Systems requirements pages, eg System requirements for IBM Integration Bus 10.0. Where a JDBC driver for the Database system you wish to utilize is not listed, the page notes that IBM Integration Bus will provide limited support subject to the following restrictions:
– The driver must be a JDBC Type 4 (Pure Java) driver.
– Discoverability in the Graphical Data Mapper component of IIB is not supported
– Automatic determination of field types in Graphical Data Maps is only supported if the driver correctly and fully implements java.sql.PreparedStatement.getParameterMetaData()

In order to add database transform in the Graphical Data Mapping editor, you must have a data definition file that defines the tables, columns and procedures that can be accessed. For fully supported databases the Integration Toolkit can run discovery to populate this data definition. When discovery is not supported you can use one of two approaches to build the required data definition, “.dbm” file.

– Create and populate the file manually, see the knowledge center topic Creating a database definition from scratch
– Create an identical data table structure in a fully supported database system and run discovery to build an equivalent database definition.

Once you have the required data definition and have included a database transform in your map, you can build transformation logic to pass values to and from the database. The map may require you to make explicit type casts, in order to avoid mapping node exceptions or database server exceptions being thrown. This depends on whether IBM Integration Bus can automatically determine the actual data type of each SQL statement insert and result data column. As noted above this is only possible if the database server and JDBC driver correctly and fully implements java.sql.PreparedStatement.getParameterMetaData().

The knowledge center topic Data type considerations for mapping database content lists the database servers that are fully support but do not provide the required interface to enable automatic determination of field types. When working with these databases your map must be designed to include explicit data type casting if the value you wish to pass to the database is obtained from a source that is not already of a suitable type. The Graphical Data Mapper provides a set of xs:”type” transforms that can be used to perform the casting.

When you configure IBM Integration Bus Mapping node via a JDBC provides configurable service to interact with a Database Server that is not listed as fully supported the runtime will by default attempt to perform automatic determination of field types. This can cause the execution of the map’s database transform to fail due to an SQL exception being thrown if the the database server and JDBC driver do not fully implement the SQL interface method java.sql.PreparedStatement.getParameterMetaData(). For example operating with a MySQL database server can fail with an error of the form:

BIP3953W: The Mapping node has run an SQL statement ”[SQL statement created for Map Database Transform]”, which has failed with the following exception: ”Parameter metadata not available for the given statement”.

Other vendors might respond with the SQL exception “java.sql.SQLFeatureNotSupportedException”, or just provide empty metadata, which would lead to unexpected data type failures.

In these cases you need to configure IBM Integration Bus runtime so that it knows not to attempt to perform automatic determination of field types. As detailed in knowledge center topic Data type considerations for mapping database content this is achieved via use of the “MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT” environment variable. By setting this to “true” you can ensure that automatic determination of field types will not be attempted. In this way you can avoid the possible exceptions. However once you have done this you will have to ensure that the map is implemented with explicit data type casting using xs:”type” casting to avoid possible SQL data type mismatch exceptions.

6 comments on"Database data type considerations for Graphical Data Mapping"

  1. Hi Martin,

    I try to use the Mapping node to make a ‘Select’ query and then map the results in a DFDL model. I need to set a condition in the transform to check if the element I get from the db is null. I have tried thn fn:nillable, fn:exists, fn:empty functions on the condition but they do not seem to work. What condition should I use to filter for NULL values?

    Thanks!

  2. Hi Martin – This is great feature/utility for reading DB. However we have a use case where we are reading building a object graph by reading bunch of tables from db2. Due to the amount of data, performance is really bad. Is there a away you can trigger parallel building of siblings ( like multiple threads going to db to build the same level sibling)?

    Thanks for your help !

    Best,
    Gaurav D.

    • Hi Gaurav,
      Thanks for you feedback.
      As I’m sure your are aware IBM Integration Bus offers a number of ways for you to pull data from databases. The Graphical Data Mapping solution is providing a visual solution which does not require programming skills. The visual solution building does not currently provide you with the capability to run multiple transforms in parallel and combine their outputs. Hence if your solution requires look-ups to multiple tables these will be performed sequentially as separate SQL calls to the RDBMS, which as you note can lead to longer execution times. It’s not clear if you use case is doing multiple DB Transforms, or you are just having very large dataset returned. One thing you might be able to do is combine Database operations using a Stored Procedure, which can then be called from the Graphical Map rather than issuing multiple Selects.

      Your use case could be specialized enough to need to look to “design for required performance” as you say by looking to run some of the processing in parallel concurrent threads. The Integration bus provides you with a couple of options, you could dispatch the work to separate message flows and aggregate responses, or using a programed compute node to match your available programing skills.

      Regards
      Martin

  3. Sailaja Jonnavithula June 15, 2015

    Hi Martin,

    I am using Windows 7. And the exact settings to be included in the profiles folder were extremely helpful. I got this scenario working fine.

    Thank you for your suggestions.

    Best Regards,
    Sailaja.

  4. Hi

    Glad to hear you found the article helpful.

    What host O/S do you have?

    For a Windows system I have

    C:\ProgramData\IBM\MQSI\common\profiles>cat MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT.cmd
    SET MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT=false
    ECHO MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT=%MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT%

    Then when restarting the IIB node it will know not to attempt to use metadata query.

    Having the “echo” is helpful as when I open a new Integration Bus console window it will remind me what I have set.

    Hope that helps – please let me know how you get on.

    Regards

    Martin

  5. Sailaja Jonnavithula June 11, 2015

    Hi Martin,

    A very useful blog to aid understanding of Message Broker’s handling of databases and types.

    I have done a small sample using MySQL and IIB 10. I have created a configurable service and settings. However I get this runtime exception you mentioned. I have gone through the link and created a file with the parameter MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT and its value true. Still, it fails at runtime.

    Could you please let me know the format of the file that is to be placed in the profiles location?

    Thank You.

    Best Regards,
    Sailaja.

Join The Discussion

Your email address will not be published. Required fields are marked *