Tuesday, March 24, 2015

Dealing with Blank Checks in DB2

"I think IBM i developers believe they must help DB2..."

hmmm, unfortunately this is sad but true.

The science of database must be understood before the art of database can be practiced. This includes the relatively simple task of comparing a column's value to a literal.  How hard can it be to compare and test for blanks? You know, the spaces sitting in a empty not null character column.

Well, apparently it's more difficult on some platforms than others, and SQL coders jump through hoops and attempt back flips to "help" the database engine.

For more information and coaching on the topic, please see Kent Milligan's excellent article on the topic here.

If you want to get better at understanding the science of DB2 for i, and proficient at practicing the art of relational database with IBM i, then please reach out - we're here to help you become successful.

Friday, February 27, 2015

Introducing DB2 Web Query DataMigrator!

IBM has announced a new product offering in the DB2 Web Query family portfolio.

DB2 Web Query DataMigrator ETL Extension (i.e. DataMigrator) provides extract, transformation, and load (ETL) capabilities integrated with DB2 Web Query and DB2 for i.

Users looking to isolate their query workloads from production environments, or needing to automate the consolidation of data from a variety of data sources will be interested in this solution.

DataMigrator allows you to automate the process of extracting data from any DB2 Web Query supported source database, the process of transforming or cleansing the data and loading the data into an optimized DB2 for i reporting repository. Depending on the design and use of that repository, the industry refers to this as an operational data store, data mart, or data warehouse.

While leveraging the same graphical look and feel of DB2 Web Query, there are also integration points with DB2 Web Query. For instance, synchronize meta data defined either in DataMigrator or DB2 Web Query.

For more information, refer to the following documents:

IBM US Announcement

IBM Europe, Middle East, and Africa Announcement

Frequently Asked Question document

If you need assistance with architecture, design or implementation of your analytics environment running in IBM i, please reach out - we are here to make you successful!

Wednesday, January 28, 2015

Using the Blueprint

In the previous post, Dan Cruikshank illuminated the concept of creating a database "blueprint" through the process of data modeling.  Continuing my conversation with Dan, I asked him about using the blueprint, i.e. data model.  Specifically, using SQL to access the data (the answer is YES!) and whether or not it makes more sense to use embedded SQL in a traditional high level language like RPG, or to use separate, and modular Stored Procedures.

Dan's answer and insight follows...


The answer to the question as to use SQL Stored Procedures (or External Stored Procedures) versus embedded SQL in a monolithic program depends on the environment in which the application exists. What that means is if you are developing both host centric as well as external (i.e. web or mobile) applications which need to perform common IO functions, such as consuming result sets, inserting, updating and deleting rows from and to a common set of tables, etc., then using stored procedures would be my recommendation. If all development is strictly host centric (should not be in this day and age) then embedded SQL would be ok, but not best practice, in my opinion.

From an application methodology perspective, we tend to recommend a Model/View/Controller (MVC) approach for design and development, where the data access and user interface are separated from the high level language code. In the case of data access this would be done via stored procedure calls.

This is not a performance based recommendation; it is more about re-usability, reduced development costs and shorter time to market. Done properly, better performance is a byproduct. Not to mention higher value returned to the business!

In addition, with the added SQL DML support for result set consumption in IBM i 7.1, it is now easier for the host centric applications (i.e. RPG and COBOL) to share a common set of procedures which return result sets. From an SQL DML perspective, prior to 7.1 this was limited to insert, update and delete procedures although accessing a result set from a stored procedure was available via CLI calls (not widely used in traditional IBM i shops).


If you need additional knowledge, skills or guidance with what Dan is sharing, please do not hesitate to reach out.

Friday, January 16, 2015

The Blueprint

This post is the result of a recent conversation about the importance of proper data modeling with my long time friend and teammate Dan Cruikshank.

For the readers who are not familiar with Dan, he is the person who invented the concept of database modernization and the methodology to pull it off in a responsible way; one of the many contributions Dan has provided to his appreciative clients around the world. We euphemistically refer to the IBM i database modernization process as "the Dan Plan".

Dan, the blog is yours...


