Specifying a list of available schemas and tables

Locations where a list can be specified

Default value if a list is not specified

How to specify the elements in a list

Examples

A list is the sum of its elements

Locations where a list can be specified

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

Who can modify: Location (Z and I Emulator for Web client): Location (Database On-Demand client): Valid contents:
1. Z and I Emulator for Web administrator The Table Filter input field on the Tables tab of the Data Transfer Defaults window in the session properties window of a 3270 Display, 5250 Display, or VT Display session. The Table Filter input field on the Tables tab of the Database On-Demand Group/User Options window in the Administration Utility. A comma-separated list of schema names, schema filters, table names, and table filters
2. User ( If the option is enabled by the Z and I Emulator for Web administrator) The Table Filter input field on the Tables tab of the Data Transfer Defaults window in the session window of a 3270 Display, 5250 Display, or VT Display session. The Table Filter input field on the Tables tab of the Database On-Demand User Options window launched from the Database On-Demand desktop. A comma-separated list of schema names, schema filters, table names, and table filters
3. User ( If the option is enabled by the Z and I Emulator for Web administrator) The Table Filter input field on the Logon tab of the SQL Wizard and on the Logon tab of the File Upload Wizard. A comma-separated list of schema names, schema filters, table names, and table filters
4. User The table name filter input field:
  • Java 1 browsers: On the Table Name Filter window of the SQL Wizard and the File Upload Wizard; or
  • Java 2-enabled browsers: On the Filter Tables window of the SQL Wizard and the File Upload Wizard.
One table name or one table filter

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 Z and I Emulator for Web 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 Z and I Emulator for Web 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 the location described in the fourth row of the table above:

  • By default Z and I Emulator for Web 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.
Note Use this format only in the location described in the fourth row of the table at the top of this help file.
Note 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).
Note Use this format only in the location described in the fourth row of the table at the top of this help file.
Note 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%

means that the following schemas and tables are available:

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

Related topics:

  • Java 2-enabled browser: