Hei !!
I need help !!! :-\

Those last days, the ETLService can not run well. It just stops and we have to restart it via SCI.
We work with the Genesys Version 7.2 and our database is configured as SQL server installed on a Cluster MSCS Microsoft W2003 Enterprise Editon with Service Pack 2.
The problem looks like the ETL service can not reach the ODS table because it is locked or there is too much transactions going on.
We defragment the ODS base but nothing changes.
If anyone have an idea, please send it.

Hi,

there are several reasons why this might happen. We had the same problem a very long time ago.

Here are some of the things to check right off the bat:

-ETL Service CANNOT BE Set to run automatically. Make sure it is set to MANUAL (in case of Windows).
-Check log for errors. Is there an error saying “Duplicate Key” or something like this?

• the most common mistake is to have your ETL conversion and transfer processes run too close to each other. There need to be at least three minute difference between them, otherwise you would get something like "another process detected within the last 180 seconds)
• do you have “INSERT statement conflicted with COLUMN FOREIGN KEY constraint” error anywhere in ETL logs?

First of all, can you post your .properties settings (you can remove the password for security reasons obviously)?

If you can also find some sort of error in the log, please post that log here as well. (make sure logging is set to debug with full verbose)

Best regards,
Vic

Thanks for your answer Victor !!

I check the different point that you already proposed :

• ETL Service is set to MANUAL.
• I don’t have any “Duplicate Key” error in the ETLService.log
• About the conversion and transfer processes, I think they are well defined because during the last 6 mounths we didn’t have any problems with the ETLService.
• The errors that we find in the log files are :
  ETL_Trans_Only*:* 05:31:59.959 Write.Heartbeat deadlock is suspected in SourceDefMonitor. Exiting…

???
Last night, the ETLService has to be restart manually 6 times (23h, 00h25, 1h25, 2h55, 4h55, 6h55).
I don’t see any errors in the file ETLService.Log corresponding to this hours.

There is some transactions which are long to be done in the SQL server and we often see some locks on the database !!?
In DMA, we see that Datasoucer need more than 10 min to transfer the data

This is our ETl.properties :

Properties generated by the the Configuration Wizards on Wednesday, November 15, 2006 5:17:51 PM

ConfServer application name of type ‘ETL-Proxy’

CfgAppName=ETL_Proxy

ConfServer user login.

CfgUser=toto

ConfServer user password.

CfgUserPassword=******

Encrypted ConfServer user password.

#CfgUserPasswordEncrypted=**********

end of installation-generated options

Common properties for all ETL modes

DATAMART owner database username

user=toto

DATAMART owner database password

pass=*******

DBA username (for tenant_alias_update)

dba_user=toto

DBA password (for tenant_alias_update)

dba_pass=*******

for ORACLE: jdbc:oracle:thin:@dbhost:dbport:ORACLE_SID

for MSSQL: jdbc:jtds:sqlserver://dbhost:dbport;DatabaseName=dbname

for SYBASE: jdbc:sybase:Tds:dbhost:dbport/dbname

for DB2: jdbc:db2://dbhost:dbport/dbname

provided to JDBC driver for connect to DATAMART database

jdbcurl=jdbc:jtds:sqlserver://CLSQLTEL:1179;DatabaseName=DataMart

Properties for transformation

will drop transferred tables as soon as make sure

that data actually was transferred

#dropTransferredTables

alternative tablespace for data results tables

[by default will create tables at user default tablespace]

#ORACLE_TablespaceNameForDataTables=

alternative tablespace for data indexes

[by default will create indexes at user default tablespace]

#ORACLE_TablespaceNameForDataIndexes=

Properties for aggregation

number of intervals which needs to be present at parent level

that will force us to do aggregation for

[defaults: will aggregate as soon as parent contain

complete interval and event about next

interval beginning was received]

#numberOfIntervalsToWaitFor_AGG_BY_HOUR=4
#numberOfIntervalsToWaitFor_AGG_BY_DAY=1

after complete aggregation of any level will update

statistics for tables&indexes belongs to this level

#updateStatsForTablesAndIndexesAfterEachAgg
#will update statistics for tables&indexes one time only
updateStatsForTablesAndIndexes

after aggregation of first child complete will update

statistics for tables&indexes belongs to level0(parent)

updateStatsForLevel0Views

end of common options

We find this errors in the log file :

ETL_Trans_Only*:* 05:31:59.959 Write.Heartbeat deadlock is suspected in SourceDefMonitor. Exiting…
ETL_Trans_Only*:* 06:34:13.411 Source.SourceDef deadlock is suspected in SrcChunkLogMonitor#1

