Using the Design Studio VQL Shell¶

With the VQL Shell, you can execute any VQL statement, including queries and DDL statements (CREATE VIEW, DROP VIEW, etc.). To execute several statements at once, separate them with ;. You can enter them or load them from a file with the button “Load”.

The VQL Shell has several controls, many of them self-explanatory. This list contains the ones that need some clarification:

• Execute. Executes all the queries of the box. To execute only part of what you entered in the queries box, select that part and press Ctrl+Enter.

• Load. Loads the content of a file into the commands box. To load a VQL file that creates several databases, do it from the “Import” wizard on the File menu instead of from the VQL Shell. The reason is that the “Import” wizard is capable of importing correctly elements of a database that depend on elements of another database.

• Limit displayed rows. The VQL Shell only displays the first N rows of the result.

• Retrieve all rows. If selected, the VQL Shell does not display more rows than Limit displayed rows, but it process the entire result set. If cleared, the VQL Shell adds the clause LIMIT to the query behind the scenes. E.g. if Limit displayed rows is set to 100 and you execute the query SELECT * FROM sales, the VQL Shell sends the query SELECT * FROM sales LIMIT 100 so the query will finish after returning 100 rows.

  Selecting this check box is useful to:

  • Estimate how much time it will take for a client application to execute this query.

  • If you are loading the cache of a view from the Execute dialog or the VQL Shell, you have to select this check box. Otherwise, the cache will not be loaded correctly (see the explanation below).

  Important

  Clearing the check box Retrieve all rows affects the queries that load the cache of a view because the query is interrupted before it finishes:

  • When querying a view with cache mode Partial, the cache of the query will not be loaded.

  • When querying a view with cache mode Full and you select Store results in cache ('cache_preload'='true'), the maximum number of rows stored in the cache is the value of Limit displayed rows.

  • When executing the command CREATE REMOTE TABLE, the maximum number of rows inserted in the remote table is the value of Limit displayed rows.

  If you select Retrieve all rows, the queries that load the cache will work as expected.

When executing a query, the VQL Shell will show the Query Results tab. In this tab you can do the following:

• Click Save to store the execution trace of the query. The generated zip file can be opened later in Tools > Trace viewer.

• See the Execution Trace of the query. The section Execution Trace of a Statement explains the elements displayed in the execution trace of a query.

The VQL Shell includes a “command log” that saves the last VQL statements run. This command log is maintained only for that specific shell. You can see it in the tab Execution Log.

You can use the Server Explorer to speed up writing VQL statements (they open a new VQL Shell):

• Right-click on a view and click VQL Shell > Select… to add the statement SELECT <fields of the view> FROM <view name>.

• Right-click on any element and click VQL Shell > Desc… to add the statement DESC VQL <element type> <element name>.