SAS/ACCESS Software for Relational Databases: Reference

SAS System Options and Settings for DB2

You can use the following SAS system options when you invoke a SAS session that accesses DB2:

is used to debug SAS code. When you submit a SAS statement that accesses DB2 data, DB2DBUG displays any DB2 SQL queries (generated by SAS) that are processed by DB2. The queries are written to the SAS log.

For example, if you submit a PROC PRINT statement that references a DB2 table, the DB2 SQL query is displayed in the SAS log. The SAS/ACCESS engine for DB2 generates the DB2 SQL query, as shown in DB2DBUG Display of DB2 SQL Queries.

libname mylib db2 ssid=db2;

proc print data=mylib.staff;
run;

proc sql;
select * from mylib.staff
   order by idnum;
quit;

DB2DBUG Display of DB2 SQL Queries

[IMAGE]

DB2 statements that appear in the SAS log are prepared and described in order to determine whether the DB2 table exists and can be accessed.

DB2DECPT=decpoint-value

specifies the setting of the DB2 DECPOINT option. The decpoint-value argument can be a . (period) or a , (comma). The default is a . (period).

DB2DECPT= is valid as part of the configuration file when you invoke the SAS System.

DB2IN='database-name.tablespace-name' | 'DATABASE database-name'

enables you to specify the database and tablespace in which you want to create a new table. The IN= option is relevant only when you are creating a new table. If you omit this option, the default is to create the table in the default database and tablespace.

database.tablespace specifies the names of the database and tablespace.

'DATABASE database-name' specifies only the database name. Enclose the entire specification in single quotes.

You can override the DB2IN= system option with the IN= libname or data set option.

DB2PLAN=plan-name

specifies the name of the plan that is used when connecting (or binding) SAS to DB2. SAS provides and supports this plan, which can be adapted for each user's site. The value for DB2PLAN= can be changed at any time during a SAS session, so that different plans can be used for different SAS steps. However, if you use more than one plan during a single SAS session, you must understand how and when the SAS/ACCESS engine for DB2 makes the connections. If one plan is in effect and you specify a new plan, the new plan does not affect the existing DB2 connections.

For details about how connections are managed by the SAS/ACCESS engine for DB2, see Information for the Database Administrator and Maximizing DB2 Performance.

DB2RRS | NODB2RRS

specifies the attachment facility to be used for this SAS session when connecting to DB2. This option is an invocation only option.

Specify NODB2RRS, the default, to use the Call Attachment Facility (CAF). Specify DB2RRS to use the Recoverable Resource Manager Services Attachment Facility (RRSAF). For details about using RRSAF, see Recoverable Resource Management Services Attachment Facility (RRSAF).

DB2SSID=subsystem-name

specifies the DB2 subsystem name. The subsystem-name argument is one to four characters that consist of letters, numbers, or national characters (#, $, or @); the first character must be a letter. The default value is DB2.

DB2SSID= is valid in the OPTIONS statement, as part of the configuration file, and when you invoke the SAS System.

You can override the DB2SSID= system option with the SSID= LIBNAME option.

DB2UPD=Y | N

specifies whether the user has privileges through the SAS/ACCESS interface to update DB2 tables. This option applies only to the user's updating privileges through the interface and not necessarily to the user's privileges while using DB2 directly. Altering the setting of DB2UPD= has no effect on your DBMS privileges, which have been set with the GRANT statement. The default is Y (Yes).

DB2UPD= is valid in the OPTIONS statement, as part of the configuration file, and when you invoke the SAS System.

Setting Your DB2 Subsystem Identifier

To connect to DB2, a valid DB2 subsystem name must be specified in one of the following ways:

the DB2SSID= system option. This value is used by the SAS/ACCESS interface if no DB2 subsystem is specified. Refer to SAS System Options and Settings for DB2 for more information.
the SSID= option in the PROC ACCESS statement.
the SSID= statement of PROC DBLOAD.
the SSID= option in the PROC SQL CONNECT statement, which is part of the Pass-Through facility.
the SSID= option in the LIBNAME statement

If a site does not specify a valid DB2 subsystem when accessing DB2, the following message is generated:

    ERROR: Cannot connect to DB2 subsystem XXXX, 
      rc=12, reason code = 00F30006. Refer 
      to the Call Attachment Facility documentation 
      for an explanation.

where XXXX is the name of the subsystem to which SAS tried to connect. To find the correct value for your DB2 subsystem ID, contact your database administrator.

Capturing DB2 Return Codes Using SYSDBRC

Use the automatic macro variable SYSDBRC to capture DB2 return codes when using the DB2 engine. The macro variable is set to the last DB2 return code that was encountered only when execution takes place through the SAS/ACCESS interface to the DB2 engine. If you reference SYSDBRC before engine processing takes place, you receive this message:

WARNING: Apparent symbolic reference SYSDBRC 
  not resolved.

Use SYSDBRC for conditional post-processing. Below is an example of how to abend a job. The table DB2TEST is dropped from DB2 after the view descriptor is created, resulting in a -204 code.

data test;
x=1;y=2;
proc dbload dbms=db2 data=test; table=db2test; 
   in 'database test';
load;run;

proc access dbms=db2;
create work.temp.access;
table=user1.db2test;
create work.temp.view;
select all;
run;
proc sql;
execute(drop table db2test)by db2;
quit;

proc print data=temp;
run;

data _null_;
if "&sysdbrc" not in ('0','100') then
 do;
   put 'The DB2 Return Code is: ' "&sysdbrc";
   abort abend;
 end;
run;

Because the abend prevents the log from being captured, the SAS log can be captured by using the SAS system option ALTLOG. Refer to the SAS Companion for the OS/390 Environment for information on ALTLOG.

Chapter Contents
Previous
Next
Top of Page