Determine the Stage type in DS_JOBOBJECTS

[yserrano]

Hello all, I have a new question.

I am trying to identify from the Repository for each of my Jobs the total number of stages of these types it uses:

• Link_Partitioner
  Transformer
  Sort
  Aggregator

Although I have been trying (experimenting) with different queries, the objective is to have something like this:

Code: Select all

SELECT DS_JOBS.NAME JobName FMT '40L', COUNT(1) FROM DS_JOBOBJECTS, DS_JOBS WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND (DS_JOBOBJECTS.OLETYPE = 'CTransformerStage' OR DS_JOBOBJECTS.OLETYPE = 'CCustomStage') GROUP BY DS_JOBS.NAME  ;

I am getting an output like this from the above query:

Code: Select all

cat_test_03                                           2|
cat_test_04                                           2|
cat_test_06                                           5|
cat_test_07                                           2|
cat_test_08                                           4|
hist_01                                               8|
hist_02                                               8|
hist_03                                               8|
hist_04                                               7|

I have two problems:
1. My query is counting also the Oracle OCI stage and I would like to know how to prevent that and include only the four stage types listed above.
2. The query is counting stages which are no longer in the job and that's not good for my final objective.

I have been trying to solve these two issues by reviewing the content of the unnamed @RECORD fields in DS_JOBOBJECTS without much success.
The first 15 fields (identified as DS_JOBOBJECTS.'@RECORD<n>') did not provide any helpful information.

Thank you.

[ray.wurlod]

Most stage types are instantiated from the CCustomStage class. You will need to find some other way to identify the particular stage type (perhaps not in the first fifteen fields). Right now I am in an airport lounge without access to that information.

[kduke]

I am not sure but I think field 8 on CCustomStage tells the real stage type on PX jobs. If that does not work then I need to look it up.

[yserrano]

Your were right, I ran a test for field 8 and this is the result:

Code: Select all

LinkName.. Transformer_3	StageType. CTransformerStage	Field8.... 
LinkName.. InterProcess_7	StageType. CCustomStage		Field8.... CInterProcess 
LinkName.. Aggregator_9	StageType. CCustomStage		Field8.... AGGREGATOR 
LinkName.. Oracle_OCI_27	StageType. CCustomStage		Field8.... ORAOCI9 
LinkName.. InterProcess_22	StageType. CCustomStage		Field8.... CInterProcess 
LinkName.. InterProcess_20	StageType. CCustomStage		Field8.... CInterProcess 
LinkName.. InterProcess_24	StageType. CCustomStage		Field8.... CInterProcess 
LinkName.. Sort_13		StageType. CCustomStage		Field8.... sort 
LinkName.. Link_Collector_17	StageType. CCustomStage		Field8.... CCollector 
LinkName.. Link_Partitioner_15	StageType. CCustomStage		Field8.... CPartitioner 
LinkName.. Copy_of_Oracle_OCI_27	StageType. CCustomStage		Field8.... ORAOCI9

With that information I am able to solve the first of my problems. Thank you.

I would like to know your suggestions about the second part of my question:

2. The query is counting stages which are no longer in the job and that's not good for my final objective.
For some jobs I am getting a total of stages higher than those active in the job (even if I made no distinction of stage types and count all of them). Are there in DS_JOBOBJECTS records of "deleted" stages? Is it possible to difference them from the active ones?

[kduke]

You may be counting the links as well. There are 2 links for most stages. Post your SQL and we might be able to help some more.

[yserrano]

I am not sure what it was.
I double checked my query and it was fine. Restarted the server and still the problem persisted.

So, I exported, deleted and imported the job and the problem of the "extra" stages reported dissapeared.

Thank you.