DB2 FAQ: Frequently Asked Questions about Application Development with DB2 LUW

Answer

Refer: DB2 FAQ for DB2 on LUW

---

Table of Contents for DB2 FAQ

A. JDBC and SQLJ

1. How to check the version of my IBM Data Server Driver for JDBC / JCC driver?
   


2. What are the different types of JDBC drivers available for DB2?
   


3. Where can I download the db2jcc.jar or the IBM Data Server Driver for JDBC/SQLJ?
   


4. How do I find the compatibility of JDBC driver with the database version on the DB2 server?
   


5. Does IBM Data Server Driver for JDBC support SSL?
   


6. How do I enable a JCC trace?
   


7. What is the URL used with DriverManager interface in the DriverManager.getConnection call to make a connection?
   


8. What is the property I use to specify the security under IBM Data Server driver for JDBC?
   


9. Where can I find the Knowledge collection for various IBM Data Server driver for JDBC issues?
   

____________________________________________________Back to Top

B. CLI

1. How to solve SQL1159 rc=10 error from my .NET application?
   


2. What are the .NET framework versions, DB2.NET data provider support?
   


3. In v9.5 and higher, the db2cli.ini file is no longer found in the SQLLIB directory. Where is the new location?
   


4. If a 64-bit DB2 product is installed, is it possible to run a 32-bit .Net application?
   


5. What data should I collect if I am getting a CLI error in my application?
   


6. How do I point to 32 bit ODBC driver on a 64 bit Microsoft system?
   


7. Why does my .NET application receive SQL0952N error for long running queries even though QUERYTIMEOUTINTERVAL=0 is set in db2cli.ini file?
   


8. What should I do if I am getting " SQL0805N Package was not found " error?
   

____________________________________________________Back to Top

C. Security

1. What should I do if I am getting SQL30082 Security processing failed with reason code15 (PROCESSING FAILURE) or 24 (USERNAME AND/OR PASSWORD INVALID)?
   


2. What should I do if I am getting SQL30082 rc=19 ("USERID DISABLED or RESTRICTED"). SQLSTATE=08001 error?
   


3. How do I set up DB2audit to only audit sysadmin activities?
   


4. What should I do if I am receiving error SQL30082N rc=42(INVALID SERVER SECURITY TOKEN) when trying to attaching an instance?
   


5. What should I do if I am getting authentication errors (SQL1092N and SQL30082N) in my windows server?
   

____________________________________________________Back to Top

D. Stored Procedures and FMP:

1. My Stored Procedure returns an error code. What kind of diagnostic information should I collect and send to IBM DB2 Technical Support for review?
   


2. How do I determine which stored procedure my package resides in?
   


3. When attempting to drop a stored procedure I receive an SQL0476N error. How do I resolve this error?
   


4. If you run sqlj.replace_jar do you need to drop and recreate your stored procedure (SP)?
   


5. If you run sqlj.remove_jar and then sqlj.install_jar do you need to drop and recreate your stored procedure?
   


6. An OutOfMemory exception is returned when running a Java routine (stored procedure or user defined function) may be returned along with a javacore and java heap dump.
   


7. Where can I find information about the db2fmp error messages logged in db2diag.log?
   

____________________________________________________Back to Top

E. Miscellaneous:

1. Why are there no GUI tools on UNIX platforms in v9.x?
   


2. Where can I find the Support policy statement for DB2 for Linux, UNIX and Windows fix packs, special builds,Total Content Ownership agreements and IBM Software Accelerated Value Program?
   


3. How can I run db2trace command remotely in a Windows® Terminal Services environment?
   


4. What are the steps I need to take if I change the hostname of the DB2 server?
   


5. How to remove hanging jobs in Task center Scheduler ?
   


6. How does the Change to daylight savings time (DST) in the U.S. and Canada affect IBM DB2 products?
   


7. Is there anything beyond what we normally do to configure DB2 Connect to user VPN? In addition when the connection is made to the remote site, how can we verify that VPN is being used?
   

____________________________________________________Back to Top


A. JDBC and SQLJ


A.1 How to check the version of my JCC driver?

The following command will retrieve the JCC driver version if executed from the command line:
java com.ibm.db2.jcc.DB2Jcc -version

Or

