+---------------------General-Use Programming Interface----------------------+
A field procedure is a General-Use programming interface. Macro ARIBFPPB is a General-Use programming interface. General-Use programming interface is defined in Programming Interface Information.
A field procedure is a user-written exit routine that transforms values in a single short-string column. When values in the column are changed, or new values are inserted, the field procedure is run to encode each value, which is then stored. When values are retrieved from the column, the field procedure is run to decode each value back to the original string value. A field procedure can be used to alter the sorting sequence of values entered in a column. For example, telephone directories sometimes require that names such as McCabe and MacCabe appear next to each other. This cannot be achieved with the standard EBCDIC sorting sequence. Languages that do not use the Roman alphabet have similar requirements. However, if a column is provided with a suitable field procedure, you can obtain the desired ordering with the ORDER BY clause.
Any indexes defined on a column that uses a field procedure are built with encoded values.
The transformation that a field procedure performs on a value is called field-encoding. The same routine is used to undo the transformation when values are retrieved; that operation is called field-decoding.
The field procedure is called when a table is created or altered, to define the data type and attributes of an encoded value to the database manager. That operation is called field-definition. The data type of the encoded value can be CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC. If the datatype is VARCHAR the length must be 254 or less. If the database is VARGRAPHIC, the length must be 127 or less. For the applicable data types, see the description for the FPVDTYPE field in Table 28. The length, precision, or scale of the encoded value must be compatible with its data type. Values in columns with a field procedure are described to the database manager in the following catalog tables:
For more information about catalog tables, see the DB2 Server for VSE & VM SQL Reference manual.
To name a field procedure for a column, use the FIELDPROC clause of the CREATE TABLE or ALTER TABLE statement, followed by the name of the procedure and, optionally, a list of parameters. You can use a field procedure only with a short string column. You cannot add a field procedure to an existing column of a table. You can, however, use the ALTER TABLE statement to add to an existing table a new column that uses a field procedure. (To do so, you would have to unload the data, recreate the table, and load the data back into the table.)
The optional parameter list that follows the procedure name is a list of constants, enclosed in parentheses, called the literal list. The literal list is incorporated into a data structure called the field procedure parameter value list (FPPVL). That structure is passed to the field procedure during the field-definition operation. At that time, the procedure can modify it or return it unchanged. The output form of the FPPVL is called the modified FPPVL; it is stored in the DB2 Server for VSE catalog as part of the field description. The modified FPPVL is passed again to the field procedure when the procedure is called for field-encoding or field-decoding.
A field procedure specified for a column is called in three situations:
In this situation the field procedure is called after any DB2 Server for VSE sort.
A field procedure is never called to process a null value.
Your field procedure must adhere to the following rules:
Attention: A field procedure should always transform one input data value into one output data value, unless the parameters are different. This means that the same field procedure with the same parameters must implement a one to one data conversion, in both directions. The field-decoding function must be the exact inverse of the field-encoding function. For example, if a routine encodes ALABAMA to 01, it must decode 01 to ALABAMA. A violation of this rule can lead to unpredictable results and possible data corruption.
When the database manager compares the values of two strings with different lengths, it temporarily pads the shorter string with blanks (in either single-byte or double-byte characters, as appropriate) up to the length of the longer string. If the shorter string is the value of a column with a field procedure, the padding is done to the encoded value, but the pad character is not encoded. Hence, if the procedure changes blanks to some other character, encoded blanks at the end of the longer string are not equal to padded blanks at the end of the shorter string. That situation can lead to errors; for example, some strings that should be equal may not be recognized as such. You should not encode blanks with a field procedure.
Field procedures are kept in the appropriate VSE library. They can reside in the PRD2 library as a separate sublibrary. The maximum number of active field procedures on one installation is 16. If this limit is exceeded, an attempt to load a field procedure results in an SQLCODE -682 with reason code 4.
If a field procedure ends abnormally, a message (ARI0022E) to remove the field procedure from the installation is issued to the operator, the database manager takes a SNAP dump, and processing continues.
Since exit routines run as extensions of the database manager and have all its privileges, they can impact its security and integrity. All field procedures must be tested and appropriate security measures taken before they are installed on a system.
By default, string data is sorted based on the S/390 collating sequence. However, the collating sequence required for certain alphabets is different from the default S/390 collating sequence. Users expect that sorted data will match the order that is culturally correct for them and that searches on data will return the result that is correct for the sorting sequence of their language. They are at ease with only one sort order, the one used in their dictionaries, telephone directories, book indices, and so on.
A way to accommodate special sorting requirements is to use Field Procedures. Field Procedures can be used to encode data being inserted into a column. The encoding effectively alters the collating sequence for the data in the column, enabling the special sorting requirements to be met by the S/390 collating sequence.
Two field procedures are provided. The procedures are supplied as A-type members.
The field procedures provided are:
The field procedures are written in Assembler. The field procedure must be assembled and the corresponding phase must be generated and placed in a library that is accessible to the database manager when it is running.
Once the phase for the field procedure has been generated and made accessible to the database manager, it can be used by specifying its name in the FIELDPROC clause of the CREATE TABLE or ALTER TABLE statement.
This section describes certain control blocks that are used to communicate to a field procedure, under the following headings:
The FPPL is pointed to by register 1 on entry to a field procedure. It, in turn, contains the addresses of five other areas, shown in Figure 115. The FPPL and the areas to which it points are all described by the mapping macro ARIBFPPB, which is provided as an E-type member.
Figure 115. Field Procedure Parameter List
The work area is an area of storage used by a field procedure as working storage. A new area is provided each time the procedure is called.
The size of the area you need depends on the way you have programmed your field-encoding and field-decoding operations. For the field-definition operation, the database manager passes your routine a value of 512 bytes for the length of the work area (FPBWKLN in FPIB). If, for example, the longest work area you need for field-encoding or field-decoding is 1024 bytes, your field-definition operation must change the length to 1024. Thereafter, whenever your field procedure is called for either encoding or decoding, the database manager makes an area of 1024 bytes available to it.
If 512 bytes is sufficient for your operations, your field-definition operation need not change the value supplied by the database manager. If you need less than 512 bytes, your field-definition can return a smaller value. However, your field-definition itself must not use more than 512 bytes.
The FPIB communicates general information to a field procedure. For example, it tells what operation is to be done, allows the field procedure to signal errors, and gives the size of the work area. Its format is shown in Table 27.
Table 27. Format of FPIB, Defined in Copy Macro ARIBFPPB
| Name | 'Hex' Offset | Data Type | Description |
|---|---|---|---|
| FPBFCODE | 0 | Signed halfword integer | Function code.
|
| FPBWKLN | 2 | Signed halfword integer | Length of work area; the maximum is 32767 bytes. |
| 4 | Signed halfword integer | Reserved. | |
| FPBRTNC | 6 | Character, 2 bytes | Return code set by field procedure. |
| FPBRSNC | 8 | Character, 4 bytes | Reason code set by field procedure. |
| FPBTOKP | 12 | Address | Address of a 40-byte area, within the work area or within the field procedure's static area, containing an error message. |
Value descriptors describe the data type and other attributes of a value. They are used with field procedures in these ways:
The CVD contains a description of a column value and, if appropriate, the value itself. During field encoding, the CVD describes the value to be encoded; during field decoding, it describes the decoded value to be supplied by the field procedure; and during field definition, it describes the column as defined in the CREATE TABLE or ALTER TABLE statement.
The FVD contains a description of a field value and, if appropriate, the value itself. During field-encoding, the FVD describes the encoded value to be supplied by the field procedure; during field-decoding, it describes the value to be decoded. Field-definition must put into the FVD a description of the encoded value.
The format of value descriptors is shown in Table 28.
Table 28. Format of Value Descriptors
| Name | 'Hex' Offset | Data Type | Description |
|---|---|---|---|
| FPVDTYPE | 0 | Signed halfword integer | Data type of the value:
|
| FPVDVLEN | 2 | Signed halfword integer | For a varying-length string value, its maximum length. |
| FPVDVALE | 4 | None | The value. If the value is a varying-length string, the first half word is the value's actual length in bytes. This field is not present in a CVD, or in an FVD used as input to the field-definition operation. |
The FPPVL communicates the literal list, supplied in the CREATE TABLE or ALTER TABLE statement, to the field procedure during field definition. At that time the field procedure can reformat the FPPVL. The reformatted FPPVL is stored in SYSTEM.SYSFPARMS and communicated to the field procedure during field encoding and field decoding as the modified FPPVL.
Its format is shown in Table 29.
Table 29. Format of FPPVL, Defined in Copy Macro ARIBFPPB
| Name | 'Hex' Offset | Data Type | Description |
|---|---|---|---|
| FPPVLEN | 0 | Signed halfword integer | Length in bytes of the area containing FPPVCNT and FPPVVDS. At least 254 for field-definition. |
| FPPVCNT | 2 | Signed halfword integer | Number of value descriptors that follow, equal to the number of parameters in the FIELDPROC clause. Zero if no parameters were listed. |
| FPPVVDS | 4 | Structure | For each parameter in the FIELDPROC clause, there is:
|
The input provided to the field-definition operation, and the output required, are as follows:
The registers have the following information:
The contents of all other registers, and of fields not listed below, are unpredictable.
The work area consists of 512 contiguous uninitialized bytes.
The FPIB has the following information:
The CVD has the following information:
The FPVDVALE field is omitted.
The FVD provided is 4 bytes long.
The FPPVL has the following information:
The registers must have the following information:
Fields listed below must be set as shown; all other fields must remain as on entry.
The FPIB must have the following information:
Errors signalled by a field procedure result in an SQL return code of -681, which is set in the SQL communication area (SQLCA). The contents of FPBRTNC and FPBRSNC, and the error message pointed to by FPBTOKP, are also placed into the tokens, in SQLCA, as field SQLERRMT. The meaning of the error message is determined by the field procedure.
The FVD must have the following information:
Field FPVDVALE must not be set; the length of the FVD is 4 bytes only.
The FPPVL can be redefined to suit the field procedure, and returned as the modified FPPVL, subject to the following restrictions:
The modified FPPVL is recorded in the SYSTEM.SYSFPARMS catalog table and is passed again to the field procedure during field-encoding and field-decoding. The modified FPPVL need not have the format of a field procedure parameter list, and it need not describe constants by value descriptors.
The input provided to the field-encoding operation, and the output required, are as follows:
The registers have the following information:
The contents of all other registers, and of fields not listed below, are unpredictable.
The work area is contiguous, uninitialized, and of the length specified by the field procedure during field-definition.
The FPIB has the following information:
The CVD has the following information:
The FVD has the following information:
The modified FPPVL produced by the field procedure during field-definition is provided if it exists.
The registers must have the following information:
The FVD must contain the encoded (field) value in field FPVDVALE. If the value is a varying-length string, the first halfword must contain its length.
The FPIB may have the following information:
Errors signalled by a field procedure result in an SQL return code of -681, which is set in the SQL communication area (SQLCA). The contents of FPBRTNC and FPBRSNC, and the error message pointed to by FPBTOKPT, are also placed into the tokens, in SQLCA, as field SQLERRMT. The meaning of the error message is determined by the field procedure.
All other fields must remain as on entry.
The input provided to the field-decoding operation, and the output required, are as follows:
The registers have the following information:
The contents of all other registers, and of fields not listed below, are unpredictable.
The work area is contiguous, uninitialized, and of the length specified by the field procedure during field-definition.
The FPIB has the following information:
The CVD has the following information:
The FVD has the following information:
The modified FPPVL, produced by the field procedure during field-definition, is provided if it exists.
The registers must have the following information:
The CVD must contain the decoded (column) value in field FPVDVALE. If the value is a varying-length string, the first halfword must contain its length.
The FPIB may have the following information:
Errors signalled by a field procedure result in an SQL return code of -681, which is set in the SQL communication area (SQLCA). The contents of FPBRTNC and FPBRSNC, and the error message pointed to by FPBTOKP, are also placed into the tokens, in SQLCA, as field SQLERRMT. The meaning of the error message is determined by the field procedure.
All other fields must remain as on entry.
Figure 116 shows an example of a field procedure.
Figure 116. Field Procedure Example
FLCTFLC TITLE 'DB2 Server for VSE FIELD PROCEDURE EXAMPLE'
FLCTFLC START 0
FLCTFLC AMODE 31
FLCTFLC RMODE ANY
************************************************************
* DB2 Server for VSE FIELD PROCEDURE TO CONVERT *
* FIXED LENGTH CHARACTER TO FIXED *
* LENGTH CHARACTER USING A LOOKUP TABLE *
************************************************************
SPACE 3
PRINT GEN
USING FLCTFLC,R3 BASE REGISTER
USING FPIB,R9 COMMON INFORMATION BLOCK
USING FPVD,R10 VALUE DESCRIPTOR
USING FPPL,R11 PARAMETER LIST
USING WA,R12 WORK AREA
USING FPPVL,R8 PARAMETER VALUE LIST
USING TBLHDRD,R7 TABLE HEADER
SPACE 3
*************************************************************
* SET UP MAIN LINE RETURN R14 *
*************************************************************
SPACE 3
SAVE (14,12),,FLCTFLC
LR R3,R15 LOAD BASE REGISTER
LR R11,R1 PARAMETER LIST POINTER
L R12,FPPWORK WORK AREA ADDRESS
ST R13,SAVE13
L R9,FPPFPIB COMMON INFORMATION BLOCK
MVC FPBRTNC,=AL2(FPBRC0) RETURN CODE = 0
LH R2,FPBFCODE
L R15,FDLFC(R2) SELECT APPROPRIATE ROUTINE
LA R14,RET1
BR R15
RET1 DS 0H
PACK WADW,FPBRTNC SET RETURN CODE R15
CLI FPBRTNC+L'FPBRTNC-1,C' '
BNE NOTBL
PACK WADW,FPBRTNC(L'FPBRTNC-1)
NOTBL DS 0H
CVB R15,WADW
L R13,SAVE13
RETURN (14,12),T,RC=(15)
LTORG
FDLFC DC A(ENCODE,DECODE,DEFINE)
SPACE 3
****************************************************************
* ENCODING ROUTINE RETURN R14 *
****************************************************************
SPACE 3
ENCODE DS 0H
MVC FUNCT,=C'ENCD'
LA R5,B1
B CHKINP CHECK INPUT DESCRIPTION
B1 DS 0H
LA R5,B2
B CHKOUT CHECK OUTPUT DESCRIPTION
B2 DS 0H
SPACE 3
******************************************************************
* LOOKUP ROUTINE FOR ENCODING *
******************************************************************
SPACE 3
L R10,FPPCVD INPUT VALUE
L R6,TABADDR TOP OF LOOKUP TABLE
LA R5,B3
B SETLUP SET UP LOOKUP VARIABLES
B3 DS 0H
SPACE 3
*****************************************************************
* SET UP LOOP VARIABLES *
*****************************************************************
SPACE 3
SR R4,R4 CLEAR R4
IC R4,ILEN LENGTH FOR COMPARE
SH R4,=H'1' -1
ITOP DS 0H
EX R4,CLCINST
BE IHIT
A R6,INCRLEN INCREMENT TO NEXT ENTRY
BCT R13,ITOP
LA R13,ER5
B ERROR4
IHIT DS 0H
L R10,FPPFVD
SPACE 3
***************************************************************
* SET UP MOVE INSTRUCTION *
***************************************************************
SPACE 3
SR R13,R13 CLEAR R13
IC R13,OLEN OUTPUT LENGTH
SH R13,=H'1' -1
SR R5,R5 CLEAR R5
IC R5,ILEN INPUT LENGTH
AR R6,R5 POINT TO OUTPUT VALUE IN TABLE
EX R13,MVCINST
BR R14
SPACE 3
*******************************************************************
* MOVE AND COMPARE INSTRUCTION FOR EXECUTION INSTRUCTION *
*******************************************************************
SPACE 3
DS 0H
CLCINST CLC 0(1,R6),FPVDVALE
MVCINST MVC FPVDVALE,0(R6)
SPACE 3
*******************************************************************
* DECODING ROUTINE *
*******************************************************************
SPACE 3
DECODE DS 0H
MVC FUNCT,=C'DECD'
LA R5,BB1
B CHKINP CHECK INPUT DESCRIPTION
BB1 DS 0H
LA R5,BB2
B CHKOUT CHECK OUTPUT DESCRIPTION
BB2 DS 0H
SPACE 3
*******************************************************************
* LOOKUP ROUTINE FOR DECODING *
*******************************************************************
SPACE 3
L R10,FPPFVD OUTPUT VALUE
L R6,TABADDR TOP OF LOOKUP TABLE
LA R5,BB3
B SETLUP SET LOOKUP VARIABLES
BB3 DS 0H
SPACE 3
*******************************************************************
* SET UP LOOP VARIABLES *
*******************************************************************
SPACE 3
SR R4,R4 CLEAR R4
IC R4,OLEN LENGTH FOR COMPARE
SH R4,=H'1' -1
SR R5,R5 CLEAR R5
IC R5,ILEN INPUT LENGTH
AR R6,R5 POINT TO OUTPUT VALUE IN TABLE
OTOP DS 0H
EX R4,CLCINST
BE OHIT
A R6,INCRLEN POINT TO NEXT ENTRY
BCT R13,OTOP
LA R13,ER8
B ERROR4
OHIT DS 0H
L R10,FPPCVD
SPACE 3
******************************************************************
* SET UP MOVE INSTRUCTION *
******************************************************************
SPACE 3
SR R13,R13 CLEAR R13
IC R13,ILEN INPUT LENGTH
SR R6,R13 POINT TO INPUT VALUE IN TABLE
SH R13,=H'1' -1
EX R13,MVCINST
BR R14
SPACE 3
*******************************************************************
* DEFINE ROUTINE RETURN R14 *
*******************************************************************
SPACE 3
DEFINE DS 0H
MVC FUNCT,=C'DEFN'
LA R5,BBB1
B CHKINP
BBB1 DS 0H
SPACE 3
******************************************************************
* UPDATE WORK AREA LENGTH IN FPIB *
******************************************************************
MVC FPBWKLN,=Y(WAEND-WA)
SPACE 3
********************************************************************
* SET UP FIELD VALUE DESCRIPTOR *
********************************************************************
SPACE 3
L R10,FPPFVD OUTPUT DESCRIPTOR
MVC FPVDTYPE,=Y(FPVDTCHR) FIXED CHARACTER
MVI FPVDVLEN,X'00'
AH R10,=H'3'
MVC 0(1,R10),OLEN
BR R14
SPACE 3
********************************************************************
* CHECK INPUT ROUTINE RETURN R5 *
********************************************************************
SPACE 3
CHKINP DS 0H
L R8,FPPPVL
L R10,FPPCVD INPUT DESCRIPTOR
CLC =Y(FPVDTCHR),FPVDTYPE FIXED CHARACTER ?
BNE CHKINPE1
CLC FPPVCNT,=H'1' ONLY ONE PARAMETER ?
BNE CHKINPE2 NO, ERROR
LA R7,TBLHDR POINT TO TABLE HEADER TABLE
LOOP1 DS 0H
CLC CODE,FPPVVDS+8 IS VALUE IN TABLE
BNE CPEND NO, INCREMENT
B CINCL YES, A HIT
CPEND DS 0H
AH R7,=H'8' EACH TABLE ENTRY 8 BYTES
CLI CODE,X'FF' END OF TABLE?
BNE LOOP1 NO
LA R13,ER3
B ERROR8 YES, ERROR
CINCL DS 0H
CLC ILEN,FPVDVLEN+1 CHECK INPUT LENGTH
BER R5
LA R13,ER4
B ERROR4
CHKINPE1 DS 0H
LA R13,ER1
B ERROR4
CHKINPE2 DS 0H
LA R13,ER2
ERROR8 DS 0H
MVC FPBRTNC,=AL2(FPBRC8)
B ERROR
ERROR4 DS 0H
MVC FPBRTNC,=AL2(FPBRC4)
ERROR DS 0H
MVC FPBRSNC,FUNCT
ST R13,FPBTOKP
BR R14
SPACE 3
*************************************************************
* CHECK OUTPUT DESCRIPTOR RETURN R5 *
*************************************************************
SPACE 3
CHKOUT DS 0H
L R10,FPPFVD FIELD DESCRIPTOR
CLC =Y(FPVDTCHR),FPVDTYPE FIXED CHARACTER ?
BNE CHKOUTE1
CLC OLEN,FPVDVLEN+1 CHECK OUTPUT LENGTH
BER R5
LA R13,ER6
B ERROR4
CHKOUTE1 DS 0H
LA R13,ER7
B ERROR4
SPACE 3
*******************************************************************
* SET UP LOOKUP VARIABLE ROUTINE RETURN R5 *
*******************************************************************
SPACE 3
SETLUP DS 0H
SR R4,R4 CLEAR R4
IC R4,ILEN INPUT LENGTH
ST R4,INCRLEN SAVE INPUT LENGTH
SR R4,R4 CLEAR R4
IC R4,OLEN OUTPUT LENGTH
A R4,INCRLEN ADD INPUT LENGTH
ST R4,INCRLEN STORE TABLE ENTRY LENGTH
SR R13,R13 CLEAR R13
IC R13,NENTR NUMBER OF ENTRIES
BR R5
SPACE 3
********************************************************************
* ERROR MESSAGES *
********************************************************************
ER1 DC CL40'INVALID COLUMN TYPE'
ER2 DC CL40'INVALID NUMBER OF PARAMETERS'
ER3 DC CL40'INVALID PARAMETER VALUE'
ER4 DC CL40'INVALID COLUMN LENGTH'
ER5 DC CL40'INVALID INPUT VALUE TO ENCODE'
ER6 DC CL40'INVALID FIELD LENGTH'
ER7 DC CL40'INVALID FIELD TYPE'
ER8 DC CL40'INVALID FIELD VALUE TO DECODE'
SPACE 3
**********************************************************************
* TABLE HEADER TABLE *
**********************************************************************
TBLHDR DS 0F
**********************************************************************
* FIRST TABLE CODE = 'A' *
**********************************************************************
DC C'A' CODE
DC X'01' INPUT LENGTH
DC X'01' OUTPUT LENGTH
DC X'03' NUMBER OF ENTRIES
DC A(TABA) ADDRESS OF LOOKUP TABLE
**********************************************************************
* SECOND TABLE CODE = 'B' *
**********************************************************************
DC C'B' CODE
DC X'04' INPUT LENGTH
DC X'01' OUTPUT LENGTH
DC X'22' NUMBER OF ENTRIES
DC A(TABB) ADDRESS OF LOOKUP TABLE
*******************************************************************
* PUT ADDITIONAL TABLE HEADER ENTRIES HERE *
*******************************************************************
SPACE 3
*******************************************************************
* END OF TABLE HEADERS *
*******************************************************************
DC X'FF'
SPACE 3
TABA DS 0H
DC C'H' HIGH
DC C'7'
DC C'M' MEDIUM
DC C'5'
DC C'L' LOW
DC C'3'
SPACE 3
TABB DS 0H
DC C'AAA '
DC X'F0' 240
DC C'AA+ '
DC X'E6' 230
DC C'AA '
DC X'DC' 220
DC C'AA- '
DC X'D2' 210
DC C'A+ '
DC X'C8' 200
DC C'A1 '
DC X'BE' 190
DC C'A '
DC X'B4' 180
DC C'A- '
DC X'AA' 170
DC C'BBB+'
DC X'A0' 160
DC C'BBB '
DC X'96' 150
DC C'BBB-'
DC X'8C' 140
DC C'BB+ '
DC X'82' 130
DC C'BB '
DC X'78' 120
DC C'BB- '
DC X'6E' 110
DC C'B+ '
DC X'64' 100
DC C'B '
DC X'5A' 90
DC C'B- '
DC X'50' 80
DC C'CCC '
DC X'46' 70
DC C'CC '
DC X'3C' 60
DC C'C '
DC X'32' 50
DC C'D '
DC X'28' 40
DC C'NR '
DC X'1E'
SPACE 3
**********************************************************************
* TABLE HEADER TABLE DSECT *
**********************************************************************
TBLHDRD DSECT
CODE DS CL1
ILEN DS CL1
OLEN DS CL1
NENTR DS CL1
TABADDR DS A
SPACE 3
*********************************************************************
* WORK AREA *
*********************************************************************
SPACE 3
WA DSECT
SAVE13 DS F
INCRLEN DS F
FUNCT DS CL4
WADW DS D
WAEND DS 0H
SPACE 3
ARIBFPPB
R0 EQU 0
R1 EQU 1
R2 EQU 2
R3 EQU 3
R4 EQU 4
R5 EQU 5
R6 EQU 6
R7 EQU 7
R8 EQU 8
R9 EQU 9
R10 EQU 10
R11 EQU 11
R12 EQU 12
R13 EQU 13
R14 EQU 14
R15 EQU 15
END
|
+------------------End of General-Use Programming Interface------------------+