0

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
  • Is this DB2 LUW or z/OS or OS/400 ? – Max Vernon Aug 29 '17 at 16:47
  • Looks like DB2 for LUW, 10.1 fp 4 – mustaccio Aug 29 '17 at 17:12
0

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;
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.