> # Troubleshooting the JDBC Client

> The SequeLink package includes the DataDirect Test™ for JDBC and DataDirect Spy™ for JDBC tools to help you troubleshoot the JDBC driver.

> ### DataDirect Test™

> DataDirect Test is a menu-driven software component that is included in the SequeLink package. It helps you debug your JDBC applications and learn how to use the JDBC driver. DataDirect Test contains menu selections that:

> - Correspond to specific JDBC functions-for example, connecting to a database or passing a SQL statement.
> - Encapsulate multiple JDBC function calls as a shortcut to perform some common tasks, such as displaying the contents of a result set.

> DataDirect Test displays the results of all JDBC function calls in one window, while displaying fully commented, Java JDBC code in an alternate window.

> This section introduces DataDirect Test and describes how to use it to test JDBC connections. Refer to the SequeLink Developer's Reference for information on additional features of DataDirect Test and a tutorial that takes you through a working example of its use.

> #### Configuring DataDirect Test™

> The default DataDirect Test configuration file is

> installdir/testforjdbc/Config.txt

> where installdir is the installation directory of the JDBC Client. You can customize this file for your environment using any text editor.

> All parameters in the DataDirect Test configuration file can be customized, but the most commonly configured parameters are:

> Drivers | A list of colon-separated JDBC driver classes.
> DefaultDriver | The default JDBC driver that appears in the Get Driver URL window.
> Databases | A list of comma-separated JDBC URLs. The first item in the list appears as the default in the database selection window. You can use one of these URLs as a template when you make a JDBC connection. The default Config.txt file contains example URLs for most databases.
> InitialContextFactory | Should be set to com.sun.jndi.fscontext.RefFSContextFactory if you are using file system data sources, or com.sun.jndi.ldap.LdapCtxFactory if you are using LDAP.
> ContextProviderURL | The location of the .bindings file if you are using file system data sources, or your LDAP Provider URL if you are using LDAP.
> Datasources | A list of comma-separated JDBC data sources. The first item in the list appears as the default in the data source selection window.

> #### Starting DataDirect Test™

> How you start DataDirect Test depends on whether you want to start it as an application or applet, and your Java Virtual Machine:

> - As a Java application on Windows: Run the testforjdbc.bat file located in the testforjdbc subdirectory in your DataDirect SequeLink for JDBC Client installation directory.
> - As a Java application on UNIX: Run the testforjdbc.sh shell script located in the testforjdbc subdirectory.

> #### Making a DataDirect Test™ Connection

> 1. Select Driver / Register Driver. DataDirect Test prompts you for the JDBC driver to load.
> 2. In the Please Supply a Driver URL field, make sure that a driver is specified, as in the following example; then, click OK.

> com.ddtek.jdbc.sequelink.SequeLinkDriver

> If the SequeLink JDBC Driver was registered successfully, the main DataDirect Test window appears with a confirmation in the JDBC/Database Output scroll box.

> The main DataDirect Test window shows the following information:

> - In the Connection List box, a list of available connections
> - In the JDBC/Database scroll box, a report indicating whether the last action succeeded or failed
> - In the Java Code scroll box, the actual Java code used to implement the last action

> TIP: The DataDirect Test windows contain two Concatenate check boxes. Select a Concatenate check box to see a cumulative record of previous actions; otherwise, only the last action is shown. Be aware that selecting Concatenate can degrade performance, particularly when displaying large result sets.

> 3. Select Connection / Connect to DB via Data Source. DataDirect Test displays the Select A Datasource window.

> 4. Select a data source from the Defined Datasources pane. In the User Name and Password fields, type the required user name and password connection properties; then, click Connect. For information about JDBC connection properties, refer to the SequeLink Developer's Reference.

> If the connection was successful, the Connection window appears and a message is displayed in the JDBC/Database Output scroll box.

> The connection window shows the following information:

> - In the JDBC/Database Output scroll box, a report indicating whether the last action succeeded or failed
> - In the Java Code scroll box, the actual Java code used to implement the last action

> TIP: Select the Concatenate check box to see the Java code of all previous actions; otherwise, only the Java code of the last action will be shown.

> ### DataDirect Spy™

