Trigger affecting the SORT work area

john king

Trigger affecting the SORT work area
Hi Listers,Wishing you Merry Christmas .Here is probelm I faced with triggers while discussing something with our designer. As per i suggested for Trigger but he objected saying that Trigger has impacts on SORT WORK area. But I know Trigger is affecting the statement cost but how it is related to SORT work area. I could not find any thing in any material and how I should defend or obey my designer unless I dont know how it is done. So I request list to add their valuable suggestions.
Thanking you all in advance.

John




sushant dash

Re: Trigger affecting the SORT work area
(in response to john king)
Hello John, Yes to some extent it is true. First of all when ever there is a trigger on a table it would make any queries on that table under diffrent category. I mean it would be categorised under 'B'. Regarding the sort work area I have knowledge about SINGLE area where it can affect the sort work area.
Consider you have defined a trigger for UPDATE STATEMENT. After the updation to the table , the trigger is activated and it is going update say multiple rows from that table. To maintain the unique ness of the business key it would sort the the rows and uodate the required field. For exaple. A Table with COL1,COL2,COL3. COL1 IS THE PRIMARY KEY. YOU ARE UPDATING THE COL3 BASED ON SOME CONDITION WHEN COL2 > SPME VALUE. IN THAT CASE TO MAINTAIN THE UNIQUE NESS OF COL1 THE TRIGGER WOULD SORT THE ROWS WHICH FALL UNDER THE QUERY CONDITIONS AND THEN UPDATE. THIS WAY THE SORT IS AFFECTED. Hope this would give you some idea.Still wait for the other ideas.
Thank you
Dash

On Sat, 22 Dec 2001 john king wrote :
> Hi Listers,Wishing you Merry Christmas .Here is probelm
> I faced with triggers while discussing something with
> our designer. As per i suggested for Trigger but he
> objected saying that Trigger has impacts on SORT WORK
> area. But I know Trigger is affecting the statement
> cost but how it is related to SORT work area. I could
> not find any thing in any material and how I should
> defend or obey my designer unless I dont know how it is
> done. So I request list to add their valuable
> suggestions.
> Thanking you all in advance.
>
> John
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list can
> be





Scott Trometer

Re: Trigger affecting the SORT work area
(in response to sushant dash)
Check out the following redbook -
http://www.redbooks.ibm.com/redbooks/SG246108.html




On Sat, 22 Dec 2001 john king wrote :
> Hi Listers,Wishing you Merry Christmas .Here is probelm
> I faced with triggers while discussing something with
> our designer. As per i suggested for Trigger but he
> objected saying that Trigger has impacts on SORT WORK
> area. But I know Trigger is affecting the statement
> cost but how it is related to SORT work area. I could
> not find any thing in any material and how I should
> defend or obey my designer unless I dont know how it is
> done. So I request list to add their valuable
> suggestions.
> Thanking you all in advance.
>
> John
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list can
>





http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]


"The sender believes that this E-mail and any attachments were free of any
virus, worm, or Trojan Horse when sent. This message and its attachments
could have been infected during transmission. By reading the message and
opening any attachments, the recipient accepts full responsibility for
taking remedial action about viruses and other defects. Cendant/RCI
is not liable for any loss or damage arising in any way from this message
or its attachment."



Max Scarpa

Re: Trigger affecting the SORT work area
(in response to Scott Trometer)
Hi

Triggers have an influence on sortwork files when you're using transition
variable/tables (REFERENCING OLD/NEW, REFERENCING NEW TABLE...) as this
cause the creation of some 'temporary' tables (no log, no catalog
definition), with a size of 24 page each (the space may extend if needed).
Of course these tables use sortwork files.

See REDBOOK:

DB2 for z/OS Application Programming Topics SG246300

HTH

Max Scarpa