About 15 years ago my wife and I jumped on an opportunity to buy a used home that was in foreclosure. The price was right, the location was fantastic; unfortunately the house was in rough shape.

There was no flooring in the main rooms; several of the walls had holes, who knew what the infrastructure was like.  We were looking at years of reconstruction and possibly 10’s of thousands of dollars in cost.

As we were going through the closets we discovered a set of the original blueprints. Suddenly years of work was now looking like weeks, and at a cost of 1’s of thousands of dollars.

About this same time my career at IBM took on a new slant. I was beginning to see that many of the performance issues I was then dealing with all seemed to be rooted around the same cause – a poor database design. IBM Rochester was launching the new SQE engine, which boasted brand new SQL capabilities that took advantage of the IBM i integrated relational data base "DB2 for i". Unfortunately many of the IBM i heritage customers were still using traditional record level access, let alone having a database that was properly designed for SQL set based access.

“Oh woe is me”, cried those customers who were now faced with a reconstruction nightmare – how to bring their applications and data into the new millennium without taking years of effort or spending millions of dollars on “modernization”. 

“If only we had been more diligent on documenting our applications”, lamented the growing number of CIOs who were now tasked with groveling for more budget dollars. “If only we had a blueprint!” they cried.

Never fear, there is a silver lining in this story. Hidden away, in a secret closet within the Rational suite of development tools, is something called the Data Perspective. The Data Perspective comes with Rational Business Developer, InfoSphere Data Architect and other bundled products; and it is included in the free (yes free) download of IBM Data Studio.

Within the Data Perspective is the Data Design Project tool. Using a Data Design Project, a database engineer can reverse engineer an existing set of tables (or DDS files) into a graphical physical data model, in other words a blueprint! This can be done for an entire schema (library) or for only those core files required to support the business.

But wait, there’s more.

Unlike the pencil drawings of yore, or the collection of shapes in a presentation tool, with a touch of a button the database engineer can generate the SQL Data Definition Language (DDL) statements from the physical data model. And, let me catch my breath, the DDL will be generated no matter if the originating source was DDS or SQL DDL. That is too cool.

And I almost left out the best part – the compare tool.

Imagine if I could have taken those original blueprints of the house, changed them and then pushed a button and my home would magically be transformed to match the blueprint. Not possible with home re-engineering projects but it is available with the Data Perspective. I can compare the graphical model or blueprint to the physical implementation and the tool will generate the appropriate ALTER, DROP and/or CREATE DDL statements, in either direction. I can apply emergency changes to the DB2 for i database and then sync up the model.

Of course having a blueprint is one thing, getting the re-engineering process right is another.

All projects are going to require some boots on the ground. In other words, the developers who have to make the changes. These "engineers" will require a little more detail, especially when ripping apart existing programs to expose the inner workings.

Oh joy, there is another secret closet in the Rational Developer tool box – The Visual Application Diagram maker. This device can take RPG or COBOL code and present it in graphical form. And what’s more, the engineer can click anywhere in the diagram to display the underlying code.
Whether you’re a database engineer or application developer, it is now time for you to take your skills to the next level. If you are not using the Rational tools then acquire them now. If you are using them, then don’t be afraid to explore some of those secret closets.

And if you're afraid of the dark, please reach out, we can provide some hand holding.

Thursday, December 18, 2014

Resolve to Gain New Knowledge and Skill

As we approach the end of 2014 and look forward to next year, I want to encourage you to take stock of your knowledge and skill as it pertains to DB2 for i and data centric design and programming.

Your value, to employers, clients, partners, and colleagues will diminish over time unless you "sharpen the saw".

To that end, I want to call your attention to a couple public classes we are offering in Rochester, Minnesota (aka: the home office).


DB2 for i Advanced SQL and Data Centric Programming

2015, February 17-19 

Skills taught


DB2 for i SQL Performance Monitoring, Analysis and Tuning

2015, February 24-27 

Skills taught


If you need more detail on what the respective classes cover, or why the knowledge and skill are critical success factors in the world of database engineering, please contact me. And if you want to explore a private and/or customized session, we can assist there as well.

“Give me six hours to chop down a tree and I will spend the first four sharpening the axe.”

 ― Abraham Lincoln

Wednesday, October 8, 2014

Trust, but Verify

I am often asked about the risk of migrating to a new version/release of IBM i; "should we go to 7.1 or 7.2"?

The same can be said about moving to the latest technology refresh (7.1 TR9 and 7.2 TR1 were just announced by the way).

I prefer to talk about the rewards of keeping up with the advances in technology - keep the tool belt fully stocked so to speak.

So, should you install the latest and greatest?  My usual answer is "yes, and...".

Whether you are configuring new hardware, putting on a new set of group PTFs, installing the latest TR or migrating to IBM i 7.2, my sincere advice is based on an old Russian proverb:

Trust, but Verify

What this really means is, YOU should be testing the new hardware, testing the group PTFs, verifying the TR code or the latest version of IBM i.  And I don't mean give it a spin for a few days on the development system.  I'm talking about proper and adequate testing; a real verification of the features and functions. Find out for yourself, do they behave as advertised?

Now here is the issue...  proper and adequate testing must be based on science, and some art.

SCIENCE, as in, using the scientific method:

  • Purpose or Question
  • Research
  • Hypothesis
  • Experiment
  • Analysis
  • Conclusion

And ART, as in - you have to be clever about how, when and where you apply the science.  If you are not testing the business processes that produce the transactions occurring in the production environment, you are not actually verifying anything, nor are you mitigating any risk. You are just fooling yourself.  And if you cannot pin down the variables and repeat the process consistently, the experiment will be inconclusive, and a waste of time.  I don't know how many times I have been in awkward conversations that go something like this:

DB2 user: "we just upgraded, my queries don't run fast anymore"

Mike: "I'm sorry to hear this... do you have any information captured about how the queries were running prior to the upgrade"?

DB2 user: "no"

Mike: "can you tell me how the queries were running prior to the upgrade"?

DB2 user: "yes, fast"

Mike: *heavy sigh*

When it comes to DB2 data integrity and data processing, three fundamental things need to be tested and verified:

  1. Correct results
  2. Performance
  3. Scalability

Correct results is obvious - did my request or process produce the expected answer or result?

Performance gets a lot of attention - did my request or process complete in the time expected?

Scalability is much more difficult to understand - did my request or process complete in the time expected when running with the full data set and under the stress of all the normal production activity?

My recommendation is that you get in a position to test (and verify!) that the new hardware and/or software meets your requirements BEFORE implementing anything in the production environment.  And with that said, verify your rollback strategy if something does slip by.

When it comes to testing and verifying DB2 for i, the point person should be your database engineer. If you don't have one, now is a good time to establish the position, install the candidates, and provide training and support. Don't forget to give them clear responsibility and authority to do the job.

If you don't have, or don't want to invest in a full fledged testing environment, or you want the subject matter experts to look over your shoulder, make a visit to IBM Rochester, Minnesota and embark on a performance and scalability engagement.

If you would like to discuss the science and art of verifying DB2 for i, please contact me.  We are here to help you be successful, and to mitigate risk.

Monday, September 15, 2014

Setting the Right SQL Course

The following guidance and course correction are compliments of  DB2 Subject Matter Expert and Center of Excellence team member, Kent Milligan!

Having been around DB2 for i since I started my IBM career a few “years” ago, it’s been a lot of fun to watch the usage of SQL grow in the i community over time. Some folks are using SQL with new databases and applications, but the vast majority of people are using SQL to modernize existing databases and applications.

Moving an existing database from DDS to SQL DDL (Data Definition Language) is a pretty straight-forward process.  A new SQL statement is created to replace each DDS-created object in your DB2 for i database. And many IBM i customers have successfully completed this conversion from DDS to SQL DDL.

When it comes to modernizing data access in applications, the transition to SQL is more challenging.  A significant number of IBM i developers have struggled with this change because their natural reaction is to replace each native record-level access request with an SQL DML (Data Manipulation Language) statement.  They are so quick to move to SQL that they forget that SQL is designed for set-based processing.

I think this car tire repair picture does a good job highlighting the issue with performing one-for-one replacements with SQL DML - functionally your programs will continue to work, but application performance is going to suffer just like the speed and handling of this “repaired” car.