Do you know those errors ?

Thanks !!

Mateo

There are some reports about deadlocks on Genesys Tech Support web site.

Do you have access ?

No I don’t have the acces !!

Have you upgraded the ETL, to atleast 7.2.000.15? I know you said your running version 7.2, but what about the ETL?

Se below:
Upgrade to the latest ETL Runtime 7.2 because of the following issues, which are related to ‘deadlock’:

Release Number 7.1.000.14 [10/13/2005] - Hot Fix

This release corrects the following defects which occurred in ETL Runtime releases 7.0.200.10, 7.0.200.12, and 7.1.000.09:

• In case of some long running operations, ETL Runtime would mistakenly determine a deadlock condition and exit.
• In trans_only and trans_and_agg mode, after experiencing several ODS(s) database exceptions, ETL Runtime stopped transferring chunks. (ER# 10823353)

Release Number 7.2.000.15 [03/31/06] – General (Under Shipping Control)

ETL Runtime ships with the JDBC driver from the jTDS project. This new driver avoids the problem of placing unnecessary locks on objects in the MSSQL tempdb database found in previous releases. (ER# 22635345)

If checking the ETL version/upgrading does not fix the problem, add the following to your ETL properties file:

LevelOfLog=ALL:6
Log-Level=ALL
debug-level=ALL

You may also want to verify buffering=true under the log section in CME.

Then stop and restart the ETL and upload the last log before it stops.

Thanks

Thanks for your answers Karma !! !

The ETLService Version is 7.2.001.6 so we don’t need to upgrade it.

I checked the ETL.properties file, I didn’t find options that you proposed.
I didn’t find the option buffering in the log section of ETL Service in CME.
What will it change in the logs when I will add those options ?

Our DBA find a bug in SQL server related to this problems. We are still looking for a solution …

Thanks

Mateo

So yes, you will need to add those options into the etl.properties and the log section of ETL in CME. The buffering will prevent the application from writing heavily to the disk, but for the ETL it doesn’t really matter as it’s only run every 15 min, but good to have set anyways. Just make sure you have plenty of disk space for the logs and monitor it if it grows over what you available, but I don’t think it will.

Another question :

Do you know how to configure the ELT runtime in the ETL.properties file to ships with the JDBC driver from the jTDS project ?

Thanks

Mateo

Hi !!

We checked our cluster and we found that the process ETLChunkData locks the tables ODS to run but it doesn’t liberate its. So the other process can not run and ETL Service stops 30 min to 1 hour.
After 1 hours, ETLService manages to run back and collect 1 hour of datas without restarting.

Do you heard about this problems ?

Ok, how about this:

1. Auto Update Statistics - turn this SQL Server feature off (google for how)
2. Are you sure you are not running SQL Server 2000? (try this to verify: select @@version and see what it returns. If you have MS SQL Server 2000, then problem is something that Genesys has already dealt with in one of its advisories - I will need to remember which one and email you later if this is the case.
3. are you sure you don’t have this: “unable to get object using id:”? If so then it would explain this…
4. VERY OBVIOUS ONE: try maxNumberOfChunksInReadQueue=1 and maxNumberOfChunksInWriteQueue=1… because default is 10 or 20 and it killed my SQL Server each and every time before…
5. Long shot: there is also SQL AutoShrink feature - try turning it off and see if this makes your life easier.

My initial guess would be (2) or (4)… I would definitely issue ticket to Genesys on this if my suggestions did not work. Out of curiosity - your your CPU is not 100% all the time is it?

Thanks Victor for your answers !!
I would like to set maxNumberOfChunksInReadQueue and maxNumberOfChunksInWriteQueue but I don’t know where they are defined. Can you help me ?

We are running SQL server 2005.
Our CPU is not 100% used.
We already open a ticket to Genesys support … without any concrete answer for the moment.
We try to update our Datasoucer with the latest version but nothing change !!

We don’t loose any data but the problem with the ETLService is the same.

Hi,

Check in CME, under the Datamart/ETL_Service application. Options for ETL_Trans_Only/Command1. There you find the options.

About the problem with lock in ODS. We had the same problem a year ago, and our workaround was to configure ETL to use the TranceOnce process instead of Trans Only. Trans Only keeps the lock on ODS database continuosly. Trans Only stops when it has finished transfer data.

If you re-configure ETL to use Trance Once 4 times an hour, it means that the lock will only be on the tables when ETL are running.

Add the option -tranceOnce in CME for ETL_Trans_Only and change start time for ETL_Trans_Only to 00:05+00:15.
Restart ETL service.

This solved the problem for us.

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.

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

[attach=#]

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