Connectivity cheat sheet for Application Developers (Part 4)

Raul Chong (rfchong@ca.ibm.com), Database consultant, SDI Corp.

Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked at IBM for six years, three years in DB2 Technical Support, and the other three as a consultant specializing in database application development and migrations from other RDBMS to DB2.

Jalud Abdulmenan, Database consultant

Jalud Abdulmenan is a former IBM employee who left the company in 2002 to continue his professional studies. While at IBM, Jalud was responsible for Quality Assurance of two DB2 migration toolkits, and was instrumental in the development of the DB2 Scholars Support program, which helped promote DB2 UDB in universities. Currently, Jalud is in the process of setting up his own database consulting business.

Summary: This is the fourth article discussing connectivity scenarios involving DB2 UDB for Linux, UNIX, and Windows Version 8, DB2 UDB for iSeries 5.2 and DB2 UDB for z/OS V8. The focus of this article is on workstation application development connectivity using C and Java languages.

Tag this!

Date: 15 Jan 2004
Level: Introductory
Activity: 2558 views

Introduction

This is the fourth article discussing connectivity scenarios involving IBM® DB2® Universal Database^TM (UDB) for Linux, UNIX® and Windows® version 8, DB2 UDB for iSeries^TM 5.2 and DB2 UDB for z/OS® V8 (At the time this article was written, DB2 UDB for z/OS version 8 had not yet been released for general availability. DB2 for OS/390 and z/OS v7 and a beta version of V8 were used to test the scenarios.). As in the third part of the article, the latest versions are being used; however, the instructions shown may apply to other versions. The focus of this article is on workstation application development connectivity using C and Java languages. We will provide sample programs developed on workstation platforms (Linux, UNIX and Windows) using JDBC^TM, SQLJ, ODBC and so on, where each program connects to a DB2 UDB database (any platform), selects from a table, and disconnects. The programs have been tested on a Microsoft® Windows 2000 platform; however, they should be portable to UNIX and Linux platforms. Due to space limitations, we provide the code listing of each sample program with minimal comments and error checking (though still fully functional). For the complete functional sample programs, please download the accompanying file to this article. A fifth article in this series will cover ADO, and ADO.NET, and a sixth article in the series will cover other languages as well. Programs written natively in z/OS, OS/390 or iSeries will not be discussed. Please note we assume you have some programming background; therefore, we will not be discussing programming tips, or how to code in general.

Working as consultants, we have encountered situations where a sample program would have been handy to test if the application connectivity setup was correct. The sample programs in this article can be used for this purpose. With DB2 UDB for Linux, UNIX and Windows V8 you can now test several types of connections using the Configuration Assistant (CA); however, the sample programs will be handy for previous version of DB2 UDB and also they give you more detail on the connectivity setup as the source code is provided.

This article should be useful also to DBAs who normally don’t need to code programs. Having a basic understanding about how to write a program against a DB2 database should be advantageous to them when interacting with their application developers.

The programs in this article have been based on the sample programs supplied with the DB2 UDB Application Development client (under directory sqllib/samples); however, they have been simplified for ease of understanding.

This article will describe the following connectivity scenarios:

C/C++:

Scenario 1: Connecting to a DB2 UDB database using CLI
Scenario 2: Connecting to a DB2 UDB database using ODBC
Scenario 3: Connecting to a DB2 UDB database using embedded SQL and C host language

Java:

Scenario 4: Connecting to a DB2 UDB database using the SUN type 1 driver (JDBC – ODBC Bridge)
Scenario 5: Connecting to a DB2 UDB database using the IBM DB2 UDB type 2 driver (App Driver)
Scenario 6: Connecting to a DB2 UDB database using the IBM DB2 UDB type 2 driver (Universal Driver)
Scenario 7: Connecting to a DB2 UDB database using the IBM DB2 UDB type 3 driver (Net Driver)
Scenario 8: Connecting to a DB2 UDB database using the IBM DB2 UDB type 4 driver (Universal Driver)
Scenario 9: Connecting to a DB2 UDB database using SQLJ

It is assumed the protocol used is the TCP/IP protocol given that it is the one most often used. Only basic setup information will be shown.

Note that we describe the scenarios in a two-tier environment. For an n-tier environment, just consider ‘machine 1’ as the system where the C or Java program is being executed from.

For all the scenarios described below, the following Windows 2000 environment variables were set in our test machines. Most of these environment variables are automatically set when installing DB2 UDB. Note that for some of these sample programs, we are using MS Visual Studio V6 for compilation, and this is reflected in some of the environment variable values. In UNIX/Linux platforms some of these variable names are slightly different. Make sure to set these variables appropriately according to your environment:

Table 1. Windows 2000 Environment Variables required

Environment Varhiable	Value
CLASSPATH	.;C:\PROGRA~1\SQLLIB\java\db2java.zip;C:\PROGRA~1\SQLLIB\java\db2jcc.jar;C:\PROGRA~1\SQLLIB\java\sqlj.zip;C:\PROGRA~1\SQLLIB\bin;C:\PROGRA~1\SQLLIB\java\common.jar;C:\PROGRA~1\SQLLIB\java\db2jcc_license_cisuz.jar;C:\PROGRA~1\SQLLIB\java\db2jcc_license_cu.jar;
INCLUDE	C:\PROGRA~1\SQLLIB\INCLUDE;C:\PROGRA~1\SQLLIB\LIB
LIB	C:\WINNT\system32;C:\Program Files\Microsoft Visual Studio\VC98\mfc\lib;C:\Program Files\Microsoft Visual Studio\VC98\lib;C:\PROGRA~1\SQLLIB\LIB
Path	C:\Program Files\SQLLIB\java\jdk\bin;C:\PROGRA~1\SQLLIB\BIN;C:\PROGRA~1\SQLLIB\FUNCTION;

Scenario 1 – Connecting to a DB2 UDB database (any platform) using CLI

