Named Arguments and Default Parameters for IBM i

IBM continues to deliver an amazing amount of enhancements for DB2 for i since IBM i 7.1 was released. If you’re not aware of these many enhancements, check out the IBM developerWorks - IBM i Technology Updates website. The IBM i Technology Updates website is a great resource to keep you up-to-date on new enhancements for DB2 for i. Even better, this site often has information regarding future enhancements allowing you to stay on top of the latest and greatest DB2 for i has to offer.

One of the newest enhancements for DB2 for i is Named Arguments and Defaults for Parameters for SQL and External Procedures. This enhancement is available with IBM i 7.1 DB Group PTF SF99701 level 18. If you’re unfamiliar with technology refreshes, you can get more information here. So what is this new enhancement? Basically, arguments for SQL and external procedures can now be given default values and/or use named parameters while calling the procedure.

Making Application Development More Flexible

Prior to this enhancement, calling SQL and external procedures was very rigid. Any call to the procedure had to use all the parameters and had to be in the correct order. Because of this rigidity, it was a very painful process to modify any of your existing procedures. All applications that called the procedure had to be identified and then updated to handle the modification.

The named arguments and default parameter enhancement provides flexibility to procedures by allowing a user to specify the order of the arguments in a way that makes sense to the caller of the procedure. This is accomplished by explicitly naming the parameter and the value supplied to that parameter. It also provides the capability of defining a parameter’s default value that’s used if the argument isn’t supplied when the procedure is called. This enhancement provides a lot of great benefits to users of SQL and external procedures, including the following:

Capability to add new parameters to procedures without modifying existing applications. If there’s a need to add additional parameters to an existing procedure, default parameters eliminate the problem of identifying and updating the applications that call the procedure. The enhancement can be used to update the procedure’s definition to include the new parameters at the end of the argument list with a specified default value. Existing applications can be selectively extended instead of having to coordinate changes to callers that don’t need to utilize the new parameters. The default values will be used for the new parameters if they aren’t specified during the call to the procedure.

Flexibility when calling procedures. Arguments can now be specified in any order that makes sense to the caller of the procedure by explicitly naming the parameter and the value supplied to that parameter. This can also help to reduce errors when calling procedures since the caller can explicitly name the parameters rather than trying to remember the order defined in the procedure. Now you can call SQL and external procedures with the same usability that CL command users are accustomed to utilizing.

Wide range of providing default values. There are several ways to specify the default values for the parameters when the procedure is created. The default can be a constant, a special register (i.e., CURRENT_DATE, CURRENT_TIMESTAMP, etc.), a global variable, an expression or the keyword NULL. When the procedure is invoked, the default value is used if the argument wasn’t specified or if the argument is specified as DEFAULT.

Fewer procedures. With the capability to specify default values and named parameters, a user can create a smaller set of procedures that can be used by a large number of users. Individual users would only need to specify the arguments they’re interested in and default values could be used for the arguments not specified.

Brett Leeser is a software engineer with the IBM i Final System Test team in Rochester, Minn.

Matt Wolfe is a software engineer with the IBM i Final System Test team in Rochester, Minn.

Marie Wilson is a software engineer with the IBM i Final System Test team in Rochester, Minn.

Michelle A. Schlicht is a staff software engineer with IBM. Michelle can be reached at

comments powered by Disqus



2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters