SQL Guide : solidDB® SQL statements : SET : SET SQL
  
SET SQL
SET SQL INFO {ON | OFF} [FILE {file_name |
   "{file_name" | '{file_name'}]
[LEVEL info_level] SET SQL SORTARRAYSIZE {array-size | DEFAULT} SET SQL JOINPATHSPAN {path-span | DEFAULT} SET SQL CONVERTORSTOUNIONS
{YES [COUNT value] | NO | DEFAULT}
Usage
All the settings are read per user session (unlike the settings in the solid.ini file, which are automatically read each time solidDB® is started).
SET SQL INFO
The SET SQL INFO statement controls the SQL Info facility which outputs trace information about each SQL statement processed by solidDB®. You can use the SQL Info facility to provide information about an SQL statement to troubleshoot problems or tune queries.
The default file is a global soltrace.out shared by all users. If the file name is given, all future SET SQL INFO ON settings will use that file unless a new file is set. Use single quotation marks when specifying the file name. Without single quotation marks, the file name is converted to uppercase.
The output is appended to an existing file. The file is never truncated. After you no longer need the SQL INFO file, delete the file manually. If the file open fails, the info output is silently discarded.
The tracing level (info_level) is defined as an integer between 0 (no tracing) and 8 (solidDB® info from every fetched row).
Info level
Description
0
no output
1
table, index, and view info in SQL format
2
SQL execution graphs (technical support use only)
3
some SQL estimate info, solidDB® selected key name
4
all SQL estimate info, solidDB® selected key info
5
solidDB® info also from discarded keys
6
solidDB® table level info
7
SQL info from every fetched row
8
solidDB® info from every fetched row
A good way to generate useful info output is to set info on with a new file name and then execute the SQL statement using EXPLAIN PLAN FOR syntax. This method gives all necessary estimator information but does not generate output from the fetches (which might generate a large output file).
Tip: The default behavior of the SQL Info facility is controlled with the SQL.Info and SQL.InfoFileName parameters.
SET SQL SORTARRAYSIZE
The SET SQL SORTARRAYSIZE statement sets the size of the array that SQL uses when ordering the result set of a query. The units are rows. For example, if you specify a value of 1000, the server creates an array large enough to sort 1000 rows.
SET SQL JOINPATHSPAN
This command is obsolete. The syntax is accepted, but the command has no effect.
SET SQL CONVERTORSTOUNIONS
The SET SQL CONVERTORSTOUNIONS statement controls whether a query that contains “OR” operations is converted into an equivalent query that uses “UNION” operations.
COUNT specifies the maximum number of OR operations that can be converted to UNION operations. The default value is 10, which is sufficient for most cases.
The following operations are logically equivalent:
select ... where x = 1 OR y = 1;
select ... where x = 1 UNION select... where y = 1;
By setting CONVERTORSTOUNIONS, you tell the optimizer that it may use equivalent UNION operations instead of OR operations if the UNIONs seem more efficient based on the volume and distribution of data. You can also specify CONVERTORSTOUNIONS with the SQL.ConvertOrsToUnionsCount parameter.
Example
SET SQL INFO ON FILE 'sqlinfo.txt' LEVEL 5
See also
SET