I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NE W_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATU S;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70 001','UPDATE FAILED'));
END
When we try to create it thruogh erwin data modeler we get the
following error
[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQL error '-20100' in the message file on this
workstation. The error was returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059
Execution Failed!
If i remove the declare,diagnos tic & value statement & just keep the
call statement then it works.
Do you have any clue as to why this might be happening.
5  6496  sh**********@gm ail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NE W_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATU S;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70 001','UPDATE FAILED'));
END
When we try to create it thruogh erwin data modeler we get the
following error
[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQL error '-20100' in the message file on this
workstation. The error was returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059
Execution Failed!
If i remove the declare,diagnos tic & value statement & just keep the
call statement then it works.
Do you have any clue as to why this might be happening.
Which version and platform? Note that there is no VALUES statement on
zOS....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
On Mar 28, 10:24 am, shethshee...@gm ail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NE W_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATU S;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70 001','UPDATE FAILED'));
END
By looking SQL Reference manual, followings may be spec. of DB2 for z/
OS.
DB2 for z/OS supports VALUES in trigger at least V6 or later.
But, it doesn't suppot "GET DIAGNOSTICS RS=RETURN_STATU S" on V7.
V7 supports only "GET DIAGNOSTICS SQL-variable=ROW_CO UNT".
Although, V8 supports "GET DIAGNOSTICS SQL-
variable=DB2_RE TURN_STATUS", GET DIAGNOSTICS statement can't be used
in TRIGGER body.
DECLARE statement is also not supported in TRIGGER body.
I think that it is worth to try to use RAISE_ERROR in OMNIP.SPSOICL0
Procedure. sh**********@gm ail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NE W_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATU S;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70 001','UPDATE FAILED'));
END
When we try to create it thruogh erwin data modeler we get the
following error
[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQL error '-20100' in the message file on this
workstation. The error was returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059
The "original tokens" say that you got a SQL0104. I believe that it chokes
on the INTEGER keyword. Try just INT instead. (I had such a problem once,
but I don't remember if it was in a similar context.)
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
On Mar 27, 10:58 pm, "Tonkuma" <tonk...@jp.ibm .comwrote:
On Mar 28, 10:24 am, shethshee...@gm ail.com wrote:I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NE W_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATU S;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70 001','UPDATE FAILED'));
END
By looking SQL Reference manual, followings may be spec. ofDB2for z/
OS.DB2for z/OS supports VALUES in trigger at least V6 or later.
But, it doesn't suppot "GET DIAGNOSTICS RS=RETURN_STATU S" on V7.
V7 supports only "GET DIAGNOSTICS SQL-variable=ROW_CO UNT".
Although, V8 supports "GET DIAGNOSTICS SQL-variable=DB2_RE TURN_STATUS", GET DIAGNOSTICS statement can't be used
in TRIGGER body.
DECLARE statement is also not supported in TRIGGER body.
I think that it is worth to try to use RAISE_ERROR in OMNIP.SPSOICL0
Procedure.
In this case the ability of the trigger to handle error in z/os is
severly restricted.
can you let me know what would be the syntax for the call statement
within the trigger with 'out parameters'.Doe s any variable have to be
defined in the trigger for capturing the out parameter.
On Mar 27, 7:08 pm, Serge Rielau <srie...@ca.ibm .comwrote:
shethshee...@gm ail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NE W_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATU S;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70 001','UPDATE FAILED'));
END
When we try to create it thruogh erwin data modeler we get the
followingerror
[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQLerror'-20100' in the message file on this
workstation. Theerrorwas returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059
Execution Failed!
If i remove the declare,diagnos tic & value statement & just keep the
call statement then it works.
Do you have any clue as to why this might be happening.
Which version and platform? Note that there is no VALUES statement on
zOS....
Cheers
Serge
--
Serge RielauDB2Soluti ons Development
IBM Toronto Lab- Hide quoted text -
- Show quoted text -
I am working on db2 version 8 new function mode on z/os v1.8 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jonathan |
last post by:
I'm puzzled by Python's behavior when binding local variables which
are introduced within exec() or execfile() statements. First, consider
this simple Python program:
# main.py
def f() :
x = 1
print "x:", x
f()
|
by: Oliver Corona |
last post by:
I am wondering if anyone has any insights on the performance benefit (or
detriment) of declaring local variables instead of referencing members.
Is allocating memory for a new variable more efficient than repeatedly
referencing the member in a loop?
Maybe using a string isn't the best example, but hopefully you get the
idea!
* example (referencing member):
|
by: fred |
last post by:
I don't know if I'm doing this correctly. I have a little programming
experience in python, c++ and some others but this is my first time with
javascript. I'm trying have my website detect the user's browser and
assign a variable with different items if it's Internet Explorer. My
code works with firefox, opera and others but not IE.
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled...
|
by: Paul Reddin |
last post by:
Hi,
Having just tested our database on V8.2 we get the following apparent
incompatibility.
A Trigger conatains the following line
CREATE TRIGGER JABS.AU_CHNG_ENQUIRYITM
AFTER UPDATE OF
..
|
by: ross.oneill |
last post by:
Hi,
I am having trouble with a simple task of declaring a variable. Is
this possible?
Here is what I want to do.
DECLARE start_date date;
DECLARE end_date date;
SET start_date = '2005-01-01'
| | |
by: ColinWard |
last post by:
Hi guys.
I have a question about declaring variables. I do a lot of re-querying
of controls in my database and I use the Set statement with a variable
set to the name of the control to tell the program which control to
requery. This makes it easy to requery controls on a different form.
However, up until today, I was dimming a new local variable for each
control I wanted to deal with ( E.G. Dim cbxctl as control) and then
setting that...
|
by: bhavin.vyas |
last post by:
Friends,
I would just like to know that why SQL Server doen't allow us to define
a text data type local variable while creating trigger?
I tried creating a text variable in a trigger as a local variable and
it raises error.
"Implicit conversion from data type text to nvarchar is not allowed.
Use the CONVERT function to run this query".
|
by: Mark A. Sam |
last post by:
Hello,
I am using Visual Web Developer 2005 Express. I want to declare a varible,
using Visual Basic as the language and can't get anywhere. For example
Public Test1 as String
I'll get en error saying to change Public to Dim. When I do, it will say
that Test1 is an unused local variable.
|
by: Hari Sekhon |
last post by:
I've got some code as follows:
import re
re_regexname = re.compile('abc')
......
...... various function defs
......
def func1():
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 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...
| |
