Maintaining Global Metadata

DBMS Connection Profiles

A DBMS connection profile is a metadata record that specifies a user name, a password, DBMS options, and other information that SAS can use to access source data or warehouse data stores in a database management system (DBMS) other than SAS. DBMS connection profiles are included in the metadata records for DBMS data stores or SAS/ACCESS LIBNAME definitions in the current Environment.

If you want SAS/Warehouse Administrator to generate code that will access source data in a DBMS or load warehouse data in a DBMS, you will probably need at least one DBMS connection profile for each target DBMS. If you want to connect to the same DBMS but with different levels of privilege or with different options, you need to create different DBMS connection profiles with the appropriate user names, passwords, and options.

Preparing to Create DBMS Connection Profiles

In SAS/Warehouse Administrator, you can create two kinds of connection profiles:

connection profiles that specify SAS/ACCESS LIBNAME statement options and are included in the metadata for SAS/ACCESS LIBNAME definitions
connection profiles that specify SQL Pass-Through statement options and DBLOAD options that are included directly in the metadata for DBMS data stores--data stores with Load processes that are similar to those provided by SAS/Warehouse Administrator Release 1.x.

Before you create a connection profile, determine how it will be used. This will help you determine what kinds of options you will specify in the profile.

Example: Creating a Connection Profile for a SAS/ACCESS LIBNAME Definition

This example summarizes how to create a connection profile that will be included in the metadata for a SAS/ACCESS LIBNAME definition.

Identify DBMS Login Information

Before you create a connection profile that will be used in a SAS/ACCESS LIBNAME definition, gather the following information:

Target DBMS	Example: Oracle
DBMS user name	Example: admin1
DBMS password	Example: ad1min
DBMS connection name	Example: V2o7223.world

Define Connection Properties

Display the Define Items Used Globally window, as described in Using the Define Items Used Globally Window.

In the Type panel of the Define Items Used Globally window, click the button beside DBMS Connections. Click Add at the bottom left of the window. A DBMS Connection Profile Properties window for the connection displays for you to enter the appropriate information as follows:

Name	enter a name for this connection profile.

[untitled graphic]

In the Name field, oracle-1 is specified.

After you have specified a name (and possibly, a description), click the Details tab. The fields on this tab are

DBMS Nickname	select the target DBMS from the drop-down list.
User/Schema	specify the name that is used to log in to the target DBMS.

[untitled graphic]

In the DBMS Nickname field, Oracle was selected from a list of DBMS nicknames known to SAS.

In the User/Schema field, admin1 is the name that is used to log in to the DBMS in our example.

After you have entered the User/Schema, click Password to define the password used to log in to the target DBMS. In the Password window, enter the password, press RETURN, then enter the password again to verify it. You will be returned to the Details tab.

Click the Options tab. The fields on this tab are

SQL/LIBNAME options

specify SAS/ACCESS LIBNAME statement options or SQL Pass-Through statement options for the current connection profile.

If you are defining a DBMS connection for a SAS/ACCESS LIBNAME definition, specify SAS/ACCESS LIBNAME statement options in this field. At a minimum, specify the DBMS server name required to connect to the target DBMS.

The options are specific to the DBMS to which you are connecting. For details about options, see the appropriate chapter in SAS/ACCESS for Relational Databases: Reference.

[untitled graphic]

In the current example, the appropriate value for the SQL/LIBNAME Options field is a PATH= option that specifies the DBMS server name--with the server name enclosed in single quotation marks: path='V2o7223.world' . Use the same syntax that you would use in a SAS/ACCESS LIBNAME statement, such as the one in Test SAS/ACCESS Engine Support.

Leave the DBLOAD Options field blank if you are defining a DBMS connection for a SAS/ACCESS LIBNAME definition.

You have now entered all of the metadata required for this profile. Click OK to save the profile. The new profile is added to the list of connections in the Define Items Used Globally window.

Top of Page