Number of Transformers

elavenil · Post by **elavenil** » Fri Oct 12, 2012 1:40 am

Understand that no of transformers in the project can be determined by querying UNIVERSE tables. please let me know the below query will provide that.

select count(*) from DS_TRANSFORMS and this command needs to be executed from Administrator client.

Is there any alternative better way of identifying this?

Thanks
Elavenil

ArndW · Post by **ArndW** » Fri Oct 12, 2012 2:04 am

How about in the Designer. Go to "Stage Types" and do a find of where-used to get a list of all jobs that use that stage type.

elavenil · Post by **elavenil** » Fri Oct 12, 2012 2:58 am

Thanks Arnd.

The way that you recommended provides no of jobs on which transformers used. For eg. more than one transformer used in the same job, the job is listed.

What i need is to get no of transformers used in the project.

Is there any best way of getting no of transformers in the project?

Regards
Elavenil

ArndW · Post by **ArndW** » Fri Oct 12, 2012 3:44 am

the "DS_TRANSFORMS" file does not contain the information you want.

I would suggest using the (documented) BASIC API Calls rather than trying to work with the DataStage internal (undocumented) hashed files.

Code: Select all

Select all Projects, for each project:
   Select all Jobs, for each Job:
      Select all stages, for each Stage "CTransformerStage" (assuming only PX transform stages are of interest)
      --> show the Project.Job.Stagename

ray.wurlod · Post by **ray.wurlod** » Fri Oct 12, 2012 5:12 pm

DS_TRANSFORMS contains Transforms (pre-stored expressions), not Transformer stages. Transformer stages are recorded in DS_JOBOBJECTS.
But I agree with Arnd's advice that the API is a smarter way to retrieve the information - we can't guarantee that DS_JOBOBJECTS will be the storage mechanism in future versions.

elavenil · Post by **elavenil** » Sun Oct 14, 2012 10:24 pm

Thanks ArndW and Ray for your recommendation.

Point noted on DS_TRANSFORMS as this does not provide the info needed.

Where is the recommended code to be written & executed?

I have limited knowledge on the BASIC code recommended.

Your help is greatly appreciated.

Thanks & Regards
Elavenil

ray.wurlod · Post by **ray.wurlod** » Mon Oct 15, 2012 2:14 pm

Now is a great time to start learning! You create server routines within DataStage Designer.

Documentation about the API functions is to be found in the DataStage BASIC manual.

Code: Select all

FUNCTION CountTransformerStages(Arg1)
* Return list of jobname|stagename pairs for Transformer stages.
$INCLUDE DSINCLUDE JOBINCLUDE.H
Ans = ""

* Generate sorted list of job names.
Perform 'SSELECT DS_JOBS WITH NAME LIKE "1A0X"'

* Process list of job names one job name at a time.
Loop
While ReadNext JobName
   hJob = DSAttachJob(JobName, DSJ.ERRNONE)
   StageList = DSGetJobInfo(hJob, DSJ.STAGELIST)

* Produce list of stage names and process these one at a time.
   Convert "," To @FM In StageList
   Loop
   Remove StageName From StageList Setting MoreStages
      StageType = DSGetStageInfo(hJob, StageName, DSJ.STAGETYPE)
      If StageType = "Transformer"
      Then
         Ans<-1> = JobName : "|", StageName
      End
   While MoreStages
   Repeat

Repeat

RETURN(Ans)

elavenil · Post by **elavenil** » Tue Oct 16, 2012 9:37 pm

Thanks Ray for your great help.

Let me test this and i will let you know the outcome soon.

Regards
Elavenil

elavenil · Post by **elavenil** » Wed Oct 17, 2012 1:18 am

It is working fine after modifying slight changes.

The same code will need to be enhanced to write the jobs' name into a file.

Thanks Ray for your help.

Regards
Elavenil

elavenil · Post by **elavenil** » Thu Oct 18, 2012 3:25 am

Hi Ray,

The recommended code was modified slightly and transformer stage for the 1st job is written in the file. But it did not go to next job and i think i am missing something.

Could someone help to get transformer stage names for all jobs in the project?

Code is pasted below.

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H
      Ans = ""

* Generate sorted list of job names.
      Perform 'SSELECT DS_JOBS WITH NAME LIKE "1A0X"'

* Process list of job names one job name at a time.

      AllJobsRead = @False

      OpenSeq 'num_of_xfms.txt' to InputFile Else
         Call DSLogInfo("File Open", "Could not Open this file")
      End

      Loop

         ReadNext JobName Else AllJobsRead = @True

      Until AllJobsRead Do


         hJob = DSAttachJob(JobName, DSJ.ERRNONE)
         StageList = DSGetJobInfo(hJob, DSJ.STAGELIST)

         MoreStages = @False
* Produce list of stage names and process these one at a time.
         Convert "," To @FM In StageList
         Loop
            Remove StageName From StageList Setting MoreStages

            StageType = DSGetStageInfo(hJob, StageName, DSJ.STAGETYPE)

            If StageType = "CTransformerStage"
            Then
               Ans = JobName : "|": StageName


               WriteSeq Ans to InputFile Else
                  Call DSLogInfo("File Write", "Error in writing into the file")
               End
            End
         Until MoreStages
         Repeat

      Repeat

      Close InputFile
      Ans = '0'
      RETURN(Ans)

Appreciate expertise help on this.

Regards
Elavenil

elavenil · Post by **elavenil** » Mon Oct 22, 2012 4:24 am

Hi DataStage Gurus,

Your enlighten inputs / clue are highly appreciated.

Thanks
Elavenil

ArndW · Post by **ArndW** » Mon Oct 22, 2012 5:05 am

I'm not sure about the next loop until constructs. Can you add some "CALL DSLogWarning('Inside xxxx loop with value yyyyy','')" type of debugging code to see why you aren't looping?

elavenil · Post by **elavenil** » Mon Oct 22, 2012 7:35 pm

Thanks ArndW for your response.

Tried calling DSLogInfo to see where it is failing. Once the both loops are completed for 1st job, ReadNext does not get the next job name in JobName variable.

Tried using ReadList as well and ReadList loads all jobs in a list but ReadNext did not go to next job name.

Any inputs to resolve this is highly appreciated.

Regards
Elavenil

ArndW · Post by **ArndW** » Tue Oct 23, 2012 8:04 am

Here a bare-bones loop structure:

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H 
   Ans = "" 
* Generate sorted list of job names. 
   Perform 'SSELECT DS_JOBS WITH NAME LIKE "1A0X"' 
* Read list to memory
   READLIST JobNames FROM 0 ELSE JobNames = ''
   CONVERT @VM TO @FM IN JobNames
   REMOVE JobName FROM JobNames SETTING JobNameType
   LOOP UNTIL JobNameType = 0
      hJob = DSAttachJob(JobName, DSJ.ERRNONE) 
      StageList = DSGetJobInfo(hJob, DSJ.STAGELIST) 
      Convert "," To @FM In StageList
      REMOVE StageName FROM StageList SETTING StageNameType
      LOOP UNTIL StageNameType = 0
print JobName:'.':StageName
         REMOVE StageName FROM StageList SETTING StageNameType
      REPEAT
      Dummy = DSDetachJob(hJob)
      REMOVE JobName FROM JobNames SETTING JobNameType
   REPEAT

elavenil · Post by **elavenil** » Tue Oct 23, 2012 8:43 pm

Thanks ArndW for your help.

It is working fine.

I will modify this code to write the job & transformer name into a file.

Once again, thanks a lot.

Regards
Elavenil