RE: Trigger pgm - SQL errors -- MAPICS-L

A variation on journaling would be to use OS/400 object auditing which 
essentially creates a special audit journal. I don't remember the specifics, 
but have used it to log all activity on a file.  I believe it puts a special 
entry in the audit journal for every read to the file and other entry type for 
other activity.

-----Original Message-----
From: Konrad Underkofler [mailto:kdunderk@xxxxxxxxxxxxx]
Sent: Thursday, June 19, 2003 2:56 PM
To: MAPICS ERP System Discussion
Subject: RE: Trigger pgm - SQL errors [bcc][faked-from]


Pete,

Sounds good, I would create a GELJRN, attach a receiver, then
periodically copy the records, clear the journal and run a query to
select the desired user ids, or just do a summary report by user id. We
did it here to monitor deletes in the CUSMAS file. Of course as soon as
you monitor deletes nobody does them...

I have a feeling the trigger problem is related to the threading work
they are doing on the 400. Green screen access and older utilities like
query may be single thread but support for Java, JDBC, ODBC has been
optimized to take advantage of the newer technologies. Strangely enough
they have no documented examples of read triggers, all the Rochester
presentations I can find say to stay away from them and the APAR
database does not contain the CPF message it generated. They also don't
allow them to be SQL triggers, they must be external programs. Very
strange.

Good luck!

Konrad 

-----Original Message-----
From: Pete Olshavsky [mailto:polshavsky@xxxxxxxxx] 
Sent: Thursday, June 19, 2003 3:08 PM
To: MAPICS ERP System Discussion
Subject: RE: Trigger pgm - SQL errors


Konrad, Thanks. I was beginning to think I was losing it. At least I
have someone to agree that it is a problem.... I like your journal idea.
Is there anything special I should know about this. I should journal to
MPXJRN in AMFLIBx? 
Dspjrn to a file, then qry file? Or is there a better way or something
else.
 
Thanks Again
Pete 

Konrad Underkofler <kdunderk@xxxxxxxxxxxxx> wrote:
Pete,

I tried a variety of scenarios with various triggers here and replicated
your results. Any direct access via ODBC throws the same message on a
read trigger. Might not be worth the effort to debug since read triggers
are expensive anyway for a high activity file like GELMAS.

Have you considered just turning on journaling for GELMAS and monitoring
the opens and closes? You could easily find who opened or closed file
that way and it is not much of a performance impact. Just periodically
copy the journal entries to a table, sort and print.

Regards

Konrad

-----Original Message-----
From: Pete Olshavsky [mailto:polshavsky@xxxxxxxxx] 
Sent: Thursday, June 19, 2003 12:43 PM
To: MAPICS ERP System Discussion
Subject: RE: Trigger pgm - SQL errors


I am looking at the addpftrg on a V4r5M0 box. And it is not avail. I
believe V5R1 is when it was introduced. 

"Neely, Chris" wrote:With what version of
the operating system did 'read' triggers come out? And, in what
scenarios were they intended to be used for?

Thanks

-----Original Message-----
From: michael.franchino@xxxxxxxxxx [mailto:michael.franchino@xxxxxxxxxx]
Sent: Thursday, June 19, 2003 10:46 AM
To: MAPICS ERP System Discussion
Subject: RE: Trigger pgm - SQL errors


SQL is an additional licensed program that many customers don't buy.. If

you have access to their machine through a PC, you can use Operation 
Navigator's SQL feature.. Also, if you want to test to see if the
trigger 
is working, do a DSPPFM on the file.. It will fire that trigger a
zillion 
times.

Just be aware, that READ triggers are very very dangerous.. Use
sparingly.

Michael Franchino
Custom Systems Corporation
http://www.cussys.com
(973) 726-0202 X214 (Phone) 
(973) 726-4552 (Fax)




Pete Olshavsky 

Sent by: mapics-l-bounces@xxxxxxxxxxxx
06/19/2003 11:42 AM
Please respond to MAPICS ERP System Discussion


To: MAPICS ERP System Discussion 
cc: 
Subject: RE: Trigger pgm - SQL errors


Konrad & Malcolm, Thanks for your input. Don't want to beat a dead horse

here. 
One more question. Malcolm. Your suggestion about strsql reminded me of 
something else... On the customer that I am trying to do this. I am not 
able to run the strslq command. Also I am not 100% sure about this but
if 
I do a GO CMDSQL ... I only have a couple of menu options. Could 
something not have been ordered on this box? Just installed beginning of

the year. Missing a licensed product (DB2 Query Mgr and SQL DevKit for 
AS/400)
Thanks again for input

Pete
Malcolm Westley wrote:
Pete,

I would agree with Konrad that it would be a good idea to do a bit more 
investigation, although there is a good case for an os400 bug. *Read= is
a fairly new trigger program option, and a quick check of the ibm w
ebsite 
produced the following APAR whereby a decimal data error in Quer= y
produced the same error you are seeing (the error was incorrectly gen 
erated).



Another way to test your problem would be to use the STRSQL command, th
en 
type in the command SELECT * FROM GELMAS and press Enter. If SQL i= s
causing your error, you should see it crop up.

As to how to report an APAR, I have not had to do so, but it looks as t 
hough you will need a customer number of a customer with a valid servic=
e contract in order to submit one. Ibm should be able to tell you how=
to go about it. No doubt the first thing they will want to know is i= f
you have all the latest fixes installed.

Good luck,
Malcolm

> Pete,
>
> I would still rule out an OS/400 bug until you have done a bit mor= e
> tracing. AM+ could be doing something strange in ODBC that is no 
> rmally not seen because of the speed of the 400.
>
> You may want to look at the stack on the job that is making the re
peated
> call to see exactly which program / line is creating the issue. = A
> further step would be to do a normal odbc read of GELMAS and see i= f
the
> same issue arises. If it does not, check the ODBC connection par
> ameters for the AM+ vs your odbc connection to see if there are 
> difference= s. Finally, if that fails turn on ODBC tracing for the AM+

> connection= to see exactly what the application is doing. In that you
> should se= e
the
> connection SQL strings as they get executed.
>
> HTH
>
> Konrad
>
>
>
> -----Original Message-----
> From: Pete Olshavsky [ma= ilto:polshavsky@xxxxxxxxx]
> Sent: Thursday, June 19, 2003 7:17 AM
> To: MAPICS ERP System Discussion
> Subject: RE: Trigger pgm - SQL errors
>
>
> Malcolm, I tend to agree with you. I have never done this. Can= you
> explain how? Also I am an independant programmer. Will I need an= IBM 
> customer no? Thanks Pete
>
> Malcolm Westley wrote:
> Pete,
>
> Looks like you found an os400 bug. You should submit an APAR to ibm.
>
> Malcolm_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.





---------------------------------
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.



_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.


_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.



---------------------------------
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.


_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.





---------------------------------
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.


_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.