22
Fri, Nov
1 New Articles

TechTip: The Many Dimensions of SQL DEFAULT Values

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: