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

Note: The document accompanying this article is available for download here.

I love it when IBM enhances CL. When I hear of a new feature, my first thought is, “What can I do with this?” My mind has been spinning since Guy Vig first told me about CL’s new string-handling functions a few weeks ago. Let me tell you about these functions and some of the uses for them that have already occurred to me.

First, here are a few facts you need to be aware of:

• IBM delivered the new functions in two PTFs: SI49061 and SI48166.

I purchased a three-year maintenance contract when I bought my Power7 machine in 2010. The contract runs out in May but I need extended maintenance through July when I’m installing a new machine and returning the old machine to the leasing company. How do I extend my hardware/software maintenance for only two months without having to buy a one-year maintenance contract?

–Jean

This is a fairly common situation and one that IBM provides an out for in their maintenance contracts. Here’s how you can sign a one- or multi-year maintenance contract for your existing machine and then cancel the contract