Performance Improvements from the “Things I Wish They’d Told Me 8 years Ago” - Visualized

1. Performance Improvements from the “Things I Wish They’d Told Me 8 years Ago” - Visualized Thomas A. Halinski, Systems Engineer CMG Las Vegas December 10, 2004

2. Performance Improvements from the “Things I Wish They’d Told Me 8 years Ago” - Visualized Thomas A. Halinski Pearls of wisdom for application performance improvements using DB2 sound good and make a lot of sense. But how do we know how well they work and to what extent? This paper will take some of the performance tips from Bonnie Baker, a DB2 industry expert, and measure them using STROBE. It will explain the theory and show the actual before and after results of implementing tips like: synchronous vs. asynchronous I/O, index usage and stage 1 vs. stage 2 predicates. After “seeing” the actual performance improvements, IT personnel will be much more confident that theory and reality can go hand in hand.

3. Birth of DB2 • Once upon a time, many years ago, in the ancient land of California, a group of developers put their heads and hearts together and created a product that would change the world of information technology. On the first day, they created SQL. • On the second day, they created DB2. — Bonnie Baker

4. Pedagogy in the DB2 World “Blind faith in the infallibility of computer software is short-lived. Anyone who has been in this business for longer than five minutes knows that the reliability of software is dependent upon many things, not the least of which is the programmer's understanding of exactly how that software works. — Bonnie Baker, Bonnie Baker Corporation “Thus came those who tutored. They first learned their lessons ‘the old fashioned way — they studied and applied IT’ and then they shared.”

5. Application Programs on the Mainframe

6. SQL and Your Program DBAS Database Functions Buffering DSNDBM1 APPLICATION PROGRAM • SQL Flow – Pass 1: Program to RDS to DM to BM to PDA (VSAM Media Manager) • SQL Flow – Pass 2: BM to DM (Stage 1 processing) to RDS (Stage 2 processing) to the Program Data Manager Stage 1 predicatesIndexable predicatesLockingVarious datamanipulations Relational Data System Stage 2 predicatesSQL statementcheckingSortingOptimizer Buffer Manager Data movementto and from DASDBufferpools Row-level Orientation Physical Data Access Set-level Orientation

7. SQL and Your Program • NOTES: • The flow of SQL from an application program goes something like this: • It enters the RDS, which checks authorization, resolves data element names, checks syntax, optimizes the SQL and generates an access path. • RDS then passes the SQL to the DM. • The DM analyzes the request for the data (table or index rows) and then calls the BM to satisfy the request. • If the requested data is in the buffer pools, it accesses the data and sends it back to the DM. • If the data is not in the buffer pools, it calls the VSAM Media Manager, which reads the data and sends it back to the BM, which then returns it to the DM. • On this second pass into the DM, as many predicates are applied as possible to reduce the answer set. This is where only Stage 1 predicates are applied. • Next, the RDS receives the data from the DM again and it applies the Stage 2 predicates, does any applicable sorting and passes the data back to the application. • So what Bonnie Baker is telling us is that if we try and use only stage one predicates, we will not incur the extra overhead of the Stage 2 Predicate process done in the RDS. Let’s take a look at what our MRI of an application program shows.

8. Stage 1 Predicate & CPU Consumption

9. DB2 Resource Consumption for Stage 1

10. RDS Resource Consumption for Stage 1

11. Stage 2 Predicate

12. DB2 Resource Consumption for Stage 2

13. RDS Resource Consumption for Stage 2

14. RDS Stage 2 System Services Module

15. Stage 1 Stage 2 % Variance DB2 System Services 51.86% 54.54% 5.2% DSNXGRDS 23.03% 26.74% 16.1% Stage 1 vs. Stage 2 Predicates Resource Consumption Table Obviously there is much more than this that contributes to the overhead attributed to Stage 2 Predicate processing, especially since there are a variety of Stage 2 Predicates, some BAD and some not so BAD. For a better insight into these, see Bonnie Baker’s “Predicate Evaluation” series in DB2 Magazine.

