I have create a flow as following : -:START_PRC SET PANEL=99...

[Martin Yergeau]

Just a thought

Have you tried to JOIN cs_source1 directly to cs_source2 instead of sub-query cs_source1 that become T2 then matched using WHERE clauses to T3

Subquery needs to be ran first

SELECT

T3.BRANCH ,

T3.PAT_NO ,

T3.STANDARD_CODE ,

T3.PAT_SEQ ,

T3.PAY_SEQ ,

T3.TOT_AMT ,

T3.DIS_AMT1 ,

T3.DIS_AMT2 ,

T3.DIS_AMT3 ,

T3.INSU_AMT ,

T3.COST_CENTER_CODE ,

T3.DIS_AMT1_APP ,

T3.DIS_AMT1_PUB ,

T3.DIS_AMT1_ABSORB ,

T3.DIS_AMT2_APP ,

T3.DIS_AMT2_PUB ,

T3.DIS_AMT2_ABSORB ,

T3.DIS_AMT3_APP ,

T3.DIS_AMT3_PUB ,

T3.DIS_AMT3_ABSORB ,

CURRENT_DATE() AS DW_DATE

FROM cs_source1 T1

INNER JOIN cs_source2 T3

ON T2.PAT_NO = T1.PAT_NO

AND T2.PAT_SEQ = T1.PAT_SEQ

AND T2.PAY_SEQ = T1.PAY_SEQ

WHERE (T1.CREATE_DATE >= &&YYMD_S AND T1.CREATE_DATE < &&YYMD_E )

OR (T1.DELETE_DATE >= &&YYMD_S AND T1.DELETE_DATE< &&YYMD_E )

May not make differences, but can try

Do you have index on T1.CREATE_DATE and T1.DELETE_DATE

Do you have index on PAT_NO, PAT_SEQ and PAY_SEQ from both files

[Nox Lee]

I have create a flow as following :

-:START_PRC

SET PANEL=9999

SET MORE=OFF

SET 2PARTNAME=ON

-RUN

-*[Variables to Control Request]

-SET &&CM__TARGET = target_p;

-SET &&CM__AUTHOR = PTHwf_admin;

-SET &&CM__REQUEST = &FOCFEXNAME.EVAL;

-SET &&CM__RETURN = 0;

-SET &&CM__FOCCPU = &FOCCPU.EVAL;

-SET &&KILL_RPC = N;

-DEFAULT &DBMSERROR = 10000000

-DEFAULT &STARTAT = 0

-DEFAULT &STOPAT = 1000000000

-TYPE (ICM18122) Request - &FOCFEXNAME (Owner: PTHwf_admin) submitted.

-GOTO :DEP_0;

-:DEP_MAIN

-TYPE (ICM18742) bi_opnfeed type MS SQL Server ODBC Existing target

SET CASESTAT=EXTENDED

SQL SET UPCASE=OFF; END

LOAD MASTER cs_source1

EX -lines 5 EDAPUT MASTER,cs_source1,A,MEM,

DEFINE BRANCH/A1=K; $

LOAD MASTER cs_source2

EX -lines 3 EDAPUT MASTER,cs_source2,A,MEM,

DEFINE BRANCH/A1=K; $

-RUN

-SET &&CM__RETURN = &FOCERRNUM;

-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;

-TYPE (ICM18429) Issuing PREPARE

SQL PREPARE SQLIN FROM

SELECT

T3.BRANCH ,

T3.PAT_NO ,

T3.STANDARD_CODE ,

T3.PAT_SEQ ,

T3.PAY_SEQ ,

T3.TOT_AMT ,

T3.DIS_AMT1 ,

T3.DIS_AMT2 ,

T3.DIS_AMT3 ,

T3.INSU_AMT ,

T3.COST_CENTER_CODE ,

T3.DIS_AMT1_APP ,

T3.DIS_AMT1_PUB ,

T3.DIS_AMT1_ABSORB ,

T3.DIS_AMT2_APP ,

T3.DIS_AMT2_PUB ,

T3.DIS_AMT2_ABSORB ,

T3.DIS_AMT3_APP ,

T3.DIS_AMT3_PUB ,

T3.DIS_AMT3_ABSORB ,

CURRENT_DATE() AS DW_DATE

FROM

(SELECT

T1.PAT_NO ,

T1.PAT_SEQ ,

T1.PAY_SEQ ,

FROM

cs_source1 T1

WHERE

(T1.CREATE_DATE >= &&YYMD_S AND

T1.CREATE_DATE < &&YYMD_E ) OR (T1.DELETE_DATE >= &&YYMD_S AND

T1.DELETE_DATE< &&YYMD_E )

) T2 ,

cs_source2 T3

WHERE

T2.PAT_NO = T3.PAT_NO AND

T2.PAT_SEQ = T3.PAT_SEQ AND

T2.PAY_SEQ = T3.PAY_SEQ

END

-RUN

-SET &&CM__RETURN = IF &FOCERRNUM EQ 14104 THEN 0 ELSE &FOCERRNUM;

-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;

-IF (&SQLAPT EQ APT) GOTO :SKIPHOLD;

-TYPE (ICM18440) Request will process data via NON-Pass Through (NON-APT)

-TYPE (ICM18451) HOLD file will be created for output file named: SQLIN.

SQL EXECUTE SQLIN;

TABLE ON TABLE HOLD AS

SQLIN

FORMAT DATREC

IF RECORDLIMIT EQ &STOPAT

END

-RUN

-SET &&CM__RETURN = &FOCERRNUM;

-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;

-:SKIPHOLD

ENGINE MSODBC SET BULKLOAD OFF

-TYPE (ICM18743) Starting Load

MODIFY FILE target_p

FIXFORM FROM SQLIN ALIAS PROPAGATE

GOTO MATCHIT1

CASE MATCHIT1

COMPUTE

BRANCH/A1=E01;

PAT_NO/A9V=E02;

PAT_SEQ/A6=E04;

PAY_SEQ/P3=E05;

STANDARD_CODE/A4V=E03;

TOT_AMT/P8 MISSING ON=E06;

DIS_AMT1/P8 MISSING ON=E07;

DIS_AMT2/P8 MISSING ON=E08;

DIS_AMT3/P8 MISSING ON=E09;

INSU_AMT/P8 MISSING ON=E10;

COST_CENTER_CODE/A4V=E11;

DIS_AMT1_APP/P8 MISSING ON=E12;

DIS_AMT1_PUB/P8 MISSING ON=E13;

DIS_AMT1_ABSORB/P8 MISSING ON=E14;

DIS_AMT2_APP/P8 MISSING ON=E15;

DIS_AMT2_PUB/P8 MISSING ON=E16;

DIS_AMT2_ABSORB/P8 MISSING ON=E17;

DIS_AMT3_APP/P8 MISSING ON=E18;

DIS_AMT3_PUB/P8 MISSING ON=E19;

DIS_AMT3_ABSORB/P8 MISSING ON=E20;

DW_DATE/YYMD=E21;

MATCH BRANCH

MATCH PAT_NO

MATCH PAT_SEQ

MATCH PAY_SEQ

MATCH STANDARD_CODE

MATCH COST_CENTER_CODE

ON MATCH UPDATE * SEG TARGET_P

ON NOMATCH INCLUDE

GOTO TOP

ENDCASE

CASE AT START

START &STARTAT

STOP &STOPAT

STOP DBMSERRORS &DBMSERROR

LOG DBMSERR MSG OFF

LOG DUPL MSG OFF

LOG INVALID MSG OFF

LOG NOMATCH MSG OFF

LOG FORMAT MSG OFF

LOG ACCEPT MSG OFF

LOG TRANS MSG OFF

CHECK 1000000

ENDCASE

DATA VIA SQLGET

