Subscribe
DB2 Express-C
Free book
Forum
ChannelDB2 Videos
developerWorks
Info Center
|
IBM DB2 is a full relational and XML data server. planetDB2 is an aggregator of blogs about DB2. We combine and republish posts by bloggers around the world. Email us to have your blog included. |
September 06, 2010
John P Kennedy |
Product management resources for newbiesEvery few weeks someone approaches me because they are interested in learning more about software product management. Here are 3 resources for product management newbies: 1. Pragmatic Marketing – As far as I can tell, Pragmatic Marketing’s framework the industry standard for high-tech product management. They claim to have trained over 60,000 product marketers and [...] |
Paul Mayhew |
sql for sampling data from tablesSome time ago, I had the requirement to sample data from a table to do validation work. Basically, we wanted to validate syncronized data with a base table, but could not afford to compare every row. We had to come up with a way to ‘sample’ the data. Each table we had to sample had [...] |
DB2 Express-C Team |
tiếng Việt - Getting Started with DB2 Express-C 9.7 The Vietnamese translation of the Getting Started with DB2… |
DB2usa |
Peter Vanroose's presentations about DB2 for z/OSHi DB2 user,Here are several Peter Vanroose's presentations about DB2 for z/OS available on Abis website: - DB2 9 New Datatypes and SQL Functions: Blessing or Curse? by Peter Vanroose - Experiences with stored procedures, triggers and XML on DB2 version 8 for z/OS<br /… |
September 04, 2010
Robert Catterall |
Thoughts on DB2 for z/OS Buffer Pool SizingA couple of years ago, on my Catterall Consulting blog, I posted an entry in which I urged mainframe DB2 people to take advantage of 64-bit addressing, a capability that debuted with DB2 for z/OS Version 8 and which allowed, among other things, the allocation of very large buffer pools. Now, I'm singing the second verse of that same song (actually, about the twentieth verse -- I have to keep repeating it): IF YOU HAVE BIG MEMORY, YOU SHOULD HAVE BIG DB2 BUFFER POOLS.Again and again I see it: a mainframe server (or logical partition thereof) that has a dozen or more gigabytes of central storage, and a production DB2 subsystem -- the only one on that z/OS instance -- that has a a buffer pool configuration with an aggregate size of a a gigabyte or less. Sometimes, pages of large and very heavily accessed tables and/or indexes are cached in a pool that has 80,000 buffers, or 40,000, or 15,000, or maybe just 10,000 buffers. The result? Way high rates of I/O activity, as in thousands of disk reads per second. That's a big drag on application performance, negatively impacting both throughput and CPU efficiency. When you have the server memory available to eliminate this undesirable situation, USE IT. So, first things first: get a handle on your buffer pool I/O situation. You can use a DB2 monitor to do this, but my preference is to use the output of the command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL. More specifically, I like to issue this command once, and then again an hour later. That way I get, in the output of the second issuance of the command, one hour's worth of buffer pool activity data. For each pool referenced in the output of the second command, I sum the numbers in five fields:
If you have a really high rate of disk read I/O activity for a pool and you want to make it larger, can you? That depends on whether or not the z/OS system in question has enough memory to accommodate a buffer pool size increase without negatively impacting other work on the server. How do you know that? A good way is to check the demand paging rate on the system. This figure indicates the number of times per second that a page, previously moved from central storage to auxiliary storage, is brought back into central storage to satisfy a program request. It's available by way of a z/OS monitor. The lower the demand paging rate, the less pressure there is on the system's central storage resource. What you want is for the demand paging rate to be less than 10 per second. You also want it to be more than zero, because a super-low demand paging rate means that the mainframe memory your organization has paid for is probably not being leveraged as it should be for system performance. If the demand paging rate is less than one per second, memory on your system is likely being underutilized. Enlarging one or more of your DB2 buffer pools is often a very good way to put underutilized server memory to productive use. In my experience, if there is one production DB2 subsystem on a z/OS instance, and if the size of that DB2 subsystem's buffer pool configuration is less than 10% of the amount of memory available to the z/OS instance, it's almost certain that the demand paging rate is very low. If you make a DB2 buffer pool (or pools) larger, check the demand paging rate again after the fact and make sure that it's still less than 10 per second (and note that an occasional incident such as a dump can cause the demand paging rate to briefly spike -- that's not a big deal). Something else: if a buffer pool's I/O rate is really high (as in a few thousand per second), and the pool is pretty small (e.g., 20,000 buffers or less for a 4K pool), and the system's demand paging rate is really low, don't just dink and dunk your way to a larger buffer pool configuration. In other words, don't add 1000 buffers to a 15,000-buffer pool. Go for way bigger. Think in terms of doubling the pool's current size, or tripling it, or even quadrupling it. Later, after it's bigger (like, 80,000 buffers or more), you can think about growing it in smaller chunks, percentage-wise (e.g., maybe make it 50% larger). Whenever you take any action to enlarge a buffer pool, follow that with the -DISPLAY BUFFERPOOL commands that I mentioned previously, to gauge the effect of the size increase on the pool's disk read I/O rate. When you do that, in addition to checking on read I/Os per second, look to see if the number of of synchronous reads associated with sequential access (SYNC READ I/O(S)) went down. When a buffer pool is really undersized relative to the volume of requests for pages in objects assigned to the pool, it may be that when a set of 32 pages is brought into the pool via a prefetch read I/O, some of those pages are flushed from the pool before the requesting application process can access them. That drives the number of synchronous reads related to sequential access higher (because those flushed-out pages, when requested, will be read into memory individually). With a larger pool, page residency time increases, and it's less likely that prefetched pages will get flushed before they are accessed for the requesting application process. Another thing: once an individual buffer pool (as opposed to the whole buffer pool configuration) gets to be pretty big (say, a gigabyte in size), before making it larger still consider the possibility of creating a new pool (maybe 25% or half the size of the big one) and moving some of the highest-activity objects from the really big pool to the new one. This is NOT a technical requirement, as a single 4K buffer pool can grow to a terabyte in size; rather, it's an opportunity to split some objects out in a way that will provide you with more granular buffer pool statistics and a chance to more finely tune the overall buffer pool configuration. Just a thought. Finally, if your DB2 environment operates in data sharing mode on a parallel sysplex mainframe cluster, keep in mind that an increase in the size of a buffer pool (and you usually want a given pool to be the same size on all members) may make an enlargement of the associated group buffer pool highly advisable -- this mainly to ensure that the group buffer pool will have enough directory entries to prevent directory entry reclaims, which get in the way of top performance. A long time ago, when the earliest users of data sharing asked for a formula to help with group buffer pool sizing, I came up with a pretty simple one: add up the size (in MB) of the local pools, and divide that sum by three (this assumes the default ratio of five directory entries for every data entry in a group buffer pool). Fifteen years later, that simple formula still works really well. So if, in a four-way data sharing group, you want to grow BP5 to 120,000 buffers (480 MB) on each member, a good size for GBP5 would be: (480 MB X 4 members) / 3 = 1920 MB / 3 = 640 MB If GBP5 is currently smaller than that, take it up to 640 MB (or more) before taking BP5 to 120,000 buffers. I hope that this information will help you to assess your buffer pool configuration from a performance perspective, and I hope that you'll grow your DB2 buffer pools to improve throughput and CPU efficiency (assuming that you have enough memory for this on your system -- and I'm sure that a lot of you do).  |
September 03, 2010
Henrik Loeser |
Obtaining information about the installed DB2 version (level)Somehow, I had a mental blackout earlier today when I tried to obtain information about the installed DB2 version and couldn't remember the command. Of course, you can always look into the db2diag.log file. Because DB2 writes an entry with version information and data about the system it is running on to that diagnostic file whenever it starts up. However, the command I couldn't come up with is |
IBM Press Room |
IBM Paves the Way for Smarter Products and Services Innovation in the Consumer Electronics IndustryAt IFA 2010 IBM will showcase how it can help Consumer Electronics (CE) manufacturers shift to a services-based business model to provide better device management, enable the quick development and launch of new value-added service capabilities and find new revenue streams. (IBM is in the IFA TecWatch area, Hall 8.1, Booth #15)
|
||||||||||||||||||||||||
September 02, 2010
Susan Visser |
Certification Crammer Courses at IODIBM software certification exams will be available to registered attendees throughout Information On Demand 2010. Attendees may take up to three IM (including SPSS, Cognos and ECM) exams at no charge; a discounted fee of $100 USD for subsequent exams will apply. See the certification site for further details. Don't miss this opportunity to validate your skills and demonstrate your proficiency in the latest IBM software technology and solutions.
To prepare yourself to pass the exams, I have a few suggestions for you: 1) Read through a previous blog entry that I did called Certification 101. 2) Buy or borrow a Study Guide that you should read before the conference. These books are available electronically through Books 24x7 or Safari Books Online. The suggested books are: DB2 9 System Administration for z/OS: Certification Study Guide: Exam 737 Judy Nall DB2 9 Database Administration Certification Study Guide for Exam 732 DB2 9 Advanced Database Administration Certification Study Guide for Exam 734 DB2 9 Fundamentals Certification Study Guide DB2 9 Database Administration Certification Study Guide for Exam 731 DB2 9 Database Administration Upgrade Certification Study Guide for Exam 736 Note that all of these books were written by members of the exam development team and each chapter contains sample questions along with answers and explanations. 3) Show up to the conference early and attend a full day Sunday class that was specifically created to give you the information you need to pass the exam. This year we have three classes scheduled: Roger Sanders: DB2 9.7 for Linux UNIX & Windows DBA Certification Crammer (Exam 541) This training is designed to introduce the student to the concepts a test candidate must know in order to take and pass the DB2 9.7 DBA for Linux, UNIX, and Window certification exam (Exam 541). The material for this course is aligned with the 541 exam objectives. Susan Lawson: DB2 for z/OS DBA Certification Crammer (Exams 730 and 732) This is a high-level certification preparation course intended to help you take DB2 9 Family Fundamentals exam (730) and DB2 9 for z/OS Database Administration exam (732). Passing of these exams will result in certification as an IBM Certified Solutions Expert - DB2 V9 z/OS Database Administration. Participants are expected to have fundamental knowledge of relational principles in addition to applied working knowledge of DB2 z/OS V8 and basic knowledge of V9 features. This will be a lecture-only class with no labs. This session offers an intense, overall review of those areas that will be on the certification exams (both the 730 -- DB2 9 Family Fundamentals and the 732 -- DB2 9 for z/OS), providing a comprehensive review for those planning on taking the certification exams to become an IBM Certified Solutions Expert - DB2 9 for z/OS Database Administration. Judy Nall: Exam 737 Crammer This one-day crammer class reviews topics on the:
Susan |
dW Downloads |
Cloud: DB2 Enterprise Developer EditionUse DB2 EnterpriseV9.7 component of IBM Database Enterprise Edition on the cloud. DB2 Enterprise is meant for designing, developing, prototyping, and testing DB2 database applications. DB2 Enterprise contains advanced data management features and is designed to meet the most demanding database server needs of mid-size to large-size businesses. |
|
dW Articles |
Monitoring in DB2 9.7, Part 1: Emulating data reset with the new DB2 9.7 monitoring table functionsThis article describes a simple method for performing a per-session reset of monitoring data returned from various monitor table functions in IBM DB2 for Linux, UNIX, and Windows. The behavior is functionally equivalent to the reset capability available in the DB2 system monitor snapshot APIs. The article includes a download with two scripts that you can use to implement this method for a number of the new monitoring table functions introduced in DB2 Version 9.7. The focus of the article is the new monitor table functions introduced in DB2 Version 9.7, but you can easily apply the same method to other table functions in DB2 that report monitoring data. |
|
Susan Visser |
Celebrate an AuthorDid I ever tell you that I love my job? Part of why I do is because I'm surrounded by some of the most creative people in the world! Yes, all these developers in the Toronto are very creative, but right now I want to mention authors.
I've met famous authors: Malcolm Gladwell and Steven Covey, famous people who became authors: Devon Harris (bobsled olympian) and Michael Eisner (Mr. Disney) and just plain interesting people who have written books: Mark Jeffries, Chris Anderson, Magnus Lindkvist, and Chad Fowler. Meeting these people is the icing on the cake, but I've noticed that they have the same enthusiasm and drive as the lesser known authors who I work with to get books about DB2 and other IBM products produced.
Let's start with some of the most prolific authors who I know. Enthusiasm is number one, but one of the things that sets these people apart from others is their desire and willingness to teach other people the tricks of the trade:
Then there are the gold consultants. These are people who don't work for IBM, but may as well. They are essentially IBM's cheerleaders. I should say that Roger, Craig, and Rebecca listed above are not gold consultants, but they are definitely passionate about IBM's DB2 products!
And then there are the IBMers who are already so busy in their day jobs but still manage to find time to write a book! Paul, George, and Bill also fall into this category:
I should also make a note here to congratulate those authors who are writing in English even though English is their second language!
Finally, there are the people who want to be authors who find the time to create a proposal and send it to me. I hope that these people one day find the right topic to become their book. These people tend to review many of the books that I work on and are amazing reviewers.
What makes a good author? Writing a book is very hard work, so the two primary things that are necessary for success are willingness to work hard and a very strong passion for writing about the chosen topic. Roger Sanders is writing his 21st book and this one is about how to become an author of technical articles and books. If you are interested in writing, I high recommend that you start with Roger's book: From Idea to Print.
I wasn't able to list all the authors who I admire in this post, because believe me, there are many more. I appreciate all the experience, passion and hard work that you pour into your projects. I hope you all have many more books in you and that you encourage others to follow your paths.
If you will be at the IBM Information on Demand Conference this October, please stop by the many book signing sessions that I have planned to congratulate an author on a job well done. To find the book signings that are scheduled, search on "signing" when logged into the Smart Site. You should find 15 entries.
Susan |
September 01, 2010
Leons Petrazickis |
Looking for help with DB2 support in MediaWikiMediaWiki is the PHP application underlying Wikipedia and other sites. Over the past couple years, I’ve spent some of my spare time to add DB2 support to it. Here’s where things stand now: Working: Installing on DB2 using the old installer Broken: Creating and editing articles Not implemented: Search Not implemented: Installing on DB2 using [...] |
|
Paul Mayhew |
Inline database shell scripts using awkOne of the things I find very powerful in my day to day work as a DBA, is the use of inline shell/awk scripts. I use this technique when I need to do the same thing to many objects. I often use it when I want something quick and dirty or create a base script [...] |
Data and Technology |
Data Privacy PoliciesDo you know what data privacy policy your service providers follow? Continue reading
|
August 31, 2010
Pat Fletcher |
New Redbook for Informix - IBM Informix Developer's HandbookHi,Glad to report on a new Informix Redbook: IBM Informix Developer's Handbook: http://www.redbooks.ibm.com/redpieces/abstracts/sg247884.html It's a great guide to the many different interfaces for application development on Informix. It covers Java, ESQL/C, OLE DB, .NET, PHP, Ruby on Rails, DataBlade®, and Hibernate. Check it out! |
Dave Beulke |
Combining Multiple MQTs to Improve SQL Optimization for DB2 Data WarehousingMaterialized Query Tables (MQTs) are really great for DB2 data warehousing situations and also for all types of reporting applications. Combining multiple MQTs is one technique that is good for providing all types of reporting capabilities and answers. By combining multiple MQTs, the DB2 SQL optimizer can sometimes automatically rewrite the queries from [...] |
ChannelDB2 Videos |
DB2 pureScale Demo This demo shows the functioning of a DB2 pureScale cluster. You can see how the system responds to increase in demand, unexpected failures, and addition/deletion of cluster membe… |
|
Henrik Loeser |
Food: Where business and life meetWorking at home has advantages. There is the "lunch is ready in 5 minutes" call to remind me to get ready to make the few meters from my desk to the table on time. Sometimes, those instant messaging windows pop up (US East Coast wakes up or urgent European business) and it becomes a struggle to be at the table when food is served. And I usually don't help to prepare lunch during the work week. |
DB2utor |
Sorting Keeps Getting FasterYears ago when I was a DB2 database consultant, I went into a company that had turned off all ability to sort in DB2. The... |
Radim Kolar |
DB2 9.7.2 packaged for Ubuntu 10.04IBM DB2 9.7.2 was added to Canonical Partner Repository. You can install it into Ubuntu Lucid Lynx 10.04 by following these instructions. It is very simple way to get DB2 going and and it is fastest way how to get started with DB2 on Ubuntu. Another good options for easy DB2 deployments are Dubuntu and … |
August 30, 2010
Matthias Nicola |
XML Construction in Views and User-Defined FunctionsMany enterprises hold significant amounts of relational data in their databases. New applications and integration scenarios often require this data to be communicated in the form of XML messages, e.g. in service-oriented architectures (SOA), web services, application integration (A2A), or data exchange with internal and external parties (B2B). Fortunately, the SQL language contains a set [...]<img alt="" border="0" height="1" src="http://stats.wordpress.com/b.gif?host=nativexmldatabase.com&b… |
|
DB2 Express-C Team |
pureScale chat on Tuesday August 31 There's an upcoming chat with the lab tomorrow:DB2 pureScale - Always-on, Elastic Database Cluster |
|
DB2 Express-C Team |
ChannelDB2 surpasses 3000+ membersThe membership of ChannelDB2 now exceeds 3000 members, of whom over 60 joined in the past week. The site is designed to help DB2 professionals share fun and instructional videos, blogs, and discussions. |
|
Henrik Loeser |
Beer or wine, elements or attributes? Designing XML schemasImage via WikipediaOne of the questions asked repeatedly during DB2 pureXML workshops is about the use of elements and attributes when designing XML documents/schemas. What is better, using elements or attributes? The answer given is that it depends on the data, the requirements, and other factors. Instead of going deeper into the reasoning for elements and attributes, I am referring to the |
|
Paul Mayhew |
db2 batch scripts – tips and tricks – part 2I’ve always been a little frustrated with db2 when running scripts and command lines with fact that when a command would run like insert, update or delete, you never knew how many rows you affected. Some GUI products will tell you, but with command line it would just come back as being successful (or not) and it [...] |
|
DB2usa |
DB2mag Spring and Summer 2010Hi DB2 user,Spring and Summer 2010 issues of DB2mag are available on line: - DB2mag Summer 2010 - DB2mag Spring 2010 - <a href="http://www.ibm.com/developerwork… |
August 28, 2010
|
Susan Visser |
The Mainstreaming of eBooksThis past February, in my blog titled Year of the e-Book, I highlighted a variety of online resources for avid readers and predicted that this year is the year of eBooks. Low and behold -- I wasn't too far from the truth! Today, I am surrounded by eBook enthusiasts. I personally use a Kindle; my son, a Sony. Just last night, my visit to the neighbourhood bookstore reminded me just how much I can save if I buy new releases in their electronic format. I'd like to share some resources that I think you will really benefit from. If you haven't read an eBook before, now's a good time to change that. Books published through IBM Press, MC Press, Apress, and Packt can now be purchased in the traditional print, Kindle, or PDF format, in addition to online book engines such as Safari Books Online and Books 24x7. The beauty of this phenomenon is that, in order to access eBooks, you don't have to own a reading device. Smartphones (equipped with apps) and computers (armed with software programs) serve as perfect substitutes for optimizing your experience. One of the many benefits of accessing Books 24x7 is that you can read an electronic book offline. All you have to do is download it! Upon subscribing to the portal, you will be able to search for keywords across hundreds of titles, bookmark books, keep track of what chapter you are on and more. Even if you prefer reading a printed version, the engine will help you filter through an enormous amount of content to find the most appropriate title of interest. IBM employees can access Books 24x7 via employee home pages. This portal is now offered to many schools as well. IBM Press books are on this site, as are books from many other publishers. Safari Books Online is another electronic book engine and, essentially, the same as Books 24x7. In addition to IBM Press books, Pearson Education and O'Reilly titles are also available. If you are looking for a personal subscription, Safari's prices are quite affordable. At times, if you're lucky, you'll even get a free trial. Electronic Book Readers These handheld devices were designed specifically for reading books. This means, they are roughly the same size and weigh as much as a book. However, the text is easier on the eyes. Best of all, they are portable! The best known ones in the market at this time are the Apple iPad, Amazon Kindle and Sony eBook Reader. A variety of publishers are already presenting me with e-book strategies for future publications. This new channel will make books available immediately in an online format of some kind. Printed copies will be available in limited quantities and/or if a request has been placed. This will reduce, if not eliminate, the need for publishers to have warehouses crowded with printed books, thus remaining environmentally friendly. The biggest benefit of all, however, is that publishers will now be able to more easily update books whenever necessary. This will drastically change the rate at which updated knowledge and information becomes available. Have we reached the era where readers only want online books? In my humble opinion, not yet. Perhaps there will always be a market for printed books (if you could only see my desk, you'd know just who makes up a good chunk of that market!). My Christmas present, the Kindle, constantly reminds me of the benefits of reading using such a device. Without a doubt, the Kindle is just the beginning. Everywhere you turn, a different and, possibly, more innovative device is waiting to be explored: Sony, Nook, iPad, apps on iPhones and Blackberries, and the list can go on. Frankly, it will only get harder to keep up with all of them. To sum it up, the mainstreaming of the phenomenon of eBooks is showing no signs of stopping so get on board today. You will be glad you did. Susan
|
August 27, 2010
PlanetDB2 News |
Welcome, Scott and PaulPlanetDB2 now syndicates DB2Night Show Replays and Paul Mayhew's DBMSguy Blog.If you'd like us to include your blog, please drop us a line at join @ planetdb2.com.  </d… |
|
Radim Kolar |
Selt Tuning Memory ManagerOne of DB2 9 features heavily marketed by IBM is without doubt STMM. When i heard about it first time, i didnt liked that idea. What a nonsense, database administrators trained performance tuning for years and now they can be replaced by some program? What can program know about database tuning. After thinking about it for a while - if we take human knowledge about db tuning and put it into program it might do something. But i was kinda skeptic because all previous attempts to do auto tuning<… |
|
Data and Technology |
Log Buffer #201, A Carnival of The Vanities for DBAsLog Buffer is the DBA community’s Carnival of the Vanities-style blog of blogs about the database world. It is the place DBAs and others interested in databases come to read and write the news of what their colleagues around the world are working on—how they’re solving problems, using their skills, and what they’re thinking about. Continue reading |
DB2Night Replays |
The DB2Night Show #27: DB2 in the Cloud - Tech Details - Part 2DB2 LUW in the Cloud - Technical Details - Part 2Your DB2 is in the Cloud, but where? In episode #27, special IBM guest Mark Wilding, STSM & DB2 Cloud Architect, covered many technical details and considerations for running DB2 in the Cloud. This show was Part 2 of a 2 part series. In part 2, Mark shared with us performance and security considerations, storage configurations and models, and other tips to optimize success with DB2 in your Cloud. As with Part 1, studio audience members rated this episode very highly. Watch this replay and see what you missed, or watch it again for review... |
IDUG News |
IDUG Loses a Supporter
Anne was a long time IDUG volunteer and advocate. She supported our organization by presenting at our conferences, participating on the North America Con… |
IDUG Loses a Supporter