Stored Procedure/Trigger Architecture Question

[login to unmask email]

Stored Procedure/Trigger Architecture Question
I would test this but my Systems Programmers are at least a month away from
getting the WLM environment up for SP.
Will the following work:

1. INSERT/UPDATE/DELETE of an elementary data table invoked from CICS
transaction
2. Appropriate after Trigger1(a,b,c) is invoked.
3. Trigger1 calls SP1
4. SP1 inserts into a 'work to be applied' table and issues a COMMIT
I want to free the thread begun in item #1.
5. Insert into 'Work to be applied' table invokes Trigger2.
6. Trigger2 calls an SP which performs significant SQL to update
the summary table

Better ideas ? The update to the summary table needs to be an asyncronous
task.

P.S. OS/390 V7

Sharon Fields
Senior Consulting DBA
AdvancePCS



Mark Fleming

Re: Stored Procedure/Trigger Architecture Question
(in response to sharon.fields@ADVANCEPCS.COM)
Sharon,

I don't have a solution, but wanted to point out a potential "gotcha". Everything that fires a trigger, including any SPs and UDFs that get invoked, and additional trigger caused by SQL they issue, are part of a single unit of work and are synchronous. The scenario you painted below will all be synchronous. The "gotcha" is in step 4, where you've had the SP issue the commit. While it's allowed in v7, the scope of the commit is everything in the current unit of work. The actual scope can include a lot more than you've described. Let's say that this CICS transaction can process more than a single transaction at a time (say accumulated transactions from another DB2 table... I'm making this up as I go so bear with me). If the steps you've outlined below are only part of the total process, that is more insert/update/delete activity occurs after step 6 but is considered part of the UOW from a design perspective, then you don't have a good point from which to rollback to a consistent point. A rollback by the CICS application would rollback work it did after step 4 but nothing prior to step 5. That's the potential "gotcha". Every situation is different and this may not be a problem in your case, and you need to make that call. I just wanted you to be aware of the potential for undesirable results so you can be prepared.

An asynchronous solution would be to initiate a transaction via an MSQueue event. This transaction would be written by the SP in step 4. I know almost nothing about MSQueue, so I could be entirely wrong on this, but my [limited] understanding is that it is asynchronous. Hopefully this may spur your creative juices to come up with another solution. Good luck.

Mark Fleming
Data Base Administration
Allstate




-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, June 27, 2003 3:35 PM
To: [login to unmask email]
Subject: Stored Procedure/Trigger Architecture Question


I would test this but my Systems Programmers are at least a month away from
getting the WLM environment up for SP.
Will the following work:

1. INSERT/UPDATE/DELETE of an elementary data table invoked from CICS
transaction
2. Appropriate after Trigger1(a,b,c) is invoked.
3. Trigger1 calls SP1
4. SP1 inserts into a 'work to be applied' table and issues a COMMIT
I want to free the thread begun in item #1.
5. Insert into 'Work to be applied' table invokes Trigger2.
6. Trigger2 calls an SP which performs significant SQL to update
the summary table

Better ideas ? The update to the summary table needs to be an asyncronous
task.

P.S. OS/390 V7

Sharon Fields
Senior Consulting DBA
AdvancePCS






Terry Fuller

Re: Stored Procedure/Trigger Architecture Question
(in response to Mark Fleming)
Another solution would be to use a CICS transient data queue to start a new
task to invoke the pieces you want to be asynchronous.

Terry Fuller, DBA
arvato systems

