Archive for the DB2 Category

DB2PD – Diagnosing a lockwait

Posted in DB2 with tags on September 1, 2011 by DBAWork

In RDBMS database, to validate data integrity, it has concept called as lockwait. Lockwait goes like wait on lock to be released by another application. The time which is need to get hold of perticular resource by holding lock is measured in time.

DB2 has tool which provides a way to diagnose lockwait condition. Following is example of this.

If you run db2pd -db databasename-locks -transactions -applications -dynamic, the results are similar to the following ones:

Locks:
Address            TranHdl Lockname                   Type Mode Sts Owner Dur HldCnt Att    ReleaseFlg
0x07800000202E5238 3       00020002000000040000000052 Row  ..X  G   3     1   0      0x0000 0x40000000
0x07800000202E4668 2       00020002000000040000000052 Row  ..X  W*  2     1   0      0x0000 0x40000000

For the database that you specified using the -db database name option, the first results show the locks for that database. The results show that TranHdl 2 is waiting on a lock held by TranHdl 3.

Transactions:
Address            AppHandl [nod-index] TranHdl Locks State Tflag      Tflag2     Firstlsn       Lastlsn        LogSpace SpaceReserved TID            AxRegCnt GXID
0x0780000020251B80 11       [000-00011] 2       4     READ  0x00000000 0x00000000 0x000000000000 0x000000000000 0        0             0x0000000000B7 1        0
0x0780000020252900 12       [000-00012] 3       4     WRITE 0x00000000 0x00000000 0x000000FA000C 0x000000FA000C 113      154           0x0000000000B8 1        0

We can see that TranHdl 2 is associated with AppHandl 11 and TranHdl 3 is associated with AppHandl 12.

Applications:
Address            AppHandl [nod-index] NumAgents CoorPid Status        C-AnchID C-StmtUID L-AnchID L-StmtUID Appid

0x07800000006879E0 12       [000-00012] 1         1073336 UOW-Waiting   0        0         17       1         *LOCAL.burford.060303225602
0x0780000000685E80 11       [000-00011] 1         1040570 UOW-Executing 17       1         94       1         *LOCAL.burford.060303225601

We can see that AppHandl 12 last ran dynamic statement 17, 1. ApplHandl 11 is currently running dynamic statement 17, 1 and last ran statement 94, 1.

Dynamic SQL Statements:
Address            AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x07800000209FD800 17     1       1      1      2      2      update pdtest set c1 = 5
0x07800000209FCCC0 94     1       1      1      2      2      set lock mode to wait 1

We can see that the text column shows the SQL statements that are associated with the lock timeout.

Database DB2 Support, DBAWorkDatabase support, Database services, Oracle ADF
Advertisements

Quick Reference for DB2 installation

Posted in DB2 with tags on September 1, 2011 by DBAWork

Please follow this process to install DB2 V9.1.

Install

Login as root to this system. As we need to create certain user group and users. Following commands will allow you to create user with needed path for home directory. We will need to create user and set its password as well.
* Create groups:
o groupadd -g 999 db2iadm1
o groupadd -g 998 db2fadm1
o groupadd -g 997 dasadm1

* Create users for each group:
o useradd -u 1004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
o useradd -u 1003 -g db2fadm1 -m -d /home/db2fenc1 db2fenc1
o useradd -u 1002 -g dasadm1 -m -d /home/dasusr1 dasusr1

* Set password for each users created:
o passwd db2inst1
o passwd db2fenc1
o passwd dasusr1

* cd to installation file directory:
o Example: /tmp/db2/exp/disk1

* Run installation script:
o ./db2_install.sh

Post Install

* Login as root.
* Install license (example):
o /opt/ibm/db2/V9.1/adm/db2licm –a /tmp/db2/exp/disk1/db2/license/db2exp_uw.lic

Create the DB2 Administration Server (DAS)

* Login as root.
* Create DAS with dasusr1
o /opt/ibm/db2/V9.1/instance/dascrt -u dasusr1

* Login as dasuser1
o Start the DAS: db2admin start

