Find DB2 EE stages used in a Project

attu · Post by **attu** » Tue Oct 13, 2009 9:16 am

Hi,

I am trying to find the number of jobs using DB2 EE stages and for this purpose I am using an SQl query ( from one of Ray's post)

Code: Select all

SELECT BREAK ON "'O'" DS_JOBS.NAME JobName, DS_JOBOBJECTS.NAME StageName FMT '48L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBOBJECTS.OLETYPE LIKE '%DSDB2PX%' ORDER BY 1, 2 ;

I am not able to get any results. I also used OLETYPE like PxDB2 and DSDB2PX, but it does not give me any records.

Pls advise.

Thanks

chulett · Post by **chulett** » Tue Oct 13, 2009 9:44 am

Add OLETYPE to the select list and cut the "LIKE" back to just "%DB2%" and see what turns up.

attu · Post by **attu** » Tue Oct 13, 2009 2:06 pm

Craig,

I am still not able to get the results. My requirement is also to find out the number of jobs (Server and Parallel) using odbc stage.
I tried this query for getting the number of jobs (server) using odbc stages and It works fine.

Code: Select all

SELECT BREAK ON "'O'" DS_JOBS.NAME JobName, DS_JOBOBJECTS.NAME StageName FMT '48L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBOBJECTS.OLETYPE LIKE '%CODBCStage%' AND DS_JOBS.JOBTYPEIND LIKE '0' ORDER BY 1, 2 ;

When I am trying to find out the number of jobs using PxOdbc stage in Parallel Jobs, I do not get any results.

Code: Select all

SELECT BREAK ON "'O'" DS_JOBS.NAME JobName, DS_JOBOBJECTS.NAME StageName FMT '48L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBOBJECTS.OLETYPE LIKE '%PxOdbc%' AND DS_JOBS.JOBTYPEIND LIKE '3' ORDER BY 1, 2 ;

Here is an output from the command

Code: Select all

SELECT DISTINCT OLETYPE FROM DS_JOBOBJECTS;

Code: Select all

OLEType.............
 
CSeqInput
CCustomStage
CCustomOutput
CJSActivityInput
CJSJobActivity
CTrxOutput
CStdPin
CAnnotation
CJSActivityOutput
CCustomInput
CTrxInput
CTransformerStage
CJSSequencer
CStdOutput
CHashedOutput
CHashedFileStage
CJSMailActivity
CHashedInput
CContainerView
CODBCOutput
CJSExecCmdActivity
Press any key to continue...
 
$<50>OLEType.............
 
CJSTerminatorActivit
y
CJobDefn
CSeqOutput
CODBCStage
CJSCondition
CSeqFileStage
CJSEndLoopActivity
CJSRoutineActivity
CJSUserVarsActivity
CJSStartLoopActivity
CContainerStage
CContainerDefn
CRecord
 
34 records listed.

chulett · Post by **chulett** » Tue Oct 13, 2009 3:07 pm

Where are you getting these OLETYPE values like "PxOdbc" that you are looking for?

From what I recall, database stages will have an OLETYPE of either "CCustomOutput" or "CCustomInput" depending on it being on the end of an output or input link and you need to check another field (a subrecord? subfield?) for the actual stage type. Someone else will need to supply the specifics, I'm afraid.

attu · Post by **attu** » Tue Oct 13, 2009 3:13 pm

chulett wrote:Where are you getting these OLETYPE values like "PxOdbc" that you are looking for?

I was looking at this post http://dsxchange.com/viewtopic.php?t=11 ... 5584fdb7cb

chulett · Post by **chulett** » Tue Oct 13, 2009 3:22 pm

OK, my information seems to be specific to Server jobs... and your list of types in your project don't seem to include any PX types. Are you sure there are Parallel jobs in the project you queried?

Edited to add: I've just queried one of my projects for a list and it too does not list the PX OLETYPE values noted in the other post. There must be more to it than meets the eye...