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
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:
- JDBC trace
- Routine debugger trace
- Visual Explain / Query tuning trace
- pureQuery runtime trace
- 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\com.ibm.datatools.db2_1.0.0.xxxxxx\driver.
Data Studio also supports Type 2 connections to the database server, using the "Other" JDBC driver option, and specifying the location of db2java.zip 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
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 constant||Integer value|
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:
- Edit the Connection Profile again: Select the context menu item Edit connection against the connection
- Click on Driver Properties
- 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
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:
- Create a text file, e.g. spdtrace.txt.
- Copy the following lines of code into this file:
com.ibm.debug.spd/debug=true com.ibm.debug.spd/debug/logging=true com.ibm.debug.spd/debug/events=true com.ibm.debug.spd.sql/debug=true com.ibm.debug.spd.sql/debug/logging=true com.ibm.debug.spd.sql/debug/events=true com.ibm.debug.spd.java/debug=true com.ibm.debug.spd.java/debug/logging=true com.ibm.debug.spd.java/debug/events=true com.ibm.debug.spd.plsql/debug=true com.ibm.debug.spd.plsql/debug/logging=true com.ibm.debug.spd.plsql/debug/events=true com.ibm.debug.spd.spl/debug=true com.ibm.debug.spd.spl/debug/logging=true com.ibm.debug.spd.spl/debug/events=true
- Launch Data Studio or ODS with the
-debugoption as shown in Figure 3:
Figure 3. Launch Data Studio with -debug for Routine Debugger
- After debugging, go to your workspace's .metadata folder, and open the .log file.
- Search this file for "com.ibm.debug.spd". These are the trace entries for the Routine Debugger.
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
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.
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
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:
- In Data Studio Preferences, set the Diagnostic error trace level on DB2 server preference field to 2, as shown in Figure 3.
- 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.
- Refresh the WLM application environment specified in this proc.
- Debug the stored procedure from Data Studio.
- After the debugger completes, quiesce the WLM application environment. The WLM address space will be purged.
- Capture the WLM address space SDSF output.
- 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,
- 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.
- 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
- Click Apply and then click OK.
- Recreate the problem.
- 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,
- Click on Enable Query Tuner trace.
- 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.
- Specify the maximum file size in MB of your trace file.
- If your target server is DB2 for z/OS, specify whether you want the server trace output to go to GTF or SMF.
- Click OK.
Figure 5B shows the completed preferences settings for Query Tuner trace.
Figure 5B. Preferences for Query Tuner trace
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
pdq.properties 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
the pdq.properties, 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
Figure 6. pdq.properties file, trace settings
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:
- Create or edit the .options file
- Launch Data Studio with the -debug option
- Set trace to "on" in the trace preferences page
Create or edit the .options file
- Find the installation directory for Data Studio. (The default directory in Windows is C:\Program Files\IBM\DS221.)
- 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:
- 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.
- Save the merged .options in the eclipse folder of the installation directory for Data Studio.
- 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 com.ibm.datatools.common.ui/debug/filter=*,actions,codegen,database_services,editors, extensions,file_io,JDBC_bean_calls,model_populate,plugin_shutdown,plugin_startup, ext_tool_launch,wizards,project com.ibm.datatools.common.ui/debug/level=300 com.ibm.datatools.common.ui/debug=true com.ibm.datatools.common.ui/debug/append=false com.ibm.datatools.common.ui/debug/fileName=c\:\\temp\\DataToolsAppTrace.txt # Logging & Tracing options for the com.ibm.datatools.core.ui plug-in # Turn on general debugging for the com.ibm.datatools.core.ui plug-in com.ibm.datatools.core.ui/debug=true # Turn on tracing for the com.ibm.datatools.core.ui com.ibm.datatools.core.ui/modelExplorer/trace=true com.ibm.datatools.core.ui/modelExplorerDecoration/trace=true # Turn on Logging for the com.ibm.datatools.core.ui com.ibm.datatools.core.ui/modelExplorer/log=true com.ibm.datatools.core.ui/editor/log=true com.ibm.datatools.core.ui/plugin/log=true com.ibm.datatools.core/debug = true com.ibm.datatools.core/clone = true com.ibm.datatools.core/dependency = true
Launch Data Studio with the -debug option
-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:
- Edit the eclipse.ini file found in the same directory level as the .options file above.
- 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:
- Go to Start > All Programs > Data Studio.
- Right-click on Create Shortcut.
- On your desktop, right-click the Data Studio icon and select Properties.
- Edit the Properties
of the shortcut, and insert
-debugin the Target command, as shown in Figure 7:
Figure 7. Launching Data Studio with -debug option
Set trace to "on" in the trace preferences page
- Go to the Trace Preferences page. (Select Window > Preferences > Data Management > SQL Development > Routines > Process.)
- Check the Trace check box, under "Trace settings," as shown in Figure 8:
Figure 8. Data Studio Trace Preferences page
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) 1180541952320:13:com.ibm.datatools.routines.dbservices.zseries.v9. SqlNativeSPZOSBuilder.postBuildProcess():ENTRY !ENTRY (trace) com.ibm.datatools.routines.dbservices 0 0 May 30, 2007 09:19:12.32 Thread[Thread-3,6,main] !MESSAGE (trace) 1180541952320:13:com.ibm.datatools.routines.dbservices.zseries.v9. SqlNativeSPZOSBuilder.postBuildProcess():RETURN !ENTRY (trace) com.ibm.datatools.routines.dbservices 0 0 May 30, 2007 09:19:12.34 Thread[Thread-3,6,main] !MESSAGE (trace) 1180541952340:13:com.ibm.datatools.routines.dbservices.zseries.v9. SqlNativeSPZOSBuilder.buildCompleted():RETURN !ENTRY (trace) com.ibm.datatools.routines.dbservices 0 0 May 30, 2007 09:19:12.34 Thread[Thread-3,6,main] !MESSAGE (trace) 1180541952340:13:JdbcUtil.executeUpdateSQL():ENTRY org.eclipse.wst.rdb.internal.core.connection.ConnectionAdapter@7d607d6 SET SCHEMA = 'ADMF001' !ENTRY (trace) com.ibm.datatools.routines.dbservices 0 0 May 30, 2007 09:19:12.34 Thread[Thread-3,6,main] !MESSAGE (trace) 1180541952340:13:JdbcUtil.executeUpdateSQL(): Executing . . . stmt.executeUpdate(sql) sql=SET SCHEMA = 'ADMF001' !ENTRY (trace) com.ibm.datatools.routines.dbservices 0 0 May 30, 2007 09:19:12.37 Thread[Thread-3,6,main] !MESSAGE (trace) 1180541952370:13:JdbcUtil.executeUpdateSQL():RETURN !ENTRY (trace) com.ibm.datatools.routines.dbservices 0 0 May 30, 2007 09:19:12.37 Thread[Thread-3,6,main]
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
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
- 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
- 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
- "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond" (IBM Redbooks, March 2008): Get details on how to set up your DB2 for z/OS environment for Data Studio.
- "Understanding the packaging of Optim Solutions for database development, administration, and performance management" (developerWorks, May 2010): Get a glimpse of the features in the Optim solutions products, including Data Studio and Optim Development Studio.
- "Debugging stored procedures in DB2 z/OS with Optim Development Studio, Part 1: Use the Unified Debugger in a sample scenario" (developerWorks, November 2010): Walk through a sample scenario for using the Unified Debugger.
- "Debugging stored procedures in DB2 z/OS with with Data Studio Developer, Part 2: Configure the stored procedure debug session manager on z/OS" (developerWorks, March 2009): This article describes the setup of the Unified Debugger's Session Manager on the DB2 for z/OS server.
- Download a free copy of IBM Data Studio to try out the concepts described in this article and get excited about Data Studio.
- Download an evaluation copy of IBM Optim Development Studio and pureQuery Runtime.
- In the Optim area on developerWorks, get the resources you need to advance your skills on Optim and Data Studio.
- Check out the Optim Development Studio and pureQuery Runtime page on developerWorks for more articles and resources.