How to include objects in a list

You can use the LISTDEF control statement options to specify which objects to include in the list. The INCLUDE clause adds objects to the list. The EXCLUDE clause removes objects from the list.

You must first specify an INCLUDE clause. You can then specify subsequent INCLUDE or EXCLUDE clauses in any order to add to or delete objects from the existing list.

Db2 constructs the list, one clause at a time, by adding objects to or removing objects from the list. If an EXCLUDE clause attempts to remove an object that is not yet in the list, Db2 ignores the EXCLUDE clause of that object and proceeds to the next INCLUDE or EXCLUDE clause. Be aware that a subsequent INCLUDE can return a previously excluded object to the list.

You must specify either INCLUDE or EXCLUDE. No default specification exists.

Specifying objects to include or exclude

Each INCLUDE or EXCLUDE clause identifies specific objects to add to or remove from the list.

You must include the following elements in each INCLUDE or EXCLUDE clause:

The object that is to be used in the initial catalog lookup for each INCLUDE or EXCLUDE clause. The search for objects can begin with databases, table spaces, index spaces, tables, indexes, or other lists. You can explicitly specify the names of these objects or use a pattern-matching expression. When referencing another list, you must specify the list name; you cannot use pattern-matching expressions. The resulting list contains only table spaces, only index spaces, or both.

The type of objects that the list contains, either TABLESPACES or INDEXSPACES. You must explicitly specify the list type only when you specify a database as the initial object by using the keyword DATABASE. Otherwise, LISTDEF uses the default list type values shown in the following table. These values depend on the type of object that you specified for the INCLUDE or EXCLUDE clause.

Table 1. Default list type values that LISTDEF uses.
Specified object	Default list type value
TABLESPACE	TABLESPACES
TABLE	TABLESPACES
INDEXSPACE	INDEXSPACES
INDEX	INDEXSPACES
LIST	Existing type value of the list

For example, the following INCLUDE clause specifies that table space DBLT0301.TLLT031A is to be added to the LIST:

INCLUDE TABLESPACE DBLT0301.TLLT031A

In the preceding example, table space DBLT0301.TLLT031A is specified as the object that LISTDEF is to use for the initial catalog lookup. By default, the list type value for a TABLESPACE object is TABLESPACES. Therefore, the list includes only table space DBLT0301.TLLT031A.

The following example INCLUDE clause is similar to the preceding example, except that it includes the INDEXSPACES keyword:

INCLUDE INDEXSPACES TABLESPACE DBLT0301.TLLT031A

In this example, the clause specifies that all index spaces over all tables in table space DBLT0301.TLLT031A are to be added to the list.

Optionally, you can add related objects to the list by specifying keywords that indicate a relationship, such as referentially related objects or auxiliary related objects. Valid specifications include the following keywords:

BASE (non-LOB and non-XML objects)
LOB (LOB objects)
XML (XML objects)
ALL (BASE, LOB, and XML objects)
TABLESPACES (related table spaces)
INDEXSPACES (related index spaces)
RI (related by referential constraints, including informational referential constraints)

The preceding keywords perform two functions: they determine which objects are related, and they then filter the contents of the list. The behavior of these keywords varies depending on the type of object that you specify. For example, if your initial object is a LOB object, the LOB keyword is ignored. If, however, the initial object is not a LOB object, the LOB keyword determines which LOB objects are related, and Db2 excludes non-LOB objects from the list.

Db2 processes each INCLUDE and EXCLUDE clause in the following order:

Perform the initial search for the object that is based on the specified pattern-matching expression, including PARTLEVEL specification, if specified.
Add or remove related objects and filter the list elements based on the specified list type, either TABLESPACES or INDEXSPACES (COPY YES or COPY NO).
Add or remove related objects depending on the presence or absence of the RI, BASE, LOB, XML, and ALL keywords.

For example, to generate a list of all table spaces in the ACCOUNT database but exclude all LOB table spaces, you can specify the following LISTDEF statement:

LISTDEF ACCNT INCLUDE TABLESPACES DATABASE ACCOUNT BASE

In the preceding example, the name of the list is ACCNT. The TABLESPACES keyword indicates that the list is to include table spaces that are associated with the specified object. In this case, the table spaces to be included are those table spaces in database ACCOUNT. Finally, the BASE keyword limits the objects to only base table spaces.

If you want a list of only LOB index spaces in the ACCOUNT database, you can specify the following LISTDEF statement:

LISTDEF ACLOBIX INCLUDE INDEXSPACES DATABASE ACCOUNT LOB

In the preceding example, the INDEXSPACES and LOB keywords indicate that the INCLUDE clause is to add only LOB index spaces to the ACLOBIX list.

Using pattern matching expressions

You can use four special pattern-matching characters (%, *, _,?) to define generic object names in a LISTDEF statement. These characters are similar to those characters that are used in the SQL LIKE predicate. Utilities that reference a list access the Db2 catalog at execution time and dynamically expand each generic object name into an equivalent enumerated list. A utility processes this enumerated list either sequentially or in parallel, depending on the utility function and the parameters that you specify.

Restrictions:

Db2 does not support all-inclusive lists (such as DATABASE * or TABLESPACE *.*).
Pattern-matching of Db2 catalog and directory objects is not supported.

Specify pattern-matching object names by using the pattern-matching characters that are shown in the following table. This table lists the pattern-matching character, the equivalent SQL symbol, and any additional information.

Table 2. LISTDEF pattern-matching characters
LISTDEF pattern-matching character	Equivalent symbol used in SQL LIKE predicates	Usage notes
Percent sign (%)	Percent sign (%)	Performs the same function.
Question mark (?)	Underscore (_)	Use the question mark (?) instead of underscore (_) as a pattern-matching character in table and index names. The underscore character (_) in table and index names represents a single occurrence of itself.
Asterisk (*)	Percent sign (%)	Performs the same function.
Underscore (_)	Underscore (_)	Use the underscore (_) as an alternative to the question mark (?) for database, table space, and index space names.

Including catalog and directory objects

If you specify Db2 directory objects (DSNDB01) and Db2 catalog objects (DSNDB06) in LISTDEF lists, you must specify the fully qualified object names for those objects. Pattern-matching is not supported for catalog or directory objects. Even if catalog and directory objects match a LISTDEF pattern matching expression, they are not included in the list. Db2 issues error messages for any catalog or directory objects that are invalid for a utility.

Although Db2 catalog and directory objects can appear in LISTDEF lists, these objects might be invalid for a utility and result in an error message.

The following valid INCLUDE clauses contain catalog and directory objects:

```
INCLUDE TABLESPACE DSNDB06.SYSDDF
```

INCLUDE TABLESPACES TABLESPACE DSNDB06.SYSDDF

```
INCLUDE INDEXSPACE DSNDB06.DSNDXX01
```

INCLUDE INDEXSPACES INDEXSPACE DSNDB06.DSNDXX01

Restriction: If you specify a catalog or directory object in a LISTDEF control statement, the following restrictions apply:

You cannot specify the following keywords for the initial object lookup:
- DATABASE
- TABLE
- INDEX
The following keywords are ignored:
- BASE
- LOB
- ALL
- RI
- XML
- HISTORY
- ARCHIVE

Restriction: You cannot specify the following SYSUTILX-related objects in a LISTDEF specification:

TABLESPACE DSNDB01.SYSUTILX
TABLE SYSIBM.SYSUTILX
TABLE SYSIBM.SYSUTIL
INDEXSPACE DSNDB01.DSNLUX01
INDEXSPACE DSNDB01.DSNLUX02
INDEX SYSIBM.DSNLUX01
INDEX SYSIBM.DSNLUX02

All LISTDEF lists automatically exclude work file databases, which consist of DSNDB07 objects and user-defined work file objects, because Db2 utilities do not process these objects.