Re: RES: [U2] Universe and triggers
Clif See point 6 on my post grin It's not enough on a 24x7 site to just have a wrapper if the name of the subroutine doesn't change - UniVerse may still cache the object code in memory if the file is opened e.g. to named common. So I hold the name of the routine to call externally, and have the master trigger read it. Then I can vary it when the subroutine changes (e.g. adding a version number to the end). Brian Oooo! Now there's a neat trick (excuse me, technique). I'd never thought of it. Have you ever used Mark Baldridge's Profiler (Travels With Mark, on the IBM Developer Works site) to quantify what I assume is a rather trivial amount of overhead in the double call? If not, I'll take a go at it and report back. It sounds like a great technique. Thanks! -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 12, 2007, at 8:23 PM, Phil Walker wrote: What I do is create a wrapper trigger program which does nothing more than call the real subroutine. That way as this trigger never or rarely changes I can modify the underlying trigger program without having to drop and recreate the trigger on the file. Clifton Oliver [EMAIL PROTECTED] wrote: = Two more come to mind: If you change and recompile the subroutine, you have to drop and re- create the trigger. Files with triggers cannot be updated via UV/Net. -- Regards, Clif On Sep 12, 2007, at 2:04 AM, Brian Leach wrote: 5. Triggers impose some limitations : they all make sense if you step back and think about them except (b): --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
DOH! Sorry. Overlooked that. So much for speed reading. - Clif On Sep 13, 2007, at 3:38 AM, Brian Leach wrote: See point 6 on my post grin --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Just to clarify 1. You can add triggers to non-sql files so long as they have a header. See the HELP SQL CREATE TRIGGER for the subroutine layout. 2. You can have up to 6 main triggers -before and after each type of event (insert, update, delete). 3. triggers must be globally cataloged since the file may be updated from any account - so a local catalog wouldn't be found. 4. To globally catalog copy the CATALOG entry from the VOC of the uv account to your local VOC (under a new name) and use that. Prefix the catalog name with an asterisk. 5. Triggers impose some limitations : they all make sense if you step back and think about them except (b): a. They observe some SQL constraints for isolation - so ALL writes must be lock protected. b. You can't move the file to a new platform (format.conv won't process it) c. Some commands are disallowed. d. No interaction is allowed, including the RAID debugger. 6. A useful tip - If a trigger changes you must ensure no-one has the file open. That's not always possible on a 24x7 system so I have a master trigger read a list of subtriggers from a file and call them in turn. Then I can back out a change by removing a routine from the list, or substitute a new routine at any time. 7. Triggers slow processing if over-used. Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Two more come to mind: If you change and recompile the subroutine, you have to drop and re- create the trigger. Files with triggers cannot be updated via UV/Net. -- Regards, Clif On Sep 12, 2007, at 2:04 AM, Brian Leach wrote: 5. Triggers impose some limitations : they all make sense if you step back and think about them except (b): --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Oooo! Now there's a neat trick (excuse me, technique). I'd never thought of it. Have you ever used Mark Baldridge's Profiler (Travels With Mark, on the IBM Developer Works site) to quantify what I assume is a rather trivial amount of overhead in the double call? If not, I'll take a go at it and report back. It sounds like a great technique. Thanks! -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 12, 2007, at 8:23 PM, Phil Walker wrote: What I do is create a wrapper trigger program which does nothing more than call the real subroutine. That way as this trigger never or rarely changes I can modify the underlying trigger program without having to drop and recreate the trigger on the file. Clifton Oliver [EMAIL PROTECTED] wrote: = Two more come to mind: If you change and recompile the subroutine, you have to drop and re- create the trigger. Files with triggers cannot be updated via UV/Net. -- Regards, Clif On Sep 12, 2007, at 2:04 AM, Brian Leach wrote: 5. Triggers impose some limitations : they all make sense if you step back and think about them except (b): --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RES: [U2] Universe and triggers
Hi Doug, The problem is that the TEST.FILE MUST be a UV SQL Table. In my case, will be a problem if a need to convert my regular files using the command CONVERT.SQL, it will be painful. Regards, Marcos Fogaca -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de Doug Miller Enviada em: terga-feira, 11 de setembro de 2007 18:01 Para: u2-users@listserver.u2ug.org Assunto: Re: [U2] Universe and triggers At 02:01 PM 9/11/2007, you wrote: It is possible create a trigger (subroutine) for a pick file (non SQL table)? Yes. I just so happened looked this up for someone else this morning. The best source of documentation I came across was this FAQ. File Trigger Example Technote (FAQ) Problem Sometimes it is useful to find out what processing has occurred on a file. For instance, records may have been deleted from a file in error and these records need to be tracked. Placing a trigger on a file which fires after the delete can keep a record of the event. Cause From the 10.0 release of UniVerse, and later, it is possible to place a trigger on a UniVerse file. Solution UniVerse executes (fires) triggers when some action changes the file's data. UniVerse treats all events that change the database as either an INSERT, UPDATE, or DELETE action. For example, a BASIC WRITE statement would be either an INSERT or UPDATE action and a BASIC DELETE would be treated as a DELETE action. The following is an example of how to place a trigger on a file which fires when a DELETE has taken place. The trigger updates a separate file which can then be read to find out what was deleted. Some example code for a trigger BASIC subroutine follows. It is called FILETRIG and is saved in the BP file. A trigger BASIC subroutine must contain 14 arguments in a specific order. For more information on these arguments, see the chapter titled File Triggers in the UniVerse System Description manual. 0001: SUBROUTINE TEST(TEST,SCHEMA,TABLE,EVENT,TIME,NEWID,NEWREC,OLDID,OLDREC,ASSOC,ASSOC.EVEN T,COUNT,CHAIN.CASCADE,CASCADE) 0002: COMMON /TEST/ AUDITFV 0003: FILEOPENED = FILEINFO(AUDITFV,0) 0004: IF FILEOPENED NE 1 THEN 0005: OPEN '','TRIG_AUDIT' TO AUDITFV ELSE CRT 'COULD NOT OPEN TRIG_AUDIT'; 0006: RETURN 0007: END 0008: ITEM = 'Deleted ':OLDID:' in ':TABLE 0009: ID = TIMEDATE(): '*' :OLDID 0010: LOOP 0011: RECORDLOCKU AUDITFV,ID 0012: ON ERROR RETURN 0013: LOCKED 0014: NAP 250 0015: CONTINUE 0016: END 0017: EXIT 0018: REPEAT 0019: WRITE ITEM ON AUDITFV,ID 0020: RETURN After FILETRIG is compiled it must be globally catalogued: CATALOG BP FILETRIG *ce*FILETRIG cataloged. Now create the trigger on the file in question: CREATE TRIGGER TEST.TRIGGER AFTER DELETE ON TEST.FILE FOR EACH ROW CALLING *ce*FILETRIG; Adding trigger TEST.TRIGGER Now create the file TRIG_AUDIT: CREATE.FILE TRIG_AUDIT 30 Creating file TRIG_AUDIT as Type 30. Creating file D_TRIG_AUDIT as Type 3, Modulo 1, Separation 2. Added @ID, the default record for RetrieVe, to D_TRIG_AUDIT. Now, if a record is deleted from the file called TEST.FILE, TEST.TRIGGER will fire and update the TRIG_AUDIT file. As an example, record id 103 was deleted from TEST.FILE. Listing TRIG_AUDIT gives: $50LIST TRIG_AUDIT 16:32:23 15-09-06 PAGE1 TRIG_AUDIT 16:32:16 1 5 SEP 2006 *103 1 records listed. Doug Miller [EMAIL PROTECTED] Manager of Technical Services Strategy 7Dallas TX --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RES: [U2] Universe and triggers
Hi, My UV is release 10.2 and if I try convert a regular pick file there are the error message : CREATE TRIGGER T_TRIGGER AFTER INSERT OR UPDATE ON GVMTA030 FOR EACH ROW CALLING '*T_TRIGGER'; UniVerse/SQL: Invalid file type specified. Warning: SICA information could not be read properly!! T_TRIGGER 001 SUBROUTINE T_TRIGGER(PAR1,PAR2,PAR3,PAR4,PAR5,PAR6,PAR7,PAR8,PAR9,PAR10,PAR1 1,PAR12,PAR13,PAR14) 002 RETURN What type of file are you using? Thanks in advance. Regards, Marcos Fogaca -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de gerry-u2ug Enviada em: terga-feira, 11 de setembro de 2007 18:26 Para: u2-users@listserver.u2ug.org Assunto: RE: [U2] Universe and triggers Hi Marcos, what version of universe are you running ? We used to use the index w subroutine method you describe but this is now gone in favour of native universe triggers. We have many files in universe with triggers on them , there is no requirement to SQL'ize the table and believe me these tables would take a LOT of work to make them sql conformant. Universe will add a SICA for the table but that's it, any garbage hashed file works just fine afaik. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: September 11, 2007 04:07 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Universe and triggers Hi Marcos, Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. Trey -- Original message -- From: Marcos Fogaga [EMAIL PROTECTED] Hi, It is possible create a trigger (subroutine) for a pick file (non SQL table)? On D3 we have CALLX, is there a feature like this in Universe? Thanks in advance. Best Regards, Marcos Fogaca Integral Sistemas Sao Paulo / Brazil --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Not as of release 10.0. On Sep 11, 2007, at 2:24 PM, Marcos Fogaga wrote: The problem is that the TEST.FILE MUST be a UV SQL Table. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
At 04:24 PM 9/11/2007, Marcos Fogaga wrote: Hi Doug, The problem is that the TEST.FILE MUST be a UV SQL Table. In my case, will be a problem if a need to convert my regular files using the command CONVERT.SQL, it will be painful. Not per this line in the FAQ as well as reports from some others posting that they implemented triggers on UV files. From the 10.0 release of UniVerse, and later, it is possible to place a trigger on a UniVerse file. Doug Miller [EMAIL PROTECTED] Manager of Technical Services Strategy 7Dallas TX --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
I use triggers on 10.2 files frequently. What file type is GVMTA030? If it's a type 1 or 19, it won't work. If it is a static hashed 2 through 18 it will. Also works on type 30. However, if it is a distributed file, you have to create the trigger on each individual partfile; you cannot name the top level DF. Also, do a tcl .L GVMTA030 And see if there is an M in line four. Creating triggers on multi data level files requires some tricks as the comma is not valid in a file name for CREATE TRIGGER (SQL syntax restriction). -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 11, 2007, at 2:53 PM, Marcos Fogaga wrote: Hi, My UV is release 10.2 and if I try convert a regular pick file there are the error message : CREATE TRIGGER T_TRIGGER AFTER INSERT OR UPDATE ON GVMTA030 FOR EACH ROW CALLING '*T_TRIGGER'; UniVerse/SQL: Invalid file type specified. Warning: SICA information could not be read properly!! T_TRIGGER 001 SUBROUTINE T_TRIGGER(PAR1,PAR2,PAR3,PAR4,PAR5,PAR6,PAR7,PAR8,PAR9,PAR10,PAR1 1,PAR12,PAR13,PAR14) 002 RETURN What type of file are you using? Thanks in advance. Regards, Marcos Fogaca -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de gerry-u2ug Enviada em: terga-feira, 11 de setembro de 2007 18:26 Para: u2-users@listserver.u2ug.org Assunto: RE: [U2] Universe and triggers Hi Marcos, what version of universe are you running ? We used to use the index w subroutine method you describe but this is now gone in favour of native universe triggers. We have many files in universe with triggers on them , there is no requirement to SQL'ize the table and believe me these tables would take a LOT of work to make them sql conformant. Universe will add a SICA for the table but that's it, any garbage hashed file works just fine afaik. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: September 11, 2007 04:07 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Universe and triggers Hi Marcos, Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. Trey -- Original message -- From: Marcos Fogaga [EMAIL PROTECTED] Hi, It is possible create a trigger (subroutine) for a pick file (non SQL table)? On D3 we have CALLX, is there a feature like this in Universe? Thanks in advance. Best Regards, Marcos Fogaca Integral Sistemas Sao Paulo / Brazil --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
