Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: Trigger on deleting contents of table

    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......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Possible

    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.
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    No Prerequisites

    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.
    HTH

    Nitin

    Ask the experienced rather than the learned

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Re: No Prerequisites

    Originally posted by nitingm
    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?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Load does not use SQL to put the data in the table, but import does.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Syntax

    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
    HTH

    Nitin

    Ask the experienced rather than the learned

  8. #8
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Originally posted by Marcus_A
    Load does not use SQL to put the data in the table, but import does.
    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.....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  9. #9
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Done the needful

    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.
    HTH

    Nitin

    Ask the experienced rather than the learned

  10. #10
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Re: Done the needful

    Originally posted by nitingm
    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?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  11. #11
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    GOT IT

    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......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •