Using the jdbcinfo tool

The jdbcinfo tool, jdbcinfo.jar, supports basic tests using JDBC data sources to help diagnose problems.

The jdbcinfo.jar file is located in %COGNOS_HOME%\webapps\p2pd\WEB-INF\lib, where %COGNOS_HOME% is the location where you installed Cognos Analytics.

The tool uses 'raw' inputs that would be supplied by a JDBC application and displays the 'raw' responses generated from the data source, its usage and the subsequent interpretation of its output is intended for an audience that is knowledgeable about the JDBC API. As such, it is expected that the tool will only be used at the direction of IBM Cognos when normal debugging and development practices used in IBM Cognos products have been exhausted.

Usage

The tool is packaged as a stand alone executable jar file that, through command-line options and classpath settings, can be used to replicate many different testing scenarios. To run it, you must use the same version of java that you are using with your IBM Cognos product. On windows platforms, this will usually be the JVM located in a directory similar to %COGNOS_HOME%\ibm-jre\jre. On Linux/Unix platforms, it will be the the JVM indicated by either the JRE_HOME or JAVA_HOME environment variables.

The syntax for executing the command is: 

java -cp <classpath> com.ibm.cognos.jdbcinfo.App [args] [actions [parameters] ...]

or just for the latest command line help: 

java -jar jdbcinfo.jar
Classpath

The value of <classpath> is a delimited list of fully qualified path names to jar files and directories containing jar files that will be loaded by jdbcinfo when running. On Windows, the delimiter is a semi-colon ( ;) while on Linux/Unix it is a colon ( :).

The class path should include any JDBC drivers and IBM Cognos jar files you wish to include in the test, in addition to jdbcinfo.jar.

Args

There are four arguments that are required in most situations, which are the basic parameters to specify the driver and database to connect to and test:

  • -u user ID
  • -p password
  • -d driver class
  • -U JDBC connection URL
Actions

Once connected, jdbcinfo can perform a number of activities (typically executing JDBC methods) that produce responses that may be useful when debugging an issue. Any number of actions may be specified and they have the following syntax: 

<action name> [parameters]

The keyword '-NULL-' is used to specify a null values parameter (where null values are allowed). Action parameters follow the naming and meaning as described for parameters the JDBC method being tested by the action. Having a JDBC API reference handy will be useful when using jdbcinfo.

The list of actions will grow as the tool is enhanced, but the current list is as shown in the following sub sections.

Attributes

Executes DatabaseMetadata.getAttributes on the specified connection. 

Attributes <catalog> <schema name pattern> <type name pattern> <attribute name pattern>
Catalogs

Executes DatabaseMetadata.getCatalogs on the specified connection. 

Catalogs
Columns

Executes DatabaseMetadata.getColumns on the specified connection. 

Columns <catalog> <schema name pattern> <table name pattern> <column name pattern>
ConnectionProperties

Executes Connection.getPropertyInfo on the specified connection 

ConnectionProperties
DatabaseMetadataInterface

Tests the object obtained via Connection.getMetaData to verify compliance with the JDBC DatabaseMetadata interface specification. 

DatabaseMetadataInterface
DriverVersion

Executes the DatabaseMetadata driver version methods on the specified connection. 

DriverVersion
ExportedKeys

Executes DatabaseMetadata.getExportedKeys on the specified connection. 

ExportedKeys <catalog> <schema> <table>
ExtraInformation

Displays some extra information that the IBM Cognos application derives from the specified connection. Requires JDBCAdaptorSDK jar files to be included in the classpath to function. 

ExtraInformation
FactoryNames

Displays the JDBC adaptor factory class name and configuration file name used by IBM Cognos JDBCAdaptorSDK classes when establishing connections to the specified JDBC driver. Requires JDBCAdaptorSDK jar files to be included in the classpath to function. 

FactoryNames
FunctionColumns

Executes DatabaseMetadata.getFunctionColumns on the specified connection. 

FunctionColumns <catalog> <schema name pattern> <procedure name pattern> <column name pattern>
Functions

Executes DatabaseMetadata.getFunctions on the specified connection. 

Functions <catalog> <schema name pattern> <function name pattern>
ImportedKeys

Executes DatabaseMetadata.getImportedKeys on the specified connection. 

ImportedKeys <catalog> <schema> <table>
IndexInfo

Executes DatabaseMetadata.getIndexInfo on the specified connection. 

IndexInfo <catalog> <schema> <table name> <unique> <approximate>

Unique and approximate are boolean values.

KerberosConnection

Establishes a Kerberos authenticated connection. The tool creates a JAAS Subject from the credentials and places it in the authorization context (Subject.doAs()). The Kerberos implementation should retrieve it from there when the JGSS-API creates credentials. 

KerberosConnection <driver class name> <jdbc url> <principal> <password>
  • jdbc url should include the necessary kerberos-related properties for connecting using pre­authorized subject.
  • principal is the client user account, not the database service.
LoadOptions

Loads command line options previously saved using the SaveOptions Action. Useful for storing connection information in external files. 

LoadOptions <file name>
ProcedureColumns

Executes DatabaseMetadata.getProcedureColumns on the specified connection. 

ProcedureColumns <catalog> <schema name pattern> <procedure name pattern> <column name pattern>
Procedures

Executes DatabaseMetadata.getProcedures on the specified connection. 

Procedures <catalog> <schema name pattern> <procedure name pattern>
SaveOptions

Stores command line options into a text file for subsequent use with the LoadOptions Action. Useful for storing connection information in an external file. 

SaveOptions <file name>
Schemas

Executes JDBC V3 version of DatabaseMetadata.getSchemas on the specified connection. 

Schemas
SchemasV4

Executes JDBC V4 version of DatabaseMetadata.getSchemas on the specified connection. 

SchemasV4 <catalog> <schema name pattern>
ServerVersion

Executes the DatabaseMetadata server version methods on the specified connection. 

ServerVersion
Sql

Executes a SQL statement on the specified connection. 

Sql <mode bitmask> <SQL query>

mode bitmask determines how the query is executed:

  • 0, use Connection.Prepare(<SQL Query>) then call execute on the resulting PreparedStatement object.
  • 1, use Connection.Prepare(<SQL Query>) then call executeQuery on the resulting PreparedStatement object.
  • 2, construct a Statement Object then call execute(<SQL Query>) on it.
  • 3, construct a Statement Object then call executeQuery(<SQL Query>) on it.
SubType

Returns the sub type code (if any) that IBM Cognos assigns to the specified connection. 

SubType
TableTypes

Executes DatabaseMetadata.getTableTypes on the specified connection. 

Tables
Tables

Executes DatabaseMetadata.getTables on the specified connection. 

Tables <catalog> <schema name pattern> <table name pattern> <type>[,type ...]
TypeInfo

Executes DatabaseMetadata.getTypeInfo on the specified connection. 

TypeInfo
UDT

Executes DatabaseMetadata.getUDT on the specified connection. 

UDT <catalog> <schema name pattern> <type name pattern> <types>
Verbose

Increases the verbosity of jdbcinfo. 

verbose

Example of saving and loading connection options

In this example, we save the connection information to a file called db2.info, and then reuse it later.

Note:

The classpath must include all the JAR files corresponding to the JDBC driver to be tested. In this example, the IBM JCC JDBC driver is used, where only one JAR file is required.

%COGNOS_DRIVERS% references the drivers folder in your CA environment into which you copied your JDBC driver JAR files.

Saving
java -cp jdbcinfo.jar:db2/%COGNOS_DRIVERS%/db2jcc4.jar com.ibm.cognos.jdbcinfo.App 
-U jdbc:db2://server:50105/dbname -d com.ibm.db2.jcc.DB2Driver -u user 
-p password saveoptions db2.info

Action SaveOptions: 
saving configuration to: db2.info 

