How to select NOT NULL columns from DB2

Question

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

• Hannah Vernon: Is this DB2 LUW or z/OS or OS/400 ?
• mustaccio: Looks like DB2 for LUW, 10.1 fp 4

Answer 1

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;

Answer 2

According to the IBM DB2 UDB for iSeries SQL Reference V5R3 documents, the SYSCOLUMNS view contains an IS_NULLABLE column.

DB2 for z/OS 10 has a NULLS column that indicates if a column is nullable in SYSIBM.SYSCOLUMNS table

Potentially, SYSCAT.COLUMNS may have the IS_NULLABLE or NULLS column.

I'm not a DB2 expert, so this may or may not be applicable to your system.