IBM Support

Collect troubleshooting data for database problems in IBM Business Process Manager (BPM)

Technote (troubleshooting)


Problem(Abstract)

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 instructions for all supported database systems in separate sections.

Overview of Database diagnostic information

Gather the following information and files:

General diagnostic information

  • Problem description
  • Environment description
  • Server log/trace files - all files from <profile_root>/logs
  • profile /config directory
As needed diagnostic information
  • Database table row data or counts
  • Database specific diagnostics

IBM Business Process Manager side


Setting the trace string

Set the applicable trace string in the following table and then reproduce the problem. For instructions, see "Enabling trace on a running server".
You may also want to increase the trace file number and size if they are rolling over too quickly. 20 20MB files is a good starting point and you generally don't want to increase the file size past 50MB.

Name
Trace string
Description
General trace string
WAS.clientinfopluslogging=all Basic trace which include details on transaction boundaries, SQL queries and execution times.
Spring Framework org.springframework.jdbc.*=all Includes parameters of SQL query made via BPM spring framework calls.
More verbose than the general string.
JDBC driver tracing Various, see JDBC trace configuration JDBC driver specific tracing.
Useful for getting transaction boundaries or SQL query parameters if they are not covered in the first 2 traces.
Also for debugging driver issues with the corresponding DB support team.
Connection Manager/ Transaction Manager Transaction=all: EJBContainer=all: WAS.j2c=all:RRA=all  Needed when product issues are suspected with the connection or transaction manager.
Very verbose trace which can cause performance issues and roll the trace files quickly.
Component trace Various, see Mustgathers Depending on your issue, you will also want the related trace for that problem type like BPD, BPEL or REST api traces.

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.
Due to this you will generally want to just start with the general and some component specific trace, with Spring framework trace if the parameters are important. The other trace can be added when the issue is narrowed further.

General diagnostic information

  1. Problem and environment description
    Provide a detailed description of the problem that you are having. Try to be detailed about the steps that lead up to the issue and include screen shots whenever possible. Include any error messages that you think are related. For example, include the answers to the following questions.
    • Is this a new environment?
    • Was there any changes prior to the issue occurring?
    • Is the issue intermittent or re-creatable? If intermittent, how often does it occur?
    • How does the issue impact your business?
    • Are there any deadlines that are affected by this issue that we should be aware of?
    • Which database vendor and which version are you running?
    • Is the database located on another physical machine? (provide hostname)
    • Which JDBC driver are you using?
    • Provide the output of versionInfo -maintenancePackages

  2. Configuration files
    Provide the configuration files for the involved profiles:
    Deloyment_Manager_profile_root/config
    If the issue is specific to the servers for a particular node, then provide the /config directory for that profile as well.

  3. Server log files
    The log files need to be gathered from the Process Center and the client that is connecting to it
    1. Set the needed trace string on the application cluster member or impacted server via runtime tab.
    2. Recreate the issue.
    3. Turn off each of the enabled trace strings.
    4. Compress the profile_root/logs directory from each involved server. The directory contains the log, trace, and ffdc files.
      Note: If you have logs/trace going to custom locations then provide those locations in addition.
    5. Provide us with the timestamps for when your issue occurred or related error message with the timestamp.

  4. Database table row information
    Depended on the issue we may need row counts or exports of the row data for the tables related to the impacted operations. When providing table row data, it is best to provide a text file with the information in a Comma Separated Value (CSV) format for each needed table.


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.

  • Collect db2support:

    db2support output should be collected for diagnostic and system information about DB2 and the platform.

    For the database (e.g. BPMDB) that you are seeing problems with collect basic information (note you have to have SYSADM permission to run that):
    db2support . -d <database-name> -c

    A db2support.zip file is generated in the directory from where you launched the command


  • Locking issues: 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 SQLCODE -911 meaning a deadlocks or lock timeout occurred, then lock event monitor data should be gathered in addition to the application side database traces above.

    See Collecting data: DB2 Locking Issues for details.


  • Query performance issues:
    If you already identified a slow running SQL statement, then capture the output of this db2support command for the particular query:
    db2support output_directory -d database_name -sf sql_file -cl 1

    The usage of this db2support command is described in the Collecting Data for DB2 Compiler Issues document.

    High CPU or Memory usage and disk limitations can result in slow executing queries and performance-based problems. On UNIX platforms, the nmon tool exists. See nmon performance: A free tool to analyze AIX and Linux performance for details. You can also use a number of other OS specific 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.

What to do next

  1. Review the logs and traces at the time of the problem to try to determine the source of the problem.

  2. Use IBM Support Assistant to search for known problems in the information center, forums, and technotes.

  3. 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.

Related information

Test the connection service
Troubleshooting installation and configuration
A simplified Chinese translation is available

Cross reference information
Segment Product Component Platform Version Edition
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

Product Alias/Synonym

BPM

Document information

More support for: IBM Business Process Manager Advanced
Databases

Software version: 7.5, 7.5.1, 8.0, 8.0.1, 8.5

Operating system(s): AIX, Linux, Solaris, Windows

Reference #: 1610455

Modified date: 2016-03-30