Requesting DB2 Data

The File-AID Data Editor Request dialog captures your options for accessing DB2 data.

You can specify selection criteria to limit the rows and columns you wish to access. You can also establish other display and run options such as Browse mode or Edit mode. You can save and re-use your request, making it easy to access your data the same way the next time you access this object.

Creating a New or Using a Saved Request

Your recently used requests are listed on the Data Editor menu accessed by clicking the toolbar File-AID icon and selecting Data Editor.

To create a new or use a saved request:

1. At the top of the File-AID Data Editor Request window, enter a Request name to create a new request, or select a request name from the Request name drop-down list to use an existing request.

   Note

   If you try to create a request with a name that has already been used, a message will appear asking whether you want to overwrite the existing request. DB2 Request Names by a single user—or all users in a single workspace—must be unique even for different tables. If you have requests for several tables and have trouble remembering what request names you have already used, you could use naming conventions for your requests where requests for a specific table start with a specific letter or group of letters. For example, requests for tables for the Human Resources department could start with HR and requests for the Sales department could start with SA. Then, request name HRTest would be different from SATest.

   Optional selection criteria determine what portion of the selected DB2 table/view becomes available for viewing or editing. You can choose which columns will be selected, conditions for selecting rows, and how the selected rows will be sorted. Only rows that match your selection criteria will appear in the editor data display. If you do not specify selection criteria, all rows and columns of the selected table/view are eligible for presentation in the editor data display.
   If you are not already at the File-AID Data Editor Request for a DB2 Object dialog, see Opening the File-AID Data Editor and Accessing DB2 Data for information on how to start the File-AID Data Editor.

   To edit DB2 selection criteria

2. Select whether you want to use a selection criteria dataset or specify your selection criteria manually. If you selected the Use selection criteria below radio button, continue with step 3 below. If you selected the Use selection criteria dataset(member) radio button, either enter the dataset name including the member name in the adjacent box, or browse to the desired dataset and member as follows:
   1. Click Browse. The Dataset Selection dialog box appears.
   2. Enter a partial name in the Datasets Name box. You can use the asterisk wildcard. Click List. A list of all datasets that meet the filter requirements appears.
   3. Select the desired SQL dataset. This populates the list of members for this dataset.
   4. Select the desired Member Name from the list. Optionally, use the Member Name box to specify a partial member name to reduce the size of the member list. The list is dynamically refreshed with each character you type.
   5. To verify that you have chosen the correct dataset and member, click View. This displays the SQL member for viewing. Click Close to return to the Dataset Selection dialog box.

   6. After making your selections, click OK. You will return to the File-AID Data Editor Request window.

      Note

      File-AID Data Editor does not support existing mainframe DB2 selections that contain aggregate functions.

      Note

      You can edit your SQL by using the Host Explorer view and selecting a supported editor (a Topaz Editor) for the PDS member containing your SQL.

3. Optionally, click on the Columns tab, select the desired columns from Available Columns and move them to Selected Columns. The search box near the top can be used to quickly find the desired columns. The columns you select are the only columns that will be viewable when you navigate to the data display. If you do not make any choices, all columns are automatically selected, and you can skip to the next step. 

   Note

   To view the entire table, without making any criteria selections, skip to step 10.

   Note

   To edit the SQL manually without first using automated selection criteria, click the Edit SQL icon to access the SQL Editor.

4. Optionally, click on the Conditions tab. Rows will be chosen based on the conditions selected. You can add conditions or groups. Groups can contain multiple conditions.
5. Under Column Name, click on <select field>. The Select filter column dialog box appears. Select the field to use as a filter. The search box near the top can be used to quickly find the desired field. You can select only one field per condition. Then click OK.
6. Select an Operator from the list (is equal to is the default). Operators are only available for selection after you have selected a field under Column Name. For more information, see 2021-08-19_07-09-05_DB2 Selection Criteria Operators.
7. Type a Value in the value column. Character literals require single quotes around values. Numeric literals (DECIMAL, INTEGER, and FLOAT) do not require quotes around values. If a character value you specify contains a single quote, you must enter two (2) single quotes. For example: 'DON''T' to find DON'T. Character literals only match data with the same exact case.

8. To enter more than one condition, click Add Condition and repeat steps 5-7 for each additional condition. Each additional condition has a Boolean connector of OR. You may override the OR to an AND by clicking on the connector and using the pulldown to select AND or OR. You may also choose to click Start New Group to create a new set of conditions. Each new group can be grouped with previous groups using AND or OR. Conditions within the group can also be grouped together using AND or OR as desired. The SQL window shows the constructed SQL to verify the conditions you have specified.  To eliminate a condition, click on the condition to select it, then click the Remove button. To remove all conditions, click the Remove All button.  

   Note

    To eliminate a condition, click on the condition to select it, then click the Remove button. To remove all conditions, click the Remove All button.  

   Note

   You may optionally click on the condition to select it, then click the Duplicate button to insert a copy of the condition following the current condition.

9. To enter more than one group, click Start New Group. Your cursor location will determine where the group will be created and the group level. You can add multiple conditions to any group - see the previous step.

10. Optionally, click on the Sorting tab. Click on <select column>. The Select sort column dialog box appears. The search box near the top can be used to quickly find the desired columns. Click the column you want to use for sorting, then click OK. 

    Note

    By default, selected rows will be sorted on this column in Ascending order. Double-click on the Sort Type field ASC (ascending) to reveal a pull-down menu where you can choose DES (descending). You can also add additional secondary sort columns by clicking the Add button to generate a new <select column> input line. After you have defined at least two columns you can highlight one by clicking on the Sort Type and use the Move Up or Move Down buttons to change their priority. You can remove a selected column by clicking on the Sort Type and clicking the Remove button. Optionally, you can right-click on a defined column to view a context menu to perform Add, Remove, and MoveUp and MoveDown actions.

11. Optionally, click the Edit SQL icon to manually edit the displayed SQL. For more information, see SQL Editor.

    Query and Display Options

12. Enter a number for the Maximum rows to select. The default is 2000.
13. Enter the Rows/records to display per page. The default is 2000. This number cannot be larger than the number you entered in the Maximum rows to select field.

14. Optionally, select the Retrieve rows with uncommitted reads (WITH UR) checkbox to modify your generated SQL to add a WITH UR clause to allow for data retrieval mode Uncommitted Reads. The Retrieve rows with uncommitted reads (WITH UR) checkbox default is controlled by the Retrieve rows with uncommitted reads (WITH UR) preference (Window > Preference > Compuware > File-AID Data Editor - DB2 tab). 

    Note

    The Retrieve rows with uncommitted reads (WITH UR) checkbox is disabled when your request is using an existing SQL dataset(member) or when you have manually edited the SQL.

    Run Mode Settings

15. Specify whether you want to view your data in Browse or Edit mode. Edit is the default. If you select Edit, specify whether to Enable Auditing. Enabled is the default. 

    Note

    The maximum rows to select, rows to display, and enable auditing defaults are set in the Windows > Preferences > Compuware > File-AID Data Editor (DB2 tab) preferences.

    Note

    Auditing is controlled by options set on the mainframe during product installation. Three choices are available: Query honors the client preference (Topaz Workbench setting) and enables or disables the audit setting as desired. Force ignores the client preference (Topaz Workbench setting) and creates an audit dataset where audit records will be stored. SMF ignores the client preference (Topaz Workbench setting) and writes audit records to SMF.

    Request Actions

16. Once you have completed your request, it is a good practice to assign a Request Name and click Save to ensure that you can reuse the request in the future.

17. Click Run. You will navigate to the data display view in the designated mode (Browse or Edit).

    Note

    After you navigate to the table, you can return to the Request by clicking the Return to Request link where you may choose to update your selection criteria. Then click Run again, and your data display view will be updated immediately with the new selection criteria.

18. Click Close to exit the request dialog.
19. To delete a saved request permanently, click Delete.
20. To return to the last saved state of a request, use the Revert button.

DB2 Selection Criteria Operators

Following is a description of the operators available for selection criteria conditions for File-AID for DB2.

For information on how to edit your selection criteria, see .

• is equal to: 'George' will select all rows that contain George in this column. Wild cards are not allowed.
• is not equal to: 'George' will select all rows that do not contain George in this column. Wild cards are not allowed.
• is greater than: 'Nancy' will select all rows with names that appear alphabetically after Nancy in this column. Nancy is not selected. Wild cards are not allowed.
• is less than: 'Nancy' will select all rows with names that appear alphabetically before Nancy in this column. Nancy is not selected. Wild cards are not allowed.
• is greater than or equal to: 'Nancy' will select all rows with names that include Nancy and appear alphabetically after Nancy in this column. Wild cards are not allowed.
• is less than or equal to: 'Nancy' will select all rows with names that include Nancy and appear alphabetically before Nancy in this column. Wild cards are not allowed.
• is in list: Use parentheses around the values and a comma between the values. ('John', 'Matt', 'Mary') will select all rows that contain John, Matt, or Mary in this column. Wild cards are not allowed.
• is not in list: Use parentheses around the values and a comma between the values. ('John', 'Matt', 'Mary') will select all rows that do not contain John, Matt, or Mary in this column. Wild cards are not allowed.
• is null: All rows that do not contain any value in this column are selected. Do not enter anything in the value field when is null is selected. Wild cards are not allowed.
• is not null: All rows that contain any value in this column are selected. Do not enter anything in the value field when is not null is selected. Wild cards are not allowed.
• is between: You must use the AND operator with two values. 'John' AND 'Matt' will select all rows that contain names starting with John and ending with Matt including all names in between. Wild cards are not allowed.
• is not between: You must use the AND operator with two values. 'John' AND 'Matt' will select all rows that do not contain names starting with John and ending with Matt including all names in between. Wild cards are not allowed.
• contains: You can enter specific data, and wild cards are allowed. For example '%ART%' will select all rows containing data that contains ART anywhere within this field.
• does not contain: You can enter specific data, and wild cards are allowed. For example '%ART%' will select all rows containing data that does not contain the value ART anywhere within this field.

SQL Editor

When you define Selection Criteria, the choices you make will automatically create the SQL required for selecting your data. The generated SQL is displayed in the SQL window of the Selection Criteria section of the Request. If, however, you want to create or edit the SQL yourself, rather than using the SQL automatically created by making selection criteria choices, you can click the Edit SQL icon () to open the SQL editor.

If you are not already at the File-AID Data Editor Request for a DB2 Object dialog, see Opening the File-AID Data Editor and Accessing DB2 Data for information on how to start the File-AID Data Editor.

To edit your SQL manually

1. Optionally, make any automated selection criteria choices as described in Edit DB2 Selection Criteria.

2. Click the Edit SQL icon (), then click Yes on the confirmation message. This opens the SQL Editor.
3. Enter your SQL as desired.

4. Optionally, click the Select Columns icon (). The Select column dialog box appears. Select the desired column(s) and click OK. The selected columns are added to the SQL.
5. If you have not yet saved your edited SQL, you can click the Undo SQL icon () to discard your changes and return to the Selection Criteria section of the Request.
6. Optionally, to save your edited SQL, click Save. This will disable any further use of automated selection criteria for this saved Request.