Learn how using DEFAULTs in DB2 for i SQL can improve code management.

With respect to computer systems, Dictionary.reference.com defines a "default" as:

value that a program or operating system assumes, or a course of action that a program or operating system will take, when the user or programmer specifies no overriding value or action.

Defaults are useful because they represent a common, standard value or processing action to take unless an alternative value or action is needed. Using defaults in DB2 for i can simplify development because developers can insert common, valid data, into table columns without necessarily having to concern themselves with "what value" is required.

Likewise, function and stored procedure parameters can be defined with defaults such that the developer doesn't have to investigate what value to pass. Further, the use of defaults allows a single value to be defined in one place such that a given default value represented by a literal or scalar expression does not have to be repeated throughout code, making code maintenance easier.

This tip will briefly examine how to define defaults in DB2 for i SQL for tables, functions, global variables, and stored procedures.

CREATE TABLE

A default can be specified on a CREATE TABLE statement to automatically give a column a certain value (unless a special value is required):

CREATE TABLE BillOfMaterials FOR SYSTEM NAME BOM (

   BillOfMaterialsID FOR BOMID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,

   ProductAssemblyID FOR PRDASMID int,

   ComponentID FOR COMPID int NOT NULL,

   StartDate Date NOT NULL DEFAULT CURRENT_DATE,

   EndDate Date DEFAULT NULL,

   UnitMeasureCode FOR UOMCODE nchar(3) NOT NULL,

   BOMLevel smallint NOT NULL,

   PerAssemblyQty FOR PERASMQTY decimal(8, 2) NOT NULL DEFAULT 1,

   ModifyStamp FOR MODSTAMP Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) RCDFMT BOMR;

When data is inserted into a table, if values are not supplied for the columns defined with defaults, DB2 for i will fill in the values based on the defaults in the table's definition. When defining a DEFAULT on a table column, the options are limited in DB2 for i to an identity value (aka an autoincrementing number managed by the database engine), a literal, or a special register such as USER or CURRENT_DATE. Unfortunately, scalar functions and global variables cannot be specified in a DEFAULT clause.

Also note that columns with certain data types, such as XML, do not allow a DEFAULT value.

Unless circumstances dictate otherwise, a developer need not worry about what to INSERT into the columns defined with default values because they're defined on the table itself. Although it may seem unnecessary, defining a column with DEFAULT NULL (as shown for column EndDate) does indicate to developers that a NULL value is normal and expected. What developer out there hasn't looked at a table definition and wondered what value a column should contain or whether a NULL is legitimate?

A column default is best utilized when there is a common value that is used the majority of the time, when there is a specific "acceptable" value that works well with the application, for identity columns, or when a special register can provide a system-supplied value such as a current timestamp or current user.

INSERT Statement

When inserting data into a table, a column default will be used in a few circumstances:

#1) When a column name is omitted from an INSERT statement the defined DEFAULT will be used for the INSERT

-- Defaulted columns are filled in using DEFAULT expression

INSERT INTO BillOfMaterials (ProductAssemblyID,ComponentID,UnitMeasureCode,BOMLevel)

VALUES(1,1,'EA',1)

Referencing the CREATE TABLE example above, the BillOfMaterialsID, StartDate, EndDate, PerAssemblyQty, and ModifyStamp columns are assigned the default values specified in the table definition. If a default has not been defined for an omitted column, then a NULL will be assigned to the column (and the column definition better allow for NULLs!).

#2) When the column name is specified and the DEFAULT keyword is used for the column's value

-- Using the default keyword, start date is defaulted to table's DEFAULT value

INSERT INTO BillOfMaterials (ProductAssemblyID,ComponentID,UnitMeasureCode,BOMLevel,StartDate)

VALUES(1,1,'EA',1,DEFAULT)

In this example, StartDate is specified in the column list, and it is assigned the special "DEFAULT" keyword as the value to use, which simply sets the column's value to the column's defined default.

Of course, a default is ignored when specifying a literal or an expression to be inserted into a column:

INSERT INTO BillOfMaterials (ProductAssemblyID,ComponentID,UnitMeasureCode,BOMLevel,StartDate)

VALUES(1,1,'EA',1,'2015-01-01')

MERGE or UPDATE Statements

As with INSERT, the UPDATE and MERGE statements can specify the DEFAULT keyword as the expression to use for a target column.

UPDATE BillOfMaterials

  SET PerAssemblyQty=DEFAULT,

      ModifyStamp=DEFAULT

WHERE PerAssemblyQty<=0;

MERGE follows the same rules as INSERT and UPDATE:

MERGE INTOBillOfMaterials Target

USING (

SELECT *

 FROM (VALUES

  (1,1,'EA',1),

  (2,1,'EA',1),

  (7,1,'LB',1),

  (8,1,'EA',1)

) x(ProductAssemblyID,ComponentID,UnitMeasureCode,BOMLevel)) Source

ON Target.ProductAssemblyId=Source.ProductAssemblyId

AND Target.ComponentId=Source.ComponentId

WHEN MATCHED THEN

   UPDATE

  SET UnitMeasureCode=Source.UnitMeasureCode

      PerAssemblyQty=DEFAULT,

      ModifyStamp=DEFAULT

WHEN NOT MATCHED THEN

   INSERT (ProductAssemblyID,ComponentID,UnitMeasureCode,BOMLevel,ModifyStamp)

   VALUES (Source.ProductAssemblyID,Source.ComponentID,Source.UnitMeasureCode,Source.BOMLevel,Default)

;

ALTER TABLE

If you want to add a new column to an existing table that contains rows, and the new column is defined as “not nullable”, you must specify the default keyword and a value. The specified value will be used to initialize the new column's row values.

The following ALTER TABLE statement will fail if the table has rows because the column is defined with NOT NULL and DB2 for i doesn't have a value to put in the column:

ALTER TABLE BillOfMaterials

ADD CreateStamp TIMESTAMP NOT NULL

In contrast, this ALTER TABLE statement will populate the new CreateDate column with the value of the CURRENT_TIMESTAMP special register:

ALTER TABLE BillOfMaterials

ADD CreateStamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Global Variables

Global variables (new to IBM i 7.1) are scalar values created using the CREATE VARIABLE statement. They are scoped to an SQL session (i.e., a SQL connection for a given job) and can be used as a work area to share information between interactive SQL statements, SQL routines, and triggers.

Here is an example of creating a variable called SHIP_DATE that can be populated and used by many DML SQL statements (remember, a variable can be used in an expression):

-- Define Variable

CREATE VARIABLE ADVWORKS.SHIP_DATE DATE;

-- Set Ship Date to be yesterday's date

SET ADVWORKS.SHIP_DATE=CURRENT_DATE - 1 DAY;

-- Get Shipments based on global variable value

SELECT * FROM SHIPMENTS WHERE SHIP_DATE=ADVWORKS.SHIP_DATE;

By adding the DEFAULT keyword, an expression can be used to automatically set a default value for the variable:

-- Assume Yesterday as the last ship date

CREATE OR REPLACE VARIABLE ADVWORKS.SHIP_DATE DATE

DEFAULT (CURRENT_DATE - 1 DAY);

A default can also be a scalar query expression:

-- Use the last shipment date prior

-- to today as the default ship date

CREATE OR REPLACE VARIABLE ADVWORKS.SHIP_DATE DATE

DEFAULT (

    SELECT SHIPDATE

      FROM ADVWORKS.SALESORDERHEADER

     WHERE SHIPDATE<CURRENT_DATE

  ORDER BY SHIPDATE DESC

  FETCH FIRST 1 ROW ONLY)

;

In this case, instead of assuming yesterday's date, the default value for the SHIP_DATE global variable will be calculated on the fly by looking up the last ship date used prior to today. If a default is defined, whenever the global variable is read, its default is returned.

A global variable can be overridden using SET:

SET ADVWORKS.SHIP_DATE='2014-08-30';

It can be reverted to the default as well:

SET ADVWORKS.SHIP_DATE=default;

