Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Create tables from old tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Wed Nov 13, 2013 6:02 pm    Post subject: Create tables from old tables
Reply with quote

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
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 13, 2013 6:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Wed Nov 13, 2013 7:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Thu Nov 14, 2013 5:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Nov 18, 2013 8:44 pm    Post subject:
Reply with quote

stop the applications, not the server...................
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 19, 2013 2:10 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Nov 22, 2013 4:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Nov 22, 2013 8:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 25, 2013 3:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us