Setting up access with DSNs

This topic includes the following sections:

• User DSNs, system DSNs, and file DSNs
• Adding a user or system DSN
• Adding a non-sharable file DSN
• Modifying a DSN
• Deleting a DSN
• Port settings

Using the ODBC Data Source Administrator, you can create and manage data source names (DSNs). Each DSN has a name and contains information needed to access a database — for example, the name of the connect file and user and password information. Once you have created a DSN for a database, users can access the database from an ODBC-enabled application by selecting the DSN. DSNs free end users from having to enter the location of the data files and other connection information, and they enable you to set connection options.

When you install xfODBC, the installation adds a sample DSN named xfODBC. Except for some default settings, this DSN specifies one thing: it specifies that the Vortex driver is “Genesis,” which means that the DSN is for a local database. Users can access any local ODBC-enabled Synergy database with this DSN, but they will be prompted for a connect file, username, and password because these aren’t specified in the DSN.

You must have write privileges to the Windows registry to create or modify a DSN. If you don’t, you’ll get a “Writing config data failed” error.

We recommend creating DSNs from scratch rather than modifying the sample xfODBC system DSN that is added with the Connectivity Series installation. When Connectivity Series is upgraded, the sample DSN is replaced. When Connectivity Series is uninstalled, the sample DSN is removed.

User DSNs, system DSNs, and file DSNs

There are three types of DSN: user, system, and file. Typically, ODBC-enabled applications can use all of these.

• User DSNs are data sources that are available to one user on one machine. User DSNs contain all or part of the information needed to access a Synergy database: connect file, username, password, and port.
• System DSNs are available to all users of one machine, and (like user DSNs) system DSNs contain all or part of the information needed to access a Synergy database.
• File DSNs can be made available to all users who have the same driver and are on the same network. Non-sharable file DSNs are set to access an existing user DSN directly. They contain a reference to a user DSN, but nothing else. Typically, non-sharable file DSNs are used in place of user DSNs for versions of Microsoft Query that don’t support user DSNs. Non-sharable file DSNs can also be placed on a client, network machine, or stand-alone system. But if a non-sharable file DSN is placed on a network machine, it must reference user DSNs on the clients. Changes you make to a non-sharable DSN are automatically applied to the corresponding user DSN.

We don’t recommend sharable file DSNs because you have to manually edit the Windows registry to create them.

Adding a user or system DSN

1.

Open the ODBC Data Source Administrator, which is available from Administrative Tools in Windows Control Panel. Be sure to use the correct version of this utility:

• 32-bit applications require 32-bit DSNs for ODBC access. To create 32-bit DSNs, 32-bit Connectivity Series must be installed (on either a 32- or 64-bit machine). 32-bit DSNs are created by the 32-bit ODBC administrator, odbcad32.exe, which is located in %windir%\SysWOW64 on 64-bit machines.
• 64-bit applications require 64-bit DSNs for ODBC access. To create 64-bit DSNs, 64-bit Connectivity Series must be installed. 64-bit DSNs are created by the native 64-bit ODBC Administrator.

64-bit Windows machines usually have a mix of 32- and 64-bit applications, especially when using Visual Studio to develop 64-bit applications. For an ODBC connection that could involve both 32- and 64-bit applications, we recommend that you create both a 32-bit DSN and a 64-bit DSN that are identical in all aspects (including name).

2.

On the User DSN or System DSN tab of the ODBC Data Source Administrator window, click the Add button. The Create New Data Source window opens and lists the currently available ODBC drivers.

3.

In the Create New Data Source window, select xfODBC.

4.

Click Finish. The xfODBC Setup window displays (see figure 1).

5.

In the xfODBC Setup window, enter the data source information in the following fields.

1. Adding a DSN.

Data source name

Enter a descriptive name to identify the DSN. Some special characters are not supported. If you use an invalid character, the message “Writing config data failed” will display when you OK the dialog.

Description

Enter a description of the DSN. This is optional.

Appended to connect string

Enter a string you want sent via SQL OpenNet to the server. This is optional and is typically used to define environment variables on the server (under the direction of Synergy/DE Developer Support). To do this, use the following syntax (note the initial comma):

, ENV_VAR=env_spec[, …]

where ENV_VAR is an environment variable and env_spec is its definition. You can specify multiple environment variables by separating them with commas. Note the following:

• To use this field, you must use SQL OpenNet — i.e., select Net in the Vortex driver field.
• With a Windows server, use this field only when using vtxnet2.
• The SQL OpenNet server uses special characters as string delimiters: the at sign (@), colon (:), and exclamation point (!). In connect strings, each of these delimiters conveys a specific instruction to the SQL OpenNet processor and generally is not passed by the processor unless an identical character follows the first. If you are using an at sign, colon, or exclamation point in an environment variable definition, or at any other place in the string, you must use a duplicate at sign, colon, or exclamation point to ensure that the parser will interpret the statement correctly.

Vortex driver

Select Net for a client/server configuration. The Net option instructs xfODBC to use SQL OpenNet. (The SQL OpenNet server must be running on the host specified in the Host field.) Select Genesis for a stand-alone configuration. The Genesis option instructs xfODBC to connect to the database directly without using SQL OpenNet.

Host

If you selected Net for the “Vortex driver” field, enter the host name or IP address for the machine on which the database resides. Host names can be up to 64 characters long. If you enter an IP address, it can be in IPv4 or IPv6 format, depending on what the SQL OpenNet service is set to recognize. See vtxnetd and vtxnet2 programs.

Note the following:

• When using vtxnet2 on Windows, the account for this user must have the “log on as a batch job” privilege.
• If the -a option is set for vtxnetd or vtxnet2, you must specify a username and password after the host name:

host_name([domain\]uid/pwd)

where uid and pwd are the username and password for an account on the host machine or, if domain is specified, an account on a domain (Windows only).

Port

If the data files are on a remote machine, enter the port address for that machine. See Port settings for more information.

User name

Enter the username exactly as it is stored in the system catalog. This field is case sensitive. You can leave this field blank if you would like the end user to be prompted for a username when accessing a Synergy database.

By default, the username and password are encrypted as they are sent across the network via SQL OpenNet. For information on changing these encryption settings, see Setting SQL OpenNet client options in net.ini.

Password

Enter the password for the user exactly as it is stored in the system catalog. This field is case sensitive. Leave this field blank if you would like the end user to be prompted for a password when accessing a Synergy database.

Connect file

Enter the connect file name. Connect files must be located in the directory specified by the GENESIS_HOME environment variable. In a client/server configuration, the connect file must be in the GENESIS_HOME directory on the server. You can leave this field blank if you would like users to be prompted to supply the connect file (which might be useful for testing).

Env. variables

Use this to define environment variables used by the xfODBC driver on the client. For services that use the xfODBC driver, such as web servers, this is the only place these environment variables can be set (unless you set them in the system environment and reboot the server). For example, for a web server in a client/server configuration, you can use this field to set any of the VORTEX_API_ environment variables, VORTEX_ODBC_CHAR, or VORTEX_ODBC_TIME. For a web server with a local database, you can use this field to set GENESIS_HOME as well.

To set environment variables in this field, use the following syntax:

ENV_VAR=env_spec[, …]

where ENV_VAR is an environment variable to be set on the client, and env_spec is the definition of the environment variable. You can specify multiple environment variables by separating them with commas.

Statements

The number of logical cursors to allocate for the connection. (xfODBC assigns a logical cursor to each SQL statement.) If this option is set to a value that is greater than the DB cursors setting, xfODBC is able to cache cursors by mapping multiple logical cursors to a single database cursor. Because logical cursors require less memory than database cursors, this improves performance. For optimal performance, set this option to the maximum number of SQL statements that will be open concurrently. Valid values are 4 through 1024. The default is 256.

DB cursors

The number of database cursors to allocate for the connection. Valid values are 4 through 256. The default is 64. This should be set to a value that is less than Statements.

Columns

The maximum number of columns that can be returned for a query. Enter the number of columns of the largest table in the database. Valid values are 4 through 1024. The default is 256.

Fetch buffer size

The number of bytes to allocate for the prefetch buffer. The fetch buffer size is the size of a data transfer block. Valid values are 0 (which disables the prefetch buffer) and 1024 through 99999999. Lower values may allow the driver to return results sooner, but cause more network overhead on large queries. The default setting is 32768.

Total

The amount of disk space in pages (4,096-byte blocks) to allocate for temporary sort tables (work files) for each open cursor. The default is 10000. Valid values are 1000 through 99999999. On 32-bit systems, however, the limit may be 2 GB due to file system limitations.

In memory

The amount of internal memory in pages (4,096-byte blocks) to allocate for temporary sort tables (work files) for each open cursor. Valid values are 1000 through 999999. The default is 1000. This setting affects the performance of join queries and queries with ORDER BY clauses.

Max number of rows

The maximum number of rows that can be returned when a statement is optimized with multimerge. Multimerge optimizes SELECT statements that have one or more OR clauses by evaluating each side of each OR clause as a separate SELECT statement and then combining the results (in a multimerge operation). Note that this works only when keys are available to optimize each side of each OR clause. This setting must be a positive numeric value from 100 to 500000.

• If the statement is not optimized with multimerge, this setting does not apply.
• To turn this setting off, use SET OPTION MERGESIZE. (See SET OPTION.) You cannot use this dialog box to turn off multimerge.
• If the result set of an optimized statement is larger than this setting, an error is generated.

By default, multimerge is enabled (the default setting is 10000) because some applications automatically generate the kind of statements that a multimerge optimizes. For example, if you use Microsoft Access to issue a query that selects all the columns in a table, Access generates a SELECT statement with a series of OR clauses that repeatedly specify key segments.

Note the following:

