Sybase ASE allows the use of Data Definition Language statements within a transaction and so we can have create table, create view etc statements with the transaction block i.e. between begin and commit or rollback tran.
However, this feature is a configurable option named - "ddl in tran" and is available at database level, which means that the configurable parameter "ddl in tran" needs to be switched on whenever we think we need to use this option. But have you ever thought - "why is it so that ddl in tran is actually a configurable option?" or it would be much better to rephrase as - "Why does Sybase won't recommend using DDL statements within a transaction for normal case?".
Well, the answer to both these questions which infact are same and differ only in the way they express the thought is that whenever a DDL command or Data Definition language commands are executed by Sybase ASE then Adaptive Server needs to acquire locks on system tables and as we all know acquring locks on system tables for a longer transaction might actually degrade database performance. Hence this option is usually not recommended by Sybase, however if we are in dire need of using this option - for example - we need to execute queries using Oracle Hetrogeneous Services (Oracle - Sybase Gateway) which wraps each sql statement run from Oracle end into an implicit transaction before sending the query to Sybase end, we can enable ddl in tran database option but try to keep our transactions as short and specific as possible.
The only place where Sybase strictly disallow us in using ddl in tran option is in the temporary database or the tempdb which is supposed to behave unexpectedly on enabling this option. If we enable this option in temporary database tempdb then the databases may stop responding and behave unexpectedly. Therefore tempdb should never be enabled with ddl in tran option.
Now coming to the basic question as how to enable this option in a Sybase database. The answer to this is - "Use sp_configure system stored procedures". The syntax for enabling this option is -
sp_configure <<Database name>>, 'ddl in tran', true
and to switch off this option simply use -
sp_configure <<Database Name>>, 'ddl in tran', false
Please note - sp_configure is a system stored procedure and while trying to enable/disable this option the user needs to be in the master database.
However, this feature is a configurable option named - "ddl in tran" and is available at database level, which means that the configurable parameter "ddl in tran" needs to be switched on whenever we think we need to use this option. But have you ever thought - "why is it so that ddl in tran is actually a configurable option?" or it would be much better to rephrase as - "Why does Sybase won't recommend using DDL statements within a transaction for normal case?".
Well, the answer to both these questions which infact are same and differ only in the way they express the thought is that whenever a DDL command or Data Definition language commands are executed by Sybase ASE then Adaptive Server needs to acquire locks on system tables and as we all know acquring locks on system tables for a longer transaction might actually degrade database performance. Hence this option is usually not recommended by Sybase, however if we are in dire need of using this option - for example - we need to execute queries using Oracle Hetrogeneous Services (Oracle - Sybase Gateway) which wraps each sql statement run from Oracle end into an implicit transaction before sending the query to Sybase end, we can enable ddl in tran database option but try to keep our transactions as short and specific as possible.
The only place where Sybase strictly disallow us in using ddl in tran option is in the temporary database or the tempdb which is supposed to behave unexpectedly on enabling this option. If we enable this option in temporary database tempdb then the databases may stop responding and behave unexpectedly. Therefore tempdb should never be enabled with ddl in tran option.
Now coming to the basic question as how to enable this option in a Sybase database. The answer to this is - "Use sp_configure system stored procedures". The syntax for enabling this option is -
sp_configure <<Database name>>, 'ddl in tran', true
and to switch off this option simply use -
sp_configure <<Database Name>>, 'ddl in tran', false
Please note - sp_configure is a system stored procedure and while trying to enable/disable this option the user needs to be in the master database.
No comments:
Post a Comment