Selecting Tables with the Wildcard Selector v6.2

When selecting tables for creating a publication for a single-master replication system (see Adding a Publication) or a multi-master replication system (see Adding a Publication), there may be cases where the number of available tables for selection is so large that simply choosing them from a checklist becomes a difficult and time-consuming process.

This difficulty can also be encountered when adding tables to an existing publication (see Adding Tables to a Publication) or deleting tables from an existing publication (see Removing Tables from a Publication).

In such cases, the wildcard selector provides the capability to choose a set of tables by using pattern matching similar to the technique used by the SQL statement LIKE clause.

Wildcard Selector Patterns

Pattern matching as performed by the wildcard selector is the process in which the eligible tables for an operation are returned in a filtered list if their schema and table name combination match a character string called a pattern. Matching a pattern means that the schema and table name combined in a string formatted as schema_name.table_name matches the pattern, character by character, according to the rules designated for the characters appearing in the pattern.

If the schema_name.table_name string matches the pattern, then the schema and table are displayed in the filtered list for that pattern, which is the Available Tables field of the Wildcard Selector dialog box. You can then selectively choose the tables from the filtered list to be added to a local list, which contains the potential, candidate tables for the operation for which you are using the wildcard selector.

Similarly, you can remove tables from the local list that had been previously selected if you decide that you do not want these particular tables applied for the operation.

With the exception of characters called wildcards, characters appearing in a pattern require that the character in the corresponding position in the schema_name.table_name string must match the pattern character in a case insensitive manner (that is, the letters A or a, match both A and a).

The pattern characters called wildcard characters or simply wildcards are interpreted in a special manner when compared to the corresponding character position of the schema_name.table_name character string.

Interpretation of pattern characters is described by the following:

• ? – Single-character wildcard specifies that any single character may exist in its position of the pattern. (The SQL LIKE clause uses the underscore character (_) for this purpose.)
• % - Multi-character wildcard specifies that any combination of multiple characters, including the absence of any character, may exist in its position of the pattern.
• [abc...] – List wildcard specifies that any one of the characters listed within the brackets may exist in its position of the pattern.
• [a-d] – Range wildcard specifies that any one character that is greater than or equal to the character preceding the hyphen (-) and less than or equal to the character following the hyphen may exist in its position of the pattern.
• [abcd-f...] – List and range combination wildcard specifies that any character that matches any of the list or range wildcard descriptions as described in the previous two bullet points may exist in its position of the pattern.
• Any character specified in the pattern other than ?, %, [, ], and the characters enclosed within the square brackets of a list or range wildcard must exist in its position of the pattern. Pattern matching of such characters is case insensitive (for example, a pattern of edb.dept matches a schema and table with the name EDB.Dept).
• NOT pattern, !pattern, ! pattern – Exclusive pattern specifies that tables that match the pattern string indicated by pattern are omitted from the filtered list. Tables that do not match pattern are included in the filtered list. The keyword NOT may be in uppercase, lowercase, or mixed case, but must be followed by a single space character preceding pattern. !pattern specifies that pattern immediately follows the exclamation point (!) with no intervening space character. ! pattern specifies that a single space character exists between pattern and the exclamation point (!).
• pattern* - Specify the asterisk (*) immediately following the pattern with no intervening space character if you want to include tables in the filtered list that match pattern and have been previously selected (that is, the local list tables) along with tables that have not been selected. In the filtered list, each previously selected, local list table is displayed with a check mark in its check box. Each filtered list table that was not previously selected has no check mark in its check box. By default when the asterisk is omitted, only tables that have not been previously selected are returned in the filtered list. Using the asterisk is useful for removing currently selected tables from the local list.

The wildcard pattern definitions and examples can be seen from a help screen displayed by clicking the secondary mouse button on the Filter Pattern text field of the Wildcard Selector dialog box:

Figure 7-1: Filter Pattern Help screen

The following section describes the basic steps for using the wildcard selector.

Using the Wildcard Selector

This section describes the basic process of using the wildcard selector. The following terms are used in the Wildcard Selector dialog box and the description of the wildcard selector feature:

• Calling Dialog Box. This is the dialog box of the operation from which you invoke the Wildcard Selector dialog box. The final set of tables from the wildcard selector is applied to the operation managed by the calling dialog box. Possible calling dialog boxes are the Create Publication dialog box (see Adding a Publication for a single-master replication system or Adding a Publication for a multi-master replication system), the Add Tables dialog box (see Adding Tables to a Publication), and the Remove Tables dialog box (see Section Removing Tables from a Publication).
• Table List. This is the list of currently selected tables displayed in the calling dialog box. Each selected table has a check mark in its check box.
• Local List. This is a temporary, internal copy of the table list managed by the wildcard selector. The wildcard selector allows you to add tables to the local list and to remove tables from the local list. When you click the Done button of the Wildcard Selector dialog box, the local list becomes the table list. In other words, the local list tables appear as the selected tables of the calling dialog box.
• Unselected Tables. These are the tables eligible for, but have not been selected for the operation with which you are using the wildcard selector. When you click the Filter List button, the unselected tables that match the filter pattern are listed in the Available Tables field of the Wildcard Selector dialog box. To list all unselected tables, use the percent sign (%) for the filter pattern.
• Selected Tables. These are the tables you have selected for the operation with which you are using the wildcard selector. That is, these are the tables comprising the local list. To display selected tables that match a filter pattern, add the asterisk character (*) immediately after the filter pattern. Each selected table has a check mark in its check box.

The following describes the steps for using the wildcard selector.

Step 1: Prior to opening the Wildcard Selector dialog box, you may start selecting tables from the list of available tables of the calling dialog box by adding a check mark to the check box of each such table.

From the calling dialog box, click the Use Wildcard Selection button to open the Wildcard Selector dialog box.

The tables that you have preselected are included in the local list used by the wildcard selector to manage the addition or removal of tables.

For example, the following is the Create Publication dialog box from which the wildcard selector can be used:

Figure 7-2: Invoking the wildcard selector from a calling dialog box

Step 2: The Available Tables field displays the filtered list matching the pattern used in the Filter Pattern text field.

When the Wildcard Selector dialog box is initially opened, the default filter pattern is the percent sign (%), which returns all eligible, unselected tables.

Figure 7-3: Wildcard Selector dialog box

Step 3: Enter a pattern in the Filter Pattern text field to narrow down your desired table selection. Click the Filter List button to display the tables that match the pattern.

Figure 7-4: Tables matching a filter pattern

Step 4: Select tables from the Available Tables list that you want to add to the local list by placing a check mark in each such table’s check box. You can also click the Select All check box to select all tables and then individually deselect certain tables by removing its check mark.

Figure 7-5: Tables selected for the local list

Step 5: Click the Apply Selections to Local List button to add the selected tables to the local list.

The following example shows that the selected tables have been removed from the Available Tables list after the Apply Selections to Local List button was clicked since they are no longer unselected.

Figure 7-6: Selected tables added to the local list

Step 6: As many times as desired, repeat steps 3 through 5 using the filter patterns needed to add all of your desired tables to the local list.

The following example shows a second filter pattern and the returned filter list.

Figure 7-7: Tables matching a second filter pattern

All tables are then selected from this filtered list by clicking the Select All check box.

Figure 7-8: Select all tables

The Apply Selections to Local List button is clicked to add all tables to the local list. After applying the selections, there are no unselected tables remaining that match the filter pattern.

Figure 7 9 – All filter list tables added to the local list

By using the asterisk after the pattern, you can display previously selected tables comprising the local list. Each selected table has a check mark its check box.

Figure 7-10: Display selected and unselected tables matching the filter pattern

You can remove selected tables from the local list by clicking on each such table’s check box to remove the check mark.

The following filter pattern includes the tables to be removed from the local list.

Figure 7-11: List selected tables to be removed

The check marks are removed from the selected tables to be removed from the local list.

Figure 7-12: Deselect tables to be removed from the local list

The removal of the deselected tables from the local list occurs along with the addition of any newly selected tables when you click the Apply Selections to Local List button.

Figure 7-13: Deselected tables removed from the local list now shown as unselected

The deselected tables still appear in the Available Tables list since they still match the pattern, but as unselected tables (that is, with no check mark in each such table’s check box).

Step 7: When the local list contains all of your desired, selected tables, click the Done button. The Wildcard Selector dialog box closes, and the local list becomes the list of selected tables displayed by the calling dialog box.

Figure 7-14: Create Publication calling dialog box with applied local list

Figure 7-15: Create Publication calling dialog box with applied local list (continued)

Alternatively, if you decide that you do not wish to apply the local list, click the Cancel button. The local list changes are discarded and the table list of the calling dialog box remains unchanged.

Step 8: You can invoke the wildcard selector again and repeat the process to add tables to, or remove tables from the table list by beginning with Step 1.

The following example verifies that if you were to invoke the wildcard selector a second time, the local list includes the table list created from the prior closure of the wildcard selector.

Figure 7-16: Local list includes selected tables from the calling dialog box table list

Step 9: When the calling dialog box contains the complete list of your desired tables, click the appropriate button of the calling dialog box to complete the operation with the selected tables.

The following shows the publication created from the selected tables.

Figure 7-17: Publication created with the selected tables

• On this page
• Wildcard Selector Patterns
• Using the Wildcard Selector