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
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:
|
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
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.
%
, signifying all tables.- For Z and I Emulator for Web 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 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.
IBMTEST%
,%TEST
,MYSCHEM_
- A percent sign (
-
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.
-
schema_name. table_name
Type the schema name,
followed by a period (
-
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%
-
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:
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 .
|
||||
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 ).
|
||||
%.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:
- 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)