473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

iSeries DB2/UDB V5R4 - Getting RRN in SQL Trigger?

Hey folks:

I'm trying to do this:

CREATE TRIGGER datawhse.emp_ti
AFTER INSERT ON emp
REFERENCING NEW AS n
FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE v_rrn DECIMAL(15,0);
SET v_rrn = n.RRN(datawhse.emp);
CALL UPDATE_CHGQUEUE('EMP', v_rrn, 'I');
END

Here's the error message I get on creation: SQL5001 - Column
qualifier or table EMP undefined.

It's complaining about this line: SET v_rrn = n.RRN(datawhse.emp);
If I reference n.empnum or n.empname, there is no error. It only
happens when trying to do a correlated reference on the RRN.

Is the Relative Record Number (RRN) of the changing row visible to a
SQL trigger? I know it's visible to an external trigger via the
trigger buffer, but I haven't found any examples in the docs anywhere
that show how to get at it from a SQL Trigger...surely there's a way
to do it?

The obvious answer here would be to use the keys of any unique logical
files (we're attaching triggers to system files rather than SQL
tables, although they look the same), but not every file/table has the
equivalent of a unique key.

Are there any other options (short of UK/PK) for uniquely identifying
a row on an AS/400 V5R4 that is visible to a SQL trigger?

Any suggestions here would be thoroughly appreciated!

Thx,
Dave
For reference, the table DDL:
CREATE TABLE emp
(empnum DECIMAL(10,0),
empname VARCHAR(50));
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empnum);
Nov 12 '05 #1
5 11481
I have *very* little experience with AS/400 so it may have different rules
that other platforms. However, the platforms I have used - OS/390, VM,
Windows, Linux, and AIX - normally update rows via a primary key or unique
key. In fact, you could argue that the main reasons for *HAVING* primary and
unique keys was to ensure that an update only affected a specific row.

It's possible that AS/400 allows an RRN to work in AS/400 but I'm curious
why you are eager to avoid primary/unique keys?

Rhino

"Dave Sisk" <da******@ipass.net> wrote in message
news:eb*************************@posting.google.co m...
Hey folks:

I'm trying to do this:

CREATE TRIGGER datawhse.emp_ti
AFTER INSERT ON emp
REFERENCING NEW AS n
FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE v_rrn DECIMAL(15,0);
SET v_rrn = n.RRN(datawhse.emp);
CALL UPDATE_CHGQUEUE('EMP', v_rrn, 'I');
END

Here's the error message I get on creation: SQL5001 - Column
qualifier or table EMP undefined.

It's complaining about this line: SET v_rrn = n.RRN(datawhse.emp);
If I reference n.empnum or n.empname, there is no error. It only
happens when trying to do a correlated reference on the RRN.

Is the Relative Record Number (RRN) of the changing row visible to a
SQL trigger? I know it's visible to an external trigger via the
trigger buffer, but I haven't found any examples in the docs anywhere
that show how to get at it from a SQL Trigger...surely there's a way
to do it?

The obvious answer here would be to use the keys of any unique logical
files (we're attaching triggers to system files rather than SQL
tables, although they look the same), but not every file/table has the
equivalent of a unique key.

Are there any other options (short of UK/PK) for uniquely identifying
a row on an AS/400 V5R4 that is visible to a SQL trigger?

Any suggestions here would be thoroughly appreciated!

Thx,
Dave
For reference, the table DDL:
CREATE TABLE emp
(empnum DECIMAL(10,0),
empname VARCHAR(50));
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empnum);

