My goal with this series is to provide some tips and tricks you might not be aware of for finding information in the IBM DB2 Information Center.
Check out Part 1 in this series: Tips for Using the IBM DB2 Information Center Part 1: Navigating
As I mentioned in Part 1, I started working with IBM DB2 documentation just before the IBM DB2 Information Center became the standard. I had a shelf of reference books for each version. In fact, this is still in my office, though a bit dusty and ununsed:
Because I learned how to use DB2 documentation starting with this shelf (or rather the DB2 version 7 version of this shelf), I still largely think of the data that is in the IBM DB2 Information Center in the same terms. I’m going to walk through some of those divisions and a couple of important pages to be aware of and possibly even bookmark. This is not an exhaustive guide to the IBM DB2 Information Center – there is plenty in there that I never touch – but a guide to the parts that I find useful as a DBA.
The funny thing is how much about the books comes back to me as I’m writing this – which volume particular items were in for the multi-volume references, the name of the books that I haven’t cracked in 5 years or more. I’ll include links to pages in the IBM DB2 9.7 Information Center, but just about everything referenced here can be found in the IBM DB2 Information Center for any version.
Important Individual Pages
There are a couple of pages that are important enough on their own that they deserve mention on their own.
SQL Limits
Probably the single page I visit the most is what I think of as the “SQL Limits” page: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html
They’ve since renamed this page “SQL and XML Limits”. But just about any question about how big something can be in DB2 is answered on this page. How many columns in an index, how big an index key can be, how big a table can be, how big a table name can be, how big a BIGINT can be – it’s all there. I frequently reference table 6 and table 7. I’m probably on that single page at least once a month, though about half of the time it’s for a blog entry and not for actual work. Very much worth bookmarking.
Syntax Diagram Page
When you’re first using the IBM DB2 Information Center, understanding syntax diagrams is critical. Luckily there’s a page to help you with that. Until you can recite it in your sleep (pretty sure I could and have), this page is great to help you figure out what parameters are required or how to build a working command: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0006726.html
System Monitor Guide and Reference
This area of the info center can be found in the table of contents under Database fundamentals -> Database monitoring -> Monitor elements -> Logical Data Groups
A side note: while writing this blog entry, I really had to force myself not to go off on tangents and dig through interesting things I might not normally read. See, I’m a DB2 information junkie. A friend recently commented that there’s so much DB2 in my head it just leaks out my ears, and yet I want more – I always want to understand better and to learn new things about DB2.
Anyway, this particular type of page is particularly useful when you are trying to monitor a database or figure out what’s going on. For example, if I’m investigating my package cache and the package cache hit ratio and want to understand where I could view information on the things that go into calculating the package cache hit ratio, I’d probably be interested in this page from the IBM DB2 Information Center:
There is a standard structure to these pages…
Name and Description
At the top is a basic description of the monitor element.
Table Function Monitoring
If the monitoring element exists in the Table Function monitoring interface introduced in DB2 9.7, then the first table will list all of the table functions where you can find this element, along with the Collection Level needed to get information on it. Notice that each line here is also a link to the page in the IBM DB2 Information Center for the table functions – so you can figure out how to query them for this information.
If this element is not included in table function monitoring, this section will be missing.
Snapshot Monitoring
The next table will be for Snapshot Monitoring – which snapshots you can find this monitoring element in, the Monitor Switch that must be ON in order for data on this monitoring element to be collected.
If this element is not included in snapshot monitoring, this section will be missing.
Event Monitoring
The final table shows what types of Event Monitoring include this monitor element.
If this element is not included in event monitoring, this section will be missing.
Useful Information
After that, there may be some useful information at the bottom – in this case, there is advice on how to calculate the package cache hit ratio using this and another monitor element.
Links/References
Finally, the IBM DB2 Information Center gives us links to related monitor elements or parameters or other IBM DB2 Info Center pages
Using the information on this page, you know where to go to get data on the monitor element. Every column of every table function for monitoring, every row in every snapshot, and each data point in event monitoring can be mapped back to one of these pages. There are hundreds of them. I was completely unaware of the system monitor guide and reference (though I had a copy on my shelf) until my first performance class after I had been a DBA for a couple of years – where the existence of this reference was just one of the things with which Melanie Stopfer completely blew my mind and changed the course of my career.
Command Reference
This Reference, as a book, contained the syntax for all of the non-SQL DB2 commands – things like BACKUP DATABASE, and RUNSTATS, and REORG. As such, it was absolutely critical. I still look up the RESTORE database command at least once a month in the IBM DB2 Information Center when I can’t remember which order the parameters go in or what the syntax is for AST databases or redirected restores.
Example:
Again, there is a structure common to each and every page:
Name and Description
A description of the command is provided here, sometimes with quite a bit of detail
Scope
This section helps you understand whether this particular command is active at the instance level or at the database level or some other level. Our example is a bit confusing here because it can be used at either the database or the instance level.
Authorization
This is one of the sections I use most. I can’t tell you how many people ask me what level of permissions or authorization is needed for a particular command. All I do to answer is pull up the IBM DB2 Information Center and pull up the command page for it, and this section tells me.
Command Syntax
This section shows the syntax used for the command. The syntax diagram uses the conventions listed on the syntax diagram page I mentioned earlier.
Required Connection
This section will tell you if you need a database connection or an instance attachment in order to run the command.
Command Parameters
This is further details on the items listed in the Command Syntax section. While the example is pretty straightforward, some commands like SET TABLESPACE CONTAINERS show a significant amount of detail here.
Examples
For most commands, there is at least one example of how the command is executed. In commands that return a large amount of output such as REORGCHK, there may be a lot of data in this section including examples of what the output looks like.
References
Again, the IBM DB2 Information Center does a pretty decent job of linking to related commands and other related information.
It is important to remember that some information about the output of commands – such as REORGCHK – is only available on the command page for the command. Understanding the REORGCHK page would make anyone a better DBA.
SQL Reference
The SQL Reference came in two volumes – one for SELECT and one for everything else. Most of the SQL reference topics can be found under Database reference -> SQL -> Statements and Database reference -> SQL -> Catalog views:
An example of an SQL statement page:
Again, the SQL statement pages have a common structure …
Name and Description
A description of what the command does
Authorization
The Authorities or privileges needed to execute this command.
Syntax
This section shows the syntax used for the command. The syntax diagram uses the conventions listed on the syntax diagram page I mentioned earlier.
Description
This section includes more detailed descriptions of each element referenced in the syntax diagram
Notes
Useful information about this command and its effects or ramifications
Examples
Examples of executing the command.
As I’m sure you can imagine, these pages can get pretty long. Just look at CREATE TABLE – I’m pretty sure that would be a small book in itself if you printed out. The truth is that if I’m looking something up for a SELECT or a stored procedure or a CREATE TRIGGER, I use Graeme Birchall’s DB2 Cookbook more often than the IBM DB2 Information Center. I am a bit concerned that the SQL Cookbook hasn’t been updated for DB2 10.1, and wonder how long it will be around. I’m sure going to miss it when it is gone. The IBM DB2 Information Center data for SQL statements is great when you already know what function you want to use and just need a bit more detail on it.
The other portion of the SQL Reference that I still use is that section on catalog views. While a simple describe statement can get a lot of the details you need, sometimes you need more and the descriptions in the IBM DB2 Information Center can provide the details you need – things like “what time unit is elapsed time in?”.
Summary
I had not planned spilling this to a third entry, but it’s just too long. In the next post, I’ll cover the parameter references, the message reference and general guidance.
