Creating a Materialized View

Define a SQL query, using the Create Materialized View wizard, to create a new materialized view in the system store (IQ catalog store).

Prerequisites

Database Version	Materialized View Privileges
SAP Sybase IQ 15.3 and 15.4	Materialized view to be owned by self – you must have RESOURCE authority with SELECT permission on tables in the view definition. You must also have CREATE permission on the selected dbspace to create a view owned by you. Materialized view to be owned by any user – you must have DBA authority.
SAP Sybase IQ 16.0	Materialized view to be owned by self – you must have CREATE MATERIALIZED VIEW system privilege. You must also have one of: CREATE ANY OBJECT system privilege CREATE object permission on the dbspace where the materialized view is being created Finally, you must have one of: SELECT ANY TABLE system privilege SELECT object permission on the underlying tables of the materialized view Materialized view to be owned by any user – you must have CREATE object permission on the dbspace where the materialized view is being created. You must also have one of: CREATE ANY MATERIALIZED VIEW system privilege CREATE ANY OBJECT system privilege Finally, you must have one of: SELECT ANY TABLE system privilege SELECT object permission on the underlying tables of the materialized view

Database Version

Materialized View Privileges

SAP Sybase IQ 15.3 and 15.4

Materialized view to be owned by self – you must have RESOURCE authority with SELECT permission on tables in the view definition. You must also have CREATE permission on the selected dbspace to create a view owned by you.

Materialized view to be owned by any user – you must have DBA authority.

SAP Sybase IQ 16.0

Materialized view to be owned by self – you must have CREATE MATERIALIZED VIEW system privilege. You must also have one of:

CREATE ANY OBJECT system privilege
CREATE object permission on the dbspace where the materialized view is being created

Finally, you must have one of:

SELECT ANY TABLE system privilege
SELECT object permission on the underlying tables of the materialized view

Materialized view to be owned by any user – you must have CREATE object permission on the dbspace where the materialized view is being created. You must also have one of:

CREATE ANY MATERIALIZED VIEW system privilege
CREATE ANY OBJECT system privilege

Finally, you must have one of:

SELECT ANY TABLE system privilege
SELECT object permission on the underlying tables of the materialized view

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.

Task

Materialized views are only supported for system store (IQ catalog store) tables.

In the Perspective Resources view, select the resource, and select Resource > Administration Console.
In the left pane, expand IQ Servers > Schema Objects > Views, and then select Materialized Views.
Click the arrow next to Materialized Views and select New.
The Create Materialized View Wizard appears.

On the Welcome page, specify

Option	Description
Select a resource for which the materialized view will be created	From the list, select the resource for which the materialized view will be created.
Which user or group do you want to own the materialized view?	From the list, select the user or role/group to own the materialized view.
What do you want to name the materialized view?	Enter a unique name for the new materialized view; maximum of 128 characters.
Comment	Specify a comment for the materialized view.

Click Next.
On the SQL page, define a SELECT statement for the materialized view. Click Next.
On the Dbspace page, specify the dbspace that will be used to store the materialized view. Click Next.
On the Free Space page, specify the amount of free space you want to reserve for each materialized view page. Click Next.
On the Options page, specify:

Option Description

Enable this materialized view Select option to enable.

Enable use of this materialized view in optimization Select option to enable.
Click Finish.

Option	Description
Enable this materialized view	Select option to enable.
Enable use of this materialized view in optimization	Select option to enable.