ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Transfer data between tables

Post new topic  Reply to topic
 Transfer data between tables « View previous topic :: View next topic » 
Author Message
vinod_sri
PostPosted: Thu Jul 19, 2007 12:59 pm    Post subject: Transfer data between tables Reply with quote

Apprentice

Joined: 10 Dec 2003
Posts: 42
Location: Atlanta

Hi Folks,

Can any one please help me with some ideas on transferring the data between two tables lies on two different databases? I have a set of tables on say XYZ database and I would like to transfer data from these tables created on ABC database and delete the tables on XYZ database. I am using Message Broker v 6.0.2 and DB2 8.2. Please share any information or ideas on this issue.

Thanks in advance,

vinod
Back to top
View user's profile Send private message Send e-mail
jefflowrey
PostPosted: Thu Jul 19, 2007 1:15 pm    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

SELECT
INSERT
DELETE
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
vinod_sri
PostPosted: Thu Jul 19, 2007 1:36 pm    Post subject: Reply with quote

Apprentice

Joined: 10 Dec 2003
Posts: 42
Location: Atlanta

Hi Jeff,

Thanks for the response. I know how to select the data from the tables on one database using the Compute node, and store it in a message tree Then subsequent Database node gets the data and populates the tables on the other database.

But, around 35 tables are going to be involved in this data transfer. So I am looking for some kind of better and easier solution.

Please help me.......


Thanks,

Vinod.
Back to top
View user's profile Send private message Send e-mail
jefflowrey
PostPosted: Thu Jul 19, 2007 1:37 pm    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

Database nodes.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
Michael Dag
PostPosted: Thu Jul 19, 2007 10:24 pm    Post subject: Reply with quote

Jedi Knight

Joined: 13 Jun 2002
Posts: 2607
Location: The Netherlands (Amsterdam)

is this a repetative thing? IMO you are migrating tables from one database to another...

why not use the database tools of the database to unload those tables and reload them on the new server???

or am I missing something???
_________________
Michael



MQSystems Facebook page
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
elvis_gn
PostPosted: Thu Jul 19, 2007 10:40 pm    Post subject: Reply with quote

Padawan

Joined: 08 Oct 2004
Posts: 1905
Location: Dubai

Hi vinod_sri,
Michael Dag wrote:
is this a repetative thing? IMO you are migrating tables from one database to another...

why not use the database tools of the database to unload those tables and reload them on the new server???

or am I missing something???
Yes, if this is a one time thing, then I don't see why you need flows for this....make a normal Db script to do it.

Don't think broker should anyway be used to Delete and create tables, doesn't sound like 'business integration' but more like 'business generation'.

Regards.
Back to top
View user's profile Send private message Send e-mail
jefflowrey
PostPosted: Fri Jul 20, 2007 3:39 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

In general, there are better tools than WMB for the specific problem of Extract/Transform/Load. This is a problem in Information integration, not Application integration.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
vinod_sri
PostPosted: Fri Jul 20, 2007 6:01 am    Post subject: Reply with quote

Apprentice

Joined: 10 Dec 2003
Posts: 42
Location: Atlanta

Thanks for your suggestions folks. Here my requirement is to purge the records that are 60 days old from about 32 tables. I developed a flow with a timer node runs every hour, that generates the primary keys of the records that are eligible for purge. This flow sends out the table_name and primary_key values in an xml format to an another flow. The second flow then reads the input values and moves that particular record to another table located in a different database. Once the move is done, then the record must be deleted from the source table. I can achieve the same thing by writing the DB scripts. But, since this is a continuous process, we decided to do it with message flows.

Here is the output from the first flow:

<SHIPMENT><TABLE_NAME>ABCD_SHIPMENT</TABLE_NAME><INSTANCE_ID>1234567890</INSTANCE_ID></SHIPMENT>

All your ideas and suggestions are well appreciated.

Thanks

Vinod
Back to top
View user's profile Send private message Send e-mail
jefflowrey
PostPosted: Fri Jul 20, 2007 7:14 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

Do it all in a stored procedure, that is triggered on insert.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
dilse
PostPosted: Fri Jul 20, 2007 12:05 pm    Post subject: Reply with quote

Master

Joined: 24 Jun 2004
Posts: 270

FYI: If you are accessing both databases from same compute node to do the operations, make sure the both databases are of same type.
Back to top
View user's profile Send private message
mymq
PostPosted: Fri Jul 20, 2007 1:28 pm    Post subject: Reply with quote

Centurion

Joined: 01 Mar 2007
Posts: 101
Location: US-Greenwille

Infact, i am DB2 Admin. If you ask me i would suggest you to go with some scripts to finish up this job rather than going to WBIMB to write some flow which is not fruitful for this task.
_________________
--SRK--
Back to top
View user's profile Send private message Send e-mail
Michael Dag
PostPosted: Fri Jul 20, 2007 2:49 pm    Post subject: Reply with quote

Jedi Knight

Joined: 13 Jun 2002
Posts: 2607
Location: The Netherlands (Amsterdam)

Sounds like an application archiving job that runs every hour...
_________________
Michael



MQSystems Facebook page
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
jefflowrey
PostPosted: Fri Jul 20, 2007 2:55 pm    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

jefflowrey wrote:
Do it all in a stored procedure, that is triggered on insert.

_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Transfer data between tables
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.