Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Question Unanswered: Fire triggers on import?

    I am trying to generate unique "rowids" for all the rows in all of the tables in my database (not just unique ids across each table - so identity columns won't help me). I am using a trigger in order to generate this unique value automatically on inserts (using the generate_unique() method). I need to load data from files in order to populate my tables, so I can't use simple inserts and I am using the db2import utility, as I know the load utility does not fire triggers. My problem is that even with this import utility, my trigger does not get fired. What I did is the following:

    create table customer
    ( ROW_ID CHAR(13) FOR BIT DATA
    , c_custkey integer not null
    , c_name varchar(25) not null
    , c_address varchar(40)
    , c_nationkey integer
    , c_phone char(15)
    , c_acctbal float
    , c_mktsegment char(10)
    , c_comment varchar(117)
    )
    in userspace1;
    ;

    CREATE TRIGGER UPDATE_CUSTOMER
    NO CASCADE
    BEFORE INSERT ON CUSTOMER
    REFERENCING NEW AS NEW_CUSTOMER
    FOR EACH ROW MODE DB2SQL
    SET NEW_CUSTOMER.ROW_ID = GENERATE_UNIQUE();
    ;

    import from customer.tbl OF del
    modified by coldel|
    method P (1, 2, 3, 4, 5, 6, 7, 8)
    messages customer.log
    insert into customer(c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment );
    ;

    When I issue select count(distinct timestamp(row_id)) from customer; I get a 0. How can I convince import to activate the trigger? I have to mention that the trigger gets fired when I issue simple INSERT statements.

    Thanks,
    Laura

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Have you checked if an AFTER INSERT trigger would works?

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    As far as I know, I can't modify new row/table transition variables with an AFTER INSERT trigger. Anyways, this is not the problem. The problem is that the trigger gets fired after a simple INSERT into CUSTOMERS statement, but not when I'm loading data using the IMPORT utility.The documentation explicitly specifies that the IMPORT utility (unlike LOAD) uses underlying INSERT statements to load the data, so logically speaking, the trigger should get fired. Are triggers deactivated by default on IMPORT executions? If so, how can I specify that I want my triggers to be fired on IMPORT? If not, then is this a bug?

    Thanks,
    Laura

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Have you tried using the LOAD utility? I believe you can use an Online LOAD Resume and get the triggers to fire.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I just noticed something:
    Quote Originally Posted by laurici

    When I issue select count(distinct timestamp(row_id)) from customer; I get a 0.
    This might be your problem... I don't think you can convert CHAR(13) FOR BIT DATA to TIMESTAMP. What does SELECT COUNT(DISTINCT ROW_ID) FROM CUSTOMER give you?

    What platform are you on? What DB2 version?

    I just tested your trigger on Win2k/DB2 8.1.5, and it seems to be working fine..

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I just noticed something:
    Quote Originally Posted by laurici

    When I issue select count(distinct timestamp(row_id)) from customer; I get a 0.
    This might be your problem... I don't think you can convert CHAR(13) FOR BIT DATA to TIMESTAMP. What does SELECT COUNT(DISTINCT ROW_ID) FROM CUSTOMER give you?

    What platform are you on? What DB2 version?

    I just tested your trigger on Win2k/DB2 8.1.5, and it seems to be working fine..

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I just noticed something:
    Quote Originally Posted by laurici

    When I issue select count(distinct timestamp(row_id)) from customer; I get a 0.
    This might be your problem... I don't think you can convert CHAR(13) FOR BIT DATA to TIMESTAMP. What does SELECT COUNT(DISTINCT ROW_ID) FROM CUSTOMER give you?

    What platform are you on? What DB2 version?

    I just tested your trigger on Win2k/DB2 8.1.5, and it seems to be working fine..

  8. #8
    Join Date
    Oct 2003
    Posts
    8
    Thanks! That was the problem indeed (no bug with import). I was confused because (as a newbie) I was typing my sql queries inside the command window, and when selecting row_id as such, nothing showed up in the results tab (I mean, the cells were all empty). I switched to the command window and I can see all the distinct char for bit data now Thank you so much again!

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This is one of the reasons why I never use Command Centre :-)

    (I'm trying hard not to hit "Submit" three times in a row)

Posting Permissions

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