DB2 v7 zos - trigger question

James Rohal

DB2 v7 zos - trigger question
We have a situation where we have two data models, the old data model will
eventually be replace by the new data model.


Until the old programs using the old model are phased out we need to keep
both models in sync. My thought is to use triggers and stored procedures.
When a row is inserted/update/delete into the old table fire a trigger to
insert/update/delete the row in the table or tables in the new data model.
Likewise when a column is inserted/update/delete into a table in the new
data model fire a trigger to insert/update/delete into the table in the old
data model.

Because of the redesign of the data model the trigger will fire a stored
procedure to determine how/which table in the new/old model needs to be
inserted/update/deleted from. As you can tell we have a situation where
'looping triggers' exist. It would be easier to make these after triggers
(before triggers can't issue update sql), but with after triggers there is
no option to prevent cascading of triggers. Does anyone have any ideas on
how to prevent cascading in 'after triggers'?


My thought is to use 'current path' or 'current schema' in the 'when'
statement to determine if the insert or update is because of a fired
trigger. I'm not sure what these values will contain when the insert is
because of a trigger. Anyone know?


James Rohal
DB2 Data Base Administrator
Office Phone: 614-331-8113
Fax: 614-331-5860
Cell phone: 614-374-2793
Email: [login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: DB2 v7 zos - trigger question
(in response to James Rohal)
Don't think you'll be able to use those. We had a similar thought a
couple years back(using current path), but the documentation and how the
trigger actually works are opposite, I had opened a problem with IBM at
the time and they agreed and said the doc was in error, which wasn't
what I wanted to hear.
As far as preventing the after triggers, perhaps you could pass in
something in one of the columns to help you identify it came from a
trigger rather than application code? That way you when clause could
discount those rows being inserted/updated/deleted by another trigger.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 10/12/05 10:38:57 AM >>>
We have a situation where we have two data models, the old data model
will
eventually be replace by the new data model.


Until the old programs using the old model are phased out we need to
keep
both models in sync. My thought is to use triggers and stored
procedures.
When a row is inserted/update/delete into the old table fire a trigger
to
insert/update/delete the row in the table or tables in the new data
model.
Likewise when a column is inserted/update/delete into a table in the
new
data model fire a trigger to insert/update/delete into the table in the
old
data model.

Because of the redesign of the data model the trigger will fire a
stored
procedure to determine how/which table in the new/old model needs to
be
inserted/update/deleted from. As you can tell we have a situation
where
'looping triggers' exist. It would be easier to make these after
triggers
(before triggers can't issue update sql), but with after triggers there
is
no option to prevent cascading of triggers. Does anyone have any ideas
on
how to prevent cascading in 'after triggers'?


My thought is to use 'current path' or 'current schema' in the 'when'
statement to determine if the insert or update is because of a fired
trigger. I'm not sure what these values will contain when the insert
is
because of a trigger. Anyone know?


James Rohal
DB2 Data Base Administrator
Office Phone: 614-331-8113
Fax: 614-331-5860
Cell phone: 614-374-2793
Email: [login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm


"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: DB2 v7 zos - trigger question
(in response to Dave Nance)
Jim - I understand the issue and Dave - I agree current path will not work.
I think there is way, however (I am hoping!!). It must be value-based.

I think the triggers need to have smart logic that are based on an
existsence check. This will complicate the logic and cannot prevent the
triogger from firing but will resolve the endless loop.

As an example, let's consider inserts only. Let's say the tables are oldtab
and newtab. On oldtab, the insert trigger will say:

insert into newtab select ... from oldtab where not exists (select * from
newtab where newtab.keys = oldtab.keys)...

Similarly for update and delete. This means you break the chain after 1
round.

Have not done it but would lke to see the code if it works (or does not).

One other thought - I think by allowing updates in both places, you may be
inviting trouble. Conflict resolution can be an issue. For example, oldtab
salary is udpated and newtab address is updated. When threy try to update
each other, will you know the time sequence? Can be done but could be
tricky. Remmeber there may be a delay in trigger execution (especially if
SP needs to be scheduled, WLM is involved etc etc).

Thanks,
Suresh


>From: David Nance <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 v7 zos - trigger question
>Date: Wed, 12 Oct 2005 08:55:47 -0600
>
>Don't think you'll be able to use those. We had a similar thought a
>couple years back(using current path), but the documentation and how the
>trigger actually works are opposite, I had opened a problem with IBM at
>the time and they agreed and said the doc was in error, which wasn't
>what I wanted to hear.
> As far as preventing the after triggers, perhaps you could pass in
>something in one of the columns to help you identify it came from a
>trigger rather than application code? That way you when clause could
>discount those rows being inserted/updated/deleted by another trigger.
>
>Dave Nance
>First Health Services, Corp.
>(804)527-6841
>
>
> >>> [login to unmask email] 10/12/05 10:38:57 AM >>>
>We have a situation where we have two data models, the old data model
>will
>eventually be replace by the new data model.
>
>
>Until the old programs using the old model are phased out we need to
>keep
>both models in sync. My thought is to use triggers and stored
>procedures.
>When a row is inserted/update/delete into the old table fire a trigger
>to
>insert/update/delete the row in the table or tables in the new data
>model.
>Likewise when a column is inserted/update/delete into a table in the
>new
>data model fire a trigger to insert/update/delete into the table in the
>old
>data model.
>
>Because of the redesign of the data model the trigger will fire a
>stored
>procedure to determine how/which table in the new/old model needs to
>be
>inserted/update/deleted from. As you can tell we have a situation
>where
>'looping triggers' exist. It would be easier to make these after
>triggers
>(before triggers can't issue update sql), but with after triggers there
>is
>no option to prevent cascading of triggers. Does anyone have any ideas
>on
>how to prevent cascading in 'after triggers'?
>
>
>My thought is to use 'current path' or 'current schema' in the 'when'
>statement to determine if the insert or update is because of a fired
>trigger. I'm not sure what these values will contain when the insert
>is
>because of a trigger. Anyone know?
>
>
>James Rohal
>DB2 Data Base Administrator
>Office Phone: 614-331-8113
>Fax: 614-331-5860
>Cell phone: 614-374-2793
>Email: [login to unmask email]
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm
>
>
>"MMS <firsthealth.com>" made the following annotations.
>------------------------------------------------------------------------------
>This message, including any attachments, is intended solely for the use of
>the named recipient(s) and may contain confidential and/or privileged
>information. Any unauthorized review, use, disclosure or distribution of
>this communication(s) is expressly prohibited. If you are not the intended
>recipient, please contact the sender by
>reply e-mail and destroy any and all copies of the original message.
>======
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: DB2 v7 zos - trigger question
(in response to Suresh Sane)
I can tell you that the existence check as you describe, does work. We
tried a similar situation using a not exists. The problem it was way too
costly. We ended up changing the application code instead to solve our
issue.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 10/12/05 12:39:56 PM >>>
Jim - I understand the issue and Dave - I agree current path will not
work.
I think there is way, however (I am hoping!!). It must be
value-based.

I think the triggers need to have smart logic that are based on an
existsence check. This will complicate the logic and cannot prevent
the
triogger from firing but will resolve the endless loop.

As an example, let's consider inserts only. Let's say the tables are
oldtab
and newtab. On oldtab, the insert trigger will say:

insert into newtab select ... from oldtab where not exists (select *
from
newtab where newtab.keys = oldtab.keys)...

Similarly for update and delete. This means you break the chain after
1
round.

Have not done it but would lke to see the code if it works (or does
not).

One other thought - I think by allowing updates in both places, you may
be
inviting trouble. Conflict resolution can be an issue. For example,
oldtab
salary is udpated and newtab address is updated. When threy try to
update
each other, will you know the time sequence? Can be done but could be

tricky. Remmeber there may be a delay in trigger execution (especially
if
SP needs to be scheduled, WLM is involved etc etc).

Thanks,
Suresh


>From: David Nance <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG
<[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 v7 zos - trigger question
>Date: Wed, 12 Oct 2005 08:55:47 -0600
>
>Don't think you'll be able to use those. We had a similar thought a
>couple years back(using current path), but the documentation and how
the
>trigger actually works are opposite, I had opened a problem with IBM
at
>the time and they agreed and said the doc was in error, which wasn't
>what I wanted to hear.
> As far as preventing the after triggers, perhaps you could pass
in
>something in one of the columns to help you identify it came from a
>trigger rather than application code? That way you when clause could
>discount those rows being inserted/updated/deleted by another
trigger.
>
>Dave Nance
>First Health Services, Corp.
>(804)527-6841
>
>
> >>> [login to unmask email] 10/12/05 10:38:57 AM >>>
>We have a situation where we have two data models, the old data model
>will
>eventually be replace by the new data model.
>
>
>Until the old programs using the old model are phased out we need to
>keep
>both models in sync. My thought is to use triggers and stored
>procedures.
>When a row is inserted/update/delete into the old table fire a
trigger
>to
>insert/update/delete the row in the table or tables in the new data
>model.
>Likewise when a column is inserted/update/delete into a table in the
>new
>data model fire a trigger to insert/update/delete into the table in
the
>old
>data model.
>
>Because of the redesign of the data model the trigger will fire a
>stored
>procedure to determine how/which table in the new/old model needs to
>be
>inserted/update/deleted from. As you can tell we have a situation
>where
>'looping triggers' exist. It would be easier to make these after
>triggers
>(before triggers can't issue update sql), but with after triggers
there
>is
>no option to prevent cascading of triggers. Does anyone have any
ideas
>on
>how to prevent cascading in 'after triggers'?
>
>
>My thought is to use 'current path' or 'current schema' in the 'when'
>statement to determine if the insert or update is because of a fired
>trigger. I'm not sure what these values will contain when the insert
>is
>because of a trigger. Anyone know?
>
>
>James Rohal
>DB2 Data Base Administrator
>Office Phone: 614-331-8113
>Fax: 614-331-5860
>Cell phone: 614-374-2793
>Email: [login to unmask email]
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm
>
>
>"MMS <firsthealth.com>" made the following annotations.
>------------------------------------------------------------------------------
>This message, including any attachments, is intended solely for the
use of
>the named recipient(s) and may contain confidential and/or privileged

>information. Any unauthorized review, use, disclosure or distribution
of
>this communication(s) is expressly prohibited. If you are not the
intended
>recipient, please contact the sender by
>reply e-mail and destroy any and all copies of the original message.
>======
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm


"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DB2 v7 zos - trigger question
(in response to Dave Nance)
Along the lines of the other suggestions there is a statement in the
manuals that, within the context of a given SQL statement, the same
timestamp value will be used for all current date/time/timestamp values.

So:
- add a last-updated timestamp column to all tables
- all triggers have a "when last-updated < current timestamp" condition
- before triggers are created with a "set last-updated = current timestamp"
statement
- after triggers are used to fire the requisite insert/update on the other
model.

When the process loops back, the when conditions on the original table's
before and after triggers will stop the loop proceeding.

However I have also seen some posts here that imply that, under some
conditions, DB2 will obtain a fresh "current timestamp". This would,
obviously, create a serious problem to this model. As would concurrent
updates coming from both models.

James Campbell

On Wed, 12 Oct 2005 10:38:57 -0400, [login to unmask email] wrote:

>We have a situation where we have two data models, the old data model will
>eventually be replace by the new data model.
>
>
>Until the old programs using the old model are phased out we need to keep
>both models in sync. My thought is to use triggers and stored procedures.
>When a row is inserted/update/delete into the old table fire a trigger to
>insert/update/delete the row in the table or tables in the new data model.
>Likewise when a column is inserted/update/delete into a table in the new
>data model fire a trigger to insert/update/delete into the table in the old
>data model.
>
>Because of the redesign of the data model the trigger will fire a stored
>procedure to determine how/which table in the new/old model needs to be
>inserted/update/deleted from. As you can tell we have a situation where
>'looping triggers' exist. It would be easier to make these after triggers
>(before triggers can't issue update sql), but with after triggers there is
>no option to prevent cascading of triggers. Does anyone have any ideas on
>how to prevent cascading in 'after triggers'?
>
>
>My thought is to use 'current path' or 'current schema' in the 'when'
>statement to determine if the insert or update is because of a fired
>trigger. I'm not sure what these values will contain when the insert is
>because of a trigger. Anyone know?
>
>
>James Rohal
>DB2 Data Base Administrator
>Office Phone: 614-331-8113
>Fax: 614-331-5860
>Cell phone: 614-374-2793
>Email: [login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Rohal

Re: DB2 v7 zos - trigger question
(in response to James Campbell)
I've added a last program updated column to each table (nullable with
default). Then whenever a trigger causes and insert we set the value of
this column to the trigger name.

In the 'When statement' we only execute the trigger when the last updated
column name is not the name of trigger. This seems to work, we need to
figure out how to handle the deletes though.

James Rohal
DB2 Data Base Administrator
Office Phone: 614-331-8113
Fax: 614-331-5860
Cell phone: 614-374-2793
Email: [login to unmask email]



James Campbell
<[login to unmask email]
NK.NET.AU> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] DB2 v7 zos - trigger
question

10/12/2005 06:44
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Along the lines of the other suggestions there is a statement in the
manuals that, within the context of a given SQL statement, the same
timestamp value will be used for all current date/time/timestamp values.

So:
- add a last-updated timestamp column to all tables
- all triggers have a "when last-updated < current timestamp" condition
- before triggers are created with a "set last-updated = current timestamp"
statement
- after triggers are used to fire the requisite insert/update on the other
model.

When the process loops back, the when conditions on the original table's
before and after triggers will stop the loop proceeding.

However I have also seen some posts here that imply that, under some
conditions, DB2 will obtain a fresh "current timestamp". This would,
obviously, create a serious problem to this model. As would concurrent
updates coming from both models.

James Campbell

On Wed, 12 Oct 2005 10:38:57 -0400, [login to unmask email] wrote:

>We have a situation where we have two data models, the old data model will
>eventually be replace by the new data model.
>
>
>Until the old programs using the old model are phased out we need to keep
>both models in sync. My thought is to use triggers and stored procedures.
>When a row is inserted/update/delete into the old table fire a trigger to
>insert/update/delete the row in the table or tables in the new data
model.
>Likewise when a column is inserted/update/delete into a table in the new
>data model fire a trigger to insert/update/delete into the table in the
old
>data model.
>
>Because of the redesign of the data model the trigger will fire a stored
>procedure to determine how/which table in the new/old model needs to be
>inserted/update/deleted from. As you can tell we have a situation where
>'looping triggers' exist. It would be easier to make these after triggers
>(before triggers can't issue update sql), but with after triggers there is
>no option to prevent cascading of triggers. Does anyone have any ideas on
>how to prevent cascading in 'after triggers'?
>
>
>My thought is to use 'current path' or 'current schema' in the 'when'
>statement to determine if the insert or update is because of a fired
>trigger. I'm not sure what these values will contain when the insert is
>because of a trigger. Anyone know?
>
>
>James Rohal
>DB2 Data Base Administrator
>Office Phone: 614-331-8113
>Fax: 614-331-5860
>Cell phone: 614-374-2793
>Email: [login to unmask email]

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

A. J. Zobjeck

Trigger
(in response to James Rohal)
I'm on DB2 V6 and I'm trying to create a trigger. I want to increment
the sequence and use the value for the access_id in the tti_faq_admin
table.
To ensure that the sequence does not get out of synch with the table id,
I want to create a trigger that gets/sets the sequence. Any help would
be great. Thanks


CREATE TRIGGER TTIFQADI
NO CASCADE BEFORE
INSERT ON DB2TEST.TTI_FAQ_ADMIN
REFERENCING NEW AS A
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET A.ACCESS_ID=(SELECT SEQ_NUM +1
FROM DB2TEST.TTI_SEQUENCE
WHERE OBJECT_NM='FAQ_ACCESS_ID');
UPDATE DB2TEST.TTI_SEQUENCE
SET SEQ_NUM =(SEQ_NUM+1)
WHERE OBJECT_NM='FAQ_ACCESS_ID';
END;
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER
: 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS SEQ_NUM,+
) -
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Trigger
(in response to A. J. Zobjeck)
Sorry, but you can't do Insert/Update/Delete in a before trigger


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Zobjeck, A. J.
Sent: 19 October 2005 16:26
To: [login to unmask email]
Subject: [DB2-L] Trigger

I'm on DB2 V6 and I'm trying to create a trigger. I want to increment
the sequence and use the value for the access_id in the tti_faq_admin
table.
To ensure that the sequence does not get out of synch with the table id,
I want to create a trigger that gets/sets the sequence. Any help would
be great. Thanks


CREATE TRIGGER TTIFQADI
NO CASCADE BEFORE
INSERT ON DB2TEST.TTI_FAQ_ADMIN
REFERENCING NEW AS A
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET A.ACCESS_ID=(SELECT SEQ_NUM +1
FROM DB2TEST.TTI_SEQUENCE
WHERE OBJECT_NM='FAQ_ACCESS_ID');
UPDATE DB2TEST.TTI_SEQUENCE
SET SEQ_NUM =(SEQ_NUM+1)
WHERE OBJECT_NM='FAQ_ACCESS_ID';
END;
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER
: 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS SEQ_NUM,+
) -
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

A. J. Zobjeck

Re: Trigger
(in response to Phil Grainger)
What about DB2 v7?

Thanks

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Wednesday, October 19, 2005 10:33 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Trigger

Sorry, but you can't do Insert/Update/Delete in a before trigger


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Zobjeck, A. J.
Sent: 19 October 2005 16:26
To: [login to unmask email]
Subject: [DB2-L] Trigger

I'm on DB2 V6 and I'm trying to create a trigger. I want to increment
the sequence and use the value for the access_id in the tti_faq_admin
table.
To ensure that the sequence does not get out of synch with the table id,
I want to create a trigger that gets/sets the sequence. Any help would
be great. Thanks


CREATE TRIGGER TTIFQADI
NO CASCADE BEFORE
INSERT ON DB2TEST.TTI_FAQ_ADMIN
REFERENCING NEW AS A
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET A.ACCESS_ID=(SELECT SEQ_NUM +1
FROM DB2TEST.TTI_SEQUENCE
WHERE OBJECT_NM='FAQ_ACCESS_ID');
UPDATE DB2TEST.TTI_SEQUENCE
SET SEQ_NUM =(SEQ_NUM+1)
WHERE OBJECT_NM='FAQ_ACCESS_ID';
END;
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER
: 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS SEQ_NUM,+
) -
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Trigger
(in response to A. J. Zobjeck)
Not V7, not V8 for sure. Not in Vnext as far as I know. I don't think this
will ever be allowed - the problem is more fundamental in nature. That is
why you have the "no cascade" clause. If allowed the chain of triggers
fired can lead to cycles and can be a real mess.

Wouldn't V8 sequence objects take care of this wihout needing the sequence
table?

Thanks,
Suresh

>From: "Zobjeck, A. J." <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] Trigger
>Date: Wed, 19 Oct 2005 11:51:42 -0500
>
>What about DB2 v7?
>
>Thanks
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
>Behalf Of Grainger, Phil
>Sent: Wednesday, October 19, 2005 10:33 AM
>To: [login to unmask email]
>Subject: Re: [DB2-L] Trigger
>
>Sorry, but you can't do Insert/Update/Delete in a before trigger
>
>
>Phil Grainger
>Computer Associates
>Product Manager, DB2
>Tel: +44 (0)161 928 9334
>Fax: +44 (0)161 941 3775
>Mobile: +44 (0)7970 125 752
>[login to unmask email]
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
>Behalf Of Zobjeck, A. J.
>Sent: 19 October 2005 16:26
>To: [login to unmask email]
>Subject: [DB2-L] Trigger
>
>I'm on DB2 V6 and I'm trying to create a trigger. I want to increment
>the sequence and use the value for the access_id in the tti_faq_admin
>table.
>To ensure that the sequence does not get out of synch with the table id,
>I want to create a trigger that gets/sets the sequence. Any help would
>be great. Thanks
>
>
>CREATE TRIGGER TTIFQADI
>NO CASCADE BEFORE
>INSERT ON DB2TEST.TTI_FAQ_ADMIN
>REFERENCING NEW AS A
>FOR EACH ROW MODE DB2SQL
>BEGIN ATOMIC
>SET A.ACCESS_ID=(SELECT SEQ_NUM +1
> FROM DB2TEST.TTI_SEQUENCE
> WHERE OBJECT_NM='FAQ_ACCESS_ID');
>UPDATE DB2TEST.TTI_SEQUENCE
> SET SEQ_NUM =(SEQ_NUM+1)
>WHERE OBJECT_NM='FAQ_ACCESS_ID';
>END;
>DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
> TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER
> : 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS SEQ_NUM,+
> ) -
>DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
>------------------------------------------------------------------------
>---------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences
>at http://conferences.idug.org/index.cfm
>
>------------------------------------------------------------------------
>---------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences
>at http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Trigger
(in response to Suresh Sane)
As I said, in a before trigger you cannot make changes to the database

Sorry

-----Original Message-----
From: DB2 Data Base Discussion List on behalf of Zobjeck, A. J.
Sent: Wed 19/10/2005 17:51
To: [login to unmask email]
Cc:
Subject: Re: [DB2-L] Trigger



What about DB2 v7?

Thanks

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Wednesday, October 19, 2005 10:33 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Trigger

Sorry, but you can't do Insert/Update/Delete in a before trigger


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Zobjeck, A. J.
Sent: 19 October 2005 16:26
To: [login to unmask email]
Subject: [DB2-L] Trigger

I'm on DB2 V6 and I'm trying to create a trigger. I want to increment
the sequence and use the value for the access_id in the tti_faq_admin
table.
To ensure that the sequence does not get out of synch with the table id,
I want to create a trigger that gets/sets the sequence. Any help would
be great. Thanks


CREATE TRIGGER TTIFQADI
NO CASCADE BEFORE
INSERT ON DB2TEST.TTI_FAQ_ADMIN
REFERENCING NEW AS A
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET A.ACCESS_ID=(SELECT SEQ_NUM +1
FROM DB2TEST.TTI_SEQUENCE
WHERE OBJECT_NM='FAQ_ACCESS_ID');
UPDATE DB2TEST.TTI_SEQUENCE
SET SEQ_NUM =(SEQ_NUM+1)
WHERE OBJECT_NM='FAQ_ACCESS_ID';
END;
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER
: 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS SEQ_NUM,+
) -
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



James Park

Trigger
(in response to Phil Grainger)
Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Tee

Re: Trigger
(in response to James Park)
Try changing the SQL delimiter in SPUFI defaults to something other than ; ,
say @

Chris

Chris Tee
DB2 DBA
Infrastructure Services & SAP Solutions (iS3)
Information Systems Europe
SONY Europe
Tel: +44 (0) 1256 82 8342

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
Sent: 29 November 2005 16:00
To: [login to unmask email]
Subject: [DB2-L] Trigger


Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

************************************************************************
The information contained in this message or any of its attachments may be confidential and is intended for the exclusive use of the addressee(s). Any disclosure, reproduction, distribution or other dissemination or use of this communication is strictly prohibited without the express permission of the sender. The views expressed in this email are those of the individual and not necessarily those of Sony or Sony affiliated companies. Sony email is for business use only.

This email and any response may be monitored by Sony to be in compliance with Sony’s global policies and standards

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Fazio

Re: Trigger
(in response to Chris Tee)
Change your SQL TERMINATOR to something other than a semi...like "@".
Then change your stmt as follows.
CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END@
_______________________________________________________


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-11-29 10:00:22 AM >>>
Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html . From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org . The IDUG List Admins can be reached at
[login to unmask email] . Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Fazio

Re: Trigger
(in response to Manas (Cognizant) Biswal)
Change your SQL TERMINATOR to something other than a semi...like "@".
Then change your stmt as follows.
CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END@
_______________________________________________________


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-11-29 10:00:22 AM >>>
Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html . From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org . The IDUG List Admins can be reached at
[login to unmask email] . Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Manas (Cognizant) Biswal

Re: Trigger
(in response to Richard Fazio)

I think this was discussed before in the list. You are using the semicolon as the SQL delimiter both inside the trigger body and as the generic SQL delimiter in SPUFI. Change the SPUFI defaults to use a different SQL delimiter. It should work out then.

Thanks,
Manas

"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin


________________________________

From: DB2 Data Base Discussion List on behalf of James
Sent: Tue 11/29/2005 11:00 AM
To: [login to unmask email]
Subject: [DB2-L] Trigger



Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Maria Isabel Sousa

Re: Trigger
(in response to James Park)
Hi James

Change the terminator character of the SQL statements.

--#SET TERMINATOR ?
CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END?



Isabel Sousa




James <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
29-11-2005 16:00
Please respond to
DB2 Database Discussion list at IDUG


To
[login to unmask email]
cc

Subject
[DB2-L] Trigger






Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Park

Re: Trigger
(in response to Richard Fazio)
Hi, Rich.

I ran your stmt but kept getting an error message.

CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END@
____________________________________
CREATE TRIGGER TRIGSSNN
00010004
AFTER UPDATE ON JAMES01.SSNN
00020004
FOR EACH ROW MODE DB2SQL
00030004
BEGIN ATOMIC
00040004
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; 00050008
---------+---------+---------+---------+---------+---------+---------+------
---+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT
BE
LEGAL ARE: QUOTE

DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 0 0 0 -1 289 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'

X'00000121' X'00000000' SQL DIAGNOSTIC INFORMATION


Any additional advice?

Thanks,

James Park

Database Support
Lockheed Martin
410-496-9576




_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Richard Fazio
Sent: Tuesday, November 29, 2005 11:13 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Trigger


Change your SQL TERMINATOR to something other than a semi...like "@". Then
change your stmt as follows.
CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END@
_______________________________________________________


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-11-29 10:00:22 AM >>>
Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html
< http://www.idugdb2-l.org/archives/db2-l.html > . From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
< http://www.idugdb2-l.org/ > . The IDUG List Admins can be reached at
[login to unmask email] <mailto:[login to unmask email]> .
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
< http://conferences.idug.org/index.cfm >


----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Basivi Inaganti

Re: Trigger
(in response to Maria Isabel Sousa)
Hi James, Change your SQL TERMINATOR .. ===> on CURRENT SPUFI DEFAULTS
panel from ; to # and try.
Thanks,
Basivi.




James <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/29/2005 11:00 AM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] Trigger


Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Basivi Inaganti

Re: Trigger
(in response to Basivi Inaganti)
and change END; to END#.

Thanks,
Basivi.




James <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/29/2005 11:00 AM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] Trigger


Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Basivi Inaganti