The DB2 UDB CLI Driver (which is also used as the DB2 UDB ODBC Driver) can be obtained by downloading (free of charge) any of the IBM DB2 UDB clients at: http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/index.d2w/report

Figure 1. DB2 UDB for Linux, UNIX and Windows CLI application client to DB2 UDB server (any platform)

Listing 1. dbconn_ODBC_CLI.c (With minimal comments and no error checking)

//***************************************************************************
// SOURCE FILE NAME: dbconn_ODBC_CLI.c
//***************************************************************************

/* Standard C/C++ Headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/* DB2 ODBC Header */
#include <sqlcli1.h>

/* Main Program 
*/int main(int argc, char** argv)
{
	/* Handles */   
	SQLHENV  henv = SQL_NULL_HENV;
	SQLHDBC  hdbc = SQL_NULL_HDBC;   
	SQLHSTMT hstmt= SQL_NULL_HSTMT;   
	
	/* Local variables */   
SQLRETURN     rc;   
SQLCHAR       statement[32761];   
SQLCHAR       mycolout[2];  
SQLINTEGER    mycoloutlen = 0;   
char          dbname[8 + 1];   
char          user[18 + 1];   
char          pswd[30 + 1];   
	
if (argc < 4) {       
	printf ("Usage: dbconn_ODBC_CLI <database name> <userid> <password>\n");       
	exit (0);   
}   
strcpy (dbname, argv[1]);   
strcpy (user,   argv[2]);   
strcpy (pswd,   argv[3]);   
	
rc = SQLAllocHandle(SQL_HANDLE_ENV,                       
					SQL_NULL_HANDLE,                       
					&henv);   
						
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,        
	(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
		   
rc = SQLAllocHandle(SQL_HANDLE_DBC,                       
					henv,                       
					&hdbc);   
						
/* connect to the database */        
		rc = SQLConnect(hdbc,        
		(SQLCHAR *)dbname,         
		SQL_NTS,        
		(SQLCHAR *)user,         
		SQL_NTS,        
		(SQLCHAR *)pswd,         
		SQL_NTS);      
	printf ("\n  Successful connection to '%s' db using ODBC/CLI \n",dbname);   
	
rc = SQLAllocHandle(SQL_HANDLE_STMT,                       
					hdbc,                       
					&hstmt);
					   
strcpy(statement,"SELECT IBMREQD");   
strcat(statement,"  FROM SYSIBM.SYSDUMMY1;");   

rc = SQLExecDirect(hstmt,                      
					statement,                      
					SQL_NTS);   
					
rc = SQLBindCol(hstmt, 1,                   
				SQL_C_CHAR,                   
				mycolout,                   
				sizeof(mycolout),                   
				&mycoloutlen);   
				
while((rc = SQLFetch(hstmt)) == SQL_SUCCESS        
		|| rc == SQL_SUCCESS_WITH_INFO)   
{     
	/* Print data */     
	printf("  Successful retrieval of record.  Column 'IBMREQD' has a value of '%s'\n",mycolout);
}   

rc = SQLCloseCursor(hstmt);   
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);   
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);   
rc = SQLDisconnect(hdbc);   
printf ("  Successful Disconnection from database '%s'\n",dbname);   

rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);   
rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);   
printf ("  End of Program\n");   
return 0;

}  // dbconn_ODBC_CLI

Table 2. DB2 UDB for Linux, UNIX and Windows CLI client application to DB2 UDB server (any platform)

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring Connectivity
Refer to Part 1 of this article series for detailed connectivity setup instructions.
Part II: Compiling and linking the CLI program
In Windows using MS Visual Studio 6: cl -Zi -Od -c -W2 -DWIN32 dbconn_ODBC_CLI.c link -debug -out:dbconn_ODBC_CLI.exe dbconn_ODBC_CLI.obj db2cli.lib db2api.lib For this article we put the two commands above in a file called ‘bldappCLI.bat’ which takes the program name (without extension) as an argument. Note that we are linking the program using db2cli.lib (CLI driver) as opposed to odbc32.lib (ODBC Driver Manager). This means this is a CLI program and thus, it will not go through an ODBC Driver Manager; therefore, the program will only work against a DB2 UDB database. * Note: The DB2 runtime client is enough to run this CLI program; however, if you are developing the program as in this case where you need to compile it, then you need the DB2 Application Development Client. Make sure your environment variables are correctly set (see table 1 for an example).
Part III: Executing the program
dbconn_ODBC_CLI sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> ‘sample’ is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias.	sample = Database name db2admin = User id on the server mypsw = Password on the server

Figure 2 shows the output of the DB2 UDB ‘list db directory’ command issued in the Windows system we have used to develop our programs. The first entry ‘NEW_JERS’ represents a DB2 UDB for z/OS database, the second entry ‘TORISC6’ represents a DB2 UDB for iSeries database, and finally, the third entry ‘SAMPLE’ represents a DB2 UDB for Linux, UNIX and Windows database. We will use all the sample programs in this article against these three databases where applicable.

Figure 2. List db directory output (System database directory)

Figure 3 shows the output of the program db2conn_ODBC_CLI.c when run against the three databases shown in Figure 2.

Figure 3. dbconn_ODBC_CLI.c program output

Scenario 2 – Connecting to a DB2 UDB database (any platform) using ODBCI

DB2 UDB conforms to ODBC 3.51. As mentioned in Scenario 1, the DB2 UDB CLI Driver acts as the DB2 UDB ODBC Driver when interacting with an ODBC Driver manager.

In Windows platforms, the MS ODBC driver manager is also installed with DB2 UDB by default (The DB2 UDB installation will not overwrite a newer version of the driver manager if found). In UNIX and Linux platforms a driver manager is not provided. Starting with DB2 UDB V8, however, IBM officially supports the open source unixODBC Driver manager.

Figure 4. DB2 UDB for Linux, UNIX and Windows ODBC application client to DB2 UDB server (any platform)

