IBM Support

App Connect Enterprise FAQ for Databases

Question & Answer


Question

The following is a list of answers to frequently asked questions (FAQ) about Database in App Connect Enterprise (ACE) and IBM Integration Bus (IIB) for new and experienced users.

Answer

Index of questions in this document:  


 

1. What database versions are supported by ACE?

Supported databases and versions are listed in the system requirements for each product.
You can find a list of links to the system requirements for each version of ACE, here:
Detailed System Requirements for IBM App Connect Enterprise, IBM Integration Bus, and WebSphere Message Broker

 

 

2. What JDBC drivers are supported with my database ?

App Connect Enterprise does not ship with JDBC drivers.
Users must ensure that their JDBC driver is compatible with their backend database.

 


 

3. How do I connect to a remote database ? Do I need a database client installation on my ACE machine ?

To connect to a remote DB2 or Informix database via ODBC, ACE requires a local database client.
Starting in ACE 11.0.0.11, a modified DB2 client is provided with the ACE installation on Linux and Unix systems.
See: Explore the new features in App Connect Enterprise version 11.0.0.11

 

 

4. How do I configure database connectivity from App Connect Enterprise?

App Connect Enterprise supports ODBC and JDBC connectivity to databases.
More information can be found in the product documentation:

App Connect Enterprise IBM Integration Bus  
 

5. How do I configure for global co-ordination (XA) for my database?

Please see the below information center pages for general global co-ordination configuration.  
 

6. Is there a way to configure connection pooling for database connections made by ACE?

ODBC connections are managed internally by the Integration Server; connection pooling is not configurable.
Connection pooling for JDBC connections can be configured in a JDBC providers policy (ACE) or configurable service (IIB):
 
 

7. Where can I download database drivers? Can I download drivers from DataDirect and use them?

ACE ships some ODBC drivers with the product. These drivers are fully tested with ACE and supported.

For ODBC connectivity to other databases, an ODBC driver must be supplied (for example, for DB2).
IBM will provide best-effort support for the product when using a user-supplied driver.

App Connect Enterprise does not ship with JDBC drivers.
Users must ensure that their JDBC driver is compatible with their backend database.
 


 

8. How do I configure credentials for database access? Is it possible to configure username and password for database access by default ?

Credentials for database connections must be configured.
Credentials can be store using mqsisetdbparms or mqsicredentials (ACE versions only) commands.

It is possible to set default credentials for ODBC or JDBC connections under an Integration Node or Integration Server.
These can be overridden at the Integration Server level or for individual datasource connections.
 
 

9. How do I invoke stored procedures from a Message flow?

Stored Procedures are covered in the documentation.
We also provide a capability scenario for this use case:
App Connect Enterprise
IBM Integration Bus
 

 

10. How do I map database content in a Mapping node ?

Graphical Data Maps interact with databases using JDBC.
Create a database definition file for use in the Toolkit and use a JDBC providers policy (ACE) or configurable service (IIB) at runtime:
 
 

11. Can database calls be optimized in ACE?

For ODBC connections, some optimizations are enabled by default.
When a database call is made from within an ESQL Compute node, the flow constructs the appropriate SQL, which is sent to the database manager. As part of this process, the SQL statement is prepared using the SQLPrepare function, and a statement handle is acquired so that the SQL statement can be executed.
To reduce the overhead associated with the SQLPrepare function, the prepared statement and handle are saved in a cache for reuse with future flow invocations. When a message flow evaluates SQL, it first queries this cache. If the statement is already in the cache, the statement handle is returned so that it can be re-executed with newly bound parameters, without needing to call SQLPrepare again.
This does present a potential complication.
As the cache stores each unique SQL statement string, if SQL statements differ (even slightly) for each message, every statement will be cached and an SQLPrepare function will always be performed (and a new ODBC cursor will be opened). This results in memory growth for the process (as the cache grows in size) and reduced performance for the compute node (as cache queries take longer as the cache grows in size).
This complication is common when using PASSTHRU statements, where SQL statements are constructed and then passed to a database. When using PASSTHRU, use parameter markers for each variable. This allows for the same SQL prepared statement for each invocation, with the parameters being bound at run time. This approach makes efficient use of the prepared statement cache.
If needed, the prepared statement cache can be disabled.
Set or Export an  environment variable 'MQSI_EMPTY_DB_CACHE=1' in your environment.
When an Integration Server starts with this variable set, prepared SQL statements are not preserved in the cache.
This causes every SQL statement to require the SQLPrepare function.

 

