And the FILE.OPEN Routine (without comments - sorry)...
SUBROUTINE FILE.OPEN(FILE.NAME, FILE.VAR.NAME) ************************************************************************ * * SUMMARY: New file open routine * * DATE INIT REQ# DESCRIPTION ************************************************************************ * ************************************************************************ * ************************************************************************ * MAIN PROGAM: ************************************************************************ * COMMON /FO.COMM/ FILE.VARS(2000), FILE.INFO * FILE.VAR.NAME = "" FOUND.FILE = 0 * CURR.ACCOUNT=OCONV(@WHO,'MCU') IF FILE.INFO<3> # CURR.ACCOUNT THEN MAT FILE.VARS = "" FILE.INFO = "" FILE.INFO<3> = CURR.ACCOUNT END * LOCATE FILE.NAME IN FILE.INFO<1> BY 'AR' SETTING POS THEN FILE.VAR.NAME = FILE.VARS(FILE.INFO<2,POS>) FOUND.FILE = 1 END ELSE * FIELD1 = FIELD(FILE.NAME,",",1) FIELD2 = FIELD(FILE.NAME,",",2) IF FIELD2 = "" THEN FIELD2 = FIELD1 FIELD1 = "" END * OPEN FIELD1,FIELD2 TO FILE.VAR.NAME THEN FOUND.FILE = 1 IF NOT(FILE.INFO) THEN MAT FILE.VARS = "" FILE.INFO = "" NEW.FILE.POS = 1 FILE.INFO<1> = FILE.NAME FILE.INFO<2> = NEW.FILE.POS END ELSE NEW.FILE.POS = DCOUNT(FILE.INFO<1>,@VM)+1 FILE.INFO = INSERT(FILE.INFO,1,POS,0,FILE.NAME) FILE.INFO = INSERT(FILE.INFO,2,POS,0,NEW.FILE.POS) END FILE.VARS(NEW.FILE.POS) = FILE.VAR.NAME END END * RETURN * ************************************************************************ * SUBROUTINES: ************************************************************************ * ************************************************************************ * END OF FILE.OPEN ************************************************************************ 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' ----- Original Message ---- From: John Kent <[email protected]> To: U2 Users List <[email protected]> Sent: Wed, May 26, 2010 4:41:29 PM Subject: Re: [U2] Historical File Tracking Shawn, can u post the other 2 as well thanks jak ----- Original Message ----- From: "Shawn Hayes" <[email protected]> To: <[email protected]> Sent: Thursday, May 27, 2010 7:18 AM Subject: [U2] Historical File Tracking This routine is a trigger routine that will track a historical perspective of a file. It can't be used by copy and pasting it into a routine and running it. There are 2 programs FILE.OPEN and GET.NEXT.SEQ.NUM that you will need or need to write (I can give you those if you want also). FILE.OPEN - facilitates the opening of the files by putting the file name and variable in a named common to allow for cross account opens. GET.NEXT.SEQ.NUM - gets the next sequential number (key) for a file. Once you have this in place, all you need to do is set the file you want to call this routine as a trigger. SUBROUTINE TRIGGER.UPDATE.CHANGE.LOG(TRIGGER.NAME, SCHEMA, TABLE, EVENT, TRIGGER.TIME, NEW.RECORDID, NEW.RECORD, OLD.RECORDID, OLD.RECORD, ASSOCIATION, ASSOCIATION.EVENT, TRIGGER.COUNT, CHAIN.CASCADE, CASCADE) ************************************************************************ * * * SUMMARY: New routine to update change logs for file triggers * * Params : This is the standard parameter list passed in by the trigger. * TRIGGER.NAME * SCHEMA * TABLE * EVENT * TRIGGER.TIME * NEW.RECORDID * NEW.RECORD * OLD.RECORDID * OLD.RECORD * ASSOCIATION * ASSOCIATION.EVENT * TRIGGER.COUNT * CHAIN.CASCADE * CASCADE * DATE INIT REQ# DESCRIPTION ************************************************************************ * ************************************************************************ * * File open routine. CALL *FILE.OPEN("AUDIT.LOG",AUDIT.LOG) CALL *FILE.OPEN("VOC",VOC) CALL *FILE.OPEN("FILECTL",FILECTL) DONE = 0 ************************************************************************ * * Begin Transactional processing * BEGIN TRANSACTION ISOLATION LEVEL 1 * GOSUB INITIALIZE.INFORMATION * * Check to see if this is an Update or Delete. If so, we need to GOSUB LOAD.INIT * The LOAD.INIT will initialize a first record item. * IF EVENT = "UPDATE" OR EVENT = "DELETE" THEN MATCH.VAR = NEW.RECORDID IF OLD.RECORDID # "" THEN MATCH.VAR = OLD.RECORDID MATCH.VAR = TABLE:"_":MATCH.VAR:"_" BSCAN ID.VAR, REC.VAR FROM AUDIT.LOG, MATCH.VAR USING "IDX.FULL.ID" THEN IF (FIELD(ID.VAR,"_",1) = FIELD(MATCH.VAR,"_",1)) AND (FIELD(ID.VAR,"_",2) = FIELD(MATCH.VAR,"_",2)) ELSE GOSUB LOAD.INIT END END ELSE GOSUB LOAD.INIT END END * * If this is an Update or Insert we do comparisons of the OLD and NEW record. IF EVENT = "UPDATE" OR EVENT = "INSERT" THEN DELIM = "" ATT.REC = "" VM.REC = "" SVM.REC = "" NEW.ITEM = "" OLD.ITEM = "" * * Loop through the record that has to largest number of Attributes. AM.NEW.CNT = DCOUNT(NEW.RECORD,@AM) AM.OLD.CNT = DCOUNT(OLD.RECORD,@AM) IF AM.NEW.CNT > AM.OLD.CNT THEN TOTAL.AM.CNT = AM.NEW.CNT END ELSE TOTAL.AM.CNT = AM.OLD.CNT END FOR ATT.CNT = 1 TO TOTAL.AM.CNT * * Check to see of the attribute is the same. Set the flag to true if * they are the same so we can skip the logging of that attribute. IF OLD.RECORD<ATT.CNT> # NEW.RECORD<ATT.CNT> THEN * * There is a FILECTL file out there that tracks information of a file (the key is the filename). * In my FILECTL record, I have identified attribute 9 as the attributes that I track * and a flag in the associated attribute 12 identifying whether we are tracking the * changes in this attribute. LOCATE ATT.CNT IN FILECTL.REC<9> BY "AR" SETTING POS THEN IF FILECTL.REC<12,POS> = "Y" THEN SKIP.ATT = 0 END ELSE SKIP.ATT = 1 END END ELSE SKIP.ATT = 1 END * * If we are not skipping this attribute, loop through the Values in each attribute. * Again, we find the number of Values in the old record and new record and loop through * the larger of the 2. IF NOT(SKIP.ATT) THEN VM.NEW.CNT = DCOUNT(NEW.RECORD<ATT.CNT>,@VM) VM.OLD.CNT = DCOUNT(OLD.RECORD<ATT.CNT>,@VM) IF VM.NEW.CNT > VM.OLD.CNT THEN TOTAL.VM.CNT = VM.NEW.CNT END ELSE TOTAL.VM.CNT = VM.OLD.CNT END FOR VM.CNT = 1 TO TOTAL.VM.CNT IF OLD.RECORD<ATT.CNT,VM.CNT> # NEW.RECORD<ATT.CNT,VM.CNT> THEN * *If there is a change, we loop through the Sub-Values. SVM.NEW.CNT = DCOUNT(NEW.RECORD<ATT.CNT,VM.CNT>,@SVM) SVM.OLD.CNT = DCOUNT(OLD.RECORD<ATT.CNT,VM.CNT>,@SVM) IF SVM.NEW.CNT > SVM.OLD.CNT THEN TOTAL.SVM.CNT = SVM.NEW.CNT END ELSE TOTAL.SVM.CNT = SVM.OLD.CNT END FOR SVM.CNT = 1 TO TOTAL.SVM.CNT IF OLD.RECORD<ATT.CNT,VM.CNT,SVM.CNT> # NEW.RECORD<ATT.CNT,VM.CNT,SVM.CNT> THEN * * Keep track of the Attribute, Value, Sub-Value, and the New Record value. * NOTE: We do not need to track the old value because we have already logged it. ATT.REC := DELIM:ATT.CNT VM.REC := DELIM:VM.CNT SVM.REC := DELIM:SVM.CNT NEW.ITEM := DELIM:NEW.RECORD<ATT.CNT,VM.CNT,SVM.CNT> DELIM = @VM END NEXT SVM.CNT END NEXT VM.CNT END END NEXT ATT.CNT * * Go to the WRITE.UPDATE.LOG if there have been changes. IF ATT.REC # "" THEN GOSUB WRITE.UPDATE.LOG * * If this record is deleted, we do not need any position information since the * record has been deleted. END ELSE ATT.REC = "" VM.REC = "" SVM.REC = "" OLD.ITEM = "" NEW.ITEM = "" GOSUB WRITE.UPDATE.LOG END * * Commit and end the transaction. COMMIT END TRANSACTION RETURN * ************************************************************************ * SUBROUTINES: ************************************************************************ * ******************* WRITE.UPDATE.LOG : * ******************* AUDIT.LOG.REC = "" AUDIT.LOG.REC<1> = SYSTEM(32) AUDIT.LOG.REC<2> = @PATH AUDIT.LOG.REC<3> = @ACCOUNT AUDIT.LOG.REC<4> = @USERNO AUDIT.LOG.REC<5> = @WHO AUDIT.LOG.REC<6> = @DATE AUDIT.LOG.REC<7> = TIME() AUDIT.LOG.REC<8> = @COMMAND AUDIT.LOG.REC<9> = TABLE AUDIT.LOG.REC<10> = NEW.RECORDID AUDIT.LOG.REC<11> = ATT.REC AUDIT.LOG.REC<12> = NEW.ITEM AUDIT.LOG.REC<14> = EVENT AUDIT.LOG.REC<15> = SCHEMA AUDIT.LOG.REC<16> = TRIGGER.NAME AUDIT.LOG.REC<17> = TRIGGER.TIME AUDIT.LOG.REC<18> = OLD.RECORDID AUDIT.LOG.REC<19> = VM.REC AUDIT.LOG.REC<20> = SVM.REC AUDIT.LOG.REC<21> = LOWER(SYSTEM(9001)) * * This is a next sequencial ID tracking routine. CALL *GET.NEXT.SEQ.NUM(AUDIT.LOG,"",NEXT.ID) WRITE AUDIT.LOG.REC TO AUDIT.LOG,NEXT.ID * RETURN ************** LOAD.INIT : ** ************** * * This uses pretty much the same logic as above. This will initialize the first record * of the AUDIT.LOG. This was written so that you could implement at any time. ATT.REC = "" VM.REC = "" SVM.REC = "" NEW.ITEM = "" DELIM = "" TOTAL.AM.CNT = DCOUNT(OLD.RECORD,@AM) FOR ATT.CNT = 1 TO TOTAL.AM.CNT IF OLD.RECORD<ATT.CNT> # "" THEN * LOCATE ATT.CNT IN FILECTL.REC<9> BY "AR" SETTING POS THEN IF FILECTL.REC<12,POS> = "Y" THEN SKIP.ATT = 0 END ELSE SKIP.ATT = 1 END END ELSE SKIP.ATT = 1 END * IF NOT(SKIP.ATT) THEN TOTAL.VM.CNT = DCOUNT(OLD.RECORD<ATT.CNT>,@VM) FOR VM.CNT = 1 TO TOTAL.VM.CNT IF OLD.RECORD<ATT.CNT,VM.CNT> # "" THEN TOTAL.SVM.CNT = DCOUNT(OLD.RECORD<ATT.CNT,VM.CNT>,@SVM) FOR SVM.CNT = 1 TO TOTAL.SVM.CNT IF OLD.RECORD<ATT.CNT,VM.CNT,SVM.CNT> # "" THEN ATT.REC := DELIM:ATT.CNT VM.REC := DELIM:VM.CNT SVM.REC := DELIM:SVM.CNT NEW.ITEM := DELIM:OLD.RECORD<ATT.CNT,VM.CNT,SVM.CNT> DELIM = @VM END NEXT SVM.CNT END NEXT VM.CNT END END NEXT ATT.CNT * HOLD.EVENT = EVENT EVENT = "INIT" IF ATT.REC # "" THEN GOSUB WRITE.UPDATE.LOG EVENT = HOLD.EVENT RETURN ************************* INITIALIZE.INFORMATION: * ************************* * * This is used to ensure we are using the correct file. The FILECTL records * uses a compound key (not my decision) to identify the ACCOUNT*FILENAME. * This GOSUB will make sure we are using the correct FILECTL record. CONTINUE.FLAG = 0 * LOOP UNTIL DONE DO READU VOC.REC FROM VOC, TABLE LOCKED END THEN IF TRIM(VOC.REC<1>)[1,1] = "F" THEN CURR.ACCOUNT=OCONV(@WHO,'MCU') FILECTL.ID = CURR.ACCOUNT:"*":TABLE END ELSE FILECTL.ID = VOC.REC<2>:"*":VOC.REC<3> END * LOOP UNTIL DONE DO READU FILECTL.REC FROM FILECTL,FILECTL.ID LOCKED END THEN DONE = 1 CONTINUE.FLAG = 1 END ELSE DONE =1 CONTINUE.FLAG = 0 END REPEAT END ELSE DONE = 1 CONTINUE.FLAG = 0 END REPEAT * RELEASE VOC,TABLE RELEASE FILECTL,FILECTL.ID * RETURN ************************************************************************ * END OF TRIGGER.UPDATE.CHANGE.LOG ************************************************************************ 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