Note that for this scenario, we use exactly the same program as shown in Listing 1. Program db2conn_ODBC_CLI.c is a ‘CLI’ program when you link it directly with the db2cli.lib library which is the CLI driver. When linked with the odbc32.lib library, this same program would be considered an ODBC program.

Table 3. DB2 UDB for Linux, UNIX and Windows ODBC client application to DB2 UDB server (any platform)

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity
Refer to Part 1 of this article series for detailed connectivity setup instructions.
Part II: Registering the database as an ODBC data source
To check if the DB2 UDB database had been registered before, list your current data sources with the following command: `db2 list system odbc data sources` To ‘register’ your DB2 UDB database as an ODBC Data Source issue: `db2 catalog system odbc data source sample` If you made a mistake in the previous command, you can remove the entry as follows: `db2 uncatalog system odbc data source sample` In the Windows 2000 platform you can also perform the above by going to: Control Panel -> Administrative Tools -> Data Sources (ODBC) In UNIX and Linux platforms some entries are required in the .odbc.ini file (or odbc.ini if Linux) and optionally in other configuration files. For example, using the unixODBC Driver Manager in a Linux environment, the following was sufficient in file odbc.ini: `[sample] Driver = /opt/IBM/db2/V8.1/lib/libdb2.so` Environment variable `ODBCINI` should also be set to the full path of file odbc.ini. Eg: `export ODBCINI=/etc/odbc.ini` Because there are slight differences between the UNIX platforms, we will not cover all the cases in this article. For details please refer to the DB2 Connect EE Quick Beginnings Manual
Part III: Compiling and linking the ODBC program
In Windows using MS Visual Studio 6: cl -Zi -Od -c -W2 -DWIN32 dbconn_ODBC_CLI.c link -debug -out:dbconn_ODBC_CLI.exe dbconn_ODBC_CLI.obj odbc32.lib For this article we put the two commands above in a file called ‘bldappODBC.bat’ which takes the program name (without extension) as an argument. Note that we are linking the program using odbc32.lib (ODBC Driver Manager). This library is supplied with MDAC SDK (formerly ODBC SDK). For this article, we used this library supplied with MS Visual Studio. Since you are linking your program to the ODBC Driver Manager library, this program will be considered an ODBC one. The ODBC Driver Manager will review the entries created in part II, and will determine which driver to use. For this example, it will determine that ‘sample’ database is a DB2 UDB database; therefore it will use the DB2 UDB ODBC driver (a.k.a., CLI driver). Once the DB2 UDB ODBC Driver is invoked, it will look at the entries in the DB2 system database directory (obtained with the command `list db directory`. See Figure 2), and it will be able to connect. * Note: The DB2 runtime client is enough to run this ODBC program. Make sure your environment variables are correctly set (see table 1 for an example)
Part IV: Executing the program
dbconn_ODBC_CLI sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> ‘sample’ is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias.	sample = Database name db2admin = User id on the server mypsw = Password on the server

Figure 5 shows the output of the ‘list system odbc data sources’ command.

Figure 5. List system odbc data sources output (System ODBC Data Sources)

The output of program dbconn_ODBC_CLI.c was shown in Figure 3. The only difference in that figure for this scenario is the fact that batch program ‘bldappODBC.bat’ is used to compile the program rather than batch program ‘bldappCLI.bat’.

Scenario 3 – Connecting to a DB2 UDB database (any platform) using embedded SQL and C host language

Embedded SQL does not mean it is static SQL. An embedded SQL can contain either dynamic or static SQL depending on whether everything in a SQL statement is known before run time or not. The example below shows a static embedded SQL program as it is easier to code. The emphasis of this article is on connectivity, so we will not discuss any further the difference between static versus dynamic SQL.

Figure 6. DB2 UDB for Linux, UNIX and Windows C embedded SQL application client to DB2 UDB server (any platform)

The sample embedded SQL C program dbconn_embed.sqc is provided is Listing 2.

Listing 2. dbconn_embed.sqc

//***************************************************************************
// SOURCE FILE NAME: dbconn_embed.sqc
//***************************************************************************
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlca.h>

int main(int argc, char** argv)
{  
EXEC SQL INCLUDE SQLCA;  

EXEC SQL BEGIN DECLARE SECTION;    
	char dbname[15]    ;    
	char user[128 + 1] ;    
	char pswd[15]      ;    
	char mycolout[2]   ;  
EXEC SQL END DECLARE SECTION; 

if (argc < 4) {      
	printf ("Usage: dbconn_embed <database name> <userid> <password>\n");      
	exit (0);   
}   

strcpy (dbname, argv[1]);   
strcpy (user,   argv[2]);   
strcpy (pswd,   argv[3]);  

/* connect to a database */    
EXEC SQL CONNECT TO :dbname USER :user USING :pswd;   
if (SQLCODE != 0) {        
		printf ("\n  *** Error ***\n");        
		exit (0);    
}    
else {        
		printf ("\n  Successful connection to '%s' db using C embedded static SQL\n",dbname);    
}    
EXEC SQL SELECT IBMREQD INTO :mycolout FROM SYSIBM.SYSDUMMY1;     

printf("  Successful retrieval of record.  Column 'IBMREQD' has a value of '%s'\n",mycolout);  

/* Disconnect from a database */     
EXEC SQL CONNECT RESET;     
printf ("  Successful Disconnection from database '%s'\n",dbname);     
printf ("  End of Program\n");     
return 0;

} /* end main */

