DataFlux Data Management Studio 2.7: User Guide

Adding ODBC Connections to SAS Web Infrastructure Platform Data Servers

The SAS Web Infrastructure Platform Data Server is included in SAS deployments for use as transactional storage by SAS middle-tier software and some SAS solutions software. This server is based on PostgreSQL database software. The server is configured specifically to support SAS software.

A SAS Web Infrastructure Platform Data Server can be used to host a DataFlux repository. You must perform the following tasks:

• Create a PostgreSQL database
• Create an ODBC PostgreSQL connection
• Create a DataFlux repository

Create a PostgreSQL database

Perform the following steps to create a PostgreSQL database in the SAS Data Management Data Server PostgreSQL connection:

1. Confirm the port number of the Postgres server connection in the SAS Data Management Server. You can do this by selecting the SAS Data Management Server in the Server Manager in SAS Management Console. The default port is 104422.
2. Download the application pgAdmin from http://www.pgadmin.org/. The pgAdmin application is a free PostgreSQL database design and management system.
3. Register the SAS Data Management Server in pgAdmin. The following display shows a sample registration in pgAdmin:

   

4. Once the registration of the SAS Data Management Data Server in pgAdmin is complete, select the postgres database connection in the Servers folder in the Object Browser in pgAdmin. Then click SQL to open the SQL Console window, as shown in the following display:

   

5. Enter the following SQL script in the SQL Console window:

   

   Note that this example shows an encrypted password of Orion123. You can use a different password if desired.

6. Click the Post SQL button to execute the script, as shown in the following display:

   

7. The dmdmsdb database should not be created and visible in the Servers folder in the Object Browser in pgAdmin, as shown in the following display:

   

Create an ODBC PostgreSQL Connection

Perform the following steps to add an ODBC PostgreSQL connection to a SAS Web Infrastructure Platform Data Server:

Click the Data riser on the DataFlux Data Management Studio desktop.

Expand the Data Connections folder.

1. Select the New Data Connection menu in the Data Connections pane on the right. Then select ODBC Connection to display the ODBC Data Source Administrator dialog.
2. Select the type of connection that you want to establish. For example, you can click System DSN to create a connection to a data source that all users on the machine can access. The System DSN tab is shown in the following display:

   

3. Click Add to select the driver for your data source. Select the DataFlux 32-bit Postgres Wire Protocol driver as shown in the following display.

   

4. Click Finish. A setup dialog displays for the selected driver (connection type).
5. Enter a name, a description, and other attributes into the setup dialog. We recommend that you avoid using special characters such as quotation marks in your data source names. The Port Number for a Postgres Wire Protocol connection must be 10443, as shown in the next figure.
   

   
   
   

6. Click OK save the connection and return to the ODBC Data Source Administrator dialog. The new connection is included on the System DSN tab of that dialog.
7. Click OK to close the ODBC Data Source Administrator dialog and return to the Data riser.
8. Refresh the Data riser in order to see the new connection in the Data Connections folder in the left pane. Select View, and then select Refresh from the main menu. The new connection should appear in the Data Connections folder.
9. To verify a connection, double-click the connection in the Data Connections folder in the left pane. Enter any required credentials. If the connection works, you will be able to see tables, fields, and other attributes in the right panel.

Create a DataFlux Repository On that Server

To use a PostgreSQL connection to create a DataFlux repository on a SAS Web Infrastructure Platform Data Server, see Adding a DBMS Repository for the Client.