Sybase troubleshooting tips
- Executing the DatabaseMetaData.getBestRowIdentifier() method in an XA transaction causes errors
- Sybase requirements for using the escapes and DatabaseMetaData methods
- Database deadlocks and XA_PROTO errors occur when using Sybase
- Executing a stored procedure containing a SELECT INTO command causes exception
- Error is incorrectly reported about IMAGE to VARBINARY conversion
- JDBC 1.0 standard methods are not implemented and generate a SQL exception when used
- Sybase transaction manager fails after trying to alleviate deadlock error
- Starting an XA transaction when the autoCommit value of the connection is false causes error
- Sybase does not throw an exception when an incorrect database name is specified
Executing the DatabaseMetaData.getBestRowIdentifier() method in an XA transaction causes errors
Executing the DatabaseMetaData.getBestRowIdentifier() method while in an XA transaction causes the following errorsSQL Exception: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database. Calling DatabaseMetaData.getBestRowIdentifier()
Currently, this method fails when using Sybase. This problem occurs with other methods as well, including...
Case 10880427 has been opened with Sybase to resolve this problem.
Sybase requirements for using the escapes and DatabaseMetaData methods
To use the escapes and DatabaseMetaData methods, install stored procedures on the Adaptive Server Enterprise or Adaptive Server Anywhere database where you want to use these methods. These stored procedures are also required by some of the connection methods.
To check for the presence of LOCATE ()...
- Open a Sybase isql command prompt.
- Type the command use master.
- Type the command go.
- Type the SQL command and select * from jdbc_function_escapes.
- Type the command go.
The following appearsescape_name map_string --------------------------------- abs abs(%1) acos acos(%1) asin asin(%1) atan atan(%1) atan2 atn2(%1, %2) ceiling ceiling(%1) :::::::::::::::::::::::::::::::::::::::::::::::::::::::... locate charindex ((convert (varchar, %1)), (convert (varchar, %2)))
If the function does not exist, upgrade jConnect to at least Version 5.2 EBF 10635 and run the following commandjava IsqlApp -U sa -P -S jdbc:sybase:Tds:hostname:4100 -I %JDBC_HOME%\sp\sql_server2.sql -c go
Database deadlocks and XA_PROTO errors occur when using Sybase
When using Sybase with the IBM WebSphere Application Server, do one of the following to prevent database deadlocks and errors...
- Change the transaction isolation level on the connection to TRANSACTION_READ_COMMITTED. Set the isolation level on the connection for unshareable connections or, for shareable connections, define the isolation levels in the resource reference for your data source using the Assembly Toolkit.
- Modify Sybase by doing one of the following...
- If you want to use the existing tables, modify the table locking scheme using the alter table table name lock datarowscommand to get a row lock level granularity.
- If you want to set the system-wide locking scheme to data rows, all subsequently created tables inherit that value and have a locking scheme of data rows.
You must drop your original databases and tables.
Executing a stored procedure containing a SELECT INTO command causes exception
An attempt to execute a stored procedure containing a SELECT INTO command results in the following exceptionSVR-ERROR: SQL Exception SELECT INTO command not allowed within multi-statement transaction
Case 10868947 has been opened with Sybase to resolve this problem.
Error is incorrectly reported about IMAGE to VARBINARY conversion
The following error is incorrectly reportedcom.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from data type 'IMAGE' to 'VARBINARY' is not allowed. Use the CONVERT function to run this query.
The error is about a VARBINARY column only and causes confusion if you also have an IMAGE column.
Do one of the following to work around this problem...
- Use a PreparedStatement.setBytes() method instead of a PreparedStatement.setBinaryStream() method
- Use a LONG VARBINARY for the column type if you want to continue using the setBinaryStream() method. You might want to make this change because the size limit for VARBINARY is 255 bytes.
For example// ***************CORRECTION***************************** // setBinaryStream fails for column type of VARBINARY , use setBytes() instead //stmt4.setBinaryStream(8,new java.io.ByteArrayInputStream(tempbyteArray),tempbyteArray.length); stmt4.setBytes(8,tempbyteArray);
JDBC 1.0 standard methods are not implemented and generate a SQL exception when used
The following JDBC 1.0 standard methods are not implemented and generate a SQL exception when used...
- ResultSetMetaData.getTableName() (implemented only for text and image datatypes)
Sybase transaction manager fails after trying to alleviate a deadlock error
If an application encounters a deadlock, Sybase detects the deadlock and throws an exception. Because of this detection, the transaction manager calls an xa_end with a TMFAIL in it.
The call succeeds, but causes another Sybase exception, XAERR_PROTO. This exception only appears in the error log and does not cause any functional problems. All applications should continue to run, therefore no workaround is necessary.
Case 10869169 has been opened with Sybase to resolve this problem.
Starting an XA transaction when the autoCommit value of the connection is false causes error
The exception thrown is javax.transaction.xa.XAException with stack trace similar to the followingat com.sybase.jdbc2.jdbc.SybXAResource.sendRPC(SybXAResource.java:711) at com.sybase.jdbc2.jdbc.SybXAResource.sendRPC(SybXAResource.java:602) at com.sybase.jdbc2.jdbc.SybXAResource.start(SybXAResource.java:312)
This problem affects you when you do both local and global transactions. If, in a local transaction, the autoCommit default value is set to false, and a global or XA transaction starts (either a user transaction started by you, or a container transaction started by a container), the exception occurs.
This problem is a Sybase bug as the start() method can fail unexpectedly, regardless of the value of autoCommit. Currently, there is no workaround for this problem, therefore it is not recommended that you mix local and global transactions. Case 10880792 has been opened to resolve this problem.
Sybase does not throw an exception when an incorrect database name is specified
Verify that your database name is correctly entered on the data source properties.
Most databases (DB2, Oracle, Informix , MS SQL Server and Cloudscape) throw an exception when the database specified does not exist. But Sybase does not throw an exception when an incorrect database name is specified. Sybase generates an SQL warning and then connects to the default database. If you misspell the requested database name, Sybase connects you to the master or the default database where the table you requested is not found.Back to Databases-Sybase Back to Known problems and workarounds
If none of these steps fixes your problem, check to see if the problem has been identified and documented by looking at the available online support (hints and tips, technotes, and fixes). If you do not find your problem listed there, contact IBM Support.
For current information available from IBM Support on known problems and their resolution, see IBM Support page.
IBM Support has documents that can save you time gathering information needed to resolve this problem. Before opening a PMR, see IBM Support page.
See AlsoTroubleshooting installation problems