SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure: DB2 Specifics

The DBLOAD procedure enables you to create and load a DBMS table from a SAS data set. This section describes the statements that you use in the SAS/ACCESS Interface to DB2. PROC DBLOAD is valid for DB2 running under HP-UX, SUN, R6000, OS/2, Windows95, and WinNT operating environments.

DBLOAD Procedure Statements for DB2

To create and load a DB2 table, the SAS/ACCESS Interface to DB2 uses the following statements in batch mode.

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

IN|DATABASE|DSN=<'>database-name<'>;

USER|UID=<'>username<'>;

PASSWORD|PASS|PWD|PW|
USING=<'>password<'>;

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

ACCDESC | ACCESS | AD=<libref.>access-descriptor;

COMMIT=commit-frequency;

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

ERRLIMIT=error-limit;

LABEL;

LIMIT=load-limit;

LIST<ALL|COLUMN|variable-identifier>;

NULLSvariable-identifier-1 = Y|N|D<...variable-identifier-n= Y|N>;

QUIT | EXIT;

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

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

SQL DBMS-specific SQL-statement;

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

WHERESAS-where-expression;

LOAD;

RUN;

IN | DATABASE | DSN= <'>database-name<'>;

specifies the name of the database in which you want to store the new DB2 table. The IN statement is required and must immediately follow the PROC DBLOAD statement. Database name is limited to eight characters. DATABASE= is an alias for the IN statement.

The database that you specify must already exist. If the database name contains the following special characters (_,$,@,#), you must enclose it in quotes. However, DB2 recommends against using special characters in database names.

USER | UID= <'>username <'>;

enables you to connect to a DB2 database, such as SQL Server or AS/400, with a user ID that is different from the default login ID.

The USER= and PASSWORD= statements are optional in DB2. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID is used.

Operating Environment Information: The USER= statement does not apply if you are running DB2 under OS/2. [cautionend]

PASSWORD | PASS | PWD | PW | USING= <'>password<'>;

specifies the DB2 password that is associated with your user ID.

The USER= and PASSWORD= statements are optional in DB2 because users have default user IDs. If you specify USER=, you must specify PASSWORD=.

Operating Environment Information: The PASSWORD= statement does not apply if you are running DB2 under OS/2. [cautionend]

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

identifies the DB2 table or DB2 view that you want to use to create an access descriptor. Table name is limited to 18 characters. If you quote the name, it is case-sensitive. A DB2 table with the same name cannot already exist. The TABLE= statement is required.

The schema-name is a person's name or group ID that is associated with the DB2 table. The schema name is limited to eight characters and is required in batch mode.

ACCDESC | ACCESS | AD=<libref.>access-descriptor;

creates an access descriptor that is based on the DB2 table that you are creating and loading.

Operating Environment Information: The ACCDESC= statement applies only if you are running DB2 under OS/2. [cautionend]

DBLOAD Procedure Examples

The following example creates a new DB2 table, SASDEMO.EXCHANGE, from the MYDBLIB.RATEOFEX data file on an OS/2 platform. An access descriptor ADLIB.EXCHANGE is also created, based on the new table. You must be granted the appropriate privileges in order to create new DB2 tables or views. For the DBMS= option of the PROC DBLOAD procedure, use db2.

proc dbload dbms=db2 data=mydblib.rateofex;
   in=sample; user=testuser; password=testpass;
   table=sasdemo.exchange;
   accdesc=adlib.exchange; /* only applies to OS/2 */
   rename fgnindol=fgnindollars 
          4=dollarsinfgn;
   nulls updated=n fgnindollars=n 
         dollarsinfgn=n country=n;
   load;
run;

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

proc dbload dbms=db2;
   in=sample;
   sql grant select on sasdemo.exchange 
      to testuser;
run;

Chapter Contents
Previous
Next
Top of Page