SAS System Options, Settings, and Macros for DB2 under z/OS

System Options

You can use these SAS system options when you start a SAS session that accesses DB2 under z/OS.

DB2DBUG | NODB2DBUG

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. NODB2DBUG is the default.

For example, if you submit a PROC PRINT statement that references a DB2 table, the DB2 SQL query is displayed in the SAS log. SAS/ACCESS Interface to DB2 under z/OS generates this query.

libname mylib db2 ssid=db2;  

proc print data=mylib.staff; 
run;  

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

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

DB2DECPT=decimal-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 start SAS.

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

lets you specify the database and table space in which you want to create a new table. The DB2IN= 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 table space.

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

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

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 SAS/ACCESS Interface to DB2 under z/OS 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.

DB2RRS | NODB2RRS

specifies the attachment facility to be used for a 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 How the Interface to DB2 Works.

DB2RRSMP | NODB2RRSMP

specifies that the multiphase SRRCMIT commit and SRRBACKrollback calls are used instead of the COMMIT and ROLLBACK SQL statements. This option is ignored unless DB2RRS is specified. This option is available only at invocation.

Specify NODB2RRSMP, the default, when DB2 is the only Resource Manager for your application. Specify DB2RRSMP when your application has other resource managers, which requires the use of the multiphase calls. Using the multiphase calls when DB2 is your only resource manager can have performance implications. Using COMMIT and ROLLBACK when you have more than one resource manager can result in an error, depending on the release of DB2.

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. For more information, see Settings.

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

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

DB2UPD=Y | N

specifies whether the user has privileges through SAS/ACCESS Interface to DB2 under z/OS to update DB2 tables. This option applies only to the user's Update 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 start SAS. This option does not affect the SQL pass-through facility, PROC DBLOAD, or the SAS 5 compatibility procedures.

Settings

To connect to DB2, you must specify a valid DB2 subsystem name in one of these ways.

the DB2SSID= system option. SAS/ACCESS Interface to DB2 under z/OS uses this value if no DB2 subsystem is specified.
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 SQL pass-through facility
the SSID= connection option in the LIBNAME statement

If a site does not specify a valid DB2 subsystem when it accesses DB2, this message is generated:

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

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.

Macros

Use the automatic SYSDBRC macro variable 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 SAS/ACCESS Interface to DB2 under z/OS. 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, you can capture the SAS log by using the SAS system option, ALTLOG.