473,837 Members | 1,441 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL0723N sequence + union all

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.sysdummy 1; --
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);

Dec 21 '05 #1
7 3617
ur***********@e nlight.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.sysdummy 1; --
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
Dec 21 '05 #2
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

Dec 21 '05 #3
ur***********@e nlight.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
Dec 22 '05 #4
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

Dec 27 '05 #5
ur***********@e nlight.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
Dec 27 '05 #6
In article <11************ **********@g47g 2000cwa.googleg roups.com>,
ur***********@e nlight.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.
Dec 27 '05 #7

ur***********@e nlight.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

Dec 29 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
29520
by: Steve Morrell | last post by:
Hi there, I'm trying to write a piece of sql to set an Oracle sequence's nextval to a number specified my the max value in a set of columns. The sequence is populating these columns, so I want to give it a kick if something goes wrong so itwon't try to duplicate numbers. I'm creating the sequence with create sequence my seq
5
7507
by: ratu | last post by:
I'd like to use a stored procedure to insert large amounts of records into a table. My field A should be filled with a given range of numbers. I do the following ... but I'm sure there is a better (faster) way: select @start = max(A) from tbl where B = 'test1' and C = 'test2' while @start <= 500000 begin insert into tbl (A, B, C) values (@start, 'test1', test2')
1
5926
by: Michael | last post by:
I want to insert a row into an ITEM table if certain SKUs are inserted. There are two triggers where each looks for a particular SKU and inserts the appropriate matching row in the same table. However when a record is inserted, the following error message occurs: Under DB2 UDB v7.2 SQL0101N The statement is too long or too complex. LINE NUMBER=2. SQLSTATE=54001 Under DB2 UDB v8.1
6
3337
by: Neil Zanella | last post by:
Hello, I would like to know what the C standards (and in particular the C99 standard) have to say about union initializers with regards to the following code snippet (which compiles fine under gcc 3.2.2 but does not produce the expected results, the expected results being the ones annotated in the comments in the code): #include <stdlib.h>
0
1352
by: S.Tobias | last post by:
Quote from 6.5.2.3 Structure and union members, Examle 3: The following is not a valid fragment (because the union type is not visible within function f): struct t1 { int m; }; struct t2 { int m; }; int f(struct t1 * p1, struct t2 * p2) { if (p1->m < 0) p2->m = -p2->m;
4
4507
by: Sim Zacks | last post by:
I am in the process of converting an existing database to PostGreSQL and wrote a generic script to update all of the sequences as they default at 1. I thought it would be useful to other people who are converting their databases. If anyone can write this script in using plpythonu, I would love to see how it is done. create or replace function UpdateSequences() returns varchar(50) as $$
1
2322
by: aarklon | last post by:
Hi folks, Recently i was reading the book C an advanced introduction by narain gehani, in page no:236 it is written as If a union contains several structures with a common initial sequence, then members of this sequence are guaranteed to have the same values and they can be
7
4273
by: aj | last post by:
DB2 LUW 8.2 FP5 (or is it 8.1 FP11?) I am CALLing a stored procedure from a trigger in order to maintain a column-level audit trail. Not only do I need to store a record of the INSERT, but also the initial values of all columns as of the INSERT, in the form of UPDATEs w/ NULL old values. I pass (amongst other things) a table name and a primary key to the SP and use dynamic sql and some syscat.columns magic to query the table and get...
0
2075
by: cngtx | last post by:
Hi, I could not figure out why I am getting the following error when I initiated the Insert into the mainframe DB2 table from a server. I have created the trigger and the stored procedure (as shown below). If I insert a row into the table using batch sql job on mainframe, the stored procedure is triggered by the db2 trigger successfully after a row is inserted into the table. However, if I do the same insert through front-end apps using a...
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10875
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10566
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10272
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9401
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5669
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4474
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4040
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.