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 |
---|---|
| ID of the recommended optimization corresponding to the job to be created. |
| Id of the optimization job to be changed (for |
| String representing the types of the indexes that the system must create after completing the replication. See the |
| 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 |
| 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 |
| Number of parallel runs the job can have; If the number is reached, further starts of the particular job will be ignored. Default: |
| Number of retry attempts before the job is set to |
| Delay in seconds between the retry attempts |
| Individual job timeout in minutes. If no |
| Job's unique identifier; please see jobName for more information |
| Custom uuid of the job to be created |
| UUID of the recommended optimization corresponding to the job to be created. |
任意のパラメータのある場合とない場合の例を示します:
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.1Incremental 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 |
---|---|
| ID of the recommended optimization corresponding to the schedule job to be created. |
| Id of the incremental optimization job to be changed (for |
| String representing the check expression for the incremental optimization schedule job. All field names used within a |
| 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 |
| 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 |
| 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 |
| Number of parallel runs the job can have; If the number is reached, further starts of the particular job will be ignored. Default: |
| Number of retry attempts before the job is set to |
| Delay in seconds between the retry attempts |
| Individual job timeout in minutes. If no |
| Job's unique identifier; please see jobName for more information |
| Custom uuid of the job to be created |
| UUID of the recommended optimization corresponding to the job to be created. |
uuid
and recOptUuid
parameters in SYSADMIN.createIncrementalOptimizationJob
and
are available since v4.1SYSADMIN.
changeIncrementalOptimizationJob
deleteOldData
パラメータの動作は以下のとおりです:
To view the full table, click the expand button in its top right corner
| identity expression | data matching | data no | "match |
---|---|---|---|---|
| no | yes | yes | the value in the source is |
| yes | no* | no | |
| no | no | no | the value in the source is |
| 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
.
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.