Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2011
    Posts
    334

    Unanswered: unique constraint problem in dpf env

    Ive got a problem recently, any suggestion will be great appreciated.
    We have a table named T1。 Some columns are :
    C1,C2,C3。
    and we create it in a dpf env tablespace using the below sql :
    create table t1 ( c1 ....,c2...., c3 .... ) distributed by c1.... primary key c1....
    。Now we want to create a unique index or a unique constraint on column ( c2,c3 ) but got a error : SQL0270N. Function not supported (Reason code = 1) .The primary key, each unique constraint, and each unique index must contain all distribution columns of the table (columns may appear in any order).
    We cant change distibuted key to ( c2,c3 ) for some performance consideration ( c1 is the join column of the most query) 。
    Are there some other ways to solve this problem on db2 side?

    thx....

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If (c2,c3) is unique and (c1) is unique, then (c2, c3, c1) is also unique, no? This meets the requirement of having the distribution key in this unique constraint.

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Thanks for your quickly response.
    But if (c1) is unique , Then (c2, c3, c1) is always unique。
    So the duplicated rows on ( c2,c3 ) could be inserted into the table。It is not what we want。

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Does:

    create unique index ... ( c2,c3 ) include ( c1 )

    work? I don't have access to any dpf installation so I can't verify my self. My guess is no, but it might be worth a try.
    --
    Lennart

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    unfortunately, got the same error。
    I think db2 mabye does not know whether the rows are unique or not at globle without using disributed key.....
    it is so depressed。。。。

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    unfortunately, got the same error。
    I think db2 mabye does not know whether the rows are unique or not at globle without using disributed key.....
    it is so depressed。。。。
    You probably right regarding the motive why this does not work. I think it is unfortunate that a pure physical concept such as partitioning has impact on a pure logical concept such as candidate key. The only thing I can think of is to enforce the uniqueness via trigger. Example:

    create index ... on t1 (c2, c3) ...

    create trigger ... on t1
    no cascade before insert on t1
    referencing new as n
    for each row
    when (exists (select 1 from t1 where (c2,c3) = (n.c2,n.c3)))
    signal sqlstate '77000' set message_text = 'Unique trigger constraint violation ...'

    create trigger ... on t1
    no cascade before update on t1
    ...

    Major drawbacks compared with a unique constraint (imo) are:

    1. It is more difficult to understand for humans.
    2. It is more difficult for the optimizer to deduce conclusions from this construct.
    3. It does not tell you anything about the content in the table, only that new rows that violate the constraints will be stopped.
    --
    Lennart

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DPF is really for data warehouses (which is why it is now called InfoSphere Warehouse instead of DPF). So the limitation discussed here of only one unique index per table in DPF does not seem very troublesome to me. Having more than one unique index per table is unusual even in OLTP, much less data warehouses.

    More troublesome (IMO) is with Table (range) Partitioning where the partitioning key (typically a date in most partitioned tables) must be part of every unique index if partitioned indexes are to be used. If partitioned indexes are not available, then instantaneous detach of a partition is not available, since the global index has to updated for the detached partition rows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    Lennart , thanks for your reply.
    I endorse every thing you just said。It is a design limitation。
    Maybe the before trigger is the only way to deal with it。
    But I have a more trouble thing: The column could be a null value!
    for the example you given above:
    if n.c2 or n.c3 is null, the statement "select 1 from t1 where (c2,c3) = (n.c2,n.c3)"
    is not a valid sql。
    So we can't prevent rows with c1 or c2 being null to be inserted into t1 repeatly。

    I want to use dynamic sql to deal with it:

    set sql = 'select count(*) from t1 where 1 = 1 '
    if n.c2 is null then
    set sql = sql + 'and c2 is null';
    else
    set sql = sql + 'and c2 = n.c2';
    end if;

    if n.c3 is null then
    set sql = sql + 'and c3 is null';
    else
    set sql = sql + 'and c3 = n.c3';
    end if;

    then excute sql dynamicly and put the result into a int variable.

    Do you know how to execute a dynamic statement in a trigger
    without using cursor?

    Since it is another question ,i will open a new thread for it, too。

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    Lennart , thanks for your reply.
    I endorse every thing you just said。It is a design limitation。
    Maybe the before trigger is the only way to deal with it。
    But I have a more trouble thing: The column could be a null value!
    for the example you given above:
    if n.c2 or n.c3 is null, the statement "select 1 from t1 where (c2,c3) = (n.c2,n.c3)"
    is not a valid sql。
    So we can't prevent rows with c1 or c2 being null to be inserted into t1 repeatly。
    Dependent on the semantics you want to implement regarding null, you can use variations like:

    select 1 from t1 where ( coalesce(c2,''), coalesce(c3, '')) = (coalesce(n.c2, ''), coalesce(n.c3, ''))

    Serge discusses null in his latest blog:

    https://www.ibm.com/developerworks/m...B2LUW/?lang=en

    You might also be interested in an earlier post where he discusses "Unique where not null":

    https://www.ibm.com/developerworks/m...exes26?lang=en
    --
    Lennart

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    Lennart ,thx a lot for your reference.

    but i encountered another problem,
    How to use update tigger to pevent duplicate rows ?
    I have done this :

    create table t1 ( c1 int, c2 int )

    insert into t1 values (2 ,2 ),(3,3 )

    drop trigger tu@
    create trigger tu
    no cascade
    before update of c1,c2 on t1
    REFERENCING new as nr old as oldr
    FOR EACH ROW MODE DB2SQL
    WHEN ( exists ( SELECT 1 FROM t1 WHERE c1 = nr.c1 and c2 = nr.c2 ) )
    signal sqlstate '90000' ('duplicated rows')
    @

    update t1 set c1 = 1, c2 = 1

    it always successes, but why? I think the second rows update will fail because of the trigger?
    Is there anything i have missed ????

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    it always successes, but why?
    The reason I guessed was this
    NO CASCADE BEFORE
    Specifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database. It also specifies that the triggered action of the trigger will not cause other triggers to be activated....
    CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows


    I tried AFTER TRIGGER which seemed to work on non-DPF environment.

    Example 1: create a test table and populate it.
    Code:
    ------------------------------ Commands Entered ------------------------------
    create table unq_trgr.t1 ( c1 int, c2 int );
    
    insert into unq_trgr.t1 values (2 ,2 ),(3,3 );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    Example 2: create a trigger.
    Code:
    ------------------------------ Commands Entered ------------------------------
    create OR REPLACE trigger unq_trgr.tu
    AFTER update of c1,c2 on unq_trgr.t1 
    REFERENCING new as nr old as oldr
    FOR EACH ROW MODE DB2SQL
    WHEN(
         (SELECT COUNT(*) FROM unq_trgr.t1 WHERE c1 = nr.c1 and c2 = nr.c2 ) > 1
    )
    signal sqlstate '90000' ('duplicated rows');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Example 3: test update statements.
    Code:
    ------------------------------ Commands Entered ------------------------------
    update unq_trgr.t1
       set c1 = 1, c2 = 1
     WHERE c1 = 3;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    update unq_trgr.t1
       set c1 = 9, c2 = 9;
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0438N  Application raised error or warning with diagnostic text: 
    "duplicated rows".  SQLSTATE=90000
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM unq_trgr.t1;
    ------------------------------------------------------------------------------
    
    C1          C2         
    ----------- -----------
              2           2
              1           1
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    update unq_trgr.t1
       set c1 = 1, c2 = 1
     WHERE c1 = 2;
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0438N  Application raised error or warning with diagnostic text: 
    "duplicated rows".  SQLSTATE=90000
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM unq_trgr.t1;
    ------------------------------------------------------------------------------
    
    C1          C2         
    ----------- -----------
              2           2
              1           1
    
      2 record(s) selected.

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    You are right tokuma, The reason is "before trigger".
    I changed it to "after trigger"

    So the final code is some thing like this :

    insert_contraint_trigger:


    drop trigger T1_trig_ai@
    create trigger T1_trig_ai
    after insert on T1
    REFERENCING new as nr
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    SELECT
    CASE -- all the columns are null
    WHEN
    (
    (
    nr.C1 is null
    )
    and
    (
    nr.C2 is null
    )
    and
    (
    nr.C3 is null
    )
    and
    (
    nr.C4 is null
    )
    )
    THEN
    CASE
    WHEN
    (
    (
    SELECT
    count(*)
    FROM
    T1
    WHERE
    C1 is null
    and C2 is null
    and C3 is null
    and C4 is null
    ) > 1 -- because it is an after trigger ,it will always select out itself。
    )
    THEN cast(raise_error('90000','duplicated rows when insert') as int)
    END
    ELSE -- at least one columns is not null
    CASE
    WHEN
    (
    ( SELECT
    count(*)
    FROM
    T1
    WHERE
    (
    nr.C1 is null
    and C1 is null
    or C1= nr.C1
    )
    and
    (
    nr.C2 is null
    and C2 is null
    or C2= nr.C2
    )
    and
    (
    nr.C3 is null
    and C3 is null
    or C3= nr.C3
    )
    and
    (
    nr.C4 is null
    and C4 is null
    or C4 = nr.C4
    )
    ) > 1
    )
    THEN cast(raise_error('90001','duplicated rows when insert') as int)
    END
    END
    FROM
    sysibm.sysdummy1;
    END
    @


    update_contraint_trigger:

    drop trigger T1_trig_au@
    create trigger T1_trig_au
    after update of C1,C2,C3,C4 on T1
    REFERENCING new as nr old as oldr
    FOR EACH ROW
    BEGIN ATOMIC
    SELECT
    CASE
    WHEN
    (
    (
    nr.C1 is null
    )
    and
    (
    nr.C2 is null
    )
    and
    (
    nr.C3 is null
    )
    and
    (
    nr.C4 is null
    )
    )
    THEN
    CASE
    WHEN
    (
    (
    SELECT
    count(*)
    FROM
    T1
    WHERE
    C1 is null
    and C2 is null
    and C3 is null
    and C4 is null
    ) > 1
    )
    THEN cast(raise_error('90002','duplicated rows when update') as int)
    END
    ELSE
    CASE
    WHEN
    (
    ( SELECT
    count(*)
    FROM
    T1
    WHERE
    (
    nr.C1 is null
    and C1 is null
    or C1= nr.C1
    )
    and
    (
    nr.C2 is null
    and C2 is null
    or C2= nr.C2
    )
    and
    (
    nr.C3 is null
    and C3 is null
    or C3= nr.C3
    )
    and
    (
    nr.C4 is null
    and C4 is null
    or C4 = nr.C4
    )
    ) > 1
    )
    THEN cast(raise_error('90003','duplicated rows when update') as int)
    END
    END
    FROM
    sysibm.sysdummy1;
    END
    @

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    I don't know how to paste the formtted sql in this thread....

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Enclose the code with [CODE] and [@CODE] (replace @ with /)

    For example:

    From this
    (left blank are not displayed, but there are one blank before "FROM" and before "WHERE".)
    [CODE]
    SELECT *
    FROM employee
    WHERE workdept = 'E11'
    [@CODE]

    Replace "@" between "[" and "CODE]" with "/".
    Code:
    SELECT *
     FROM  employee
     WHERE workdept = 'E11'

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think your insert trigger can be simplified like the follwing example,
    and update trigger too.

    Example 4:
    Code:
    create trigger T1_trig_ai
    after insert on T1
    REFERENCING new as nr
    FOR EACH ROW MODE DB2SQL
    WHEN(
        (SELECT count(*)
          FROM  T1
          WHERE
           (    nr.C1 is null
            and    C1 is null
            or     C1 = nr.C1
           )
            and
           (    nr.C2 is null
            and    C2 is null
            or     C2 = nr.C2
           )
            and
           (    nr.C3 is null
            and    C3 is null
            or     C3 = nr.C3
           )
            and
           (    nr.C4 is null
            and    C4 is null
            or     C4 = nr.C4
           )
        ) > 1
    )
    signal sqlstate '90001' ('duplicated rows when insert')
    ;
    Last edited by tonkuma; 04-01-12 at 08:35. Reason: Replace a raise_error function with a signal statement.

Posting Permissions

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