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
 

 

Data replication from multiple Db2 tables in single table

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

New User


Joined: 24 May 2010
Posts: 91
Location: Pune

PostPosted: Mon Mar 27, 2017 9:58 pm    Post subject: Data replication from multiple Db2 tables in single table
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

Senior Member


Joined: 21 Sep 2010
Posts: 1676
Location: NY,USA

PostPosted: Mon Mar 27, 2017 11:01 pm    Post subject:
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: 16
Location: UK

PostPosted: Tue Mar 28, 2017 5:24 pm    Post subject:
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: 662
Location: Pennsylvania

PostPosted: Tue Mar 28, 2017 5:44 pm    Post subject:
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: 2252
Location: @my desk

PostPosted: Tue Mar 28, 2017 6:27 pm    Post subject:
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: 662
Location: Pennsylvania

PostPosted: Tue Mar 28, 2017 6:35 pm    Post subject:
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: 91
Location: Pune

PostPosted: Wed Apr 12, 2017 1:49 pm    Post subject:
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: 91
Location: Pune

PostPosted: Wed Apr 12, 2017 1:50 pm    Post subject:
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: 360
Location: USA

PostPosted: Wed Apr 12, 2017 8:21 pm    Post subject: Reply to: Data replication from multiple Db2 tables in single table
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

Senior Member


Joined: 21 Sep 2010
Posts: 1676
Location: NY,USA

PostPosted: Wed Apr 12, 2017 9:57 pm    Post subject:
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    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
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts SORT JSON type of data maxsubrat DFSORT/ICETOOL 8 Wed Apr 19, 2017 6:01 pm
No new posts Validating file with multiple header/... mohitsaini DFSORT/ICETOOL 6 Thu Apr 13, 2017 1:53 am
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 7 Mon Mar 27, 2017 3:59 pm


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