By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

db2 LUW v8 after inser trigger encounter error sql0348

P: n/a
Problem: after inser trigger encounter error sql0348

Env:db2 v8 + fp 13 + win xp

Description:
we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table
DB2.TEST1,
insert data into DB2.TEST1 encounter error sql0348

but we re-create trigger DB2.TRG2 before DB2.TRG1 and re-insert data
into DB2.TEST1 successfully.

did anyone can help to advice why db2 create triggers sequence will
get difference result ?
sample case as follow:
================================================== ========
connect sample @

drop view db2.sum @
drop trigger DB2.TRG1 @
drop trigger DB2.TRG2 @
drop table DB2.Q1 @
drop table DB2.Q2 @
drop table DB2.TEST1 @
drop table DB2.T1 @
drop table DB2.T2 @
drop sequence db2.test_1_seq @

CREATE SEQUENCE DB2.TEST_1_SEQ AS INTEGER
MINVALUE 1 MAXVALUE 2147483647
START WITH 1 INCREMENT BY 1
CACHE 10000 CYCLE NO ORDER @

create table db2.t1 (id int, name varchar(5), dep varchar(5), code
int ) @
create table db2.t2 (id int, name varchar(5), dep varchar(5), code
int ) @
create table DB2.TEST1 ( ID INT, NAME CHAR(10)) @
create table DB2.Q1 ( ID INT, NAME CHAR(10)) @
create table DB2.Q2 ( ID INT, NAME CHAR(10)) @

create view db2.sum ( id, name, code )
as
select id, name, dep
from db2.t1
where dep='A00'
union all
select id, name, dep
from db2.t1
where dep='B00' @
create trigger DB2.TRG1
after insert on DB2.TEST1
referencing
new as nw_row for each row
mode db2sql
BEGIN ATOMIC
IF ( (select count(*) from syscat.indexes) 10 )
THEN
insert into DB2.Q1 (ID, NAME)
values (
(select count(*) from DB2.SUM),
-- nw_row.ID,
nw_row.NAME) ;
ELSE
insert into DB2.Q1 (ID, NAME)
values ( (500+nw_row.ID),
nw_row.NAME) ;
END IF;
END @

create trigger DB2.TRG2
after insert on DB2.TEST1
referencing
new as nw_row for each row
mode db2sql
BEGIN ATOMIC
insert into DB2.Q2 (ID, NAME)
values ( NEXTVAL FOR DB2.TEST_1_SEQ,
nw_row.NAME) ;
END @
================================================== ========
Insert data:
db2 connect to sample
db2 "insert into db2.test1 values(11111,'xxyyzz') "
* will get error sql0348

if re-create trigger DB2.TRG2 before DB2.TRG1 , the trigger will
successfully without error.

Thanks

Apr 26 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
wu*******@gmail.com wrote:
Problem: after inser trigger encounter error sql0348

Env:db2 v8 + fp 13 + win xp

Description:
we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table
DB2.TEST1,
insert data into DB2.TEST1 encounter error sql0348

but we re-create trigger DB2.TRG2 before DB2.TRG1 and re-insert data
into DB2.TEST1 successfully.

did anyone can help to advice why db2 create triggers sequence will
get difference result ?
That's a bug! Can you call support and get an APAR opened?
Your repro is good. If support gives you a headache refer them to me. :-)

Chers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 26 '07 #2

P: n/a
On Apr 27, 1:13 am, Serge Rielau <srie...@ca.ibm.comwrote:
wugon....@gmail.com wrote:
Problem: after inser trigger encounter error sql0348
Env:db2 v8 + fp 13 + win xp
Description:
we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table
DB2.TEST1,
insert data into DB2.TEST1 encounter error sql0348
but we re-create trigger DB2.TRG2 before DB2.TRG1 and re-insert data
into DB2.TEST1 successfully.
did anyone can help to advice why db2 create triggers sequence will
get difference result ?

That's a bug! Can you call support and get an APAR opened?
Your repro is good. If support gives you a headache refer them to me. :-)

Chers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks your advice.

Apr 27 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.