Solved

update db2 V 8.01 table but get time out error 68

Posted on 2015-01-09
33
221 Views
Last Modified: 2015-01-27
Dear all,

we are just updating a talbe via a view, and it works days ago, but today when I do it again, it said:

- DB2 Database Error: ERROR [40001] [IBM][DB2/SUN] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".

how can I know which query lock the underly table so that I can kill.

the kill command is kill and then followed by what number?

tell me how you guys troubleshoot it.

this is the best link I found :

http://stackoverflow.com/questions/7957887/db2-deadlock-timeout-sqlstate-40001-reason-code-68-due-to-update-statements-ca

but it seems that I get problem when login, any script to run by IBM data studio/Toad UI ?
0
Comment
Question by:marrowyung
  • 21
  • 12
33 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 40540153
Hi marrowyung,

You can run db2top to show the locks.  Then search the Object Name column for the table name, and the Is Blocker column to find the offending task.

db2 force application will kill it.


Good Luck!
Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40540526
hi,

"You can run db2top to show the locks. "

from within Toad? or must in command mode? what is the full command ?

it seems it run within toad.

"db2 force application will kill it."

what is that mean ? you mean I run db2top and it return the session id or caller id, then I use the kill command to kill it?

will db2top show which table it locks ? this is very important to me.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40540583
Sorry.  I got ahead of myself.  The DB2TOP command isn't available to a V8 system.  That's really old -- do you have any plans to upgrade?

The commands that I'm about to describe must be run from the DB2 command line processor.  Depending on the version of TOAD that you're running, there may be (should be) a mode where the command is passed to the CLP instead of DB2.

  db2 "get snapshot for locks on {dbname}"

That will return information for all of the connections.  Each connection will display something that resembles this:

Application handle                         = 67
Application ID                             = *LOCAL.DB2.150107183555
Sequence number                            = 00001
Application name                           = db2taskd
CONNECT Authorization ID                   = User1
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1252
Locks held                                 = 0
Total wait time (ms)                       = Not Collected

Open in new window


Look for the connection(s) where the value for "Locks held" is non-zero.  If you decide that you want to kill that connection, type:

  db2 "force application {handle}"

The value for {handle} will be the value in the first line of the displayed block.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40543672
"Sorry.  I got ahead of myself.  The DB2TOP command isn't available to a V8 system.  That's really old -- do you have any plans to upgrade?
"

will be transform to MS SQL

here is the lock snapshot:

           Database Lock Snapshot

Database name                              = ARSYSTEM
Database path                              = /space/home/db2inst6/db2inst6/NODE0000/SQL00001/
Input database alias                       = ARSYSTEM
Locks held                                 = 3
Applications currently connected           = 17
Agents currently waiting on locks          = 0
Snapshot timestamp                         = 01/12/2015 04:13:23.813863

Application handle                         = 110
Application ID                             = *LOCAL.db2inst6.150112041118
Sequence number                            = 0005
Application name                           = db2bp
CONNECT Authorization ID                   = DB2INST6
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 126
Application ID                             = *LOCAL.db2inst6.150105090700
Sequence number                            = 9341
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 122
Application ID                             = *LOCAL.db2inst6.150105090530
Sequence number                            = 4494
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 116
Application ID                             = *LOCAL.db2inst6.150105090500
Sequence number                            = 0651
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 64
Application ID                             = *LOCAL.db2inst6.150105083520
Sequence number                            = 4847
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 55
Application ID                             = *LOCAL.db2inst6.150105083435
Sequence number                            = 2120
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 26
Application ID                             = *LOCAL.db2inst6.150105083434
Sequence number                            = 7053
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 70
Application ID                             = *LOCAL.db2inst6.150105083433
Sequence number                            = 4771
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 47
Application ID                             = *LOCAL.db2inst6.150105083432
Sequence number                            = 4790
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 57
Application ID                             = *LOCAL.db2inst6.150105083431
Sequence number                            = 0002
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 52
Application ID                             = *LOCAL.db2inst6.150105083430
Sequence number                            = 0002
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 53
Application ID                             = *LOCAL.db2inst6.150105083429
Sequence number                            = 0003
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 41
Application ID                             = *LOCAL.db2inst6.150105083428
Sequence number                            = 0003
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 56
Application ID                             = *LOCAL.db2inst6.150105083427
Sequence number                            = 0002
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 46
Application ID                             = *LOCAL.db2inst6.150105083426
Sequence number                            = 9922
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 43
Application ID                             = *LOCAL.db2inst6.150105083200
Sequence number                            = 7458
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 0
Total wait time (ms)                       = 0


Application handle                         = 35
Application ID                             = *LOCAL.db2inst6.150105083003
Sequence number                            = 0019
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 3
Total wait time (ms)                       = 0

List Of Locks
 Lock Name                   = 0x00000C77000000010001FF0056
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Variation Lock
 Mode                        = S

 Lock Name                   = 0x535953534832303028FFFFFF41
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x00020422000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 1
 Lock Object Name            = 1058
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = ROOT
 Table Name                  = T280
 Mode                        = IS

Open in new window


we focus on lock on T280 , what should we do now to kill the one who locks the table T280 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40543696
I tried both IBM data studio client and Toad 10.0 for DB2, they both give me the same result !

instead of this "db2 "get snapshot for locks on {dbname}" from command prompt, any command I can use from inside IBM data studio client or Toad to list out what is locking T280 table and let me kill it ?

BTW, what is teh command to show out all connect as well the command runing on DB2 v8.02 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40543724
oen thing, update the table one record by one record is ok ! udpate by batch is not ok !

update from application side is ok too !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40543739
I am thinking about what if I use a Toad which hasn't get the auto commit on ! and I didn't commit any statement at all.

should the table has anything not commited and that left the T280 table locked?
 
anyway to commit the any transaction hold the T280 / commit all transcation running in the background so that lock can be release ?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40544183
If auto commit is turned off and you don't commit the changes manually, the locks remain active.

Do you have the IBM client installed on your desktop?  That should give you access to the command line processor, db2top, db2pd, etc.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40544614
"If auto commit is turned off and you don't commit the changes manually, the locks remain active."

then should I type commit; and let DB2 commit everything so the lock release? I type commit; but it seems doesn't work.

"Do you have the IBM client installed on your desktop?  That should give you access to the command line processor, db2top, db2pd, etc.
"

I can just putty to it, right? the latest version of toad use IBM client. I have IBM data studio as well but that one very slow.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40544617
but you have just said  my DB2 v8.02 do not have db2pd, right?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40544631
db2pd is documented as being available with version 8.
db2top came in with version 9.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40545807
should I just db2>  force application 35 ?

will the DB2 down because of this ?
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 40545910
Do you have the DB2 command line processor available?  If so, as noted above, do this:

  db2> get snapshot for locks on {dbname}

Then

  db2> force application {number}

The FORCE APPLICATION command affects only the connection identified by {number}.  It has no other impacts on DB2.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40545929
"Do you have the DB2 command line processor available?  If so, as noted above, do this:"

yes!

let me try
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40545932
wait, when I type the applicatoin handle number in

db2> force application {number}

Application handle                         = 35
Application ID                             = *LOCAL.db2inst6.150105083003
Sequence number                            = 0019
Application name                           = arserverd
CONNECT Authorization ID                   = ROOT
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 819
Locks held                                 = 3
Total wait time (ms)                       = 0

List Of Locks
 Lock Name                   = 0x00000C77000000010001FF0056
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Variation Lock
 Mode                        = S

 Lock Name                   = 0x535953534832303028FFFFFF41
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x00020422000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 1
 Lock Object Name            = 1058
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = ROOT
 Table Name                  = T280
 Mode                        = IS

Open in new window


it said :
db2 => force application 35
SQL0104N  An unexpected token "35" was found following "APPLICATION".
Expected tokens may include:  "ALL".  SQLSTATE=42601

Open in new window


then which number I should type in ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40545937
the command should be force application ( number) , e.g.

db2 >force application (35)
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:marrowyung
ID: 40545956
I can tell you that db2pd -lock doesn't work !

even db2pd -runstats doesn't work.

the error is :

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

