ETL Problem

[jbserna]

We installed the solution Genesys Express 4.5 with MSSQL Server 2005 on Windows 2003 Enterprise SP3.

Since it was created incorrectly in the DMA a number of variables to store historical data (these variables were created several times with the same name) is not being transferred data and therefore have no historical data from that day.


We eliminated these variables in DMA and in the tables and views in the database ETL and ODS. Now we get the following error in the log of ETL.

Starter: Process [ETL_Trans_Only] is still running [PID=704], skipping the start point  Fri Oct 29 21:09:00 2010
Starter: Fri Oct 29 21:09:00 2010 [ETL_Trans_Only] will be started on Fri Oct 29 21:10:00 2010 start in 60 seconds
Starter: Waiting for process [ETL_Trans_Only] to exit...
Starter: Process [ETL_Trans_Only] has terminated with exit code 5
Starter: Starting 'ETL_Trans_Only' on  Fri Oct 29 21:10:00 2010
Starter: Current directory is set to .
Starter: Executing "java -jar transform.jar -conf etl.properties -jdbcurl=jdbc:jtds:sqlserver://genesys01:1433;DatabaseName=ETL -user=genesys -passEncrypted=******"
Starter: Pipe 300 "\\.\pipe\063760000107748_CCA" and Event 208 created for ETL_Trans_Only
Starter: waiting for a client to connect to the pipe 300.
Starter: client connected to the pipe 300.
Starter: Fri Oct 29 21:10:00 2010 [ETL_Trans_Only] will be started on Fri Oct 29 21:11:00 2010 start in 60 seconds
ETL_Trans_Only*:* Time        Origin                    Message
ETL_Trans_Only*:* ------------ -------------------------- ----------------------------------------
ETL_Trans_Only*:* 21:10:00.990 Main ETL Runtime, Version: 7.2.002.02, Build: 2006/10/23 12:34 (C) Copyright 1994-2005 Genesys Telecommunications Laboratories, Inc.
ETL_Trans_Only*:* 21:10:01.194 Main.Env Runtime info: {java.runtime.name=Java(TM) 2 Runtime Environment, Standard Edition, sun.boot.library.path=C:\GCTI\MCR\jre\bin, java.vm.version=1.4.2_14-b05, java.vm.vendor=Sun Microsystems Inc., java.vendor.url=http://java.sun.com/, path.separator=;, java.vm.name=Java HotSpot(TM) Client VM, file.encoding.pkg=sun.io, user.country=ES, sun.os.patch.level=Service Pack 2, java.vm.specification.name=Java Virtual Machine Specification, user.dir=C:\GCTI\CC_Analyzer\etl_runtime, java.runtime.version=1.4.2_14-b05, java.awt.graphicsenv=sun.awt.Win32GraphicsEnvironment, java.endorsed.dirs=C:\GCTI\MCR\jre\lib\endorsed, os.arch=x86, java.io.tmpdir=C:\WINDOWS\TEMP\, line.separator=<CR><LF>, java.vm.specification.vendor=Sun Microsystems Inc., user.variant=, os.name=Windows 2003, sun.java2d.fontpath=, java.library.path=C:\WINDOWS\system32;.;C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files\HP\NCU;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Java\jdk1.5.0_12\jre\bin\server, java.specification.name=Java Platform API Specification, java.class.version=48.0, java.util.prefs.PreferencesFactory=java.util.prefs.WindowsPreferencesFactory, os.version=5.2, user.home=C:\WINDOWS\system32\config\systemprofile, user.timezone=Europe/Paris, java.awt.printerjob=sun.awt.windows.WPrinterJob, file.encoding=Cp1252, java.specification.version=1.4, java.class.path=transform.jar, user.name=SYSTEM, java.vm.specification.version=1.0, java.home=C:\GCTI\MCR\jre, sun.arch.data.model=32, user.language=es, java.specification.vendor=Sun Microsystems Inc., awt.toolkit=sun.awt.windows.WToolkit, java.vm.info=mixed mode, java.version=1.4.2_14, java.ext.dirs=C:\GCTI\MCR\jre\lib\ext, sun.boot.class.path=C:\GCTI\MCR\jre\lib\rt.jar;C:\GCTI\MCR\jre\lib\i18n.jar;C:\GCTI\MCR\jre\lib\sunrsasign.jar;C:\GCTI\MCR\jre\lib\jsse.jar;C:\GCTI\MCR\jre\lib\jce.jar;C:\GCTI\MCR\jre\lib\charsets.jar;C:\GCTI\MCR\jre\classes, java.vendor=Sun Microsystems Inc., file.separator=\, java.vendor.url.bug=http://java.sun.com/cgi-bin/bugreport.cgi, sun.io.unicode.encoding=UnicodeLittle, sun.cpu.endian=little, sun.cpu.isalist=pentium i486 i386}
ETL_Trans_Only*:* 21:10:01.194 Main.Options Options: {jdbcurl=jdbc:jtds:sqlserver://genesys01:1433;DatabaseName=ETL, user=genesys, passEncrypted=7F7245B9D06A87A6}
ETL_Trans_Only*:* 21:10:01.210 Main TRANSFORM started
ETL_Trans_Only*:* 21:10:01.492 Write.Heartbeat Pulse: 2010/10/29, CEST, 900, 1.9M
ETL_Trans_Only*:* 21:10:01.523 LFolder Connected database: Microsoft SQL Server, version: 09.00.3042, driver: 1.2
ETL_Trans_Only*:* 21:10:01.853 LFolder views OK in LayoutFolder[l:1_1,f:1]
ETL_Trans_Only*:* 21:10:01.853 LFolder views OK in LayoutFolder[l:1_10,f:10]
ETL_Trans_Only*:* 21:10:01.868 LFolder views OK in LayoutFolder[l:1_11,f:11]
ETL_Trans_Only*:* 21:10:01.884 LFolder views OK in LayoutFolder[l:1_12,f:12]
ETL_Trans_Only*:* 21:10:01.884 LFolder views OK in LayoutFolder[l:1_13,f:13]
ETL_Trans_Only*:* 21:10:01.884 LFolder views OK in LayoutFolder[l:1_14,f:14]
ETL_Trans_Only*:* 21:10:01.900 LFolder views OK in LayoutFolder[l:1_15,f:15]
ETL_Trans_Only*:* 21:10:01.900 LFolder views OK in LayoutFolder[l:1_16,f:41]
ETL_Trans_Only*:* 21:10:01.915 LFolder views OK in LayoutFolder[l:1_17,f:42]
ETL_Trans_Only*:* 21:10:01.915 LFolder views OK in LayoutFolder[l:1_18,f:43]
ETL_Trans_Only*:* 21:10:01.931 LFolder views OK in LayoutFolder[l:1_2,f:2]
ETL_Trans_Only*:* 21:10:01.947 LFolder views OK in LayoutFolder[l:1_3,f:3]
ETL_Trans_Only*:* 21:10:01.963 LFolder views OK in LayoutFolder[l:1_4,f:4]
ETL_Trans_Only*:* 21:10:01.963 LFolder views OK in LayoutFolder[l:1_5,f:5]
ETL_Trans_Only*:* 21:10:01.978 LFolder views OK in LayoutFolder[l:1_6,f:6]
ETL_Trans_Only*:* 21:10:01.978 LFolder views OK in LayoutFolder[l:1_7,f:7]
ETL_Trans_Only*:* 21:10:01.994 LFolder views OK in LayoutFolder[l:1_8,f:8]
ETL_Trans_Only*:* 21:10:01.994 LFolder views OK in LayoutFolder[l:1_9,f:9]
ETL_Trans_Only*:* 21:10:02.010 Source.SourceDef starting monitoring: DatabaseAccessPoint[ name:source#1, ConnectObject[ url:jdbc:jtds:sqlserver://genesys01:1433;DatabaseName=ODS, user: genesys, pass: ***** ] ], Database: Microsoft SQL Server, Version: 09.00.3042, Driver: 1.2
ETL_Trans_Only*:* 21:10:02.966 Thread.DstChunkWritersManager caught an exception. Monitor: Writer#1 Message: Writer#1: unable to execute command: WRITE
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.BatchUpdateException: Invalid column name 'SLR_ENTERED_CIU_1'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:901)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_ENTERED_CIU_2'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_ENTERED_CIU'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_ABAND_CIU_1'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_ABAND_CIU'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_T_ANSW_CIU'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_ABAND_CIU'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_ENTERED_CIU'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: 42S22
ETL_Trans_Only*:* Vendor:  207
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* java.sql.SQLException: Invalid column name 'SLR_T_ANSW_CIU'.
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.TdsCore.getBatchCounts(TdsCore.java:1435)
ETL_Trans_Only*:* at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:880)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.doInsertData(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.processChunks(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.ChunkWriterJob.perform(Unknown Source)
ETL_Trans_Only*:* at ETL.Writer.WriterMonitor.executeCommand(Unknown Source)
ETL_Trans_Only*:* at ETL.Threads.BasicMonitor.run(Unknown Source)
ETL_Trans_Only*:* at java.lang.Thread.run(Unknown Source)

What could we do to solve the problem?

Thanks for your help

[cavagnaro]

[quote]
ETL_Trans_Only*:* 21:10:02.966 Thread.DstChunkWritersManager  caught an exception. Monitor: Writer#1 Message: Writer#1: unable to execute command: WRITE
[/quote]

Seems that the user doesn't have full permissions on the DB

[Grum]

Hello, jbserna.
I had the problem with ETL like yours', but I don't know if my solving the problem can help you.
It is one of the most Genesys tip&tricks, so-called "pitfall".
Start at the begginer...
When you modelling you data model for your statistic in Data Modelling Assistent you should know one thing
!!!you must not create the NEW statistic with the name has already ONCE EXISTED!!!
1)For example, you have a statistic named M_TALK_TIME from statistical type MaxInTalkTime in some Layout Teamplate and resp., in Report Layout.
2)You desire to create a NEW statistic like already existed M_TALK_TIME but, for example, with some filter.
WRONG STEPS:
3)You delete your statistic M_TALK_TIME and create a new one with filter and with the SAME name "M_TALK_TIME"
In this case statistic information collected from your statistical types will stop transfering to historical DB.
RIGHT STEPS:
3)You should create a new statistic WITHOUT deleting old stat. with a name "M_TALK_TIME_FILTER".
You can create a new statistic also with deleting M_TALK_TIME but in future you can foget it. So I adviсe you NOT TO delete older statistic ANYTIME and create a new one just with other name.

So, what you should do:
1) Simplest solving - find out, what layout was activated at the day when statistic was stopped transfering, delete it and then create a new one with UNIQUE names of statistic types. Or just delete only one or several problem statistic and create them with a UNIQUE names too.
2) Sophisticated solving - if the first solving will not help you, then you should enter you DB manually, watch and delete statistic with DUPLICATED column names. Also you can find a special script for deleting DUPLICATED column names on Genesys support but I don't advice to use it. It will be better to do it yourself manually.

Hope my advice help you. Good luck!

Regards,

Grum

[jbserna]

Thanks for the contribution

I'll try to make the solutions that you offer.

I mention the result.

Regards