Problem with ETL Service

[MATEOB]

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.

[victor]

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

[MATEOB]

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=<tablespace name>
# alternative tablespace for data indexes
# [by default will create indexes at user default tablespace]
#ORACLE_TablespaceNameForDataIndexes=<tablespace name>
##
## Properties for aggregation
##
# number of intervals which needs to be present at parent level
# that will force us to do aggregation for <AGG LEVEL>
# [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

[MATEOB]

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

[Sylvainsjc]

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

Do you have access ?

[MATEOB]

No I don't have the acces !!

[Daimonas]

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)

[Daimonas]

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

[MATEOB]

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

[Daimonas]

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.

[MATEOB]

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

[MATEOB]

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 ?

[victor]

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?

[MATEOB]

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.

[catanirex]

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.