Quiz: Where is a good place to find information about DB2 fixpacks?

Nobody is perfect, nothing is complete. So is DB2. But where do you find information about fixpacks and fixes for DB2 for Linux, UNIX, and Windows? Where can you look up what features have been added as part of a fixpack?

Post your sources as comments or let me know in other ways.

Black Friday and DB2 recovery objects - new db2adutl feature

Black Friday and Cyber Monday are coming up, busy days for many database systems working in the background of brick and mortar and online shops. Will the infrastructure keep up with the traffic? Will response times be acceptable for shoppers and store personnel alike? How much revenue is made with which types of products? Many questions at the start of the holiday season.

There is an interesting feature in DB2 that shipped in version 9.7 FP5 (see here for the db2adutl feature announcement) and in version 10.1 to manage high peak load on DB2 systems. db2adutl now allows to UPLOAD recovery objects (backup images, archived logs) to TSM in a very simple way without breaking the log chain. Some examples of the new UPLOAD feature are shown here. The idea is to first archive to a fast disk instead to TSM, so that during a peak like Black Friday or Cyber Monday log archiving can keep up with the rest of the system. Later on, the recovery objects can be moved from disk to TSM to allow TSM-based recovery if needed.

Happy Thanksgiving (for those in the USA) and interesting shopping deals for all of us...

BTW: As of DB 10.1 archived logs can also be compressed reducing what needs to be written out.

Where to find information about granted privileges in DB2

Yesterday, I reveived a question about where to find specific metadata in DB2. The customer wanted to find out which privileges had been granted within a database and they were aware that db2look can produce this list. But where does this information come from?

Let's start with a glimpse at db2look. It is the "DB2 statistics and DDL extraction tool" and can be used to produce the DDL statements for the objects inside a database. There are also options specific to handling authorizations, i.e., the privileges (see the -x, -xdep, -xd, and -xddep options). All the statements that db2look produces are based on special data stored in the database, the so-called metadata. Those tables that hold the metadata are called System Catalog in DB2 and Data Dictionary in Oracle. The system catalog than can be queried using regular SELECT statements because the information is provided in tables (users can stay within the data mode, the relation model).

DB2 offers the metadata in two different sets of views. The views in the SYSCAT schema basically have all the metadata. The views in the SYSSTAT schema have a subset of the data and have (some) updatable columns, so that object statistics can be changed (for good or for worse...). The views are built on top of the internal catalog tables which are managed in the SYSIBM schema. It is advised to only use the SYSCAT and SYSSTAT views because structure of the internal tables can change without warning whereas the external catalog views are kept intact.

Now, where can the information about granted privileges be found? The DB2 Information Center has a so-called "Road map to catalog views" which is a listing of all the offered metadata found in the SYSCAT and SYSSTAT views. All the views that end in *AUTH carry authorization information, i.e., data about privileges. To give some examples, in SYSCAT.INDEXAUTH you can find out who has CONTROL privilege on an index, SYSCAT.ROLEAUTH is useful to see who has ADMIN authority for granting a specific role, and finally, as a complex example, SYSCAT.TABLEAUTH manages all the table privileges like insert, update, delete, select, alter, control, and even more privileges.

Administration tools for DB2 access that information, procedures and scripts can select data from these views, and of course applications and users. Which of these views are made available for the PUBLIC is up to the administrators. Remember, it's an honor, not a privilege...

Comments or questions?

Where are the soap bars...? - Database Appliances

Do you remember the days when there were soap bars in the hotel bathrooms? The days when you began washing the hands and then realizing that the soap bar had to be unwrapped? The room service would restock soap, clean the soap dish, and empty the waste basket. Now enter the age of appliances. Many hotels have switched over to "appliances", wall-mounted soap dispensers. They allow for savings for the hotels due to less effort (service, restocking, ...) and are "greener", i.e., the dispensers cause significantly less waste.

The same transition is also happening for database servers. Instead of purchasing hardware (server, storage, network equipments, etc.) and software separately and assemble and test them on-site in a lengthly process, appliances often are delivered ready-to-use (see IBM PureData System). The key to success is to have the right ingredients. Would you use a soap dispenser that says "good for hands, hair, and dishes"? You would expect to have cream soap, shampoo and conditioner, and other cleaning liquids in separate appliances. The same is true for database appliances. That is the reason why IBM (other than some competition ("supports both OLTP and OLAP" )) is offering PureData System for Transactions, PureData System for Operational Analytics, and PureData System for Analytics. Different workloads and requirements need database appliances with different ingredients.

BTW: I still like a nicely shaped, slightly scented bar of soap. It makes the right gift...