Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
GTT issue - help !

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 38
Location: brisbane

PostPosted: Sat Mar 23, 2019 4:17 am    Post subject: GTT issue - help !
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

Senior Member


Joined: 21 Sep 2010
Posts: 2129
Location: NY,USA

PostPosted: Sat Mar 23, 2019 5:13 am    Post subject:
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: 38
Location: brisbane

PostPosted: Sat Mar 23, 2019 5:33 am    Post subject:
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: 38
Location: brisbane

PostPosted: Sat Mar 23, 2019 5:35 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2129
Location: NY,USA

PostPosted: Sat Mar 23, 2019 5:36 am    Post subject:
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: 38
Location: brisbane

PostPosted: Sat Mar 23, 2019 8:06 am    Post subject:
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: 2162
Location: UK

PostPosted: Sun Mar 24, 2019 6:53 pm    Post subject: Reply to: GTT issue - help !
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

Senior Member


Joined: 21 Sep 2010
Posts: 2129
Location: NY,USA

PostPosted: Mon Mar 25, 2019 5:18 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Tivoli job scheduler issue John Poulakos IBM Tools 12 Sat Mar 23, 2019 12:32 am
No new posts ACS Routines Management class issue upendrasri All Other Mainframe Topics 5 Mon Jan 28, 2019 4:51 am
No new posts Facing issue while executing multi ro... aagarwal88 DB2 6 Tue Oct 02, 2018 8:11 am
This topic is locked: you cannot edit posts or make replies. S0C4-X'11' issue - page translation e... ashek15 JCL & VSAM 10 Wed Aug 15, 2018 4:23 am
No new posts Issue While Recovering a Tablespace satish.ms10 DB2 4 Tue Aug 07, 2018 2:54 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us