Automatic Job Generator - does one exist??

[jdmiceli]

Hey Peter! (pclarebu):

First of all, welcome aboard! :D On behalf of the DSXchange community, I'd like to welcome you to what I consider the best forum for this product.

Now, to your post. I'm still fairly new to the product. Could you fill in a couple of holes for me please:

1. What version of DataStage are you working with (server, parallel, etc?)
2. Are the OSH schema files from MetaStage or some other utility? Or am I just being dense... that could be too.
3. What propagate utility? Is this a homebrew you have developed or part of one of the products? I only have access to DataStage right now, though we are supposed to have ProfileStage and MetaStage available to us soon, so please excuse my ignorance if this is common info.

It may just be my inexperience with aspects of the toolset, but 'OSH schema files' and 'propagator utility' are unfamiliar terms for me. Just want to make sure I'm not missing something I might be able to use.

Bestest!

[ray.wurlod]

My guess is "propagation utility" means Runtime Column Propagation.

[jdmiceli]

Thanks, Ray. I will dig through the docs and see what I can find.

Bestest!

[jdmiceli]

Hi all,

I'm getting into the guts of the export files and trying to resolve duplication issues.

One thing that might bite me is unique labels with regards to identifiers in the export file. I question whether the following fields in each of the DSRECORD or DSJOB sections of the file are unique within a project, category or only the .dsx file:

Identifier
NextStageID
Partner
InputPins
OutputPins
StageList

I need to know if these tags need to be unique within the entire project, the category, maybe even the entire DataStage installation. It may be as simple as they just need to be unique within the .dsx file (which would be my favorite response because that would be easy to handle).

What I have done thus far to try and figure these things out on my own is as follows:

1. I broke my jobs out into categories according to it's source database, so there are three categories

2. I exported all jobs in each of those categories to .dsx files

3. Then, I started comparing the individual jobs in the order they were created (first job to first job between categories).

Doing this I was able to come up with the following observations and I guess I am primarily looking for a comfort level that I won't be violating any primary key type settings the DataStage tables:

Identifier - this is the identifier of the job/stage within the .dsx file
NextStageID - this is a reference to the next stage identifier in the job

Partner - Seems to point to the last stage in a job unless it is the DSRECORD for the last stage, which points to the first record

InputPins - Uses the StageID as a root with a 'P#' (# being the sequence of the data field in the table being addressed).

OutputPins - Same as InputPins

StageList - appears to list the various stages associated with the job's root Identifier. So, the root generally is 'V0' and stages carry the root plus another ID (StageID?) like 'V0S33'.


I'm leaning toward this all being inter-job, which makes this all an easy issue to address. I would appreciate any input.

Bestest!

[ray.wurlod]

Jump into the folder containing the Documentation Tool. Here you will find SQL scripts for creating a database. Open the doc_tool.mdb file using Microsoft Access (hold down the Shift key to bypass the startup macro, or press F11). From the menu, have Access generate a Relationships diagram. While not totally up to date, it will at least give you some idea of the foreign key relationships.

[kduke]

John

Here are the types of things I was looking for:

Code: Select all

            Begin Case  
               Case SectionName = 'HEADER' and FirstWord = 'Date'
                  NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : DQuote(Today)
               Case Identifier = 'ROOT' and FirstWord = 'Description'
                  NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : '"':@LOGNAME:' ':Today:' created."'
               Case OLEType = 'CJobDefn' and FirstWord = 'Category'
                  NewDsxLine = NewDsxLine[1, EndOfFirstWord]:' "UserDefined\\KimD\\Snowflaked\\':UpperLowerName(TargetTable):'"'
               Case OLEType = 'CTrxOutput' and FirstWord = 'Columns'
                  gosub GenTrxOutputLkp
               Case OLEType = 'CCustomOutput' and SecondWord = '"CCustomProperty"'
                  gosub GenCCustomPropertyLkp
            End Case

What you want to find in the DSX file was the begining of where columns are defined or the SQL statement of a stage. These are the types of things that need replacing.

[kduke]

Here are some more:

Code: Select all

Begin Case  
   Case SectionName = 'HEADER' and FirstWord = 'Date'
      NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : DQuote(Today)
   Case OLEType = 'CTrxOutput' and FirstWord = 'Columns'
      gosub GenTrxOutput
   Case OLEType = 'CCustomOutput' and SecondWord = '"CCustomProperty"'
      gosub GenCCustomProperty
End Case

Code: Select all

Begin Case  
   Case SectionName = 'HEADER' and FirstWord = 'Date'
      NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : DQuote(Today)
   Case Identifier = 'ROOT' and FirstWord = 'Description'
      NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : '"':@LOGNAME:' ':Today:' created."'
   Case OLEType = 'CJobDefn' and FirstWord = 'Category'
      NewDsxLine = NewDsxLine[1, EndOfFirstWord]:' "UserDefined\\KimD\\Snowflaked\\':UpperLowerName(TargetTable):'"'
   Case OLEType = 'CTrxOutput' and FirstWord = 'Columns'
      gosub GenTrxOutput
   Case OLEType = 'CCustomOutput' and SecondWord = '"CCustomProperty"'
      gosub GenCCustomProperty
End Case

Code: Select all

Begin Case  
   Case SectionName = 'HEADER' and FirstWord = 'Date'
      NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : DQuote(Today)
   Case Identifier = 'ROOT' and FirstWord = 'Description'
      NewDsxLine = NewDsxLine[1, BeginOfSecondWord-1] : '"':@LOGNAME:' ':Today:' created."'
   Case OLEType = 'CJobDefn' and FirstWord = 'Category'
      NewDsxLine = NewDsxLine[1, EndOfFirstWord]:' "UserDefined\\KimD\\Snowflaked\\':UpperLowerName(TargetTable):'"'
   Case OLEType = 'CHashedOutput' and FirstWord = 'Columns'
      gosub GenCHashedOutput
   Case OLEType = 'CTrxOutput' and FirstWord = 'Columns' and NameFromOLEType = '"OutMemberDimHlk"'
      gosub GenCHashedOutput
   Case OLEType = 'CTrxOutput' and FirstWord = 'Columns'
      gosub GenTrxOutput
   Case OLEType = 'CCustomInput' and SecondWord = '"CCustomProperty"'
      gosub GenCCustomProperty
   Case OLEType = 'CSeqOutput' and FirstWord = 'Columns'
      gosub GenCSeqOutput
End Case

[kduke]

So I read one line at a time. I keep track of what section in the DSX I am in. I find the first column and start replacing until I reach the last column in that link.

[jdmiceli]

Hi Ray and Kim,

You guys are awesome! :D

I appreciate all your assistance with my little 'labor of love' project. If I get it working, my intention is to make it available to the DSXchange community and to continue to make it grow over time. So, your wisdom (as one of Ray's taglines so aptly states) with the product is invaluable. Not that I'm limiting it to just the two of you; I have had many responses from others as well.

I do feel that I'm working on something that even the Premium posters, such as yourselves, find to be an interesting project at the very least. Hopefully, it will bear fruit that the entire community can use to shave significant amounts of time off of development cycles. At a minimum, it should provide working, fully functioning templates that can be added to to provide more functionality as their individual projects warrant.

Bestest!

[kduke]

The code I have is all related to server jobs. I was going to post it on this site when they have an area to post. I was told only premium members could download from there. Maybe it will be available soon.