> DataDirect Spy is a software component for tracking JDBC calls at runtime. It passes calls issued by an application to an underlying JDBC driver and logs detailed information about those calls. DataDirect Spy provides the following advantages:

> - Logging is JDBC 3.0-compliant, including support for the JDBC 2.0 Optional Package.
> - Logging is consistent, regardless of the JDBC driver used.
> - All parameters and function results for JDBC calls can be logged.
> - Logging can be enabled without changing the application.
> - DataDirect Spy can only be used with the SequeLink for JDBC Driver and the DataDirect Connect® for JDBC drivers.

> When you enable DataDirect Spy for a connection, you can customize DataDirect Spy logging for your needs by setting one or multiple options for DataDirect Spy. For example, you may want to direct logging to a local file on your machine.

> ### Generating a Pool Manager Trace File

> Connection pooling allows connections to be reused rather than created each time a connection is requested. Your application can use connection pooling through the DataDirect Connection Pool Manager. Refer to the SequeLink Developer's Reference for information about using the Pool Manager.

> #### Enabling Pool Manager Tracing

> You can enable Pool Manager logging by calling setTracing(true) on the PooledConnectionDataSource connection. To disable tracing, call setTracing(false) on the connection. See "Pool Manager Trace File Example" for information about using a Pool Manager trace file for troubleshooting.

> By default, the DataDirect Connection Pool Manager logs its pool activities to the standard output System.out. You can change where the Pool Manager trace information is written by calling the setLogWriter() method on the PooledConnectionDataSource connection.

> #### Pool Manager Trace File Example

> The following example shows a DataDirect Connection Pool Manager trace file. The numbers in bold superscript are note indicators that correspond to the notes listed in "NOTES" following the example. They provide explanations for the referenced text to help you understand the content of your own Pool Manager trace files.

>     jdbc/sequeLinkPool: *** ConnectionPool Created
>        (jdbc/sequeLinkPool,
>        com.ddtek.jdbcx.sequelink.SequeLinkDataSource@1835282, 5, 5, 10, scott)1
>     jdbc/sequeLinkPool: Number pooled connections = 0.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Enforced minimum!2
>     NrFreeConnections was: 0
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 5.
>     jdbc/sequeLinkPool: Reused free connection.3
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 4.
>     jdbc/sequeLinkPool: Reused free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 3.
>     jdbc/sequeLinkPool: Reused free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 2.
>     jdbc/sequeLinkPool: Reused free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 1.
>     jdbc/sequeLinkPool: Reused free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Created new connection.4
>     jdbc/sequeLinkPool: Number pooled connections = 6.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Created new connection.
>     jdbc/sequeLinkPool: Number pooled connections = 7.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Created new connection.
>     jdbc/sequeLinkPool: Number pooled connections = 8.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Created new connection.
>     jdbc/sequeLinkPool: Number pooled connections = 9.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Created new connection.
>     jdbc/sequeLinkPool: Number pooled connections = 10.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Created new connection.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.5
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 1.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 2.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 3.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 4.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 5.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 6.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 7.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 8.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 9.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 10.
>     jdbc/sequeLinkPool: Connection was closed and added to the cache.
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 11.
>     jdbc/sequeLinkPool: Enforced minimum!6
>     NrFreeConnections was: 11
>     jdbc/sequeLinkPool: Number pooled connections = 11.
>     jdbc/sequeLinkPool: Number free connections = 11.
>     jdbc/sequeLinkPool: Enforced maximum!7
>     NrFreeConnections was: 11
>     jdbc/sequeLinkPool: Number pooled connections = 10.
>     jdbc/sequeLinkPool: Number free connections = 10.
>     jdbc/sequeLinkPool: Enforced minimum!
>     NrFreeConnections was: 10
>     jdbc/sequeLinkPool: Number pooled connections = 10.
>     jdbc/sequeLinkPool: Number free connections = 10.
>     jdbc/sequeLinkPool: Enforced maximum!
>     NrFreeConnections was: 10
>     jdbc/sequeLinkPool: Number pooled connections = 10.
>     jdbc/sequeLinkPool: Number free connections = 10.
>     jdbc/sequeLinkPool: Enforced minimum!
>     NrFreeConnections was: 10
>     jdbc/sequeLinkPool: Number pooled connections = 10.
>     jdbc/sequeLinkPool: Number free connections = 10.
>     jdbc/sequeLinkPool: Enforced maximum!
>     NrFreeConnections was: 10
>     jdbc/sequeLinkPool: Number pooled connections = 10.
>     jdbc/sequeLinkPool: Number free connections = 10.
>     jdbc/sequeLinkPool: Dumped free connection.8
>     jdbc/sequeLinkPool: Number pooled connections = 9.
>     jdbc/sequeLinkPool: Number free connections = 9.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 8.
>     jdbc/sequeLinkPool: Number free connections = 8.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 7.
>     jdbc/sequeLinkPool: Number free connections = 7.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 6.
>     jdbc/sequeLinkPool: Number free connections = 6.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 5.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 4.
>     jdbc/sequeLinkPool: Number free connections = 4.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 3.
>     jdbc/sequeLinkPool: Number free connections = 3.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 2.
>     jdbc/sequeLinkPool: Number free connections = 2.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 1.
>     jdbc/sequeLinkPool: Number free connections = 1.
>     jdbc/sequeLinkPool: Dumped free connection.
>     jdbc/sequeLinkPool: Number pooled connections = 0.
>     jdbc/sequeLinkPool: Number free connections = 0.
>     jdbc/sequeLinkPool: Enforced minimum!9
>     NrFreeConnections was: 0
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 5.
>     jdbc/sequeLinkPool: Enforced maximum!
>     NrFreeConnections was: 5
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 5.
>     jdbc/sequeLinkPool: Closing a pool of the group
>           jdbc/sequeLinkPool10
>     jdbc/sequeLinkPool: Number pooled connections = 5.
>     jdbc/sequeLinkPool: Number free connections = 5.
>     jdbc/sequeLinkPool: Pool closed 11
>     jdbc/sequeLinkPool: Number pooled connections = 0.
>     jdbc/sequeLinkPool: Number free connections = 0.

