Optim Configuration Manager Overview for LUW and Z

Dean Compher

29 August 2012

 

InfoSphere Optim Configuration Manager (OCM) is a new and very useful tool for centrally tracking and managing the configuration of your DB2 servers and clients.  It works for DB2/LUW and DB2/zOS database servers, but does not require any additional software on those servers.  At regular intervals that you specify it collects information about the database servers such as DB/DBM (LUW) and zParms (z/OS) configurations, configuration of objects like tables and indexes, DB2 version and fix pack, and Operating System version with maintenance level, plus other useful information.  This is great information to have since problems are often caused by a change in the system and with OCM you can see if anything changed around the time that the problem began such as a spike in the number of users or a configuration update.  It is also just useful to have an inventory of your databases and clients and how they have been configured over time.  Since OCM gets information from the database, it can also see information about the clients connected to the database.  All DB2 servers know a lot about the clients connected to them.  OCM can see this from its connection to the database so it can report information such as the IP addresses of all the clients, the number of clients connected, the type of client (CLI, Java, etc), authorization id, and other useful information.  This is a great way to track how the profile of your client base changes over time and even allows you to collect information about who is connecting to your database so that you know who to notify when you need to make database changes.  Further, there is a component that you can install on Java clients to collect java connection properties and to centrally manage the Java properties defining database server connections allowing you to direct each client to the appropriate database server which is especially useful for environments that have multiple database servers such as HADR, Data Sharing or pureScale.  OCM can also control Java properties related to Workload Manager (WLM) so that you can more finely control the resources consumed on your z/OS database servers by those clients.   

 

The Optim Configuration Manager is a process that runs on a server and connects to the DB2 databases that you tell it about.  It collects and stores information in a database called the OCM Repository for later review.  It uses the various performance APIs and stored procedures built into DB2 to gather this information.  If you install the OCM client with a DB2 client then the OCM server can communicate directly with the client to see Java properties and manage certain client properties.  When you install an OCM client on a DB2 client such as an application server, then we call that client a Managed Client.  Having managed clients is optional.  Clients without the OCM Client are called Recorded Clients and you can still see a significant amount of information about them through the database server’s view of its clients.  See Figure 1 for an example OCM configuration:

 

Figure 1.  Example OCM Configuration

 


The OCM V2.1 Roadmap page contains links to all of the information that you need about the tool including links for how to install it and how to use it. 

 

The OCM Server captures and stores a lot of information about your DB2 database servers and the clients connected to those databases.  Database Server information collected includes:

·         Database and Database Manager (DB & DBM) Configurations on Linux, UNIX and Windows servers

·         zParms on z/OS servers

·         Database Objects including tables, indexes, and sequences.

·         Authorizations (granted permissions)

·         DB2 Version and Fix Pack Level

·         Operating System (z/OS, AIX, Linux, etc.)  plus OS Version.

·         Hostname, IP address and other information about the connected instances or subsystems. 

 

Database Client information collected about each client:

 

How does OCM gather this information?  When you set up OCM, you tell it what databases you want to monitor and it makes client connections to those databases.  You do the OCM configuration through a web browser.  OCM has several built-in collection tasks that it can perform such as collect DBM configurations, collect database structure information or collect information about clients.  As part of the configuration of each database, you pick which OCM tasks you want and tell it how frequently you wish it execute each task such as daily, weekly, etc.  You can then look at any collected information or view how information has changed between any two collection tasks.  It gets the information from the databases using DB2 built-in stored procedures or APIs that you could make yourself to look at any of this information.  There are additional collection tasks that you can schedule for managed clients.  All of this data is stored in the repository database that you create for OCM.  That repository database can be local with the OCM server or on a different database and you can see which DB2 editions can be used for the repository on the System Requirements page.  You can also set an expiration time for each type of collected data so that data is automatically pruned for you.

 

Just being able to view this information for the most recent sample collected or for at a time in the past is very useful.  With this feature you can verify that all of the databases and clients are on a supported version and that each has your standard fix pack level by looking in one place.  Checking individual samples also allows you to look at the tables and indexes in each database without connecting to that database.  This also allows you to keep a self-updating inventory of all of your DB2 databases and clients.  Further you can view the number of connections to each database at different times to see if the number of connections is growing, and if so, at what rate.  This can be very useful for database server capacity planning. 

 

The comparison feature takes the value of OCM to another level by allowing you to see how values changed over time.  For example, through your browser connection to the OCM server you can view all changes for a certain time period.  This is extremely useful if you know that a problem started occurring during some timeframe.  In this case if OCM highlights a major performance configuration change or that an index was created or dropped, then you have probably found the culprit.  You can also query OCM to show all changes over time.  This is useful for when you believe that something like a DBM CFG parameter in your environment changed and want to determine when it changed.  It is also useful when a colleague says, “I know that this table was in the database and now it is not!”  With OCM you can prove whether the object ever existed and if it did, when it went away. 

 

