|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Hi ,
I have to create a new table(with different schema name) from an existing table. The new table will have to be loaded with the same data as the existing table. What are the procedures and techniques that may be used?
1. How will the relationships and refrentia constaraints be handled for the new table. The new table should have the identical referential constaraints as the old table.
2. What are the ways data can be loaded to the new table. 1 ways I can think of is to unload the data from original table and load it to the new table after creating the new table. But for this do we have to stop any update/isert/del access to the old table after we unload it untill and unless we load the new table.
Note the table name will remain same only schema name wil change like example abc. table1--------> xyz.table1
Please help.
Thanks |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
1.You need to understand the architecture and purpose of creating the table to decide upon referential constraints.
2.Better to select the data from OLD table only after the data is commited and then load to new table |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| Pandora-Box wrote: |
1.You need to understand the architecture and purpose of creating the table to decide upon referential constraints.
2.Better to select the data from OLD table only after the data is commited and then load to new table |
Good point mentioned about the dat to be selected after the recent commit. how will the referential constraints be handled? |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Do we need to stop the DB2 server before executing the unloads so that no updates/inserts/delete happen after the unlaod has been taken care of?
I want to make sure that
a) all the data in the tables has been commited and
b)no updates happen to the table after the unload so that we do not miss any updates/inserts/deletes
How can these be eccomplished?
Also is it necessary to have 2 outputs for each unload job ---> 1.) containing table data 2) the utility control statement that can be added to the LOAD utility
Thanks,
Arijit |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| stop the applications, not the server................... |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rijit,
You should get a DBA involved to do these sort of tasks.
1. To get the ddl of the old table with referential contraints, you should have BMC(Catalog Manager or Change Manager) / CA(RCQuery or RC Migrator)
2. For loading the table, tools can generate the strategy or CDLs.
If you are going manual way,
a)Start the tablespace in UT mode
b)Unload
c)Load
(or)
b,c)if the source and target are in same subsystem you can go for Cross-Loader function
d)Start the tablespace in RW mode
| Quote: |
| Also is it necessary to have 2 outputs for each unload job ---> 1.) containing table data 2) the utility control statement that can be added to the LOAD utility |
1. I believe unload will give you table data, if the syntax is correct
2. SYSPUNCH
Thanks,
Sushanth |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| sushanth bobby wrote: |
Rijit,
You should get a DBA involved to do these sort of tasks.
1. To get the ddl of the old table with referential contraints, you should have BMC(Catalog Manager or Change Manager) / CA(RCQuery or RC Migrator)
2. For loading the table, tools can generate the strategy or CDLs.
If you are going manual way,
a)Start the tablespace in UT mode
b)Unload
c)Load
(or)
b,c)if the source and target are in same subsystem you can go for Cross-Loader function
d)Start the tablespace in RW mode
| Quote: |
| Also is it necessary to have 2 outputs for each unload job ---> 1.) containing table data 2) the utility control statement that can be added to the LOAD utility |
1. I believe unload will give you table data, if the syntax is correct
2. SYSPUNCH
Thanks,
Sushanth |
I am trying to get hold of a DBA who can assist, Anyone aware how to make sure the RIs are maintained and in the new tables created? Which option is better to refer to identify all the RI assosciated with a table:
a) SYSIBM.SYSRELS does that also contain the RI ?
b) BMC tool give RI command against the table
c) refer the table DDL using BMC
??
Thanks, |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Why is it necessary to start the database in UT(Utility Access) mode? IF the below command is issued what will happen?
DSN
-START DATABASE(XXXXX) SPACENAM(YYYYYY) ACCESS(UT)
END
is it only DB2 utilities can access the table? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rijit,
Easy/Lazy Way would be --> BMC Catalog Manger RI command.
| Quote: |
Why is it necessary to start the database in UT(Utility Access) mode? IF the below command is issued what will happen?
|
I thought you wanted to
| Quote: |
stop any update/isert/del access to the old table after we unload it untill and unless we load the new table.
|
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
