The built-in data types are provided with DB2. DB2 supports a wide range of data types for your business need. A summary of DB2 built-in data types are shown in Figure 3.1.

Figure 3.1. DB2 built-in data types.

DB2 for iSeries and zSeries supports the ROWID data type. A ROWID data type is one that uniquely identifies a row. A query that uses ROWID navigates directly to the row because the column implicitly contains the location of the row. When a row is inserted into a table, DB2 generates a value for the ROWID column, unless one is supplied. If it is supplied, it must be a value that was previously generated. The value of ROWID cannot be updated and does not change, even after table space reorganizations. There can only be one ROWID column in a table.

There are six numeric data types in DB2. Their precisions and value ranges are listed in Table 3.1.

DB2 supports both single-byte and double-byte character strings. DB2 uses 2 bytes to represent each character in double-byte strings. Their maximum lengths are listed in Table 3.2.

You can also specify a subtype for string data types. For example, CHAR and VARCHAR columns can be defined as FOR BIT DATA to store binary data. On iSeries, other subtypes can be specified such as FOR SBCS DATA, FOR DBCS DATA, and CCSID. On zSeries, other subtypes that can be specified are FOR SBCS DATA and FOR MIXED DATA.

DB2 date and time data types include DATE, TIME, and TIMESTAMP. The TIMESTAMP data type consists of both the date part and the time part, while DATE and TIME data types only deal with the date and the time component, respectively. Their limits are listed in Table 3.3.

The last data type in Figure 3.1, DATALINK, is used to work with files stored outside the database. It is not covered in this book.

Dates and times are the data types that differ the most among Database Management Systems (DBMSs). This section shows you examples of some of the basic date and time manipulations.

You can get the current date, time, and timestamp by using the appropriate DB2 special registers:

SELECT CURRENT DATE      FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT TIME      FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP are three DB2 special registers. Another useful DB2 special register for date and time operation is CURRENT TIMEZONE. You can use it to get the CURRENT TIME or CURRENT TIMESTAMP adjusted to GMT/CUT. All you need to do is to subtract the CURRENT TIMEZONE register from the CURRENT TIME or CURRENT TIMESTAMP:

SELECT CURRENT TIME - CURRENT TIMEZONE      FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT TIMESTAMP - CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY1;

Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:

SELECT YEAR (CURRENT TIMESTAMP)        FROM SYSIBM.SYSDUMMY1;
SELECT MONTH (CURRENT TIMESTAMP)       FROM SYSIBM.SYSDUMMY1;
SELECT DAY (CURRENT TIMESTAMP)         FROM SYSIBM.SYSDUMMY1;
SELECT HOUR (CURRENT TIMESTAMP)        FROM SYSIBM.SYSDUMMY1;
SELECT MINUTE (CURRENT TIMESTAMP)      FROM SYSIBM.SYSDUMMY1;
SELECT SECOND (CURRENT TIMESTAMP)      FROM SYSIBM.SYSDUMMY1;
SELECT MICROSECOND (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

You can also extract the date and time independently from a timestamp:

SELECT DATE (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
SELECT TIME (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

The date and time calculations are very straightforward:

SELECT CURRENT DATE + 1 YEAR                           FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT DATE + 3 YEARS + 2 MONTHS + 15 DAYS     FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT TIME + 5 HOURS - 3 MINUTES + 10 SECONDS FROM SYSIBM.SYSDUMMY1;

DB2 also provides many date and time functions for easy date and time data manipulation. For a complete list, refer to the SQL Reference corresponding to your platform.

A few date and time functions are used here as examples to show you how you can work with date and time data in DB2. To calculate how many days there are between two dates, you can subtract dates as in the following:

SELECT DAYS (CURRENT DATE) - DAYS (DATE('2000-01-01'))
  FROM SYSIBM.SYSDUMMY1;

If you want to concatenate date or time values with other text, you need to convert the value into a character string first. To do this, you can simply use the CHAR function:

SELECT CHAR(CURRENT DATE)            FROM SYSIBM.SYSDUMMY1;
SELECT CHAR(CURRENT TIME)            FROM SYSIBM.SYSDUMMY1;
SELECT CHAR(CURRENT TIME + 12 HOURS) FROM SYSIBM.SYSDUMMY1;

To convert a character string to a date or time value, you can use:

SELECT TIMESTAMP ('2002-10-20-12.00.00.000000') FROM SYSIBM.SYSDUMMY1;
SELECT TIMESTAMP ('2002-10-20 12:00:00')
  FROM SYSIBM.SYSDUMMY1; -- For LUW, zSeries
--SELECT TIMESTAMP '2002-10-20 12:00:00'
    FROM SYSIBM.SYSDUMMY1; -- For iSeries
SELECT DATE ('2002-10-20')                      FROM SYSIBM.SYSDUMMY1;
SELECT DATE ('10/20/2002')                      FROM SYSIBM.SYSDUMMY1;
SELECT TIME ('12:00:00')                        FROM SYSIBM.SYSDUMMY1;
SELECT TIME ('12.00.00')                        FROM SYSIBM.SYSDUMMY1;

String manipulation is relatively easy compared with date and timestamps. Again, DB2 built-in functions are heavily used. A few of them are used in this section to show you how DB2 string operations work. For a complete list, refer to the SQL Reference corresponding to your platform.

You can use either the CONCAT function or the || operator for string concatenation. The following two statements are exactly the same:

SELECT CONCAT('ABC', 'DEF') FROM SYSIBM.SYSDUMMY1;
SELECT 'ABC' || 'DEF' FROM SYSIBM.SYSDUMMY1;

However, when you have more than two strings to concatenate, the || operator is much easier to use.

You might have to use UPPER or LOWER function in string comparisons if you want the comparison to be case-insensitive. DB2 string comparison is case-sensitive.

COALESCE is another frequently used string function. It returns the first argument that is not null. In your application, if you have the following query SELECT coalesce(c1, c2, 'ABC') FROM t1;

assuming the c1 and c2 columns of table T1 are both nullable character strings, you will receive the value of c1 if it is not null. If c1 is null, you will receive the value of c2 if it is not null. If both c1 and c2 contain null values, you will receive the string ''ABC'' instead.

An identity column is a numeric column defined in a table for which the column values can be generated automatically by DB2. The definition of an identity column is specified at table creation time. Existing tables cannot be altered to add or drop an identity column. Figure 3.7 shows the syntax of an identity column clause used in a CREATE TABLE statement. Only one column in a table can be defined to be an identity column.

|---column-name----+---------------------+--------------------->
                   |                     |
                   '-| data-type |-------'

---'-GENERATED--+-ALWAYS-----+--AS--+-IDENTITY--+-------------------------+-+|
                '-BY DEFAULT-'                  '-| identity-attributes |-'

identity-attributes:
|--+---------------------------------------------------------+--|
   |    .-----------------------------------------------.    |
   |    V                      .-1----------------.     |    |
   '-(-----------+-START WITH--+-numeric-constant-+---+-+--)-'
                 |               .-1----------------. |
                 +-INCREMENT BY--+-numeric-constant-+-+
                 | .-NO MINVALUE----------------.     |
                 +-+-MINVALUE--numeric-constant-+-----+
                 | .-NO MAXVALUE----------------.     |
                 +-+-MAXVALUE--numeric-constant-+-----+
                 | .-NO CYCLE-.                       |
                 +-+-CYCLE----+-----------------------+
                 | .-CACHE--20---------------.        |
                 +-+-NO CACHE----------------+--------+
                 | '-CACHE--integer-constant-'        |
                 | .-NO ORDER-.                       |
                 '-+-ORDER----+-----------------------'

Data types for identity columns can be any exact numeric data type with a scale of zero such as SMALLINT, INTEGER, BIGINT, or DECIMAL. Single and double precision floating-point data types are considered to be approximate numeric data types, and they cannot be used as identity columns.

Within the IDENTITY clause, you can set a number of options to customize the behavior of an identity column. Before discussing these options, let’s look at Figure 3.8 to see how a table can be created with an identity column.

CREATE TABLE service_rq
    ( rqid SMALLINT NOT NULL
        CONSTRAINT rqid_pk
        PRIMARY KEY                                                    -- (1)
    , status VARCHAR(10) NOT NULL
        WITH DEFAULT 'NEW'
        CHECK ( status IN ( 'NEW', 'ASSIGNED', 'PENDING',
        'CANCELLED' ) )                                                -- (2)
    , rq_desktop CHAR(1) NOT NULL
        WITH DEFAULT 'N'
        CHECK ( rq_desktop IN ( 'Y', 'N' ) )                           -- (3)
    , rq_ipaddress CHAR(1) NOT NULL
        WITH DEFAULT 'N'
        CHECK ( rq_ipaddress IN ( 'Y', 'N' ) )                         -- (4)
    , rq_unixid CHAR(1) NOT NULL
        WITH DEFAULT 'N'
        CHECK ( rq_unixid IN ( 'Y', 'N' ) )                            -- (5)
    , staffid INTEGER NOT NULL
    , techid INTEGER
    , accum_rqnum INTEGER NOT NULL                                     -- (6)
        GENERATED ALWAYS AS IDENTITY
        ( START WITH 1
       ,  INCREMENT BY 1
       ,  CACHE 10 )
    , comment VARCHAR(100))

Figure 3.8 is the definition of a table called service_rq, which will be used in a later sample. The service_rq table contains an identity column called accum_rqnum, shown in Line (6). Note that the GENERATED ALWAYS option is specified, and therefore DB2 will always generate a unique integer. The value of accum_rqnum will start at 1 and increment by 1.

From examining the other column definitions (2, 3, 4, and 5), you will see that some are defined with a CHECK constraint so that only the specified values are allowed as column values. A primary key is also defined for this table, as shown in Line (1).

In DB2 for zSeries, explicitly create a unique index on the primary key column:

CREATE UNIQUE INDEX rqid_pk ON service_rq (rqid); -- zSeries only

Figures 3.9 and 3.10 show two different ways to insert a record into the service_rq table.

INSERT INTO service_rq
    ( rqid
    , rq_desktop
    , rq_ipaddress
    , rq_unixid
    , staffid
    , comment )
VALUES
    ( 1
    , 'Y'
    , 'Y'
    , 'Y'
    , 10
    , 'First request for staff id 10' )

INSERT INTO service_rq
    ( rqid
    , status
    , rq_desktop
    , rq_ipaddress
    , rq_unixid
    , staffid
    , techid
    , accum_rqnum
    , comment )
VALUES
    ( 2
    , DEFAULT            -- (1)
    , 'Y'
    , 'Y'
    , 'Y'
    , 10
    , NULL
    , DEFAULT            -- (2)
    , 'Second request for staff id 10' )

The use of the DEFAULT keyword in Figure 3.10 is the same as what has been discussed in the generated columns section.

As shown in Figure 3.7, a few other options are available when defining the identity attribute. The START WITH option indicates the first value of the identity column and can be a positive or negative value. Identity values can be generated in ascending or descending order, and can be controlled by the INCREMENT BY clause. The default behavior is to auto-increment by 1 (and therefore, it is ascending). Options MINVALUE and MAXVALUE allow you to specify the lower and upper limit of the generated values. These values must be within the limit of the data type. If the minimum or maximum limit has been reached, you can use CYCLE to recycle the generated values from the minimum or maximum value governed by the MINVALUE and MAXVALUE option.

The CACHE option can be used to provide better performance. Without caching, (by using option NO CACHE), DB2 will issue a database access request every time the next value is requested. Performance can be degraded if the insert rate of a table with an identity column is heavy. To minimize this synchronous effect, specify the CACHE option so that a block of values is obtained and stored in memory to serve subsequent identity value generation requests. When all the cached values in memory are used, the next block of values will be obtained. In the example shown in Figure 3.8, 10 values are generated and stored in the memory cache. When applications request a value, it will be obtained from the cache rather than from the system tables that are stored on disk. If DB2 is stopped before all cached values are used, any unused cached values will be discarded. After DB2 is restarted, the next block of values is generated and cached, introducing gaps between values. If your application does not allow value gaps, use the NO CACHE option instead of the default value of CACHE 20.

It is often useful to be able to use the identity value previously generated by DB2 in subsequent application logic. The generated value can be obtained by executing the function IDENTITY_VAL_LOCAL within the same session of the INSERT statement; otherwise NULL is returned. The function does not take any parameters. Figure 3.11 demonstrates two different ways to use the IDENTITY_VAL_LOCAL function.

CREATE PROCEDURE addnewrq ( IN p_rqid SMALLINT
                          , IN p_staffid INTEGER
                          , IN p_comment VARCHAR(100)
                          , OUT p_accum_rqnum INTEGER )
    LANGUAGE SQL
    SPECIFIC addnewrq                             -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
BEGIN
    INSERT INTO service_rq
        ( rqid, status, rq_desktop
        , rq_ipaddress, rq_unixid, staffid
        , techid, accum_rqnum, comment )
    VALUES
        ( p_rqid, DEFAULT, 'Y'
        , 'Y', 'Y', p_staffid
        , NULL, DEFAULT, p_comment ) ;

    SELECT                           -- (1)
        identity_val_local()
    INTO
        p_accum_rqnum
    FROM
        sysibm.sysdummy1;

    VALUES                           -- (2)
        identity_val_local()
    INTO
        p_accum_rqnum;
END

In Figure 3.11, procedure addnewrq uses two ways to obtain the value just inserted into service_rq. On Line (1), it uses the SYSIBM.SYSDUMMY1 table. Another method is to instead use the VALUES clause shown in Line (2). If you call the procedure multiple times with the same rqid value, as in

CALL addnewrq(3, 1050, 'New Request', ?)

you receive an error with SQLSTATE 23505 indicating a unique constraint was violated because the rqid column is defined as a primary key in Figure 3.8. Note that the result of IDENTITY_VAL_LOCAL keeps increasing even though the INSERT statement fails. This indicates that once an identity value is assigned by DB2, it will not be reused regardless of the success or failure of the previous INSERT statement.

Notice that the example in Figure 3.11 only involves a single row insert. If the statement inserts multiple rows prior to execution of IDENTITY_VAL_LOCAL, it will not return the last value generated—it will return NULL.

Consider the example in Figure 3.12.

CREATE PROCEDURE insert_multirow
    ( OUT p_id_generated INTEGER )
    LANGUAGE SQL
    SPECIFIC insrt_multirow                       -- applies to LUW and iSeries
BEGIN
    INSERT INTO service_rq                        -- (1)
        ( rqid
        , staffid
        , accum_rqnum
        , comment )
    VALUES
        ( 30000, 1050, DEFAULT, 'INSERT1')        -- (2)
       ,( 30001, 1050, DEFAULT, 'INSERT2')        -- (3)
    ;
    VALUES                                        -- (4)
        identity_val_local()
    INTO
        p_id_generated;

    -- For clean up purpose
    DELETE FROM service_rq
        WHERE rqid = 30000 or rqid = 30001;

END

Two sets of values on Lines (2) and (3) are being inserted with a single INSERT statement separated by a comma. The output parameter p_id_generated is assigned to the result of the IDENTITY_VAL_LOCAL function at Line (4). Successfully calling insert_multirow will give you the following on LUW:

P_ID_GENERATED: NULL
"INSERT_MULTIROW" RETURN_STATUS: "0"

On iSeries, the result would be something similar to

Output Parameter #1 = 5
Statement ran successfully  (761 ms)

Because an identity column is part of a table definition, to reset or change a characteristic of an identity column you need to issue an ALTER TABLE statement as shown in Figure 3.13.

                                    .-COLUMN-.
>>-ALTER TABLE--table-name---ALTER--+--------+--column-name------>
          .-----------------------------------------.
          V                                         |
|-----------+-SET INCREMENT BY--numeric-constant--+-+----------><
            +-SET--+-NO MINVALUE----------------+-+
            |      '-MINVALUE--numeric-constant-' |
            +-SET--+-NO MAXVALUE----------------+-+
            |      '-MAXVALUE--numeric-constant-' |
            +-SET--+-NO CYCLE-+-------------------+
            |      '-CYCLE----'                   |
            +-SET--+-NO CACHE----------------+----+
            |      '-CACHE--integer-constant-'    |
            +-SET--+-NO ORDER-+-------------------+
            |      '-ORDER----'                   |
            '-RESTART--+------------------------+-'
                       '-WITH--numeric-constant-'

Except for the RESTART option (which has not been introduced), the options listed in Figure 3.13 behave exactly the same as they were described earlier in this chapter. If you want the identity column to be restarted at a specific value at any time, you will find the RESTART option very useful. Simply alter the table, provide the RESTART WITH clause, and explicitly specify a numeric constant.

A sequence is a database object that allows automatic generation of values. Unlike an identity column that is bound to a specific table, a sequence is a global and stand-alone object that can be used by any table in the same database. The same sequence object can be used for one or more tables. Figure 3.14 lists the syntax for creating a sequence object.

                                         .-AS INTEGER-----.
>>-CREATE SEQUENCE--sequence-name---*----+----------------+--*-->
                                         '-AS--data-type--'
>-----+-------------------------------+--*---------------------->
      '-START WITH--numeric-constant--'
      .-INCREMENT BY 1------------------.
>-----+---------------------------------+--*-------------------->
      '-INCREMENT BY--numeric-constant--'
      .-NO MINVALUE-----------------.
>-----+-----------------------------+--*------------------------>
      '-MINVALUE--numeric-constant--'
      .-NO MAXVALUE-----------------.       .-NO CYCLE--.
>-----+-----------------------------+--*----+-----------+--*---->
      '-MAXVALUE--numeric-constant--'       '-CYCLE-----'
      .-CACHE 20-----------------.       .-NO ORDER--.
>-----+--------------------------+--*----+-----------+--*------><
      +-CACHE--integer-constant--+       '-ORDER-----'
      '-NO CACHE-----------------'

As with identity columns, any exact numeric data type with a scale of zero can be used for the sequence value. These include SMALLINT, INTEGER, BIGINT, or DECIMAL. In addition, any user-defined distinct type based on of these data types can hold sequence values. This extends the usage of user-defined distinct types in an application. You may already notice that options supported for sequence objects are the same as the ones for identity columns. Refer to the previous subsection for their descriptions.

Figure 3.15 and Figure 3.16 show the creation of two sequence objects. For example, the sequence staff_seq is used to provide a numeric ID for each staff member. It is declared as an INTEGER, starts at 360, is incremented by 10, and no maximum value is explicitly specified. It is implicitly bound by the limit of the data type. In this example, values generated are within the limit of an INTEGER data type. The NO CYCLE option indicates that if the maximum value is reached, SQLSTATE 23522 will be returned, which means that the values for the sequence have been exhausted. The second sequence object, shown in Figure 3.16, is defined as SMALLINT and used to generate ticket numbers for service requests. This sequence object will start at 1 and increment by 1. Because NO CYCLE is specified, the maximum value generated will be 5000. The CACHE 50 option indicates that DB2 will acquire and cache 50 values at a time for application use. Like identity columns, if DB2 is stopped and sequence values were cached, gaps in sequence values may result.

CREATE SEQUENCE staff_seq AS INTEGER
    START WITH 360
    INCREMENT BY 10
    NO MAXVALUE
    NO CYCLE
    NO CACHE

CREATE SEQUENCE service_rq_seq AS SMALLINT
   START WITH 1
   INCREMENT BY 1
   MAXVALUE 5000
   NO CYCLE
   CACHE 50

At any time, you can either drop and re-create the sequence object or alter the sequence to change its behavior. Figures 3.17 and 3.18 show the syntax of the ALTER SEQUENCE and DROP SEQUENCE statements, respectively.

>>-ALTER SEQUENCE--sequence-name-------------------------------->
        .-------------------------------------------.
        V                                           |
  >-------+-RESTART--+-------------------------+-+--+------------><
          |          '-WITH--numeric-constant--' |
          +-INCREMENT BY--numeric-constant-------+
          +-+-MINVALUE--numeric-constant--+------+
          | '-NO MINVALUE-----------------'      |
          +-+-MAXVALUE--numeric-constant--+------+
          | '-NO MAXVALUE-----------------'      |
          +-+-CYCLE----+-------------------------+
          | '-NO CYCLE-'                         |
          +-+-CACHE--integer-constant--+---------+
          | '-NO CACHE-----------------'         |
          '-+-ORDER----+-------------------------'
            '-NO ORDER-'

                                   .-RESTRICT-.
--DROP--+-SEQUENCE--sequence-name--+----------+---------------------------+<

Just like other database objects in DB2, manipulation of sequence objects is controlled by privileges. By default, only the sequence creator or a user with administrative authorities (such as SYSADM and DBADM on LUW), hold the ALTER and USAGE privileges of the object. If you want other users to be able to use the sequence, you need to issue the following:

GRANT USAGE ON SEQUENCE <sequence_object_name> TO PUBLIC

The USAGE and ALTER privileges can be granted to PUBLIC or any individual user or group.

Two expressions, NEXT VALUE and PREVIOUS VALUE, are provided to generate and retrieve a sequence value. Figure 3.19 is an example of their usage. Two alternate expressions, NEXTVAL and PREVVAL, can be used interchangeably with NEXT VALUE and PREVIOUS VALUE, respectively, for backward compatibility reasons.

CREATE PROCEDURE seqexp ( out p_prevval1 int
                        , out p_nextval1 int
                        , out p_nextval2 int
                        , out p_prevval2 int )
    LANGUAGE SQL
    SPECIFIC seqexp                               -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
BEGIN

    -- DECLARE host variables
    DECLARE v_prevstaffno INT;

    -- Procedure logic
    INSERT INTO staff
        ( id, name, dept
        , job, years, salary
        , comm )
    VALUES
        ( NEXT VALUE FOR staff_seq, 'Bush', 55
        , 'Mgr', 30, NULL
        , NULL);

    UPDATE staff
       SET id = ( NEXT VALUE FOR staff_seq )
     WHERE name='Bush';

    VALUES PREVIOUS VALUE FOR staff_seq INTO v_prevstaffno;     -- (1)

    DELETE FROM staff WHERE id = v_prevstaffno;                 -- (2)

    VALUES                                                      -- (3)
        ( PREVIOUS VALUE FOR staff_seq
        , NEXT VALUE FOR staff_seq
        , NEXT VALUE FOR staff_seq
        , PREVIOUS VALUE FOR staff_seq )
    INTO p_prevval1, p_nextval1, p_nextval2, p_prevval2;
END

You can use the NEXT VALUE and PREVIOUS VALUE expressions in SELECT, VALUES, INSERT, and UPDATE statements. In Figure 3.19 on Line (2), the DELETE statement needs to reference the value just generated in the WHERE clause. Because NEXT VALUE and PREVIOUS VALUE cannot be used in a WHERE, clause you need to use two separate SQL statements. You can use a VALUES INTO statement to obtain and store the generated value in a variable, v_prevstaffno. The DELETE statement can then specify the variable in the WHERE clause.

The last VALUES statement on Line (3) in the example shows that if more than one sequence expression for a single sequence object is used in a statement, DB2 will execute NEXT VALUE and PREVIOUS VALUE only once. In the example, assuming the value last generated is 500, the statement on Line (3) will have the result 500, 510, 510, 500.

For more examples on how to use sequence objects in your stored procedures, refer to Chapter 10, “Leveraging DB2 Application Development Features.”