Customizing the Oracle Environment

In this section:

The Adapter for Oracle provides several parameters for customizing the environment and optimizing performance. This topic provides an overview of customization options.


Top of page

x
Choosing a Source for System Date and Time

How to:

When an SQL SELECT statement refers to CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, it will obtain the date or time value from the Server (the default), or from the Oracle DBMS. You can override the default using the new SET DATETIME_PROCESS option. This is effective only for reports using Automatic Passthru.



x
Syntax: How to Choose a Source for System Date and Time

To choose a source for system date and time, the syntax is

SQL SET DATETIME_PROCESS=SERVER|DBMS
END

where:

SERVER

Specifies that the date and time values will be taken from the Server. This is the default.

DBMS

Specifies that the date and time values will be taken from the Oracle DBMS.


Top of page

x
Designating a Default Tablespace

How to:

You can use the SET DBSPACE command to designate a default tablespace for tables you create. For the duration of the session, the adapter places these tables in the Oracle tablespace that you identify with the SET DBSPACE command. If the SET DBSPACE command is not used, Oracle uses the default tablespace for the connected user.



x
Syntax: How to Set DBSPACE
ENGINE SQLORA SET DBSPACE tablespace

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

tablespace

Is a valid tablespace in the database.

Note: This command will affect only CREATE FILE and HOLD FORMAT SQLORA requests issued by Table Services. It does not affect Passthru CREATE TABLE commands.


Top of page

x
Overriding Default Parameters for Index Space

How to:

You can use the SET IXSPACE command to override the default parameters for the index space implicitly created by the CREATE FILE and HOLD FORMAT commands.



x
Syntax: How to Set IXSPACE
ENGINE SQLORA SET IXSPACE 

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

index-spec

Is the portion of the Oracle CREATE INDEX statement that defines the parameters for the index. It can consist of up to 94 bytes of valid Oracle index space parameters. To reset the index space parameters to their default values, issue the SET IXSPACE command with no parameters.

Note: Refer to the Oracle documentation for more information on this command.

The long form of SQL Passthru syntax for commands exceeding one line is:

ENGINE SQLORA
SET IXSPACE index-spec 
END

For example, to specify the NOSORT, NOLOGGING, and TABLESPACE portions of the Oracle CREATE INDEX statement, enter the following commands:

ENGINE SQLORA
SET IXSPACE NOSORT NOLOGGING
TABLESPACE TEMP
END

Note: This command will only affect CREATE INDEX requests issued by CREATE FILE and HOLD FORMAT SQLORA commands. It does not affect Passthru CREATE INDEX commands, for example:

ENGINE SQLORA SET IXSPACE TABLESPACE tablespace_name 
TABLE FILE table_name 
PRINT *
ON TABLE HOLD AS file_name FORMAT SQLORA
END


Top of page

x
Activating NONBLOCK Mode

How to:

xThe Adapter for Oracle has the ability to issue calls in NONBLOCK mode. The default behavior is BLOCK mode.

This feature allows the adapter to react to a client request to cancel a query while the adapter is waiting on engine processing. This wait state usually occurs during SQL parsing, before the first row of an answer set is ready for delivery to the adapter or while waiting for access to an object that has been locked by another application.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Activate NONBLOCK Mode
ENGINE SQLORA SET NONBLOCK {0|n}

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is a positive numeric number. 0 is the default value, which means that the adapter will operate in BLOCK mode. A value of 1 or greater activates the NONBLOCK calling and specifies the time, in seconds, that the adapter will wait between each time it checks to see if the:

  • Query has been executed.
  • Client application has requested the cancellation of a query.
  • Kill Session button on the Web Console is pressed.

Note: A value of 1 or 2 should be sufficient for normal operations.


Top of page

x
Obtaining the Number of Rows Updated or Deleted

How to:

xPASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Obtain the Number of Rows Updated or Deleted
ENGINE SQLORA SET PASSRECS {ON|OFF}

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.

OFF

Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.


Top of page

x
Specifying the Maximum Number of Parameters for Stored Procedures

How to:

You can use the SET SPMAXPRM command to specify the maximum number of input parameters that can be associated with any Oracle stored procedure.



x
Syntax: How to Set SPMAXPRM
ENGINE SQLORA SET SPMAXPRM nnn

where:

SQLORA

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

nnn

Is the maximum number of parameters that can be passed to any stored procedure available to be run in this client session. 256 is the default value.


iWay Software