ETLStats -issue .Need help to setup etlstat up and running.

efxuser · Post by **efxuser** » Fri Mar 13, 2009 1:06 pm

I am on ds 7.5.1A.I am having issues getting etlstats to work. Thease are the steps I followed.
1.I downloaded ETLStats.
2.Ran the sqlscripts on oracle db.
3.Imported dsx files.
4.edited the email scripts to remove kdukes adrs.
5.compiled all jobs.
6.After compilation I get these errors -Compiling: Source = 'DSU_BP/DSU.KgdArchiveLog', - ^
End of Line unexpected, Was expecting: END, "REPEAT", "UNTIL", "WHILE"
0039 Repeat

7.I tried to ignore this error and run SeqJobStatus.I got fatal error SeqJobStatus..JobControl (fatal error from UtilityAbortToLog): USER INFO: SeqJobStatus Aborted. JOB=> SeqJobStatus STAGE=> JobControl LINK=> -8

Any suggestion/guidance needed.

Thanks
Kripa.K

kduke · Post by **kduke** » Fri Mar 13, 2009 1:35 pm

That is a routine. I do not remember that name. You need to find it and remove the call to it. So your jobs will compile. I will have to do research to find that routine. It may just need compiling. You need to compile all the routines too. Do that then recompile all the jobs. Let us know the error messages. There is a little video on how to install it.

efxuser · Post by **efxuser** » Fri Mar 13, 2009 1:57 pm

Thanks for your quick response. I followed the video steps .
I compiled all the routines.2 routines failed.they are KgdArchiveLog and KgdTestSendMail.
But I am stuck here.

kduke · Post by **kduke** » Fri Mar 13, 2009 10:42 pm

I do not remember archive log. I would not worry about either. Need to find the job that fails because of the archive log and disable the call to that routine.

efxuser · Post by **efxuser** » Wed Mar 18, 2009 12:18 pm

ok i have made some progress but far from making it to work.
When I run Seqjobstatus it finished.
Now when i try to run the sequence Seqprepare etl stats it failed .The job

BuildHashParam in the folder ETL_Job_Params has aborted with this message
DSD.SEQOpen Failed to open DwMaster.ini in directory .
STATUS = -1.

ANy ideas about this ini file? gudance/suggestion appreciated.

Thanks

kduke · Post by **kduke** » Thu Mar 19, 2009 1:28 pm

That should be a file in the zip file. Copy it to the project folder. If it does not exist then put a blank file there.

foryou · Post by **foryou** » Thu Mar 19, 2009 5:03 pm

Kdude,

Is ETLStats compatible to latest version of Info server 8.1?

Thanks

kduke · Post by **kduke** » Thu Mar 19, 2009 9:36 pm

I have no idea. I am on 7.5.1.

efxuser · Post by **efxuser** » Fri Mar 20, 2009 8:32 am

I have progressed further and this is what I got.
LoadEtlJoblink failed with the following error.

LoadEtlJobLink..TransDsJobLinks: |LoadEtlJobLink..ETL_LINK.ETL_LINK: DSD.BCIOpenW call to SQLPrepare failed.
SELECT PROJECT_NAME,JOB_NAME,LINK_NAME,LINK_TYPE,SOURCE_IND,JOB_CATEGORY,AVERAGE_ROW_LENGTH FROM ETL_LINK
SQLSTATE=S0022, DBMS.CODE=904
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00904: "AVERAGE_ROW_LENGTH": invalid identifier|

I am using ds server 7.5.1a. My database is oracle 10.2.The tables are created in oracle.Should I do anything about the ODBC?

When i went back to Etlstatsinstall.txt, it says "You need an ODBC connection to your TargetDSN. It is also called
StatDSN and SqlDSN in the EtlStats jobs. "

Is this error related to above stmt.If so can you please point me to what should I be doing about it?

efxuser · Post by **efxuser** » Fri Mar 20, 2009 8:37 am

interesting....

In my Job I found the sql as INSERT INTO ETL_LINK(PROJECT_NAME, JOB_NAME, LINK_NAME, LINK_TYPE, SOURCE_IND, JOB_CATEGORY, AVERAGE_ROW_LENGTH) VALUES (?,?,?,?,?,?,?);

where as in the oracle etl link table has only these columns
SELECT a.project_name, a.job_name, a.link_name, a.link_type,
a.source_ind, a.job_category
FROM etl_link a

Should I delete the AVERAGE_ROW_LENGTH from the job?

chulett · Post by **chulett** » Fri Mar 20, 2009 9:27 am

That or add it to the table, apparently.

kduke · Post by **kduke** » Fri Mar 20, 2009 10:59 am

Craig is correct. The DDL used to create your tables is out of sync with the job. So just add the column. ETL_LINK is not a critical table but I think it is useful. I also think average row length is useful. This times the number of rows will give you the MB that went down this link.

efxuser · Post by **efxuser** » Fri Mar 20, 2009 1:49 pm

Ok.added the column to the table.
Made more progress but stuck at this point.

I ran the prrequisite for DsJobreportdriver .
ExtractJobStatusSeq - creates ./Staging/JobStatusStg.txt ->worked
b. BuildJobStatusHash - used as lookup in this job ->worked as well.
I tried to run DsJobreportdriver .This ran without error.

.I tried to run dsjobreport DB but I got a error saying <jobname>.xml is not found.

Quesstions which job creates this xml.Assuming it is the driver job?

dsjobcmd paramter for the dsjobreport driver is
/landing01/dataint/Ascential/DataStage/DSEngine/bin/dsjob -report
.Should I add an XML in the end to it like this ?
/landing01/dataint/Ascential/DataStage/DSEngine/bin/dsjob -report XML?

kduke · Post by **kduke** » Fri Mar 20, 2009 5:29 pm

That is correct. You usually only run the Driver job. Feed it the job name or 'all'. If the job is a sequence then it gets row counts for all jobs in the sequence. If you use 'all' then it gets row counts for all jobs in the project. Usually the driver job is the last job in sequence. The sequences included show you how to do this. I think you are done.

There is some reporting included. So you can email row counts at the end of sequence as well. Generates html attachments if setup correctly. The html is crude but looks okay.

efxuser · Post by **efxuser** » Mon Mar 23, 2009 7:29 am

Ok.May be I am not clear with my question.When I run the dbdriver job with a sequence name.what is my expected output.Because when I run the dsJobreportdb, job there is no xml created at all so all my tables JOBHIST,ROWHIST AND PARAMHIST are populated with 0 rows.
My dsjobcmd in dsjobreportdb is "/landing01/dataint/Ascential/DataStage/DSEngine/bin/dsjob -report XML" .

Thanks for all of your guidance and responses.