Professional Web Applications Themes

Can't drop UDF because of Trigger Dependencies - IBM DB2

We have a User define Function that is invoked by many of our (100+) triggers. The UDF needs to change, but you can't drop it decause of the dependencies. Is there no other way other than to extract all the trigger SQL, drop the triggers, change the function, and then put the triggers back? Thanks Fred Prose [email]fprosenospam_supreme.sp.state.az.us[/email]...

  1. #1

    Default Can't drop UDF because of Trigger Dependencies

    We have a User define Function that is invoked by many of our (100+)
    triggers. The UDF needs to change, but you can't drop it decause of
    the dependencies.

    Is there no other way other than to extract all the trigger SQL, drop
    the triggers, change the function, and then put the triggers back?

    Thanks

    Fred Prose
    [email]fprosenospam_supreme.sp.state.az.us[/email]
    Fred Prose Guest

  2. #2

    Default Re: Can't drop UDF because of Trigger Dependencies

    Depending on the change the the UDF (parm type etc) changing it can
    change function resolution etc, and thus the semantics of the
    trigger...that's why db2 is complaining about the dependency. If you're
    just changing an attribute of the UDF (threadsafe, not threadsafe etc),
    you can get away with alter function...if not, then you might want to
    use db2look to pull out the trigger ddl before you drop etc.

    Fred Prose wrote:
    > We have a User define Function that is invoked by many of our (100+)
    > triggers. The UDF needs to change, but you can't drop it decause of
    > the dependencies.
    >
    > Is there no other way other than to extract all the trigger SQL, drop
    > the triggers, change the function, and then put the triggers back?
    >
    > Thanks
    >
    > Fred Prose
    > [email]fprosenospam_supreme.sp.state.az.us[/email]
    Sean McKeough Guest

  3. #3

    Default Re: Can't drop UDF because of Trigger Dependencies

    "Fred Prose" <com> wrote in message
    news:google.com... 

    db2 -x select text || ' ;' from syscat.triggers where text like '%UDFNAME%'
    order by create_time > recreate.sql
    db2 -x select 'DROP TRIGGER ' || TRIGNAME || ';' from syscat.triggers where
    text like '%UDFNAME%' order by create_time > drop.sql
    db2 -tf drop.sql
    -- now change UDF
    db2 -tf recreate.sql

    Regards
    Paul Vernon
    Business Intelligence, IBM Global Services


    Paul Guest

Similar Threads

  1. Replies: 0
    Last Post: September 14th, 10:19 PM
  2. [RFC] Devel::Dependencies
    By Jean-Louis Leroy in forum PERL Modules
    Replies: 4
    Last Post: January 17th, 04:48 PM
  3. Using Yaromat for Dependencies
    By Bohak1 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 4
    Last Post: July 13th, 07:21 PM
  4. pg_dump dependencies
    By Eric E in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 6th, 06:45 PM
  5. rpms and dependencies
    By David Z Maze in forum Debian
    Replies: 4
    Last Post: July 30th, 03:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139