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

[Dave Sisk]

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

[Rhino]

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

[Dave Sisk]

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

[Kent Milligan]

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)

[Dave Sisk]

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)

[Karl Hanson]

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