 |
|
db2Dean’s 2009 IOD Highlights
|
|
The Information on Demand (IOD) conference was great again this year. In addition to being able to renew my deep and meaningful relationship with Marilyn Monroe I learned lots of great things about DB2, especially the newest versions. In this month’s article I’ll list the variety of random gems that I found. There was much more information than what I can show here, but I have still included several useful items. By the way, I was on the IOD site yesterday and noticed that most of the presentations are there now. If the one that you wanted was not there before, it is probably on the conference web site now. If you would like to have someone bring any of these presentations to you, just let me know. I look forward to seeing you and Marilyn again at next year’s conference!
|
|
|
Using DB2 Table (Range) Partitioning
|
|
|
You have heard a lot about DB2’s Range Partition feature that allows data in a single table to be placed in multiple tablespaces and that allows more scalability, elimination of unneeded partitions for individual queries, and the ability to quickly roll-in and roll-out partitions of data. There are plenty of resources (links are at the bottom) that describe what it is and tell you how to implement Range Partitions, but in this article, I’ll explore when you want to use Table Partitions and provide tips on getting the most out of them. It should be noted that almost all of the information in this article comes from the excellent presentation given by Christopher Tsounis, an IBM Executive IT Specialist at the 2009 North American IDUG Conference.
|
|
|
DB2 Clients - Which one is Right for Me?
|
|
|
I just want to connect to my database, so what should I use? I’ve heard of the Data Server Client, Administrative Client, Runtime Client, Data Studio, JDBC Driver, and other drivers. I’ve even heard of something called DB2 Connect. Which one is the best for me? Well, you’ve come to the right place. In this article I will try to sort this all out for you.
|
|
|
DB2 Infomercial
|
|
|
For a little levity (ok VERY little) please watch my DB2 infomercial at http://www.channeldb2.com/video/db2-infomercial or just click the link above. It includes a guest appearance by renowned pitch man, Chris Williams! If you have a moment, please click on the stars after the word "rating" and rate my video after you watch it.
|
|
|
New Features of DB2/LUW v9.7
|
|
|
On Friday, 19 June 2009, the latest version of “DB2 on Linux, UNIX and Windows” (DB2/LUW) was released. The biggest change to this release was the SQL Compatibility feature that allows many applications and scripts written for Oracle databases to now be easily be run with DB2 databases. Even though the SQL Compatibility feature is very important and is seeing the most hype, there are many other features that will help you on a daily basis. I will devote the majority of this article to those features, by providing a summery of the ones that I feel will be the most useful to the most people. Before upgrading your existing databases, you should also take a close look at the deprecated and discontinued features that I have listed at the end of this article.
|
|
|
Database Federation or Replication
|
|
|
Federation and Replication are topics that are easy to confuse especially for those who are just starting to investigate them. Sometimes even experienced customers can get them confused. This situation is not helped when you consider that our oldest replication tool uses federation under the covers. Because of this a lot of the replication documentation talks about configuring federation.
At a high level, federation and replication are tools that IBM provides that promote our Information on Demand strategy of helping customers view data wherever it is in the organization and to copy it to the most convenient locations if desired.
|
|
|
Data Studio Overview
|
|
|
By now you may have heard about Data Studio and its various components that can help a DBA get more database work done in a shorter time. If you have only been seeing it in dribs and drabs, then you are probably confused about it like it was until I took some recent training. For anyone who is working with DB2 or Informix databases, knowing about Data Studio will become more important as time goes on, because it will be the platform from which many of our database and development tools will be launched and integrated. By integrated I mean that each additional tool that you add such as Control-Center-like tools, database object change management tools, or data modeling tools will just be additional views and functions that get added to the same launch pad called Data Studio. For example, if you have ever used Control Center you almost always started by expanding the left pane to drill down to the object such as “table” with which you wanted to work. In Data Studio, all database related products will have a “view” called the Data Source Explorer.
|
|
|
DB2 Cost Saving Tips
|
|
|
More than ever companies are looking for ways to save money in all aspects of their business including IT infrastructure. There are several DB2 tools that can help you do exactly this, with some of them showing results right away. Just think what the boss will think of you when you show up in her office with a plan to reduce your database costs!
|
|
|
DB2 Built-in Performance & Health Tools
|
|
|
There are quite a few free tools built into DB2 for monitoring and tuning the performance and health of your databases. In fact there are so many that I often loose track of the ones that are available and in which versions they exist Therefore, I decided to write this db2Dean article about them to provide a summary. In this document I will list them and provide a short description about the tool and when it would be used. In later articles I will expand on individual tools or groups of related tools. This article only describes the tools. It does not tell you what you need to do to make your database run well. There are many articles about that and you can easily find them by searching Google.
|
|
|
High Availability Options for DB2/LUW
|
|
|
This article lists and briefly describes the options for increasing the availability of your DB2 on Linux, UNIX and Windows (DB2/LUW) systems through the use of redundant systems and clustering. Please contact me if you want to discuss any of them more in depth. This sort of technology is very appealing to many of us because we like to brag to our friends about the cool new technology that we are using. While there are many legitimate uses for this technology, I will to take a moment at the beginning of this article and rain on your parade. However boring it may be, following good practices in planning, building, maintaining and monitoring your systems yields the best results and is very cost effective. Since these are not the focus of this article I will not get into them here, but I would be happy to discuss them with you at length. I would also like to note that I consider these good practices to be a prerequisite to using any of the more advanced features for high availability, as they add complexity and can DECREASE availability if you have not laid a good foundation. However, once you have implemented good practices the following items can add that incremental availability that many systems need.
|
|
|
DB2 Options for Moving Data
|
|
|
Probably one of the subject areas that people ask me about most often is how to move their data from one system to another. Some want a copy of production data on a test system. Others need multiple copies of test databases so that different groups of developers can test without interfering with each other. Still others need to copy a tablespace or group of tables from one database to another. The good news is that there are several options and some will be better than others depending on what you are trying to accomplish. This list is intended to help you narrow your choices for moving data. Once you narrow the alternatives you will probably want to read more in depth about them in the Data Movement Utilities Guide and Reference manual or other links noted below. Several of these methods for moving data do more than what is described here, but I mostly just describe how they are used to move data.
|
|
|
Replication Options for DB2 and Beyond
|
|
|
There are many tools for replicating between databases, but which one is right for you. This article describes the various important considerations for replication tools and has a table that compares the various options for replication between a variety of IBM and non-IBM databases.
|
|
|
SMS, DMS and Automatic Storage
|
|
|
I often get the question about when SMS tablespaces should be used and when DMS tablespaces should be used. We also now have automatic storage that will pick SMS or DMS for you. There has been much written about what these tablespaces are, but in this article I will share my opinions of when to use each.
|
|
|
Lesser Known Features of DB2 v9.1
|
|
|
By now you have probably heard of the major new features introduced in DB2 v9.1 such as pureXML, Deep Compression, Self Tuning Memory Manager, Automatic Storage, LBAC and Table Partitioning. However, DB2 v9.1 also included many new features and changes that are quite useful on a day to day basis. In this document I’ll present a brief summary of the ones that I think are important, but get less press. All of the new features including the ones listed here are described in the What’s New manual for DB2 v9.1. I highly recommend looking it over for the full list of new features as well as for reading more about the features that interest you most.
|
|
|
New Features of DB2 v9.5
|
|
|
By now you have probably heard of the major new features introduced in DB2 9.1 such as pureXML, Deep Compression, Self Tuning Memory Manager, Automatic Storage and Table Partitioning. DB2 9.5 introduced a Workload Manager and a whole raft of other new features that will be useful on a day to day basis. In this document I’ll present a brief summary of the v9.5 features that I think are most important. All of the new features including the ones listed here are listed in the What’s New manual for DB2 v9.5. I highly recommend looking it over for the full list of new features as well as for reading more about the features that I list here.
|
|
|
DB2 Administrative Views
|
|
|
Half of the time that I need to get some information about a DB2 system I can’t remember if I need to use the GET or LIST command. Is it LIST DBM CFG or GET DBM CFG? So I get a syntax error on my first attempt most times I try. I also get tired of pouring through massive amounts of output when I’m only interested in a few candidates. For example, if I want to examine the snapshot output for the few applications that are experiencing lock waits I hate looking through the output for hundreds of applications that the GET SNAPSHOT FOR APPLICATIONS command gives me. Fortunately, I no longer need to do this in DB2 v9. We now have the Administrative Views that allow me to use SQL select output to get information that I once had to use a LIST, GET or other command to perform.
|
|
|
DB2 Performance Expert
|
|
|
DB2 Performance Expert can isolate problems faster and with fewer resources, and is used with service level agreements and objectives manifested in exception processing. Ultimately, this frees up time for the production DBA to focus on value-creation activities. This product provides a great way to monitor and tune all of your databases. Please see the link above for more information on this great tool!
|
|
|
DB2 Connect® Usage and Editions
|
|
|
The primary use of DB2 Connect is to allow DB2® clients (PC’s, Application Servers, and DB2/LUW Database Servers) to connect to DB2 on zSeries and iSeries servers. It translates character data between EBCDIC encoding used on zSeries or iSeries and ASCII encoding used on Linux, UNIX or Windows. This sounds simple enough, but in reality DB2 Connect provides your applications, whether written in Java, .NET, ODBC, Ruby, PHP, CLI, etc., to be tightly integrated with the powerful zSeries and iSeries servers! It can even take advantage of Workload Manager and Sysplex implementations. In addition, it allows you to place your data on the platform that makes the most sense to your organization while making the physical location invisible to your applications. There are various editions of DB2 Connect that all do the same thing, but the terms of how you are allowed to use them differ. If you use Type-4 JDBC drivers you can connect to DB2 on zSeries and iSeries servers, but you still have to license DB2 Connect. I’ll explain the licensing terms (Editions) later in this document.
|
|
|
Essential DB2 Health Check
|
|
|
Everyone wants their systems to be highly available and to perform well on an ongoing basis, but verifying the health of their system can seem like a daunting task. Good News! The things that matter the most and help avoid the most frequent problems are the easiest to implement! They are also the foundation of other in depth and focused areas of system verifications. In this article I’ll discuss a number of things that you can do periodically to ensure that you have a well running DB2 system. They are so easy that even db2Dean can figure them out. When these are done regularly, they can prevent many outages and are very cheap. You can even put them into a script and run them once a month to send you a report.
|
|
|
Replicating Data Loads from a DB2 9 HADR
|
|
|
When you use the LOAD utility to put data into the primary database in an HADR pair that data is not replicated because it is not logged. To see how you can place that load data into the secondary, please read this article.
|
|
|
Virtual Databases
|
|
|
A problem that a number of organizations face these days is having several different databases on different vendors’ Database Management Systems (DBMS) and needing to write applications that talk to more than one of them at a time. For example, you might have databases on Oracle on UNIX, SQL Server on Windows and DB2 on z/OS and need to write applications that get data out of all of them. This can get rather complex because the person writing the application needs to know how to connect and write efficient queries for all of them. Often one person will not have the skills for all of the databases. It can be time consuming for them to learn new databases and the calls to some of the databases may be sub-optimal. Also you have to make the decision as to whether to imbed passwords to each database in each application or have the user of the application provide multiple passwords for multiple databases. With WebSphere Federation Server you can avoid these problems. Through the rest of this article, I will refer to the product simply as WFS.
|
|
|
DB2 Samples
|
|
|
There have always been some samples in DB2 for some things like user exits and some code stubs, but as of DB2 v9.5 we are really getting serious about it. There are now many samples for application development and administration. The samples are not yet all in one place, but I am writing this article to summarize what is available and where to find them. Even if you are running an older version of DB2, many of these samples will be useful to you because they exploit features of DB2 that have been there for a long time.
|
|
|
Enable Yourself to Open IBM Support Requests
|
|
|
There are several avenues to get help from IBM for DB2 for Linux, UNIX and Windows (DB2/LUW) problems and questions. One of my favorite ways is to open an Electronic Service Request (ESR), but first you must get registered to do so. The most common use of the ESR is to get help resolving a problem with DB2 either caused by you or the product itself. However, if you have specific how-to questions the ESR tool is for that as well. In any case, before you can open an ESR you must register to do so. If the right person in your organization is not around when you want to register then this can take a while, so I recommend that you register now, so that you are ready when you need to open a ticket.
|
|
|
Enable Yourself to Down Load IBM Software
|
|
|
From time to time, you will need to install some database related software because you bought something new, you want to put what you already have on a new server or for some other reason. IBM usually does not send CDs or DVDs with software anymore, but instead places it on a website where you can download it called Passport Advantage (PPA). Since this is licensed software and we don’t want anyone but you to get it, there is a process to ensure that only those entitled to get your software can download it. As long as your organization keeps paying the maintenance on your software, all current versions of it on all operating systems where it runs will be on the PPA site. Just remember that just because it is on your PPA site does not mean you can install the software as much as you want. For most software you are allowed to run it on some limited number of CPU’s or have a limit on the number of users who can use it. If you are unsure of these limits, you can contact your friendly Sales Rep or IT Specialist to get the exact answer.
|
|
|
|
Show member list