|
View previous topic :: View next topic
|
| Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
My DB2 tables are creating as : ABMMDDYY everyday for 5 tables.
MM - Month, DD - Day, YY as Year
Everyday my table is creating in the above format, for ex: AB020523
So I have so many tables.
My requirement is to get the data for 10 years based on some selection criteria which I know.
If I write a query for 1 table, then I have 5 jobs/SQL for 5 tables.
In a year, I think 365* 5 = 1825.. for 10 years: It would be = 1825 * 10 = 18250 jobs which is difficult to create.
Is there any better way we can do this ?
Thanks in advance. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2154
Location: USA
|
|
|
|
It there any difference between your tables, and rows?
What do you really create everyday? And how?
How your SQL (or your "job"???!!!) looks like? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2154
Location: USA
|
|
|
|
| maxsubrat wrote: |
If I write a query for 1 table, then I have 5 jobs/SQL for 5 tables.
|
Please, clarify, with example(s): how it is possible - while making a query for 1 table to get 5 SQL(?) for 5 tables at the end???
Kindly explain your terminology: what is the difference between query-SQL-job?. |
|
| Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
Hi,
My DB2 table is creating everyday and the table names like ABmmddyy.EMPDATA.
For ex: AB02012023.EMPDATA
AB02022023.EMPDATA
AB02032023.EMPDATA
AB02042023.EMPDATA. etc...
Everyday new data stores in the newly created table.
So I have around 365 tables in a year for EMPDATA table.
The columns and same for all, but data is different.
I know WHERE condition what to fill.
But the challenge is if I am going to fetch suppose for 1 year data for EMPDATA, then I need to write 365 DB2 queries which is very difficult. For 10 years data, I mean I need to create 3650 queries or jobs.
Same way I need for 4 other tables. Please let me know whether it is clear or not.
Thanks |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2154
Location: USA
|
|
|
|
Any real example????
There are hundreds of available tools to automatically generate any number of SQL queries (which are in fact nothing else but a standardized text of code). Most of those tools are as primitive as toys for little boys. You can easily use any of them.
To give you a specific advise, we need to understand better: what you are actually doing, and what is your final goal, with example(s)? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3077
Location: NYC,USA
|
|
|
|
| maxsubrat wrote: |
Hi,
My DB2 table is creating everyday and the table names like ABmmddyy.EMPDATA.
For ex: AB02012023.EMPDATA
AB02022023.EMPDATA
AB02032023.EMPDATA
AB02042023.EMPDATA. etc...
Everyday new data stores in the newly created table.
So I have around 365 tables in a year for EMPDATA table.
The columns and same for all, but data is different.
I know WHERE condition what to fill.
But the challenge is if I am going to fetch suppose for 1 year data for EMPDATA, then I need to write 365 DB2 queries which is very difficult. For 10 years data, I mean I need to create 3650 queries or jobs.
Same way I need for 4 other tables. Please let me know whether it is clear or not.
Thanks |
I don't understand the rationale behind creating new table every day. I have never seen that before. Who designed that and how did DBA approve such model?
The best bet is talk to your DBA and ask them if they can provide you table space or database level dump and then you can use DFSORT to filter as needed. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2154
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| maxsubrat wrote: |
Hi,
My DB2 table is creating everyday and the table names like ABmmddyy.EMPDATA.
For ex: AB02012023.EMPDATA
AB02022023.EMPDATA
AB02032023.EMPDATA
AB02042023.EMPDATA. etc...
Everyday new data stores in the newly created table.
So I have around 365 tables in a year for EMPDATA table.
The columns and same for all, but data is different.
I know WHERE condition what to fill.
But the challenge is if I am going to fetch suppose for 1 year data for EMPDATA, then I need to write 365 DB2 queries which is very difficult. For 10 years data, I mean I need to create 3650 queries or jobs.
Same way I need for 4 other tables. Please let me know whether it is clear or not.
Thanks |
I don't understand the rationale behind creating new table every day. I have never seen that before. Who designed that and how did DBA approve such model?
The best bet is talk to your DBA and ask them if they can provide you table space or database level dump and then you can use DFSORT to filter as needed. |
The general design really looks some stupid...
There are two possible ways:
1) to redesign the application from scratch, or
2) to advise how to automatically generate hundreds of SQL queries, whatever it is used for... |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3077
Location: NYC,USA
|
|
|
|
| sergeyken wrote: |
| Rohit Umarjikar wrote: |
| maxsubrat wrote: |
Hi,
My DB2 table is creating everyday and the table names like ABmmddyy.EMPDATA.
For ex: AB02012023.EMPDATA
AB02022023.EMPDATA
AB02032023.EMPDATA
AB02042023.EMPDATA. etc...
Everyday new data stores in the newly created table.
So I have around 365 tables in a year for EMPDATA table.
The columns and same for all, but data is different.
I know WHERE condition what to fill.
But the challenge is if I am going to fetch suppose for 1 year data for EMPDATA, then I need to write 365 DB2 queries which is very difficult. For 10 years data, I mean I need to create 3650 queries or jobs.
Same way I need for 4 other tables. Please let me know whether it is clear or not.
Thanks |
I don't understand the rationale behind creating new table every day. I have never seen that before. Who designed that and how did DBA approve such model?
The best bet is talk to your DBA and ask them if they can provide you table space or database level dump and then you can use DFSORT to filter as needed. |
The general design really looks some stupid...
There are two possible ways:
1) to redesign the application from scratch, or
2) to advise how to automatically generate hundreds of SQL queries, whatever it is used for... |
for point#2 - We can do either thru DFSORT or programmatically to create 1000s of control cards for unload, but DBA would have more better way to dump the whole data.
Let us wait for TS to go back to DBA or explain us about this design. |
|
| Back to top |
|
|
dneufarth
Active User
Joined: 27 Apr 2005
Posts: 420
Location: Inside the SPEW (Southwest Ohio, USA)
|
|
|
|
| It appears maxsubrat would like to concatenate DB2 tables much like datasets can be concatenated in jobs. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3077
Location: NYC,USA
|
|
|
|
| That is correct but having thousands of SELECT unions isn’t realistic nor unload each one thousands times and then concat. |
|
| Back to top |
|
|
|
|
