Read-only table design advice wanted

Raymond Bell

Read-only table design advice wanted
Colleagues,

We have a relatively simple design issue I'd like some comments on. We have
a number of solutions to what we want to achieve, all with pros and cons,
and I'd appreciate some independent advice. Environment: OS/390 2.8, DB2
for OS/390 V5.

We run multiple application environments within a given DB2 subsystem, each
with different qualifiers for their objects. Some of the tables involved
are read-only and each environment has its own copy of these tables. Some
of the rows in these read-only tables are common across the environments,
some are unique to that environment. These tables are large-ish, so we want
to create one central copy of the read-only tables containing all rows from
all environments, create environment-specific views on these tables and drop
the original read-only tables. The main driver for this is saving space.

The tables are subsets of Production data. Occasionally this information is
added to from Production (via the load utility), based on users' data
requests. Also, occasionally old data is purged from the tables once they
pass a certain date, and that environment no longer requires the rows
(identified by keys in an environment-specific reference table).

So, to kinda summarise: multiple environments sharing read-only data. New
read-only data added occasionally, and is purged occasionally based on date
and absence of the key from a controlling table.

We're initially thinking of two solutions: let's say A and B. Solution A is
to create a partitioned table(space), load the new extract data into
different partitions as it comes, and purge occasionally by date and key.
Solution B is to create multiple tables in their own tablespaces, load the
new extract data into these, and again purge occasionally. Both options
would require views to be created, one per environment sharing the tables,
that showed the rows each was interested in.

Re: loading the data. We like separate partitions/tables to make adding the
data easy; simply load resume. Trouble is, data is loaded at the back,
while the purge purges from all over the place. With A we could load a new
partition, except we don't have anything useful to partition on, and don't
want to have to change the data extract process to create an artificial
partitioning key. Also, program access to the data wouldn't provide the
partitioning key, so response would be awful. Each environment's view on
the table would, however, be dead simple.

With B we could load a separate table for each extract. However the view
would, I think, be a nightmare. No unknown partitioning key to worry about,
but each environment's view would be an n-way union, where n is the number
of tables we create for each extract's data. Something like 'select * from
t1 union select * from t2 union.... select * from t10'. Again, quite ugly.

The DASD we stand to save is significant, and will increase as other
environments in other subsystems merge into one subsystem and can share
these read-only tables too (sorry, no data sharing here). Both of the
answers I can think of have a major problem. I'd be interested if any of
you out there can a) be bothered to read all this ;o) and b) suggest
alternatives or enhancements that might do the trick. Personally I like the
multi-table approach, but that 10-way union scares me. I also like
partitioning, but not a query on it without the first partitioning key.

All advice gratefully received.


Raymond Bell
Database Administrator and Electron Inconveniencer
PS. Sorry about the essay...



Edward Vassie

Re: Read-only table design advice wanted
(in response to Raymond Bell)
My suggestions are along the lines of: keep it simple.

I would be very reluctant to have more than one table in a tablespace,
unless forced by external reasons (e.g. buying a DBA-friendly ERP package).
This is because table and tablespace maintenance is (I find) easier and less
error-prone. If the production table/tablespace setup is mirrored in
development, as it should be!, then multiple tablespaces in a tablespace is
an accident waiting to happen, often soon after new staff are hired. Even
production DBAs have been known to get their LOAD control statements wrong.

Therefore, strongly consider a 1-to-1 match between tables and tablespaces.

Partitioning is another issue. I think there are two reasons to consider
partitioning:
a) For performance - DB2 parallelism works better with partitions
b) For management - Keeping your object size down also means you need less
free-space when you reorganise. Also backups and recoveries of individual
partitions are often quicker than for a single larger segmented tablespace.

When choosing your partitioning key, it is normally best to look at your
application query requirements. Try to choose the key that will give the
best performance to the largest number of performance-critical programs. If
you need a unique index, and the unique index is not a good choice for
performance, then create a NPI for the unique index. Making the primary key
of a table the partitioning key, then using a NPI for the majority of your
read access is normally a very good way to get poor performance.

