SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure: SYBASE Specifics

The following section describes the DBMS-specific statements that you use in the SAS/ACCESS Interface to SYBASE.

DBLOAD Procedure Statements for SYBASE

To create and load a SYBASE table, the SAS/ACCESS Interface uses the following statements in the DBLOAD procedure in line and batch mode.

The database description statements include USER=, PASSWORD=, DATABASE=, SERVER=, and INTERFACE= (for some installations). If specified, these statements must immediately follow the PROC DBLOAD statement. The order of the statements within this group does not matter. The TABLE= statement must follow the database description statements.

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

USER=<'>user-name<'>

PASSWORD=|PASS= |SYBPW= <'>password<'>

DATABASE=|DB= <'>database-name<'>;

SERVER=<'>server-name<'>;

INTERFACE=<'>file-name<'>;

BULKCOPY=|BULK= Y|N;

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

ACCDESC=<libref.>access-descriptor;

COMMIT=commit-frequency;

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

ERRLIMIT=error-limit;

LABEL;

LIMIT=load-limit;

LIST <ALL|COLUMN|variable-identifier>;

LOAD;

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 Transact-SQL-statement;

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

WHERE SAS-where-expression;

RUN;

Note: If a database object contains lowercase, national, or special characters, you must enclose it in quotation marks. SYBASE is usually set for case sensitivity. Therefore, any objects that were given lowercase names when they were created, or whose names contain special characters, must be enclosed in quotation marks. See Case Sensitivity in SYBASE for more information. [cautionend]

DATABASE=<'>database-name<'>;

specifies the name of the database in which you want to store the new SYBASE table. If you omit this statement, the default database for your SYBASE user name is used.

DATABASE= can also be specified with the DB= alias.

INTERFACE=<'>file-name<'>;

specifies the name and location of the interfaces file that is searched when you connect to the SYBASE server. The interfaces file contains names and access information for available servers on the network.

If you omit this statement, the default action for your operating system occurs. INTERFACE= is not used in some operating environments. Contact your DBA to determine whether this statement applies to your operating environment.

USER= <'>user-name<'>;

specifies the SYBASE user name (also called the login name) that you use when executing the DBLOAD procedure. If you omit a user name, your operating system account name is used, if applicable to your operating environment.

PASSWORD=<'>password<'>;

specifies the password for SYBASE. If you omit a password, a default password of NULL is used.

PASSWORD= can also be specified with the PASS= and SYBPW= aliases.

SERVER=<'>server-name<'>;

specifies the server with which to connect. This server accesses the database in which your new table is stored. If the server name contains lowercase or special characters, you must enclose it in quotation marks.

If you omit this statement, the default action for your operating system occurs. On UNIX systems, the value of the environment variable DSQUERY is used if it has been set.

BULKCOPY=Y | N

uses the SYBASE bulk copy utility to insert rows into a SYBASE table. The default value is N.

If you specify BULKCOPY=Y, BULKCOPY= calls the SYBASE bulk copy utility in order to load data into a SYBASE table. This utility groups rows so that they are inserted as a unit into the new table. Using the bulk copy utility can improve performance.

You use the COMMIT= statement to specify the number of rows in each group; this argument must be a nonnegative integer. After each group of rows is inserted, the rows are permanently saved in the table. While each group is being inserted, if one row in the group is rejected, then all of the rows in that group are rejected.

If you specify BULKCOPY=NO, rows are inserted into the new table using Transact-SQL INSERT statements. Refer to your SYBASE documentation for more information on the bulk copy utility.

BULKCOPY= can also be specified with the BULK= alias.

DBLOAD Procedure Example

The following example creates a new SYBASE table, EXCHANGE, from the DLIB.RATEOFEX data file. An access descriptor ADLIB.EXCHANGE is also created, which is based on the new table. The DBLOAD procedure sends a Transact-SQL GRANT statement to SYBASE. You must be granted SYBASE privileges to create new SYBASE tables or to grant privileges to other users.

Note: The DLIB.RATEOFEX data set is included in the sample data that is shipped with your software. [cautionend]

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

proc dbload dbms=sybase data=dlib.rateofex;
   server='server1'; database='testdb';
   user='testuser'; password='testpass';
   table=EXCHANGE;
   accdesc=adlib.exchange;
   rename fgnindol=fgnindolar 4=dolrsinfgn;
   nulls updated=n fgnindol=n 4=n country=n;
   load;
run;

Chapter Contents
Previous
Next
Top of Page