Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    James Wu
    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
    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
    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
    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
    [BMC Software]
    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]


    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact