| Author |
Message
|
| vinod_sri |
 Posted: Thu Jul 19, 2007 12:59 pm Post subject: Transfer data between tables |
 |
|
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 |
|
 |
| jefflowrey |
| Posted: Thu Jul 19, 2007 1:15 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
SELECT
INSERT
DELETE
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| vinod_sri |
| Posted: Thu Jul 19, 2007 1:36 pm Post subject: |
|
|
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 |
|
|
| jefflowrey |
| Posted: Thu Jul 19, 2007 1:37 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Database nodes.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Michael Dag |
| Posted: Thu Jul 19, 2007 10:24 pm Post subject: |
|
|
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 |
|
|
| elvis_gn |
| Posted: Thu Jul 19, 2007 10:40 pm Post subject: |
|
|
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 |
|
|
| jefflowrey |
| Posted: Fri Jul 20, 2007 3:39 am Post subject: |
|
|
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 |
|
|
| vinod_sri |
| Posted: Fri Jul 20, 2007 6:01 am Post subject: |
|
|
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 |
|
|
| jefflowrey |
| Posted: Fri Jul 20, 2007 7:14 am Post subject: |
|
|
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 |
|
|
| dilse |
| Posted: Fri Jul 20, 2007 12:05 pm Post subject: |
|
|
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 |
|
|
| mymq |
| Posted: Fri Jul 20, 2007 1:28 pm Post subject: |
|
|
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 |
|
|
| Michael Dag |
| Posted: Fri Jul 20, 2007 2:49 pm Post subject: |
|
|
Jedi Knight
Joined: 13 Jun 2002
Posts: 2607
Location: The Netherlands (Amsterdam)
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Jul 20, 2007 2:55 pm Post subject: |
|
|
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 |
|
|
|
|
