Su casa es mi casa: Restore in DB2 and obtaining SECADM and other privileges

One of the changes from DB2 9.5 to DB2 9.7 was the enhanced security, including extended abilities for SECADM and less or changed for SYSADM and DBADM. The idea was to introduce more security and prevent data theft. However, as we have learned during life, all good comes with some drawbacks, and so it is for security as well. It reduces what an administrator can do and cuts down on flexibility (remember how taking a flight was more than a decade ago?).

A common problem is with taking backups of a system and try to use them, e.g., for testing, on a different system. Users like SECADM need to be recreated in order to make things work on the system using the restored database. And that's why a "shortcut" was introduced in DB2 9.7 FP2. If the DB2 registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES is set to ON, then SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user performing the RESTORE DATABASE, typically one of the system administrators. Different methods of restore are supported.

High availability, epilepsy, functional MRI, and DB2 commands

In the past I had written a couple times about epilepsy and some computer-related aspects. Today, I want to point you to some more interesting aspects of your brain and high availability. As written earlier, one of my sons has epilepsy which was caused by a brain tumor. Last January, my son and I had a very interesting session in the hospital where a functional MRI (fMRI or fMRT) was performed. The reason has to do with the high availability of the brain.

What happens when you use DB2 with HADR or pureScale and one machine fails? If all is configured right and it indeed works, tasks should move over to one of the machines still up. When a child has epilepsy, depending on the type and seriousness, parts of the brain can get damaged. However, the brain is flexible and to some degree self-repairing. As a result, the functional "processors" of the impacted area can move to a different part of the brain. In DB2 you can monitor the HADR environment and query the state of the pureScale cluster: Who is primary, who is in peer state, on which machine are what services active? But how do you find out where in your brain the speech center is located (actually one of many)? The solution that helped in our case was to perform a functional MRI. It showed where important parts were located and whether it was safe later during surgery to operate in those areas where planned.

Conclusions: DB2 is simple to administrate compared to planning brain surgery.

BTW: Many hospitals use DB2 for patient records and much more.

"Remember to flush" - Your options in DB2

When I hear the word "flush", I always have to think about the movie "The Man Who Knew Too Little" and the scene where Wallace/Spenser is told "Remember to flush".

In DB2 there are statements to flush the event monitors, to flush the package cache, and to flush the optimization profile cache. Well, with DB2 pureScale, the feature for application cluster transparency, you have one more option. It is for another cache, a big data cache. The new statement is FLUSH BUFFERPOOLS. It writes out all dirty pages from the buffer pools to disk. The less dirty data is buffered, the shorter the recovery time in the event of failures. Depending on your strategy and configuration for the page cleaners, this is a new statement to remember. Remember to flush...

Some DB2 commands for the pureScale feature that saved my box...

DB2 nanoCluster

Some days ago I told you about db2greg and how I used it to repair a DB2 pureScale demo cluster. Well, there are more commands that help to repair an inconsistent cluster, some are new DB2 commands. I will describe them in this article (so I can quickly find them later on).

Before I start let me tell you about the demo cluster which is nicknamed "DB2 nanoCluster". It is inexpensive hardware like Intel Atom CPUs, 4 GB of RAM and Gigabit Ethernet. The system uses SUSE Linux and DB2 9.8 (which is the pureScale feature). It consists of 3 machines, one used as storage node and two others hosting a DB2 member and a cluster caching facility each. It can be built for few hundred Euros or US Dollars and fits nicely into a box or between shelves (see the picture). Because of the (insufficient) hardware and the difference to a production system, there are also performance differences and, important, missing redundancy. If you pull 2 power cables, including the one on the storage box, expect to have problems. Pull an Ethernet cable, have fun as a single cable would be redundant connectivity to the storage server in a production system, multiple Infiniband adapters, and external network to the application cluster. Anyway, the nanoCluster and DB2 work nonetheless - at least in most cases, hence the repair commands...

What commands are useful to repair inconsistent configuration?

"db2iupdt -fixtopology" comes in handy when adding or removing cluster components resulted in a timeout and inconsistent state (someone pulling a cable by mistake?). It is used to repair the instance configuration.

"db2cluster -cm -repair -resources" can be used to fix inconsistencies between the db2nodes.cfg file and the cluster manager resource model (the RSCT layer).

Sometimes there are alerts by the DB2 members that need to be taken care of. "db2cluster -cm -list -alert" displays them, "db2cluster -cm -clear -alert" clears them.

The "db2instance -list" command shows the current status of the entire cluster.

That's it for today, my cluster is up and running. Do you want to build and operate your own DB2 cluster? Take a look at this page.

New Redbook: Security Functions of IBM DB2 10 for z/OS

A quick recommendation: Few days ago IBM published a new and very interesting Redbook titled "Security Functions of DB2 10 for z/OS". In the 450+ pages book the authors touch base and dig into all aspects of security, such as access control, cryptography, auditing and many more.

DB2 pureXML for the Protein Data Bank - Managing Atoms and Molecules with XML

Yesterday a new interesting article was published on developerWorks, "Managing the Protein Data Bank with DB2 pureXML". It describes how scientists with highly complex data (the Protein Data Bank), atoms and molecules that make up protein, can benefit from a highly sophisticated database management system, DB2.

At the core of the solution is pureXML to reduce the schema complexity (see the graphic for a relational design in the article). Compression is used to keep the database small and keep I/O to a minimum. Now add database partitioning across multiple nodes, range partitioning and multi-dimensional clustering to further help organize the data and to cut complexity and improve performance.

What a good example of combining many of DB2's features for the benefit of science.

BTW: This work was possible through the IBM Academic Initiative which as part of the benefits allows free use of DB2.

Things for the curious: db2greg

It's Friday and it is a slow day. How about I tell you about a DB2 tool that until 2 weeks ago I had never heard of and never had used before? It is a tool that has been in DB2 for an eternity (I found an entry in the Information Center for version 8). I am talking about db2greg which is used to view and change the DB2 global registry.

hloeser@BR857D67:~/Downloads$ db2greg -dump
V,DB2GPRF,DB2SYSTEM,HENRIK,/opt/ibm/db2/V9.7,
V,DB2GPRF,DB2ADMINSERVER,dasusr1,/opt/ibm/db2/V9.7,
I,DB2,9.7.0.4,hloeser,/home/hloeser/sqllib,,1,0,/opt/ibm/db2/V9.7,,
V,DB2GPRF,DB2INSTDEF,hloeser,/opt/ibm/db2/V9.7,
V,DB2GPRF,DB2FCMCOMM,TCPIP4,/opt/ibm/db2/V9.7,
S,DB2,9.7.0.4,/opt/ibm/db2/V9.7,,,4,0,,1305817517,0
S,DAS,9.7.0.4,/opt/ibm/db2/V9.7/das,lib/libdb2dasgcf.so,,4,, ,,
I,DAS,9.7.0.4,dasusr1,/home/dasusr1/das,,1,0,/opt/ibm/db2/V9.7/das,,

Using the "-dump" option as shown above lists the current entries, here for a DB2 9.7. But why would I have to use db2greg and how did I find out about it? The reason is DB2 9.8 which basically is the pureScale feature that brings application cluster transparency. By lack of coffee and sleep and too much enthusiasm I had pulled too many power cables at the same time on a demo machine (nanoCluster). That resulted in some "extra time" in bringing back the machine to "fully operational". In that process I had to clean up some system entries, e.g., like shown in this example in the Information Center.

You will notice in the example that some information DB2 needs to know about the GPFS and the RSCT clusters are stored in the global registry (PEER_DOMAIN, GPFS_CLUSTER, etc.). If parts of a system are manually (re-)build, the registry may become inconsistent and that's when db2greg options like "-delvarrec" and "-addvarrec" are needed to patch up the registry.

For me the mishap ended up with some extra work, but lots of new things learned. And remember, patch responsibly...