> ##### NOTES

> 1 The Pool Manager creates a connection pool. In this example, the characteristics of the connection pool are shown using the following format:

>     (JNDI_name,DataSource_class,initial_pool_size,
>     min_pool_size,max_pool_size,user)

> where:

> - `JNDI_name` is the JNDI name used to look up the connection pool (for example, `jdbc/sequeLinkPool`).
> - `DataSource_class` is the DataSource class associated with the connection pool (for example `com.ddtek.jdbcx.sequelink.SequeLinkDataSource`).
> - `initial_pool_size` is the number of physical connections created when the connection pool is initialized (for example, `5`).
> - `min_pool_size` is the minimum number of physical connections that will be kept open in the connection pool (for example, `5`).
> - `max_pool_size` is the maximum number of physical connections allowed within a single pool at any one time. When this number is reached, additional connections that would normally be placed in a connection pool are closed (for example, `10`).
> - `user` is the name of the user establishing the connection (for example, `scott`).

> 2 The Pool Manager checks the pool size. Because the minimum pool size is five connections, the Pool Manager creates new connections to satisfy the minimum pool size.

> 3 The driver requests a connection from the connection pool. The driver retrieves an available connection.

> 4 The driver requests a connection from the connection pool. Because a connection is unavailable, the Pool Manager creates a new connection for the request.

> 5 A connection is closed by the application and returned to the connection pool.

> 6 The Pool Manager checks the pool size. Because the number of connections in the connection pool is greater than the minimum pool size, five connections, no action is taken by the Pool Manager.

> 7 The Pool Manager checks the pool size. Because the number of connections in the connection pool is greater than the maximum pool size, 10 connections, a connection is closed and discarded from the pool.

> 8 The Pool Manager detects that a connection was idle in the connection pool longer than the maximum idle timeout. The idle connection is closed and discarded from the pool.

> 9 The Pool Manager detects that the number of connections dropped below the limit set by the minimum pool size, five connections. The Pool Manager creates new connections to satisfy the minimum pool size.

> 10 The Pool Manager closes one of the connection pools in the pool group. A pool group is a collection of pools created from the same PooledConnectionDataSource call. Different pools are created when different user IDs are used to retrieve connections from the pool. A pool group is created for each user ID that requests a connection. In our example, because only one user ID was used, only one pool group is closed.

> 11 The Pool Manager closed all the pools in the pool group. The connection pool is closed.