Statements that set options

The SET OPTION command enables you to set SQL options. There are two ways to set these: by including them in a SQL statement run from the third‑party application that accesses Synergy data, or by including them in a query processing options file (a text file that the GENESIS_INITSQL environment variable is set to). (There is an exception: xfODBC ignores TMPINDEX settings in query processing options files.) For more information, see Creating a file for query processing options.

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_INTITSQL, 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.

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.

Notes on DATETIME

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.

Notes on MERGESIZE

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.)

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:

Notes on PLAN

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

Look at the KEYNUM value for TAG_KEY (which is 1). This value is the key of reference in the Synergy ISAM file.

Notes on SORTPAGES

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 Manger) does not decrease while vtxnetd is running.