Sometimes you just have to think out of the box. Sometimes there isn’t an answer on google. Sometimes you have to bend the laws of physics to get the answer. Sometimes you get lucky.
I had a problem, I desperately needed to relocate where my instance was physically installed and move it from one filesystem to another. The only help I could find involved using a DB2MOVE and relocating everything from instance to database.
There had to be a better way. Learning from an experience with IBM support many years ago I applied a fix in a way not originally taught to me.
So, let me say that the method below is not blessed by IBM or Ember Crooks. I have done this successfully more than once but may be using commands as they were not intended. Use this at your own risk.
To move your instance directory in a few easy steps:
1. Backup database and instance information.
Database Filesystem, DB2SET parameters, Instance Filesystem
2. Shut down the database and instance cleanly, possibly using IPCLEAN.
3. Uncatalog the database.
4. Drop the existing instance.
5. Have your SA change the home directory of the instance ID to new location.
6. Create a new instance with the same ID, port settings, etc.
(It will auto-create the instance in a new home directory)
Double check your DBM and DB2SET parameters, recreate if needed.
db2set PARAMETER=VALUE
7. Start DB2.
Notice when you switch to the instance your home directory is new and the instance binaries are now installed there.
8. Catalog database with original database name and path.
9. Activate database, check for clean error log.
10. Confirm instance is in new location and all is well.
This process, with SA availability, takes me less than five minutes. I’ve successfully used it to move roughly 5-10 instances with no impact after the fact. Hopefully this will provide you with a sneaky workaround to a unusual problem.
Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: mkrafick
Mike’s blog posts include:
10 Minute Triage: Assessing Problems Quickly (Part I)
10 Minute Triage: Assessing Problems Quickly (Part II)
Now, now you two play nice … DB2 and HACMP failover
Technical Conference – It’s a skill builder, not a trip to Vegas.
Why won’t you just die?! (Cleaning DB2 Process in Memory)
Attack of the Blob: Blobs in a Transaction Processing Environment
Automatic Storage Tablespaces (AST): Compare and Contrast to DMS
DB2 v10.1 Column Masking
Automatic Storage (AST) and DMS
Mike, fantastic shortcut method that I literally just employed due to being in the same boat because of a prior consultant’s bad installation. The onsite DB2 DBA, whom I have a lot of respect for, had cobbled together a Rube Goldberg type of solution using dbrelocatedb, rsync, db2move and some fairy dust. I showed her your solution and it worked like a charm and mgmt said they would be sending you a case of their finest malt beverage. Hope iDUG is great this year, and keep up the great postings.
And that, ladies and gentlemen, is why I like to blog. You made me smile ear to ear. If I can make someone else not go through the same pain I did – all the better.
It’s funny, at IDUG, I was asked by some IBM’ers why Id idn’t use relocatedb. In short I didn’t see anyone successfully use it for this purpose. It was either not successful or it became another Rube Goldberg.
Thank you for the feedback!
Mike – Great post.. Few months ago, I tried few options to change (relocate) the home directory of an instance owner ID but I was not able to do it. I even opened a PMR with IBM and it did not go too far. That one was on AIX 7.1. I will try giving a shot as you suggested. Thank you.
Let me know Pavan, I would be interested to hear.
[…] Mike’s blog posts include: 10 Minute Triage: Assessing Problems Quickly (Part I) 10 Minute Triage: Assessing Problems Quickly (Part II) Now, now you two play nice … DB2 and HACMP failover Technical Conference – It’s a skill builder, not a trip to Vegas. Why won’t you just die?! (Cleaning DB2 Process in Memory) Attack of the Blob: Blobs in a Transaction Processing Environment Automatic Storage Tablespaces (AST): Compare and Contrast to DMS DB2 v10.1 Column Masking Automatic Storage (AST) and DMS Reloacting the Instance Home Directory […]
[…] Mike’s blog posts include: 10 Minute Triage: Assessing Problems Quickly (Part I) 10 Minute Triage: Assessing Problems Quickly (Part II) Now, now you two play nice … DB2 and HACMP failover Technical Conference – It’s a skill builder, not a trip to Vegas. Why won’t you just die?! (Cleaning DB2 Process in Memory) Attack of the Blob: Blobs in a Transaction Processing Environment Automatic Storage Tablespaces (AST): Compare and Contrast to DMS DB2 v10.1 Column Masking Automatic Storage (AST) and DMS Reloacting the Instance Home Directory […]
Not sure why IBM (or anyone else) would not “bless” this procedure; it’s all DB2 commands. For me this does not give what I need, as the database data does not move with the instance. I am looking to COMPLETELY move an instance so that I can remove the old location. Of course IBM says yes it can be done using a redirected restore, but their steps do not work.
The location of the instance is completely separate from the locations of all databases. Each database can be moved using either a redirected restore or db2move.