SQL0723N sequence + union all

[urban.widmark]

Hello

We are having some problems with triggers, sequences and union all in
V8 on code that worked fine in V7. Was wondering if someone else has
seen this and/or knows what to do.

A trigger that runs after insert on, where the insert uses nextval on a
sequence for the key and the trigger uses union all we get this
message:

SQL0723N An error occurred in a triggered SQL statement in trigger
"TMP.T".
Information returned for the error includes SQLCODE "-348", SQLSTATE
"428F9"
and message tokens "NEXTVAL FOR TMP.AA". SQLSTATE=09000

Adding a full example below.

At first I though it was the reference we did to "newrow.id" (set by
nextval) that failed, but see the last trigger that fails with no
direct reference to the input data.

Tested with DB2 v8 (fp7 and fp10) on fedora core 3 and 4 (unsupported,
I know - plan on downloading the v8 windows trial version and see if
that does the same unless someone else runs this test for me ... hint,
hint :)

/Urban
--
-- Silly example to show a problem when moving from V7 to V8
--

-- create database apa;

connect to apa;

set schema tmp;

drop trigger T;
drop sequence aa restrict;
drop table aa;
drop table aa2;
create table aa (
id integer not null,
val integer default 0,

primary key (id)
);

create table aa2 (
id integer not null,
val integer default 0
);

create sequence aa;

insert into aa(id, val) values (nextval for aa, 1);
insert into aa(id, val) values (nextval for aa, 2);
insert into aa(id, val) values (nextval for aa, 3);

--
-- this trigger works
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select newrow.id, 22 from aa; --
end;
drop trigger T;

--
-- the values() part fails in V8 but works in V7
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select newrow.id, 33 from aa
union all
values (newrow.id, -1); --
end;
drop trigger T;

--
-- also fails
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select id, 44 from aa
union all
select newrow.id, -1 from sysibm.sysdummy1; --
end;
drop trigger T;

--
-- also fails
--
create trigger T
after insert on aa
for each row mode db2sql
begin atomic
insert into aa2(id, val)
values (2, 2)
union all
values (1, 1); --
end;
insert into aa(id, val) values (nextval for aa, 4);
insert into aa(id, val) values (nextval for aa, 5);
insert into aa(id, val) values (nextval for aa, 6);

-- works
insert into aa(id, val) values (99, 4);

[Serge Rielau]

ur***********@enlight.net wrote:

Hello

We are having some problems with triggers, sequences and union all in
V8 on code that worked fine in V7. Was wondering if someone else has
seen this and/or knows what to do.

A trigger that runs after insert on, where the insert uses nextval on a
sequence for the key and the trigger uses union all we get this
message:

SQL0723N An error occurred in a triggered SQL statement in trigger
"TMP.T".
Information returned for the error includes SQLCODE "-348", SQLSTATE
"428F9"
and message tokens "NEXTVAL FOR TMP.AA". SQLSTATE=09000

Adding a full example below.

At first I though it was the reference we did to "newrow.id" (set by
nextval) that failed, but see the last trigger that fails with no
direct reference to the input data.

Tested with DB2 v8 (fp7 and fp10) on fedora core 3 and 4 (unsupported,
I know - plan on downloading the v8 windows trial version and see if
that does the same unless someone else runs this test for me ... hint,
hint :)

/Urban
--
-- Silly example to show a problem when moving from V7 to V8
--

-- create database apa;

connect to apa;

set schema tmp;

drop trigger T;
drop sequence aa restrict;
drop table aa;
drop table aa2;
create table aa (
id integer not null,
val integer default 0,

primary key (id)
);

create table aa2 (
id integer not null,
val integer default 0
);

create sequence aa;

insert into aa(id, val) values (nextval for aa, 1);
insert into aa(id, val) values (nextval for aa, 2);
insert into aa(id, val) values (nextval for aa, 3);

--
-- this trigger works
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select newrow.id, 22 from aa; --
end;
drop trigger T;

--
-- the values() part fails in V8 but works in V7
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select newrow.id, 33 from aa
union all
values (newrow.id, -1); --
end;
drop trigger T;

--
-- also fails
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select id, 44 from aa
union all
select newrow.id, -1 from sysibm.sysdummy1; --
end;
drop trigger T;

--
-- also fails
--
create trigger T
after insert on aa
for each row mode db2sql
begin atomic
insert into aa2(id, val)
values (2, 2)
union all
values (1, 1); --
end;
insert into aa(id, val) values (nextval for aa, 4);
insert into aa(id, val) values (nextval for aa, 5);
insert into aa(id, val) values (nextval for aa, 6);

