Hallo all
Is it possible to change schema of table in DB2 v.8.2 without
recreating and coping a table into a new schema?
I need it for several purposes, one of them - refactoring of existing
old database with more then 250 tables. I would like to split it to
subsystems, but some of tables are quite large.
And the size of tables prevents me from getting copy of production
database for our test envi-ronment. Total backup is almost 500 Gbytes.
But 98% of these data are stored in 3 tables, which are just archive.
So I planned:
1) move them to another schema,
2) create views in old schema, which will point to a new,
3) create a new user, which will have read access only to old schema.
So I will be able to make a backup under this new user with almost all
tables but much less then 500GB.
Why don't I use IMPORT/EXPORT? Because a lot of tables have IDENTITY
GENERATED ALWAYS, so I have conflicts in IDs when I load exported
data. Also, db2move tool allows only 10 table names to be specified at
a time...
But may be there are another ways to make partial backup of the big
database? And another way to refactor it to group tables by systems
else then by schemas?
Thank you in advance!
Is it possible to change schema of table in DB2 v.8.2 without
recreating and coping a table into a new schema?
I need it for several purposes, one of them - refactoring of existing
old database with more then 250 tables. I would like to split it to
subsystems, but some of tables are quite large.
And the size of tables prevents me from getting copy of production
database for our test envi-ronment. Total backup is almost 500 Gbytes.
But 98% of these data are stored in 3 tables, which are just archive.
So I planned:
1) move them to another schema,
2) create views in old schema, which will point to a new,
3) create a new user, which will have read access only to old schema.
So I will be able to make a backup under this new user with almost all
tables but much less then 500GB.
Why don't I use IMPORT/EXPORT? Because a lot of tables have IDENTITY
GENERATED ALWAYS, so I have conflicts in IDs when I load exported
data. Also, db2move tool allows only 10 table names to be specified at
a time...
But may be there are another ways to make partial backup of the big
database? And another way to refactor it to group tables by systems
else then by schemas?
Thank you in advance!
Comment