Chapter Contents

Previous

Next

LIBNAME Statement: SYBASE Specifics

Valid:

Anywhere

Arguments

libref

is any SAS name that serves as an alias to associate the SAS System with a database, schema, server, or group of tables and views.

SAS/ACCESS-engine-name

is a SAS/ACCESS engine name for your DBMS, in this case, sybase. SAS/ACCESS engines are implemented differently in different operating environments. The engine name is required.

SAS/ACCESS-engine-connection-options

are options that you specify to connect to a particular database; these options are different for each database. If the connection options contain characters that are not allowed in SAS names, enclose the values of the options in quotation marks. If you specify the appropriate system options or environment variables for your database, you can often omit the connection options. See your SYBASE documentation for details.

SAS/ACCESS-LIBNAME-options

are options that apply to the processing of objects and data in a DBMS, such as its tables or indexes. For example, the BULK_BUFFER= option enables you to specify the number of bulk rows of DBMS data that the DBMS can write to the buffer. Support for many of these options is DBMS specific.

Some SAS/ACCESS LIBNAME options have the same names as SAS/ACCESS data set options. When you specify an option in the LIBNAME statement, it applies to objects and data that are referenced by the libref. A SAS/ACCESS data set option applies only to the data set on which it is specified. If a like-named option is specified in both the SAS/ACCESS engine LIBNAME statement and after a data set name (which references a DBMS table or view), the SAS System uses the value that is specified later, on the data set name. See Data Set Options: SYBASE Specifics for more information on data set options.

Details

The LIBNAME statement associates a libref with a SAS/ACCESS engine to access tables or views in a database management system (DBMS). The SAS/ACCESS engine enables you to connect to a particular DBMS and, therefore, to specify a DBMS table or view name in a two-level SAS name. For example, in MYDBLIB.EMPLOYEES_Q2, MYDBLIB is a SAS libref that points to a particular group of DBMS objects, and EMPLOYEES_Q2 is a DBMS table name. When you specify MYDBLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. Beginning in Version 7, SAS software supports reading, updating, creating, and deleting DBMS tables.

To disassociate or clear a libref from a DBMS, use a LIBNAME statement, specifying the libref (for example, MYDBLIB) and the CLEAR option as follows:

libname mydblib CLEAR;

See SAS/ACCESS LIBNAME Statement for more information on options that you can use in the LIBNAME statement.

SAS/ACCESS Engine Connection Options

The SAS/ACCESS engine connection options for SYBASE are as follows:

	USER=
	PASSWORD=
	DATABASE=
	SERVER=

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

specifies the SYBASE user name (also called the login name) that you use to connect to the database that contains the tables and views that you want to access. If the username contains spaces or nonalphanumeric characters, you must enclose the user name in quotation marks.

PASSWORD=<'>SYBASE-password<'>

specifies the password that is associated with the SYBASE user name.

If you omit the password, a default password of NULL is used. If the password contains spaces or nonalphanumeric characters, you must enclose the password in quotation marks.

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

DATABASE=<'>database-name<'>

specifies the name of the SYBASE database that contains the tables and views that you want to access.

If the database name contains spaces or non-alphanumeric characters, you must enclose it in quotation marks. If you omit DATABASE=, the default database for your SYBASE user name is used.

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

SERVER=<'>server-name<'>

specifies the server with which to connect. This server accesses the database that contains the tables and views that you want to access.

If the server name contains lowercase, spaces, or nonalphanumeric characters, you must enclose it in quotation marks.

If you omit SERVER=, 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.

SAS/ACCESS LIBNAME Options

When you specify any of the following options in the LIBNAME statement, the option is applied to all objects (such as tables, views, and indexes) in the database that the libref represents.

The SAS/ACCESS interface to SYBASE supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options, except for PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES=. In addition to the supported options, the following LIBNAME options are used only in the interface to SYBASE or have SYBASE-specific aspects to them:

	CONNECTION=
	DBLINK=
	ENABLE_BULK=
	MAX_CONNECTS=
	PACKETSIZE=
	QUOTED_IDENTIFIER=
	READ_BUFFER=
	READ_ISOLATION_LEVEL=
	READ_LOCK_TYPE=
	SCHEMA=
	UPDATE_ISOLATION_LEVEL=
	UPDATE_LOCK_TYPE=

CONNECTION=SHAREDREAD | UNIQUE | GLOBALREAD

indicates whether multiple opens for read access can use the same physical connection.

Note:   When you use the SQL procedure, you must specify CONNECTION= UNIQUE to create tables with the SELECT statement and to perform correlated subquery joins. You must also specify CONNECTION= UNIQUE when you use the APPEND procedure to append data from one table to another.  

For a full description of this option, refer to SAS/ACCESS LIBNAME Statement.

DBLINK=database-link

allows you to link to another database within the same server to which you are connected.

If you omit DBLINK=, SAS can only access objects in your default database.

DBLINK= and SCHEMA= can be used together, so that a SYBASE object can be referenced as libref.schema-owner's-name.object.

ENABLE_BULK=YES | NO

enables the connection to process bulk copy when you load data into a SYBASE table. Bulk copy groups rows so that they are inserted as a unit into the SYBASE table. Using bulk copy can improve performance.

Default value: YES

ENABLE_BULK=YES enables the connection to perform a bulk copy of SAS data into SYBASE. Specifying ENABLE_BULK=NO might save some memory but it also disables the bulk copy ability for that libref.

If you use both the LIBNAME option, ENABLE_BULK=, and the data set option, BULKCOPY=, the values of the two options must be the same or an error is returned. However, since the default value of ENABLE_BULK= is YES, you do not have to specify ENABLE_BULK= in order to use the BULKCOPY= data set option.

Note:   In V7 and previous releases, this option was called BULKCOPY=. In Version 8, an error is returned if you try to specify BULKCOPY=.  

MAX_CONNECTS=numeric-value

allows you to specify the maximum number of simultaneous connections that SYBASE allows.

If you omit MAX_CONNECTS=, the maximum number of connections defaults to 25. Note that increasing the number of connections has a direct impact on memory.

PACKETSIZE=numeric-value

allows you to specify the packet size for SYBASE to use. You may specify any multiple of 512, up to the limit of the maximum network packet size setting on your server.

If you omit PACKETSIZE=, the packet size defaults to the current server setting. You can query the default network packet value in ISQL by using the SYBASE sp_configure command.

QUOTED_IDENTIFIER=YES | NO

allows you to specify table and column names with embedded spaces and special characters. This option is used in place of the PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options, which have no effect on the SYBASE engine.

Default value: NO.

QUOTED_IDENTIFIER= can also be specified with the QUOTED= alias.

READ_BUFFER=numeric-value

specifies the number of bulk rows of DBMS data to write to the buffer.

Default value: 100

This option improves performance by specifying a number of rows that can be held in memory for efficient input into SYBASE. A higher number signifies that more rows can be held in memory and accessed quickly during read operations. If you omit READ_BUFFER=, the default value is READ_BUFFER=100. The maximum number of rows allowed is dependent on the amount of memory that is available to your system.

READ_BUFFER= can also be specified with the BUFFSIZE= alias.

READ_ISOLATION_LEVEL=1 | 2 | 3

specifies which level of read isolation locking for SYBASE to use when it reads tables and views.

If you omit READ_ISOLATION_LEVEL=, the default value is READ_ISOLATION_LEVEL=1, which is the SYBASE default transaction isolation level that prevents dirty reads. Refer to your SYBASE documentation for more information.

READ_ISOLATION_LEVEL= can also be specified with the READ_ISO_LVL= and R_ISOLVL= aliases.

READ_LOCK_TYPE=NOLOCK

specifies the method of locking to use during the reading of tables and views.

In the SAS/ACCESS Interface to SYBASE, the default value and only valid value is READ_LOCK_TYPE=NOLOCK.

For a full description of this option, refer to SAS/ACCESS LIBNAME Statement.

SCHEMA=schema-name

allows you to view another user's database tables and views. If you omit SCHEMA=, you can view only your own tables and views.

SCHEMA= can also be specified with the OWNER= alias.

UPDATE_ISOLATION_LEVEL=1 | 3

specifies which level of read isolation locking for SYBASE to use when it reads tables and views for update.

If you omit UPDATE_ISOLATION_LEVEL=, the default value is UPDATE_ISOLATION_LEVEL=1, which is the SYBASE default cursor isolation level. SYBASE uses a shared or update lock on base table pages that contain rows that represent a current cursor position. This option applies only when UPDATE_LOCK=PAGE. Refer to your SYBASE documentation for more information.

UPDATE_ISOLATION_LEVEL= can also be specified with the CURSOR_ISOLATION_LEVEL=, CURSOR_ISO_LVL=, UPD_ISO_LVL=, and U_ISOLVL= aliases.

Note:   This option applies to updates only when UPDATE_LOCK_TYPE=PAGE because cursor updating is in effect. It does not apply when UPDATE_LOCK_TYPE=NOLOCK.  

UPDATE_LOCK_TYPE=NOLOCK | PAGE

specifies the method of locking to use during the updating of tables and views.

If you specify UPDATE_LOCK_TYPE=NOLOCK, SAS/ACCESS uses SYBASE browse mode updating, in which the table that is being updated must have a primary key and a timestamp column. If you specify UPDATE_LOCK_TYPE=PAGE, SAS/ACCESS uses a cursor that can be updated. When you use UPDATE_LOCK_TYPE=PAGE, it is recommended that the table have a defined primary key. If you omit UPDATE_LOCK_TYPE=, the default value is UPDATE_LOCK_TYPE=PAGE.

For a full description of this option, refer to SAS/ACCESS LIBNAME Statement.

Example 1: Specifying a LIBNAME Statement to Access SYBASE Data

In this example, the libref MYDBLIB connects to a SYBASE database. The SAS/ACCESS engine connection options are USER=, PASSWORD=, DATABASE=, and SERVER=.

libname mydblib sybase user=testuser 
   password=testpass database=testdb
   server=testserver;

proc print data=mydblib.CUSTOMERS; 
   where gender='M';   
run;

Example 2: Appending SAS Data to a SYBASE Table Using BULKCOPY=

In this example, rows from the SAS data set, HR2.HIRES90S, are being added to the SYBASE table, MIDHIRES.STAFF. By specifying the BULKCOPY=YES data set option, the rows are inserted into the SYBASE tables as a unit. The BULKCOPY= option enables the connection to perform a bulk copy and, therefore, the rows of data are appended more quickly.

libname midhires sybase user=alhafez password=football server=hr_svr
               database=personnel;
libname hr2 'Your-SAS-Data-Library'; 
proc append base=midhires.staff(bulkcopy=yes) data=hr2.hires90s;
             where HIREDATE between '01JAN1990'd and '31DEC1997'd; 
run;
 

The SQL Chapter in the SAS Procedures Guide for enhancements in Version 7 and Version 8.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.