How to Select NOT NULL Columns in DB2

db2db2-luw

I've got a script I'm using against a DB2 database, and I'd like it to be able to check that all required values are present prior to trying an INSERT— so I'd like to select a list of the not null columns from the table I'm about to insert into. Since the table definition may change I'd like to do this rather than using a static list so the script won't break if there's a new not null column added.

Is there a way to get these values from a DB2 query, maybe against syscat?

VERSIONNUMBER VERSIONTIMESTAMP    AUTHID    VERSIONBUILDLEVEL
-------------------------------------------------------
10010400    2016-12-03 16:46:01.509317  ADMIN   s140509

Best Answer

If you are on LUW you can use the view syscat.columns:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001038.html

For a certain table:

select colname 
from syscat.columns 
where tabschema = 'DB2INST1' 
  and tabname = 'STAFF' 
  and nulls = 'N' 
order by colno;
Related Question