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

GTT issue - help !


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

New User


Joined: 31 Oct 2006
Posts: 87
Location: brisbane

PostPosted: Sat Mar 23, 2019 4:17 am
Reply with quote

I have a specific requirement to generate a large series of reports that are dependent upon a single Global Temporary Table.

The cost of generating the GTT is very large. The cost of each (SQL) report is also significant, but not as costly as the initial generation of the GTT.

The problem I have is that the complete sequence takes about 2 to 3 hours to run. Ideally I would like to generate the GTT, and then run the 30 or so queries in parallel. By my calculations, the job will then finsih in about 30 minutes on average.

Obvious solution is to materialse the GTT or define a new (temporary) db2 table, but production policy will not allow this.

I've tried splitting the reports 10-wide, but then i'm essentailly generating the GTT 10 times which is very wasteful of production resources.

suggestions for a strategy ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Sat Mar 23, 2019 5:13 am
Reply with quote

If GtT and sql is factored most in to the cost then it can think of vsams or a PS Data set.
Just unload what you need in GTT into PS and use that to create any reports.

Second, why put it into GTT one shot everything ? Split up the jobs per reports and filter the necessary data per report and run them in parallel.
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 87
Location: brisbane

PostPosted: Sat Mar 23, 2019 5:33 am
Reply with quote

- Just unload what you need in GTT into PS and use that to create any reports.

this won't work. The issue is that the 30 reports are dependent on the GTT *and* data in other DB2 tables. Yes, could extract everything into PS and join/merge in DFSORT etc, but overkill and not a realistic solution.

The point of the GTT was to assemble the common elements, and thereby greatly simplify the various reports - which are still, notwithstanding, complex in their own right.
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 87
Location: brisbane

PostPosted: Sat Mar 23, 2019 5:35 am
Reply with quote

to anticipate a likely offering :

why not give to Business Intelligence?

yes, that's the best long term option, but the requirement is a here and now requirement. BI is not agile enough to accommodate this request in a short time.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Sat Mar 23, 2019 5:36 am
Reply with quote

Why use GTT ? Create a same DB2 table that you need and load the common element in one batch job into it and then use this one db2 table creating many reports in separate jobs.

See if DBA can find you any performance improvement solutions if such design changes are not feasible .
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 87
Location: brisbane

PostPosted: Sat Mar 23, 2019 8:06 am
Reply with quote

unfortunately we (developers) don't have create table authority in production.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2449
Location: Hampshire, UK

PostPosted: Sun Mar 24, 2019 6:53 pm
Reply with quote

Why do developers run jobs against production data? Your job, surely, is to develop not run?

Create your jobs to create the temporary DB2 table and give the whol mess to the production people to run.

Or get sign-off to be allowed to create a DB2 production table.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Mon Mar 25, 2019 5:18 pm
Reply with quote

It looks like you are expecting quick solution since it’s a production support issue and instead of rethinking for a long term solution , that means only DBA can help you by looking at the query and other stats for that execution.

Please approach such team at your site as said earlier as well.
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
This topic is locked: you cannot edit posts or make replies. issue with Syntax error SYNCSORT 2
No new posts Issue while fetching a column(Nullabl... DB2 18
No new posts Issue while reading a file in browse ... TSO/ISPF 7
No new posts Issue in SFTP a file All Other Mainframe Topics 1
No new posts Issue in KWIKKEY utility while creat... JCL & VSAM 2
Search our Forums:

Back to Top