Global variables can reference one another:

CREATE VARIABLE ADVWORKS.CURRENT_GROSS_SALES DEC(19,4) DEFAULT

(SELECT SUM(SUBTOTAL)

  FROM ADVWORKS.SALESORDERHEADER

 WHERE SHIPDATE=ADVWORKS.SHIP_DATE

)

Global variable DEFAULTs are great when using logic based on values in the database. However, there are no parameterization options, unless you want to populate other global variables to serve as parameters! When parameters are needed, then scalar functions are usually a better choice to implement specific logic.

Global variables coupled with default values are particularly useful because they allow the centralized storage of business logic. In the SHIP_DATE example, the logic to figure out the working SHIP_DATE is actually the last business day prior to today. It's better to reference the global variable in 100 SQL statements than to repeat the default expression 100 times!

Incidentally, when a CREATE VARIABLE statement is issued, the global variable is implemented as a service program (*SRVPGM) object behind the scenes.

CREATE FUNCTION and CREATE PROCEDURE Parameters

Parameter DEFAULTs for procedures have only been available in IBM i 7.1 TR5 or later. Parameter DEFAULTs for functions are available starting with IBM i 7.2.

When writing functions and procedures, it is typical for a developer to inspect a parameter for a NULL to invoke "default" logic. For example, if a NULL is passed to this function, logic is invoked to use the CURRENT_DATE register.

CREATE OR REPLACE FUNCTION ADVWORKS.SHIPVAL

(@SHIPDATE DATE)

RETURNS DEC(11,2)

DETERMINISTIC

BEGIN

   RETURN

   (SELECT SUM(SUBTOTAL)

      FROM ADVWORKS.SALESORDERHEADER

     WHERE SHIPDATE=CASE WHEN @SHIPDATE IS NULL

                         THEN CURRENT_DATE

                         ELSE @SHIPDATE END);

END;

This works well except in cases when NULL will be a legitimate parameter value or where NULL already means something specific to the application.

In DB2 for i 7.2, the DEFAULT keyword can be specified on the parameter itself so that no special code and no "special meaning values" are required:

CREATE OR REPLACE FUNCTION ADVWORKS.SHIPVAL

(@SHIPDATE DATE DEFAULT CURRENT_DATE)

RETURNS DEC(11,2)

DETERMINISTIC

BEGIN

   RETURN

   (SELECT SUM(SUBTOTAL)

      FROM ADVWORKS.SALESORDERHEADER

     WHERE SHIPDATE=@SHIPDATE>);

END;

The function can be invoked with the "DEFAULT" parameter value which tells the function to use CURRENT_DATE:

VALUES (ADVWORKS.SHIPVAL(DEFAULT));

The benefit of using DEFAULT is that if the DEFAULT changes (say, from CURRENT_DATE to CURRENT_DATE - 1 DAY):

CREATE OR REPLACE FUNCTION ADVWORKS.SHIPVAL

(@SHIPDATE DATE DEFAULT (CURRENT_DATE - 1 DAY))

Then the change needs to be done only in the function. All SQL invocations passing the DEFAULT keyword will adjust accordingly to the function's revised default.

DB2 for i allows global variables to serve as a function's DEFAULT. Say the CURRENT_SHIP_DATE global variable retrieves the last shipment date in the shipments table. This is superior to the CURRENT_DATE - 1 DAY expression because the latter won't work for weekends or holidays if the business isn't shipping. Variable CURRENT_SHIP_DATE can be the default for the @SHIPDATE parameter of the SHIPVAL user-defined function (UDF):

CREATE OR REPLACE FUNCTION ADVWORKS.SHIPVAL

(@SHIPDATE DATE DEFAULT ADVWORKS.CURRENT_SHIP_DATE)

RETURNS DEC(11,2)

DETERMINISTIC

BEGIN

   RETURN

   (SELECT SUM(SUBTOTAL)

      FROM ADVWORKS.SALESORDERHEADER

     WHERE SHIPDATE=@SHIPDATE);

END;

;

