SQL Reference

SET SERVER OPTION

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

The authorization ID of the statement must have either SYSADM or DBADM authority on the federated database.

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. Refer toServer Options for descriptions of the server options.

TO string-constant

Specifies the setting for server-option-name as a character string constant. Refer to Server Options for descriptions of possible settings.

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.
• SET SERVER OPTION currently only supports the password, fold_id, and fold_pw server options.
• 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.

Examples

Example 1:  An Oracle data source called RATCHIT is defined to a federated database called DJDB. RATCHIT 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 ratchit");
   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' (yes, validate 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;