Rebinding a package after every time RUNSTATS is executed seems to be a bit
too much unless the tables used are drastically changing each time your
procedures execute.
Now, for your option number 1, have the SP do the rebind, well, that could
get complicated. You would need to take advantage of an exit that could be
used to execute commands at the system level from a DB2 stored procedure.
This does not provide the ability of knowing whether or not the command
executed successfully so you would need to provide coding to somehow check
if the bind even worked.
For your option number 2, I would suggest you check the REOPT option of the
REBIND statement. This will cause DB2 to, in a sense, do a redetermination
of access paths based upon current statistics. For instance,
REBIND PACKAGE schemaname.packagename REOPT ALWAYS
By the way, the above assumes you are running DB2/LUW.
Al Glinter, Lead Database Administrator
Federated Systems Group
FSG St. Louis - 19th Floor
611 Olive Street
St. Louis, MO 63101
314-342-6800 X-3389
"Ashok Rathi "
<db2udbdba-ezmlms
hield-x80062646.x To
[Email Address Removed] "LazyDBA Discussion"
A.com> <[Email address protected]
cc
03/26/2007 02:08
PM Subject
Bind and Lock timeouts
Last week I read a thread on binding of packages. My problem is slightly
different. Right after Runstats, we used to bind all packages in one
shot at the database level. This would frequently result in lock
timeouts since our app was still running. Then we switched to individual
package-level binding. This mitigated lock timeouts considerably. Next I
want to explore how I can completely eliminate lock timeouts.
I have a couple of thoughts, but not sure if they are doable in DB2:
#1: When creating Stored Procs, always force them to be rebound when
executed. I understand the performance penalty for rebinding, but these
are long-running SPs anyway. If they take a few seconds extra with every
execution, I don't care.
#2: Don't bind packages right after Runstats. Instead make them dirty so
that when SP is executed, the corresponding packages are automatically
rebound. Not sure if it is possible in DB2...let me know if you do.
Any other suggestion would be helpful!!!
Thanks a lot!
-- ashok
**************************************************************************
DemandTec Email Notice
This email and any attachments may contain confidential and/or proprietary
information and is intended solely for the use of the addressee. If you are
not the intended recipient we request that you notify us via email or
telephone and delete all copies of the message from your systems.
Additionally, although DemandTec has taken reasonable precautions to ensure
the security of this email and any attachments, we encourage you to take
similar precautions and accept no liability for any loss or damage
resulting from its use.
DemandTec, 1 Circle Star Way, Suite 200, San Carlos, CA 94070, 650-226-4600
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page