-----Original Message-----
From: Fleming, Mark [mailto:[login to unmask email]
Sent: Monday, June 30, 2003 9:39 AM
To: [login to unmask email]
Subject: Re: Stored Procedure/Trigger Architecture Question


Sharon,

I don't have a solution, but wanted to point out a potential "gotcha".
Everything that fires a trigger, including any SPs and UDFs that get
invoked, and additional trigger caused by SQL they issue, are part of a
single unit of work and are synchronous. The scenario you painted below will
all be synchronous. The "gotcha" is in step 4, where you've had the SP issue
the commit. While it's allowed in v7, the scope of the commit is everything
in the current unit of work. The actual scope can include a lot more than
you've described. Let's say that this CICS transaction can process more than
a single transaction at a time (say accumulated transactions from another
DB2 table... I'm making this up as I go so bear with me). If the steps
you've outlined below are only part of the total process, that is more
insert/update/delete activity occurs after step 6 but is considered part of
the UOW from a design perspective, then you don't have a good point from
which to rollback to a consistent point. A rollback by the CICS application
would rollback work it did after step 4 but nothing prior to step 5. That's
the potential "gotcha". Every situation is different and this may not be a
problem in your case, and you need to make that call. I just wanted you to
be aware of the potential for undesirable results so you can be prepared.

An asynchronous solution would be to initiate a transaction via an MSQueue
event. This transaction would be written by the SP in step 4. I know almost
nothing about MSQueue, so I could be entirely wrong on this, but my
[limited] understanding is that it is asynchronous. Hopefully this may spur
your creative juices to come up with another solution. Good luck.

Mark Fleming
Data Base Administration
Allstate




-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, June 27, 2003 3:35 PM
To: [login to unmask email]
Subject: Stored Procedure/Trigger Architecture Question


I would test this but my Systems Programmers are at least a month away from
getting the WLM environment up for SP.
Will the following work:

1. INSERT/UPDATE/DELETE of an elementary data table invoked from CICS
transaction
2. Appropriate after Trigger1(a,b,c) is invoked.
3. Trigger1 calls SP1
4. SP1 inserts into a 'work to be applied' table and issues a COMMIT
I want to free the thread begun in item #1.
5. Insert into 'Work to be applied' table invokes Trigger2.
6. Trigger2 calls an SP which performs significant SQL to update
the summary table

Better ideas ? The update to the summary table needs to be an asyncronous
task.

P.S. OS/390 V7

Sharon Fields
Senior Consulting DBA
AdvancePCS








http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]



Gary Blumenthal

Re: Stored Procedure/Trigger Architecture Question
(in response to Terry Fuller)
If I am not mistaken, in every RDBMS I have ever seen a trigger firing is
part of the same (i.e. original) transaction. So a trigger calls a SP
(which performs DML) causing another trigger to fire (which does more DML)
is all one LONG transaction.

Your first trigger can insert (via a stored procedure or direct SQL) but you
would need a secondary (a daemon that wakes up periodically for example)
process that trolls through your 'work to be applied' table and updates your
summary table. This will split up the unit of work appropriately for you.

Gary B.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, June 27, 2003 3:35 PM
To: [login to unmask email]
Subject: Stored Procedure/Trigger Architecture Question


I would test this but my Systems Programmers are at least a month away from
getting the WLM environment up for SP.
Will the following work:

1. INSERT/UPDATE/DELETE of an elementary data table invoked from CICS
transaction
2. Appropriate after Trigger1(a,b,c) is invoked.
3. Trigger1 calls SP1
4. SP1 inserts into a 'work to be applied' table and issues a COMMIT
I want to free the thread begun in item #1.
5. Insert into 'Work to be applied' table invokes Trigger2.
6. Trigger2 calls an SP which performs significant SQL to update
the summary table

Better ideas ? The update to the summary table needs to be an asyncronous
task.

P.S. OS/390 V7

Sharon Fields
Senior Consulting DBA
AdvancePCS








http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]



Phil Grainger

Re: Stored Procedure/Trigger Architecture Question
(in response to Gary Blumenthal)
Not only is it the same unit of work, but anything initiated by a trigger is all part of the same STATEMENT as well - beware performance implications!

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: Blumenthal, Gary [mailto:[login to unmask email]
Sent: Monday, June 30, 2003 4:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Stored Procedure/Trigger Architecture Question


If I am not mistaken, in every RDBMS I have ever seen a trigger firing is
part of the same (i.e. original) transaction. So a trigger calls a SP
(which performs DML) causing another trigger to fire (which does more DML)
is all one LONG transaction.

Your first trigger can insert (via a stored procedure or direct SQL) but you
would need a secondary (a daemon that wakes up periodically for example)
process that trolls through your 'work to be applied' table and updates your
summary table. This will split up the unit of work appropriately for you.

Gary B.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, June 27, 2003 3:35 PM
To: [login to unmask email]
Subject: Stored Procedure/Trigger Architecture Question


I would test this but my Systems Programmers are at least a month away from
getting the WLM environment up for SP.
Will the following work:

1. INSERT/UPDATE/DELETE of an elementary data table invoked from CICS
transaction
2. Appropriate after Trigger1(a,b,c) is invoked.
3. Trigger1 calls SP1
4. SP1 inserts into a 'work to be applied' table and issues a COMMIT
I want to free the thread begun in item #1.
5. Insert into 'Work to be applied' table invokes Trigger2.
6. Trigger2 calls an SP which performs significant SQL to update
the summary table

Better ideas ? The update to the summary table needs to be an asyncronous
task.

P.S. OS/390 V7

Sharon Fields
Senior Consulting DBA
AdvancePCS


the





http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]






Stan Johnson