For drivers that are not yet installed:
java -cp ./db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version


A. 2 What are the different types of JDBC drivers available for DB2?

DB2 Legacy (CLI-based) JDBC driver:

This driver is also known as the DB2 JDBC Type 2 Driver. It has been deprecated since version 8.
The connection string will show classes of the following type:
COM.ibm.db2.jdbc
This class files for this driver are included in db2java.zip.

DB2 Universal JDBC Driver (Type 2 or Type 4):

This driver is known by several different names, depending on the version of DB2:
Version 8: DB2 Universal JDBC Driver
Version 9.1: IBM DB2 Driver for JDBC and SQLJ
Version 9.5 and above: IBM Data Server Driver for JDBC and SQLJ

The connection string will show classes of the following type:
com.ibm.db2.jcc

This class files for the driver are included in db2jcc.jar, and/or sqlj.zip (SQLJ applications) and db2jcc4.jar (JDBC 4.0 applications).

A.3 Where can I download the db2jcc.jar or the IBM Data Server Driver for JDBC/SQLJ?

You can download the driver from the following link:
https://www-304.ibm.com/support/docview.wss?rs=71&uid=swg21363866

or

You can get it from DB2 Fixpak download site:
https://www-304.ibm.com/support/docview.wss?rs=71&uid=swg21321001


A. 4 How do I find the compatibility of JDBC driver with the database version on the DB2 server?

The IBM Data Server Driver for JDBC and SQLJ is always downward compatible with DB2® databases at the previous release level.

For example, IBM Data Server Driver for JDBC and SQLJ type 4 connectivity from the IBM Data Server Driver for JDBC and SQLJ version 3.61, which is shipped with DB2 Database for Linux, UNIX, and Windows Version 9.7 Fix Pack 3, to a DB2 Database for Linux, UNIX, and Windows Version 8 database is supported.

The IBM Data Server Driver for JDBC and SQLJ is upward compatible with the next version of a DB2 database if the applications under which the driver runs use no new features.

For example, IBM Data Server Driver for JDBC and SQLJ type 4 connectivity from the IBM Data Server Driver for JDBC and SQLJ version 2.x, which is shipped with DB2 for z/OS® Version 8, to a DB2 for z/OS Version 9.1 database is supported, if the applications under which the driver runs contain no DB2 for z/OS Version 9.1 features.


A.5 Does IBM Data Server Driver for JDBC support SSL?

The IBM® Data Server Driver for JDBC and SQLJ provides support for the Secure Sockets Layer (SSL) through the Java Secure Socket Extension (JSSE).
You can use SSL support in your Java applications if you use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2® for z/OS® Version 9 or later, to DB2 Database for Linux, UNIX, and Windows Version 9.1, Fix Pack 2 or later.


A.6 How do I enable a JCC trace?

There are different ways to enable trace depending upon how the driver is used in your application.
Refer to the following link for more information.
https://www-304.ibm.com/support/docview.wss?rs=71&uid=swg21196160


A.7 What is the url used with DriverManager interface in the DriverManager.getConnection call to make a connection?

a. jdbc:db2:<dbalias> for the Legacy Type 2 driver connection
b. jdbc:db2:<hostname>:<port>//<database> for the IBM Data Server Driver for JDBC and SQLJ


A.8 What is the property I use to specify the security under IBM Data Server driver for JDBC?

When you use the IBM® Data Server Driver for JDBC and SQLJ, you can use the securityMechanism property to specify security mechanism you want to use.
More information on the values for the property, please refer to:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.java.doc/doc/cjvjcsec.html


A.9 Where can I find the Knowledge collection for various IBM Data Server driver for JDBC issues?

The knowledge collection can be found here:
https://www-304.ibm.com/support/docview.wss?uid=swg21358484

____________________________________________________Back to Top

B.CLI


B.1 How to solve SQL1159 rc=10 error from my .NET application?

Detail steps on solving SQl1159 RC=1- error can be found from the following link:
https://www-304.ibm.com/support/docview.wss?uid=swg21413731


B.3 In v9.5 and higher, the db2cli.ini file is no longer found in the SQLLIB directory. Where is the new location?

Here are the links from the info center for new locations in v9.5 and v9.7 based on version and Fixpack levels:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/t0007289.html
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/t0007289.html


B.4 If a 64-bit DB2 product is installed, is it possible to run a 32-bit .Net application?

In v9.7:

Versions of IBM Data Server Provider for .NET support 32-bit .NET provider in the 64-bit client packages. When you install the 64-bit .NET provider package, both 32-bit and 64-bit providers are installed and configured.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.swg.im.dbclient.adonet.doc/doc/c0024875.html

In v9.5:

Only the 64-bit edition of the IBM Data Server Provider for .NET is installed with DB2 Version 9 (or later) or DB2 Connect. The IBM Data Server Provider for .NET, Framework 1.1 is not installed. The 64-bit edition of the IBM Data Server Provider for .NET do not support the IA-64 architecture.

You can run 32-bit .NET applications on a 64-bit Windows instance, using WOW64, but you will need a 32-bit edition of the IBM Data Server Provider for .NET instead. To get a 32-bit IBM Data Server Provider for .NET on your 64–bit computer, you can install the 32–bit version of IBM Data Server Driver Package.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.swg.im.dbclient.adonet.doc/doc/c0024875.html


B.5 What data should I collect if I am getting a CLI error in my application?

The following technote explains in detail about the traces that need to be collected when you are getting CLI error.
https://www-304.ibm.com/support/docview.wss?uid=swg21284894


B.6 How do I point to 32 bit ODBC driver on a 64 bit Microsoft system?

The following technote explains the steps you need to do in detail.
https://www-304.ibm.com/support/docview.wss?uid=swg21384435


B.7 Why does my .NET application receive SQL0952N error for long running queries even though QUERYTIMEOUTINTERVAL=0 is set in db2cli.ini file?

In DB2 V9.7, .NET applications no longer use the db2cli.ini file.
To resolve the issue, the application should code QueryTimeout=0 in the connection string for the application. Starting in Version 9.7 fixpak 2,
you can use the .NET configuration file, db2dsdriver.cfg, to code the QueryTimeout value.

More details: https://www-304.ibm.com/support/docview.wss?uid=swg21450816


B.8 What should I do if I am getting " SQL0805N Package <package-name> was not found " error?

The SQL0805N error could be caused by multiple reasons.
Here is a knowledge collection of all the technotes that has the resolution for this issue.
https://www-304.ibm.com/support/docview.wss?uid=swg21358478

____________________________________________________Back to Top

C.Security

C.1 What should I do if I am getting SQL30082 Security processing failed with reason code15 (PROCESSING FAILURE) or 24 (USERNAME AND/OR PASSWORD INVALID) ?

There are various scenarios that could cause this error.
Here is the technote that explains the steps to resolve the issue.
https://www-304.ibm.com/support/docview.wss?uid=swg21416382


C.2 What should I do if I am getting SQL30082 rc=19 ("USERID DISABLED or RESTRICTED"). SQLSTATE=08001 error?

Here are the information you need to check to solve the issue.
https://www-304.ibm.com/support/docview.wss?uid=swg21409496


C.3 How do I set up DB2audit to only audit sysadmin activities?

Here is the step by step instruction in setting up the DB2Audit:
https://www-304.ibm.com/support/docview.wss?uid=swg21461792


C.4 What should I do if I am receiving error SQL30082N rc=42(INVALID SERVER SECURITY TOKEN) when trying to attaching an instance?

The following techdoc has the details about cause and solution to the problem.
https://www-304.ibm.com/support/docview.wss?uid=swg21428687


C.5 What should I do if I am getting authentication errors (SQL1092N and SQL30082N) in my windows server?

Here is the problem determination guide for DB2 Security model in Windows platform.
https://www-304.ibm.com/support/docview.wss?rs=71&uid=swg21423923

____________________________________________________Back to Top

D. Stored Procedures and FMP:


D.1 My Stored Procedure returns an error code. What kind of diagnostic information should I collect and send to IBM DB2 Technical Support for review?

Information regarding specific diagnostics to send for a Stored Procedure error code can be found in the following technote.
https://www-304.ibm.com/support/docview.wss?uid=swg21383251


D.2. How do I determine which stored procedure my package resides in?

With a specific package name you may run SQL against the catalog views to determine which stored procedure contains the package.

