Thanks for the detailed response. I was also in search of this. I was
thinking of yet another laborious method of achieving the same effect. What
was there on my mind is given below:
PS: The below given procedure is acceptable only if you don=92t deploy
database objects such as stored procedures and UDFs etc into your database.
1. Export data from each of the tables to IXF files going with the same name
of table names, e.g. if there is a table named employee in your database
then export data of employee table into an IXF file named employee.ixf
2. Get the DDL file with witch you create table definitions and tablespace
definitions for your database. Replace the tablespace definitions in this
DDL file to use DMS in place of SMS in this DDL file. It should be something
like this:
------------------------------------------------------
-- Script that creates the your table spaces
------------------------------------------------------
CREATE REGULAR TABLESPACE my_table_space1
PAGESIZE 8 K
MANAGED BY DATABASE
USING (
FILE '/some_location/my_table_space_path/my_table_space1' 20G
) NO FILE SYSTEM CACHING
EXTENTSIZE 128 PREFETCHSIZE AUTOMATIC BUFFERPOOL my_bp_8k_data;
Where,
my_table_space1 =96 Name of your SMS table
space
/some_location/my_table_space_path =96 Location into the file system of your
machine (or secondary hard-disk) for your DMS tablespaces.
my_bp_8k_data =96 The bufferpool you defin=
e
for your tablespaces
3. Drop existing database
4. Recreate the database with new DDL. This will create the DMS based
tablespaces.
5. Then import data into tables from corresponding IXF files using IMPORT
command.
That=92s it!!
Thanks and regards,
-----------------------------------------------
Pankaj
-----Original Message-----
From: p [mailto:db2udbdba-ezmlmshield-x17975678.[Email address protected]
Sent: Wednesday, June 27, 2007 6:38 PM
To: LazyDBA Discussion
Subject: Re : RE: Conversion of SMS table spaces to DMS table spaces.
On the same system:
0) Backup the source tablespace
1) run db2llok with the -e option and others (GRANTS, etc.).to extract the
DDL of the table(s) and of all objects pertasining to the tables(s). For
exampole if they all have the same schema name use -z SCHEMANAME
2) run db2move dbname export and specify tables to be moved by susing proper
option. This extracts the data.
2a) You may want to edit the output file to see if everything is
there as you will want it.
3) Drop the SMS tablespace
4) Create the required tablespace as DMS with same name as SMS. db2look
generates the command to create the tables in the tablespace with the same
name.
5) run the output file of db2 look as: db2 -tf filename. The file has a
connect to dbname in it. It will create the DDL for all objects.
6) run db2move dbname load to move the data in. The loads are all
nonrecvoerable.
7) backup the new tablespace and validate your new data.
Regards, Pierre.
----- Message d'origine -----
De: Tummala Sreenivas <db2udbdba-ezmlmshield-x10716359.[Email address
protected]
Date: Mercredi, Juin 27, 2007 7:17 am
Objet: RE: Conversion of SMS table spaces to DMS table spaces.
=C0: LazyDBA Discussion <[Email address protected]
> Hi Gurus!
>
>
> One of our customers is interested in migrating table spaces
> from SMS to
> DMS. Can you please advise as to how this can be done and what
> is the effort
> required say for 100 tables?
>
>
> Thanks and Regards
> Sreenivas
>
>
>
> -----------------------------------------------------------------
> ----
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
Pierre Saint-Jacques
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DISCLAIMER=0A=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=
This e-mail may contain privileged and confidential information which is the=
property of Persistent Systems Pvt. Ltd. It is intended only for the use of=
the individual or entity to which it is addressed. If you are not the inten=
ded recipient, you are not authorized to read, retain, copy, print, distribu=
te or use this message. If you have received this communication in error, pl=
ease notify the sender and delete all copies of this message. Persistent Sys=
tems Pvt. Ltd. does not accept any liability for virus infected mails.
DB2 & UDB email list listserv db2-l LazyDBA home page