DB2 application development: Problem determination tools in Developer Workbench

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Intermediate

Marichu Scanlon (marichu@us.ibm.com), Advisory Software Engineer, IBM　

14 Jun 2007

Developer Workbench (DWB) is an Eclipse-based IDE client tool for developing DB2® business objects. In this article, find various problem determination tips, including tracing and logging in Developer Workbench. Learn about the various traces and logs generated in DWB, the steps necessary to activate each trace type or log, and some problem determination capabilities within DWB, including an overview of the Unified Debugger.

Tracing in DWB

Tracing in DWB is implemented not only by the DWB plug-in, but also by other components within the Eclipse framework. This article discusses two types of tracing that are helpful in providing application developers additional information about what is being processed in their applications, and specifically in their stored procedures.

The two types of tracing are:

1. JDBC tracing
2. DWB plug-in tracing

JDBC tracing in DWB

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

• Connecting to the database. Through the Connection Wizard, DWB 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 done 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, DWB will re-use an open connection, rather than create 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. DWB 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.

The article "DB2 application development: Tracing with the DB2 Universal JDBC Driver" (developerWorks, June 2005) provides more examples of when you may want to perform a JDBC trace, such as for performance tuning in a multi-tier environment, or when a third-party software is involved.

DB2 Universal JDBC Driver

DWB supports both Type 2 and Type 4 connection to your server. The default connection is Type 4, using the IBM DB2 Universal JDBC Driver. DWB ships the IBM DB2 Universal Driver JAR files in the following directory: <DWB Install Directory>\dwb_prod\eclipse\plugins\com.ibm.datatools.db2_1.0.0\driver.

DWB 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 DWB (especially for Java stored procedures) are only available with the Type 4 connection.

When the Connection Wizard is launched, the default JDBC driver is the DB2 Universal JDBC Driver. As you enter information in the Connection Wizard, DWB composes the connection URL. However, the composed URL is a read-only field.

To activate JDBC tracing when using the DB2 Universal Driver in DWB, select Other for the JDBC driver, and fill in the connection URL for the IBM DB2 Universal Driver, as shown in Figure 1:

Append the trace options to the Connection URL as follows:

                
jdbc:db2://localhost:50000/SAMPLE:retrieveMessagesFromServerOnGetMessage=true;
traceFile=trace;traceFileAppend=false;traceLevel=1;traceDirectory=C:\JCCTrace
			

Where:

• 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:

Note: The trace level values in Table 1 are excerpted from the article"DB2 application development: Tracing with the DB2 Universal JDBC Driver."

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

1. Launch the Connection Wizard again: Select the context menu item Edit connection against the connection
2. Remove the trace options from the connection URL

Back to top

Plug-in tracing in DWB

JDBC trace data gives you information about DWB'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.

DWB provides a Trace Manager that encapsulates the filtering logic for recording the various trace points available in Eclipse. The DWB 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 DWB, the you need to follow a series of steps:

1. Create or edit the .options file
2. Launch DWB 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 DWB. (The default directory in Windows is C:\Program Files\IBM\DWB\V9.1\dwb_prod.)
2. Go to the eclipse/plugins directory, and search for the .options files for the plug-ins you want to trace.
   
   For DWB, these plug-ins are:
   • com.ibm.datatools.core.ui
   • com.ibm.datatools.core
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 DWB.
5. Edit the merged .options file, and set the plug-in trace and debug keywords to true.

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

                
      # Logging & Tracing options for DWB
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 DWB with the -debug option

The -debug option can be set in the eclipse.ini file of DWB or specified when starting DWB.

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 <DWB install directory>\dwb_prod\eclipse\.options

   
   

If you are using the Windows > Start Programs command to start DWB:

1. Create a shortcut of DWB on your desktop.
2. Edit the Properties of the shortcut, and insert -debug in the Target command, as shown in Figure 2:
   
   Figure 2. Launching DWB with -debug option
   
   
   

Set trace to "on" in the trace preferences page

1. Go to the Trace Preferences page. (Select Window > Preferences > Data > Stored Procedures > Process.)
2. Check the Trace check box, under "Trace settings," as shown in Figure 3:
   
   Figure 3. DWB Trace Preferences page
   
   
   

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

The plug-in traces give a wealth of information on how DWB 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. An example of the contents of the trace file is shown in Listing 2:

                
!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]
      

In the next release of DWB, the connection management APIs will also be traced.

Back to top

Logs in DWB

Both DWB and Eclipse generate several types of logs that can be beneficial to the user or to the IBM Support personnel. DWB also delivers a single debugger client for SQL and Java stored procedures, which supports the Unified Debugger. It can greatly help you in determining stored procedure code problems. The Unified Debugger is distributed with the following DB2 family of servers:

• DB2 9 for LUW
• DB2 9.1 for z/OS
• DB2 for iSeries V5R4

DWB 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 9 for z/OS server
• Debug a single SQL/Java procedure or nested SQL/Java procedures
• Perform source-level debugging, using the Debug Perspective
• Launch client- or server-side session manager. If on the client, a user can point to any z/OS system to debug.

The Unified Debugger supports traditional debugger capabilities, as shown in Figure 4. A full discussion of the Unified Debugger is beyond the scope of this article. Stay tuned for an article on debugging using the Unified Debugger.

