Using Pre and Post DML SQL Statements
Each SQL-based data object contains standard properties to execute Pre-DML and/or Post-DML type SQL statements. DML stands for Data Manipulation Language and refers to SQL statements such as INSERT, UPDATE and DELETE as well as data definition functions such as CREATE TABLE, etc...
The Pre and Post DML properties give you the added flexibility to run SQL statements on the fly before or after a transformation and load process occurs. For example, say you wanted to insert a record into an unrelated table after your loading process is finished to log the progress of your process. You could use the Post DML property to insert a record and capture the number of records you loaded like this:
insert into my_process_log(jobdesc, whendt, numrecs) (select 'Finished loading sales data', CURRENT_TIMESTAMP, count(*) from portal_dashboard)
Here we are simply getting a count of records that was loaded into our portal_dashboard table and logging the fact that it is now completed with the current date and time into the my_process_log table. If you did something like this throughout your whole nightly process you could then build a simple "Daily log" web page that you can quickly look at each morning!
Note: CURRENT_TIMESTAMP is the ANSI Standard system date time function, you could also use the system date function relative to your specific database engine. For instance, SQL Server has GetDate() and Oracle has SYSDATE.
Pre and Post DML for Oracle databases
For multiple statements to work with an Oracle database, you must wrap the code in a PL/SQL block of BEGIN and END; For example, below we are truncating and Oracle table, then updating another table with a date/time stamp
dbms_utility.exec_ddl_statement('TRUNCATE TABLE RETAILER_TEMPDATA');
UPDATE RETAILER_DATA set ADDED_DATE = sysdate;
Handling DML Failure
Each SQL based data object also has properties to control your job if a DML statement fails called OnPreDMLFailure and OnPostDMLFailure. The two options are ContinueOn and AbortJob. It really depends on how important the DML is to your nightly process. If you use the ContinueOn option, the failure will be logged in the job log but your job will not abort and will continue processing. If you use AbortJob, the job will stop with a status of Aborted.
© 2003 - 2007 Relational Solutions, Inc. - All rights reserved