Wednesday, May 21, 2014

IBM DB2 11 Tools and Utilities: Delivering timely value to your business

Migrating to any new software release can be a lot easier when you are familiar with new features prior to deployment. So it stands to reason that you should familiarize yourself with new DB2 functionality before you try to migrate your environment to a new version. This training can take the form of:
  • Reading the DB2 manuals, especially the What’s New manual and the Technical Overview redbook that typically comes out with each new version of DB2
  • Attending presentations on the new release, whether online, at user groups, or even at IDUG or IBM Insight (which used to be the IOD conference)
  • Formal training from IBM or other sources

But the bottom line is that you need to educate yourself in advance of migrating to any new version of DB2... Otherwise, you may not be ready to move to the new version on a schedule that fits your business needs.

With IBM DB2 11 for z/OS, you can have a smoother migration process that also enables you to deploy key applications faster. New features and capabilities, both within DB2 11 and the tools and utilities that support DB2 can make migration easier.

And, of course, DB2 11 for z/OS comes with out-of the box cost savings and features that allow you to do more with business-critical analytics and applications. But are your DB2 Tools and DB2 Utilities ready to provide you complete exploitation or support? Do you know the difference?

Join me on June 10, 2014 as I deliver a webcast on DB2 11 for z/OS Tools and Utilities on behalf of IBM. During this informative webinar I will take you through some of the key features in DB2 11 and the importance of timely support for these features by your DB2 tools and utilities. I’ll expose some of the new capabilities of IBM’s tools and utilities for DB2 and I’ll also share ways to make your DB2 11 migration simpler, safer and faster.

And I’ll see you in June!

Monday, May 19, 2014

Another Great IDUG DB2 Tech Conference Concludes

Last week, the annual North American IDUG DB2 Tech Conference was held in Phoenix, AZ... and, as usual, it was a great week with a LOT of educational and networking opportunities.

The week started off with a great selection of day long seminars conducted by industry luminaries, highlighted by the likes of Bonnie Baker, Susan Lawson, Dan Luksetich, Judy Nall, Roger Sanders and more. There was something for everyone whether you were a newbie or an old hat... using DB2 on LUW or z/OS... wanting to cram for certification or just learn something new.

On Tuesday the day started off with a bang as Dr. Vladimir Bacvanski, Founder of SciSpike, regaled the attendees on the nuances of Big Data and NoSQL. And he didn't use Powerpoint slides! He drew all of his stuff live, free-hand, much to the enjoyment of everybody. Especially enjoyable was his big pile of data (see below)...

After Valdimir's inspiring talk Leon Katsnelson of IBM came up to talk about the Big Data industry trends driving innovation at IBM. My next session on the first day at IDUG was another highlight of the week -- the DB2 for z/OS spotlight session (Trends and Directions). This session talked about what has already been delivered and what might be coming next (code name: Cypress).

I don't want to get into walking through every presentation I attended during the week, but suffice it to say that there was a lot of informative material shared with IDUG attendees. If you work with DB2 for a living, you really should try to get to this conference every year!

And I delivered two presentations at this year's event. The first was titled A Big Data Roadmap for the DB2 Professional, in which I explained and defined Big Data and NoSQL terminology and use cases... and offered up my opinions on Big Data and how it can/will impact the IT industry and the place of relational DBMS within it.

My second presentation was a VSP for Datavail, a DBA managed services company. In this presentation I offered up a definition of Database Administration as a management discipline and provided some best practices for DB2 DBAs. Datavail will be sponsoring me later this year in a webinar on the same topic, so be sure to keep an eye on my blog for an announcement of when that will be coming!

Other than the traditional hour long presentations, IDUG offers a fantastic opportunity for networking, especially at the vendor exhibition hall. Here I am catching up with Dave Beulke, Judy Nall, and Gerry Hodge:

And I also was able to catch up with friends I rarely see, like Peter Ong (see below), except for at conferences like IDUG:

Another highlight of the conference was the second keynote, delivered on Thursday by Donald Feinberg of Gartner. I was particularly happy to hear his presentation on data trends, especially his take on Big Data, which was remarkably similar to the one I had espoused earlier in the day!

