Welcome to Sustainable Software

Sustainable Software Ltd

Free code downloads for the DB2 UDB Database (Linux, Unix, Windoze)

It took a long time to figure some of this stuff out! Periodically we'll add a fresh snippet of reusable application or administration code. Most of it is my own work, but sometimes the original germ of a creative idea came from elsewhere, and sometimes colleagues and I have been through an iterative process of improvement and refinement. I would like to thank especially Enzo 'Nonsenzo' Siri, Dan 'The Man' Day and Steve Harris (California, not Nottingham). You're very welcome to use and share all code here, in the hope that the time it saves you will allow you to do something more worthwhile!

All software provided here is 'copyleft' - that is, you are positively encouraged to download it, modify it as you see fit and distribute it free of charge, in the best traditions of Open Software and worldwide collaboration.

There are only 3 conditions:

You keep comment lines specifying the original author and purpose in place

You comment any amendments to the original in a clearly marked amendment history.

You do not sell or charge for it.

Usual disclaimers: we accept no responsibility, give no warranty, no guarantees express or implied, at your own risk blaa blaa etc.

All software provided through these pages has been tested on 'real' DB2 systems, up to the version shown; much of it is in production use at client sites. As time goes on, you may need to replace deprecated functions as new DB2 versions are released.

File notes Comments

DB2 Sudoku Puzzle Solver! Solve any Sudoku puzzle the relational way! - declarations and constraints, not procedures. All instructions in the readme file. Can be turned into an app - open to offers to collaborate. May 2014.

Track database size (ksh93) Track database growth over time by partition, tablespace and key filesystem. July 2013.

Compare 2 Databases (ksh93) Compares the DDL structure of 2 databases, one of which can be on a remote server and instance, for most common database object types. Easily extended. April 2013.

DB2 Housekeeping Suite (ksh93) Modular maintenance library for most DB2 housekeeping tasks. Uses function library below, saves days of sweat! Unpack in instance home and follow instructions under MAINTENANCE/A_MAINTENANCE_README. Last update 17 Oct 2013.

DB2 Function Library (ksh93) Modular and easily extensible function library for many common DB2 operations.All your standard logging and error handling in one place! Unpack in instance home and follow instructions in FUNCTIONS/1_README. Last update 19 June 2013.

DB2 Availability and Performance Nagios Monitoring Suite (ksh93) Far more extensive, current and robust than standard Nagios DB2 plug-ins. Unpack in instance home and follow instructions under the readme. Last update 19 June 2013.

DB2 instance and database startup for root users (ksh93) A robust, comprehensive and defensive alternative to any vendor-supplied script we have seen - including IBM's own scripts! June 2013.

DB2 instance and database shutdown for root users (ksh93) As above. June 2013.

Report and repair SQL Replication differences (ksh93) Intended for situations where differences are known to exist, but not at volume. April 2013.

db2cols (ksh) Is it reasonable of DB2 to expect you to type out a long list of column names, when it already knows what they are! This utility adds a DB2 column list at the edit insertion point.

SMART RUNSTATS Checker (tar.gz) A smarter way to report tables with stale statistics and optionally RUNSTATS them, without using automatic table maintenance features.

Schema Comparison (ksh) Nifty utility to compare 2 schemata for tables, rowcounts, views, aliases, nicknames, indexes,constraints triggers and routines.

Information Constraint Checker (ksh) Utility to check whether unenforced referential constraints have integrity.

Family Trees (sql) A set of recursive queries to display parent child relationships.

Backup,Prune,Delete 3-in-1 (ksh) Coordinated Backup, Prune History and transaction log cleardown - all in one!

V9.1 online REORG suite (tar.gz) Update for V8.2 entry - Complete online REORG suite for V9.1 including RUNSTATS and best index selection algorithm, fully commented.

Transaction Log full? (ksh) A simple utility to identify the culprit connection, its status and any lock chain dependencies

csv column headers (sql) Export comma-separated column headers along with your DB2 data, in 3 different ways! This is just to make it easier for end-users importing DB2 data into spreadsheet packages.

distribution checker (ksh) Check DPF table data are evenly distributed across the partitions for the tablespace.

strip repeat blanks (sql) Recursive SQL to remove surplus spaces from text strings, leaving just one space between words. Useful for name and address cleanup and VARCHAR stripping.

Integer to date conversion (sql function) sic

Decimal to date conversion (compound sql function) sic

unhex (sql) DB2 has a built-in scalar HEX function, but nothing in reverse. Displays hex data as a character string (particularly useful for CHAR FOR BIT data and some CLOB substrings).

pension (ksh) A little light-hearted fun: use DB2 to calculate the wait until you can draw a pension and display the results in a highlighted box.

top_pagers (ksh93) Discover if DB2 memory has been swapped out to paging space. This is really bad for performance but no DB2 monitoring tools or built-in AIX commands cover it in one step.

best_pagesize.sql Calculates a 'best fit' pagesize for application tables. See comments for further detail.

online_reorgs (tar.gz) DB2 UDB Special! Complete online REORG suite for V8.2 including RUNSTATS and best index selection algorithm, fully commented. A V9 version exists, above.

type1_index (ksh) Discover and convert any type-1 indexes left over since migrations from earlier releases.

BO DB2 Harmony.doc Analysis and suggested configuration for Business Objects reporting over DB2 UDB databases.

