The
SET SERVER OPTION statement specifies a server option setting that
is to remain in effect while a user or application is connected to
the federated database. When the connection ends, this server option's
previous setting is reinstated.
This statement is not under transaction control.
Invocation
This statement can be issued
interactively. It is an executable statement that can be dynamically
prepared.
Authorization
None required.
Syntax
>>-SET SERVER OPTION--server-option-name--TO--string-constant--->
>--FOR--SERVER--server-name------------------------------------><
Description
- server-option-name
- Names the server option that is to be set.
- TO string-constant
- Specifies the setting for server-option-name as
a character string constant.
- SERVER server-name
- Names the data source to which server-option-name applies.
It must be a server described in the catalog.
Notes
- Server option names can be entered in uppercase or lowercase.
- One or more SET SERVER OPTION statements can be submitted when
a user or application connects to the federated database. The statement
(or statements) must be specified at the start of the first unit of
work that is processed after the connection is established.
- SYSCAT.SERVEROPTIONS will not be updated based on a SET SERVER
OPTION statement, because this change only affects the current connection.
- For static SQL, using the SET SERVER OPTION statement affects
only the execution of the static SQL statement. Using the SET SERVER
OPTION statement has no effect on the plans that are generated by
the optimizer.
Examples
- Example 1: An Oracle data source called ORASERV is defined
to a federated database called DJDB. ORASERV is configured to disallow
plan hints. However, the DBA would like plan hints to be enabled for
a test run of a new application. When the run is over, plan hints
will be disallowed again.
CONNECT TO DJDB;
strcpy(stmt,"set server option plan_hints to 'Y' for server oraserv");
EXEC SQL EXECUTE IMMEDIATE :stmt;
strcpy(stmt,"select c1 from ora_t1 where c1 > 100"); /*Generate plan hints*/
EXEC SQL PREPARE s1 FROM :stmt;
EXEC SQL DECLARE c1 CURSOR FOR s1;
EXEC SQL OPEN c1;
EXEC SQL FETCH c1 INTO :hv;
- Example 2: You have set the server option PASSWORD to 'Y'
(validating passwords at the data source) for all Oracle 8 data sources.
However, for a particular session in which an application is connected
to the federated database in order to access a specific Oracle 8 data
source-one defined to the federated database DJDB as ORA8A-passwords
will not need to be validated.
CONNECT TO DJDB;
strcpy(stmt,"set server option password to 'N' for server ora8a");
EXEC SQL PREPARE STMT_NAME FROM :stmt;
EXEC SQL EXECUTE STMT_NAME FROM :stmt;
strcpy(stmt,"select max(c1) from ora8a_t1");
EXEC SQL PREPARE STMT_NAME FROM :stmt;
EXEC SQL DECLARE c1 CURSOR FOR STMT_NAME;
EXEC SQL OPEN c1; /*Does not validate password at ora8a*/
EXEC SQL FETCH c1 INTO :hv;