Re: Trigger
(in response to Basivi Inaganti)
Hi James, Change your SQL TERMINATOR .. ===> on CURRENT SPUFI DEFAULTS
panel from ; to # and try.
Thanks,
Basivi.




James <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/29/2005 11:00 AM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] Trigger


Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Basivi Inaganti

Re: Trigger
(in response to Basivi Inaganti)
Hi James, Change your SQL TERMINATOR .. ===> on CURRENT SPUFI DEFAULTS
panel from ; to # and try.
Thanks,
Basivi.




James <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/29/2005 11:00 AM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] Trigger


Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dan Cyr

Re: Trigger
(in response to Basivi Inaganti)
John,

I use DSNTEP2 and it works just fine.

//YOURIDCC JOB (DBA02),'TRIGSSNN',
// CLASS=P,MSGLEVEL=1,MSGCLASS=H,REGION=0M,TIME=NOLIMIT,
// NOTIFY=&SYSUID
/*JOBPARM SYSAFF=*,L=99999
//*+-----------------------------------------------------------------+
//*| JCL Stored in Dataset: ??????.???.JCL(??????) |
//*+-----------------------------------------------------------------+
//*| new trigger ??????.TRIGSSNN |
//*+-----------------------------------------------------------------+
//CCB603A EXEC PGM=IKJEFT1B,DYNAMNBR=119
//STEPLIB DD DISP=SHR,DSN=SYS3.SSID.DSNEXIT
// DD DISP=SHR,DSN=SYS3X.DB2.SSID.LOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(SSID)
RUN PROG(DSNTEP2) PLAN(DSNTEP2) LIB('SYS3.SSID.RUNLIB.LOAD')
END
/*
//SYSIN DD DISP=SHR,DSN=HIGHLVL.???.????????(MEMBER) See MEMBER below
//*+-----------------------------------------------------------------+
//*| REBIND trigger ???????? |
//*+-----------------------------------------------------------------+
//REBIND EXEC PGM=IKJEFT1B,DYNAMNBR=119
//STEPLIB DD DISP=SHR,DSN=SYS3.SSID.DSNEXIT
// DD DISP=SHR,DSN=SYS3X.DB2.SSID.LOAD
//ABNLIGNR DD DUMMY
//SYSTERM DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(SSID)
REBIND TRIGGER PACKAGE (??????.TRIGSSNN) CURRENTDATA(NO) +
EXPLAIN(YES) FLAG(I) IMMEDWRITE(NO) +
ISOLATION(CS) RELEASE(COMMIT)
/*
//

MEMBER content (see above)

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","??????"
;
--#SET TERMINATOR ~

CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END ~
--#SET TERMINATOR ;

COMMENT ON TRIGGER ??????.TRIGSSNN
IS 'BLA BLA BLA'
;

Dan Cyr 
DB2 Data Base Administration
United States Postal Service, WBIBSSC
Stegmaier Building
7 North Wilkes-Barre Blvd, Suite 501
Wilkes-Barre,  PA  18702-9200
office:    570-830-5841
cell:   919-451-2131
e-mail:  [login to unmask email]
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information.  If you have received it in error, please notify the sender immediately and delete the original.  Any other use of the e-mail by you is prohibited.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of James
Sent: Tuesday, November 29, 2005 11:00 AM
To: [login to unmask email]
Subject: [DB2-L] Trigger

Hi, List.

We use DB2 on OS/390 (v7).
I've tried to create a simple trigger using SPUFI, but kept getting an
error message.


CREATE TRIGGER TRIGSSNN
AFTER UPDATE ON JAMES01.SSNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE HEIGHT SET HEIGHT = HEIGHT + 10;
END;
_______________________________________________________
This is the error message I got.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT
MIGHT BE
LEGAL ARE:
QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 0 0 0 -1 302 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000012E' X'00000000' SQL DIAGNOSTIC
INFORMATION

What's wrong with the statement? Can I create a trigger in SPUFI?
Thanks for your help in advance.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm