September 24, 2015, 08:59 AM
Sharon R.Yes, I have done this, but you will probably need to use a data migrator stored procedure to make the determination for the value for the global parameter. The newer D.M. has the SET VARIABLES option now, and I haven't explored it that much yet as we've only just converted from the old 7.6 Data Migrator - but it looks like it can query only system/statistical variables - but you might check out the documentation on it to see if it can do more than that. If not, and what I have used successfully, is to write a D.M. stored procedure to read a given file that holds the resultant condition to be checked. Then, depending on the value returned, it sets a global (&&) variable which can be queried in the flow connecting line.
For example - below is a stored proc that reads a control table we created. This file has records for each table that we load during an ETL process. It indicates whether the given table was loaded successfully or not. The stored proc simply reads it to count how many were successfully loaded and how many failed based on the particular table grouping that was passed into it; the proc uses that information to determine the value to which to set the global variable &&LOAD_RESULT.
In your process flow, you have the stored proc 'box' that represents the below code and out of it can come your flow paths. I have three for this one to send it down the desired path, which could include just stopping after the condition has been met (or stopping after sending appropriate alerts, etc.):
First flow path - custom condition checked:
(&FOCERRNUM EQ 0 AND &&LOAD_RESULT EQ 'OK')
Second flow path - custom condition checked:
(&FOCERRNUM EQ 0 AND &&LOAD_RESULT EQ 'NOTOK')
Third flow path - failed condition checked:
(&FOCERRNUM NE 0)
Here is a sample D.M. stored procedure (If you are not familiar with D.M. stored procs, they look a lot like a WF .fex and it is fact a .fex type of program):
=======================================================================================
-**************************************************************************************
-* PROC_TRODS_PROCESS_DETERMINE_IF_ALL_LOADED
-*
-* This procedure will read the TRODS_CTL_TABLE_LOAD_CONTROL file and determine if
-* all the tables of the designated (via input parm) PROCESS_TYPE and SOURCE_SYSTEM
-* were loaded successfully or not. If all loaded successfully
-* it will set the global variable "&&LOAD_RESULT" to "OK"; if not it will set it to "NOTOK"
-*
-*
-**************************************************************************************
-TYPE *******************************************************************************
-TYPE *******************************************************************************
-***********Variable Initialization***********
-SET &THISPROCNAME='PROC_TRODS_DETERMINE_IF_ALL_LOADED';
-SET &&NBR_LOADED_OK = 0;
-SET &&NBR_NOT_LOADED_OK = 0;
-SET &&LOAD_RESULT = 'UNKNOWN';
-SET &&DBCONNECTIONTGT='WFRARG_RW';
-SET &&DBENGINETGT='SQLMSS';
-TYPE "&THISPROCNAME" started
-*********************************************************************
-* OBTAIN THE NUMBER SUCCESSFULLY LOADED
-**********************************************************************
ENGINE SQLMSS SET DEFAULT_CONNECTION WFRARG_RW
-RUN
-SET MESSAGE=OFF;
SQL SQLMSS
SELECT COUNT(*)
FROM TRODS_CTL_TABLE_LOAD_CONTROL
WHERE
SOURCE_SYSTEM = '&SOURCE_SYSTEM' AND
PROCESS_TYPE = '&PROCESS_TYPE' AND
TABLE_STATUS = 'SUCCESSFUL' ;
TABLE ON TABLE SAVE AS SQLOUT00
END
-RUN
-SET MESSAGE=ON;
SET NODATA = ' '
-READ SQLOUT00 &TABCOUNT.A11
-RUN
-TYPE READ FOR SUCCESSFUL STATUS:
-TYPE IORETURN : &IORETURN
-TYPE NBR LOADED OK: &TABCOUNT
-IF &IORETURN NE 0 THEN GOTO :THE_END;
-SET &TABCOUNT=STRIP(11, &TABCOUNT, ' ', 'A11V');
-SET &TABCOUNT=TRUNCATE(&TABCOUNT);
-SET &&NBR_LOADED_OK = &TABCOUNT;
-*********************************************************************
-* OBTAIN THE NUMBER NOT SUCCESSFULLY LOADED
-**********************************************************************
ENGINE SQLMSS SET DEFAULT_CONNECTION WFRARG_RW
-RUN
-SET MESSAGE=OFF;
SQL SQLMSS
SELECT COUNT(*)
FROM TRODS_CTL_TABLE_LOAD_CONTROL
WHERE
SOURCE_SYSTEM = '&SOURCE_SYSTEM' AND
PROCESS_TYPE = '&PROCESS_TYPE' AND
TABLE_STATUS <> 'SUCCESSFUL' ;
TABLE ON TABLE SAVE AS SQLOUT00
END
-RUN
-SET MESSAGE=ON;
SET NODATA = ' '
-READ SQLOUT00 &TABCOUNT.A11
-RUN
-TYPE READ FOR NOT SUCCESSFUL STATUS
-TYPE IORETURN : &IORETURN
-TYPE NBR NOT LOADED OK: &TABCOUNT
-IF &IORETURN NE 0 THEN GOTO :THE_END;
-SET &TABCOUNT=STRIP(11, &TABCOUNT, ' ', 'A11V');
-SET &TABCOUNT=TRUNCATE(&TABCOUNT);
-SET &&NBR_NOT_LOADED_OK = &TABCOUNT;
-:THE_END
-TYPE *******************************************************************************
-SET &&LOAD_RESULT = IF &&NBR_NOT_LOADED_OK GT 0 OR &&NBR_LOADED_OK = 0 THEN 'NOTOK' ELSE 'OK';
-TYPE PROCESS TYPE.................: &PROCESS_TYPE
-TYPE NBR_NOT_LOADED_SUCCESSFULLY..: &&NBR_NOT_LOADED_OK
-TYPE NBR_LOADED_SUCCESSFULLY......: &&NBR_LOADED_OK
-TYPE LOAD_RESULT..................: &&LOAD_RESULT
-TYPE FOCERRNUM....................: &FOCERRNUM
-TYPE "&THISPROCNAME" completed
-TYPE *******************************************************************************
-TYPE *******************************************************************************
-EXIT
=======================================================================================