SAS/ACCESS Interface to DB2 Under z/OS

DBLOAD Procedure Specifics for DB2 Under z/OS

See DBLOAD Procedure for general information about this feature. DB2 z/OS examples are available.

SAS/ACCESS Interface to DB2 under z/OS supports all DBLOAD procedure statements in interactive line, noninteractive, and batch modes. Here are the DBLOAD procedure specifics for SAS/ACCESS Interface to DB2 under z/OS.

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

SSID=DB2-subsystem-id

specifies the DB2 subsystem ID to connect to at connection time. SSID= is optional. If you omit it, SAS connects to the DB2 subsystem that is specified in the SAS system option, DB2SSID=. The DB2 subsystem ID is limited to four characters. See Settings for more information.

SERVER=DRDA server

specifies the DRDA server that you want to connect to. SERVER= enables you to access DRDA resources stored at remote locations. Check with your system administrator for system names. You can connect to only one server per LIBNAME statement.

SERVER= is optional. If you omit it, you access tables from your local DB2 database unless you have specified a value for the LOCATION= LIBNAME option. There is no default value for this option.

For information about configuring SAS to use the SERVER= option, see the z/OS installation instructions.

IN database.tablespace |'DATABASE database'

specifies the name 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, specify the word DATABASE , followed by a space and the database name. Enclose the entire specification in single quotation marks.
Here is the NULLS= statement:
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.

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.
See DB2 Null and Default Values for information about NULL values that is specific to DB2.
Here is the TABLE= statement:

TABLE= <authorization-id.>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. 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 z/OS) user ID. However, in batch mode you must specify an authorization ID or an error message is generated.

Examples

This example creates a new DB2 table, Testid.Invoice, from the Dlib.Invoice data file. The AmtBilled column and the fifth column in the table (AmountInUS) are renamed. You must have the appropriate privileges before you can 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;

For example, you can create a SAS data set, Work.Schedule, that 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 this 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;

Top of Page