Sybase troubleshooting tips

 

  1. Executing the DatabaseMetaData.getBestRowIdentifier() method in an XA transaction causes errors
  2. Sybase requirements for using the escapes and DatabaseMetaData methods
  3. Database deadlocks and XA_PROTO errors occur when using Sybase
  4. Executing a stored procedure containing a SELECT INTO command causes exception
  5. Error is incorrectly reported about IMAGE to VARBINARY conversion
  6. JDBC 1.0 standard methods are not implemented and generate a SQL exception when used
  7. Sybase transaction manager fails after trying to alleviate deadlock error
  8. Starting an XA transaction when the autoCommit value of the connection is false causes error
  9. 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 errors

SQL 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 ()...

  1. Open a Sybase isql command prompt.
  2. Type the command use master.
  3. Type the command go.
  4. Type the SQL command and select * from jdbc_function_escapes.
  5. Type the command go.

The following appears

escape_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 command

java 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...

 

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 exception

SVR-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 reported

com.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...

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...

 

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 following

       at 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 Also

Troubleshooting installation problems