Professional Web Applications Themes

Only 1 of 3 columns per row can ever be populated! - IBM DB2

Thanks to denormalised developers, I have a table where only one column of three can ever be populated per row. I feel a) BEFORE INSERT/UPDATE triggers would have an unacceptable performance impact (this table will be accessed heavily). b) creating three views would be clumsy. c) restricting inserts/updates using the application is far too much to ask. Does anyone have any bright ideas? Thanks Bruce...

  1. #1

    Default Only 1 of 3 columns per row can ever be populated!

    Thanks to denormalised developers, I have a table where only one
    column of three can ever be populated per row. I feel
    a) BEFORE INSERT/UPDATE triggers would have an unacceptable
    performance impact (this table will be accessed heavily).
    b) creating three views would be clumsy.
    c) restricting inserts/updates using the application is far too much
    to ask.

    Does anyone have any bright ideas?

    Thanks
    Bruce
    Bruce Guest

  2. #2

    Default Re: Only 1 of 3 columns per row can ever be populated!

    It would be easier to give advice if saw the DDL and some sample rows
    for the table. Could you define all three columns with default values
    and somehow define a constraint to enforce the rules you want?

    Bruce Pullen wrote:
     

    Blair Guest

  3. #3

    Default Re: Only 1 of 3 columns per row can ever be populated!

    Bruce,

    Is this dynamic SQL? As long as you exploit dynamic statement cache and or
    use static SQL I don't see how a well written before trigger with a SIGNAL
    statement can have a significant negative performance impact. The runtime
    impact should be the evaluation time for the condition.
    Keep in mind that DB2 for LUW uses inline SQL PL for triggers. Can't get any
    faster than that.

    Cheers
    Serge


    Serge Guest

  4. #4

    Default Re: Only 1 of 3 columns per row can ever be populated!

    Bruce Pullen <com> wrote:
     

    What's the behavior that you want to have if 2 (or 3) of these columns are
    to be populated? If you want to return an error, then a simple check
    constraint on the table could be sufficient:

    CHECK ( col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL OR
    col1 IS NULL AND col2 IS NOT NULL AND col3 IS NULL OR
    col1 IS NULL AND col2 IS NULL AND col3 IS NOT NULL )

    If you want to handle the situation in the database system, then you would
    need some more logic and triggers could be the way to go.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

Similar Threads

  1. #40284 [NEW]: _REQUEST NOT POPULATED
    By mirko at stranicata dot com in forum PHP Bugs
    Replies: 1
    Last Post: January 30th, 10:25 AM
  2. CFSELECT POPULATED
    By sartini in forum Coldfusion Flash Integration
    Replies: 2
    Last Post: October 6th, 06:27 PM
  3. Fields not getting populated.
    By cindi_combs@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 2
    Last Post: April 22nd, 03:37 PM
  4. Data only populated on first page
    By Mark in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: July 8th, 11:08 AM
  5. Replies: 0
    Last Post: July 2nd, 06:18 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