STARJOIN RESET Statement Options

SPD Server uses RESET statements in SPD Server SQL to provide information about and to configure SPD Server STARJOIN settings.

The NOSTARJOIN option suppresses the use of the SPD Server STARJOIN optimizer in the planning and running of SQL statements that have valid STARJOIN patterns or star schemas. When NOSTARJOIN is enabled, SPD Server ignores STARJOIN and uses pairwise joins to plan and run SQL statements. The default setting is NOSTARJOIN=0, which means that STARJOIN is enabled, and STARJOIN optimization occurs when SQL recognizes a valid SPD Server pattern or star schema.

Usage:

execute(reset nostarjoin=<1/0>)
  by sasspds ; 

STARMAGIC is the STARJOIN counterpart to the SQL MAGIC number option. You can use STARMAGIC options to manually adjust certain internal STARJOIN heuristics to improve certain join strategies.

The STARMAGIC option uses bit flags to configure the STARJOIN code. You can select different controls by adding the values for the bit flags below

execute(reset starmagic=<1/2/4/8/16>)
  by sasspds ; 

The RESET DETAILS option prints details about your SPD Server STARJOIN facility settings. All internal STARJOIN debugging information is tied to the stj$ DETAILS key. You issue the stj$ reset option to display available information as SPD Server attempts to validate a join subtree. The RESET DETAILS="stj$" option is very useful for debugging STARJOIN and SQL statement execution.

execute(reset details="stj$")
  by sasspds ;  

The following example connects to sasspds. Then the code issues the "stj$" RESET option to display all available information as SPD Server attempts to validate the join subtree for the SQL on a star schema. The STARMAGIC=16 setting disables the STARJOIN COMPOSITE join strategy (STARJOIN COMPOSITE joins are enabled by default in SPD Server). The NOSTARJOIN=0 setting means that STARJOIN is enabled (or resets a disabled STARJOIN facility) and ensures that STARJOIN optimization occurs if SPD Server SQL recognizes a valid SPD Server pattern or star schema. (The STARJOIN facility is enabled by default in SPD Server.)

After you submit the following SQL statements, the code disconnects from sasspds and quits:

    PROC SQL;
      connect to sasspds
        (dbq="star"
         server=sunburn.5007
         user='anonymous');

      execute (reset
         DETAILS="stj$"
         STARMAGIC=16
         NOSTARJOIN=0)

      by sasspds;

      execute (
         ...
         SQL statements
         ...);
      by sasspds;

      disconnect from sasspds;
    quit;