Index schema
Index name
Table schema
Table name
Available columns
>>, >, <, <<
Selected columns
Include columns
Unique
Cluster
Allow reverse scans
Percentage of free space to be left on index pages
Percentage of minimum amount of used space to be left
on index pages
Comment
Ascending and Descending
Up
Down
OK
Cancel
Show SQL
Estimate Size
Index schema
Use this box to specify the schema of the index that you are creating. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:
Index name
Type a name for the index that you are creating. This name:
Attention: This name must be unique within the indexes' schema. No other object in the schema can have the same name.
Index name
Type a name for the index that you are creating. This name:
Attention: This name must be unique within the indexes created by the same owner. No other index owned by the same user id can have the same name.
Table name
Type the name of the table on which the index is created. The table must be a base table (not a view) described in the catalog. It must not be a catalog table. The table name:
Table name
Click on the down arrow to display a list of existing tables and select the table on which the index is created. The table must be a base table (not a view) described in the catalog. It must not be a catalog table. The table name:
Available columns
This list box lists all of the columns defined for the table specified in the Table name field. Use this box to select the column or columns that you want to define as part of the index key. You can select one or more columns at the same time.
You can specify up to 16 columns. At least one column must be specified to define an index.
The sum of length attributes cannot be longer than 255.
>>, >, <, <<
Use these push buttons to change the list of columns displayed in the Available columns, Selected columns, and Include columns list boxes. You can use:
Selected columns
Use this box to specify the column or columns that you want to define as part of the index key.
To add a column name to this box, select it from the Available columns list box and select the > push button.
You can specify up to 16 columns. At least one column must be specified to define an index.
The sum of the length attributes of the specified columns must not be greater than 255 bytes.
Ascending and Descending
Use these radio buttons to specify the sort order of the columns that you selected in the Selected columns list box.
Unique
Select this check box to indicate that a table will not contain two or more rows with the same value of the index key. The constraint is enforced when rows of the table are updated or new rows are inserted. The constraint is also checked during the execution of the CREATE INDEX statement. If the table already contains rows with duplicate key values, the index is not created.
When the Unique check box is selected, null values are treated as any other values. For example, if the key is a single column that can contain null values, that column can contain no more than one null value.
Percentage of free space to be left on index pages
Use this field to specify the percentage of each index page to leave as free space when building the index.
The first entry in a page is added without restriction. When additional entries are placed in an index page, the value specified in this field determines the percentage of free space left on each page.
Select a percent from 0 to 99. The default value is 10 percent. If you select a value greater than 10, only 10 percent free space will be left in non-leaf pages.
Name
Type the name of the nodegroup in the Name field. This name:
| Note: | In the Alter Nodegroup window, the name of the selected nodegroup is displayed in the Name field for information only and cannot be changed. |
Available nodes
This box displays all the available nodes that can be included in the nodegroup. Use this list to select nodes to add to the Selected nodes list.
In the Alter Nodegroup window, this box displays all the nodes in the database minus the nodes included in the named nodegroup. The list is provided for information only and cannot be changed from the Alter Nodegroup window.
Selected nodes
This box displays the nodes selected to be included in the named nodegroup.
In the Alter Nodegroup window, this box displays all the nodes in the named nodegroup. The list is provided for information only and cannot be changed from the Alter Nodegroup window.
>>, >, <, <<
Click on these push buttons to change the list of nodes displayed in the Available nodes and Selected nodes boxes:
| Note: | These buttons are only enabled from the Create Nodegroup window. They cannot be used from the Alter Nodegroup window, since only the comment of a nodegroup can be altered. |
Subscription name
Type a subscription set name for the source and target table combination.
This name:
Target server
This field displays a DB2 alias from a list of DB2 aliases cataloged on the system. The default is the alias of the source server. To select another server, either:
Apply qualifier
Type an apply qualifier name for the current subscription. This field does not contain a default value.
This name:
Source table
This field displays the source tables that you selected from the list of replication tables.
Target table
This field displays a list of target tables named to match the corresponding source tables by default. You can substitute a different name for each table by clicking on the name, then typing over it. You can use an existing name or a new name. If you choose a new name, you will need to create the table by selecting the Create table check box adjacent to the source and target table combination.
Create table
Select this check box to create the source and target table combination.
Advanced
Click on this push button to open the Advanced Subscription Definition notebook so that you can specify the target type, target columns, and target rows for any of the tables in the subscription.
SQL
Click on this push button to open the SQL window so that you can add or remove subscription SQL run-time statements.
Timing
Click on this push button to open the Subscription Timing notebook so that you can specify when and how often to replicate the subscription.
Remove
Click on this push button to remove the selected source and target table combinations from the subscription set. A Remove Confirmation window enables you to confirm your deletions.
Add
Click on this push button to open the Add window so you can add one or more source and target table combinations to the current subscription. You can also specify whether you want to create the target table if it does not currently exist.
Change
Click on this push button to open the Change window and change the target table. You can change the target table to an existing target table or create a new target table.
Start date
Use this field to specify the start date for a subscription.
Start time
Click on the hours, minutes, or seconds portion of this field, then use the spin buttons to adjust the time. Your adjustments specify a start time for a subscription.
Time-based
Select this check box to specify how often to replicate the subscription. If you select this check box, you must select one of the following radio buttons to specify the type of timing:
Select this radio button to apply changes to the target table or source at the intervals you specify. Use the following spin buttons to specify time parameters:
Select this radio button to apply changes to the target table without interruption.
Event-based
Select this check box to specify that the table be replicated whenever the event you specify in the Event name field occurs. The replication program reads the Event name in your job scheduling program to initiate the Apply process for the target table.
In order to enable event-based replication, you must also populate (using the Command Center or an application program) the subscription events table with a timestamp for the event name. When the Apply program detects the event, it begins replication. See the Replication Guide and Reference for more information about the subscription events table.
This field:
Target table
Type the name of the target table that you want to use for the selected replication source. This field does not contain a default value.
This name:
Create target table
Click on this check box to create the new target table you named in the Target table field.
Replication source
Use the Replication source list box to select the replication source you want to add to the subscription set.
Available columns
This field displays column names from the source table.
Define as source
Use this check box to specify columns for replication. This field is selected by default. Ensure all columns are selected if the table might be used for update-anywhere replication.
Capture before image
Use this check box to determine whether the before image of a selected source column will be captured, in addition to the after-image. The before image provides an image of the column prior to being updated, useful for recovery.
When selected, a column is replicated to the target table containing the values of the source table column before the values were updated. By default, the name of the before-image column begins with the prefix character, X. For example:
XNAME
This character must be unique. If any column in the target table already begins with X, a different, unique prefix character is used. If there are no unique characters, before-image columns cannot be selected.
Changed data for partitioned key columns captured as delete and
insert
Select this check box to specify that any update to primary key columns is captured as a DELETE and INSERT in the change data table rather than as an UPDATE.
Select this check box under any of the following circumstances:
The Capture program treats updates to primary key columns as INSERT and DELETE statements, rather than UPDATE statements.
Table will be used for update anywhere
Select this check box to specify that this source table can be used in update-anywhere replication. When selected, this table can have changes replicated to an updateable replica target table. The replica table can, in turn, be updated and its changes are replicated to the replication source table.
Conflict detection
Select one of the following radio buttons to determine the conflict detection level for user tables and all associated replicas:
Conflicting updates between the origin table and the replica will not be detected. This option is not recommended for update-anywhere replication.
Conflict detection does not guarantee data integrity for read dependencies. For example, an application reads the replica's updated row before it is replicated back to the source table. If the transaction for the update is later rejected as a conflict, conflict detection cannot detect this conflict.
Data capture is full-refresh only
Select this check box to specify that the source table will be used for full-refresh copying to the target table. The Capture program does not log changes for this table and update copying is not available.
Select this check box when you want all rows of the source table replicated to the target table. Use full-refresh copying when:
Do not use full refresh copying when:
Grant All
On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other groups and users).
On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.
On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.
Revoke All
Click on this push button to:
Example
Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Change Group notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:
Schema
Use this box to specify the name of a schema that includes one or more tables, indexes, or views that you want to grant privileges on. Use one of these methods:
Grant All
On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other groups and users).
On the Table and View pages, click on this push button to grant all privileges on the selected tables and views, and to provide the Grant option with all privileges except CONTROL.
On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.
Revoke All
Click on this push button to cancel all requests for privileges on the selected schemas, tables, indexes, or views.
Example
Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Add Group notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:
Schema
Use this box to specify the name of a schema that includes one or more tables, indexes, or views that you want to grant privileges on. Use one of these methods:
Table schema
Use this box to specify the schema of the table that you're creating. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:
Table name
Type a name for the table that you're creating. This name:
Attention: This name must be unique within the table's schema. No other object in the schema can have the same name as the table.
Table space
Optional: Use this box to specify the table space in which to create the table. To select a table space:
If you do not specify a table space, either:
You must specify a REGULAR DMS table space other than the default table space if you plan to specify a specific index table space or long data table space.
Index table space
Optional: Use this box to specify a table space in which to create any indexes on the table. Indexes can be stored in a different table space than the table itself. You might want to specify an index table space to improve performance for very large tables.
To select a table space in which to create indexes:
This field is available only if you specified a table space other than the default in the Table space box. If you do not specify an index table space, the indexes will be created in the same table space as the table.
Long data table space
Optional: Use this box to specify the table space in which to store the values of any long columns. Long data can be LONG VARCHAR, LONG VARGRAPHIC, LOB data types, or distinct types with any of these as source types.
To select a table space for values of long columns:
This field is available only if you specified a table space other than the default in the Table space box. If you do not specify a long data table space, the long data will be created in the same table space as the table.
Columns
This box contains a list of all of the columns defined for the table that you're creating. Use this box to view your column definitions or to select a column that you want to change, remove, or reorder. You can select a new column to change any field, remove the new column from the table, or reorder the new column among the other new columns. Click on Add to add a column to this list.
Add
Click on this push button to open the Add Column window so you can add a new column to the table that you're creating.
You can add up to 500 columns to your table. The sum of the byte counts of the columns must not be greater than 4005.
Change
Click on this push button to open the Change Column window so that you can change an attribute of the column that is selected in the Columns box.
Remove
Click on this push button to remove from the box the column that is selected in the Columns box.
Up
Click on this push button to move the column that is selected in the Columns box up one position.
Down
Click on this push button to move the column that is selected in the Columns box down one position.
Column name
Type the name for the column that you're adding in this field. This name:
Attention: You cannot use the same name for more than one column of the table.
Use this box to specify the data type of the column that you're adding. To specify the data type, click on the down arrow to display a list of data types and select one.
The data type can be one of:
Use this field to specify the length (byte count) of the columns based on the specified data type:
LOB unit
Use this box to specify a multiplier for the length of columns of data type BLOB, CLOB, or DBCLOB. If you specify a LOB unit of:
This box is available only if you specified BLOB, CLOB, or DBCLOB in the Data type box.
Precision
Use this field to specify the precision of a DECIMAL number. The precision is the total number of digits, which can range from 1 to 31. If you do not specify the precision, a default value of 5 is used.
This field is available only if you specified DECIMAL in theData type box.
Scale
Use this field to specify the scale of a DECIMAL number. The scale is the number of digits to the right of the decimal point, which can range from 0 to the precision of the number. If you do not specify the scale, a default value of 0 is used.
This field is available only if you specified DECIMAL in theData type box.
LOB option
Select the following check boxes to define your LOB options:
| Note: | LOBs greater than 1 GB cannot be logged, and LOBs greater than 10 MB should not be logged. |
| Note: | Storing data in this way can cause a performance penalty in any append (length-increasing) operations on the column. |
These check boxes are available only if you specified a data type of BLOB, CLOB, DBCLOB, or a distinct type with any of these as source types in the Data type box.
Bit data
Optional: Select this check box if you want the contents of the column to be treated as bit (binary) data. During data exchange with other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database collating sequence.
This check box is available only if you specified a data type of CHARACTER, VARCHAR, or LONG VARCHAR in the Data type box.
Optional: Use this box to specify a default value for the
column. Type the constant value, CURRENT DATE, CURRENT TIME, CURRENT
TIMESTAMP, USER, or NULL in this field. The default value that you type
will be used exactly as entered. For example, if you put double quotes
around the value you type in, the double quotes will be part of the default
value. If you do not specify a specific default value, the default
value depends on the data type of the column as shown in the following
table.
Table 1. Default Values (when no value specified)
| Data Type | Default Value |
|---|---|
| Numeric | 0 |
| Fixed-length character string | Blanks |
| Varying-length character string | A string of length 0 |
| Fixed-length graphic string | Double-byte blanks |
| Varying-length graphic string | A string of length 0 |
| Date | For existing rows, a date corresponding to January 1, 0001. For added rows, the current date. |
| Time | For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time. |
| Timestamp | For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds. For added rows, the current timestamp. |
| Binary string (BLOB) | A string of length 0 |
You can specify the following types of default values:
Nullable
Select this check box to allow the column to contain null values. The default value for the column is either the null value or, if the Default check box is checked, the value specified in the Default field.
Attention: If the column will be a primary key column, it cannot be nullable.
Comment
Optional: Type a comment to document the column that you're adding. You can type up to 254 characters, including embedded blanks.
Add
Click on this push button to add the new column that you just defined. You can add multiple columns before closing the Add Column window.
You can add up to 500 columns to your table. The sum of the byte counts of the columns must not be greater than 4005.
Close
Click on this push button to close the Add Column window and return to the Columns page of the Create Table notebook. Any changes you made that you didn't add are not saved. You can add multiple columns before clicking on the Close push button.
Column name
This field displays the name currently specified for the column. To specify a different name, type the new name for the column in this field. This name:
Attention: You cannot use the same name for more than one column of the table.
Data type
This box contains the data type currently specified for the column. To specify a different data type, click on the down arrow to display a list of data types and select one.
The data type can be one of:
Length
This field displays the length (byte count) currently specified for the column. To specify a different length, type a value in this field based on the specified data type:
This field is available only if you specified CHARACTER, VARCHAR BLOB, CLOB, DBCLOB, GRAPHIC, or VARGRAPHIC in the Data type box.
LOB unit
This box contains the LOB unit currently specified for the column. To specify a different multiplier for the length of columns of data type BLOB, CLOB, or DBCLOB, select one of the following LOB units:
This box is available only if you specified BLOB, CLOB, or DBCLOB in the Data type box.
Precision
This field displays the precision currently specified for the column of data type DECIMAL. To change the precision, type a new value in this field. The precision is the total number of digits in a DECIMAL number, which can range from 1 to 31. If you do not specify the precision, a default value of 5 is used.
This field is available only if you specified DECIMAL in theData type box.
Scale
This field displays the scale currently specified for the column of data type DECIMAL. To change the scale, type a new value in this field. The scale is the number of digits to the right of the decimal point, which can range from 0 to the precision of the number. If you do not specify the scale, a default value of 0 is used.
This field is available only if you specified DECIMAL in theData type box.
LOB option
These check boxes display the LOB options currently selected for the column of LOB data types. To change the LOB options, select or clear the following check boxes:
| Note: | LOBs greater than 1 GB cannot be logged, and LOBs greater than 10 MB should not be logged. |
| Note: | Storing data in this way can cause a performance penalty in any append (length-increasing) operations on the column. |
These check boxes are available only if you specified a data type of BLOB, CLOB, DBCLOB, or a distinct type with any of these as source types in the Data type box.
Bit data
This check box is displayed as currently specified for the column. You can select or clear this check box to change the setting. If you select this check box, the contents of the column will be treated as bit (binary) data. During data exchange with other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database collating sequence.
This check box is available only if you specified a data type of CHARACTER, VARCHAR, or LONG VARCHAR in the Data type box.
Default
This field displays the default value currently specified for the
column. To change the default value, type the constant value, CURRENT
DATE, CURRENT TIME, CURRENT TIMESTAMP, USER, or NULL in this field. The
default value that you type will be used exactly as entered. For
example, if you put double quotes around the value you type in, the double
quotes will be part of the default value. If you do not specify a
specific default value, the default value depends on the data type of the
column as shown in the following table.
Table 2. Default Values (when no value specified)
| Data Type | Default Value |
|---|---|
| Numeric | 0 |
| Fixed-length character string | Blanks |
| Varying-length character string | A string of length 0 |
| Fixed-length graphic string | Double-byte blanks |
| Varying-length graphic string | A string of length 0 |
| Date | For existing rows, a date corresponding to January 1, 0001. For added rows, the current date. |
| Time | For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time. |
| Timestamp | For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds. For added rows, the current timestamp. |
| Binary string (BLOB) | A string of length 0 |
You can specify the following types of default values:
Nullable
This check box is displayed as currently specified for the column. You can select or clear this check box to change the setting. If you select this check box, the column can contain null values. The default value for the column is either the null value or the value specified in the Default field.
Attention: If the column will be a primary key column, it cannot be nullable.
Comment
Optional: This field contains the comment currently specified for the column. To change the comment, type the new comment in this field. You can type up to 254 characters, including embedded blanks.
Available columns
This box lists all of the columns defined for the table that are not currently defined as primary key columns. Use this box to select the column or columns that you want to define as primary key columns and then click on the > push button. You can select one or more columns at the same time.
You can define up to 16 columns to be primary key columns. The sum of their length attributes must not exceed 255 bytes.
Attention: You cannot define columns of data type LOB, LONG VARCHAR, or LONG VARGRAPHIC to be primary key columns.
Primary key columns
This box lists all of the columns defined as primary key columns. The order of the columns in this list is significant because the database manager uses the primary key for efficient access to table data.
Use this box to view the list of primary key columns or to select a column or columns to remove from the list with the < push button.
To add a column to this list, select a column from the Available columns box and then click on the > push button.
>, >>, <, <<
Use these push buttons to change the list of columns displayed in the Available columns and Primary key columns boxes. You can use:
Available columns
This box lists all of the columns defined for the table that are not currently defined as partitioning key columns. Use this box to select the columns that you want to define as partitioning key columns and then click on the > push button.
Attention: You cannot define columns of data type LOB, LONG VARCHAR, or LONG VARGRAPHIC to be partitioning key columns.
Partitioning key columns
This box lists all of the columns defined as partitioning key columns. The order of the columns in this list is significant because the database manager uses the partitioning key for efficient access to table data.
Use this box to view the list of partitioning key columns or to select columns that you want to remove from the list with the < push button.
To add a column to this list, select a column from the Available columns box and then click on the > push button.
>, >>, <, <<
Use these push buttons to change the list of columns displayed in the Available columns and Partitioning key columns boxes. You can use:
Column categories
This box lists all of the categories of predefined columns. Use this box to select a column category from which you want to choose a predefined column to add to your table.
Predefined columns
This box lists all of the predefined columns in the category that you selected in the Column categories box. Use this box to select the predefined column or columns that you want to add to your table. You can select one or more predefined columns at the same time.
Columns to create
This box lists all of the predefined columns to add to your table. To add a predefined column to this box, select it in the Available columns box and click on the > push button.
Column name
Optional: This field contains the name of the column selected in the Columns to create box. To change the column name, type a new name or edit the existing name. This name:
Attention: You cannot use the same name for more than one column of the table.
>, >>, <, <<
Use these push buttons to change the list of columns displayed in the Columns to create box. You can use:
Change
Click on this push button to open the Change Column Categories window so that you can add a new column category; change a column category name; delete a column category; and add, change, or remove a column from a column category.
Category name
This field contains the name currently specified for the column category that you're editing. To specify a different name, type a new name in the field.
Columns
This box lists all of the columns defined for the column list that you selected in the Category name box. Use this box to view the column definitions or to select a column that you want to change, remove, or reorder. Click on Add to add a column to this list.
Add (Category name)
Click on this push button to add a new list of columns with the name specified in the List name field.
Change (Category name)
Click on this push button to change the name of the selected column list to the name specified in the List name field.
Remove (Category name)
Click on this push button to remove the selected list of columns. The list name and all columns in the list are removed from theColumn lists box.
Add (Column)
Click on this push button to open the Add Column window so that you can add a new column to the column list that you selected in the List name box.
Change (Column)
Click on this push button to open the Change Column window so that you can change an attribute of the column that is selected in the Columns box.
Remove (Column)
Click on this push button to remove from the box the column that is selected in the Columns box.
Up
Click on this push button to move the column that is selected in the Columns box up one position.
Down
Click on this push button to move the column that is selected in the Columns box down one position.
Message file
Type the name of the file that will contain warning and error messages that occur during export. If you do not type the full path, the current directory and default drive are used. If the file already exists, the information is appended. If you do not specify a message file, an error is returned.
Add
Click on this push button to add objects, such as files and columns, to the list box. The Add window opens.
Path to large object (LOB)
Type the path to the large object file into this field.
The LOB path specifies the path or paths to store the LOB files. When file space is exhausted on the first path, the second path is used, and so on.
When LOB files are created during an export, they are constructed by appending the current base name (specified in the File name of large object (LOB) field) to the current path. A 3-digit sequence number is then appended to the file. For example, if the current LOB path is the directory /U/FOO/LOB/PATH, and the current LOB file name is BAR, then the LOB files created will be /U/FOO/LOB/PATH/BAR.001, /U/FOO/LOB/PATH/BAR.002, and so on.
Add
Click on this push button to add the item you entered in this window to the list box on the previous notebook page.
After you click on the Add push button, the Add window remains open so you can add more items.
Close
Click on this push button to close the window and return to the notebook.
File name of large object (LOB)
Type a base file name into this field.
The file name specifies the base file name of the LOB files. When name space is exhausted for the first name, the second name is used, and so on.
When LOB files are created during an export, they are constructed by appending the current base name (specified in the File name of large object (LOB) field) to the current path. A 3-digit sequence number is then appended to the file. For example, if the current LOB path is the directory /U/FOO/LOB/PATH, and the current LOB file name is BAR, then the LOB files created will be /U/FOO/LOB/PATH/BAR.001, /U/FOO/LOB/PATH/BAR.002, and so on.
Column name
Type a new column name into this field. If you do not specify column names, the names in the existing table are used. This name:
Attention: You cannot use the same name for more than one column of the table.
Revoke All
Click on this push button to revoke all privileges that the selected users or groups hold on the selected table.
When you click on Revoke All:
Grant All
Click on this push button to grant to the selected users or groups whatever privileges they don't hold on the selected table. Grant All also provides the Grant option -- the right to grant a privilege to other users and groups -- on all privileges except CONTROL.
When you click on Grant All:
This box indicates whether the selected users or groups hold the SELECT privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The SELECT privilege allows a user to retrieve rows from a table, to use the EXPORT utility to do so, and to create views on the table.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the SELECT box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the INSERT privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The INSERT privilege allows a user to insert rows into a table, and to use the IMPORT utility to do so.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the INSERT box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the UPDATE privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The UPDATE privilege allows a user to update a table.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the UPDATE box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the DELETE privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The DELETE privilege allows a user to delete rows from a table.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the DELETE box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the CONTROL privilege on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
With the CONTROL privilege on a table, a user:
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the CONTROL box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the ALTER privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The ALTER privilege on a table allows a user to:
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the ALTER box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the INDEX privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The INDEX privilege allows a user to create an index on a table.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the INDEX box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the REFERENCES privilege, with the Grant option, on the selected table. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The REFERENCES privilege allows a user to create and drop a foreign key.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the REFERENCES box is blank. But the settings that you specified remain in effect.
Do not recognize character x'1A' as the end-of-file
character (NOEOFCHAR)
OS/2 only: Select this check box if you want to specify that the optional end-of-file character x'1A' is not to be recognized as the end of the file. It will be seen as a normal character.
Delimiters
These boxes contain the type of delimiters you can use for your data. To change the type, use one of these methods:
The delimiters you can change are:
Change
Click on this push button to change the object selected in the list box. The Change window opens.
Add
Click on this push button to add column names into the Column names box. The Add window opens.
Column name
Type a name for the column that you are creating. This name:
Attention: You cannot use the same name for more than one column of the table.
Retrieve large objects (LOBs) from the specified paths
(LOBSINFILE)
Select this check box if you want to load large object (LOB) data from the data files specified in the LOB paths box.
LOB paths
This box lists the path names to the data files that contain the large object (LOB) data to be loaded. Use the Add push button to add a path name to this box. You can also select a path name to be changed, removed, or rearranged.
The names of the LOB data files are stored in the data file column that will be loaded into the table's LOB column.
This box is available only when the Retrieve large objects (LOBs) from the specified paths (LOBSINFILE) check box is selected.
Add
Click on this push button to open the Add window so you can add the name of a LOB path to the LOB paths box.
The new entry is added to the list following the entry that is currently selected. If no entry is selected, the new entry is appended to the list.
Change
Click on this push button to open the Change window so you can change the name of the selected LOB path in the LOB paths box.
This push button is available only when a name is selected in the LOB paths box.
Remove
Click on this push button to remove the selected LOB path names from the LOB paths box. (The actual LOB files are not removed from the system.)
This push button is available only when one or more names are selected in the LOB paths box.
Up
Click on this push button to move up one position the name of the selected LOB path in the LOB paths box.
This push button is available only when a name is selected in the LOB paths box.
Down
Click on this push button to move down one position the name of the selected LOB path in the LOB paths box.
This push button is available only when a name is selected in the LOB paths box.
Do not recognize character x'1A' as the end-of-file
character (NOEOFCHAR)
Select this check box if you want to specify that the optional end-of-file character
x'1A'
is not to be recognized as the end of the file. It will be seen as a normal character.
Suppress warnings about rejected rows (NOROWWARNINGS)
Select this check box to stop DB2 from issuing warnings when rows are rejected during the load process.
Insert an implied decimal point on decimal data
(IMPLIEDDECIMAL)
Select this check box to specify that the location of an implied decimal point is determined by the column definition, and is not at the end of the value. For example, the value 12345 is loaded into a DECIMAL(0,2) column as 123.45 and not 12345.00.
Use default values on defaultable columns when missing input data
(USEDEFAULTS)
Select this check box to specify that if an input value is missing for a particular table column and that table column has a default defined on it, the default value will be inserted in place of the missing value.
Perform reduced sanity checking on user supplied column values
(FASTPARSE)
Select this check box to enhance performance of the load utility by reducing the amount of data checking that is done on user-supplied column values. Even with the reduced amount of data checking, the loaded table is guaranteed to be architecturally correct and the load utility is guaranteed to perform sufficient data checking to prevent a segmentation violation or trap. Data that is in a correct form will be loaded correctly.
Percentage of free space in each data page (PAGEFREESPACE)
This field contains the percentage of each data space that is to be left as free space. To change this percentage, use one of these methods:
For example, if you specify 50, then 50% of each data page is left as free space. The default value of 0 specifies that no space in each data page will be specifically left as free space.
If you specify an invalid value because of the minimum row size (for example, a row that is at least 3000 bytes long and a page free space of 50), the row will be placed on a new page. If you specify 100, each row will reside on a new page.
Percentage of total free space reserved in the table
(TOTALFREESPACE)
This field contains the percentage of the total pages in the table that is to be appended to the end of the table as free space. To change this percentage, use one of these methods:
For example, if you select 20 and the table has 50 pages, then 10 additional empty pages will be appended. The total number of data pages for the table will be 60.
The default of 0 specifies that free space will not be appended to the end of the table.
Include column in table load
Select this check box if you want to load data into the table column displayed in the Table column field of the Change window.
Grant all
Click on this push button to grant privileges to all users or groups selected. A check mark indicates that a privilege was granted. A double check mark indicates that users were granted that privilege with the ability to grant the same privilege to others.
The Grant all push button opens the Select Grant Option window. On the Select Grant Option window, select a radio button to indicate whether or not you want to give granting privileges to the selected users. Click on the With Grant option radio button (default) or the No Grant option radio button.
Revoke all
Click on this push button to revoke privileges for all users or groups selected. Revoked privileges are indicated by an X.
Use these boxes to grant or revoke the schema privileges for selected users or groups. Click on the down arrow of the CREATEIN, DROPIN, or ALTERIN box and select Yes to grant or No to revoke the privilege. Select Grant to grant the privilege with the ability to grant.
The icons in the columns will show a check mark to indicate that a user has the privilege, a double check mark to indicate that the user has the privilege with the ability to grant, or an X to indicate that the user does not have the privilege.
The privileges for schemas are:
Grant All
On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other users and to groups).
On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.
On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.
Revoke All
Click on this push button to:
Example
Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Change User notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:
Symbols in privileges columns
On the Schema, Table, Index, and View pages, symbols in the privileges columns indicate whether the user already has privileges on the objects that you selected, and whether grants and revocations of privileges are pending or have been made. The symbols are:
Example
SCHEMA1 is listed in the Schema column on the Schema page. You want to grant the CREATEIN privilege on SCHEMA1; so you select SCHEMA1 and specify Yes in the CREATEIN box. Your grant of the CREATEIN privilege is now pending. As a sign of this, a check mark framed by corner brackets appears in the CREATEIN column next to SCHEMA1.
Then you click on Apply, and the privilege, but not the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the check mark unframed.
Example
TABLE2 is listed in the Table column on the Table page. You want to grant:
Accordingly, you select TABLE2 and specify Grant in the INSERT box. Your grant of the INSERT privilege and the Grant option is now pending. As a sign of this, a double check mark framed by corner brackets appears across from TABLE2 in the INSERT column.
Then you click on Apply, and the privilege, with the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the double check mark unframed.
Example 1
INDEX3 is listed in the Index column on the Index page. The selected user has the CONTROL privilege on INDEX3, but no longer needs it. So you decide to revoke it. Accordingly, you select INDEX3 and specify No in the CONTROL box. The revocation that you want is now pending. As a sign of this, an X framed by corner brackets appears across from INDEX3 in the CONTROL column.
Then you click on Apply, and the privilege is revoked. As a sign of this, the corner brackets disappear, leaving the X unframed.
Example 2
You want to grant the DELETE privilege on VIEW4. So, on the View page, you select VIEW4 in the View column and specify Yes in the DELETE box. Then you determine that it would be better to grant the DELETE privilege to a group rather than to individual users. So you override the Yes, by selecting VIEW4 again and specifying No in the DELETE box. As a sign that this override is pending, an X framed by corner brackets appears in the DELETE column across from VIEW4.
Example 3
On the View page, you click on the Add View push button. Then, on the Add View window, you select the schema that contains VIEW5, select VIEW5, and click on Add and Close. Consequently, VIEW5 appears in the View column; and unframed Xs appear across from it in the view privileges columns. These unframed Xs mean that the selected user doesn't have any privileges on VIEW5.
Schema
Use this box to specify a schema that includes one or more tables, indexes, or views that you want to grant or revoke privileges on. Use one of these methods:
Grant All
On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other users and to groups).
On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.
On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.
Revoke All
Click on this push button to cancel all requests for privileges on the selected schemas, tables, indexes, or views.
Example
Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Add User notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:
Schema
Use this box to specify the name of a schema that includes one or more tables, indexes, or views that you want to grant privileges on. Use one of these methods:
Revoke All
Click on this push button to revoke all the privileges that the selected users or groups hold on the selected view.
When you click on Revoke All:
Grant All
Click on this push button to grant to the selected users or groups whatever privileges they don't hold on the selected view. Grant All also provides the Grant option -- the right to grant a privilege to other users and groups -- on all privileges except CONTROL.
When you click on Grant All:
SELECT
This box indicates whether the selected users or groups hold the SELECT privilege, with the Grant option, on the selected view. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The SELECT privilege allows a user to retrieve rows from a view and to create views on a view.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the SELECT box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the INSERT privilege, with the Grant option, on the selected view. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The INSERT privilege allows a user to insert rows into an updatable view.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the INSERT box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the UPDATE privilege, with the Grant option, on the selected view. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The UPDATE privilege allows a user to update rows in an updatable view.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the UPDATE box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the DELETE privilege, with the Grant option, on the selected view. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
The DELETE privilege allows a user to delete rows from an updatable view.
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the DELETE box is blank. But the settings that you specified remain in effect.
This box indicates whether the selected users or groups hold the CONTROL privilege on the selected view. To change the box's setting, click on the down arrow and select the setting that you want. You can select:
With the CONTROL privilege on a view, a user:
If you select multiple users or groups for whom you specified different settings (for example, Yes for one user andNo for the others), the CONTROL box is blank. But the settings that you specified remain in effect.
Performance variable
The name of the performance variable.
Description
The description of the performance variable.
Level
The level of the information collected by the performance variable. There are five levels:
Instance
Database
Table
Table space
Connection
Category
The category of the information collected by the performance variable. Each performance variable will return data associated with one of the following groups:
Agents
Buffer pool and I/O
Connections
Lock and Deadlock
Sort
SQL statement activity
Table
Unit of Work
Date and Time
The value of the performance variable at the date and time specified in the column heading. If there is no data, then the selected performance monitor was not collecting information for this performance variable at that time.
Graph
A check mark in this column indicates that values for the performance variable will be graphed.
Graph Settings
Color indicates the color of the line for the performance variable in the graph.
Upper Alarm
The highest value the monitor will return for a performance variable before issuing the upper alarm actions set for that performance variable. If there is no data, then no alarm has been set. Alarms can be enabled or disabled.
Upper Warning
A value (less than the upper alarm value) used to warn you that a performance variable is approaching its upper alarm value. When the upper warning value is reached, the upper warning actions set for the performance variable are issued. If there is no data, then no warning has been set. Warnings can be enabled or disabled.
Lower Alarm
The lowest value the monitor will return for a performance variable before issuing the lower alarm actions set for that performance variable. If there is no data, then no alarm has been set. Alarms can be enabled or disabled.
Lower Warning
A value (greater than the lower alarm value) used to warn you that a performance variable is approaching its lower alarm value. When the lower warning value is reached, the lower warning actions set for the performance variable are issued. If there is no data, then no warning has been set. Warnings can be enabled or disabled.
Application Handle (agent ID)
A system-wide unique ID for the application. On multi-node systems, where a database is partitioned, this ID will be the same on every node where the application may make a secondary connection.
Authorization ID
The authorization ID of the user who invoked the application. On a gateway node this is the user's authorization ID on the host.
Application Name
The name of the application running on the client that identifies it to the database manager or DB2 Connect.
Database Name
The real name of the database to which the application is connected. This is the name that the database was given when it was created.
Force
Click on this push button to forcibly disconnect one or more applications from a database.
Force All
Click on this push button to forcibly disconnect all applications from all databases for an instance.
Refresh
Click on this push button to update the information being displayed.
Add
Click on the Add push button to add performance variables to the performance monitor being displayed. The Add window opens.
Remove
Click on the Remove push button to remove selected performance variables from the performance monitor being displayed.
Graph
Click on the Graph push button to add or remove selected performance variables on the performance monitor's Details graph.
Monitor name
The original name of the monitor appears in this field.
New description
Enter a new description of the monitor in this field. The description can be up to 255 characters in length.
Monitor name
This column contains the name of the monitor.
Description
This column contains the description of the monitor.
Status
This column shows the status of the monitor (either started or stopped).
Default for level
If a monitor is the default monitor for a level or levels, that level will appear in the Default for level column. A monitor can be the default monitor for more than one level. If no level (either instance, database, table, table space, or connections) appears in the Default for level column, then that monitor is not the default monitor for any level.
Created by
This column indicates who created the monitor. In the case of IBM supplied predefined monitors, NULLID appears in this column.
Dynamic explain
States whether the explained SQL statement was dynamic. (If it was not, it was a static SQL statement in a package.)
Explain date
The date when the statement had an explain operation performed on it.
Explain snapshot
States whether an explain snapshot has been taken for the SQL statement. (If it has not, you cannot view an access plan graph for the statement.)
Explain snapshot
This box contains the selection criteria for the Explain snapshot column in Explainable Statements and Explained Statements History windows.
To change the value, use one of these methods:
Explain time
The time when the statement had an explain operation performed on it.
Latest bind
If the statement is contained in a package, this field indicates whether or not the statement is associated with the latest bound package.
Query number
The query number that is associated with the statement.
Query tag
The query tag that is associated with the statement.
Remarks
Any remarks associated with the statement. (For example, for a static SQL statement, the remarks associated with the package containing the statement.)
Section number
The number of the section within the package that is associated with the SQL statement.
SQL text
Type a string that will be used to filter on the SQL text field.
Statement number
The line number of the SQL statement in the source module of the application program.
Total cost
The estimated total cost (in timerons) of the statement.
Graph
Click on this tab to open the Access Plan Graph notebook.
Node
Click on this tab to display the Node notebook page.
Operator
Click on this tab to display the Operator notebook page.
Basic
Click on this tab to display the Operator Basic notebook page.
Extended
Click on this tab to display the Operator Extended notebook page.
Update
Click on this tab to display the Operator Update notebook page.
Miscellaneous
Click on this tab to display the Operator Miscellaneous notebook page.
Operand
Click on this tab to display the Operand notebook page.
Operator name: CMPEXP
Represents: The computation of expressions required for intermediate or final results.
(This operator is for debug mode only.)
Operator name: DELETE
Represents: The deletion of rows from a table.
This operator represents a necessary operation. To improve access plan costs, concentrate on other operators (such as scans and joins) that define the set of rows to be deleted.
Performance Suggestion:
Operator name: EISCAN
Represents: This operator scans a user defined index to produce a reduced stream of rows. The scanning uses the multiple start/stop conditions from the user supplied range producer function.
This operation is performed to narrow down the set of qualifying rows before accessing the base table (based on predicates).
Performance Suggestion:
Operator name: FETCH
Represents: The fetching of columns from a table using a specific row identifier (RID).
Performance suggestions:
The quantile and frequent value statistics provide information on the selectivity of predicates, which determines when index scans are chosen over table scans. To update these statistics, use the runstats command on a table with the WITH DISTRIBUTION clause.
Operator name: FILTER
Represents: The application of residual predicates so that data is filtered based on the criteria supplied by the predicates.
Performance suggestions:
Operator name: GENROW
Represents: A built-in function that generates a table of rows, using no input from tables, indexes, or operators.
GENROW may be used by the optimizer to generate rows of data (for example, for an INSERT statement or for some IN-lists that are transformed into joins).
To view the estimated statistics for the tables generated by the GENROW function, double-click on its node.
Operator name: GRPBY
Represents: The grouping of rows according to common values of designated columns or functions. This operation is required to produce a group of values, or to evaluate set functions.
If no GROUP BY columns are specified, the GRPBY operator may still be used if there are aggregation functions in the SELECT list, indicating that the entire table is treated as a single group when doing that aggregation.
Performance suggestions:
Operator name: HSJOIN
Represents: A hash join for which the qualified rows from tables are hashed to allow direct joining, without pre-ordering the content of the tables.
A join is necessary whenever there is more than one table referenced in a FROM clause. A hash join is possible whenever there is a join predicate that equates columns from two different tables. The join predicates need to be exactly the same data type. Hash joins may also arise from a rewritten subquery, as is the case with NLJOIN.
A hash join does not require the input tables be ordered. The join is performed by scanning the inner table of the hash join and generating a lookup table by hashing the join column values. It then reads the outer table, hashing the join column values, and checking in the lookup table generated for the inner table.
For more information, see the section on join concepts in the Administration Guide.
Performance suggestions:
Operator name: INSERT
Represents: The insertion of rows into a table.
This operator represents a necessary operation. To improve access plan costs, concentrate on other operators (such as scans and joins) that define the set of rows to be inserted.
Operator name: IXAND
Represents: The ANDing of the results of multiple index scans using Dynamic Bitmap techniques. The operator allows ANDed predicates to be applied to multiple indexes, in order to reduce underlying table accesses to a minimum.
This operator is performed to:
Performance suggestions:
Operator name: IXSCAN
Represents: The scanning of an index to produce a reduced stream of rows. The scanning can use optional start/stop conditions, or may apply to indexable predicates that reference columns of the index.
This operation is performed to narrow down the set of qualifying rows before accessing the base table (based on predicates).
For more information, see the section on index scans in the Administration Guide.
Performance suggestions:
For more guidelines about indexes, see Creating appropriate indexes.
Operator name: MSJOIN
Represents: A merge join for which the qualified rows from both outer and inner tables must be in join-predicate order. A merge join is also called a merge scan join or a sorted merge join.
A join is necessary whenever there is more than one table referenced in a FROM clause. A merge join is possible whenever there is a join predicate that equates columns from two different tables. It may also arise from a rewritten subquery.
A merge join requires ordered input on joining columns, since the tables are typically scanned only once. This ordered input is obtained by accessing an index or a sorted table.
For more information, see the section on join concepts in the Administration Guide.
Performance suggestions:
For guidelines about indexes, see Creating appropriate indexes.
Operator name: NLJOIN
Represents: A nested loop join that scans (usually with an index scan) the inner table once for each row of the outer table.
A join is necessary whenever there is more than one table referenced in a FROM clause. A nested loop join does not require a join predicate, but generally performs better with one.
A nested loop join is performed either:
For more information, see the section on join concepts in the Administration Guide.
Performance suggestions:
Another (less important) way to make the join more efficient is to create an index on the join columns of the outer table so that the outer table is ordered.
For more guidelines about indexes, see Creating appropriate indexes.
Related information:
Operator name: PIPE
Represents: The transfer of rows to other operators without any change to the rows.
(This operator is for debug mode only.)
Operator name: RETURN
Represents: The return of data from a query to the user. This is the final operator in the access plan graph and shows the total accumulated values and costs for the access plan.
This operator represents a necessary operation.
Performance Suggestion:
Operator name: RIDSCN
Represents: The scan of a list of row identifiers (RIDs) obtained from one or more indexes.
This operator is considered by the optimizer when:
Operator name: RQUERY
Represents: An operator used in the federated system to retrieve data from a remote data source. This operator is considered by the optimizer when: An RQUERY operator sends a SQL SELECT statement to a remote data source to retrieve the query result. The SELECT statement is generated using the SQL dialect supported by the data source, and can contain any valid query as allowed by the data source.
Performance Suggestion: Refer to Chapter 4 in the Administration Guide Vol 2, Federated Database Query and Network Tuning Information.
Operator name: SORT
Represents: The sorting of the rows in a table into the order of one or more of its columns, optionally eliminating duplicate entries.
Sorting is required when no index exists that satisfies the requested ordering, or when sorting would be less expensive than an index scan. Sorting is usually performed as a final operation once the required rows are fetched, or to sort data prior to a join or a group by.
If the number of rows is high or if the sorted data cannot be piped, the operation requires the costly generation of temporary tables.
For more information on sorts, see the Administration Guide.
Performance suggestions:
For guidelines about indexes, see Creating appropriate indexes.
Operator name: TBSCAN
Represents: A table scan (relation scan) that retrieves rows by reading all the required data directly from the data pages.
This type of scan is chosen by the optimizer over an index scan when:
For more information on table and index scans, see the Administration Guide.
Performance suggestions:
For more guidelines about indexes, see Creating appropriate indexes.
For more information, see the section on prefetching data into the buffer pool in the Administration Guide.
The quantile and frequent value statistics provide information on the selectivity of predicates. For example, these statistics would be used to determine when index scans are chosen over table scans. To update these values, use the runstats command on a table with the WITH DISTRIBUTION clause.
Operator name: TEMP
Represents: The action of storing data in a temporary table, to be read back out by another operator (possibly multiple times). The table is removed after the SQL statement is processed, if not before.
This operator is required to evaluate subqueries or to store intermediate results. In some situations (such as when the statement can be updated), it may be mandatory.
Operator name: TQUEUE
Represents: A table queue that is used to pass table data from one database agent to another when there are multiple database agents processing a query. Multiple database agents are used to process a query when parallelism is involved.
Table queue types are:
Operator name: UNION
Represents: The concatenation of streams of rows from multiple tables.
This operator represents a necessary operation. To improve access plan costs, concentrate on other operators (such as scans and joins) that define the set of rows to be concatenated.
Operator name: UNIQUE
Represents: The elimination of rows having duplicate values for specified columns.
Performance Suggestion:
For guidelines about indexes, see Creating appropriate indexes.
Operator name: UPDATE
Represents: The updating of data in the rows of a table.
This operator represents a necessary operation. To improve access plan costs, concentrate on other operators (such as scans and joins) that define the set of rows to be updated.
A database name must be specified when a database is created. All databases managed by the same database manager instance must have unique alias names.
DB2 database names are displayed as fields in the system database directory, the local database directory, and the database connection services directory.
A database name consists of 1 - 8 characters from the following list:
Also, a database name:
| Note: | To avoid potential problems, do not use the special characters @, #, and $ in a database name if you intend to use the database in a communications environment. Also, because the characters @, #, and $ are not common to all keyboards, do not use them if you plan to use the database in another country. |
Prefetching reads the data needed by a query before it is referenced by the query, so that the query does not have to wait for I/O to be performed.
A schema is a collection of named objects. It provides a logical classification of objects in the database and may contain objects such as aliases, tables, views, indexes, triggers, distinct types, functions, and packages.
You can explicitly create a schema using the Create Schema window. A schema can be implicitly created when an object (such as an alias, table, view, index, trigger, or package) is created. The schema exists in the database as an object.
When creating an object, you can assign it to a schema by specifying the schema name. The schema name is used as the first part of the two-part object name. If a schema name is not specified, the authorization name of the creator of the object is used as the default.
If the keyword 1ST_CONN is defined for the tm_database parameter, the first database to which the application connects in the transaction will be used as the transaction manager database.
Care must be taken when using 1ST_CONN.
You should only use this configuration if it is easy to ensure that all databases involved are cataloged correctly. For example, use it in the following situations:
Before a client application can access a remote database, the database must be cataloged on the server node and on any client nodes that will connect to it.
When you create a database, it is automatically cataloged on the server with the Database alias (Database_alias) and adopts the Database name (Database_name) in the process.
The information in the database directory, along with the information in the node directory, is used on the client to establish a connection to the remote database.
Ordinary and delimited identifiers (name)
There are two types of SQL identifiers: ordinary identifiers and delimited identifiers.
An ordinary identifier:
If you type a lowercase letter as part of an ordinary identifier in a field, the lowercase letter is stored as an uppercase letter.
Attention: Validation of identifiers occurs when you submit an action for processing (for example, when you click OK in the Create Table notebook). At that time, you will receive an error message if the identifier you specified is invalid.
A delimited identifier:
Leading spaces are stored as part of a delimited identifier, but trailing spaces are ignored.
Attention: Validation of identifiers occurs when you submit an action for processing (for example, when you click on OK on the Create Table notebook). At that time, you will receive an error message if the identifier you specified is invalid.
Examples
To name a table WKLYSAL, you can type: WKLYSAL or wklysal or Wklysal in the appropriate field. The lowercase letters are changed to uppercase, and the table name is stored as WKLYSAL. If you want the name of the table to be Wkly Sal, type: "Wkly Sal" in the appropriate field. The quotation marks are recognized as the delimiters of the identifier, and the table name is stored as Wkly Sal.
If you want the name of a column to be "Nickname", type: """Nickname""" in the appropriate field. The outermost quotation marks are recognized as the delimiters of the identifier, and each pair of consecutive quotation marks represents one quotation mark. The column name is stored as "Nickname".
Transaction manager (TM) database
The DB2 transaction manager (TM) assigns identifiers to transactions, monitors their progress, and takes responsibility for transaction completion and failure. DB2 Universal Database (UDB) and DB2 Connect provide a transaction manager. The DB2 TM stores transaction information in the designated TM database.
Database configuration parameter: Transaction Manager (TM) Database (TM_DATABASE)
Transaction processing (TP) monitor
Transaction processing (TP) monitors are used to improve performance in OLTP environments. Many users can share a smaller number of database connections in order to execute transactions.
TP monitors can also be used to coordinate multisite updates across heterogenous resources. For example, a TP monitor can coordinate a single transaction involving updates to a DB2 for OS/390 server and an update to a print queue.
When TP monitors are used to coordinate multisite updates, the transaction states are maintained by the TP monitor itself, not by the native DB2 transaction manager. Therefore, in this environment there is no need for a TM database.
Database configuration parameter: Transaction Processing Monitor Name (TM_MON_NAME)
Frequency
The frequency of an SQL statement represents the number of times it is run. The Create Index wizard uses this number to judge the importance of the statement.
| Note: | If an SQL statement is more important or more critical than some of the other SQL statements in the workload, even though it is run less frequently, then specify a frequency that is higher than that of the other statements in the workload. |
A timeron is an abstract unit of measure. It does not directly equate to any actual elapsed time, but gives a rough relative estimate of the resources (cost) required by the database manager to execute an access plan.
Workload
A workload consists of a number of SQL statements that are run against a database. Each SQL statement in the workload is given a frequency. The Create Index wizard uses the specified workload to determine which indexes to recommend and create.
Table schema
Use this box to specify the schema of the table on which you are creating an index. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:
Table name
Type the name of the table on which the index is created. The table must be a base table (not a view) described in the catalog. It must not be a catalog table. The table name:
Available columns
This list box lists all of the columns defined for the table specified in the Table name field. Use this box to select the column or columns that you want to define as part of the index key. You can select one or more columns at the same time.
You can specify up to 16 columns. At least one column must be specified to define an index.
The sum of length attributes cannot be longer than 255.
>>, >, <, <<
Use these push buttons to change the list of columns displayed in the Available columns, Selected columns, and Include columns list boxes. You can use:
Selected columns
Use this box to specify the column or columns that you want to define as part of the index key.
To add a column name to this box, select it from the Available columns list box and select the > push button.
You can specify up to 16 columns. At least one column must be specified to define an index.
The sum of the length attributes of the specified columns must not be greater than 255 bytes.
Ascending and Descending
Use these radio buttons to specify the sort order of the columns that you selected in the Selected columns list box.
Unique
Select this check box to indicate that a table will not contain two or more rows with the same value of the index key. The constraint is enforced when rows of the table are updated or new rows are inserted. The constraint is also checked during the execution of the CREATE INDEX statement. If the table already contains rows with duplicate key values, the index is not created.
When the Unique check box is selected, null values are treated as any other values. For example, if the key is a single column that can contain null values, that column can contain no more than one null value.
Cluster
Select this check box to specify that an index is the clustering index of the table. Only one clustering index may exist for a table so Cluster may not be specified if it was used in the definition of any existing index on the table.
A clustering index may not be created on a table that is set to use append mode.
Allow reverse scans
Select this check box to specify that Reverse scans can be performed on the index that you are creating. When the check box is checked, the index can support both forward and reverse scans; that is, the scan can be performed in the order defined at INDEX CREATE time and in the opposite (or reverse) order. This eliminates the need for the optimizer to create a temporary table for a reverse scan and it eliminates the need for you to create 2 indexes for the same columns on the table, one for forward and one for reverse scanning. It also facilitates determination of the maximum key of an index.
When the check box is not checked, the index only supports forward scans or scanning of the index in the order defined at INDEX CREATE time.
Percentage of free space to be left on index pages
Use this field to specify the percentage of each index page to leave as free space when building the index.
The first entry in a page is added without restriction. When additional entries are placed in an index page, the value specified in this field determines the percentage of free space left on each page.
Select a percent from 0 to 99. The default value is 10 percent. If you select a value greater than 10, only 10 percent free space will be left in non-leaf pages.
Percentage of minimum amount of used space to be left on index
pages
This field specifies, for Online Index Reorganization, the threshold for the minimum percentage of used space on an index leaf page. If the percentage of space used on the page is at or below integer % after a key is deleted from an index leaf page, an attempt will be made to merge the remaining keys on this page with those of a neighbouring page. If there's sufficient space on one of these pages, the merge will be performed and one of the pages will be deleted.
The value of integer can be from 0 to 99. However, a value of 50 or below is recommended for performance reasons. The default value is 10 percent.
Comment
Type a comment to document the index that you are creating or altering. You can type up to 254 characters, including embedded blanks.
Include columns
Use this box to specify additional columns to be included in the index but not as part of the unique index key. These columns may improve the performance of some queries through index-only access. The columns must be distinct from the columns used to enforce uniqueness.
To add a column name to this box, select it from the Available columns list box and select the > push button.
You can specify up to 16 columns. The sum of the length attributes of the specified columns must not be greater than 255 bytes.
Estimate Size
Click on this push button to open the Estimate Size window, in which you can estimate the amount of storage space for a new or existing table or index.
