Martin Packer's point is, of course, well taken. Egregiously bad SQL can be the villain. I dealt recently with a situation in which the SQL builtin function max was used repeatedly to obtain a next, new 'treaty number' in an insurance application. It did its job, correctly, by scanning all of the rows of one of the largest DB2 tables I have encountered over and over and over again, with predictable consequences.
I believe, however, that, as here, bad SQL is very often bad at bottom because it triggers too much implicit i/o. DB2's read and write engines are very good indeed at what they do; but both they and the sort invoked from DB2 can be asked to do too much gratuitous work. John Gilmore, Ashland, MA 01721 - USA On 1/13/12, Martin Packer <[email protected]> wrote: > Not always I/O-related. Sometimes CPU-related but where SQL tuning would > be more appropriate than application code instruction cycle tuning. > > Cheers, Martin > > Martin Packer, > Mainframe Performance Consultant, zChampion > Worldwide Banking Center of Excellence, IBM > > +44-7802-245-584 > > email: [email protected] > > Twitter / Facebook IDs: MartinPacker > Blog: > https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker > > > > From: > John Gilmore <[email protected]> > To: > [email protected], > Date: > 13/01/2012 13:20 > Subject: > Re: How bad is the EX instruction? > Sent by: > IBM Mainframe Assembler List <[email protected]> > > > > Gerhard Postpischil wrote: > > <begin snippet> > In general I tend to agree with this, but I've worked or consulted at > installations that either had problems completing overnight jobs in > their assigned batch window, or just processing large amounts of data. > </end snippet> > > I value GP's concurrence. Let me add, however, that 1) in my > experience batch-window problems are always i/o-related; and 2) the > unwashed always attack them in the wrong way, devoting resources to > "optimizing" instruction sequences that, even if it had been possible > to reduce their CP consumption to zero, would have left the > batch-window problem unresolved. > > These applications, like most commercial batch ones, were i/o-bound, > and their resolution required the use of overlapped, asynchronous i/o, > which, for those who know how to do it, is not difficult. What it > was/is in most of these shops was/is, quite literally, unthinkable. > (The RESIDENCE time of a classical MFU can always be cut by a factor > of four or more using asynchronous i/o.) > > John Gilmore, Ashland, MA 01721 - USA > > > > > > > > > Unless stated otherwise above: > IBM United Kingdom Limited - Registered in England and Wales with number > 741598. > Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU > -- John Gilmore, Ashland, MA 01721 - USA
