Field procedures enable you to alter the sorting sequence of values entered in a single short string column (CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC). For some applications the standard EBCDIC sorting sequence is not appropriate. For example, telephone directories sometimes require that names like "McCabe" and "MacCabe" appear next to each other, and the standard sorting routine would separate them. Another example is a national language character set that does not use the Roman alphabet. For example, Kanji (Japanese) can only be sorted properly using a field procedure.
If you assign a field procedure to a column, it is called whenever values in that column are changed or are inserted, and it transforms (encodes) the original value into one value that sorts properly.
When you retrieve a row from the encoded column, the same field procedure decodes it into the original form. You will never see the encoded string. From a user's point of view, all a field procedure does is change the sorting sequence for a column.
For example, consider a table with a short string column that contains the four divisions in a company: North, South, East, and West. Divisions are usually sorted as follows:
East North South West
You can, however, write a field procedure that encodes North as 1, South as 2, East as 3 and West as 4. The divisions would then be sorted as follows:
North South East West
| Note: | The encoded values do not have to be the same data type as the decoded values. Refer to the DB2 Server for VSE & VM SQL Reference manual for a description of the catalog table SYSCOLUMNS, which contains the descriptions of decoded columns, and SYSFIELDS, which contains the descriptions of the corresponding encoded columns. |
While field procedures are used primarily to alter the standard EBCDIC sorting sequence, they can also be used in any application program that requires short strings to be stored differently from how they are inserted or retrieved.
For a sample field procedure and the rules for writing field procedures, refer to the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.
DB2 Server for VSE & VM provides two field procedures for performing cultural sorts. They are:
If Data Propagator Capture for VSE or VM is being used on tables that have columns with field procedures, "1-way" field procedures must be defined on the Data Propagator Change Data (CD) tables to properly propagate this data. Refer to the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual for more information.
To assign a field procedure to a new column, include the FIELDPROC clause on either the CREATE TABLE or ALTER TABLE statement. To assign field procedures to columns in an existing table, you must unload the data, recreate the table to include the field procedures, and they reload the data back into the table. If you create a column without a field procedure, you cannot add one later.
Refer to the DB2 Server for VSE & VM SQL Reference manual for the syntax diagrams for the CREATE and ALTER TABLE statements. The fieldproc-block for these diagrams is shown below.
Figure 89. fieldproc-block Syntax
>>-FIELDPROC--program_name----+-------------------------+------><
| .-,-----------. |
| V | |
'-(-----constant---+---)--'
|
For example:
ALTER TABLE SCOTT.SUPPLIERS ADD RATING CHAR(6) FIELDPROC MYFLDPRO (10,5)
The constants (10,5), that follow the program_name MYFLDPRO are optional parameters, defined when the field procedure is written and passed to the field procedure when it is invoked.
In most cases you will not have to worry about the rules that define when a field procedure encodes or decodes a short string. However, if you understand when the database manager calls field procedures, this can help you understand their performance implications. The less you call field procedures to encode or decode strings the better your application's performance will be.
Understanding when field procedures are called can also help you to avoid some pitfalls. For example, consider a table TABLE_A with a column COLUMN_A that has fieldproc F1, and consider these two statements:
SELECT SUBSTR(MAX(COLUMN_A,1,5)) FROM TABLE_A SELECT MAX(SUBSTR(COLUMN_A,1,5)) FROM TABLE_A
You might assume that the two statements should return essentially the same result; however, different results can be returned depending on your coding. In the first statement, the database manager does the following:
In the second statement, the database manager does the following:
That is, the first statement MAX is applied to encoded values, and the second is applied to decoded values.
The rest of this section covers the rules that define when a field procedure encodes or decodes a short string.
The field procedure is called to encode data when your application program inserts or updates data. This includes the following statements:
The field procedure is called to decode data when your application program fetches or selects data. This includes the following statements:
If a column with a field procedure is compared to a constant, the constant is first encoded by the field procedure. The comparison is then performed between the encoded values in the column and the encoded value of the constant. Host-variables, parameter markers, and the USER special register are treated the same way.
For example, consider the following SQL statement where COLUMN_A has field procedure F1:
SELECT * FROM MY_TABLE WHERE COLUMN_A > 'SMITH'
When processing the above statement, the database manager first encodes 'SMITH', and then for each row in MY_TABLE, compares F1 to the encoded value in COLUMN_A.
A field procedure can only encode short strings values. If the variable or constant is of a data type other than CHAR, VARCHAR, GRAPHIC or VARGRAPHIC, a negative SQLCODE is returned.
If a column with a field procedure is compared to another column, both columns must have field procedures with the same program_name, comparable encoded data type, and the same CCSID. If not, a negative SQLCODE is returned.
If a primary key column has a field procedure, then the foreign key column must have the same field procedure, and the CCSIDs of both key columns must be the same. Otherwise, a negative SQLCODE is returned. For two field procedures to be the same, their program_names, encoded data type, encoded data length, and input parameters must be identical.
For example, the following is correct:
CREATE TABLE PRIMARY
(COLUMN_A CHAR(10) FIELDPROC F1 NOT NULL,
COLUMN_B INTEGER)
PRIMARY KEY(COLUMN_A)
CREATE TABLE FOREIGN
(COLUMN_A CHAR(10) FIELDPROC F1 NOT NULL,
COLUMN_B CHAR(10))
ALTER TABLE FOREIGN
ADD FOREIGN KEY (COLUMN_A)
REFERENCES PRIMARY ON DELETE SET NULL
All scalar functions operate on decoded values. For example, if 'V' is a string in a column with a field procedure, HEX('V') returns the hexadecimal representation of 'V'. The result is not associated with the original column's field procedure. However, if the result of a scalar function is compared to a column that is associated with a field procedure, this result is encoded by the comparison column's field procedure. The comparison is then made between the encoded value of the column and the encoded result of the scalar function. This is consistent with how columns with field procedures are compared to constants.
For example:
SELECT * FROM MY_TABLE WHERE COLUMN_A > SUBSTR(COLUMN_B,3,3)
For each row of MY_TABLE, the following occurs:
SELECT * FROM MY_TABLE WHERE COLUMN_A > VALUE(COLUMN_B,COLUMN_C)
For each row of MY_TABLE, the following occurs:
| Note: | A field procedure is never called to encode or decode a NULL value. A NULL value always maps to a NULL. |
The column functions MAX and MIN operate on encoded values. The remaining column functions operate on numeric data, and are not affected by field procedures.
The concatenation operator is basically a scalar function, and follows the same rules as a scalar function.
For example, consider a table (MY_TABLE) with COLUMN_A that has field procedure F1 and COLUMN_B that has field procedure F2. Now, consider the following SQL statement:
SELECT * FROM MY_TABLE WHERE COLUMN_A > 'ADDITION' CONCAT COLUMN_B
For each row in MY_TABLE, the following occurs:
These predicates operate the same as a comparison between a column with a field procedure and a constant.
This predicate operates on decoded values.
Indexes will be based on encoded values. The ORDER BY and GROUP BY clauses will sort the data according to the encoded format. The database manager also sorts values during a UNION operation.
While a column with a field procedure may be defined to allow null values, the field procedure is never called to process a null value. A decoded null value always maps to an encoded null value, and an encoded null always maps to a decoded null.
The rules for comparing two columns with field procedures apply to unions and joins. The two columns must have the same field procedure.
All the rules described above apply to sub-SELECTs.
For example:
SELECT * FROM TABLE_1
WHERE COLUMN_A=(SELECT COLUMN_B FROM TABLE_2);
SELECT * FROM TABLE_1
WHERE COLUMN_A IN (SELECT COLUMN_B FROM TABLE_2);
If the columns COLUMN_A and COLUMN_B have different field procedures these statements are invalid (field procedure comparison rules apply). For example:
INSERT INTO T1 (COLUMN_A) SELECT COLUMN_B FROM T2;
In this statement, the decoded data types for COLUMN_A and COLUMN_B must be compatible. If so, the value in COLUMN_B will be decoded with F2. The decoded value is then encoded by F1, and the resulting value is inserted into COLUMN_A.