Thursday, March 16, 2017

Using SAP on IBM i?

Are you running SAP on IBM i?

If so, seriously consider attending the upcoming IBM - SAP on Power Summit event to be held in Rochester, Minnesota USA on April 26 - 27.

More details about the event can be found here.

Late April is a great time to visit SE Minnesota.  The snow should be melted by then!

Friday, February 17, 2017

A Few More Tools

In case you missed it, you now have a few more tools for your "hero bag".

Earlier this week, IBM announced the IBM i 7.3 TR2 and IBM i 7.2 TR6 with a planned availability date of March 17, 2017.  If you are still running on IBM i 7.1, nothing new for your kit.  Please get on with upgrading to 7.3. Even Superman needed a cape to be a hero...  just saying

As mentioned in the previous post, one of the very nice things you can do with DB2 Web Query is take advantage of the SQL based IBM i services available.  It is a quick and easy way to demonstrate more value. With that in mind, there are even more services available with the new technology refreshes.

A couple other interesting additions are some JSON functionality and IBM i Access Client Solutions (ACS) enhancements.  If you are not exploring the use of ACS to interact with DB2 for i, now is a good time start. I suggest making it part of your DBE toolkit.

With regards to JSON, document stores and unstructured, non-relational databases, look for more information and insight to come. The notion of employing "other" databases that have advantageous attributes and specific characteristics tailored to today's ever expanding data storage and processing requirements is something we need to illuminate and discuss. Stay tuned.

As usual, Mr. Forstie has done a very nice job detailing the technology refreshes for 7.2 here, and 7.3 here.  Check it out.

And if you have any questions or concerns as to how to take advantage of all the tools and techniques, please let me know.

Friday, February 10, 2017

Want to be a Hero?

If so, follow these simple steps to gain more visibility from your business leaders, and to demonstrate more value to your boss.

Sound good?

Ok, let's get started...

Step 1.

Go here and read how to obtain the DB2 Web Query EZ-Install.  Please be sure that you understand the pre-requisites and instructions before proceeding with the installation process.

Step 2.

Try out some of the reports included in the package.  Get familiar with the features, functions and graphical elements represented in the examples.  Step back and look at the capabilities in a broad and expansive way.  Think about what might be possible.  Now, determine what reports best exhibit positive characteristics and attributes that will resonate with your business leaders.  You want to illicit a "wow!, we can do that?" response.

Step 3.

Identify one or more key business leaders who you believe need or want more timely and insightful information.  Focus on the idea of providing new and different insight, in new and different ways, visually.  Make an appointment to demonstrate what you have in the package.  You want 30 to 60 minutes of uninterrupted time.  Your goal is to show the potential of providing a lot more capability, effectively, by using new tools and new techniques.  Ultimately, you are seeking a green light to pursue a proof-of-concept or proof-of-technology using your own business' data to solve a real problem.  And if they ask a question, such as: "can we incorporate weather data or ask Watson a question", answer "yes!".

Step 4.

Do the demo.  Show off.  Be positive.  Represent your ability to show real value.  After the demonstration has completed, thank the business leader(s) for his or her time and attention.  Declare that you will be following up with them to determine the next steps (for the proof), and that you look forward to working together in the near future.  Leaders do not ask for permission.  Be a leader.


Now that you have illuminated what is possible and easily achievable, keep up the momentum.  Define, design and initiate a meaningful project to do more with your data.  Team up with the business leader to provide him or her with more insight and information.  Be their partner.

To minimize the technical and financial risks, while simultaneously increasing the probably of success, reach out to me or Doug Mack.

We can provide all manner of direction, guidance and assistance with turning your data into actionable information.  This includes preparing you for that all important first demonstration and initial impression.

Fundamentally, we are here to help you succeed, and to make you a hero!

Friday, December 23, 2016

A Retirement

No, not mine. Someone much more eminent.

It's with very mixed emotions that I open the blog to my good friend and long time associate Mr. Dan Cruikshank.  Before I hand it over to Dan, a few words if I may...

I have had the pleasure and privilege to work with, for and along side Dan for 20+ years, on four continents. As with most people who have experienced Dan's unique wit and wisdom, I am better off for it.

If you have heard of database "modernization", you can thank Dan - he invented it.

If you have used IBM Data Studio, you can thank Dan - he was the first to promote it.

If you have applied data-centric programming technique, you can thank Dan - he championed it.

Needless to say, Dan's fingerprints are all over everything we do with DB2 for i.  Good for us!

Without further ado, here is Dan...



The time has come for me to retire. I wasn’t sure so I wrote the following SQL procedure for confirmation:

     IN p_Employeer VARCHAR(3),
     IN p_Emp_Key CHAR(6),
     IN p_Monthly_Retire_Income INTEGER,
     OUT Retirement_status VARCHAR(40))

Career: BEGIN
   FOR ever AS
      M_I_Done_Yet CURSOR FOR
     SELECT birthdate, salary
     FROM employee
     WHERE empno = p_Emp_Key
        IF p_Monthly_Retire_Income >= salary/12
          AND p_Employeer = 'IBM'
          AND YEAR(CURRENT DATE) - YEAR(birthdate) >= 65
                 SET Retirement_status = 'Congratulations';
             SET Retirement_status = 'Hang in there';
           END IF;
END Career

It appears I have met my goals.

In case you’re wondering, the above code and corresponding result were accomplished using the Data Perspective which comes with IBM Data Studio (and other IBM products).

I was fortunate enough to enter the IT industry at a time when the costs of owning a computer were drastically cut. The computer industry was in the midst of a revolution, and IBM Rochester was at the forefront with the introduction of the System 3. This system introduced the RPG language to a new breed of computer programmers; many like myself, did not have college degrees. Others had degrees in non-IT areas, for example accounting. I believe that there was one thing that we all had in common: we were the first wave of computer nerds. We were shy, introverted, acted inappropriately at times, were picked on and made fun of. The simplicity of the RPG programming language gave many of us a way to become heroes. How satisfying was it to take someone’s idea, concocted during a coffee or lunch break, and deliver a result almost overnight. We referred to that as “from napkin to code”. Today we call it “agile”.

The System/38 was called the programmers “dream” machine. It provided an interactive interface for coding, compiling, testing and deploying programs called the “Programmers Menu”. Who would ever need more than that? Then came the AS/400 and the Program Development Manager (PDM). Then came WebSphere Developer Studio client. Then came Rational Developer for i. Then came IBM Data Studio. Then came…  

I believe that RPG, DB2 and SQL were three of IBM’s greatest inventions. I worked hard to master all of them, and was fortunate enough to share my knowledge and expertise with IBM customers worldwide. I never considered myself to be a fantastic programmer. I was always better at fixing someone's program than writing my own. You know what they say “Those that can do, do.  Those that can’t do, teach”.

My career has been filled with many ups and downs. Let me share with you a few ups:

               Sometimes you must move on, in order to move up.
               When someone steps aside, be ready to step up.
               Do not be afraid to speak up, but learn when to shut up.
               Giving in is not the same as giving up.
               The tallest person in the room is the one who stands up.
               Fresh up with 7 Up.

I would like to thank all of the people who have benefited me in some way throughout my career. The list is much too long to show here, as it includes everyone I have ever worked with. You have all helped in more ways than you ever know. I only hope that the advice and guidance I have provided to you over the years has been more beneficial than not.

I am also grateful to those who have allowed me to do so much in my career, especially those I have worked with at IBM. Working for IBM is one of my greatest achievements, and I am proud to be retiring as an IBM employee.

Tuesday, November 8, 2016

The Data Model Matters

For a couple of years now, the major theme of our DB2 for i Technical Forums held around the world has been: design it, build it, secure it, tune it.

During the "design it" portion of the Forum, we focus extensively on the data model. No surprise. Our vast experience tells us that, when it comes to implementing a solution that is flexible, extensible, agile and scalable, the data model matters.

Historically speaking, the S/38, AS/400, iSeries and IBM i development communities have embarked on building data centric applications without the presence of a database administrator. While this has in some ways been an advantage, it has also resulted in a gap. Specifically, a gap in the understanding of the science and art of relational data modeling.

