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

Large number of data causes problem with online transaction


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

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Tue Apr 21, 2015 10:53 am
Reply with quote

I've a table which has got "record length=200". It has 100 partitions based on the last 2 digits of some account. This table gets updated on a daily basis based with two types of data - say R and C. The C & R are moved to a archival table on a daily basis. Daily around 5 M of data gets processed (C + R) while on Monthends the same count goes to 20 M. Anf some times, before specific month end the table gets populated with the heavy payments to reach 30 M records.

Due to this, the online screens are not able to retrieve data from table on a given due date (special day or month end) with high amount of data when the primary has close to 30 M records - what can be the possible solution?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Apr 21, 2015 11:27 am
Reply with quote

My initial thoughts are that if you could split the data in different files/tables and then have your online programs access that data based on certain conditions (may be use date as a criteria). So, rather than having all the programs accessing same file/table, we can have different programs accessing different files/tables.

If you could explain the issue a little more then it would be better for me to understand
.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Tue Apr 21, 2015 2:32 pm
Reply with quote

Is the Index partitioned as well ?
Back to top
View user's profile Send private message
DB2 Guy

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Tue Apr 21, 2015 2:36 pm
Reply with quote

RahulG31 wrote:
My initial thoughts are that if you could split the data in different files/tables and then have your online programs access that data based on certain conditions (may be use date as a criteria). So, rather than having all the programs accessing same file/table, we can have different programs accessing different files/tables.

If you could explain the issue a little more then it would be better for me to understand
.
Table is already partitioned on the last 2 digits of "account number" and have 100 partitions.
Back to top
View user's profile Send private message
DB2 Guy

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Tue Apr 21, 2015 2:36 pm
Reply with quote

Marso wrote:
Is the Index partitioned as well ?
Yes, it is.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Apr 21, 2015 8:47 pm
Reply with quote

Could you tell us how much data is being added daily and how much is being archived?

Are you saying that 20M and 30M records are being added in a single day on special days?

What is your selection criteria in the program?

And I hope that you are doing a Reorg when large amount of data is being added in the table.
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Moderator


Joined: 14 Mar 2007
Posts: 10714
Location: italy

PostPosted: Tue Apr 21, 2015 9:00 pm
Reply with quote

define the
Quote:
the online screens are not able to retrieve data from table on a given due date


abend, slow response times,

please be more specific
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Apr 21, 2015 11:34 pm
Reply with quote

You should partition on due date and improve the cardinality of the SQL. Also make a use of TSQ's if you are not using it.
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 Very Large Sort failed - Seeking reco... DFSORT/ICETOOL 7
No new posts Secondary index error while loading d... IMS DB/DC 2
No new posts Sysncsort - Justification problem SYNCSORT 6
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts How to find which file contains my se... DFSORT/ICETOOL 6
Search our Forums:

Back to Top