SQL0723N sequence + union all

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

urban.widmark@enlight.net - 21 Dec 2005 13:56 GMT

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);

Reply to this Message

Serge Rielau - 21 Dec 2005 14:14 GMT

> Hello
>
[quoted text clipped - 120 lines]
> -- 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

Signature

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

Reply to this Message

urban.widmark@enlight.net - 21 Dec 2005 15:35 GMT

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

Reply to this Message

Serge Rielau - 22 Dec 2005 22:36 GMT

> 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

Signature

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

Reply to this Message

urban.widmark@enlight.net - 27 Dec 2005 10:53 GMT

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

Reply to this Message

Knut Stolze - 27 Dec 2005 12:20 GMT

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Gert van der Kooij - 27 Dec 2005 13:49 GMT

> Heh, where did I say it doesn't work on V7?
>
[quoted text clipped - 4 lines]
>
> /Urban

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

Reply to this Message

lennart@kommunicera.umea.se - 29 Dec 2005 09:01 GMT

> Heh, where did I say it doesn't work on V7?
>
[quoted text clipped - 4 lines]
>
> /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

Reply to this Message