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

Have you checked if an AFTER INSERT trigger would works?

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

Have you tried using the LOAD utility? I believe you can use an Online LOAD Resume and get the triggers to fire.

I just noticed something:
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..

I just noticed something:
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..

I just noticed something:
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..

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!

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)