|
|
|
|
|
|
| | | |
Testing JDBC Connections and Troubleshooting
The following sections describe how to test JDBC connections and provide troubleshooting tips:
The following sections describe how to test connections.
Validating a DBMS Connection from the Command Line
BEA provides utilities that you can use to test two-tier and three-tier JDBC database connections after you install WebLogic two-tier drivers, WebLogic Server, or WebLogic JDBC.
How to Test a Two-Tier Connection from the Command Line
To use the utils.dbping utility, you must complete the installation of your JDBC driver. Make sure you have completed the following:
Use the utils.dbping utility to confirm that you can make a connection between Java and your database. The dbping utility is only for testing a two-tier connection, using a WebLogic two-tier JDBC driver like WebLogic jDriver for Oracle.
Syntax
$ java utils.dbping DBMS user password DB
Arguments
DBMS
Use: ORACLE, MSSQLSERVER4, or INFORMIX4
user
Valid username for database login. Use the same values and format that you use with isql for SQL Server, sqlplus for Oracle, or DBACCESS for Informix.
password
Valid password for the user. Use the same values and format that you use with isql, sqlplus, or DBACCESS.
DB
Name of the database. The format varies depending on the database and version. Use the same values and format that you use with isql, sqlplus, or DBACCESS. Type 4 drivers, such as MSSQLServer4 and Informix4, need additional information to locate the server since they cannot access the environment.
Examples
Oracle
Connect to Oracle from Java with WebLogic jDriver for Oracle using the same values that you use with sqlplus.
If you are not using SQLNet (and you have ORACLE_HOME and ORACLE_SID defined), follow this example:
$ java utils.dbping ORACLE scott tiger
If you are using SQLNet V2, follow this example:
$ java utils.dbping ORACLE scott tiger TNS_alias
where TNS_alias is an alias defined in your local tnsnames.ora file.
Microsoft SQL Server (Type 4 driver)
To connect to Microsoft SQL Server from Java with WebLogic jDriver for Microsoft SQL Server, you use the same values for user and password that you use with isql. To specify the SQL Server, however, you supply the name of the computer running the SQL Server and the TCP/IP port the SQL Server is listening on. To log into a SQL Server running on a computer named mars listening on port 1433, enter:
$ java utils.dbping MSSQLSERVER4 sa secret mars:1433
You could omit ":1433" in this example since 1433 is the default port number for Microsoft SQL Server. By default, a Microsoft SQL Server may not be listening for TCP/IP connections. Your DBA can configure it to do so.
Informix (Type 4 driver)
Connect to Informix from Java with WebLogic jDriver for Informix using the same values that you use with DBACCESS. The order of arguments follows the pattern:
$ java utils.dbping INFORMIX user pass db@server:port
As shown in this example:
$ java utils.dbping INFORMIX bill secret stores@myserver:8543
How to Validate a Multitier WebLogic JDBC Connection from the Command Line
Use the utils.t3dbping utility to confirm that you can make a multitier database connection using a WebLogic Server. The t3dbping utility is only for testing a multitier connection, after you have verified that you have a working two-tier connection, and after you have started WebLogic.
If the two-tier JDBC driver is a WebLogic jDriver, you should test the two-tier connection with utils.dbping. Otherwise, see the documentation for the two-tier JDBC driver to find out how to test that connection before you test the multitier connection.
Syntax
$ java utils.t3dbping URL user password DB driver_class driver_URL
Arguments
URL
URL of the WebLogic Server.
username
Valid username for the DBMS.
password
Valid password for that user.
DB
Name of the database. Use the same values and format that are shown above for testing a two-tier connection.
driver_class
Class name of the JDBC driver between WebLogic and the DBMS. For instance, if you are using WebLogic jDriver for Oracle on the server side, the driver class name is weblogic.jdbc.oci.Driver. Note that the class name of the driver is in dot-notation format.
driver_URL
URL of the JDBC driver between WebLogic and the DBMS. For instance, if you are using WebLogic jDriver for Oracle on the server side, the URL of the driver is jdbc:weblogic:oracle. Note that the URL of the driver is colon-separated.
Examples
These examples are displayed on multiple lines for readability. Each example should be entered as a single command.
Oracle
Here is an example of how to ping the Oracle DBMS DEMO20 running on the server bigbox, on the same host as WebLogic, which is listening on port 7001:
$ java utils.t3dbping // command
t3://bigbox:7001 // WebLogic URL
scott tiger // user password
DEMO20 // DB
weblogic.jdbc.oci.Driver // driver class
jdbc:weblogic:oracle // driver URL
DB2 with AS/400 Type 4 JDBC driver
This example shows how to ping an AS/400 DB2 database from a workstation command shell using the IBM AS/400 Type 4 JDBC driver:
$ java utils.t3dbping // command
t3://as400box:7001 // WebLogic URL
scott tiger // user password
DEMO // database
com.ibm.as400.access.AS400JDBCDriver // driver class
jdbc:as400://as400box // driver URL
WebLogic jDriver for Microsoft SQL Server (Type 4 JDBC driver)
This example shows how to ping a Microsoft SQL Server database using WebLogic jDriver for Microsoft SQL Server:
$ java utils.t3dbping // command
t3://localhost:7001 // WebLogic URL
sa // user name
abcd // password
hostname // database@hostname:port
//(optional if specified
// as part of the URL)
weblogic.jdbc.mssqlserver4.Driver // driver class
jdbc:weblogic:mssqlserver4:pubs@localhost:1433
// driver URL:database@hostname:port
//(optional if used in the database parameter)
The following sections provide troubleshooting tips.
Troubleshooting JDBC Connections
If you are testing a connection to WebLogic, check the WebLogic log. By default, the log is kept in a file called weblogic.log in the weblogic/myserver directory.
UNIX Users
If you encounter a problem trying to load native_login, use truss to determine the source of the problem. For example, to run tutorial.example3, enter:
$ truss -f -t open -s\!all java tutorial.example3
WinNT
If you get an error message that indicates that the .dll failed to load, make sure your PATH includes the 32-bit database-related .dlls.
SEGVs with JDBC and Oracle Databases
Several conditions can cause segmentation violation errors (SEGVs) or hangs when you use JDBC and an Oracle database.
A common cause of out-of-memory errors is failing to close ResultSets. The error message is usually similar to the following:
Run-time exception error; current exception: xalloc
No handler for exception
When using array fetches, the native layer allocates memory in C, not in Java, so Java garbage collection does not immediately clean up the memory. The only way to release the memory is to close the ResultSet. (You can minimize this memory usage for better performance.)
To avoid out-of-memory errors, make sure that your program logic closes all ResultSets in all cases. To test whether failing to close ResultSets is causing the out-of-memory errors, minimize the size of the array fetches so that the amount of C memory allocated for selects is small. You can do this by setting the weblogic.oci.cacheRows property (a JDBC connection property) to a small number. For example,
Properties props = new java.util.Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO" );
props.put("weblogic.oci.cacheRows", "1" );
Driver d =
(Driver)Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn = d.connect("jdbc:weblogic:oracle", props);
If the out of memory errors cease, it is likely that ResultSets are not being closed somewhere in your code. For more information, see Closing JDBC Objects.
Codeset Support
WebLogic supports Oracle codesets with the following consideration:
For more information, see Codeset Support in Using WebLogic jDriver for Oracle.
Other Problems with Oracle on UNIX
Check the threading model you are using. Green threads can conflict with the kernel threads used by OCI. When using Oracle drivers, WebLogic recommends that you use native threads. You can specify this by adding the -native flag when you start Java.
Thread-related Problems on UNIX
On UNIX, two threading models are available: green threads and native threads. For more information, read about the JDK for the Solaris operating environment on the Sun Web site.
You can determine what type of threads you are using by checking the environment variable called THREADS_TYPE. If this variable is not set, you can check the shell script in your Java installation bin directory.
Some of the problems are related to the implementation of threads in the JVM for each operating system. Not all JVMs handle operating-system specific threading issues equally well. Here are some hints to avoid thread-related problems:
$ echo $SHLIB_PATH
Use the set or setenv command (depending on your shell) to append the WebLogic shared library to the path defined by the symbol SHLIB_PATH. For the shared library to be recognized in a location that is not part of your SHLIB_PATH, you will need to contact your system administrator.
WebLogic also recommends—and good programming practice dictates—that you always close JDBC objects, like Connections, Statements, and ResultSets, in a finally block to make sure that your program executes efficiently. Here is a general example:
try {
Driver d =
(Driver)Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn = d.connect("jdbc:weblogic:oracle:myserver",
"scott", "tiger");
Statement stmt = conn.createStatement();
stmt.execute("select * from emp");
ResultSet rs = stmt.getResultSet();
// do work
}
catch (Exception e) {
// deal with any exceptions appropriate
}
finally {
try {rs.close();}
catch (Exception rse) {}
try {stmt.close();}
catch (Exception sse) {}
try {conn.close();
catch (Exception cse) {}
}
Abandoning JDBC Objects
You should also avoid the following practice, which creates abandoned JDBC objects:
//Do not do this. stmt.executeQuery(); rs = stmt.getResultSet();
//Do this instead rs = stmt.executeQuery();
The first line in this example creates a result set that is lost and can be garbage collected immediately.
Behavior for the second line varies depending on which service pack of WebLogic Server you are running. Before WebLogic Server 6.1SP5, the server would return a clone of the original object, which was still subject to garbage collection. After 6.1SP5, WebLogic Server returns the original object and does not garbage collect the object until it is no longer used.
Troubleshooting Problems with Shared Libraries on UNIX
When you install a native two-tier JDBC driver, configure WebLogic Server to use performance packs, or set up BEA WebLogic Server as a Web server on UNIX, you install shared libraries or shared objects (distributed with the WebLogic software) on your system. This document describes problems you may encounter and suggests solutions for them.
The operating system loader looks for the libraries in different locations. How the loader works differs across the different flavors of UNIX. The following sections describe Solaris and HP-UX.
Use the procedures for setting your shared libraries as described in this document. The actual path you specify will depend on your Oracle client version, your Oracle Server version and other factors. For details, see Installing WebLogic jDriver for Oracle.
To find out which dynamic libraries are being used by an executable you can run the ldd command for the application. If the output of this command indicates that libraries are not found, then add the location of the libraries to the LD_LIBRARY_PATH environment variable as follows (for C or Bash shells):
# setenv LD_LIBRARY_PATH weblogic_directory/lib/solaris/oci817_8
Once you do this, ld should no longer complain about missing libraries.
Incorrectly Set File Permissions
The shared library problem you are most likely to encounter after installing WebLogic on an HP-UX system is incorrectly set file permissions. After installing WebLogic, make sure that the shared library permissions are set correctly with the chmod command. Here is an example to set the correct permissions for HP-UX 11.0:
% cd weblogic_directory/lib/hpux11/oci817_8
% chmod 755 *.sl
If you encounter problems loading shared libraries after you set the file permissions, there could be a problem locating the libraries. First, make sure that the weblogic_directory/lib/hpux11 is in the SHLIB_PATH environment variable:
% echo $SHLIB_PATH
If the directory is not listed, add it:
# setenv SHLIB_PATH weblogic_directory/lib/hpux11:$SHLIB_PATH
Alternatively, copy (or link) the .sl files from the WebLogic distribution to a directory that is already in the SHLIB_PATH variable.
If you still have problems, use the chatr command to specify that the application should search directories in the SHLIB_PATH environment variable. The +s enabled option sets an application to search the SHLIB_PATH variable. Here is an example of this command, run on the WebLogic jDriver for Oracle shared library for HP-UX 11.0:
# cd weblogic_directory/lib/hpux11
# chatr +s enable libweblogicoci37.sl
Check the chatr man page for more information on this command.
Incorrect SHLIB_PATH
You may also encounter a shared library problem if you do not include the proper paths in your SHLIB_PATH when using Oracle 9. SHLIB_PATH should include the path to the driver (oci901_8) and the path to the vendor-supplied libraries (lib32). For example, your path may look like:
export SHLIB_PATH=
$WL_HOME/lib/hpux11/oci901_8:ORACLE/lib32:$SHLIB_PATH
Note also that your path cannot include the path to the Oracle 8.1.7 libraries, or clashes will occur. For more instructions, see Setting Up the Environment for Using WebLogic jDriver for Oracle.
|
|
|
|
|
Copyright © 2001 BEA Systems, Inc. All rights reserved.
|