SAS Micro Analytic Service
supports I/O through the DS2 SQLStmt package. Supported databases
include DB2, Greenplum, Netezza, Oracle, Postgres, SQL Server, and
Teradata.
Connection strings are
used to specify database connection information such as URL, credentials,
and options. Only one connection string can be specified per user
context. However, connection strings can be federated, allowing multiple
databases to be used concurrently.
The SQLStmt package
supports the FedSQL dialect. Therefore, the connection string should
begin with DRIVER=SQL;CONOPTS=(,
where sql specifies the FedSQL language driver
as the managing driver, and one or more target driver connection strings
are specified within the CONOPTS= option. The following example illustrates
a federated connection string that includes Oracle and PostgreSQL
data sources:
driver=sql;conopts=((driver=oracle;catalog=acat;uid=scott;
pwd=tiger;path=oraclev11.abc.123.com:1521/ORA11G);
(driver=postgres;catalog=bcat;uid=myid;pwd='mypass';
server=sv.abc.123.com;port=5432;DB=mydb;schema=public))
If you use the SAS Micro
Analytic Service REST interface, you can enter your connection string
in the Configuration Manager plug-in in SAS Management Console. If
you use the Java API, you can set your connection string by calling
setDS2Connection(). Connection string forms vary from database to
database. Most of the data source drivers require some client configuration,
such as modifications to the environment variables that enable the
driver software to be found and used correctly. You
must ensure that the environment has been set up appropriately for
the data source drivers that are being used. For more information,
see
Table Service Driver Reference.Package SQLStmt enables
you to specify a connection string in the DS2 code. However, this
technique is not recommended. If the connection string is set through
Configuration Manager or the Java API, SAS Micro Analytic Service
manages the database connection, detects whether the connection has
been lost, and tries to reconnect periodically. If the connection
string is set in the DS2 code, the connection is managed by the DS2
run time, which will not recover from lost connections. If connection
strings are specified both in the DS2 code and through Configuration
Manager or the Java API, SAS Micro Analytic Service overrides the
connection string that was set in DS2.
When you are calling
package SQLStmt to perform database I/O from a DS2 method, certain
types of severe errors can cause DS2 to render the SQLStmt instance,
and the DS2 package that called it, unusable. To maximize reliability,
SAS Micro Analytic Service will detect this condition and recompile
the offending package. This is useful if SQLStmt temporarily encounters
fatal errors while performing database I/O. If a recompilation is
successful, SAS Micro Analytic Service returns the error code MASDS2FatalRecompiled
to indicate that the method failed but the package was successfully
recompiled. If the recompilation fails, the error code MASDS2FatalRecompFailed
is returned. If a given DS2 package must be recompiled more than
1000 times, SAS Micro Analytic Service removes the module from the
system, and returns the error code MASDS2RevisionEjected.
Access to SAS data sets
is supported. However, since they use file-level locking, they are
not suitable for writing from multiple threads. Set appropriate connection
options carefully before reading SAS data sets from multiple threads.
Otherwise, a deadlock will occur. For these reasons, the use of
a third-party database management system is highly recommended.
Note: If SAS Micro Analytic Service
is installed with SAS Decision Manager, SAS Micro Analytic Service
must be installed on servers that have the same operating system family
as the SAS Decision Manager server tier. For more information, see SAS
Decision Manager Administrator’s Guide. This requirement
ensures that appropriate data access components are licensed for use
by both SAS Micro Analytic Service and SAS Decision Manager.
SAS Micro Analytic
Service enables access to HTTP and HTTPS web services through the
DS2 HTTP package, which can execute HTTP requests to, and receive
responses from, HTTP and HTTPS web services. Direct file I/O is not
supported. As a result, DS2 hash packages cannot be populated from
the contents of a file.
For more information
about DS2 and FedSQL, see SAS 9.4 DS2 Language Reference. and SAS
9.4 FedSQL Language Reference.