Problem determination tools in Data Studio and Optim Development Studio V2.2.1

Set up and obtain information from traces and logs in Data Studio, and other tips

IBM® Data Studio and IBM Optim™ Development Studio (ODS) are Eclipse-based IDE client tools for developing database business objects. In this article, find various problem determination tips, including tracing and logging in Data Studio and Optim Development Studio. Learn about the various traces and logs generated in Data Studio, the steps necessary to activate each trace type or log, and some problem determination capabilities within Data Studio. The article covers new capabilities for discovering code errors, validating with the database, and enabling trace for the routine debugger, query tuning, and pureQuery runtime.

Originally written about Developer Workbench, this article has been updated to cover many product changes and updates.


Marichu Scanlon (, Advisory Software Engineer, IBM

Photo of Marichu ScanlonMarichu Scanlon is a developer for the Information Management Application Development Tooling department. Among the products the group develops is the Developer Workbench for DB2 9. She has also been involved in the development and testing of the AD Tooling features in Rational Data Architect, Rational Application Developer V6, and the Development Center. She has given several presentations at IDUG and the IM Technical Conference, and participates as a Development Beta Advocate for the department's product beta programs.

03 February 2011 (First published 14 June 2007)


Logging and tracing in Data Studio is implemented not only with Data Studio components, but also by other components within the Eclipse framework. This article discusses various types of tracing that can help application developers to debug their SQL statements and stored procedures.

The types of tracing are:

  1. JDBC trace
  2. Routine debugger trace
  3. Visual Explain / Query tuning trace
  4. pureQuery runtime trace
  5. Data Studio plug-in trace

JDBC tracing in Data Studio

JDBC tracing allows Data Studio users to obtain information about Data Studio's interface to the database. The generated JDBC trace can give good insight into what Data Studio is doing in the following situations:

  • Connecting to the database. Through the driver properties of the Connection Profile, Data Studio obtains the necessary information to construct the connection URL to the database. This information is stored for each database connection. The actual connection to the database is made only when needed to perform a task such as loading catalog information into the Database Explorer, deploying a stored procedure, or executing an SQL statement. Whenever possible, Data Studio re-uses an open connection, rather than creating a new one. The JDBC trace gives information about what the actual values were for the JDBC driver version, host, database name, port, the JDBC properties in effect, and whether the connection attempt succeeded or not.
  • Executing an SQL statement. The JDBC trace provides the statement number, JDBC statement, and SQL statement being executed. Data Studio performs "on demand" catalog loading, which means that the contents of a folder (schemas, for example) are only loaded when the folder is expanded. The JDBC trace shows the exact SQL statement used to retrieve this. The JDBC trace also shows information about result sets from a query that was executed.

IBM Data Server Driver for JDBC and SQLJ

Data Studio supports both Type 2 and Type 4 connections to your server. The default connection is Type 4, using the IBM Data Server Driver for JDBC and SQLJ, also known as the "JCC driver." Data Studio ships the JCC driver JAR files in the following directory: <Data Studio Install Directory>\eclipse\plugins\\driver.

Data Studio also supports Type 2 connections to the database server, using the "Other" JDBC driver option, and specifying the location of in the Driver class location. However, using a Type 2 connection is discouraged, as most of the new functions in Data Studio (especially for Java™ stored procedures) are only available with the Type 4 connection.

Aside from the IBM Data Server driver for JDBC and SQLJ, Data Studio supports drivers for connecting to Informix. ODS also supplies a driver for connecting to Oracle.

When a connection profile is created, the default JDBC driver is the JCC driver. As you enter information in the Connection Profile wizard, Data Studio constructs the connection URL. However, the composed URL is a read-only field.

To activate JDBC tracing when using the JCC driver in Data Studio, right-click on the newly created connection profile and select Properties. In the Properties for dbname dialog, select Driver Properties menu item. Click on the Tracing tab Uncheck the Disable tracing button. You can now select which of the trace levels you want to activate, as shown in Figure 1.

Figure 1. Using the IBM Data Server Driver for JDBC and SQLJ in the Data Studio Connection Profile wizard
Screen capture: choose trace levels: Connection calls, result set calls, connects, result set metadata, diagnostics, XA calls, statement calls, driver configuration, DRDA flows, parameter metadata, and SQLJ

While the Connection URL does not reflect the trace options, internally, Data Studio constructs the connection with the trace options as if it were coded as follows:

Listing 1: Connection URL with trace options


  • traceDirectory is the directory in which the trace files will be generated
  • traceFile is the text that is used as the basis for the actual trace file names. If traceFile=foo, then the generated files will have names such as foo_1, foo_2, foo_3.
  • traceFileAppend specifies if the new trace data is appended to the given trace file. The value is either true or false.
  • traceLevel is an integer that represents the trace levels desired

The various trace levels are provided in Table 1:

Table 1. Values for trace levels
Trace constantInteger value 2

Once the trace options in the driver properties are set, JDBC tracing starts as soon as a connection is started. To turn JDBC tracing off:

  1. Edit the Connection Profile again: Select the context menu item Edit connection against the connection
  2. Click on Driver Properties
  3. In the Tracing tab, check the Disable Tracing button

Routine Debugger tracing

Data Studio and Optim Development Studio v2.2.1 provide a single debugger client for SQL, Java and PL/SQL stored procedures, called the Routine Debugger. In previous versions of Data Studio and Optim Development Studio, this feature was called the "Unified Debugger." With V2.2.1, the Session Manager component was integrated with the client debug code and renamed "Routine Debugger." The Routine Debugger can debug SQL, Java and PL/SQL stored procedures against the following target servers:

  • DB2® for Linux®, UNIX®, and Windows®, Versions 9.1 and 9.5 using the IBM Data Server for JDBC and SQLJ (SQL and Java)
  • DB2 for Linux, UNIX, and Windows, Version 9.7 (SQL, Java and PL/SQL)
  • DB2 for z/OS®, Version 8 (with APAR PK1057 applied), Versions 9 and 10 (SQL and Java)
  • DB2 for i V5R4 and later (SQL)
  • Oracle Version 10 and 11 (PL/SQL routines)
  • Informix® V11.70 (SPL routines)

Data Studio is the client front-end that allows users to:

  • Remotely debug server side SQL and Java stored procedures.
  • Debug native SQL stored procedures against a DB2 for z/OS V9 server.
  • Debug PL/SQL stored procedures against a DB2 for Linux, UNIX, and Windows V9.7 server.
  • Debug a single a SQL/Java procedure or nested SQL/Java procedures
  • Perform source-level debugging using the Debug Perspective
  • Launch built-in, client-side or server-side Session Manager.

The Routine Debugger supports traditional debugger capabilities, as shown in Figure 2. A full discussion of the Routine Debugger is beyond the scope of this article. There are several articles listed in the Resources section on debugging stored procedures using the Routine Debugger.

Figure 2. Routine Debugger in Data Studio
Screen cap: shows explorer in top pane, SQL procedure in bottom pane

When the user is debugging a stored procedure using the integrated Session Manager, and the debugger trace is enabled, the Routine Debugger populates the .logfile in the workspace's .metadata directory. This log is shared with other Data Studio components, so you will need to filter this file to see the entries related to the Routine Debugger.

Tracing on the debug client

To enable tracing in the client side, when the integrated Session Manager is used, ODS needs to be started with an additional option "-debug" and an input file to specify the debug plugins whose trace will be enabled. To accomplish this:

  1. Create a text file, e.g. spdtrace.txt.
  2. Copy the following lines of code into this file:
  3. Launch Data Studio or ODS with the -debug option as shown in Figure 3:
    Figure 3. Launch Data Studio with -debug for Routine Debugger
    Screen cap: target properties of started program
  4. After debugging, go to your workspace's .metadata folder, and open the .log file.
  5. Search this file for "". These are the trace entries for the Routine Debugger.

db2dbgm.log file

In previous versions of Data Studio and ODS, the Session Manager was launched from a command window. Data Studio provided a batch file, db2dbgm.bat for launching the Session Manager. To enable trace in this scenario, edit the batch file and specify the location of the db2dbgm.log. If the Session Manager is set up independent of Data Studio, you will need to specify the location of this Session Manager to Data Studio. Click Window > Preferences > Run / Debug > Routine Debugger > IBM. Set the Routine Debug Session Manager Location to Use already running session manager. Specify the Host IP address and port number of the Session Manager.

db2psmd.log file

You can enable tracing on the DB2 for Linux, UNIX, and Windows server side by creating a db2psmd.log file in a .tmp folder of your file system:

  • For Windows, create this in the C:\tmp folder.
  • For UNIX and Linux, create this in \tmp directory. Make sure you have write permission to this directory.

In the Preferences page, select Run/Debug > Routine Debugger > IBM. Set the Diagnostic error trace level on DB2 server preference field to 2, as shown in Figure 4.

Figure 4. Set diagnostic trace on server
Screen cap: shows diagnostic error trace level set to 2

Enabling Routine Debugger tracing on DB2 for z/OS server side requires access to the z/OS files and WLM. On DB2 for z/OS, the stored procedure, in debug mode, executes in a WLM environment specified in the CREATE PROCEDURE DDL of the stored procedure. The Routine Debugger messages are written out to an output file that is specified in the WLM application environment's procedure. To enable writing these trace messages on the server side:

  1. In Data Studio Preferences, set the Diagnostic error trace level on DB2 server preference field to 2, as shown in Figure 3.
  2. On the server side, edit the procedure's WLM application environment proc JCL. When debugging a SQL stored procedure, add a "//PSMDEBUG DD" to the JCL. For a Java stored procedure, add a "//JSPDEBUG DD" to the JCL. You can assign this to SYSOUT or to a file. If assigned to SYSOUT=A, the trace messages will be captured in the WLM log.
  3. Refresh the WLM application environment specified in this proc.
  4. Debug the stored procedure from Data Studio.
  5. After the debugger completes, quiesce the WLM application environment. The WLM address space will be purged.
  6. Capture the WLM address space SDSF output.
  7. Resume the WLM application environment.

Visual Explain and Query Tuning trace

Single query tuning and Visual Explain are available in Data Studio v2.2.1, both the stand-alone version and the IDE version. Single query tuning is not available in Optim Development Studio v2.2.1.

Enabling Visual Explain trace

You can enable trace for Visual Explain by clicking on Preferences > Data Management > Visual Explain. On this page,

  1. Set the location of the temporary files the same as the trace files. You can use the default folder, or you can specify a different location using the file browser.
  2. Click on the checkboxes in the "Options for trace files". You can trace (1) the rendering of the access plan graph, or (2) the collection of explain data, or (3) both, as shown in Figure 5A.
    Figure 5A. Preferences for Visual Explain trace
    Screen cap: Shows Visual Explain preferences selected
  3. Click Apply and then click OK.
  4. Recreate the problem.
  5. Open the directory where you specified the trace files, and sort the files by "Date Modified" to find the latest trace file or files.

Enabling Query Tuner trace

You can enable trace for Query Tuning by clicking on Preferences > Data Management > Query Tuner. In the "Trace Settings" area,

  1. Click on Enable Query Tuner trace.
  2. You can use the default folder shown as the directory for your trace files, or you can click Browse to specify a different location using the file browser.
  3. Specify the maximum file size in MB of your trace file.
  4. If your target server is DB2 for z/OS, specify whether you want the server trace output to go to GTF or SMF.
  5. Click OK.

Figure 5B shows the completed preferences settings for Query Tuner trace.

Figure 5B. Preferences for Query Tuner trace
Screen cap: Shows Query Tuner preferences selected

pureQuery Runtime trace

pureQuery is a feature available only in Optim Development Studio. There are numerous articles on pureQuery on developerWorks. In this article, we will show you how to set tracing for pureQuery's client optimization feature.

When you use pureQuery's client optimization feature, it generates a file called which will be read by the JDBC driver at runtime. You can specify two properties, traceFile and traceLevel in this file, as shown in Figure 6. In the editor for the, you can invoke content assist (shift-space bar) so you can see all the properties you can set, as well as a short explanation of each property.

Figure 6. file, trace settings
Screen cap: shows traceFile and traceLevel in the

Data Studio plug-in trace

JDBC trace data gives you information about Data Studio's interactions with the database server. To get information about events such as GUI initialization, saving a project, interaction with other plug-ins, and so on, you need to generate an Eclipse trace.

Data Studio provides a Trace Manager that encapsulates the filtering logic for recording the various trace points available in Eclipse. The Data Studio Trace Manager captures information regarding:

  • Plug-in startup and shutdown
  • Plug-in extension interfaces and the arguments passed
  • Database service API calls, such as those needed to build, drop, or run a stored procedure
  • Database JDBC/databean calls, such as those needed to call a stored procedure or run a query
  • Launching outside tools, such as Visual Explain
  • Wizard summary page data

To enable plug-in tracing in Data Studio, you need to follow a series of steps:

  1. Create or edit the .options file
  2. Launch Data Studio with the -debug option
  3. Set trace to "on" in the trace preferences page

Create or edit the .options file

  1. Find the installation directory for Data Studio. (The default directory in Windows is C:\Program Files\IBM\DS221.)
  2. Go to the eclipse/plugins directory, and search for the .options files for the plug-ins you want to trace.

    For Data Studio, these plug-ins are:
  3. Merge all the contents of the .options file for each plug-in you want to trace, into a single .options file, using an editor or some other method of your choice.
  4. Save the merged .options in the eclipse folder of the installation directory for Data Studio.
  5. Edit the merged .options file, and set the plug-in trace and debug keywords to true.

Listing 2 includes an example of a merged .options file:

Listing 2. .options file
      # Logging & Tracing options for Data Studio*,actions,codegen,database_services,editors,

# Logging & Tracing options for the plug-in
# Turn on general debugging for the plug-in

# Turn on tracing for the

# Turn on Logging for the = true = true = true

Launch Data Studio with the -debug option

The -debug option can be set in the eclipse.ini file of Data Studio or specified in the target properties on the desktop shortcut when starting Data Studio. The latter option is what we did to enable Routine Debugger tracing as shown in Figure 3.

To start tracing using the first option:

  1. Edit the eclipse.ini file found in the same directory level as the .options file above.
  2. Add the following statement at the beginning of this file:

     -debug  file:<DS install directory>\.options

    Note: During install, Data Studio v2.2.1 will be installed in the following default directories:

    • In Windows: "C:\Program Files\IBM\DS22" and "C:\Program Files\IBM\SDPShared"
    • In Linux: "/opt/IBM/DS2.2" and "/opt/IBM/SDPShared"

If you installed Data Studio in Windows and want to use the second option:

  1. Go to Start > All Programs > Data Studio.
  2. Right-click on Create Shortcut.
  3. On your desktop, right-click the Data Studio icon and select Properties.
  4. Edit the Properties of the shortcut, and insert -debug in the Target command, as shown in Figure 7:
    Figure 7. Launching Data Studio with -debug option
    Screen cap: Shows Data Studio properties, with target as datastudio.exe -debug

Set trace to "on" in the trace preferences page

  1. Go to the Trace Preferences page. (Select Window > Preferences > Data Management > SQL Development > Routines > Process.)
  2. Check the Trace check box, under "Trace settings," as shown in Figure 8:
    Figure 8. Data Studio Trace Preferences page
    Screen cap: Shows Trace selected, with trace file name

The Trace check box allows you to dynamically turn tracing on and off, assuming that the .options file contains trace and debug set to true.

The plug-in traces give a wealth of information on how Data Studio is processing the user's selections in the UI, what plug-ins are being loaded, what classes are being instantiated, and what methods are being called. Listing 3 shows an example of the contents of the trace file:

Listing 3. Data Studio trace file
!MESSAGE (trace)
!ENTRY     (trace) 0 0 May 30, 2007 09:19:12.32 
!MESSAGE   (trace)
!ENTRY     (trace) 0 0 May 30, 2007 09:19:12.34 
!MESSAGE   (trace)
!ENTRY     (trace) 0 0 May 30, 2007 09:19:12.34 
!MESSAGE   (trace) 1180541952340:13:JdbcUtil.executeUpdateSQL():ENTRY 		
!ENTRY     (trace) 0 0 May 30, 2007 09:19:12.34
!MESSAGE   (trace) 1180541952340:13:JdbcUtil.executeUpdateSQL():

Executing . . .

      sql=SET SCHEMA = 'ADMF001'

!ENTRY     (trace) 0 0 May 30, 2007 09:19:12.37 
!MESSAGE   (trace) 1180541952370:13:JdbcUtil.executeUpdateSQL():RETURN
!ENTRY     (trace) 0 0 May 30, 2007 09:19:12.37 

General logs in Data Studio

Both Data Studio and Eclipse generate several types of logs that can be beneficial to the user or to the IBM support personnel. The logs do not require any setup, and you can display them in the output view of Data Studio. Click Window > Show View to display these logs in the workspace. These logs are:

  • Error log - The error log is a table view of the general log generated by Eclipse. This log is found in the workspace's .metadata folder, and is simply named .log. This log contains stack traces for Java, JDBC and SQL exceptions. The information about the plug-in that caught them is high-level and limited to the plug-in name, line number in the plug-in, and the timestamp of each successive failure. The JDBC and Data Studio plug-in logs contain the details about each error or exception.
  • Problem log - The problem log show compilation errors and warnings encountered when launching Data Studio and while it executes. This log is used more by the IBM Support personnel than the user. Some user problems related to database connections, such as creating duplicate connections, are recorded here.
  • Console log - The console log shows stdout messages generated by Data Studio plug-ins, as well as some error and exception messages. The chief useful output of this log is the "Java stack trace." This information is useful in determining (1) what kind of exception was thrown by Data Studio, and (2) which plug-in caught the exception. However, information about the specific artifact and its value is not listed here. This information is obtained from the Data Studio plug-in trace mentioned above.
  • CVS log - This log shows information on check in and check out of Data Studio projects. Data Studio supports sharing of projects using either the Concurrent Versions System control management system or the ClearCase Remote Client. Information on both are available in the Help Contents (Help > Tasks > Working in the team environment using CVS).

No setup is required to generate these logs. The Console Log may not be visible when Data Studio is initially launched. To display the Console log, select Window > Show View > General > Console. From the Console log, you can launch the CVS console log, as shown in Figure 9.

Figure 9. CVS Console from the Console log
Screen cap: Shows popup window to start CVS console

Other development features

Data Studio provides assistance to users during stored procedure development in the following ways:

  • During SQL statement or stored procedure creation, both the SQL and XQuery editor and the Routine Editor provide feedback to the user when invalid syntax is entered in an SQL statement or stored procedure. In the SQL and XQuery editor, the Validation tab allows you to check the existence of the database objects used in the statement against the server. You can also specify the DB2 or Informix database server you wish to validate against, as each database has slightly different methods for parsing an SQL statement. See Figure 10 for a screen capture of the SQL and XQuery editor's validation tab.
    Figure 10. SQL and XQuery Editor, validation tab
    Screen cap: shows choice of databases for validation
  • You can validate the objects you are manipulating in an SQL stored procedures. Highlight the SQL statement and then right-click Run SQL against the database. If you have a large number of statements, you can opt to let the database find the errors for you by deploying the stored procedure. The default bind option is VALIDATE BIND, which will check for the existence of the database objects used in the stored procedure during the bind process. If errors are found, the deploy will fail. Some errors are always fatal, such as missing BEGIN keyword at the start of the SQL body. These will not be reported as warnings, regardless of the validate option.
  • In an SQL stored procedure, the GET DIAGNOSTICS statement can be used to report problems encountered during execution of the stored procedure. In order for Data Studio to report this, you must specify the extendedDiagnosticLevel=241 JDBC property in the connection profile as shown in Figure 11.
    Figure 11. Specifying extendedDiagnosticLevel JDBC property
    Screen cap: Shows extendedDiagnostiLevel property specified as an optional driver property
  • When a stored procedure or SQL Statement is executed, error messages are displayed in the Data Studio Data Output view. Data Studio reports the returned SQL code and SQLState as well as other messages if you have requested these fields to be reported during the stored procedure creation. Data Studio also keeps a history of your results in the status table. You can save this history in your file system for more analysis by right-clicking on the status table and selecting Save History....

Summary and what's ahead

This article discussed the various problem determination capabilities within Data Studio. It showed how you can enable various traces and view logs to get detailed information about errors and exceptions. It also discussed the many ways Data Studio informs you of errors during the creation, modification, deployment, and execution of SQL statements and stored procedures. The Data Studio team continues to add features that will assist you in troubleshooting your problems, so stay tuned.


The author would like to thank the following IBMers who gave valuable input for this article.

  • Hung P. Le
  • Kendrick Ren
  • Gary Lazzotti
  • Diem H. Mai
  • Emily Zhang



Get products and technologies



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management
ArticleTitle=Problem determination tools in Data Studio and Optim Development Studio V2.2.1