Therefore, partition if you can justify it, otherwise use a segmented
tablespace (SEGSIZE 64 if over 2Mb in size). If you partition, then choose
a partitioning key that gives best read performance.

From Edward Vassie...


-----Original Message-----
From: Bell, Raymond W [mailto:[login to unmask email]
Sent: 08 January 2001 01:04
To: [login to unmask email]
Subject: Read-only table design advice wanted


Colleagues,

We have a relatively simple design issue I'd like some comments on. We have
a number of solutions to what we want to achieve, all with pros and cons,
and I'd appreciate some independent advice. Environment: OS/390 2.8, DB2
for OS/390 V5.

We run multiple application environments within a given DB2 subsystem, each
with different qualifiers for their objects. Some of the tables involved
are read-only and each environment has its own copy of these tables. Some
of the rows in these read-only tables are common across the environments,
some are unique to that environment. These tables are large-ish, so we want
to create one central copy of the read-only tables containing all rows from
all environments, create environment-specific views on these tables and drop
the original read-only tables. The main driver for this is saving space.

The tables are subsets of Production data. Occasionally this information is
added to from Production (via the load utility), based on users' data
requests. Also, occasionally old data is purged from the tables once they
pass a certain date, and that environment no longer requires the rows
(identified by keys in an environment-specific reference table).

So, to kinda summarise: multiple environments sharing read-only data. New
read-only data added occasionally, and is purged occasionally based on date
and absence of the key from a controlling table.

We're initially thinking of two solutions: let's say A and B. Solution A is
to create a partitioned table(space), load the new extract data into
different partitions as it comes, and purge occasionally by date and key.
Solution B is to create multiple tables in their own tablespaces, load the
new extract data into these, and again purge occasionally. Both options
would require views to be created, one per environment sharing the tables,
that showed the rows each was interested in.

Re: loading the data. We like separate partitions/tables to make adding the
data easy; simply load resume. Trouble is, data is loaded at the back,
while the purge purges from all over the place. With A we could load a new
partition, except we don't have anything useful to partition on, and don't
want to have to change the data extract process to create an artificial
partitioning key. Also, program access to the data wouldn't provide the
partitioning key, so response would be awful. Each environment's view on
the table would, however, be dead simple.

With B we could load a separate table for each extract. However the view
would, I think, be a nightmare. No unknown partitioning key to worry about,
but each environment's view would be an n-way union, where n is the number
of tables we create for each extract's data. Something like 'select * from
t1 union select * from t2 union.... select * from t10'. Again, quite ugly.

The DASD we stand to save is significant, and will increase as other
environments in other subsystems merge into one subsystem and can share
these read-only tables too (sorry, no data sharing here). Both of the
answers I can think of have a major problem. I'd be interested if any of
you out there can a) be bothered to read all this ;o) and b) suggest
alternatives or enhancements that might do the trick. Personally I like the
multi-table approach, but that 10-way union scares me. I also like
partitioning, but not a query on it without the first partitioning key.

All advice gratefully received.


Raymond Bell
Database Administrator and Electron Inconveniencer
PS. Sorry about the essay...








Michael McMorrow

Re: Read-only table design advice wanted
(in response to Edward Vassie)
Hi Raymond,
Would you consider defining 2 partitions with a toggle strategy dynamically
deeming one to be 'active' and one to be 'inactive'. Updates would be
applied to the 'inactive' partition and, at a convenient time of your
choice, this partition would be flipped over to 'active' by updating a
control table. Application access would be via a view which referenced this
control table for the key value of the currently active partition.
Not a million miles away from the IMS approach to acblibs/mfslibs etc.
Michael.


> From: Bell, Raymond W <[login to unmask email]>
> To: [login to unmask email]
> Subject: Read-only table design advice wanted
> Date: Monday, January 08, 2001 1:03 AM
>
> Colleagues,
>
> We have a relatively simple design issue I'd like some comments on. We
have
> a number of solutions to what we want to achieve, all with pros and cons,
> and I'd appreciate some independent advice. Environment: OS/390 2.8, DB2
> for OS/390 V5.
>
> We run multiple application environments within a given DB2 subsystem,
each
> with different qualifiers for their objects. Some of the tables involved
> are read-only and each environment has its own copy of these tables.
Some
> of the rows in these read-only tables are common across the environments,
> some are unique to that environment. These tables are large-ish, so we
want
> to create one central copy of the read-only tables containing all rows
from
> all environments, create environment-specific views on these tables and
drop
> the original read-only tables. The main driver for this is saving space.
>
> The tables are subsets of Production data. Occasionally this information
is
> added to from Production (via the load utility), based on users' data
> requests. Also, occasionally old data is purged from the tables once
they
> pass a certain date, and that environment no longer requires the rows
> (identified by keys in an environment-specific reference table).
>
> So, to kinda summarise: multiple environments sharing read-only data.
New
> read-only data added occasionally, and is purged occasionally based on
date
> and absence of the key from a controlling table.
>
> We're initially thinking of two solutions: let's say A and B. Solution A
is
> to create a partitioned table(space), load the new extract data into
> different partitions as it comes, and purge occasionally by date and key.
> Solution B is to create multiple tables in their own tablespaces, load
the
> new extract data into these, and again purge occasionally. Both options
> would require views to be created, one per environment sharing the
tables,
> that showed the rows each was interested in.
>
> Re: loading the data. We like separate partitions/tables to make adding
the
> data easy; simply load resume. Trouble is, data is loaded at the back,
> while the purge purges from all over the place. With A we could load a
new
> partition, except we don't have anything useful to partition on, and
don't
> want to have to change the data extract process to create an artificial
> partitioning key. Also, program access to the data wouldn't provide the
> partitioning key, so response would be awful. Each environment's view on
> the table would, however, be dead simple.
>
> With B we could load a separate table for each extract. However the view
> would, I think, be a nightmare. No unknown partitioning key to worry
about,
> but each environment's view would be an n-way union, where n is the
number
> of tables we create for each extract's data. Something like 'select *
from
> t1 union select * from t2 union.... select * from t10'. Again, quite
ugly.
>
> The DASD we stand to save is significant, and will increase as other
> environments in other subsystems merge into one subsystem and can share
> these read-only tables too (sorry, no data sharing here). Both of the
> answers I can think of have a major problem. I'd be interested if any of
> you out there can a) be bothered to read all this ;o) and b) suggest
> alternatives or enhancements that might do the trick. Personally I like
the
> multi-table approach, but that 10-way union scares me. I also like
> partitioning, but not a query on it without the first partitioning key.
>
> All advice gratefully received.
>
>
> Raymond Bell
> Database Administrator and Electron Inconveniencer
> PS. Sorry about the essay...
>
>
>





**********************************************************************
This document is strictly confidential and is intended for use by
the addressee unless otherwise indicated.

Allied Irish Banks
**********************************************************************



Glenn Mackey

Re: Read-only table design advice wanted
(in response to Michael McMorrow)
If this concept is an option, consider doing the switching via a table
RENAME.


