Can You Put Tables in USERSPACE1 With Indexes In INDEXSPACE1 ?

[Data Goob]

Hi,

I thought we would be able to have indexes in a
separate tablespace from the tables in another
tablespace. So I went and created a INDEXSPACE1,
only to not see the syntax on how to create index
.... in "INDEXSPACE1" . We have 6 nodes on 3
physical servers ( 2 nodes per server ), each
server is a 4-CPU machine.

I cannot find DB2 documentation to support this idea,
is this possible in DB2? ( Separate the indexes from
the tables ).

We're running 8.1 with Fixpack 5 on SLES 8 Linux.

Thanks in advance!

[Mark A]

"Data Goob" <da******@hotmail.com> wrote in message
news:vy*******************@fe06.usenetserver.com.. .

Hi,

I thought we would be able to have indexes in a
separate tablespace from the tables in another
tablespace. So I went and created a INDEXSPACE1,
only to not see the syntax on how to create index
... in "INDEXSPACE1" . We have 6 nodes on 3
physical servers ( 2 nodes per server ), each
server is a 4-CPU machine.

I cannot find DB2 documentation to support this idea,
is this possible in DB2? ( Separate the indexes from
the tables ).

We're running 8.1 with Fixpack 5 on SLES 8 Linux.

Thanks in advance!

First create the tablespace for the index(s) you want. Then when you create
the table, specify the tablespace for the data, and the tablespace to be
used for any indexes on that table. Obviously if the table is already
created, you will have to drop and recreate it (I don't think you can alter
this table attribute).

[Data Goob]

Thanks Mark.

It's a bit un-intuitive and backward to do it this
way, but I guess that's the way it works.
"Mark A" <[e-mail]> wrote in message news:Se****************@news.uswest.net...

"Data Goob" <da******@hotmail.com> wrote in message
news:vy*******************@fe06.usenetserver.com.. .

Hi,

I thought we would be able to have indexes in a
separate tablespace from the tables in another
tablespace. So I went and created a INDEXSPACE1,
only to not see the syntax on how to create index
... in "INDEXSPACE1" . We have 6 nodes on 3
physical servers ( 2 nodes per server ), each
server is a 4-CPU machine.

I cannot find DB2 documentation to support this idea,
is this possible in DB2? ( Separate the indexes from
the tables ).

We're running 8.1 with Fixpack 5 on SLES 8 Linux.

Thanks in advance!

First create the tablespace for the index(s) you want. Then when you create
the table, specify the tablespace for the data, and the tablespace to be
used for any indexes on that table. Obviously if the table is already
created, you will have to drop and recreate it (I don't think you can alter
this table attribute).

[Ian]

Data Goob wrote:

Hi,

I thought we would be able to have indexes in a
separate tablespace from the tables in another
tablespace. So I went and created a INDEXSPACE1,
only to not see the syntax on how to create index
.... in "INDEXSPACE1" . We have 6 nodes on 3
physical servers ( 2 nodes per server ), each
server is a 4-CPU machine.

I cannot find DB2 documentation to support this idea,
is this possible in DB2? ( Separate the indexes from
the tables ).

Also, be aware the both tablespaces must be DMS in order
to do this. You can't separate data/index when using SMS
tablespaces.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Mark A]

"Data Goob" <da******@hotmail.com> wrote in message
news:If******************@fe10.usenetserver.com...

Thanks Mark.

It's a bit un-intuitive and backward to do it this
way, but I guess that's the way it works.

It is more straightforward on DB2 for z/OS (mainframe) where the indexspace
is defined with the create index statement.

But that is partly because the original DB2 UDB (OS/2 Database Manager) did
not allow separate tablespaces for indexes. But on DB2 for Unix, Linux, and
Windows, even if the data and indexes are in the same tablespace, they are
not in the same file.

This is different than DB2 for z/OS where a tablespace or indexspace is a
single VSAM dataset (unless the underlying VSAM dataset has gone into
extents).

[Data Goob]

Thanks for the background.

I'll make sure in the future to specify the index space upfront
when creating tables.

By the way, I have heard that we should create one server partition
for each CPU for performance. As I mentioned before we have 3 physical
servers, 4-CPUs each. We're on DB2 EEE. Today they have 2 partitions
each, for a total of 6 partitions. Should we move to 4 partitions per
server? Can I re-partition on an existing database and cluster?

Thanks again to you and the others for great background and help!

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

"Data Goob" <da******@hotmail.com> wrote in message
news:If******************@fe10.usenetserver.com...

Thanks Mark.

It's a bit un-intuitive and backward to do it this
way, but I guess that's the way it works.

It is more straightforward on DB2 for z/OS (mainframe) where the indexspace
is defined with the create index statement.

But that is partly because the original DB2 UDB (OS/2 Database Manager) did
not allow separate tablespaces for indexes. But on DB2 for Unix, Linux, and
Windows, even if the data and indexes are in the same tablespace, they are
not in the same file.

This is different than DB2 for z/OS where a tablespace or indexspace is a
single VSAM dataset (unless the underlying VSAM dataset has gone into
extents).

[Mark A]

"Data Goob" <da******@hotmail.com> wrote in message
news:pO*******************@fe33.usenetserver.com.. .

Thanks for the background.

I'll make sure in the future to specify the index space upfront
when creating tables.

By the way, I have heard that we should create one server partition
for each CPU for performance. As I mentioned before we have 3 physical
servers, 4-CPUs each. We're on DB2 EEE. Today they have 2 partitions
each, for a total of 6 partitions. Should we move to 4 partitions per
server? Can I re-partition on an existing database and cluster?

Thanks again to you and the others for great background and help!

You can add additional partitions to an existing database.

The decision as to how many partitions per physical node depends on other
factors such as number of separate disk controllers and their bandwidth,
disk configuration (preferably separate arrays or disks for each partition),
physical memory that can be allocated for each partition, and possibly the
number of Ethernet ports.

Having 2 processors per partition might not be wasted if intra-partition
parallelism is being utilized, although inter-partition parallelism is more
predictable.

[Ian]

Data Goob wrote:

By the way, I have heard that we should create one server partition
for each CPU for performance. As I mentioned before we have 3 physical
servers, 4-CPUs each. We're on DB2 EEE. Today they have 2 partitions
each, for a total of 6 partitions. Should we move to 4 partitions per
server? Can I re-partition on an existing database and cluster?

The most compelling reason that I have had to have a 1:1 CPU:Partition
ratio is to maximize utility performance (especially LOAD).

I would start with 6 partitions (2:1 CPU:Partition) and evaluate based
on your requirements.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[gnuoytr]

Mark A wrote:

"Data Goob" <da******@hotmail.com> wrote in message
news:If******************@fe10.usenetserver.com...

Thanks Mark.

It's a bit un-intuitive and backward to do it this
way, but I guess that's the way it works.

It is more straightforward on DB2 for z/OS (mainframe) where the

indexspace is defined with the create index statement.

But that is partly because the original DB2 UDB (OS/2 Database Manager) did not allow separate tablespaces for indexes. But on DB2 for Unix, Linux, and Windows, even if the data and indexes are in the same tablespace, they are not in the same file.

This is different than DB2 for z/OS where a tablespace or indexspace is a single VSAM dataset (unless the underlying VSAM dataset has gone into
extents).

v7.1-z/os-1.x

well, we were getting -904 on tables, so the MF guys ran the recover on
the table, but then got -904 on the index. so it had to be run again.
no problem, really. except in trying to understand the sequence of
events, i find that there is no description of calling an indexspace in

the current docs available on-line from ibm. it appears that an index
goes into a "space" named for the index in all cases. is this an
undocumented clause in the CREATE TABLE or CREATE INDEX statments??

looking at SYSINDEXES, the INDEXSPACE column is always the index name.
with the following exceptions: the plan table(s), indexes with 8
character names (the indexspace is the truncated name), and indexes
with
names longer than 8 characters (the indexspace is a concatenation of
a base string and a random string).

thanks,
robert

[Matt Emmerton]

<gn*****@rcn.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

Mark A wrote:

"Data Goob" <da******@hotmail.com> wrote in message
news:If******************@fe10.usenetserver.com...

Thanks Mark.

It's a bit un-intuitive and backward to do it this
way, but I guess that's the way it works.

It is more straightforward on DB2 for z/OS (mainframe) where the

indexspace

is defined with the create index statement.

But that is partly because the original DB2 UDB (OS/2 Database

Manager) did

not allow separate tablespaces for indexes. But on DB2 for Unix,

Linux, and

Windows, even if the data and indexes are in the same tablespace,

they are

not in the same file.

This is different than DB2 for z/OS where a tablespace or indexspace

is a

single VSAM dataset (unless the underlying VSAM dataset has gone into
extents).

v7.1-z/os-1.x

well, we were getting -904 on tables, so the MF guys ran the recover on
the table, but then got -904 on the index. so it had to be run again.
no problem, really. except in trying to understand the sequence of
events, i find that there is no description of calling an indexspace in

the current docs available on-line from ibm. it appears that an index
goes into a "space" named for the index in all cases. is this an
undocumented clause in the CREATE TABLE or CREATE INDEX statments??

It's quite well documented in "CREATE TABLE" -- look for "INDEX IN".

http://publib.boulder.ibm.com/infoce...n/r0000927.htm

--
Matt Emmerton

[dotyet]

Even I thought so!!
wonder what went wrong where (wwwww)

regards,
dotyet

[gnuoytr]

Matt Emmerton wrote:

<gn*****@rcn.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

Mark A wrote:

"Data Goob" <da******@hotmail.com> wrote in message
news:If******************@fe10.usenetserver.com...
> Thanks Mark.
>
> It's a bit un-intuitive and backward to do it this
> way, but I guess that's the way it works.
>
>
It is more straightforward on DB2 for z/OS (mainframe) where the indexspace

is defined with the create index statement.

But that is partly because the original DB2 UDB (OS/2 Database

Manager) did

not allow separate tablespaces for indexes. But on DB2 for Unix,

Linux, and

Windows, even if the data and indexes are in the same tablespace,

they are

not in the same file.

This is different than DB2 for z/OS where a tablespace or indexspace

is a

single VSAM dataset (unless the underlying VSAM dataset has gone

into extents).

v7.1-z/os-1.x

well, we were getting -904 on tables, so the MF guys ran the

recover on the table, but then got -904 on the index. so it had to be run again. no problem, really. except in trying to understand the sequence of
events, i find that there is no description of calling an indexspace in
the current docs available on-line from ibm. it appears that an index goes into a "space" named for the index in all cases. is this an
undocumented clause in the CREATE TABLE or CREATE INDEX statments??

It's quite well documented in "CREATE TABLE" -- look for "INDEX IN".

http://publib.boulder.ibm.com/infoce...n/r0000927.htm
--
Matt Emmerton

that's the UDB docs, not 390.