*** This package is subject to change. ***

Note:

Extension of the classes in this package is prohibited unless otherwise documented. Similarly, extension or implementation of the interfaces in this package is prohibited except as documented.

Package com.sas.iquery.dataretrieval

Provides entry points that are used to retrieve result data by executing queries (data selections).

See:
Description

Interface Summary

QueryConnectorInterface

Specifies methods that are used to execute queries.

Class Summary

ConnectionConfiguration	Contains settings that are used to configure connection behaviour.
OfflineResultSetConfiguration	Contains settings that are used to configure offline result sets.
QueryConnector	Retrieves result sets by executing business queries.
QueryConnector.CloseOptions	Contains bit values that are used to close specific resources that are managed by the QueryConnector.
RetrievalPolicy	Contains query policy settings.
SimpleQueryConnector	A basic query connector without result or connection management.

Exception Summary

DataRetrievalException

Specifies an exception that is thrown on errors in SAS Query Services data retrieval.

Package com.sas.iquery.dataretrieval Description

Provides entry points that are used to retrieve result data by executing queries (data selections).

This page describes the use of queries based on information maps. See com.sas.iquery.metadata.business for a description of the underlying models such as business models, data selections and how to build them both.

Executing a query

The QueryConnector object serves as a facade for executing queries and managing the resources that are obtained and the results that are returned.

Queries are represented by DataSelection objects and cab be executed in order to retrieve result sets, which enable applications to pull data from the server.

Based on the abstract definitions of data sources, sorting, formatting and other properties of the result data items of a data selection, the query connector will build a query in the server?s native language, for example using SAS, SQL or MDX.

The result of executing this native query will be a result data set object specific to the type of server, such as a tabular result set or a multi-dimensional OLAP data set. These result sets are interactive objects, which enable applications to control how they work through the returned data.

To accommodate abstract applications, which do not need to know about the specific types of servers or query results, the server-type-specific result data sets are always wrapped in an abstract ResultSetInterface, which can be manipulated independently of the underlying type of result data.

The general sequence of interactions that are used to execute a query is outlined in the diagram below:

See executing a relational query for an example of query execution.

Query types

Currently, the query connector supports three types of queries:

• Tabular queries that return tabular result sets (also known as relational queries)
  Tabular result sets are usually displayed as tables in applications:
  
  A business query on a relational table InformationMap (a relational query) returns a tabular result set if it contains only data items whose roles are neither Role.ROW nor Role.BACKGROUND.
  
  
• Tabular queries that return multi-dimensional cross tabular data sets (also known as relational cross tabular queries or ROLAP queries)
  Relational cross tabular queries produce cross tabulations of aggregated tabular data.
  
  If a relational query contains data items with roles other than those described in the previous example, then a multi-dimensional cross tabular data set is returned. Relational cross tabular data sets are similar to OLAP data sets.
  
  
• OLAP queries that return multi-dimensional OLAP data sets
  If a query's information map references an OLAP cube rather than a relational table, then the business query is called an OLAP query.
  OLAP queries always produce OLAP data sets when they are executed, regardless of the roles of their data items.
  OLAP data sets are usually displayed as cross tabulations in applications and may enable users to drill into or expand data items.
  
  

Both plain relational and relational cross tabular queries are performed against tabular data (information maps referencing tables). They are only distinguished from one another by the roles of the data items that they contain.

See com.sas.iquery.metadata.business for examples of how to build different types of queries.

Querying options

The query connector?s mode of operation depends on a collection of settings that are passed to it queries are executed. These settings are collected into an object known as a RetrievalPolicy.

See an example of how to use the retrieval policy.

The retrieval policy contains settings for three areas:

• A behavior policy
• Connection configuration
• Offline result set configuration

Behavior policy

The behavior policy uses the following settings to determine the general strategy to use for retrieving data:

• FRESHEST will attempt to fetch the most recent data from the server, even if cached, local data is available. This is the default behavior.
• FASTEST will attempt to use the fastest source of the data requested, possibly retrieved by an earlier query.

Currently, the only difference between these two settings is in relation to embedded data, support for which might be removed or changed in future releases of SAS Data Services.

See RetrievalPolicy for more information about behavior policies.

Connection configuration

The connection configuration contains two settings:

• Connection lifetime
• Connection wait time

Connection lifetime

The connection lifetime determines how connections are managed for subsequent queries that are made by the same user.

ConnectionConfiguration.USER_SESSION re-uses a single connection per user for all queries against a server, until the connection is explicitly released.

ConnectionConfiguration.ACQUIRE_RELEASE creates a new connection to a server each time a query is executed. This is usually slower than re-using a single connection.

See also the section below on managing resources.

Connection wait time

The connection wait time determines how long to wait for an available connection to the server if no connections are immediately available.

See ConnectionConfiguration for more information about connection configurations.

Offline result set configuration

For the system to scale to larger numbers of users, currently these users must be able to share a limited number of server connections. In order to release server connections early, it is possible to make tabular query result sets pre-load some of their data and then immediately return the connection for use by others. A tabular result set that contains such pre-loaded data and that is disconnected from the server, is called an offline result set.

Offline result sets make it possible for the user to interact (scroll) within the pre-loaded data without needing to make a new connection to the server. If the user scrolls beyond the pre-loaded data, then the same query is transparently executed against the server again in order to load more data.

OLAP result sets cannot currently be made off-line and therefore won't work after their connection has been closed.

For offline result sets, it is possible to configure the number of rows to pre-load and how many of these to keep in memory and on disk. Adjusting these values to match real-world usage is critical to good and scalable performance.

Note that enabling offline result sets might incur significant overhead because of disk writes and re-queries, so only enable them if you need to release connections immediately and still need to interact with tabular result sets after this point.

See OfflineResultSetConfiguration for information about offline result set configuration.

Managing resources

The management of resources that are obtained and returned by the query connector depends on the connection lifetime settings.

When you exclusively use the ACQUIRE_RELEASE connection lifetime, the query connector does not manage any resources. Connections are acquired for each query, and are attached to the particular result set (ResultSetInterface) that is returned. When the result set is closed, so is its associated connection.
A useful pattern for ensuring clean-up of connections and result sets is to wrap the retrieval and use of result sets in a try...finally block which ensures that result sets are closed. If a result set is not closed explicitly, then its associated connection will remain until the result object is garbage collected.

When you use USER_SESSION connection lifetime, the query connector will manage both server connections and the result sets that are returned. Because multiple result sets can depend on a server connection, these should be disposed of in conjunction with the connection. (Offline or relational cross tabular result sets do not depend on retaining their original connection. They will re-use a user's connection if one is present or acquire a new one as necessary.)
See QueryConnector.closeResources(SessionContextInterface, int) for more information about managing resources.

Information map stored processes

Some relational information maps might have associated stored processes. The first time a query based on such a map is executed, the stored process is run, and the query is performed against the output of the stored process. Subsequent queries against the map using the same connection will not re-execute the stored process.

• When using ConnectionConfiguration.ACQUIRE_RELEASE, each query creates a new connection, and so the stored process is re-executed every time a query is run.
• For ConnectionConfiguration.USER_SESSION connection lifetime, the connection is re-used and so the stored process is not re-executed except after an explicit closing of the tabular workspace server connection using QueryConnector.closeResources(SessionContextInterface, int).
  That is, the only way to re-execute a stored process associated with an information map when using ConnectionConfiguration.USER_SESSION is to close the current connection.

*** This package is subject to change. ***

Note:

Extension of the classes in this package is prohibited unless otherwise documented. Similarly, extension or implementation of the interfaces in this package is prohibited except as documented.