DataStage Job Report for FixEtlElapsedTime

Collapse All | Expand All | Show Default

FixEtlElapsedTime
DataStage Job Report
Generated 2005-02-15 08:26:49pm

- Job Design

- Job Information

- Job Properties (6)

Property	Value
Object Type	Server
Full Description	Job: FixEtlElapsedTime ================================================================= Description: This job fixes a bug in dsjob -report used in DSJobReportDb where it does not calculate ElapsedSecs properly when it crosses midnight. ================================================================= Parameters: 1. TargetDSN 2. TargetPwd 3. TargetUser ================================================================= Sources: Same as targets. ================================================================= Targets: 1. ETL_JOB_HIST 2. ETL_ROW_HIST 3. ETL_PARAM_HIST ================================================================= To use in next job: ================================================================= Modification History ================================================================= Developer Date Modification description ======== ========= ============================================== kduke 2005-02-10 created ======== ========= ==============================================
Category	EtlStats
Multiple Instance	False
Job Version Number	40.0.0
Hash File Cache Sharing	False

- Parameters (3)

Parameter Name	Prompt	Default Value	Help Text
TargetDSN	TargetDSN	kduke
TargetUser	TargetUser	etluser
TargetPwd	TargetPwd	LDH@9:VA=93L0O5I<;J

- Stages (6)

- Source Stages (2)

ETL_JOB_HIST_In (oraoci8)

ETL_ROW_HIST_In (oraoci8)

- Processing Stages (2)

XfmStats (transformer)

XfmRows (transformer)

- Target Stages (2)

ETL_JOB_HIST (oraoci8)

ETL_ROW_HIST (oraoci8)

- Source Stages (2)

- ETL_JOB_HIST_In (oraoci8)

- Stage Properties (4)

Property	Value
DATABASE	#TargetDSN#
USERID	#TargetUser#
PASSWORD	HD:@I:V@L93?0GEI<;J<0K1GB
ISOPS	NO

- Output Links (1)

+ JobStats triggers XfmStats

- ETL_ROW_HIST_In (oraoci8)

- Stage Properties (4)

Property	Value
DATABASE	#TargetDSN#
USERID	#TargetUser#
PASSWORD	HD:@I:V@L93?0GEI<;J<0K1GB
ISOPS	NO

- Output Links (1)

+ RowStats triggers XfmRows

+ Custom Properties (13)

