Tuesday, March 22, 2011

Changing installpaths using db2iupdt command

Hello All,

Here is my current install path : "/opt/ibm/db2/V9.1". Is it possible to change the install path to a new directory once DB2 is installed with a instance/database created.

The answer to this is NO, but we could install DB2 again to the path you wanted and run the db2iupdt command to update the instance to the existing path. Here is a simple test case:

1) Here is the current db2level which confirms my DB2 is installed at "/opt/ibm/db2/V9.1"
DB21085I  Instance "db2v91" uses "64" bits and DB2 code release "SQL09019" with
level identifier "020A0107".
Informational tokens are "DB2 v9.1.0.10", "s110109", "MI00382", and Fix Pack
"10".
Product is installed at "/opt/ibm/db2/V9.1".

2) Using db2_install script I installed DB2 to a different path now : "/opt/ibm/db2/V91FP10". Here is the db2ls output which confirms that I have 2 installs for V9.1 FP-10

db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.1                9.1.0.10       10                            Wed Mar  9 06:35:00 2011 CST             0
/opt/ibm/db2/V91FP10             9.1.0.10       10                            Mon Mar 21 19:32:47 2011 CDT           0

3) Now make sure the instance that is associated with the default install path(i.e /opt/ibm/db2/V9.1) is down completely before we run db2iupdt command: In my testcase it is 'db2v91'

4) Login as root and move to the 'instance' directory under newly created install path and run the 'db2iupdt' command with -D option. Using -D option moves an instance from a higher code level on one path to a lower code level installed on another path.

cd /opt/ibm/db2/V91FP10/instance

./db2iupdt -D db2v91
DBI1070I Program db2iupdt completed successfully.

5) Now logged in as the instance owner 'db2v91' and ran the db2level command and I can see it reflects to the new path:

DB21085I  Instance "db2v91" uses "64" bits and DB2 code release "SQL09019" with
level identifier "020A0107".
Informational tokens are "DB2 v9.1.0.10", "s110109", "MI00382", and Fix Pack
"10".
Product is installed at "/opt/ibm/db2/V91FP10".

Good luck!!

Thanks
Satya 

Monday, March 21, 2011

SECADM

Hello All,

We might get confused from the DB2 9.7 info center documentation regarding who actually gets the SECADM authority. Starting DB2 9.7 we have noticed many changes when it comes to the  security(Eg: The instance owner does not have SECADM authority by default which is unacceptable.)

Coming to SECADM question, since it is a Database authority and specific to a database, the user who creates the database will get the SECADM authority.

Here is a test case...logged in as the user 'v97fp2' and created a 'db1' database.

- db2 create db db1
DB20000I  The CREATE DATABASE command completed successfully.

- db2 connect to db1

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.2
 SQL authorization ID   = V97FP2
 Local database alias   = DB1

- db2 "select grantee, securityadmauth from syscat.dbauth"

GRANTEE          SECURITYADMAUTH
---------------- ---------------
V97FP2                  Y --> Automatically got the SECADM authority.
PUBLIC                  N

  2 record(s) selected.

NOTE: SECADM authority can be granted only by the security administrator (who holds SECADM authority) and can be granted to a user, a group, or a role.

Thanks
Samyn

db2imigr vs db2iupgrade

Hello All, 

If you are migrating the instance from DB2 V9.1 to V9.5:

1) Since we are migrating the instance from V9.1 to V9.5, make sure we meet the Prerequisites before starting the migration process.

Migration for DB2 servers:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.migration.doc/doc/c0011933.html

2) Once we meet the  Prerequisites, we migrate the instance to 9.5 using "db2imigr" command.

3) Once done migrating the instance to 9.5, we migrate the database's under that instance using "migrate database" command.

====

If you are migrating the instance from DB2 V9.1 to V9.7:

1) Since we are migrating the instance from V9.1 to V9.7, make sure we meet the Prerequisites before starting the migration process:

Upgrade essentials for DB2 servers:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.qb.upgrade.doc/doc/c0023734.html

2) Once we meet the  Prerequisites, we migrate the instance to 9.7 using the "db2iupgrade" command since db2imigr is deprecated.

3) Once done upgrading the instance to 9.7, we upgrade the database's under that instance using "upgrade database" command.

We use db2imigr and db2iupgrade commands only when upgrading the instance from a lower version. db2imigr is deprecated starting DB2 9.7 and we use db2iupgrade instead.

Thanks
Samyn

Tuesday, March 8, 2011

Best method to do DB2 Offline Backup's

db2 connect to <database_name>
db2 quiesce database immediate force connections
db2 terminate
db2 deactivate db <database_name>
db2 backup db <database_name> to <directory_path>
db2 connect to <database_name>
db2 unquiesce database
db2 terminate

I suggest you to follow the above steps if you have a application server which has retry logic and attempting to make a connection to the database all the time.