SASTRACE= System Option

Generates trace information from a DBMS engine.

Valid in:	configuration file, SAS invocation, OPTIONS statement
Default:	none
Data source:	DB2 under UNIX and PC Hosts, DB2 under z/OS, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Teradata
Tip:	You can also use more than one SASTRACE= option at a time (for example, `SASTRACE=',,d,d'`).
See:	SASTRACELOC= system option, Generating Trace Information for Threaded Reads

Syntax

Details

Examples

Example 1: Use SQL Trace ',,,d'

Example 2: Use Log Trace ',,d'

Example 3: Use DBMS Trace 'd,'

Example 4: Use Brief SQL Trace ',,,db'

Example 5: Use Time Trace ',,,s'

Example 6: Use Time All Trace ',,,sa'

Example 7: Use 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 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.

',,,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 that it includes:

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

Details

SASTRACE= and SASTRACELOC= behavior 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 can help you manage SASTRACE= output in your DBMS.

When using SASTRACE= on PC platforms, you must also specify SASTRACELOC=.
Here is how to turn SAS tracing off:
```
options sastrace=off;
```

Log output is much easier to read if you specify NOSTSUFFIX. Because this code is entered without specifying the option, the resulting log is longer and harder to decipher.

Note: NOSTSUFFIX is not supported on z/OS.

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

Use NOSTSUFFIX to make the log file 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

Example 1: Use SQL Trace ',,,d'

These examples use NOSTSUFFIX and SASTRACELOC=SASLOG.

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;

Examples are based on this data set.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname mydblib oracle user=scott password=tiger schema=bday_data;
data mydblib.snow_birthdays;
   set work.winter_birthdays;
run;
libname mydblib clear;

Output for this ',,,d' example is written to the SAS log, as specified in the SASTRACELOC=SASLOG option.

SAS Log Output from the SASTRACE= ',,,d' System Option

30   data work.winter_birthdays;
31      input empid birthdat date9. lastname $18.;
32      format birthdat date9.;
33   datalines;
NOTE: The data set WORK.WINTER_BIRTHDAYS has 3 observations and 3 variables.
NOTE: DATA statement used (Total process time):
     real time           0.03 seconds
     cpu time            0.04 seconds
37   ;
38   run;
39   options sastrace=',,,d' sastraceloc=saslog nostsuffix;
40   libname mydblib oracle user=scott password=XXXXX schema=bday_data;
NOTE: Libref MYDBLIB was successfully assigned as follows:
     Engine:        ORACLE
     Physical Name:
41   proc datasets library=mydblib;
  delete snow_birthdays;run;
ORACLE_1: Prepared:
SELECT * FROM SNOW_BIRTHDAYS
ORACLE_2: Executed:
DROP TABLE  SNOW_BIRTHDAYS
NOTE: Deleting MYDBLIB.SNOW_BIRTHDAYS (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
     real time           0.26 seconds
     cpu time            0.12 seconds
42   data mydblib.snow_birthdays;
43      set work.winter_birthdays;
44   run;
ORACLE_3: Prepared:
SELECT * FROM SNOW_BIRTHDAYS
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ORACLE_4: Executed:
CREATE TABLE SNOW_BIRTHDAYS(empid NUMBER ,birthdat DATE,lastname VARCHAR2 (18))
ORACLE_5: Prepared:
INSERT  INTO SNOW_BIRTHDAYS (empid,birthdat,lastname) VALUES
(:empid,TO_DATE(:birthdat,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:lastname)
NOTE: There were 3 observations read from the data set WORK.WINTER_BIRTHDAYS.
ORACLE_6: Executed:
Prepared statement ORACLE_5
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
NOTE: The data set MYDBLIB.SNOW_BIRTHDAYS has 3 observations and 3 variables.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
NOTE: DATA statement used (Total process time):
     real time           0.47 seconds
     cpu time            0.13 seconds
ORACLE_7: Prepared:
SELECT * FROM SNOW_BIRTHDAYS
45   proc print data=mydblib.snow_birthdays; run;
ORACLE_8: Executed:
Prepared statement ORACLE_7
NOTE: There were 3 observations read from the data set MYDBLIB.SNOW_BIRTHDAYS.
NOTE: PROCEDURE PRINT used (Total process time):
     real time           0.04 seconds
     cpu time            0.04 seconds
46
47   libname mydblib clear;
NOTE: Libref MYDBLIB has been deassigned.