DB2 Server for VSE & VM: Application Programming

Using Data Types

Assigning Data Types When the Column Is Created

Each column of every DB2 Server for VSE & VM table is given an SQL data type when the column is created. Figure 25 shows the data types and how they are stored internally.

Figure 25. SQL Data Types

SQL Data Type	How Stored
INTEGER or INT	Stored as a signed 31-bit binary integer
SMALLINT	Stored as a signed 15-bit binary integer
DECIMAL[(p[,s])] or DEC[(p[,s])] 4 , 5	Stored as a packed decimal number of precision p and scale s. Precision is the total number of digits; scale is the number of digits to the right of the decimal point. For example, 251.66 fits in a DECIMAL(5,2) data area. When precision and scale are calculated, if the precision is greater than 31, leading zeros will be removed until it is equal to 31. Trailing zeros are not removed. The default scale is 0 and the default precision is 5.
FLOAT(n) 1	Stored as a single-precision (4-byte) floating-point number in short System/390 floating-point format, or as a double-precision (8-byte) floating-point number in long System/390 floating-point format.
CHARACTER[(n)] or CHAR[(n)] 3	Stored as a character string of fixed length n, where <= 254. The default length is 1.
VARCHAR(n) 2 , 3	Stored as a varying-length character string of maximum length n, where n <= 32767. If 254 < n <= 32767, VARCHAR(n) is considered a long string.
LONG VARCHAR 3	Stored as a varying-length character string of maximum length 32767.
GRAPHIC[(n)]	Stored as a string of double-byte character set (DBCS) characters of fixed length n, where n <= 127. The default length is one DBCS character.
VARGRAPHIC(n) 2	Stored as a varying-length string of DBCS characters of maximum length n, where n <= 16383. If 127 < n <= 16383, VARGRAPHIC(n) is considered a long string.
LONG VARGRAPHIC	Stored as a varying-length string of DBCS characters of maximum length 16383.
DATE	Stored as a string of 4 bytes. Each byte is two packed decimal digits. The first two bytes are the year, the next is the month, and the last is the day.
TIME	Stored as a string of 3 bytes. Each byte is two packed decimal digits. The first byte is the hour, the next is the minute, and the last is the second.
TIMESTAMP	Stored as a string of 10 bytes. Each byte is two packed decimal digits. The first 4 bytes are the date, the next 3 are the time, and the last 3 are the microsecond.

Notes:

1. The FLOAT data type refers to either single-precision floating-point data (4 bytes) or double-precision floating point-data (8 bytes).
   • REAL and FLOAT(n), where n is from 1 to 21, are synonyms. They are both stored as 4 bytes.
   • FLOAT, DOUBLE PRECISION, and FLOAT(n), where n is from 22 to 53, are synonyms. They are all stored as 8 bytes.
   • When single- and double-precision floating-point data are compared to one another, the result of the comparison may not be what you expected. See Constructing Search Conditions.

2. These data types have some special considerations to watch out for.
   • For the CREATE TABLE and ALTER TABLE statements, when VARCHAR(n) or VARGRAPHIC(n) has "n" greater than 254 or 127 respectively, the database manager treats the column as a long string when storing and retrieving data. Long strings are discussed in the next section.

     The column is treated as VARCHAR or VARGRAPHIC, however, in two respects:

     • The value stored in the LENGTH and SYSLENGTH columns of SYSTEM.SYSCOLUMNS is "n".
     • The value returned to the user in the SQLLEN field of the SQLDA is "n".

     When n is less than 255 (VARCHAR) or 128 (VARGRAPHIC) on these statements, the treatment of the column is unchanged.

   • Trailing blanks are not considered relevant in comparisons of VARCHAR or VARGRAPHIC values, unless these values are either concatenated, returned to the application program, or used in a scalar function.

     For example, if string X1 = "STRING " and string X2 = "STRING" and X3 = X1 CONCAT X2 then X3 will be equal to "STRING STRING". However, X1 is considered equal to X2 in a compare statement such as a SELECT...WHERE.

3. Columns defined with these data types can contain MIXED or BIT data.

4. NUMERIC is a synonym for DECIMAL, and may be used when creating or altering tables. In such cases, however, the CREATE or ALTER function will establish the column (or columns) as DECIMAL.

5. C application programs can use the decimal data type so that host variables can match table definitions and do not have to do C numeric conversions for table columns that are defined as decimal.

Using Long Strings

Defining Long Strings

A long string column is either a LONG VARCHAR, LONG VARGRAPHIC, VARCHAR(n) (where 254 < n <= 32 767), or VARGRAPHIC(n) (where 127 < n <= 16 383). Long strings are intended for storage of unstructured data such as text strings, images, and drawings. For a list of restrictions on the use of long strings, refer to the section on data types in the DB2 Server for VSE & VM SQL Reference manual.