Re: Stored Procedure/Trigger Architecture Question
(in response to Phil Grainger)
just another thought...we use MQSeries for asynchronous tasks. we have a
stored procedure do asynchronous MQ writes. the message ends up in a
triggered queue where additional work continues.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, June 27, 2003 3:35 PM
To: [login to unmask email]
Subject: Stored Procedure/Trigger Architecture Question


I would test this but my Systems Programmers are at least a month away from
getting the WLM environment up for SP.
Will the following work:

1. INSERT/UPDATE/DELETE of an elementary data table invoked from CICS
transaction
2. Appropriate after Trigger1(a,b,c) is invoked.
3. Trigger1 calls SP1
4. SP1 inserts into a 'work to be applied' table and issues a COMMIT
I want to free the thread begun in item #1.
5. Insert into 'Work to be applied' table invokes Trigger2.
6. Trigger2 calls an SP which performs significant SQL to update
the summary table

Better ideas ? The update to the summary table needs to be an asyncronous
task.

P.S. OS/390 V7

Sharon Fields
Senior Consulting DBA
AdvancePCS








Murari Selvakesavan

Stored procedure question
(in response to Stan Johnson)
DB2V7.1 Z/OS 1.2

Hello,
We have a requirement that applications from different platforms Oracle, SQL Server, MS Access and the like would have to access a Common DB2 Stored Procedure on the mainframe to pull data. We have done it in the past using Access ADO by setting the connection string and executed SPs on the mainframe. We are now stretching a little further to access from other platforms. I am not sure if the connection string mechanism is valid for Oracle and SQL Server. It would be of immense help if anyone doing work similar to what we are trying here can pass any information in this regard.


Thanks


Murari Selvakesavan.
Data Resource Management.
First Health Services Corp.
804.965.7601

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.
Thank you.

Walter Janißen

Re: Trigger Question
(in response to Peter Backlund)
Dave

I think, you cannot use a SELECT-statement in a SET-clause. Look up SQL
Reference for the syntax of the SET transition variable.

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

Dave Nance

Trigger Question
(in response to Murari Selvakesavan)
Can't get past a -104 on the create of a trigger. I'm trying to create a Before Insert trigger on a table. I want to populate a column in the table with the value from a column in another table. Here's the general SQL that I'm using:

CREATE TRIGGER DB2Q85.T85CP671 NO CASCADE BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEW.COL5 = (SELECT T2.COL5 FROM TABLE2 T2
WHERE T2.KEY = NEW.KEY) ;
END

Thanks for any help you can provide.


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

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.
Thank you.

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

Peter Backlund

Re: Trigger Question
(in response to Toine Michielse)
In DB2 for z/OS you can only use select clauses in the when clause, not in the set in the trigger body

Peter

David Nance wrote:
   Can't get past a -104 on the create of a trigger. I'm trying to create a Before Insert trigger on a table. I want to populate a column in the table with the value from a column in another table. Here's the general SQL that I'm using:
 
CREATE TRIGGER DB2Q85.T85CP671  NO CASCADE BEFORE  INSERT      
  ON TABLE1
REFERENCING NEW AS NEW                                            
  FOR EACH ROW        MODE DB2SQL                                   
  BEGIN ATOMIC                                                      
SET NEW.COL5 = (SELECT T2.COL5 FROM TABLE2 T2
                                WHERE T2.KEY = NEW.KEY) ;
  END                                                                
Thanks for any help you can provide.
 
 
Dave Nance
First Health Services, Corp.
(804)527-6841
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.
Thank you.
############################################################# Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email]. Only send one command at a time.The owners of the list can be reached at [login to unmask email].
-- ===> See you October, 11-14, at IDUG in Prague, Czech Republic <=== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ ############################################################# Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

Toine Michielse

Re: Trigger Question
(in response to Dave Nance)
David,

Are you using a SQL delimiter different from ';' ?

(see manuals for details)

Toine
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of David Nance
Sent: Freitag, 31. Oktober 2003 15:37
To: [login to unmask email]
Subject: Trigger Question


Can't get past a -104 on the create of a trigger. I'm trying to create a Before Insert trigger on a table. I want to populate a column in the table with the value from a column in another table. Here's the general SQL that I'm using:

CREATE TRIGGER DB2Q85.T85CP671 NO CASCADE BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEW.COL5 = (SELECT T2.COL5 FROM TABLE2 T2
WHERE T2.KEY = NEW.KEY) ;
END

Thanks for any help you can provide.


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

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.
Thank you.
############################################################# Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

[login to unmask email]

Re: Trigger Question
(in response to Walter Janißen)
David,

