ETL Transfering problems

[Toni]

Hi my friends!

I'm experimenting some troubles with ETL, some estrange ones that I don't know what can be happening.

The problem is that ETL is getting SQL Timeouts exceptions when tries to access the ODS database, as shown:


ETL_Trans_Only*:* 11:35:17.762 ERROR                    Exception happened:
ETL_Trans_Only*:* SQLState: 40001
ETL_Trans_Only*:* Message:  DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 68
ETL_Trans_Only*:* Vendor:  -911
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 68
ETL_Trans_Only*:*      at com.ibm.db2.jcc.c.rc.b(rc.java:3189)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.b.fb.i(fb.java:227)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.b.fb.a(fb.java:183)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.b.fb.a(fb.java:34)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.b.s.a(s.java:30)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.b.h.Gb(h.java:325)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.c.rc.R(rc.java:3031)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.b.d.f(d.java:1070)
ETL_Trans_Only*:*      at com.ibm.db2.jcc.c.r.a(r.java(Compiled Code))
ETL_Trans_Only*:*      at com.ibm.db2.jcc.c.rc.c(rc.java(Compiled Code))
ETL_Trans_Only*:*      at com.ibm.db2.jcc.c.rc.next(rc.java(Compiled Code))
ETL_Trans_Only*:*      at SrcChunkLogMonitor.readAll(SrcChunkLogMonitor.java:71)
ETL_Trans_Only*:*      at SrcChunkLogMonitor.executeCommand(SrcChunkLogMonitor.java:44)
ETL_Trans_Only*:*      at BasicMonitor.run(BasicMonitor.java:50)
ETL_Trans_Only*:*      at java.lang.Thread.run(Thread.java:568)
ETL_Trans_Only*:* 11:35:17.768 Thread.SourceManager#1  caught an exception. Monitor: SrcChunkLogMonitor#1 Message: SrcChunkLogMonitor#1: unable to execute command: START
ETL_Trans_Only*:* 11:35:17.769 Source                  the only left source has caught an exception. Exiting...
ETL_Trans_Only*:* 11:35:18.131 Main                    TRANSFORM finished




That's happenning continously, but some times I can see is getting and transfering data, and some other gets stopped. For example, it was geeting data From Friday 10:00 am till Sunday 0:00, and then, stopped.

Datasourcer seems to be working properly...any idea my friends?

The only changes I made was to change some layout filters in DMA Applications, those filters were created in CCAnayzer Stat Server and where working ok, because StatServer logs don't complain about those new filters.


Thank you!

[mark]

Hi Toni,

When it fails to transfer, are you seeing any 'deadlock' errors being reported in the logs?

Mark

[René]

Hi Toni,

Seems the issue is caused by a deadlock so check if only one instance of ETL is running. Having two instances of ETL running could lead to such situation - two ETL_Transfer processes trying to update the same table...

René

[Toni]

Hello,

I've checked, and there is only one instance of ETL running at a time.

Also I've started it mannualy with:

java -jar transform.jar -conf etl.properties -transOnce

And is also giving me the same timeout error, only in the ETL log. Ive looked at ods database server log, datasourcer... and I didn't see any deadlock error, nor timelock.

The problem is that historical data is not beeing transefered for days, so the user is getting angry...

We've talked with our database admin, and he's going to see which application, if there is any, is the responsible of those timeouts, but I don't know if it is a database problem or not.

Toni

[Kevin S]

Not sure if your environment is single site or multiple site, but...
Is the database ETL is trying to access local, or remote?

One issue we had in a multi-site was where we had Datasourcer A, DB Server A, and the MS SQL Server in location A, and Datasourcer B + DB Server B in Location B . Datasourcer B would consistently be lagging behind and writing to the save file. It might take 2 or 3 days lag behind, but it always did.

Our solution was to move DB Server B to location A (the same site as the MS SQL Server). Once this was done, the datasourcer has not lagged behind.

To echo René's posting, we had the same issue when we tried to have two ETLs processing from Datasourcer. We shut the second one (remote) off, and the single ETL has been working fine.

[Toni]

Hi Kevin,

Our environment is single site, and all the historical components are in the same machine.. ...

Thanks

[René]

Toni,

Does the users use reports working over non-aggregated data? If they do then the report's query could lock the table holding non-aggregated (15min) data so ETL is unable to add new rows to it. I'm really guessing because I don't know your environment and have no experience with DB2.

René