DSXchange

Help appreciated!!!

I am in the process of upgrading to datastage 6 (and sybase 12.5) on Solaris 8. And am getting the following warning with all jobs using sybase stage, even with a simple 'select' sybase stage:
========================================================
test..SYBASEOC_0: Sybase Server warning 2762 (severity 16) from stored procedure 'sp_tables', line 212: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
========================================================

I had debugging turned on in Sybase and saw nothing strange.

Any ideas?

Thanks,
Zinna

The message is very clear, you're trying to get Sybase to do something (use CREATE TABLE in the tempdb database from within a multi-statement transaction) which Sybase's own rules do not permit.
Either use a different database, or use a single statement transaction to create the table (which might, in turn, involve using a separate stored procedure if that's how you want to control things).

Edited by - ray.wurlod on 12/10/2002 16:56:00

But the problem is, no where in my job do I create a table. It is a simple select sybase stage which just selects data from one table and output to sequential file. Why would a "Create Table.." be issued in tempdb? Transaction logging is off, auditing is off. Does datastage use temporary tables when I use Sybase stage?

I'd be more inclined to believe that the stored procedure is using a temporary table somehow. Can you post the stored procedure without breaking any confidentiality? Perhaps you can post a copy of the stored procedure definition, with identifier names changed, for Forum participants to contemplate.

I am not using any stored procedures from datastage. The sp_tables is a system stored procedure on Sybase. I do not know why this is being called or when/where the "Create Table" command is being issued, as transaction logging is off on Sybase.

Hmm, tricky.
Looks like sp_tables is itself attempting to create a temporary table, and Sybase is objecting because tempdb database is being used (and, presumably, sp_tables is a multi-statement stored procedure). I suspect this is happening when the Sybase Open Client software is attempting to access a list of available tables in the database. (But, to be honest, Sybase is not my forte - consult your Sybase support/DBA.)
Might be worth checking whether you can set/change your default database, to try to avoid tempdb.
I note also that you are upgrading both DS and Sybase. Do you have any opportunity to try upgrading just one, to see whether it's the new version of DS or the new version of Sybase that's causing you this grief?

Edited by - ray.wurlod on 12/13/2002 13:16:48

I am also aware of a site that's experiencing this warning. Maybe I can get them to come look at this info, try it and if they do we'll post the results.

Many thanks, Meena. :D