how to determine not null constraint in db2

nemo_m · #1 (**permalink**) 09-28-10, 04:56

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.

ARWinner · #2 (**permalink**) 09-28-10, 08:02

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

nemo_m · #3 (**permalink**) 09-29-10, 02:05

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.

ARWinner · #4 (**permalink**) 09-29-10, 08:22

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

nemo_m · #5 (**permalink**) 10-01-10, 00:38

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech