SQL Scripting for SAS Federation Server Administration

About the Configuration for SQL Scripting

Overview

SAS Federation Server provides SQL language scripting capabilities to handle administrative needs for start-up and shutdown events. Administrators can write and execute scripts to manage auditing or related event notifications. SQL scripts execute in one of two phases: Startup.Epilog and Stop.Prolog. To run a script, add the name of the script to the server’s configuration file, dfs_serv.xml.

Additional configuration information, including an example, appears at the end of this topic.

About the Configuration for SQL Scripting

XML Format

SQL scripts are specified in an XML configuration file as an SQL node in an OptionSet element that includes a name attribute of SQL:

<OptionSet name="SQL">
...
</OptionSet>

An option set can consist of one or more option names. Option names that belong in an OptionSet will not be assessed correctly if they are placed outside of the OptionSet.

Scripts are arranged in a hierarchical, parent-child format. Here is an example of a parent option set containing two child options. The child option sets are siblings to each other:

<OptionSet name="SQL">
              ...
             <Option name="Command">command 1</Option>
             <Option name="Command">command 2</Option>
              ...
       </OptionSet>

SQL nodes can be nested with the outer-most node, which is the script. Nested nodes correspond to SQL commands that run within the script. Additional XML elements provide execution context for the SQL nodes. Here is an example of nested nodes:

<OptionSet name="SQL">
              <Option name="Condition">SQL Boolean scalar result query</Option>
       <OptionSet name="SQL">
              <Option name="Command">command 1</Option>
              <Option name="Command">command 2</Option>
              ...
       </OptionSet>
       ...
       <OptionSet name="SQL">
              ...
              <Option name="Command">command 1</Option>
              <Option name="Command">command 2</Option>
              ...
       </OptionSet>
</OptionSet>

The nested sibling SQL nodes are highlighted in gray. The commands of each of the inner SQL nodes are run according to the specified error mediation if the condition is true in the outer (parent) SQL node.

Elements of SQL Scripting

Listed below are the valid elements within an SQL node.

SQL Node

<OptionSet name="SQL">...</OptionSet>

The OptionSet specifies an SQL node containing a nested SQL script composed of other elements, each specified within an OptionName.

Name

<Option name=”name”>SQL
                                    node name or description</Option>

Text that specifies the name of the SQL node. This option is used for logging context.

SQL Error Mediation Action

<Option name="SQLErrorMediationAction">Error
                                    mediation action</Option>

Specifies the required action that is needed to mediate errors during SQL command execution:

STOP

Specifies that the script terminate execution when it encounters an error that satisfies the current SQL state and match mode criteria. STOP is the default mediation action.

CONTINUE

Specifies that the script continue executing the next SQL node regardless of encountering an error that satisfies the current SQL state and match mode criteria. This is used when a set of SQL commands should run without regard to the success of those commands previously executed within the same SQL node.

Error mediation for the SQL node is inherited from the nesting SQL node, if it exists.

SQL Error Mediation SQL State Match Mode

<Option name="SQLErrorMediationSSMatchMode">SS
                                    match mode</Option>

Specifies the SQL state match mode used to identify specific SQL states requiring mediation action:

EXCLUDE

Action taken when the SQL state does not match one of the states specified within the set of scoped states of the current SQL node.

INCLUDE

Action taken when the SQL state matches one of the states specified within the set of scoped states of the current SQL node.

IGNORE

Specifies that SQL states within scope are ignored. This causes the mediation action to be honored based on the success or failure of the command regardless of the SQL state. IGNORE is the default match mode.

The SQL state match mode of the SQL node, which is inherited from the nesting SQL node, if it exists.

Phase

<Option name="Phase">Server
                                    phase</Option>

SQL scripts execute in one of two phases: Startup.Epilog and Stop.Prolog.

Startup.Epilog

Startup.Epilog is executed on start-up of SAS Federation Server before listening for connections and after administration DDL can be executed. These scripts can connect to any configured data service and execute SQL that is run as the process user.

Stop.Prolog

Stop.Prolog is executed on SAS Federation Server shutdown after quiescing or dropping client connections. These scripts can connect to any configured data service and execute SQL that is run as the process user.

SQL State

<Option name="SQLState">SQL
                                    state expression</Option>

Specifies an SQL state or prefix that is used to identify specific SQL states requiring error mediation. An SQL state can be specified as a full 5–character mnemonic such as HY001, or as a prefix matching any SQL state starting with a specified prefix. In addition, a leading + character (concatenation operator) can be prepended to the mnemonic to add the SQL state to the current set of constraining SQL states. Without the leading concatenation operator, the specified SQL states replaces any SQL states that were previously specified.

The SQL states for the SQL node are inherited from the nesting SQL node if any.

Connection String

<Option name="ConnectionString">connection-string</Option>

Specifies the connection string that is used to access the data source to which SQL commands are submitted for execution.

Command

<Option name="Command">SQL
                                    command</Option>

Specifies the SQL command to execute. The status and SQL state of the execution is processed according to the current error mitigation configured in the containing SQL node lineage.

Nested parameterized SQL commands can be specified where the inner command is executed once for each row materialized in the outer command's result set. Parameters are specified using @n syntax where n is a single parameter number corresponding to the n'th column of the outer command's result set. Note that commands can be nested only once. Here is an example of the nested command:

<OptionSet name="SQL">
...
<Option name="Command">outer query command</OptionSet>
<OptionSet name="SQL">
...
<Option name="Command">parameterized inner command 1</Option>
</OptionSet>
...
<OptionSet name="SQL">
...
<Option name="Command">parameterized inner command n</Option>
</OptionSet>
</OptionSet>

Condition

<Option name="Condition">SQL
                                    Boolean scalar result query</Option>

Specifies a query that resolves to a scalar Boolean result. Nonconforming commands will fail, causing script execution to end. If the command result is 1, all sibling SQL nodes are executed. Otherwise, they are skipped. Only the first condition is processed within an SQL node parent.

As a precursor to executing a set of SQL commands, a condition can be used to check for the existence of a table, a row within a table, or a value within a row. A condition on the outermost level SQL node will effectively make the entire script's execution dependent on the result of the specified query.

The following example condition returns 1 when table T has at least one row matching the WHERE clause, which is not shown. If the query returns 1, sibling SQL nodes contained in the parent SQL node are executed:

<Option name="Condition"> select cast(case when count(*) > 0 then 1
  else 0 end as integer) from T where ...</Option>

Auto Commit

<Option name="AutoCommit">value</Option>

Use autocommit to create a block of SQL that executes under a single transaction. The options are true (default) and false.

TRUE

When autocommit is set as true, each statement is executed as its own transaction and there is no rollback.

FALSE

When autocommit is set as false, the transaction is committed or rolled back depending on the SQL state at the end of the block of SQL statements. If no errors have occurred, or errors are permitted by the settings of the SQL script, the transaction is committed. Otherwise, it is rolled back.

Example SQL Script

This example script copies the content of in-memory MDS tables to a persistent data store when the server is stopped. You can use an inverse script to load the tables back into the MDS service in the Startup.Epilog phase. Error mediation prevents CREATE TABLE commands from stopping script execution when the table already exists in the data store. Errors with SQL states beginning with 42S are excluded from the stop action. Also, if the C_STORE_MDS catalog requires credentials to connect, you can supply those in the connection string.

<?xml version="1.0" encoding="utf-8" ?>
<OptionSet name="SQL">
   <!--
    Default phase, SQL error mediation control
   -->
    <Option name="name">MDS to STORE_MDS store</Option>
    <Option name="SQLErrorMediationAction">STOP</Option>
    <Option name="SQLErrorMediationSSMatchMode">EXCLUDE</Option>

   <!--
    MDS Store script
   -->
   <OptionSet name="SQL">
    <Option name="Phase">Stop.Prolog</Option>
    <Option name="SQLState">42S</Option>
    <Option name="ConnectionString">
    driver=FEDSQL;conopts=((security=NO;catalog=C_STORE_MDS);
                           (security=NO;catalog=C_MDS))
   </Option>
   <!--
   Result set generator command: Enumerate all MDS tables...
   -->
   <Option name="Command">
   select TABLE_SCHEM,
          TABLE_NAME
   from DICTIONARY.TABLES
      where TABLE_CAT='C_MDS' and
            TABLE_TYPE='TABLE'
   </Option>
   <!--
   Result set iterator command:
   Store MDS catalog tables in C_STORE_MDS "mirror" catalog
   -->
   <OptionSet name="SQL">
    <Option name="Command">
    create table C_STORE_MDS.S."@2" as
       select * from C_MDS."@1"."@2" where 1=0
    </Option>
   </OptionSet>
   <OptionSet name="SQL">
    <Option name="Command">
    delete from C_STORE_MDS.S."@2"
    </Option>
   </OptionSet>

   <OptionSet name="SQL">
    <Option name="Command">
    insert into C_STORE_MDS.S."@2"
       select * from C_MDS."@1"."@2"
    </Option>
  </OptionSet>
 </OptionSet>
</OptionSet>

This example script was saved as store_mds.xml. After saving the script, edit the dfs_serv.xml configuration file as highlighted in the following example:

<?xml version="1.0" encoding="UTF-8"?>

<DOCTYPE Config [
<ENTITY % entities SYSTEM "dfs_entities.dtd">
%entities;
<ENTITY MDS_SCRIPT SYSTEM "store_mds.xml">
]>

<Config name="TSConfig">

<!-- Common server options -->
&SERVER_COMMON;

<!-- Run the MDS script -->
&MDS_SCRIPT;


</Config>

For additional information about the dfs_serv.xml configuration file, see SAS Federation Server Configuration Reference.

Last updated: March 6, 2018