Impressively, a scalar query can also serve as a DEFAULT value:

CREATE OR REPLACE FUNCTION ADVWORKS.SHIPVAL

(@SHIPDATE DATE DEFAULT

   (SELECT SHIPDATE

      FROM ADVWORKS.SALESORDERHEADER

  ORDER BY SHIPDATE DESC

FETCH FIRST 1 ROW ONLY))

RETURNS DEC(11,2)

...

The only potential issue when defining a default in this manner is that you can't reference any other function parameter values in the subquery to limit results, etc.

Using DEFAULT is superior to implementing a NULL or "special value" hack because a default expression is clearly defined on the function parameter, and the use of the DEFAULT keyword as a parameter value clearly indicates to a developer the intended use of the parameter (whereas NULL may be unclear.)

Just to round out the topic, here is an example of a stored procedure with default parameters defined:

CREATE OR REPLACE PROCEDURE ADVWORKS.Process_Shipments (

CUSTOMER_TYPE   INT,

INCLUDE_PARTIAL IN CHAR(1) DEFAULT 'Y',

SHIP_DATE       IN DATE DEFAULT (CURRENT_DATE - 1 DAY)

)

BEGIN

   /* More Logic Here */

END

The first parameter (CUSTOMER_TYPE) has no default, so it must be passed in a CALL statement for the procedure to run. The remaining two parameters (INCLUDE_PARTIAL and SHIP_DATE) have defaults. The procedure’s parameter values will be assigned the default values if the parameters are omitted in the CALL statement (Example 1) or if the default keyword is used on the CALL (Example 2):

-- Example 1: omit defaulted parameters

CALL ADVWORKS.Process_Shipments (1)

-- Example 2: include default parameters

CALL ADVWORKS.Process_Shipments (1,default,default)

-- Example 3: name two of the three parameters

CALL ADVWORKS.Process_Shipments (CUSTOMER_TYPE=>1,SHIP_DATE=>default)

-- Example 4: use a specific value for INCLUDE_PARTIAL

CALL ADVWORKS.Process_Shipments (CUSTOMER_TYPE=>1,INCLUDE_PARTIAL=>'N')

System Catalog

Finally, information about column, variable, and parameter defaults can be found in the various catalog views as shown below:

-- Find table columns defined with DEFAULT values in DEV schema

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,

DATA_TYPE,HAS_DEFAULT,COLUMN_DEFAULT

 FROM QSYS2.SYSCOLUMNS

WHERE TABLE_SCHEMA='DEV'

  AND HAS_DEFAULT<>'N'

-- Find global variables with DEFAULT values in ADVWORKS schema

SELECT VARIABLE_SCHEMA,VARIABLE_NAME,DATA_TYPE,DEFAULT

 FROM QSYS2.SYSVARIABLES

WHERE VARIABLE_SCHEMA='ADVWORKS'

  AND DEFAULT IS NOT NULL;

-- Find routines with parameter DEFAULTs in ADVWORKS schema

SELECT R.SPECIFIC_SCHEMA,R.SPECIFIC_NAME,R.ROUTINE_TYPE,
P.ORDINAL_POSITION,PARAMETER_MODE,PARAMETER_NAME,DATA_TYPE,DEFAULT

 FROM QSYS2.SYSROUTINES R

 JOIN QSYS2.SYSPARMS P ON P.SPECIFIC_SCHEMA=R.SPECIFIC_SCHEMA

                      AND P.SPECIFIC_NAME=R.SPECIFIC_NAME

                      AND P.DEFAULT IS NOT NULL

WHERE ROUTINE_SCHEMA='ADVWORKS'

Delve into Defaults

Defaults benefit developers by defining "normal" expected values to be included in specific table columns. Moreover, when used with global variables, default values offer a way to write code that's easier to maintain by encapsulating a common scalar expression within a variable instead of repeating the expression throughout numerous SQL statements. Finally, using parameters in functions and procedures allows a developer to easily learn something about the expected input, and helps avoid the use of "hack" values such as a NULL to implement default logic.

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL