Property	Value
Object Type	Server
Short Description	Last Changed: 11-01-2004
Full Description	Job: GetEtlQaSqlStatDriver ------------------------------------------------------------------------------------- Non-technical description: This job will get QA stats for QaCategory. ------------------------------------------------------------------------------------- Business rules: Need to manually load ETL_QA_RESULT. ------------------------------------------------------------------------------------- Rules for restarting this job: Just rerun. ------------------------------------------------------------------------------------- Parameters: 1. TargetDSN 2. TargetUser 3. TargetPwd 4. QaCategory ------------------------------------------------------------------------------------- Sources: tbd ------------------------------------------------------------------------------------- Targets: tbd ------------------------------------------------------------------------------------- Modification History: ------------------------------------------------------------------------------------- UserId Date Version Modification Description ------------------------------------------------------------------------------------- kduke 11-01-2004 1 Created job. -------------------------------------------------------------------------------------
Category	EtlStats
Multiple Instance	True
Job Version Number	40.1.1
Hash File Cache Sharing	False
Job Control	* ------------------------------------------------------------ * GetEtlQaSqlStatDriver(QaCategory) * Decription: This job will get QA stats for QaCategory * Written by: Kim Duke * ------------------------------------------------------------ * $INCLUDE DSINCLUDE JOBCONTROL.H * ------------------------------------------------------------ Ans = "" open 'DS_JOBS' to DsJobs else ErrMsg = "Error: Unable to open DS_JOBS" goto ErrRtn * goto TheEnd end open 'EtlQaSqlHash' to EtlQaSqlHashPtr else ErrMsg = "Error: Unable to open EtlQaSqlHash" goto ErrRtn * goto TheEnd end Cnt = 0 * ------------------------------------------------------------ if downcase(QaCategory) = 'all' then Cmd = 'SELECT EtlQaSqlHash BY RUN_ORDER TO 9' Call DSLogInfo(Cmd, "JobControl") execute Cmd capturing output * Call DSLogInfo(output, "JobControl") loop while readnext QaStatId from 9 gosub GetStats Kim1: repeat end else Cmd = 'SELECT EtlQaSqlHash WITH QA_CATEGORY = "':QaCategory:'" BY RUN_ORDER TO 9' Call DSLogInfo(Cmd, "JobControl") execute Cmd capturing output Call DSLogInfo(output, "JobControl") loop while readnext QaStatId from 9 gosub GetStats Kim2: repeat end goto TheEnd * ------------------------------------------------------------ * ETL_QA_SQL_ID NUMBER, * QA_CATEGORY VARCHAR2(50 BYTE), * QA_STAT_NAME VARCHAR2(30 BYTE), * RUN_ORDER NUMBER, * SOURCE_OR_TARGET VARCHAR2(1 BYTE), * DSN_ID VARCHAR2(30 BYTE), * DSN_USER_ID VARCHAR2(30 BYTE), * DSN_PWD VARCHAR2(30 BYTE), * TABLE_NAME VARCHAR2(255 BYTE), * SQL_TO_RUN VARCHAR2(2000 BYTE) * ------------------------------------------------------------ GetStats: read EtlQaRec from EtlQaSqlHashPtr, QaStatId then ThisQaCategory = EtlQaRec<1> QaStatName = EtlQaRec<2> RunOrder = EtlQaRec<3> SourceOrTarget = EtlQaRec<4> DsnId = EtlQaRec<5> DsnUserId = EtlQaRec<6> DsnPwd = EtlQaRec<7> TableName = EtlQaRec<8> SqlToRun = EtlQaRec<9> if DsnPwd = '' then DsnPwd = DsnUserId end * Kim StatId = ThisQaCategory :"_": QaStatId StatId = QaStatName * ------------------------------------------------------------ Cnt += 1 JobReportName = "GetEtlQaSqlStat.":StatId Call DSLogInfo("Attaching to ":JobReportName, "JobControl") * ------------------------------------------------------------ * Setup GetEtlQaSqlStat, run it, wait for it to finish, and test for success hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL) If NOT(hJob1) Then Call DSLogFatal("Job Attach Failed: ":JobReportName, "JobControl") Abort End LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then Call DSLogInfo(JobReportName, "Reseting job and last run status") ErrCode = DSRunJob(hJob1, DSJ.RUNRESET) ErrCode = DSWaitForJob(hJob1) ErrCode = DSDetachJob(hJob1) hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL) If NOT(hJob1) Then Call DSLogFatal("Job Attach Failed: ":JobReportName, "JobControl") Abort End end ErrCode = DSSetParam(hJob1, "StatDSN", DsnId) ErrCode = DSSetParam(hJob1, "StatUser", DsnUserId) ErrCode = DSSetParam(hJob1, "StatPwd", DsnPwd) ErrCode = DSSetParam(hJob1, "TableName", TableName) ErrCode = DSSetParam(hJob1, "SqlToRun", SqlToRun) ErrCode = DSSetParam(hJob1, "StatId", StatId) ErrCode = DSSetParam(hJob1, "TargetDSN", TargetDSN) ErrCode = DSSetParam(hJob1, "TargetUser", TargetUser) ErrCode = DSSetParam(hJob1, "TargetPwd", TargetPwd) ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(hJob1) Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return * Kim Call DSLogFatal("Job Failed: ":JobReportName, "JobControl") Call DSLogInfo("Job Failed: ":JobReportName, "JobControl") End end else ErrMsg = 'Error: ':QaStatId:' not a valid QaStatId.' goto ErrRtn end * ------------------------------------------------------------ SkipRun: return * ------------------------------------------------------------ if downcase(QaCategory) = 'all' then goto Kim1 end else goto Kim2 end * ------------------------------------------------------------ ErrRtn: Call DSLogInfo(ErrMsg , "JobControl") * Call DSLogFatal(ErrMsg, "JobControl") abort * return * ------------------------------------------------------------ TheEnd: Call DSLogInfo('Processed ':Cnt:' records.', "JobControl")

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

- Source Stages (0)  + Source Stages (0)

- Processing Stages (0)  + Processing Stages (0)

- Target Stages (0)  + Target Stages (0)