Table 4. DB2 UDB for Linux, UNIX and Windows embedded SQL C client application to DB2 UDB server (any platform)

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity
Refer to Part 1 of this article series for detailed connectivity setup instructions.
Part II: Precompiling and binding
db2 connect to sample user db2admin using mypsw db2 prep dbconn_embed.sqc bindfile db2 bind dbconn_embed.bnd db2 connect reset The precompile command (prep) will parse the dbconn_embed.sqc file and create two files:dbconn_embed.c (pure C code)dbconn_embed.bnd (Bind file with only SQL) Then you need to bind the bind file dbconn_embed.bnd to create a package in the database; thus, you should connect to the database prior to issuing this command.
Part III: Compiling the program
cl -Zi -Od -c -W2 -DWIN32 dbconn_embed.c link -debug -out:dbconn_embed.exe dbconn_embed.obj db2api.lib * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. Make sure your environment variables are correctly set (see table 1 for an example)
Part IV: Executing the program
dbconn_embed sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> ‘sample’ is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias.	sample = Database name db2admin = User id on the server mypsw = Password on the server

The Windows batch file bldapp_embed.bat contains the precompile, bind, compile and link steps. Figure 7 shows the execution of this batch file as well as the execution of the embedded SQL C program. Due to space limitation, we will only show the output when connecting to a DB2 UDB for Linux, UNIX and Windows database.

Figure 7. dbconn_embed.sqc program Output

Scenario 4 – Connecting to a DB2 UDB database (any platform) using the SUN type 1 driver (JDBC – ODBC Bridge)

Since the type 1 driver is converting JDBC method calls into ODBC function calls, it normally provides a slower performance than the other driver types. For completeness we will cover this scenario; however, we recommend you to use any of the other IBM DB2 JDBC drivers. You can obtain them by downloading (free of charge) any of the IBM DB2 UDB Clients at: http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/index.d2w/report

Given that this driver will be using ODBC, make sure you registered your ODBC Data Source as described in scenario 2 before using this JDBC driver.

Figure 8. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using SUN’s type 1 driver

The sample JDBC program DBConn_Type1.java is provided is Listing 3.

Listing 3. DBConn_Type1.java

//***************************************************************************
//SOURCE FILE NAME: DBConn_Type1.java
//***************************************************************************

import java.sql.*;

class DBConn_Type1
{  
	public static void main(String argv[])  
	{   
	// Check arguments   
	if (argv.length < 3) {       
		System.out.println("Usage: java DBConn_Type1 <database name> <userid> <password>");
		return;    
		}    
		
		try    
		{     
		String dbname    = argv[0];     
		String userId    = argv[1];     
		String password  = argv[2];     
		String url       = "jdbc:odbc:" + dbname;     
		String v_IBMREQD = null;     
		// Registering driver and connecting     
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();     
		Connection con = DriverManager.getConnection( url, userId, password );     
		
		System.out.println(      
		"  Successful connection to '" + dbname + "' db using JDBC type 1 driver" );     
		
		// Retrieving a record from a table     
		Statement stmt = con.createStatement();     
		ResultSet rs   = stmt.executeQuery("SELECT IBMREQD FROM SYSIBM.SYSDUMMY1");    
		
		// There is only one record in SYSIBM.SYSDUMMY1.  Note that if you are not     
		// a DB2 instance owner, you may have to issue this command before running this program:      
		// db2 GRANT select on sysibm.sysdummy1 to public     
		while ( rs.next() )        
			{         
		 		v_IBMREQD = rs.getString(1);        
		 	}     
		 System.out.println(     
		 "  Successful retrieval of record.  Column 'IBMREQD' has a value of '" + v_IBMREQD + "'");    
		 
		 // Closing statement         
		 stmt.close();    
		 
		 // Disconnecting        
		 con.close();             
		 
		 System.out.println(      		 
		 "  Successful Disconnection from database '" + dbname + "'" );     
		 System.out.println("  End of Program");    
		 } catch (Exception e)        
		 	{        
		 	System.out.println("*** Error ***");        
		 	System.out.println( e.toString() );    
		 }  
	} // main
} // DBConn_Type1

Table 5. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using Sun’s JDBC Type 1 Driver

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity
Refer to Part 1 of this article series for detailed connectivity setup instructions.
Part II: Registering the database as an ODBC data source
Refer to Scenario 2 of this article for details.
Part III: Compiling the JDBC program that uses SUN’s Type 1 driver
javac DBConn_Type1.java Things to notice in the JDBC program: The URL must have the format: jdbc:odbc:<dbname> The type 1 driver included with Java 2 is specified by:sun.jdbc.odbc.JdbcOdbcDriver * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. The type 1 driver is supplied with the JDK that comes with DB2 UDB in sqllib\java\jdk Other demo programs can be found in sqllib\java\jdk\demo Make sure your environment variables are correctly set (see table 1 for an example)
Part IV: Executing the program
java DBConn_Type1 sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> ‘sample’ is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias.	sample = Database name db2admin = User id on the server mypsw = Password on the server

Figure 9. DBConn_Type1.java program Output

Scenario 5 – Connecting to a DB2 UDB database (any platform) using the IBM DB2 UDB type 2 driver (App Driver)

Figure 10. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using IBM DB2 UDB type 2 App driver

The sample JDBC program DBConn_Type2.java is shown in Listing 4.

Listing 4. DBConn_Type2.java

//***************************************************************************
// SOURCE FILE NAME: DBConn_Type2.java
//***************************************************************************
import java.sql.*;

