blog.mycado.fr

DB2 Data compression

Disk storage is not cheaper, take place, make noise, so, for large warehouses (or huge volumes database), the cost of the storage become important. IBM DB2 have a solution, with the data compression named “Venom” technology, reducing storage requirements, improving I/O, and providing quicker data access from the disk.

DB2 uses a dictionary based algorithm for compressing data records. DB2 9 will scan tables, and search for duplicate occurrences, then assign a short numeric key to each entries. You will understand better with a picture:

Each new occurences of “Sophie”, “New York”, “IBM US”, will be respectively assign to “(01)”, “(19)” and “(09)”. This is very useful on large table. Each time a new row is added to the table, DB2 will automatically compress these new data.

Seems to be good, let’s try on our DB2 now. To enable the compression,  we should turn on the “COMPRESS” option on the table.

CREATE TABLE Users COMPRESS YES

or use alter if the table is already created:

ALTER TABLE Users COMPRESS YES

Typically, we can save an average ratio of 45 to 75 percent of our storage space. Next time, we’ll see how to use the inspect tool, to determine the compression ratio.

Tags: compression, DB2

Customize your DB2 CLP prompt

You have a nice DB2 CLP prompt, looks like “db2>”, but you want more, you want a efficient prompt. It is possible with DB2 to add or modify your CLP ! DB2 have a registry variable named DB2_CLPPROMPT which allows us to define the prompt to be used in the CLP interactive mode.

To define this variable, let’s use the DB2set command:

Db2set DB2_CLPPROMPT="db2isgreat> "

Our new DB2 Prompt will be “db2isgreat> “. It’s much more better, but we can do more. DB2_CLPPROMPT registry variable can contain the tokens %n, %ia, %d, %da and %i:

• %n – New line
• %ia – Authorization ID of the current instance attachment
• %d – Local alias of the currently connected database
• %da – Authorization ID of the current database connection
• %i – Local alias of the currently attached instance

Now, let’s try with these tokens:

Db2set DB2_CLPPROMPT="%ia@%i, %da@%d> "

With an instance attachment to instance “DB2″ with authorization ID “mycado”. Database “sample” with authorization ID “mycadoax”, will return something like:

MYCADO@DB2, MYCADOAX@SAMPLE >

You can now everytime where you are, and one which instance/database you’re working on.

Tags: CLP, DB2, db2set, prompt