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

Need to fetch data from so many DB2 tables for 10 years


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

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Sun Feb 05, 2023 7:25 am
Reply with quote

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

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA

PostPosted: Sun Feb 05, 2023 7:36 pm
Reply with quote

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

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA

PostPosted: Sun Feb 05, 2023 11:48 pm
Reply with quote

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

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Mon Feb 06, 2023 4:27 pm
Reply with quote

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

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA

PostPosted: Mon Feb 06, 2023 6:22 pm
Reply with quote

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

Global Moderator


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

PostPosted: Mon Feb 06, 2023 8:50 pm
Reply with quote

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

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA

PostPosted: Mon Feb 06, 2023 9:14 pm
Reply with quote

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

Global Moderator


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

PostPosted: Mon Feb 06, 2023 10:21 pm
Reply with quote

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

Active User


Joined: 27 Apr 2005
Posts: 420
Location: Inside the SPEW (Southwest Ohio, USA)

PostPosted: Tue Feb 07, 2023 1:28 am
Reply with quote

It appears maxsubrat would like to concatenate DB2 tables much like datasets can be concatenated in jobs.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Feb 07, 2023 3:01 am
Reply with quote

That is correct but having thousands of SELECT unions isn’t realistic nor unload each one thousands times and then concat.
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 How to save SYSLOG as text data via P... All Other Mainframe Topics 2
No new posts Store the data for fixed length COBOL Programming 1
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top