The problem I think is , we can not realize below working flow, Begin work; Purgedata table_name; Upsert into table_name; Commit;
However, we can do this, Begin work; Drop table table_name; Create table as select .. from ...; Commit; But this approach need recreate table and reupdate statistics thus it may not good for performance. Best regards, Yuan -----Original Message----- From: Rohit Jain [mailto:[email protected]] Sent: Monday, January 15, 2018 11:26 AM To: [email protected] Subject: RE: purgedata can not be put in a transaction? I think the main issue is that for an unexperienced Trafodion user they might not know that purgedata is not transactional. So, the question is whether not knowing it is not transactional could potentially cause some inconsistencies or unexpected results down the road, such as if a point in time recovery is done, or if the table is being covered by xDC, and being replicated, etc. If it is purely cosmetic and does not add any value, then why do it. Question is what problem are we trying to solve here? -----Original Message----- From: Sean Broeder [mailto:[email protected]] Sent: Sunday, January 14, 2018 2:17 PM To: [email protected] Subject: RE: purgedata can not be put in a transaction? I think we could treat it like a drop. There we disable the table during phase0 of the transaction and allow any other phase0 operations to continue. There may be both DML and other DDL in the transaction. But we don't actually truncate the table until phase2 when all participants have voted yes. If any participant votes no we can rollback be simply enabling the table again. Technically, I think it's very doable. I would be interested to hear if others think it's important. Regards, Sean -----Original Message----- From: Dave Birdsall [mailto:[email protected]] Sent: Sunday, January 14, 2018 12:08 PM To: [email protected] Subject: RE: purgedata can not be put in a transaction? Hi, DROP TABLE and CREATE TABLE have been transactional in Trafodion's predecessor products since the beginning. As have most DDL operations. In the beginning, when Oracle was born, it was not well-understood how to make DDL transactional. At that time there was no SQL standard that specified DDL transactional behavior either. That has since changed -- such behavior is described in the SQL standard as an optional feature. PURGEDATA is a bit different. PURGEDATA is not a DDL operation, rather it is the same as "DELETE * FROM T". If you want a transactional version of PURGEDATA, you can get it by using DELETE instead. But the implementation is quite inefficient: All the rows from T will be written to the audit log to be used in case of rollback. PURGEDATA, being non-transactional, just does truncates on the underlying HBase table instead; nothing goes into the audit log. It is conceivable that we could make PURGEDATA transactional, but we'd have to under the covers map it to something like "DROP TABLE T" + "CREATE TABLE T". And there are complications such as preserving any dependent objects and privileges on top of that. DDL operations are expensive in Trafodion so it might not turn out to be particularly efficient. Dave -----Original Message----- From: Liu, Yuan (Yuan) [mailto:[email protected]] Sent: Friday, January 12, 2018 8:04 PM To: [email protected] Subject: RE: purgedata can not be put in a transaction? 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
