fhg
Volume 13, Number 4 -- February 20, 2013

Stored Procedure Parameter Defaults And Named Arguments In DB2 For i

Published: February 20, 2013

by Michael Sansoterra

Starting with IBM i 7.1 Technology Refresh 5, DB2 for i stored procedure parameters support a default value, and the CALL statement supports named arguments. (DB2 for i Group PTF Level 18 or higher provides this new functionality). These simple and related enhancements can have surprising benefits for code comprehension and code maintenance.

Let's look at creating a procedure with one or more parameters having a default value:

CREATE PROCEDURE DEV.CREATE_ORDER (
IN @CUSTOMER_ID INT,
IN @ORDER_DATE DATE DEFAULT CURRENT_DATE,
IN @ORDER_ID INTEGER DEFAULT (NEXT VALUE for DATALIB.ORDER_ID_SEQUENCE) ,
IN @ORDER_SOURCE VARCHAR(10) DEFAULT 'WEB') 
LANGUAGE SQL
BEGIN

END

In this example, three of the four parameters have assigned defaults. If values are not supplied or if the default values are specifically requested, the value or expression specified with the DEFAULT keyword will be passed to the procedure when executed. The first parameter with a default (@ORDER_DATE) is defaulted to the CURRENT_DATE special register.

The second defaulted parameter (@ORDER_ID) will use the next available number in a sequence as its default value. This is nice because if there are 10 procedures calling this procedure to create an order, none of the calling procedures need to contain the code to get the next order ID in the sequence (unless there is a special reason). Instead, they can just let the parameter default take care of it. That simplifies coding.

The last parameter (@ORDER_SOURCE) is defaulted to the literal value 'WEB'. Although not shown here, a scalar subquery can also be specified as a default value--that's pretty powerful stuff.

Now that defaults are defined for some of the parameters, there are new options for calling the procedure. Of course, there is the traditional call, supplying all parameters:

CALL DEV.CREATE_ORDER (1,'2013-03-12',100,'ERP');

Next, it is permissible to have a CALL statement that just passes the first (and only required) parameter while the omitted parameters are defaulted:

CALL DEV.CREATE_ORDER (1);

Please note that this example only works because the remaining parameters have a default. This syntax would not work if, for instance, parameter three did not have a default. If a parameter does not have a default, its value must be supplied in the CALL statement.

Another option is to use the DEFAULT keyword in place of a parameter value:

CALL DEV.CREATE_ORDER (1,default,default,default);

Further, parameter values and defaults can be supplied in any combination:

CALL DEV.CREATE_ORDER (1,default,default,'DATA ENTRY');

Finally, note that NULL and DEFAULT are not equivalent:

CALL DEV.CREATE_ORDER (1,NULL,default,'DATA ENTRY');

In this call, the @ORDER_ID parameter would receive a NULL value, not the next sequence number.

Defaults can be defined by a constant, special register, global variable, or an expression. The expression has limitations in that it can't refer to aggregates or, in my testing, other parameter names. Also, a default cannot be assigned to an OUT parameter.

Now let's look at the enhancement to the CALL statement for named arguments. Sometimes stored procedures can have an unwieldy parameter list. Let's say a stored procedure accepts a potentially large number of parameters. In this case the parameters provide filtering criteria when returning results to a customer service order search facility. Take this "Order Search" parameter signature for example:

