Sunday, July 29, 2007

db2_all and RAHUSER

If you need to run a command on all or a subset of database partitions, you can use db2_all.

For example, to change the database configuration LOGFILSZ to 100 for sample database on all database partitions that are specified in node configuration file, we can use the following command:

db2_all “UPDATE DB CFG FOR sample USING LOGFILSZ 100”

Similarly, if you have to change the database configuration only on partition 1, you can run the following command:

db2_all “<<+1<UPDATE DB CFG FOR sample USING LOGFILSZ 100”

If you have to change the database configuration on all the database partitions except partition 0, you can run the following command:

db2_all “<<-0<UPDATE DB CFG FOR sample USING LOGFILSZ 100”


On UNIX based platforms, db2_all uses the remote shell program specified by DB2RSHCMD registry variable. Starting with DB2 v8.1 FP 9, we can chose either rsh or ssh for DB2RSHCMD registry variable. If this registry variable is not set, rsh (or remsh for HP-UX) is used.

There is an environment variable RAHUSER, which can be used to specify the user ID under which the remote command is to be run on UNIX based platforms.

Though the primary purpose of db2_all is to run the command on all the database partitions that you specify, it can also be used for the purpose of running the command under a different user ID with the help of RAHUSER environment variable. Thus, db2_all becomes useful even in a single-partition database. However, db2_all is available with Enterprise Server Edition (ESE) only. So, if you have a Workgroup Server Edition (WSE) instance, you would not be able to use db2_all.

The above mentioned use of db2_all, which allows you to run a command under a different user ID is particularly helpful in situations where your task automation script always runs as a fixed user ID or a common user ID say ScriptUser and if you wish to run the commands inside those scripts under a different user ID say DB2CommandUser (e.g. a user with SYSDBA authorization). In such a scenario, you can use following two solutions:

1. Use the ssh or rsh command directly to run the commands inside script as DB2CommandUser.
2. Use db2_all instead to run the command inside script as DB2CommandUser. This allows you to have a generic script which can use either rsh or ssh remote shell program depending on the DB2RSHCMD registry variable.

If you are using ssh as remote shell program for db2_all, ssh authentication should be configured such that ScriptUser can run the commands under the login of DB2CommandUser without prompted for any password or passphrase. This can be verified by running the following command while logged in as ScriptUser.

ssh DB2CommandUser@<HostName>

pwd

If the above command runs successfully without prompting for any password or passphrase, it indicates that the ssh authentication has been setup correctly. In the above command <HostName> can be localhost or a remote host depending on where do you want to run the command.

Similarly, if you are using rsh remote shell program for db2_all, the .rhosts file under the DB2CommandUser home directory must include the following line in order to allow ScriptUser to run command under DB2CommandUser login.

<HostName>
ScriptUser

Where, <HostName> is the host name of computer where ScriptUser exists. The permission of .rhosts file should not allow the Group or Other to write to this file. The recommended permission for .rhosts is 600, though 644 will also work.

On Windows platform, db2_all uses “DB2 Remote Command Service” to run the commands on all database partitions that you specify. Also, RAHUSER should be the logon account associated with the DB2 Remote Command Service.

Friday, July 27, 2007

Copy and Drop Schema

Do you need to make the exact copy of all the objects in a given schema? Are you planning to write a script for this task? If you are using DB2 version 9, perhaps you can avoid writing this script and still perform the same task using a single statement.

DB2 version 9 provides a new stored procedure called ADMIN_COPY_SCHEMA that can be used to copy all the objects from one schema to another schema name. The syntax for this stored procedure can be found at


ADMIN_COPY_SCHEMA at DB2 Info Center

In addition to the copy schema stored procedure, DB2 9 also offers a drop schema stored procedure to drop all the objects in a schema. The stored procedure is called ADMIN_DROP_SCHEMA. The syntax of this stored procedure can be found at

ADMIN_DROP_SCHEMA at DB2 Info Center