We have a scenario where we use a notes script to write data from Notes databases to DB2 staging tables.

Every time the script runs , it first deletes the contents and then insert the contents of the notes database.

I am looking for a way to detect from DB2 point of view if the script has run at all. Can this be done by using a trigger upon the delete event and then writing a record with timestamp to a logging table?

I have used triggers in ORACLE before , but not in DB2......

Hi,

Yes I think this is possible. What you can do is create a trigger which on delete on the particular inserts a row in the logging table, where you can use the timestamp value selected from sysibm.sysdumm1, but in order to know whether the delete has run, you would need to manually query the logging table.

Secondly, whenever you do a load, the trigger would get invalidated, so you have to check for that as well.

Nitin.

Okay,

That brings me to the second part of the story....

What are the prequirements to be able to create the trigger?
Do I have to activate something like a DB2 subprocess?

From the IBM development site I get the following example:

1: CREATE TRIGGER log_delete
2: AFTER DELETE ON orders_t
3: REFERENCING OLD AS 0
4: FOR EACH ROW MODE DB2SQL
5: INSERT INTO delete_log_t VALUES (
'Order #'|| CHAR (0.order_id)||
'Was deleted on' || CHAR(CURRENT TIMESTAMP));

Now, I obviously need just one logging record for each delete action...

Why would the trigger be invalidated by the way?

Hi

To the best of my knowledge you do not need any pre-requisites except rights to the object to create any triggers.

The reason i feel the trigger gets invalidated during load process is because load does not check for referential integrity and puts the table in check pending state. So unless the tables are in a proper state i.e. ensured that valid data is loaded the trigger would not get fired.

Hope this is right..

Nitin.

Actually we are not using the DB2 load facility, but we use an ODBC connection to write the data to the DB2 table, which is veeeeeery slow, but works for us (It is done during nightly hours)

Command reference does not give a clue how to check whether a table is in 'check pending state' Do you have the syntax for the CLP command?

Load does not use SQL to put the data in the table, but import does.

hi,

The one below is for the tables, with whom there is no relation.

db2 set integrity for schemaname.tablename materialized query, generated column, foreign key, staging, check immediate unchecked


The one below is for transaction tables, that we anticipate are in relations.

db2 set integrity for schemaname.tablename immediate checked

I don't think your ODBC is a load process, it would be an insert in the code thus you don't need to verify for check pending state.

regards

Nitin

I know, but we are neither importing or loading in the traditional fashion.

I do not think the staging tables ever go into 'check pending state' , but would appreciate if someone could direct me to the syntx to check this.....

Hi,

My previous post has the syntax..

Guess got a bit late in posting as I was trying to figure out which point was Marcus_A trying to explain.

Nitin.

Nitin,

Thanks a lot for the syntax issue.
Do you have any idea how to generate just one record for the deletion of the entire staging table?

Okay,

This is what I did:

SET SCHEMA DB2HR;
SET PATH = "SYSIBM","SYSFUN","DB2HR";

CREATE TRIGGER DB2HR.LOG_DEL_PERSONEEL
AFTER
DELETE
ON DB2HR.ODS_PERSONEEL
FOR EACH STATEMENT
MODE DB2SQL
INSERT INTO HR_LOGGING
VALUES('PERSONEEL',CURRENT TIMESTAMP);

COMMENT ON TRIGGER DB2HR.LOG_DEL_PERSONEEL
IS 'Trigger on Delete ODS_PERSONEEL';


Unbelievabily this worked in just one attempt, so I am gonna celebrate my first ever created DB2 trigger..........

Thanks a lot guys......