470,631 Members | 1,456 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

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 2141
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bruce Pullen | last post: by
2 posts views Thread by SKC | last post: by
3 posts views Thread by Zeng | last post: by
4 posts views Thread by Alex Callea | last post: by
reply views Thread by rattas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.