generating jobs reports

[kduke]

Do you have this line in your job?

Code: Select all

Deffun JobReport(JobName,OutputDirectory,LogoPath,Style,Language) calling "DSU.JobReport"

Secondly do these exist?

FilePath = /interface/xx/vv/uu/reports/
LogoPath = /interface/mm/yy/rr/yy/reports/logo.jpg
CategoryToDoc = MySequence

CategoryToDoc should be a valid category or sequence or job. The directory /interface/xx/vv/uu/reports/ needs to exist because it will write to it.

At TCL run:

Code: Select all

SELECT DISTINCT CATEGORY FROM DS_JOBS;

[Rajender]

Kim,

First of all many thanks for your patience.

I do have all the routines defend

Deffun JobReport(JobName,OutputDirectory,LogoPath,Style,Language) calling "DSU.JobReport"

The path is manually created by me in UNIX, the exact path as fallows

/interface/test/test_ql/source/reports/KimD/Jobs/20061115

Category is a valid one since it executed the below query successfully and selected 4 jobs under the sequence

SSELECT DS_JOBS UNLIKE "\..." AND WITH CATEGORY LIKE "DQL_GTB..." TO 9
4 record(s) selected to SELECT list #9.

Still the job aborts at...

KgdGenHtmlJobDocs..JobControl (KgdGenHtmlJobDocs): JobReport(Job_Control_GREF_ESD_Load,OutputDirectory,LogoPath,Style,Language)

Attempting to Cleanup after ABORT raised in stage KgdGenHtmlJobDocs..JobControl

However the job ran successfully once I comment the below code line

* Junk = JobReport(JobName, OutputDirectory, LogoPath, Style, Language)

And this was able to generate the main index page, which was not much help since I get only main index html page without any links to the individual jobs

Thanks in advance for your input

Regards
R

[Rajender]

Kim,

Also to be more precise

I have commented the below code which you have written with the path that I have manually created in Unix

*OutputDirectory = FilePath:"KimD":Sep:"Jobs":Sep:ThisDay

OutputDirectory="/interface/test/test_ql/source/reports/KimD/Jobs/20061115"

So that the OutputDirectory is clear now

Also tried with this, but no luck

Junk = JobReport(JobName,"/interface/test/test_ql/source/reports/KimD/Jobs/20061115", "/interface/ test/test_ql/source/reports/logo-dhl.jpg", Style, Language)

I believe there is some problem in calling the routine JobReport

Regards
R

[kduke]

Will it run one job? Make CategoryToDoc the job you want to test and run it with the routine uncommented out.

[Rajender]

No Sir, result is same tried that as well

It aborts at

KgdGenHtmlJobDocs..JobControl (KgdGenHtmlJobDocs): JobReport(dq_comet,OutputDirectory,LogoPath,Style,Language)

Attempting to Cleanup after ABORT raised in stage KgdGenHtmlJobDocs..JobControl

[loubi]

GenHtmlFromSql can take any SQL statement and turn it into html. If any column ends in ".htm" or ".html" then it becomes a link. The DSN for Universe is uvlocal.

Now create a SELECT which reports CATEGORY from DS_JOBS. Post it here. Next add ".htm" to the column.

As far as the getting the list of jobs in that CATEGORY and all their documentation. IT IS BUILT IN. It has always been able to do it. RUN THE JOB KgdGenHtmlJobDocs. Fill out the parameters properly and it will do all the work.

Hi Duke.

I tried genHtmlFromSql job but I was not able to make it work ! I apologize ! You should think that we are stupid but that'll be fine if you could try to help us once again !

To use GenHtmlFromSql, I imported it into my environment. I also copy "Copy2ProjectDir" dir contents to c:\ascential\datastage\projects\Myproject dir but when I try to launch the Job, I had an abort with this error :

Code: Select all

GenHtmlFromSql..JobControl (JobControl): Error: Unable to open Sql2HtmlDriveP

I don' t understand why..... I can't know if I've forgotten one step in importing job and co's or if this is an execution problem with parameters or others for example.

Thanks a lot for your help and your patiency.

Loubi

[kduke]

That is my fault. That code needs to be removed but until then

Code: Select all

CREATE.FILE Sql2HtmlDriveP 19

I never think anyone is stupid especially if they are trying to do something new. You are learning. Give yourself some credit. I would not uncomment the code your are talking about. Leave it there. I would do all the steps in the install notes for EtlStats. Maybe reimport the jobs too. If you downloaded EtlStats awhile back then try again. I will post the code without Sql2HtmlDriveP next week.

Keep trying. It will work.

[loubi]

That is my fault. That code needs to be removed but until then

Code: Select all

CREATE.FILE Sql2HtmlDriveP 19

I never think anyone is stupid especially if they are trying to do something new. You are learning. Give yourself some credit. I would not uncomment the code your are talking about. Leave it there. I would do all the steps in the install notes for EtlStats. Maybe reimport the jobs too. If you downloaded EtlStats awhile back then try again. I will post the code without Sql2HtmlDriveP next week.

Keep trying. It will work.

Thanks again Duke ! :D

That's better that's better !

HTML file with all categories' links is generated successfully ! The first step is now OK !

My second step is to generate each job's HTML doc and link them in its category HTML file.

I know that it's possible to do this using job KgdGenHtmlJobDocs specifying the category name in parameters.
But I would like to make this automatically so I must find a way to make a loop which run KgdGenHtmlJobDocs which each category found with this sql query (used to generate HTML file I spoke above)

Code: Select all

 select Distinct CATEGORY from DS_Jobs order by CATEGORY ASC [ /code]

I tried modifying KgdGenHtmlFromSql job to include code for KgdGenHtmlJobDocs execution for each category found but That's not very easy to modify this Universe Code because I don't understand all lines !  :oops: 

Thanks to help me a little ! In fact I don't know if my strategy (modify KgdGenHtmlFromSql to include line to execute KgdGenHtmlJobDocs  with category parameter found by the statement) is great.

Thanks again to read me and help me !

[kduke]

Awesome job. Keep going until you understand all of these steps.

You need to know a lot about BASIC to pull this off. You need to process multiple SELECT statements and not SQL type SELECTs. In BASIC a SELECT will create a list of IDs. An ID is the same as a key. You use this SELECT within a loop with a READNEXT VAR where VAR is any variable you want. The value of the next ID or key is on this variable. It is used with a READ statement. This type of READ uses dynamic arrays. Dynamic arrays are just like dimensioned arrays in other langauges. To be more precise they are more like collections. If field 3 is the CATEGORY name then this is how you would read it.

Code: Select all

open 'DS_JOBS' to DsJobsFilePointer else stop
SELECT DsJobsFilePointer
loop while readnext id
   read JobsRecord from DsJobsFilePointer, id then
      Category = JobsRecords<3>
      ... do logic here to run KgdGenHtmlJobDocs with each category ...
   end
repeat

You can have 10 SELECTs active at the same time 0 thru 9. You may need to

SELECT DsJobsFilePointer to 3
readnext id from 3

I am on vacation so the syntax may not be perfect. I will correct it later. Basically what I am suggesting is not modify this job. Write a job which runs KgdGenHtmlJobDocs over and over. Feed it one category name at a time. Very simple.

Try that and let me know what happens.

[loubi]

Code: Select all

open 'DS_JOBS' to DsJobsFilePointer else stop
SELECT DsJobsFilePointer
loop while readnext id
   read JobsRecord from DsJobsFilePointer, id then
      Category = JobsRecords<3>
      ... do logic here to run KgdGenHtmlJobDocs with each category ...
   end
repeat

You can have 10 SELECTs active at the same time 0 thru 9. You may need to

SELECT DsJobsFilePointer to 3
readnext id from 3

I am on vacation so the syntax may not be perfect. I will correct it later. Basically what I am suggesting is not modify this job. Write a job which runs KgdGenHtmlJobDocs over and over. Feed it one category name at a time. Very simple.

Try that and let me know what happens.

Ok Duke, thanks again.

I tried applying your code, but I don't understand ths first part of it :

Code: Select all

open 'DS_JOBS' to DsJobsFilePointer else stop
SELECT DsJobsFilePointer

In fact, I have a SGBD, SQL and PL/SQL formation and so I Don't understand how, with your code, you extract categories of each job. I understand that I haven't to use SQL SELECT but universe multiple select but That's difficult to apply these new concepts.

Antoher thing, I realized that I must apply a "distinct" in the query which return list of categories if I don't want that job re-generate HTML page for jobs of a category for each category occurency.

I'm not sure my post is very understandable.

Don't hesitate asking me more informations if you don't understand my problems !

Thanks a lot !

[kduke]

Code: Select all

Cmd = 'SELECT DS_JOBS SAVING UNIQUE CATEGORY TO 3'
execute Cmd capturing output
loop while readnext CatName from 3
   ... run genhtml job here ...
repeat

To run a job in job control then there is a drop down list of jobs. Pick the right and it will add the code needed to run this job.

[DSguru2B]

Great work Kim. I like your patience and step by step explanation. You can really make a poster feel comfortable. Keep it up

[kduke]

Thanks.

[loubi]

Code: Select all

Cmd = 'SELECT DS_JOBS SAVING UNIQUE CATEGORY TO 3'
execute Cmd capturing output
loop while readnext CatName from 3
   ... run genhtml job here ...
repeat

To run a job in job control then there is a drop down list of jobs. Pick the right and it will add the code needed to run this job.

Hi Kim, Yes many thanks again for your help.

So, I tried your code for generation and it works in my environment.

There is only one thing which keep bugging ! This code doesn't work with sub-categories !
In fact, HTML generation of All_index with links to categories HTML files doesn't work with sub-caterogies because code generates links like that :

In all_index.Html I've these links :

Code: Select all

file:///projectDir/__documentation/Jobs/20061204/6fret/W6FRET_index.html

Where 6fret is a category and W6fret is its sub-category.

But in my generated documentation I've these HTML files :

Code: Select all

6fret_Index.htm (for 6fret category)
6fret_W6FRET_Index.html (For W6fret sub-category of 6fret)

So I have an error 404 because HTML files are not generated in sub-directories by sub-categories.

So I must find a way to generate links like that in my index fo categories :

Code: Select all

file:///projectDir/__documentation/Jobs/20061204/6fret_W6FRET_index.html
instead of 
file:///projectDir/__documentation/Jobs/20061204/6fret/W6FRET_index.html

So I tried this :
add a Replace function in SQL query to replace category/subcategory.html by category_subcategory.HTML (to match with files generated by HTML generation code. To make this, I've coded this query in execution of KgdGenHtmlFromSql :

Code: Select all

SELECT DISTINCT REPLACE(CATEGORY, '\', '_')  || '_index.html' FROM DS_JOBS order by 1

I also try with '/' instead of '\' in replace parameters.

But generated All_index.html file is empty, query returns no categories.

Thanks to help me with this last step before VICTORY ! :D

Many many thanks to you Kim.

[kduke]

Your SELECT will not work without a EVAL. I would fix the html after generating it. You need to probably replace '\' with '_'.