16. Indexes – Clustered and Not • By using indexes, access to requested rows of data in a table is usually much faster. • We will now look at a couple of general types of Indexes, clustered and non-clustered. • A Cluster ratio is an indication of the degree to which table data in relation to an index is clustered. • A higher cluster ratio means that the rows are ordered on the data pages in index key sequence.

17. Indexes – Not Clustered

18. Indexes – Not Clustered…continued

19. Indexes – Not Clustered…continued

20. Indexes – Not Clustered…continued Indexes – Clustered and Not…continued

21. Indexes – Clustered

22. Indexes – Clustered…continued

23. Indexes – Clustered…continued

24. Indexes – Clustered…continued Indexes – Clustered and Not…continued

25. Indexes – Clustered and Not…continued Resource Consumption Table Cluster Ratio 0% Cluster Ratio 100% % Variance Ave. Execution Time .0015 .0010 33% Bonnie Baker has several anecdotal papers dealing with the use of clustered versus non-clustered indexes. Her articles “Myths of the CLUSTERRATIO” and “ Two ways of using an index – the Normal way and the Aunt Louise way” are examples.

26. Synchronous and Asynchronous I/O; Singleton Select and Cursor • Bonnie tells the story of building a patio out of bricks at her home in Florida and uses her 2 children as deliverers of the bricks to her as she needs them. • Of course the bricks are dropped off in the front of the house and she will be building the patio in the back . • She asks her daughter, Beth, to bring her some bricks and she goes to the front of the driveway where they are and picks one up, walks back to where Bonnie is working and gives her one brick and returns to what she was doing . • Bonnie puts the brick in place and asks Beth to bring her another one. • Bonnie waits for Beth to return with the brick, one again. • Bonnie and Beth were working in sync, analogous to DB2 synchronous I/O, with DB2 returning a row on a page.

27. Synchronous and Asynchronous - Singleton Select • In the first computer program to help illustrate this, we read a small 2000 row table using a singleton Select. The table has no indexes. We read the rows for the state of Alabama, which number 99, but do this by reading each VENDOR-ID that has a state of Alabama.

28. Synchronous and Asynchronous I/O - Singleton Select…continued

29. Synchronous and Asynchronous I/O - Singleton Select…continued

30. Synchronous and Asynchronous I/O - Cursor • Bonnie now continues her patio building story by bringing in her son Scott. She asks him to get her bricks and Scott goes and gets a wheelbarrow from the garage and fills it to its capacity, which is, totally by chance, 32 bricks. • He then delivers them to Bonnie and goes and gets 32 more. • Bonnie now has these 32 bricks at her fingertips and proceeds to put them in place, one at a time. She no longer has to wait for more bricks to arrive and can continue her work. • It took Scott a bit longer to bring the bricks to Bonnie than Beth, but he would bring 32 at a time, averaging far better per brick than the one brick method used by Beth. • When Scott went for the second 32 bricks, he was doing asynchronous I/O, out of synch with Bonnie.

31. Synchronous and Asynchronous I/O - Cursor…continued • In the next computer program to help illustrate this concept, we read the small 2000 row table again using a Cursor with Fetch. The table has no indexes. We read the rows for the state of Alabama, which number 99, but do this by using a Cursor and having the WHERE clause with VENDOR-STATE equal to the state of Alabama.

32. Synchronous and Asynchronous I/O - Cursor…continued

33. Synchronous and Asynchronous I/O - Cursor…continued

34. Synchronous and Asynchronous I/O;Singleton Select and Cursor…continued Singleton Select Cursor/Fetch % Difference Synchronous Asynchronous Run Time 3.93 sec. .23 sec. 94% SQL Ave. .0127 sec. .0002 sec. 84% Exec. Time Bonnie Baker has written many articles dealing with DB2 performance. For further details on her “pearls of wisdom” see her web site at “http://www.bonniebaker.com”.

35. Summary *To Developers and Tutors, a quote for you: “Tis God gives skill, But not without Men’s hands: He could not make Antonio Stradivari’s violins Without Antonio.” – Stradivarius [McWilliams, P.; “DO IT! Let’s Get Off Our Buts”, Mary Books/Prelude Press, (1991). ]. * Thank you all. * I especially thank Bonnie Baker for her permission to use her insights into “visualizing” what she says. Rest assured, they are of a quantifiable value.