* Optional: to enable autostarting of the DAS upon reboot
o /opt/ibm/db2/V9.1/instance/dascrt/dasauto –on

Create DB2 instance

* Login as root.
* Create instance with users db2fenc1 and db2inst1:
o /opt/ibm/db2/V9.1/instance/db2icrt -a server -u db2fenc1 db2inst1
* Optional: enable autostarting of the db2inst1 instance
o /opt/ibm/db2/V9.1/instance/db2iauto –on db2inst1

Update environment variables

* Example:
* Login as db2inst1
* edit .bash_profile
o vi /home/db2inst1/.bash_profile
o insert the following line at the end of the file – “. /home/db2inst1/sqllib/db2profile”

* Do the same for dasusr1, using its corresponding directory.

Database service

* Login as root:
* Add new service entry:
o vi etc/services
o insert this line “DB2_TMINST 50000/tcp” at the end of the file

Verification

* Login as db2inst1
* List installed DB2 products and features: db2ls
* Display the default instance: db2 get instance
o Result: The current database manager instance is: db2inst1
* Start the database instance: db2start
o Result: SQL1063N DB2START processing was successful.
* Stop the database instance: db2stop
o Result: SQL1064N DB2STOP processing was successful.

Remote Database Support,DBAWorkDatabase support, Database services, Oracle ADF

SQL5005C error IBM DB2 LUW

Posted in DB2 with tags , , , , , , , on July 16, 2011 by DBAWork

Product : IBM DB2 LUW
Product version : 9.1,9.5,9.7
Platform: Linux ,Unix and Windows

Title : SQL5005C error

Question :
SQL5005C error window pop up.
Cause :

1. DB2SYSTM not found in installation directory.

Following entries are seen in db2diag file.

2010-12-03-03.06.32.584000+540 I22054739H284 LEVEL: Error
PID : 5352 TID : 5376 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
EDUID : 5376
FUNCTION: DB2 UDB, config/install, sqlfGetDbmCfg, probe:10
RETCODE : ZRC=0xFFFFEC73=-5005
2010-12-03-03.06.32.584000+540 I22055025H339 LEVEL: Severe
PID : 5352 TID : 5376 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
EDUID : 5376
FUNCTION: DB2 UDB, config/install, sqlf_alert, probe:8
DATA #1 : Hexdump, 4 bytes
0x05CAE74C : 73EC FFFF s…
2. File permission changed from 755 to 775,700 and 744 because of rmsoke

Following entries are seen in db2diag

2011-05-27-13.04.18.619309-240 I95211A453 LEVEL: Error
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: DB2 UDB, config/install, sqlf_read_db_and_verify, probe:30
MESSAGE : SQL5005: sqlf_openfile rc =
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFE9820 : 840F 0001 ….

2011-05-27-13.04.18.619937-240 I95665A348 LEVEL: Error
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: DB2 UDB, config/install, sqlf_read_db_and_verify, probe:0
RETCODE : ZRC=0xFFFFEC73=-5005

2011-05-27-13.04.18.620423-240 I96014A420 LEVEL: Severe
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: DB2 UDB, config/install, sqlf_alert, probe:9
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFE99F0 : FFFF EC73 …s

2011-05-27-13.04.18.620591-240 I96435A315 LEVEL: Severe
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: , , , probe:9
RETCODE : ZRC=0xFFFFEC73=-5005

Trace shows following entries

db2trace file showS:

2466 | | | | | | | | | | | | | | | | sqlf_read_db_and_verify data
[probe 0]
2467 | | | | | | | | | | | | | | | | | | | | sqloclose entry
2468 | | | | | | | | | | | | | | | | | | | | sqloclose exit
2469 | | | | | | | | | | | | | | | | | | | _pdlogInt exit
2470 | | | | | | | | | | | | | | | | | | pdLogRC exit
2471 | | | | | | | | | | | | | | | | | sqlt_logerr_zrc exit
2472 | | | | | | | | | | | | | | | | sqlf_read_db_and_verify exit
[rc = 0xFFFFEC73 = -5005]

3. Open control center in Windows 2003 server gets SQL5005C error

The user needs to have read,write and execute access on db2 directory.