I believe the semicolon is only required if you have multiple SQL's in the
ATOMIC statement.

Fred Jaschick
PeopleSoft
877-339-9858
''If man could have half his wishes, he would double his troubles.'' -
Benjamin Franklin






"David Nance"
<[login to unmask email] To: [login to unmask email]
> cc:
Sent by: "DB2 Subject: Trigger Question
Data Base
Discussion List"
<[login to unmask email]
ORG>


10/31/2003 09:37
AM
Please respond to
"DB2 Database
Discussion list
at IDUG"







Can't get past a -104 on the create of a trigger. I'm trying to create a
Before Insert trigger on a table. I want to populate a column in the table
with the value from a column in another table. Here's the general SQL that
I'm using:

CREATE TRIGGER DB2Q85.T85CP671 NO CASCADE BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEW.COL5 = (SELECT T2.COL5 FROM TABLE2 T2
WHERE T2.KEY = NEW.KEY) ;
END
Thanks for any help you can provide.


Dave Nance
First Health Services, Corp.
(804)527-6841
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.
Thank you.
############################################################# Welcome to
the IDUG DB2-L list. To change your subscription options or subscribe or to
cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE
THE WEB page but if you can't then send commands to the list as follows: To
UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to
[login to unmask email] and to set yourself to use DIGEST mode or NOMAIL
send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to
[login to unmask email] Only send one command at a time.The owners of
the list can be reached at [login to unmask email]

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

Dave Nance

Re: Trigger Question
(in response to fred_jaschick@PEOPLESOFT.COM)
Yes. We're using a ~. The trigger is failing with a -104 at the column being selected. Thanks.

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


>>> [login to unmask email] 10/31/03 9:54:47 AM >>>
David,

Are you using a SQL delimiter different from ';' ?

(see manuals for details)

Toine
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of David Nance
Sent: Freitag, 31. Oktober 2003 15:37
To: [login to unmask email]
Subject: Trigger Question


Can't get past a -104 on the create of a trigger. I'm trying to create a Before Insert trigger on a table. I want to populate a column in the table with the value from a column in another table. Here's the general SQL that I'm using:

CREATE TRIGGER DB2Q85.T85CP671 NO CASCADE BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEW.COL5 = (SELECT T2.COL5 FROM TABLE2 T2
WHERE T2.KEY = NEW.KEY) ;
END

Thanks for any help you can provide.


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

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.
Thank you.
############################################################# Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at http://www.idugdb2-l.org/adminscripts/waexe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://wwwidugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

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.
Thank you.

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

Peter Backlund

Re: Trigger Question
(in response to Dave Nance)
In a previous reply to David Nance my reply was a bit short.
He wanted to use a "select" in a set statement in a before trigger.
This is not allowed, however, you can use a function.
This has to be a user defined function.

In DB2 for Windows... this can be an SQL UDF

In DB2 for z/OS it has to be an External UDF (COBOL, Java,...)

Now you can do a "set n.col = myudf(n.col)"

Best regards,

Peter

--

===> See you October, 11-14, at IDUG in Prague, Czech Republic <===

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

#############################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L webpage at
http://www.idugdb2-l.org/adminscripts/wa.exe?REPORT&Z=3. WE URGE YOU TO USE THE WEB page but if you can't then send commands to the list as follows: To UNSUBSCRIBE send the UNSUBSCRIBE DB2-L command to [login to unmask email] and to set yourself to use DIGEST mode or NOMAIL send the SET DB2-L DIGEST or SET DB2-L NOMAIL command to [login to unmask email] Only send one command at a time.The owners of the list can be reached at [login to unmask email]

Murari Selvakesavan

Stored procedure question
(in response to Peter Backlund)
Hello all,
DB2 V7.1 z/OS 1.2
We are running into some difficulty at runtime (sqlcode : -804) while executing a Stored procedure written in Cobol. It is a simple SP, having 2 input parameters defined each as CHAR(200) and few output paramters. We tested calling this SP from a CICS transaction & Stored procedure builder same results -804 with no reason code. Based on the message, the problem is with the input parameters. Following are some details, I couldn't think of any thing else that causes this error. Appreciate any help in this regard.

