Insight and perspectives

Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...

  One of the DBA objectives is to decrease the query cost. Decreasing the query cost – improves overall performance. The db2advis recommends   indexes for a query. A syntax   and process example:   Step 1 – Create the source file     Put a   query in a input file called index.sql. This is just a sample query select SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10),substr(name,1,30), SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard, sequential_pages, density, iid, uniquerule, stats_time, colnames from sysibm.sysindexes a order by  tbcreator, TBNAME, NAME;   Step 2   Execute the db2advis command   db2advis -d myDB -i index.sql   If you see the error message:   Explain tables not set up properly for schema MYINST   The insert into the ADVISE_INSTANCE table has failed.   0 solutions were evaluated by the advisor   exiting with error code [-219]     To fix that proble...

Monitoring lock waits is a great way of measuring query performance slowdown. Lock waits are a normal part query processing but when the lock waits start taking longer than normal , it’s a sign of trouble. According to the DB2 LUW documentation a lock wait is defined as “A lock wait occurs when one transaction (composed of one or more SQL statements) tries to acquire a lock whose mode conflicts with a lock held by another transaction” Common symptoms of lock waits taking longer than normal are: Applications are not completing tasks SQL query performance slowdown Lock escalations. A small amount is OK, but excessive counts are an issue The ideal is to monitor continuously : Lock wait, Lock timeout and deadlock locking   To report on lock wait chains Db2pd –locks waits –alldbs Use this query with joins on views sysibmadm.snapappl_info which return information about applications from an application snapshot and sysibmadm.snapappl which reports on   cumulativ...

The database partition configuration file (db2nodes.cfg) contains configuration information that tells DB2 which database partition on which server participates in an instance. There is a db2nodes.cfg file for each instance in a partitioned database environment.   On Linux and UNIX: $HOME/sqlllib/db2nodes.cfg On Windows: sqllibdb2db2nodes.cfg   The db2nodes.cfg file must contain one entry for each database partition on a particular server that will participate in the instance. When you create an instance, the db2nodes.cfg file is automatically created, and an entry for the instance-owning database partition is added.   On Linux and UNIX, the database partition configuration file (db2nodes.cfg) uses the following format: The format of the db2nodes.cfg file is as follows: nodenum hostname logical port netname resourcesetname   where: Dbpartitionnum is the number DB2 uses to identify a database partition. Hostname is the network interface DB2 u...

  The Fast communications manager (FCM) provides communications support for DB2 server products. Each database partition server has one FCM sender, and one FCM receiver daemon to provide communications between database partition servers to handle agent requests and to deliver message buffers. The FCM daemon is started when you start the instance.   If communications fail between database partition servers or if they re-establish communications, the FCM daemons updates information (that you can query with the database system monitor) and cause the appropriate action (such as the rollback of an affected transaction) to be performed. You can use the database system monitor to help you set the FCM configuration parameters.   You can specify the number of FCM message buffers with the fcm_num_buffers database manager configuration parameter and the number of FCM channels with the fcm_num_channels database manager configuration parameter. The fcm_num_buffers and fcm_num_ch...

  The DB administration server (DAS) provides support services for DB2 tools such as the Control Center and the Configuration Assistant. DAS assists the Control Center and Configuration Assistant when working on the following administration tasks: • Enabling remote administration of DB2 UDB servers. • Providing the facility for job management, • Administrative tasks against jobs located either remotely or locally to the DAS using the Task Center. • Providing a means for discovering information about the configuration of DB2 UDB instances, databases, and other DB2 administration.   To create a DAS, you must have root user authority on UNIX operating systems or you must be using an account that has the correct authorization to create a service. You can only have one DAS in a database server.   1) Login as root 2) Ensure that dasusr1 user is created   [root@server1 instance]# cd /opt/ibm/db2/V9.1/instance [root@server1 instance]# ./dascrt -u dasusr...

  There are two types of license keys associated with DB2 database products: a) Base license keys b) Full license keys.   These license keys are stored in plain text files, which are referred to as license files or license entitlement certificates. A “base” license does not has any usage rights. It is included in the DB2 database product installation media and is applied automatically during the installation process. For example, db2ese.lic is a base license file for DB2 Enterprise Server Edition. License keys are required for all DB2 database products including DB2 Connect and for each optional database feature. The license key is found in the /db2/license directory of the Activation CD, which is supplied as a part of the product installation media. For example, db2ese_u.lic is a license key and can be found on the IBM DB2 Enterprise Server Edition – Authorized User Option Activation CD.   Obtain License >>The licenses for DB2 database produ...

  Instance owner The DB2 instance is created in the instance owner home directory. This user ID controls all DB2 processes and owns all filesystems and devices used by the databases contained within the instance. The default user isdb2inst1and the default group isdb2iadm1. Fenced user The fenced user is used to run user defined functions (UDFs) and stored procedures outside of the address space used by the DB2 database. The default user isdb2fenc1and the default group isdb2fadm1. DB2 administration server user The user ID for the DB2 administration server user is used to run the DB2 administration server (DAS) on your system. The default user isdasusr1and the default group isdasadm1. This user ID is also used by the DB2 GUI tools to perform administration tasks against the local server database instances and databases. There is only one DAS per computer. One DAS services one or more database instances, including database instances that belong to different inst...