SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure: CA-OpenIngres Specifics

The following section describes the DBMS-specific statements that you use in the SAS/ACCESS interface to CA-OpenIngres.

DBLOAD Procedure Statements for CA-OpenIngres

To create and load a CA-OpenIngres table, use the DBMS=INGRES option and one database-description statement in the PROC DBLOAD step, DATABASE=. The database-description statement supplies database-specific information to the SAS System.

The syntax for other statements that you use to create and load a CA-OpenIngres table is provided below.

Note: The SAS/ACCESS interface to CA-OpenIngres DBLOAD procedure does not use the following procedure statements: USER= and PASSWORD=. [cautionend]

The SAS/ACCESS interface to CA-OpenIngres DBLOAD procedure uses the following statements in interactive line, noninteractive, or batch mode:

PROC DBLOAD <DBMS=INGRES> <DATA=<libref.> SAS-data-set> <APPEND>;

DATABASE='<v-node::>database</server-type>';

TABLE=<'>table-name<'>;

ACCDESC= <libref.>access-descriptor;

COMMIT=commit-frequency;

DELETE variable-identifier-1 <...> variable-identifier-n;

ERRLIMIT=error-limit;

LABEL;

LIMIT=load-limit;

NULLS variable-identifier-1 Y|N <...variable-identifier-n Y|N>;

QUIT;

RENAME variable-identifier-1=<'>column-name-1<'>
<...variable-identifier-n=<'>column-name-n<'>>;

RESET ALL|variable-identifier-1<...variable-identifier-n>;

SQL CA-OpenIngres-SQL-statement;

TYPE variable-identifier-1 = `column-type-1'
<...variable-identifier-n =`column-type-n'>

WHERE SAS-where-expression;

LIST <list-selection>;

LOAD;

RUN;

PROC DBLOAD Options

APPEND

enables you to load SAS data set observations into an existing CA-OpenIngres table, beginning with Release 6.12 of the SAS System. Any SAS data set (SAS data file or SAS data view) can be used as input for APPEND processing. APPEND is optional and can be specified only in the PROC DBLOAD step.

The APPEND option assumes that the table that is specified in the TABLE= statement exists. To create a CA-OpenIngres table to append to, first use PROC DBLOAD with the SQL statement. Then specify another PROC DBLOAD step with APPEND. If the table does not exist, the procedure fails and an error message is displayed.

Other PROC DBLOAD statements such as DELETE and RENAME work differently when they are used with the APPEND option.

Suppose that you have a data set named INVDATA that you want to append to an existing CA-OpenIngres table. The following example shows how to use the APPEND option to append rows from the INVDATA data set to the table named INVOICE.

proc dbload dbms=ingres data=invdata append;
     database='testnode::testdatabase/star';
     table=invoice; load;
   run;

PROC DBLOAD Statements

DATABASE='<v-node::>database </server-type >';

indicates the name of the database where you want to store the new table. You must enclose it in single or double quotes. This statement is required. If you specify a database, it must exist. If you do not know the names of your databases, contact your DBA.

If you use a remote database, you enter the remote virtual node name. If you use a distributed database, you enter the database server type. If you use a remote distributed database, you enter the database node name and server type.

v-node: is the name of the remote virtual node where the database is located. It implies that the network node address and protocol have been defined by the NETU utility that is provided by CA-OpenIngres. If you are accessing a local database, you do not need to enter the virtual node name. For more information about the NETU utility, refer to your CA-OpenIngres/Net user's and administrator's guide.
database: is the name of the database.
server-type: is the type of server that is being accessed at the local or remote site. Valid server types are INGRES for a CA-OpenIngres DBMS server or STAR for a CA-OpenIngres/Star server. (You can replace /STAR with its alias, /D.) If you omit a server type, the default server type, INGRES, for the CA-OpenIngres DBMS is used.

The following example creates a new CA-OpenIngres table, INVOICE, from the DLIB.INVOICE data file. An access descriptor ADLIB.INVOICE is also created, based on the new table. The AMTBILLED column and the 5th column in the table (AMOUNTINUS) are renamed. You must be granted the appropriate privileges in order to create new CA-OpenIngres tables or views.

libname adlib 'SAS-data-library';
libname dlib 'SAS-data-library';

proc dbload dbms=ingres data=dlib.invoice;
   database='textiledb'; table=invoice; 
   accdesc=adlib.invoice;
   rename amtbilled = amountbilled 
          5 = amountindollars;
   nulls invnum=n amtbill=n;
   load;
run;

The next example only sends an SQL GRANT statement to the TEXTILEDB database and does not create a new table. Therefore, the TABLE= and LOAD statements are omitted.

proc dbload dbms=ingres;
   database='textiledb';
   sql grant select on customers to frederick;
run;

Statement Interactions with the APPEND Option

DELETE and RENAME

DELETE and RENAME enable you to perform one-to-one matching of SAS variables to CA-OpenIngres columns. When you use the APPEND option, there must be a CA-OpenIngres column for every SAS variable that you are loading.

Use the DELETE statement to drop a SAS input variable that has no corresponding CA-OpenIngres column. Use the RENAME statement if the names do not match. If there is not a CA-OpenIngres column for each SAS variable, the APPEND operation is rejected.

The RENAME statement indicates the column name in the CA-OpenIngres table for the SAS data set variable that you specify. This example loads the data that is associated with the SAS variable COUNTRY into the CA-OpenIngres column named ORIGIN.

rename country=origin;

ERRLIMIT=

The DBLOAD ERRLIMIT= statement has a default of 10 when the APPEND option is specified, instead of the default of 100.

LOAD

The LOAD statement is required.

NULLS

The NULLS statement has no effect when the APPEND option is specified, unless the table is created in the same step.

TYPE

The TYPE statement has no effect because the input SAS variable types and the CA-OpenIngres column types must have a one-to-one match.

Chapter Contents
Previous
Next
Top of Page