Nov 12 '05 #2
Good question. I'm building a custom change detection piece for an ETL
process for this particular client, and there's a huge number of
files/tables. I'd like to use almost exactly the same triggers on each file
(one line will be different) so that I can generate them from a template.
The source applications are done as system applications rather than SQL
applications, meaning they use unkeyed physical files and keyed logical
files rather than "tables" with primary key and unique key
constraints...although a unique logical file does closely resemble a unique
key constraint, I don't believe there are data dictionary entries for key
fields in a logical file (I might be wrong). Regardless, the design is such
that all changed rows are logged into a single queue table by a single
stored proc....I want a consistent single-column method to uniquely identify
the changed row in any file (the queue table's PK is filename + rrn). We're
only recording the fact that a row has changed...what exactly the change was
will be negotiated at transfer time...this approach is called "net change
compression".

It looks like one possibility is to use system triggers rather than SQL
triggers (the RRN is passed into a system trigger's buffer), but I'd really
like to keep all the pieces of this process in SQL-land so it'll be easier
for future folks to maintain....

Thx,
Dave
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message
news:tT********************@news20.bellglobal.com. ..
I have *very* little experience with AS/400 so it may have different rules
that other platforms. However, the platforms I have used - OS/390, VM,
Windows, Linux, and AIX - normally update rows via a primary key or unique
key. In fact, you could argue that the main reasons for *HAVING* primary and unique keys was to ensure that an update only affected a specific row.

It's possible that AS/400 allows an RRN to work in AS/400 but I'm curious
why you are eager to avoid primary/unique keys?

Rhino

"Dave Sisk" <da******@ipass.net> wrote in message
news:eb*************************@posting.google.co m...
Hey folks:

I'm trying to do this:

CREATE TRIGGER datawhse.emp_ti
AFTER INSERT ON emp
REFERENCING NEW AS n
FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE v_rrn DECIMAL(15,0);
SET v_rrn = n.RRN(datawhse.emp);
CALL UPDATE_CHGQUEUE('EMP', v_rrn, 'I');
END

Here's the error message I get on creation: SQL5001 - Column
qualifier or table EMP undefined.

It's complaining about this line: SET v_rrn = n.RRN(datawhse.emp);
If I reference n.empnum or n.empname, there is no error. It only
happens when trying to do a correlated reference on the RRN.

Is the Relative Record Number (RRN) of the changing row visible to a
SQL trigger? I know it's visible to an external trigger via the
trigger buffer, but I haven't found any examples in the docs anywhere
that show how to get at it from a SQL Trigger...surely there's a way
to do it?

The obvious answer here would be to use the keys of any unique logical
files (we're attaching triggers to system files rather than SQL
tables, although they look the same), but not every file/table has the
equivalent of a unique key.

Are there any other options (short of UK/PK) for uniquely identifying
a row on an AS/400 V5R4 that is visible to a SQL trigger?

Any suggestions here would be thoroughly appreciated!

Thx,
Dave
For reference, the table DDL:
CREATE TABLE emp
(empnum DECIMAL(10,0),
empname VARCHAR(50));
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empnum);


Nov 12 '05 #3
Which release are you really using, V4R5 or V5R1, V5R4 does not exist?

The key information for keyed logical files, here's an example:
ibm.com/servers/eserver/iseries/db2/indexlist.htm

I don't think that RRN is available to SQL Triggers, plus the RRN for a row can
change.

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)
Nov 12 '05 #4
Ooops...typo...the O/S release is V5R2. Thanks for the post. As far as I
can tell, the RRN isn't available to the SQL trigger. Sounds hard to
believe (especially since it's available to a system trigger), but it
appears to be fact. We've decided to use system or external triggers rather
than SQL triggers to get around this problem...however, I'm having a new
problem now, which I'll post a new thread about.

Yes, I'm aware that RRN's can change....for instance, on a restore or after
a reorg. However, I think we can work within those limitations...
"Kent Milligan" <km***@us.eye-bee-m.com> wrote in message
news:40***************@us.eye-bee-m.com...
Which release are you really using, V4R5 or V5R1, V5R4 does not exist?

The key information for keyed logical files, here's an example:
ibm.com/servers/eserver/iseries/db2/indexlist.htm

I don't think that RRN is available to SQL Triggers, plus the RRN for a row can change.

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)

