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.

SNAPSHOT_DYN_SQL and decimal() function

select NUM_EXECUTIONS,(decimal(TOTAL_EXEC_TIME)/NUM_EXECUTIONS) as AVERAGEEXECTIME,STMT_TEXT from table(SNAPSHOT_DYN_SQL('',-1)) as SNAPDYN where NUM_EXECUTIONS > 0 order by AVERAGEEXECTIME desc fetch first 8 rows only".

For any queries like the one listed here on snapshot table function snapshot_dyn_sql,decimal() function can be used to get the time in subseconds.If you do not use decimal function, it would display the time in seconds only and a 0 would appear if your query response time is below a second

Viewing lockchains

If you have a lock wait situation you can use the following stored procedure to view the lockchains :

db2 call sysproc.am_get_lock_chns(15,?)

15 in the above call statement stands for application handle.Output would look like 15 --> 11 --> 7 It implies that 15 is waiting for 11 which inturn is waiting for 7

SQL stored procedure text

You can view a SQL stored procedure by doing the following select


db2 -x "select text from syscat.routines where routinename='TOTAL_RAISE'"

If you do not use the -x option and run the command as
db2 "select text from syscat.routines where routinename='TOTAL_RAISE'"

you would get some junk characters included ..Following is the example output without -x option

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE PROCEDURE total_raise ( IN p_min DEC(4,2) ,IN p_max DEC(4,2) ,OUT p_total DEC(9,2) )SPECIFIC total_raiseLANGUAGE SQLtr: BEGIN -- Declare variables DECLARE v_salary DEC(9,2); DECLARE v_bonus DEC(9,2); DECLARE v_comm DEC(9,2); DECLARE v_raise DEC(4,2); DECLARE v_job VARCHAR(15) DEFAULT 'PRES'; -- Declare returncode DECLARE SQLSTATE CHAR(5);
-- Procedure logic DECLARE c_emp CURSOR FOR SELECT salary, bonus, comm FROM employee WHERE job != v_job; -- (1)
OPEN c_emp; -- (2)
SET p_total = 0;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (3)
WHILE ( SQLSTATE = '00000' ) DO SET v_raise = p_min;
IF ( v_bonus >= 600 ) THEN SET v_raise = v_raise + 0.04; END IF;
IF ( v_comm < 2000 ) THEN SET v_raise = v_raise + 0.03; ELSEIF ( v_comm < 3000 ) THEN SET v_raise = v_raise + 0.02; ELSE SET v_raise = v_raise + 0.01; END IF;
IF ( v_raise > p_max ) THEN SET v_raise = p_max; END IF;
SET p_total = p_total + v_salary * v_raise; FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; END WHILE;
CLOSE c_emp; -- (4)END tr

6 byte RID support in V9

6 byte RID in V9

V8 and prior versions of DB2 limited the size of a RID to be 4 bytes.3 bytes for page and 1 byte for slot number.This limitation placed constraints on the tablespace sizes:
Pagesize--------Tablespace size limitation
4k page 64GB
8k page 128GB
16k page 256GB
32k page 512GB

V9 has 6 byte RID which allows for bigger tablespaces.4 bytes for page and 2 bytes for slot number.
Pagesize--------Tablespace size limitation
4k page 2TB
8k page 4TB
16k page 8TB
32k page 16TB

ALTER TABLESPACE TABLESPACENAME CONVERT TO LARGE statement can be used to convert tablespaces after the migratrion.It should be noted that only DMS tablespaces can be large.So,SMS tablespaces can not be large tablespaces even in V9.

HADR enhancements---db2haicu in V9.5

db2haicu stands for db2 high availability instance configuration utility.Db2 V9.5 has integrated HA feature which needs to be configured and maintained using db2haicu.
From version 9.5 there is no necessity to install heartbeat scripts seperately.Heartbeat scripts come with HADR and they need to be set up using db2haicu.

When db2haicu is run for the first time,it will search the database system for database configuration that is related to cluster configuration.Then it creates a cluster domain for instance if it does not exist.

db2haicu maintainance mode::

db2haicu will be run in maintainance mode once the cluster domain is created.Cluster elements such as databases or cluster nodes can be added or removed in maintainance mode.Also the failover mechanism can be defined using db2haicu in maintainance mode.

MDC---PART 1

Multi dimensional clustered tables can be used to improve performance in many cases.With clustered index you can only cluster data in one dimension.With MDC you can cluster data in multiple dimensions.The other difference between MDC and clustered index is that MDC does not require reorgs as the data is automatically clustered.Below is a create statement for a MDC table for MAKE base table.
CREATE TABLE MAKEMDC(
ID INT,
MAKE CHAR(30),
MODEL CHAR(30)
)
ORGANIZE BY (MAKE,MODEL)

CELL: Every unique combination of dimensions form a cell.A cell can have one or more blocks.
BLOCK:It is a block of pages that contain a unique combination.Its size(blocking factor) is equal to the extent size defined.
A cell for unique combination of FORD(make) FOCUS(model) will be like this

DESIGN OF THE MDC TABLES:
Number of cells can be estimated by executing a select with distinct clause on the dimension columns to arrive at the unique combinations of the dimension values.
For example, for the above mentioned MDC table,the number of cells can be known by executing the following query:
SELECT DISTINCT MAKE,MODEL from MAKEMDC;
A large value of C/R might indicate the wastage of space where C indicates the number of cells and R indicates the total number of rows in the table.So,dimension columns should be wisely chosen.Choosing a firstname column as dimension column with employeeid might generate lot of cells and thereby wastage of space.A column like state with 10 possible values might be a good candidate as it would have only 10 possible values

Space occupied per cell(SC) = R/C * average row size
If SC is less than (extentsize*pagesize) then there is a wastage of space.So,the design should be done in a way such that SC matches up the (extentsize*pagesize) value.
Following are the options that can be used to design MDCs optimally:
1)Decrease the extent size if the blocks are not filled up.Page size can be increased if there are too many blocks per cell
2)Granularity can be varied to decrease the cells ..For example a new dimensional column can be added to the totalbalance table defined as totalbalance/100 to decrease the cells.
3)New dimensional keys can be added or existing ones can be removed depending on the situation.
4)A larger extent size might help in reducing the I/O but it might impact the usage of the space.
5)It is always better to do a comparison with the base table before the MDC table is actually used.For example MAKEMDC mentioned above should be compared with the MAKE table(base table) interms of space usage and performance before the decisions are actually taken.

Reference::Advanced DBA Certification Guide and Reference for DB2 UDB by Snow and Thomas

GET_DBSIZE_INFO procedure and database size

GET_DBSIZE_INFO procedure can be used to calculate database size.It takes 4 parameters in total.3 of them are output parameters and one is input parameter:

snapshot-timestamp:This indicates the time when the database size was actually calculated.This is an output parameter.

dbsize:This is the size of the database in bytes.This is an output parameter.

dbcapacity:This indicates the capacity of the database in bytes.This is an output parameter.

refresh-window:This is an input parameter.It indicates the number of minutes after which the database calculation values are refreshed.


Examples::
1)CALL GET_DBSIZE_INFO(?, ?, ?, -1) -1 which defaults to 30 minutes indicates that the cached values will be refreshed if 30 minute window is reached.

2)CALL GET_DBSIZE_INFO(?, ?, ?, 0) This indicates the cached values are refreshed immediately

3)CALL GET_DBSIZE_INFO(?, ?, ?, 100) indicates that the refresh happens every 100 minutes.

Automatic client reroute registry parameters

DB2_CONNRETRIES_INTERVAL and DB2_MAX_CLIENT_CONNRETRIES are the registry level(db2set) variables that can be configured for Automatic client reroute.

Example::
DB2_CONNRETRIES_INTERVAL=4
DB2_MAX_CLIENT_CONNRETRIES=2
DB2_CONNRETRIES_INTERVAL variable specifies the sleep time between consecutive connection retries, in seconds, for the automatic client reroute feature.This indicates that 2 clientretiries happen with a gap time of 4 seconds before the client rerooute actually happens