Large partitioned table issue

[steve.keanie]

Hi ... we're a mainframe V7 shop planning an imminent upgrade to V8. My
application team is converting an IMS DB into a DB/2 table ...
approximately 40GB of uncompressed (~20 GB compressed) data spread over
10 partitions. Approximately 35% of the data is "inactive" (easily
identifiable as such) and of limited (but some) use. We require 2
non-partitioning indexes on the data. The DBA group would like us to
put the "inactive" data into a separate table because of the sort
resource requirements to build the NPIs. Separating the data into two
tables would require application code to decide which table needs to be
accessed. Before agreeing to make application changes based on
operational requirements, I'd like some informed decisions as to
whether this should really be necessary and whether it will achieve
what the DBA group says it will.

Any takers?

Regards,
Steve Keanie
Ministry of Transport, Ontario

[Mark A]

<st**********@rogers.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...

Hi ... we're a mainframe V7 shop planning an imminent upgrade to V8. My
application team is converting an IMS DB into a DB/2 table ...
approximately 40GB of uncompressed (~20 GB compressed) data spread over
10 partitions. Approximately 35% of the data is "inactive" (easily
identifiable as such) and of limited (but some) use. We require 2
non-partitioning indexes on the data. The DBA group would like us to
put the "inactive" data into a separate table because of the sort
resource requirements to build the NPIs. Separating the data into two
tables would require application code to decide which table needs to be
accessed. Before agreeing to make application changes based on
operational requirements, I'd like some informed decisions as to
whether this should really be necessary and whether it will achieve
what the DBA group says it will.

Any takers?

Regards,
Steve Keanie
Ministry of Transport, Ontario

First of all, there is no product named DB/2. It is DB2. You must be an old
fart who remembers PS/2 and OS/2 and think that everything must end in /2
from IBM.

I would take a look at UNION ALL views, with constraints defined as to which
data goes into which table. If DB2 can identify where the data is located by
the constraint, it will only look at the table(s) necessary and ignore the
others. The UNION ALL view will shield the application from knowing which
tables to access.

[Serge Rielau]

st**********@rogers.com wrote:

Hi ... we're a mainframe V7 shop planning an imminent upgrade to V8. My
application team is converting an IMS DB into a DB/2 table ...
approximately 40GB of uncompressed (~20 GB compressed) data spread over
10 partitions. Approximately 35% of the data is "inactive" (easily
identifiable as such) and of limited (but some) use. We require 2
non-partitioning indexes on the data. The DBA group would like us to
put the "inactive" data into a separate table because of the sort
resource requirements to build the NPIs. Separating the data into two
tables would require application code to decide which table needs to be
accessed. Before agreeing to make application changes based on
operational requirements, I'd like some informed decisions as to
whether this should really be necessary and whether it will achieve
what the DBA group says it will.

Just to clarify you are talking about DB2 V8 for zOS, right?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[P. Adhia]

st**********@rogers.com wrote:

Hi ... we're a mainframe V7 shop planning an imminent upgrade to V8.

Are your NPIs unique? If not and if you are upgrading to V8, why not
use DPSI? That eliminates most of the problmes associated with NPIs.

P Adhia

[P. Adhia]

Mark A wrote:

I would take a look at UNION ALL views, with constraints defined as to which

UNION ALL views are not updatable in z/OS DB2. So if you can isolate
coding changes that update/insert/delete to use the base tables and use
views for all queries, this would work. Also, if you design this in V7,
then have your system programmers to verify that they have applied
APARs that enable query pruning (equality predicates only though)

Regards

P Adhia

[steve.keanie]

>

First of all, there is no product named DB/2. It is DB2. You must be an old
fart who remembers PS/2 and OS/2 and think that everything must end in /2
from IBM.

Actually, I'm a vintage fart with a somewhat nutty bouquet and a hint
of cheesiness, but I thought it was called DB/2 because it aspired to
one day perform half as well as IMS :-)

>
I would take a look at UNION ALL views, with constraints defined as to which
data goes into which table. If DB2 can identify where the data is located by
the constraint, it will only look at the table(s) necessary and ignore the
others. The UNION ALL view will shield the application from knowing which
tables to access.

[steve.keanie]

Serge Rielau wrote:

Just to clarify you are talking about DB2 V8 for zOS, right?

Yes Serge, that is correct.

[Serge Rielau]

st**********@rogers.com wrote:

Serge Rielau wrote:

>Just to clarify you are talking about DB2 V8 for zOS, right?

Yes Serge, that is correct.

OK, now keeping in mind that I'm ignorant to how the storage model works
on zOS, couldn't you just use a range partitioned table to achieve what
you want?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Mark A]

<st**********@rogers.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Actually, I'm a vintage fart with a somewhat nutty bouquet and a hint
of cheesiness, but I thought it was called DB/2 because it aspired to
one day perform half as well as IMS :-)

IMS was IBM's first database and Database 2 was IBM's second. Later the name
was officially shortened to DB2.

While it is true that IMS can perform faster than DB2 in certain situations
(although not as many as you think), many DB2 schema changes can be made
while the system is running in production (even changes to existing tables),
and users and programmers can access the data without a extensive knowledge
of programming or DL/I.

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:4l***********@individual.net...

OK, now keeping in mind that I'm ignorant to how the storage model works
on zOS, couldn't you just use a range partitioned table to achieve what
you want?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

In the OP, he said that:

"The DBA group would like us to put the "inactive" data into a separate
table because of the sort resource requirements to build the NPIs."

I don't know if that is a legitimate concern, but it was a constraint that I
was operating under when I suggested UNION ALL views.

[steve.keanie]

Mark A wrote:

While it is true that IMS can perform faster than DB2 in certain situations
(although not as many as you think), many DB2 schema changes can be made
while the system is running in production (even changes to existing tables),
and users and programmers can access the data without a extensive knowledge
of programming or DL/I.

Gee, I didn't realize DB2 could do all that ... is it, like, relational
too? Thanks for setting me straight, sonny! ;-)

[Mark A]

<st**********@rogers.comwrote in message
news:11*********************@75g2000cwc.googlegrou ps.com...

>
Gee, I didn't realize DB2 could do all that ... is it, like, relational
too? Thanks for setting me straight, sonny! ;-)

You are welcome grandpa.