PIP Caching

Introduction

The PIP Service provides several capabilities for caching data including materialized views. This is used to significantly improve performance in many situations.

In-Memory Cache

To create an in-memory cache, simply add the relevant options as part of the view definition.

Example:

CREATE View v_Person (
    id varchar,
    name varchar,
    dob date,
    PRIMARY KEY (id)
) OPTIONS (
    UPDATABLE 'false', materialized true,  
    "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
    "teiid_rel:MATVIEW_POLLING_QUERY" '(SELECT NOW() from SYSADMIN.MatViews where Name = ''v_aad_users'' AND LoadState = ''FAILED_LOAD'')
        UNION
        (SELECT Updated FROM SYSADMIN.MatViews where Name = ''v_aad_users'' AND LoadState = ''LOADED'')',
    "teiid_rel:MATVIEW_POLLING_INTERVAL" 30000
) as select * from <USER>.Users;

*Note: See Polling Query and Interval Properties for more information

Polling Query and Interval Properties

The Polling Query and Interval properties provide a mechanism to continuously attempt to refresh the cache until successful, avoiding data availability issues. A cache refresh helps ensure that the Materialized Views reflect the most current and accurate data, which is essential for maintaining data consistency and availability for the Authorization process. These properties allow users to handle memory cache failure scenarios like connectivity issues with the underlying data source.
The PIP Service samples the materialized view status on a regular basis according to the predefined query and interval. The PIP then decides if triggering a refresh is required based on the timestamp returned from the polling query.

Persistent Cache

Persistent cache caches the view data to an external physical database system. When a new session issues a query against this view, the request will be redirected to the external physical database system and the cached results will be returned, rather than re-computing. This can prove timesaving and CPU-saving if your query sentences are complex and across multiple, heterogeneous data stores.

Creating a Persistent Cache:

1. Create a new Data Source for caching (RDBMS).
2. Enable Is Transactional.
3. Model Name: materialized.
4. Create Status Table DDL.

CREATE FOREIGN TABLE status
(
   VDBName varchar(50) not null,
   VDBVersion varchar(50) not null,
   SchemaName varchar(50) not null,
   Name varchar(256) not null,
   TargetSchemaName varchar(50),
   TargetName varchar(256) not null,
   Valid boolean not null,
   LoadState varchar(25) not null,
   Cardinality long,
   Updated timestamp not null,
   LoadNumber long not null,
   NodeName varchar(25) not null,
   StaleCount long,
   PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
);

5. Create a DDL for a Materialized Table

Example model DDL for External Materialized View.

CREATE FOREIGN TABLE PersonCached (
    id varchar,
    name varchar,
    dob date,
    LoadNumber long,
    PRIMARY KEY (id)
) OPTIONS (UPDATABLE true);

The table should include exactly the same columns and an additional column LoadNumber with the type long. This step is required.

6. Create a cached view. Go to the Views List and click Manage to add the following view definition.

Example:

CREATE View v_Person (
    id varchar,
    name varchar,
    dob date,
    PRIMARY KEY (id)
) OPTIONS (
    MATERIALIZED 'TRUE',
    UPDATABLE 'FALSE',
    MATERIALIZED_TABLE 'materialized.PersonCached',
    "teiid_rel:MATVIEW_TTL" 20000000,
    "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
    "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
    "teiid_rel:MATVIEW_STATUS_TABLE" 'materialized.STATUS'
    "teiid_rel:MATVIEW_POLLING_INTERVAL" 30000,
    "teiid_rel:MATVIEW_POLLING_QUERY" '(SELECT NOW() from SYSADMIN.MatViews where       Name = ''v_USER'' AND LoadState = ''FAILED_LOAD'')
    UNION
    (SELECT Updated FROM SYSADMIN.MatViews where Name = ''v_USER'' AND                 LoadState = ''LOADED'')'
     ) as select * from <USER>.Users;
    );

View Options

The following View properties are extension properties that are used in the management of the Materialized View.

Multivalue support

If multiple values are managed on the Asset or on the User, the required view structure should flatten the values into multi records as demonstrated in the example below:

Accounts can be associated with multiple countries. To support this type of use case, the required view structure should be according to the following example:

Notes: When creating an Asset Template, you need to define which columns have multiple values.

Multivalue attributes in Asset Templates are not supported in LDAP Data Sources.

Views List

A list of all available views is presented on the main page. Upon expanding, a list of all available columns within a view is shown.