-----Original Message-----
From: Michael McMorrow [mailto:[login to unmask email]
Sent: Monday, January 08, 2001 8:21 AM
To: [login to unmask email]
Subject: Re: Read-only table design advice wanted


Hi Raymond,
Would you consider defining 2 partitions with a toggle strategy dynamically
deeming one to be 'active' and one to be 'inactive'. Updates would be
applied to the 'inactive' partition and, at a convenient time of your
choice, this partition would be flipped over to 'active' by updating a
control table. Application access would be via a view which referenced this
control table for the key value of the currently active partition.
Not a million miles away from the IMS approach to acblibs/mfslibs etc.
Michael.


> From: Bell, Raymond W <[login to unmask email]>
> To: [login to unmask email]
> Subject: Read-only table design advice wanted
> Date: Monday, January 08, 2001 1:03 AM
>
> Colleagues,
>
> We have a relatively simple design issue I'd like some comments on. We
have
> a number of solutions to what we want to achieve, all with pros and cons,
> and I'd appreciate some independent advice. Environment: OS/390 2.8, DB2
> for OS/390 V5.
>
> We run multiple application environments within a given DB2 subsystem,
each
> with different qualifiers for their objects. Some of the tables involved
> are read-only and each environment has its own copy of these tables.
Some
> of the rows in these read-only tables are common across the environments,
> some are unique to that environment. These tables are large-ish, so we
want
> to create one central copy of the read-only tables containing all rows
from
> all environments, create environment-specific views on these tables and
drop
> the original read-only tables. The main driver for this is saving space.
>
> The tables are subsets of Production data. Occasionally this information
is
> added to from Production (via the load utility), based on users' data
> requests. Also, occasionally old data is purged from the tables once
they
> pass a certain date, and that environment no longer requires the rows
> (identified by keys in an environment-specific reference table).
>
> So, to kinda summarise: multiple environments sharing read-only data.
New
> read-only data added occasionally, and is purged occasionally based on
date
> and absence of the key from a controlling table.
>
> We're initially thinking of two solutions: let's say A and B. Solution A
is
> to create a partitioned table(space), load the new extract data into
> different partitions as it comes, and purge occasionally by date and key.
> Solution B is to create multiple tables in their own tablespaces, load
the
> new extract data into these, and again purge occasionally. Both options
> would require views to be created, one per environment sharing the
tables,
> that showed the rows each was interested in.
>
> Re: loading the data. We like separate partitions/tables to make adding
the
> data easy; simply load resume. Trouble is, data is loaded at the back,
> while the purge purges from all over the place. With A we could load a
new
> partition, except we don't have anything useful to partition on, and
don't
> want to have to change the data extract process to create an artificial
> partitioning key. Also, program access to the data wouldn't provide the
> partitioning key, so response would be awful. Each environment's view on
> the table would, however, be dead simple.
>
> With B we could load a separate table for each extract. However the view
> would, I think, be a nightmare. No unknown partitioning key to worry
about,
> but each environment's view would be an n-way union, where n is the
number
> of tables we create for each extract's data. Something like 'select *
from
> t1 union select * from t2 union.... select * from t10'. Again, quite
ugly.
>
> The DASD we stand to save is significant, and will increase as other
> environments in other subsystems merge into one subsystem and can share
> these read-only tables too (sorry, no data sharing here). Both of the
> answers I can think of have a major problem. I'd be interested if any of
> you out there can a) be bothered to read all this ;o) and b) suggest
> alternatives or enhancements that might do the trick. Personally I like
the
> multi-table approach, but that 10-way union scares me. I also like
> partitioning, but not a query on it without the first partitioning key.
>
> All advice gratefully received.
>
>
> Raymond Bell
> Database Administrator and Electron Inconveniencer
> PS. Sorry about the essay...
>
>
>





**********************************************************************
This document is strictly confidential and is intended for use by
the addressee unless otherwise indicated.

Allied Irish Banks
**********************************************************************








John Hardy

Re: Read-only table design advice wanted
(in response to Glenn Mackey)
Please correct me if I'm wrong but..

Raymond's requirement is to save DASD, and I think that you are advocating
a strategy based upon data redundancy. Am I missing something?


>-----Original Message-----
>From: Michael McMorrow [mailto:[login to unmask email]
>Sent: Monday, January 08, 2001 8:21 AM
>To: [login to unmask email]
>Subject: Re: Read-only table design advice wanted
>
>
>Hi Raymond,
>Would you consider defining 2 partitions with a toggle strategy dynamically
>deeming one to be 'active' and one to be 'inactive'. Updates would be
>applied to the 'inactive' partition and, at a convenient time of your
>choice, this partition would be flipped over to 'active' by updating a
>control table. Application access would be via a view which referenced this
>control table for the key value of the currently active partition.
>Not a million miles away from the IMS approach to acblibs/mfslibs etc.
>Michael.
>
>
>> From: Bell, Raymond W <[login to unmask email]>
>> To: [login to unmask email]
>> Subject: Read-only table design advice wanted
>> Date: Monday, January 08, 2001 1:03 AM
>>
>> Colleagues,
>>
>> We have a relatively simple design issue I'd like some comments on. We
>have
>> a number of solutions to what we want to achieve, all with pros and cons,
>> and I'd appreciate some independent advice. Environment: OS/390 2.8, DB2
>> for OS/390 V5.
>>
>> We run multiple application environments within a given DB2 subsystem,
>each
>> with different qualifiers for their objects. Some of the tables involved
>> are read-only and each environment has its own copy of these tables.
>Some
>> of the rows in these read-only tables are common across the environments,
>> some are unique to that environment. These tables are large-ish, so we
>want
>> to create one central copy of the read-only tables containing all rows
>from
>> all environments, create environment-specific views on these tables and
>drop
>> the original read-only tables. The main driver for this is saving space.
>>
>> The tables are subsets of Production data. Occasionally this information
>is
>> added to from Production (via the load utility), based on users' data
>> requests. Also, occasionally old data is purged from the tables once
>they
>> pass a certain date, and that environment no longer requires the rows
>> (identified by keys in an environment-specific reference table).
>>
>> So, to kinda summarise: multiple environments sharing read-only data.
>New
>> read-only data added occasionally, and is purged occasionally based on
>date
>> and absence of the key from a controlling table.
>>
>> We're initially thinking of two solutions: let's say A and B. Solution A
>is
>> to create a partitioned table(space), load the new extract data into
>> different partitions as it comes, and purge occasionally by date and key.
>> Solution B is to create multiple tables in their own tablespaces, load
>the
>> new extract data into these, and again purge occasionally. Both options
>> would require views to be created, one per environment sharing the
>tables,
>> that showed the rows each was interested in.
>>
>> Re: loading the data. We like separate partitions/tables to make adding
>the
>> data easy; simply load resume. Trouble is, data is loaded at the back,
>> while the purge purges from all over the place. With A we could load a
>new
>> partition, except we don't have anything useful to partition on, and
>don't
>> want to have to change the data extract process to create an artificial
>> partitioning key. Also, program access to the data wouldn't provide the
>> partitioning key, so response would be awful. Each environment's view on
>> the table would, however, be dead simple.
>>
>> With B we could load a separate table for each extract. However the view
>> would, I think, be a nightmare. No unknown partitioning key to worry
>about,
>> but each environment's view would be an n-way union, where n is the
>number
>> of tables we create for each extract's data. Something like 'select *
>from
>> t1 union select * from t2 union.... select * from t10'. Again, quite
>ugly.
>>
>> The DASD we stand to save is significant, and will increase as other
>> environments in other subsystems merge into one subsystem and can share
>> these read-only tables too (sorry, no data sharing here). Both of the
>> answers I can think of have a major problem. I'd be interested if any of
>> you out there can a) be bothered to read all this ;o) and b) suggest
>> alternatives or enhancements that might do the trick. Personally I like
>the
>> multi-table approach, but that 10-way union scares me. I also like
>> partitioning, but not a query on it without the first partitioning key.
>>
>> All advice gratefully received.
>>
>>
>> Raymond Bell
>> Database Administrator and Electron Inconveniencer
>> PS. Sorry about the essay...
>>
>>
>>
>
>
>
>
>
>**********************************************************************
>This document is strictly confidential and is intended for use by
> the addressee unless otherwise indicated.
>
>Allied Irish Banks
>**********************************************************************
>
>
>
the
>
>
>
>
>





Michael McMorrow

Re: Read-only table design advice wanted
(in response to John Hardy)
....Yup, that's true but only duplication (not triplication /
quadruplication / whatever multiplication he has at the moment)....M.

