## Purpose Besides an in-memory cache valid during the duration of a session, Invantive UniversalSQL offers an integrated cache storing data persistently using an on-premise or cloud relation database such as SQL Server or PostgreSQL. When configured, Invantive UniversalSQL first tries to find sufficiently fresh data in the cache. This reduces the number of data loads from slow data containers such as some cloud platforms. In general, the performance increase when the rows can be fully retrieved from a cache is between a factor 25 and 2.500. Invantive UniversalSQL itself manages the table structure and table contents in the relation database used as a data cache. On initial use just provide an empty database. Platforms supported include SQL Server, Oracle, PostgreSQL and MySQL. Invantive UniversalSQL installs a repository consisting of a dozen tables. The repository tables have names starting with 'dc_'. Most repository tables are accompanied by a view named identically except for a trailing 'r'. The repository views join in all master tables for easier analysis from within the database storing the facts For each table partition version, a so-called 'facts table' is created. A facts table contains a full copy of the rows retrieved from the data container. Facts tables have names starting with 'dcd_', followed by a unique hash signaling the table partition version. When necessary, additional database objects are maintained such as indexes to improve performance. As with facts table names, all column names are also hashed based upon an algorithm including the original column name. These facts tables are not intended for direct use using native SQL. Each facts table has a unique state from the following state, with 'Ready' state signaling the now current version: - Initializing ('I'): the facts table will be created. - View creation ('V'): logical views will be created. - Prepared ('P'): the facts table has been created, but contains yet no rows. - Seeding ('S'): the facts table is being seeded with the contents of the previously current version. - Loading ('L'): loading new facts from data container using water shed or another algorithm. - Ready ('R'): the facts table is available and the current one to be used. - Obsoleted ('O'): the facts table still exists, but the data has passed it's conservation period. Often a newer version is now current. - Dropped ('D'): the facts table now longer exist, but the metadata is still present in the repository tables. The persistent cache in the database can be used with native SQL when extended by Invantive Data Replicator. Invantive Data Replicator creates and maintains database view (a so-called 'partition view') for the now current version of table partition. Similarly, it can create an 'overall view', showing the rows across all partitions of the now current versions per partition. Invantive Data Replicator provides a high-performance high-volume operational data store with little effort. The overall views are typically used for consolidation purposes, bringing together data across multiple companies or persons. ## Syntax ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR Start((START)) --> | | alterPersistentCacheSetStatement alterPersistentCacheSetStatement --> End((END)) Start((START)) -->| | alterPersistentCacheDownloadStatement alterPersistentCacheDownloadStatement --> End((END)) Start((START)) -->| | alterPersistentCachePurgeStatement alterPersistentCachePurgeStatement --> End((END)) Start((START)) -->| | alterPersistentCacheRefreshStatement alterPersistentCacheRefreshStatement --> End((END)) Start((START)) -->| | alterPersistentCacheLoadStatement alterPersistentCacheLoadStatement --> End((END)) Start((START)) -->| | alterPersistentCacheTableRefreshStatement alterPersistentCacheTableRefreshStatement --> End((END)) Start((START)) -->| | alterPersistentCachePartitionRefreshStatement alterPersistentCachePartitionRefreshStatement --> End((END)) Start((START)) -->| | alterPersistentCacheDropStatement alterPersistentCacheDropStatement --> End((END)) Start((START)) -->| | alterPersistentCacheConfigureWebhooksStatement alterPersistentCacheConfigureWebhooksStatement --> End((END)) ``` ## Purpose The purge statement maintains existing table partition versions. It can purge table partition versions in several ways: - ready: selects table partition versions in state 'Ready'. Table partition versions go to the state 'Obsolete'. Facts are not deleted. - obsolete: selects table partition versions in state 'Obsolete'. Facts of these are deleted. Table partition versions go to the state 'Dropped'. The facts deleted might be used in queries that started running when they were in state 'Ready'. The facts can be deleted safely when the database platform supports reading data from rows already deleted, such as Oracle. Purging obsolete facts is often used as a background task separate from refresh to reduce refresh runtime, since the deletion of data has already been executed before refresh. - droppable: selects table partition versions in state 'Dropped'. Facts are deleted when any facts are still present despite the dropped state. This will be useful only under exceptional conditions, since the dropped status indicates that the facts have already been deleted. Table partition versions remain in the state 'Dropped'. - unknown: queries the database for tables that match the naming convention 'dcd...' or 'dcs...'. Any tables that is not registered in the repository is dropped from the database. This will be useful only under exceptional conditions, such as when the repository was unintentionally dropped or incorrectly changed. - all: first performs the "unknown" variant and then the "droppable" variant. This will be useful only under exceptional conditions. Specification of a data container limits the scope of table partition versions to the listed data container. ### PSQL Packages Invantive PSQL comes with a number of pre-defined packages. These can be queried using SystemPackages@DataDictionary and SystemPackageFunctions@DataDictionary. ## alterPersistentCachePurgeStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCachePurgeStatement_start((START)) alterPersistentCachePurgeStatement_start --> alterPersistentCachePurgeStatement_0_0[ALTER]:::quoted alterPersistentCachePurgeStatement_0_0 --> alterPersistentCachePurgeStatement_0_1[PERSISTENT]:::quoted alterPersistentCachePurgeStatement_0_1 --> alterPersistentCachePurgeStatement_0_2[CACHE]:::quoted alterPersistentCachePurgeStatement_0_2 --> alterPersistentCachePurgeStatement_0_3[PURGE]:::quoted alterPersistentCachePurgeStatement_0_3 --> alterPersistentCachePurgeStatement_0_4[UNKNOWN]:::quoted alterPersistentCachePurgeStatement_0_4 --> alterPersistentCachePurgeStatement_0_5[DROPPABLE]:::quoted alterPersistentCachePurgeStatement_0_5 --> alterPersistentCachePurgeStatement_0_6[OBSOLETE]:::quoted alterPersistentCachePurgeStatement_0_6 --> alterPersistentCachePurgeStatement_0_7[READY]:::quoted alterPersistentCachePurgeStatement_0_7 --> alterPersistentCachePurgeStatement_0_8[ALL]:::quoted alterPersistentCachePurgeStatement_0_8 --> alterPersistentCachePurgeStatement_0_9[TABLE]:::quoted alterPersistentCachePurgeStatement_0_9 --> alterPersistentCachePurgeStatement_0_10[PARTITION]:::quoted alterPersistentCachePurgeStatement_0_10 --> alterPersistentCachePurgeStatement_0_11[VERSIONS]:::quoted alterPersistentCachePurgeStatement_0_11 --> alterPersistentCachePurgeStatement_0_12[DATACONTAINER]:::quoted alterPersistentCachePurgeStatement_0_12 --> alterPersistentCachePurgeStatement_0_13[stringConstant] alterPersistentCachePurgeStatement_0_13 --> alterPersistentCachePurgeStatement_end((END)) ``` ## alterPersistentCacheDownloadStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheDownloadStatement_start((START)) alterPersistentCacheDownloadStatement_start --> alterPersistentCacheDownloadStatement_0_0[ALTER]:::quoted alterPersistentCacheDownloadStatement_0_0 --> alterPersistentCacheDownloadStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheDownloadStatement_0_1 --> alterPersistentCacheDownloadStatement_0_2[CACHE]:::quoted alterPersistentCacheDownloadStatement_0_2 --> alterPersistentCacheDownloadStatement_0_3[DOWNLOAD]:::quoted alterPersistentCacheDownloadStatement_0_3 --> alterPersistentCacheDownloadStatement_0_4[FEED]:::quoted alterPersistentCacheDownloadStatement_0_4 --> alterPersistentCacheDownloadStatement_0_5[LICENSE]:::quoted alterPersistentCacheDownloadStatement_0_5 --> alterPersistentCacheDownloadStatement_0_6[CONTRACT]:::quoted alterPersistentCacheDownloadStatement_0_6 --> alterPersistentCacheDownloadStatement_0_7[CODE]:::quoted alterPersistentCacheDownloadStatement_0_7 --> alterPersistentCacheDownloadStatement_0_8[stringConstant] alterPersistentCacheDownloadStatement_0_8 --> alterPersistentCacheDownloadStatement_0_9[TOKEN]:::quoted alterPersistentCacheDownloadStatement_0_9 --> alterPersistentCacheDownloadStatement_0_10[stringConstant] alterPersistentCacheDownloadStatement_0_10 --> alterPersistentCacheDownloadStatement_0_11[DATACONTAINER]:::quoted alterPersistentCacheDownloadStatement_0_11 --> alterPersistentCacheDownloadStatement_0_12[stringConstant] alterPersistentCacheDownloadStatement_0_12 --> alterPersistentCacheDownloadStatement_0_13[PARTITION]:::quoted alterPersistentCacheDownloadStatement_0_13 --> alterPersistentCacheDownloadStatement_0_14[partitionSimpleIdentifier] alterPersistentCacheDownloadStatement_0_14 --> alterPersistentCacheDownloadStatement_0_15[LIMIT]:::quoted alterPersistentCacheDownloadStatement_0_15 --> alterPersistentCacheDownloadStatement_0_16[numericConstant] alterPersistentCacheDownloadStatement_0_16 --> alterPersistentCacheDownloadStatement_0_17[NO]:::quoted alterPersistentCacheDownloadStatement_0_17 --> alterPersistentCacheDownloadStatement_0_18[DELETE]:::quoted alterPersistentCacheDownloadStatement_0_18 --> alterPersistentCacheDownloadStatement_end((END)) ``` ## alterPersistentCacheConfigureWebhooksStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheConfigureWebhooksStatement_start((START)) alterPersistentCacheConfigureWebhooksStatement_start --> alterPersistentCacheConfigureWebhooksStatement_0_0[ALTER]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_0 --> alterPersistentCacheConfigureWebhooksStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_1 --> alterPersistentCacheConfigureWebhooksStatement_0_2[CACHE]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_2 --> alterPersistentCacheConfigureWebhooksStatement_0_3[ENABLE]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_3 --> alterPersistentCacheConfigureWebhooksStatement_0_4[DISABLE]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_4 --> alterPersistentCacheConfigureWebhooksStatement_0_5[WEBHOOKS]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_5 --> alterPersistentCacheConfigureWebhooksStatement_0_6[LICENSE]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_6 --> alterPersistentCacheConfigureWebhooksStatement_0_7[CONTRACT]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_7 --> alterPersistentCacheConfigureWebhooksStatement_0_8[CODE]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_8 --> alterPersistentCacheConfigureWebhooksStatement_0_9[stringConstant] alterPersistentCacheConfigureWebhooksStatement_0_9 --> alterPersistentCacheConfigureWebhooksStatement_0_10[TABLE]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_10 --> alterPersistentCacheConfigureWebhooksStatement_0_11[tableSpec] alterPersistentCacheConfigureWebhooksStatement_0_11 --> alterPersistentCacheConfigureWebhooksStatement_0_12[PARTITION]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_12 --> alterPersistentCacheConfigureWebhooksStatement_0_13[partitionSimpleIdentifier] alterPersistentCacheConfigureWebhooksStatement_0_13 --> alterPersistentCacheConfigureWebhooksStatement_0_14[DATACONTAINER]:::quoted alterPersistentCacheConfigureWebhooksStatement_0_14 --> alterPersistentCacheConfigureWebhooksStatement_0_15[stringConstant] alterPersistentCacheConfigureWebhooksStatement_0_15 --> alterPersistentCacheConfigureWebhooksStatement_end((END)) ``` ## Purpose Trickle loading is an advanced strategy to efficiently and fast update the replicate data. It requires all changes (insert, update and delete) on the replicated data to be registered as event messages, possibly with a delay. The registration of changes can be activated on selected platforms using webhooks. Configuration of the webhooks can be done using this statement. When not specified, the license contract code of the current subscription will be used. ## alterPersistentCacheRefreshStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheRefreshStatement_start((START)) alterPersistentCacheRefreshStatement_start --> alterPersistentCacheRefreshStatement_0_0[ALTER]:::quoted alterPersistentCacheRefreshStatement_0_0 --> alterPersistentCacheRefreshStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheRefreshStatement_0_1 --> alterPersistentCacheRefreshStatement_0_2[CACHE]:::quoted alterPersistentCacheRefreshStatement_0_2 --> alterPersistentCacheRefreshStatement_0_3[FORCE]:::quoted alterPersistentCacheRefreshStatement_0_3 --> alterPersistentCacheRefreshStatement_0_4[REFRESH]:::quoted alterPersistentCacheRefreshStatement_0_4 --> alterPersistentCacheRefreshStatement_0_5[DATACONTAINER]:::quoted alterPersistentCacheRefreshStatement_0_5 --> alterPersistentCacheRefreshStatement_0_6[dataContainerAlias] alterPersistentCacheRefreshStatement_0_6 --> alterPersistentCacheRefreshStatement_0_7[PARALLEL]:::quoted alterPersistentCacheRefreshStatement_0_7 --> alterPersistentCacheRefreshStatement_0_8[numericConstant] alterPersistentCacheRefreshStatement_0_8 --> alterPersistentCacheRefreshStatement_0_9[APPROACH]:::quoted alterPersistentCacheRefreshStatement_0_9 --> alterPersistentCacheRefreshStatement_0_10[COPY]:::quoted alterPersistentCacheRefreshStatement_0_10 --> alterPersistentCacheRefreshStatement_0_11[TRICKLE]:::quoted alterPersistentCacheRefreshStatement_0_11 --> alterPersistentCacheRefreshStatement_0_12[SAMPLE]:::quoted alterPersistentCacheRefreshStatement_0_12 --> alterPersistentCacheRefreshStatement_0_13[DEFAULT]:::quoted alterPersistentCacheRefreshStatement_0_13 --> alterPersistentCacheRefreshStatement_0_14[WHEN]:::quoted alterPersistentCacheRefreshStatement_0_14 --> alterPersistentCacheRefreshStatement_0_15[OBSOLETE]:::quoted alterPersistentCacheRefreshStatement_0_15 --> alterPersistentCacheRefreshStatement_0_16[WITHIN]:::quoted alterPersistentCacheRefreshStatement_0_16 --> alterPersistentCacheRefreshStatement_0_17[intervalConstant] alterPersistentCacheRefreshStatement_0_17 --> alterPersistentCacheRefreshStatement_end((END)) ``` ## Purpose This statement triggers the refresh of replicated data of all connected or one specific data container alias. A refresh of the replicated data can also be triggered by executing a SQL statement specifying the use of replicated data of a specific age. In default mode (without 'force'), a refresh is only executed on the specified data when the age of the replicated data exceeds the configured maximum age. An offset to the configured maximum age can be specified using the 'when obsolete within' clause. With a forced refresh, the replicated data is always replaced by a recent version of the source data. The maximum number of parallel processes to replicate the data can be configured using the 'parallel' clause to increase throughput and decrease runtime of the replication process. By default the approach as configured on each table partition is used to update the replica. However, a deviating approach can be specified. This is typically done after switching a table partition to trickle loading to run a one-time replication process with the copy approach, effectively ensuring that no changes have gone unnoticed. ## alterPersistentCacheLoadStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR alterPersistentCacheLoadStatement_start((START)) alterPersistentCacheLoadStatement_start --> alterPersistentCacheLoadStatement_0_0[ALTER]:::quoted alterPersistentCacheLoadStatement_0_0 --> alterPersistentCacheLoadStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheLoadStatement_0_1 --> alterPersistentCacheLoadStatement_0_2[CACHE]:::quoted alterPersistentCacheLoadStatement_0_2 --> alterPersistentCacheLoadStatement_0_3[LOAD]:::quoted alterPersistentCacheLoadStatement_0_3 --> alterPersistentCacheLoadStatement_end((END)) ``` ## Purpose Loads all available tables across all connected data containers in the cache. Typically used for demonstrations. ## alterPersistentCacheTableRefreshStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheTableRefreshStatement_start((START)) alterPersistentCacheTableRefreshStatement_start --> alterPersistentCacheTableRefreshStatement_0_0[ALTER]:::quoted alterPersistentCacheTableRefreshStatement_0_0 --> alterPersistentCacheTableRefreshStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheTableRefreshStatement_0_1 --> alterPersistentCacheTableRefreshStatement_0_2[CACHE]:::quoted alterPersistentCacheTableRefreshStatement_0_2 --> alterPersistentCacheTableRefreshStatement_0_3[TABLE]:::quoted alterPersistentCacheTableRefreshStatement_0_3 --> alterPersistentCacheTableRefreshStatement_0_4[tableSpec] alterPersistentCacheTableRefreshStatement_0_4 --> alterPersistentCacheTableRefreshStatement_0_5[FORCE]:::quoted alterPersistentCacheTableRefreshStatement_0_5 --> alterPersistentCacheTableRefreshStatement_0_6[REFRESH]:::quoted alterPersistentCacheTableRefreshStatement_0_6 --> alterPersistentCacheTableRefreshStatement_0_7[PARTITION]:::quoted alterPersistentCacheTableRefreshStatement_0_7 --> alterPersistentCacheTableRefreshStatement_0_8[partitionIdentifierWithAlias] alterPersistentCacheTableRefreshStatement_0_8 --> alterPersistentCacheTableRefreshStatement_0_9[PARALLEL]:::quoted alterPersistentCacheTableRefreshStatement_0_9 --> alterPersistentCacheTableRefreshStatement_0_10[numericConstant] alterPersistentCacheTableRefreshStatement_0_10 --> alterPersistentCacheTableRefreshStatement_0_11[APPROACH]:::quoted alterPersistentCacheTableRefreshStatement_0_11 --> alterPersistentCacheTableRefreshStatement_0_12[COPY]:::quoted alterPersistentCacheTableRefreshStatement_0_12 --> alterPersistentCacheTableRefreshStatement_0_13[TRICKLE]:::quoted alterPersistentCacheTableRefreshStatement_0_13 --> alterPersistentCacheTableRefreshStatement_0_14[SAMPLE]:::quoted alterPersistentCacheTableRefreshStatement_0_14 --> alterPersistentCacheTableRefreshStatement_0_15[DEFAULT]:::quoted alterPersistentCacheTableRefreshStatement_0_15 --> alterPersistentCacheTableRefreshStatement_0_16[WHEN]:::quoted alterPersistentCacheTableRefreshStatement_0_16 --> alterPersistentCacheTableRefreshStatement_0_17[OBSOLETE]:::quoted alterPersistentCacheTableRefreshStatement_0_17 --> alterPersistentCacheTableRefreshStatement_0_18[WITHIN]:::quoted alterPersistentCacheTableRefreshStatement_0_18 --> alterPersistentCacheTableRefreshStatement_0_19[intervalConstant] alterPersistentCacheTableRefreshStatement_0_19 --> alterPersistentCacheTableRefreshStatement_end((END)) ``` ## Purpose Refresh all data of a specificied table. The options are explained at alterPersistentCacheRefreshStatement. ## alterPersistentCachePartitionRefreshStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCachePartitionRefreshStatement_start((START)) alterPersistentCachePartitionRefreshStatement_start --> alterPersistentCachePartitionRefreshStatement_0_0[ALTER]:::quoted alterPersistentCachePartitionRefreshStatement_0_0 --> alterPersistentCachePartitionRefreshStatement_0_1[PERSISTENT]:::quoted alterPersistentCachePartitionRefreshStatement_0_1 --> alterPersistentCachePartitionRefreshStatement_0_2[CACHE]:::quoted alterPersistentCachePartitionRefreshStatement_0_2 --> alterPersistentCachePartitionRefreshStatement_0_3[PARTITION]:::quoted alterPersistentCachePartitionRefreshStatement_0_3 --> alterPersistentCachePartitionRefreshStatement_0_4[partitionIdentifierWithAlias] alterPersistentCachePartitionRefreshStatement_0_4 --> alterPersistentCachePartitionRefreshStatement_0_5[FORCE]:::quoted alterPersistentCachePartitionRefreshStatement_0_5 --> alterPersistentCachePartitionRefreshStatement_0_6[REFRESH]:::quoted alterPersistentCachePartitionRefreshStatement_0_6 --> alterPersistentCachePartitionRefreshStatement_0_7[PARALLEL]:::quoted alterPersistentCachePartitionRefreshStatement_0_7 --> alterPersistentCachePartitionRefreshStatement_0_8[numericConstant] alterPersistentCachePartitionRefreshStatement_0_8 --> alterPersistentCachePartitionRefreshStatement_0_9[APPROACH]:::quoted alterPersistentCachePartitionRefreshStatement_0_9 --> alterPersistentCachePartitionRefreshStatement_0_10[COPY]:::quoted alterPersistentCachePartitionRefreshStatement_0_10 --> alterPersistentCachePartitionRefreshStatement_0_11[TRICKLE]:::quoted alterPersistentCachePartitionRefreshStatement_0_11 --> alterPersistentCachePartitionRefreshStatement_0_12[SAMPLE]:::quoted alterPersistentCachePartitionRefreshStatement_0_12 --> alterPersistentCachePartitionRefreshStatement_0_13[DEFAULT]:::quoted alterPersistentCachePartitionRefreshStatement_0_13 --> alterPersistentCachePartitionRefreshStatement_0_14[WHEN]:::quoted alterPersistentCachePartitionRefreshStatement_0_14 --> alterPersistentCachePartitionRefreshStatement_0_15[OBSOLETE]:::quoted alterPersistentCachePartitionRefreshStatement_0_15 --> alterPersistentCachePartitionRefreshStatement_0_16[WITHIN]:::quoted alterPersistentCachePartitionRefreshStatement_0_16 --> alterPersistentCachePartitionRefreshStatement_0_17[intervalConstant] alterPersistentCachePartitionRefreshStatement_0_17 --> alterPersistentCachePartitionRefreshStatement_end((END)) ``` ## Purpose Refresh all data of a specificied partition. The options are explained at alterPersistentCacheRefreshStatement. ## alterPersistentCacheDropStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheDropStatement_start((START)) alterPersistentCacheDropStatement_start --> alterPersistentCacheDropStatement_0_0[ALTER]:::quoted alterPersistentCacheDropStatement_0_0 --> alterPersistentCacheDropStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheDropStatement_0_1 --> alterPersistentCacheDropStatement_0_2[CACHE]:::quoted alterPersistentCacheDropStatement_0_2 --> alterPersistentCacheDropStatement_0_3[DROP]:::quoted alterPersistentCacheDropStatement_0_3 --> alterPersistentCacheDropStatement_0_4[TABLE]:::quoted alterPersistentCacheDropStatement_0_4 --> alterPersistentCacheDropStatement_0_5[tableSpec] alterPersistentCacheDropStatement_0_5 --> alterPersistentCacheDropStatement_0_6[PARTITION]:::quoted alterPersistentCacheDropStatement_0_6 --> alterPersistentCacheDropStatement_0_7[partitionIdentifier] alterPersistentCacheDropStatement_0_7 --> alterPersistentCacheDropStatement_0_8[PARTITION]:::quoted alterPersistentCacheDropStatement_0_8 --> alterPersistentCacheDropStatement_0_9[partitionIdentifier] alterPersistentCacheDropStatement_0_9 --> alterPersistentCacheDropStatement_0_10[DATACONTAINER]:::quoted alterPersistentCacheDropStatement_0_10 --> alterPersistentCacheDropStatement_0_11[stringConstant] alterPersistentCacheDropStatement_0_11 --> alterPersistentCacheDropStatement_end((END)) ``` ## Purpose Drops all facts stored in table partition versions from the database and the associated metadata from the cache repository for a range of table partitions. The tables and partitions can be specified using the table, partition and/or data container clause. ## alterPersistentCacheSetStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheSetStatement_start((START)) alterPersistentCacheSetStatement_start --> alterPersistentCacheSetStatement_0_0[ALTER]:::quoted alterPersistentCacheSetStatement_0_0 --> alterPersistentCacheSetStatement_0_1[PERSISTENT]:::quoted alterPersistentCacheSetStatement_0_1 --> alterPersistentCacheSetStatement_0_2[CACHE]:::quoted alterPersistentCacheSetStatement_0_2 --> alterPersistentCacheSetStatement_0_3[SET]:::quoted alterPersistentCacheSetStatement_0_3 --> alterPersistentCacheSetStatement_0_4[FRESH]:::quoted alterPersistentCacheSetStatement_0_4 --> alterPersistentCacheSetStatement_0_5[RETENTION]:::quoted alterPersistentCacheSetStatement_0_5 --> alterPersistentCacheSetStatement_0_6[FORWARDED]:::quoted alterPersistentCacheSetStatement_0_6 --> alterPersistentCacheSetStatement_0_7[INCOMING]:::quoted alterPersistentCacheSetStatement_0_7 --> alterPersistentCacheSetStatement_0_8[MESSAGES]:::quoted alterPersistentCacheSetStatement_0_8 --> alterPersistentCacheSetStatement_0_9[METADATA]:::quoted alterPersistentCacheSetStatement_0_9 --> alterPersistentCacheSetStatement_0_10[RECYCLEBIN]:::quoted alterPersistentCacheSetStatement_0_10 --> alterPersistentCacheSetStatement_0_11[DATA]:::quoted alterPersistentCacheSetStatement_0_11 --> alterPersistentCacheSetStatement_0_12[MODEL]:::quoted alterPersistentCacheSetStatement_0_12 --> alterPersistentCacheSetStatement_0_13[VERSION]:::quoted alterPersistentCacheSetStatement_0_13 --> alterPersistentCacheSetStatement_0_14[numericConstant] alterPersistentCacheSetStatement_0_14 --> alterPersistentCacheSetStatement_0_15[TOKEN]:::quoted alterPersistentCacheSetStatement_0_15 --> alterPersistentCacheSetStatement_0_16[stringConstant] alterPersistentCacheSetStatement_0_16 --> alterPersistentCacheSetStatement_0_17[LOGICAL]:::quoted alterPersistentCacheSetStatement_0_17 --> alterPersistentCacheSetStatement_0_18[OVERALL]:::quoted alterPersistentCacheSetStatement_0_18 --> alterPersistentCacheSetStatement_0_19[PARTITION]:::quoted alterPersistentCacheSetStatement_0_19 --> alterPersistentCacheSetStatement_0_20[VIEW]:::quoted alterPersistentCacheSetStatement_0_20 --> alterPersistentCacheSetStatement_0_21[NAME]:::quoted alterPersistentCacheSetStatement_0_21 --> alterPersistentCacheSetStatement_0_22[PREFIX]:::quoted alterPersistentCacheSetStatement_0_22 --> alterPersistentCacheSetStatement_0_23[POSTFIX]:::quoted alterPersistentCacheSetStatement_0_23 --> alterPersistentCacheSetStatement_0_24[stringConstant] alterPersistentCacheSetStatement_0_24 --> alterPersistentCacheSetStatement_0_25[MAINTAIN]:::quoted alterPersistentCacheSetStatement_0_25 --> alterPersistentCacheSetStatement_0_26[booleanConstant] alterPersistentCacheSetStatement_0_26 --> alterPersistentCacheSetStatement_0_27[LOAD]:::quoted alterPersistentCacheSetStatement_0_27 --> alterPersistentCacheSetStatement_0_28[MY]:::quoted alterPersistentCacheSetStatement_0_28 --> alterPersistentCacheSetStatement_0_29[MESSAGES]:::quoted alterPersistentCacheSetStatement_0_29 --> alterPersistentCacheSetStatement_0_30[INDEX]:::quoted alterPersistentCacheSetStatement_0_30 --> alterPersistentCacheSetStatement_0_31[FACTS]:::quoted alterPersistentCacheSetStatement_0_31 --> alterPersistentCacheSetStatement_0_32[HISTORY]:::quoted alterPersistentCacheSetStatement_0_32 --> alterPersistentCacheSetStatement_0_33[TABLES]:::quoted alterPersistentCacheSetStatement_0_33 --> alterPersistentCacheSetStatement_0_34[booleanConstant] alterPersistentCacheSetStatement_0_34 --> alterPersistentCacheSetStatement_0_35[AUTO]:::quoted alterPersistentCacheSetStatement_0_35 --> alterPersistentCacheSetStatement_0_36[UPGRADE]:::quoted alterPersistentCacheSetStatement_0_36 --> alterPersistentCacheSetStatement_0_37[ONCE]:::quoted alterPersistentCacheSetStatement_0_37 --> alterPersistentCacheSetStatement_0_38[alterPersistentCacheSetBackingConnectionString] alterPersistentCacheSetStatement_0_38 --> alterPersistentCacheSetStatement_0_39[alterPersistentCacheSetTableOptions] alterPersistentCacheSetStatement_0_39 --> alterPersistentCacheSetStatement_0_40[alterPersistentCacheSetDataContainerOptions] alterPersistentCacheSetStatement_0_40 --> alterPersistentCacheSetStatement_end((END)) ``` ## Purpose Change central maintained settings for the cache, including defaults for new tables or data containers. - Fresh: number of seconds during which facts are considered fresh after completiong of loading. - Retention: number of seconds trickle loading messages are kept available after processing. - Metadata: number of seconds metadata are kept available after dropping the associated facts in the version. - Metadata recyclebin: number of seconds dropped metadata are kept available. - Version: reset data model version to another version to re-run upgrade scripts. - Token: secret text used as token to retrieve trickle loading messages. - Prefix/postfix: prefix and/or postfix for Invantive Data Replicator maintained database views. - Maintain: indicator whether to maintain database views per table partition and/or overall table. - My Messages: indicator whether to place a copy of incoming trickle loading messages in dc_my_incoming_messages for custom code purposes. - Index Tables: whether to create non-unique indexes on the facts tables for speedier retrieval on database views. - Auto Upgrade: whether to run a data model upgrade once. - Data Container Prefix/postfix: prefix and/or postfix for Invantive Data Replicator maintained overall views. ## alterPersistentCacheSetBackingConnectionString ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR alterPersistentCacheSetBackingConnectionString_start((START)) alterPersistentCacheSetBackingConnectionString_start --> alterPersistentCacheSetBackingConnectionString_0_0[BACKING]:::quoted alterPersistentCacheSetBackingConnectionString_0_0 --> alterPersistentCacheSetBackingConnectionString_0_1[ENCRYPTED]:::quoted alterPersistentCacheSetBackingConnectionString_0_1 --> alterPersistentCacheSetBackingConnectionString_0_2[CONNECTION]:::quoted alterPersistentCacheSetBackingConnectionString_0_2 --> alterPersistentCacheSetBackingConnectionString_0_3[STRING]:::quoted alterPersistentCacheSetBackingConnectionString_0_3 --> alterPersistentCacheSetBackingConnectionString_0_4[stringConstant] alterPersistentCacheSetBackingConnectionString_0_4 --> alterPersistentCacheSetBackingConnectionString_end((END)) ``` ## alterPersistentCacheSetDataContainerOptions ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheSetDataContainerOptions_start((START)) alterPersistentCacheSetDataContainerOptions_start --> alterPersistentCacheSetDataContainerOptions_0_0[DATACONTAINER]:::quoted alterPersistentCacheSetDataContainerOptions_0_0 --> alterPersistentCacheSetDataContainerOptions_0_1[stringConstant] alterPersistentCacheSetDataContainerOptions_0_1 --> alterPersistentCacheSetDataContainerOptions_0_2[LOGICAL]:::quoted alterPersistentCacheSetDataContainerOptions_0_2 --> alterPersistentCacheSetDataContainerOptions_0_3[OVERALL]:::quoted alterPersistentCacheSetDataContainerOptions_0_3 --> alterPersistentCacheSetDataContainerOptions_0_4[VIEW]:::quoted alterPersistentCacheSetDataContainerOptions_0_4 --> alterPersistentCacheSetDataContainerOptions_0_5[NAME]:::quoted alterPersistentCacheSetDataContainerOptions_0_5 --> alterPersistentCacheSetDataContainerOptions_0_6[PREFIX]:::quoted alterPersistentCacheSetDataContainerOptions_0_6 --> alterPersistentCacheSetDataContainerOptions_0_7[POSTFIX]:::quoted alterPersistentCacheSetDataContainerOptions_0_7 --> alterPersistentCacheSetDataContainerOptions_0_8[stringConstant] alterPersistentCacheSetDataContainerOptions_0_8 --> alterPersistentCacheSetDataContainerOptions_end((END)) ``` ## Purpose Changes settings of a data container. The prefix and postfix for the logical overall view of every new replicated table registered in the data container can be specified. This is typically done when there are multiple data containers pointing to the same platform, such as multiple subscriptions on Teamleader or multiple Exact Online countries. Specifying a prefix ensures that the automatically generated logical overall views have unique and recognizable names. ## alterPersistentCacheSetTableOptions ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD alterPersistentCacheSetTableOptions_start((START)) alterPersistentCacheSetTableOptions_start --> alterPersistentCacheSetTableOptions_0_0[TABLE]:::quoted alterPersistentCacheSetTableOptions_0_0 --> alterPersistentCacheSetTableOptions_0_1[tableSpec] alterPersistentCacheSetTableOptions_0_1 --> alterPersistentCacheSetTableOptions_0_2[LOGICAL]:::quoted alterPersistentCacheSetTableOptions_0_2 --> alterPersistentCacheSetTableOptions_0_3[OVERALL]:::quoted alterPersistentCacheSetTableOptions_0_3 --> alterPersistentCacheSetTableOptions_0_4[VIEW]:::quoted alterPersistentCacheSetTableOptions_0_4 --> alterPersistentCacheSetTableOptions_0_5[MAINTAIN]:::quoted alterPersistentCacheSetTableOptions_0_5 --> alterPersistentCacheSetTableOptions_0_6[booleanConstant] alterPersistentCacheSetTableOptions_0_6 --> alterPersistentCacheSetTableOptions_0_7[NAME]:::quoted alterPersistentCacheSetTableOptions_0_7 --> alterPersistentCacheSetTableOptions_0_8[stringConstant] alterPersistentCacheSetTableOptions_0_8 --> alterPersistentCacheSetTableOptions_0_9[PARTITION]:::quoted alterPersistentCacheSetTableOptions_0_9 --> alterPersistentCacheSetTableOptions_0_10[VIEW]:::quoted alterPersistentCacheSetTableOptions_0_10 --> alterPersistentCacheSetTableOptions_0_11[MAINTAIN]:::quoted alterPersistentCacheSetTableOptions_0_11 --> alterPersistentCacheSetTableOptions_0_12[booleanConstant] alterPersistentCacheSetTableOptions_0_12 --> alterPersistentCacheSetTableOptions_0_13[NAME]:::quoted alterPersistentCacheSetTableOptions_0_13 --> alterPersistentCacheSetTableOptions_0_14[PREFIX]:::quoted alterPersistentCacheSetTableOptions_0_14 --> alterPersistentCacheSetTableOptions_0_15[POSTFIX]:::quoted alterPersistentCacheSetTableOptions_0_15 --> alterPersistentCacheSetTableOptions_0_16[stringConstant] alterPersistentCacheSetTableOptions_0_16 --> alterPersistentCacheSetTableOptions_0_17[INDEX]:::quoted alterPersistentCacheSetTableOptions_0_17 --> alterPersistentCacheSetTableOptions_0_18[FACTS]:::quoted alterPersistentCacheSetTableOptions_0_18 --> alterPersistentCacheSetTableOptions_0_19[HISTORY]:::quoted alterPersistentCacheSetTableOptions_0_19 --> alterPersistentCacheSetTableOptions_0_20[TABLE]:::quoted alterPersistentCacheSetTableOptions_0_20 --> alterPersistentCacheSetTableOptions_0_21[booleanConstant] alterPersistentCacheSetTableOptions_0_21 --> alterPersistentCacheSetTableOptions_0_22[STATE]:::quoted alterPersistentCacheSetTableOptions_0_22 --> alterPersistentCacheSetTableOptions_0_23[OBSOLETE]:::quoted alterPersistentCacheSetTableOptions_0_23 --> alterPersistentCacheSetTableOptions_0_24[DROPPED]:::quoted alterPersistentCacheSetTableOptions_0_24 --> alterPersistentCacheSetTableOptions_0_25[PARTITION]:::quoted alterPersistentCacheSetTableOptions_0_25 --> alterPersistentCacheSetTableOptions_0_26[partitionIdentifier] alterPersistentCacheSetTableOptions_0_26 --> alterPersistentCacheSetTableOptions_0_27[APPROACH]:::quoted alterPersistentCacheSetTableOptions_0_27 --> alterPersistentCacheSetTableOptions_0_28[COPY]:::quoted alterPersistentCacheSetTableOptions_0_28 --> alterPersistentCacheSetTableOptions_0_29[TRICKLE]:::quoted alterPersistentCacheSetTableOptions_0_29 --> alterPersistentCacheSetTableOptions_0_30[SAMPLE]:::quoted alterPersistentCacheSetTableOptions_0_30 --> alterPersistentCacheSetTableOptions_0_31[LICENSE]:::quoted alterPersistentCacheSetTableOptions_0_31 --> alterPersistentCacheSetTableOptions_0_32[CONTRACT]:::quoted alterPersistentCacheSetTableOptions_0_32 --> alterPersistentCacheSetTableOptions_0_33[CODE]:::quoted alterPersistentCacheSetTableOptions_0_33 --> alterPersistentCacheSetTableOptions_0_34[stringConstant] alterPersistentCacheSetTableOptions_0_34 --> alterPersistentCacheSetTableOptions_end((END)) ``` ## Purpose Change table and table partition-specific settings for the cache. Most options are identical as on alterPersistentCacheSetStatement, but intended for table/table partition-specific deviations. Additional options are: - State: move all table partition versions with an earlier state to a specific end state. - Approach: replication strategy.