Thanks, Troy.
I was going to write that after I got to 11.1.
(The upgrade was scheduled for Q3, but I hear rumours it got bumped because salesmen just sold something we can't do - yet. It pays the bills.)

Troy, I want to borrow your code, but I want to include proper attribution in the program header.

Chuck

On 8/27/2013 7:02 PM, Buss, Troy (Contractor, Logitek Systems) wrote:
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!


_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Reply via email to