db2.info contains the following 
#automatically generated file 
URL=jdbc\:db2\://server\:50105/dbname 
driverClass=com.ibm.db2.jcc.DB2Driver 
user=user 
password=password
Loading
java -cp jdbcinfo.jar:db2/%COGNOS_DRIVERS%/db2jcc4.jar com.ibm.cognos.jdbcinfo.App loadoptions db2.info 

Action LoadOptions: loading configuration from: db2.info

Example of displaying version information

In this example, we retrieve the driver and server version information. For brevity, we use the connection information we saved in the Saving Connection Options example.

Note:

The classpath must include all the JAR files corresponding to the JDBC driver to be tested. In this example, the IBM JCC JDBC driver is used, where only one JAR file is required.

%COGNOS_DRIVERS% references the drivers folder in your CA environment into which you copied your JDBC driver JAR files.

Command
java -cp jdbcinfo.jar:db2/%COGNOS_DRIVERS%/db2jcc4.jar com.ibm.cognos.jdbcinfo.App 
loadoptions db2.info driverVersion serverversion
Response
configuration: 
Java version: n.n.n_nn 
Java vendor: Oracle Corporation 
Java vm name: OpenJDK 64-Bit Server VM 
Using classpath jdbcinfo.jar:db2/n.nn.nn/db2jcc4.jar 

Action LoadOptions: 
loading configuration from: db2.info 

Action DriverVersion: 
* attempting to load driver... 
* Driver loaded. driver major version: n driver minor version: n driver is JDBC compliant: true 
* attempting to get metadata from connection ... 
* attempting to connect... 
* connected. 
* successfully created metadata object. 
driver version: n.nn.nn 
JDBC major version: n 
JDBC minor version: n 

Action ServerVersion: 
database product name: DB2/AIX64 
database major version: nn 
database minor version: n 
database product version: xxxnnn 

closing connection ...

Example of displaying Tables

In this example, we retrieve the list of tables of type VIEW in the database.

Note:

The classpath must include all the JAR files corresponding to the JDBC driver to be tested. In this example, the IBM JCC JDBC driver is used, where only one JAR file is required.

%COGNOS_DRIVERS% references the drivers folder in your CA environment into which you copied your JDBC driver JAR files.

Command
java -cp jdbcinfo.jar:db2/%COGNOS_DRIVERS%/db2jcc4.jar com.ibm.cognos.jdbcinfo.App 
loadoptions db2.info tables -null- -null- -null- VIEW
Response
Action LoadOptions: 
loading configuration from: db2.info 

Action Tables: calling getTables(null, null, null, [VIEW]) 
* attempting to get metadata from connection ...
* attempting to load driver... 
* Driver loaded. 
* attempting to connect... 
* connected. 
* successfully created metadata object. 
361 rows returned. 
       1  TABLE_CAT (VARCHAR): null  
       1  TABLE_SCHEM (VARCHAR): dbname  
       1  NAME (VARCHAR): VIEW_€_TABLE  
       1  TABLE_TYPE (VARCHAR): VIEW  
       1  REMARKS (VARCHAR): null  
       1  TYPE_CAT (VARCHAR): null  
       1  TYPE_SCHEM (VARCHAR): null  
       1  TYPE_NAME (VARCHAR): null  
       1  SELF_REF_COL_NAME (VARCHAR): null  
       1  REF_GENERATION (VARCHAR): null  
       
       2  TABLE_CAT (VARCHAR): null  
       2  TABLE_SCHEM (VARCHAR): dbname  
       2  NAME (VARCHAR): VIEW_BETWEEN1  
       2  TABLE_TYPE (VARCHAR): VIEW  
       2  REMARKS (VARCHAR): null  
       2  TYPE_CAT (VARCHAR): null  
       2  TYPE_SCHEM (VARCHAR): null  
       2  TYPE_NAME (VARCHAR): null  
       2  SELF_REF_COL_NAME (VARCHAR): null  
       2  REF_GENERATION (VARCHAR): null  
... 
closing connection ...