lockouts on new report

We have a report that is reading a db2 table. Occasionally the table is updated and it appears if it's being updated while webfocus is trying to read it, we get a deadlock/timeout and have to get a dba involved. Is there a command that I can put in my webfocus code to tell it not to worry about updates, just take a look at that point in time and get the info needed. We can't have deadlock/timeouts in production. Has this ever happened to anyone else? We have hundreds of reports in existence and have never seen this happy before.

Thanks.

webfocus 8.105M; os: windows; pdf, html, exl2k, csv

Posts: 179 | Registered: November 10, 2004

smiths

Platinum Member

posted

Hide Post

Pam,

Are you issuing a COMMIT on the update of the DB? I had a problem where the (Oracle) DB was locked because I forgot to issue the COMMIT at the end.

Sean

------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode

Posts: 210 | Location: Ottawa | Registered: November 03, 2005

Ignored post by smiths posted

Show Post

BlueZone

Platinum Member

posted

Hide Post

Pam :
If a DB2 table is being updated, the DB2 resource manager will automatically lock out everyone else from reading Un-commited data. This is for data integrity and it cannot be bypassed.

However, if you can get your DBA to reduce the lock-size parameter on the table to the smallest size possible (Page-level for sure, I believe even Row-level locks can be done), you can circumvent the dead-lock issue.

Size and Space permitting, you can also look at having an image copy of the table to a new one, that is not open to random updates. This new static table can be re-loaded once or twice a day to keep as current as possible.

Sandeep Mamidenna.

-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !!

Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006

Ignored post by BlueZone posted

Show Post

Pam Kratt

Platinum Member

posted

Hide Post

The program that is running is not doing commits. I checked and it's supposed to run at 2:30 a.m. CT so it shouldn't be a problem in production technically. That program is being rewritten to load to the db2 table differently but it will be going in after my report that reads that same table goes into production.

I'm going to make sure that the update to the table is done off-hours and that should keep us in good shape until the program change can go in to fix the lack of commits.

Thanks for your help.

webfocus 8.105M; os: windows; pdf, html, exl2k, csv

Posts: 179 | Registered: November 10, 2004

Ignored post by Pam Kratt posted

Show Post

KevinG

Platinum Member

posted

Hide Post

Pam,

Or use SQL pass-thru making sure
FOR FETCH ONLY WITH UR;
is the last SQL statement.

Kevin

WF 7.6.10 / WIN-AIX

Posts: 141 | Location: Denver, CO | Registered: December 09, 2005

Ignored post by KevinG posted