Up to Blog Posts in Mainframe 2.0 Community

Mainframe 2.0 Community

6 Posts tagged with the dave_beulke tag
Dave Beulke

Index design is one of the most important items or maybe the most important item in database design. Sometimes it is better to enhance the database with more indexes instead of trying to fix the application SQL. Tuning SQL is always good but tuning many SQL statements takes too much time. When time is tight most of the problem application SQLs can be instantaneously fixed through better indexes.


DB2 Version 9 indexes have several new options that help performance. First is the enhancement that I have written about before in the blog (Expression Performance) is Index on Expression. The Index on Expression enhancement alone can make a huge performance impact by tailoring the index to the application WHERE clause predicates. This customization of an index design can be a huge performance improvement for many applications, especially applications using DATE, TIME and other SQL functions within the SQL.


Next, is Index Compression which cuts disk requirements for the index structure dramatically. Most of the time compression can yield over a 50% savings and sometimes over 80%. This is especially important for large BI/DW databases indexes that can sometimes be terabytes before compression.


Next, index page sizes can be set to a larger page size that helps reduce the overall number of pages and the number of page splits within the index structure. Minimizing page splits helps avoid contention, helps cache all the index entries and keeps index value entries near each other on the same index page.


Another improvement for indexes that have ascending/descending keys is the new key randomization feature. Using the RANDOM option during the CREATE or ALTER of the index causes the index entries to be stored at random places in the index tree. This random inserting cuts down on index contention and is especially important for robust data sharing systems that are inserting many ascending/descending index keys.


So next time an application has SQL problems take a look at improving or defining a new index on the database. You might improve a huge number of applications instead of only a single application program and have time left to focus on other issues.


120 Views 0 Comments 0 References Permalink Tags: dave_beulke, db2_9, performance, index, index_design, database_design
Dave Beulke

It seems that last week’s blog struck a chord with many readers. Many people commented on how their management is increasingly out of touch with the mainframe. Also, comments also stated that the System Z environment is really processing almost all of the transactions in their company and how all the Windows platform systems continue to have scalability issues.


These comments are great, please keep them coming because it only reinforces the success story of the mainframe System Z. Although the mainframe revenue for IBM suffered in 2009 because of its upgrade cycle, the introduction of the new z10 System platform continues to be the best open system. Yes that is correct, the mainframe is the most open system available because it runs all types of workloads, of course the legacy standards of Assembler, COBOL, PL1 etc. but also C++, C#, java, PHP and the rest of the languages that run on UNIX and Windows boxes.


