generating jobs reports

[nageshmb]

JobReport works in 7.1. You can download it and GenHtml from ADN or my tips page. I think 7.5.1 was the first version to document a job from Designer or dsjob.

Thanks. Will try JobReport.

- Nagesh.

[auvray.muriel]

Hello,

I seek the routine "jobs reports" for DataStage V5.2.
I have it for V7, but I cannot import it on V5.
Can somebody help me?

Thank you in advance

Muriel

[chulett]

There is no version of that routine for any version of DataStage older than 7.x.

[ray.wurlod]

Muriel, this routine is not part of DataStage. You can download it from Kim Duke's tips page.

[Daddy Doma]

Has anyone got any advice for merging the two different methods of creating HTML documentation in DataStage?

I've used Kim Duke's wrapper for JobReport to generate an overall index page and HTML per job. But there is a preference to use the Ascential format of documentation, mostly because of the bitmap that drills through to corresponding stage information.

I was able to hack together a solution by:

1. Generating the HTML files and drill through bitmaps using a DSaveAsBmp.bat that calls dsjob with /R report switch turned on,
2. Import and run Kim's dsx package into DataStage and generate the index page and HTML files,
3. Manually replace the HTML files from step 2 with those from step 1, including renaming the file extension from .htm to .html so that the index page works.

This is not going to be managable to support large scale or regular documentation...any ideas?

[kduke]

I posted your 1. solution on ADN a long time ago. I think it is on my tips page. If you have EtlStats installed then you run a SQL statement to generate the html index. If any column ends in ".htm" or ".html" then it turns it into a link. The job called GenHtmlFromSql does this for you. It will turn any SQL into a html page. It will even email it to you. It stores the results in a directory called Sql2Html below the project. GenHtmlFromSql has a parameter for the filename.

Code: Select all

SELECT 
   EVAL "@ID : '.html'" AS JOB_NAME FMT '35L',
   CATEGORY,
   EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L',
   EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',40,'X') = '2' then 'Y' else 'N'" AS SEQ
FROM 
   DS_JOBS
WHERE
   SEQ = 'Y'
ORDER BY
   NAME
;

This SQL is an example of a list of sequences. This is one of the reports included with EtlStats. It is called DsJobSequences2.sql. All of these reports can run by job GenHtmlFromSqlDriver. This job lets pick the report name from a list in parameter. Most have never user a parameter with list. Very useful. This job even fills in a :1 and :2 in SQL. The SQL really has :1,:2,:3 ... in it. This allows me to list the log file by filling in RT_LOG:1. GenHtmlFromSqlDriver's parameter Replacements is the one to feed the :1,:2 values. The parameter RepSeparator is the separator between :1 and :2. It defaults to "~". So if SQL has :1 for job and :2 for link name then you put LoadCustomerDim~InsertCustomer for parameter Replacements. I hope this makes sense.

I use this a way to send reports during ETL to me and users to show row counts or to show that source totals match target totals. These standard reports let you shine as Craig says. If you dream up another report then please let me add it to the list. The same is true on any of these jobs. All of these jobs can be improved. I would appreciate others contributing to these. You may want to rename the job so I do not overwrite the standard version.

[loubi]

hi all,

I tried HTML doc generation tool produced by Kim D. and that's very useful.
However, I would like to customize it, in particular, I would like to have an index.html by category.

So I Would like to know how can I automatically retrieve list of categories to schedule doc generation by category. It's my first question.

Once this generation by category done, I would like to generate the all_index.Html which only reference category index.html

Thanks to help me Kim or others.

ps: Kim, I can't access to routine code for HTML generation. Is this Normal ? that's disturbing to customize HTML generation so I would like to know if That's your choice Kim, to keep your code protected.

To be continued.

[kduke]

JobReport was not written by me. It was written by an IBM employee. They protected it by exporting without source. There one other routine in EtlStats which is protected.

There is enough code to do what you want. If you are using GenHtml then you can feed it one category at a time. Instead of using "all" for a job name then use a category name. By looking at the job named KgdGenHtmlJobDocs then you could automate this.

[kduke]

If you are using EtlStats then it is a little different. You can give the job GenHtmlFromSql a SQL statement and it will turn it into an html document.

SELECT CATEGORY || '.html' AS CATEGORY FROM DS_JOBS;

Any column which ends in htm or html will create a link to another html web page. So just save each category list as its own name and with html concatenated to it then you are done. GenHtmlFromSql has a parameter to name the html file.

Be creative all the pieces are there. I hate writing documentation so DwNav, PNAV and EtlStats all write their own documentation.

What was that saying, Craig?

[loubi]

JobReport was not written by me. It was written by an IBM employee. They protected it by exporting without source. There one other routine in EtlStats which is protected.

There is enough code to do what you want. If you are using GenHtml then you can feed it one category at a time. Instead of using "all" for a job name then use a category name. By looking at the job named KgdGenHtmlJobDocs then you could automate this.

Yes Kim thanks,

I looked at KgdGenHtmlJobDocs code and I already made some modification to customize it form my company but I still don't know how get list of categories automatically.

I know that I can generate doc for category by specifying it in parameters however, that will be static and if a new category is added, I'll have to modify sequence which launch KgdGenHtmlJobDocs to add the generation for this new category.
That's why I would like to know if datastage has a function to list existing categories to automatize generation by category.

For the all_index referencing categories indexes, I think I'm going to make it by myself but It will be static too and that's disturbing.

thanks a lot for your reply.

[loubi]

If you are using EtlStats then it is a little different. You can give the job GenHtmlFromSql a SQL statement and it will turn it into an html document.

SELECT CATEGORY || '.html' AS CATEGORY FROM DS_JOBS;

Any column which ends in htm or html will create a link to another html web page. So just save each category list as its own name and with html concatenated to it then you are done. GenHtmlFromSql has a parameter to name the html file.

Be creative all the pieces are there. I hate writing documentation so DwNav, PNAV and EtlStats all write their own documentation.

What was that saying, Craig?

I unfortunatly can't use ETLStats because of a decision of our computer department so I must find a solution only using KgdGenHtmlJobDocs.

Thanks for this solution but I can't use it.

[ray.wurlod]

Of course you can. Re-type the code as if it were your own work, but include an acknowedgment in the routine code that it is based on ideas obtained from here and in particular from Kim Duke (www.duke-consulting.com). That way it's not third-party, and highlights the foolishness of that policy.

[kduke]

Secondly, please run job KgdGenHtmlJobDocs and where it asks for a CategoryToDoc put in the job category name instead of all.

[loubi]

Of course you can. Re-type the code as if it were your own work, but include an acknowedgment in the routine code that it is based on ideas obtained from here and in particular from Kim Duke (www.duk ...

For information, my Computer department doesn't want to install ETLStats because of installation procedure.

I didn't think about import Jobs and re-type code. that's a good idea. But, to be honnest, I don't understand how to use GenHtmlFromSql to extract categories and then automatize HTML generation by these categories.

Sorry for all theses questions but I'm not very skilled with using Universe Code

Thanks a lot Guys.

[kduke]

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.