SQL Pass-Through Facility Specifics for Aster nCluster

Key Information

For general information about this feature, see Overview of SQL Procedure Interactions with SAS/ACCESS. Aster nCluster examples are available.

Here are the SQL pass-through facility specifics for the Aster nCluster interface.

The dbms-name is ASTER.
The CONNECT statement is required.
PROC SQL supports multiple connections to Aster nCluster. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default ASTER alias is used.
The database-connection-arguments for the CONNECT statement are identical to its LIBNAME connection options.

CONNECT Statement Example

This example uses the DBCON alias to connect to mynpssrv the Aster nCluster database and execute a query. The connection alias is optional.

proc sql;
   connect to aster as dbcon
   (server=mynpssrv database=test user=myuser password=mypwd);
select * from connection to dbcon
   (select * from customers WHERE customer like '1%');
quit;

Special Catalog Queries

SAS/ACCESS Interface to Aster nCluster supports the following special queries. You can use the queries to call the ODBC-style catalog function application programming interfaces (APIs). Here is the general format of the special queries:

Aster::SQLAPI'parameter–1', 'parameter-n'

Aster::

is required to distinguish special queries from regular queries. Aster:: is not case sensitive.

SQLAPI

is the specific API that is being called. SQLAPI is not case sensitive.

'parameter n'

is a quoted string that is delimited by commas.

Within the quoted string, two characters are universally recognized: the percent sign (%) and the underscore (_). The percent sign matches any sequence of zero or more characters, and the underscore represents any single character. To use either character as a literal value, you can use the backslash character (\) to escape the match characters. For example, this call to SQL Tables usually matches table names such as myatest and my_test:

select * from connection to aster (ASTER::SQLTables
"test","","my_test");

Use the escape character to search only for the my_test table.

select * from connection to aster (ASTER::SQLTables "test","","my\_test");

SAS/ACCESS Interface to Aster nCluster supports these special queries.

ASTER::SQLTables <'Catalog', 'Schema', 'Table-name', 'Type'>

returns a list of all tables that match the specified arguments. If you do not specify any arguments, all accessible table names and information are returned.

ASTER::SQLColumns <'Catalog', 'Schema', 'Table-name', 'Column-name'>

returns a list of all tables that match the specified arguments. If you do not specify any arguments, all accessible table names and information are returned.

ASTER::SQLColumns <'Catalog', 'Schema', 'Table-name', 'Column-name'>

returns a list of all columns that match the specified arguments. If you do not specify any argument, all accessible column names and information are returned.

ASTER::SQLPrimaryKeys <'Catalog', 'Schema', 'Table-name''Type' >

returns a list of all columns that compose the primary key that matches the specified table. A primary key can be composed of one or more columns. If you do not specify any table name, this special query fails.

ASTER::SQLStatistics <'Catalog', 'Schema', 'Table-name'>

returns a list of the statistics for the specified table name, with options of SQL_INDEX_ALL and SQL_ENSURE set in the SQLStatistics API call. If you do not specify any table name argument, this special query fails.

ASTER::SQLGetTypeInfo

returns information about the data types that the Aster nCluster database supports.

ASTER::SQLTablePrivileges<'Catalog', 'Schema', 'Table-name'>

returns a list of all tables and associated privileges that match the specified arguments. If no arguments are specified, all accessible table names and associated privileges are returned.