CREATE PROCEDURE XXX.MY_SP
(IN IREC1 CHAR ( 200 ) FOR SBCS DATA CCSID EBCDIC,
IN IREC2 CHAR ( 200 ) FOR SBCS DATA CCSID EBCDIC,
OUT INUM CHAR ( 20 ) FOR SBCS DATA CCSID EBCDIC,
OUT SPCODE INTEGER,
OUT MSGTEXT VARCHAR ( 80 ) FOR SBCS DATA CCSID EBCDIC)
DYNAMIC RESULT SETS 0
EXTERNAL NAME 'MYSP'
LANGUAGE COBOL
PARAMETER STYLE GENERAL
FENCED
MODIFIES SQL DATA
NO DBINFO
COLLID DB2U85
WLM ENVIRONMENT DBT1WLMU
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
RUN OPTIONS 'MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)'
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS

MYSP (Cobol)
===========
LINKAGE SECTION.
01 INPUT-1 PIC X(200).
01 INPUT-2 PIC X(200).
01 I-NBR PIC X(20).
01 SP-CODE PIC S9(09) COMP.
01 MESSAGE-AREA PIC X(80).



Murari Selvakesavan.
Data Resource Management.
First Health Services Corp.
804.965.7601



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.
Thank you.

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Agus Kwee

Re: Stored procedure question
(in response to Murari Selvakesavan)
Murari,

Did you get the sqlcode : - 804 after execution of
exec sql call 'procedurename' in the CICS program?
Did the calling program initialize all the 5 call parameters
prior to the sql call statement?
I think the Cobol definition for the varchar(80) parameter
should be:
01 message-area.
49 message-area-len pic s9(4) comp.
49 message-area-text pic x(80).

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

PK Ganapathy

Re: Stored procedure question
(in response to Agus Kwee)
Hi,

You may want to compile your calling and called programs with SSRANGE
compiler option and then run your test. One of the reasons is the SQLDA
gets corrupted when array indexes overflow causing you to get a -804 when
you encounter the first SQL statement. The SSRANGE can tell you if your
array variables are overflowing.


-PK.Ganapathy
IBM Certified Solutions Expert
DB2 UDB V7.1 Database Administration for OS/390
Verizon

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Ravi

Stored Procedure Question
(in response to PK Ganapathy)
Hi,

We have stored (input 4 parms) procedure in the WLM enviornment and its
working fine in test as well as in production.(two different tso system
and db2 ssid). Now we have added one more new input parm (toatlly 5 parms)
for the same procedure and wanted to test the old side and newside. (if we
pass the 5 parms then it should point to new version else it should point
to old version) All out SPs are in cobol and in z/os with db2 version
7.

We tried to create "create procdure stmt with same deatils like old except
input parms 5. But its is giving error. Is that possible to have two
versions and test it?
Thanks
Sankar Ravi

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

David Seibert

Re: Stored Procedure Question
(in response to Ravi)
Hi Sankar,

It sounds to me like you may be confusing the behavior of User Defined
Functions and Stored Procedures.

Stored Procedures are unique by Schema & Name
With UDFs you can have multiple versions of the same Schema & Name differing
by parameter count or datatype

To have different versions of the Stored Procedure within a subsystem, you
need to vary the name or the schema.

David Seibert
Compuware Corporation Database Product Architect
[login to unmask email]

>Hi,

> We have stored (input 4 parms) procedure in the WLM enviornment and its
working fine in test as well as in production.(two different tso system
and db2 ssid). Now we have added one more new input parm (toatlly 5 parms)
for the same procedure and wanted to test the old side and newside. (if we
pass the 5 parms then it should point to new version else it should point
to old version) All out SPs are in cobol and in z/os with db2 version
7.

>We tried to create "create procdure stmt with same deatils like old except
input parms 5. But its is giving error. Is that possible to have two
versions and test it?
>Thanks
>Sankar Ravi




The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Mike O'Meara

Trigger Question
(in response to David Seibert)
lease forgive what might seem an intuitively obvious question from a
newbie to triggers.

I wish to create a trigger that will capture an entire row (old image)
when the row is updated. Is there some all encompassing statement to
insert the entire row into another table without listing (shudder) each
column? Can you either a) send me an example, or b) point me to a
website or manual that might explain this?

Thanks.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Mike O'Meara

Re: Trigger Question
(in response to Mike O'Meara)
I have my answer. You no longer need reply. Thanks for your
assistance. It was obvious and I must have coded something incorrectly.

-----Original Message-----
From: OMeara, Mike /laxko
Sent: Wednesday, December 24, 2003 1:37 PM
To: '[login to unmask email]'
Subject: Trigger Question

lease forgive what might seem an intuitively obvious question from a
newbie to triggers.

I wish to create a trigger that will capture an entire row (old
image) when the row is updated. Is there some all encompassing
statement to insert the entire row into another table without listing
(shudder) each column? Can you either a) send me an example, or b)
point me to a website or manual that might explain this?

Thanks.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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