IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Data replication from multiple Db2 tables in single table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kishpra

New User


Joined: 24 May 2010
Posts: 92
Location: Pune

PostPosted: Mon Mar 27, 2017 9:58 pm
Reply with quote

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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Mar 27, 2017 11:01 pm
Reply with quote

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

New User


Joined: 30 Aug 2014
Posts: 24
Location: UK

PostPosted: Tue Mar 28, 2017 5:24 pm
Reply with quote

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

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue Mar 28, 2017 5:44 pm
Reply with quote

I vote for #3, DB2 Trigger
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Mar 28, 2017 6:27 pm
Reply with quote

Why duplicating data 'every minute'?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue Mar 28, 2017 6:35 pm
Reply with quote

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. icon_sad.gif
Back to top
View user's profile Send private message
kishpra

New User


Joined: 24 May 2010
Posts: 92
Location: Pune

PostPosted: Wed Apr 12, 2017 1:49 pm
Reply with quote

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

New User


Joined: 24 May 2010
Posts: 92
Location: Pune

PostPosted: Wed Apr 12, 2017 1:50 pm
Reply with quote

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

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Apr 12, 2017 8:21 pm
Reply with quote

After so many days of the initial post, you could not even decide whether there are 3 tables or 4 ? icon_evil.gif

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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Apr 12, 2017 9:57 pm
Reply with quote

Quote:
After so many days of the initial post, you could not even decide whether there are 3 tables or 4 ?
icon_biggrin.gif
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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top