Resources

db2list_candidate_tables_for_compression.ksh

Topic: Code Place

Subtopic: DB2 for LUW



This script lists candidate tables for compression. A text file is generated with information about the server, instance, database, # tablespaces, tablespace types (D=DMS, S=SMS), schema, table name and row count (cardinality) from DB2 SYSCAT catalog views. Output can be imported into a spreadsheet and used to help plan a compression project. db2 connect to Database echo "Identifying tables that are candidates for compression." db2 -x "select a.tbspace,b.tbspacetype,digits(a.card),a.tabschema,a.tabname from syscat.tables a,syscat.tablespaces b where a.tabschema not like 'SYS%' and a.tbspace=b.tbspace and a.card > 0 and a.type in ('T','S') order by a.card desc,a.tbspace,a.tabschema,a.tabname" > FileName.dat cat FileName.dat | while read Tbspace TbspaceType Card TabSchema TabName do echo '"'$(hostname)'","'${DB2INSTANCE}'","'Database'","'${Tbspace}'",
"'${TbspaceType}'","'${TabSchema}'","'${TabName}'","'${Card}'"' | tee -a FileName_excel.txt done rm FileName.dat echo "Candidate tables for compression located in FileName_excel.txt"



Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.