Monday, November 15, 2010

DB2 9.7 HADR with TSA - Part 01 - Installing and configuring VMWare environment

In this blog i will explain the steps needed to install VMWare software.
DB2 9.7 HADR with TSA Part 00 - Introduction

A) Introduction

VMWare is the industry standard virtualization software company. VMWare Server is one of the virtualization software that allows to run multiple Guest OS on top of single Host OS. For example, a laptop having Microsoft Windows XP Operating System is termed as Host OS. VMWare Server software is then installed on this Host OS. VMWare Server then provides services that allows running multiple OS called as Guest OS under its runtime environment. 

B) Download Details
The VMware software can be downloaded from VMWare website
Item#Description
Download Sourcehttp://www.vmware.com/products/server/
Documentationhttps://www.vmware.com/support/pubs/server_pubs.html


C) Installation













D) Configuration

Start the VMWare Server from Windows XP Start Menu

Start -> VMware -> VMware Server -> VMware Server Home Page

Step 1: Login to VMware server using your Host OS Login crediantial


Step 2:
  • Create Virtual Machine using the menu Virtual Machine -> Create Virtual Machine
  • Provide name for your new virtual machine. In our case the name of the Virtual Machine is mumbai
  • Select the Guest OS that will be installed on Virtual Machine. In our case the guest OS is Red Hat Enterprise Linux 5.
  • Select the Physical Memory for the Guest OS. This memory will the pulled from Host OS. In our case I specified 1024MB. Kindly select appropriate memory based on available memory on you machine. Giving more memory than what is available on laptop may result into paging.
  • Create hard disk for virtual machine that will be used to install Guest OS. Select Create a New Virtual Disk
  • Provide value for Hard disk properties
