|
View previous topic :: View next topic
|
| Author |
Message |
DB2 Guy
New User
Joined: 28 Oct 2008
Posts: 98
Location: Cubicle
|
|
|
|
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 |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| Is the Index partitioned as well ? |
|
| Back to top |
|
|
DB2 Guy
New User
Joined: 28 Oct 2008
Posts: 98
Location: Cubicle
|
|
|
|
| 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 |
|
|
DB2 Guy
New User
Joined: 28 Oct 2008
Posts: 98
Location: Cubicle
|
|
|
|
| Marso wrote: |
| Is the Index partitioned as well ? |
Yes, it is. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
|
|
