DirectConnect for Oracle Installation/Reference for Windows NT

Home

DirectConnect for Oracle Installation/Reference for Windows NT

Chapter 3: Running DirectConnect for Oracle

Chapter 3

Running DirectConnect for Oracle

In This Chapter

This chapter explains how to configure and run DirectConnect for Oracle.

Understanding the Architecture

Before you configure DirectConnect for Oracle, you should have a basic understanding of how the product works.

DirectConnect for Oracle accesses Oracle databases using the Oracle Call Interface (OCI) programming interface(API). The OCI API allows DirectConnect for Oracle to manipulate data in an Oracle database.

DirectConnect for Oracle itself is built using the client/server paradigm. DirectConnect for Oracle is a Sybase Open Server application which provides the flexibility of a configurable number of concurrent client connections.

DirectConnect for Oracle communicates with ASE and OmniConnect using server class access_server.

The following diagram shows how DirectConnect for Oracle works with OmniConnect and as a stand-alone gateway:

Figure 3-1: DirectConnect block diagram
raster

Creating the Configuration File

Before running DirectConnect for Oracle for the first time, create a configuration file for DirectConnect for Oracle. The configuration file is a plain text file which contains definitions for the DirectConnect.

A sample configuration file is provided in SYBASE\sample\access\oracle\sample.cfg. See Appendix B, "Sample Configuration File" for the text of sample.cfg.

The configuration file must have the same name as the DirectConnect for Oracle server name as defined in the interfaces file. For example, if the server name of the DirectConnect in the interfaces file is defined as ORACLEDC, the configuration file name should be ORACLEDC.cfg. The name of the configuration file is case sensitive.

The configuration file must be located in the SYBASE directory.

The configuration file for a DirectConnect for Oracle contains all of the configuration information for a particular server. An instance of a DirectConnect can be configured to respond to multiple service names. A service name represents a connection route with a specific target database and a set of attributes defined with service-specific parameters. This allows a single instance of the DirectConnect for Oracle to have different attributes or targets.

In the configuration file, lines beginning with ";" are comments and are ignored by the server. A service name is enclosed in square brackets.

The single configuration file for an instance of DirectConnect for Oracle defines one or more services. One of the service names must be the same as the servername you selected. All global properties are defined under the servername heading. This name is also the name of your configuration file.

Global Parameters

Define all global configuration properties as shown in Table 3-1 in the service entry that has the same name as the configuration file itself. This primary service name is also the servername. Global properties apply to all services.

Table 3-1: Global parameters for configuration file
Keyword
Description
Default
administrator
Determines who can issue sp_shutdown.
None
charset
Sets the default character set.
cp850
language
Sets the default language.
us_english
max_packetsize
Sets the maximum packet size that can be negotiated for a client connection.
2048
max_users
Defines the total number of users who can be connected to the DirectConnect concurrently.
25
network_tracing
Turns on a packet trace between the client and the DirectConnect. Also provides tracing of communications between threads.
0 (off)

**Table 3-1: Global parameters for configuration file**
Keyword	Description	Default
administrator	Determines who can issue sp_shutdown.	None
charset	Sets the default character set.	cp850
language	Sets the default language.	us_english
max_packetsize	Sets the maximum packet size that can be negotiated for a client connection.	2048
max_users	Defines the total number of users who can be connected to the DirectConnect concurrently.	25
network_tracing	Turns on a packet trace between the client and the DirectConnect. Also provides tracing of communications between threads.	0 (off)

administrator

This configuration parameter defines who can execute the sp_shutdown command. If you want the user "ozzie" to be the account with authority to shut down the DirectConnect, the value of the administrator configuration parameter must be "ozzie". This user must also be a valid Oracle user. Oracle validates user ids and passwords. If this parameter is not defined no one will be able to shut down DirectConnect for Oracle. Only one user can have administrator privileges. This parameter is case sensitive.

Example:

administrator = ozzie

charset

