btnmw-server-local/mwlog/storeprocedure/merge_dlog_md.db2

/**
 * @ Usage: CALL MWCONFIG.MERGE_DLOG_MD(ADAPTOR_ID, PORT, SERVER, GROUP)
 */

CREATE OR REPLACE PROCEDURE MERGE_DLOG_MD (
    IN type VARCHAR(256),
    IN codex VARCHAR(256),
    IN pcorrelationid VARCHAR(1024),
    IN psource VARCHAR(1024),
    IN pcode VARCHAR(1024),
    IN pbase VARCHAR(1024),
    IN modified_by VARCHAR(64) DEFAULT CURRENT USER,
    IN c1 VARCHAR(64),
    IN p1 VARCHAR(256),
    IN c2 VARCHAR(64),
    IN p2 VARCHAR(256),
    IN c3 VARCHAR(64),
    IN p3 VARCHAR(256),
    IN c4 VARCHAR(64),
    IN p4 VARCHAR(256),
    IN c5 VARCHAR(64),
    IN p5 VARCHAR(256),
    IN c6 VARCHAR(64),
    IN p6 VARCHAR(256),
    IN c7 VARCHAR(64),
    IN p7 VARCHAR(256),
    IN c8 VARCHAR(64),
    IN p8 VARCHAR(256),
    IN c9 VARCHAR(64),
    IN p9 VARCHAR(256),
    IN c10 VARCHAR(64),
    IN p10 VARCHAR(256),
    IN c11 VARCHAR(64),
    IN p11 VARCHAR(256),
    IN c12 VARCHAR(64),
    IN p12 VARCHAR(256),
    IN c13 VARCHAR(64),
    IN p13 VARCHAR(256),
    IN c14 VARCHAR(64),
    IN p14 VARCHAR(256),
    IN c15 VARCHAR(64),
    IN p15 VARCHAR(256),
    IN c16 VARCHAR(64),
    IN p16 VARCHAR(256),
    IN c17 VARCHAR(64),
    IN p17 VARCHAR(256),
    IN c18 VARCHAR(64),
    IN p18 VARCHAR(256),
    IN c19 VARCHAR(64),
    IN p19 VARCHAR(256),
    IN c20 VARCHAR(64),
    IN p20 VARCHAR(256),
    IN c21 VARCHAR(64),
    IN p21 VARCHAR(256),
    IN c22 VARCHAR(64),
    IN p22 VARCHAR(256),
    IN c23 VARCHAR(64),
    IN p23 VARCHAR(256),
    IN c24 VARCHAR(64),
    IN p24 VARCHAR(256),
    IN c25 VARCHAR(64),
    IN p25 VARCHAR(256),
    IN c26 VARCHAR(64),
    IN p26 VARCHAR(256),
    IN c27 VARCHAR(64),
    IN p27 VARCHAR(256),
    IN c28 VARCHAR(64),
    IN p28 VARCHAR(256),
    IN c29 VARCHAR(64),
    IN p29 VARCHAR(256),
    IN c30 VARCHAR(64),
    IN p30 VARCHAR(256),
    IN c31 VARCHAR(64),
    IN p31 VARCHAR(256),
    IN c32 VARCHAR(64),
    IN p32 VARCHAR(256),
    IN c33 VARCHAR(64),
    IN p33 VARCHAR(256),
    IN c34 VARCHAR(64),
    IN p34 VARCHAR(256),
    IN c35 VARCHAR(64),
    IN p35 VARCHAR(256),
    IN c36 VARCHAR(64),
    IN p36 VARCHAR(256),
    IN c37 VARCHAR(64),
    IN p37 VARCHAR(256),
    IN c38 VARCHAR(64),
    IN p38 VARCHAR(256),
    IN c39 VARCHAR(64),
    IN p39 VARCHAR(256),
    IN c40 VARCHAR(64),
    IN p40 VARCHAR(256)
)
BEGIN
	DECLARE SQL_STMT VARCHAR(4000);

	SET SQL_STMT = 'MERGE INTO MWLOG.DLOG_MD AS T ' ||
	'USING (VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ' ||
	'AS S (TYPE, CODEX, PCORRELATIONID, PSOURCE, PCODE, PBASE, MODIFIED_BY, C_1, P_1, C_2, P_2, C_3, P_3, C_4, P_4, C_5, P_5, C_6, P_6, C_7, P_7, C_8, P_8, C_9, P_9, C_10, P_10, C_11, P_11, C_12, P_12, C_13, P_13, C_14, P_14, C_15, P_15, C_16, P_16, C_17, P_17, C_18, P_18, C_19, P_19, C_20, P_20, C_21, P_21, C_22, P_22, C_23, P_23, C_24, P_24, C_25, P_25, C_26, P_26, C_27, P_27, C_28, P_28, C_29, P_29, C_30, P_30, C_31, P_31, C_32, P_32, C_33, P_33, C_34, P_34, C_35, P_35, C_36, P_36, C_37, P_37, C_38, P_38, C_39, P_39, C_40, P_40) ' ||
	'ON (T.TYPE = S.TYPE) ' ||
	'WHEN MATCHED THEN ' ||
	'UPDATE SET T.CODEX = CASE WHEN S.CODEX IS NOT NULL THEN S.CODEX ELSE T.CODEX END, ' ||
	'T.PCORRELATIONID = CASE WHEN S.PCORRELATIONID IS NOT NULL THEN S.PCORRELATIONID ELSE T.PCORRELATIONID END, ' ||
	'T.PSOURCE = CASE WHEN S.PSOURCE IS NOT NULL THEN S.PSOURCE ELSE T.PSOURCE END, ' ||
	'T.PCODE = CASE WHEN S.PCODE IS NOT NULL THEN S.PCODE ELSE T.PCODE END, ' ||
	'T.PBASE = CASE WHEN S.PBASE IS NOT NULL THEN S.PBASE ELSE T.PBASE END, ' ||
	'T.MODIFIED_BY = CASE WHEN S.MODIFIED_BY IS NOT NULL THEN S.MODIFIED_BY ELSE T.MODIFIED_BY END, ' ||
	'T.TIMESTAMP = CURRENT TIMESTAMP, ' ||
	'T.C_1 = CASE WHEN S.C_1 IS NOT NULL THEN S.C_1 ELSE T.C_1 END, ' ||
	'T.P_1 = CASE WHEN S.P_1 IS NOT NULL THEN S.P_1 ELSE T.P_1 END, ' ||
	'T.C_2 = CASE WHEN S.C_2 IS NOT NULL THEN S.C_2 ELSE T.C_2 END, ' ||
	'T.P_2 = CASE WHEN S.P_2 IS NOT NULL THEN S.P_2 ELSE T.P_2 END, ' ||
	'T.C_3 = CASE WHEN S.C_3 IS NOT NULL THEN S.C_3 ELSE T.C_3 END, ' ||
	'T.P_3 = CASE WHEN S.P_3 IS NOT NULL THEN S.P_3 ELSE T.P_3 END, ' ||
	'T.C_4 = CASE WHEN S.C_4 IS NOT NULL THEN S.C_4 ELSE T.C_4 END, ' ||
	'T.P_4 = CASE WHEN S.P_4 IS NOT NULL THEN S.P_4 ELSE T.P_4 END, ' ||
	'T.C_5 = CASE WHEN S.C_5 IS NOT NULL THEN S.C_5 ELSE T.C_5 END, ' ||
	'T.P_5 = CASE WHEN S.P_5 IS NOT NULL THEN S.P_5 ELSE T.P_5 END, ' ||
	'T.C_6 = CASE WHEN S.C_6 IS NOT NULL THEN S.C_6 ELSE T.C_6 END, ' ||
	'T.P_6 = CASE WHEN S.P_6 IS NOT NULL THEN S.P_6 ELSE T.P_6 END, ' ||
	'T.C_7 = CASE WHEN S.C_7 IS NOT NULL THEN S.C_7 ELSE T.C_7 END, ' ||
	'T.P_7 = CASE WHEN S.P_7 IS NOT NULL THEN S.P_7 ELSE T.P_7 END, ' ||
	'T.C_8 = CASE WHEN S.C_8 IS NOT NULL THEN S.C_8 ELSE T.C_8 END, ' ||
	'T.P_8 = CASE WHEN S.P_8 IS NOT NULL THEN S.P_8 ELSE T.P_8 END, ' ||
	'T.C_9 = CASE WHEN S.C_9 IS NOT NULL THEN S.C_9 ELSE T.C_9 END, ' ||
	'T.P_9 = CASE WHEN S.P_9 IS NOT NULL THEN S.P_9 ELSE T.P_9 END, ' ||
	'T.C_10 = CASE WHEN S.C_10 IS NOT NULL THEN S.C_10 ELSE T.C_10 END, ' ||
	'T.P_10 = CASE WHEN S.P_10 IS NOT NULL THEN S.P_10 ELSE T.P_10 END, ' ||
	'T.C_11 = CASE WHEN S.C_11 IS NOT NULL THEN S.C_11 ELSE T.C_11 END, ' ||
	'T.P_11 = CASE WHEN S.P_11 IS NOT NULL THEN S.P_11 ELSE T.P_11 END, ' ||
	'T.C_12 = CASE WHEN S.C_12 IS NOT NULL THEN S.C_12 ELSE T.C_12 END, ' ||
	'T.P_12 = CASE WHEN S.P_12 IS NOT NULL THEN S.P_12 ELSE T.P_12 END, ' ||
	'T.C_13 = CASE WHEN S.C_13 IS NOT NULL THEN S.C_13 ELSE T.C_13 END, ' ||
	'T.P_13 = CASE WHEN S.P_13 IS NOT NULL THEN S.P_13 ELSE T.P_13 END, ' ||
	'T.C_14 = CASE WHEN S.C_14 IS NOT NULL THEN S.C_14 ELSE T.C_14 END, ' ||
	'T.P_14 = CASE WHEN S.P_14 IS NOT NULL THEN S.P_14 ELSE T.P_14 END, ' ||
	'T.C_15 = CASE WHEN S.C_15 IS NOT NULL THEN S.C_15 ELSE T.C_15 END, ' ||
	'T.P_15 = CASE WHEN S.P_15 IS NOT NULL THEN S.P_15 ELSE T.P_15 END, ' ||
	'T.C_16 = CASE WHEN S.C_16 IS NOT NULL THEN S.C_16 ELSE T.C_16 END, ' ||
	'T.P_16 = CASE WHEN S.P_16 IS NOT NULL THEN S.P_16 ELSE T.P_16 END, ' ||
	'T.C_17 = CASE WHEN S.C_17 IS NOT NULL THEN S.C_17 ELSE T.C_17 END, ' ||
	'T.P_17 = CASE WHEN S.P_17 IS NOT NULL THEN S.P_17 ELSE T.P_17 END, ' ||
	'T.C_18 = CASE WHEN S.C_18 IS NOT NULL THEN S.C_18 ELSE T.C_18 END, ' ||
	'T.P_18 = CASE WHEN S.P_18 IS NOT NULL THEN S.P_18 ELSE T.P_18 END, ' ||
	'T.C_19 = CASE WHEN S.C_19 IS NOT NULL THEN S.C_19 ELSE T.C_19 END, ' ||
	'T.P_19 = CASE WHEN S.P_19 IS NOT NULL THEN S.P_19 ELSE T.P_19 END, ' ||
	'T.C_20 = CASE WHEN S.C_20 IS NOT NULL THEN S.C_20 ELSE T.C_20 END, ' ||
	'T.P_20 = CASE WHEN S.P_20 IS NOT NULL THEN S.P_20 ELSE T.P_20 END, ' ||
	'T.C_21 = CASE WHEN S.C_21 IS NOT NULL THEN S.C_21 ELSE T.C_21 END, ' ||
	'T.P_21 = CASE WHEN S.P_21 IS NOT NULL THEN S.P_21 ELSE T.P_21 END, ' ||
	'T.C_22 = CASE WHEN S.C_22 IS NOT NULL THEN S.C_22 ELSE T.C_22 END, ' ||
	'T.P_22 = CASE WHEN S.P_22 IS NOT NULL THEN S.P_22 ELSE T.P_22 END, ' ||
	'T.C_23 = CASE WHEN S.C_23 IS NOT NULL THEN S.C_23 ELSE T.C_23 END, ' ||
	'T.P_23 = CASE WHEN S.P_23 IS NOT NULL THEN S.P_23 ELSE T.P_23 END, ' ||
	'T.C_24 = CASE WHEN S.C_24 IS NOT NULL THEN S.C_24 ELSE T.C_24 END, ' ||
	'T.P_24 = CASE WHEN S.P_24 IS NOT NULL THEN S.P_24 ELSE T.P_24 END, ' ||
	'T.C_25 = CASE WHEN S.C_25 IS NOT NULL THEN S.C_25 ELSE T.C_25 END, ' ||
	'T.P_25 = CASE WHEN S.P_25 IS NOT NULL THEN S.P_25 ELSE T.P_25 END, ' ||
	'T.C_26 = CASE WHEN S.C_26 IS NOT NULL THEN S.C_26 ELSE T.C_26 END, ' ||
	'T.P_26 = CASE WHEN S.P_26 IS NOT NULL THEN S.P_26 ELSE T.P_26 END, ' ||
	'T.C_27 = CASE WHEN S.C_27 IS NOT NULL THEN S.C_27 ELSE T.C_27 END, ' ||
	'T.P_27 = CASE WHEN S.P_27 IS NOT NULL THEN S.P_27 ELSE T.P_27 END, ' ||
	'T.C_28 = CASE WHEN S.C_28 IS NOT NULL THEN S.C_28 ELSE T.C_28 END, ' ||
	'T.P_28 = CASE WHEN S.P_28 IS NOT NULL THEN S.P_28 ELSE T.P_28 END, ' ||
	'T.C_29 = CASE WHEN S.C_29 IS NOT NULL THEN S.C_29 ELSE T.C_29 END, ' ||
	'T.P_29 = CASE WHEN S.P_29 IS NOT NULL THEN S.P_29 ELSE T.P_29 END, ' ||
	'T.C_30 = CASE WHEN S.C_30 IS NOT NULL THEN S.C_30 ELSE T.C_30 END, ' ||
	'T.P_30 = CASE WHEN S.P_30 IS NOT NULL THEN S.P_30 ELSE T.P_30 END, ' ||
	'T.C_31 = CASE WHEN S.C_31 IS NOT NULL THEN S.C_31 ELSE T.C_31 END, ' ||
	'T.P_31 = CASE WHEN S.P_31 IS NOT NULL THEN S.P_31 ELSE T.P_31 END, ' ||
	'T.C_32 = CASE WHEN S.C_32 IS NOT NULL THEN S.C_32 ELSE T.C_32 END, ' ||
	'T.P_32 = CASE WHEN S.P_32 IS NOT NULL THEN S.P_32 ELSE T.P_32 END, ' ||
	'T.C_33 = CASE WHEN S.C_33 IS NOT NULL THEN S.C_33 ELSE T.C_33 END, ' ||
	'T.P_33 = CASE WHEN S.P_33 IS NOT NULL THEN S.P_33 ELSE T.P_33 END, ' ||
	'T.C_34 = CASE WHEN S.C_34 IS NOT NULL THEN S.C_34 ELSE T.C_34 END, ' ||
	'T.P_34 = CASE WHEN S.P_34 IS NOT NULL THEN S.P_34 ELSE T.P_34 END, ' ||
	'T.C_35 = CASE WHEN S.C_35 IS NOT NULL THEN S.C_35 ELSE T.C_35 END, ' ||
	'T.P_35 = CASE WHEN S.P_35 IS NOT NULL THEN S.P_35 ELSE T.P_35 END, ' ||
	'T.C_36 = CASE WHEN S.C_36 IS NOT NULL THEN S.C_36 ELSE T.C_36 END, ' ||
	'T.P_36 = CASE WHEN S.P_36 IS NOT NULL THEN S.P_36 ELSE T.P_36 END, ' ||
	'T.C_37 = CASE WHEN S.C_37 IS NOT NULL THEN S.C_37 ELSE T.C_37 END, ' ||
	'T.P_37 = CASE WHEN S.P_37 IS NOT NULL THEN S.P_37 ELSE T.P_37 END, ' ||
	'T.C_38 = CASE WHEN S.C_38 IS NOT NULL THEN S.C_38 ELSE T.C_38 END, ' ||
	'T.P_38 = CASE WHEN S.P_38 IS NOT NULL THEN S.P_38 ELSE T.P_38 END, ' ||
	'T.C_39 = CASE WHEN S.C_39 IS NOT NULL THEN S.C_39 ELSE T.C_39 END, ' ||
	'T.P_39 = CASE WHEN S.P_39 IS NOT NULL THEN S.P_39 ELSE T.P_39 END, ' ||
	'T.C_40 = CASE WHEN S.C_40 IS NOT NULL THEN S.C_40 ELSE T.C_40 END, ' ||
	'T.P_40 = CASE WHEN S.P_40 IS NOT NULL THEN S.P_40 ELSE T.P_40 END ' ||
	'WHEN NOT MATCHED THEN ' ||
	'INSERT (TYPE, CODEX, PCORRELATIONID, PSOURCE, PCODE, PBASE, MODIFIED_BY, TIMESTAMP, C_1, P_1, C_2, P_2, C_3, P_3, C_4, P_4, C_5, P_5, C_6, P_6, C_7, P_7, C_8, P_8, C_9, P_9, C_10, P_10, C_11, P_11, C_12, P_12, C_13, P_13, C_14, P_14, C_15, P_15, C_16, P_16, C_17, P_17, C_18, P_18, C_19, P_19, C_20, P_20, C_21, P_21, C_22, P_22, C_23, P_23, C_24, P_24, C_25, P_25, C_26, P_26, C_27, P_27, C_28, P_28, C_29, P_29, C_30, P_30, C_31, P_31, C_32, P_32, C_33, P_33, C_34, P_34, C_35, P_35, C_36, P_36, C_37, P_37, C_38, P_38, C_39, P_39, C_40, P_40) ' ||
	'VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT TIMESTAMP, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@