Volume 10, Number 22 -- July 21, 2010

It's My (De)fault That You're a Zero

Published: July 21, 2010

by Ted Holt

Unless you say otherwise, numeric database fields have a value of zero and character fields are blank, right? Not necessarily. There's more to default field values than some i Gurus realize.

A field's default value is the value the system assigns to the field when an application does not specify a value. Default values come into play in several situations.

  1. A record (row) is added to (inserted into) a physical file (table) by means of a logical file or SQL view that does not contain one or more fields.
  2. RPG O specs list the fields of an externally described file, but not all fields are listed.
  3. An SQL insert command does not include one or more fields in the list of columns.
  4. New fields are added to the DDS of a physical file and Change Physical File (CHGPF) is used to rebuild the file.
  5. The Initialize Physical File Member (INZPFM) command is used with the RECORDS(*DFT) parameter to add records to a database file.

The default value of a field is based on three criteria:

  1. Whether the programmer specified a default value.
  2. Whether the physical file was created with DDS or SQL.
  3. The field's data type.

If you use an SQL command, such as CREATE TABLE, to create a new physical file, fields are null by default. If you use DDS, however, fields are only null-capable if you add the ALWNULL keyword.

For non-nullable fields, default values are zero for numeric fields, blanks for character fields, and current values for date/time/timestamp fields. If you're interested, here's something to try. Create a physical file with fields of different data types:

A          R DFTVALREC
A            CHAR           3A 
A            PACKED         7P 2
A            ZONED          3S 0
A            DATE            L
A            TIME            T
A            STAMP           Z

Add one record to the file.


Query the file to see the values in the record. Those are the default values.

            .00      0   2010-07-21  07.38.59  2010-07-21-

To assign your own default values, use the DFT keyword in DDS and WITH DEFAULT in SQL.

A          R DFTVALREC
A            CHAR           3A         DFT('ABC')
A            PACKED         7P 2       DFT(250)
A            ZONED          3S 0       DFT(-1)
A            DATE            L         DFT('2000-01-01')
A            TIME            T         DFT('00.00.00')
A            STAMP           Z         DFT('2000-12-31-24.00.00')

create table somefile
  (  KeyField    dec  (5,0),
     CharField   char (3)   with default 'XYZ',
     PackedField dec  (7,2) with default 1.5)

In addition to literal values, SQL lets you insert special values:

  • USER (the current user profile)

And if you want the default value to be null, you can make it happen, whether you use DDS or SQL.

Make it a habit to think about default values when you create new physical files (or SQL tables). For instance, shouldn't a sales order be a regular order by default, rather than a special order?

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

Sponsored By

Take it to the Summit!

Rise to the i can ... can you? challenge at the next RPG & DB2 Summit this October 12-14. Upgrade your skills - and your career - with the latest on RPG IV, embedded SQL, RPG & the Web, PHP, RDi, DB2, SQL tuning, more!

Learn practical, use-it-today tips and techniques from top experts Susan Gantner, Jon Paris, Skip Marchesani, Paul Tuohy, Scott Klement & others in a highly interactive, invigorating, fun environment. YOU have the power to keep the IBM i - and your skills - vital to your company.

Click to see the sessions.
Register by July 30 for just $1095!

Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Erwin 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

Help/Systems:  Drive your enterprise with event-driven scheduling. FREE white paper!
PowerTech:  FREE Webinar! An Auditor's View: Assess Your IBM i in 15 Minutes. July 28, 10 a.m. CT
COMMON:  Join us at the Fall 2010 Conference & Expo, Oct. 4 - 6, in San Antonio, Texas


IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
The iSeries Express Web Implementer's Guide: List Price, $49.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
Can the AS/400 Survive IBM?: List Price, $49.00
Chip Wars: List Price, $29.95

The Four Hundred
The Rest of the Power7 Lineup Is Coming August 17

Microsoft Azure: An AS/400 for Private and Public Clouds

Increase in IT Jobs Led by Contract Worker Demands

Mad Dog 21/21: Smart Cube Is IBM's Half-AS Imitation of Apple

QlikTech Soars in IPO

Four Hundred Stuff
'Birst'-ing Onto the Cloud-BI Scene

inFORM Introduces High Speed Batch Scanning

RTC Partners with Manthan to Sell BI to Retailers

10ZiG Welcomes WES 7 as New Thin Client OS

New GXS Analytics App to Live on Microsoft's Azure Cloud

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

System i PTF Guide
July 10, 2010: Volume 12, Number 28

July 3, 2010: Volume 12, Number 27

June 26, 2010: Volume 12, Number 26

June 19, 2010: Volume 12, Number 25

June 12, 2010: Volume 12, Number 24

June 5, 2010: Volume 12, Number 23

TPM at The Register
IBM zEnterprise 196 mainframe due July 22

RNA rejiggers server memory pooling

HP, Red Hat chase Solaris shops

NASA and Rackspace open source cloud fluffer

Dell proposes settlement with SEC

AMD sales up but ink still red

Ellison loses bid for US basketball franchise

So long then, Windows 2000

Dell OEMs server management from Microsoft

Neon Software sells cut-down zPrime for IMS

IDC: Second quarter PC sales hit the bull's eye

Top Solaris developer flees Oracle


WorksRight Software
System i Developer
RJS Software Systems

Printer Friendly Version

AAA Secures IBM i Server

It's My (De)fault That You're a Zero

Admin Alert: The Poor Manager's 5250 Single Sign-On

Four Hundred Guru


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-2010 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement