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.

When modernizing with SQL DML, you first need to step back and analyze what data request is that sequence of native record-level access operations performing? Often times a series of SETLL and CHAIN requests is really simulating a join operation that can be performed with a single SQL SELECT statement - instead of multiple SQL statements. Leveraging the set-based processing advantage of SQL is a critical success factor when it comes to application performance. 

There are resources to help you modernize with SQL using best practices including set-based processing.  One of the newer IBM Redbooks, Modernizing IBM i Applications from the Database Up, has several chapters devoted to the topic of modernizing with SQL.  The DB2 for i Advanced SQL Workshop introduces and explores SQL syntax from a set-based processing perspective. And you can always contact the DB2 for i Center of Excellence team for SQL knowledge and skills transfer services.

 Avoid the quick one-for-one replacement with SQL DML and keep your eye on the SET of operations!