Software Configuration Management. NET Applications. Mastering Delphi 7. If you may any questions please contact us: flylib qtcs. Privacy policy. This website uses cookies. Click here to find out more. Specifying the LOG NO option to avoid logging data changes will further enhance the performance of the mass delete. Note, however, the following considerations:. If multiple tables are assigned to a simple table space, the LOAD utility deletes all rows for all tables in that table space.
Consider loading a DUMMY data set even for segmented table spaces if a large amount of data must be deleted. To enhance the performance of the LOAD utility, use a fixed blocked input data set rather than a variable blocked data set. It is usually more efficient to process the loaded data once, as it is loaded, than to process the data twice, once to load it and once to check it.
Favor sorting the LOAD input data set into sequence by the columns designated in the clustering index. Be sure to sort the data in the appropriate sequence, either ascending or descending, depending on how the index was defined. Otherwise, the LOAD utility does not load data in clustering order, and the table space and indexes will be inefficiently organized.
When DFSORT encounters duplicates, it sends one of the values to the output file then discards the remaining duplicates. When the LOAD utility encounters duplicates in the input file, it sends all of the duplicates to the discard file. The first starts in position 1 for 4 bytes, the second starts in position 5 for 4 bytes, and the third starts in position 9 for 12 bytes. The first two fields are unsigned binary and the third is character.
And the sort is to be ascending for each field. If data is not loaded in clustering sequence, consider following the LOAD with a table space reorganization. It is best to avoid sorting the input in this case. Doing so avoids the overhead of logging the loaded data and speeds load processing.
If data is loaded without being logged, however, follow the LOAD utility with a full image copy. However, this option should be utilized only when you are sure that the same basic type of data is being loaded into the table. If the type of data differs substantially, allowing the LOAD utility to rebuild the compression dictionary will provide for more optimal data compression.
Loading tables with nullable columns can degrade the LOAD utility's performance. If a table will be loaded frequently daily, for example , consider reducing or eliminating the number of nullable columns defined to the table to increase the performance of the LOAD utility.
Additionally, nullable columns might make more sense than default values given the specification of the application. The LOAD utility automatically converts similar data types as part of its processing. Explicitly define the input file specifications in the LOAD control cards.
Do this even when the data set to be loaded conforms to all the default lengths specified in Table If the input file specifications are not explicitly identified, the LOAD utility assumes that the input data set is formatted with the defaults specified in Table This length value is placed in a four-byte binary field at the beginning of the LOB value.
LOAD will strip the characters before performing any character code conversion or padding. If a specific character is not coded, the default is to strip blanks. It is usually more efficient to define all indexes before using the LOAD utility. The initial loading of DB2 table data usually involves the insertion of many more rows than does typical application processing. For the initial population of table data, the LOAD utility is generally more efficient and less error-prone than a corresponding application program, and also maintains free space.
These keywords specify that you are loading a table from scratch and that any previous data will be lost. Accurate statistics are necessary to maintain current information about your table data for access path determination.
Of course, access paths for static SQL will not change unless all packages and plans accessing the table are rebound. Any dynamic SQL statements will immediately take advantage of the new statistics. Concurrent loading of multiple partitions of a single table space can be achieved using partition independence. This technique is useful for reducing the overall elapsed time of loading a table in a partitioned table space.
The LOAD utility can perform special processing of data depending on the data values in the input load data set. Data contingency processing parameters indicate a field defined in the LOAD parameters or a beginning and ending location of data items to be checked. The data contingency processing parameters follow:.
Sets column values to a predefined default value if a particular character string is found at a particular location. For example. Used when there are record types in the input load data set. ID Technical bulletin: A new parameter enables terminating jobs that run with duplicate consistency points. ID Installation and maintenance: Scheduled outage for filedownload. An incremental copy of a LOB table space that extends to multiple data sets might not include all of the updated pages.
The primary key value for each selected row is saved. This specifies the relationships to be traversed when extracting data. This portion of the panel is used to specify the selection criteria when extracting data from each table and displaying the data for a Point- and-Shoot session. Error Handling When an error condition is encountered, it must be resolved. Some errors prevent the user from proceeding in the definition process; other errors must be resolved before saving the Access Definition.
The following errors must be resolved before saving the Access Definition. Appropriate error messages are displayed. Use HELP to display additional information. Any data Abandons editing or line commands that are present are ignored. All changes, Changes including those that have introduced errors, are abandoned. Any specifications previously stored by the SAVE command are retained. MOVE provides several methods for specifying selection criteria.
These include specifying:. When more than one form of criteria is specified, they are combined. However, if Point-and-Shoot values are specified for a Start Table along with selection criteria, a prompt is displayed on the Specify EXTRACT Parameters and Execute panel enabling you to specify whether only the Point-and-Shoot values or both the values and selection criteria are to be used. When selection criteria or an SQL WHERE Clause is not specified, all rows in the non-start tables are candidates for extraction based only on the related data they contain and, if specified, a random selection factor and maximum row limit value.
The prompts for selection criteria and an SQL WHERE clause are displayed on different panels and discussed with the appropriate panel description in the following sections. Selection criteria limit the set of rows extracted from each table and determine which rows are displayed during a Point-and-Shoot session. Selection criteria can be specified for one or more columns.
The Specify Selection Criteria for AD panel is used to specify the selection criteria for each table. By default, all rows in each table are included, subject to user and site maximums. Alternatively, you can enter the line command, SEL, in the Cmd field next to the table name as shown in the following figure.
Combine All Specifies the logical operator to apply to the Column Criteria by column criteria. A All criteria are logically ANDed. O All criteria are logically ORed. Cmd Line command entry field. The supported line commands include:. M or MM Move line. A or B Destination for move. Z Zoom to display all prompts for each column on a single panel. The names cannot be edited and columns can not be inserted or deleted on this display.
By default, the columns are listed initially in the order in which they were specified to DB2. Use the Move line command to rearrange the order for Point-and-Shoot. The order has no impact on data selection or sort sequence. Column Name The name of the column. All columns in the table are listed. Notation is displayed to the right of the Table Name indicating the relative position of the first displayed column name within the list. This notation is displayed as Col x of y where x represents the relative position of the first displayed column and y the total number of columns in the list.
Selection Criteria Specifies the selection criteria for each column. Any valid SQL predicate is allowed. IN a,b,c,d NOT IN a,b,c,d Specifying the Selection criteria are specified by supplying an appropriate operator Criteria along with a corresponding value or a list of values.
If the fields are ORed, all customers satisfying either condition are included. If the fields are ANDed, only the customers that satisfy both conditions are included. That is, only the customers whose names begin alphabetically greater than M and are from the state of Massachusetts are included. The following figure displays the selection criteria to obtain customers who satisfy both conditions.
The Describe Columns for AD panel is used to define other column specifications for the selected table. These specifications apply to the Point-and-Shoot session only and do not affect the Extract Process. The column specification prompts are shown in the following figure.
Panel Fields The information on this portion of the panel is used for a Point-and- Shoot or browse session to determine how data is displayed. In addition to the Cmd field and the Column Name field, the panel includes:. Disp Specifies whether the column is included or omitted when the Access Definition is used for Point-and- Shoot. At least one column must be displayed. Acc Specifies whether the data in this column is display only or can be modified using Access for DB2.
If Access for DB2 is not installed, this field is not displayed. Regardless, it has no impact on MOVE processing. Sort Specifies the sort criteria for displaying the data for a Point-and-Shoot session. If values are not specified, the order in which rows are displayed is undetermined. Sort specifications are composed of two values:. Lvl A numeric value indicating the order of priority of this column in sorting the rows.
The value can be from 1 though 64 inclusive with 1. The Lvl fields must contain unique and consecutive values starting with 1. Heading Specifies the heading to be displayed with the column and the position of the heading when the data is displayed for Point-and-Shoot browsing. Valid heading values are:. N The column name. L The label defined in the DB2 Catalog. The position of the heading is determined in relation to the width of the data for a column.
When the width of the data exceeds the width of the heading, this value positions the heading. However, if the width of the heading exceeds the width of the data, the data is positioned based on this value. The valid values for the heading position are:.
L Left-justified. R Right-justified. C Centered. The display position of the data within the data field depends on the data type. Character data is left-justified and numeric data is right-justified. Data Type Shows the data type and dimension of each column. For the most part, the DB2 data type format is displayed. However, due to space limitations on the panel, the following data type specifications are displayed in a shortened format:.
Null Indicates whether the column is nullable. This field is for display only. The possible values are:. Crit Indicates whether selection criteria have been specified on the Selection Criteria for AD panel. YES Criteria have been specified. NO Criteria have not been specified. To display all of the information for a single column on one panel rather than scrolling left and right to complete the specifications, use the ZOOM command.
ZOOM can be assigned to a program function key. Use the ZOOM primary command to return to the column list display. Notation is provided to the right of the Table Name to indicate the relative position of the currently displayed column and the total number of columns in the table. Although Combine All Column Criteria is displayed with each column in zoom mode, it is a global setting and applies to the entire table. Modifying the value when any column is displayed modifies the value for the table.
Sometimes the column selection criteria can not be adequately specified on the Specify Selection Criteria for AD panel. Columns for AD panels, enter the SQL primary command with no operands to display the panel for the current table. This heading includes an ellipsis, This is especially useful for qualifying a column name when there are multiple tables referenced in the SQL.
Data area Specify the desired SQL in the data area of the panel. The data area displays the SQL in segments of six lines containing 72 positions. A maximum of lines is available for specifying the SQL and self- documenting comments. Standard DB2 conventions apply to comments; each line must begin with two hyphens This area is scrollable. Notation in the upper right above the data area indicates the total number of lines and the relative position of the first displayed line. To facilitate editing, a Cmd field is provided for each line.
Several functions can be performed using line commands. The functions and the line commands are:. When you type the data, all leading and trailing spaces on each line are maintained. This list can be scrolled and selections can be made directly from this list using the Select line command.
All of the standard ISPF facilities are available. The edited data is inserted into the panel. Use the SAVE command to save the current specifications and continue editing.
The clause must be lines or less and the clause must be acceptable to DB2. In either case, a message is displayed detailing. Archive Criteria Archive criteria determine the columns that are indexed in the PST Directory and limit the set of rows archived from each table. Archive criteria can be specified for one or more columns and are combined with selection and other criteria in an Archive Process. The Status field will contain SEL when selection.
However, if the default column settings have not been modified and selection criteria has not been specified, COL and SEL are not displayed in the Status field. These selected rows are used to start an Extract Process. If selection criteria have also been defined for the Start Table, you can specify whether the Point-and-Shoot list overrides the criteria or is used to supplement the selection criteria.
Primary Key The specific rows you select from the Start Table in a Point-and- Shoot session are identified by the primary key values, therefore, the Start Table must have a primary key. Value Stored The primary key values are stored in a sequential or partitioned data set that you specify. If the data set does not exist, MOVE prompts for the required information and allocates it for you.
See Appendix B. Allocating External Files for more information. Once saved, these values can be used and modified as needed. Specifications made while defining an Access Definition must be permanent so they can be used in a future Extract Process request. The Point-and-Shoot specifications made when defining an Extract Process can be stored permanently or used only for the current request.
You can use a previously saved row list by specifying the name of an input data set containing the row list to be used. Overtype the name to use another Input DSN. To stop using the named input data set, erase the field. If an Input DSN is supplied, the previously selected rows are identified on the display.
If the Input DSN field is blank, there are no. If an Input DSN is supplied, the primary key values in the named input data set are to be used. Thus, you can use the primary key values from an existing input data set without entering a Point-and-Shoot session. If the Input DSN field is blank, there are no primary key values to be used. This does not delete the data set, but discontinues its use for this Access Definition.
Intervening Changes may have been made between the time the primary key Changes values were originally selected and stored, and the current Point-and- Shoot session. Due to these changes, MOVE may be unable to find rows for primary key values saved in the Point-and-Shoot file because the rows were deleted or changed in the database. If this occurs, MOVE displays a prompt notifying you of the situation.
The number of rows previously saved in the Point-and-Shoot file are also displayed. The following options are provided:. The following sections discuss the facilities that are available during a Point-and-Shoot session. For more information about the primary and line commands discussed in these sections, see the Relational Tools Command Reference Manual. Access Definition Parameters. D Data Begin with data from the named Start Table and all subsequent tables.
This is the default. For example, if you defined selection criteria to select only the customers in the state of Massachusetts, only those customers are displayed in the Point-and-Shoot session. All other tables begin with the data display. A Criteria for All Tables Display the selection criteria prompt for the named Start Table and for each table added to the display via the JOIN command before displaying the data for that table during a Point-and-Shoot session.
You can use it to respecify the selection criteria for any currently displayed table. The criteria specified during the Point-and-Shoot session is used for the session only and is not saved permanently in the Access Definition. For details on this panel see Section 3. If the selection criteria for the Start Table are changed such that the set of rows in the Point-and-Shoot file are not appropriate, MOVE will prompt you to specify whether to continue with the session or to retain or discard the previously selected rows.
However, the ability to select specific rows and therefore the primary key values while browsing DB2 data requires additional screen elements and facilities. This section discusses Point-and-Shoot screen elements, commands, and display modes. Display Format There are two basic display formats: columnar and sidelabels.
Columnar format is the default format. In columnar format the column headings are displayed across the top of the panel and the data is displayed in columns beneath the headings. Many rows from the table are displayed:. In sidelabels format, the column headings are displayed down the left side of the panel and the data is displayed to the right of the headings.
The display is focused on a single row, however, more columns are displayed for that row:. Most of the screen examples throughout this manual are shown in columnar format. The screen elements provided by columnar and sidelabels format are the same; only the positioning is different. Columnar Format The following figure highlights some key screen elements displayed during a typical columnar format edit session.
Sidelabels Format The same screen elements are displayed when using sidelabels format, however, the location may differ. Column Headings By default, the DB2 column names are displayed as the column headings. You can specify that the DB2 column labels are to be used on the Describe Columns panel.
See Section 3. Status Flag Field Status flag field identifies which rows have been selected by displaying an S in this field for each selected row. In Figure Columnar Screen Elements, three rows have been selected. The table name may be truncated if the combined length of the Creator ID and table name exceeds 22 characters. Short Name A short name or identifier for each table Tn or view Vn , where n uniquely identifies the table or view.
For example, in a multi-table display, T1 identifies the first table, T2 the second table, and so on. The Tn or Vn value can be used as a command operand in place of the table name. Truncated Column In columnar format, the maximum display Indicator width of columns can be in the range of 15 to 70 characters. In sidelabels format, the maximum display width can be in the range of 50 to These values are controlled by editor options and apply to all displayed columns.
When the width of a column exceeds this value, the data from that column is truncated. In sidelabels format, an equal sign is displayed to the right of the column heading. Row Count Indicates the current position within the total set of rows. The total row count includes the line the 'Excluded Lines' message is on but. Horizontal Scroll Displayed in columnar format only. For more information on scrolling see Section 3. Column Count Displayed in sidelabels format only.
Indicates the current position within the total set of columns. The number of rows selected from the Start Table to be used for an Extract Process is always displayed in the upper right-hand corner of the screen. These commands perform the same functions in both columnar and sidelabels format. In columnar format, sufficient screen space must be available to accommodate the display width of a column.
When the specified display width of the column exceeds the available space, the column is not displayed on the current screen. Scroll left or right as appropriate. For example, if the display width of a column is 20 characters but only 10 spaces are available, that column is omitted from the display. The column can be displayed by scrolling right. In sidelabels format, if insufficient space is available, partial columns may be displayed.
COUNT Command When the Maximum Fetch Limit is less than the total number of rows that satisfy the criteria, a message displays indicating that all possible rows have not been retrieved.
You can use the COUNT command to display the total number of rows in a named table that satisfies the search criteria. You can respecify the selection criteria to retrieve fewer rows. For example, if your Maximum Fetch Limit is This command only affects the current session. To increase the maximum number of fetch rows for all of your sessions, use the Maximum Fetch Rows option on the Editor and Display Options panel.
When selecting rows to be extracted, it may be helpful to display the attributes of the columns. The column attributes include the data type, length, and nullability of the column. They are displayed directly beneath the column name. The format of the attribute information is type n. The presence of :N indicates nullability. To conserve space on the screen, certain data types are abbreviated. The data type indicators and the DB2 data types that they represent are listed below. When the attributes mode is activated, attributes are displayed regardless of the display mode in effect columnar, sidelabels, or hex mode.
Both primary and line commands are available. Many of these commands are introduced in the following sections. Detailed information about each command is provided in the Relational Tools Command Reference Manual.
Primary The following is a list of primary commands available during a Point- Commands and-Shoot session. Line Commands The following line commands are available during a Point-and-Shoot session. The J and UNJ line commands are not available when using sidelabels format. For example, to select all customers in the city of Amherst, the command can be entered as:. In the following figure, the last selected row, Reely Great Videos is unselected. Only rows in the Start Table can be directly selected and unselected.
However, the related rows from the other tables included in the Extract Process will also be extracted if the related Start Table row is extracted. Several commands are available to scroll the data. You can scroll vertically when the number of rows to display exceeds the number of lines available on the screen. You can scroll horizontally when the width of the data exceeds the width of the screen.
When a single table is displayed, vertical scrolling works identically to vertical scrolling in ISPF. Scrolling a multi-table display is discussed in Section 3. During a Point-and-Shoot session, the columns to be displayed may not all fit on the screen. When the combined width of the columns exceeds the width of the screen, the scroll indicator, MORE and an appropriate direction arrow, are displayed on the information line for the table.
Operands for these commands enable you to scroll by column name, numeric value, or cursor position. When multiple tables are displayed, indicate which table to scroll by specifying a table name or identifier with the column name for example, T2.
If a table is not indicated, the lowest level table is scrolled. The LOCK command repositions the named column to the left of the screen and retains the column in that position when a horizontal scroll is performed. Multiple columns may be locked. For example, assume that the combined width of the displayed column data for the CUSTOMERS table exceeds the width of the screen, therefore the display must be scrolled left and right to view all of the data.
The column is repositioned to the left of the display and is retained in that position regardless of left and right scrolling. The first locked column is placed in the left-most position. Each succeeding locked column is positioned to the right of the previously locked columns.
If that column is also locked, it is identified by a series of dots under the column heading to distinguish it from the locked columns that are not truncated. For more information on handling this truncated data, see Section 3. Locking Multiple Any series of columns may be locked as long as enough space remains Columns to display the widest column that is not locked yet in addition to the locked columns. The reserved area is determined by the user-defined maximum display width for any column.
For example, if the user- defined maximum display width is 20 characters, then the right-most 21 character positions on the screen are reserved for unlocked columns. This ensures that at least one unlocked column is always displayed. More columns may be displayed if they fit, in their entirety, in the remaining area.
Specify the name of the column with the command to unlock only that column. When a large number of rows are displayed, it can be very useful to exclude some of those rows from the display leaving a more manageable set. These rows are retained in the table, but are not displayed. In the following figure the Exclude line command is used. The excluded rows are replaced with a message indicating the location and number of excluded rows.
Note that the row count changes to reflect the excluded lines. The excluded lines message counts as one line. If the row is not currently displayed, it is scrolled to the top of the display in the window of the searched table.
In a multiple table display, any lower level tables that are displayed are automatically scrolled appropriately. A variety of operands are available for the FIND command to specify where the search should begin and whether the search should include excluded rows. If the column name is omitted, all columns with an appropriate data type as the search string are searched.
0コメント