Capacity :- 10GB
Location :- [standard] chicago/chicago.vmdk
File Option :- Make sure “Allocate all disk space now” is unchecked.
Disk Mode :- Independent -> Persistent
Virtual Device Node :- Adapter => SCSI 0 Device => 0
Policies :- Optimize for safety


  • Add network adapter for the virtual machine. Select Add a Network Adapter
  • Select Bridged Network Connection


  • Create Media drive for your Guest OS. Select “Use a Physical Drive
  • Give drive name for the media drive
  • Ignore creation of Floppy dirve for Guest OS
  • Add USB controller for Guest OS



  • Summary of the new Virtual machine that will be created. Click Finish to create virtual machine





    • New virtual machine is created in is listed on Left Panel in VMware Home Page

    Step 3 : Installing the Guest Operating System on Virtual Machine


    After creating the Virtual machine, next task is to install the Guest OS on the Virtual Machine. For this sample example we use CentOS 5.2 Linux Operating System. This is the same package that will is used to develop the RED HAT Enterprise Linux Operating System.



  • Donwload Instruction





  • Item#Description
    Download Sourcehttp://mirror.ukhost4u.com/centos/5.4/isos/i386/
    CentOS-5.4-i386-bin-1of6.iso
    CentOS-5.4-i386-bin-2of6.iso
    CentOS-5.4-i386-bin-3of6.iso
    CentOS-5.4-i386-bin-4of6.iso
    CentOS-5.4-i386-bin-5of6.iso
    CentOS-5.4-i386-bin-6of6.iso
    Documentationhttp://www.centos.org/docs/5/

    • Start the Virtual Machine by clicking the Green Play button on top menu. Before starting the Virtual Machine make sure the CentOS CD is inserted into your CD Drive. VMware pick the detail and start the Linux Installation setup.
    • Click the Console tab which will let you see the insall screen. Click the Window which will open a new VMware client window

    • This will show the Linux OS install steps. Follow the Linux step to install Linux OS on Virtual Machine


    • Install all the package to avoid problem of missing library files. Also provide following details
    Hostname : chicago.localdomain
    IP Address eth0 : 192.168.9.111
    Default Gateway eth0: 192.168.9.1
    IP Address eth1 : 192.168.0.105
    Default Gateway eth1 : None

    Step 4: Starting the Viirtual Machine
    • Once the OS is installed on the virtual machine, start the virtual machine to login to Guest OS
    • Login to the Guest OS using the login credential provide during the Linux OS installation.

    Sunday, November 14, 2010

    Sunday, July 18, 2010

    DB2 9.5 and DB2 9.7 Locking Tips


    DB2 Locking basics

    In any relational database, in order to maintain consistency among the transaction locks need to be acquired on the resource on which the transaction will be executed. Resource can be a Relational Table, or Rows. Locking provide isolation of resource between transaction thereby providing concurrency between independent transaction. DB2 provides four isolation levels.
    • Uncommitted Read
    • Cursor Stability
    • Read Stability
    • Repeatable Read
    • Currently committed (New in DB 9.7)
    In order to utilize the isolation level in the transaction, they can be specified at
    1) Statement level (SELECT, DELETE, UPDATE)
    eg:- SELECT FROM WITH {UR, CS, RR, RS}

    2) At session level
    eg:- SET CURRENT ISOLATION = {UR, CS, RR, RS}


    Commonly referred LOCKING terms

    1) Locks
    In order to use any resource, DB2 lock the resource before the transaction can use it.


    2) Lock-Wait
    If any transaction is requesting lock on a resource, and that resource is currently being held by other transaction, then the application requesting the lock goes into LOCK-WAIT mode until the lock on the resource is released by the primary transaction.

    3) Deadlock
     If two transaction are waiting on each others resources the the both the transaction goes into deadlock state.

     4) Lock Escalation
     For each lock acquired on a resource, DB2 maintain that information in DB2 locklist memory component. If the locklist memory started getting exhausted, DB2 escalate all row level lock to table level lock in order to release memory.

    How DB2 locking can be monitored?

    1) Monitor switches
    2) GET SNAPSHOT command
    3) db2pd tool
    4) Snapshot monitor SQL administrative routines
    5) DB2_CAPTURE_LOCKTIMEOUT ( New in DB2 9.5 locking) Practical Use of DB2_CAPTURE_LOCKTIMEOUT
    6) db2cos scripts
    7) db2pdcfg with -catch option
    8) Locking event monitor as apposed to Deadlock event monitory (New in DB2 9.7 Locking)

    Lock related DB CFG parameter

    1) LOCKLIST
    2) MAXLOCK
    3) LOCKTIMEOUT
    4) DLCHKTIME

    Lock related registry variable

    1) DB2LOCK_TO_RB
    2) DB2_KEEPTABLELOCK
    3) DB2_MAX_NON_TABLE_LOCKS
    4) DB2_EVALUNCOMMITED
    5) DB2_SKIPDELETED
    6) DB2_SKIPINSERTED


    Reference URLs:

    1) Example to understand the DB2 Locking Mechanism

    2) Example on DB2 Snapshot and Event monitors

    3) Whitepaper describing technique to Diagnose and resolve lock problems

     4) DB2 9.5 lock monitoring - Part 1

     5) DB2 9.5 Lock monitoring - Part 2

     6) DB2 9.5 Optimistic Locking ( New in DB2 9.5)

    Sunday, June 20, 2010

    My JUNE favourite DB2 9.7 articels

    Finally, today i completed my DB2 9.7 Database administrator certification. DB2 9.7 have a good bunch of new feature that simplifies every DB2 DBAs job. Oracle Compatibility is very good feature that IBM brought into the product.

    WLM which was launched as part of DB2 9.5 is enhanced in DB2 9.7 and is really one of the powerful feature in DB2 9.7. Along with it couple of new monitoring views, event monitors are released that help in identifying how data server is performing at a given point in time. New DB2 HADR feature provide read only standby.
    Some of the other interesting feature that i rated are
    1) CGTT
    2) Index compression
    3) New Monitoring views
    4) New Event monitors
    5) WLM enhancement
    6) Online table move

    and many more.....

    I am not very interested in XML technology in DB2, but IBM had extended the XML capability in DPF environment which was not possible in previous releases.

    DB2 9.7 and all the future version will bring really interesting extensions to DB2.

    I am awaiting for DB2 9.8 pureScale for Linux. DB2 9.8 is really gone shake the market for OLTP application. It has brilliant technology running under its bonnet which is borrowed from its successful implementation in Mainframe.
     
    Some of the good dw articles i find interesting for the month of June 2010 are

    1) DB2 pureScale - Group Crash Recovery
    http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1006wucx/index.html

    2) IBM InfoSphere Federation Server Best Practice
    http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1006lihf/index.html

    3) Unleash the power of table partitioning in your DB2 9.7 warehouse
    http://www.ibm.com/developerworks/data/library/techarticle/dm-1006tablepartitioning/index.html

    4) IBM DB2 e-kit for Database Professional
    http://www.ibm.com/developerworks/data/kits/dataprokit/index.html

    5) Whats new with DB2 Express-C 9.7.2
    http://www.ibm.com/developerworks/data/library/techarticle/dm-1006db2expressc972/index.html

    6) Build skills for DB2 for Linux, Unix and Windows
    http://www.ibm.com/developerworks/data/db2skills/?ca=dnw-1123&ca=dth-i

    Thursday, June 17, 2010

    DB2 Client Server Connectivity and Configuration

    In this post i will discuss different scenario of DB2 client connectivity to DB2 server. In each new release of DB2, the DB2 client is re-branded with different name. The table below describe the DB2 9.1, DB2 9.5 and DB2 9.7 client name.

    DB2 V9.1

    DB2 V9.5

    DB2 V9.7

    DB2 Client IBM Data Server Client IBM Data Server Client
    DB2 Runtime Client IBM Data Server Runtime Client IBM Data Server Runtime Client
    IBM DB2 Driver for JDBC and SQLJ IBM Data Server Driver Package IBM Data Server Driver Package
    IBM DB2 Driver for ODBC and CLI IBM Data Server Driver Package IBM Data Server Driver Package

    Below are link to DB2 Information that gives the details of DB2 Client Type

    DB2 Version

    InfoCenter Link

    V9.1 http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.rn.doc/doc/c0023028.htm
    V9.5 http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html
    V9.7 http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html

    In order to connect to DB2 Server, one of this DB2 client needs to be installed. This DB2 client binary then allows the application to connect to the DB2 server. We will present two Case of DB2 client connectivity to DB2 Server.

    Requirement for setup:

    1)  You need to set service port on the DB2 Server through which the DB2 Client communicate with DB2 Server. On Windows, the entry is recorded in “c:\windows\System32\drivers\etc\services” file. On Linux, the entry is recorded in “/etc/services” file. The service port entry in this file is of the form  
      •   service_name           port_number          /tcp
      •   For Example:-    DB2_test     7777   /tcp
    2) Update the DB2 Database Manager configuration SVCENAME with the service port number. The following command is executed to set the SVCENAME
      • update dbm cfg using svcename DB2_test
    3) DB2COMM registry variable needs to be set to value “TCPIP”. The DB2 instance needs to be bounced in order reflect the changes made to the DB2COMM registry variable. The following set of command needs to be executed in sequence to make this changes
      • db2set DB2COMM=TCPIP
      • db2stop force
      • db2start
    4) On the client, DB2 Client instance must be created if it didnt already exist. This is needed to catalog the remote DB2 database. The client is created using the following command
      • db2icrt –s client {instance_name}
    5) On client, the CATALOG DATABASE and CATALOG NODE command must be executed as the DB2 Client Instance owner.

    Case 1:  DB2 Client is located on the same server where the DB2 Server is executing


    a)  In this DB2 Client binary is NOT needed to be installed since all the files needed to connect to DB2 Server is installed as part of DB2 Server Installation. 

    b) If we need to access database with different name then we need to catalog the database with different name. Following command is executed in sequence
      • CATALOG DATABASE {source_db_name} AS {alias_db_name} ON {drive | path}
    c) Then connect to the alias using the following command
      • CONNECT TO {alias_db_name}

    Case 2: DB2 Client is Located on the Remote Machine which in turn connect to DB2 Server running on different machine.

    a) In this the DB2 Client need to be installed on the client machine. 

    b) We need to catalog the remote instance. This step is needed to access the remote database created under that instance. Following command are executed
      • CATALOG TCPIP NODE {nodename} REMOTE {hostname OR IP address} SERVER {service_port_set_on_DB2_Server}
    c) We then need to catalog the database in order for client to connect to the DB2 server. Following command is executed
      • CATALOG DATABASE {source_db_name} AS {alias_db_name} AT NODE {node_name_from_step_2}
    d) Then connect to the alias using the following command
      • CONNECT TO {alias_db_name}




    Case 3: Remote application is located on the Remote Machine which want to connect to DB2 Server running on different machine using the JDBC connection.

    a) Download the IBM Data Server Driver Packages client from the following website
     http://www-01.ibm.com/support/docview.wss?uid=swg24025865

    Download Steps:



       1) Select "Windows 32-bit X86"
       2) Select "IBM Data Server Driver Packages"
       3) Select "fix pack: DSClients-nt32-dsdriver-9.5.500.784-FP005"
       4) Select "Download using your browser (HTTP)"
       5) Select "v9.5fp5_nt32_dsdriver_EN.exe (24.95 MB)"



    b) More details on the driver can be read at below link
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html

    c) Driver Installation details can be read at below link
      http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0054554.html


    d) Set the CLASSPATH environment variable to {driver_install_path}\java\db2jcc.jar 


    e) Connect to DB2 Server using the JDBC URL as described by JAVA documentation.

    Wednesday, June 16, 2010

    DB2 Database Directories


    When the database is created and/or catalogued in an instance, it record its details in three directory. They are System Database directory, Local database directory and Node directory

    The System database directory
    contain all the database information. The database may be a local database or it can also show the remote database details. Local database is represented with keyword “INDIRECT” and remote database is represented by “REMOTE”.

    When the connection to the DB2 Database is made, DB2 first check the details in the System Database directory. If the connection is to the “INDIRECT” database then it check the local database directory. If the connection is to the “REMOTE” database then it check the Node directory.

    System Database directory is a binary file with a name SQLDBDIR. On Windows, the file can be located in DB2INSTPROF/SQLDBDIR/SQLDBDIR. On Linux, the file is located in INSTHOME/sqllib/sqldbdir/sqldbdir. The content of this directory is displayed by using the command “list db directory”. The purpose of the System database directory is to record all the database (local or remote) that are accessible.

    DB2INSTPROF = DB2 Registry variable. If not set then the default value is the DB2 Installed directory path.

    INSTHOME = Is the user profile variable which is set to the Instance home directory.

    Local Database Directory is a binary file with the name SQLDBDIR. However, this file is different from the System Database directory binary file and also reside in different storage path. On Windows and Linux, the file can be located in DFTDBPATH/NODEXXXX/sqldbdir/sqldbdir which will list all database created in the default directory. If we need to check database in non-default directory then it can be located in /NODEXXXX/sqldbdir/sqldbdir.The content of this directory is displayed by using the command “list db directory on ”. The purpose of the local database directory is to record all the database (local only) created in specific directory. This directory also give the SQLXXXXX directory name of the database.

    DFTDBPATH = DB2 Database Manager variable which show the default path for creating a new database.

    Node Directory is a binary file with the name SQLNODIR. On Windows, the file can be located in DB2INSTPROF/SQLNODIR/SQLNODIR. On Linux, the file is located in INSTHOME/sqllib/sqlnodir/sqlnodir. The content of this directory is displayed by using the command “list node directory”. The purpose of the Node Directory is it record how to communicate with the remote DB2 instance.





    Database Manager Configuration File (DBM CFG)

    The DBM CFG files store all the Database Manager (Instance) parameter values. Each Instance has its own DBM CFG file. This is a binary file. The file is called as "db2systm". On Linux the file is located at 
    INSTHOME/sqllib/db2systm





    Database Configuration File (DB CFG)


    The DB CFG files stores all the Database parameter values. Each database has it unique DB CFG file. This is a binary file. The file is called as "SQLDBCONF" starting from v8.2 and later.
    To locate the file on Linux following step are performed
    1) Get the database path
    db2 list active databases



    2) Change to "Database Path" directory returned from the output of previous command. The file will be located under that path





    Useful Reference



    1) DB2 directory Structure on Linux

    2) Database directories and files


    IBM Balanced Warehouse D5100 Upgrade Guide available

    Finally the IBM Balanced Warehouse D5100 Upgrade Guide for Linux is available. It can be downloaded from

    http://www-01.ibm.com/support/docview.wss?rs=3354&uid=swg21295194


    The upgrade guide explain the complete Balanced Warehouse D5100 stack upgrade.

    While reading the document i skimmed through some of the component that are upgraded.


    1) OS from SLES10 SP1 to SLES10 SP2

    2) InfoSphere Warehouse 9.5.2 (9.5.1 refresh) to InfoSphere Warehouse 9.7.1

    3) DB2 is upgraded from DB2 9.5.3b to DB2 9.7.1

    4) NFS v3 to V4


    It is really great that IBM has released document for Linux first as apposed to AIX.

    Sunday, May 02, 2010

    DB2 V9.5 Locking

    Environment
    Step 1:   Create a sample database using the following command
    db2sampl

    This will create sample database which is shifted with DB2 software. It help to learn new concepts of DB2 and also for testing purpose.

    Step 2: Open three command line which will help us see the transactions

    Screen 1:
    This screen is used by user A.

    Screen 2: 
    This screeen is used by user B.

    Screen 3:
    This screen is used to monitor how the transaction work. Here we will use the "db2pd" command to see the lock on the tables

    Step 3:

    Screen 1 (Txn 01) Screen 2 (Txn 02) Screen 3
    db2 connect to sample user usera using usera db2 connect to sample user userb using userb db2 connect to sample
    db2 +c update employee set salary = salary * 0.1 db2 +c update employe set bonus = salary * 0.1 where job = 'MANAGER' db2 "select *from employee"


    Step 4:
    After performing the TXN given in Step 3, we can see that Screen 2 TXN will pause because DB2 has held the lock on all row because of the transaction done on Screen 1. This gives us the good chance in learning the DB2 Locks. Now in order to learn more about DB2 LOCK we will be using Screen 3 for monitoring the locks.

    Perform the following operation
    Single Partition
    Multiple Partition
    db2pd -d sample -locks wait showlocks db2pd -d alldbs -locks wait showlocks



    Output




    In this TranHdl = 2 is performed by Txn 01 and TranHdl = 9 is performed by Txn 02. Txn02 [TranHdl = 9] in our case is in LOCK WAITING state.

    What is Extracted

    Parameter
    TXN 01
    TXN 02
    TranHdl 2 9


    Step 5:
    Now Txn 01 has updated all the records from employee table we can see the details of it using the following command. It shows all the records that are updated as part of Txn01.

    db2pd -d sample -locks 2
    -- Here 2 is the value taken from Step 4 TranHdl column.





    db2pd -d sample -locks 9
    -- Here 9 is the value taken from Step 4 TranHdl column.


    When Txn02 try to update the record, DB2 block the DML operation and block the transaction resulting into LOCK WAIT problem. We can see what transaction is trying to update usnig the following command. Here it want to update a set of ROW. When it trys to aquire the LOCK for first row to update, it fails and goes in LOCK WAITING state as show in row 2 in above screen shot.


    Step 6:
    To get the list of all transaction running in the system execute the below command. The screen shot is truncated. Therefore i have split it into two screen shot. Hereafter if the image is followed one after another without any text, its a continuation of the previous screen.

    db2pd -sample -transactions





    We have got all the transaction executing in the DB2 Server. Now our concern is only with the lock waiting transaction that was identified in Step 4. In our case it was TranHdl (2 and 9). In order to pull the details of specific transaction we execute the command

    db2pd -sample -transactions 2




    From the above screen shot very important information can be extracted.
    1) Firstlsn and Lastlsn can be used to identified how many log files it spanned. Using the column value we can also find which log file it belong to using the db2flsn .
    2) You can Txn State which help us to understand what operation the Txn was doing.
    3) TID provides the transaction ID which can be used to find more information about the transactions.
    4) We can also find the application that is executing the transaction using the value in the AppHandl column value. In our case the Application 77 (AppHandl=77) is permforming the Txn.


    db2pd -sample -transactions 9




    In the above screen, we can see that there is no LOG space used by this transaction as the transaction is in LOCK WAITING state. Application 90 (AppHandl=90) is performing the Txn 02.


    What is extracted

    Parameter
    TXN 01
    TXN 02
    TranHdl 2 9
    AppHandl 77 90

    From this we can conclude that Application 77 (AppHandl = 77) is holding LOCK on which Application 90 (AppHandl = 90) is waiting.


    Step 7:

    After getting the Transaction Details and Application Details, our next task would be to find the details about the application. There are three way to find the detials about the application.

    1) db2 list application show detail





    In the column Status shows the Txn state. In this Application 77 (Txn 01) is in UOW Waiting which mean it waiting for application to any more transaction i.e it is right now idle and the application is not performing any operation on database.
    Application 90 (Txn 02) show Lock-wait status which mean it is Waiting for Lock to be released by other transaction.

    2) db2pd -agents app=77


    In the above screen, ClientPid column show the value where the command is executed. It does not represent the user and/or application executing the transaction.

    db2pd -agents app=90



    3) db2pd -applications agent=8416 -db sample


    In this the value provided with agent clause is the Co-Ordinator agent ID which can be used to find more details about the application. It can be extracted from Method 1 or Method 2 described above.

    db2pd -applications agent=8416 -db sample



    From the above two output it can be seen that SystemAuthID show the actual user name executing the transaction.
    C-AnchID mean the current executing SQL statement C=Current
    L-AnchID mean the last SQL statement that was executed L=Last


    What is extracted

    Parameter
    TXN 01
    TXN 02
    TranHdl 2 9
    AppHandl 77 90
    SystemAuthID DB2ADMIN GGONSALVES
    EDUID 8416 11760
    C-AnchID 0 268
    L-AnchID 36 268



    Step 8:

    Next find the statement executed by the applications. This will help us to find what are SQL query executed against the DB by each of this application. This SQL executed are extracted using the C-AnchID and L-AnchID collected from step 7.

    db2pd -db sample -dynamic anch=36


    In this we got the statement executed by the Txn 01 i.e Application 77. It show the all rows in the EMPLOYEE table is updated. The Statement executed was
    "UPDATE employee SET salary = salary * 1.02".

    db2pd -db sample -dynamic anch=268


    The Statement executed by Txn 02 i.e Application 90 is
    "UPDATE employee SET bonus = salary * 0.1 WHERE job = 'MANAGER'"

    From the ouput of Application 90, we can see that the application is waiting on Lock to be released. The "Dynamic SQL Environments" shows the Isolation Level of the statement that is executed. The "Dynamic SQL Variations" show the Lock on which the application is waiting.

    What is extracted

    Parameter
    TXN 01
    TXN 02
    TranHdl 2 9
    AppHandl 77 90
    SystemAuthID DB2ADMIN GGONSALVES
    EDUID 8416 11760
    C-AnchID 0 268
    L-AnchID 36 268
    Text (SQL Statement) UPDATE employee SET salary = salary * 1.02xn UPDATE employee SET bonus = salary * 0.1 WHERE job = 'MANAGER'

    Step 9:

    From the above operation, it can be summarized that the EMPLOYEE table is been accessed by Txn 01 and Txn 02 and each of this transaction had requested the exclusive lock on the row in this table. But since the Txn 01 was first to execute it got the Exclusive lock on the rows but it did not commit. Because the Txn 01 is not commited, Txn 02 request for exclusive lock on some rows was halted and Txn 02 goes in LOCK-WAITING state.

    Two option can be used to resolve from this LOCK-WAITING state
    1) LOCKTIMEOUT => This DB CFG parameter is defaulted to -1 which mean the application have to wait indefinately until the lock which it is requesting is released. In order to wait indefinately for a LOCK, it can be set to value other than -1. The value specified is the time after which the waiting transaction must rollback itself. It is given in seconds.

    2) Execute the COMMIT operation which will allow the transaction to release the LOCK so that other Transaction can continue to operate.


    Step 10:

    In order to perform the above step in finding the LOCK details, the approach is to specific all the caluse in db2pd command. It will show different section for each of the caluse. The command is

    db2pd -db sample -locks wait show locks -transactions -agents -applications -dynamic

    The output of the above command will give details of all the "Transactions" , "Agents", "applications" and all "dynamic" statements. In order to cut shot the output following step can be carried out
    1) Find the Transaction handle that are part of LOCK WAITING. We get Transaction Handle
    db2pd -db sample -locks wait show locks

    2) Find the application executing transaction. We get Application Handle
    db2pd -db sample -transactions

    3) Find the EDU ID for application that is executing. We get EDUID
    db2pd -db sample -agents app=<Apphdl>

    4) Find the L(Last) and C(Current) anchor ID for each application. We get C-AnchID and L-AnchID
    db2pd -db sample -applications agent=

    5) Finally execute the command to get all the detail in one output
    db2pd -db sample -locks wait showlocks -transactions "TranHdl" -agents app="applhdl" -applications agent="AgentEDUID" -dynamic anch="C-AnchID ==OR== L-AnchID"