SET SERVER OPTION statement

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

Read syntax diagramSkip visual syntax diagramSET SERVER OPTIONserver-option-name TOstring-constantFORSERVERserver-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;