SQL Scripting for SAS Federation Server Administration

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. To run a script, add the name of the script to the main configuration file, dfs_serv.xml using an external entity defined in the DTD section. Additional configuration information, including an example, appears at the end of this topic.

SQL Scripting Phases

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 cn connect to any configured data service and execute SQL this 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.

About the Configuration for SQL Scripting

XML Format

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

<OptionSet name="SQL">
...
</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.

Elements of SQL Scripting

The following Option and OptionSet elements are valid within an SQL node:

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

Specifies an SQL node containing a nested SQL script composed of other elements.

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

Specifies the name of the SQL node. This option is used for logging context.

<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.

<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.

<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.

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

Specifies the connection string that is used to access the data source for which SQL commands are submitted for execution. The connection string for the SQL node is DSN=ADMIN.

<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>

<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 or 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>

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.