17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

The following rules apply to the use of wildcard syntax in the CHECK command. Assume that the databases and tables in the examples exist in the system, unless stated otherwise.

Rule Example
You can specify the following valid ASCII characters in the wildcard syntax:
  • A … Z
  • a … z
  • 0 … 9
  • _ (low line or underscore)
  • $ (dollar sign)
  • # (number sign)

You cannot use digits 0 … 9 as wildcards to describe the first character in the name.

Example 1: The following is a valid command:
CHECK db1.t[#af_2r]1 AT LEVEL ONE;

Example 2: The following is not a valid command:

CHECK db[#,kA-d159]xy AT LEVEL ONE;

The above command results in a syntax error because the wildcards specified for database name include the non-valid comma (,). For information on syntax error messages, see Syntax Error Messages.

You must specify the wildcard characters within square brackets. The wildcard syntax begins with a left square bracket ([) and ends with a right square bracket (]). Example 1:Databases db1, db2, db3, db4, and db5 exist, and you want only the tables in db1 and db5 checked. Type the following:
CHECK db[15] AT LEVEL ONE;

CheckTable checks all the tables in databases db1 and db5 at level one. The wildcard syntax defines two possible values (1 and 5) for the third character in the database name.

Example 2:Databases db1, dc1, dd1, and so on exist, and each database contains tables t1, t2, t3, and so on. Using the wildcard syntax in any place in the name, type the following:

CHECK d[bd]1.t[123] AT LEVEL ONE;

CheckTable checks tables t1, t2, t3 in databases db1 and dd1.

Example 3: To specify wildcard syntax in multiple places in a name, type the following:

CHECK db[12][pq] AT LEVEL TWO;

CheckTable checks databases db1p, db2p, db1q, and db2q at level two. The wildcard syntax defines the possible values for the third and fourth characters of the database name.

You cannot specify the special characters % and ? within wildcard syntax. However, you can use the special characters % and ? with any valid wildcard syntax. Example 1:Databases dba1, dba2, db11 and db12 exist, and you want to check databases dba1, dba2, db11, and db12. Type the following:
CHECK db[a1]? at level one;

This command is valid, because the ‘?’ is outside the wildcard syntax.

Example 2: The following is not a valid command, because the ‘?’ is not allowed in wildcard syntax.

CHECK db[a1?] at level one;
You can use wildcard syntax to specify the names or lists of the databases and tables to check and the list of databases or tables not to check. Example 1:Databases db1, db2, db3 and db4 exist, and you type the following:
CHECK db% exclude db[34] at level one;

Databases db1 and db2 are checked.

Example 2: Databases db1, db2, db3 and db4 exist, and all these databases have tables t1, t2, t3 and t4. You type the following:

CHECK db[23] exclude t[14] at level one;

CheckTable checks tables t2 and t3 in databases db2 and db3.

You can use wildcard syntax to specify a range of characters by separating two characters with a hyphen (-). For example, C and J separated by the hyphen (C-J) represent any characters lexically between C and J inclusive.
  • The two characters should be of the same type: uppercase, lowercase, or digit.
  • The two characters can be in ascending or descending lexical order. For example, [A-D] and [D-A] both specify the same range of characters: A through D inclusive.
Example 1:
CHECK db1.t[1-35] AT LEVEL ONE;

CheckTable checks the tables t1, t2, t3, and t5 in database db1 at level one. 1-3 is considered a range, and 5 is an additional value.

Example 2:

CHECK db[a-5] AT LEVEL ONE;

The check does not take place. CheckTable reports a syntax error because the range specified in dbname is invalid. For information on syntax error messages, see Syntax Error Messages.

Wildcard syntax can include characters that might not have any matching object names in the system.

If the syntax contains some characters that do not have a match at the position specified in any object names in the system, CheckTable checks (or excludes from checking) all the objects whose names match the specified wildcards. CheckTable also ignores the characters that do not have any matching objects. This is true of any number of wildcards.

Example 1: Assume a system contains only databases db1 and db5 but not db2, db3, and so on. Type the following:
CHECK db[125] AT LEVEL ONE;

CheckTable checks all the tables in databases db1 and db5 at level one. Since database db2 does not exist, CheckTable ignores character 2 in the wildcard syntax.

Example 2: Assume a system contains the database db1 but not db2, db3, or db4. Type the following:

CHECK db[1-4] AT LEVEL ONE;

CheckTable checks all the tables in the database db1 and ignores the remaining wildcard characters.

Multiple occurrences of the same character in the wildcard syntax are valid. If you repeat the same character in the syntax for the same position, then CheckTable recognizes the first occurrence and ignores the repeated instances. Example 1: In the following command, character b is repeated in the same position.
CHECK d[abb]1 AT LEVEL ONE;

CheckTable checks all tables in the databases da1 and db1 at level one and ignores the second instance of character b. No warning appears.

Example 2: In the following command, character 3 is specified as part of the hyphen range 1-5 and is repeated separately in the same position.

CHECK db[1-53] AT LEVEL ONE;

CheckTable checks all tables in the databases db1, db2, db3, db4, and db5 at level one. CheckTable ignores the repeated character 3.

The wildcard syntax does not apply when enclosed between apostrophes or double quotation marks. In the following command, character p is a wildcard enclosed in double quotation marks.
CHECK db1."[p]" AT LEVEL ONE;

CheckTable ignores the square brackets and checks only table “[p]”, if it exists in database db1. If table “[p]” does not exist in db1, then a warning appears.