tag:blogger.com,1999:blog-4516533711330247058.post9197693814134568905..comments2024-03-28T07:32:09.246-07:00Comments on Robert's Db2 blog: Migrating from DB2 for z/OS V8? DO THISRoberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-4516533711330247058.post-61505249358029987922013-02-06T15:15:17.823-08:002013-02-06T15:15:17.823-08:00Sorry about the delay in responding, George.
If b...Sorry about the delay in responding, George.<br /><br />If by "maxing out on the space used by the SQL queries" you mean exhausting work file space on the subsystem, there are several approaches you could take to deal with the situation. Among these are the following (listed in no particular order):<br /><br />a) Increase the work file space resource. You could add more work file table spaces in DSNDB07.<br /><br />b) It would appear that some of your ad-hoc queries are driving large-scale sorts. If you can identify some of the culprit queries, get the associated access path information (via EXPLAIN or Data Studio or a query analysis tool) and take a look at the parts of the query access plan that involve sort operations (could be associated with nested loop join and/or GROUP BY sort and/or ORDER BY sort, etc.). Then, see if you could take index actions that might eliminate these sorts. You might want to index one or more columns that are not currently indexed. Of course, doing this may not be feasible if the target table(s) already has quite a few indexes -- too many indexes on a table can make insert and delete and some update operations more expensive than you'd like.<br /><br />c) Consider creating an operational data store (ODS), if you don't already have one. Generally speaking, this is a copy of certain production tables, created in a separate DB2 subsystem that has its own work file resources. These tables could be updated nightly or, if needed, updated constantly via a "trickle-feed" mechanism (the latter usually takes the form of a data change replication tool). Direct the ad-hos queries to the ODS database, and that will serve to insulate the "operational" DB2-accessing programs from the ad-hoc queries.<br /><br />d) This can end up being related to item "c" above. Use the DB2 resource limit facility (RLF) to automatically terminate a dynamic query on the production system that exceeds a specified CPU time threshold. How is this connected to the ODS idea? Well, if ad-hoc query issuers continue to target the production system after you've set up an ODS, and then complain when RLF terminates their long-running queries, explain that the queries can run longer on the ODS. They will then send queries to that system.<br /><br />e) Consider implementing a DB2 Analytics Accelerator, and have these long-running dynamic queries automatically redirected to that server (information on the DB2 Analytics Accelerator can be found at http://www-01.ibm.com/software/data/db2/zos/analytics-accelerator/).<br /><br />Robert Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-55084698104008693992013-02-03T15:49:13.339-08:002013-02-03T15:49:13.339-08:00Robert
I cannot give you too many details but we a...Robert<br />I cannot give you too many details but we are using Microstrategy BI to Z/OS DB2 using DB2connect.<br /><br />A couple of times we have had Adhoc BI queries causing all DB2 Users (Batch and Online) to grind to a halt due to what I am lead to believe is maxing out on the space used by the SQL queries generated by Microstrategy. The trouble with end user Adhoc is the efficiency of the SQL cannot be controlled.<br /><br />If this is something that has an obvious solution then you can respond through my personal email :-<br /><br />tuscan.diane@gmail.com<br /><br />regards<br />George BullockAnonymousnoreply@blogger.com