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...surel y 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); 5 11558
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.goog le.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...surel y 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);
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...a lthough 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******** ************@ne ws20.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.goog le.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...surel y 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);
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)
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...howev er, 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)
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...a lthough 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******** ************@ne ws20.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.go ogle.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...surel y 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: roberto |
last post by:
Hello,
I'm trying to retrieve some records in my web application using a jdbc
Statement like this:
statement =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
resultSet = statement.executeQuery(...);
with DB2 v8.2 EE that has a connection to an iSeries v5.3 and JBoss as
application server.
|
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 version is Java officially
supported?
Thank you
|
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
GET_INVENTORY_SEQUENCE in BPCSAX_PRD not created. Cause . . . . . :
SQL procedure, function, or trigger GET_INVENTORY_SEQUENCE in
BPCSAX_PRD was not created. The compile was not successful. SQL creates
an SQL procedure, function, or trigger as a C program...
|
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 databases.
The problem we seem to be having is after I am all done with my queries
and I close out of everything there are still jobs (QZDAOSINIT?) sitting
idle for my profile.
My web application can hit our AS/400 hundreds of times at once...
|
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 iSeries Access ODBC driver to connect to an iSeries (i5/OS, V5R4) midrange box. I've used this driver and a similar DSN in the past with no problems.
When an attempt to open a connection is made, an exception is thrown with the following message -
| |
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 installed it, but
apparently I need an additional product, "DB Connect" to actually make a
connection. "DB connect" says that it will only display tables with a type
of "T", and as far as I know, we are using files, not tables on the 400.
Can...
|
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 not see his query, but what are the statement size limits? I
thought they would be so large that this should not happen in a
practical situation.
|
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
files on an iSeries (AS/400, System i5, or whatever IBM calls it
today), version v5r4, and would like to permanently migrate them to a
DB2 UDB for Linux v8 system. All of these files are used by native
iSeries programs (written mostly in CL and...
|
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 “MODIFIES SQL DATA”
could be changed to “READS SQL DATA” – but would it give much/any
performance boost?
CREATE PROCEDURE APPGENERIC.GETACTIVEITEMSKUS9_ORIG (
IN IN_COMPANY VARCHAR(3) ,
|
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...
|
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,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |