No Seats At Cruikshank’s SQL Database Sessions

August 15, 2016 Dan Burger

Dan Cruikshank’s presentations at the OCEAN Tech Conference last month were packed. Conference attendees had plenty of session options running opposite of Cruikshank, but his topics enticed IBM i advocates like free ice cream cones in the park on the Fourth of July. The seats are filled by folks hungry for technical advice on SQL, database efficiency, and business value. He gave them sessions on database re-engineering, reusable SQL procedures, modernizing IBM i applications, and data-centric programming.

Based on “hands-up” session surveys during similar sessions he presents around the world, Cruikshank is convinced that programmers are increasing their efforts to write modular, reusable code. On the front end, it’s a key to programming more quickly and with fewer mistakes. On the backend, it reduces the burden of code maintenance. The cost savings related to both instances can be substantial.

Dan Cruikshank’s SQL and DB2 for i sessions at the OCEAN Tech Conference were a major attraction.

“At the beginning of my sessions, I ask how many people are using ILE. Almost everyone raises their hands,” Cruikshank says. “Then I ask how many are using modular (reusable) service programs. Not many hands go up.”

So a starting point for Cruikshank sessions is explaining how a reusable procedure can be called from multiple procedures. Step two is explaining how flexible procedures can be reused and how they accept different inputs and produce output based on those inputs. That leads to dynamic SQL and procedures that are written to adapt to business needs that are subject to change.

If you are a subscriber to IT Jungle newsletters, you are aware of the emphasis IBM has placed on the DB2 for i integrated database with each release of the IBM i operating system and all the Technology Refreshes in between major releases. You know that IBM stopped enhancing DDS many years ago and all its database enhancements have been SQL (DDL) ever since. Its message to IBM i shops is to embrace data-centric programming. At the end of this article, you’ll find numerous links to related articles in the ITJ archives.

To nudge his session attendees in this direction, Cruikshank, points out two IBM software products that are free: Data Studio and DB2 Express-C. Together they can be used to create a functioning DB2 environment. As a member of the IBM Rochester Lab Services DB2 for i team, Cruikshank knows this territory very well.

Data Studio can be used to create, test, debug, and deploy database routines, including stored procedures and triggers. It allows developers to browse and edit data in tables, create and run SQL statements, and debug SQL and Java stored procedures, among many additional capabilities. It’s a graphical development tool based on Eclipse technology, which means it integrates nicely with Rational Developer for i. One of its key attributes is that it enables developers and administrators to create and manage heterogeneous database environments. That means the SQL can come from Oracle, SQL Server, and DB2 for LUW, for example.

Because Data Studio has the capability to manage multiple database environments, it makes me wonder why IBM has separate tools for the three DB2 databases. If the tool is database agnostic, why not have a single tool? That’s a question for Scott Forstie, the DB2 for i business architect.

Data Studio supports IBM i 7.2, but not 7.3. That seems odd because the companies that have already moved to 7.3 would probably be companies most likely to use Data Studio. Apparently the DB2 for i development team had its hands full with temporal and OLAP support delivered in 7.3 and so Data Studio remained on the to-do list. Maybe the early adopters of 7.3 will let IBM know if they think support for Data Studio is important.

During the early evolution of Data Studio, it was bundled with the Rational tools for Power. Later, when the Rational tools were rebranded as Rational Developer for i, many of the advanced functions in Data Studio were removed. Those functions are back now and Data Studio is a standalone product available at no cost. Previously the purchase of the Rational tools package was required.

For more information and access to the free download of Data Studio, click on this link.

DB2 Express-C is a no-charge community edition of DB2 database software, which provides the core features of more scalable DB2 editions. It is designed for quick and easy setup and use and provides SQL developers with a non-production database before deployment.

It is similar to DB2 Express, but a few differences are worth noting. Express-C has lower CPU and memory limits. Express-C is free. Express is not. And Express-C version is unsupported–regular DB2 Fixpacks cannot be applied. IBM does not release fixes, but it does publish updated installation images and remove old ones. Upgrading Express-C to Express requires a reinstall of DB2.

On the topic of data perspective, Cruikshank notes Data Studio functionality does not require source code and it avoids tooling that is specifically IBM i oriented.

“This is important to companies looking five or ten years into the future. The point is being ready to adapt to whatever comes along,” he says. “Data Studio doesn’t know anything about RPG or DDS.”

There are tools available from IBM i vendors that are concerned with data mining source code and finding the hidden business rules that probably should be in the database to realize the full value of data-centric programming. Those that are built on the open source Eclipse framework can plug into Data Studio and Rational Developer for i.

Products from the IBM i third-party vendor community include X-analysis from Fresche, Transformer DB from ARCAD, Xcase from Resolution, and Adsero Optima from TEMBO.

Modernization, in the big picture, involves rethinking the creation and maintenance of applications that have served well, but are in need of enhancement. These tools take into account application modernization as well as database modernization and understanding the makeup of the applications is as important as understanding the makeup of the database. At their most basic level, they automate the conversion of DDS to SQL, uncovering relationships and understanding impacts. Compared to manually attempting to accomplish this, the analysis tools save days, weeks, and months depending on the scope of the project.

“A good first step in standardizing existing IBM i applications is to convert non-standard query commands to industry standard SQL,” Cruikshank points out while noting that tools and methodologies are significantly different with improvements in functionality and productivity.

Taking into account that IBM offered Structured Query Language (SQL) as an alternative to DDS for creating databases when the AS/400 was introduced in 1988, it’s been a very long road leading to the type of database modernization that IBM i shops are undertaking today. But there’s a reason Cruikshank’s sessions are attracting crowds. Organizations are figuring out where they want to be in five or 10 years and making plans to make it happen.

Recommended Reading:

SQL Procedures, Triggers, and Functions on IBM DB2 for i

Modernizing IBM i Applications from the Database up to the User Interface and Everything in Between

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search