Nov 12 '05 #5
Dave Sisk wrote:
Good question. I'm building a custom change detection piece for an ETL
process for this particular client, and there's a huge number of
files/tables. I'd like to use almost exactly the same triggers on each file
(one line will be different) so that I can generate them from a template.
The source applications are done as system applications rather than SQL
applications, meaning they use unkeyed physical files and keyed logical
files rather than "tables" with primary key and unique key
constraints...although a unique logical file does closely resemble a unique
key constraint, I don't believe there are data dictionary entries for key
fields in a logical file (I might be wrong). Regardless, the design is such
that all changed rows are logged into a single queue table by a single
stored proc....I want a consistent single-column method to uniquely identify
the changed row in any file (the queue table's PK is filename + rrn). We're
only recording the fact that a row has changed...what exactly the change was
will be negotiated at transfer time...this approach is called "net change
compression".

It looks like one possibility is to use system triggers rather than SQL
triggers (the RRN is passed into a system trigger's buffer), but I'd really
like to keep all the pieces of this process in SQL-land so it'll be easier
for future folks to maintain....

Thx,
Dave

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message
news:tT********************@news20.bellglobal.com. ..
I have *very* little experience with AS/400 so it may have different rules
that other platforms. However, the platforms I have used - OS/390, VM,
Windows, Linux, and AIX - normally update rows via a primary key or unique
key. In fact, you could argue that the main reasons for *HAVING* primary


and
unique keys was to ensure that an update only affected a specific row.

It's possible that AS/400 allows an RRN to work in AS/400 but I'm curious
why you are eager to avoid primary/unique keys?

"Dave Sisk" <da******@ipass.net> wrote in message
news:eb*************************@posting.google. com...
Hey folks:

I'm trying to do this:

CREATE TRIGGER datawhse.emp_ti
AFTER INSERT ON emp
REFERENCING NEW AS n
FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE v_rrn DECIMAL(15,0);
SET v_rrn = n.RRN(datawhse.emp);
CALL UPDATE_CHGQUEUE('EMP', v_rrn, 'I');
END

Here's the error message I get on creation: SQL5001 - Column
qualifier or table EMP undefined.

It's complaining about this line: SET v_rrn = n.RRN(datawhse.emp);
If I reference n.empnum or n.empname, there is no error. It only
happens when trying to do a correlated reference on the RRN.

Is the Relative Record Number (RRN) of the changing row visible to a
SQL trigger? I know it's visible to an external trigger via the
trigger buffer, but I haven't found any examples in the docs anywhere
that show how to get at it from a SQL Trigger...surely there's a way
to do it?

The obvious answer here would be to use the keys of any unique logical
files (we're attaching triggers to system files rather than SQL
tables, although they look the same), but not every file/table has the
equivalent of a unique key.

Are there any other options (short of UK/PK) for uniquely identifying
a row on an AS/400 V5R4 that is visible to a SQL trigger?
For reference, the table DDL:
CREATE TABLE emp
(empnum DECIMAL(10,0),
empname VARCHAR(50));
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empnum);




Here is some info passed along from an iSeries SQL expert:

The argument of the RRN function is a table designator. Only update,
delete, and select statements have a table designator.
It might be possible to add some capability so that another use would be
allowed in a trigger, but it's not something I would recommend.

--
Karl Hanson
Nov 12 '05 #6

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

Similar topics

2
by: roberto | last post by:
Hello, I'm trying to retrieve some records in my web application using a jdbc Statement like this: statement =...
2
by: chmmr | last post by:
Hello, I would like to ask if there are any differences between DB2 in these versions (e.g. support of triggers, Java stored procedures and such). Also, does anyone know from which DB2 AS/400...
0
by: Johan Neidenmark | last post by:
When i try to run this SQL statements in iSeries Access for windows (against my customers db2) i get: SQL State: 42904 Vendor Code: -7032 Message: SQL procedure, function, or trigger...
2
by: scase | last post by:
I need some trying to figure out what is happening on our AS/400. I am not an AS/400 programmer, but I am using ASP.NET (VB) and the IBM.Data.DB2.iSeries control for .NET to access our DB2...
6
seligerasmus
by: seligerasmus | last post by:
Greetings, I'm having a bit of trouble with a small web service I've been writing, specifically with the connectivity aspect of the service. My scenario is as follows : I'm using the IBM...
6
by: Al G | last post by:
Can someone tell me what I need to get connected to our AS400? I am trying to write an app in VS2005(Data source, Gridview) that requires data from files on our AS400. I've downloaded DB2, and...
2
by: --CELKO-- | last post by:
We just got a "SQL Statement too long or complex" error on the ISERIES. James was able to get a compile after replacing some full table names with (much shorter) correlation names, etc. I have...
0
by: c0d3lib | last post by:
I am attempting to implement, what I believe to be, an unusual scenario, and am hoping to find someone that has experience with something similar. I currently have a series of physical and logical...
2
by: jmuehe | last post by:
Hello, What are the optimal settings to use in a CREATE PROCEDURE – when creating a SQL procedure on the iSeries? To read data? To update? Here is a pretty standard one of ours. I know the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.