More On DB2 support, DBAWorkDatabase support, Database services, Oracle ADF

DB2PD – Diagnosing an Application Information

Posted in DB2 with tags on July 1, 2011 by DBAWork

As per the memory structure DB2 has setting for minimum connection pool to be maintained on database level. Following is the command to get more information for the applications which are connected to current database.

Using the -apinfo parameter to capture detailed runtime information about the lock owner and the lock waiter

The following sample output was generated under the same conditions as those for Example
 
venus@boson:/home/venus=>db2pd -apinfo 47 -db pdtestDatabase Partition 0 — Database PDTEST — Active — Up 0 days 00:01:30Application :
Address :                0x0780000001676480
AppHandl [nod-index] :   47       [000-00047]
Application PID :        876558
Application Node Name :  boson
IP Address:              n/a
Connection Start Time :  (1197063450)Fri Dec  7 16:37:30 2007
Client User ID :         venus
System Auth ID :         VENUS
Coordinator EDU ID :     5160
Coordinator Partition :  0
Number of Agents :       1
Locks timeout value :    4294967294 seconds
Locks Escalation :       No
Workload ID :            1
Workload Occurrence ID : 2
Trusted Context :        n/a
Connection Trust Type :  non trusted
Role Inherited :         n/a
Application Status :     UOW-Waiting
Application Name :       db2bp
Application ID :         *LOCAL.venus.071207213730  ClientUserID :           n/a
ClientWrkstnName :       n/a
ClientApplName :         n/a
ClientAccntng :          n/aList of inactive statements of current UOW :
UOW-ID :          2
Activity ID :     1
Package Schema :  NULLID
Package Name :    SQLC2G13
Package Version :
Section Number :  203
SQL Type :        Dynamic
Isolation :       CS
Statement Type :  DML, Insert/Update/Delete
Statement :       insert into pdtest values 99venus@boson:/home/venus =>db2pd -apinfo 46 -db pdtest

Database Partition 0 — Database PDTEST — Active — Up 0 days 00:01:39

Application :
Address :                0x0780000000D77A60
AppHandl [nod-index] :   46       [000-00046]
Application PID :        881102
Application Node Name :  boson
IP Address:              n/a
Connection Start Time :  (1197063418)Fri Dec  7 16:36:58 2007
Client User ID :         venus
System Auth ID :         VENUS
Coordinator EDU ID :     5913
Coordinator Partition :  0
Number of Agents :       1
Locks timeout value :    4294967294 seconds
Locks Escalation :       No
Workload ID :            1
Workload Occurrence ID : 1
Trusted Context :        n/a
Connection Trust Type :  non trusted
Role Inherited :         n/a
Application Status :     Lock-wait
Application Name :       db2bp
Application ID :         *LOCAL.venus.071207213658

ClientUserID :           n/a
ClientWrkstnName :       n/a
ClientApplName :         n/a
ClientAccntng :          n/a

List of active statements :
*UOW-ID :          3
Activity ID :     1
Package Schema :  NULLID
Package Name :    SQLC2G13
Package Version :
Section Number :  201
SQL Type :        Dynamic
Isolation :       CS
Statement Type :  DML, Select (blockable)
Statement :       select * from pdtest

More on Database Support, DBAWorkDatabase support, Database services, Oracle ADF

System Databases – SQL Server

Posted in DB2, General, Oracle, SQL Server with tags on June 3, 2011 by DBAWork

Microsoft SQL Server is a relational database server developed by Microsoft : It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).

Master DB

Records all the system-level information for an instance of SQL Server.
This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.
Therefore, SQL Server cannot start if the master database is unavailable.

msdb DB

It is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

model DB

Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

ResourceDB

Is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Tempdb DB

It is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Distribution

Database responsible for the replication metadata between the publisher and subscriber(s)

ReportServer

Primary database for Reporting Services to store the metadata and object definitions

  • Reports security
  • Job schedules and running jobs
  • Report notifications
  • Report execution history

ReportServer TempDB

Temporary storage for Reporting Services

Session information

Cache

Remote database support, DBAWorkDatabase support, Database services, Oracle ADF