Solved

DB2 on AIX:  Error when creating trigger that calls a stored procedure

Posted on 2004-10-19
3
759 Views
Last Modified: 2008-02-26
I am working with DB2 8.1 on AIX 5.2.  I am trying to call a stored procedure from a trigger.  The trigger is written in SQL.  The stored procedure is declared in SQL and defined as an external routine in C.  The declarations are as follows:

create procedure testschema.proc_test (in chat_id char(96), in in_clob clob)
dynamic result sets 0
language c
parameter style general
fenced not threadsafe
modifies sql data
program type sub
external name 'proc_test!proc_test'@

create trigger testschema.proc_test
after
insert on testschema.w_chat_transcript
referencing new as n
for each row
begin atomic
  declare rs integer default 0;
  call testschema.proc_test (n.chat_id, n.transcript);
  get diagnostics rs = return_status;
  values (case when rs < 0 then raise_error ('70001', 'proc call failed'));
end@

I can catalog the stored proc just fine but when I go to catalog the trigger I get the following error:
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "call" was found following "integer default 0;
".  Expected tokens may include:  "<compound_return>".  LINE NUMBER=8.  
SQLSTATE=42601

I have looked at many examples and they are all using the 'call' statement just as I am.  Any insight as to why I am seeing this error?
0
Comment
Question by:PJarosak
3 Comments
 

Author Comment

by:PJarosak
ID: 12364293
I am answering my own question.  DB2 8.2 is required to call a stored procedure from a trigger.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12591876
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now