As an update for the records:
After playing around with it most of the day, it turns out that the trigger
error is really caused by a lack of data. If the user that you are tracking
data for is not signed on or doing anything, no data is collected into the
database, then when you query it, it will either return a null set or blow
up the triggers involved.
So to fix it, I:
1) deleted the authority collection altogether
2) made sure the user was on the system working
3) re-run the collection
4) run SQL to report data
I think the key was to delete and recreate the data.
Nothing to do at all with the OEM software.
--
Jim Oberholtzer
Agile Technology Architects
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Monday, September 25, 2017 3:17 PM
To: Midrange Systems Technical Discussion
Subject: RE: Remove (temporaritly) Triggers
Perhaps I should have said
CHGPFTRG ... STATE(*DISABLED)
instead of just
CHGPFTRG STATE(*DISABLED)
Talk about nesting...
select *
from qsys2.views
where table_name like '%AUTHORITY%';
And basically authority_collection is a view over some table functions.
select ... (first time I've ever seen LAND (LogicalAND) used in
production)
FROM TABLE (QSYS2.OBJECT_STATISTICS('QSYS', 'USRPRF','*ALL')) AS A, TABLE
(QSYS2.AUTHORITY_COLLECTION(A.OBJNAME)) AS B LEFT OUTER JOIN TABLE
(QSYS2.OBJECT_STATISTICS(B.SYSTEM_OBJECT_SCHEMA, B.SYSTEM_OBJECT_TYPE,
B.SYSTEM_OBJECT_NAME)) AS C ON B.SYSTEM_OBJECT_TYPE = C.OBJTYPE AND
B.SYSTEM_OBJECT_NAME = C.OBJNAME
You might want to ask Help Systems for suggestions.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 09/25/2017 03:15 PM
Subject: RE: Remove (temporaritly) Triggers
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Would that not disable all the triggers on the system? I'm only after the
ones for the security collection. The rest of them need to stay in
place.
So the root question is do we know the names of the tables that are used
with the QSYS2.AUTHORITY_COLLECTION view? Maybe a naïve question, but an
honest one at least. I've not delved into those tables much yet.
--
Jim Oberholtzer
Agile Technology Architects
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Monday, September 25, 2017 2:06 PM
To: Midrange Systems Technical Discussion
Subject: Re: Remove (temporaritly) Triggers
I believe the HA products do this by disabling the triggers. Such as
CHGPFTRG STATE(*DISABLED)
Production:
Trigger Description
Trigger name . . . . . . . . . . . . . . . : TRG QSYS_TRIG_ERPLXF_
____IIM________000002
Trigger library . . . . . . . . . . . . . : ERPLXF
Trigger state . . . . . . . . . . . . . . : STATE *ENABLED
Trigger status . . . . . . . . . . . . . : *OPERATIVE
Trigger event . . . . . . . . . . . . . . : TRGEVENT *INSERT
Trigger time . . . . . . . . . . . . . . : TRGTIME *AFTER
Allow repeated change . . . . . . . . . . : ALWREPCHG *NO
Program Name . . . . . . . . . . . . . . : PGM SYS638B
Library . . . . . . . . . . . . . . . . : ERPLXPTFO
H/A:
Trigger state . . . . . . . . . . . . . . : STATE *DISABLED
Trigger status . . . . . . . . . . . . . : *OPERATIVE
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 09/25/2017 02:58 PM
Subject: Remove (temporaritly) Triggers
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Folks:
I am working on a security project where we are using V7R3 and the
authority
collection capability. The goal is to analyze the data and set up object
access in a much better way.
The Customer has purchased several products from Help Systems, to bolster
their tooling, to which I have no problem at all, they are very reasonable
products, they do however present a challenge. Apparently they put
several
triggers on the database tables that QSYS2.AUTHORITY_COLLECTION uses, so
we
cannot write our own SQL against it to do any special reporting we might
wish to do that might not be included in the tooling.
How do I disable the trigger(s) temporarily? I thought CHGPFTRG would
work
but I have not been successful yet.
--
Jim Oberholtzer
Agile Technology Architects
midrange.com
