A Systematic Approach to View Creation

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. 

And if you follow the guidelines contained in this article, in the end, all that will remain is a beautiful view!

"Unstructured " Data and LOBs

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...

As organizations expand their digital information footprint, more types of unstructured data are being captured, stored, and made available for analysis. And not just internally generated data, but from more external data sources, too.

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:

•  Cannot use a LOB in a GROUP BY or ORDER BY clause
• Cannot specify SELECT DISTINCT on a LOB
• LOBs cannot be used in the context of an INCLUDE(column-name) clause in a MERGE statement
• Cannot define check constraints, primary key, unique, or foreign keys on LOBs
• LOBs cannot be used in any predicate except EXISTS, LIKE and NULL

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:

1. The ROWID-Version number in the base table row may not be found in the LOB index.
2. There may be entries in the LOB index that are not referenced by any row in the base table.
3. The LOB data itself may not be where the LOB index points to.
4. There may be LOBs in the LOB table space that are not referenced by the LOB index.

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:

1. Run CHECK DATA to verify that the ID fields specified in the base table are also found in the LOB index. 
2. Run CHECK INDEX to verify that the LOB index is valid.
3. Run CHECK LOB to verify that the internal structure of the LOB table space is sound.

 Of course, there are easier ways. It can make a lot of sense to consider using a modern tool that understands the nuances of LOBs so you can manage them accordingly and appropriately.

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. 

Db2, SQL, and Modern Coding Techniques

 

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.

 

How Do You Store Boolean Data in Db2?

Recently, I was reading an article written by Ben Brumm on using Boolean data in SQL and it got me thinking about Db2 and Boolean data. You see, if you click over to the article, you'll see that Ben talks about several popular database systems, but not Db2!

So first of all, what is Boolean data? Sometimes all you want to do is to be able to store a Yes/No, True/False, 1/0 value in a column of a Db2 table. But that can be problematic. What data type should you choose? How should you model that column? Does it matter?

Well, up until recently, Db2 did not support a Boolean data type. But I guess I should be more clear: Db2 LUW offers Boolean support, but Db2 for z/OS does not have a built-in Boolean data type.

As of V9.7, you can use a Boolean data type for local variables, global variables, parameters, or return types in compound SQL (compiled) statements. So it is really only a viable option for SQL PL. As of V11.1, you can use the Boolean data type for a column in a Db2 table.

OK, so what should you do if you do not have Boolean support in the Db2 you are currently using? In my opinion, the best approach is to use a numeric solution with 1 as TRUE and 0 as FALSE. You might want to consider using a Character column with YES/NO or TRUE/FALSE, but there are several issues with that approach. Firstly, it will likely consume more storage, secondly without a constraint on the column users might specify “YES” or “Yes” or “y” or “Y”… or other variations to mean the same thing, and finally, if English is not your first language then users may have the urge to specify “Si” or “Da” or… well, you get the idea.

A good choice for a Boolean data type could be BINARY(1) which only stores binary data, so no constraint is needed. Or you might want to specify SMALLINT with a check constraint only allowing 0 and 1 as options.

If you want to allow "Unknown" as an option, then you can make the column nullable.

Summary

So, share your experiences with us here on the blog. Tell us, have you implemented Boolean data in your Db2 tables? How did you do it? And how is it working out for you? 

What is Db2?

For regular readers of this blog the title of this particular post might seem to be a bit odd. After all, why visit the Db2Portal blog if you don't know what Db2 is, right?

Well, the purpose of this short post is to refer my regular readers to an article I wrote for TechTarget, a website that regularly posts "What Is" type of articles.

So, you guessed it, I wrote the latest, updated "What is Db2?" article for TechTarget... and I figured I'd share it with my blog readers. 

Now whenever somebody asks a high-level question about Db2, like "what is it?" or "what are the advantages and disadvantages of Db2" or even "where can I run Db2?" you now have a place to send them...

Here ---> What is Db2?