db2Dean’s 2011 IOD
28 November 2011
The IOD Conference
started off great when I met the Abominable Snowwoman at the Expo Grand Opening
the first night of the conference It got even better with all of the great
information that I learned at the sessions and with my success on the DB2 v9.7
Advanced Administration exam! Since Marilyn Monore stayed away again this
year, I’m going to unfriend her on Facebook. There was tons of information and
I’ll give you a summary of what I learned along with many random tips and
tricks. If you attend the conference as you should have, then you can download
many of the presentations from the Conference SmartSite. As
time has gone on many more have been added, so try again if something you
wanted was not available on the site when you first got back. 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 my new fried again at next year’s
conference! Also please add anything else you though was really interesting to
my db2Dean Facebook Page or to the
“Message Board” section of my db2Dean
IBM Data Studio
As you already know all new DB2 GUI administration features
are being added to the IBM
Data Studio with the DB2 Control Center being deprecated. The big news
with Data Studio 3.1 is that all of the features of Optim Database
Administrator (ODA) and Optim Development Studio (ODS) are now included. My
old Data Studio
Article contains summaries of these tools towards the bottom of the page.
These tools do many things and are free for licensed users of DB2! One of the
great features of ODA in Data Studio 3.1 is that you can now do things like
comparing two databases and having data studio generate complex change scripts
for unloading, loading, dropping, creating and preserving all dependent objects
when that is needed. With the ODS features built in, you can now give Data
Studio to your Java developers so that they can easily capture their SQL when
they test their applications and just send you a file so you can see if there
are any problem queries. Here is a partial list of new features on Data Studio
- Stop/Start of remote instances is now available. By
default Data Studio 3.1 makes an SSH connection to the server hosting DB2
to administer the instances. This must be configured though. Data Studio
can also connect to the DAS instance like Control Center did, but the
functionality of that connection is limited.
- Can now configure tasks like backup and runstats.
- Browse and Edit table data features can now filter rows
and columns so that you don’t have to bring all data into your client.
- pureScale administration support is in Data Studio, but
not Control Center
- The Data
Studio Web Console is now available to create and monitor jobs on the
database server and to monitor the health and availability of your
- It is a separate download from IDE/Stand alone client
- Installs a light web server and configures it on a
machine of your choice on your network.
Console feature is a web based replacement for Health Center.
Manager feature replaces Task Center and allows you to create, alter
and monitor jobs.
- Job Manager can schedule SQL Scripts, CLP Scripts, Others
- Job Manager can create job hierarchies and dependencies
Studio 3.1 links – overviews, components, installing, much more
- Still no Federation configuration a few other CC features
are still not in Data Studio either.
- Free How-to Videos
on how to use Data Studio.
- Many features for both DB2 on LUW and Z
- The new Administration View provides a “Flat” view like Control Center
DB2 University is a web site that
contains lots of DB2 and Big Data online training videos. They are great for
learning about DB2 and associated tools when you can’t attend a class. The
courses are typically broken into several short (< 5 minutes) videos that
discuss specific topics, so this is also a great when you need to reference a
particular function in Data Studio or DB2. There are also more in depth topics
like DB2 Essentials. The courses also provide lots of good related links for
things like manuals and other books that can be downloaded that are related to
the specific topic.
Tired of using the
catalog commands or the Configuration Assistant to configure you DB2 Client
connections? Well, you are in luck. You can now update the db2dsdriver.cfg
file with your favorite editor instead. It is an XML file that can also be
used for several other client configurations as well including pureScale. It
allows you to create connections for your client and e-mail it to your users
instead of cataloging databases on each client. Here are some important points:
- V9.5 fp3 introduced
it for the DS Driver
- V9.7 fp3a
introduced it for CLI clients (e.g. Control Center)
- V9.7 fp4
introduced it for .NET clients.
- Can be used for
OCBC, CLI, .NET, OLE DB, and open source (PHP or Ruby) connections
- Find a sample of
the file for V9.7 fp3 and later in a file called db2dsdriver.cfg.sample
after you install your Data Server (Runtime)
Client or Data Server Driver.
- I’ve also
included some samples of the file in the “Files” section at the bottom of
and Friends Community page that Brent Gross provided in his
- To validate your
file use the db2cli
tool to see if you have errors.
DB2 v”Next” Beta
Program. Want to try out the next version of DB2 that is now in a beta
program, but don’t have a server to install it on? That is no problem. We
will let you try it out on the Amazon cloud and we will pay for a reasonable
amount of your use of that cloud server. If this interests you then please see
Early Experience Page.
3.1 of Optim Query Tuner is now available. The biggest change is that it
now allows you to tune a workload (multiple queries) instead of just one at a
time to get the best overall recommendations for a set of queries. It can get
the workload from several sources including Data Studio and the DB2/LUW package
cache. It can also advise on the impact of a candidate index, suggest
candidate indexes and tell you where you have missing runstats or do not have
good enough runstats.
- When you need
really fast fail over one of the big concerns is the time it takes to
detect failures. A lot of work went into the design of DB2 pureScale and
we had a lot of success with this. A DB2 pureScale database cluster can
typically detect a software failure in less than one second and hardware
failures in less than 3 seconds. The trick with failure detection is
separating delayed response situations from true failures. The speed at
which pureScale can do this really sets it apart from other clustered
databases that take much longer to detect failures.
- DB2 pureScale on
Linux is now available on IBM xSeries H-series blades. It can now also
use certain 10 Gigabit Ethernet equipment in addition to InfiniBand. For
more information see the list
of compatible hardware for pureScale on Linux.
- Starting in pureScale
(v9.8) fix pack 3, you can have more than one database.
- Starting in fix
pack 4 you can use multiple ports on the InfiniBand adapter, allowing for
the use of multiple IB switches for even greater availability and somewhat
better performance for Cluster Facility processes (CFs).
Every year at IOD
there is at lease one very good DB2 Connect
presentation and this near was no exception with Brent Gross’ excellent
session. If your shop makes extensive use of DB2 Connect, then it is worth
attending the conference just for this presentation. Here is some of the great
information from this year’s session.
- The DB2 JCC (JDBC
type-4) driver has been able to do Workload Balancing across z/OS Sysplex
members since DB2 v8, and the CLI has been able to do this since DB2
Connect v9.5 fp3 without a DB2 Connect Gateway. If the only reason that
you are using the DB2 Connect Gateway was to support WLM across the
Sysplex, then you can now use direct connections and get rid of your
- By the time you
upgrade DB2/zOS to v10 then you will want to have upgraded all of your DB2
Connect clients and gateways to at least DB2 Connect v9.7 fp3. Earlier
versions of DB2 Connect do not support v10 features.
- When you use a
DB2 Connect Gateway server, the functionality of the lowest version of the
software is all that is supported. For example, if your client is running
at DB2 V9.7 fp4, but your DB2 Connect Gateway is only running DB2 Connect
v9.5, then you would not be able to use any features new in the DB2/zOS
v10 system from the client. The same would be true for that client if the
client and gateway versions were reversed.
- For users of DB2
Connect Unlimited Edition, you can now install
the license key once on the z/OS Server. Then all DB2 Connect clients,
drivers and gateways can use it without installing individual license
- Many apps see
15-30% elapsed time improvement when using a direct connect over a gateway.
OLTP systems see the biggest benefits of direct connections.
- Although IBM now
recommends using direct connect instead of a gateway for most enterprises,
there are still some reasons to use it.
- Many transaction
managers (e.g. Tuxedo, Encina) still require a server gateway for 2-phase
commits in certain circumstances.
- It is a great
place to federate multiple databases that include DB2/z or DB2/I
- Using a DB2 Connect
Server is a license requirement for DB2 Connect Enterprise Edition
Concurrent Users license.
- You can use the db2dsdriver.cfg
file instead of cataloging theDB2/zOS system on your clients and also
define Sysplex parameters there. See Brent’s examples in the “Files”
section of my db2Dean
and Friends Community.
- Use Statistical
Views to improve query performance on tables with non-uniform
distribution of data in columns used in predicates or where joins are done
on tables where at least one table in the join has many rows that do not
match the other tables.
- Random I/O
benefits the most from Solid State Disks with sequential reads benefitting
- Always use the
COMMIT command, even when doing read-only transactions
existing databases to the Currently Committed isolation level can give a
performance boost to some workloads.
- The DB2 Workload
Manager can help improve performance for databases with many connections.
- DB2 on zLinux
- DB2 on Power
- Sizing page
space at 2X RAM is recommended for best performance.
- AIX 6.1 is
configured out of the box as recommend by DB2
- Use “Tcp_nagle_limit=1”
to disable packet consolidation
- Enable jumbo
frames, especially for FCM
- Define LPARS
from the most to least important
- DB2 on Intel
- Staying Current
on Linux Service Packs helps a lot.
- The best
performing Linux file systems for DB2 are:
- Consider using
Linux deadline I/O scheduler for maximum performance.
- It us a good
idea to use DB2 Automatic features like Self Tuning Memory Manager if you
allow your Linux partitions to dynamically change sizes.
I hope that you found
at least a few pieces of information in this article to be new and useful. I
hope to see you at the conference next year.
HOME | Search