Requesting RDBMS Data

The File-AID Data Editor Request dialog captures your options for accessing RDBMS 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. Your recently used requests are listed on the Data Editor menu accessed by clicking the toolbar File-AID icon  and selecting Data Editor.

Creating a New or Using a Saved Request

To create a new or use a saved request:

1. At the top of the File-AID Data Editor RDBMS Request window, enter a Request Name to create a new request, or select a saved request name from the request name pull-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. RDBMS 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.

   Edit RDBMS Selection Criteria

   Optional selection criteria determines what portion of the selected RDBMS 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 an RDBMS Object dialog, see Opening the File-AID-Data-Editor and Accessing-Distributed-RDBMS-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 local file containing a valid supported SQL query or specify your selection criteria manually. If you selected the SQL file radio button, enter the file name or click Browse to search for the desired file.

   1. Click Browse. The Open dialog box appears.
   2. After locating the file containing the SQL you want to use for accessing the database, click Open. You will return to the DB2 Request window.

   Important

   • File-AID Data Editor does not support SQL selections that contain aggregate functions.
   • You can edit your SQL using the Host Explorer view and selecting a supported editor (a Topaz Editor) for the local file containing your SQL.

3. Optionally, click on the Columns tab, select the desired columns from Available Columns and move them to Selected 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 supported columns are automatically selected, and you can skip to the next step.

   Note

   • To view all supported column types for the entire table, without making any criteria selections, skip to step 10. 
   • Not all column types are supported by the File-AID Data Editor. Contact the BMC Support for details of supported column types for each type of database.
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. To add a new condition, click Select Where, then click Add Condition.

5. Under Column Name, click on <select field>. The Select filter field dialog box appears. Select the field to use as a filter. You can select only one field per condition. Then click OK.

   Important

   Not all column types are supported for creating a condition.

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

   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.
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>. Click the column you want to use for sorting. 

    Important

    By default, selected rows will be sorted on this column in Ascending order. Double-click on the Sort Type ASCENDING to reveal a pull-down menu where you can choose 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.

    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 checkbox. This will allow statements to read rows that have been modified by other transactions but not yet committed. The Retrieve rows with uncommitted reads checkbox default is controlled by the Retrieve rows with uncommitted reads preference (Window > Preference > Compuware > File-AID Data Editor - JDBC tab). 

    Important

    The Retrieve rows with uncommitted reads checkbox is disabled when your request is using an existing SQL file 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 display and enable auditing defaults are set in the Windows > Preferences > Compuware > File-AID Data Editor > JDBC tab preferences.

    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.

RDBMS Selection Criteria Operators

Following is a description of the operators available for selection criteria conditions for distributed RDBMS requests.
For information on how to edit your selection criteria, see the preceding topic Requesting RDBMS Data.

• 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. Generated SQL displays in the SQL window of the Selection Criteria section of the Request. If, however, you want to create the SQL yourself, rather than create it automatically by making selection criteria choices, you can click the Edit SQL icon () to open the SQL editor.

To Edit Your SQL Manually:

1. Start Topaz Workbench and start File-AID Data Editor. 

   Important

   You can start using the automated selection criteria and just make minor changes manually or you can go directly to the manual SQL editor.

2. Make any automated selection criteria choices first.
3. Click the Edit SQL icon (). This opens the SQL Editor.
4. Enter your SQL as desired.
5. Click Save.
6. Change your Maximum rows to select and Rows to display per page if desired.

7. Select your run mode (Browse or Edit), and click Run.

   Important

   File-AID Data Editor does not support SQL that contains UNIONS, JOINS, Correlation Names, or Special Functions.