0

This could be a silly question, but please bare me as I have little knowledge on database with Java. I am developing a component for which I want to give an option to user so that auto-commit can be set to false. This is for performance boost. I am connecting to Sybase ASE with JDK7u25.

I can call setAutoCommit(false) method on java.sql.Connection. However, when I do this and calls a simple stored procedure which inserts a record into the table, I get following error. I am not creating a table from my stored procedure.

com.sybase.jdbc3.jdbc.SybSQLException: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

I read on the Sybase documentation page and came to know that this is known issue with error code 2762. This can be fixed by issuing following command on the database:

sp_dboption dbName, "ddl in tran", true.

However, when I execute this command in Interactive SQL and try to run my program I still get this error. Should I explicitly run this command from my java program?

On the other note: I also had below problem:

Space available in the log segment has fallen critically low in database 'myDB'.  All future modifications to this database will be %S_MSG until the log is successfully dumped and space becomes available.
The transaction log in database myDB is almost full. Your transaction is being suspended until space is made available in the log.

I fixed this up by issuing following command in Interactive SQL:

sp_dboption myDB, "trunc log", true

If I didn't have to explicitly execute this command through my java program and it worked, why should be the reason (if it has to be) to execute the sp_dboption with 'ddl in tran' to true through my java program? Is it because session created by java program is different from using one in Interactive SQL?

ParagJ
  • 1,566
  • 10
  • 38
  • 56

2 Answers2

0

Your "trunc log" truncates the log files. That affects all sessions (obviously, since the log files are common to all sessions). So you'll need to perform the "ddl in tran" command once per session, unless you can find some way in the db config to make it stick.

By the way, auto-commit shouldn't be about performance boost, but rather to guarantee that you have proper transactionality. Of course it does affect performance, but your wording struck me as odd.

Kayaman
  • 72,141
  • 5
  • 83
  • 121
  • Thanks. Is there any way I can set it using db config? When I try to execute this command using Java program (java.sql.Statement.executeUpdate()) it gives me an error saying: com.sybase.jdbc3.jdbc.SybSQLException: Usage: sp_dboption [dbname, optname, {true | false}] – ParagJ Sep 25 '13 at 13:31
  • You'll have to dig through the documentation. I've never used sybase. As for setting that through Java, you can't use executeUpdate(), since that's for updating a table. See the JDBC documentation about how to call a stored procedure. – Kayaman Sep 25 '13 at 13:33
0

Once you issue the sp_dboption for "ddl in tran" the value is set to true for all sessions connecting to that database. It's not a per seesion value, so issuing the command in your code will probably not resolve the issue. To check the current options for each database you can issue sp_helpdb which will show the current options set for each database.

Since your error is complaining about tempdb, I would suggest you ensure that "ddl in tran" is enabled on both your tempdb, and your production db.

Also, just to clarify, your command to truncate the log, doesn't actually truncate the log. What it does is set the value for Truncate Log on Checkpoint to true, so your log will get truncated when you issue a checkpoint. To manually truncate the log, the command is dump tran myDB with [options]

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34