473,581 Members | 2,480 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

V5R2 DB2/UDB iSeries: can a system trigger call a SQL stored proc?

I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined
a SQL stored proc, and the trigger is set to call this SP. I've posted the
simplified source below. I can manually call the stored proc, and the
external trigger is created without any errors. However, when I do an
insert into the table in question, the trigger returns an error (posted
below). I believe my problem is either 1) I'm not accepting the trigger
buffer contents correctly in the stored proc, or 2) you simply can't have an
external trigger call a SQL stored proc. (I don't believe #2 is the
problem, based on the docs, but I could be wrong.) I believe #1 is the
problem, but I can't find any example in the docs or any redbooks that show
how to have a system trigger call a SQL stored proc. What I'm actually
after is the RRN that is passed in inside the trigger buffer...that's the
piece I really need to get!

If #1 is the problem, please point me to an example, or make any suggestions
that come to mind. If #2 is the problem, then I'd imagine that I can wrap
the SQL stored proc in a CL program or RPG stub. Essentially, the trigger
would call the CL or RPG program, which would call the SQL stored proc.

I'm posting the simplified source below. If anyone can advise me or point
me to example, I would appreciate it very much!

Thx in advance,
Dave

Tables:
CREATE TABLE emp
(empnum DECIMAL(10,0),
empname VARCHAR(50));

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empnum);
CREATE TABLE trigbuf (trigbuf VARCHAR(8000), buflength varchar(10));

SQL stored procedure:

CREATE PROCEDURE updchgq2
(IN in_triggerbuffe r VARCHAR(8000),
IN in_length VARCHAR(10))
LANGUAGE SQL MODIFIES SQL DATA
BEGIN
INSERT INTO trigbuf values(in_trigg erbuffer, in_length);
END

I modeled the input parameters that the stored proc expects roughly on the e
xample of a CL trigger I found in the IBM redbook on iSeries Stored Procs,
UDF's, and Triggers.

External trigger:

ADDPFTRG FILE(EMP) TRGTIME(*AFTER) TRGEVENT(*INSER T) PGM(UPDCHGQ2)
insert into emp values(1,'Test' ) returns this error message:

CEE9901 - Application error. MCH3601 unmonitored by UPDCHGQ2 at statement
0000000007, instruction X'0000'.

SQL stored procs get compiled into ILE C program objects on the iSeries, so
I'm not sure how relevant this error message is.
Nov 12 '05 #1
0 7131

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

Similar topics

1
4329
by: Jen S | last post by:
I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF (@@ERROR <> 0) BEGIN ROLLBACK TRANSACTION; RAISERROR('An error occurred in the stored proc.', 16, 1);
4
18281
by: Dave Sisk | last post by:
Hi folks: We have a script containing DDL statements to create tables and indexes, etc. On DB2 for Unix, I'd do a "db2 -tvf script_name.sql -z logfile_name.log", but I don't think that works on the iSeries. I've found in the docs that you can run DDL scripts from a client application called "iSeries Navigator". I was wondering if there's...
5
11533
by: Dave Sisk | last post by:
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);
0
1756
by: Dave Sisk | last post by:
Hi Folks: I've got this scenario: User table/file....external triggers declared for *AFTER *INSERT, *AFTER *UPDATE, and *BEFORE *DELETE. The 3 external triggers call a SQLRPG ILE stub program which catches the trigger buffer and drops it into a data structure, then the SQLRPG ILE program calls a SQL stored proc with the right parameter...
18
5959
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query against a different table (that uses the LIKE predicate) but cannot get around the SQL0132 error . I have tried the hex notation after the LIKE such...
4
4172
by: Alexander Pope | last post by:
I am using db2 udb v8.2 AIX I have created trigger, however I am not confident it meets industry standards. If I make it fail, I cant tell from the message where it is failing. what can I add to trap the errors properly? CREATE TRIGGER myschema.t1_upd_t AFTER UPDATE OF dt ON myschema.t1 REFERENCING NEW AS N
0
1328
by: Scott | last post by:
Has anyone setup DB2 8.1 (fix patch 7) on Windows 2000 to be a DRDA server? I want to have an iSeries with OS400 V5R2 act as a DRDA client to an 8.1 DB2 UDB database hosted on Windows and so it can access the data stored on the windows database. What are the steps to set this up? Also, is this the best way to go about accessing a windows...
2
1849
by: Ken Sturgeon | last post by:
Is it possible to execute an iSeries program from a VB.Net application? Where might I find some reference material that would get me started?
3
6190
by: yinzara | last post by:
I have the following trigger that calls a DB2 stored procedure: DROP TRIGGER GGWU.TRI_A_MULTI_PROP@ CREATE TRIGGER GGWU.TRI_A_MULTI_PROP AFTER INSERT ON GGWU.MULTIPLIER_PROPERTY REFERENCING NEW AS POST FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL GGWU.PKG_MULT_PROP_INSERT(POST.C_PROPERTY_CODE,POST.I_MULTIPLIER_ID); END@
0
7857
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...
0
7788
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...
1
7890
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...
0
8163
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...
0
6545
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...
0
3799
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...
0
3813
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1397
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1127
bsmnconsultancy
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...

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.