Cognos SQL, Native SQL and Pass-Through SQL

Cognos SQL 

Cognos SQL is generated by Framework Manager in more optimized way to preserve its functionality. Cognos SQL is a database agnostic language which is usually translated into the Native SQL and then sent to the database for processing. In some cases, database may not be supporting some of the operations or functions. In those cases, only part of the Cognos SQL is translated into the Native SQL and sent to the database. So, some part of the query is processed in the database and remaining portion is processed in the Cognos server.

Advantages:

1. Can contain metadata from multiple data sources.

2. Have fewer database restrictions

3. Interact more effectively with Cognos applications.

Disadvantages:

You cannot use nonstandard SQL.

Native SQL

The SQL that is supported by the data sources such as Oracle, SQL Server and DB2 etc. You cannot use Native SQL in a model query subject that references more than one data source in the project.

Advantages:

1. Performance is optimized across all related query subjects.

2. You can use SQL that is specific to your database.

Disadvantages:

1. You cannot use SQL that the data source does not support for sub queries.

2. The query subject may not work on different database types.

Pass-through SQL

Lets you use Native SQL without any restrictions the data source imposes on sub queries. Pass-Through SQL query subjects are not processed as sub queries. Instead, the SQL for each query subject is sent directly to the data source where the query results are generated. 

Note: since pass-through SQL is directly sent to the data source rather than being optimized by Framework Manager, performance will not be as good as the other two query types.

If you use curly braces { } around your SQL statement in a Tabular SQL object, that SQL statement is considered as Pass-Through and is sent directly to the database. Please note that this SQL is not validated by Cognos.

Step1: Create a Tabular SQL object. Write a statement surrounded with { }.

Actual:

SELECT * FROM ORDER_HEADER

Pass-Through: 

{SELECT * FROM ORDER_HEADER}

 
Advantages:

1. You can enter any SQL supported by the database.

Disadvantages:

1. There is no opportunity for Framework manager to automatically optimize performance. The SQL may not work on a different data source.