+

Search Tips   |   Advanced Search

Data access problems - Sybase data source


This article provides troubleshooting tips for accessing Sybase data sources.

 

What kind of problem are you having accessing your Sybase database?

 

"Sybase Error 7713: Stored Procedure can only be executed in unchained transaction mode" error

This error occurs when either:

To fix the autocommit(true) mode problem, let the application change the connection to chained mode using the Connection.setAutoCommit(false) mode, or use a set chained on language command.

To resolve the stored procedure problem, use the sp_procxmode procedure_name "anymode" command.

 

"JZ0XS: The server does not support XA-style transactions. Please verify that the transaction feature is enabled and licensed on this server."

This error occurs when XA-style transactions are attempted on a server that does not have Distributed Transaction Management (DTM) installed. To resolve this problem, use the instructions in the Sybase Manual titled: Use Adaptive Server Distributed Transaction Management Features to enable Distributed Transaction Management (DTM). The main steps in this procedure are:

  1. Install the DTM option.

  2. Check the license.dat file to verify that the DTM option is installed.

  3. Restart the license manager.

  4. Enable DTM in ISQL.

  5. Restart the ASE service.

 

A container managed persistence (CMP) enterprise bean is causing exceptions

This error is caused by improper use of reserved words. Reserved words cannot be used as column names.

To correct this problem: Rename the variable to remove the reserved word. You can find a list of reserved words in the Sybase Adaptive Server Enterprise Reference Manual; Volume 1: Building Blocks, Chapter 4. This manual is available online at: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman.

 

Sybase JDBC data source fails with "Incorrect URL format" exception in IPv6 environment

Problem If we configure a Sybase JDBC data source through the admin console and attempt to use it in an IPv6 environment, we can experience the following exception: 

java.sql.SQLException: JZ0NE: Incorrect URL format

Cause

The Sybase JDBC drivers that are listed as selections for JDBC provider type in the admin console do not support IPv6. The admin console does not contain a pre-formatted template for the Sybase jConnect JDBC driver v6.0 EBF12884, which is the version required to connect to the database in an IPv6 environment.

However, we can still use the admin console to define the Sybase jConnect JDBC driver v6.0 EBF12884.

Solution Complete the following steps to define the Sybase jConnect JDBC driver v6.0 EBF12884:

  1. In the admin console, go to Resources > JDBC Providers and click New.

  2. Select User-defined for the database type. This selection triggers the console to set the provider type to User-defined JDBC provider and implementation type to User-defined.

  3. Click Next to go to the JDBC provider general configuration page.

  4. In the Class path field, replace the default JAR file name with jconn3.jar.

  5. For Implementation class name:

    • For a data source implementation that supports only one phase transactions, input 

      com.sybase.jdbc3.jdbc.SybConnectionPoolDataSource

    • For an implementation that supports two phase transactions, input 

      com.sybase.jdbc3.jdbc.SybXADataSource

  6. Click Apply. Then click the Data sources link.

  7. Click New to define the general properties of the data source.

  8. Click Apply after defining the general data source properties. Then click the Custom properties link.

  9. Define the following properties as custom properties. To set these properties, click New. Input the property name and a valid value for each one.

    • databaseName

    • serverName
    • portNumber

 

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 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 WAS, 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 we 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 the 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 we misspell the requested database name, Sybase connects you to the master or the default database where the table you requested is not found.

If none of these steps fixes the 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 we do not find the problem listed there, contact IBM Support.


Related concepts


Troubleshooting help from IBM

 

Related tasks


Develop data access applications
Example: Using IBM extended APIs to share connections between CMP beans and BMP beans

 

Related


Data access problems
Data access problems - Oracle data source
Data access problems - DB2 database
Data access problems - Microsoft SQL Server data source
Data access problems - Apache Derby database
Extensions to data access APIs