Pragmas and annotations

Pragmas and annotations are special SQL comments which are interpreted by SESAM/SQL. You can use them to provide information for the execution of SQL or utility statements. Pragmas and annotations containing syntax errors are treated as comments and ignored by SESAM/SQL.

A pragma can only be contained at the start of an SQL or utility statement. They may be preceded only by comments (including further pragmas) and delimiters. Pragmas have an effect on the entire statement, including the views used. The PREFETCH pragma even has an effect on all operators with a cursor.

An annotation can only be contained at certain positions in the text of a statement. Irrespective of its position, it has an effect only on one particular operation in the statement. Only one annotation can ever be contained at each of these positions. However, a statement can contain multiple annotations and also the views used.

Pragmas and annotations have an effect only in the case of particular sets of statements, otherwise they are ignored. For information on using pragmas in routines, see section "Pragmas in routines".

Pragmas and annotations are used for different purposes. They are described in various SESAM/SQL manuals, see the tables on the following pages.

Format

---

pragma ::= --%PRAGMA pragma_text ,... end_of_line

annotation ::= /*% annotation_text %*/

---

pragma_text

A string of keywords, literals and names.
The string may contain blanks but no other delimiters.

The formats for pragma_text and its effect are described in the places specified in the table below:

pragma_text begins with	Meaning	For description see
AUTONOMOUS TRANSACTION	Write data independently of the surrounding transaction	"AUTONOMOUS TRANSACTION pragma"
CHECK	Observe integrity constraints	“ SQL Reference Manual Part 2: Utilities” manual
DATA TYPE	Use old CALL-DML types	"DATA TYPE pragma"
DEBUG ROUTINE	Receive error information for routines	"DEBUG ROUTINE pragma"
DEBUG VALUE	Receive information for assignments in routines	"DEBUG VALUE pragma"
EXPLAIN	Output access plan	"EXPLAIN pragma"
IGNORE	Ignore index	“ Performance” manual
ISOLATION LEVEL	Define isolation level	"ISOLATION LEVEL pragma"
JOIN	Select join method	“ Performance” manual
KEEP JOIN ORDER	Retain join order	“ Performance” manual
LIMIT ABORT_EXECUTION	Limit resource utilization	"LIMIT ABORT_EXECUTION pragma"
LOCK MODE	Set lock mode	"LOCK MODE pragma"
LOOP LIMIT	Limit number of loop passes	"LOOP LIMIT pragma"
OPTIMIZATION	Restrict access planning	“ Performance” manual
PREFETCH	Control block mode	"PREFETCH pragma"
SIMPLIFICATION	Control optimization techniques	“ Performance” manual
USE	Use index	“ Performance” manual
UTILITY MODE	Control transaction management	"UTILITY MODE pragma"

Table 8: pragmas

When you specify more than one pragma beginning with the same keyword in a statement, the last one specified is used. However, regardless of their order the IGNORE and USE pragmas are interpreted according to special rules.

end_of_line

New line in the SQL source text.
When the SQL text is specified as a string in a PREPARE or EXECUTE IMMEDIATE statement, the alphanumeric character X'15' in this string means new line.

annotation_text

A string of keywords.
The string may contain blanks and new lines, but no comments.

An annotation must follow a keyword. Only blanks and new lines may be contained between these, but no comments. The preceding keyword determines the permitted format of annotation_text and the effect of the annotation. An annotation which does not comply with these rules is regarded as a comment and ignored.

The formats for annotation_text and its effects are described in the place specified in the table below:

Annotation after keyword	Meaning	For description see
JOIN	Select join algorithm	“ Performance” manual
CACHE	Cache CSV file in temporary file	“ Performance” manual
VOLATILE	Always calculate function value anew	"Uncorrelated function calls"
IMMUTABLE	Do not calculate function value anew in uncorrelated function calls	"Uncorrelated function calls"

Table 9: Annotations

If a pragma and an annotation would have different effects on an operation in a statement (e.g. selection of different Join algorithm), the annotation normally has priority. The description of the annotation contains the details.