In addition to capturing configuration information about your database, OCM can additionally perform other tasks for your DB2/LUW databases.  You can ask it to show how much space you could save if you were to compress your tables and indexes.  It can create a report showing the savings for each table and index with the ones that benefit the most at the top.  It can also manage the aging of your Multi-Temperature Tables.  Multi-Temperature tables allow you to move data to lower classes of storage as it ages and becomes less used.  If you recall from my DB2 10.1 New Features article, I noted that the movement can be scripted.  However, you can avoid scripting by creating aging rules in OCM and letting it manage the movement of your data through the different storage groups.  For DB2/LUW, OCM also shows how frequently objects are used by capturing the used-counts on the objects each time a sample is recorded.  With this information you can determine appropriate storage for the object or even if the object such as an index is even needed. 

 

 

Managed Clients

 

Without installing anything on your application servers you can see a fair amount of information about the clients through the information that you capture from your database server.  However, you can do even more if you install the OCM client with your IBM JCC (JDBC) driver on your DB2 clients.  These are called Managed Clients.  At the most basic level, the OCM Server can capture the Java and WAS properties on a periodic basis just as it captures database configuration information.  Another very useful feature is OCM’s ability to centrally manage which clients connect to which database servers.  This allows you to dynamically manage the connections among the cluster of database servers and redirect those connections as needed, regardless of whether those servers are DB2/LUW or DB2/zOS.  In general, each client knows the IP address and port of the DB2 database server or member to which it typically connects.  However, in a High Availability Disaster Recovery (HADR) or other HA configuration that client may not know where to look if the primary database server is down, especially if you have moved the backup to a new location.  Using OCM, you can update the secondary server IP address on every managed client with one operation.  That is much easier than having to get access to every application server in your environment and changing the Java properties individually.  Also one of the advantages of using DB2/LUW pureScale and DB2/zOS Data Sharing is the ability to put new members in and out of service as you desire.  Each member has its own IP and port.  Using OCM with managed clients you can easily manage the connection properties of your various members as you add or delete members, thus giving every client a persistent list of the available servers in the cluster.

 

For DB2/zOS systems OCM has some features that are not yet available for DB2/LUW databases.  You may have noticed that your application developers don’t know about and don’t care about the management of the workload on your DB2 database server.  However, the DBA does care and wants to make sure that Workload Manager (WLM) provides the right resources to the right transactions.  The OCM client along with the IBM JDBC driver allows you to set several properties in the JDBC Type-4 database connection that are passed to DB2 specifically to tell WLM about the application.  With these properties WLM can very easily place each transaction into the correct workload.  Using OCM you can centrally manage these WLM connection properties for all of your managed clients.  You don’t even need to log onto the application server to do it after the initial install and configuration of the OCM Client.  This allows you to give the right resources to each application from a holistic perspective.  It is also useful when one of the applications goes berserk.    When this happens you can assign the bad application to a workload that gets few resources, protecting your overall system.  Further, if you notice that a particular application is putting an excessive load on the database, OCM can configure it to only submit transactions to a particular Data Sharing member, containing the performance problems to that member.  You can also create rules that automatically direct transactions from certain managed clients to certain members at different times or under different circumstances.  For example, if one application starts running large reports between certain hours, you might want that client to only send transactions to a particular member during those hours with the other clients sending transactional transactions to other members.  Again, these features currently only work for DB2 z/OS database servers and members only. 

 

As of the writing of this article, only Java clients are supported as managed clients.  However, other types of clients are supported as recorded clients.   You can think of the OCM client plugging into your JCC driver, allowing it to update the driver’s properties and to see everything that the driver sees.  OCM requires a minimum level of the JCC driver and you can check that in the OCM System Requirements page. 

 

Licensing

 

Optim Configuration Manager for DB2/LUW and Optim Configuration Manager for DB2/zOS are two separately licensed products and both must be purchased if you want to use it for DB2 servers in both the distributed and mainframe environments.  OCM for DB2/LUW is included in DB2/LUW Advanced Server Edition (DB2 ASE) and can be installed and used as much as you like for those ASE databases at no additional charge.  You can purchase OCM to manage other DB2 editions.  Both OCM licenses come with a complimentary license of DB2/LUW Enterprise Server Edition that is restricted to being used for your repository database, so you don’t have to pay for a DB2 license to store your OCM data.  You can see the details of OCM licensing at these links:

 

InfoSphere Optim Configuration Manager for DB2 for Linux, UNIX and Windows V2.1

InfoSphere Optim Configuration Manager for DB2 for z/OS V2.1

 

***

 

OCM is undergoing significant development to add new features so watch for them in the next several months.  If you find anything else interesting about OCM please comment on what you find on my  Facebook Page or my db2Dean and Friends Community

 

HOME | Search