Creating visual queries using the Prompted Query editor

You can build simple to complex queries using the Prompted Query editor. The Prompted Query editor prompts you for the information it needs to build the SQL statement.

1. Select File > New > Other and expand the QMF Objects folder.
   1. Double-click Visual Query.
   2. Specify the name of the query in the Name field and specify where the query will be saved in the Data Source field.
   3. Click Finish.
   4. Click the Prompted tab.
2. Use each of the following panes to build your query:
   • Tables where you specify the tables that will be used in the query.

     If you are running a version of Db2 that supports temporal data (for example, DB2 z/OS v10), when a table that has been configured for temporal data is added to the prompted query, the Time Period icon is enabled. If there are multiple tables in the query, the Time Period icon is enabled only when the table that is configured for temporal support is selected.

     Clicking the Time Period icon launches a window in which you can specify the points in time as either system time or business time (depending on the type of time for which the table has been configured). Only one of the system time or business time pair will be enabled for any given table.

     Temporal data support provides a historical perspective to table data. Historical data can often serve a specific business case. For example, a business might require access to information about the level of insurance coverage that was in place at the time of a claim several months in the past. A table with temporal data support stores and records information about the period of time when a row is valid with respect to system time, business time, or both. System time, or transaction time, is when the transaction is recorded. Business time, or valid time, is when the data is valid with respect to information about the current real world business data.

     If the DBA specified the tables and columns that require temporal data support when they are created, Db2 automatically maintains the history whenever an update is made to the data.

     To specify a time period, click the Time Period icon and specify values for System Time and Business Time.

     1. The drop-down list to the right of the For System Time and For Business Time labels provide SQL reference options.
     2. Select As Of, From, or Between. Consult your SQL reference documentation for details on the usage of these options.
     3. Set the associated date by clicking the date and time icon .

     If you do not need the period you have specified, simply clear the corresponding check box. If you remove a table or edit its owner or name, then all the specified time periods will be lost.

     The As Of option creates a temporal specification in the SQL of a FOR statement <SYSTEM_TIME | BUSINESS_TIME> AS OF <selected timestamp>

     The From option creates a temporal specification in the SQL of a FOR statement <SYSTEM_TIME | BUSINESS_TIME> FROM <selected timestamp>

     The Between option creates a temporal specification in the SQL of a FOR statement <SYSTEM_TIME | BUSINESS_TIME> BETWEEN <selected timestamp1> AND <selected timestamp2>

   • Join conditions where you specify the join conditions for tables if you are using multiple tables in the query.
   • Columns where you specify the columns that will be included in the query results.
   • Sort conditions where you specify how the rows in the query results will be sorted.
   • Row conditions where you specify conditions that will limit the rows that are returned in the query results.
   • Include duplicate rows check box where you specify that duplicate rows will be included in the query results.
3. To add a distinct SQL statement to your query, click in the toolbar.
4. To remove an unwanted SQL statement, click in the toolbar.
5. When you are done building the query, click the Run Query icon.

   The query is run against the currently connected to data source. Results are returned.

   The query results are identified with a Results tab.

   You can switch between the multiple views of a query by clicking the corresponding tabs.

   Once you have obtained query results you have several options. You can:

   • Format the query results' columns and rows.
   • Aggregate columns or rows.
   • Add calculated columns to the query results.
   • Generate reports
   • Export the query results to numerous formats.

   The query object stays open until you close it. Upon closing, if you want to run the query object again you must save it.

6. Click the Save icon to open the Save object window.

   Depending on the permissions granted to your user ID, you can save the query object to a file, a workspace, the QMF catalog, or the repository.