SYSPROC.SYSTS_CREATE

Invoke the SYSPROC.SYSTS_CREATE stored procedure to enable a text column for text search indexing, which allows the text search index to be used in SQL queries that contain the CONTAINS function or the SCORE function.

The physical text search index is created on one of the text search servers that is listed in the SYSIBMTS.SYSTEXTSERVERS table. Because updating a text search index is an extensive operation, the text search index that is maintained on the text search server is not updated synchronously when the Db2 table is updated. Instead, changes to the Db2 table column are captured by triggers to a local log table.

Tip: Consider the following tips when using this stored procedure:
  • You can prevent a text search server from being chosen by updating the STATUS column of the text search server entry in the SYSIBMTS.SYSTEXTSERVERS table to a value of 1. Only servers with a STATUS value of 0 (zero) are selected for creating a new text search index. Changing the STATUS value also affects the text search servers that are considered by the SYSPROC.SYSTS_TAKEOVER stored procedure. Other administration stored procedures are not affected by the STATUS value.
  • This stored procedure defines only the text search index. The text search index does not contain any data until after the first invocation of the SYSPROC.SYSTS_UPDATE stored procedure for the new text search index. You should create the text search index after the table is initially populated. By creating the text search index after the table is initially populated, you prevent the activation of change triggers before an initial index update.
  • To get the best performance for the CONTAINS and SCORE functions, the table that is specified in the SYSPROC.SYSTS_CREATE stored procedure must have an index on its ROWID column.
  • If you create the ROWID column in your text table with the option NOT NULL GENERATED BY DEFAULT, Db2 automatically creates an implicit index on the ROWID column. If you create the ROWID column with the option NOT NULL GENERATED ALWAYS, Db2 does not create an implicit index on the ROWID column.
Note: All stored procedures can perform COMMIT statements on the current connection. This capability makes it possible to keep changes on the text search server consistent with your Db2 subsystem. After you invoke a stored procedure, you cannot roll back the changes that are made on your Db2 subsystem or the text search server. Rollback in a stored procedure is done only to a savepoint that is set when you invoke the stored procedure.

Prerequisites

Before you invoke the SYSPROC.SYSTS_CREATE stored procedure, verify the following prerequisites:
  • Db2 text search functionality was started by invoking the SYSPROC.SYSTS_START stored procedure.
  • The table includes a column that is defined as ROWID column.
  • The SYSIBMTS.SYSTEXTSERVERS table contains at least one entry, and the DB2ENCRYPTEDPW column in the SYSIBMTS.SYSTEXTSERVERS table has a valid, encrypted password value for the user ID that the text search server uses to connect to Db2 for z/OS®.
  • At least one text search server is running.
  • The distributed data facility (DDF) must be started, even if you call the stored procedure locally on your z/OS system. The DDF is required to allow the text search server to connect to Db2 to store a text search index.

Authorization

The user ID under which this stored procedure is invoked must have the following privileges:
  • EXECUTE on the procedure
  • EXECUTE on the packages SYSIBMTS.*
  • SELECT, INDEX, and TRIGGER privileges on the table that the text search index is created on
  • CREATEIN privilege on the schema "*"
  • DBADM authority on database SYSIBMTS

Alternatively, for DB2® 10 and later, the user ID under which this stored procedure is invoked could have the following privileges instead of the privileges listed previously:

  • EXECUTE on the procedure
  • EXECUTE on the packages SYSIBMTS.*
  • SELECT, INDEX, and TRIGGER privileges on the table that the text search index is created on
  • DBADM WITHOUT ACCESSCTRL WITHOUT DATAACCESS

In addition, the user ID that is listed in the SYSIBMTS.SYSTEXTCONNECTINFO table, or that is obtained from the data source property at the text search server, must have SELECT, INSERT, UPDATE, and DELETE privileges for the text search index table after connecting to Db2 for z/OS on a T4 Java™ connection and going through the DRDA connect processing. The database name is SYSIBMTS. The index table name is SYSIBMTS.INDEX_[n], where [n] is the index ID according to the INDEXID column of the SYSIBMTS.SYSTEXTINDEXES administration table.

