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

    Unanswered: DB2 z/OS V9.1 UNIQUE column problem.

    Hi all,

    I wrotten a DDL script to create my schema on DB2 10.1 for linux. This DDL is OK.
    When I try to run this DDL on DB2 z/OS V9.1 without to create Database (whith défault database)
    When I try to run this DDL on the database I created this line does not work :

    Code:
    ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUECONSTRAINT UNIQUE (MY_UNIQUECODE);
    Code:
    TABLE MYDB.MY_TABLE DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PRIMARY OR UNIQUE KEY. SQLCODE=-625, SQLSTATE=55014, DRIVER=4.14.113
    Script creation of database :
    Code:
    CREATE DATABASE MYDB               
           STOGROUP SYSDEFLT               
           BUFFERPOOL BP32K                
           INDEXBP BP8K0                   
           CCSID UNICODE ;                 
                                           
    CREATE TABLESPACE TSFORMYDB             
    IN MYDB                   
           USING STOGROUP SYSDEFLT          
           PRIQTY 15000                      
           SECQTY 9000                       
           ERASE NO                         
           LOCKSIZE ROW                        
           LOCKMAX SYSTEM                     
           BUFFERPOOL BP32K                      
           CLOSE NO                    
           CCSID UNICODE ;             
                                       
     COMMIT ;
    Why my DDL work correctly on the default database and not on the database I created ?

    Thanks.

    Nassa.
    Last edited by nassarane; 06-14-13 at 06:55.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I believe this is expected as per:
    IBM Information Management Software for z/OS Solutions Information Center

    " If the table is in a table space that is implicitly created, and no unique index is defined on the identified columns, DB2 will automatically create a primary index."


    Test results:

    My db/tablespace:

    alter table test add constraint tuniq unique (c1)
    --ter table test2 add constraint tuniq unique (c1)
    ---------+---------+---------+---------+---------+---------+---------+-------
    DSNT408I SQLCODE = -625, ERROR: TABLE SSDBDC.TEST DOES NOT HAVE AN INDEX TO
    ENFORCE THE UNIQUENESS OF THE PRIMARY OR UNIQUE KEY
    DSNT418I SQLSTATE = 55014 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXIAB5 SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000064' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION



    Default db/tablespace:

    alter table test2 add constraint tuniq unique (c1)
    ---------+---------+---------+---------+---------+---------+---
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

  3. #3
    Join Date
    Feb 2013
    Posts
    21
    Thanks DB2Gril,

    The solution is to replace this :
    Code:
    ALTER TABLE MY_TABLE
      ADD CONSTRAINT MY_UNIQUECONSTRAINT (MY_UNIQUECODE);
    By that :

    Code:
    CREATE UNIQUE INDEX MY_UNIQUECONSTRAINT ON MY_TABLE(MY_UNIQUECODE);
    That's it ?

    Nassa.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Yes, create unique index and then alert table:

    create unique index tu on test (c1); 00000620
    ---------+---------+---------+---------+---------+---------+---------+---------+
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
    ---------+---------+---------+---------+---------+---------+---------+---------+
    alter table test add constraint tuniq unique (c1) 00000700
    --ter table test2 drop constraint tuniq 00000800
    --ter table test2 add constraint tuniq unique (c1) 00000900
    ---------+---------+---------+---------+---------+---------+---------+---------+
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
    ---------+---------+---------+---------+---------+---------+---------+---------+

  5. #5
    Join Date
    Feb 2013
    Posts
    21
    Thanks very much DB2Girl !

    Nassa.

  6. #6
    Join Date
    Sep 2009
    Posts
    66
    No need to create unique index. I can add a unique constraint with 9.5 and 9.7 Linux.

Posting Permissions

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