Macro Variables and System Options for Relational Databases

SASTRACE= System Option

Generates trace information from a DBMS engine.

Default value:	none
Valid in:	configuration file, SAS invocation, OPTIONS statement
DBMS support:	DB2 under UNIX and PC Hosts, DB2 under z/OS, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Teradata

Syntax
Syntax Description
Examples
	Example 1: SQL Trace ',,,d'
	Example 2: Log Trace ',,d'
	Example 3: DBMS Trace 'd,'
	Example 4: Brief SQL Trace ',,,db'
	Example 5: Time Trace ',,,s'
	Example 6: Time All Trace ',,,sa'
	Example 7: Threaded Trace ',,t,'

Syntax

SASTRACE= ',,,d' | ' ,,d,' | ' d,' | ',,,db' | ' ,,,s' | ' ,,,sa' | ',,t,'

Syntax Description

',,,d'

specifies that all SQL statements that are sent to the DBMS are sent to the log. Here are the applicable statements:

SELECT	DELETE
CREATE	SYSTEM CATALOG
DROP	COMMIT
INSERT	ROLLBACK
UPDATE

For engines that do not generate SQL statements, API calls and all parameters are sent to the log.

',,d,'

specifies that all routine calls are sent to the log. All function enters, exits, and pertinent parameters and return codes are traced when you select this option. The information varies from engine to engine, however.

This option is most useful if you have a problem and need to send a SAS log to technical support for troubleshooting.

'd,'

specifies that all DBMS calls--such as API and client calls, connection information, column bindings, column error information, and row processing--are sent to the log. This information will vary from engine to engine, however.

This option is most useful if you have a problem and need to send a SAS log to technical support for troubleshooting.

',,,db'

specifies that only a brief version of all SQL statements that the ',,,d' option normally generates are sent to the log.

',,,s'

specifies that a summary of timing information for calls made to the DBMS is sent to the log.

',,,sa'

specifies that timing information for each call that is made to the DBMS is sent to the log along with a summary.

',,t,'

specifies that all threading information is sent to the log. Here is the information it includes:

the number of threads that are spawned
the number of observations that each thread contains
the exit code of the thread, if it fails

Details Specific to SAS/ACCESS

The SASTRACE= options have behavior that is specific to SAS/ACCESS software. SASTRACE= is a very powerful tool to use when you want to see the commands that SAS/ACCESS sent to your DBMS. SASTRACE= output is DBMS-specific. However, most SAS/ACCESS engines show you statements like SELECT or COMMIT as the DBMS processes them for the SAS application. These details below can help you manage SASTRACE= output in your DBMS.

When using SASTRACE= on PC platforms, you must also specify SASTRACELOC=.
To turn SAS tracing off, specify this option:
```
options sastrace=off;
```

Log output is much easier to read if you specify NOSTSUFFIX. (NOSTSUFFIX is not supported on z/OS.) Because this code is entered without specifying the option, the resulting log is longer and harder to decipher.

options sastrace=',,,d' sastraceloc=saslog;
proc print data=mydblib.snow_birthdays;
run;

Here is the resulting log.

0 1349792597 sastb_next 2930 PRINT
ORACLE_5: Prepared: 1 1349792597 sastb_next 2930 PRINT
SELECT * FROM scott.SNOW_BIRTHDAYS 2 1349792597 sastb_next 2930 PRINT
3 1349792597 sastb_next 2930 PRINT
16 proce print data=mydblib.snow_birthdays; run;

4 1349792597 sastb_next 2930 PRINT
ORACLE_6: Executed: 5 1349792597 sastb_next 2930 PRINT
Prepared statement ORACLE_5 6 1349792597 sastb_next 2930 PRINT
7 1349792597 sastb_next 2930 PRINT

However, by using NOSTSUFFIX, the log file becomes much easier to read.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc print data=mydblib.snow_birthdays;
run;

Here is the resulting log.

ORACLE_1: Prepared:
SELECT * FROM scott.SNOW_BIRTHDAYS

12 proc print data=mydblib.snow_birthdays; run;

ORACLE_2: Executed:
Prepared statement ORACLE_1

Examples

These examples use NOSTSUFFIX and SASTRACELOC=SASLOG and are based on this data set:

data work.winter_birthdays;
   input empid birthdat date9. lastname $18.;
   format birthdat date9.;
datalines;
678999 28DEC1966 PAVEO             JULIANA        3451
456788 12JAN1977 SHIPTON           TIFFANY        3468
890123 20FEB1973 THORSTAD          EDVARD         3329
;
run;