473,289 Members | 2,106 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Rebind operation locks up

Hi

I'm using DB2 V9.1.0 on SUSE Linux with kernel version 2.6.5.

In the system I'm working on, there are several stored procedures which
perform simple selects through different views.

Those views are redefined (drop/create) periodically. DB2 rebinds the
stored procedures that use the views, automatically.

The stored procedures are executed concurrently by another program. My
problem is that the rebind operations seem to interfere with each
other, causing a lockup. Neither can proceed.

With a long lock timeout (5 minutes) they'll wait the full 5 minutes,
every time.

If I manually issue rebind commands for the stored procs, one after the
other, the stored procs execute trouble-free when the program using
them runs.

I'm seeing lock waits like this, from db2pd -locks wait

0x26218AE0 18 00000F00430024590100000052 Row ..X W 19 1 0 0x10
0x40000000 TbspaceID 0 TableID 15 PartitionID 0 Page 88356 Slot 67
0x26219B40 19 00000F00430024590100000052 Row ..U G 19 1 0 0x10
0x40000000 TbspaceID 0 TableID 15 PartitionID 0 Page 88356 Slot 67

The table with ID 15 in tablespace 0 is SYSIBM.SYSSECTION. It appears
to be 2 locks on a row in that table that is at the center of the
problem. But this is an operation under DB2's control.

Has anyone else seen lockups with automatic rebinds? Could this be a
DB2 bug?

Thanks
Ben

Nov 3 '06 #1
2 2254
In article <11**********************@i42g2000cwa.googlegroups .com>,
Be**********@gmail.com says...
Hi

I'm using DB2 V9.1.0 on SUSE Linux with kernel version 2.6.5.

In the system I'm working on, there are several stored procedures which
perform simple selects through different views.

Those views are redefined (drop/create) periodically. DB2 rebinds the
stored procedures that use the views, automatically.

The stored procedures are executed concurrently by another program. My
problem is that the rebind operations seem to interfere with each
other, causing a lockup. Neither can proceed.

With a long lock timeout (5 minutes) they'll wait the full 5 minutes,
every time.

If I manually issue rebind commands for the stored procs, one after the
other, the stored procs execute trouble-free when the program using
them runs.

I'm seeing lock waits like this, from db2pd -locks wait

0x26218AE0 18 00000F00430024590100000052 Row ..X W 19 1 0 0x10
0x40000000 TbspaceID 0 TableID 15 PartitionID 0 Page 88356 Slot 67
0x26219B40 19 00000F00430024590100000052 Row ..U G 19 1 0 0x10
0x40000000 TbspaceID 0 TableID 15 PartitionID 0 Page 88356 Slot 67

The table with ID 15 in tablespace 0 is SYSIBM.SYSSECTION. It appears
to be 2 locks on a row in that table that is at the center of the
problem. But this is an operation under DB2's control.

Has anyone else seen lockups with automatic rebinds? Could this be a
DB2 bug?

Thanks
Ben

I don't know if it is a real bug but I've seen it happen a lot of times
with the bind files belonging to our COBOL programs. Manually binding
them didn't cause a problem but the automatic rebinds were causing a lot
of trouble. We never did any table/etc.. updates during online hours,
only during the housekeeping interval.
Nov 3 '06 #2
Gert van der Kooij wrote:
In article <11**********************@i42g2000cwa.googlegroups .com>,
Be**********@gmail.com says...
>Hi

I'm using DB2 V9.1.0 on SUSE Linux with kernel version 2.6.5.

In the system I'm working on, there are several stored procedures which
perform simple selects through different views.

Those views are redefined (drop/create) periodically. DB2 rebinds the
stored procedures that use the views, automatically.

The stored procedures are executed concurrently by another program. My
problem is that the rebind operations seem to interfere with each
other, causing a lockup. Neither can proceed.

With a long lock timeout (5 minutes) they'll wait the full 5 minutes,
every time.

If I manually issue rebind commands for the stored procs, one after the
other, the stored procs execute trouble-free when the program using
them runs.

I'm seeing lock waits like this, from db2pd -locks wait

0x26218AE0 18 00000F00430024590100000052 Row ..X W 19 1 0 0x10
0x40000000 TbspaceID 0 TableID 15 PartitionID 0 Page 88356 Slot 67
0x26219B40 19 00000F00430024590100000052 Row ..U G 19 1 0 0x10
0x40000000 TbspaceID 0 TableID 15 PartitionID 0 Page 88356 Slot 67

The table with ID 15 in tablespace 0 is SYSIBM.SYSSECTION. It appears
to be 2 locks on a row in that table that is at the center of the
problem. But this is an operation under DB2's control.

Has anyone else seen lockups with automatic rebinds? Could this be a
DB2 bug?

Thanks
Ben


I don't know if it is a real bug but I've seen it happen a lot of times
with the bind files belonging to our COBOL programs. Manually binding
them didn't cause a problem but the automatic rebinds were causing a lot
of trouble. We never did any table/etc.. updates during online hours,
only during the housekeeping interval.
I've also experienced DB2 crashes when we called a procedure that was
invalid and DB2 had to rebuild it. We tracked the problem down to invalid
functions (had to be recreated). When we recreate those functions first and
then recreate procedures everything is working.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 4 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul Moore | last post by:
I can't find anything which spells this out in the manuals. I guess that, at some level, the answer is "a single bytecode operation", but I'm not sure that explains it for me. This thought was...
1
by: Dale Franklin | last post by:
This should be a rather ROUTINE procedure for you DBAs. I keep hearing at technical conferences and user groups the phrase "then rebind all your plans and packages" (after reorg, load, &...
0
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in...
2
by: SKC | last post by:
Platform : Solaris UDB : version 7.2 FP9 DB2RBIND PACKAGE SCHEMA.PACKAGE fails with SQL0029N error. When I issue, "db2rbind DBNAME all", it is successfull. Can anyone help me understand...
3
by: Zeng | last post by:
Hello, Is the assignment operation atomic? That is if one thread assigns to static variable an object and another uses the object assigned to the variable, would it be safe? Thanks! zeng
4
by: Alex Callea | last post by:
Hi there, We have a web application handling thousands of requests per seconds reading sql server data which is heavily updated. We are generally experiencing no performance problems. On some...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
1
by: shenanwei | last post by:
I have db2 v8.2.5 on AIX V5.3 with all the switches on Buffer pool (DFT_MON_BUFPOOL) = ON Lock (DFT_MON_LOCK) = ON Sort ...
0
by: rattas | last post by:
Hi all I'm using DB2 V9.1.0 on SUSE Linux with kernel version 2.6.5. In the system I'm working on, there are 2 stored procedures which perform simple selects through different views. Those...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.