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

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,

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.*?

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

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

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

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