November 2011

DB2 server authentication and file permissions

On one of our DB2 servers users recently started seeing errors similar to this:

db2 => connect to bq1 user sapr3
Enter current password for sapr3:
SQL30082N  Security processing failed with reason "42" ("ROOT CAPABILITY REQUIRED").  SQLSTATE=08001

and warnings similar to this in db2diag.log:

2011-11-08-16.50.58.947610-300 E124555E379         LEVEL: Warning
PID     : 6414                 TID  : 47208304407824PROC : db2star2
INSTANCE: db2bq1               NODE : 000
FUNCTION: DB2 UDB, base sys utilities, sqleCheckForNonRootInstance, probe:1
MESSAGE : ADM0509E  A non root capable instance of DB2 has been detected.
          Limited functionality will be available.

When we started investigating the problem this looked like a DB2 install had been run from a user other than root, see this IBM note.

However ,we soon discovered though that someone had recently changed the file permissions of every file and directory under INSTHOME to 755.
Apparently they were having problems connecting to the database and thought this would solve the problem.

Anyway, the point of this post is to describe a simple way of fixing the file permissions of an entire DB2 directory tree.
The only proviso is that you need another, working, DB2 server to generate the required script.

• On a DB2 server that is working correctly.
  Log on as the instance owner and generate a script to change permissions:

  db2bq1 51> cd $INSTHOME
  db2bq1 52> ls -l
  total 488
  drwxr-xr-x 37 root   root       4096 Jul  1 16:13 db2_software
  drwxrwsr-t 20 db2bq1 dbbq1adm   4096 Jul  1 17:02 sqllib

  (there may be other files/directories in INSTHOME, db2_software and sqllib are the only ones we are interested in).

• Generate the scripts

  db2bq1 53> find sqllib -name \* -printf "chmod %m %p \n" > change-perms-sqllib
  db2bq1 54> find db2_software -name \* -printf "chmod %m %p \n" > change-perms-db2_software

• Shutdown DB2 (and any SAP processes) on the target server.

• Copy the files to INSTHOME on the target server and run them AS ROOT
  Change <INSTHOME> to the correct directory
  e.g.

  sh-3.2# cd <INSTHOME>
  sh-3.2# ls -l
  total 8
  -rwx------  1 root   root     460654 Nov  9 09:16 change-perms-db2_software
  -rwx------  1 root   root       6007 Nov  9 09:16 change-perms-sqllib
  drwxrwxrwx 37 root   root     4096 Nov  4 23:10 db2_software
  drwxrwxrwx 20 db2bq1 dbbq1adm 4096 Nov  7 11:31 sqllib
  sh-3.2# ./change-perms-sqllib > change-perms-sqllib.log 2>&1
  sh-3.2# ./change-perms-db2_software > change-perms-db2_software.log 2>&1
  sh-3.2# ls -l
  total 480
  -rwx------  1 root   root     460654 Nov  9 09:16 change-perms-db2_software
  -rw-r--r--  1 root   root       3038 Nov  9 09:17 change-perms-db2_software.log
  -rwx------  1 root   root       6007 Nov  9 09:16 change-perms-sqllib
  -rw-r--r--  1 root   root        925 Nov  9 09:16 change-perms-sqllib.log
  drwxr-xr-x 37 root   root       4096 Nov  4 23:10 db2_software
  drwxrwsr-t 20 db2bq1 dbbq1adm   4096 Nov  7 11:31 sqllib

  There will be a few errors in the logs where permission changes have been generated for temporary files that don't exist on the target server, these can be ignored.

Did you know #7

In a crontab it is possible to:

• Specify shortcuts for execution times

• Define variables which can subsequently be used in command strings, e.g.

  # Redirect cron mail. Any output generated by commands will be sent to this address.
  MAIL="user@example.com"
  # Local DB2 SID
  DB2SID=db2inst1
  #
  # Script executed 05:01 daily and cron passes DB2SID as argument to script
  # Output of script is emailed to address defined above
  01 05 * * * /path/to/some_script.sh $DB2SID
  #
  # Same script as above but this time output is redirected
  # In this no email will be sent as no output will be generated
  01 05 * * * /path/to/some_script.sh $DB2SID > /path/to/some_script.log 2>&1