INSERT Trigger for LOAD

James Wu

INSERT Trigger for LOAD
IBMers,

An INSERT trigger only works for INSERT operations. Can IBM consider
making the INSERT triggers work for LOAD operations as well, or create LOAD
triggers that work in the similar way of the INSERT triggers? I know we can
get the similar functionality by using EDITPROC or FLDPROC, but using
trigger for both INSERT and LOAD will make our life much easier.

James Wu:-)
(847)646-5548
[login to unmask email]



Susan Lawson

Re: INSERT Trigger for LOAD
(in response to James Wu)
James,

In version 7 - if you use the new online LOAD resume -
triggers will be fired. Basically this new feature is
executing inserts under the covers.

Susan
--- "Wu, James" <[login to unmask email]> wrote:
> IBMers,
>
> An INSERT trigger only works for INSERT
> operations. Can IBM consider
> making the INSERT triggers work for LOAD operations
> as well, or create LOAD
> triggers that work in the similar way of the INSERT
> triggers? I know we can
> get the similar functionality by using EDITPROC or
> FLDPROC, but using
> trigger for both INSERT and LOAD will make our life
> much easier.
>
> James Wu:-)
>
> (847)646-5548
>
> [login to unmask email]
>
>
> 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
>
>


=====
Susan Lawson
DB2 UDB OS/390 & z/OS Certified DBA
[login to unmask email]
YL&A,Inc.
Database Performance Professionals
www.ylassoc.com

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/



Peter Backlund

Re: INSERT Trigger for LOAD
(in response to Susan Lawson)
Susan and James,

I just had a "conversation" with Roger Miller.
For the new LOAD SHRLEVEL CHANGE (which is an INSERT under the covers),
row after insert triggers are fired - not exactly what the doctor ordered.

Peter

PS Will I see You Susan in Santa Cruz?

Susan Lawson wrote:

>James,
>
>In version 7 - if you use the new online LOAD resume -
>triggers will be fired. Basically this new feature is
>executing inserts under the covers.
>
>Susan
>--- "Wu, James" <[login to unmask email]> wrote:
>
>>IBMers,
>>
>> An INSERT trigger only works for INSERT
>>operations. Can IBM consider
>>making the INSERT triggers work for LOAD operations
>>as well, or create LOAD
>>triggers that work in the similar way of the INSERT
>>triggers? I know we can
>>get the similar functionality by using EDITPROC or
>>FLDPROC, but using
>>trigger for both INSERT and LOAD will make our life
>>much easier.
>>
>> James Wu:-)
>>
>>(847)646-5548
>>
>>[login to unmask email]
>>
>>
>>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
>>
>>
>
>
>=====
>Susan Lawson
>DB2 UDB OS/390 & z/OS Certified DBA
>[login to unmask email]
>YL&A,Inc.
>Database Performance Professionals
>www.ylassoc.com
>
>__________________________________________________
>Do You Yahoo!?
>Make international calls for as low as $.04/minute with Yahoo! Messenger
>http://phonecard.yahoo.com/
>
>
>
>

--
+-------------------------------+---------------------------------+
| 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! |
+-----------------------------------------------------------------+


James Szabo

Re: INSERT Trigger for LOAD
(in response to Peter Backlund)
James,

Another possibility that we are considering is the design that all
triggers have no logic in them other than calling a stored procedure, where
the real business logic is.

This would allow you to build a manual process to fire the stored
procedure from a stub program, that could use the same input file as your
load.

The downside to this approach is that the stored procedure gets fired
after all of the records have been added, so this approach could not replace
a BEFORE INSERT trigger, unless you do the following: load your input file
into a declared or global temp table with the same characteristics as your
destination table; fire your stored procedure from a program stub against
this temp table, deleting records if they would "fail" your BEFORE
INSERT-equivalent code. When you are done, SELECT all remaining records
from the temp table, essentially building a new load file.

If your business logic would produce a different result from firing
after every loaded record, this method would not be for you; you would have
to use LOAD RESUME SHRLEVEL CHANGE as suggested by Ms. Lawson.

Finally, to be extra careful, you would have to post-process the load
input file prior to calling your program stub, removing records that did not
make it into your table, due to data formatting errors or constraint
violations (if using ENFORCE CONSTRAINTS).

Jim Szabo


----- Original Message -----
From: "Wu, James" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, August 29, 2001 12:32 PM
Subject: INSERT Trigger for LOAD


> IBMers,
>
> An INSERT trigger only works for INSERT operations. Can IBM consider
> making the INSERT triggers work for LOAD operations as well, or create
LOAD
> triggers that work in the similar way of the INSERT triggers? I know we
can
> get the similar functionality by using EDITPROC or FLDPROC, but using
> trigger for both INSERT and LOAD will make our life much easier.
>
> James Wu:-)
> (847)646-5548
> [login to unmask email]
>
>
>


>



Phil Grainger

Re: INSERT Trigger for LOAD
(in response to James Szabo)
Obvious question, so sorry for asking it!

I'm assuming this means NO before INSERT triggers and NO STATEMENT triggers will be triggered by on-line LOAD RESUME - correct??

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]