DB2 Table and Index Commands

IBM DB2 UDB for Linux, Unix and Windows Command Examples

Look at columns and data types of a table

=>db2 describe table schema.tabelename

Look at one column's data type.

=>db2 "describe select <colname> from schema.tablename"

Look at indexes on a table:

=>db2 describe indexes for table schema.tablename

Export data to a file in ixf format:

=>db2 export to tablename.ixf of ixf messages tablename.txt select * from SCHEMA.TABNAME

Import data from an ixf file:

=>db2 import from filename.ixf of ixf messages tablename.txt insert into SCHEMA.TABNAME

Import using Load which is faster (doesn't check constraints) and NONRECOVERABLE doesn't log anything.

=>db2 load from filename.ixf of ixf insert into SCHEMA.TABNAME nonrecoverable;

Build a file to set integrity on all tables that are in set integrity pending state.

=>db2 "select 'set integrity for '||tabschema||'.'||tabname ||' immediate checked;' from syscat.tables where type = 'T' and status = 'C'" > file.out

###Largest tables by number of pages in unix

db2pd -db <dbname> -tcbstats | awk '$8 >= "A"'| awk '{print $10+$12,$8,$7}' | sort -n | tail -30

########Range Partition

##add partition

alter table TABSCHEMA.TABNAME add partition PART28 STARTING('2013-09-20') ENDING('2013-11-20') EXCLUSIVE IN TBSP_NAME INDEX IN IDX_TBSP_NAME;

alter table TABSCHEMA.TABNAME add partition PART29 STARTING('2013-11-20') ENDING('2014-01-20') EXCLUSIVE IN TBSP_NAME INDEX IN ICX_TBSP_NAME;

##find max partition value

select substr(a.tabschema,1,8) , substr(a.tabname,1,30), max(a.HIGHVALUE) from syscat.datapartitions a, syscat.tables b where substr(a.tabschema,1,8) in (schema1', 'schema2') and a.HIGHVALUE <> ' ' and b.type ='T' group by a.tabname,a.tabschema