Generating and executing SQL queries

To test SQL access to your data, generate and execute a SQL query from an existing virtual table or virtual view.

Before you begin

To avoid fetching large result sets that are memory intensive, the Data Virtualization Manager studio provides settings related to SQL generation and retrieval that can limit the amount of data that is actually retrieved for a particular query execution. For more information, see SQL preferences.

Important: When writing SQL to access Adabas data, use caution when using the BASE_KEY in WHERE predicates, (for example, [PARENT TABLE].BASE_KEY = [CHILD TABLE].PARENT_KEY) when joining the parent table with a child subtable, since this will result in a table scan of the entire Adabas file. It is recommended instead to use the CHILD_KEY (for example, [PARENT TABLE].CHILD_KEY = [CHILD TABLE].PARENT_KEY).

Procedure

  1. On the Server tab, right-click a virtual table and select Generate Query.
  2. Choose from the following options:
    • Execute – Generate the SQL query in the Data Source Editor and execute the query.
    • Cancel – Generate the SQL query in the SQL Editor without executing the query. The generated SQL SELECT statement has all columns from the selected table. If the table contains a large number of columns, to avoid enumerating the various column names you can choose all columns using the Generate Query with * option.
  3. Optional: In the SQL Editor view, modify the SQL to select only the data that you want to access. Any ANSI compliant SQL is acceptable.
  4. To view or test the data that the SQL statement returns, right-click the highlighted SELECT statement and click either Execute SQL to view results in the SQL Results view, or Execute SQL and File results to save the results in a .csv file.
  5. Optional: To create a virtual view of the SQL, highlight the SELECT statement, right-click and select Create a virtual view.

Results

In the SQL Results view:
  • Double-click a row to view additional details about that row.
  • Select the Export Result Set view option to export the SQL results to a .csv file.
  • Click SQL Messages to view query-related system messages.

By default, if a result set includes 25 or more columns, each set of 25 columns are displayed incrementally as groups. You can choose which group you want to view using the Columns Group field. You can set the number of columns that you want to include in each group, ranging from 25-200, in the Columns per group field.

To change how SQL results display in the SQL Results view, see Data Virtualization Manager preferences.

What to do next

After the SQL statement is generated, you can perform any of the following tasks:
  • Modify the SQL to meet your needs
  • Execute the SQL to test and view the resulting data
  • Create virtual views to join data or include missing columns
  • Generate a SQL class to get access to data from your programs or applications