The default character set for DirectConnect for Oracle is cp850. The character set is requested when a connection is made to Oracle. Results are converted into the client's character set when they are sent to the client. There is a single default character set for DirectConnect.

Example:

charset = cp850

language

This sets the default language used by DirectConnect for Oracle. The client's language might override this value. When a connection is made to Oracle, the language requested is the client language.

Setting language changes the language of messages in the log file. The language used to connect to Oracle is passed on from the DirectConnect's client.

Example:

language = us_english

max_packetsize

max_packetsize sets the maximum packet size that can be negotiated for a client connection. Without this parameter, the maximum packet size defaults to 2048. If you increase the packet size used in ASE or OmniConnect through sp_configure (using "ctlib packet size") to a value greater than 2048, you need to set this value to match.

Do not set this parameter larger than necessary because this amount of memory is allocated whether used or not. The maximum packet size supported is 32,256 bytes.

Example:

max_packetsize = 4096

max_users

max_users defines the total number of users that can be connected to a DirectConnect concurrently. As the number of users increase, additional resources are required.

max_users cannot be set to a value less than 5.

Example:

To support 50 concurrent connections set max_users as shown below:

max_users = 50

network_tracing

This enables packet tracing between the client and DirectConnect for Oracle. It also enables tracing for internal communications between threads.

Trace entries between the client and DirectConnect for Oracle go in SYBASE\SERVERNAME.log.

Internal trace entries go in SYBASE\SERVERNAME.tds.

Example:

network_tracing = 1

This feature should not be used unless required. It will cause log files to fill up quickly.

Service-specific Parameters

The properties that are not global can be specified for any service. 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.

The following table lists the service-specific parameters:

Table 3-2: Service-specific parameters for configuration file
Keyword
Description
Default
connect_string*
Determines the method and target for connectivity
None
default_precision
Defines default precision for data conversion of Oracle number datatype
38
default_scale
Defines default scale for data conversion of Oracle number datatype
0
number_mode
Determines rules for data conversion of Oracle number datatype
1
traceflags
Sets trace options
0 (off)
truncate_text
Determines whether text and image data is truncated to 255 bytes
0
* Asterisk indicates parameter required

**Table 3-2: Service-specific parameters for configuration file**
Keyword	Description	Default
connect_string*	Determines the method and target for connectivity	None
default_precision	Defines default precision for data conversion of Oracle number datatype	38
default_scale	Defines default scale for data conversion of Oracle number datatype	0
number_mode	Determines rules for data conversion of Oracle number datatype	1
traceflags	Sets trace options	0 (off)
truncate_text	Determines whether text and image data is truncated to 255 bytes	0
	* Asterisk indicates parameter required

connect_string

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

Oracle provides three methods of connecting to a target Oracle database.

Method 1

If DirectConnect for Oracle is running on the same machine as the Oracle server, the connect_string contains the keyword local and the Oracle System ID (SID). Direct process-to-process communication is provided. The ORACLE_HOME environment variable must be defined. The Oracle System ID is defined during the installation of Oracle by the Oracle system administrator.

The following is the syntax for a local connection:

connect_string = local, ora_sid

Method 2

A second method of communication is using SQLNET V1. The connect string identifies a remote Oracle system. The connect string is of the format

@T:hostname:sid

where @T: indicates that the TCP/IP driver is used for communication. hostname specifies the host that contains the database that is the target for your connection request.

sid specifies the Oracle system ID (SID) for the Oracle database you want to access. Refer to SQL*Net documentation for more information on connect strings.

The following is an example of a connect string specification using SQLNET V1:

connect_string = @T:nautilus:ora_svr4

Note: SQLNET V1 must be used when DirectConnect for Oracle is used to access Oracle 6 data. This is true even if DirectConnect for Oracle and Oracle 6 are running on the same machine.

Method 3

A third method of communication is using SQLNET V2. To use SQLNET V2 connect strings, the ORACLE_HOME environment variable has to be set before DirectConnect for Oracle is started. The tnsnames.ora file is searched for in the ORACLE_HOME\network\admin directory.

For this, you need to have an entry in the tnsnames.ora file describing the instance you will be connecting to. It is of the form:

ORACLE_SERVER = (DESCRIPTION
                (ADDRESS=
                    (COMMUNITY=yy)
                    (PROTOCOL=TCP)
                    (HOST=ORA_HOST)
                    (PORT=xx))
                (CONNECT_DATA=
                    (SID=ORACLE_SRV)))

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. COMMUNITY is set by the Oracle database administrator. 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 could 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:

connect_string = @ORACLE_SERVER

default_precision

default_precision specifies the Sybase precision when an Oracle number datatype is defined without precision or scale. This parameter affects defgen datatype mappings and sp_columns results.

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

default_scale

default_scale specifies the Sybase scale when an Oracle number datatype is defined without precision or scale. This parameter affects defgen 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.

number_mode

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

Applications requiring compatibility with the method data was managed in OmniSQL Server 10.1.2 should set this parameter to "0". The data will be returned as tinyint, smallint, int, or float. The datatype numeric is not used and overflows are possible.

If pre-System 10 isql is used to access DirectConnect for Oracle, number_mode will automatically default to a value of 0 (overriding any user setting). numeric values are not supported by pre-System 10 isql.

With a setting of "1", DirectConnect attempts to return data as tinyint, smallint, int, float or numeric, for best performance by OmniConnect. Overflows are possible. This is the default value.

With a setting of "2", DirectConnect returns data using numeric or float datatypes.

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

Table 3-3: Rules for conversion of Oracle datatype number
Scale
number_mode = 0
number_mode = 1
number_mode = 2
scale = 0
If 0 <= precision <=9
Send back as shown in Table 3-4
else
Send back data as float
If 0 <= precision <=9
Send back as shown in Table 3-4
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 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 3-3: Rules for conversion of Oracle datatype number**
Scale	number_mode = 0	number_mode = 1	number_mode = 2
scale = 0	If 0 <= precision <=9 Send back as shown in Table 3-4 else Send back data as float	If 0 <= precision <=9 Send back as shown in Table 3-4 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 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 3-4: 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

**Table 3-4: 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

traceflags

The traceflags parameter 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 - not used
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

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

Example:

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

truncate_text

The truncate_text parameter is provided for applications requiring compatibility with the data management method in OmniSQL Server 10.1.2.

The default value for this parameter is "0", where Oracle varchar, long, and long raw data is treated as text or image, and is not truncated. Each service defined in a configuration can define a setting for truncate_text.

truncate_text should not be used with OmniConnect text datatypes.

Table 3-5 shows how Oracle datatypes are converted to OmniConnect datatypes with different truncate_text parameters.

Table 3-5: Datatype conversions with truncate_text
Oracle Datatype
CIS (Omni) Datatype
truncate_text= 0
CIS (Omni) Datatype
truncate_text = 1
Values Truncated to 255
varchar2(n)
(n > 255)
text
char(255), varchar(255)
long
text
char(255), varchar(255)
long raw
image
binary(255), varbinary(255)

**Table 3-5: Datatype conversions with truncate_text**
Oracle Datatype	CIS (Omni) Datatype truncate_text= 0	CIS (Omni) Datatype truncate_text = 1 Values Truncated to 255
varchar2(n) (n > 255)	text	char(255), varchar(255)
long	text	char(255), varchar(255)
long raw	image	binary(255), varbinary(255)

Data Compatibility

An application might require conversions of Oracle datatypes long, long raw, number and varchar data to operate as in OmniSQL Server 10.1.2. For this behavior set truncate_text to "1" and number_mode to "0".

For optimum compatibility with Adaptive Server or OmniConnect, use the default values of "0" for truncate_text and "1" for number_mode.

Other combinations of these parameters can be used to change the operation of conversions of these datatypes.

