Sybase connection

Zinna · Post by **Zinna** » Tue Dec 10, 2002 4:46 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Dec 10, 2002 4:55 pm

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

Zinna · Post by **Zinna** » Wed Dec 11, 2002 4:50 pm

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?

ray.wurlod · Post by **ray.wurlod** » Wed Dec 11, 2002 7:07 pm

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.

Zinna · Post by **Zinna** » Thu Dec 12, 2002 3:58 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 13, 2002 1:14 pm

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

mleroux · Post by **mleroux** » Fri Sep 10, 2004 12:05 am

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.

ccormack · Post by **ccormack** » Fri Sep 10, 2004 5:16 am

Many thanks, Meena. :D