Run the following SQL from a DB2 command window:

db2 "select distinct a.routinename, b.bname from syscat.routines a,
syscat.routinedep b, syscat.packages c where c.valid = 'Y' and
b.routinename = a.specificname and b.bname = c.pkgname"


D.3. When attempting to drop a stored procedure I receive an SQL0476N error. How do I resolve this error?

SQL0476N Reference to routine "SCHEMA.PROCEDURENAME" was made without a signature, but the routine is not unique in its schema. SQLSTATE=42725

When multiple stored procedures are created with the same name but with a different number of parameters, then the stored procedure is considered overloaded. This means that the stored procedure is not unique in the schema. To drop the procedure you must specify the data types that were specified on the CREATE PROCEDURE statement or use the stored procedure's specific name.

db2 "DROP PROCEDURE procedure-name(int, varchar(12))"
db2 "DROP SPECIFIC PROCEDURE specific-name"

Note: The specific-name can be identified by selecting the SPECIFICNAME column from syscat.routines catalog view.


D.4 If you run sqlj.replace_jar do you need to drop and recreate your stored procedure (SP)?

No. Dropping and recreating a Stored Procedure just registers the Stored Procedure in the database. There is no need to drop and recreate the Stored Procedure as the Stored Procedure definition is unchanged. However, you do want to run SQLJ.REFRESH_CLASSES() after sqlj.replace_jar to load the updated jar file to memory.


D.5. If you run sqlj.remove_jar and then sqlj.install_jar do you need to drop and recreate your stored procedure?

No. Run SQLJ.REFRESH_CLASSES() to load the updated jar file to memory.


D.6. An OutOfMemory exception is returned when running a Java routine (stored procedure or user defined function) may be returned along with a javacore and java heap dump.

This technote has information on solving the issue.
https://www-304.ibm.com/support/docview.wss?uid=swg21412612


D.7 Where can I find information about the db2fmp error messages logged in db2diag.log?

Here is the technical article on more information on db2fmp error messages.
https://www-304.ibm.com/support/docview.wss?uid=swg21470035

____________________________________________________Back to Top

E. Miscellaneous:


E.1 Why are there no GUI tools on UNIX platforms in v9.x?

As seen in this link:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004670.htm

"In previous releases, the DB2 Administration Tools, including the Control Center, were supported on all platforms. In Version 9 and later, the DB2 Administration Tools, are supported only on Windows x86, Windows x64 (AMD64 or EM64T), Linux on x86, and Linux on AMD64 or EM64T."


E.2 Where can I find the Support policy statement for DB2 for Linux, UNIX and Windows fix packs, special builds,Total Content Ownership agreements and IBM Software Accelerated Value Program?

Here is the Support policy statement.
https://www-304.ibm.com/support/docview.wss?uid=swg21180416


E.3 How can I run db2trace command remotely in a Windows® Terminal Services environment?

The detailed steps can be found in the following link:
https://www-304.ibm.com/support/docview.wss?uid=swg21226313


E.4 What are the steps I need to take if I change the hostname of the DB2 server?

Step by step instructions can be found in this technote:
https://www-304.ibm.com/support/docview.wss?uid=swg21258834


E.5 How to remove hanging jobs in Task center Scheduler ?

Scheduled tasks may sometimes appear as running in the Scheduler even though they have completed. The following link has detailed information on removing those hanging jobs.
https://www-304.ibm.com/support/docview.wss?uid=swg21116369


E.6 How does the Change to daylight savings time (DST) in the U.S. and Canada affect IBM DB2 products?

Here is the technote that has information on how DB2 products are affected due to the extended DST.
https://www-304.ibm.com/support/docview.wss?uid=swg21248542


Is there anything beyond what we normally do to configure DB2 Connect to user VPN? In addition when the connection is made to the remote site, how can we verify that VPN is being used?

Install and configuration of DB2 in a VPN environment is the same as in any other environment. The best way to verify what VPN address is being used is to setup a network trace and have it analyzed by your network team. You can also collect a DB2 trace as outlined in the below link:

https://www-304.ibm.com/support/docview.wss?uid=swg21286651

Then send the trace files to DB2 support for review along with the desired VPN address.

____________________________________________________Back to Top