The week also was highlighted by lots of business meetings, lots of casual conversations with friends and colleagues, great food, and I even got the chance to sit down with Dan Luksetich and record a podcast on DB2 auditing with him... I'll be sure to blog about the podcast when it goes live on Dan's site.

So, all in all, IDUG week was fantastic success... but that is no surprise to those of us who make IDUG a regular, annual event. Hope to see you there next year (in Philadelphia).

Saturday, May 10, 2014

DB2 for z/OS, Distributed Workload, and Enclaves

If you work with DB2 for z/OS and have to manage distributed workload… or if you are enabling zIIP specialty processors… chances are you’ve at least heard the term “enclave” or “enclave SRB” before. But do you understand what an enclave is and why it is important?

An enclave is a construct that represents a transaction or unit of work. Enclaves are a method of managing mainframe transactions for non-traditional workloads. You can think of an enclave as an anchor point for resource accumulation regardless of where the transaction is executing.

With traditional workloads it is relatively easy to map the resources consumed to the actual transaction doing the consumption. But with non-traditional workloads – web transactions, distributed processing, etc. – it is more difficult because the transaction can span platforms. Enclaves are used to overcome this difficulty by correlating closely to the end user’s view of the transaction. 

An enclave can consist of many pieces spread over many server address spaces. So even though a non-traditional transaction can comprise multiple “pieces” spanning many address spaces, and can share those address spaces with other transactions, an enclave gives you control over the non-traditional workload. And WLM can be used to more effectively manage non-traditional workload via the enclave.

If you are interested in more details on enclaves and how they are managed, read through Enclaves – Managing Business Transactions from IBM’s RMF Newsletter.

Friday, May 09, 2014

IDUG DB2 Tech Conference in Phoenix, Arizona

Today's blog post is just a quick one to remind everybody about the 2014 IDUG DB2 Tech Conference coming up next week in Phoenix, Arizona. 

As usual, there will be a plethora of useful information shared at the conference - as well as the opportunity to meet with developers, gold consultants and DBAs/developers from all over the country... and even the world!

There are complimentary workshop sessions scheduled for Friday on a variety of topics and you will probably also want to sign up for one of the educational seminars on Monday. And don't forget to take advantage of the free IBM certification exams offered up at every IDUG conference.

This year's keynote presentation will be delivered by noted Gartner analyst Donald Feinberg, who is responsible for Gartner's research on database management systems, data warehousing infrastructure and Big Data.

And just check out the impressive agenda of DB2 presentations that will be available at this year's IDUG! 

Add to all of the above an array of Special Interest Groups, the DB2 z/OS and LUW panel sessions (where you can ask questions of the developers), and let's not forget the vendor exhibition where you can check out the latest products and services for DB2... and it all adds up to IDUG being the place to be next week if you have anything to do with DB2. I know I'll be there...

...and I hope to see you there, too.

Monday, May 05, 2014

Using Check Constraints to Simulate Domains

Check constraints are a very useful, though somewhat underutilized feature of DB2. Check constraints enable enhanced data integrity without requiring procedural logic (such as in stored procedures and triggers). Let’s examine the basics of table check constraints.
A constraint is basically a restriction placed upon the data values that can be stored in a column or columns of a table. Of course, most RDBMS products provide several different types of constraints, such as referential constraints (to define primary and foreign keys) and unique constraints (to prohibit duplicates).
Check constraints place specific data value restrictions on the contents of a column through the specification of a Boolean expression. The expression is explicitly defined in the table DDL and is formulated in much the same way that SQL WHERE clauses are formulated. Any attempt to modify the column data (i.e. during INSERT and UPDATE processing) will cause the expression to be evaluated. If the modification conforms to the Boolean expression, the modification is permitted to continue.  If not, the statement will fail with a constraint violation.

This functionality is great for simulating the relational concept of a domain. A domain is basically the set of valid values that a column or data type can take on. Check constraints only simulate domains, though, because there are other features provided by domains that are not provided by check constraints. One such feature is that columns pooled from separate domains must not be compared or operated on by expressions that require the same type of data for both operands. For domains to truly be supported the DBMS should support both check constraints and user-defined data types with strong type checking. This prohibits allowing ridiculous operations, such as comparing IQ to shoe size or adding Australian dollars to Euros.

Forming Check Constraints

Check constraints are written using recognizable SQL syntax. This makes them easy to implement for anyone who has even a passing familiarity with SQL. The check constraint consists of two components: a constraint name and a check condition. 

The constraint name is an SQL identifier and is used to reference or identify the constraint. The same constraint name cannot be specified more than once for the same table. If a constraint name is not explicitly coded, DB2 will create a unique name automatically for the constraint. 

The check condition defines the actual constraint logic.  The check condition can be defined using any of the basic predicates (>, <, =, <>, <=, >=), as well as BETWEEN, IN, LIKE, and NULL.  Furthermore, AND and OR can be used to string conditions together.

There are, however, restrictions on how check constraints are formulated. Some of these restrictions include:
·         Limitations on the entire length of the check condition.
·         Other tables may not be accessed in the check condition.
·         Only a limited subset of SQL operations are permitted (for example subselects and column functions are prohibited in a check constraint).
·         One of the operands (usually the first) of the check constraint must be the name of a column contained in the table for which the constraint is defined.
·         The other operand (usually the second) must be either another column name in the same table or a constant value.
·         If the second operand is a constant, it must be compatible with the data type of the first operand.  If the second operand is a column, it must be the same data type as the first column specified.

Check Constraint Examples

Check constraints enable the DBA or database designer to specify more robust data integrity rules directly into the database.  Consider the following example:

      (EMPNO                      INTEGER

               CHECK (EMPNO BETWEEN 100 and 25000),

        EMP_ADDRESS     VARCHAR(70),
        EMP_TYPE               CHAR(8)

               CHECK (EMP_TYPE IN (‘TEMP’,   ‘FULLTIME’, ‘CONTRACT’)),

        EMP_DEPT              CHAR(3)                        NOT NULL WITH DEFAULT,
        SALARY                    DECIMAL(7,2)               NOT NULL

               CHECK (SALARY < 50000.00),

        COMMISSION          DECIMAL(7,2),
        BONUS                      DECIMAL(7,2)

The CREATE statement for the EMP table contains three different check constraints:

1.      The name of the first check constraint for the EMP table is CHECK_EMPNO.  It is defined on the EMPNO column.  The constraint ensures that the EMPNO column can contain values that range from 100 to 25000 (instead of the domain of all valid integers).
2.      The second check constraint for this table is on the EMP_TYPE column.   This is an example of an unnamed constraint.  Though this is possible, it is not recommended.  It is best to always provide an explicit constraint name in order to ease identification and administration.  This specific constraint restricts the values that can be placed into EMP_TYPE as: 'TEMP', 'FULLTIME', and 'CONTRACT';  no other values would be accepted.
3.      The last check constraint on this table is named CHECK_SALARY.  It effectively ensures that no employee can be entered with a salary of more than $50,000. (Now who would want to work there?)

Column vs. Table Level Constraints

The first check constraint example we reviewed showed a column-level check constraint. However, check constraints also may be coded at the table-level. A column-level check constraint is defined in the DDL immediately after the column. Appropriately enough, a table-level check constraint is defined after all of the columns of the table have already been defined.

It is quite common for business rules to require access to multiple columns within a single table.  When this situation occurs, it is wise to code the business rule into a check constraint at the table-level, instead of at the column level.  Of course, any column-level check constraint can also be defined at the table-level, as well.  In terms of functionality, there is no difference between an integrity constraint defined at the table-level and the same constraint defined at the column-level.

Let’s augment our sample table DDL to add two table-level check constraints:

      (EMPNO                      INTEGER
               CHECK (EMPNO BETWEEN 100 and 25000),
        EMP_ADDRESS     VARCHAR(70),
        EMP_TYPE               CHAR(8)
               CHECK (EMP_TYPE IN (‘TEMP’,   ‘FULLTIME’, ‘CONTRACT’)),
        EMP_DEPT              CHAR(3)                        NOT NULL WITH DEFAULT,
        SALARY                    DECIMAL(7,2)               NOT NULL
               CHECK (SALARY < 50000.00),
        COMMISSION          DECIMAL(7,2),
        BONUS                      DECIMAL(7,2),

               CHECK (SALARY > COMMISSION),

               CHECK (COMMISSION>0 OR BONUS > 0),

The CREATE statement for the EMP table has been modified to contain two table-level check constraints having the following ramifications:

1.      The name of the first table-level check constraint for the EMP table is COMM_VS_SALARY.  This constraint will ensure that no employee can earn more commission than salary.
2.      The second table-level check constraint is named COMM_BONUS.   This constraint will ensure that every employee either earns a commission or a bonus (or possibly, both).

Check Constraint Benefits

So what are the benefits of check constraints? The primary benefit is the ability to enforce business rules directly in each database without requiring additional application logic. Once defined, the business rule is physically implemented and cannot be bypassed. Check constraints also provide the following benefits:
·         Because there is no additional programming required, DBAs can implement check constraints without involving the application programming staff. This effectively minimizes the amount of code that must be written by the programming staff.  With the significant application backlog within most organizations, this can be the most crucial reason to utilize check constraints.
·         Check constraints provide better data integrity.  As check constraints are always executed whenever the data in the column upon which they are defined is to be modified, the business rule is not bypassed during ad hoc processing and dynamic SQL. When business rules are enforced using application programming logic instead, the rules can not be checked during ad hoc processes.
·         Check constraints promote consistency. Because they are implemented once, in the table DDL, each constraint is always enforced. Constraints written in application logic, on the other hand, must be executed within each program that modifies any data to which the constraint applies. This can cause code duplication and inconsistent maintenance resulting in inaccurate business rule support.
·         Typically check constraints coded in DDL will outperform the corresponding application code.

The overall impact of check constraints will be to increase application development productivity while at the same time promoting higher data integrity.

Check Constraints, NULLs, and Defaults

An additional consideration for check constraints is the relational NULL. Any nullable column also defined with a check constraint can be set to null. When the column is set to null, the check constraint evaluates to unknown.  Because null indicates the lack of a value, the presence of a null will not violate the check constraint.

Additionally, DB2 provides the ability to specify defaults for table columns – both system-defined defaults (pre-defined and automatically set by the DBMS) and user-defined defaults. When a row is inserted or loaded into the table and no value is specified for the column, the column will be set to the value that has been identified in the column default specification.  For example, we could define a default for the EMP_TYPE column of our sample EMP table as follows:

        EMP_TYPE               CHAR(8)        DEFAULT ‘FULLTIME’
               CHECK (EMP_TYPE IN (‘TEMP’,   ‘FULLTIME’, ‘CONTRACT’)),

If a row is inserted without specifying an EMP_TYPE, the column will default to the value, ‘FULLTIME’.

A problem can arise when using defaults with check constraints. Most DBMS products do not perform semantic checking on constraints and defaults. The DBMS, therefore, will allow the DBA to define defaults that contradict check constraints.  Furthermore, it is possible to define check constraints that contradict one another. Care must be taken to avoid creating this type of problem. 

Examples of contradictory constraints are depicted below:

CHECK (EMPNO > 10 AND EMPNO <9 o:p="">

In this case, no value is both greater than 10 and less than 9, so nothing could ever be inserted.


In this case, the default value is not one of the permitted EMP_TYPE values according to the defined constraint.  No defaults would ever be inserted.


In this case, the constraints are redundant.  No logical harm is done, but both constraints will be checked, thereby impacting the performance of applications that modify the table in which the constraints exist.

Other potential semantic problems could occur if the constraints contradicts a referential integrity DELETE or UPDATE rule, if two constraints are defined on the same column with contradictory conditions, or if the constraint requires that the column be NULL, but the column is defined as NOT NULL.

Other Potential Hazards

Take care when using the LOAD utility on a table with check constraints defined to it. By specifying the ENFORCE NO parameter you can permit DB2 to load data that does not conform to the check constraints (as well as the referential constraints). Although this eases the load process by enabling DB2 to bypass constraint checking, it will place the table space into a check pending state. You can run CHECK DATA to clear this state (or force the check pending off by using START with the FORCE option or the REPAIR utility). If you do not run CHECK DATA, constraint violations may occur causing dirty data.


Check constraints provide a very powerful vehicle for supporting business rules in the database. They can be used to simulate relational domains. Because check constraints are non-bypassable, they provide better data integrity than corresponding logic programmed into the application. It is a wise course of action to use check constraints in your database designs to support data integrity, domains, and business rules in all of your relational database applications.