END

-RUN

-TYPE (ICM18744) Ending Load

-SET &&CM__RETURN = &FOCERRNUM;

-SET &&CM__RETURN = IF (&&CM__RETURN EQ 1416) AND (&DBMSERR LT &DBMSERROR)

 

THEN 0 ELSE &&CM__RETURN;

 

-:ENDJOB

-TYPE (ICM18040) Return Code = &&CM__RETURN

SET CASESTAT=OFF

SQL SET UPCASE=ON; END

SET EMGSRV=OFF

SQL PURGE SQLIN;

END

FI SQLIN CLEAR

SET EMGSRV=ON

SQL MSODBC

COMMIT WORK;

END

-RUN

-TYPE (ICM18076) Request: &FOCFEXNAME - finished processing

-SET &&CM__FOCCPU = &FOCCPU.EVAL - &&CM__FOCCPU;

-TYPE (ICM18007) CPU Time : &&CM__FOCCPU

-[Main Condition]

-GOTO :ENDDEP

-[Main End]

-*[Dependence]

-:DEP_0

-TYPE (ICM18015) DEP_0: procedure get_info_cs started.

-RUN

-IF (&&KILL_RPC EQ Y) GOTO :STOPRPCS;

-SET &RET_CODE = IF &&CM__RETURN EQ 0 THEN &FOCERRNUM ELSE &&CM__RETURN;

-IF (&FOCERRNUM EQ 0) GOTO :DEP_MAIN;

-GOTO :ENDDEP

-:ENDDEP

-EXIT

-:STOPRPCS

-TYPE (ICM18094) User termination via KILL_RPC flag

END

-RUN

SET PANEL=0

SET MORE=ON

SET 2PARTNAME=OFF

-RUN

As above code, I notice on the SQL:

SELECT

T3.BRANCH ,

T3.PAT_NO ,

T3.STANDARD_CODE ,

T3.PAT_SEQ ,

T3.PAY_SEQ ,

T3.TOT_AMT ,

T3.DIS_AMT1 ,

T3.DIS_AMT2 ,

T3.DIS_AMT3 ,

T3.INSU_AMT ,

T3.COST_CENTER_CODE ,

T3.DIS_AMT1_APP ,

T3.DIS_AMT1_PUB ,

T3.DIS_AMT1_ABSORB ,

T3.DIS_AMT2_APP ,

T3.DIS_AMT2_PUB ,

T3.DIS_AMT2_ABSORB ,

T3.DIS_AMT3_APP ,

T3.DIS_AMT3_PUB ,

T3.DIS_AMT3_ABSORB ,

CURRENT_DATE() AS DW_DATE

FROM

(SELECT

T1.PAT_NO ,

T1.PAT_SEQ ,

T1.PAY_SEQ ,

FROM

cs_source1 T1

WHERE

(T1.CREATE_DATE >= &&YYMD_S AND

T1.CREATE_DATE < &&YYMD_E ) OR (T1.DELETE_DATE >= &&YYMD_S AND

T1.DELETE_DATE< &&YYMD_E )

) T2 ,

cs_source2 T3

WHERE

T2.PAT_NO = T3.PAT_NO AND

T2.PAT_SEQ = T3.PAT_SEQ AND

T2.PAY_SEQ = T3.PAY_SEQ

END

will perform cs_source1 and save temp as a hold file on edatemp in Iway Server, them I notice the job try load ALL records from cs_source2 from the source DB, and my cs_source2 table contain over 1 Million of records, which I notice it take long during I/O into Iway Server, Im looking for after filter cs_source1 and keep the temp records on source DB instead hold as temp on Iway Server, so that cs_source2 does not need to load all records to Iway Server to do second part of filter on Iway Server Assume cs_source1 and cs_source2 are in same DB.

[Nox Lee]

Thanks for suggestion, I have tried Join as well, and I think Iway is try to pull all data source down to Iway server then do the Join, which is take long as well.