Regarding the recent discussion of using indexes as triggers, I use the following to update timestamps of record changes to a master item stats file that we use for controlling updates to a business warehouse (BW). Without modifying hundreds of legacy programs to support proper timestamps in the primary records, I added the following indexed Idescriptors to files I need to track. We are on universe 11.1.1 and this has been working well for over a year.
DICT AR-INVOICE INDEX..MODIFIEDZ 0001 I 0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F0,"") 0004 \ 0005 1L 0006 S 0049 used to create item created, modified, delete in zitemstats file - create index with this and no.nulls keyword EOI 216 bytes In this example, we have a AR-INVOICE header record and a related ARLI line item file. For our purposes to keep in sync with the BW, we want to timestamp the corresponding header record whenever a line item is updated so the ARLI dict item also updates AR-INVOICE header ZITEMSTATS with an "I" indirect update flag. DICT ARLI INDEX..MODIFIEDZ 0001 I 0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "ARLI!":F0,""):SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F1,"I") 0004 \ 0005 1L 0006 S 0049 used to create item created, modified, delete in zitemstats file - create index with this and no.nulls keyword EOI 264 bytes Alternate Key Index Summary for file ARLI File........... ARLI Indices........ 3 (0 A-type, 0 C-type, 0 D-type, 3 I-type, 0 SQL, 0 S-type) Index Updates.. Enabled, No updates pending Index name Type Build Nulls In DICT S/M Just Unique Field num/I-type INDEX..MODIFIEDZ I Not Reqd No Yes S L N SUBR("ZIDESC.ITE MSTATS.UPDATE", "ARLI!":F0,""):S UBR("ZIDESC.ITEM STATS.UPDATE", " AR-INVOICE!":F1, "I") Subroutine ZIDESC.ITEMSTATS.UPDATE: 0001 SUB (RETURN.VALUE, ZITEMSTATS.ID, OPTIONS) 0002 * updates zitemstats with timestamps; create, modified, deleted 0003 * 0004 * Revision history: 0005 * 0006 * tdb.1190 2012-04-22_1602 new 0007 * 0008 * 0009 * input: ZITEMSTATS.ID item id to use for ZITEMSTATS file 0010 * OPTIONS 0011 * I update attb 5 with "I" for indirect reference 0012 * 0013 * output: RETURN.VALUE NULL 0014 * 0015 * common vars: /ZITEMSTATS/ 0016 * 0017 * called by: INDEX..MODIFIEDZ dictionary items 0018 * calls: 0019 * 0020 * notes: 0021 * 0022 * 0023 $OPTIONS TIME.MILLISECOND 0024 * 0025 * @idx.iotype = 0 - Not being used from an index; 0026 * 1 - INSERT (new) record; 0027 * 2 - DELETE record; 0028 * 3 - Derive old index value; 0029 * 4 - Derive new index value; 0030 * 0031 COMMON /ZITEMSTATS/ F.ZITEMSTATS, ZITEMSTATS.FLAG.OPEN, ZITEMSTATS.WHO 0032 * 0033 EQU NUL TO "" 0034 * 0035 RETURN.VALUE = NUL 0036 * 0037 * the following handles initial call and logto commands 0038 * 0039 IF NOT(ZITEMSTATS.FLAG.OPEN) OR (@WHO # ZITEMSTATS.WHO) THEN 0040 OPEN "ZITEMSTATS" TO F.ZITEMSTATS ELSE 0041 RETURN ;* to caller. 0042 END 0043 * 0044 ZITEMSTATS.FLAG.OPEN = 1 0045 ZITEMSTATS.WHO = @WHO 0046 END 0047 * 0048 LOG.TYPE = NUL 0049 * 0050 BEGIN CASE 0051 CASE @IDX.IOTYPE = 0 0052 * 0053 CASE @IDX.IOTYPE = 1 0054 LOG.TYPE = "C" ;* new - created - easier to see than 'N' new 0055 * 0056 CASE @IDX.IOTYPE = 2 0057 LOG.TYPE = "D" ;* delete 0058 * 0059 CASE @IDX.IOTYPE = 3 0060 * 0061 CASE @IDX.IOTYPE = 4 0062 LOG.TYPE = "M" ;* modified 0063 * 0064 END CASE 0065 * 0066 IF LOG.TYPE # NUL THEN 0067 * 0068 DATE.NOW = DATE() 0069 TIME.NOW = TIME() 0070 * 0071 * handle instance of midnight between date/time functions 0072 * 0073 IF DATE.NOW # DATE() AND TIME() > TIME.NOW THEN DATE.NOW = DATE() 0074 * 0075 TIMESTAMP = DATE.NOW * 86400 + TIME.NOW 0076 * 0077 READU ZITEMSTATS$ FROM F.ZITEMSTATS, ZITEMSTATS.ID ELSE ZITEMSTATS$ = NUL 0078 * 0079 INDIRECT.FLAG = COUNT(OPTIONS, "I") 0080 * 0081 IF INDIRECT.FLAG THEN 0082 ZITEMSTATS$<1> = "M" ;* modified 0083 END ELSE 0084 ZITEMSTATS$<1> = LOG.TYPE 0085 END 0086 * 0087 * created 0088 * 0089 IF INDIRECT.FLAG ELSE 0090 IF LOG.TYPE = "C" THEN 0091 ZITEMSTATS$<2> = TIMESTAMP 0092 END 0093 END 0094 * 0095 * modified 0096 * 0097 ZITEMSTATS$<3> = TIMESTAMP 0098 * 0099 * deleted 0100 * 0101 IF INDIRECT.FLAG ELSE 0102 IF LOG.TYPE = "D" THEN 0103 ZITEMSTATS$<4> = TIMESTAMP 0104 END ELSE 0105 ZITEMSTATS$<4> = NUL 0106 END 0107 END 0108 * 0109 IF INDIRECT.FLAG THEN 0110 ZITEMSTATS$<5> = "I" ;* indirect update flag 0111 END ELSE 0112 ZITEMSTATS$<5> = EREPLACE(ZITEMSTATS$<5>, "I", "") ;* remove indirect update flag 0113 END 0114 * 0115 WRITE ZITEMSTATS$ ON F.ZITEMSTATS, ZITEMSTATS.ID 0116 * 0117 END 0118 * 0119 RETURN ;* to calling program 0120 * 0121 * The type attribute 1 just records the last kind of update; C/M/D. DICT ZITEMSTATS: Field................................ Type & Column............................. Field................................................................. Conversion.. Output Depth &............. Name................................. Field. Heading............................ Definition............................................................ Code........ Format Assoc............... Number F1 D 1 TYPE........... 1L S F2 D 2 CREATED STAMP.. 10R S F3 D 3 MODIFIED STAMP. 10R S F4 D 4 DELETED STAMP.. 10R S F5 D 5 FLAGS.......... 10L S @ID I ZITEMSTATS F0 25L S TYPE I Type F1 1L S SAMPLE ZITEMSTATS DATA: ZITEMSTATS............... TYPE........... CREATED STAMP.. MODIFIED STAMP. DELETED STAMP.. FLAGS.......... AR-INVOICE!93088 M 1400794643 1400830180 1400830064 I ARLI!59516*1 D 1400794745 1400830040 1400830040 ARLI!104022*63 D 1400794736 1400830209 1400830209 ARLI!100377*7 D 1400794731 1400830203 1400830203 AR-INVOICE!330209 M 1400794672 1437737863 ARLI!109409*3 D 1400794718 1400830222 1400830222 AR-INVOICE!233438 M 1400794659 1400830391 1400830139 I ARLI!208438*2 D 1400794788 1400830361 1400830361 ARLI!178439*1 D 1400794761 1400830317 1400830317 ARLI!367465*3 C 1400794916 1400794916 ARLI!307467*1 C 1400794857 1400794857 AR-INVOICE!136667 M 1400794645 1400830263 1400830090 I ARLI!91773*1 D 1400794868 1400830177 1400830177 ARLI!261662*7 D 1400794958 1400830423 1400830423 DICT AR-INVOICE FIELDS (to pull in date/time data from zitemstats): Field................................ Type & Column............................. Field................................................................. Conversion.. Output Depth &............. Name................................. Field. Heading............................ Definition............................................................ Code........ Format Assoc............... Number ZITEMSTATS..CREATED.DATE I Itemstats INT(TRANS("ZITEMSTATS",@FILENAME:"!":F0,F2,"X")/"86400") D4- 10R S Created Date ZITEMSTATS..CREATED.TIME I Itemstats MOD(TRANS("ZITEMSTATS",@FILENAME:"!":F0,F2,"X"),"86400") MTS 8R S Created Time ZITEMSTATS..MODIFIED.DATE I Itemstats INT(TRANS("ZITEMSTATS",@FILENAME:"!":F0,F3,"X")/"86400") D4- 10R S Modified Date ZITEMSTATS..MODIFIED.STAMP I Itemstats TRANS("ZITEMSTATS",@FILENAME:"!":F0,F3,"X") 10R S Modified Stamp ZITEMSTATS..MODIFIED.TIME I Itemstats MOD(TRANS("ZITEMSTATS",@FILENAME:"!":F0,F3,"X"),"86400") MTS 8R S Modified Time I think I'm using the ZITEMSTATS..MODIFIED.STAMP field for comparison to a 'last update stamp' control file in order to determine changed record updates to the BW. The credit for this idea and derivative source code goes to an article by Clifton Oliver in Spectrum Magazine Sep/Oct 2011 that documented the @IDX.IOTYPE variable for indexes. See page 25: http://webcache.googleusercontent.com/search?q=cache:IK9MKCeUrIcJ:www.intl-spectrum.com/mag-SEPOCT.2011.pdf+&cd=1&hl=en&ct=clnk&gl=us&client=opera Clif added, "Don't forget to set your return value to "" in your subroutine and create your index with the NO.NULLS option. That way you won't be indexing anything. No write to the B-tree. Just the call to your subroutine which, of course, writes to your audit file but then effectively is telling the index mechanism "never mind, nothing to index." Thanks again Clif! _____________________________________________________________________________ Scanned by IBM Email Security Management Services powered by MessageLabs. For more information please visit http://www.ers.ibm.com This email is intended only for the use of the party to which it is addressed and may contain information that is privileged, confidential, or protected by law. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of the email or its contents is strictly prohibited. If you have received this message in error, please notify us immediately, by replying to the message and deleting it from your computer. WARNING: Internet communications are not assured to be secure or clear of inaccuracies as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Therefore, we do not accept responsibility for any errors or omissions that are present in this email, or any attachment, that have arisen as a result of e-mail transmission. _____________________________________________________________________________ _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users