The EXPLAIN pragma is used to output the access plan selected by the optimizer. You can only use this pragma if the current authorization identifier has the special privilege UTILITY.
This pragma is only effective in the following SQL statements:
CALL
cursor description (for dynamic cursors)
DECLARE CURSOR (for a static cursor)
DELETE
INSERT
MERGE
SELECT
UPDATE
In routines, the pragma is ignored, see section "Pragmas in routines".
This pragma is only effective in a static statement if you precompile the program while the database is online.
EXPLAIN INTO file
file
Name of the SAM file into which the explanation is to be output. If the file already exists, the explanation is appended to the file.
If file includes a BS2000 user ID, this user ID is used. If not, the ID of the Data Base Handler for the database referenced in the SQL statement is used. In both cases the DBH must have write permission for the file. You specify an alphanumeric literal for file. No lowercase letters should be contained in this.
In the case of dynamic statements, the explanation is output when the PREPARE statement or EXECUTE IMMEDIATE statement is executed. For static statements, the explanation is output during precompilation.
The explanation comprises the SQL statement and an edited representation of the access plan. The representation of access plans is described in the “ Performance” manual.
You can display the contents of the file with SHOW-FILE. If you want to read the file with EDT, you must enter the following command:ADD-FILE-LINK LINK-NAME=EDTSAM,FILE-NAME=file,...,BUFFER-LENGTH=(STD,2),...
In the EDT you can also enter: @OPEN F=file,TYPE=CATALOG