Syntax

Read syntax diagramSkip visual syntax diagramSYSTS_CREATE(indexSchemanull,indexName,textSource,options)

The schema qualifier is SYSPROC.

Parameters

indexSchema
Identifies the schema of the text search index. If this parameter is null, the value of the CURRENT SCHEMA special register for the invoker is used.

Recommendation: Use a valid SQL name for this parameter.

The data type of this parameter is VARCHAR(128).

indexName
Identifies the name of the text search index. The name of the text search index together with the index schema uniquely identifies the text search index in the Db2 subsystem. You must specify a non-null value for this parameter.

Recommendation: Use a valid SQL name for this parameter.

The data type for this parameter is VARCHAR(128).

textSource
Identifies the table and column specification for the document text source. This parameter can include user-defined functions. You must specify a non-null value for this parameter.

The data type for this parameter is VARCHAR(1024).

textSource
Read syntax diagramSkip visual syntax diagramtableSchema. tableName( text-column-namefunction-schema.function-name(text-column-name) )
tableSchema
Identifies the schema of the table that the text search index is created on.
tableName
Identifies the name of the text table that contains the column that the external text search index is created on.
Restriction: Views are not supported.
text-column-name
Identifies the name of the column that contains the text that is used for creating the text search index. This column must be a string data type or XML. If the data type of the column is not one of these data types, you can specify an external function that provides access to the text document that you want to index.
Restriction: Only one text search index is allowed for a column. If a text search index already exists for the column, SQLCODE-20427 is returned.
function-schema. function-name
Identifies the schema and the name of a built-in or user-defined function that is to be used by the Db2 for z/OS text search feature to access text documents that are in a column that is not of a supported data type, or that are stored elsewhere. The function has one input parameter for the text column data type (for example, an integer that serves as a foreign key to the document content in another table), and it returns a value of one of the supported data types. The function transforms the text column content to the indexed document content.
Tip: For optimal performance, specify the ALLOW PARALLEL option when the function is created.
Restriction: Cast functions and functions with more than one argument are not allowed.
options
A character string that specifies the various options that are available for this stored procedure.

The data type for this parameter is VARCHAR(32000).

options
Read syntax diagramSkip visual syntax diagram<text-default-information><update-characteristics><index-configuration-options>
text-default-information
Read syntax diagramSkip visual syntax diagramCCSIDccsidLANGUAGElanguageFORMATformat
update-characteristics
Read syntax diagramSkip visual syntax diagramUPDATE FREQUENCYNONE< update-frequency >UPDATE MINIMUMminchanges
index-configuration-options
Read syntax diagramSkip visual syntax diagramINDEX CONFIGURATION(,optionvalue)
text-default-information
Specifies the coded character set identifier that is used when indexing binary text documents, the language that is used when processing documents, and the format of text documents in the column.
CCSID ccsid
Specifies the coded character set identifier that is used for a text search index on a column with a binary data type. The default value is 1208 and is taken from the SYSIBMTS.SYSTEXTDEFAULTS table. All of the CCSIDs that are supported for conversion to UTF-8 by z/OS Unicode Conversion Services are allowed for this parameter.

This parameter is ignored for a text search index on a column with a non-binary data type because text columns inherit the CCSID from the table specification. The format value INSO lets the text search server determine the format. In this case, the ccsid value is ignored, however ccsid must be a valid input if it is specified in SYSTS_CREATE stored procedure. If the text search server cannot determine the document format, then a document error is noted in the events table during processing by the SYSPROC.SYSTS_UPDATE stored procedure.

LANGUAGE language
Specifies the language that the text search server uses for the linguistic processing of text documents. The default value is en_US (English). If you specify the value for this parameter as AUTO, the text search server tries to determine the language.
Important: If the language of the documents is not English, do not use the default value of en_US. Change the value to the language of the documents; otherwise, linguistic processing does not work as expected.
FORMAT format
Identifies the format of text documents in the column, such as HTML. The text search server needs to know the format, or content type, of the text documents that you intend to index and search. If you do not specify the FORMAT parameter, the default value is taken from the FORMAT column in the SYSIBMTS.SYSTEXTDEFAULTS table. The supported format values are TEXT, HTML, XML, and INSO.

An XML column is always interpreted always as format XML, ignoring any value that you specify and ignoring the default value.

The format value INSO lets the text search server determine the format. In this case, the ccsid value is ignored; however, the ccsid must be a valid input if it is specified in SYSTS_CREATE stored procedure. If the text search server cannot determine the document format, then a document error is noted in the events table during processing by the SYSPROC.SYSTS_UPDATE stored procedure.

update-characteristics
Specifies the frequency of updates to the text search index and the minimum number of changes to text documents before the text search index is updated incrementally at the specified time.
UPDATE FREQUENCY update-frequency
Specifies when to make updates to the text search index. The default value is NONE. This option might be useful for a text column in which no further changes are planned.

Text search index updates are not performed automatically. The scheduling of update requests is the responsibility of the Db2 for z/OS database administrator.

For each text search index, the UPDATE FREQUENCY value is stored as data type VARCHAR in the UPDATEFREQUENCY column of the SYSIBMTS.SYSTEXTINDEXES administration table. The value is stored in UNIX cron format.

To activate automatic text search index updates, schedule an index update task in Db2 for z/OS for each text search index. This task must call the SYSPROC.SYSTS_UPDATE stored procedure.

To schedule an index update task in Db2 for z/OS, you can use the Db2 administrative task scheduler. Call the SYSPROC.ADMIN_TASK_ADD stored procedure and provide the UPDATEFREQUENCY value from the SYSIBMTS.SYSTEXTINDEXES administration table as the input parameter point-in-time.

update-frequency
Read syntax diagramSkip visual syntax diagram< minute>< hour>< dayOfMonth>< monthOfYear>< dayOfWeek>

The format of the update-frequency option is a list of the five values separated by a blank space. The five values represent the minutes, the hours, the days of the month, the months of the year, and the days of the week beginning with Sunday.

If you specify an interval of values, or an asterisk (*), you can use a forward slash (/) at the end of the defined interval to specify a step value for this interval.

Example: This example specifies that the index update is to run every quarter hour (0,15,30,45) on the even hours between 8 a.m. and 6:45 p.m. (8-18/2 is equivalent to 8,10,12,14,16,18), from Monday to Friday every month of the year (* * 1-5). 
0,15,30,45 8-18/2 * * 1-5
minute
Specifies the minutes of the hour when the text search index is to be updated. You can specify an asterisk (*) for an interval of every five minutes, or you can specify an integer from 0 (zero) through 59. You cannot repeat values. The minimum update frequency is five minutes. A value of 1,4,8 is an invalid interval.
update-frequency (minute)
Read syntax diagramSkip visual syntax diagram*/ 0...59,0...59 - 0...59/ 0...590...59
hour
Specifies the hours of the day when the text search index is to be updated. You can specify an asterisk (*) for every hour, or you can specify an integer from 0 (zero) through 23. You cannot repeat values.
update-frequency (hour)
Read syntax diagramSkip visual syntax diagram*/ 0...23,0...23 - 0...23/ 0...230...23
dayOfMonth
Specifies the days of the month when the text search index is to be updated. You can specify an asterisk (*) for every day, or you can specify an integer from 1 through 31. You cannot repeat values.
update-frequency (dayOfMonth)
Read syntax diagramSkip visual syntax diagram*/ 1...31,1...31 - 1...31/ 1...311...31
monthOfYear
Specifies the months of the year when the text search index is to be updated. You can specify an asterisk (*) for every month, or you can specify an integer from 1 through 12. You cannot repeat values.
update-frequency (monthOfYear)
Read syntax diagramSkip visual syntax diagram*/ 1...12,1...12 - 1...12/ 1...121...12
dayOfWeek
Specifies the days of the week when the text search index is to be updated. You can specify an asterisk (*) for every day, or you can specify an integer from 0 (zero) through 7. Both 0 and 7 are valid values for Sunday. You cannot repeat values.
update-frequency (dayOfWeek)
Read syntax diagramSkip visual syntax diagram*/ 0...7,0...7 - 0...7/ 0...70...7
UPDATE MINIMUM minchanges
Specifies the minimum number of changes that are made to text documents before the text search index is updated incrementally at the time specified in the update-frequency option. The value must be an integer in the range 1–2147483647. The default value is taken from the UPDATEMINIMUM column in the SYSIBMTS.SYSTEXTDEFAULTS table.

