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 image2021-8-18_11-40-45.png 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.

    Important

    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.

To edit DB2 selection criteria

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.

  1. 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.

      Important

      • File-AID Data Editor does not support existing mainframe DB2 selections that contain aggregate functions.
      • 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.

  2. 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. 

    Important

    • To view the entire table, without making any criteria selections, skip to step 10.
    • To edit the SQL manually without first using automated selection criteria, click the Edit SQL icon image2021-8-18_11-43-34.pngto access the SQL Editor.
  3. 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.
  4. 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.
  5. 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 DB2 Selection Criteria Operators.
  6. 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.

  7. 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.  

    Important

    • 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.  
    • 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.
  8. 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.

  9. 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. 

    Important

    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.

  10. Optionally, click the Edit SQL icon image2021-8-18_11-44-8.png to manually edit the displayed SQL. For more information, see SQL Editor.

    Query and Display Options

  11. Enter a number for the Maximum rows to select. The default is 2000.
  12. 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.

  13. 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). 

    Important

    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

  14. 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. 

    Important

    • 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.
    • 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

  15. 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.

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

    Important

    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.

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

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*