Handles contain the CCSID, format, and language of their text documents. Handles for external files contain additionally a pointer to the external file. These handles are created when you enable a text column or external files.
The UDFs described here let you set or change the text information in the handles.
>>-INIT_TEXT_HANDLE---------------------------------------------> >-----+-(--format--,--language--)-------------------------+---->< '-(--CCSID--,--format--,--language--,--filename--)--'
When you run the ENABLE TEXT COLUMN command to enable a text column that already contains text, you can implicitly set the format and language of the text to the values specified in the text configuration settings. These format and language settings are then stored in the handle. If you want different format and language values, you can specify them explicitly in the ENABLE TEXT COLUMN command.
When you run the ENABLE TEXT FILES command, you can also set the document's CCSID and location.
When you later insert a row containing text, an insert trigger creates a handle and sets the text format and language to the values that were used when the text column was enabled.
To set the format and language to values that are different from these values, use the INIT_TEXT_HANDLE function in the INSERT command. While the row is being inserted, the INIT_TEXT_HANDLE function creates a partially initialized handle that contains the language and format values you specify. The insert trigger then fills in the other values in the handle.
In the following example, INIT_TEXT_HANDLE presets the language and format in an initialized handle. The INSERT command places this handle in the COMMENTHANDLE column.
INSERT INTO DB2TX.SAMPLE (DOCID, COMMENT, COMMENTHANDLE) VALUES ('doc 101', 'I have installed...', DB2TX.INIT_TEXT_HANDLE('AMI', 'GERMAN') )
The value returned by INIT_TEXT_HANDLE is type DB2TEXTH, or DB2TEXTFH.
>>-+-CCSID----+--(--handle--)---------------------------------->< +-FORMAT---+ +-LANGUAGE-+ '-FILE-----'
Here is an example of extracting a CCSID from a handle:
SELECT DISTINCT DB2TX.CCSID(COMMENTHANDLE) FROM DB2TX.SAMPLE
In the same way, you can extract the format or the language of a text document, or the location of external files. Here is an example that illustrates the use of the FORMAT function. It returns the number of ASCII (TDS) documents in the sample table.
SELECT COUNT(*) FROM DB2TX.SAMPLE WHERE DB2TX.FORMAT(COMMENTHANDLE) = 'TDS'
>>-+-FORMAT--(--handle--,--format--)------------+-------------->< +-LANGUAGE--(--handle--,--language--)--------+ '-FILE--(--handle--,--file-path-and-name--)--'
The FORMAT and LANGUAGE functions can also change the corresponding specification in a handle. These functions return the changed handle as a value of type DB2TEXTH, or DB2TEXTFH.
The following example shows how to change the language setting of a text document.
UPDATE DB2TX.SAMPLE SET COMMENTHANDLE = DB2TX.LANGUAGE(COMMENTHANDLE, 'FRENCH') WHERE ...
Using the LANGUAGE UDF again, you can see that the change has occurred:
SELECT DISTINCT DB2TX.LANGUAGE(COMMENTHANDLE) FROM DB2TX.SAMPLE