Sybase ETL 4.9 extends data commit and rollback support at the end of the write operation across multiple components within a job and project execution or simulation. Data is committed at the end of the write operation for a successful execution or rolled back for an unsuccessful execution. In addition, the Multi-Project and Synchronizer job components now provide more control over the included projects by allowing you to commit intermediate work as soon as the processing finishes. If a project downstream the Multi-Project components fails, the projects that have already been committed remain unaffected; only downstream projects are rolled back.
In versions earlier than 4.9, you could not use transactions across multiple components or projects. Also, you could not roll back the complete set of changes in a target component. This is because, transactionality was supported only on component and batch levels.
Now, you can use transactionality across multiple projects, thus allowing you to execute or apply an all-or-nothing database update and load strategy. You can also set the job or project property to enable commit and rollback features for all those components that supports transactions. If you do not select the Propagate Rollback option, then the project or job does not enforce a transaction rollback on successful components, if one or more components fail. Each failed component rolls back its own transactions.
You can also select whether to commit or roll back any operation done by transactional components during project simulation. You can simulate either a success or failure condition on execution termination and reset the project to its initial state. This clears all port buffers, releases temporary tables, and closes database connections and temporary files. You can also disable new transactionality feature for all components within a job or project.
The improved transactionality feature lets you:
Perform an incremental load of data from a source database to Sybase IQ. On success, new rows are inserted and modified rows are updated into Sybase IQ. On execution failure, all modifications to the target table and staging table in IQ are rolled back as part of a single transaction.
Load multiple tables into Sybase IQ. On success, all tables in IQ are loaded with the new information from the data in the source database, and if execution fails, the modifications to all target tables are rolled back.
Delete multiple rows from a target IQ database in a single transaction.
Load IQ via Load Table as part of a single transaction.
Rollback source components pre- and post-processing SQL data from the target table at the end of the project execution.
Simulate a DBMS-style transaction when writing data to a local text file using the Text Data Sink component.
Commit all the projects of a multiproject job within a single distributed transaction.
Simulate a transactional project and commit or roll back at any step during the simulation process.
Stop a transactional job and ensure the data written to transactional resources within the job rolls back.
Create a job using the Migration wizard, defining transactionality for all projects and target DB components.
All database changes, including pre-SQL and post-SQL statements that are enclosed in a database transaction are rolled back along with all the other transactional components in the project, if project execution fails. You can also manually roll back any uncommitted tasks during project simulation.
However, you must execute the data definition language (DDL) SQL commands (create, drop, and alter) before executing any pre-SQL commands that you may need to rollback. This is because DDL SQL commands causes the database server to do an implicit commit transaction. For example, if you insert data into a table and execute CREATE TABLE command, the inserted data cannot be rolled back because it has already been implicitly committed by the database.
See Chapter 5, “Components” in the Sybase ETL 4.9 Users Guide.