----------
> From: John Hardy <[login to unmask email]>
> To: [login to unmask email]
> Subject: Re: Read-only table design advice wanted
> Date: Monday, January 08, 2001 3:04 PM
>
> Please correct me if I'm wrong but..
>
> Raymond's requirement is to save DASD, and I think that you are
advocating
> a strategy based upon data redundancy. Am I missing something?
>
>
> >-----Original Message-----
> >From: Michael McMorrow [mailto:[login to unmask email]
> >Sent: Monday, January 08, 2001 8:21 AM
> >To: [login to unmask email]
> >Subject: Re: Read-only table design advice wanted
> >
> >
> >Hi Raymond,
> >Would you consider defining 2 partitions with a toggle strategy
dynamically
> >deeming one to be 'active' and one to be 'inactive'. Updates would be
> >applied to the 'inactive' partition and, at a convenient time of your
> >choice, this partition would be flipped over to 'active' by updating a
> >control table. Application access would be via a view which referenced
this
> >control table for the key value of the currently active partition.
> >Not a million miles away from the IMS approach to acblibs/mfslibs etc.
> >Michael.
> >
> >
> >> From: Bell, Raymond W <[login to unmask email]>
> >> To: [login to unmask email]
> >> Subject: Read-only table design advice wanted
> >> Date: Monday, January 08, 2001 1:03 AM
> >>
> >> Colleagues,
> >>
> >> We have a relatively simple design issue I'd like some comments on.
We
> >have
> >> a number of solutions to what we want to achieve, all with pros and
cons,
> >> and I'd appreciate some independent advice. Environment: OS/390 2.8,
DB2
> >> for OS/390 V5.
> >>
> >> We run multiple application environments within a given DB2 subsystem,
> >each
> >> with different qualifiers for their objects. Some of the tables
involved
> >> are read-only and each environment has its own copy of these tables.
> >Some
> >> of the rows in these read-only tables are common across the
environments,
> >> some are unique to that environment. These tables are large-ish, so
we
> >want
> >> to create one central copy of the read-only tables containing all rows
> >from
> >> all environments, create environment-specific views on these tables
and
> >drop
> >> the original read-only tables. The main driver for this is saving
space.
> >>
> >> The tables are subsets of Production data. Occasionally this
information
> >is
> >> added to from Production (via the load utility), based on users' data
> >> requests. Also, occasionally old data is purged from the tables once
> >they
> >> pass a certain date, and that environment no longer requires the rows
> >> (identified by keys in an environment-specific reference table).
> >>
> >> So, to kinda summarise: multiple environments sharing read-only data.
> >New
> >> read-only data added occasionally, and is purged occasionally based on
> >date
> >> and absence of the key from a controlling table.
> >>
> >> We're initially thinking of two solutions: let's say A and B.
Solution A
> >is
> >> to create a partitioned table(space), load the new extract data into
> >> different partitions as it comes, and purge occasionally by date and
key.
> >> Solution B is to create multiple tables in their own tablespaces, load
> >the
> >> new extract data into these, and again purge occasionally. Both
options
> >> would require views to be created, one per environment sharing the
> >tables,
> >> that showed the rows each was interested in.
> >>
> >> Re: loading the data. We like separate partitions/tables to make
adding
> >the
> >> data easy; simply load resume. Trouble is, data is loaded at the
back,
> >> while the purge purges from all over the place. With A we could load
a
> >new
> >> partition, except we don't have anything useful to partition on, and
> >don't
> >> want to have to change the data extract process to create an
artificial
> >> partitioning key. Also, program access to the data wouldn't provide
the
> >> partitioning key, so response would be awful. Each environment's view
on
> >> the table would, however, be dead simple.
> >>
> >> With B we could load a separate table for each extract. However the
view
> >> would, I think, be a nightmare. No unknown partitioning key to worry
> >about,
> >> but each environment's view would be an n-way union, where n is the
> >number
> >> of tables we create for each extract's data. Something like 'select *
> >from
> >> t1 union select * from t2 union.... select * from t10'. Again, quite
> >ugly.
> >>
> >> The DASD we stand to save is significant, and will increase as other
> >> environments in other subsystems merge into one subsystem and can
share
> >> these read-only tables too (sorry, no data sharing here). Both of the
> >> answers I can think of have a major problem. I'd be interested if any
of
> >> you out there can a) be bothered to read all this ;o) and b) suggest
> >> alternatives or enhancements that might do the trick. Personally I
like
> >the
> >> multi-table approach, but that 10-way union scares me. I also like
> >> partitioning, but not a query on it without the first partitioning
key.
> >>
> >> All advice gratefully received.
> >>
> >>
> >> Raymond Bell
> >> Database Administrator and Electron Inconveniencer
> >> PS. Sorry about the essay...
> >>
> >>
> >>
visit
> >the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can
> >
> >
> >
> >
> >**********************************************************************
> >This document is strictly confidential and is intended for use by
> > the addressee unless otherwise indicated.
> >
> >Allied Irish Banks
> >**********************************************************************
> >
> >
> >
> the
> >DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can
be
> >
> >
> >
> >
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can
>
>
>
>





