Java Type 4 Coonection in V8.x

[Rhino]

I'm trying to use the JDBC Universal driver to get a Type 4 connection in a
Java client program running under DB2 V8.2.1 but I keep getting a
"connection refused: connect" message.

I'm running DB2 Personal Edition so I am trying to connect to the database
SQLPROCS on this same machine. I've checked the manual and am imitating the
examples as well as I can but the examples are geared to remote machines,
not the local one, so I strongly suspect that the syntax is somehow
different for local machines. Anyway, here is the relevant code:
/**
* Load the JDBC driver.
*/
public void loadDriver() {

String METHOD_NAME = "loadDriver()";

String JDBC_DRIVER_NAME = "com.ibm.db2.jcc.DB2Driver"; //new Universal
JDBC driver

/* Load the JDBC driver. */
try {
Class.forName(JDBC_DRIVER_NAME).newInstance();
}
catch (Exception excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error " +
"while attempting to load JDBC driver, " + JDBC_DRIVER_NAME + ".");
excp.printStackTrace();
System.exit(16);
}
}

/**
* Get a connection to the database and set Autocommit.
*/
public void connectToDatabase() {

String METHOD_NAME = "connectToDatabase()";

String DB2_URL = "jdbc:db2:sqlprocs"; //old and new Type 2 URL - works
// String DB2_URL =
"jdbc:db2://localhost/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work
// String DB2_URL =
"jdbc:db2://127.0.0.1/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work

boolean AUTOCOMMIT = false;

/* Connect to the database. */
try {
conn01 = DriverManager.getConnection(DB2_URL);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"connecting to URL, " + DB2_URL + ".");
sql_excp.printStackTrace();
System.exit(16);
}

/* Set autocommit. */
try {
conn01.setAutoCommit(AUTOCOMMIT);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"setting autocommit to " + AUTOCOMMIT + ".");
sql_excp.printStackTrace();
System.exit(16);
}
}
Only the first version of the DB2_URL variable works; the other two don't.
That means that I can get a Type 2 connection but not a Type 4 connection.
What am I doing wrong?

By the way, I can successfully ping both localhost and 127.0.0.1 from my
Windows command prompt so I don't seem to be missing and etc/ entries.
--
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare

[Jan M. Nelken]

Rhino wrote:

I'm trying to use the JDBC Universal driver to get a Type 4 connection in a
Java client program running under DB2 V8.2.1 but I keep getting a
"connection refused: connect" message.

I'm running DB2 Personal Edition so I am trying to connect to the database
SQLPROCS on this same machine. I've checked the manual and am imitating the
examples as well as I can but the examples are geared to remote machines,
not the local one, so I strongly suspect that the syntax is somehow
different for local machines. Anyway, here is the relevant code:
/**
* Load the JDBC driver.
*/
public void loadDriver() {

String METHOD_NAME = "loadDriver()";

String JDBC_DRIVER_NAME = "com.ibm.db2.jcc.DB2Driver"; //new Universal
JDBC driver

/* Load the JDBC driver. */
try {
Class.forName(JDBC_DRIVER_NAME).newInstance();
}
catch (Exception excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error " +
"while attempting to load JDBC driver, " + JDBC_DRIVER_NAME + ".");
excp.printStackTrace();
System.exit(16);
}
}

/**
* Get a connection to the database and set Autocommit.
*/
public void connectToDatabase() {

String METHOD_NAME = "connectToDatabase()";

String DB2_URL = "jdbc:db2:sqlprocs"; //old and new Type 2 URL - works
// String DB2_URL =
"jdbc:db2://localhost/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work
// String DB2_URL =
"jdbc:db2://127.0.0.1/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work

boolean AUTOCOMMIT = false;

/* Connect to the database. */
try {
conn01 = DriverManager.getConnection(DB2_URL);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"connecting to URL, " + DB2_URL + ".");
sql_excp.printStackTrace();
System.exit(16);
}

/* Set autocommit. */
try {
conn01.setAutoCommit(AUTOCOMMIT);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"setting autocommit to " + AUTOCOMMIT + ".");
sql_excp.printStackTrace();
System.exit(16);
}
}
Only the first version of the DB2_URL variable works; the other two don't.
That means that I can get a Type 2 connection but not a Type 4 connection.
What am I doing wrong?

By the way, I can successfully ping both localhost and 127.0.0.1 from my
Windows command prompt so I don't seem to be missing and etc/ entries.

Try this (watch for wrapped lines):

import java.lang.*;
import java.sql.*;

public class MyJccTest
{
public static void main (String args[]) throws Exception
{
if (args.length !=3)
{
System.out.println("required: <databasename> <userid> <password>");
System.exit(0);
}

String url = "jdbc:db2://localhost:50000/" + args[0];
System.out.println(" Connect to '" + args[0]+ "' database using JCC driver
and type 4 connectivity." );
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection con = DriverManager.getConnection( url, args[1], args[2]);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select '1',user,current sqlid from sy
sibm.sysdummy1");
while (rs.next())
System.out.println(rs.getString(1) + ' ' + rs.getString(2) + ' ' +
rs.getString(3));
stmt.close();
rs.close();
con.close();
}
}

Jan M. Nelken

[Phil Sherman]

Type 4 connections will fail if the db2jd daemon is not active. It is
started by issuing the "db2jstrt" command. You can specify a tcpip port
(db2jstrt port_num) or take the default, 6789. The default can be
changed by setting the DB2JD_PORT_NUMBER registry variable.

Please note that this (db2jstrt) command is not documented in the
command reference (8.2), and I couldn't find it in my master index. The
online information center doesn't have a reference to it either.
Referential documentation can be found in the communications registry
variables. Execute the command with a "-?" parameter and it will tell
you that you can specify the port number.

Phil Sherman

Rhino wrote:

I'm trying to use the JDBC Universal driver to get a Type 4 connection in a
Java client program running under DB2 V8.2.1 but I keep getting a
"connection refused: connect" message.

I'm running DB2 Personal Edition so I am trying to connect to the database
SQLPROCS on this same machine. I've checked the manual and am imitating the
examples as well as I can but the examples are geared to remote machines,
not the local one, so I strongly suspect that the syntax is somehow
different for local machines. Anyway, here is the relevant code:
/**
* Load the JDBC driver.
*/
public void loadDriver() {

String METHOD_NAME = "loadDriver()";

String JDBC_DRIVER_NAME = "com.ibm.db2.jcc.DB2Driver"; //new Universal
JDBC driver

/* Load the JDBC driver. */
try {
Class.forName(JDBC_DRIVER_NAME).newInstance();
}
catch (Exception excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error " +
"while attempting to load JDBC driver, " + JDBC_DRIVER_NAME + ".");
excp.printStackTrace();
System.exit(16);
}
}

/**
* Get a connection to the database and set Autocommit.
*/
public void connectToDatabase() {

String METHOD_NAME = "connectToDatabase()";

String DB2_URL = "jdbc:db2:sqlprocs"; //old and new Type 2 URL - works
// String DB2_URL =
"jdbc:db2://localhost/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work
// String DB2_URL =
"jdbc:db2://127.0.0.1/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work

boolean AUTOCOMMIT = false;

/* Connect to the database. */
try {
conn01 = DriverManager.getConnection(DB2_URL);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"connecting to URL, " + DB2_URL + ".");
sql_excp.printStackTrace();
System.exit(16);
}

/* Set autocommit. */
try {
conn01.setAutoCommit(AUTOCOMMIT);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"setting autocommit to " + AUTOCOMMIT + ".");
sql_excp.printStackTrace();
System.exit(16);
}
}
Only the first version of the DB2_URL variable works; the other two don't.
That means that I can get a Type 2 connection but not a Type 4 connection.
What am I doing wrong?

By the way, I can successfully ping both localhost and 127.0.0.1 from my
Windows command prompt so I don't seem to be missing and etc/ entries.

[Jan M. Nelken]

Phil Sherman wrote:

Type 4 connections will fail if the db2jd daemon is not active. It is
started by issuing the "db2jstrt" command. You can specify a tcpip port
(db2jstrt port_num) or take the default, 6789. The default can be
changed by setting the DB2JD_PORT_NUMBER registry variable.

Please note that this (db2jstrt) command is not documented in the
command reference (8.2), and I couldn't find it in my master index. The
online information center doesn't have a reference to it either.
Referential documentation can be found in the communications registry
variables. Execute the command with a "-?" parameter and it will tell
you that you can specify the port number.

1. db2jd applet daemon is not required for JCC Type 4 connections.

2. db2jstrt command is documented in Infoemation center (available online at:
http://publib.boulder.ibm.com/infoce...elp/index.jsp). Just search for
db2jstrt string; you will find that it says:

Connecting with the Type 3 ("net") Driver

To connect with the type 3 driver, first modify the Applt.html file according to
the instructions in the file. Then, start the JDBC applet server on the TCP/IP
port specified in Applt.html. For example, if in Applt.html, you specified param
name=port value='6789', then you would enter:

db2jstrt 6789

Make sure the JDBC port number in the connection string is the recommended
default, "6789". Only change this if you are sure the number does not conflict
with another port number. Do not use the database port number, "50000".
I recommend again to try my test code.

Jan M. Nelken

[Phil Sherman]

Thanks for the correction about the difference between type3 and 4
connections.

I still say that this isn't the best place to provide this
documentation. It also implies that you match the port on the server to
what you specify in the applet. I believe that most shops would prefer
to designate the port on the server and have the applets "march to the
server's tune".

Phil Sherman

Jan M. Nelken wrote:

1. db2jd applet daemon is not required for JCC Type 4 connections.

2. db2jstrt command is documented in Infoemation center (available
online at: http://publib.boulder.ibm.com/infoce...elp/index.jsp).
Just search for db2jstrt string; you will find that it says:

Connecting with the Type 3 ("net") Driver

To connect with the type 3 driver, first modify the Applt.html file
according to the instructions in the file. Then, start the JDBC applet
server on the TCP/IP port specified in Applt.html. For example, if in
Applt.html, you specified param name=port value='6789', then you would
enter:

db2jstrt 6789

Make sure the JDBC port number in the connection string is the
recommended default, "6789". Only change this if you are sure the number
does not conflict with another port number. Do not use the database port
number, "50000".
I recommend again to try my test code.

Jan M. Nelken

[Jan M. Nelken]

Phil Sherman wrote:

Thanks for the correction about the difference between type3 and 4
connections.

I still say that this isn't the best place to provide this
documentation. It also implies that you match the port on the server to
what you specify in the applet. I believe that most shops would prefer
to designate the port on the server and have the applets "march to the
server's tune".

Please also see:
"DB2 JDBC Type 3 Driver for Linux, UNIX and Windows (deprecated as of DB2 V8.1)

The DB2 JDBC type 3 driver, also known as the applet or net driver, consists of
a JDBC client and a JDBC server. The DB2 JDBC applet driver can be loaded by a
Web browser along with the applet, or the applet driver can be used in
standalone Java applications. When the applet requests a connection to a DB2
database server, the applet driver opens a TCP/IP socket to the DB2 JDBC applet
server on the machine where the Web server is running. After a connection is set
up, the applet driver sends each of the subsequent database access requests from
the applet to the JDBC server through the TCP/IP connection. The JDBC server
then makes corresponding DB2 calls to perform the task. On completion, the JDBC
server sends the results back to the JDBC client through the connection. The
JDBC server process is db2jd.

The DB2 JDBC Type 3 Driver for Linux, UNIX and Windows will not be supported in
future releases of DB2. You should therefore consider moving to the DB2
Universal JDBC Driver."
Keeping in mind that JDBC type 3 driver will not be supported in future releases
- please also note that port db2jds will be listening on can be changed via
registry variable DB2JD_PORT_NUMBER.

Jan M. Nelken

[Rhino]

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:Pc********************@rogers.com...

Phil Sherman wrote:

Thanks for the correction about the difference between type3 and 4
connections.

I still say that this isn't the best place to provide this
documentation. It also implies that you match the port on the server to
what you specify in the applet. I believe that most shops would prefer
to designate the port on the server and have the applets "march to the
server's tune".
Please also see:
"DB2 JDBC Type 3 Driver for Linux, UNIX and Windows (deprecated as of DB2

V8.1)
The DB2 JDBC type 3 driver, also known as the applet or net driver, consists of a JDBC client and a JDBC server. The DB2 JDBC applet driver can be loaded by a Web browser along with the applet, or the applet driver can be used in
standalone Java applications. When the applet requests a connection to a DB2 database server, the applet driver opens a TCP/IP socket to the DB2 JDBC applet server on the machine where the Web server is running. After a connection is set up, the applet driver sends each of the subsequent database access requests from the applet to the JDBC server through the TCP/IP connection. The JDBC server then makes corresponding DB2 calls to perform the task. On completion, the JDBC server sends the results back to the JDBC client through the connection. The JDBC server process is db2jd.

The DB2 JDBC Type 3 Driver for Linux, UNIX and Windows will not be supported in future releases of DB2. You should therefore consider moving to the DB2
Universal JDBC Driver."
Keeping in mind that JDBC type 3 driver will not be supported in future releases - please also note that port db2jds will be listening on can be changed via registry variable DB2JD_PORT_NUMBER.

Jan M. Nelken

Thank you both for your help, Jan and Phil. Your code works perfectly, Jan.
Mine works perfectly too now; all I needed to do was add the port number to
the URL that I was using. In other words, I changed:

static final String DB2_URL =
"jdbc:db2://127.0.0.1/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work
to

static final String DB2_URL =
"jdbc:db2://127.0.0.1:50000/sqlprocs:user=rhino;password=rhino;"; //new Type
4 URL - works
Sorry, I probably should have caught this myself but, for some reason, I was
sure that the port would default to 50000 so I left it out. Obviously, it
DOESN'T default to 50000 as I thought.

Thanks again!

Rhino

[aj]

Hi Jan:

Notice how in this sample DB2 JDBC java source, and all others,
that the password is hardcoded in the source?

Is there any way around this w/ the DB2 v8 type 4 driver?

My shop has had some security audits recently, and this came up.
I run Java DB2 JDBC programs in batch mode in a cron rather than
interactively. How can I avoid a hardcoded password? Is there
a standard way to do this?

PS - I'm a former Informix DBA, and the Informix type 4 jdbc driver
was sensitive to the contents of the unix hosts.equiv file on the
server. With the correct entries in hosts.equiv, it was not
necessary to specify a password - this is how we previously got
around this. But the DB2 JDBC driver does not act this way.

Any help appreciated.

aj
Jan M. Nelken wrote:

Rhino wrote:

I'm trying to use the JDBC Universal driver to get a Type 4 connection
in a
Java client program running under DB2 V8.2.1 but I keep getting a
"connection refused: connect" message.

I'm running DB2 Personal Edition so I am trying to connect to the
database
SQLPROCS on this same machine. I've checked the manual and am
imitating the
examples as well as I can but the examples are geared to remote machines,
not the local one, so I strongly suspect that the syntax is somehow
different for local machines. Anyway, here is the relevant code:
/**
* Load the JDBC driver.
*/
public void loadDriver() {

String METHOD_NAME = "loadDriver()";

String JDBC_DRIVER_NAME = "com.ibm.db2.jcc.DB2Driver"; //new
Universal
JDBC driver

/* Load the JDBC driver. */
try {
Class.forName(JDBC_DRIVER_NAME).newInstance();
}
catch (Exception excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error " +
"while attempting to load JDBC driver, " + JDBC_DRIVER_NAME +
".");
excp.printStackTrace();
System.exit(16);
}
}

/**
* Get a connection to the database and set Autocommit.
*/
public void connectToDatabase() {

String METHOD_NAME = "connectToDatabase()";

String DB2_URL = "jdbc:db2:sqlprocs"; //old and new Type 2 URL -
works
// String DB2_URL =
"jdbc:db2://localhost/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work
// String DB2_URL =
"jdbc:db2://127.0.0.1/sqlprocs:user=rhino;password=rhino;"; //new Type 4
URL - doesn't work

boolean AUTOCOMMIT = false;

/* Connect to the database. */
try {
conn01 = DriverManager.getConnection(DB2_URL);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"connecting to URL, " + DB2_URL + ".");
sql_excp.printStackTrace();
System.exit(16);
}

/* Set autocommit. */
try {
conn01.setAutoCommit(AUTOCOMMIT);
}
catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " - Encountered
error while " +
"setting autocommit to " + AUTOCOMMIT + ".");
sql_excp.printStackTrace();
System.exit(16);
}
}
Only the first version of the DB2_URL variable works; the other two
don't.
That means that I can get a Type 2 connection but not a Type 4
connection.
What am I doing wrong?

By the way, I can successfully ping both localhost and 127.0.0.1 from my
Windows command prompt so I don't seem to be missing and etc/ entries.

Try this (watch for wrapped lines):

import java.lang.*;
import java.sql.*;

public class MyJccTest
{
public static void main (String args[]) throws Exception
{
if (args.length !=3)
{
System.out.println("required: <databasename> <userid> <password>");
System.exit(0);
}

String url = "jdbc:db2://localhost:50000/" + args[0];
System.out.println(" Connect to '" + args[0]+ "' database using JCC
driver and type 4 connectivity." );
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection con = DriverManager.getConnection( url, args[1], args[2]);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select '1',user,current sqlid from sy
sibm.sysdummy1");
while (rs.next())
System.out.println(rs.getString(1) + ' ' + rs.getString(2) + ' ' +
rs.getString(3));
stmt.close();
rs.close();
con.close();
}
}

Jan M. Nelken

[Jan M. Nelken]

aj wrote:

Hi Jan:

Notice how in this sample DB2 JDBC java source, and all others,
that the password is hardcoded in the source?

Is there any way around this w/ the DB2 v8 type 4 driver?

Notice that in my yesy program - userid and password was not hardcoded at source
but supplied as argument:

Connection con = DriverManager.getConnection( url, args[1], args[2]);

which of course does not help you if you are talking about script submitted via
cron.

V8.2 adds plugin authentication - so you can code almost everything in your
security plugin in order to authenticate.

Please also note that local connection to database does not require password -
it assumes userid of already logged user. You may want to experiment with rcmd
and/or rlogin to achieve that.

Jan M. Nelken