Optimization Jobs は、推奨される最適化に関連する Data Sources の完全な Replication を An alytical Storageに実行します。ジョブが複数回スケジュールされる場合、実行のたびに新しいマテリアライズドテーブルが作成され、Optimization に割り当てられます。その間に新しいレプリケーションが行われたために古くなったテーブルは、cleanUpジョブによって削除されます。

ここでは、推奨されるOptimization のジョブを作成する方法を説明します:

SYSADMIN.createOptimizationJob(IN recOptId biginteger, IN allowIndexCreation string, IN gatherNativeStats boolean, IN indexCreationByStatus string, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN runTimeout integer, IN jobName string, IN uuid string, IN recOptUuid string, OUT id biginteger NOT NULL RESULT)

このプロシージャは新しく作成されたジョブのidを返し、必要に応じてジョブの変更に使用できます:

SYADMIN.changeOptimizationJob(IN jobId biginteger, IN allowIndexCreation string, IN gatherNativeStats boolean, IN indexCreationByStatus string, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN runTimeout integer, IN uuid string, IN recOptUuid string, OUT id biginteger NOT NULL RESULT)

どちらのプロシージャも以下のパラメータを使用します:

To view the full table, click the expand button in its top right corner

Parameter

Description

recOptId

ID of the recommended optimization corresponding to the job to be created. recOptId or recOptUuid or both should be set

jobId

Id of the optimization job to be changed (for changeOptimizationJob)

allowIndexCreation

String representing the types of the indexes that the system must create after completing the replication. See the ALLOW_INDEX_CREATION section in Default Options for information on which values are allowed. This parameter is optional and the default value is ALL

gatherNativeStats

Flag to define if native statics on the materialized table should be gathered or not when the replication has been completed. This parameter is optional and the default value is TRUE

indexCreationByStatus

String representing the status that the indexes must have in order to be created after completing the replication. Please refer to Recommended Index Status for a list of possible values. The default value is the value of the recommended optimization's allowIndexCreationByStatus field.

parallelRunsAllowed

Number of parallel runs the job can have; If the number is reached, further starts of the particular job will be ignored. Default: 1

retryCounter

Number of retry attempts before the job is set to FAILED state

retryDelay

Delay in seconds between the retry attempts

runTimeout

Individual job timeout in minutes. If no runTimeout is set, configuration set via default value option JOB_RUN_TIMEOUT will be considered for the job; if runTimeout is reached before the job is finished, the job terminates with the INTERRUPTED state

jobName

Job's unique identifier; please see jobName for more information

uuid

Custom uuid of the job to be created

recOptUuid

UUID of the recommended optimization corresponding to the job to be created. recOptId or recOptUuid or both should be set

任意のパラメータのある場合とない場合の例を示します:

CALL SYSADMIN.createOptimizationJob(recOptId => 5)
 
CALL SYSADMIN.createOptimizationJob(recOptId => 5, allowIndexCreation => 'JOIN, MANUAL')
 
CALL SYSADMIN.createOptimizationJob(recOptId => 5, allowIndexCreation => 'NONE', gatherNativeStats => FALSE)

uuid and recOptUuid parameters in SYSADMIN.createOptimizationJob and changeOptimizationJob are available since v4.1

Deleting a Scheduler Job

この特別なプロシージャは、id またはuuid によってスケジューラージョブを削除します:

SYSADMIN.deleteSchedulerJob(IN id biginteger, IN uuid string)
uuid parameter in SYSADMIN.deleteSchedulerJob is available since v4.1

Incremental Optimization Jobs

この特別なタイプのOptimization Jobs は、マテリアライズドテーブルへのデータの増分ロードをサポートします。CData Virtuality Server は、newRowCheckExpressionパラメータの評価に対応するマテリアライズドテーブルの行を更新し、対応する行を削除(オプション)および挿入します。

このようなジョブの最初の実行はフル・レプリケーションとなり、新しいMaterialized Tableの作成が含まれます。最初のフルレプリケーション実行には時間がかかり、その間にソースに新しいデータが到着している可能性があるため、インクリメンタルロードが直接実行されます。それ以降の実行では、新しいMaterialized Tableは作成されません。データは、Incremental Optimizationスケジュールジョブの設定に従って、既存のMaterialized Tableに追加されます。

そして、このような増分更新のためのスケジュールジョブの作成方法です:

SYSADMIN.createIncrementalOptimizationJob(IN recOptId biginteger, IN newRowCheckExpression string NOT NULL, IN identityExpression string, IN deleteOldData boolean NOT NULL, IN gatherNativeStats boolean, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN runTimeout integer, IN jobName string, IN uuid string, IN recOptUuid string, OUT id biginteger NOT NULL RESULT)

このプロシージャは新しく作成されたジョブのidを返し、必要に応じてジョブの変更に使用できます:

SYSADMIN.changeIncrementalOptimizationJob(IN jobId biginteger, IN newRowCheckExpression string NOT NULL, IN identityExpression string, IN deleteOldData boolean NOT NULL, IN gatherNativeStats boolean, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN runTimeout integer, IN uuid string, IN recOptUuid string, OUT id biginteger NOT NULL RESULT)

どちらのプロシージャも以下のパラメータを使用します:

To view the full table, click the expand button in its top right corner

Parameter

Description

recOptId

ID of the recommended optimization corresponding to the schedule job to be created. recOptId or recOptUuid or both should be set

jobId

Id of the incremental optimization job to be changed (for changeIncrementalOptimizationJob)

newRowCheckExpression

String representing the check expression for the incremental optimization schedule job.  All field names used within a newRowCheckExpression should be wrapped in double quotes

identityExpression

String representing the identity expression for the incremental optimization schedule job expressed in SQL language. If this parameter is null, no identity expressions will be used to delete old rows from the materialized table. All field names used within an identityExpression should be wrapped in double quotes

deleteOldData

Determines how data which is already in the materialized table and has a matching check expression or a matching identity expression is treated; see below for more information

gatherNativeStats

Flag to define if native statics on the materialized table should be gathered or not when the replication has been completed. This parameter is optional and the default value is FALSE

parallelRunsAllowed

Number of parallel runs the job can have; If the number is reached, further starts of the particular job will be ignored. Default: 1

retryCounter

Number of retry attempts before the job is set to FAILED state

retryDelay

Delay in seconds between the retry attempts

runTimeout

Individual job timeout in minutes. If no runTimeout is set, configuration set via default value option JOB_RUN_TIMEOUT will be considered for the job; if runTimeout is reached before the job is finished, the job terminates with the INTERRUPTED state

jobName

Job's unique identifier; please see jobName for more information

uuid

Custom uuid of the job to be created

recOptUuid

UUID of the recommended optimization corresponding to the job to be created. recOptId or recOptUuid or both should be set

uuid and recOptUuid parameters in SYSADMIN.createIncrementalOptimizationJob and SYSADMIN.changeIncrementalOptimizationJob are available since v4.1


deleteOldDataパラメータの動作は以下のとおりです:

To view the full table, click the expand button in its top right corner

deleteOldData

identity expression
is used

data matching
row-check expression
will be deleted
in materialized table

data no
longer present
at source and
matching
row-check expression
will be lost in
materialized table

"match
row-check
expression
in source" means

TRUE

no

yes

yes

the value in the source is
equal or greater
then the maximal value
in materialized table

TRUE

yes

no*

no

FALSE

no

no

no

the value in the source is
greater than the
maximal value in
materialized table

FALSE

yes

no

no

* 厳密には、一部のデータは削除されますが、データのみが削除されます。

    • これはまだソースに存在します;
    • ソースにマッチするROWS-CHECK 式がある場合;
    • IDITY 式がすでにマテリアライズド・テーブルに存在する場合。

これは、Replicationジョブ内でソースからの新しい値と共に再度Materialized Tableに挿入されるため、実質的にこれらの行は更新されます。

異なる値の例をいくつか示します:

CALL SYSADMIN.createIncrementalOptimizationJob(recOptId => 159, newRowCheckExpression => 'YEAR(orderDate)', identityExpression => null, deleteOldData => true)
 
CALL SYSADMIN.createIncrementalOptimizationJob(recOptId => 159, newRowCheckExpression => 'YEAR(orderDate)', identityExpression => 'id', deleteOldData => false)
 
CALL SYSADMIN.createIncrementalOptimizationJob(recOptId => 159, newRowCheckExpression => 'YEAR(orderDate)', identityExpression => 'id', deleteOldData => false, gatherNativeStats => true)


The accessState  of the corresponding materialized table (in SYSADMIN.MaterializedTable) will be set to UPDATING during the execution of the incremental update bob to inform the user that an incremental job is running on the current materialized table. The old (non-updated) values from this materialized table will be returned unless the update has finished. If an error occurs during the incremental update, accessState will be set to INCOMPLETE.

If the server is killed when an incremental job is updating a materialized table, its state will be set to INCOMPLETE when the server restarts.


At the moment, we only support incremental updates on a single table (Recommended Optimizations with type MAT_TABLE). If an incremental optimization job for a MAT_JOIN is created, an error message will be shown to the user.