Performing Operations on Long Strings

The only operations permitted on long strings are:

• SELECT in an outer-level query (not in a subquery).
• INSERT into the database from an input host variable (not from a constant or from a subquery). You can, however, insert null values into long strings with the usual INSERT statement mechanisms. (That is, you are not restricted to host variables when inserting nulls.)
• UPDATE from an input host variable or UPDATE to the null value. (SET LONGFIELD=:X and SET LONGFIELD=NULL are permitted, but SET LONGFIELD='HELLO' and SET LONGFIELD=OTHERFIELD are not permitted.)
• DELETE of rows containing long strings.

Programming Tip

The restrictions on the use of long strings can usually be avoided by the appropriate use of the SUBSTR function.

Using Datetime Data Types

Datetime is a collective DB2 Server for VSE & VM term that includes date, time, and timestamp. Although datetime values can be used in certain arithmetic operations and are compatible with certain strings, they are neither strings nor numbers. Conversely, strings and numbers are not datetime values. A datetime value is either:

• A DATE, TIME, or TIMESTAMP column value
• A value returned by the DATE, TIME, or TIMESTAMP scalar functions
• A value returned by the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special registers.

Datetime values of the same type can be subtracted. If date1 and date2 are DATE columns, date1 - date2 is a valid expression. |Date1 - '01/01/2000' is also a valid expression because |'01/01/2000' is a valid string representation of a date. |However, '01/01/2000' - '12/20/1999' is not valid because |strings cannot be subtracted and a string is interpreted as a date only if the |other operand is a value of data type DATE. Scalar functions are |provided to explicitly convert strings to datetime values. The |following expression is valid: |DATE('01/01/2000') - '12/20/1999'.

For detailed information on the components and valid formats and lengths of the date, time, and timestamp data types and the assignment of these data types to host variables or CHAR-type columns, see the DB2 Server for VSE & VM SQL Reference manual.

Using Character Subtypes and CCSIDs

Character subtypes and coded character set identifiers (CCSIDs) provide a means of identifying the character data representation scheme to be used for character and graphic data in your system. For example, by using a certain CCSID, you can specify that all character data in your system is single-byte EBCDIC data.

Subtypes are a way of specifying that you want to use the application server system default CCSID associated with that subtype. CCSIDs apply to both character and graphic data, while subtypes apply only to character data.

For a detailed description of coded character sets and CCSIDs, see the DB2 Server for VSE & VM SQL Reference manual.

For most applications, you do not need to specify subtypes or CCSIDs, because the system defaults can usually meet your character data representation requirements.

If this is not the case, you may have to become familiar with Character Data Representation Architecture (CDRA). Refer to the section about data integrity concerns in the Character Data Representation Architecture Reference and Registry manual for a discussion of using CDRA to meet your requirements.

The following are examples of problems that can be solved by the specification of CCSIDs or subtypes. The solutions to these problems are discussed in Assigning Subtypes and CCSIDs When a Column Is Created and Assigning Subtypes and CCSIDs to Data in a Program.

• A column is required in a table to contain mixed data (that is, data that can contain both double-byte and single-byte characters), but the system default specifies that all newly created columns will be used to contain single-byte character set data only.
• A table creation program is required that is to be used at multiple sites, all of which can use different system default subtype and CCSID values. The tables to be created must have the ability to store data of a particular CCSID.
• An application program written in assembler language must insert data into a graphic column, but variables with a graphic data type are not supported.

Determining Default Subtypes and CCSIDs

Refer to the SYSTEM.SYSOPTIONS catalog table to determine the application server system defaults. The rows containing the following values in the SQLOPTION column are important: CHARSUB, CCSIDSBCS, CCSIDMIXED, CCSIDGRAPHIC, and CHARNAME.

DB2 Server for VM
For the application requester system defaults, invoke the SQLINIT EXEC using the QUERY option. The fields that contain important information are CCSIDSBCS, CCSIDMIXED, CCSIDGRAPHIC, and CHARNAME. (For a discussion of the SQLINIT EXEC, refer to the DB2 Server for VSE & VM Database Administration manual.)

Examples of items that assume application requester system defaults are input and output SQLDA elements (the default can be overridden), and host variables.

The following are examples of items that assume application server system defaults:

• Columns (default can be overridden)
• Special registers.

The following are examples of items that assume application requester system defaults:

• Input and output SQLDA elements (default can be overridden)
• Host variables.

For information on setting system defaults, refer to the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

Assigning Subtypes and CCSIDs When a Column Is Created

There are three ways to assign subtypes or CCSIDs to a column:

• Use the application server system defaults.
• Use the preprocessor parameters CHARSUB, CCSIDSBCS, CCSIDMIXED, and CCSIDGRAPHIC to override the system default for columns created by the CREATE TABLE and ALTER TABLE statements in the package. (See "Preprocessing the Program" (DB2 Server for VM) "Preprocessing the Program" (DB2 Server for VSE) for information on these parameters.)
• Use the subtype or CCSID clause in a column's definition within the CREATE TABLE or ALTER TABLE statement to override the application server system default or the preprocessor default. (For more information on these statements, refer to the DB2 Server for VSE & VM SQL Reference manual.)

Assigning Subtypes and CCSIDs to Data in a Program

There are two ways to assign subtypes or CCSIDs to the data items in a program:

• Use application requester system defaults
• Execute the SQL statement using dynamic SQL so that the data items can be described in a user-defined SQLDA. A CCSID can be assigned to each data item in the SQLDA.

  For examples of how to build an SQLDA that contains CCSID information, see Chapter 7, Using Dynamic Statements. For a more detailed discussion on using the SQLDA, refer to the DB2 Server for VSE & VM SQL Reference manual.

Converting Data

For the database manager, the operands in an assignment or comparison operation must be compatible. For example, a character string cannot be compared to a numeric string, a graphic string cannot be compared to a character string, and an arithmetic operation cannot contain a character string operand. Refer to the DB2 Server for VSE & VM SQL Reference manual for more details about compatible data types.

Operands that are compatible but are not identical in data types, lengths, datetime formats, or CCSIDs, can be used in assignment and comparison operations but require data conversion as follows:

• For assignment operations, conversion is done before the data is assigned. For example, if a host variable is defined as a SMALLINT field and a column is defined as INTEGER, a SELECT INTO operation converts the INTEGER column to SMALLINT before it is assigned to the host variable. In this situation, overflow may occur if the value is too large to fit into a SMALLINT field. Depending on the data types and the host language, some data may be lost. The DB2 Server for VSE & VM SQL Reference manual discusses potential data loss in the assignment of COBOL integers.

  To retrieve a datetime value, (that is, a DATE, TIME, or TIMESTAMP), it must be assigned to a character string host variable. The assignment operation converts the datetime value to a character string representation. Whenever a string representation of a datetime value is used in any other operation with a datetime value, the operation is performed with a temporary copy of the string that has been converted to the data type of the datetime value.

  If a conversion error occurs when the database manager assigns a value to a host variable in the INTO clause of a SELECT or FETCH statement, and if you have provided an indicator variable for the affected host variable, the system returns the following:

  • A value of -2 in the indicator variable
  • An undefined value in the host variable
  • Warning values in both SQLCODE and SQLSTATE that are appropriate for the condition.

  If you have not provided an indicator variable, both SQLCODE and SQLSTATE return error codes (a negative value for SQLCODE, and a data exception for SQLSTATE).

• For comparison operations, one field may be converted if necessary to match the data type, length, or CCSID of another. For example, if two character strings in a comparison operation have different CCSIDs (one is an SBCS string and the other is a mixed string), a temporary copy of the SBCS data is converted to the mixed data CCSID before the data is compared.

For more information about data conversion and conversion errors, see the discussion about assignments and comparisons in the DB2 Server for VSE & VM SQL Reference manual.

Summarizing Data Conversion

Data conversion is summarized in tabular form in the DB2 Server for VSE & VM SQL Reference manual. Overflow (loss on the left) or truncation (loss on the right) may occur on some conversion attempts.

Truncating Data

Truncations are handled differently for numeric, character, and datetime data.

Numeric data

Truncation of zeros on the left, or of the fractional part of decimal or floating-point values (single-precision or double-precision) takes place without error or warning. Any other loss of data on conversion is an overflow error. If overflow occurs in an outer select and an indicator variable is supplied for the host variable, the indicator variable is set to -2 and a positive SQLCODE is returned; otherwise, a negative SQLCODE is returned.

Character data

When output from the database manager does not fit into a host variable, a warning is returned. SQLWARN1 is set to indicate truncation. In this case, if you provide an indicator variable, the value within it denotes the actual length of the variable in characters before truncation.

When an input character string value does not fit into a DB2 Server for VSE & VM column, an error results.

Whenever truncation occurs, it follows specific rules depending on the character subtype involved. Also, padding may occur when a string is assigned to either a fixed-length host variable or to a fixed-length column and the source string is shorter than the length of the target. Padding, like truncation, follows rules depending on subtype. These rules are in the DB2 Server for VSE & VM SQL Reference manual.

SBCS and mixed are the only two types of character data truncation. In mixed truncation, the integrity of target data is ensured. For example, if 'ab<(CCDDEE)>cd' is truncated to a length of 6, the result with mixed truncation is 'ab<(CC)>'. The system counts to byte 6. Because this would split a double-byte character, the number of bytes is rounded to the next lowest whole number. It also always ensures that the < and > characters correctly identify the double-byte characters.

