Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: how to determine not null constraint in db2

    The problem is how to determine not null constraint in db2, which we added using alter table command.
    Please see the commands below-
    1) I created employee table -> create table employee ( Name varchar(30),age smallint ) ;
    2) When we describe it says nulls as Yes -> describe table employee
    3) SELECT nulls FROM syscat.columns WHERE tabname = 'employee' AND colname = 'name' AND TABSCHEMA = CURRENT SCHEMA
    0 row(s) returned successfully.
    4) Adding not null constraint ->ALTER TABLE employee ADD CHECK (Name IS NOT NULL)
    5) commit and reorg table employee
    6) SELECT nulls FROM syscat.columns WHERE tabname = 'employee' AND colname = 'name' AND TABSCHEMA = CURRENT SCHEMA
    0 row(s) returned successfully.

    How to check if name field in employee table is marked not-nullable? I tried SYSSTAT.COLUMNS, syscat.checks, syscat.tabconst but of no help.

    Can you provide us query for it.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is your use of strings against the catalog. You created the table using the normal method of specifying the table/column names (not quoting them). When you do this, DB2 will put the names into the catalog as all upper case letters, so you need to query it that way:

    Code:
    SELECT nulls FROM syscat.columns WHERE tabname = 'EMPLOYEE' AND colname = 'NAME' AND TABSCHEMA = CURRENT SCHEMA
    Andy

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Thanks Andy, but this query does not return correct data for columns which are later altered to not nullable. This is what I found

    Column created first and later added not null check constraint following query will give correct result-

    select * from syscat.checks where tabname ='EMPLOYEE' and TEXT like '%NAME IS NOT NULL%'



    Columns created with not null constraint (ALTER TABLE employee ADD column test smallint not null) following query will give correct result-

    select * from syscat.columns where tabname = 'EMPLOYEE' AND colname = 'TEST'



    Is there some other better way to find out by providing tablename and column name to query and it returns 'Y' or 'N' ( may be join or if /else of both query and it should return 'Y' or 'N')

    As current code fires a query and it returns 'Y' or 'N' which is used at many places and we want to avoid refactoring.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know what you problem is. I tried doing every combination and it seems to work just fine:

    Code:
    -->create table andy.qqq(col1 int not null,col2 int)
    DB20000I  The SQL command completed successfully.
    -->select nulls,colname from syscat.columns where tabschema = 'ANDY' and tabname = 'QQQ'
    
    NULLS COLNAME
    ----- --------------------------------------------------------------------------------------------
    N     COL1
    Y     COL2
    
      2 record(s) selected.
    
    -->alter table andy.qqq alter column col2 set not null
    DB20000I  The SQL command completed successfully.
    -->select nulls,colname from syscat.columns where tabschema = 'ANDY' and tabname = 'QQQ'
    
    NULLS COLNAME
    ----- --------------------------------------------------------------------------------------------
    N     COL1
    N     COL2
    
      2 record(s) selected.
    
    -->alter table andy.qqq alter column col2 drop not null
    DB20000I  The SQL command completed successfully.
    -->select nulls,colname from syscat.columns where tabschema = 'ANDY' and tabname = 'QQQ'
    
    NULLS COLNAME
    ----- --------------------------------------------------------------------------------------------
    N     COL1
    Y     COL2
    Andy

  5. #5
    Join Date
    Sep 2010
    Posts
    3
    Thanks a lot Andy. My alter table sql was not correct.
    I was using
    ALTER TABLE employee2 ADD CHECK (Name IS NOT NULL);

    and hence
    select nulls,colname from syscat.columns where TABSCHEMA = CURRENT SCHEMA and tabname = 'EMPLOYEE3'

    was not giving correct data.

    So now I will modify alter table sql.

    Thanks for trying out all combinations.
    Last edited by nemo_m; 10-01-10 at 02:53.

Posting Permissions

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