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 data 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 only be modified by the Webmaster. The file could be corrupted if it is simultaneously modified by multiple people.

Using dsdef

dsdef prompts for information about data sources, SAS data 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 the Enter key.

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

Note: User input is indicated by bold print.

   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.

A Step-by-Step Explanation of dsdef

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

1. At 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, it is created.

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

2. At the Enter a Data Source Name to configure: prompt, enter the name of your data source. This is the value 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. Note that case is significant in data source names.

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

4. At 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 node name 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

     • for the ID= option of the PROC SERVER statement that is used to define the SAS/SHARE server

   • port is the port number of the SAS/SHARE server.

     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.

   This two-part name is the same name that you specify in a LIBNAME or PROC SQL CONNECT TO statement in a SAS program.

5. At 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 the 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.

6. At 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.

7. At 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.

8. At 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 may need to specify a fully qualified domain address for the server such as server1.unx.sas.com.

9. At 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 secured 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.

10. At 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 secured mode; otherwise, the value is ignored.

    If the server is running in secured 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.

11. At 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.

12. If 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 eight 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.

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

14. At 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.

15. At 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.

16. At 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.

17. At 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 the Enter key 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.

18. At the Enter a Data Source Name to configure: prompt, you can either enter the name of another data source or you can press the Enter key 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.

19. If in step 4 you specify a SAS/SHARE server that is already defined for the data source, dsdef prompts to see if you want to update the server configuration information. You can either enter yes or press the Enter key 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.