db2 restore with automatic storage tablespace

I was trying to restore a db from inst1 to inst2. The db path (file system paths) in inst1 were different as compared to inst2. The file systems between the 2 have very restricted permissions. Usually when I am restoring a database from one system to another I am used to creating a blank database with correct path and then in the restore I specify REDIRECT keyword. And my assumption in doing so was the restore will happen to the path that is there in the database (not in the backup set). So, when I tried to restore a db from inst1 to inst2, my assumption did not work, received following error message,

SQL0970N  The system attempted to write to a read-only file.  SQLSTATE=55009

I looked at the db2diag.log and found following message.

INSTANCE: inst2                NODE : 000         DB   : {existing db name}
EDUID   : 9933                 EDUNAME: db2loggr ({existing db name}) 0
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:10
MESSAGE : ZRC=0x840F0001=-2079391743=SQLO_ACCD "Access Denied"
          DIA8701C Access denied for resource "", operating system return code
          was "".
CALLED  : OS, -, mkdir
OSERR   : EACCES (13) "Permission denied"
DATA #1 : File name, 78 bytes
/home/inst1/db2fs/inst1/NODE0000/{old db name}/LOGARC/inst2/{existing db name}/NODE0000/C0000001/
DATA #2 : signed integer, 4 bytes
488
DATA #3 : signed integer, 4 bytes
488
DATA #4 : String, 105 bytes
Search for ossError*Analysis probe point after this log entry for further
self-diagnosis of this problem.

Looks like db2 still tries to restore to the path that is there hidden in the backup set. In my case inst2 was trying to restore on inst1's (db) file system paths, to which it did not had access permissions.

So whats the solution ? use following format of restore command.

db2 "RESTORE DB {dbname in image} FROM {backup image file path} TAKEN AT {timestamp} ON {db2fs_path} DBPATH ON {db_path} INTO {existing db name}
db2 "rollforward db {existing db name} complete"

And this the case with "automatic storage tablespace paths" and I guess the DMS should not have this issue.

How to share Windows folder on Ubuntu (guest OS)

My system environment looks as follows - Windows host with Ubuntu 10 guest OS in VMWare

I followed the below URL from wmware to setup Windows share on Ubuntu. http://www.vmware.com/support/ws3/doc/ws32_running9.html. But this did not quiet work in a single given the fact that I am new to Ubuntu/Window intraction.

Try command "smbmount" if this works that means the smb pieces are install, else install them

if smb components are not install then run "apt-get install smbfs" - ensure that the installation goes correct.

Create a directory that will be mapped to window share "mkdir /home/gvphubli/win_shared"

Here is the last command in this process

mount -t smbfs -o username=win_user,password=win_user_pwd 

//win_system_IP/Java_Learning  /home/gvphubli/win_shared 

In case of CentOS or Fedora follow these steps,

Test for samba, run this command in terminal "smbd -V" if samba is installed and available the version number will be displayed, else you will see bash error.

If you see error, install samba using following steps, I used my root privileges to do so.

yum install samba-swat samba-client

After successfull installation, use the same command "smbd -V" to verify. If things look good go to next stop of creating a mount, here is he command

mount -t cifs -o username=win_user,password=win_user_pwd //win_system_IP/Java_Learning  /home/gvphubli/win_shared

Note: one difference in this command for Ubuntu v/s RH (Fedora and CentOS) is the change from "smbfs" to "cifs"

You should be all set to share files between host and guest OS

...

db2 restore with mix of full and incremental backups

Following are the steps to restore db2 database when you have mix of db backup with full, incremental and delta are involved.This illustration is based on Windows system.

text in blue are db2 commands
text in green are my comments
text in black are output seen on command line console.

db2 create db zzz_tmp
db2 restore db zzz incremental automatic from C:\ taken at 20110707154108 into zzz_tmp
db2 rollforward db zzz_tmp to end of backup and complete

-- you will see following message, if roll-forward is not complete

SQL4970N Roll-forward recovery on database "ZZZ_TMP" cannot reach the
specified stop point (end-of-log or point-in-time) on database partition(s)
"0". Roll-forward recovery processing has halted on log file "S0000026.LOG".

-- if you see a message as listed above you need to get(copy or restore from TSM)
-- the specified log file to zzz_tmp's active log directory

db2 rollforward db zzz_tmp to end of backup and complete

-- if successfull (you mostly will be) you will see following message

Rollforward Status


Input database alias = zzz_tmp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read = Log files processed = S0000026.LOG - S0000026.LOG
Last committed transaction = 2011-07-07-22.41.13.000000 UTC


DB20000I The ROLLFORWARD command completed successfully.

-- do some smoke testing, by connecting and possibly quering the db

db2 connect to zzz_tmp

Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = db2admin
Local database alias = ZZZ_TMP

db2 merge backup - utility test

I was very excited about "db2 merge backup" tool. As per the documentation this tool is supposed to merge incremental and delta backup and merge it with the most recent full db backup prior to these incremetnal or delta backups. More details about this utility can be found here

So, if you have this tool working you take one full backup and there are after you just do delta backups and keep merging them with you full backup. Here after you never take full db backups again.

Out of excitement I decided to test this tool. Downloaded this tool and set it up on my window workstation. I did my full backup, ran couple of database transactions and took the delta backup. Below is the control file options and the command used and the output as well.

Below are the contents from "SampleMergeCtrlFile.txt" control file used in the test. I used the control file examples from the following link here

MERGE DATABASE ZZZ
PART (0)
START FROM "C:" TAKEN AT 20110707153755
END FROM "C:" TAKEN AT 20110707154108
OUTPUT TO "C:\Merged\"

Below is the my db2 merge command output.

db2mbk -i DB2INST1 -f SampleMergeCtrlFile.txt -s -p 0
MBKI445W Warning: you are still using a temporary license.
         you need to enroll DB2 Merge Backup for Linux, UNIX, and Windows within 29 days.
MBKM031I DB2 Merge Backup for Linux, UNIX, and Windows 01.01.000(110505) 32 bits 7/7/2011 (Windows)
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----10----+----11----+----12----+----13--
000001 MERGE DATABASE ZZZ
PART (0)
START FROM "C:" TAKEN AT 20110707153755
END FROM "C:" TAKEN AT 20110707154108
OUTPUT TO "C:\Merged\"
MBKB005I MBK control step start   : 16:00:40.287.
MBKB037I Merged backups will not be registered into DB2.
MBKB005I [0] MBK control step start   : 16:00:40.302.
MBKB006I [0] MBK control step end     : 16:00:40.318.
MBKB006I MBK control step end     : 16:00:40.318.
MBKB053I MBK run step start       : 16:00:40.318.
MBKB007I [0] MBK inventory step start : 16:00:40.318.
MBKB021I [0] The partition 0 backup image taken at 20110707154108 is involved in the merge (type INCREMENTAL ONLINE DATABASE, device DISK)
MBKB008I [0] MBK inventory step end   : 16:00:40.349.
MBKB009I [0] MBK merge step start     : 16:00:40.349.
MBKB028I [0] The utility will build the partition 0 backup image taken at 20110707154109 (type FULL ONLINE DATABASE, device DISK)
MBKB021I [0] The partition 0 backup image taken at 20110707153755 is involved in the merge (type FULL ONLINE DATABASE, device DISK)
MBKB010I [0] MBK merge step end       : 16:00:41.849.
MBKB054I MBK run step end         : 16:00:41.849.
MBKI442I MBK successfully ended: real time -> 0m1.562530s


As per the output the merge was successfull. Then I attempted to restore above merged backup set to a new database. Remember merged backup image is equivalent to full backup. The attempted restore failed and db2 complained that the backup image is corrupt!!

db2 restore db zzz from C:\ taken at 20110707154109 into zzz_tmp
SQL2529W  Warning!  Restoring to an existing database that is different from
the backup image database, and the alias name "ZZZ_TMP" of the existing
database does not match the alias name "ZZZ" of the backup image, and the
database name "ZZZ_TMP" of the existing database does not match the database
name "ZZZ" of the backup image. The target database will be overwritten by the
backup version. The Roll-forward recovery logs associated with the target
database will be deleted.
Do you want to continue ? (y/n) y
SQL2530N  The backup image is corrupted.  Restore of database from this backup
image is not possible.

I felt may be my original backups (full and delta) are corrupt. I restored those backups sets instead of the merged and they work just fine. This concluded that the db2 merge utility is doing something funky.

If you find my test is not appropriate, correct me by leaving a comments.

Update: I was testing this on db2 express 9.7.0 on Window XP. Looks like this issue got resolved after I upgraded db2 to FP4. Later I also fond that if you don't want to upgrade db2 you can modify the merge utility configutaion with following file entry "vendor_object = no" in the configuration file located at "DB2TOOLS\MergeBackup11\cfg\db2mbk.cfg"

db2 error SQL10007N - Reason code: "3"

Have you ever encountered errors like,


SQL10007N Message "-1390" could not be retrieved.  Reason code: "3".


or


Error encountered during the Add record action: Failed to lock the registry


I usually encounter these issues when we are managing multiple instances on the same box. So, to set the db2 instance context for commands like db2stop, db2start etc. The way we set context is to change the environment variable to DB2INSTANCE, the command is


export DB2INSTANCE=db2inst1


After setting the instance context using above command, all the instance level command and all the db2 command will be directed to that instance, in our case all the commands like db2stop, db2start, db2 list db directory etc. will be directed to "db2inst1"


However instance name used in export command is case-sensitive. So accidentally if you typed


export DB2INSTANCE=DB2INST1


Then when you run any db2 commands they will complain with error message


SQL10007N Message "-1390" could not be retrieved.  Reason code: "3".


You can't even do  "db2 ? SQL10007N" to get help. For your reference here is the help text



SQL10007N  Message "" could not be retrieved. Reason code:
      "".


Explanation: 


The requested message could not be retrieved from the message
file. Reason code is one of the following: 
1. The environment variable "DB2INSTANCE" is either not set, or is set
   to an invalid instance. Correct it and try again.
2. The message file was found, but the file could not be opened because
   of permissions. Check the file permissions of the files under the
   message directory.
3. The message file could not be found. Either the file does not exist,
   or the directory the message file should be in does not exist. Check
   that either a default directory or a directory with the same name as
   the 'LANG' environment variable exists under the message directory.
4. The requested message does not exist in the message file. Either the
   message file is outdated, or it is the wrong one.
5. Either DB2CODEPAGE is set to a code page which the database does not
   support, or the client's locale is not supported by the database.
6. An unexpected system error occurred. Try execution again. If problem
   persists, contact your IBM representative.
7. Not enough memory. An attempt to get private memory failed. Try
   again.


User response: 


Reissue the command after verifying the following: 
*  ensure that the DB2INSTANCE environment variable is set to the
   correct literal string for the username attempting this command
*  ensure that correct home directory is specified for the username
   attempting this command (i.e. in the /etc/passwd file)
*  ensure that the LANG environment variable is set to the correct value
   for the installed language, or is set to 'C', on the username
   attempting this command
*  Even if the message file is outdated, you can still find up-to-date
   information about the message in the database server documentation.


If all of the above are correct and the error still occurs, reinstall
DB2.




If you do db2ilist you get the instance list along a message..


Error encountered during the Add record action: Failed to lock the registry


These are some of the side effects when you don't set the DB2INSTANCE variable correctly.



db2 drop db error !

I was trying to drop database but was getting following error,

SQL1035N  The database is currently in use.  SQLSTATE=57019

So, while I logged in as instance owner, I tried following.

-- first attempt

db2 list applications global

No sessions were listed except my own local connections. Then I tried following set of statements.

-- second attempt

db2 deactivate db myDB

db2 drop database myDB

Still no luck, I kept getting the same message. Then I tried another variation

-- third attempt

db2 connect to myDB

db2 quiesce database immediate force connections

db2 connect reset

db2 terminate

db2 drop database myDB

Finally I tried this,

-- final attempt

db2 terminate

db2 drop database myDB

I was puzzled, why my third attempt did not work and the final one worked?

I thought it might be the sequence of statements. I looked into the "db2 terminate" command documentation on db2 information center, and her is what it says,

Although TERMINATE and CONNECT RESET both break the connection to a database, only TERMINATE results in termination of the back-end process.

It is recommended that TERMINATE be issued prior to executing the db2stop command. This prevents the back-end process from maintaining an attachment to a database manager instance that is no longer available.

What all this means is always use "db2 terminate" if you really want to disconnect your connections/sessions before doing db level options like db drop etc.

db2 luw 9.7 - Event monitors

Quick steps to setup event monitoring to capture the db2 sql activity and get to the output to some readable format.

-- db2 event monitoring for tracking sql statements etc.

-- first created for troubleshooting PowerDesigner issue.

-- sql statement to create evt monitor

CREATE EVENT MONITOR evtmon_pd_issue

FOR STATEMENTS

WHERE AUTH_ID = 'USER_ID'

WRITE TO FILE '/home/db2admin' -- this is a directory path

MAXFILES 5

MAXFILESIZE 1024

NONBLOCKED

APPEND;

db2 "set event monitor evtmon_pd_issue state 1"

db2 "set event monitor evtmon_pd_issue state 0"

--After stopping the evt monitor you will see a file in "/home/db2admin" which has word "evt" in its name and has sequential number at the end if there are many files created

-- this will format out the evt monitor captured data

db2evmon -path '/home/db2admin' >> evtmon_pd_issue.out

-- in *.out file you see output in readable format (txt)

-- ones done with your experiements, drop the evt monitor(s)

db2 "drop event monitor evtmon_pd_issue"

For more information on event monitors go to

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.mon.doc/doc/c0055386.html

db2 luw monitoring using WLM

Quick steps to setup WLM (workload manager) and its related tables. This is an extension to event monitoring. In this sample I am using default system WLM service class

-- create workload (uses default system service classes)

CREATE WORKLOAD "WL_POWERDESIGNER" SYSTEM_USER ('APPUSER_ID')

    COLLECT ACTIVITY DATA

    ON COORDINATOR DATABASE PARTITION

    WITH DETAILS AND VALUES;

GRANT USAGE ON WORKLOAD WL_POWERDESIGNER TO PUBLIC;

SET CURRENT SCHEMA = "WLM";

-- following create statements will create all the required tables for capturing the trace output.

CREATE EVENT MONITOR "DB2STATISTICS"

FOR STATISTICS

WRITE TO TABLE

    CONTROL (TABLE "CONTROL_DB2STATISTICS", IN USERSPACE1 ),

    HISTOGRAMBIN (TABLE "HISTOGRAMBIN_DB2STATISTICS", IN USERSPACE1 ),

    QSTATS (TABLE "QSTATS_DB2STATISTICS", IN USERSPACE1 ),

    SCSTATS (TABLE "SCSTATS_DB2STATISTICS", IN USERSPACE1 ),

    WCSTATS (TABLE "WCSTATS_DB2STATISTICS", IN USERSPACE1 ),

    WLSTATS (TABLE "WLSTATS_DB2STATISTICS", IN USERSPACE1 )

    AUTOSTART;

CREATE EVENT MONITOR "DB2ACTIVITIES" FOR ACTIVITIES

WRITE TO TABLE

    ACTIVITY (TABLE "ACTIVITY_DB2ACTIVITIES", IN USERSPACE1 ),

    ACTIVITYSTMT (TABLE "ACTIVITYSTMT_DB2ACTIVITIES", IN USERSPACE1 ),

    ACTIVITYVALS (TABLE "ACTIVITYVALS_DB2ACTIVITIES", IN USERSPACE1 ),

    CONTROL (TABLE "CONTROL_DB2ACTIVITIES", IN USERSPACE1 )

    AUTOSTART;

CREATE EVENT MONITOR DB2THRESHOLDVIOLATIONS

       FOR THRESHOLD VIOLATIONS

       WRITE TO TABLE

       THRESHOLDVIOLATIONS (TABLE THRESHOLDVIOLATIONS_DB2THRESHOLDVIOLATIONS IN USERSPACE1 ),

       CONTROL (TABLE CONTROL_DB2THRESHOLDVIOLATIONS IN USERSPACE1 )

       AUTOSTART;

-- activate the EM's

-- SET STATE = 1 and STATE = 0 to deactivate

SET EVENT MONITOR DB2ACTIVITIES STATE 1;

SET EVENT MONITOR DB2STATISTICS STATE 1;

SET EVENT MONITOR DB2THRESHOLDVIOLATIONS STATE 1;

Now run some queries on the given database and data will be captured in below tables.

-- following are the table to look at, I used activity_id to link the two.

SELECT * FROM WLM.ACTIVITYSTMT_DB2ACTIVITIES

SELECT * FROM WLM.ACTIVITYVALS_DB2ACTIVITIES

Note the “CREATE WORKLOAD” statement has “WITH DETAILS AND VALUES” clause, which means we capture the statements and also the host variables passed to the statements if any. So, the “ACTIVITYVALS_DB2ACTIVITIES” table will have multiple rows for every row in “ACTIVITYSTMT_DB2ACTIVITIES” table depending on how many variables were passed in the statements.

For more details go to Creating workloads and defining limits