tablespace scan, but not table scan

[robert]

just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told
that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan.
hmmmm. next step up (or down, depending on your outlook) wasn't a
Table Scan.

thought this might be a MainFrame thing; but a look at DB2Info on my
UDB 7.2 server showed the same thing.

this is truly puzzling. is there an explanation of EXPLAIN which
describes how to get a Table Scan???? or are we stuck with a scan
of the TableSpace if there is no index support on the Table??? nothing
in between??

thanks,
robert

[Mark A]

"robert" <gn*****@rcn.com> wrote in message
news:da*************************@posting.google.co m...

just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told
that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan.
hmmmm. next step up (or down, depending on your outlook) wasn't a
Table Scan.

thought this might be a MainFrame thing; but a look at DB2Info on my
UDB 7.2 server showed the same thing.

this is truly puzzling. is there an explanation of EXPLAIN which
describes how to get a Table Scan???? or are we stuck with a scan
of the TableSpace if there is no index support on the Table??? nothing
in between??

thanks,
robert

If the OS/390 tablespace is simple or partitioned, DB2 will scan the entire
tablespace. For these, it is obviously best to have only one table per
tablespace.

If the tablespace is segmented, a tablespace scan will only scan those
segments belonging to the table in question (and not other tables in that
tablespace).

[Mark A]

"robert" <gn*****@rcn.com> wrote in message
news:da*************************@posting.google.co m...

just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told
that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan.
hmmmm. next step up (or down, depending on your outlook) wasn't a
Table Scan.

thought this might be a MainFrame thing; but a look at DB2Info on my
UDB 7.2 server showed the same thing.

this is truly puzzling. is there an explanation of EXPLAIN which
describes how to get a Table Scan???? or are we stuck with a scan
of the TableSpace if there is no index support on the Table??? nothing
in between??

thanks,
robert

If the OS/390 tablespace is simple or partitioned, DB2 will scan the entire
tablespace. For these, it is obviously best to have only one table per
tablespace.

If the tablespace is segmented, a tablespace scan will only scan those
segments belonging to the table in question (and not other tables in that
tablespace).

[robert]

"Mark A" <[e-mail]> wrote in message news:<mO****************@news.uswest.net>...

"robert" <gn*****@rcn.com> wrote in message
news:da*************************@posting.google.co m...

just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told
that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan.
hmmmm. next step up (or down, depending on your outlook) wasn't a
Table Scan.

thought this might be a MainFrame thing; but a look at DB2Info on my
UDB 7.2 server showed the same thing.

this is truly puzzling. is there an explanation of EXPLAIN which
describes how to get a Table Scan???? or are we stuck with a scan
of the TableSpace if there is no index support on the Table??? nothing
in between??

thanks,
robert

If the OS/390 tablespace is simple or partitioned, DB2 will scan the entire
tablespace. For these, it is obviously best to have only one table per
tablespace.

If the tablespace is segmented, a tablespace scan will only scan those
segments belonging to the table in question (and not other tables in that
tablespace).

getting a bit further afield: my understanding of the idea of tablespace
is to keep related tables together. the notion of replacing a table
as if it were a file isn't relational; it's VSAM or dBaseII. if a table
gets corrupted, one replaces the tablespace from which it came, thus
preserving not just the physical data, but the logical too; eg, orders
and order_lines in one tablespace.

the result of this (DB2) design is to severely penalize the DBA/designer
who doesn't provide indexes. i guess that's OK; but it seems to me just a
bit odd not to support a table scan when DB2 forces each table into an OS
file. it isn't as if DB2 didn't have each table's physical storage
(separate from any higher level storage object [aka tablespace])
readily to hand.

maybe Serge can illuminate??

robert

[robert]

"Mark A" <[e-mail]> wrote in message news:<mO****************@news.uswest.net>...

"robert" <gn*****@rcn.com> wrote in message
news:da*************************@posting.google.co m...

just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told
that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan.
hmmmm. next step up (or down, depending on your outlook) wasn't a
Table Scan.

thought this might be a MainFrame thing; but a look at DB2Info on my
UDB 7.2 server showed the same thing.

this is truly puzzling. is there an explanation of EXPLAIN which
describes how to get a Table Scan???? or are we stuck with a scan
of the TableSpace if there is no index support on the Table??? nothing
in between??

thanks,
robert

If the OS/390 tablespace is simple or partitioned, DB2 will scan the entire
tablespace. For these, it is obviously best to have only one table per
tablespace.

If the tablespace is segmented, a tablespace scan will only scan those
segments belonging to the table in question (and not other tables in that
tablespace).

getting a bit further afield: my understanding of the idea of tablespace
is to keep related tables together. the notion of replacing a table
as if it were a file isn't relational; it's VSAM or dBaseII. if a table
gets corrupted, one replaces the tablespace from which it came, thus
preserving not just the physical data, but the logical too; eg, orders
and order_lines in one tablespace.

the result of this (DB2) design is to severely penalize the DBA/designer
who doesn't provide indexes. i guess that's OK; but it seems to me just a
bit odd not to support a table scan when DB2 forces each table into an OS
file. it isn't as if DB2 didn't have each table's physical storage
(separate from any higher level storage object [aka tablespace])
readily to hand.

maybe Serge can illuminate??

robert

[Mark A]

> > If the OS/390 tablespace is simple or partitioned, DB2 will scan the
entire

tablespace. For these, it is obviously best to have only one table per
tablespace.

If the tablespace is segmented, a tablespace scan will only scan those
segments belonging to the table in question (and not other tables in that tablespace).

getting a bit further afield: my understanding of the idea of tablespace
is to keep related tables together. the notion of replacing a table
as if it were a file isn't relational; it's VSAM or dBaseII. if a table
gets corrupted, one replaces the tablespace from which it came, thus
preserving not just the physical data, but the logical too; eg, orders
and order_lines in one tablespace.

the result of this (DB2) design is to severely penalize the DBA/designer
who doesn't provide indexes. i guess that's OK; but it seems to me just a
bit odd not to support a table scan when DB2 forces each table into an OS
file. it isn't as if DB2 didn't have each table's physical storage
(separate from any higher level storage object [aka tablespace])
readily to hand.

maybe Serge can illuminate??

robert

Maybe we are confusing OS/390 with DB2 for Unix, Linux, and Windows. A
tablespace scan on DB2 for Unix, Linux, and Windows only scans the table.
The terminology is a bit confusing because tablespace scan originally came
from DB2 for OS/390 (MVS back then), and segmented tablespaces were not in
the initial implementation of DB2. So initially, a tablespace scan would
scan all the tables in the tables, but when segmented tablespaces were later
introduced for DB2 for OS/390, and for all tablespaces in DB2 for Unix,
Linux, and Windows, that is not true, even though the phrase tablespace scan
still survives.

Combining tables in a single tablespace has many purposes. These days it is
mostly a matter of administrative convenience. In most databases, the
relationships between tables (with declared or undeclared RI) are so complex
that not all related tables can be in the same tablespace.

So I think your conclusion that DB2 does not support table scans is
incorrect and is just a terminology problem. The only exception is a simple
or partitioned tablespace on OS/390, but any decent DBA should know to use
segmented tablespaces in this circumstance. In fact, most DBA's use
segmented tablespaces for everything (except for partitioned where it would
not be reasonable to have multiple tables defined in the same tablespace).

[Mark A]

> > If the OS/390 tablespace is simple or partitioned, DB2 will scan the
entire

tablespace. For these, it is obviously best to have only one table per
tablespace.

If the tablespace is segmented, a tablespace scan will only scan those
segments belonging to the table in question (and not other tables in that tablespace).

getting a bit further afield: my understanding of the idea of tablespace
is to keep related tables together. the notion of replacing a table
as if it were a file isn't relational; it's VSAM or dBaseII. if a table
gets corrupted, one replaces the tablespace from which it came, thus
preserving not just the physical data, but the logical too; eg, orders
and order_lines in one tablespace.

the result of this (DB2) design is to severely penalize the DBA/designer
who doesn't provide indexes. i guess that's OK; but it seems to me just a
bit odd not to support a table scan when DB2 forces each table into an OS
file. it isn't as if DB2 didn't have each table's physical storage
(separate from any higher level storage object [aka tablespace])
readily to hand.

maybe Serge can illuminate??

robert

Maybe we are confusing OS/390 with DB2 for Unix, Linux, and Windows. A
tablespace scan on DB2 for Unix, Linux, and Windows only scans the table.
The terminology is a bit confusing because tablespace scan originally came
from DB2 for OS/390 (MVS back then), and segmented tablespaces were not in
the initial implementation of DB2. So initially, a tablespace scan would
scan all the tables in the tables, but when segmented tablespaces were later
introduced for DB2 for OS/390, and for all tablespaces in DB2 for Unix,
Linux, and Windows, that is not true, even though the phrase tablespace scan
still survives.

Combining tables in a single tablespace has many purposes. These days it is
mostly a matter of administrative convenience. In most databases, the
relationships between tables (with declared or undeclared RI) are so complex
that not all related tables can be in the same tablespace.

So I think your conclusion that DB2 does not support table scans is
incorrect and is just a terminology problem. The only exception is a simple
or partitioned tablespace on OS/390, but any decent DBA should know to use
segmented tablespaces in this circumstance. In fact, most DBA's use
segmented tablespaces for everything (except for partitioned where it would
not be reasonable to have multiple tables defined in the same tablespace).