test_indoubt (ksh) Simple background or foreground continuous check for indoubt transactions in distributed environments.

find_invalid_procedures (sql) Identify any invalidated, compiled stored procedures still hanging around after upgrade to V8.2; a db2rbind will generally take care of these.

5NF.sql A table is in fifth normal formal form if all columns form part of the key. 5NF tables need special treatment in SQL Replication, since an update needs to be treated as a paired insert/delete. Find all database tables in fifth normal form, and update the SQL Replication REGISTER table.

Feedback
Was this useful? Please tell us of your experience with the download software. We will not use your personal details other than to respond if you request a reply. Please send feedback, online beer tokens, book vouchers etc. to feedback@sustainablesoftware.net

File notes	Comments
DB2 Sudoku Puzzle Solver!	Solve any Sudoku puzzle the relational way! - declarations and constraints, not procedures. All instructions in the readme file. Can be turned into an app - open to offers to collaborate. May 2014.
Track database size (ksh93)	Track database growth over time by partition, tablespace and key filesystem. July 2013.
Compare 2 Databases (ksh93)	Compares the DDL structure of 2 databases, one of which can be on a remote server and instance, for most common database object types. Easily extended. April 2013.
DB2 Housekeeping Suite (ksh93)	Modular maintenance library for most DB2 housekeeping tasks. Uses function library below, saves days of sweat! Unpack in instance home and follow instructions under MAINTENANCE/A_MAINTENANCE_README. Last update 17 Oct 2013.
DB2 Function Library (ksh93)	Modular and easily extensible function library for many common DB2 operations.All your standard logging and error handling in one place! Unpack in instance home and follow instructions in FUNCTIONS/1_README. Last update 19 June 2013.
DB2 Availability and Performance Nagios Monitoring Suite (ksh93)	Far more extensive, current and robust than standard Nagios DB2 plug-ins. Unpack in instance home and follow instructions under the readme. Last update 19 June 2013.
DB2 instance and database startup for root users (ksh93)	A robust, comprehensive and defensive alternative to any vendor-supplied script we have seen - including IBM's own scripts! June 2013.
DB2 instance and database shutdown for root users (ksh93)	As above. June 2013.
Report and repair SQL Replication differences (ksh93)	Intended for situations where differences are known to exist, but not at volume. April 2013.
db2cols (ksh)	Is it reasonable of DB2 to expect you to type out a long list of column names, when it already knows what they are! This utility adds a DB2 column list at the edit insertion point.
SMART RUNSTATS Checker (tar.gz)	A smarter way to report tables with stale statistics and optionally RUNSTATS them, without using automatic table maintenance features.
Schema Comparison (ksh)	Nifty utility to compare 2 schemata for tables, rowcounts, views, aliases, nicknames, indexes,constraints triggers and routines.
Information Constraint Checker (ksh)	Utility to check whether unenforced referential constraints have integrity.
Family Trees (sql)	A set of recursive queries to display parent child relationships.
Backup,Prune,Delete 3-in-1 (ksh)	Coordinated Backup, Prune History and transaction log cleardown - all in one!
V9.1 online REORG suite (tar.gz)	Update for V8.2 entry - Complete online REORG suite for V9.1 including RUNSTATS and best index selection algorithm, fully commented.
Transaction Log full? (ksh)	A simple utility to identify the culprit connection, its status and any lock chain dependencies
csv column headers (sql)	Export comma-separated column headers along with your DB2 data, in 3 different ways! This is just to make it easier for end-users importing DB2 data into spreadsheet packages.
distribution checker (ksh)	Check DPF table data are evenly distributed across the partitions for the tablespace.
strip repeat blanks (sql)	Recursive SQL to remove surplus spaces from text strings, leaving just one space between words. Useful for name and address cleanup and VARCHAR stripping.
Integer to date conversion (sql function)	sic
Decimal to date conversion (compound sql function)	sic
unhex (sql)	DB2 has a built-in scalar HEX function, but nothing in reverse. Displays hex data as a character string (particularly useful for CHAR FOR BIT data and some CLOB substrings).
pension (ksh)	A little light-hearted fun: use DB2 to calculate the wait until you can draw a pension and display the results in a highlighted box.
top_pagers (ksh93)	Discover if DB2 memory has been swapped out to paging space. This is really bad for performance but no DB2 monitoring tools or built-in AIX commands cover it in one step.
best_pagesize.sql	Calculates a 'best fit' pagesize for application tables. See comments for further detail.
online_reorgs (tar.gz)	DB2 UDB Special! Complete online REORG suite for V8.2 including RUNSTATS and best index selection algorithm, fully commented. A V9 version exists, above.
type1_index (ksh)	Discover and convert any type-1 indexes left over since migrations from earlier releases.
BO DB2 Harmony.doc	Analysis and suggested configuration for Business Objects reporting over DB2 UDB databases.
test_indoubt (ksh)	Simple background or foreground continuous check for indoubt transactions in distributed environments.
find_invalid_procedures (sql)	Identify any invalidated, compiled stored procedures still hanging around after upgrade to V8.2; a db2rbind will generally take care of these.
5NF.sql	A table is in fifth normal formal form if all columns form part of the key. 5NF tables need special treatment in SQL Replication, since an update needs to be treated as a paired insert/delete. Find all database tables in fifth normal form, and update the SQL Replication REGISTER table.