Specifying a list of available schemas and tables

Locations where a list can be specified

The following table has four rows. Each row describes one of the locations in the Host On-Demand user interface (and the corresponding location in the Database On-Demand user interface) where a list of available schemas and tables can be specified:

The locations described in the second and third rows of the above table are available to the user only if permitted by the administrator. To make these functions available to the user:

• For Host On-Demand clients, enable the option Edit Table filter on the Edit Permissions tab of the Disable Functions window (see Edit Table filter).
• For Database On-Demand clients, select the checkbox Allow editing of Table filter on the General tab of the Database On-Demand Group Options window or the Database On-Demand User Options window (see Allow editing of Table filter).

The location described in the fourth row is always available to the user.

Default value if a list is not specified

For the locations described in the first three rows of the table above:

• By default Host On-Demand leaves the input field blank.
• The significance of a blank input field depends on the platform on which the database is located:
  • If the database is on a non-iSeries platform, then a blank field means all schemas and tables.
  • If the database is on an iSeries platform, then a blank field means all schemas (iSeries "libraries") in the user portion of the iSeries library list. (This is equivalent to the value *USRLIBL on the iSeries.)

    SQL Assist Exception

    Users may encounter the error message SQL Assist Exception in the following circumstances:
    • The user is running a Java 2-enabled browser.
    • The user is attempting to connect to a remote database server on an iSeries running an older version of OS/400 (such as OS/400 V4R5).
    • The system administrator has specified the default value (a blank field, signifying *USRLIBL) in the Table Filter field.
    To work around this problem, the system administrator should set the Table Filter field to %, signifying all tables.
    • For Host On-Demand clients, see Table Filter
    • For Database On-Demand clients, see Table Filter (Administrator, Database On-Demand).

For the location described in the fourth row of the table above:

• By default Host On-Demand sets this input field to the percent sign (%).
• In this input field, a percent sign (%) by itself signifies all tables (literally, every table whose name contains 0 or more characters).

How to specify the elements in a list

The last column of each row in the table at the top of this help file shows, for the specified location, what are the valid contents of the list of available schemas and tables. More specifically:

• In the locations described in the first three rows of the table, you can specify a comma-separated list containing one or more of the following elements:
  • Schema name
  • Schema filter
  • Table name
  • Table filter

  For example:

  QIWS, IBMTEST%, MYSCHEMA.TEST01, MYSCHEMA.%TEST, %R.%

  For an explanation of this example, see the last row of the table of examples in the next subsection (Examples).

• In the location described in fourth row of the table at the top of this help file, you can specify either:
  • One table name; or
  • One table filter.

  For example:

  %TEST

  For an explanation of this example, see the table of examples in the next subsection (Examples).

  Special rules for the location described in the fourth row of the table above:
  Do not specify a comma-separated list of table names and table filters in this location. Do not specify a schema name or a schema filter in this location. To specify a table, do not use the format schema.table in this location. Instead, use the format table.

The following paragraphs describe how to specify each type of element. For an explanation of the examples, see the table of examples in the next subsection (Examples).

• Schema name:
  Type the schema name, without a period (.). Examples: QIWS, MYSCHEMA
• Schema filter:
  Type a schema name that includes one or more of the following special characters:
  • A percent sign (%) signifies 0 or more occurrences of any character. (Therefore a percent sign by itself signifies every schema whose name contains 0 or more characters, that is, all schemas.)
  • An underline character ( _ ) signifies 1 occurrence of any character.
  Examples: IBMTEST%, %TEST, MYSCHEM_
• Table name:
  Use one of the following formats:
  • schema_name.table_name
    Type the schema name, followed by a period (.), followed by a table name. Examples: IBMTEST.TEST01

    Use this format only in the locations described in the first three rows of the table at the top of this help file.

  • table_name
    Type just the table name, without a schema name or a period. Examples: TEST01

    Use this format only in the location described in the fourth row of the table at the top of this help file.

• Table filter:
  Type a table name that includes one or more of the special characters percent (%) or underline ( _ ). These special characters have the same meanings in a table filter as they do in a schema filter.

  Use the appropriate format:

  • schema_name.table_name
    Use this format only in the locations described in the first three rows of the table at the top of this help file. Examples: MYSCHEMA.%TEST, %.R%
  • table_name
    Use this format only in the location described in the fourth row of the table at the top of this help file. Examples: %TEST, R%

Examples

The following table contains examples of how to specify schema names, schema filters, table names, and table filters. The table includes both the examples mentioned in preceding subsections of this help file and also other examples.

Example:	Meaning:
%	All schemas. (Literally, every schema whose name contains 0 or more characters.)
*USRLIBL,MYSCHEMA	All schemas (iSeries "libraries") in the user portion of the iSeries library list, plus the schema named MYSCHEMA.
_	All schemas whose names have exactly one character.
QIWS	The schema named QIWS.
MYSCHEMA	The schema named MYSCHEMA.
IBMTEST%	All schemas with names beginning with IBMTEST (such as IBMTESTSUITE, IBMTEST01, and so on).
%TEST	All schemas with names ending with TEST (such as IBMTEST, INITIALTEST, and so on).
MYSCHEM_	All schemas with names beginning with MYSCHEM and having exactly eight characters (such as MYSCHEMA, MYSCHEMB, and so on).
__TEST	All schemas with names ending with TEST and having exactly six characters (such as RETEST, MYTEST, and so on).
%ES%	All schemas with names containing the character sequence ES (such as IBMTEST, ESPY, TRIES, and so on).
_ES_	All schemas with names containing the character sequence ES and having exactly one character before and one character after (such as TEST, REST, and so on).
IBMTEST.TEST01	Table TEST01 in schema IBMTEST.
%.TEST01	Table TEST01 in all schemas (that is, table TEST01 in schema IBMTEST, and table TEST01 in schema MYSCHEMA, and table TEST01 in schema MYSCHEMB, and so on).
TEST01	Table TEST01. Use this format only in the location described in the fourth row of the table at the top of this help file. Do not use a schema name, a schema filter, or the schema.table table format in this location.
MYSCHEMA.%TEST	All tables in MYSCHEMA with names ending in TEST (such as MYSCHEMA.MYTEST).
MYSCHEMA._TEST	All tables in MYSCHEMA with names ending in TEST and having exactly five characters. (such as MYSCHEMA.XTEST).
%TEST	All tables with names ending in TEST (such as MYTEST, RETEST). Use this format only in the location described in the fourth row of the table at the top of this help file. Do not use a schema name, a schema filter, or a different table format in this location.
%.R%	All tables with names beginning with R, in all schemas.
QIWS, IBMTEST%, MYSCHEMA.%TEST, %.R%	The schema QIWS , all schemas with names beginning with IBMTEST, all tables in MYSCHEMA with names ending in TEST, and all tables beginning with R in all schemas.

A list is the sum of its elements

The meaning of an entire list is the sum of the meanings of the individual elements. In more technical terms, the meaning of a entire list is arrived at by logically adding together (using logical OR) the meanings of the individual elements.

For example the following list:

R%, MYSCHEMA.TEST01, A%

• All the schemas beginning with R, plus
• Table TEST01 in schema MYSCHEMA, plus
• All the schemas beginning with A.

Related topics:

• Levels of filtering for schema names and table names
• List of available tables
• Table Filter (Administrator, Database On-Demand)
• Table filter
• Java 1 browser:
  • Table Filter (SQL Wizard)
  • Table Filter (File Upload)
  • Selected schema(s) (SQL Wizard, File Upload)
  • Enter the table name filter to use below (SQL Wizard, File Upload)
• Java 2-enabled browser:
  • Table Filter (SQL Wizard)
  • Table Filter (File Upload)
  • Selected schemas (SQL Wizard, File Upload)
  • Enter a table name filter (SQL Wizard, File Upload)