Copied Cursor Name Chaos

 

As our IBM Technology Expert Labs team performs SQL performance assessments for clients, we get the opportunity to review a wide variety of SQL statements.  These SQL statements run the gamut from simple to head-scratching to inefficient. 

One inefficient SQL coding technique that I’ve recently seen way too much is the same cursor name being used over and over again. I was a developer once, so I understand the common coding practice of copying existing code (i.e., cursor declaration) and customizing the copied code to meet your needs. However, the customization of copied cursor code should include changing the cursor name and I’ll explain why. 

Inputting a cursor name like C1 or CUR1 is fast and easy, but there are a couple of reasons why you should avoid reusing the same cursor name in your application programs. The reasons really come down to avoiding chaos which is defined as complete disorder and confusion.  The cursor name chaos impacts both the Db2 for i engine and your IBM i team.

First, duplicate cursor names can add a small amount of overhead to the runtime execution of your cursors. Db2 creates runtime data structures at program execution time to support the usage of your cursors. Once these runtime data structures are created for a cursor, Db2 tries to reuse the runtime data structures on future usages of that cursor within a job or database connection.  Db2 uses the cursor name to find these cached runtime data structures.  If your program has a half dozen cursors all named C1, then Db2 must do more work to figure out which runtime data structures belongs to the 6 different cursors named C1. If each of the cursors in the program had a unique name, Db2 is able to more quickly find the associated runtime data structures to reuse.

Second, duplicate cursor names add overhead and complexity to usage of SQL analysis tools such as those found in the IBM i Access Client Solutions (ACS) SQL Performance Center. Below, you’ll find a screen capture from my recent usage of the Plan Cache Snapshot tooling on a client’s snapshot. 

I think you’ll agree that the repeated cursor names make it difficult to differentiate between the long list of C1 cursors. At first glance, you can be tricked into thinking that they’re all the same cursor. The user must scan their eyes further down the cursor declaration to find the C1 cursor that they’re trying to analyze and tune. Making the Statement column wider helps, but I think it’s easier when the leading characters of a cursor definition clearly define the cursor.

At this point, hopefully you’re convinced that cursor names matter. If so, the next logical question to ask is: how should I name my cursors? I believe the best approach is to use a name that describes the cursor result set from a business perspective. Cursor names like LatePayments or LowInventory would be good examples of this approach. If you think the business perspective approach requires too much thinking, then consider using the program name as a suffix or prefix.  For example, the two cursors declared in PgmA might be C1_PgmA and C2_PgmA. 

Either of these cursor naming approaches is going to eliminate the overhead of finding the cached runtime data structures. In addition, they provide context when you’re reviewing a list of queries using ACS tools to analyze the Plan Cache or a Plan Cache Snapshot. More descriptive cursor names definitely make it easier to understand the who and why related to the usage of a cursor.  

Now that you understand there’s more than meets the eye when it comes to cursor names, I hope that you’ll be updating the SQL coding standards at your shop to avoid the chaos.