Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2013
    Posts
    21

    UNIQUE constrains on the nullable column

    Hi all,


    I tried to create a unique index with null values ​​supporting the following command:
    Code:
    CREATE UNIQUE WHERE NOT NULL INDEX ON UNQ_IDX MYTABLE (MYFOREIGNKEY_ID)
    DB2 and answers:
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "WHERE NOT NULL" was found following "CREATE
    UNIQUE ".  Expected tokens may include:  "<space>".  SQLSTATE=42601
    I work on DB2 V9.7 on AIX.

    In the DB2 for z/OS documentation :
    Code:
    >>-CREATE--+----------------------------+--INDEX--index-name---->
               '-UNIQUE--+----------------+-'
                         '-WHERE NOT NULL-'
    but in the DB2 for Linux, UNIX, and Windows documentation :
    Code:
    >>-CREATE--+--------+--INDEX--index-name------------------------>
               '-UNIQUE-'
    the WHERE NOT NULL is not available !

    I am looking for a way to create a UNIQUE constrains on the nullable column.

    this command line does not work (a error is generated)
    Code:
    ALTER TABLE MYTABLE ADD CONSTRAINT UNQ UNIQUE (MYFOREIGNKEY_ID)
    Thanks.

    Nassa.

  2. #2
    Join Date
    Mar 2003
    Posts
    279
    Quote Originally Posted by nassarane View Post
    Hi all,


    I tried to create a unique index with null values ​​supporting the following command:
    Code:
    CREATE UNIQUE WHERE NOT NULL INDEX ON UNQ_IDX MYTABLE (MYFOREIGNKEY_ID)
    DB2 and answers:
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "WHERE NOT NULL" was found following "CREATE
    UNIQUE ".  Expected tokens may include:  "<space>".  SQLSTATE=42601
    I work on DB2 V9.7 on AIX.

    In the DB2 for z/OS documentation :
    Code:
    >>-CREATE--+----------------------------+--INDEX--index-name---->
               '-UNIQUE--+----------------+-'
                         '-WHERE NOT NULL-'
    but in the DB2 for Linux, UNIX, and Windows documentation :
    Code:
    >>-CREATE--+--------+--INDEX--index-name------------------------>
               '-UNIQUE-'
    the WHERE NOT NULL is not available !

    I am looking for a way to create a UNIQUE constrains on the nullable column.

    this command line does not work (a error is generated)
    Code:
    ALTER TABLE MYTABLE ADD CONSTRAINT UNQ UNIQUE (MYFOREIGNKEY_ID)
    Thanks.

    Nassa.
    Not sure I understand the problem, does this article help:

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

  3. #3
    Join Date
    Feb 2013
    Posts
    21
    lelle12

    Your link is the z/OS documentation.
    I work on AIX system not z/OS.

    This option WHERE NOT NULL does not existe on Linux, UNIX and Windows system.

    The Linux, UNIX and Windows documentation is : http://pic.dhe.ibm.com/infocenter/db...Fr0000919.html

    Nassa.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,295
    not all that is available on one platform is also available on all platforms
    use the syntax from the platform you are working on
    unique constraint : col must be not null otherwise use unique index instead
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    To put the post from przytula_guy in another context, the zOS version of DB2 comes from a different code base with different options/syntax than UDB/LUW versions of DB2. The feature that you want (allowing NULL values in a UNIQUE constraint/index) is not available in the DB2 that you are running.

    The only answers that I can see is to either deal without the NULL values or upgrade to zOS DB2 which in turn implies that you need to either have or buy a zOS machine to run it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2013
    Posts
    21
    Ok,

    I do not need to INDEX, but only UNQIUE CONSTRAINT.
    So giving my case there is no solution.
    I have to manage the software UNIQUE CONSTRAINT ?

    Thanks.

    Nassa.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    You can create a pseudo unique constraint where the NULL meta-value is allowed duplicates but all other values are unique using a trigger. You could also apply the same kind of exception processing for a value like 0 or 1 if that suited your needs too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2013
    Posts
    21
    Ok,

    But I am not DBA, I don't know DB2.
    I just adapted a MySQL DDL to DB2.
    I did not know before DB2.

    Thanks.

    Nassa.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    One of the people that does more MySQL to DB2 migrations might know of a tool that does this automagically. It seems to me that there is a migration tool offered by IBM that will do this for you, but I don't know what it is called.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2003
    Posts
    279
    Quote Originally Posted by nassarane View Post
    lelle12

    Your link is the z/OS documentation.
    [....]
    Nassa.
    Huh, the link I provided is most certainly for LUW, what gives you the impression that it concerns z/OS? I'll repeat the link:

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

  11. #11
    Join Date
    Feb 2013
    Posts
    21
    Ok,

    The IBM convertion tools is IBM Data Movement Tool : https://www14.software.ibm.com/webap...US&source=idmt
    I tried to use it.
    It generated this :

    Code:
    CREATE UNIQUE INDEX "SCH"."UQ692_MYTABLE" ON "SCH"."MYTABLE"
    (
    "MYFOREIGNKEY_ID"  
    )
    ALLOW REVERSE SCANS
    ;
    It's not good, this solution supports only one key value to NULL.

    Thanks.

    Nassa.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    While I haven't tested it, the URL that Lelle12 posted ought to work in any current version of DB2 as long as there is a suitable PK or unique column to use.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Feb 2013
    Posts
    21
    Hi all,

    I tried the Serge RIELAU method suggested by Lelle12. It works!
    Thank you very much for your help, and Lelle12 Pat Phelan.

    Nassa.

Posting Permissions

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