SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure: DB2 Specifics

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

DBLOAD Procedure Statements for DB2

To create and load a DB2 table, the SAS/ACCESS interface to DB2 uses the following statements in interactive line, noninteractive, or batch mode.

Note: Unlike some other SAS/ACCESS interface products, the SAS/ACCESS interface to DB2 does not use the following procedure statements: USER= and PASSWORD=. [cautionend]

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

INdatabase.tablespace |'DATABASE database';

SSID=DB2-subsystem-id;

SERVER=DB2-database-system|DRDA-database-system;

TABLE=<authorization-id.>table-name;

ACCDESC=<libref.>access-descriptor;

COMMIT=commit-frequency;

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

ERRLIMIT=error-limit;

LABEL;

LIMIT=load-limit;

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

QUIT;

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

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

SQL DB2 SQL-statement;

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

WHERE SAS-where-expression;

LIST <ALL|COLUMN|variable-identifier>;

LOAD;

RUN;

IN database.tablespace|'DATABASE database';

specifies the nasme of the database or the table space in which you want to store the new DB2 table. A table space can contain multiple tables. The database and tablespace arguments are each limited to 18 characters. The IN statement must immediately follow the PROC DBLOAD statement.

database.tablespace: specifies the names of the database and the table space, which are separated by a period.
'DATABASE database ': specifies only the database name. In this case, you specify the word DATABASE, then a space and the database name. Enclose the entire specification in single quotes.

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

enables you to specify whether the DB2 columns that are associated with the listed SAS variables allow NULL values. By default, all columns accept NULL values. for more information.

The NULLS statement accepts any one of these three values:

Y specifies that the column accepts NULL values. This is the default.

N specifies that the column does not accept NULL values.

D specifies that the column is defined as NOT NULL WITH DEFAULT.

Refer to DB2 NULLs and DB2 Default Values for DB2-specific information on NULL values.

SSID=DB2-subsystem-id;

specifies the DB2 subsystem ID to use for the access descriptor. The DB2-subsystem-id is limited to four characters. Refer to Setting Your DB2 Subsystem Identifier for more information on setting SSID= or contact your DBA.

The SSID= statement is optional. If you omit it, the SAS System connects to the default DB2 subsystem that is specified by the SAS system option DB2SSID=. If your site has not set DB2SSID=, the SSID= statement is required.

SERVER=DRDA-database-system;

enables direct access to DRDA resources (such as SQL/DS tables) from the SAS/ACCESS interface to DB2. SERVER= is an optional statement.

Enter a DRDA database system name that is assigned by your system administrator to make the connection to the desired database system. Check with your system administrator for valid database system names. You can connect with only one server at a time.

TABLE= <authorization-id.>table-name;

identifies the DB2 table that you want to use to create. The table-name is limited to 18 characters. A DB2 table by this name cannot already exist, unless you are using the APPEND option on the PROC DBLOAD statement. The TABLE= statement is required.

The authorization-id is a user ID or group ID that is associated with the DB2 table. The authorization ID is limited to eight characters. If you omit the authorization ID, DB2 uses your TSO (or OS/390) user ID except in batch mode; in batch mode, you must specify an authorization ID.

The following example creates a new DB2 table, TESTID.INVOICE, from the DLIB.INVOICE data file. 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 DB2 tables.

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

proc dbload dbms=db2 data=dlib.invoice;
   ssid=db2;
   table=testid.invoice;
   accdesc=adlib.invoice;
   rename amtbilled = amountbilled
          5 = amountindollars;
   nulls invoicenum=n amtbilled=n;
   load;
run;

Suppose that you just created a SAS data set, WORK.SCHEDULE, which includes the names and work hours of your employees. You can use the SERVER= command to create the DB2 table TESTID.SCHEDULE and load it with the schedule data on the DRDA resource, TESTSERVER, as shown in the next example.

libname adlib 'SAS-data-library';

proc dbload dbms=db2 data=work.schedule;
  in sample;
  server=testserver;
  accdesc=adlib.schedule;
  table=testid.schedule;
  list all;
  load;
run;

Chapter Contents
Previous
Next
Top of Page

Y	specifies that the column accepts NULL values. This is the default.
N	specifies that the column does not accept NULL values.
D	specifies that the column is defined as NOT NULL WITH DEFAULT.