Also, some people are starting to run “virtualized” windows on the mainframe environments. PCWORLD highlighted this capability early last year (http://bit.ly/25OhnD) and with the System Z speed, scalability and network the mainframe continues to be the best solution for all types of workloads. A nice short demo of z/Vos is on YouTube http://www.youtube.com/watch?v=I_Bnx84hcDA along with many other videos that you can show to your iPhone obsessed boss that consolidating those hundreds of MS SQL Server instances is also possible.


The story of virtualization continues to drive UNIX consolidation to the mainframe. Just last year Allianz consolidated 60 servers into a single mainframe saving substantial operating, licensing and energy costs while improving scalability. This story detailed in this ComputerWorld article (http://bit.ly/9Yg9H7) is being repeated at many companies as the mainframe IFL, zIIP and zAAP specialty engines continue to bring processing power at PC or minimized prices. This consolidation activity has a very short term return on investment as these efforts pay for themselves usually in the first year and reduce power consumption dramatically making it a “green” saving the planet project.


So next time the hundreds of windows or UNIX server configurations need an OS, database or other software upgrade make sure to mention how System Z is saving other companies time and operating costs and overall costs through consolidating these environments to the best cost alternative the mainframe System Z.

177 Views 0 Comments 0 References Permalink Tags: dave_beulke, system_z, virtualization, iphone
Dave Beulke

Within the recent IBM Z Summit road show, there were several presentations detailing the mainframe platform advantages over UNIX and Windows platforms such as the lowest total cost of ownership, the best availability and unparalleled scalability. These presentations cut through the rumors with detailed facts and figures of the different platform configurations. Download these presentations and distribute them to your management for a little reminder why the mainframe continues to be the best platform for your enterprise applications.


The Windows and UNIX platforms proponents always discount and minimize the total cost of ownership, availability and scalability topics. It is our duty to periodically remind management of the extra costs of these UNIX and Windows systems with their huge power consumption costs, software license fees, and software maintenance costs of working with several hundred or thousands of disparate systems. The mainframe quietly continues to process the majority of the transactions at the Fortune 500 companies and everyone, especially younger management types that think that the world can run on an iPhone, needs to understand that the System Z infrastructure is the best backbone for any company.


The System Z mainframe is also evolving since it now has all of its specialized processors such as the IFL, zIIP and zAAP specialty processors for reducing overall operational and licensing costs. These specialty processors along with a new System Z smaller configuration offer a single small platform to consolidate any number of UNIX workloads into a single footprint with a smaller greener energy footprint and better licensing configuration.


The presentations detailed benchmarks, licensing fees and labor costs of various mainframe versus UNIX platforms configurations. The figures showed it takes sometimes double the number of processor cores on a UNIX configuration to start to scale out a configuration. Then even more UNIX processors to achieve transaction rates that are still only performing one-fourth of what the mainframe System Z executes. These UNIX systems are also dedicated to the production transaction environment with no thought of supporting testing, QA or failover facilities that have yet to be priced or considered and come standard within the System Z environment.


System Z also continues to grow because of its faster chips. Ask any PC or UNIX platform personnel “what platform has the fastest clock speed processors” and you will quickly find out who keeps up with the industry information. The chip clock speeds of the System Z and other IBM platforms have seen their chip speeds improve like the rest of the PC industry. In fact, the System Z z10 chip operates at 4.4 GHz and comes in a 64-way quad core configuration that can speed up any application performance problem. This is almost twice as fast as the HP Superdome processors and a third faster than the Intel Nehalem chips.


So the mainframe continues to lead the industry. Does your management know the cost savings and performance figures of System Z? Tell them and show them the presentations before someone tries to “replace the mainframe” again with a more troublesome, power hunger, bad performing clustered iPhone configuration.

95 Views 0 Comments 0 References Permalink Tags: dave_beulke, system_z, total_cost_of_ownership, ifl, ziip, zaap
Dave Beulke

Within the IBM Z Summit road show that was recently in Washington DC, the DB2 presentations highlighted a number of DB2 utilities improvements. All of these DB2 utilities improvements are integrated into the new DB2 10 but there were many that can be incorporated into DB2 9 and some even into DB2 8 for z/OS.


One of the biggest performance improvements has come for the SORTBLD phase for indexes. Since many indexes have small SECQTY allocations, the APAR PK60956 (V8 & V9) helps DB2 with the serialization and their possible write delays. This APAR can speed up some LOAD, REORG, REBUILD, and RECOVER processes by as much as 20 times with indexes that have small SECQTY allocations.


APAR PK61759 When applied to V8 and V9, APAR PK61759 helps within the DFSORT phase of the LOAD and REORG facilities to reduce CPU by 10% within the RELOAD phase of these utilities.


Another APAR, PK74993 for DB2 9, improves the performance of the COPY utility. This improves the efficiencies of handling small data sets and can result in a 20% elapsed time improvement for jobs that copy a large number of small datasets to tape.


Also talked about were a number of other APARs that will help within your utility processes. All of them help performance for their unique situations. They were:


  • APAR PK63324 & PK63325 for DB2 9 helps by copying the compression dictionary instead of recreating it with the new REORG LOAD COPYDICTIONARY keywords.


  • When using the BACKUP SYSTEM or disk mirroring, APAR PK41711 for DB2 9 runs a data consistency checks without impacting overall performance


  • APAR PK80304 for DB2 V9 helps avoid the check pending status after a PIT recovery of a referential group. This can save and avoid enormous amount of time verifying all your RI relationship objects especially for SAP and PeopleSoft database applications.


  • APAR PK45916 for DB2 version 8 and PK41899 for DB2 Version 9 provide greater utility parallelism with SORTNUM elimination. This is a big performance boost for utility sort processing.


  • For those who use LOAD/UNLOAD LOBs, APAR PK75216 improves performance dramatically when using PDSs by as much as 56% for UNLOADs and 93% for LOADs.


  • For UNLOAD processes from multi-table table spaces, APAR PK77313 for DB2 V8 and V9 a uses the database DBD instead of a DB2 catalog lookup. This can especially improve performance for large databases for software packages.


Of course, there may be a number of considerations and some of these may be older APARs, so research if your system needs them before putting them on your system. Also check out the ZSummit Roadshow when it comes to your town.


167 Views 0 Comments 0 References Permalink Tags: db2_9, z/os, system_z, dave_beulke, z/summit, performance
Dave Beulke

Today, IBM announced the start of its DB2 10 for z/OS Beta program. Companies can get involved in the beta program and get an early competitive edge from the many performance, scalability, security, application integration, XML, SQL and Data Warehousing features.


Check out the some of the DB2 x information and testimonials at

http://www-01.ibm.com/software/data/db2/zos/testimonials.html .


Also check out and register for the z/Summit road shows at

http://www-05.ibm.com/se/news/events/db2roadshow/, where further information about DB2 X is discussed.

 

 

Leave a comment below. If you don't see the Add a Comment button, please log in or register.
155 Views 0 Comments 0 References Permalink Tags: db2_x, beta, z/summit, xml, sql, data_warehouse, dave_beulke
Dave Beulke

[Ed. Note: We're pleased to be able to feature Dave's blogs in the community. This post is actually the 16th in a series on performance features in DB2 9 for z/OS. You can read the previous entries in the series at www.davebeulke.com.]

 

Some of the new tablespace partitioning choices and features in DB2 Version 8 and Version 9 for z/OS are making everyone realize there are a number of table design choices to fit any application access type.

 

In DB2 V8, the first of the tablespace definition partitioning options were enhanced with partitioning and clustering being separate options.  This enhancement brought out the options for the table clustering to be one way and partitioned in another direction.  For example, the separation of partitioning and clustering lets you quickly partition by time and have the clustering be by customer id within each partitioning.  This separation of clustering and partitioning provides a variety of choices and solutions to tough application access designs.

 

Now with the DB2 V9 for z/OS, the Universal tablespace and the partition by growth (PBG) tablespace provide even more design enhancements, flexibility and manageability.  The PBG and Universal tablespaces have become the defacto standards for software packages where the table population is unknown and the number of tables is too big for detailed analysis until the application usage trends are fully realized.  The PBG allows safety of operation since it can expand to match the application needs.  These options let the DBA define the tablespace for the variety of software packages, let them grow and provide good performance by separating the I/O into many partitions.

 

The partition-by-growth PBG tablespace provides even more flexibility by, as the name implies, growing a new partition as the number of rows increase in the table.  This is great for robust applications that are continuing to grow their rows.  The PBG tablespace is also great for segmenting these new rows away from the old rows within the tablespace.  This can be a tremendous benefit for focusing the  I/Os within the system into a portion of the tablespace and avoiding the scan of an entire tablespace.  The focusing of the I/OS also enhances the benefits from the DB2 buffer pool and allows more focused data and index entries to be cached within this vital memory resource.

 

Each tablespace type has different number of data sets and different size parts where DB2 I/O and extent management need analysis to be properly optimized for the applications.  With all these tablespace design choices, make sure to leave analysis time to pick the best configuration and optimize your tablespace choice for the best application performance.

 

Post a comment below. Remember, you'll need to  log in or register if you don't see the Reply button.

223 Views 0 Comments 0 References Permalink Tags: db2_9, partitioning, partition_by_growth, pbg, buffer_pool, i/o, dave_beulke


Popular Blog Posts