**********************************************************************
This document is strictly confidential and is intended for use by
the addressee unless otherwise indicated.

Allied Irish Banks
**********************************************************************



Roger Miller

Re: Read-only table design advice wanted
(in response to Michael McMorrow)
Here are a couple of thoughts.

You'll get better flexibility and concurrency with separate tables. If the
tables have many non-partitioning indexes, then then need to separate into
multiple tables increases and vice-versa.

The UNION processing is more expensive, and the key question is how much of
the processing will need it. We did provide a couple of improvements in V7,
with the ability to put the UNION in a view and the ability to LOAD with a
technique that is more like INSERT.

Can you use REORG DISCARD for the purge run? You remembered to set the
PCTFREE and FREEPAGE to 0 on the data and on indexes that will be inserted
only at the end. Other indexes still need the insert space.

Roger Miller, DB2 for OS/390



Raymond Bell

Re: Read-only table design advice wanted
(in response to Roger Miller)
Greetings Ed(ward), Michael, Glen, Marc, John, Roger, etc.,

Thanks for all your comments, although only Roger was really able to provide
insight, albeit generically.

Ed, I was never going to have multi-table tablespaces; either partitioned
(somehow) or separate tables with separate tablespaces.

Mike/Glen, I don't want to toggle partitions (or tables) as, although I like
the idea, it would involve artificially creating a partitioning key which a)
we're not really in a position to do, and b) wouldn't be able to be provided
by the application. Actually, you've just made me start thinking about
something I'll have to investigate... Anyway, I won't be renaming either
'cause I'm not really after a 'toggle' solution. And yes, John, I would end
up having more DASD than with just one set of copies, but as Mike said it
would be better than the multiple copies I've got now (but still one more
than I want). I'm assuming this 'toggle' answer came up as a way to ease
the update process, by the way.

Marc, shared read-only isn't a goer because, as I should have said, we're
going to V6 over the next few months.

Roger, separate tables would indeed provide the most flexibility and
concurrency. However you pointed out something I'd forgotten; that a view
can't have a union until V7, which is what I'd need to union all my tables
together to provide one view of them all. I don't think some full outer
join magic would help either. Good lead on the 'reorg discards' idea; will
look into that. Some of the tables have >1 index, most don't. Those whose
keys would be continuously ascending are indeed freepage 0 pctfree 0.

What I was going to try (thanks for prompting the idea, Mike) is to
partition with 'fake' partitioning keys and load each production extract
into a new partition, specifying a literal - not in the file - for the
partitioning key value. Good idea, except you can't do it. The value must
be in the file, and I can't change the file.

I think what we're going to do is partition on number ranges and constantly
load resume into the whole tablespace. We'll let DB2 work out what records
go in what partitions. We'll run our mainly-date-based purge program
occasionally and reorg by partition shortly thereafter (or possibly just
reorg/discard). It's not ideal - loading at the back of each partition,
freeing space from throughout - but view access is simple and it seems to be
the least offfensive of the options.

Thanks again, everyone.


Raymond