SYSPROC.SYSTS_DROP

Invoke the SYSPROC.SYSTS_DROP stored procedure to drop a text search index that was defined by using the SYSPROC.SYSTS_CREATE stored procedure.

Be aware that dropping the table in Db2 does not drop a text search index. You must drop a text search index by using the SYSPROC.SYSTS_DROP stored procedure either before or after dropping the table. The table does not need to exist to invoke this stored procedure; therefore, you can invoke this stored procedure after a table is dropped.

If the text search server is not reachable, the collection on the text search server might become orphaned at the server. If that happens, the collection needs to be deleted manually. This stored procedure writes console message DSN5001I, which contains the name of the collection that needs to be deleted. When the server is available again, use the Administration Tool to delete the collection at the server.

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_DROP stored procedure, verify the following prerequisites:

Db2 text search functionality was started by invocation of the SYSPROC.SYSTS_START stored procedure.
The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).
Ensure that the following stored procedures are not running for the text search index that you want to drop: SYSPROC.SYSTS_CREATE, SYSPROC.SYSTS_UPDATE, and SYSPROC.SYSTS_DROP.
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.*
INDEX privilege on the table that the text search index is created on

In addition, if the SQL ID that calls this stored procedure is different from the SQL ID that called the SYSPROC.SYSTS_CREATE stored procedure, the authorizations to drop the following objects are required:

The table SYSIBMTS.EVENTS_[n]
The table SYSIBMTS.INDEX_[n]
The table SYSIBMTS.STAGING_[n]
The trigger DSNIBMTS.ISTAGING_[n]
The trigger DSNIBMTS.USTAGING_[n]
The trigger DSNIBMTS.DSTAGING_[n]

where [n] is the index ID according to the INDEXID column of the SYSIBMTS.SYSTEXTINDEXES administration table.

Syntax

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).

Example

This example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_DROP stored procedure. This example uses the SYSPROC.SYSTS_DROP stored procedure to drop a text search index that was defined by using the SYSPROC.SYSTS_CREATE stored procedure.



CALL SYSPROC.SYSTS_DROP('SCHEMA1','IVC2')

Here is a list of the parameters used in this example.

'SCHEMA1' = indexSchema
'IVC2' = indexName

Use the SYSPROC.SYSTS_DROP stored procedure either before or after dropping the Db2 table.