BASIC DB2 OBJECTS AND OPERATORS - IBM Mainframe

In this section we will discuss the basic scalar objects and operators supported by DB2. The basic data object is the scalar value. For example, the object appearing at the intersection of a given row and a given column of a given table is a scalar value. Each such value belongs to a particular scalar data type and for each such data type there can be an associated format for writing literals of that type. Scalar objects can be operated upon by means of scalar operators. For example two numeric scalar objects can be added together using the scalar arithmetic operator '+'. DB2 provides scalar functions, which can also be regarded as scalar operators. Scalar operators and objects can be combined to form scalar expressions.

Data Types

DB2 supports the following scalar data types.

Numeric Data

  • INTEGER - Four byte binary integer, 31 bits and sign. Ex. 4, -95, +364, 0
  • SMALLINT - Two byte binary integer, 15 bits and sign. Ex. 4, -95, +364, 0
  • DECIMAL(p,q) - Packed decimal number, p digits and sign (0<p<32), with assumed decimal point q digits from the right (0<=q<^p^ occupying (p+1)/2 or (p+2)/2 bytes according as p is odd or even. Ex. 4., -95.7, +364.05, 0.00*7
  • FLOAT(p) - Floating point number n, represented by a binary fraction f of p binary digits precision (-1<f<+l, 0<p<54) and a binary integer exponent e(-65<e<+64) such that n=f*(16**e). If p<22 the number n is single precision and occupies 4 bytes, otherwise it is double precision and occupies 8 bytes. Ex. 4E3, -95.7E46, +364E-5, 0.7E1

String Data

  • CHARACTER (n) - Fixed length string of exactly 8-bit characters (0<n<255), occupying r. bytes. Ex. '123 Main St.', 'Alexis'
  • VARCHAR(n) - Varying length string of up to n 8-bit characters ()<n) occupying n+2 bytes (2 bytes for a hidden length field). Ex. 'Internet', 'pghyt'
  • GRAPHIC(n) - Fixed length string of exactly n 16-bit characters (0<n<128) occupying 2n bytes. Ex. G'<..... >'
  • VARGRAPHIC(n) - Varying length string of up to n 16-bit characters (0<n), occupying 2n+2 bytes (2 bytes for the hidden length field). Ex. G '< >'

Date/Time Data

  • DATE - Date, represented as a sequence of 8 unsigned packed decimal digits (yyyymmdd), occupying 4 bytes. Permitted values are legal dates in the range Jan 1st 1AD to Dec 31st 9999 AD. Ex. '1/18/1967', '12/12/1995'
  • TIME - Time, represented as a sequence of 6 unsigned packed decimal digits (hhmmss), occupying three bytes. Permitted values are legal times in the range 000000 to 240000. Ex. '10:00 AM', '9.30 PM'
  • TIMESTAMP - 'Timestamp', represents a sequence of 20 unsigned packed decimal digits (yyyymmddhhmmssnnnnnn), occupying 10 bytes. Permitted values are legal timestamps in the range of 00010101000000000000 to 99991231240000000000.

Notes

  • There is nothing like a date literal. The above examples are character string representations of the date values. If a character string literal appears in a context that requires a date value, then that character string will be interpreted as a date value, if it is of the appropriate form. The same rule applies for time and timestamps.
  • Thus date, time and timestamps are written as strings as given in the examples: date('mm/dd/yyyy'), time('hh:mm AM' or 'hh:mm PM') or timestamp('yyyy-mm-dd-hh.mm.ss.nnnnnn").
  • Date/time/timestamp column definitions use the conventional DB2 column definition syntax i.e column name data-type [NOT NULL [WITH DEFAULT | UNIQUE]] where data-type is DATE, TIME or TIMESTAMP.
  • If NOT NULL WITH DEFAULT is specified during a CREATE TABLE command the default values will be CURRENT DATE, CURRENT TIME and CURRENT TIMESTAMP for DATE, TIME and TIMESTAMP respectively.
  • But if NOT NULL WITH DEFAULT is specified during an ALTER TABLE command then the default values will be '01/01/0001' for DATE, '00:00 AM' for TIME, and '0001-01-01-00.00.00.000000' for TIMESTAMP.

The valid operators that can be used with date, time and timestamp are the infix arithmetic operators '+ and '-'. A complete list is given below:

BASIC DB2 OBJECTS AND OPERATORS

Given below are some of the examples for the above expressions. Some of them are illegal for obvious reasons:

illegal for obvious reasons

Date/Time arithmetic is performed in accordance with the calendar and permissible date/time values. Therefore, the expression DATE ('5/31/1977') + 1 MONTH will yield the result '6/30/1977' and not '6/31/1977'. But the expression DATE ('6/30/1977') -1 MONTH yields the result '5/30/1977' and not '5/31/1977*.

In general if we add a date duration d to some date and subtract the same duration from the result. we are not guaranteed to end up with the date we started with. Thus DATE ('5/31/1988') + 1MONTH - IMONTH does not yield '5/31/1988'. But the expression DATE ('5/31/1988')+30 DAYS-30DAYS will definitely yield'5/31/1988'.

Scalar Operators and Scalar Functions

DB2 provides a number of scalar operators and functions that can be used in the construction of scalar expressions. These operators and functions are summarized below:

  • Numeric Operators - +, -, *, / (The operators + and - can be used with dates, times and timestamps as well as with numbers)
  • Comparison Operators - =, =, <, <, <=, >,>,> =
  • Concatenation -'[ J* This operator can be used to concatenate 2 character strings or 2 graphic strings. It can be used as an infix operation. For example FNAME 11 LNAME can be used to concatenate the values of First name and Last name.
  • CHAR - Converts a date, time, timestamp or decimal number to its character string representation.
  • DATE - Converts a scalar value to date.
  • DAY - Extracts the day portion of a date or timestamp.
  • DAYS -Converts a date or timestamp into number of days.
  • DECIMAL - Converts a number to decimal representation with specified precision.
  • DIGITS - Converts a number to character string representation
  • FLOAT - Converts a number to floating point representation.
  • HEX - Converts a scalar value to character string representing internal hexadecimal encoding of the value.
  • HOUR - Extracts the hour portion of the time or timestamp.
  • INTEGER - Converts a number to integer precision.
  • LENGTH - Computes the length of a scalar value in bytes
  • MICROSECOND -Extracts the microseconds portion of a timestamp.
  • MINUTE - Extracts the minutes portion of the time or timestamp.
  • MONTH - Extracts the month portion of a date or timestamp
  • SECOND - Extracts the seconds portion of the time or timestamp.
  • SUBSTR - Extracts a sub-string from a string. Example: the expression SUBSTR (NAME,1,3) will extract the first 3 characters of the variable NAME.
  • TIME - Converts a scalar value to time.
  • TIMESTAMP - Converts either a single scalar value or a pair of scalar values to a timestamp.
  • VALUE - Converts a null into a non-null value.
  • VARGRAPHIC - Converts a character string into a graphic string.
  • YEAR - Extracts the year portion form a date or timestamp.

Special Registers

DB2 supports a number of special registers. They are given below:

  • USER - Returns the 'primary authorization ID'
  • CURRENT SQLID - Returns the current authorization id.
  • CURRENT SERVER - Returns the ID of the current server
  • CURRENT PACKAGESET - Returns the ID of the collection currently in use.
  • CURRENT DATE - Returns the current date.
  • CURRENT TIME - Returns the current time.
  • CURRENT T1MESTAMP - Returns the current timestamp

Nulls

Nulls are special markers by which SQL systems like DB2 represents missing information. For example, when we say that the height of a Person is null, it means that, we know that such a Person exists, he/she does have a height, but we do not know what the height is. In other words we do not know the exact height that could sensibly be put in the HEIGHT slot in the row of the Person in question. Therefore, we mark it as null, and we interpret that the mark means that we do not know what the real value is. But the whole point about nulls is that they are not values and are not same as blank or a zero.

In general any column can have nulls unless the definition of that column explicitly specifies NOT NULL. If a given column is allowed to contain nulls, when a row is inserted into the table and no values are specified for that column DB2 will automatically place a null into that column. By the same token, DB2 will not allow the updating of a column for which NOT NULL has been specified with a null value.

When the option ' NOT NULL' is used 'WITH DEFAULT, it means that the column cannot contain nulls. But it is still possible to omit the values for the column during INSERT or UPDATE. If a row is inserted and no values are specified for some columns DB2 will insert one of the following default values in that column:

  • Zero for numeric columns
  • Blanks for fixed length string columns.
  • Empty (zero-length string) for varying length columns.
  • The values CURRENT DATE, CURRENT TIME or CURRENT TIMESTAMP for date, time and timestamp respectively.

Two special comparison operators, IS NULL and IS NOT NULL are provided to test for the presence or absence of nulls. For example, let A=l, B='x' and 'C is null. Then C IS NULL is true, A IS NULL is false and B IS NOT NULL is true.

Even though, DB2 has given the provision of Nulls, it is -always better and safer not to use Nulls, because they can create more trouble than they are worth. Nulls when included will display strange and inconsistent behavior and are rich source of error, confusion and chaos. So it is always to specify either NOT NULL or NOT NULL WITH DEFAULT for all columns.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics