The SET OPTION command enables you to set SQL options. There are two ways to set these:
- By including them in an SQL statement run from the third-party application that accesses Synergy data. When run from an application, these commands should be executed separately (one at a time). For example, if you want to set the following from an application, be sure to use two separate operations:
SET OPTION LOGFILE 'C:\temp\myplan'
SET OPTION PLAN ON
- By including them in a query processing options file (i.e., a text file that the GENESIS_INITSQL environment variable is set to). See Creating a file for query processing options for more information. And note that there is an exception to this: xfODBC ignores TMPINDEX settings in query processing options files.
For links to more information on xfODBC’s support for SQL, see Appendix B: SQL Support.
SET OPTION
The SET OPTION command sets SQL options.
SET OPTION option_type param1
or
SET OPTION option_type param1 param2
where option_type, param1, and param2 are the following:
|
SQL Options |
|||
|---|---|---|---|
|
Option_type |
Param1 |
Param2 |
Description |
|
COMPSORT |
ON | OFF |
|
Sets sort page compression. By default COMPSORT is ON. |
|
DATETIME |
[n] format_str |
|
Enables you to modify the conversion masks used to interpret dates and times that are part of SQL statements. See Notes on DATETIME. |
|
ERROR |
ON | OFF |
|
Records internal error information for use by Synergy/DE Developer Support. To use this, LOGFILE must also be set. By default ERROR is OFF. |
|
EXPR |
ON | OFF |
|
Records internal expression information for use by Synergy/DE Developer Support. To use this option, LOGFILE must also be set. By default EXPR is OFF. |
|
HASH |
ON | OFF |
|
Records internal hash information for use by Synergy/DE Developer Support. To use this, LOGFILE must also be set. By default HASH is OFF. |
|
HEAPBLOCKSIZE |
bytes |
|
Sets the minimum heap block size (in bytes) used to allocate memory. (Larger blocks may be allocated.) This is set to 32768 by default. We don’t recommend changing this, but you can set it to any value from 0 to 1000000 (inclusive). Larger sizes require less CPU overhead but may result in excessive memory use. If you set this to zero, heap blocks are allocated in the exact sizes needed. |
|
LOGFILE |
‘filename’ |
|
Sets the name and location of the debugging log file. Generally this file is used in conjunction with PLAN to record information on the index(es) used for a query. The other logging options (ERROR, EXPR, HASH, TRACE, and TREE) are for use by Synergy/DE Developer Support. Note that if this is set in a file specified by GENESIS_INITSQL, and that environment variable is set in a DSN, logging will be limited to a single connection (a connection made with the DSN). |
|
MAXOPTLOOP |
max_combos |
|
Limits the number of ways the query optimizer will try to optimize a query that has a multi-table join. Setting this option may enable you to reduce the time it takes to process the query. By default, max_combos is set to 100, which limits the query optimizer to trying 100 configurations. You can, however, set it to any positive number, or you can set it to 0, which turns off this feature, allowing the optimizer to try any number of combinations. |
|
MERGESIZE |
max_rows |
|
(Windows only) Optimizes SELECT statements that have one or more OR clauses and one or more AND clauses. See Notes on MERGESIZE. |
|
OPTIMIZE |
ON | OFF |
|
Enables you to control whether or not optimization is used. By default OPTIMIZE is ON. |
|
PLAN |
ON | OFF |
|
Records indexes used for a query. To use this option, LOGFILE must also be set. By default PLAN is OFF. See Notes on PLAN. |
|
PREOPT |
ON | OFF |
|
Optimizes SELECT statements that have an IN clause or an OR clause that is part of an AND clause. For example: SELECT myfield FROM mytable WHERE id=1 AND num IN (48,49,50) or SELECT myfield FROM mytable WHERE id=1 AND (num=48 OR num=49 OR num=50) When set to ON (the default), the fields on both sides of the AND clause (id and num in the example) are included in the key to optimize the statement. When set to OFF, the field for the IN or OR side of the AND clause is omitted from the key. |
|
SORTPAGES |
totalpages |
mempages |
Sets the amount of disk and memory storage used for sort operations for subsequently opened cursors. See Notes on SORTPAGES. |
| TIMEOUT | seconds |
The number of seconds to wait for the first record in the result set to be returned. If no record is returned before the time specified by seconds elapses, a “Fetch timed out” error is returned. Once the first record is returned, the timeout is canceled for the query. If this option is not set or if seconds is 0, no timeout is set for the query. |
|
|
TMPINDEX |
ON | OFF |
|
Enables or disables temporary indexes for inner joins. (Temporary indexes are used only for inner joins.) By default, TMPINDEX is on. Note that this option will be ignored if it’s in a query processing options file (a file specified by the GENESIS_INITSQL environment variable). |
|
TRACE |
ON | OFF |
|
Records internal trace information for use by Synergy/DE Developer Support. To use this, LOGFILE must also be set. By default TRACE is OFF. |
|
TREE |
ON | OFF |
|
Records internal tree information for use by Synergy/DE Developer Support. To use this, LOGFILE must also be set. By default TREE is OFF. |
The following are some example settings:
SET OPTION DATETIME 0 'DD-MM-YYYY HH:MI' SET OPTION DATETIME 1 'DD-MM-YYYY' SET OPTION DATETIME 2 'HH:MI' SET OPTION LOGFILE 'mylogfile' SET OPTION MAXOPTLOOP 100 SET OPTION MERGESIZE 0 SET OPTION TRACE ON SET OPTION PLAN ON SET OPTION SORTPAGES 8000 2000 SET OPTION TMPINDEX ON
Note that if you use SDMS logging as you run SET OPTION commands, you’ll see begintx/endtx pairs in the log file. This is the correct and expected behavior.
This option enables you to modify the conversion masks used to interpret dates and times that are part of SQL statements. Dates and times in SQL statements are strings and must be converted to the xfODBC driver’s internal format. This command works with SQL Connection when using the Synergy Database driver (VTX4), and it works with xfODBC. (This option is ignored, however, if you put it in a query processing options file specified by the GENESIS_INITSQL environment variable.)
There are four masks. By default they are set to the following:
|
0 |
YYYY-MM-DD HH:MI:SS |
|
1 |
YYYY-MM-DD |
|
2 |
HH:MI:SS |
|
3 |
YYYY-MM-DD HH:MI:SS.UUUUUU |
The xfODBC driver attempts to use the 0 mask first. If this doesn’t match the data, it tries the 1 mask. If that doesn’t match, it tries the 2 mask, and finally it tries the 3 mask.
To modify one of these masks, use the n parameter to specify which mask to set. If you don’t specify the n parameter, the 0 mask is set by default.
For information on the characters you can include in the format_str string, see the date masks listed in TO_CHAR.
For information on setting the xfODBC display format for dates, see Configuring Data Access.
This optimizes SELECT statements that have one or more OR clauses by evaluating each side of each OR clause as a separate SELECT statement and then combining the results (in a multimerge operation). This works only when keys are available to optimize each side of each OR clause. And note that in some cases, this feature could impair performance. The max_rows argument specifies the maximum number of rows that can be returned when a statement is optimized with multimerge.
Max_rows must be either 0 (which turns this feature off) or a positive numeric value from 100 to 500000. (Anything from 1 to 99 will cause a runtime error.)
- If the result set of an optimized statement is larger than max_rows, an error is generated.
- If the statement is not optimized with multimerge, the max_rows limit doesn’t apply.
By default MERGESIZE is enabled (the default setting is 10000) because some applications automatically generate the kind of statements that MERGSIZE optimizes. For example, if you use Microsoft Access to issue a query that selects all the columns in a table, Access generates a SELECT statement with a series of OR clauses that repeatedly specify key segments.
Note the following:
- With each row allowed by the max_rows value, xfODBC uses six bytes of memory, so setting max_rows to a large value doesn’t generally affect performance.
- You can also set this option for a DSN by setting the “Max number of rows” field in the xfODBC Setup dialog box, the dialog box that enables you to add and configure xfODBC DSNs. For more information, see Adding a user or system DSN.
To find out which indexes are used for a query, use SET OPTION PLAN in conjunction with SET OPTION LOGFILE. This generates a log file that includes a “Pushed key#” line that lists the index used to optimize the query, and it includes a table that lists index information. For example, if you create a query for the sample database (distributed with Connectivity Series) so that it selects rows from the VENDORS table where VEND_RTYPE = 1 and VEND_KEY < 44, your log file will contain something like the following:
PUBLIC.VENDORS has 4 keys, Table/Buffer: 0/0 Key NKC Unq Nul Col/Dty/Dir/Name --- --- --- --- ----------------------------------------- 0 1 Y N 0/2/A/VEND_KEY 1 1 Y N 0/2/A/VEND_KEY 2 2 Y N 1/2/A/VEND_RTYPE 0/2/A/VEND_KEY 3 1 Y N 11/98/D/ROWID Execution plan -------------- Tables ........... 1 Keys used ........ 1 Columns pushed ... 2 ANDs not pushed .. 0 Plan chosen ...... 3 of 4 Fetch node for table: 'PUBLIC.VENDORS' Cursor# ......... 2 Buffer# ......... 0 Table# .......... 0 Pushed key# ..... 2, Col/Dty/Dir/Name: 1/2/A/VEND_RTYPE 0/2/A/VEND_KEY KeySeg 0 oper .. = Constant ...... dty: 2, flg: 0, len: 2, 1 KeySeg 1 oper .. < Constant ...... dty: 2, flg: 0, len: 2, 44
In this case, the index used for the query (listed on the “Pushed key#” line) is 2. To find out what this is, look for “2” in the Key column of the table. The KeySeg lines under “Pushed key#” list segment information for the index and correspond to the segments listed on the “Pushed key#” line. The first segment listed on this line is considered segment 0, the second is considered segment 1, and so on. So, for the example above, KeySeg 0 refers to VEND_RTYPE, and KeySeg 1 refers to VEND_KEY.
Note that you can also use this information to get key of reference (KRF) information. To do this, you’ll need to run the following query:
SELECT a.i_table, a.i_name, a.i_type, a.columns, a.keynum,
b.x_name, b.x_position
FROM genesis_indexes a, genesis_xcolumns b
WHERE (a.i_name=b.x_index)
AND (a.i_table=b.x_table)
AND (a.i_owner=b.x_owner)
AND (a.i_database=b.x_database)
AND (a.i_table='table_name')
ORDER BY 5, 2, 7
where table_name is the case-sensitive name of the table used in the query. Then count down the rows in the result set (starting with 0 for the first row) until you get to the number listed in as the Pushed key#. Then, for the corresponding key of reference in the Synergy ISAM file, look at the KEYNUM value for the row.
For our example query, the index number is 2, but what’s the KRF? To find out, run the query documented above, replacing table_name with VENDORS (use all capital letters). This should return the following:
I_TABLE I_NAME I_TYPE COLUMNS KEYNUM X_NAME X_POSITION VENDORS KEY0 U 1 0 VEND_KEY 0 VENDORS $_VTX_TAG_VIX_0001 U 1 1 VEND_KEY 0 VENDORS TAG_KEY U 2 1 VEND_RTYPE 0 VENDORS TAG_KEY U 2 1 VEND_KEY 1
Now count down the rows, starting from 0 and ending with the number listed as Pushed key#, which is 2. For our example, count
- the KEY0 row as 0.
- the $_VTX_TAG_VIX_0001 row as 1.
- the first TAG_KEY row as 2 (there are two returned rows for TAG_KEY, one for each segment).
Look at the KEYNUM value for TAG_KEY (which is 1). This value is the key of reference in the Synergy ISAM file.
SORTPAGES sets the amount of disk and memory storage used for sort operations for subsequently opened cursors. This overrides the “Total” and “In memory” DSN settings (see Adding a user or system DSN).
The totalpages argument sets the number of pages to use, and mempages is the number of these pages kept in memory. (Pages are 4,096-byte blocks.) Totalpages must be greater than or equal to mempages. The default value for totalpages is 10000, and the default value for mempages is 1000. Note that memory for SORTPAGES is allocated for every subsequently opened cursor even if no sort is performed for a cursor.
You can set SORTPAGES any time after connecting, but once it is set, the specified memory is allocated until SORTPAGES is reset or a cursor is closed. An application uses the sum of the memory specified for all concurrently open cursors.
On Windows, vtxnetd uses the sum of memory specified by SORTPAGES for every open cursor for every connected application. Though heap memory is freed for reuse when a cursor closes, the memory used for the vtxnetd process (reported as private bytes in Task Manager) does not decrease while vtxnetd is running.
