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.

INZPFM FILE(SOMEFILE) RECORDS(*DFT) TOTRCDS(1)

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

....+....1....+....2....+....3....+....4....+....5....+....6....+....7...
CHAR     PACKED   ZONED  DATE        TIME      STAMP
            .00      0   2010-07-21  07.38.59  2010-07-21-07.38.59.766261

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)
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP

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