SQL power tools

Generate SQL (DDL), run whole scripts, explain query performance, SQL history and saved queries.

  1. SQL power tools
  2. Explain SQL (performance)

SQL power tools

  • Generate SQL (DDL): right-click a file → Generate SQL (DDL), or run the command and type LIB/OBJECT. Works for tables, physical and logical files, views, indexes, procedures and functions. The CREATE statement opens in a new SQL editor. Needs a Mapepire SQL engine.
  • Run SQL Script: Ctrl+Shift+Enter in a SQL editor runs every statement in the file (or the selection) one after another. Each statement shows ✔ or ✖, its time, its rows or row count, or its error. It stops at the first error unless you turn off vanthrex.sql.scriptStopOnError. It asks once before running destructive statements.
  • SQL history: every statement you run is remembered (last 50). SQL History… lets you run it again, open it, insert it at the cursor or copy it. Click ☆ to save it.
  • Saved queries: in a SQL editor, right-click → Save Query… and give it a name. Saved Queries… lists them for running or inserting; the 🗑 button deletes one.

Explain SQL (performance)

What it is: A Visual Explain-style summary of a query. New in 0.6.

You see Meaning
Table scans and why Every row of a table was read — for example no index exists
Indexes used The indexes or logical files the optimizer chose
Temporary indexes Indexes Db2 had to build while running the query
Sorts and temporary results Extra work for ORDER BY / GROUP BY
Advised indexes Indexes the optimizer recommends for this query, plus the index advisor history of the tables
Advice Plain-language tips

How: Put the cursor in a SELECT (or select it) in a SQL editor → Explain SQL (Performance) in the editor title or the right-click menu. Vanthrex runs the query (first 100 rows) under a database monitor (STRDBMON) in your SQL job and shows the report. Click an advised index to get a CREATE INDEX statement to review, rename and run. Only queries can be explained, and it needs a Mapepire SQL engine.