action.skip

Setting Up a Relational Database

This chapter covers instructions for setting up a relational database to receive DMSII data.

Setting Up a SQL Server Database

Use the following procedures to set up a Microsoft SQL Server database for use with Databridge Client for SQL Server.

Databridge Client uses only the ODBC data source parameter to connect to ODBC. The ODBC data source parameter provides the database and server parameters required to run BCP.

In addition, the following notes apply to Microsoft SQL Server:

  • The default schema for the Databridge user determines the ownership of tables.

  • Databridge Client for SQL Server uses ODBC, which places no restrictions on the user's schema.

  • You can use primary keys with non-clustered indexes without having to edit the scripts generated by the client.

To set up a SQL Server 2016 database

Note

Starting with SQL Server 2016, SQL Server Management Studio no longer allows you to add "NT AUTHORITY\SYSTEM" to the sysadmin role.

The available options are:

  1. Run the service under the user account that is setup to run the command line client (dbutility) and use Integrated Windows authentication.

  2. Setup the database to use SQL Server authentication and run the service under the built-in system account.

To set up a SQL Server 2012 or 2012 R2 database

  1. If you use Integrated Windows Authentication with Databridge Client on Microsoft SQL Server 2012 and are running the service using the default account, you must add NT AUTHORITY\SYSTEM to the sysadmin Server Role. In the Microsoft SQL Server 2012 Management Studio, use the left pane to navigate to Security > Server Roles, right-click sysadmin, click Properties, then click the Add button.

  2. Create an ODBC data source, unless one already exists. See the following procedure for instructions.

    Important

    When creating ODBC data sources for Databridge Client, use the Microsoft SQL Server Native Client driver. The Microsoft SQL Server driver doesn't support columns longer than 32K, multi-threaded updates, and several other important Databridge features.

  3. Make sure that you have enough disk space free for the Microsoft SQL Server database.

  4. If your DMSII data sets contain case-sensitive ALPHA key items, using the default Microsoft SQL Server installation may result in duplicate keys. To avoid this, make certain the collating sequence for the client database is set to a case-sensitive or binary collation.

Create an ODBC Data Source

Use this procedure to set up an ODBC data source. If you have not created a new SQL Server database, and are not using an existing database, you must create a new database as outlined in the previous section. This database will hold your Databridge Client control tables and the replicated DMSII tables.

To create an ODBC Data Source

  1. Click Start > Settings > Control Panel > Administrative Tools. The Administrative tools dialog box opens.

  2. Double-click Data Sources (ODBC). The ODBC Data Source Administrator dialog box opens.

  3. Click the User DSN or System DSN tab. User DSNs are limited to the user who created them. System DSN data sources are available to all users of the computer and are required if you use the service.

  4. Click the Add button.

  5. In the Create New Data Source dialog box, select the Microsoft ODBC driver for SQL Server (recommended) or the driver SQL Native Client, and then click Finish.

    !!! caution} The driver SQL Server doesnS't support columns longer than 32K, multi-threaded updates, and several other important Databridge features.

  6. In the Create a New Data Source to SQL Server wizard, name the data source (for example, databasenameDS). You will enter this name as the data source parameter value in the Databridge Client Configuration file.

  7. In the Server field, choose your local computer name, and then click Next.

  8. To connect to this data source, choose an authentication method, and then click Next:

    Options Description
    Integrated Windows Authentication SQL Server uses the credentials of the user currently logged in to validate the login. Most installations use Integrated Windows Authentication.

    NOTE:If you select this option on Microsoft SQL Server 2012 and are running the service using the default account, you must add NT AUTHORITY\SYSTEM to the sysadmin Server Role. In the Microsoft SQL Server 2012 Management Studio, use the left pane to navigate to Security > Server Roles, right-click sysadmin, select Properties, and click the Add button.
    SQL Server Authentication Databridge connects to SQL Server using the user ID/password pair specified in the Databridge configuration file.

  9. Click Finish to create the ODBC data source.

Setting Up an Oracle Database

The following notes apply to Oracle:

  • The Oracle clients have a configuration parameter that allows you to specify a rollback segment larger than the default rollback segment. You can use a rollback segment to execute the SQL statement SET TRANSACTION USE ROLLBACK SEGMENT at the start of every transaction.

  • The Oracle clients support the CLOB data type for holding DMSII ALPHA items that are too large for the VARCHAR2 data type. The parameters in PL/SQL stored procedures for these clients limit the CLOB data type to 32K (Oracle 11g). For backward compatibility, this feature is enabled via the use_clob parameter.

  • Starting in Oracle 11g Release 2, Oracle database passwords are case-sensitive.

The Oracle open_cursors Parameter

We recommend that you add the open_cursors parameter to the initSID.ora file. Depending on the setting of the aux_stmts parameter, you may need to increase the value of open_cursors to make it slightly higher than the value of aux_stmts.

If open_cursors is not set high enough, an error occurs, indicating that the maximum number specified was exceeded. Refer to your Oracle documentation for information on setting these values.


Setting Up a User ID

If you're installing Databridge Client software for the first time, you must set up a user ID that allows the client to access the relational database.

Microsoft SQL Server

Caution

Do not use the sa user ID to run Databridge Client. The sa user ID typically owns tables that Databridge Client should not have access to.

If you are the Microsoft SQL Server database administrator (DBA) or if you have a user ID with DBA privileges, set up a user ID for Databridge Client that has the following:

  • (Required) The ability to select from any table, including system tables. Databridge reads the data dictionary to determine if a table or procedure exists.

  • (Required) The ability to create tables, stored procedures, and indexes for tables that the user ID owns.

  • (Optional) Unlimited space usage. This is recommended because of the large amounts of data that can be replicated from a DMSII database.

To include rights to modify the database as needed, use the Databridge owner (db_owner) to set up the user ID. After you set up the user ID, you must configure the user ID, password, server, and database. For instructions, see Signon Configuration.

Oracle

Caution

Do not use IDs such as SYS, SYSTEM, or SYSMAN to run Databridge Client. These user IDs typically own tables that Databridge Client should not have access to.

If you are the database administrator (DBA), or if you have a user ID with DBA privileges, set up a user ID for Databridge Client that has the following.

(With Oracle 12c or newer, the DBA role is much more restrictive and is not sufficient to run Databridge Client. In Oracle 12c or newer, the user ID must start with C## or c##. This identifies a common user.)

  • (Required) The ability to create and drop tables, stored procedures, and indexes for tables that the user ID owns.
  • (Required) The ability to alter tables.
  • (Required) The ability to alter the session.
  • (Optional) Unlimited tablespace privileges. This is recommended because of the large amounts of data that can be replicated from a DMSII database.

Note

In Oracle 12c or newer, you can accomplish all of the above by granting the DBA privilege to the user.

For information on creating a user ID, see your Oracle documentation.


Signon Configuration

If you have installed Databridge Client for the first time, you must supply the appropriate logon parameters to your relational database. In addition, you may be required to supply a password to sign on to the Databridge Server on the host.

When you create a new data source from the Client Console, the Hostname and Port settings (in the Add Data Source dialog box) provide these logon parameters. If a KEY is specified for the source in the DATA/SERVER/CONTROL, provide one in the Password box. These settings are saved to the binary Client configuration file (dbridge.cfg), which automatically encodes passwords.

If you supply logon parameters via dbutility command-line options, you must type them each time you run dbutility.

Note

Configuration file settings override environment variables. However, command-line options override both configuration file settings and environment variables.


Signon Parameters

The following table shows signon parameters in the dbutility configuration file (dbridge.cfg) and their equivalent command-line options.

Configuration file parameter Command-line option Description
user -U Specifies the user ID for the relational database. The user ID must have read and write access to the designated database.
password -P Specifies the password associated with the user ID for the relational database. Note that Oracle 11g and later uses case-sensitive passwords.

Configuration file only: If the password contains non-alphanumeric characters besides the underscore (_), you must enclose it in double quotation marks, as follows:

password = "a$bb%"
database -D For Oracle, this name identifies the Oracle instance or Net8 service being accessed. If the service name contains non-alphanumeric characters, you must enclose them in double quotation marks, as follows:

database = "orcl.databridge.com"
datasource -O For the Databridge SQL Server client, this parameter is the name that identifies the ODBC data source, as configured within the Windows Control Panel.
use_nt_authen -W When the ODBC data source uses Integrated Windows authentication, set this parameter to True. When the ODBC data source uses SQL Server Authentication (using userid and password), set it to False. If you set this parameter incorrectly, the client will work, but bulk loader operations will fail.


Integrated Windows Authentication for Microsoft SQL Server

The Microsoft SQL Server database provides two methods for database user authentication. SQL Server authentication uses a userid/password mechanism to authenticate user connections to SQL Server. Integrated Windows Authentication relies on the Windows operating system to authenticate user connections to SQL Server.

Note

Databridge Client requires a user account with administrator privileges when using Integrated Windows Authentication to access SQL Server. For details about use_nt_authen, see [signon] in Appendix C of the Databridge Client Administrator's Guide.