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

    Unanswered: 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
    280
    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,440
    Provided Answers: 8
    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
    15,325
    Provided Answers: 31
    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
    15,325
    Provided Answers: 31
    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
    15,325
    Provided Answers: 31
    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
    280
    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
    15,325
    Provided Answers: 31
    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
  •