SYSPROC.SYSTS_ALTER

Invoke the SYSPROC.SYSTS_ALTER stored procedure to modify the options of a text search index that was created by using the SYSPROC.SYSTS_CREATE stored procedure.

After you modify the options for a text search index, you must run the SYSPROC.SYSTS_UPDATE stored procedure to update the text search index that is stored on the text search server.

Prerequisites

Before you invoke the SYSPROC.SYSTS_ALTER stored procedure, verify the following prerequisites:
  • Db2 text search functionality was started by invoking the SYSPROC.SYSTS_START stored procedure.
  • The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).
  • 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®.
  • The following stored procedures are not running for the index that you want to modify: SYSPROC.SYSTS_CREATE, SYSPROC.SYSTS_DROP, SYSPROC.SYSTS_RESTORE, SYSPROC.SYSTS_TAKEOVER, and SYSPROC.SYSTS_UPDATE.
  • 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 and UPDATE privileges on the following administration tables:
    • SYSIBMTS.SYSTEXTCOLUMNS
    • SYSIBMTS.SYSTEXTCONFIGURATION
    • SYSIBMTS.SYSTEXTINDEXES

In addition, the user ID that is listed in the SYSIBMTS.SYSTEXTCONNECTINFO table 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_ALTER(indexSchemanull,indexName,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).

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<update-characteristics><index-configuration-options><rename-function-information>
update-characteristics
Read syntax diagramSkip visual syntax diagramUPDATE FREQUENCYNONE< update-frequency >UPDATE MINIMUMminchanges
index-configuration-options
Read syntax diagramSkip visual syntax diagramINDEX CONFIGURATION(,optionvalue)
rename-function-information
Read syntax diagramSkip visual syntax diagramRENAME FUNCTION function-schema.function-name
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.

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.

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.

rename-function-information
Specifies the schema and name of the user-defined function that is used to access text documents.
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.

Examples

Example 1: The following example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_ALTER stored procedure. This example uses the SYSPROC.SYSTS_ALTER stored procedure to modify the value of the UPDATEWITHBACKUP parameter.

CALL SYSPROC.SYSTS_ALTER ('SCHEMA1', 'IVC2', 
'INDEX CONFIGURATION (UPDATEWITHBACKUP 0)');

This example uses the following parameters:

  • 'SCHEMA1' = indexSchema
  • 'IVC2' = indexName
  • 'INDEX CONFIGURATION (UPDATEWITHBACKUP 0)' = options

Example 2: The following example shows calling the SYSPROC.SYSTS_ALTER stored procedure to modify the value of the UPDATE MINIMUM parameter.

CALL SYSPROC.SYSTS_ALTER ('SCHEMA1', 'IVC2', 'UPDATE MINIMUM 10');

This example uses the following parameters:

  • 'SCHEMA1' = indexSchema
  • 'IVC2' = indexName
  • 'UPDATE MINIMUM 10' = options

Example 3: 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 200 rows with four commit cycles.

CALL SYSPROC.SYSTS_ALTER(  'USRT002', 'INDEX2' ,' UPDATE FREQUENCY NONE 
UPDATE MINIMUM 1 INDEX CONFIGURATION (   UPDATEAUTOCOMMIT 200, COMMITTYPE ROWS, 
COMMITCYCLES 4 )'  )

In this next example, the commit operation is performed after four hours with two commit cycles.

CALL SYSPROC.SYSTS_ALTER(  'USRT002', 'INDEX2' ,' UPDATE FREQUENCY NONE 
UPDATE MINIMUM 1 INDEX CONFIGURATION (   UPDATEAUTOCOMMIT 4, COMMITTYPE HOURS, 
COMMITCYCLES 2 )'  )

In this example, the commit operation is performed after four hours with no restriction on the number of commit cycles.

CALL SYSPROC.SYSTS_ALTER(  'USRT002', 'INDEX2' ,' UPDATE FREQUENCY NONE 
UPDATE MINIMUM 1 INDEX CONFIGURATION (   UPDATEAUTOCOMMIT 4, COMMITTYPE HOURS )'  )

In this final example, the commit operation is performed after four hours with two commit cycles.

CALL SYSPROC.SYSTS_ALTER(  'USRT002', 'INDEX2' ,' UPDATE FREQUENCY NONE 
UPDATE MINIMUM 1 INDEX CONFIGURATION (   UPDATEAUTOCOMMIT 4, COMMITTYPE HOURS, 
COMMITCYCLES 2 )'  )