db2 plans - slow access after Reorg, Rebind or Bind

2012-01-24  Source: Original Site  Category:DB2  Views:0 

Advertisement

This customer site is using DB2 for z/OS v7. The application is using DB2 plans only, no packages yet. The online system is very active, and during online, most of the locking is row locking , to allow more concurrency.

There were 2 incidents we observed that caused the application having many contentions, deadlocks and slow access.

Incident 1 - an online REORG was done on all the tablespaces, then on weekend, while CICS regions were shutdown, a REBIND of all the plans were executed. After this, the application had a systemwide locks suspended and extremely slow performance. We saw this problem during CICS online window, we did a BIND on the plans, and that fixed the issues. After the BIND Plans, the application ran smooth and fast again.

Incident 2 - wednesday night, drop a tablespace, recreate it as one of the column in the middle of the table needed to be increased from 2 characters to 10 characters. After the recreate, data loading, grant access, a BIND on the affected plans were performed. This was done during CICS shutdown too.
the next day, again, observed systemwide slowness, some affected plans not running fast enough, causing deadlocks. Resolution was during the CICS online window, we BIND the plans again to solve the slow and deadlocks issues.

I am trying to figure out the rootcause here , Incident 1 - why was it a REBIND after a REORG not working OK , DB2 seem stucked and confused with the access path, things all jammed up. Incident 2 - why wasn't the first BIND did right after the DROP TS/RECREATE TS tasks were done. The online seem stucked until a 2nd BIND was performed.

Does anyone encounter this kind of issues before ? any ideas , please...

Related articles
  • db2 plans - slow access after Reorg, Rebind or Bind 2012-01-24

    This customer site is using DB2 for z/OS v7. The application is using DB2 plans only, no packages yet. The online system is very active, and during online, most of the locking is row locking , to allow more concurrency. There were 2 incidents we obse

  • To create a db2 plan to access two tables present in different qualifierss 2012-01-02

    I want to create a DB2 plan such that the owner of plan is say 'ABCD' and qualifier is 'PQRS'.The DBRMs associated with this plan accesses two tables say table M (with qualifier PQRS) and table N (with qualifier XYZ).In this case how should we write

  • Rebinds of DB2 plans? from ver 5.2 to 7.1 2014-08-01

    Does anyone know if it is necessary to rebind DB2 plans when you are migrating from DB2 OS/390 version 5.2 to version 7.1??? Or should the DB2 plans in an older version not affect a newer version?

  • Performance problems after REORG 2014-05-25

    The brief explanation of the problem is, After DB2 v7 maintenance window, performance has gone down, it takes some hours to retrieve the data. We have provided some table spaces to Reorg/Runstat during the maintenance window . After REORG some of the

  • DB2 client cannot access the db2 instance on AIX 2013-01-12

    I am facing problems connecting to db2 instance from the client. Things were all fine- DB2 client(on Windows Server 2003) was connecting to the db2 server(on AIX) instance(db2inst1). I could run queries and deploy jobs in the server. I was troublesho

  • Ubuntu Internet slows down after suspend/hibernate 2013-12-09

    My wireless internet speed slows down after starting my Asus K52F laptop from suspend or hibernate. When I shut down or restart my laptop my internet speed (as tested on speedtest.net) will be something like: 30-40 ping, 4 Mbps download, 0.5 Mbps upl

  • Oracle Removing user access after hours 2012-03-06

    All, We are running Oracle 10g. What is the best way to remove user access after hours say from 5:00 pm to 6:00 am? I have searched this site and the only thread that I found was http://forums.oracle.com/forums/thread.jspa?messageID=4392977 which is

  • Ubuntu Very slow boot after installing a new 11.04 2012-03-08

    My notebook, which used to boot in ~25s, is now taking almost 5 minutes to boot. Here's the interesting parts of the dmesg output: [ 5.041723] ACPI: Video Device [GFX0] (multi-head: yes rom: yes post: no) [ 5.041904] [drm] Initialized i915 1.6.0 2008

  • Ubuntu Slow Down After Upgrade 2012-03-16

    Has anyone else noticed a slow down after upgrading? It seems all my apps seem to take a lot longer to load. Any work arounds to speed things up a bit?

  • Ubuntu Desktop effects slow down after some time 2012-06-25

    They worked well until yesterday, then sudden;y they stopped working properly. Window movement is not as smooth as it used to be and minimize effect slows down after some time. It will work better for a few minutes if I click "reload window manager"

  • Slow access - Any more distributed ways to deploy Reqpro ? 2012-11-15

    > Hi, > > Today, we are running a multi-nation Reqpro project based on a centre or single Oracle DB. The people complained about the extremely slow access from remote side, e.g. unacceptable speed to open a Word Doc. from native client. I kn

  • Ubuntu Extremely slow transfers after running Deluge BT 2013-02-21

    I have a system running Ubuntu Hardy, and have had a problem with slow transfers after leaving Deluge running overnight. After a fresh format of the drive, transfers are about 35 MB/sec, but after running Deluge overnight, transfers crawl at 500KB/se

  • DB2 supports concurrent access to RFT project is it easy to maintain? 2013-04-03

    DB2 supports concurrent access to RFT project is it easy to maintain? Any guidelines about the usage of DB2 with RFT will be useful.

  • Find DB2 plan of my program (MVS COBOL ) with in this program while running 2013-10-08

    I am working Mainframe DB2. I have a DB2 COBOL program running on IBM Mainframe. I need to capture its associated DB2 Plan with in this COBOL program under which it is running. Depending upon the DB2 plan that this program is running under, I need to

  • determine space saved after REORG 2013-11-19

    After performing REORG, how do I determine if it was advantageous to execute the utility in terms of space utilization i.e how do i know how much space I gained after REORG?

  • Ubuntu laptop extremely slow even after resolving bcm43xx_microcode5.fw issue 2014-03-29

    i have an HP compaq nx7300 laptop; after installing ubuntu 7.04, i would get keep getting (during boot-up) the error message "bcm43xx_microcode_5.fw not found or couldnt be loaded". and the laptop would be terribly slow in all applications (especiall

  • Will Ubuntu performance slow down after using for long time like in Windows? 2014-05-06

    I'm wondering whether Ubuntu performance will slow down after using it for several years like in Windows where as you install and uninstall programs more and more junks are accumulated or there are other factors which I don't know yet.

  • Intermittent slow access to cifs mounted shares – Help stop my school going Windows 2014-06-02

    I am having major issues with slow access to windows shares that are mounted using cifs. The shares get mounted correctly and you can navigate through the directories and open files. The only problem is that it randomly starts going really slow takin

  • Index still flagged on F6 after reorg. 2014-11-25

    After reorging a table and doing runstats the index for the table still stays flagged as in a need for a reorg. Can anyone explain what F6 exactly means (I know the formula) and how I can get the index so that it will not show up flagged anymore? Her

  • DB2 V5R2M0 Client Access Express 2014-12-17

    I'm looking for DB2 V5R2M0 Client Access. I made a search on IBM site, but nothing appears. I need to know where I can download or buy V5R2M0. I appreciate some help Best