News, views, and items of interest on IBM's Db2 database management system and mainframes.
Just a quick blog post to wish everybody a very happy holiday season. Whether you celebrate Christmas, Hanukkah, Kwanza, or some other holiday season, all the best to you from me!
And a very Happy New Year to one and all. The past couple of years have been difficult and stressful for many, so here's a wish for a better 2022 for everybody.
I plan to take some time off over the last week of 2021 through New Year's Day, but I'll see you all again on the other side of the holiday season.
One of the constant struggles with Db2 development, and indeed developing for any relational DBMS, is designing and creating appropriate indexes. Perhaps the most important thing you can do to assure optimal application performance when accessing data in a relational/SQL database is to create correct indexes for your tables based on the queries your applications use. Of course, this is easier said than done.
But we
can start with some basics. For example, consider this SQL statement:
|
SELECT LASTNAME, SALARY |
What
index (or indexes) would it make sense to build for this simple query? First, think about all
the possible indexes that you could create. Your first short-list probably
looks something like this:
This is a good start, and Index3 is likely to be the best of the lot. It enables the optimizer to use the index to immediately look up the row or rows that satisfy the two simple predicates in the WHERE clause. Of course, if you already have a lot of indexes on the EMP table, you might want to examine the impact of creating yet another index on the table.
With
the caveat that appropriate index creation can be complicated, let’s look at
the Top 10 things you can do to build the right indexes on your tables:
1. Index by workload, not by object
Many
people make the mistake of just guessing at some indexes to create when they
are creating other database objects (like tables and tablespaces). But without an
idea of how the tables are going to be accessed these guesses are
usually wrong – at least some of them.
Indexes
should be built to optimize the access of your SQL queries. To properly create
an optimal set of indexes requires a list of the SQL to be used, an estimate of
the frequency that each SQL statement will be executed, and the importance of
each query. Only then can the delicate balancing act of creating the right
indexes to optimize the right queries most of the time be made.
If you
are doing it any other way, you are doing it wrong.
2. Build indexes based on predicates
3. Index most-heavily used queries
Numbers
2 and 3 can be thought of as corollaries to Number 1… that is, these are the
aspects of application workload that need to be examined to produce appropriate
and effective indexes.
Look at the predicates of the queries in the workload and work to create a set of indexes that match up to most (if not all) of them. When it is not practical to build all of the indexes you have identified, then it makes sense to look at the queries that will be used most often and optimize them first.
4. Index important queries
The more important the query, the more you might want to tune by index creation. If you are coding a query that the CIO will run every day, you want to make sure it delivers optimal performance. So building indexes for that particular query is important. On the other hand, a query for a clerk might not necessarily be weighted as high, so that query might have to make do with the indexes that already exist.
Of course, the decision should depend on the application’s importance
to the business - not just on the user’s importance. For example, if that clerk runs a query hundreds of times a day and the CIO runs his query only once, then perhaps the clerk's query is more important.
5. Index to avoid sorting (GROUP BY, ORDER BY)
In
addition to building indexes to optimize data access, indexes can be used to
avoid sorting. The GROUP BY and ORDER BY clauses tend to invoke sorts, which
can cause performance slowdowns. By indexing on the columns specified in these
clauses, the optimizer can use an index to avoid a sort, and thereby
potentially improve performance.
6. Create indexes for uniqueness (PK, U)
Some
indexes are required in order to make the database schema valid. Most Database
systems require that unique indexes be created when unique and primary key
constraints exist.
7. Create indexes for foreign keys
Even if
not required, creating indexes for each foreign key can optimize the
performance when accessing and enforcing referential constraints (RI –
referential integrity).
8. Consider adding columns for index-only access
Sometimes
it can be advantageous to include additional columns in an index to increase
the chances of index-only access. With index-only access, all of the data needed
to satisfy the query can be found in the index alone — without having to read
data from the tablespace.
For
example, suppose that there is an index on the DEPTNO column of the DEPT table.
The following query may use this index:
|
SELECT DEPTNAME
|
Of course, this is not always a good idea. You have to take into account whether other queries use the index and how it might negatively impact their performance.
It is also worth mentioning index include columns, which allows you to define additional (included) columns that are not part of the actual key but are available in the index. So we can create an index like this:
CREATE INDEX IXNAME
ON DEPT ( DEPTNO )
INCLUDE ( DEPTNAME )An example of a bad standard would be to place an artificial limit on indexing. There should be no arbitrary limit on the number of indexes that you can create for any database table. Indexes are undoubtedly one of the most important factors in creating efficient queries. Relational optimizers rely on indexes to build fast access paths to data. Without indexes data must be scanned – and that can be a long, inefficient means by which to retrieve your data. When a rule such as this exists, it usually is stated in the standards manual using verbiage something like “Each table can have at most five indexes created for it” — or — “Do not create more than three indexes for any single table in the database.” These are bad standards.
If
you already have three indexes, or five indexes, or even 57 indexes, and
another index will improve performance why would you arbitrarily want to avoid
creating that index?
Anyway, a good indexing standard, if you choose to have one, should read something like this: “Create indexes as necessary to support your database queries. Limitations on creating new indexes should only be entertained when they begin significantly to impede the efficiency of data modification.”
Which brings us to…
10. Be aware of I/U/D implications
Db2 must automatically maintain every index you create. This means every
INSERT and every DELETE to an indexed table will insert and delete not just
from the table, but also from its indexes.
Additionally, when you UPDATE the value of a column that has been defined in an index, the DBMS must also update the index. So, indexes speed the process of retrieval but slow down modification.
So the general rule of thumb should be "Index until it hurts... and then back off the least effective index to make it no longer hurt." Sure, that is easier said than done, but it is a valid philosophy to follow.
Summary
Following
these Top 10 index design techniques can go a long way toward improving not
just your index usage, but also the performance of your database applications.
Historically, one of the most fertile grounds for disagreement between database professionals was the appropriate usage of views. Some analysts promote the liberal creation and usage of views, whereas others preach a more conservative approach.
When properly implemented and managed, views can be fantastic tools that help to ease data access and simplify development. Although views are simple to create and implement, few organizations take a systematic and logical approach to view creation. And therein lies the problem. A strategic and reasonable policy guiding the creation and maintenance of views is required to avoid a muddled and confused mish-mash of view usage. Basically, views are very useful when implemented wisely, but can be an administrative burden if implemented without planning.
Let's back up a minute and define what a view is. First, remember that all SQL access to relational tables results in another table. This is called relational closure and it is a requirement of the relational model. A view can be considered to be a logical table. That is, a view is a "logical" representation of data that is "physically" stored in other tables (and perhaps other views as well). Views are defined using SQL and are represented internally to the DBMS by SELECT statements, not by stored data (unless it is a materialized view, which is another thing altogether and beyond the scope of this particular post). Almost any SQL that can be issued natively can be coded into a view.
View Implementation Rules
After you understand the basics of views, you should develop guidelines for view creation in order to limit administrative burden. The following rules can be used to ensure that views are created in a responsible and useful manner at your shop. These rules were developed over a number of years as a result of reviewing implementations and working with views in many different environments. There are three basic view implementation rules:
1. The View Usage Rule
2. The Proliferation Avoidance Rule
3. The View Synchronization Rule
The View Usage Rule: The first rule is simple: your view creation strategy should be goal-oriented. Views should be created only when they satisfy a specific application or business requirement. That requirement should be documented somewhere, preferably in a dictionary or repository. Although this rule may seem to be obvious, views are implemented at some shops without much thought as to how they will be used. This can cause the number of views that must be supported and maintained to continually expand until so many views exist that it is impossible to categorize their uses. And the time needed to maintain and administer the system increases as the number of views increases.
There are five basic uses for which views excel. These are:
1) to provide row and column level security,
2) to ensure efficient access paths,
3) to mask complexity from the user,
4) to ensure proper data derivation, and
5) to rename tables and/or columns.
The second rule is the Proliferation Avoidance Rule. This rule is simple and to the point: do not needlessly create views (or, indeed, any database objects) that are not absolutely required. Whenever a database object is created additional entries are placed in the system catalog. Creating needless views causes "catalog clutter"…that is, entries in the catalog for objects which are not needed and/or are not used.
The proliferation avoidance rule is based on common sense. Why create something that is not needed? It just takes up space that could be used for something that is needed.
The third, and final view implementation rule is the View Synchronization Rule. The basic intention of this rule is to ensure that views are kept in sync with the base tables upon which they are based.
Whenever a change is made to a base table, all views that are dependent upon that base table should be analyzed to determine if the change impacts them. All views should remain logically pure. The view was created for a specific reason (if we followed the View Usage Rule above). The view should therefore remain useful for that specific reason. This can only be accomplished by ensuring that all subsequent changes that are pertinent to a specified usage are made to all views that satisfy that usage.
For example, say a view was created to satisfy an access usage, such as a join between the employee table and the department table. If a column is added to the employee table specifying the employee’s location, it should also be added to the EMP_DEPT view if it is pertinent to that view’s specific use. Of course, the column can be added to the table immediately and to the view at the earliest convenience of the development team.
The synchronization rule requires that strict change impact analysis procedures be in place. Every change to a base table should trigger the usage of these procedures. Simple SQL queries can be created to assist in the change impact analysis. These queries should pinpoint any SQL in QMF queries, application packages, dynamic packages, and so on. Policies for informing users of the views to be impacted also need to be established before view changes can be implemented.
View synchronization is needed to support the view usage rule. By keeping views in sync with table changes the original purpose of the view is maintained.
Synopsis
Views are practical and helpful when implemented in a systematic and thoughtful manner. Hopefully, this post has provided you with some food for thought pertaining to how views are implemented at your shop.
Let's take a look at the burgeoning role of "unstructured" data and the impact of using LOBs to store that data in our Db2 databases.
The Growth of "Unstructured" Data
Although structured
data remains the bedrock of the information infrastructure in most
organizations, unstructured data is
growing in importance. And there is much more "unstructured" data “out
there” than structured. Indeed, analysts at IDC estimate that unstructured
data accounts for as much as 90 percent of all digital information.
The rise of "unstructured" data is often attributed to the growing amount of multimedia data being adopted by organizations as they store more audio, video, and images. But that is only part of the story. Text documents, in the form of business forms, letters, and documents, and most importantly, e-mail, greatly contribute to the growing importance of unstructured data.
Now I'm sure you've noticed the quotes I've been putting around the word "unstructured," and they are there for a reason. You see, the word unstructured is a horrible adjective to use in conjunction with data to refer to this type of data. I refer you to this article I wrote called The Big Unstructured Data Lie for why I think this way. Nevertheless, the term "unstructured" will continue to be used to describe this type of data, so from now on I will stop using the quotes...
Db2 and Unstructured
Data
Db2 for z/OS can be used to store unstructured data using
BLOB, CLOB, and DBCLOB data types – collectively known as LOBs. Historically,
LOBs were added to relational products like DB2 to compete more capably against
the Object-Oriented databases of the time – this was back in the late 1990s.
The idea was to enable relational tables to house unstructured data like images
and audio and video data.
But Db2 for z/OS users were slow to adopt LOBs in their
mainframe databases. This was due to several different reasons, not the least
of which is that it took several versions of Db2 for LOBs to mature into
capable, useful things for enterprise applications. Early implementations of
LOBs in Db2 were somewhat kludgy and difficult to administer and use. But IBM
has corrected many of those deficiencies over time and there are now tools that
can help organizations to exploit and effectively manage Db2 LOBs, too.
The other new force driving LOB usage is the whole Big Data
movement. Big Data is driving organizations to accumulate and analyze more
data, and more varied types of data, to gain business insight. A specific example
of Big Data driving the usage of LOBs in Db2 is the JSON support that has been
added to Db2. JSON objects are stored in Db2 as BLOBs.
So, more and more organizations are adopting LOB data in their Db2 databases – to support unstructured data, for their big data projects, and to store documents and multimedia data.
Nevertheless, sometimes I hear DBAs say things like “Oh, well, I don’t use LOBs so I don’t really need to worry about them.” That can cause trouble, because you have been using LOBs for some time now, whether you know it or not. LOB data has been part of the Db2 Catalog since Version 7 and the number of LOB columns used in the Db2 Catalog has been increasing over the past couple of Db2 releases.
As you can
see in table below, the Db2 Catalog now has over 40 LOB columns. So, even if
you have not created any user Db2 tables with LOBs, you have system Db2 tables
with LOBs. For example, SYSIBM.SYSVIEWS contains a CLOB containing the source
text used to create the VIEW.
LOBs are also used in the Db2 Directory, in the DBD01
“table” a BLOB column is used to store the DBD data (2GB). And in the SPT01
“table” there are two BLOB columns for storing data and explain information.
Using LOBs in Db2
I will not attempt to train you on the usage of LOBs in Db2
in this blog post. Suffice it to say that LOBs require different management and
administration tactics to ensure their accuracy and usability. But I do want to
bring up some of the administration concerns that using LOBs can create.
The first thing to keep in mind is that most LOBs are larger
than your typical column data. I mean, it’s right there in the name LOB = Large OBject. As the size of data increases, so do the management concerns,
such as lengthy elapsed times to run utilities on the data, slower performance of
accessing the data, and so on. And you’ll also need to decide whether or
not to log changes to your LOB data. If you do
log LOB changes, then you can stress your logs because of the size of the LOB
data. If you do not log LOB changes, then you will need to make sure that you
have sufficient methods to recover the LOB data because the changes between
image copies won’t be there in the log. Generally speaking, most usually choose to avoid the logging of LOBs. You can turn off LOB logging by specifying
NOT LOGGED in the LOB table space DDL.
There are also many restrictions on how LOB data can be used
with SQL. LOB data is not like traditional, structured database data, so Db2 imposes some limitations, for example:
These are for illustrative purposes only. There are other
restrictions, all of which can be found in the IBM SQL Reference manual.
Unless it is an inline LOB where the entire LOB is stored in
the base table, a LOB will require a LOB table space, auxiliary table, and LOB
index. When building auxiliary tables and indexes, you do not specify columns
the same way that you do for normal tables and indexes. For the Auxiliary Table
you specify the LOB column and base table and Db2 automatically generates the
columns needed. For the Auxiliary Index you just specify the auxiliary table
and Db2 implicitly generates the needed index keys.
Each table can have 0, 1, or many LOB columns and each LOB instance
can be up to 2GB in size. Each table with at least 1 LOB must have a ROWID; the
ROWID is a varying-length 17 byte field. One page of a LOB table space will never
contain more than one LOB, but one LOB can span multiple LOB table space pages.
An auxiliary table, which resides in the LOB table space, can store only one
LOB column of a base table; there must be one and only one index on this column.
All of these things can alter the way in which you manage and administer your
Db2 tables and table spaces.
Let’s backtrack and review the size of LOBs in a little more
depth. Each LOB instance can be up to 2G – and that is per row! Each LOB table space
can have as many as 254 different data sets with a DSSIZE from 2G to 64G each
for a total of about 16 terabytes (TB). This is per partition, so if there are
4096 partitions, then the total size for a single LOB is
over 66,000 TB. That is big!
Unless all of your LOB data is static – meaning it never
changes – the size of your LOB data sets will continue to grow. Are you
prepared for running utilities on such large table spaces?
When you drop a LOB column from a base table, DB2 will not
automatically clean up LOB table spaces. After removing the LOB column you can
either drop the LOB table space yourself explicitly, or perhaps reuse it for
another LOB.
And finally, LOB columns are not really updated. The old
version of the LOB is de-allocated, and a new LOB is allocated. So LOBs are a bit
different than the traditional data we are used to managing.
What can go wrong
with LOBs?
Errors with LOBS
occur when there are inconsistencies between the components of the LOB. We
all know that “normal” Db2 indexes can be inconsistent with their associated
table, but the issues are multiplied for LOB indexes:
CHECK DATA can be used to find errors 1 and 2 (from the list
above); CHECK LOB can be used to find errors 3 and 4. But it is possible that CHECK
LOB will convert a type 4 error into a type 2 error, so proceed with caution.
Then there is the issue of LOB index consistency. If the LOB
index is inconsistent with the base table data, the LOB data cannot be
accessed. There is no direct access to the LOB table space except through the
LOB index. If the LOB index is inconsistent with the LOB table space, Db2 will
get errors trying to access the LOB data for that row.
Maintaining LOBs
Db2 maintains LOB data in the LOB table space using a
hierarchical structure.
LOB data in the LOB table space can be distributed over many
different pages of the LOB table space. Remember, this LOB data is very large. Db2 uses a structure of map pages to point to data pages. At the top is the first
map page and it is this page number that is stored in the LOB index. This first
map page contains a list of pages, which can be other map pages and data pages.
It also contains the total size of the LOB data. If all the data pages are not
referenced by map pages or if the map pages are not properly referenced by a
higher level map page, LOB data will be
lost.
With all of these pointers and structures to maintain, there are a variety of things that can go wrong. To verify that your LOBs are structurally sound you must run a series of DB2 utilities, in the following order:
The Bottom Line
Business and industry trends dictate that unstructured data,
in the form of LOBs, is increasingly being stored in our Db2 databases. This
type of data is different than traditional, structured data and must be managed
with these differences in mind. To do so requires in-depth knowledge and
planning to avoid inconsistencies and errors.
Mainframe application development has changed dramatically during the lifetime of Db2. Way back in the beginning, and even for a long time thereafter, most Db2 programs were written in COBOL and coded to use static SQL. This meant that the SQL was bound before it was executed and the access paths were etched in stone. And it also meant that the access paths for any SQL in almost any program were readily available to DBAs in PLAN_TABLEs.
Fast forward to the present and static SQL in COBOL is the
exception rather than the rule. Oh, sure, those COBOL programs with static SQL
are still there, running in CICS or in batch (or even IMS/TM), but most new development is not done
this way anymore. Today, programmers use IDEs to build code that accesses
mainframe Db2 data using distributed data access over the web or from a GUI.
Most modern, distributed, application development projects
typically rely upon application development frameworks. The two most
commonly-used frameworks are Microsoft .NET and Java/J2EE. And these frameworks
use dynamic SQL, not static.
An additional contributing force is the adoption of commercial
off-the-shelf applications for ERP and CRM like SAP, Peoplesoft, and Siebel (the last two of which are now Oracle products).
These applications are not tied to a specific DBMS but support by multiple
different DBMSs, one of which is (was) DB2 for z/OS. So, these applications use
dynamic SQL because that makes it easier to accomplish multi-vendor DBMS
support. For the most part though, none of these ERP packages run on the mainframe (or Db2) because two of the are now Oracle products and SAP is promoting its own DBMS (HANA).
The point though is that the manner in which Db2 applications are developed has
changed. And that means the manner in which Db2 is managed is changing, too.
Instead of relying on access paths already being there, DBAs must develop
ways of capturing access paths for dynamic SQL statements. Prepared dynamic SQL
can be cached in the dynamic statement cache so that the same SQL statement can
reuse the mini plan for the statement the next time it runs. And the BIND
command can be used to snap the statements in the cache and find the access
paths used. But the dynamic statement cache works like a buffer pool, with least
recently used statements being flushed to make room for new statements… so you
might not find the dynamic SQL you are looking for, at least not without
helpful tools or scripts to stay on top of things.
This change has caused many organizations to experience SQL
performance problems. Because dynamic SQL is easier to introduce into the
system performance can become problematic. It is difficult to get a handle on what dynamic SQL runs
when. And it is difficult to track because the DBAs are not accustomed to monitoring
and tuning dynamic SQL… or they just do not have the proper tools to be able to
do the job appropriately. So, these black holes of dynamic SQL performance
problems open up that are left to run wild.
New SQL Stuff
It is not just the new programming paradigm that causes
management problems for modern DB2 usage. The amount of new SQL statements and
functions and features continues to grow with each new version (and even function level) of Db2. This can
be a mixed blessing though. It is good because expanding SQL functionality
makes it easier to program and access Db2 data. But it can be problematic
because it can be more confusing and difficult to learn these new features.
Exacerbating the issue is that many organizations do not
invest a sufficient amount of time and effort in educating their staff. If the DBAs are not
trained in all of the new functionality, then new SQL can cause big issues. How?
Imagine a shop that just migrated to a new version or level of Db2, but the only education
available was to read the manuals. So, some of the DBAs are not knowledgeable on
the new SQL code. Then a developer picks up a manual, and learns how to use a
new function or arrays as parameters. Kudos to the developer for the
initiative, but if problems arise there may not be anybody who knows how to
support the new feature.
And there is a lot of new SQL functionality being added. If
we focus on just the past few versions of Db2, here
is a representative list of new SQL programming related enhancements that have
been introduced: TRUNCATE, new data
types (DECFLOAT, VARBINARY), optimistic locking, FETCH CONTINUE, ROLE, MERGE,
SELECT from MERGE, pureXML, FETCH FIRST & ORDER BY in subselect and
fullselect, INTERSECT, EXCEPT, Indicator Variables, TIMESTAMP precision and
time zones, Moving sums and averages,
Inline and Non-inline SQL scalar functions, SQL table functions, extended
implicit casting, RANK(), ROW_NUMBER(), XQuery, transparent archive query, APPLCOMPAT, IDAA/analytics, grouping sets, ROLLUP, Hadoop access, FTBs, LISTAGG…
That is a lot to learn and this is just a selection of what has been added!
Summary
Things are moving at a fast and furious pace for application
developers these days. And the resultant changes can introduce problems that
impact your business unless you adapt to align your management and DBA
capabilities with the new development methods.