To fill this gap, we routinely recommend standing up an IBM i database engineer (DBE). One of the important responsibilities of the DBE, is to design, implement and support a proper data model.

When it comes to getting the most out of DB2 for i, the data model matters.  By telling DB2 about your data centric business rules, the data element attributes, and the relationships between sets, DB2 can do more work on your behalf. This results in higher productivity, greater efficiency and better performance.

Define your date column as a DATE!  By telling DB2 the column value represents a date, the database management system knows more and can do more. The SQL Reference is filled with date manipulation functions.  Why would you ever write one when it's already provided - assuming of course you define your data elements appropriately.

When it comes to formulating and executing SQL queries, the data model matters. SQL is set oriented. As such, the proper definition of sets and their various relationship(s) are fundamental to developing good set based operations. Sure, DB2 can overcome issues with a poorly defined and implemented data model; but at some point, the amount of work it takes to connect the dots to find the answer to your crazy procedural query will overwhelm everyone's patience and budget.

When it comes to handling very large data sets and over coming limits to growth, the data model matters.  Performance and scalability are two different things; and they do not necessarily go hand in hand. As the data set grows, it is not guaranteed that the performance and throughput will stay in the acceptable range. Relational data modeling best practices can mitigate the risk of growing data sets, as well as provide solutions to over coming database size limits and restrictions. Designing (the data model) for growth and scalability will also pay dividends when your business decides to makes an acquisition resulting in the inevitable large leap in data quantity and transaction processing volumes.

A sound data model also matters when it comes to data governance and control. In other words, properly securing your rows and columns is made much easier with a good data model. By applying relational concepts and normalization rules, the task of isolating and protecting personal identifiable information (PII) with DB2 RCAC becomes much easier. On the other hand, carelessly and needlessly propagating PII data throughout the database makes it virtually impossible to secure the sensitive elements in any effective way.

A pop quiz: is it easier to protect your tax ID stored in one table, or your tax ID stored in 1,000 different tables?

Best practices when defining and implementing your relational data model include:
  • Follow the normalization rules (3rd normal form is a good starting point)
  • All columns have an appropriate type, length, precision and scale
  • Use only one key column to represent the relationship between any two tables
  • Be consistent with column naming conventions
  • Define and use primary, unique, referential and check constraints
  • Define and implement an initial indexing strategy
  • Define and implement views to assist the database users
  • Document the model and keep it current (IBM Data Studio makes this relatively easy)

Now, this all sounds well and good when designing a new data model. But what if you are facing a legacy application with a poorly designed data model decades old?

This is where our targeted database modernization and re-engineering strategy comes into play!

Database modernization and re-engineering, if approached in a financially and technically responsible manner, will provide increased flexibility, extensibility, agility and scalability.

If you want to know more about designing and implementing a new database, or if you are faced with the task of enhancing an existing database to meet new requirements, please reach out. We invented the art of modernizing AS/400 databases, and we can help with reinventing yours.

Tuesday, October 11, 2016

Are You Falling Behind?

Increasingly, we are finding that IBM i installations are falling behind. In many cases, perilously so...

Recently, we engaged a client who only applies PTFs when there is an actual problem encountered. That means, almost never. In other words, no proactive or preventive maintenance. No periodic updates or upgrades. And yes, they do have several planned maintenance windows available per year.

Another set of clients have just migrated to IBM i 7.1. That, after waiting several years to finally get "current". Unfortunately, they are miles away from current.

More and more we see IBM i shops who are in some kind of analysis paralysis as they attempt to make a decision to stay with IBM i or move off to some "other" platform. All the while their business is changing, expanding and branching out in ways that require a non-linear approach to information management and data processing. My experience shows that virtually all of these shops have business requirements that can be met very nicely using current technology available via the combination of Power+IBM i+DB2. Technology that is readily available to them, I might add.

Presently, the IBM i user community enjoys significant stability, as well as tremendous flexibility when it comes to OS and RDBMS features and functions. Not only are there three OS releases available and supported (7.1, 7.2 and 7.3), there are also periodic enhancements available in the form of a technology refresh or update. While a given update is typically delivered as a robust set of PTFs, you certainly should not view a Technology Refresh (TR) as a simple set of "fixes".

Hey Look, Another Technology Refresh!

Today, IBM is announcing yet another technology refresh, specifically 7.3 TR1 and 7.2 TR5.

Alas, if you are using 7.1, you are falling behind. There is no TR available for the n-2 release.

The general availability for the 7.3 and 7.2 updates should be in about a month, so you have time to begin your testing and installation planning process.

From the database point of view, what are some of the items we can expect from this TR?

As usual, the focus is on increasing capabilities around data centric processing and enhancing database engineer productively...

A new table function which works very much like XML_TABLE, allowing for JavaScript Object Notation data to be returned as rows and columns.

Provides a way to include (pull source code into) another SQL procedure/function.

PERCENT_RANK as an ordered OLAP specification (7.3 only)
A distribution function that returns a relative percentile rank of a row within an OLAP window.

Additional Services (via SQL)

Retrieve data from QHST

Retrieve a list of jobs by user(s)

Analyze an SQL statement and extract information about the DB object(s),  column(s) and UDF(s)

Misc. Performance Improvements

LPAD/RPAD functions
UDF in-lining (more complex UDFs can now be inline)

IBM Access Client Solutions - Database GUI
Enhancements to SQL Performance Center, Visual Explain and Run SQL Scripts


For more details on the new DB2 for i Technology Refresh, the illustrious Mr. Scott Forstie has what you need here.

For more information on all various TRs, go here.


What Should You Do Now?

Regardless of any future and far off "migration plans", get current with PTFs and TRs. That includes HIPER, Group and the latest Technology Refresh for your particular release, even 7.1.

While the various IT "leaders" are off trying to identify your future platform, you can get on with solving current business problems and providing immediate value with IBM i.

To facilitate keeping current (and relevant), stand up a DB2 for i sandbox. This can be a small but balanced LPAR or stand alone system running 7.3 plus TR1.

Take some dedicated time every week to discover, experiment, and try new database features and functions.  Show off your work to the higher ups.  Be a hero.

Please secure the personal identifiable information (PII) stored in your files and tables.

Start by asking, and answering the following question:  

Who has *ALLOBJ authority on any system where PII is stored?

After you get over the shock and awe of finding out just how exposed you are, embark on the serious journey of securing the at risk data. Begin with the correct application of IBM i object based security, then continue with separating roles and responsibilities (the security officer and the database engineer should have different levels of authority based on their respective responsibilities).

Take a good look at using DB2 Row and Column Access Control. It will allow you to apply a more granular level of security on the sensitive data. DB2 RCAC is available in 7.2 and 7.3, so yet another reason to get current.

Identify, train, and support an IBM i database engineer. Ensure this person has not only the responsibility, but also the authority to do the job.

The database engineer is the font of knowledge and skill around DB2 for i features, functions and benefits. The database engineer will not only keep your organization current, but also be the trusted advisor for when and how DB2 technology can be applied to solving data centric business problems!

Start using modern tools and modern techniques immediately.

Go get IBM Data Studio and learn to use it.

Go get Access Client Solutions and try it out.

And finally, do more with your data, right now.

Go get DB2 Web Query, create some compelling visualizations and demonstrate the value to your business leaders.  You can use that IBM i 7.3 sandbox to check out the advanced aggregation functions and OLAP specifications. Again, be a hero.

If you need more information or assistance with DB2 for i features and/or data centric programming technique, please do not hesitate to reach out.

Monday, September 19, 2016

Doug Mack joins the Blogosphere

I am excited to see that my long time colleague and good friend Doug Mack has joined the blogging community!

Doug and I began working together some time back in 1994. That was the period during which the idea of warehousing data and performing analysis on consolidated information began hitting the market.

Also back then, Doug was instrumental in creating and leading the AS/400 Data Warehousing movement, telling audiences all over the world that, "yes, you can use AS/400 for business intelligence". It should come as no surprise, that today Doug continues to provide insight through his discussion of query modernization and report re-engineering, all in pursuit of delivering more value to your business.

I invite you to visit and follow Doug's new DB2 Web Query blog, where he will be sharing knowledge and know how on getting the most value out of your IBM i data. 

And yes, you can use IBM i for business intelligence.

Welcome Doug!