Chapter 2: Configuring and Operating DirectConnect for Oracle

Service-specific parameters

The properties that are not global can be specified for any service. However, if a service-specific keyword is not defined, the default value applies. Additional services after the primary service do not inherit service-specific parameters from the primary service. These parameters are used with sp_configure.

The following table lists the service-specific parameters:

**Table 2-3: Service-specific parameters for configuration file**
Keyword	Description	Default
autocommit	Gives transactional control in passthrough mode.	1
array_size	Specifies the number of rows retrieved with a single fetch.	50
connect_string	Defines a connection to the target Oracle database.
csp_uppercase	Assigns uppercase object names when executing catalog stored procedures.	1
date_format	Applies the syntax for inserting dates in passthrough mode.	MON DD YYYY HH:MI:SSAM
timestamp_ms_support	Specifies whether the timestamp_format is used.	0
timestamp_format	Specifies the timestamp_format used in SQL statements.	MON DD YYYY HH:MI:SSAM
default_precision	Specifies the Sybase precision when an Oracle number datatype is defined without precision or scale.	38
default_scale	Specifies the Sybase scale when an Oracle number datatype is defined without precision or scale.	0
insert_array_size	Specifies the number of rows to batch before issuing an insert.	50
number_mode	Determines the behavior for converting Oracle data with a datatype number.	2
session_time_limit	Specifies the maximum length of time a 2pc transaction can be inactive before it is automatically aborted by the Oracle system. The unit for this time limit is in seconds.	60
traceflags	Controls the types of messages written to the log file.
triggers	For internal use only.	0
two_phase_commit	Enables the two-phase commit feature.	0

autocommit

This configuration parameter gives the client application transactional control in passthrough mode. If autocommit = 0 in the configuration file, the client application has transactional control in passthrough mode. If autocommit = 1 then Direct Connect for Oracle autocommits each SQL statement from the client application:

Example 7

autocommit = 1

This parameter is dynamic.

array_size

This sets the number of rows retrieved with a single fetch. It defaults to 50.

This parameter is dynamic.

connect_string

The connect_string parameter defines a connection to the target Oracle database. Each service defines a connect_string.

Oracle provides a way of connecting to a target Oracle database using SQLNET8. It searches for the tnsnames.ora file in the $SYBASE/$SYBASE_DCO/network/admin directory.

For this, you need to have an entry in the tnsnames.ora file describing the instance to which you will connect. In the following form:

DCSERVER.world = 
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL=TCP) (HOST = dixville) (PORT 				= 1521))
		(CONNECT_DATA =
			(SERVICE_NAME = DCSERVER.world)
		)
	)

where:

ORA_HOST is the name of the host running Oracle RDBMS. ORACLE_SRV is the SID of the RDBMS you intend to connect to. It is analogous to the subnetwork name: PORT is the socket number on which the TNS Listener is listening for connection requests.
ORACLE_SERVER is the alias used to connect to the Oracle RDBMS. The alias name can be any logical name. It is the same as the server name in Sybase terminology. You can have multiple entries for different servers in tnsnames.ora, just as in the Sybase sql_ini file.

In this case, the connect string required by DirectConnect for Oracle is ORACLE_SERVER.

This is used when defining the connect_string parameter in the configuration file:

Example 8

connect_string = ORACLE_SERVER

This parameter is dynamic.

csp_uppercase

Before querying Oracle, this converts names to uppercase in the catalog stored procedures sp_tables, sp_columns, sp_statistics, and sp_stored_procedures.

This parameter is dynamic.

date_format

Specifies the date format used in SQL statements. This setting is ignored for connections from Adaptive Server Enterprise. The default date format is MON DD YYYY HH:MI:SSAM. See your Oracle documentation for more information on date format strings.

The syntax for date_format is:

date_format=Oracle_Date_Format_String

This parameter is dynamic.

timestamp_ms_support

Specifies whether the timestamp_format is used in SQL statements. A value of 1 indicates that the timestamp_format is used, and a value of 0 (default) indicates it is not used.

This parameter is dynamic.

timestamp_format

Specifies the timestamp format used in SQL statements. This setting is ignored for connections from Adaptive Server Enterprise. The timestamp format depends on the value of the timestamp_ms_support:

If the value = 0, the default timestamp format is: MON DD YYYY HH:MI:SSAM
If the value = 1, the timestamp format is: MON DD YYYY HH:MI:SS.FFAM

This parameter is dynamic.

default_precision

Specifies the Sybase precision when an Oracle number datatype is defined without precision or scale. This parameter affects proxy table datatype mappings and sp_columns results.

A distinct default_precision setting can be defined for each service. Valid Sybase precision range is 1-38, with the default of 0.

This parameter is dynamic.

default_scale

Specifies the Sybase scale when an Oracle number datatype is defined without precision or scale. This parameter affects proxy table datatype mappings and sp_columns results.

A distinct default_precision setting can be defined for each service. Valid Sybase scale range is 0-38. The default value for this configuration parameter is 0.

This parameter is dynamic.

insert_array_size

This represents the number of rows that DirectConnect for Oracle will buffer before calling Oracle to insert them. Array inserts are performed when a bulk load event is sent to the DirectConnect for Oracle.

This parameter is dynamic.

number_mode

This determines the behavior for converting Oracle data with datatype number. A distinct number_mode setting can be defined for each service.

With a setting of 1, DirectConnect attempts to return data as tinyint, smallint, int, float or numeric. Overflows are possible.

A setting of 0 was used to support pre-system 10 servers. However, pre-system 10 servers are no longer supported by DirectConnect for Oracle, a setting of 2 causes all numbers to be returned as numeric. This setting gives the best performance and is the default.

The following table describes how data is returned, based on the number_mode parameter and scale value of the Oracle number datatype.

**Table 2-4: Rules for Oracle datatype number conversion**
Scale	number_mode = 0	number_mode = 1	number_mode = 2
scale = 0	If 0 <= precision <=9 Send back as shown in Table 2-5, else send back data as float.	If 0 <= precision <=9, send back as shown else if precision is valid, send back data as numeric, else send back data as float.	If precision is valid, send back data as numeric, else send back data as float.
scale !=0	Send back data as float.	If valid precision and scale, send back data as numeric, else send back data as float.	If valid precision and scale, send back data as numeric.

**Table 2-5: Conversion for Oracle datatype number**
Oracle datatype	Sybase datatype
number(1,0)	tinyint
number(2,0)	tinyint
number(3,0)	smallint
number(4,0)	smallint
number(5,0)	int
number(p,0) where 5 <= p <= 9	int

This parameter is dynamic.

session_time_limit

Specifies the maximum length of time that a two-phase commit transaction can be inactive before it is automatically aborted by the Oracle system. The unit for this time limit is in seconds. The value of 0 indicates no limit. The default is 60 seconds.

The syntax for session_time_limit is:

session_time_limit=value

This parameter is dynamic.

traceflags

Controls the types of messages written to the log file.

The following traceflags values are recognized:

1 - logs startup information 2 - logs connection information 3 - logs language event processing 4 - logs rpc event processing 5 - logs cursor event processing 6 - logs dynamic event processing 7 - logs bulk events 8 - logs messages sent to client 9 - logs interaction with Oracle RDBMS 10 - logs information contained in TDS login record 11 - logs all DONE packet processing 12 - forces shutdown 13 - traces XA (ASTC, DTM) RPCs 14 - enables XA LIB tracing. The output is written to $SYBASE/$SYBASE_DCO/rdbmd/log/xa_servicenameDate.trc 15 - logs parser errors

Multiple traceflags can be specified at once if separated by commas:

Example 9

traceflags = 1,2,3,4,5,6

This parameter is dynamic.

triggers

For internal use only.

two_phase commit

Enables the two-phase commit feature. This is a separately licensed feature.

This parameter is static.

View this book as PDF