ETLSTATS Help

[Sunshine2323]

Hi All,

I am trying to generate Job Statistics using Kim's ETLSTATS.

I am not able to understand job named ...dbdriver.

I HAVE THE FOLLOWING QUESTIONS:

1)How is the xml input generated?

2)What does /job/@name mean in the xml input stage?

3)Also i wanted to know the correct syntax for dsjob -report

Can someone help?

Thanks,
Sunshine

[kduke]

dsjob -report Project JobName XML is the command being executed. This creates an XML file like:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<Job Name="BuildAgencyHash"
   Desc=""
   Status="1"
   WaveNo="4"
   StartDateTime="2004-06-09T09:25:56"
   EndDateTime="2004-06-09T09:25:58"
   ElapsedTime="00:00:02"
   ElapsedSecs="2">
   <ParamSet>
      <Param Name="TargetDSN"
         Type="0"
         Desc=""
         Value="msdv"/>
      <Param Name="TargetUser"
         Type="0"
         Desc=""
         Value="adsalesdwh"/>
      <Param Name="TargetPwd"
         Type="1"
         Desc=""
         Value="-16"/>
   </ParamSet>
   <ComponentSet>
      <Stage Name="XfmAgency"
         StageStatus="2"
         StageType="CTransformerStage"
         Desc=""
         StartDateTime="2004-06-09T09:25:56"
         EndDateTime="2004-06-09T09:25:58"
         ElapsedTime="00:00:02"
         ElapsedSecs="2">
         <InputLinks>
            <Link Name="InAgencyDimTlk"
               LinkType="1"
               Desc=""
               Stage="TgtAgencyDimOra"/>
         </InputLinks>
         <OutputLinks>
            <Link Name="OutAgencyHlk"
               LinkType="3"
               Desc=""
               Stage="TgtAgencyKeyHash"/>
         </OutputLinks>
         <InstanceSet>
            <Instance Id=""
               CPU="0.72"
               PID="-14773">
               <Link Name="InAgencyDimTlk"
                  RowCount="2624"/>
               <Link Name="OutAgencyHlk"
                  RowCount="2624"/>
            </Instance>
         </InstanceSet>
      </Stage>
   </ComponentSet>
</Job>

The derivations like /job/@name looks for a job section and the name line below it in the XML file. So it finds:

Code: Select all

<Job Name="BuildAgencyHash"

An returns just BuildAgencyHash which is the job that we ran dsjob -report on. Some sections like Row Counts are what they call repeating. The repeating fields can only be processed one group at a time. That is why parameters are processed separately from row counts. They are in different sections. If you process Job Name with Row Counts and there are not Row Counts then the job fails. Job sequences have no row counts so these need ro be processed separate data streams. I wanted elapsed times for sequences and batches. I wanted parameter history and row history so it will populate separate tables for all 3 of these.

I run reports by job categories so I have a couple more jobs which extract job categories to a similar ETL table which can be joined to these tables to do my reports. I want at some point to associate row counts with tablenames. I will extract tablenames soon and add these jobs.

What I want to know did you get these jobs to work? What problems did you or anyone else have? You need a KimD directory below your project or modify these jobs. I put all this junk like the html documentation in the same directory.

Just in case you wanted to know the link names and row counts are in these lines and can be extracted together.

Code: Select all

               <Link Name="InAgencyDimTlk"
                  RowCount="2624"/>
               <Link Name="OutAgencyHlk"
                  RowCount="2624"/>

I am a XML newbie. I am sure I used the terms incorrectly but who cares. Maybe Ray. I got it to work and it is very stable.

Is anyone else using this stuff? I do not track who downloads this stuff. So I do not get much feedback on if they got it to work or do they use it. Tom downloaded KgdGenHtml and even modified it to add short description to the html. I was impressed.