This option is ignored when you update the text search index, unless you specify the USING UPDATE MINIMUM option in the SYSIBMTS.SYSTS_UPDATE stored procedure.

index-configuration-options
Specifies additional index-specific values as option value pairs. You must enclose string values in single quotation marks. A single quotation mark within a string value must be represented by two consecutive single quotation marks.
COMMENT
Specifies a comment that is stored in the REMARKS column of the SYSIBMTS. SYSTEXTINDEXES administration table and as the description of the text search server collection.

The value for this option is a string value that is less than or equal to 512 bytes.

IGNOREEMPTYDOCS
Specifies whether to represent empty documents (documents with an empty string or a null value) in the text search index.

The supported values for this option are 0 (zero) and 1. The default value is 1.

If this option is set to 1, empty documents are not represented in the text search index. If you use this option and change the document content to empty, the next incremental update deletes the documents from the text search index.

UPDATEAUTOCOMMIT
Specifies how often a commit operation is performed when fetching documents during an index update. A value of 0 (zero) means that a commit operation occurs only at the end of processing. The default value is 100 and COMMITTYPE specified as ROWS.

UPDATEAUTOCOMMIT requires a numeric value. This value represents either a number of rows or a number of hours, depending on the specification for COMMITTYPE. If COMMITTYPE is set to HOURS, the value cannot exceed 24. The text index is committed when the value of UPDATEAUTOCOMMIT is reached.

If update processing takes a very long time and Db2 active logs are small, consider using the default value. Otherwise, the active log entries of customer transactions that run in parallel to the text search index update cannot be cleared and might lead to a full the active log.

COMMITTYPE
Specifies either ROWS or HOURS. If you specify ROWS, the text index is committed after the number of rows that is specified by UPDATEAUTOCOMMIT is reached. If you specify HOURS, the text index is committed after the period of time that is specified by UPDATEAUTOCOMMIT is reached. The default value is ROWS.

You must use this option in conjunction with UPDATEAUTOCOMMIT or COMMITCYCLES.

COMMITCYCLES
Specifies a numeric value for the number of commit cycles for the processing of an index update. By default, the number of commit cycles is unrestricted. If COMMITCYCLES is 0 (zero), the update process uses as many cycles as needed to finish processing.

You must use this option in conjunction with UPDATEAUTOCOMMIT and COMMITTYPE.

SERVER
Specifies the value for the server on which to create an index.

The value can be an integer specified as the SERVERID in the SYSIBMTS.SYSTEXSTSERVERS table. There is no default value for this option. This option is valid with the SYSTS_CREATE stored procedure only.

UPDATEWITHBACKUP
Specifies whether the text search index is backed up.

The supported values for this option are 0 (zero) and 1. The default value is 1.

The value 1 specifies that the text search index is backed up in the index table during the processing of the SYSIBMTS.SYSTS_UPDATE stored procedure.

The value 0 (zero) specifies that the text search index is not backed up in the index table during processing of the SYSIBMTS.SYSTS_UPDATE stored procedure.

If the value is changed from 0 to 1, you must use the SYSIBMTS.SYSTS_UPDATE stored procedure with the ALLROWS option to save the current index in the backup table.

Default values for the options parameter

When you enable text search support for Db2 for z/OS, the SYSIBMTS.SYSTEXTDEFAULTS table is created and populated with default values for the options parameter of the SYSPROC.SYSTS_CREATE stored procedure.

The following table lists the options, default values, and descriptions of the options.

Table 1. Default values for the options parameter
Option Default value Description
CCSID 1208 Specifies the coded character set identifier that is used when binary text documents are indexed.
LANGUAGE en_US Specifies the language that the text search server uses to process text documents.
FORMAT TEXT Identifies the format of text documents in the column. The default format is plain text, except for XML columns, which are always identified as XML.
UPDATEFREQUENCY NONE RESERVED. (Indicates that no updates are scheduled.)
UPDATEMINIMUM 1 If at least one document changed since the last index update, the SYSPROC.SYSTS_UPDATE stored procedure starts processing.
IGNOREEMPTYDOCS 1 Specifies that empty documents (documents with an empty string or a null value) are not represented in the text search index. The metadata fields for these documents are not available for search.
UPDATEAUTOCOMMIT 100 and COMMITTYPE as ROWS Specifies how often a commit operation is performed when documents are fetched during an index update.

If update processing takes a very long time and Db2 active logs are small, consider using the default value. Otherwise, the active log entries of customer transactions that run in parallel to the text search index update cannot be cleared and might lead to a full active log.
COMMITTYPE ROWS Specifies whether the value for UPDATEAUTOCOMMIT represents a number of rows or a number of hours.
COMMITCYCLES 0 Specifies the number of commit cycles for the processing of an index update.
MINIMUMUPDATEINTERVAL 5 Specifies the intervals for the UPDATEFREQUENCY option. Intervals cannot be shorter than five minutes.
USEREXITTHREADS 0 Reserved
UPDATEWITHBACKUP 1 Specifies that the Text Search index is backed during the processing of SYSTS_UPDATE.

Examples

Example 1: This example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_CREATE stored procedure. This example uses the SYSPROC.SYSTS_CREATE stored procedure to create a text column for text search indexing, which allows the text search index to be used in SQL queries that contain the CONTAINS function or the SCORE function.



CALL SYSPROC.SYSTS_CREATE('SCHEMA1','IVC2','SYSADM.T1(VC2)','')
This example uses the following parameters:
  • 'SCHEMA1' = indexSchema
  • 'IVC2' = indexName
  • 'SYSADM.T1(VC2)' = textSource
  • ' ' = options

The options parameter is null by default. The values in the SYSIBMTS.SYSTEXTDEFAULTS table are used as the default value when the options parameter is null. The SYSIBMTS.SYSTEXTDEFAULTS table is created and populated with default values for the options parameter of the SYSPROC.SYSTS_CREATE stored procedure.

Example 2: This example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_CREATE stored procedure with the index configuration SERVER option.



call sysproc.systs_create('SCHEMA1', 'IVC3', 'SYSADM.T1(VC3)', 
'INDEX CONFIGURATION( SERVER 1 )')
This example uses the following parameters:
  • 'SCHEMA1' = indexSchema
  • 'IVC3' = indexName
  • 'SYSADM.T1(VC3)' = textSource
  • 'INDEX CONFIGURATION( SERVER 1 )' = options

Example 3: This example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_CREATE stored procedure with the index configuration UPDATEWITHBACKUP option.



call sysproc.systs_create('SCHEMA1', 'IVC4', 'SYSADM.T1(VC4)', 
'INDEX CONFIGURATION( UPDATEWITHBACKUP 0 )')
This example uses the following parameters:
  • 'SCHEMA1' = indexSchema
  • 'IVC4' = indexName
  • 'SYSADM.T1(VC4)' = textSource
  • 'INDEX CONFIGURATION( UPDATEWITHBACKUP 0 )' = options

Example 4: The following examples show how you can use the UPDATEAUTOCOMMIT, COMMITTYPE, and COMMITCYCLES options to specify how often a commit operation is performed when documents are fetched during an index update. In the first example, the commit operation is performed after two hours with two commit cycles. In the second example, the commit operation is performed after two hours with no restriction on the number of commit cycles.

CALL SYSPROC.SYSTS_CREATE('USRT002', 'ITEST', 'USRT002.TEST(TEXT)',
 'INDEX CONFIGURATION ( UPDATEAUTOCOMMIT 2, COMMITTYPE HOURS ,COMMITCYCLES 2)' )
CALL SYSPROC.SYSTS_CREATE('USRT002', 'ITEST', 'USRT002.TEST(TEXT)',
 'INDEX CONFIGURATION ( UPDATEAUTOCOMMIT 2, COMMITTYPE HOURS )' )