Property	Value
TABLE	ETL_ROW_HIST
ISCASESENS	NO
GENSQL	Yes
USERSQL	SELECT ETL_ROW_HIST.PROJECT_NAME,ETL_ROW_HIST.JOB_NAME,ETL_ROW_HIST.LINK_NAME,ETL_ROW_HIST.START_TS,ETL_ROW_HIST.END_TS,ETL_ROW_HIST.ELAPSED_TIME,ETL_ROW_HIST.ELAPSED_SEC,ETL_ROW_HIST.ROW_COUNT,ETL_ROW_HIST.ROWS_PER_SEC,ETL_JOB_HIST.END_TS FROM ETL_ROW_HIST WHERE ELAPSED_SEC = 0
FULLYGENSQL	SELECT ETL_ROW_HIST.PROJECT_NAME, ETL_ROW_HIST.JOB_NAME, ETL_ROW_HIST.LINK_NAME, ETL_ROW_HIST.START_TS, ETL_ROW_HIST.END_TS, ETL_ROW_HIST.ELAPSED_TIME, ETL_ROW_HIST.ELAPSED_SEC, ETL_ROW_HIST.ROW_COUNT, ETL_ROW_HIST.ROWS_PER_SEC, ETL_JOB_HIST.END_TS AS END_TS_2 FROM ETL_ROW_HIST ETL_ROW_HIST, ETL_JOB_HIST ETL_JOB_HIST WHERE ETL_ROW_HIST.PROJECT_NAME = ETL_JOB_HIST.PROJECT_NAME AND ETL_ROW_HIST.JOB_NAME = ETL_ROW_HIST.JOB_NAME AND ETL_ROW_HIST.START_TS = ETL_JOB_HIST.START_TS AND ETL_ROW_HIST.ELAPSED_SEC = 0
Pre42BINDING	Yes
ARRAYSIZE	1
WHERE_CLAUSE	ELAPSED_SEC = 0
TX_ISO_LEVELO	Read Committed
CONTBEFORESQL	YES
CONTAFTERSQL	YES
DISABLEWARNS	Yes
QUERYDEF	JTYPE=0;SCOLS=;;D="";DEF=;X=ETL_ROW_HIST.PROJECT_NAME;T=9;;F=VarChar;G=;Z=;K=50;M=0;N=FALSE;b=ETL_ROW_HIST;A=PROJECT_NAME;S=1;B=;;;D="";DEF=;X=ETL_ROW_HIST.JOB_NAME;T=9;;F=VarChar;G=;Z=;K=255;M=0;N=FALSE;b=ETL_ROW_HIST;A=JOB_NAME;S=1;B=;;;D="";DEF=;X=ETL_ROW_HIST.LINK_NAME;T=9;;F=VarChar;G=;Z=;K=255;M=0;N=FALSE;b=ETL_ROW_HIST;A=LINK_NAME;S=1;B=;;;D="";DEF=;X=ETL_ROW_HIST.START_TS;T=9;;F=VarChar;G=;Z=;K=23;M=0;N=FALSE;b=ETL_ROW_HIST;A=START_TS;S=1;B=;;;D="";DEF=;X=ETL_ROW_HIST.END_TS;T=9;;F=VarChar;G=;Z=;K=23;M=0;N=FALSE;b=ETL_ROW_HIST;A=END_TS;S=0;B=;;;D="";DEF=;X=ETL_ROW_HIST.ELAPSED_TIME;T=9;;F=VarChar;G=;Z=;K=23;M=0;N=FALSE;b=ETL_ROW_HIST;A=ELAPSED_TIME;S=0;B=;;;D="";DEF=;X=ETL_ROW_HIST.ELAPSED_SEC;T=9;;F=Decimal;G=;Z=;K=38;M=0;N=FALSE;b=ETL_ROW_HIST;A=ELAPSED_SEC;S=0;B=;;;D="";DEF=;X=ETL_ROW_HIST.ROW_COUNT;T=9;;F=Decimal;G=;Z=;K=38;M=0;N=FALSE;b=ETL_ROW_HIST;A=ROW_COUNT;S=0;B=;;;D="";DEF=;X=ETL_ROW_HIST.ROWS_PER_SEC;T=9;;F=Decimal;G=;Z=;K=38;M=0;N=FALSE;b=ETL_ROW_HIST;A=ROWS_PER_SEC;S=0;B=;;;D="";DEF=;X=ETL_JOB_HIST.END_TS;T=9;;F=VarChar;G=;Z=;K=23;M=0;N=FALSE;b=ETL_JOB_HIST;A=END_TS_2;S=0;B=END_TS_2;;;WCONC=;;LP=0;L=;X=ETL_ROW_HIST.PROJECT_NAME;T=-1;;R=;X=ETL_JOB_HIST.PROJECT_NAME;T=-1;;B=0;RP=0;C=;X==;T=-1;;;;LP=0;L=;X=ETL_ROW_HIST.JOB_NAME;T=-1;;R=;X=ETL_ROW_HIST.JOB_NAME;T=-1;;B=0;RP=0;C=;X==;T=-1;;;;LP=0;L=;X=ETL_ROW_HIST.START_TS;T=-1;;R=;X=ETL_JOB_HIST.START_TS;T=-1;;B=0;RP=0;C=;X==;T=-1;;;;LP=0;L=;X=ETL_ROW_HIST.ELAPSED_SEC;T=-1;;R=;X=0;T=-1;;B=-1;RP=0;C=;X==;T=-1;;;;HCONC=;;GCOLS=;;OCOLS=;;TABS=;;F=Kim\Etl_20041101\ETL_ROW_HIST;G=ETL_ROW_HIST;N=ETL_ROW_HIST;O=;S=ETL_ROW_HIST;;;F=Kim\Etl_20041101\ETL_JOB_HIST;G=ETL_JOB_HIST;N=ETL_JOB_HIST;O=;S=ETL_JOB_HIST;;;

+ Columns (9)

- Processing Stages (2)

- XfmStats (transformer)

+ Stage Variables (0)

- Input Links (1)

+ JobStats triggered by ETL_JOB_HIST_In

- Output Links (1)

+ ETL_JOB_HIST triggers ETL_JOB_HIST

- XfmRows (transformer)

+ Stage Variables (0)

- Input Links (1)

+ RowStats triggered by ETL_ROW_HIST_In

- Output Links (1)

+ ETL_ROW_HIST triggers ETL_ROW_HIST

- Target Stages (2)

- ETL_JOB_HIST (oraoci8)

- Stage Properties (4)

Property	Value
DATABASE	#TargetDSN#
USERID	#TargetUser#
PASSWORD	HD:@I:V@L93?0GEI<;J<0K1GB
ISOPS	NO

- Input Links (1)

+ ETL_JOB_HIST triggered by XfmStats

+ Custom Properties (19)

+ Columns (7)

- ETL_ROW_HIST (oraoci8)

- Stage Properties (4)

Property	Value
DATABASE	#TargetDSN#
USERID	#TargetUser#
PASSWORD	HD:@I:V@L93?0GEI<;J<0K1GB
ISOPS	NO

- Input Links (1)

+ ETL_ROW_HIST triggered by XfmRows

+ Custom Properties (19)

+ Columns (9)

Ascential and DataStage are trademarks of Ascential Software Corporation or its affiliates and may be registered in the United States or other jurisdictions.
For questions or comments regarding this utility, contact tony.curcio@ascentialsoftware.com.

Column Name	Data Type	Description	Derivation
PROJECT_NAME	VarChar [50]
JOB_NAME	VarChar [255]
START_TS	VarChar [23]
END_TS	VarChar [23]
JOB_STATUS	VarChar [20]
ELAPSED_TIME	VarChar [23]
ELAPSED_SEC	Decimal [38]

Column Name	Data Type	Description	Derivation
PROJECT_NAME	VarChar [50]
JOB_NAME	VarChar [255]
LINK_NAME	VarChar [255]
START_TS	VarChar [23]
END_TS	VarChar [23]
ELAPSED_TIME	VarChar [23]
ELAPSED_SEC	Decimal [38]
ROW_COUNT	Decimal [38]
ROWS_PER_SEC	Decimal [38]

Column Name	Data Type	Description	Derivation
PROJECT_NAME	VarChar [50]
JOB_NAME	VarChar [255]
START_TS	VarChar [23]
END_TS	VarChar [23]
JOB_STATUS	VarChar [20]
ELAPSED_TIME	VarChar [23]
ELAPSED_SEC	Decimal [38]

Column Name	Data Type	Description	Derivation
PROJECT_NAME	VarChar [50]		JobStats.PROJECT_NAME
JOB_NAME	VarChar [255]		JobStats.JOB_NAME
START_TS	VarChar [23]		JobStats.START_TS
END_TS	VarChar [23]		JobStats.END_TS
JOB_STATUS	VarChar [20]		JobStats.JOB_STATUS
ELAPSED_TIME	VarChar [23]		If JobStats.ELAPSED_SEC = 0 Then ElapsedTimeRtn(JobStats.START_TS, JobStats.END_TS) Else JobStats.ELAPSED_TIME
ELAPSED_SEC	Decimal [38]		If JobStats.ELAPSED_SEC = 0 Then ElapsedSecsRtn(JobStats.START_TS, JobStats.END_TS) Else JobStats.ELAPSED_SEC

Column Name	Data Type	Description	Derivation
PROJECT_NAME	VarChar [50]
JOB_NAME	VarChar [255]
LINK_NAME	VarChar [255]
START_TS	VarChar [23]
END_TS	VarChar [23]
ELAPSED_TIME	VarChar [23]
ELAPSED_SEC	Decimal [38]
ROW_COUNT	Decimal [38]
ROWS_PER_SEC	Decimal [38]