• In some cases, multimerge could impair performance.
• For each row allowed by this setting, xfODBC uses six bytes of memory, so setting “Max number of rows” to a large value doesn’t generally affect performance.
• You can also set this option in an SQL statement by using SET OPTION MERGESIZE.

SSL required

Specifies whether the SQL OpenNet client requires SSL encryption (data packet encryption) for the DSN. If this option is selected, the SQL OpenNet service must be set to use SSL encryption when the DSN is used. Additionally, the “TLS level” and “Certificate file” settings are used for connections that use the DSN (and SSL settings in net.ini are ignored).

If this option is not selected, the “TLS level” and “Certificate file” settings are ignored, and the client will require SSL encryption only if the ssl option in net.ini is set. (Note that SSL encryption is used if the SQL OpenNet service is set to use it, regardless of this setting or the ssl setting in net.ini.)

For information on installing and configuring SSL for SQL OpenNet, see Using data packet encryption for SQL OpenNet. For information on net.ini settings, see Setting SQL OpenNet client options in net.ini.

TLS level

Specifies which TLS level will be required for SQL OpenNet connections. TLS 1.1 and 1.2 are supported, but server settings determine which levels are available (see Encrypting data packets (-e)). Note that security best practices require TLS 1.2.

This setting is used only if the SSL required option is selected.

Certificate file

Specifies the name and location of a file on the client that is used to validate the server certificate for SSL (the certificate used for the SQL OpenNet service). If the server certificate is from a trusted certificate authority (CA), this setting should specify a certificate trust store file on the client (see Requesting a certificate from a certificate authority). If the certificate for the server is self-signed, this setting should specify a root certificate on the client (see Creating a local certificate authority).

This setting is used only if the SSL required option is selected. If no certificate file is specified for the SQL OpenNet client, the client will not validate the server certificate.

6.

Click OK.

You can now use the DSN in an ODBC-enabled application.

Adding a non-sharable file DSN

1.

Add a user DSN by following the steps in Adding a user or system DSN.

2.

Use a text editor to create a file with the following (where dsn_name is the name of the user DSN):

[ODBC]
DSN=dsn_name

3.

Save this file to the directory where DSNs are stored for the ODBC Administrator. Give it a .dsn extension. You can now use the file DSN in an ODBC-enabled application.

Modifying a DSN

To modify an existing user, system, or non-sharable file DSN, do the following:

1.

Open the ODBC Data Source Administrator utility. See step 1 in Adding a user or system DSN.

2.

Select the DSN on the User DSN, System DSN, or File DSN tab. Then click the Configure button. When you click Configure, the xfODBC Setup window displays the current configuration.

3.

Make any necessary changes to the fields in the xfODBC Setup window, and then click OK. See Adding a user or system DSN for information on these fields.

If you’re using the ODBC Data Source Administrator, changes made to non-sharable file DSNs are automatically applied to the corresponding user DSN. If you make a change to a non-sharable file DSN that resides on a network machine, the change is applied to the corresponding user DSN on the machine you use to make the change. The corresponding user DSNs on other clients must be modified separately.

Prompting the user for information

If you would prefer that users enter the username, password, or connect file for the database, leave that information blank in the xfODBC Setup window. When the user attempts to access the Synergy data through a third-party application, the xfODBC driver checks the DSN and prompts the user for missing information as shown in figure 2).

2. The xfODBC Info window.

Deleting a DSN

1.

On the User DSN, File DSN, or System DSN tab of the ODBC Data Source Administrator, select the DSN you want to delete.

2.

Click the Remove button.

Port settings

To make a connection with SQL OpenNet, the port setting for the client must match the port number for the SQL OpenNet server. For example, if vtxnetd is started on port 1990, the client must use port 1990 to connect to the SQL OpenNet server.

On the server…

You can specify the port number in either of the following. We recommend setting the port number in the vtxnetd or vtxnet2 command.

• The vtxnet setting in the services file on a Windows or UNIX system. This is in %windir%\system32\drivers\etc on Windows and /etc on UNIX. A port setting in the services file is used only when the port is not specified in the vtxnetd or vtxnet2 start-up command.
• The vtxnetd or vtxnet2 command in opennet.srv (on Windows), or in the vtxnetd command in the startnet script (on UNIX) or NET.COM (on OpenVMS). A port setting in a vtxnetd or vtxnet2 command overrides a port setting in the services file.

For more information on server-side port settings, see Configuring Connectivity Series.

On the clients…

On clients, specify the port number in the DSN or, for a DSN-less connection, specify it with one of the following:

• The vtxnet setting in the TCP/IP services file, which is in %windir%\system32\drivers\etc on Windows and /etc on UNIX. For more information, see Configuring Connectivity Series.
• A port setting in net.ini. This overrides the services file setting. For more information, see Setting SQL OpenNet client options in net.ini.

A quick way to ensure your port settings match is to run either the synxfpng utility (with the -x option) or the vtxping utility. For more information, see synxfpng utility and vtxping utility.