SQL preferences

Use SQL preferences to specify settings related to SQL query generation, the SQL Results view, and SQL metadata retrieval.

SQL preferences are identified and described in the table that follows.

Field Description
SQL Generate Query Behavior Determines whether you are prompted to execute SQL or if SQL executes automatically. Options include:
  • Generate query and issue user prompt. This is the default setting.
  • Generate and execute query (no prompt)
  • Generate query but do not execute query (no prompt)
SQL Results Max Rows Maximum number of rows to return in the SQL Results view. The default value is 1000.
SQL Results Max Bytes Maximum number of data bytes to return in the SQL Results view. The default value is 1000000.
SQL Results values accessed as Specifies how data values are returned. Options include String or Object. The default setting is String.
DB2 CARDINALITY in generated UDTF query Specifies the cardinality value for the CARDINALITY clause that is included in generated UDTF queries. Using the CARDINALITY clause can improve the performance of queries with UDTF references. The default value is 10000. Specifying 0 omits the CARDINALITY clause from the generated query.
Use prepared statement to retrieve SQL column info for DB2 or DRDA tables The Data Virtualization Manager studio obtains column metadata information from the server for Db2 and DRDA tables and views when you expand a table or view node under the Other Subsystems tree in the Server view, or in other situations where column information needs to be retrieved.

The Data Virtualization Manager studio supports two different ways of retrieving this column metadata information:

  • Using a prepared statement. Typically, this server call will be faster; however, this option requires that the user have SELECT privileges to the table in the remote database. This method is the default and will be used when this preference is selected.
  • Using the JDBC getColumns() API. This method is the more conventional approach; however, in some cases (for example, Oracle), the remote DRDA subsystem may take a long time to process the metadata query. This method will be used when this preference is cleared.
Fetch primary key and index information for virtual tables If this preference is selected, then when you expand a virtual table or view in the Server view, any primary key or indexed column nodes will be identified. This identification process requires the Data Virtualization Manager studio to make additional metadata calls to the server. To disable these calls and the associated identifications, you can clear this preference and thus speed up the time taken to populate the column nodes. This preference is selected by default.
Fetch primary key and index information for DB2 or DRDA tables If this preference is selected, then when you expand a table or view node under the Other Subsystems tree in the Server view, any primary key or indexed column nodes will be identified. This identification process requires the Data Virtualization Manager studio to make additional metadata calls to the server (and subsequently to the remote database). In some cases, these additional calls may be rather expensive (for example, Oracle). To disable these calls and the associated identifications, you can clear this preference to speed up the time taken to populate the column nodes. This preference is cleared by default.