DB2 LOAD RESUME YES SHRLEVEL CHANGE

Debra Anne Kopecky

DB2 LOAD RESUME YES SHRLEVEL CHANGE
We are executing 5 parallel LOAD RESUME YES SHRLEVEL CHANGE jobs, each
loading 5 million rows. This occurs 5 times per month. The final
execution of the series of jobs ran for 40 hours elapsed and
171.63minutes CPU each.

We made several mistakes such as not executing a REORG on the data (only
on the indexes) after each of the load series completed, and not having
sufficient FREEPAGE (set at 31 for both data and indexes) and PCTFREE
(set at 15 both data & indexes) The tablespace is nonsegmented/not
partitioned. The table starts out empty and all data is loaded to the
table via the LOAD RESUME YES SHRLEVEL CHANGE jobs.

I have made freespace allocations for the new month's table at FREEPAGE
15 (for both data and indexes) and PCTFREE = 40 for the indexes and
PCTFREE = 30 for the data. And have a REORG scheduled after each series
of loads. What I need to know is this going to be sufficient? What does
DB2 do about the freespace settings when a new page is allocated? A
traditional load creates the page and freepage with the defined values?
I don't believe this is true with the SHRLEVEL CHANGE.

OR Will the following be better?

I thought about setting FREEPAGE/PCTFREE to 0 for the tablespace at the
outset (the indexes would have the appropriate values of FREEPAGE 15
PCTFREE 40.) This way the data pages at the end of the tablespace would
be filled and written with deferred writes thus avoiding all the
getpage/synchronous I/O and claim requests. But I'm not sure what would
happen with the concurrent loads all writing to the last pages at the
same time. What kind of contention would result? We're taking this
approach for massive programmatic insert jobs executing in parallel
since IBM recommended it. But is there really a difference? The load
functions as an insert for the LOAD RESUME YES SHRLEVEL CHANGE.

HELP!

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Debra Anne Kopecky

LOAD RESUME YES SHRLEVEL CHANGE
(in response to Debra Anne Kopecky)
OK change to the question I posted earlier.

>>>>>>> Change to this comment <<<<<<<<

I thought about setting FREEPAGE/PCTFREE to 0 for the tablespace at the
outset (the indexes would have the appropriate values.) This was the data
pages at the end of the tablespace would be filled and written with
deferred writes thus avoiding all the getpage/synchronous I/O and claim
requests. But I'm not sure what would happen with the concurrent loads all
writing to the last pages at the same time. I know we're taking this 0/0
approach for the eligibility loads but those are programmatic inserts.
But is there really a difference? The load functions as an insert in this
case.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>

If the loads are executed serially then this would work. I realized that
the approach of PCTFREE/FREEPAGE = 0 with parallel loads, was directed at
a partitioned tablespace with the input files sorted and divided into
partition key files. I relooked at PQ86037 and it also mentioned that
the tablespace for this APAR was defined as MEMBER CLUSTER; I have an ETR
into IBM asking if the PCTFREE/FREEPAGE = 0 technique will also skip
scanning the spacemap pages if the tablespace is NOT defined as MEMBER
CLUSTER (part of a data sharing environment.) I imagine the answer will
be yes.


Has anyone else run LOAD RESUME YES SHRLEVEL CHANGE using this technique in
a non-data sharing environment?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DB2 LOAD RESUME YES SHRLEVEL CHANGE
(in response to Debra Anne Kopecky)
Hi Debra

Some thoughts

I guess you are running these loads in parallel to get them done faster,
but as you say, they all have to be SHRLEVEL(CHANGE) otherwise they will
contend with each other.

If no other access is required to the data, have you timed a LOAD
SHRLEVEL(NONE) with no parallelism just to see if it is actually slower
- it might even be faster.

Alternatively, have you thought about arbitrarily partitioning the
table? If you can find a way to partition it such that the 5 load jobs
will ONLY be loading data into one (or a few) partitions each with NO
overlap where more than one job can load into the same partition, then
you can do parallel SHRLEVEL(NONE) loads which will definately be faster
than the SHRLEVEL(CHANGE) variety.

I know that these are not necessarily answers to your questions, but
they could be solutions to your problem!


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Debra Anne Kopecky
Sent: 29 April 2005 18:23
To: [login to unmask email]
Subject: [DB2-L] DB2 LOAD RESUME YES SHRLEVEL CHANGE

We are executing 5 parallel LOAD RESUME YES SHRLEVEL CHANGE jobs, each
loading 5 million rows. This occurs 5 times per month. The final
execution of the series of jobs ran for 40 hours elapsed and
171.63minutes CPU each.

We made several mistakes such as not executing a REORG on the data (only
on the indexes) after each of the load series completed, and not having
sufficient FREEPAGE (set at 31 for both data and indexes) and PCTFREE
(set at 15 both data & indexes) The tablespace is nonsegmented/not
partitioned. The table starts out empty and all data is loaded to the
table via the LOAD RESUME YES SHRLEVEL CHANGE jobs.

I have made freespace allocations for the new month's table at FREEPAGE
15 (for both data and indexes) and PCTFREE = 40 for the indexes and
PCTFREE = 30 for the data. And have a REORG scheduled after each series
of loads. What I need to know is this going to be sufficient? What does
DB2 do about the freespace settings when a new page is allocated? A
traditional load creates the page and freepage with the defined values?
I don't believe this is true with the SHRLEVEL CHANGE.

OR Will the following be better?

I thought about setting FREEPAGE/PCTFREE to 0 for the tablespace at the
outset (the indexes would have the appropriate values of FREEPAGE 15
PCTFREE 40.) This way the data pages at the end of the tablespace would
be filled and written with deferred writes thus avoiding all the
getpage/synchronous I/O and claim requests. But I'm not sure what would
happen with the concurrent loads all writing to the last pages at the
same time. What kind of contention would result? We're taking this
approach for massive programmatic insert jobs executing in parallel
since IBM recommended it. But is there really a difference? The load
functions as an insert for the LOAD RESUME YES SHRLEVEL CHANGE.

HELP!

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm