× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Have a customer running some pretty serious SQL queries from MS products to i. Those have recently begun taking Waaaaaaaaaaaaaaaaaay longer than they used to. Like from 20 minutes last month end to 24+ Hours this time. Only difference is about 50% more data. Yeah that's a lot but it shouldn't make this level of difference!!!

Power8. 6 Cores. 384GB Memory. SSD Storage on Storwize. i 7.1. Current PTFs.

The queries in question (QZDASOINIT) run in a private memory pool to a subsystem NOT in a shared pool. Thus the paging option (*CALC *FIXED) is forced to *FIXED.

Anyone seen where this makes a significant difference moving to a shared pool with *CALC? Given the fabulously low I/O I'm thinking not.

A bit more on the environment:

The pool has 15GB memory and just one query at a time runs there. For EXTENDED periods of time the I/O numbers for the pool from WRKSYSSTS are .0 .0 .0 .0 .0 .0 .0. I/Os in the job are from WRKACTJOB might be 4 or 5 per second. There are four active threads each effectively consuming a full processor and all are running DbopThreadMain__FP14DbopThreadParm. The jobs burn through 250,000 CPU seconds in 24 hours.

We did bring up visual explain and yeah there is a lot going on there. The thing could make a cool "FatHead" decal and would need to be that big to be readable on a wall. But the same queries ran last month so....

IBM's only advice is 'you need to build the indices the query optimizer is recommending.' There are 9 it recommends with a total build time of about 45 minutes so not insignificant. Tables are in the 4M rows plus in size so 'big' but certainly not 'huge'. We are working on building these to shop standard naming. Hoping they really do help!


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.