CREATE OR REPLACE PROCEDURE ORDER_SEARCH (
IN ORDER_NUMBER INT DEFAULT NULL,
IN START_DATE DATE DEFAULT NULL,
IN END_DATE DATE DEFAULT NULL,
IN CUSTOMER_NAME VARCHAR(50) DEFAULT NULL,
IN ORDER_TYPE SMALLINT DEFAULT NULL,
IN ORDER_CLASS SMALLINT DEFAULT NULL,
IN ORDER_VALUE DEC(19,4) DEFAULT NULL,
IN ITEM_NUMBER CHAR(15) DEFAULT NULL,
IN ITEM_NUMBER_2 CHAR(15) DEFAULT NULL,
IN ITEM_NUMBER_3 CHAR(15) DEFAULT NULL,
IN CITY VARCHAR(50) DEFAULT NULL,
IN STATE_CODE CHAR(2) DEFAULT NULL,
IN POSTAL_CODE VARCHAR(10) DEFAULT NULL
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
 /* Dynamic SQL Code Goes Here */
END

If we want to search for orders with an item number "BIKE_26IN", the call would normally look like this:

CALL ORDER_SEARCH (NULL,NULL,NULL,NULL,NULL,
NULL,NULL,'BIKE_26IN',NULL,NULL,NULL,NULL,NULL)

Yuk!

However, because this procedure has default values specified, and because the CALL statement supports named parameters, we can reduce the CALL statement to this:

CALL ORDER_SEARCH (ITEM_NUMBER=>'BIKE_26IN')

The "=>" syntax is used to indicate a parameter name followed by the "argument" or value.

This second CALL example illustrates clearly which parameter is being filled in, and thus provides better documentation than the first call. Another benefit to using named parameters is that the procedure's signature can change without having to review all of the CALL statements. If you've ever had a developer insert a new parameter at the beginning of the parameter list and cause other CALLs to the procedure to break, you know why this new method is helpful!

As you might expect, you can specify multiple named arguments:

CALL ORDER_SEARCH (ITEM_NUMBER=>'BIKE_26IN', ORDER_TYPE=>2)

The order of the parameter names doesn't matter. If a default is not specified on a procedure parameter, then a value must be supplied.

Going back to the initial example, two named arguments can be passed by name like this:

CALL DEV.CREATE_ORDER (&@Customer_Id=>1,&@Data_Source=>'DATA ENTRY')

Parameter defaults and named arguments operate for both SQL and external (high-level language) stored procedures. Hopefully, we will see this default parameter functionality included with user-defined functions soon. In summary, these new features enhance code maintainability and readability. These features also exist in other versions of DB2 and can now be included when writing portable code within the DB2 family.


Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.


RELATED STORY

New in DB2 for i 7.1: Use Global Variables to Track Environment Settings



                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot


Sponsored By
WORKSRIGHT SOFTWARE

Do you need area code information?
Do you need ZIP Code information?
Do you need ZIP+4 information?
Do you need city name information?
Do you need county information?
Do you need a nearest dealer locator system?

We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

Just call us and we'll arrange for 30 days FREE use of either
ZIP/CITY or PER/ZIP4.

WorksRight Software, Inc.
Phone: 601-856-8337
Fax: 601-856-9432
E-mail: software@worksright.com
Web site: www.worksright.com


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

looksoftware:  Free Live Webcast: HTML5 & IBM i - Connect, Discover, Create. March 12 & 13
United Computer Group, Inc.:  Vault400 for secure online backup and disaster recovery solutions
Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars


 

More IT Jungle Resources:

System i PTF Guide: Weekly PTF Updates
IBM i Events Calendar: National Conferences, Local Events, and Webinars
Breaking News: News Hot Off The Press
TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan


 
The Four Hundred
PureApplication Systems Get Power7+, But Not IBM i

IBM Rochester Shows Off Its Agility

Storage And I/O Enhancements Come To Power Boxes

Mad Dog 21/21: The Post Office And Dell, Delivering Less, Hoping For More

SQL Server Not Best Ingredient In IBM i BI Pie

Four Hundred Stuff
PowerHA Installs Exceed 2,000 Globally, IBM Says

PowerTech Shines a Light on 'Black Hole' Commands

Vision Launches Cloud Initiative for RaaS

Cilasoft Monitors Additional Exit Points with IBM i Security Tool

ARCAD Updates IT Service Management Tool

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
February 16, 2013: Volume 15, Number 7

February 9, 2013: Volume 15, Number 6

February 2, 2013: Volume 15, Number 5

January 26, 2013: Volume 15, Number 4

January 19, 2013: Volume 15, Number 3

January 12, 2013: Volume 15, Number 2

TPM at The Register
Intel pits QDR-80 InfiniBand against Mellanox FDR

MTurbo 'invisible hand' control freak grabs more virty servers

IBM forges Power7+ PureApplication appliance

Arista wants to DANZ for high freaky traders

Cray readies XC30 supers for Ivy Bridge and coprocessors

Nvidia revenues fight the PC tide, but annual profits pinched

Scottish uni slams on the Accelerator to boost UK boffinry

Cisco wins tax battles with Uncle Sam, posts big bump in profits

More naysayers pour cold water on Dell LBO

Ericsson readies carrier-grade OpenStack for telcos and SPs

Rackspace rides OpenStack, lassos chubby, cloudy cash cow

Pertino uncloaks, fires 'cloud network engine' at Cisco

THIS ISSUE SPONSORED BY:

WorksRight Software
Help/Systems
System i Developer


Printer Friendly Version


TABLE OF CONTENTS
Stored Procedure Parameter Defaults And Named Arguments In DB2 For i

New CL String-Handling Functions

Getting Short-Term Maintenance For Your Power i Machine

Four Hundred Guru

BACK ISSUES




 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2013 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement