DBLOAD Procedure Specifics for DB2 under UNIX and PC Hosts

Key Information

For general information about this feature, see DBLOAD Procedure. DB2 under UNIX and PC Hosts examples are available.

SAS/ACCESS Interface to DB2 under UNIX and PC Hosts supports all DBLOAD procedure statements in batch mode. Here are the DBLOAD procedure specifics for the DB2 under UNIX and PC Hosts interface.

DBMS= value is DB2.
Here are the database description statements that PROC DBLOAD uses:

IN= <'>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. The 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 _, $, @, or # special character, you must enclose it in quotation marks. DB2 recommends against using special characters in database names, however.

USER= <'>user name<'>;

lets you connect to a DB2 database with a user ID that is different from the default login ID.

USER= is optional in SAS/ACCESS Interface to DB2 under UNIX and PC Hosts. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID is used.

PASSWORD= <'>password<'>;

specifies the password that is associated with your user ID.

PASSWORD= is optional in SAS/ACCESS Interface to DB2 under UNIX and PC Hosts because users have default user IDs. If you specify USER=, however, you must specify PASSWORD=.

If you do not wish to enter your DB2 password in uncoded text on this statement, see PROC PWENCODE in Base SAS Procedures Guide for a method to encode it.
Here is the TABLE= statement:

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

identifies the DB2 table or DB2 view that you want to use to create an access descriptor. The table-name is limited to 18 characters. If you use quotation marks, the name is case sensitive. 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.
Here is the NULLS statement.

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

lets you specify whether the DB2 columns that are associated with the listed SAS variables allow NULL values. By default, all columns accept NULL values.

The NULLS statement accepts any one of these 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.

Examples

The following example creates a new DB2 table, SASDEMO.EXCHANGE, from the MYDBLIB.RATEOFEX data file. You must be granted the appropriate privileges in order to create new DB2 tables or views.

proc dbload dbms=db2 data=mydblib.rateofex;
   in='sample';
   user='testuser';
   password='testpass';
   table=sasdemo.exchange;
      rename fgnindol=fgnindollars
         4=dollarsinfgn;
   nulls updated=n fgnindollars=n
         dollarsinfgn=n country=n;
   load;
run;

The following 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;