Thanks Anoop. DROP TABLE and CREATE TABLE are both DDL. As I know, those DDL are non-transactional in RDBMS such as Oracle. So I am curious that DROP TABLE can be rollbacked in Trafodion.
By the way, it is possible that we change PURGEDATA transactional? Best regards, Yuan -----Original Message----- From: Anoop Sharma [mailto:[email protected]] Sent: Saturday, January 13, 2018 12:24 AM To: [email protected] Subject: RE: purgedata can not be put in a transaction? Most of the DDL operations are transactional and supported by traf transaction manager (DTM) layer. This is a traf feature that enables DDL operations to be handled in an atomic transactional way. It means that one can do (for ex): begin work; drop table t; create table t1 (a..) rollback work; and get to the same state that existed before the begin work. Is there a reason or some confusion on 'drop table' being a transactional operation? One can set autocommit to ON in a session by doing: set transaction autocommit ON; This is automatically set to on from sqlci and trafci. There may be a conn property to set it to ON as well. anoop -----Original Message----- From: Liu, Yuan (Yuan) [mailto:[email protected]] Sent: Thursday, January 11, 2018 10:20 PM To: [email protected] Subject: RE: purgedata can not be put in a transaction? Hi Anoop, Thanks for your feedback. It is strange that 'drop table' is a transactional operation. When using "purgedata" in a java application flow, we saw below error, ERROR[20124] This DDL operation cannot be performed if AUTOCOMMIT is OFF. Can we set AUTOCOMMIT to ON in trafodion? How to set it? Best regards, Yuan -----Original Message----- From: Anoop Sharma [mailto:[email protected]] Sent: Friday, January 12, 2018 12:44 PM To: [email protected] Subject: RE: purgedata can not be put in a transaction? currently, purgedata(or truncate) is a non-transactional operation. It is performed by truncating the underlying traf/hbase object. That truncate operation cannot be undone or rolled back as it is not protected by traf transactional layer (dtm). 'drop table' on the other hand, is a transactional operation. One can 'rollback' a 'drop table' and get the table back. anoop -----Original Message----- From: Liu, Yuan (Yuan) [mailto:[email protected]] Sent: Thursday, January 11, 2018 8:15 PM To: [email protected] Subject: purgedata can not be put in a transaction? Hi Trafodioneers, I found that purgedata can not be put in a transaction. If we put below rows after begin transaction, error 20123 occurred. >> begin; >>purgedata test1; *** ERROR[20123] A user-defined transaction has been started. This DDL operation cannot be performed. However, "drop" and "delete with no rollback form" can run normally. From my side, I think both drop and purgedata are non-transactional, but why they behave different? Is there any that we can workaournd this error? Best regards, Yuan
