Use the SET SQL statement to specify a number of SQL-related settings.
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).
The SET SQL statement takes effect immediately, starting from the next SQL statement.Parameters, clauses, keywords, and variables
▪ 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. For more information, see SQL Info facility.
– ON | OFF: Turns SQL Info facility on or off.
– FILE: Specifies the name of the trace file. The default file is controlled by the SQL.InfoFileName parameter, see SQL section.
If a file name is specified, all future trace output is sent to that file until 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. After you no longer need the SQL INFO file, delete the file manually. If the file open fails, the info output is silently discarded.
– LEVEL: The tracing level as an integer between 0 and 8. The default level for the SQL Info facility is set with the SQL.Info parameter, see SQL section.
Levels are described in the following table:
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 specify a new trace file and then use the EXPLAIN PLAN FOR statement for an SQL query. This method gives all necessary estimator information but does not generate output from the fetches (which might generate a large output file).
▪ MAXSIZE: Specifies the maximum size of the trace file in bytes. Optionally, follow the value with K (to specify a value in KB), M (to specify a value in MB), or G (to specify a value in GB). For example:
SET SQL INFO ON FILE trace.out MAXSIZE 300M
Note The default maximum size of the trace file is configured by using the SQL.InfoFileSize parameter, see SQL section.
▪ SORTARRAYSIZE: 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.
▪ JOINPATHSPAN: Obsolete; the syntax is accepted, but the statement has no effect.
▪ CONVERTORSTOUNIONS: 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 can 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, see SQL section.