IBM Data Management magazine Issue 2, 2009

physical data models, and design the target data warehouse data model. It then enables you to build the extract and load process, using SQL data flows or your own command and control functions.

I have a large library of scripts, programs, and functions that I use for loading data, and the ability from within Design Studio to call external programs or scripts from a command box made it very fast and easy to convert some of the load programs I had already developed. You can also use the command box to call Informix High Performance Loader scripts from Design Studio; once you have the process and data transformation rules defined and tested, there is a separate Warehouse Administration Console you can use to manage, schedule, and run your load process.

Data compression in Informix 11.50.UC4

Another key announcement was the release of fix pack UC4 for Informix 11.50, which includes data compression. I was also in the beta for this, and besides saving space, compression may speed up your system. The standard benchmark I use (and the Fastest Informix DBA contest was based on this benchmark) showed about a 25 percent performance improvement when using compression. With compression, fewer pages need to be read into memory from disk, making the overall system perform better. The larger the system, the better this seems to work.

To enable compression on a server, run the following SQL command to execute a function in the sysadmin database:

EXECUTE FUNCTION sysadmin:task ("enable compression");

Next you can compress and uncompress specific tables. To compress a table, run the following SQL command to execute the function in the sysadmin database:

EXECUTE FUNCTION sysadmin:task

("table compress", "table_name", "database_ name", "table_owner");

Table 1: Common commands to compress, pack, and shrink tables

Turn on compression in a server EXECUTE FUNCTION sysadmin:task ("enable compression");

Estimate how much space may be saved by compressing a table

Create a compression dictionary without compressing a table

EXECUTE FUNCTION sysadmin:task ("table estimate_ compression", "table", "database", "owner"); EXECUTE FUNCTION sysadmin:task ("table create_ dictionary", "table", "database", "owner");

Compress a table (automatically creates a dictionary)

EXECUTE FUNCTION sysadmin:task ("table compress", "table", " database", "owner");

Compress a table and return the freed table space to the pool of free space

Uncompress a table

EXECUTE FUNCTION sysadmin:task ("table compress repack shrink", "table", "database", "owner"); EXECUTE FUNCTION sysadmin:task ("table uncompress", "table", "database", "owner");

Compress a fragment of a partitioned table EXECUTE FUNCTION sysadmin:task ("fragment compress", " partnum");

Consolidate space in a table without compressing it EXECUTE FUNCTION sysadmin:task ("table repack", "table", "database", "owner");

Consolidate space in a table, locking and taking the table offline, without compressing it Consolidate space in a fragment, locking and taking the table offline, without compressing it

EXECUTE FUNCTION sysadmin:task ("table repack_ offline", "table", "database", "owner");

EXECUTE FUNCTION sysadmin:task ("fragment repack_offline", "partnum");

Shrink a table and return space to the pool of free space

EXECUTE FUNCTION sysadmin:task ("table shrink", "table", "database", "owner");

Shrink a fragment and return space to the pool of free space

EXECUTE FUNCTION sysadmin:task ("fragment shrink", "partnum");

Replace table_name with the name of your table, database_name with the database, and table_owner with the name of the owner. These are the only two commands I had to add to my benchmark script to see the performance gains with compression. There are other commands that can be used to uncompress tables, control locking while compressing, and to display space savings. IBM also has an IDS Compression Estimator program that you can download from ibm.com/Informix to see the space savings.

Conference kudos

This spring was a great time for Informix with all the new features released, and the IIUG Informix Conference in Kansas City at the end of April topped it off. The conference could not have taken place without the hard work of a core group of volunteer users on the steering committee. I would really like to thank the conference planning committee for all their work that resulted in such a fine user conference.