-- works
insert into aa(id, val) values (99, 4);

Faszinating! That's a bug. The trigger should not make a difference.

Please open a PMR.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[urban.widmark]

Is there a way to submit a PMR (or other form of bug report) without a
support contract? Don't think we have any and if we do it will be for
V7, but I will ask around.

/Urban

[Serge Rielau]

ur***********@enlight.net wrote:

Is there a way to submit a PMR (or other form of bug report) without a
support contract? Don't think we have any and if we do it will be for
V7, but I will ask around.

/Urban

I thought you said it works on V7? Aside I don't think there are any
more fixpacks on V7.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[urban.widmark]

Heh, where did I say it doesn't work on V7?

We are using V7 so we may have some support contract for that, seems
impossible to find out right now with people away on holiday. But we
don't have any contract for V8 (if there is a difference) as we are
only experimenting with a trial version.

/Urban

[Knut Stolze]

ur***********@enlight.net wrote:

Heh, where did I say it doesn't work on V7?

We are using V7 so we may have some support contract for that, seems
impossible to find out right now with people away on holiday. But we
don't have any contract for V8 (if there is a difference) as we are
only experimenting with a trial version.

Unless you have an extended contract for V7, you're a bit out of luck. V7
is out of service for about 1 year already.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Gert van der Kooij]

In article <11**********************@g47g2000cwa.googlegroups .com>,
ur***********@enlight.net (ur***********@enlight.net) says...

Heh, where did I say it doesn't work on V7?

We are using V7 so we may have some support contract for that, seems
impossible to find out right now with people away on holiday. But we
don't have any contract for V8 (if there is a difference) as we are
only experimenting with a trial version.

/Urban

AFAIK, if the update protection option is contained in your support
contract you automatically have support for V8.

[lennart]

ur***********@enlight.net wrote:

Heh, where did I say it doesn't work on V7?

We are using V7 so we may have some support contract for that, seems
impossible to find out right now with people away on holiday. But we
don't have any contract for V8 (if there is a difference) as we are
only experimenting with a trial version.

/Urban

FWIW, the example fails with Redhat ES 3 (which is supported) as well:

[db2inst1@wb-01 ~/nobackup/ltjn/experiment]$ uname -a
Linux wb-01 2.4.21-20.ELsmp #1 SMP Wed Aug 18 20:46:40 EDT 2004 i686
i686 i386 GNU/Linux
[db2inst1@wb-01 ~/nobackup/ltjn/experiment]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08022"
with level identifier "03030106".
Informational tokens are "DB2 v8.1.0.89", "OD_14086", "MI00105_14086",
and
FixPak "9".
Product is installed at "/opt/IBM/db2/V8.1".

set schema tmp;

drop trigger T;
drop sequence aa restrict;
drop table aa;
drop table aa2;

create table aa (
id integer not null,
val integer default 0,

primary key (id)
);

create table aa2 (
id integer not null,
val integer default 0
);

create sequence aa;

insert into aa(id, val) values (nextval for aa, 1);
insert into aa(id, val) values (nextval for aa, 2);
insert into aa(id, val) values (nextval for aa, 3);
create trigger T
after insert on aa
for each row mode db2sql
begin atomic
insert into aa2(id, val)
values (2, 2)
union all
values (1, 1); --
end;

[db2inst1@wb-01 ~/nobackup/ltjn/experiment]$ db2 "insert into aa(id,
val) values (nextval for aa, 4)"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0723N An error occurred in a triggered SQL statement in trigger
"TMP.T".
Information returned for the error includes SQLCODE "-348", SQLSTATE
"428F9"
and message tokens "NEXTVAL FOR TMP.AA". SQLSTATE=09000

Replacing "union all" with union in the trigger doesnt work either, but
using an identity column in aa does.

set schema tmp;

drop trigger T;
drop sequence aa restrict;
drop table aa;
drop table aa2;

create table aa (
id integer not null
GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1),
val integer default 0,
primary key (id)
);

create table aa2 (
id integer not null,
val integer default 0
);

create sequence aa;

insert into aa(val) values (1);
insert into aa(val) values (2);
insert into aa(val) values (3);
create trigger T
after insert on aa
for each row mode db2sql
begin atomic
insert into aa2(id, val)
values (2, 2)
union
values (1, 1); --
end;
Dont know if it is possible, but you could try hiding aa behind a view
and use instead of triggers to update it. Example

create table aa_prim (
id integer not null
GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1),
val integer default 0,
primary key (id)
);

create view aa as select * from aa_prim;

create trigger XXX
instead of insert on AA
[...]
HTH
/Lennart