472,779 Members | 1,772 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,779 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 11415
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...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

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.