SASTRACE= System Option

Generates trace information about a data source.

Valid in:	OPTIONS statement, configuration file, SAS invocation
Default:	NONE
Supports:	All

Syntax

Syntax Description

Details

Examples

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

Example 2: Using Log Trace ',,d,'

Example 3: Using Time Trace ',,,s'

Syntax

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

Syntax Description

',,,d'

specifies that all SQL statements that were sent to the data source are sent to the log. These statements include the following:

SELECT	DELETE
CREATE	SYSTEM CATALOG
DROP	COMMIT
INSERT	ROLLBACK
UPDATE

For those data sources that do not generate SQL statements, the API calls, including all parameters, are sent to the log.

',,d,'

specifies that all routine calls are sent to the log. When this option is selected, all function enters and exits, as well as pertinent parameters and return codes, are traced. The information, however, varies for each data source.

This option is most useful if you are having a problem and need to send a SAS log to SAS Technical Support for troubleshooting.

'd,'

specifies that all data source calls, such as API and Client calls, connection information, column bindings, column error information, and row processing are sent to the log. However, this information varies for each data source.

This option is most useful if you are having a problem and need to send a SAS log to SAS Technical Support for troubleshooting.

',,,s'

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

',,,sa'

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

Details

SASTRACE= is a powerful tool to use when you want to see the commands that are sent to your data source. SASTRACE= output is data source specific. However, most data sources show you statements like SELECT or COMMIT as the data source processes them for the SAS application. To manage SASTRACE= output, consider the following:

When using SASTRACE= on PC platforms, you must also specify the SASTRACELOC= system option.
To turn SAS tracing off, you can specify the following option:
```
options sastrace=off;
```
Log output is much easier to read if you specify NOSTSUFFIX.

Note: NOSTSUFFIX is not supported on z/OS.

The following code is entered without specifying the option, and the resulting log is longer and harder to decipher.

options sastrace=',,,d' sastraceloc=saslog;

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;

proc print data=mydblib.snow_birthdays;
run;

The resulting log is as follows:

  17 1544121286 du_prep 825 PRINT
SASTSE_5: Prepared: on connection 0 18 1544121286 du_prep 825 PRINT
SELECT * FROM SNOW_BIRTHDAYS 19 1544121286 du_prep 825 PRINT
  20 1544121286 du_prep 825 PRINT
  21 1544121286 du_exec 1795 PRINT
SASTSE_6: Executed: on connection 0 22 1544121286 du_exec 1795 PRINT
Prepared statement SASTSE_5 23 1544121286 du_exec 1795 PRINT
  24 1544121286 du_exec 1795 PRINT

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

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;

proc print data=mydblib.snow_birthdays;
run;

The resulting log is as follows:

SASTSE_1: Prepared: on connection 0
SELECT * FROM SNOW_BIRTHDAYS
 
8          proc print data=mydblib.snow_birthdays; run;

 
SASTSE_2: Executed: on connection 0
Prepared statement SASTSE_1

Examples

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

The examples in this section are based on the following table, and are shown using 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;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;
data mydblib.snow_birthdays;
   set work.winter_birthdays;
run;
libname mydblib clear;

The output is written to the SAS log, as specified by the SASTRACELOC=SASLOG system option.

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

10   options sastrace=',,,d' sastraceloc=saslog nostsuffix;
SASTSE: AUTOCOMMIT is NO for connection 0
11         options sastrace=',,,d' sastraceloc=saslog nostsuffix;
12         libname mydblib fedsvr
12       ! connect_string=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
12       ! XXXXXXXXXXXXXXXXXXX;
NOTE: Libref MYDBLIB was successfully assigned as follows:
      Engine:        FEDSVR
      Physical Name:
13
14         proc delete data=mydblib.snow_birthdays;run;
SASTSE: AUTOCOMMIT is NO for connection 1
SASTSE: AUTOCOMMIT turned ON for connection id 1
SASTSE_1: Executed: on connection 1
DROP  TABLE SNOW_BIRTHDAYS
SASTSE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
NOTE: Deleting MYDBLIB.SNOW_BIRTHDAYS (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.28 seconds
      cpu time            0.04 seconds
15
16         data mydblib.snow_birthdays;
17         set work.winter_birthdays;
18         run;
SASTSE_2: Prepared: on connection 1
SELECT * FROM SNOW_BIRTHDAYS WHERE 0=1
SASTSE: AUTOCOMMIT is NO for connection 2
SASTSE_3: Executed: on connection 2
CREATE TABLE SNOW_BIRTHDAYS (empid DOUBLE,birthdat DATE,lastname VARCHAR(18))
SASTSE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
SASTSE: COMMIT performed on connection 2.
SASTSE_4: Prepared: on connection 2
INSERT INTO SNOW_BIRTHDAYS (empid,birthdat,lastname)  VALUES ( ? , ? , ? )

SASTSE_5: Executed: on connection 2
Prepared statement SASTSE_4
SASTSE_6: Executed: on connection 2
2                                        The SAS System            11:39 Friday, December 5, 2008
Prepared statement SASTSE_4
SASTSE_7: Executed: on connection 2
Prepared statement SASTSE_4
NOTE: There were 3 observations read from the data set WORK.WINTER_BIRTHDAYS.
SASTSE: COMMIT performed on connection 2.
NOTE: The data set MYDBLIB.SNOW_BIRTHDAYS has 3 observations and 3 variables.
SASTSE: COMMIT performed on connection 2.
SASTSE: COMMIT performed on connection 2.
NOTE: DATA statement used (Total process time):
      real time           0.29 seconds
      cpu time            0.01 seconds
19
SASTSE: AUTOCOMMIT turned ON for connection id 0
SASTSE_8: Prepared: on connection 0
SELECT * FROM SNOW_BIRTHDAYS
20         proc print data=mydblib.snow_birthdays;run;
SASTSE_9: Executed: on connection 0
Prepared statement SASTSE_8
NOTE: There were 3 observations read from the data set MYDBLIB.SNOW_BIRTHDAYS.
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           3.04 seconds
      cpu time            0.34 seconds
21
22
23         libname mydblib clear;
NOTE: Libref MYDBLIB has been deassigned.

Example 2: Using Log Trace ',,d,'

options sastrace=',,d,' sastraceloc=saslog nostsuffix;
libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;
data mydblib.snow_birthdays;
   set work.winter_birthdays;
run;
libname mydblib clear;

The output is written to the SAS log, as specified by the SASTRACELOC=SASLOG system option.

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

11   options sastrace=',,d,' sastraceloc=saslog nostsuffix;
12
ACCESS ENGINE:  Entering dbiconi.
ACCESS ENGINE:  Exiting dbiconi.  rc=0x00000000
ACCESS ENGINE: Entering DBICON
ACCESS ENGINE:  CONNECTION= SHAREDREAD
SASTSE: Successful connection made, connection id 0
ACCESS ENGINE: Successful physical conn id 0
ACCESS ENGINE: Number of connections is 1
ACCESS ENGINE: Exiting DBICON with  rc=0X00000000
13         options sastrace=',,d,' sastraceloc=saslog nostsuffix;
14
15         libname mydblib fedsvr
15       ! connect_string=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
15       ! XXXXXXXXXXXXXXXXXXX;
NOTE: Libref MYDBLIB was successfully assigned as follows:
      Engine:        FEDSVR
      Physical Name:
16
17         data mydblib.snow_birthdays;
18         set work.winter_birthdays;
19         run;
NOTE: Libref MYDBLIB has been deassigned.
ACCESS ENGINE:  Entering yoeopen
ACCESS ENGINE: Entering DBIEXST with table name being SNOW_BIRTHDAYS
ACCESS ENGINE: Using a utility connection for verifying table existence
ACCESS ENGINE: Entering DBICON
ACCESS ENGINE:  CONNECTION= SHAREDREAD
SASTSE: Successful connection made, connection id 1
ACCESS ENGINE: Successful physical conn id 1
ACCESS ENGINE: Number of connections is 2
ACCESS ENGINE: Exiting DBICON with  rc=0X00000000
ACCESS ENGINE: Entering dbiopen
SASTSE: Enter duopen, table is SNOW_BIRTHDAYS, openmode is INPUT, statement 0, connection 1
SASTSE: Using FETCH for file SNOW_BIRTHDAYS on connection 1
SASTSE: Enter setconloc, table is SNOW_BIRTHDAYS, statement 0, connection 1
SASTSE: Exit duopen, rc = 0x00000000
ACCESS ENGINE: Successful dbiopen, open id 0, connect id 1
ACCESS ENGINE: Exit dbiopen with rc=0X00000000
SASTSE: Enter duexist, table is SNOW_BIRTHDAYS, statement 0, connection 1
SASTSE: Exit duexist, table DOES NOT exist
ACCESS ENGINE: Entering dbiclose
SASTSE: Enter duclose, table is SNOW_BIRTHDAYS, statement 0, connection 1
SASTSE: Exit duclose, rc = 0x00000000
ACCESS ENGINE: DBICLOSE open_id 0, connect_id 1
ACCESS ENGINE: Exiting dbiclos with rc=0X00000000
ACCESS ENGINE: Exit DBIEXST rc=0X00403809
ACCESS ENGINE:  Open Mode is XO_OUTPUT
ACCESS ENGINE:  Access Mode is XO_SEQ
ACCESS ENGINE:  Shr flag is XHSHRMEM

ACCESS ENGINE: Entering DBICON
ACCESS ENGINE:  CONNECTION= SHAREDREAD
SASTSE: Successful connection made, connection id 2
ACCESS ENGINE: Successful physical conn id 2
ACCESS ENGINE: Number of connections is 3
ACCESS ENGINE: Exiting DBICON with  rc=0X00000000
ACCESS ENGINE: Entering dbiopen
SASTSE: Enter duopen, table is SNOW_BIRTHDAYS, openmode is OUTPUT, statement -99, connection 2
SASTSE: Using FETCH for file SNOW_BIRTHDAYS on connection 2
SASTSE: Enter setconloc, table is SNOW_BIRTHDAYS, statement -99, connection 2
SASTSE: Exit duopen, rc = 0x00000000
ACCESS ENGINE: Successful dbiopen, open id 0, connect id 2
ACCESS ENGINE: Exit dbiopen with rc=0X00000000
ACCESS ENGINE: Exit yoeopen with SUCCESS.
ACCESS ENGINE:  Begin yoeinfo
ACCESS ENGINE: Exit yoeinfo with SUCCESS.
SASTSE: Enter duoload, table is SNOW_BIRTHDAYS, statement 0, connection 2
ACCESS ENGINE: Entering dbrload with SQL Statement set to
          CREATE TABLE SNOW_BIRTHDAYS (empid DOUBLE,birthdat DATE,lastname VARCHAR(18))
SASTSE: Enter duexec, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit duexec, rc = 0x00000000
SASTSE: Enter duforc, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit duforc, rc = 0x00000000
SASTSE: Exit duoload, rc = 0x00000000
SASTSE: Enter duins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Enter prepins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit prepins, rc = 0x00000000
SASTSE: Enter doins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit doins, rc = 0x00000000
SASTSE: Exit duins, rc = SUCCESS
SASTSE: Enter duins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Enter doins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit doins, rc = 0x00000000
SASTSE: Exit duins, rc = SUCCESS
SASTSE: Enter duins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Enter doins, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit doins, rc = 0x00000000
SASTSE: Exit duins, rc = SUCCESS
NOTE: There were 3 observations read from the data set WORK.WINTER_BIRTHDAYS.
SASTSE: Enter duforc, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit duforc, rc = 0x00000000
NOTE: The data set MYDBLIB.SNOW_BIRTHDAYS has 3 observations and 3 variables.
ACCESS ENGINE:  Enter yoeclos
ACCESS ENGINE: Entering dbiclose
SASTSE: Enter duclose, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Enter duforc, table is SNOW_BIRTHDAYS, statement 0, connection 2
SASTSE: Exit duforc, rc = 0x00000000
SASTSE: Exit duclose, rc = 0x00000000
ACCESS ENGINE: DBICLOSE open_id 0, connect_id 2
ACCESS ENGINE: Exiting dbiclos with rc=0X00000000
ACCESS ENGINE: Entering DBIDCON
SASTSE: Successful disconnection, connection id 2
SASTSE: Successful CLI free environment from connection 1
ACCESS ENGINE: Physical disconnect on id = 1
ACCESS ENGINE: Exiting DBIDCON with rc=0X00000000, rc2=0X00000000
20
21   libname mydblib clear;

Example 3: Using Time Trace ',,,s'

options sastrace=',,,s' sastraceloc=saslog nostsuffix;
libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;
data mydblib.snow_birthdays;
   set work.winter_birthdays;
run;
libname mydblib clear;

The output is written to the SAS log, as specified by the SASTRACELOC=SASLOG system option.

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

11         options sastrace=',,,s' sastraceloc=saslog nostsuffix;
12
13         libname mydblib fedsvr  connect_string=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
NOTE: Libref MYDBLIB was successfully assigned as follows:
      Engine:        FEDSVR
      Physical Name:
14
15         data mydblib.snow_birthdays;
16         set work.winter_birthdays;
17         run;
Summary Statistics for FEDSVR are:
Total SQL prepare seconds were:                     0.009349
Total seconds used by the FEDSVR ACCESS engine were     0.010446
NOTE: There were 3 observations read from the data set WORK.WINTER_BIRTHDAYS.
NOTE: The data set MYDBLIB.SNOW_BIRTHDAYS has 3 observations and 3 variables.
Summary Statistics for FEDSVR are:
Total SQL execution seconds were:                   0.092450
Total SQL prepare seconds were:                     0.026569
Total seconds used by the FEDSVR ACCESS engine were     0.127992
NOTE: DATA statement used (Total process time):
      real time           0.46 seconds
      cpu time            0.06 seconds
18
19         libname mydblib clear;
NOTE: Libref MYDBLIB has been deassigned.