Wednesday, September 25, 2013

Using the DISPLAY Command, Part 2

In the first part of this series on the DISPLAY command, we focused on using DISPLAY to monitor details about you database objects. In today's second installment of this series, we will look into using DISPLAY to monitor your DB2 buffer pools.

The DISPLAY BUFFERPOOL command can be issued to display the current status and allocation information for each buffer pool. For example:


DSNB401I  =DB2Q BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 202
DSNB402I  =DB2Q BUFFER POOL SIZE = 4000 BUFFERS  AUTOSIZE = NO     
             ALLOCATED       =     4000   TO BE DELETED  =        0
             IN-USE/UPDATED  =        0   BUFFERS ACTIVE =     4000
DSNB406I  =DB2Q PGFIX ATTRIBUTE -                                  
             CURRENT = NO                                          
             PENDING = NO                                          
           PAGE STEALING METHOD = LRU                              
DSNB404I  =DB2Q THRESHOLDS -                                       
            VP SEQUENTIAL    = 50                                  
            DEFERRED WRITE   = 15   VERTICAL DEFERRED WRT  = 5,  0
            PARALLEL SEQUENTIAL =0   ASSISTING PARALLEL SEQT = 0   
DSNB409I  =DB2Q INCREMENTAL STATISTICS SINCE 11:20:17 DEC 31, 2011 
DSNB411I  =DB2Q RANDOM GETPAGE = 6116897 SYNC READ I/O (R) = 37632 
           SEQ.   GETPAGE      = 799445  SYNC READ I/O (S) = 10602
           DMTH HIT            = 0       PAGE-INS REQUIRED = 0
DSNB412I  =DB2Q SEQUENTIAL PREFETCH -                              
             REQUESTS        =    11926   PREFETCH I/O    =   11861
             PAGES READ      =   753753                            
DSNB413I  =DB2Q LIST PREFETCH -                                    
             REQUESTS        =        0   PREFETCH I/O    =       0

             PAGES READ      =        0                            

We can see by reviewing these results that BP0 has been assigned 4,000 pages, all of which have been allocated. We also know that the buffers are not page fixed. The output also shows us the current settings for each of the sequential steal and deferred write thresholds.

For additional information on buffer pools you can specify the DETAIL parameter. Using DETAIL(INTERVAL) produces buffer pool usage information since the last execution of DISPLAY BUFFERPOOL. 

To report on buffer pool usage since the pool was activated, specify DETAIL(*). In each case, DB2 will return detailed information on buffer-pool usage such as the number of GETPAGEs, prefetch usage, and synchronous reads. The detailed data returned after executing this command can be used for rudimentary buffer pool tuning. We can see such detail in the example above.

For example, you can monitor the read efficiency of each buffer pool using the following formula:


  (Total GETPAGEs) / [ (SEQUENTIAL PREFETCH) +
                       (DYNAMIC PREFETCH) +
                       (SYNCHRONOUS READ)
                     ]
A higher read efficiency value is better than a lower one because it indicates that pages, once read into the buffer pool, are used more frequently. Additionally, if buffer pool I/O is consistently high, you might consider adding pages to the buffer pool to handle more data.


Finally, you can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open table spaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the table spaces and indexes reported by LIST. Statistical information is reset each time DISPLAY with LSTATS is issued, so the statistics are as of the last time LSTATS was issued.

Wednesday, September 18, 2013

Using the DISPLAY Command, Part 1

Often times some of the simplest commands and features of DB2 can be used to great effect -- if you just remember that they are there and learn how to use them. The DISPLAY Command is one of those features.

Although a DB2 performance monitor is probably the best solution for gathering information about your DB2 subsystems and databases, you can gain significant insight into “what is going on out there” using the simple DISPLAY command.

In today's blog post, and a series of subsequent posts, we will learn about the DISPLAY command and how it can be used to monitor various aspects of DB2.

There are eight variations of the DISPLAY command that you can utilize, depending on the type of information you are looking for. The DISPLAY command can be used to return information about the status of DB2 data sharing groups, databases and table spaces, threads, stored procedures, user-defined functions, utilities, and traces; it can also monitor the Resource Limit Facility (RLF) and distributed data locations.

Let’s start with coverage of what is probably the most often-used variation of the DISPLAY command, looking into database information.

By running the DISPLAY DATABASE command, you can gather information on DB2 databases and table spaces. The output of the basic command will show the status of the objects specified along with any exception states that apply. For example:

-DISPLAY DATABASE(DBNAME)

Issuing this command will display details on the DBNAME database including information about the tablespaces and indexes in that database. So, with a simple command you can easily find all of the tablespaces and indexes within any database —  pretty powerful stuff. But the status information for each space is useful, too. When a status other than RO or RW is encountered, the object is in an indeterminate state or is being processed by a DB2 utility. The possible statuses that DB2 can assign to a page set are detailed here:

ARBDP
Index is in Advisory Rebuild Pending status; the index should be rebuilt to improve performance and allow the index to be used for index-only access again.
AREO*
The table space, index, or partition is in Advisory Reorg Pending status; the object should be reorganized to improve performance. This status is new as of DB2 V8.
ACHKP
The Auxiliary Check Pending status has been set for the base table space. An error exists in the LOB column of the base table space.
AREST
The table space, index space, or partition is in Advisory Restart Pending status. If back-out activity against the object is not already underway, either issue the RECOVER POSTPONED command or recycle the specifying LBACKOUT=AUTO.
AUXW
Either the base table space or the LOB table space is in the Auxiliary Warning status. This warning status indicates an error in the LOB column of the base table space or an invalid LOB in the LOB table space.
CHKP
The Check Pending status has been set for this table space or partition.
COPY
The Copy Pending flag has been set for this table space or partition.
DEFER
Deferred restart is required for the object.
GRECP
The table space, table space partition, index, index partition, or logical index partition is in the group buffer pool Recover Pending state.
ICOPY
The index is in Informational Copy Pending status.
INDBT
In-doubt processing is required for the object.
LPL
The table space, table space partition, index, index partition, or logical index partition has logical page errors.
LSTOP
The logical partition of a non-partitioning index is stopped.
PSRBD
The entire non-partitioning index space is in Page Set Rebuild Pending status.
OPENF
The table space, table space partition, index, index partition, or logical index partition had an open data set failure.
PSRCP
Indicates Page Set Recover Pending state for an index (non-partitioning indexes).
PSRBD
The non-partitioning index space is in a Page Set Rebuild Pending status.
RBDP
The physical or logical index partition is in the Rebuild Pending status.
RBDP*
The logical partition of a non-partitioning index is in the Rebuild Pending status, and the entire index is inaccessible to SQL applications. However, only the logical partition needs to be rebuilt.
RECP
The Recover Pending flag has been set for this table space, table space partition, index, index partition, or logical index partition.
REFP
The table space, index space, or index is in Refresh Pending status.
RELDP
The object has a release dependency.
REORP
The data partition is in a REORG Pending state.
REST
Restart processing has been initiated for the table space, table space partition, index, index partition, or logical index partition.
RESTP
The table space or index is in the Restart Pending status.
RO
The table space, tables pace partition, index, index partition, or logical index partition has been started for read-only processing.
RW
The table space, table space partition, index, index partition, or logical index partition has been started for read and write processing.
STOP
The table space, table space partition, index, index partition, or logical index partition has been stopped.
STOPE
The table space or index is stopped because of an invalid log RBA or LRSN in one of its pages.
STOPP
A stop is pending for the table space, table space partition, index, index partition, or logical index partition.
UT
The table space, table space partition, index, index partition, or logical index partition has been started for the execution of utilities only.
UTRO
The table space, table space partition, index, index partition, or logical index partition has been started for RW processing, but only RO processing is enabled because a utility is in progress for that object.
UTRW
The table space, table space partition, index, index partition, or logical index partition has been started for RW processing, and a utility is in progress for that object.
UTUT
The table space, table space partition, index, index partition, or logical index partition has been started for RW processing, but only UT processing is enabled because a utility is in progress for that object.
WEPR
Write error page range information.

Of course, there are many additional options that can be used in conjunction with the DISPLAY DATABASE command. The following options can be used to narrow down the amount of information displayed:

  • USE displays what processes are using resources for the page sets in the database 
  • CLAIMERS displays the claims on the page sets in the database
  • LOCKS displays the locks held on the page sets in the database
  • LPL displays the logical page list entries
  • WEPR displays the write error page range information.


Additionally, for partitioned page sets, you can specify which partition, or range of partitions, that you wish to display.

The OVERVIEW option can be specified to display each object in the database on its own line. This condenses the output of the command and makes it easier to view. The OVERVIEW keyword cannot be specified with any other keywords except SPACENAM, LIMIT, and AFTER.

Another tactic that can be used to control the amount of output generated by DISPLAY DATABASE is to use the LIMIT parameter. The default number of lines returned by the DISPLAY command is 50, but the LIMIT parameter can be used to set the maximum number of lines returned to any numeric value. For example:

-DISPLAY DATABASE(DBNAME) LIMIT(300)

Using the LIMIT parameter in this manner would increase the limit to 200 lines of output. To indicate no limit, you can replace the numeric limit with an asterisk (*).

Finally, you can choose to display only objects in restricted or advisory status using either the ADVISORY or RESTRICT key word.



And be sure to tune in for the next blog post which will look into using DISPLAY to uncover information about your DB2 buffer pools.

Wednesday, September 11, 2013

Answering a Question: Dealing with Different Data Types

I get a lot of e-mail. Some of it is junk, but some of it contains questions on all sorts of issues. I cannot answer everything that comes into my in box or I wouldn't have time to earn a living. But every now and then I will answer a question here on the blog.

Today's question is:


Dear Mr. Mullins, 
I have a question I hope you can help me with.  In DB2 I am attempting to compare two columns, however one column is data type "Integer" and the other is data type "Decimal".  Can you give me an idea on how to convert the data types so they can be compared?




And here is my answer:
You can cast one data type to the other. For example, to cast the decimal to an integer you would use the INTEGER function, like so:

           WHERE INTEGER(dec_col) = int_col

Or you could cast the integer column to a decimal like this:

           WHERE DECIMAL(int_col) = dec_col
Alternately, you can use CAST to cast either column to either data type. For example, CAST(dec_col AS INTEGER)…  Hope this helps…


Friday, September 06, 2013

Top Ten Most Pervasive Myths About DB2 for z/OS

Today's blog offers up yet another Top Ten list for DB2 users, perusers, and abusers... This time counting down the most common myths that are perpetrated "out there" regarding DB2 and how it works (or doesn't work)...


1.Use Views to Insulate Programs from Change

              This lie has been told for almost as long as DB2 has been around. I first wrote about this way back in 1991 for Database Programming & Design. Check that article out here if you don't understand why this is a bad idea, in general. 

2.Locking Problems Indicate a Database Problem

              Locking problems are generally caused by bad program design. You should write code to reduce the duration of locks and to COMMIT regularly... and then locking won't be a problem, for the most part.

3.Primary Key is Usually a Good Choice for Clustering

              Actually, the foreign key is likely to be a better choice. When you join PK-->FK there will be one PK to multiple FK. Wouldn't it be best if the FKs were clustered on the same page (or pages)?

4.Just Using the Defaults Should Work Out Well

              Don't rely on defaults. Many of them are outdated or wrong... and even if they aren't it will be better if you review and investigate all options before explicitly specifying the parameter value you want. 

5.Programmers Don’t Need to Know How to Tune SQL

              Programming performance-oriented SQL into your programs from the beginning would go a long way toward improving performance overall... and reducing the length of the application development lifecycle. 

6.Black Boxes Work Well for Performance

              No they don't!!!

7.Using NULLs Can Save Space

              No they can't!!!

8.RUNSTATS Aren’t That Important

              If you don't work with up-to-date RUNSTATS then you are hobbling the DB2 Optimizer and almost assuredly getting sub-optimal access paths for your DB2 SQL. 


9.DB2 is a Hog

              If you don't use it properly, every piece of software can become a resource hog. If you acquire the knowledge on how to work properly with DB2 then it will hum along like a well-oiled machine!

10.It Depends!

              This is the answer that can be used for every DB2 question. But if that answer is not followed up with what "it" depends upon, then it is a useless answer... and whoever gave you that answer is probably just trying to get rid of you instead of helping you with your problems.

Sunday, September 01, 2013

Top Ten Common SQL Mistakes (with DB2 for z/OS)

There are many different types of development mistakes that can be made when you are coding a complex application system. But probably the most pervasive mistakes being made when developing an application using DB2 for z/OS are SQL mistakes... well, actually, that statement is probably true regardless of the DBMS, but I am going to focus on DB2 for z/OS given that this is a blog that focuses on DB2 and mainframe issues.

  1. Assuming an ORDER BY is not necessary for ordered results - if you want your results always to be returned in a specific order, you must include the ORDER BY clause. If you fail to do so, a future rebind can change the access path and also possibly change the order of your results set.
  2. Forgetting the NULL indicator - when your query can return a null, but sure to include a null indicator or you will get a -305 SQLCODE (22002 SQLSTATE). And be sure to check the null indicator to see if the data is null or not!
  3. Incorrect expectations when using the NOT IN predicate with NULLs - Nulls can be confusing when using the IN predicate. For example, what if we want to find all Colors who are not assigned to a particular Product using a query like shown below. THe problem arises when the P.color can be NULL. The NULL causes the predicate to be UNKNOWN so the results set is always empty. 
           SELECT C.color
           
FROM   Colors AS C
           
WHERE  C.color NOT IN (SELECT P.color
                                  
FROM Products AS P);

  1. Coding predicates appropriately in Outer Joins - Sometimes it can be difficult to figure out how to code outer joins appropriately if you don't code them very often. Terry Purcell has written a nice article on the topic that can be found here.
  2. Not coding a cursor for a multi-row result - When more than one row can be returned by your query you must use a cursor (or specify FETCH FIRST 1 ROW ONLY)
  3. Recompiling but not binding - if you make changes to the SQL, you have to BIND it again. Otherwise it won't work! 
  4. Forgetting to use single quotes around strings (instead of double quotes) - DB2 SQL expects single quotes around character strings.
  5. Trying to modify a Primary Key column - you cannot modify a primary key. A primary key should be unique within the table and immutable.
  6. Forcing dynamic SQL into static SQL (sometimes hundreds of static SQL statements) - you should analyze the type and nature of your database queries to determine whether they should be static or dynamic. Consider using the advice here (static SQL v. dynamic SQL) to guide you.
  7. Asking for more data than you need (columns and/or rows) - specify only the columns that you actually need in your SELECT-list... and use WHERE clauses (predicates) to filter the data to just that data that you need before bringing it into the program. The lesser the amount of data that DB2 needs to transfer from the database to your program, the more efficient things will be!