Sample Configuration File

This is a sample configuration file named ORACLEDC.cfg with two services.

A more detailed sample configuration file is in Appendix B, "Sample Configuration File."

[ORACLEDC]
max_users = 25
max_packetsize = 4096
language = us_english
charset = cp850
administrator = system
connect_string = @T:nautilus:ora_svr4

[ORATEST]
connect_string = local,ora_NT
number_mode = 1
truncate_text = 0

There are two entries for two different Oracle instances. Each service requires an entry in the interfaces file. The [ORACLEDC] service name matches the configuration file name. All global properties must be defined here. Service-specific properties are defined here. If OmniConnect sends a request for data using the [ORACLEDC] service, all the parameters under that service apply to that service and the connection to Oracle.

[ORATEST] inherits global entries from [ORACLEDC]. Service-specific properties are defined here.

The following diagram illustrates the sample configuration file:

Figure 3-2: Diagram of sample configuration file
raster

Configuring ASE or OmniConnect to Use DirectConnect for Oracle

Before using DirectConnect for Oracle and Adaptive Server or OmniConnect to access remote data, you must define the remote server to OmniConnect. For more information on configuring ASE or OmniConnect refer to ASE and OmniConnect documentation.

Oracle Server Definition

Use the system OmniConnect stored procedure sp_addserver to define the service, server class and network access information for DirectConnect for Oracle. The server class is access_server.

Use sp_addserver to add an entry for each service.

Parameters to sp_addserver take on meaning as follows:

server_name - The name of the server as it is known to OmniConnect in the sysservers table.
server_class - The class to use when defining a DirectConnect for Oracle server is access_server.
network_name - The value of this parameter represents the name in the interfaces file associated with this server. The network_name parameter defaults to the server_name.

The following example illustrates:

sp_addserver ORACLEDC, access_server, ORACLEDC

Object Name Mapping

When using the stored procedure sp_addobjectdef, the second parameter contains the specification of the Oracle table name in the form server.database.owner.object_name, where:

server - maps to the OmniConnect name corresponding to the instance of DirectConnect for Oracle as found in sysservers.
database - this parameter is ignored except when referring to an Oracle package name.
owner - maps to an Oracle owner (user) name.
object_name - maps to an Oracle table or view name.

Running DirectConnect for Oracle

To run DirectConnect for Oracle, complete these steps:

Change directory to SYBASE\bin.
Execute one of the following commands:
If you are using V 7.1 of the Oracle client software:
```
dco71 -SSERVERNAME 
```
If you are using V 7.3 of the Oracle client software:
```
dco73 -SSERVERNAME 
```
-S indicates the name of the DirectConnect for Oracle. This value is used to locate the configuration file. It is also used to reference the interfaces file to find network connection information.

This will start a DirectConnect for Oracle process with the server name SERVERNAME. This name must agree with a name entered in the sql.ini file. You can connect to the DirectConnect using isql, an Open Client program you have developed, or OmniConnect. The log files are created in the SYBASE directory.

Use the NT start command to start DirectConnect for Oracle in a new window.

Displaying the Version

To find the version of DirectConnect for Oracle, change directories to SYBASE\bin and execute one of the following commands:

If you are using V 7.1 of the Oracle client software:

dco71 -v

If you are using V 7.3 of the Oracle client software:

dco73 -v

This prints the version string and exits.

Using isql

When accessing DirectConnect for Oracle with isql you must identify the service name with the -S option using the following syntax:

isql -Uuser_name -Ppassword -Sservice_name

Note: The user must be a valid Oracle user.

Terminating DirectConnect for Oracle

To shutdown DirectConnect for Oracle you must be logged in using the account described by the configuration parameter, administrator.

While in isql use sp_shutdown to terminate DirectConnect for Oracle as shown in the following example:

exec sp_shutdown [nowait]

The nowait option terminates the DirectConnect for Oracle regardless of the state it is in.


	Expand Search