Jump-start your SQL Development with new ACS SQL Generators

With the first day of Fall quickly approaching, it won’t be too long before us Minnesotans need to make sure we have jumper cables in our vehicles for the cold winter months. Hopefully, that’s later rather than sooner…

The new SQL generators for Db2 tables (& physical files) in IBM i Access Client Solutions (ACS) 1.1.9.3 provide a different type of jump-start. These recently released generators should help developers that are new to SQL by providing the option of generating Insert, Update, and Delete statements along with Stored Procedures that perform the same data change operations.

The following figure highlights the new Generate options available for a table with ACS 1.1.9.3. Prior to this latest round of enhancements, ACS only provided the ability to generate the table definition or a query to retrieve all the columns from a table.

Here's an example of the SQL produced by the Update and Delete statements generated for the Employee table in the Db2 sample database.

Notice that the generated statements include host variable placeholders to make it easier to embed the SQL statements in your high-level language programs. Also, notice how the ACS generator utilizes the IS DISTINCT predicate on the WHERE clause. The IS DISTINCT predicate is handy with null capable columns because it automatically accounts for the null values - either both values being compared must both be null or both of the non-null values must match.

Here's an example of the SQL generated by the Procedure->Delete option.

You may be wondering what’s the point of generating a stored procedure that wrappers an Insert, Update or Delete statement. The usage of stored procedures to support CRUD (Create, Read, Update, Delete) operations on database tables is a pretty common coding pattern/architecture. Some DBMS products advocate this approach because their stored procedures offer better performance for Create, Read, Update, and Delete operations. While this performance difference doesn’t apply to Db2 for i, the CRUD stored procedure approach does offer security and application architecture advantages.

ACS 1.1.9.3 currently only generates stored procedures for create (ie, insert), update, and delete operations. Adding a stored procedure generator for the read operation is on IBM’s list of future possible enhancements.

You should now see how these new SQL generators in ACS can provide a nice boost to the coding productivity of developers who are new to using SQL and/or stored procedures on IBM i.