You are having a database or SQL query issues related to the IBM Business Process Manager products. For example, you might be experiencing a problem when you connect to one of the databases or a particular SQL query is failing. You would like to know what documentation you must collect (MustGather) so that the IBM Business Process Manager Support team can diagnose your problem. If you gather this documentation before contacting support, it will expedite the troubleshooting process and save you time.
Diagnosing the problem
Before collecting the troubleshooting data, you may want to take a look at the knowledge collection for already known database problems in the IBM Business Process Manager product.
For problems in the database context, the IBM Business Process Manager product side and the database system side need to be considered. You will find SQL statements and instructions for all supported database systems in separate sections.
Database side (for your database administrator)
As Needed: Especially for performance-related problems, it is important to know if the database server is virtualized. There is some database server-related information listed for each database system that is needed to give a quick diagnosis.
- db2support output for the BPMDB database: To understand the current configuration, it is important that you collect the db2support output. If you already identified a slow running SQL statement, you can use the db2exfmt option to collect the corresponding information. The usage of the db2support command is described in the Collecting Data for DB2 Compiler Issues document.
- Lock contention: Lock contention can happen for a number of reasons. Therefore, it is important to understand which statements are participating. The lock event monitor is an important tool in this context. If you see lock contention on the database side or transaction timeouts on the IBM Business Process Manager side, it is worth the time to enable the lock event monitor as described in the Lock events for DB2 for Linux, UNIX, and Windows, Part 3: Use the lock event monitor in DB2 9.7 to solve concurrency issues document.
Important: Use the "with history and values" to also capture the values that are passed to the statements.
- Hardware contention: In a number of cases, CPU and disk limitations can result in slow executing queries and performance-based problems. On UNIX platforms, the nmon tool exists. You can use a number of other tools to collect CPU, memory, disk I/O and network traffic data over time.
- AWR report (1 hour time window when problem was observed): Oracle databases will, by default, create database snapshots every 60 minutes that are kept for 7 days. From these snapshots, an Automatic Workload Repository (AWR) report can be generated. Thus, it is possible to collect database information even after a problem was observed. The best settings for a report are a one hour time frame. It allows you to focus on data that was collected during the problem occurrence.
- Explain/autotrace output for a problematic SQL statement: If a specific SQL statement was already identified and it shows a bad performance, an explanatory output or an autotrace output with statistics information and execution plan will be required and can speed up things.
- SQL Server Profiler trace: Microsoft SQL Server provides the SQL Server Profiler trace functionality, which can be easily enabled. It provides deep insights about what is happening from a Microsoft SQL Server side. Depending on the nature of the problem, you can enable default trace settings.
IBM Business Process Manager side
Setting the trace string
Depending on the nature of a problem different trace settings will apply:
- You are aware that there is a database-related problem and you want to know the statements as well as the origin of the statements. In this case, you need to enable the following setting:
- If you are only interested in the SQL statements that are executed, set the trace string to the following value. This trace logs the SQL queries that are run in the background.
Note: Enabling the trace might slow down your system. This delay can lead to transaction time outs and errors. Disable this trace after collecting the requested information.
To set the tracing and generate a new set of logs and traces, complete the following steps for the servers or cluster that show the issue:
- In the Integrated Solutions Console, under Troubleshooting > Log and Trace, select the server for which you want to change the settings.
- Click Change Log Detail Levels.
- Select the configuration tab to set the tracing. This change takes effect after the next server restart and a completely new set of log files can be collected. Optionally, select the runtime tab to dynamically set the tracing.
- Enter the trace string and click OK.
- If you selected the configuration tab, stop the server.
- Archive and delete the current logs, traces, and ffdc from the following directories:
- Start the server and check the trace.log file to make sure that the correct tracing is set.
- Reproduce the problem and note the timestamp. Check the trace.log file to ensure that the time of the issue is covered in the file. Gather the complete server log files and the ffdc directory.
- Delete the trace string from the Log Detail Level for your server.
Note: Ensure that you have set the Maximum trace file size (recommendation: 20MB) as well as the number of historical trace files (recommendation: 20) to an appropriate value. You can find those values at Servers > Application Servers > server_name > Diagnostic Trace Service.
General diagnostic information
Collect the general troubleshooting information as described in the Collect troubleshooting data for the IBM Business Process Manager products document.
In addition, describe the problem in detail and answer the following questions:
- Can you determine which database or data source has the issue?
- Which database vendor and which version are you running?
- Is the database located on another physical machine?
- Which JDBC driver are you using?
- Is the connection test for your data sources successful? (In the Integrated Solutions Console, click Test Connection under Resources > JDBC > Data sources)
What to do next
- Review the logs and traces at the time of the problem to try to determine the source of the problem.
- Use IBM Support Assistant to search for known problems in the information center, forums, and technotes.
- If you cannot find related problems or cannot solve the problem, send the information you have collected to IBM by following the instructions in Exchanging Information with IBM Technical Support.
|Business Integration||IBM BPM Advanced Pattern on Red Hat Enterprise Linux Server||Databases||Linux Red Hat - xSeries||8.0.1, 8.0|
|Business Integration||IBM Business Process Manager Express||Databases||Linux, Windows||8.5, 8.0.1, 8.0, 7.5.1, 7.5|
|Business Integration||IBM Business Process Manager Standard||Databases||AIX, Linux, Solaris, Windows||8.5, 8.0.1, 8.0, 7.5.1, 7.5|
|Business Integration||IBM Business Process Manager Advanced Pattern on AIX||Databases||AIX||8.0.1|