Maximum connections allowed on a DB2 database - MAXAPPLS and MAX_COORDAGENTS

Question: How can I find out the maximum amount of connections  allowed on a DB2 database?

Answer:  Work through this example to analyse the connections. The example indicates  support for 68 (with room to grow)  and the high watermark is 38.

 To check at a database level check MAXAPPLS . This parameter specifies the maximum number of concurrent applications that can be connected  to a database.

 

db2 get db cfg for DB_NAME | grep 'MAXAPPLS'
--returns
Max number of active applications            (MAXAPPLS) = AUTOMATIC(68)

 

To check at an instance level check :  MAX_COORDAGENTS , which is  used to limit the number of coordinating agents  and MAX_CONNECTIONS which reports the maximum number of client connections allowed per database partition

 

db2 get dbm cfg | grep 'MAX'
--returns
Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

To check for the high watermark use

db2 get snapshot for dbm | grep 'High'

--returns
High water mark for agents registered          = 38
High water mark for coordinating agents        = 38
       High water mark (bytes)                 = 15466496
       High water mark (bytes)                 = 851968
       High water mark (bytes)                 = 327680

 

Read More

Command to see connection on a DB2 database - list applications

DB2 Instance Memory and dbptnmem - DBA DB2

 

DB2 - Collecting data for database performance problems

In DB2 database performance troubleshooting , one of the challenges is to collect the right amount and type of data to assist the DBA to get clues.   In How to report a DB2 performance problem  there are questions to think about – guiding you towards a general approach to reporting a performance problem

This post lists the information required when you’re sending me DB2 performance related queries. It gives me enough data to gain an overview of the database. It doesn’t guarantee a solution , but covers the main data sets.

Run the following from a DB2 command window

 

db2look -d  -e -o .ddl -a -l -f 
db2 get dbm cfg > dbmcfg.out 
db2 get db cfg for  > -dbcfg.out 
connect to  
db2 -tvf table.sql > -table.out 
db2 -tvf index.sql > -index.out 
db2 -tvf buffer.sql > -buffer.out 
db2level > db2level.out 
db2set -all > db2set.out 
db2 -tf updmon.sql 
--after this, run db2 get monitor switches 
--they should all say "ON" 
--run whatever process is causing the issue(s) 
db2 get snapshot for all on  > -dbsnap.out 
--this must be run from the same command window as step 10 
--if you can do multiple snapshots that would be better, an hour apart or 
--so while the S&P agent is running, use different output files 

--where  is the name of your database 

 

Please send the following files:

<dbname>.ddl

dbmcfg.out

<dbname>-dbcfg.out

<dbname>-table.out

<dbname>-index.out

<dbname>-buffer.out

db2level.out

db2set.out

<dbname>-dbsnap.out

Read More

 

How to report a DB2 performance problem - DBA DB2

Database Tuning overview – Access Path Tuning

Performance Tuning series – Performance Stack - DBA DB2 ...

OLTP performance checklist - DBA DB2

DB2 – Audit Failed logons

Troubleshooting  DB2 failed logon attempts is an important part of  DB2 databases security management.  Beyond establishing security threats , such as hacking or viruses ,   the purpose of troubleshooting can extend to discovering application issues.

For example , there may be an application in the Development environment , that has JDBC connection pointing to a Production environment. This can have a disastrous impact on Production data. Ideally, an environment should have Development, Test,QA, Production servers split out into separate OUs , and there should be necessary blocks in place to  exclude connection attempts across the different domains. Nevertheless, it does happen and it is the DBAs responsibility to ensure steps are in place to discover this possibility.

Failed logon attempts are recorded in the db2diag.log file. This offers clues about the logon attempt , database attempted and referral source. This should be enough information to establish which DB2 instance requires auditing .

Setting up a DB2 audit is straightforward. The db2audit facility is specifically designed to help trace access to data, based on creating an audit trail of defined database events. Below is a sample of setting up a db2audit and recording some failed logon attempts.

Steps to audit Failed Logins

Note: For database-level authorization SECADM authority level is required . Read DB2 - A Security Primer  for more DB2 security details

#start the db2audit
db2audit start 

#define an auditing policy on the current server 
db2 "create audit policy fl categories validate status both error type normal"
db2 "audit database using policy fl"

#create some event 
db2 “connect to mydb user jackvamvas using evilpw".


Next was to flush the audit logs, archive and extract them:
#flush the audit logs
db2audit flush
#archive the audit logs
db2audit archive database mydb to /tmp
#extract the audit logs
db2audit extract delasc to /tmp from files /tmp/db2audit.db.mydb.log

 

The db2audit process created the file “ /tmp/validate.del”. 

Open it , and you should see entries with error -30082, related to failed logons.

How to monitor a deadlock in DB2

Question: How can I trace and capture DB2 database deadlock details? I then want to  view the deadlock details.

Answer: Capturing DB2 deadlock details is straightforward. Use a similar process to below. Basically, you create an Event Monitor , capture some activity into a file, drop the event monitor.

 

#connect to db#1-Create event monitor- make sure you've got write permissionsdb2 "CREATE EVENT MONITOR dlmon FOR DEADLOCKS WRITE TO FILE '/tmp'"#2 Turn on event monitordb2 SET EVENT MONITOR dlmon STATE = 1#3. (Do stuff to the database that you want to monitor)#4. Turn off event monitordb2 SET EVENT MONITOR dlmon STATE = 0#5. Translate event monitor into readable stuffdb2evmon -path /tmp > /tmp/filtered.out#6. Read the eventsvi /tmp/filtered.out#7. db2 DROP EVENT MONITOR dlmon 

 

 Read More

SQL0911N Reason Code 68 The current transaction has been rolled back because of a deadlock or timeout

DB2 - Drop the DB2DETAILDEADLOCK event monitor

SQL0911N Reason Code 68 The current transaction has been rolled back because of a deadlock or timeout

Question : I am seeing this SQL0911N Reason Code 68 message in my DB2 diaglog file:

command : db2 backup db MYDB online use TSM include logs
SQL0911N  The current transaction has been rolled back because of a deadlock
or timeout.  Reason code "68".  SQLSTATE=40001

 

How do I fix this SQL0911N error?

 

Answer: The DB2  command line error utility  shows this detail for  the SQL0911N Reason Code 68 error., indicating that the SQL0911N error is caused by a deadlock or locktimeout.

A DB2  ONLINE backup attempts to obtain a IN lock on tables in SMS table spaces. DB2 tries to upgrade BLOB fileds to an S LOCK during an ONLINE backup.

 If an application is holding a lock incompatible with the ONLINE backup lock attempt, The ONLINE BACKUP waits for the period of the LOCKTIMEOUT ,  where the ONLINE backup fails.

Possible solutions

1)       Analyse scheduled backup times and move to a quieter time frame

2)       Change table space to DMS from SMS. DMS doesn’t require the SHARE lock for BLOBs

3)       Force the application off.

4)       Increase LOCKTIMEOUT

Read More

How to monitor a deadlock in DB2 - DBA DB2

DB2 - Drop the DB2DETAILDEADLOCK event monitor