Problem with ETL Service

[edobellamy]

Hi,

This is my old docs for the problem, not all applies but maybe the sql for the daad ock

Cheers,

Possible Actions
1. Try to manually resolve the dead-lock situation.

2. Change settings that can cause a dead-lock.

3. Increase Server physical memory.

4. Migrate CC Analyser to version 6.5.

5. Patch SQLServer 2000 to SP2 or SP3

6. Check DB with DBCC CHECKDB

Resolve Dead-Lock
Wee need to verify that there are not multiple java processes being started.

Change of parameters:

maxNumberOfChunksInReadQueue=10

maxNumberOfChunksInWriteQueue=20

in ETL Service application properties, 'ETL_Trans_Only' section in CME to


      maxNumberOfChunksInReadQueue=1

maxNumberOfChunksInWriteQueue=1


Create full backup of ODS and DataMart dbs.


Perform in the MSSQL Query Analyzer this script:


update ODS.genesys.ol_chunk_log set transferred_time = begin_time where ODS.genesys.ol_chunk_log.transferred_time is NULL and log_id in (select src_chunk_ID from DataMart.genesys.chunk_log


Note, if this query doesn’t run, is bauause the amount of data and lack of appropriate indexes. Another choice is to set all to transferred, but it will lose data. If is acceptable it can be a way to move forward to unlock the ETL process. 



Increase Server Memory
Currently there is around 200 MB spare capacity from about 1 GB. An increase in memory usage due to any temporary issue might cause a paging situation and repeat the problem.

Upgrade to 6.5.001.15 version
Genesys recommends updating to version 6.5.001.15 which includes performance Improvements.

ChangE tran_BATCH_MODE in DB Server
This setting Improves performance for inserts, changing this setting in the db server that inserts ODS data might result in performance improvement. This needs to be thoroughly tested as it is not a setting that normally should be changed. 

[NEXT]

just a note :

I think that maxNumberOf-ChunksInRead-Queue and maxNumberOf-ChunksInWrite-Queue are not used any more with 7.2 (?)

Look at the 72rt_us_etlruntime[1].pdf:

Note: Starting with the 7.0.2 release, ETL Runtime no longer processes this
parameter. Refer to the chunksAtOnce parameter instead.


You find chunksAtOnce in etl.properties and can try to update it to 64 (max value)

You can look too :
chunkBufferQuota :
Default Value: 786432 (statistics)
Valid values: Positive integers from 1024 to 16777216

and chunkQuota :
Default value: 1536 (data chunks)
Valid values: 2 to 8192


But i’m not sure that it is your issue

[MATEOB]

[attach=#]

Thanks for your answers ! The last one resolve our problems !!
ETL service run perfectly now !