/*--------------------------------------------------------------------------
       File        : bprepl/repl_tmpl/tmpl_mreplproc_oracle.p
       Purpose     : Oracle Bulk load template 
	                 Uses "--Bulk-insert Start" and "--Bulk-insert End"
					 to widen the Oracle transaction which increases bulk-load
					 performance.
       Copyright(c): Progress Software Inc.

       Author(s)   : Terry Mays, BravePoint
       Created     : 05/31/2017
       Patches:    : TP#72372 - added support for widening the Oracle transaction.
--------------------------------------------------------------------------*/
{predefs.i}

DEFINE NEW SHARED VARIABLE strLogDir   AS CHARACTER NO-UNDO.
DEFINE NEW SHARED VARIABLE logVerify   AS LOGICAL   NO-UNDO.
DEFINE NEW SHARED VARIABLE strRLogFile AS CHARACTER NO-UNDO.
DEFINE NEW SHARED VARIABLE strVLogFile AS CHARACTER NO-UNDO.
DEFINE NEW SHARED VARIABLE strRLogDate AS CHARACTER NO-UNDO.
DEFINE NEW SHARED VARIABLE strVLogDate AS CHARACTER NO-UNDO.
DEFINE NEW SHARED VARIABLE vThread#    AS INTEGER   NO-UNDO.

DEFINE VARIABLE hdlReplLib    AS HANDLE    NO-UNDO.
DEFINE VARIABLE strKillLib    AS CHARACTER NO-UNDO.
DEFINE VARIABLE hdlReplProc   AS HANDLE    NO-UNDO.
DEFINE VARIABLE hdlVerProc    AS HANDLE    NO-UNDO.
DEFINE VARIABLE intRecordNum  AS INTEGER   NO-UNDO.
DEFINE VARIABLE intErrorNum   AS INTEGER   NO-UNDO.
DEFINE VARIABLE intLocked     AS INTEGER   NO-UNDO.
DEFINE VARIABLE strRecordID   AS CHARACTER NO-UNDO.
DEFINE VARIABLE strStatus     AS CHARACTER NO-UNDO.
DEFINE VARIABLE strErrMsg     AS CHARACTER NO-UNDO.
DEFINE VARIABLE intI          AS INTEGER   NO-UNDO.
DEFINE VARIABLE istrEvent     AS CHARACTER INITIAL "C" NO-UNDO.

DEFINE BUFFER   bfrSrc<<SRC_TABLE25>> FOR <<SOURCE_DB>>.<<SRC_TABLE>>.
DEFINE BUFFER   bfrTgt<<TGT_TABLE25>> FOR <<TARGET_DB>>.<<TGT_TABLE>>. 

DEFINE VARIABLE intTransactionMax AS INTEGER   NO-UNDO.  /*TP#72372*/
DEFINE VARIABLE intTransactionCnt AS INTEGER   NO-UNDO.  /*TP#72372*/
DEFINE VARIABLE hdlOracle         AS INTEGER   NO-UNDO.  /*TP#72372*/
DEFINE VARIABLE intTransBlkCnt    AS INTEGER   NO-UNDO.  /*TP#72372*/

DEFINE NEW SHARED STREAM stmReplLog.
DEFINE NEW SHARED STREAM stmVerLog.

DEFINE STREAM   stmOutLog.
DEFINE STREAM   stmTableLog.
DEFINE STREAM   stmErrorLog.

{{&CODE_DIR}ReplLib.i IN SUPER}

/* Ping the Pro2SQL Procedure Library */
PUBLISH "ReplLibPing" (OUTPUT hdlReplLib).
IF NOT VALID-HANDLE(hdlReplLib) THEN
DO:
  /* Start the Library */
  RUN {&CODE_DIR}ReplLib.p PERSISTENT SET hdlReplLib NO-ERROR.
  IF ERROR-STATUS:ERROR OR
    NOT VALID-HANDLE(hdlReplLib) THEN
  DO:
    MESSAGE "Replication Library Procedure failed to load.".
    IF ERROR-STATUS:ERROR THEN
    DO intI = 1 TO ERROR-STATUS:NUM-MESSAGES:
      MESSAGE ERROR-STATUS:GET-MESSAGE(intI).
    END.
    RETURN.
  END.
  ASSIGN strKillLib = RETURN-VALUE.
  SESSION:ADD-SUPER-PROCEDURE(hdlReplLib).
END.


OUTPUT STREAM stmOutLog TO VALUE("<<LOG_DIR>>/<<DB_LOG_FILE>>") APPEND UNBUFFERED.
OUTPUT STREAM stmTableLog TO VALUE("<<LOG_DIR>>/<<TABLE_LOG_FILE>>") UNBUFFERED.
OUTPUT STREAM stmErrorLog TO VALUE("<<LOG_DIR>>/<<ERROR_LOG_FILE>>") UNBUFFERED.

DO FOR ReplProperties:  /*TP#72372*/
   /* Grab up the Transcation count to determine how many rows to send per transaction.  */
   FIND FIRST ReplProperties WHERE ReplProperties.PropertyName = "Oracle_Bulk_Transaction_Count" NO-LOCK NO-ERROR.
   intTransactionMax = IF AVAILABLE ReplProperties THEN INT(ReplProperties.PropertyValue) ELSE 1000 NO-ERROR.
   IF ERROR-STATUS:ERROR THEN intTransactionMax = 1000.  /* default value */
END. /*DO FOR ReplProperties TP#72372 */  

PUT STREAM stmOutLog UNFORMATTED
  "Began Mass Replication of <<SOURCE_DB>>.<<SRC_TABLE>> on "
  STRING(TODAY,"99/99/99") " at " STRING(TIME,"HH:MM:SS") SKIP.
PUT STREAM stmTableLog UNFORMATTED
  "Began Mass Replication on " STRING(TODAY,"99/99/99") " at " STRING(TIME,"HH:MM:SS") SKIP.

/*TP#72372*/  
IF intTransactionMax =  1000 
  THEN PUT STREAM stmTableLog UNFORMATTED "Using default Oracle_Bulk_Transaction_Count of " intTransactionMax SKIP.
  ELSE PUT STREAM stmTableLog UNFORMATTED "Using Oracle_Bulk_Transaction_Count of " intTransactionMax SKIP.

PUT STREAM stmErrorLog UNFORMATTED
  "Began Mass Replication on " STRING(TODAY,"99/99/99") " at " STRING(TIME,"HH:MM:SS") SKIP.

ASSIGN logVerify = FALSE
       intRecordNum = 0
       intErrorNum  = 0.

/*TP#72372 - This REPEAT block controls the transaction width*/
TRANSBLOCK:
REPEAT:
    ASSIGN intTransactionCnt  = 0. 
           intTransBlkCnt = intTransBlkCnt + 1.
		   
	/* TP#72372 - OK..Put Oracle in bulkcopy mode */
    RUN STORED-PROC SEND-SQL-STATEMENT
        hdlOracle = PROC-HANDLE("--Bulk-insert Start").
        
    CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE proc-handle = hdlOracle.   /*TP#72372*/
	/*TP#72372 - moved from FOR EACH bfrSrc*/
	DO WHILE TRUE:   
        IF strRecordID = ? 
             THEN FIND FIRST bfrSrc<<SRC_TABLE>> NO-LOCK NO-ERROR.
             ELSE FIND NEXT  bfrSrc<<SRC_TABLE>> NO-LOCK NO-ERROR.
		
		IF NOT AVAILABLE bfrSrc<<SRC_TABLE>> THEN DO:
           PUT STREAM stmTableLog UNFORMATTED "Find loop lookup returned not available. All done, leaving find loop." SKIP.
           LEAVE.
        END. 	 
		
        ASSIGN intRecordNum = intRecordNum + 1
	           strRecordID = (IF "<<PRROWID_ADD_DB>>" = "YES"
                                 THEN UPPER(PDBNAME("<<SOURCE_DB>>")) 
			 					 ELSE "") + STRING(ROWID(bfrSrc<<SRC_TABLE25>>)).		
	
		FIND FIRST bfrTgt<<TGT_TABLE25>> 
		     WHERE bfrTgt<<TGT_TABLE25>>.prrowid EQ strRecordID
             EXCLUSIVE-LOCK NO-WAIT NO-ERROR.   
		
		IF NOT AVAILABLE bfrTgt<<TGT_TABLE25>> THEN DO:
 		   IF LOCKED bfrTgt<<TGT_TABLE25>> THEN DO:
		      ASSIGN intLocked = intLocked + 1.
              PUT STREAM stmErrorLog UNFORMATTED
                  "Record " strRecordID ":  Target record is locked." SKIP.
              NEXT.
           END.  /*IF LOCKED bfrTgt<<TGT_TABLE25>>*/
		   CREATE bfrTgt<<TGT_TABLE25>>.
           ASSIGN bfrTgt<<TGT_TABLE25>>.prrowid     = strRecordID
		          bfrTgt<<TGT_TABLE25>>.pro2srcpdb  = "<<SOURCE_DB>>".
				  /*bfrTgt<<TGT_TABLE25>>.pro2created = now.*/
		END.  /*IF NOT AVAILABLE bfrTgt<<TGT_TABLE25>*/   
		/*bfrTgt<<TGT_TABLE25>>.pro2modified = now.*/
        <<ASSIGN>>
        IF ERROR-STATUS:ERROR OR ERROR-STATUS:NUM-MESSAGES GT 0 THEN DO:
		    ASSIGN intErrorNum = intErrorNum + 1.
            PUT STREAM stmErrorLog UNFORMATTED
                 "Record " strRecordID
                 ":  Encountered error when assigning cust columns to SQL." SKIP.
            DO intI = 1 TO ERROR-STATUS:NUM-MESSAGES:
                PUT STREAM stmErrorLog UNFORMATTED
                       " " STRING(intI) " - " ERROR-STATUS:GET-MESSAGE(intI) SKIP.
             END.
 	    END. /*IF ERROR-STATUS:ERROR THEN DO */
        RELEASE bfrTgt<<TGT_TABLE25>>.
        IF intRecordNum MODULO 100000 EQ 0 
		   THEN PUT STREAM stmTableLog UNFORMATTED NOW
                    "Processed " TRIM(STRING(intRecordNum,">>>,>>>,>>>,>>9")) 
					" records so far."                                             SKIP
                    "  " TRIM(STRING(intErrorNum,">>>,>>>,>>>,>>9"))
                    " of these either failed to replicate or failed verification." SKIP
                    "  " TRIM(STRING(intLocked,">>>,>>>,>>>,>>9"))
                    " records were locked and could not be copied."                SKIP.
		
		/*TP#72372*/ 			
        IF intTransactionCnt >= intTransactionMax THEN DO:
           RUN STORED-PROC SEND-SQL-STATEMENT hdlOracle = PROC-HANDLE("--Bulk-insert End").
           CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE proc-handle = hdlOracle.    
           NEXT TRANSBLOCK.
        END.
    END. /* DO WHILE TRUE - TP#72372 */
	RUN STORED-PROC SEND-SQL-STATEMENT hdlOracle = PROC-HANDLE("--Bulk-insert End").  /*TP#72372 */
    CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE proc-handle = hdlOracle.               /*TP#72372 */
    LEAVE.  /* If we get here we're done */                                           /*TP#72372 */
END.   /* REPEAT - TP#72372 */	

PUT STREAM stmErrorLog UNFORMATTED
    "Finished Mass Replication at " STRING(TIME,"HH:MM:SS") "." SKIP
    "A total of " TRIM(STRING(intErrorNum,">>>,>>>,>>>,>>9"))
    " records failed replication." SKIP
    "A total of " TRIM(STRING(intLocked,">>>,>>>,>>>,>>9"))
    " records were locked and could not be copied." SKIP.
PUT STREAM stmTableLog UNFORMATTED
    "Finished Mass Replication at " STRING(TIME,"HH:MM:SS") "." SKIP
    "Processed a total of " TRIM(STRING(intRecordNum,">>>,>>>,>>>,>>9"))
    " records." SKIP.
IF intErrorNum GT 0 OR intLocked GT 0 THEN DO:
  PUT STREAM stmTableLog UNFORMATTED
      "Please review Error Log for " TRIM(STRING(intErrorNum,">>>,>>>,>>>,>>9"))
      " errors and " TRIM(STRING(intLocked,">>>,>>>,>>>,>>9"))
      " locked records." SKIP.
END.

PUT STREAM stmOutLog UNFORMATTED
  "Finished Mass Replication of <<SOURCE_DB>>.<<SRC_TABLE>> on "
  STRING(TODAY,"99/99/99") " at " STRING(TIME,"HH:MM:SS") SKIP.

OUTPUT STREAM stmErrorLog CLOSE.
OUTPUT STREAM stmTableLog CLOSE.
OUTPUT STREAM stmOutLog CLOSE.

RETURN.
