Defining a Data Source

Overview of Defining a Data Source

A data source identifies a SAS/SHARE server that htmSQL can get data from. A data source definition can also include SAS libraries or an external database management system (DBMS) that htmSQL accesses through that server.

After the Webmaster defines a data source, an htmSQL programmer can access it by specifying its name in the query or update section of an htmSQL input file.

Creating a Data Source Definition File

A program called dsdef is supplied with htmSQL. Use dsdef to define data sources for htmSQL. dsdef prompts the user for data source information and then creates or updates a data source definition file. For invocation and syntax information about dsdef, see Instructions for Invoking dsdef.

Note: The data source definition file should be modified only by the Webmaster. The file could be corrupted if it is simultaneously modified by multiple users.

Using dsdef

dsdef prompts for information about data sources, SAS libraries, and SAS/SHARE servers. If the data source, library, or server that you specify was defined previously, dsdef puts the existing attribute values in square brackets ([]) next to the prompts for new values. You can accept the existing value by not entering a new value and pressing Enter.

The following dialog box is generated by dsdef. To get more information about each step, select the number that precedes the prompt.

1SystemPrompt> dsdef<return> 

Configure data sources for htmSQL
=================================

Use this program to create or modify the definition of one or more data
sources for htmSQL.

A data source specifies exactly one SAS/SHARE server and may also specify
one or more SAS data libraries or an external DBMS to be accessed through
the server.

Data source names can be any length and can contain any character except a
double quote ("). They are case-sensitive and must be entered in an htmSQL
input file exactly as they are defined.

In the dialog that follows, default or previously specified values are shown
in square brackets ([]); to accept such a value, press return or enter.
The only required values in a data source definition are the data source
name and server name. You can omit all other values by press return or
enter when you are prompted for them.

When you have finished defining data sources, you can save your changes
by pressing return or enter at the 'Enter a Data Source Name' prompt.
You can cancel your changes by entering a 'c' instead.

2 Enter a Data Source Name to configure: datasrc1<return>
Enter information for: datasrc1
 3Description: sample data source<return>
 4SAS/SHARE server name (host.service): node1.server1<return>
5 Require SAS SQL processor to undo partial updates? (usually NO): <return>
6 DBMS to pass SQL to (omit for SAS data): <return>
7 Options to pass when connecting to DBMS: <return>
 Enter information for: Server node1.server1
8SAS/SHARE server host IP name (fully qualified) or address [node1]: <return>
9User ID for SAS/SHARE server host: <return>
10Password for specified user ID: <return>
11 SAS/SHARE server user access password: pword<return>
12Enter a library in data source "datasrc1" to configure: userlib1<return>
Enter information for: datasrc1 USERLIB1
13Description: a sample library<return>
14Library path name: sasuser/<return>
15 SAS engine the SAS/SHARE server should use: <return>
16 Options (only ACCESS=READONLY and SLIBREF=server-libref supported): <return>
17Enter a library in data source "datasrc1" to configure: <return>
18Enter a Data Source Name to configure: datasrc2<return>
Enter information for: datasrc2
3Description: sample data source 2<return>
4SAS/SHARE server name (host.service): node1.server1<return>
5Require SAS SQL processor to undo partial updates? (usually NO): <return>
6DBMS to pass SQL to (omit for SAS data): <return>
7Options to pass when connecting to DBMS: <return>
19Do you want to update configuration for server node1.server1? <return>
17Enter a library in data source "datasrc1" to configure: <return>
18Enter a Data Source Name to configure: <return>

The following steps explain the information that you must provide to the dsdef program.

1At the system command-line prompt, enter dsdef. If you want to save your data source definition file in a directory other than the default directory, you must specify the -config option and the pathname for the file. The following example illustrates this:

dsdef -config c:\htmSQL\mydata.dsf

If -config is not specified, the definition is written to a default pathname. If the definition file already exists, it is updated. Otherwise, the file is created.

Note: To end the program, enter c to cancel without saving or press Enter to save your data source information and then end the program. Depending on where you are in the program, you might need to press Enter more than once to completely exit the program.

2At the Enter a Data Source Name to configure: prompt, enter the name of your data source. This is the value that you specify for the datasrc= parameter of the {query} or {update} directive that you specify in your htmSQL input (.hsql) file.

A data source name can be of any length and can contain any character (including blank spaces) except for the following characters: []{}()"?*=!@,:;. Use a name that you can remember and type accurately. Case is significant in data source names.

3At the Description (): prompt, enter a description of the data source. This value is optional. The description can be up to 1,024 characters long.

4At the SAS/SHARE server name (host.service): prompt, enter the name of the SAS/SHARE server for this data source. Specify a two-part name (host.service or host.port), where

host is the nodename of the machine where the server runs
service is the service name that is specified when the SAS/SHARE server is defined as a service in the TCP/IP SERVICES file. The service name is specified for the ID= option of the PROC SERVER statement that is used to define the SAS/SHARE server.

5port is the port number of the SAS/SHARE server. This two-part name is the same name that you specify in a LIBNAME or PROC SQL CONNECT TO statement in a SAS program.

Note: If you use a port number to identify a SAS/SHARE server, then you do not need to modify the SERVICES file on the Web server machine.

6At the Require SAS SQL processor to undo partial updates? (usually NO): prompt, specify the setting for the UNDO_POLICY option of the SAS SQL processor. The following values are valid:

n, N, no, or NO (default value)	resets UNDO_POLICY to NONE. NONE specifies that if the UPDATE or INSERT of a row fails, then any rows that were updated or inserted by that SQL statement (before the failure) remain inserted or updated.
y, Y, yes, or YES	retains the default value (REQUIRED) of UNDO_POLICY. REQUIRED specifies that if the UPDATE or INSERT of a row fails, then any rows that were updated or inserted by that SQL statement (before the failure) are undone.

7At the DBMS to pass SQL to (omit for SAS data): prompt, if your data is in an external DBMS, specify the SAS/ACCESS engine for the DBMS. Example values are DB2, ORACLE, and SQLDS. If your data is in a SAS library, do not specify a value.

8At the Options to pass when connecting to DBMS: prompt, enter any options that are required for connecting to the external DBMS. The exact options that are available and the exact option names depend on the DBMS that you specify for step 6 and for the SAS/ACCESS view engine for that DBMS. The connection options correspond to the DBMS arguments that are documented in the SQL Procedure Pass-Through facility's documentation for that SAS/ACCESS view engine. Example values are USERID=userid and PASSWORD=password, where userid and password are the user ID and password for the DBMS.

9At the SAS/SHARE server host IP name (fully qualified) or address [node1]: prompt, enter the server's nodename. If you do not enter a nodename, this value defaults to the nodename that you specified in step 4 (in this example, node1 is the default value). In a complex environment, you might need to specify a fully qualified domain address for the server, such as server1.unx.sas.com.

10At the User ID for SAS/SHARE server host: prompt, enter a user ID for the system that the server runs on. This is an optional value that you specify if the server is running in secure mode. Otherwise, the value is ignored.

If you omit the user ID from the data source definition, the htmSQL programmer must specify the user ID in the htmSQL input file by using the userid= parameter of the {query} or {update} directive.

11At the Password for specified user ID: prompt, enter the password for the user ID that you specified in step 9. This is an optional value that you specify if the server is running in secure mode. Otherwise, the value is ignored.

If the server is running in secure mode and you omit the password from the data source definition, the htmSQL programmer must specify the password in the htmSQL input file by using the password= parameter of the {query} or {update} directive.

12At the SAS/SHARE server user access password: prompt, enter the server access password for users. This is an optional value. This must be the same password that is specified in

the UAPW= option of the SERVER procedure that was used to define the SAS/SHARE server. You must specify a password if user access to the server is password-protected.
the SAPW= option of the LIBNAME statement and the SQL procedure's CONNECT TO statement.

13If the SAS library that contains your data is not predefined to the SAS/SHARE server, then at the Enter a library in data source "datasrc1" to configure: prompt, enter a libref for the library. htmSQL programmers use this libref as the high-level qualifier for the table names in the SQL queries and statements that their applications send to the SAS/SHARE server. Steps 13 through 16 request additional information about this library.

The library name can be up to 8 characters long. The first character must be a letter or an underscore. Subsequent characters can be letters, numeric digits, or underscores. Blanks and special characters are not allowed.

14At the Description (): prompt, enter a description of the library. This value is optional. The description can be up to 1,024 characters long.

15At the Library path name: prompt, enter the physical name of the library. This must include a valid pathname for the operating system in which your server library is stored.

16At the SAS engine the SAS/SHARE server should use: prompt, specify the SAS engine that is required for writing to and reading from this server library. This option is required only if you do not want the SAS/SHARE server to use the engine that the server selects by default. For information about other engines, see the description of the LIBNAME statement in the SAS companion for the operating system in which your server library is stored.

17At the Options (only ACCESS=READONLY and SLIBREF=server-libref supported): prompt, specify one or both of the following values (these values are optional):

SLIBREF=server-libref	specifies the server's library reference name for the library.
ACCESS=READONLY	gives users read-only access to the SAS data sets in the library.

18At the Enter a library in data source "datasrc1" to configure: prompt, you can either enter the name of another server library or you can press Enter if you do not want to add any more libraries to this data source.

Note: If you do specify another library, dsdef takes you through steps 13 through 16 for that library. If you do not specify another library, dsdef proceeds to step 18.

19At the Enter a Data Source Name to configure: prompt, you can either enter the name of another data source or you can press Enter if you do not want to add any more data sources.

Note: If you do specify another data source, dsdef takes you through steps 3 through 7 for that data source. If you do not specify another data source, the dsdef program ends.

20If in step 4 you specify a SAS/SHARE server that is already defined for the data source, dsdef prompts to see whether you want to update the server configuration information. You can either enter yes or press Enter for no.

Note: If you do specify yes, dsdef takes you through steps 8 through 11 so you can update the information for that server. Otherwise, dsdef proceeds to step 12.