checklists are handy sometimes

As a DBA one of the common task is setting up new database(s) for a new application. So when the application is built from scratch you will be involved in the full life cycle of the project. Do you have a handy checklist to validate the configurations for the new databases. What if you dont have one?

Sometimes it is scary because a wrong configuration could result in a huge business loss and thereby putting your job at risk. An example scenario would be not checking on your database backup strategy. Lets say you are in a hurry and did not update trackmod or setup your database for online backups. Hmm You are in trouble now. So it is always good to have a complete checklist of items to be applied on a brand new application. Below is task list that I compiled and it could vary depending on your environment. You may take a print of this image for your reference when you work on building a new application.

First and important category is the kernel settings. Shmmax and shmall are two important parameters that influence the memory allocations at instance and database level. If they are not properly set you might encounter the infamous "Shared memory segments can not be allocated". Following links come handy for setting kernel parameters

Modifying kernel parameters (Linux)
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/t0008238.html

Kernel parameter requirements ( Linux )
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/c0057140.html

OS user limit requirements (Linux and UNIX)
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/r0052441.html

maxfilop - Maximum database files open per application configuration parameter
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.config.doc/doc/r0000280.html

Backup & Recovery settings are vital. If you forget to setup the database for archival logging or if you ignore trackmod ,you would run into issues for sure. Also decide upon the backup strategy well in advance.Do not forget to take offline backup once the database is setup for archival logging. Testing the full cycle of taking backups and restoring it back to fulfill several failure scenarios is essential step for critical applications. After all a DBA`s primary responsibility is to secure the critical data

Initial communication settings like svcename and DB2COMM are trivial but essential for application connections. Sometimes although you have svcename set and DB2COMM set , you might still notice communication issues. Look for db2tcpcm and ipccm in the 'db2pd -edus' output. If you dont see them there do a db2_kill and try again

LOCKTIMEOUT change from -1 : Setting lock timeout to -1 might result in non-terminating lock waits.

STMM configuration : STMM has to turned off/on depending on your workloads and needs

automatic maintainance : Automatic backups , runstats have to be configured

memory settings : Do you want your instance_memory to be automatic or set to a manual value. Things like these have to be decided upfront

diagpath change : diaglogs might grow huge with time and one should not ignore the storage requirements for diaglog and path for diaglog.

Capacity planning is crucial in the initial stages. Do you have adequate memory,cpu and storage resources? Is your applications scalable?

Other house keeping tasks like scheduling backups,runstats,reorgs are important. My list here is just an indicative list and not comprehensive enough to cover all the scenarios. My intention was to pen about the necessity of maintaining checklists as a DBA. I know we all hate documentation but believe me it saves our job sometimes

Shared memory issue::shared memory segments can not be allocated

SQL1084C Shared memory segments cannot be allocated

Have you seen above message anytime? It is annoying when you see this message.Basically first thing you can try is changing instance_memory and database_memory

Important kernel parameters to be considered here are shmmax and shmall.Shmmax is the maximum size of a shared memory segment and shmall is the maximum allocatable shared memory(sum of shared mem segments should be equal or less than this).Recommended value for shmmax is setting it equal to the RAM and for shmall it is 90% of physical memory

ipcs -l is the command to check the values of these kernel parameters

------ Shared Memory Limits --------
max number of segments = 4096 // SHMMNI
max seg size (kbytes) = 32768 // SHMMAX
max total shared memory (kbytes) = 8388608 // SHMALL
min seg size (bytes) = 1

Sometimes you need to increase the value of shmmax to accomodate bigger segment or even increase shmall value to help more overall shared memory.

How do we map shared memory on the box with shared memory parameters on the database and instance??

We use db2pd -memsets,db2pd -mempools to look at the shared memory segments allocated and we try to map it to the original values on the box.My next blog post will reveal the mapping mechanism and the usage of db2pd -memsets,db2pd -mempools commands

Db2 purescale and Oracle RAC

IBM announced a new technology called Purescale recently.It is basically a technology that excels in horizontal scalability.

So what is horizontal scalability??How is it different from vertical scalability??

Horizontal scalability is the ability to add capacity by adding nodes to the cluster whereas vertical stability is the ability to increase capacity by adding extra resources to the existing entity/server.

Purescale is aimed at achieving 3 important goals:
1)Application transparency: No coding changes are required when you add extra nodes

2)Unlimited capacity:This is achieved by adding as many nodes as needed.But there are limitations on the platforms to begin with

3)Data availability:This system aims at zero downtime and is completely available if one or many nodes fail

Is Purescale a replacement for HADR??

No. Purescale is not a replacement and it has only one shared database copy.So, if HADR can be applied for a purescale system it is the optimal availability scenario.IBM thinks that they can do this in the coming days


Oracle RAC Vs DB2 Purescale:Which is better??

When you use Oracle RAC lot of application changes have to be made when we add new nodes.But with Purescale the application is completely transparent to the changes in nodes.Also Purescale has centralized resource management system that manages the lock and other resources.

Basic db2 federation setup

Db2 federation can be used to retrieve information from non-db2 sources like oracle,sql server.It also can be used with db2 datasources.Setting up federation can be confusing at times.Below is the basic procedure that can be used to setup federated system:

1)Enable federation: Federation can be enabled by running the following command

update dbm cfg using federated yes immediate;

2)Create wrapper:Below is the command you would issue for a db2 datasource.Wrappers differs for each RDBMS.Please refer to IBM documentation to get more information on wrappers

create wrapper DRDA;

3)Catalog the datasource information: Datasource should be cataloged properly and the federated server uses the access method depending on the datasource type.

4)Create a server definition.Refer to the below example for db2/udb datasource

db2 "create server SAMPLEHOST

type DB2/UDB

version 9.5 wrapper drda

authorization 'user1'

password '*****'

options(node 'SAMPLENODE', dbname 'SAMPLTST')"

5)Create user mapping:

db2 "CREATE USER MAPPING FOR federuser server SAMPLEHOST options(remote_authid 'user1',REMOTE_PASSWORD '*****')"

6)Create nickname:

CREATE NICKNAME EMP FOR SAMPLEHOST.SAMPLTST.EMP;

If you follow all the above steps, basic federation setup is done.Nicknames can be on tables or views that reside in the datasource.Once the federation is setup, nicknames can be used to refer to the actual datasource objects.If some part of the code can not be processed by the data source, it is not passed to the data source.The datasource in this case will be using alternate functionality that is close or the data set will be sent to the federated server for additional processing

HADR performance - part 1

HADR stands for high availability disaster recovery .This solution is used for disaster recovery purpose on db2 udb databases.HADR should be configured properly inorder to have optimal performance:

1)HADR synchronization mode:HADR can be run in 3 different modes SYNC,NEARSYNC,ASYNC.SYNC mode gives the best protection to data.In this mode primary has to wait until the changes are committed and written on the standby.Primary waits for the acknowledgement from the standby server.In NEARSYNC mode, standby sends acknowledgement as soon as the logs are in memory of standby server.And in ASYNC mode , primary does not wait for any kind of acknowledgement from the standby.Proper synchronization mode has to be chosen for optimal performance

2)DB2_HADR_BUF_SIZE:This registry variable controls the size of the receive buffer .Receive buffer is the area of memory where the logs are received before they are replayed.You can use the db2pd -db dbname -hadr on standby to monitor the usage of receive buffer.If you see it reaching 100 during the workload, you need to increase the value of DB2_HADR_BUF_SIZE

3)DB2_HADR_SOSNDBUF and DB2_HADR_SORCVBUF:There are the socket send buffer size and socket receive buffer size respectively.If the size for these parameters is too small then the full bandwidth can not be utilized.Generally increasing this to a bigger value would not impact performance negatively.


4)Logfilsz:Size of the logfile plays an important role in the performance,Generally this size should be few hundred MB.

Alter table not logged initially

Here is a little code that descripts the usage of "alter table not logged initially"


db2 "connect to SAMPLE";

DB2CMD1="alter table SAMPLE.employee activate not logged initially"

DB2CMD2="INSERT INTO SAMPLE.employee values(,,,,,,,,,,)"
DB2CMD3="commit"

db2 +c -tv "${DB2CMD1}"; db2 "${DB2CMD2}"; db2 "${DB2CMD3}";

Checking the instance peaks

If you want to check the peak usage of memory on any instance use the following


db2 "select * from table (sysproc.admin_get_dbp_mem_usage(-1) ) as t" more
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM PEAK_PARTITION_MEM-------------- -------------------- --------------------- -------------------- 0 1597186048 447676416 450101248


DBPARTITIONNUM::The database partition number from which memory usage statistics is retrieved.
MAX_PARTITION_MEM::The maximum amount of instance memory (in bytes) allowed to be consumed in the database partition.
CURRENT_PARTITION_MEM::The amount of instance memory (in bytes) currently consumed in the database partition.
PEAK_PARTITION_MEM::The peak or high watermark consumption of instance memory (in bytes) in the database

Important Admin views

REORG---->>

SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE, REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM

LOCK WAIT--->>>

SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK, LOCK_MODE_REQUESTED FROM SYSIBMADM.SNAPLOCKWAIT WHERE DBPARTITIONNUM = 0

BPHIT RATIO ------>>>

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME, TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT, DBPARTITIONNUM FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM

TOP DYNAMIC SQL ------>>>

SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 5 ROWS ONLY

UTILITY PROGRESS ------->>

SELECT UTILITY_ID, PROGRESS_TOTAL_UNITS, PROGRESS_COMPLETED_UNITS, DBPARTITIONNUM FROM SYSIBMADM.SNAPUTIL_PROGRESSMONITOR

LOG UTILIZATION ----------->>

SELECT * FROM SYSIBMADM.LOG_UTILIZATION

Monitoring STMM changes

How to monitor the activity of STMM??
STMM logs and db2diag.log provides information about the changes that happen through STMM.db2diag.log gives the information about the changes in the SORTHEAP,BUFFERPOOLS,LOCKLIST,PACKAGECACHE.

Monitoring bufferpool changes on the diaglog:
db2diag -g "message:=Altering bufferpool" db2diag.log

Monitoring the configuration changes by STMM:
db2diag -g "changeevent:=CFG DB" db2diag.log


Interpreting the STMM logs:

Interpreting the STMM logs is not an easy task.IBM has come up with a perl based parser that can parse the STMM logs and throw out some readable results.Below is an example as to how we can call the perl script
perl testperl.pl stmm0.log SAMPLE s

Options for calling the script:
s gives the history of all the memory heap tuning by STMM
o database memory resizes(database_memory)
v sortheap resizes
m minsize information for the consumers
b benefit data for the consumer


You can download the script from http://www.ibm.com/developerworks/data/library/techarticle/dm-0708naqvi/.. There is a dowload link towards the bottom


Gnuplot to build a graph from the output of the perl script:
Gnuplot can be used to plot the data against the data collected from the perl script.

Plot ‘test.dat’ using 1:5 with lines

STMM internals

OS limitations:
For Linux servers,prior to V9.5 setting DATABASE_MEMORY to automatic is not allowed.So essentially sharing of memory between OS and database was not allowed.However with V9.5 DATABASE_MEMORY can be set to automatic when INSTANCE_MEMORY is set to a static value.
STMM Controller:
How does STMM know where to take and where to give?This process is controlled by a component called STMM Controller.STMM does a cost/benefit analysis by using a generic performance rule to assess all the memory consumers.Bufferpools,sotheap,locklist,package cache are the memory consumers that participate in STMM.Bufferpool hit ratio,lock escalations,sort overflows and package cache hit ratio are the indicators of the performance for bufferpools,locklist,sortoverflow,package cache respectively.But there should be a common indicator for these consumers so that STMM can compare the cost/benefits between various consumers.The common indicator can be savings in the I/O or savings in CPU or savings in agent processing time.
Minsize for each consumer:
Each memory consumer will have a minsize limit and it can not donate beyond that limit.Insufficient memory is always dangerous and so STMM uses the minsize constraint when it distributes the memory between the memory consumers.
Tuning interval:
STMM can adjust its tuning interval as quickly as 30 seconds or as infrequently as 10 minutes.If the work load consists of shorter transactions(OLTP) , STMM might use shorter intervals
Free memory target:
STMM steals from OS memory when it needs some for DATABASE_MEMORY.But there is a minimum limit of free memory that has to be left on OS .On smaller servers a higher amount of memory is left out for middleware and other applications.
STMM and sorts:
Setting Sheapthres to a value of 0 and setting sortheap,sheapthres_shr to automatic will allow STMM to tune sort memory.With this setting all the sorts will happen in shared memory and not in private memory.

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.

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