class DBConn_Type2
{  
	public static void main(String argv[])  
	{   
	// Check arguments   
	if (argv.length < 3) {       
		System.out.println("Usage: java DBConn_Type2 <database name> <userid> <password>");
		return;    
		}    
		
		try    
		{     
		String dbname    = argv[0];     
		String userId    = argv[1];     
		String password  = argv[2];     
		String url       = "jdbc:db2:" + dbname;     
		String v_IBMREQD = null;     
		
		// Registering driver and connecting     
		Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();     
		Connection con = DriverManager.getConnection( url, userId, password );     
		
		System.out.println(      
		" Successful connection to '" + dbname + "' db using JDBC type 2 App driver" );
		… 
		// !!! Note: The code after the above line is exactly the same as in listing 3, so we have 
		//           removed it from this listing due to space limitations in the article !!!} 
		
		// DBConn_Type2

Table 6. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 JDBC Type 2 Driver (App Driver)

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity
Refer to Part 1 of this article series for detailed connectivity setup instructions.
Part II: Compiling the JDBC program that uses DB2 UDB Type 2 Driver (App Driver)
javac DBConn_Type2.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2:<dbname> The type 2 App driver is specified by:COM.ibm.db2.jdbc.app.DB2Driver * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. The DB2 type 2 App driver is stored in file db2java.zip under sqllib\java Make sure your environment variables are correctly set (see table 1 for an example)
Part III: Executing the program
java DBConn_Type2 sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> ‘sample’ is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias.	sample = Database name db2admin = User id on the server mypsw = Password on the server

Figure 11. DBConn_Type2.java program output

Scenario 6 – Connecting to a DB2 UDB database (any platform) using the IBM DB2 UDB type 2 driver (Universal Driver)

Figure 12. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using the type 2 driver (Universal Driver)

The sample JDBC program DBConn_Type2U.java is shown in Listing 5.

Listing 6. DBConn_Type2U.java

//***************************************************************************
// SOURCE FILE NAME: DBConn_Type2U.java
//***************************************************************************
import java.sql.*;

class DBConn_Type2U
{  
	public static void main(String argv[])  
	{   
	// Check arguments   
	if (argv.length < 3)	{       
		System.out.println("Usage: java DBConn_Type2U <database name> <userid> <password>");
		return;    
		}    
		try    
		{     
		String dbname    = argv[0];     
		String userId    = argv[1];     
		String password  = argv[2];     
		String url       = "jdbc:db2:" + dbname;     
		String v_IBMREQD = null;     
		
		// Registering driver and connecting     
		Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();     
		Connection con = DriverManager.getConnection( url, userId, password );     
		
		System.out.println(      
		"  Successful connection to '" + dbname + "' db with JDBC type 2 Universal driver");
		… 
		// !!! Note: The code after the above line is exactly the same as in listing 3, so we have 
		//           removed it from this listing due to space limitations in the article !!!
		
		} // DBConn_Type2U

Table 7. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 JDBC Type 2 Driver (Universal Driver)

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity
Refer to Part 1 of this article series for detailed connectivity setup instructions.
Part II: Compiling the JDBC program that uses DB2 Type 2 Universal Driver
javac DBConn_Type2.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2:<dbname> The type 2 Universal driver is specified by:com.ibm.db2.jcc.DB2Driver * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. The DB2 type 2 Universal driver is stored in file db2jcc.jar under sqllib\java Make sure your environment variables are correctly set (see table 1 for an example)
Part III: Executing the program
java DBConn_Type2U sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> ‘sample’ is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias.	sample = Database name db2admin = User id on the server mypsw = Password on the server

Figure 13. DBConn_Type2U.java program Output

As you can see in Figure 13, type 2 Universal Driver did not work correctly against an iSeries database. The problem has been reported to iSeries Defect support, and this article may be updated in the future.

Scenario 7 – Connecting to a DB2 UDB database using the IBM DB2 UDB type 3 driver (Net Driver)

The JDBC Type 3 driver is deprecated (still supported but it will not be enhanced in the future). The recommendation starting with DB2 UDB for Linux, UNIX and Windows V8 is to use the type 4 driver instead, which provides better performance. We will include this scenario in this article for completeness, but only for DB2 UDB for Linux, UNIX and Windows.

Figure 14. JDBC application client to DB2 UDB for Linux, UNIX and Windows server using the IBM DB2 UDB type 3 driver

The sample JDBC program DBConn_Type3.java is provided in Listing 6.

Listing 6. DBConn_Type3.java

//***************************************************************************
//SOURCE FILE NAME: DBConn_Type3.java
//***************************************************************************
import java.sql.*;

class DBConn_Type3
{  
	public static void main(String argv[])  
	{   
	// Check arguments   
	if (argv.length < 5) {       
		System.out.println("Usage: java DBConn_Type3 <database name> <userid> <password>");
		System.out.println("           <server hostname> <DB2's Java Applet Server port>");
		return;    
	}    
	
	try    
	{     
	String dbname      	= argv[0];     
	String userId      		= argv[1];     
	String password    	= argv[2];     
	String server     		= argv[3];     
	String portNumber  	= argv[4];      
	String url         		= "jdbc:db2://" + server + ":" + portNumber + "/" + dbname;     
	String v_IBMREQD   	= null;     
	
	// Registering driver and connecting     
	
	Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();     
	Connection con = DriverManager.getConnection( url, userId, password );     
	
	System.out.println(     
	 "  Successful connection to '" + dbname + "' db using JDBC type 3 driver" );

… 
// !!! Note: The code after the above line is exactly the same as in listing 3, so we have 
//           removed it from this listing due to space limitations in the article !!!

} // DBConn_Type3

Table 8. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 UDB JDBC Type 3 Driver

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Compiling the JDBC program that uses DB2 UDB JDBC Type 3 Driver
javac DBConn_Type3.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2://<server hostname or IP address>:<DB2 JDBC Applet Server port number> / <dbalias> The type 3 driver is specified by:COM.ibm.db2.jdbc.net.DB2Driver * Note: No DB2 UDB client needs to be installed at the client machine. The IBM DB2 UDB JDBC type 3 driver will be downloaded from the server to this client machine when you load your html page with an applet tag instructing the browser to download the driver. The DB2 UDB type 3 driver is stored in file db2java.zip under sqllib\java (the same file as for the type 2 App driver) Make sure your environment variables are correctly set (see table 1 for an example)	The DB2 UDB JDBC Applet Server is started with the command: `db2jstrt <port number>` When a port number is not provided, the default value of 6789 is used. Note that this is the port number you need to specify in the URL of the JDBC program, NOT the DB2 instance port number. Every time the db2jstrt command is executed, a db2jd process is created.
Part III: Executing the program
java DBConn_Type3 sample db2admin mypsw 9.23.190.24 3320 This sample program was designed to take 5 arguments in this order: <database alias> <userid> <password> <host name or IP address> <DB2 JDBC applet server port number> · Note that ‘sample’ is NOT the alias used for the database name specified in the catalog db command, but the actual name of the database as defined in the server. In fact, for this scenario, there is no need to perform any ‘catalog’ command.	sample = Database name db2admin = User id on the server mypsw = Password on the server 9.23.190.24 = IP Address of server 3320 = Port number of the DB2 JDBC applet server started with the command: db2jstrt 3320

Figure 15. DBConn_Type3.java program output

In Figure 15 we issued db2jstrt in the same machine as where the application executes because the client and server machines are the same. Otherwise, this command would only be required at the server machine.

Scenario 8 – Connecting to a DB2 UDB database (any platform) using the IBM DB2 UDB type 4 driver (Universal Driver)

Figure 16. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using the IBM DB2 UDB type 4 driver

The sample JDBC program DBConn_Type4.java is shown in Listing 7.

Listing 7. DBConn_Type4.java

//***************************************************************************
// SOURCE FILE NAME: DBConn_Type4.java
//***************************************************************************
import java.sql.*;

class DBConn_Type4
{  
	public static void main(String argv[])
	{  
	
	// Check arguments   
	if (argv.length < 5) {      
		System.out.println("Usage: java DBConn_Type4 <database name> <userid< <password>");
		System.out.println("           <server hostname> <DB2 Instance Port>");             
		return;    
		}    
		
		try    
		{     
		String dbname     	= argv[0];     
		String userId     		= argv[1];     
		String password   	= argv[2];     
		String server     		= argv[3];     
		String portNumber 	= argv[4];     
		String url        		= "jdbc:db2://" + server + ":" + portNumber + "/" + dbname;     
		String v_IBMREQD  	= null;     
		
		// Registering driver and connecting     
		Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();     
		Connection con = DriverManager.getConnection( url, userId, password );     
		
		System.out.println(     
		"Successful connection to '" + dbname + "' db using JDBC type 4 Universal driver" );
		
… 
// !!! Note: The code after the above line is exactly the same as in listing 3, so we have 
//           removed it from this listing due to space limitations in the article !!!

}// DBConn_Type4

Table 9. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 UDB JDBC Type 4 Universal Driver

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Compiling the JDBC program that uses DB2 UDB Type 4 Universal Driver
javac DBConn_Type4.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2://<server hostname or IP address>:<DB2 UDB Instance port number> / <dbalias> The type 4 Universal driver is specified by:com.ibm.db2.jcc.DB2Driver * Note: No DB2 client needs to be installed at this client machine; however, the IBM DB2 JDBC type 4 Universal driver (and the other JDBC and ODBC drivers) is provided with the DB2 UDB Clients. We recommend you download the Application Development Client if you are planning to develop JDBC applications on this machine; otherwise, if you only plan to execute JDBC applications, download the runtime client. If developing applets, the driver will be downloaded from the server to this client machine when you load your HTML page with an applet tag instructing the browser to download the driver. The DB2 type 4 Universal driver is stored in file db2jcc.jar under sqllib\java (This Universal Driver implements Type 2 and Type 4 drivers). Type 4 only works with the TCP/IP protocol. Make sure your environment variables are correctly set (see table 1 for an example)	Note that for the type 4 driver, the DB2 instance port number is provided rather than the DB2 JDBC Applet Server port number as in scenario 7. The DB2 UDB JDBC Applet Server does not need to be running.
Part III: Executing the program
java DBConn_Type4 sample db2admin mypsw 9.23.190.24 50000 This sample program was designed to take 5 arguments in this order: <database alias> <userid> <password> <host name or IP address> <DB2 Instance Port number> · Note that ‘sample’ is NOT the alias used for the database name specified in the catalog db command, but the actual name of the database as defined in the server. In fact, for this scenario, there is no need to perform any ‘catalog’ command. Special considerations when connecting to DB2 UDB for z/OS, OS/390 and iSeries: The DB2 Connect software is required because it comes with a required license file: db2jcc_license_cisuz.jar but not because of technical reasons. Make sure to use upper case when you specify the database name. The userid and password are not case sensitive. This is shown in the output of the program. - The DB2 UDB type 4 driver requires prerequisite stored procedures and views on the target DB2 UDB server. DB2 UDB for Linux, UNIX and Windows V8 has the required stored procedures and views. For DB2 on OS/390, the stored procedures must be installed manually.	sample = database name (for DB2 zSeries it would be the value of LOCATION NAME; for DB2 iSeries it would be the value of the local RDB name) db2admin = User id on the server mypsw = Password on the server 9.23.190.24 = IP Address of server 50000 = Port number of the DB2 UDB Instance at this server. (For DB2 zSeries and iSeries, it’s normally 446). Review Part I (scenarios 2 and 4) of this article series for more details about how to obtain the port number for DB2 UDB zSeries and iSeries.

Figure 17. DBConn_Type4.java program output

Scenario 9 – Connecting to a DB2 UDB database (any platform) using SQLJ

Figure 18. DB2 UDB for Linux, UNIX and Windows SQLJ application client to DB2 UDB (any platform)

The sample SQLJ program DBConn_Type3j.sqlj is provided is Listing 8.

Listing 8. DBConn_Type3j.sqlj

//*************************************************************************************************
// SOURCE FILE NAME: DbConn_Type3j.sqlj
//*************************************************************************************************

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

class DBConn_Type3j

{  
	public static void main(String argv[])  
	{   
	// Check arguments   
	
	if (argv.length < 5) {       
		System.out.println("Usage: java DBConn_Type3j <database name> <userid> <password> <server hostname> 
		<DB2 Java Applet Server Port number>");              
		return;    
	}    
	try    
	{     
	String dbname     	= argv[0];     
	String userId     		= argv[1];     
	String password   	= argv[2];     
	String server     		= argv[3];     
	String portNumber 	= argv[4];     
	String url       		= "jdbc:db2://" + server + ":" + portNumber + "/" + dbname;     
	String h_ibmreqd 	= null;     
	
	// Registering the Type 3 driver and connecting     
	Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
	DriverManager.getConnection( url, userId, password );     
	DefaultContext ctx = new DefaultContext(DriverManager.getConnection(url, userId,
	             password) );     
	DefaultContext.setDefaultContext(ctx);     
	
	System.out.println(     
	"  Successful connection to '" + dbname + "' db using SQLJ with JDBC type 3 Net driver" );     
	
	// Retrieving a record from a table     
	// There is only one record in SYSIBM.SYSDUMMY1.  Note that if you are not     
	// a DB2 instance owner, you may have to issue this command before running this program:      
	// db2 GRANT select on sysibm.sysdummy1 to public     
	
	#sql [ctx] {select IBMREQD INTO :h_ibmreqd from sysibm.sysdummy1};     
	System.out.println(     
	"  Successful retrieval of record.  Column 'IBMREQD' has a value of '" + h_ibmreqd + "'");     
	
	// Closing context, disconnecting     
	if( ctx != null )      
	{        
		ctx.close();      
	}     
	
	System.out.println(      
	"  Successful Disconnection from database '" + dbname + "'" );      
	
	System.out.println("  End of Program");    
	
	} catch (Exception e)        
		{        
		System.out.println("*** Error ***");        
		System.out.println( e.toString() );       
		}  
		
	} // main
} // DBConn_Type3j

Table 10. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using SQLJ

Machine 1 ('myblue') DB2 UDB for Linux, UNIX and Wihndows	Machine 2 ('aries') DB2 UDB server (any platform)
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring Connectivity
Refer to scenarios 4, 5, 6, 7 and 8 depending on the type of JDBC driver you are planning to use.
Part II: Compiling and customizing the SQLJ program
For this example the sample program to compile uses the IBM DB2 UDB JDBC Type 3 driver. To compile, issue: sqlj DBConn_Type3j.sqlj. To profile customize the program, we use the db2sqljcustomize command (in previous versions of DB2, the command was db2profc). This command is also a Java program using type 2 or type 4 universal JDBC drivers. Based on the way you invoke db2sqljcustomize, the customization will be done by connecting to the database using either of these JDBC driver types. Profile customizer using Type 2 Universal Driver:db2sqljcustomize -url jdbc:db2:sample user db2admin -password mypsw DBConn_Type3j_SJProfile0 OR Profile customizer using Type 4 Universal Driver (since server and port parameters are provided): db2sqljcustomize -url jdbc:db2://9.23.190.24:50000/sample user db2admin -password mypsw DBConn_Type3j_SJProfile0 Make sure your environment variables are correctly set (see table 1 for an example).
Part III: Executing the program
java DBConn_Type3j sample db2admin sample db2admin mypsw 9.23.190.24 5678 This sample program was designed to take 5 arguments in this order: <database alias> <userid> <password> <host name or IP address> <DB2 JDBC applet server port number> · Note that ‘sample’ is NOT the alias used for the database name specified in the catalog db command, but the actual name of the database as defined in the server. In fact, for this scenario, there is no need to perform any ‘catalog’ command.	The DB2 UDB JDBC Applet Server is started with the command: `db2jstrt <port number>` When a port number is not provided, the default value of 6789 is used. Note that this is the port number you need to specify in the URL of the JDBC program, NOT the DB2 instance port number. Every time the db2jstrt command is executed, a db2jd process is created. sample = Database name db2admin = User id on the server mypsw = Password on the server 9.23.190.24 = IP Address of server 5678 = Port number of DB2 Java Applet Server

Figure 19. DBConn_Type3j.sqlj program Output

In Figure 19 we issued db2jstrt in the same machine as where the application executes because the client and server machines are the same. Otherwise, this command would only be required at the server machine.

Due to space constraints in this article we are not showing other sample programs using SQLJ and type 1, type 2 (App Driver), type 2 (Universal Driver) or type 4 drivers; however, we did test those programs, and they are provided in the accompanying file to this article. Tests were also performed using all of these programs against a DB2 UDB for zSeries and DB2 UDB for iSeries server. Table 11 below summarizes the results obtained. The file SQLJResults.pdf also provided, includes print screens of all the cases shown in this table.

To save space, we will use the following naming conventions for Table 11:
DB2 LUW = DB2 UDB for Linux, UNIX and Windows
DB2 z/OS = DB2 UDB for zSeries
DB2 iSeries = DB2 UDB for iSeries

Table 11. SQLJ sample program test results

Test #	Profile Customize batch file used	SQLJ Sample Program Name (Name tells which JDBC driver is being used)	Database Platform	Results
1	bldsqljType2U.bat	-DBConn_Type1j.sqlj	-DB2 LUW	-OK
2	bldsqljType2U.bat	DBConn_Type1j.sqlj	DB2 z/OS	OK
3	bldsqljType2U.bat	DBConn_Type1j.sqlj	DB2 iSeries	During Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support
4	bldsqljType2U.bat	DBConn_Type2j.sqlj	DB2 LUW	OK
5	bldsqljType2U.bat	DBConn_Type2j.sqlj	DB2 LUW	During execution:Successful connection but error retrieving record:SQL0804N The application program parameters for the current request are not valid. Reason code "". Problem has been reported to DB2 UDB Defect Support
6	bldsqljType2U.bat	DBConn_Type2j.sqlj	DB2 UDB for iSeries	During Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support
7	bldsqljType2U.bat	DBConn_Type2j.sqlj	DB2 LUW	OK
8	bldsqljType2U.bat	DBConn_Type2j.sqlj	DB2 z/OS	OK
9	bldsqljType2U.bat	DBConn_Type2j.sqlj	DB2 iSeries	During Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support
10	bldsqljType2U.bat	DBConn_Type3j.sqlj	DB2 LUW	OK (Same as scenario 9)
11	bldsqljType2U.bat	DBConn_Type3j.sqlj	DB2 z/OS	Type 3 not supported
12	bldsqljType2U.bat	DBConn_Type3j.sqlj	DB2 iSeries	Type 3 not supported
13	bldsqljType2U.bat	DBConn_Type4j.sqlj	DB2 LUW	OK
14	bldsqljType2U.bat	DBConn_Type4j.sqlj	DB2 z/OS	OK
15	bldsqljType2U.bat	DBConn_Type4j.sqlj	DB2 iSeries	During Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support
16	bldsqljType4.bat	DBConn_Type1j.sqlj	DB2 LUW	OK
17	bldsqljType4.bat	DBConn_Type1j.sqlj	DB2 z/OS	OK
18	bldsqljType4.bat	DBConn_Type1j.sqlj	DB2 iSeries	OK
19	bldsqljType4.bat	DBConn_Type2j.sqlj	DB2 LUW	OK
20	bldsqljType4.bat	DBConn_Type2j.sqlj	DB2 z/OS	During execution:Successful connection but error retrieving record: SQL0607N "SYSIBM .SYSDUMMY1 " is not defined for system objects. Problem has been reported to DB2 UDB Defect Support
21	bldsqljType4.bat	DBConn_Type2j.sqlj	DB2 iSeries	During execution:Successful connection but error retrieving record: SQL0804N The application program parameters for the current request are not valid. Reason code "". Problem has been reported to DB2 UDB Defect Support
22	bldsqljType4.bat	DBConn_Type2Uj.sqlj	DB2 LUW	OK
23	bldsqljType4.bat	DBConn_Type2Uj.sqlj	DB2 z/OS	OK
24	bldsqljType4.bat	DBConn_Type2Uj.sqlj	DB2 iSeries	OK
25	bldsqljType4.bat	DBConn_Type3j.sqlj	DB2 LUW	OK
26	bldsqljType4.bat	DBConn_Type3j.sqlj	DB2 z/OS	Type 3 not supported
27	bldsqljType4.bat	DBConn_Type3j.sqlj	DB2 iSeries	Type 3 not supported
28	bldsqljType4.bat	DBConn_Type4j.sqlj	DB2 LUW	OK
29	bldsqljType4.bat	DBConn_Type4j.sqlj	DB2 z/OS	OK
30	bldsqljType4.bat	DBConn_Type4j.sqlj	DB2 iSeries	OK

Table 12. Solutions to common errors

	Error	Applicable to	Solution
1	java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified	JDBC Type 1 (In JDBC or SQLJ)	Since the Type 1 JDBC driver is based on the ODBC driver, make sure the ODBC data source is cataloged. See scenario 2 of this article for details. Make sure you specify the correct name for your database. Test if the database has been catalogued correctly by connecting from the CLP: db2 connect to <dbname> user <userid> using <psw>
2	SQL30082N Attempt to establish connection failed with security reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001	All	Make sure you specify the correct user id and password. If using ODBC, CLI, JDBC Type 1, 2, 2U, then test if the database has been catalogued correctly by connecting from the CLP: db2 connect to <dbname> user <userid> using <psw>
3	SQL1013N The database alias name or database name "<dbname>" could not be found. SQLSTATE=42705	All	Make sure you specify the correct name for your database. If using ODBC, CLI, JDBC Type 1, 2, 2U, then test if the database has been catalogued correctly by connecting from the CLP: db2 connect to <dbname> user <userid> using <psw> If using JDBC type 3 or type 4, make sure you are not using the database names as indicated in the catalog commands at the client machine (system db directory) but the actual names in the database server machine. If using type 4 and connecting against DB2 for zSeries or DB2 for iSeries, make sure the database name is in upper case.
4	COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0616E Error opening socket. SQLSTATE=08S01	JDBC type 3	You may need to start the DB2 JDBC Applet Server using db2jstrt <port number> You may have specified a wrong port number, or an incorrect hostname/IP Address
5	COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0106E Connection is closed.	JDBC type 3	Your userid/password may be incorrect. The dbname may be incorrect
6	[IBM][JDBC Driver] CLI0615E Error receiving from socket, server is not responding	JDBC type 3	DB2 for zSeries does not support type 3 driver programs. If you are attempting to connect to such database server, you may get this error.
7	[ibm][db2][jcc][sqlj][Throwable@2470b8] Message = Serialized Profile <name> not found	SQLJ using any driver type	A serialized profile will only be created if there are SQL statements in the program other than the connection statements. Make sure your program has any embedded SQL statements.
8	[ibm][db2][jcc][sqlj] SQL Error - SQLCODE : -551, SQLSTATE : 42501	SQLJ using any driver type	Make sure the user id has the right privileges in the target database server using the GRANT statement.
9	COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0805N Package "NEW_JERSEY.NULLID.DBCONN02.4E42576D56444B70" was not found. SQLSTATE=51002	SQLJ using any driver type	DBCONN01, DBCONN02, DBCONN03, and DBCONN04 are packages bound during the profile customization for an SQLJ program. You may have attempted to execute the program without correctly compiling it or running the profile customization program db2sqljcustomize (included in the batch files bldsqljType2U.bat and bldsqljType4.bat).
10	Review the errors per part 1 of this article series	ODBC, CLI, JDBC Type 1, 2, 2U Drivers

Resources

About the authors

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=13448

ArticleTitle=Connectivity cheat sheet for Application Developers (Part 4)

publish-date=01152004

author1-email=rfchong@ca.ibm.com

author1-email-cc=

author2-email=

author2-email-cc=