Open in new window


db2top doesn't exists !
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40546499
Hi Marrowyung,

If db2top and db2pd are available, you run them from the DOS prompt, not the DB2 prompt.

Open the db2 Command Line Processor window and then type QUIT at the db2 prompt.  You'll be dropped into a DOS command shell where you can try those commands.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40550537
"If db2top and db2pd are available, you run them from the DOS prompt, not the DB2 prompt."

ok , but we are running DB2 on Solars 9, so this command no need or you are talking about AFTER installing IBM DB2 client on PC then we can run that afte connect to DB2?

we use putty to connect to DB2, is not related to DOS at all..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40552858
any more update to me Kent ?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40553307
Sorry....  I thought that you'd resolved this....

What's left to do?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40553864
"ok , but we are running DB2 on Solars 9, so this command no need or you are talking about AFTER installing IBM DB2 client on PC then we can run that afte connect to DB2?

 we use putty to connect to DB2, is not related to DOS at all.. "
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40554047
Ah... Ok....

The DB2 command line processor is the same whether run in a Windows or *nix environment.  You should be able to connect to the DB2 server with putty, type "db2", and enter the commands necessary to see the tasks that are holding locks (get snapshot for locks) and terminate those tasks (force application).

If, after you connect to the server, you can't run the db2 command, you'll need to execute the db2profile.  (You might want to execute it from your default profile.)  If it's in the default location, add these 4 lines to your profile.

if [ -f /home/db2inst1/sqllib/db2profile ]; then
  . /home/db2inst1/sqllib/db2profile --//this is the db2profile path
  check your instance home directory
fi


A clever bit of korn (or bash) shell scripting can parse the output of "get snapshot for locks" and display only those tasks where the lock count is non-zero.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40561472
"If, after you connect to the server, you can't run the db2 command, you'll need to execute the db2profile.  (You might want to execute it from your default profile.)  If it's in the default location, add these 4 lines to your profile."

you mean if can't run db2 command, I need to run db2profile command to edit my own profile so that db2 command is available ? then I can run db2top and db2pd  too ?

can't see how related to db2top and db2pd by that:

if [ -f /home/db2inst1/sqllib/db2profile ]; then
   . /home/db2inst1/sqllib/db2profile --//this is the db2profile path
   check your instance home directory
 fi 

Open in new window



tks,
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40561868
You'll have to take the pseudo comment off of line 2.  :)

The db2profile sets your environment for running db2 utilities.  (The path variable is one of the things that gets set.  Other variables are created or set.)

You can execute the db2profile from the command line to set up the environment

     . /home/db2inst1/sqllib/db2profile

But it's easier to execute it from your user profile.  Include these lines in your profile.

  if [ -f /home/db2inst1/sqllib/db2profile ]; then
     . /home/db2inst1/sqllib/db2profile
   fi

The extra lines are just to make sure that the profile doesn't abort if the db2profile can't be found.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40563775
so you mean that once I set the profile as it can find the path to db2top and db2pd , then I can run it from unix/linux prompt?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40564129
If db2top and db2pd are part of your db2 system, running the db2profile should enable them at the command prompt.

Note that if you can run the db2 command from the command prompt, the db2profile has already been run.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40565924
"Note that if you can run the db2 command from the command prompt, the db2profile has already been run.
"

yes, exactly what I am thinking if I can run db2 with enter, it can see the db2 command already, but the point is if I run either db2top or db2pd, as I said it returns:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
                                          

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
ID: 40566325
You need to quit out of the db2 command line processor back to the O/S shell to run the db2top and db2pd commands.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570097
so no reboot of that after changing/setting the profile ?

anyway to check the current profile informaton ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570101
it seems db2pd is working and it give me a db2pd > prompt,

what should I type to show out locking process?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40570395
db2pd works a lot like the command processor, except that it doesn't have an interactive help mode.  To get a full help, type the following at the command line prompt:

  db2pd -h

It'll show you a lot more than you want to see.

To get just the locks for a particular database, try this:

  db2pd -db {mydb} -locks
or
  db2pd -db {mydb} -locks showlocks
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40571989
tks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now