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
