View previous topic :: View next topic
|
Author |
Message |
kishpra
New User
Joined: 24 May 2010 Posts: 92 Location: Pune
|
|
|
|
Hi All,
I have 3 to 4 DB2 tables getting written by Stored procedure inserts. From these 3 to 4 tables, I need few columns data of each table to be loaded into a another single DB2 table every minute.
Please let me know what is the best way to achieve this.
Thank you. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
I have 3 to 4 DB2 tables getting written by Stored procedure inserts. From these 3 to 4 tables, I need few columns data of each table to be loaded into a another single DB2 table every minute. |
1.Unload the data by joining them either by a program or a unload job
2. Create a load job to the target table by referring to the unload file created in step 1.
3. Or look more on DB2 Triggers.
3. But. you need to tell us how often and how these events should occur? |
|
Back to top |
|
|
farhad_evan
New User
Joined: 30 Aug 2014 Posts: 24 Location: UK
|
|
|
|
How many records should be load into target table every minute ?
Also you can use another stored procedure or a COBOL job and insert data to target table instead of load.
As Rohit says you need to tell us more information.
Thanks. |
|
Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
I vote for #3, DB2 Trigger |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Why duplicating data 'every minute'? |
|
Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
Quote: |
Why duplicating data 'every minute'? |
I think is the OP giving a bad problem description.
My understanding is that the Stored Procedure is updating 3 to 4 DB2 Tables.
This is occurring every minute.
At this time, selected columns from these newly added tables/rows, need to be added to a fifth table/rows.
This is a good place for a DB2 trigger. The other option is to modify he Stored Procedure to write to the fifth table.
Perhaps the OP can clarify the requirement. But alas, they are a no show since the original post. Typical. |
|
Back to top |
|
|
kishpra
New User
Joined: 24 May 2010 Posts: 92 Location: Pune
|
|
|
|
Hi All,
The data in the 3 to 4 DB2 tables gets inserted by stored procedure and Java layer directly. I need a delta (only recent changes) every 2 mins or so to be reflected in the single DB2 table which has columns from the other DB2 tables. |
|
Back to top |
|
|
kishpra
New User
Joined: 24 May 2010 Posts: 92 Location: Pune
|
|
|
|
And on the number of records that should go in a target table every 2 mins will be around 100-200 in addition to updates to the existing records. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
After so many days of the initial post, you could not even decide whether there are 3 tables or 4 ?
Is there any relation between these '3 to 4 tables'? If there is any then you have to tell that.
What is the key of target table, if any?
Are you Not satisfied with having a trigger?
The usual practice in DB2 is to have a created timestamp in each table. Based on that you could also run a batch job to extract data inserted in last 2 minutes.
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
After so many days of the initial post, you could not even decide whether there are 3 tables or 4 ? |
Quote: |
The usual practice in DB2 is to have a created timestamp in each table. Based on that you could also run a batch job to extract data inserted in last 2 minutes. |
TS/OP is certainly overlooking earlier suggestions made. So no point wasting time here and adding more inputs.
Quote: |
The data in the 3 to 4 DB2 tables gets inserted by stored procedure and Java layer directly. I need a delta (only recent changes) every 2 mins or so to be reflected in the single DB2 table which has columns from the other DB2 tables |
Why don't you insert it at the same time to this 5th table when you have all the information in hand and why to wait for 2 minutes, anything special going on in those 2 minutes? |
|
Back to top |
|
|
|