I/O

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. 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, 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 detects this condition and recompiles 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 occurs. For these reasons, the use of a third-party database management system is highly recommended.

For detailed driver reference information, see Table Service Driver Reference.

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.