12. Are there any additional steps required for migration between versions?

From WebSphere Message Broker V7 onwards there is no database pre-requisite.
App Connect Enterprise and IBM Integration Bus does not require a database for any internal function.
For ODBC connections, ensure that the ODBCINI and ODBCSYSINI variables point to the expected locations and that ODBC driver references are correct.
For JDBC connections, ACE uses JDBC provider policies, as configurable services have been replaced:


 

13. What database credential did I configure for database access?

Security Identities configured with the mqsisetdbparms command can be queried with the mqsireportdbparms command:

For credentials stored in the vault, the mqsicredentials command has a report feature:

 

 

14. How can I view JDBC provider configurable service properties?

In ACE, JDBC provider policies replace configurable services.
View provider properties from the *.policyxml file within its policy project.

In IIB, display all configured JDBC providers for a single Integration Node with this command:

mqsireportproperties MY_NODE -c JDBCProviders -o AllReportableEntityNames -r   
 
 

15. Is there a way to validate an ODBC configuration?

Use the command 'mqsicvp' to test and validate an ODBC configuration:
mqsicvp -n primaryDatasourcename -u primaryDatasourceUserId -p primaryDatasourcePassword
mqsicvp MY_NODE -n primaryDatasourcename
 
 

16. Does ACE report database SQL exceptions if an error occurs?

Yes.
Database exceptions in message flows are thrown as "recoverable exceptions".
If not caught by the flow, the Integration Server reports database exceptions in messages BIP2322E and BIP2393E. These error messages include inserts which show the SQL exception received from database during processing.

 

 

17. Is there a database connection idle time? Can this be tuned?

Database connection lifespan depends on the component and the configuration.
  • Database connections associated with an additional instance of a flow persist for the life of the flow thread.
  • Database connections associated with the main instance of a flow have a default timeout of 60 seconds.
  • The default timeout can be tuned with the parameter 'maxConnectionAge'.
It is also possible to force an active database connection to be reset after a certain number of uses with the parameter 'maxConnectionUseCount'.
This setting was added via APAR IT32608 in ACE 11.0.0.11 and IIB 10.0.0.22.
For more information:  
 

18. Is the IE02 SupportPac required for database connectivity?

From WMB V8 onwards, IE02 is required for an Integration Server to establish ODBC connectivity with all supported databases.
IE02 is installed automatically as part of the product installation.

 
 

19. How do I capture ODBC / JDBC traces in IIB?

Index: App Connect Enterprise and IBM Integration Bus trace directions

 
 

20. Are there any additional references that may help me with IIB connectivity with the application databases?

The Integration Community is an excellent resource for questions and concerns.
It also includes blog posts, such as this one, from product developers and support staff:

 

[{"Product":{"code":"SSNQK6","label":"IBM Integration Bus"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Database","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"10.0;9.0","Edition":"All Editions","Line of Business":{"code":"LOB45","label":"Automation"}},{"Type":"MASTER","Line of Business":{"code":"LOB45","label":"Automation"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSDR5J","label":"IBM App Connect Enterprise"},"ARM Category":[{"code":"a8m0z000000cvj9AAA","label":"ACE-\u003EDatabase"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Product Synonym

WMB MB WebSphere Message Broker IBM Integration Bus IIB IBMIB MQ Integrator WBIMB WBI-MB MQSI WMQI

Document Information

Modified date:
24 May 2022

UID

swg21649256