Sybase troubleshooting tips

This article describes how to diagnose the following problems related to Sybase:

 

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:

  • getBestRowIdentifier();

  • getVersionColumns();

  • getTablePrivileges();

  • getProcedureColumns();

  • getPrimaryKeys();

  • getIndexInfo();

  • getImportedKeys();

  • getExportedKeys();

  • getCrossReference();

  • getColumns();

  • getColumnPrivileges();

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 V5.2 EBF 10635 and run the following command 

java IsqlApp -U sa -P -S jdbc:sybase:Tds:hostname:4100 -I %JDBC_HOME%\sp\sql_server12.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 an assembly tool.

  • 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 datarows command 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.

      Note: 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 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:

  • 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.getSchemaName()

  • ResultSetMetaData.getTableName() (implemented only for text and image datatypes)

  • ResultSetMetaData.getCatalogName()

 

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 the IBM Support page.

IBM Support has documents that can save you time gathering information needed to resolve this problem. Before opening a PMR, see the IBM Support page.

 

Related Tasks


Troubleshooting by task

 

See Also


Troubleshooting installation problems