SEO

Hello,

I want to plan rearranging tables in our database according to
business areas. say all tables in business area A will be in seperate
tablespace or tablespaces. I am planning to monitor activity on tables
for a month by taking snapshots on tables and then i will see if this
rearrangement is possible.

What are the factors, measures etc. i should consider for rearranging
tables.
anyone has done this before?
please guide me through process and/or direct me to some online/books
resources for the same.

regards,
jagdip

I would think that one thing you could do to simplify your life is to define
the schema name of tables according to which business area they belong. The
tablespace you put them in should be important because of how they are
accessed not necessarily who or what do they logically belong to.
This is true if you are not concerned about physically isolating tables of a
business area from others.
Tablespaces relate to physical location of data and how it is accessed and
managed.
Schemas tend to relate to how data in tables is logically related.
I know I'm over simplifying but it can be a good start for you.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"db2admin" <jagdip@gmail.com> a écrit dans le message de news:
1172618312.046942.4970@h3g2000cwc.googlegroups.com...
> Hello,
>
> I want to plan rearranging tables in our database according to
> business areas. say all tables in business area A will be in seperate
> tablespace or tablespaces. I am planning to monitor activity on tables
> for a month by taking snapshots on tables and then i will see if this
> rearrangement is possible.
>
> What are the factors, measures etc. i should consider for rearranging
> tables.
> anyone has done this before?
> please guide me through process and/or direct me to some online/books
> resources for the same.
>
> regards,
> jagdip
>

Hello Pierre,
Thanks for your reply. The need to rearrange tables into tablespaces
based on business areas arose from
needing to rollback tables related to one business area by restoring
previous backup
We get some issues with data in production and sometime there is need
to bring back all tables in one business area to previous state before
screwup
I understand the implications related to performance by rearranging
tables/tablespaces and i am really just exploring the possibilities
here
I also understand that better solutions is to examine the applications
code or enhance to avoid such data errors but things are out of
control
What other routes/possibilities exist ( if any ) other than
rearranging tables?



On Mar 1, 8:00 pm, "Pierre Saint-Jacques" <sesc...@invalid.net> wrote:
> I would think that one thing you could do to simplify your life is to define
> the schema name of tables according to which business area they belong. The
> tablespace you put them in should be important because of how they are
> accessed not necessarily who or what do they logically belong to.
> This is true if you are not concerned about physically isolating tables of a
> business area from others.
> Tablespaces relate to physical location of data and how it is accessed and
> managed.
> Schemas tend to relate to how data in tables is logically related.
> I know I'm over simplifying but it can be a good start for you.
> HTH, Pierre.
>
> --
> Pierre Saint-Jacques
> SES Consultants Inc.
> 514-737-4515
> "db2admin" <jag...@gmail.com> a écrit dans le message de news:
> 1172618312.046942.4...@h3g2000cwc.googlegroups.com ...
>
> > Hello,
>
> > I want to plan rearranging tables in our database according to
> > business areas. say all tables in business area A will be in seperate
> > tablespace or tablespaces. I am planning to monitor activity on tables
> > for a month by taking snapshots on tables and then i will see if this
> > rearrangement is possible.
>
> > What are the factors, measures etc. i should consider for rearranging
> > tables.
> > anyone has done this before?
> > please guide me through process and/or direct me to some online/books
> > resources for the same.
>
> > regards,
> > jagdip

All right back to square one.
I guess the way you need to do this means that there has to be a unique
naming convention for your tablespaces that encompasses all your tables and
indexes.
The only way I can do this, I think, is by building an id that has a
specific identifying relationship with the business it will represent. Use
this id which has to have DBADM authority at least to create the
tablespaces. The name is what you'll choose but the DEFINER will be the id
you use. Unless your analysis has shown you different, spread the containers
of your tbspcs. over some of the available drives. You won't want to spread
over each drive as each business's tbspcs. will step on the others' shoe
laces.
From there, create the tables and indexes of a business in its tablespaces.
That way, you can write a script which selects the tblspcs to backup with a
where clause = 'DEFINER`' and use the reverse to restore, roll forward.

What you want to get out of your analysis, which will get you very familiar
with the get snapshot command, is which tables are highly used with each
other eithin a business and which tables are highly used within the db.
You'll want to put those tables in tablespaces that do not share their
containers in the same physical drive.

A different way to do this if you do not have many businesses to represent
and the business does not necessarily have huge volumes of data is to create
one db for each business. There you get total independance and control.
I've worked with a customer (state government) that is small. They have a
relatively large Linux box and they run 12 instances, each with a single db
for each of the Dept. they have to serve (Finance, HR, Health, Education,
....) It seems to work quite well for them. No I cannot refer you to them
(sorry)!

Hope this helps, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"db2admin" <jagdip@gmail.com> a écrit dans le message de news:
1172850210.996215.17180@n33g2000cwc.googlegroups.c om...
Hello Pierre,
Thanks for your reply. The need to rearrange tables into tablespaces
based on business areas arose from
needing to rollback tables related to one business area by restoring
previous backup
We get some issues with data in production and sometime there is need
to bring back all tables in one business area to previous state before
screwup
I understand the implications related to performance by rearranging
tables/tablespaces and i am really just exploring the possibilities
here
I also understand that better solutions is to examine the applications
code or enhance to avoid such data errors but things are out of
control
What other routes/possibilities exist ( if any ) other than
rearranging tables?



