FREE QUERY (DSN)

Environment

You can use FREE QUERY from DB2I, or from a DSN session under TSO that runs in either the foreground or background. You can also use the SYSPROC.ADMIN_COMMAND_DSN stored procedure to submit this subcommand from a remote requester.

Authorization

Syntax

>>-FREE QUERY--filter-block--package-block---------------------><

>>-+-FILTER('filter-name')-+-----------------------------------><
   +-PACKAGE(package-name)-+   
   +-QUERYID(number)-------+   
   '-QUERYID(ALL)----------'   

>>-+---------------+--.--+-collection-id-+--.--+-package-id-+--->
   '-location-name-'     '-*-------------'     '-*----------'   

>--.--(--+------------+--)-------------------------------------><
         +-version-id-+      
         '-*----------'      

Option descriptions

FILTER('filter-name')

Specifies the queries that are to be removed from the access path repository. filter-name is a value in the USERFILTER column of the SYSIBM.SYSQUERY catalog table. During FREE QUERY processing, all the rows in the SYSIBM.SYSQUERY table that have the USERFILTER value filter-name are removed. Deletions from the SYSIBM.SYSQUERY table are cascaded to the SYSIBM.SYSQUERYPLAN table or the SYSIBM.SYSQUERYOPTS table.

PACKAGE(package-name)

The name of the package from which the queries are to be freed.

QUERYID(number)

Frees an entry in the SYSIBM.SYSQUERY table that has the same QUERYID value, and the corresponding entries in the SYSIBM.SYSQUERYPLAN table or the SYSIBM.SYSQUERYOPTS table.

QUERYID(ALL)

Frees all the entries from the SYSIBM.SYSQUERY table and the corresponding entries from the SYSIBM.SYSQUERYPLAN table or the SYSIBM.SYSQUERYOPTS table.

location-name

Specifies the location of the data server where the query is to be freed. Only the location name of the local DB2® subsystem can be specified. If the location name is specified, the name of the local DB2 subsystem must be defined in the SYSIBM.LOCATIONS table. If this table does not exist or the data server is not found, an error message is issued.

The default value is the local DB2 subsystem.

collection-id or (*)

Identifies the collection that is associated with the query to be freed. There is no default.

You can use an asterisk ( * ) to free all packages with the specified package-id in all the collections that you are authorized to free.

package-id or (*)

Identifies the package that is associated with the query to be freed. There is no default.

You can use an asterisk ( * ) to free all packages in the collection-id that you are authorized to free.

version-id or (*)

Identifies the version of the package for which the associated query is to be freed.

You can use an asterisk ( * ) to free all local packages in the collection-id and package-id that you are authorized to free. You cannot use the * to free remote packages.

If you specify () for version-id , the empty string is used for the version ID.

If you omit version-id , the default depends on how you specify package-id . If you use * for package-id , version-id defaults to *. If you provide an explicit value for package-id , version-id defaults to an empty string.

( * )

Frees all local DB2 packages that you are authorized to free.

Specifying (*) is equivalent to specifying the package name as (*.*.(*)) or (*.*).

Usage notes

Freeing multiple queries:If you use FREE QUERY to free multiple queries, each successful free operation is committed before the next query is freed. If an error occurs on a query, FREE QUERY terminates for that package and continues processing the next query.

Examples

Example 1: Free all access paths for all queries:

FREE QUERY QUERYID(ALL)

Example 2: Free queries for which the USERFILTER column of the SYSIBM.SYSQUERY catalog table contains SALESAPP:

FREE QUERY
      FILTER('SALESAPP')

Example 3: Free all queries in package SALESPACK:

FREE QUERY
      PACKAGE(SALESCOLL.SALESPACK)