Figure 26 shows the type of truncation that occurs depending on the subtype of the source and target data.

Figure 26. Truncation Types

Subtype of Source	Subtype of Target	Result
Mixed	Mixed	Mixed truncation
SBCS	SBCS	SBCS truncation
Mixed	SBCS	SBCS truncation1
SBCS	Mixed	SBCS truncation1
Note: If the source data contains DBCS data, a conversion error occurs during SBCS truncation.

Figure 27 shows the results of SBCS and mixed truncation when selecting 'ab<(CCDDEE)>fg' into various host variables:

Figure 27. Examples of Mixed Data Truncation and SBCS Truncation

Target Host Variable	SBCS Truncation	Mixed Truncation
CHAR(6)	'ab<CCD'	'ab<(CC)>'
CHAR(7)	'ab<CCDD'	'ab<(CC)> '
VARCHAR(7)	'ab<CCDD'	'ab<(CC)>'

Note:

For mixed data, the only difference between the second and the third example is the length of the resulting VARCHAR string. A blank is added to the fixed string.

TIME data

When the seconds part of a retrieved ISO, JIS, or EUR format TIME value is truncated, SQLWARN1 is set to indicate that truncation has occurred. The seconds that are truncated are placed in the indicator variable if one is provided.

TIMESTAMP data

On output, any portion of the microseconds part of a TIMESTAMP may be truncated (including the decimal point). However, no warning is given (SQLWARN1 is not set). If an indicator variable is provided, it is unchanged.

For more information about how computations are performed internally or how overflows can occur, refer to the section about arithmetic operations in the DB2 Server for VSE & VM Database Administration manual.

Using a Double-Byte Character Set (DBCS)

DBCS characters can be used in identifiers, constants, and data in DB2 Server for VSE & VM programs. Strings containing DBCS characters are formatted as <(XXXX)>, where < represents the shift-out character, and > represents the shift-in character.

Each XX represents one double-byte character set character. The <> delimiters are single-byte character set (SBCS) characters.

In identifiers, characters constants, and character data, the delimiters are significant so redundant delimiter pairs are not removed. For example, the following strings of DBCS characters are not equivalent:

<(AABB)><(CCDD)> and <(AABBCCDD)>

In graphic data and constants, the delimiters are not significant.

Each DBCS character requires 2 bytes for its representation; therefore, an even number of bytes must be between the < and >. The number of bytes used to represent a string of DBCS characters is equal to:

    2 * the number of DBCS characters + 2 (for mixed data)
 
    2 * the number of DBCS characters (for graphic data)

Strings of DBCS characters cannot span lines, whereas mixed strings containing strings of DBCS and SBCS characters can span lines if each string of DBCS characters in the mixed string is on one input record. For a discussion of the rules for using DBCS characters in constants, see Using Character Constants and Using Graphic Constants.

To use DBCS characters in application programs, you must know the following:

• To use host identifiers that contain DBCS characters in DB2 Server for VM, your compiler must support DBCS and the application requester must have the DBCS option set to YES. To check whether this setting is correct, do an SQLINITQRY; if you need to change this setting, issue an SQLINIT with the DBCS option set to YES. (For a detailed discussion of the SQLINIT EXEC, see the DB2 Server for VSE & VM Database Administration manual.)
• To use host identifiers in DB2 Server for VSE, the DBCS option in the SYSTEM.SYSOPTIONS catalog must be set to yes. Your compiler must also support DBCS.
• To use SQL identifiers that contain DBCS characters, the application server must support DBCS characters and mixed data. To verify this for the application server, make sure that in the SYSTEM.SYSOPTIONS catalog table the CHARNAME setting identifies a mixed character set and the DBCS setting is YES. In addition, DBCS characters must be permitted in the particular identifier. For a discussion of rules for using DBCS characters in identifiers, refer to the DB2 Server for VSE & VM SQL Reference manual.
• To use host variables with graphic data type, the preprocessor must allow a graphic data type for the host language of the source program. This is true for COBOL and PL/I only in DB2 Server for VSE. The DB2 Server for VM preprocessors that allow graphic data type are COBOL and PL/I. If you need this facility when using another language, see the appendix for that language for a discussion of alternative actions.
• To use graphic and mixed constants (that is, character constants that contain DBCS characters) in an application program, the DB2 Server for VM application server and application requester or DB2 Server for VSE application server must support mixed data. To verify this for the application server, make sure that the CHARNAME setting in the SYSTEM.SYSOPTIONS catalog table identifies a mixed character set. To verify this for for the DB2 Server for VM application requester, issue an SQLINIT command with the QRY option. The CHARNAME value returned identifies a mixed character set. For a discussion of character sets, refer to > the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual. If the DB2 Server for VM application requester does not support DBCS characters, you can obtain this support by using the SQLPREP GRaphic option (available to COBOL and PL/I only).