Temporary tablespace and space limitations

System temporary tablespaces are used for sorts and joins.They are supposed to release the space after the operation , but there is a registry variable DB2_SMS_TRUNC_TMPTABLE_THRESH that controls the number of extents that can be left out after the operation.If it is set to zero then all the extents are deleted after the sort/join operation

db2ilist issue after upgrade

If you upgrade some of your instances to V9.5 and the remaining stay at 8.2,You might see this issue.db2ilist lists the instances that are at the current instance level.Lets suppose you have db2inst1,db2inst2 and db2inst3 and only db2inst3 is upgraded to V9.5 and remaining are at 8.2.If you are attached to db2inst3 and if you hit db2ilist,you would only see db2inst3 there.

There is a workaround for this problem.You can use the db2greg utility and parse the verbose output.Below is an example that i used to list all instances that start with 'db2inst' prefix:


db2greg -dump -v grep -i db2inst cut -d',' -f4

SQL0444N after fixpack upgrade

Have you ever noticed SQL0444N reason code 4 after migration or a fixpack upgrade?If so here is the solution.

Please check if a link for db2clifn.a exists under /sqllib/function like below:

lrwxrwxrwx 1 root db2iadm 46 2008-12-08 22:33 db2clifn.a -> /opt/ibm/db2/V9.5/fixpack2/function/db2clifn.a


If it does not exist you should run db2iupdt again to fix the broken links.And while doing so you might come across another error

DBI1282W The database manager configuration files could not be merged. The original configuration file was saved as /home/db2inst2/sqllib/backup/db2systm.old. (The original instance type is ese. The instance type to be migrated or updated is ese.)

So better save the dbm configuration before you do the db2iupdt.

Fixpack upgrades on V9.5

Starting from V9.5 db2iupdt is automated after fixpack install.Also the binding of packages happen during the first connection after the upgrade.But the major change that I observed is with alternative fixpacks.Consider a situation where you have 2 instances serving two different applications on a server.If you want to maintain one instance at V9.5 fixpack0 and the other at V9.5 fixpack2 you got to follow a new strategy from now on.You have to use db2_install and not InstallfixPack to accomplish this.Let the two instances be db2inst1 and db2inst2 (both at V9.5 fixpack0).You want to upgrade only db2inst2 to fixpack2.The command to be used in this case will be some thing like ./db2_install -b /opt/ibm/db2/V9.5/fixpack2.And after the install cd to /opt/ibm/db2/V9.5/fixpack2/instance and do a db2iupdt for db2inst2.

REDIRECTED RESTORE ISSUE

If you are restoring into a different alias using into clause, you should use the original database name in db2 restore continue statement.If you use the alias you will recieve the error:DB21080E No previous RESTORE DATABASE command with REDIRECT option was issuedfor this database alias, or the information about that command is lost.Refer to the following example.

EXAMPLE SCRIPT:

db2 restore db sample to /db2kk/db2inst1/SAMPLE1 into SAMPLE1 redirect without prompting;

db2 "set tablespace containers for 5 using (PATH "/db2kk/db2inst1/sample1")";

db2 restore db SAMPLE continue;

db2 rollforward db SAMPLE1 to end of logs and complete;

LOGINDEXBUILD in HADR environment

LOGINDEXBUILD parameter should be set in HADR environments.If this is OFF in HADR environments,index creation and reorgs will not be completely logged which will delay the failover process.The failover process is delayed because the index building occurs at the time of failover.

db2ckbkp to know the paths

db2ckbkp -T SAMPLE.0.db2inst1.NODE0000.CATN0000.20080401133802.001 grep -i name

The above command can be used to view tablespace paths from backup image without verifying the image
db2ckbkp -S SAMPLE.0.db2inst1.NODE0000.CATN0000.20080401133802.001

This one gives the storage paths from a backup image if autostorage option is being used.