Unified Debugger logs

The Unified Debugger has three parts to it: a client front end, a network middleware component (which is the Session Manager), and the DB2 server side. This section discusses gathering logs when a) using the Session Manager at the local client, and b) the debug server is DB2 for z/OS.

Unified Debugger error log on the client - This is a log maintained by the Unified Debugger's session manager while the debugger is active. The log is automatically created and populated when the user is debugging a stored procedure. The user can specify the location of the Session Manager in the db2dbgm.bat file, found in the directory where DWB was installed.

Unified Debugger log on DB2 for z/OS - This is a log written out by the Unified Debugger on the server side. This log contains message lines written out by the Unified Debugger code on the server side. The messages are written out to the output log of the WLM application environment specified in the CREATE PROCEDURE ddl of the stored procedure that is being debugged. To enable writing these trace messages on the server side, you need to:

• Set the "Diagnostic error trace level on DB2 server" preference field to 2 on the client side in DWB, as shown in Figure 5.
  
  Figure 5. Set diagnostic trace on server
  
  
  
• Edit the procedure's WLM application environment proc JCL on the server side, and 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 DWB.
• Quiesce the WLM application environment after the debugger completes. (The WLM address space will be purged.)
• Capture the WLM address space SDSF output.
• Resume the WLM application environment.

Other logs

Eclipse provides several logs that can assist problem determination by providing information that may or may not be captured in the traces. These logs are:

• Error log - The error log is a table view of the general log generated by Eclipse. This log is persisted as a .log in the file system, under the workspace's .metadata directory. JDBC and SQL exceptions, the plug-in that caught them, and the timestamp are reported in this log. Details about these exceptions are reported in the JDBC tracing, the DWB plug-in traces, or both.
• Problem log - The problem log should be clean most of the time. 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 print messages generated by DWB plug-ins, as well as some error 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 DWB, and (2) which plug-in caught the exception. Unfortunately, information about the specific artifact and its value is not listed here. This information is obtained from the DWB plug-in trace mentioned above.
• CVS log - This log shows information on check in and check out of DWB projects. DWB 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).

There is no setup needed to generate these logs. The console log might not be visible when DWB is initially launched. To display the console log, select Window > Show View > Basic > Console. From the console log, you can launch the CVS console log, as shown in Figure 6:

Back to top

Other problem determination tools

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

• During stored procedure creation, the SQL Builder and Routine Editor provide feedback when invalid syntax is entered in an SQL statement or stored procedure. To activate the parser after making changes to the code, you need to first save the object. In most cases, a red marker is added to the editor code to show the line number where the invalid entry is made. Java source errors for Java stored procedures are also reported in a similar manner in the editor view. Once you correct the errors and save the object, the red markers disappear.
• It is possible to deploy an SQL stored procedure with errors caused by referencing objects that have not been created, such as tables and nested stored procedures, using a cursor that has not been declared, and so on. If the VALIDATE BIND option is specified, the deploy process reports these errors as SQL exceptions and fail the deploy. If VALIDATE RUN is specified, these errors are reported as warnings, and the deploy will succeed. 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.
• When you specify the extendedDiagnosticLevel=241 JDBC property on the connection URL, as shown in Listing 3:
  
  Listing 3. Specifying extendedDiagnosticLevel JDBC property
  

  jdbc:db2://v27ec184.svl.ibm.com:446/STLEC1:extendedDiagnosticLevel=241;

  
  
• When a stored procedure or SQL statement is executed, error messages are displayed in the DWB Data Output view. DWB reports the returned SQL code and SQLState, as well as other messages, if you have requested these fields reported during the stored procedure creation.
• Finally, DWB offers you the use of the Unified Debugger for debugging stored procedures.

Back to top

Summary and what's ahead

This article discussed the various problem determination capabilities within DWB. It showed how you can set up traces and view logs to get detailed information about problems. It also discussed the many ways DWB informs you of errors during the creation, modification, deployment, and execution of SQL statements and stored procedures. The next release offers more facilities in problem determination such as:

• Colorization and content assist for SQL statements, and the create procedure DDL
• Validation of SQL syntax by the target server's DB2 parser
• Tighter linkage to the help documentation
• A GUI interface for specifying trace options in the Connection Wizard

Resources

• "DB2 application development: Tracing with the DB2 Universal JDBC Driver" (developerWorks, June 2005): Learn about JDBC tracing and find sample Java programs that help demonstrate the various trace facilities.
  
  
• "DB2 for z/OS Stored Procedures: Through the CALL and Beyond" (IBM Redbook, February 2006): Get details on how to set up your WLM environment as well details on setting up for Development Center.
  
  
• "DB2 Developer Workbench, Part 1: Developer Workbench concepts and basic tasks" (developerWorks, August 2006): Discover the basics of Eclipse and DWB. Although run against a DB2 9 for Linux, UNIX, and Windows server, this tutorial provides an excellent way to familiarize yourself with DWB's wizards and processes.
  
  
• "Introducing DB2 9: Application Development enhancements" (developerWorks, July 2006): Get an overview of the application development enhancements in DB2 9, including Developer Workbench, deeper integration with .NET environments, rich support for XML and SOA environments, new drivers and adapters for PHP and Ruby on Rails, and new application samples.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore: Browse for books on these and other technical topics.

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

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

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top