Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] DB2 issues - how to set NONRECOVERABLE w/INSERTSIZE & how to avoid deadlocks

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] DB2 issues - how to set NONRECOVERABLE w/INSERTSIZE & how to avoid deadlocks
 Login/Join
 
Member
posted
We (non-I.T. power users) create large DB2 tables under 2 circumstances:
1. ETL type processes bringing mainframe data or external files into DB2 to work with
2. Part of ad-hoc type queries where keeping result sets in DB2 for subsequent steps is advantageous.

Our datamart is DB2 on Unix, approx 22TB.

Some of the tables we create are large (12 million+ rows), and the load jobs can run a long time (14+ hours). We do this because we need external data (MF, SQL) but need it sooner than fits into the I.T. release schedule.
ie - we do it because we have to, not because we want to.

We create the tables using "ON TABLE HOLD AS xxxx FORMAT DB2". While I would LOVE to leverage Data Migrator for the loads between mainframe and DB2, that is NOT an option for me. (We have considered using MODIFY for the static ETL type loads, but that isn't optimal for our ad-hoc type queries) My DBA tells me the data is being inserted 1 row at a time.

QUESTION #1: Looking at adjusting the INSERTSIZE at the DB2 adapter level, the DBA said there was an issue since the NONRECOVERABLE command was not being set, and that would put the tablespace into a pending backup condition. Is there a way to set this either at the adapter/server level, or in the code? Is this set as part of the WebFOCUS configuration or as part of the DB2 client?

I'm thinking we would get a good performance boost by changing the INSERTSIZE from "1" to a larger value. Does this invoke the native bulk load, and would this stop the records inserting 1 at a time? We have SAS jobs that load similar data via bulk load in MINUTES rather than what it is taking WebFOCUS to do.

Our current adapter settings are:
FETCHSIZE=500
INSERTSIZE=1
FASTLOAD=ON


QUESTION #2: Because our load jobs run so long - and because our tablespace shares the system tables with production - we often create deadlocks which not only freeze us out, but can impact the production loads.
We code passthru SQL COMMIT statements after every drop command and every ON TABLE HOLD AS xxx FORMAT DB2 command per our DBAs recommendation. We also run ISOLATION=RU. Both of these have eased the symptoms, but haven't cured the disease. We've had weekly jobs run for years with no issues, then cause a deadlock condition. (per our DBA)

SAS also runs in this same space, but the DBA seems to want to always blame WebFOCUS Frowner

Is there anything else I can do to either determine why we are causing deadlocks, or to further minimize the chance of this?

Our other adapter settings are:
OPTIMIZATION=ON
ISOLATION=RU
AUTODISCONNECT=ON FIN

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.1
AIX / Windows 2003
 
Posts: 17 | Location: Milwaukee | Registered: February 16, 2006Report This Post
Expert
posted Hide Post
ENGINE [DB2] SET INSERTSIZE n
ENGINE [DB2] SET FASTLOAD [ON|OFF]


Have you taken a thorough look at Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS Version 7 Release 6.*?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Yes, I've been reading the manual(s), but have not yet come across what I need. (Also have not come across anything on this specific issue searching the IBI website or FocalPoint)

If I'm interpreting your response correctly, I do know how to physically change the INSERTSIZE and FASTLOAD settings - that isn't my issue.

My specific problem is when I DO that (set n=500), the DB2 option "NONRECOVERABLE" is not being set and this puts the entire DB2 tablespace into a pending backup condition when I'm done writing a table.

This may be occurring via the DB2 client for all I know. Neither my DBA or support area can tell me why this happens, so the settings don't change, I insert records 1 at a time (per my DBA), and my job runs 14 hours. My next step may be to use SAS - which can load the table in under an hour. But I don't want to go to the dark side Smiler

Since I'm not in I.T., I don't have access to all of the logs or tools I need to get 100% of the picture - just what I'm told by my DBA - which is why I'm appealing to the collective brainpower of these forums Smiler

If there is a specific section of the manual that covers this that I should be concentrating on, any guidance would be appreciated. I'm just an end-user, so it takes me longer Smiler


WebFOCUS 7.6.1
AIX / Windows 2003
 
Posts: 17 | Location: Milwaukee | Registered: February 16, 2006Report This Post
Expert
posted Hide Post
Hi Dave,

I pass this one to our internals and was suggested that you may want to open a case regarding this issue, since it is relatively involved. You may either call at 1-800-736-6130, or access online at InfoResponse.

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] DB2 issues - how to set NONRECOVERABLE w/INSERTSIZE & how to avoid deadlocks

Copyright © 1996-2020 Information Builders