On Mar 1, 8:00 pm, "Pierre Saint-Jacques" <sesc...@invalid.net> wrote:
> I would think that one thing you could do to simplify your life is to
> define
> the schema name of tables according to which business area they belong.
> The
> tablespace you put them in should be important because of how they are
> accessed not necessarily who or what do they logically belong to.
> This is true if you are not concerned about physically isolating tables of
> a
> business area from others.
> Tablespaces relate to physical location of data and how it is accessed and
> managed.
> Schemas tend to relate to how data in tables is logically related.
> I know I'm over simplifying but it can be a good start for you.
> HTH, Pierre.
>
> --
> Pierre Saint-Jacques
> SES Consultants Inc.
> 514-737-4515
> "db2admin" <jag...@gmail.com> a écrit dans le message de news:
> 1172618312.046942.4...@h3g2000cwc.googlegroups.com ...
>
> > Hello,
>
> > I want to plan rearranging tables in our database according to
> > business areas. say all tables in business area A will be in seperate
> > tablespace or tablespaces. I am planning to monitor activity on tables
> > for a month by taking snapshots on tables and then i will see if this
> > rearrangement is possible.
>
> > What are the factors, measures etc. i should consider for rearranging
> > tables.
> > anyone has done this before?
> > please guide me through process and/or direct me to some online/books
> > resources for the same.
>
> > regards,
> > jagdip

Thanks a lot for all input
I am going to monitor database and will consider all you said while
analyzing


On Mar 4, 11:57 am, "Pierre Saint-Jacques" <sesc...@invalid.net>
wrote:
> All right back to square one.
> I guess the way you need to do this means that there has to be a unique
> naming convention for your tablespaces that encompasses all your tables and
> indexes.
> The only way I can do this, I think, is by building an id that has a
> specific identifying relationship with the business it will represent. Use
> this id which has to have DBADM authority at least to create the
> tablespaces. The name is what you'll choose but the DEFINER will be the id
> you use. Unless your analysis has shown you different, spread the containers
> of your tbspcs. over some of the available drives. You won't want to spread
> over each drive as each business's tbspcs. will step on the others' shoe
> laces.
> From there, create the tables and indexes of a business in its tablespaces.
> That way, you can write a script which selects the tblspcs to backup witha
> where clause = 'DEFINER`' and use the reverse to restore, roll forward.
>
> What you want to get out of your analysis, which will get you very familiar
> with the get snapshot command, is which tables are highly used with each
> other eithin a business and which tables are highly used within the db.
> You'll want to put those tables in tablespaces that do not share their
> containers in the same physical drive.
>
> A different way to do this if you do not have many businesses to represent
> and the business does not necessarily have huge volumes of data is to create
> one db for each business. There you get total independance and control.
> I've worked with a customer (state government) that is small. They have a
> relatively large Linux box and they run 12 instances, each with a single db
> for each of the Dept. they have to serve (Finance, HR, Health, Education,
> ...) It seems to work quite well for them. No I cannot refer you to them
> (sorry)!
>
> Hope this helps, Pierre.
>
> --
> Pierre Saint-Jacques
> SES Consultants Inc.
> 514-737-4515
> "db2admin" <jag...@gmail.com> a écrit dans le message de news:
> 1172850210.996215.17...@n33g2000cwc.googlegroups.c om...
> Hello Pierre,
> Thanks for your reply. The need to rearrange tables into tablespaces
> based on business areas arose from
> needing to rollback tables related to one business area by restoring
> previous backup
> We get some issues with data in production and sometime there is need
> to bring back all tables in one business area to previous state before
> screwup
> I understand the implications related to performance by rearranging
> tables/tablespaces and i am really just exploring the possibilities
> here
> I also understand that better solutions is to examine the applications
> code or enhance to avoid such data errors but things are out of
> control
> What other routes/possibilities exist ( if any ) other than
> rearranging tables?
>
> On Mar 1, 8:00 pm, "Pierre Saint-Jacques" <sesc...@invalid.net> wrote:
>
> > I would think that one thing you could do to simplify your life is to
> > define
> > the schema name of tables according to which business area they belong.
> > The
> > tablespace you put them in should be important because of how they are
> > accessed not necessarily who or what do they logically belong to.
> > This is true if you are not concerned about physically isolating tablesof
> > a
> > business area from others.
> > Tablespaces relate to physical location of data and how it is accessed and
> > managed.
> > Schemas tend to relate to how data in tables is logically related.
> > I know I'm over simplifying but it can be a good start for you.
> > HTH, Pierre.
>
> > --
> > Pierre Saint-Jacques
> > SES Consultants Inc.
> > 514-737-4515
> > "db2admin" <jag...@gmail.com> a écrit dans le message de news:
> > 1172618312.046942.4...@h3g2000cwc.googlegroups.com ...
>
> > > Hello,
>
> > > I want to plan rearranging tables in our database according to
> > > business areas. say all tables in business area A will be in seperate
> > > tablespace or tablespaces. I am planning to monitor activity on tables
> > > for a month by taking snapshots on tables and then i will see if this
> > > rearrangement is possible.
>
> > > What are the factors, measures etc. i should consider for rearranging
> > > tables.
> > > anyone has done this before?
> > > please guide me through process and/or direct me to some online/books
> > > resources for the same.
>
> > > regards,
> > > jagdip