Journaling-does it reflect a record change when iSeries file has been updated outside of this environment?
First of all I want to note that we journal all our files on the iSeries where I work at now. I want to mention that long ago where I worked at before, we had to get into the journals to discover what was causing records to be deleted randomly and no one could find out what program was causing it...we just knew a bunch of records were being deleted radomly. I remember by looking through the journals somehow we found the records showing the deletes and it gave us what program was deleting it. Now we had direction, found the bug and fixed the program.
Here where I work at now, we have an agressive user who does a lot with MS Query, Access, etc and uses ODBC (read only that IT dept. set up) to get the data from the iSeries to his stuff. This person wants to be able to use this means to update info back to the iSeries files and naturally IT department does not want this to occur and have stressed that. This person is quite PC savy and I think this person already found way to create his own ODBC link and turn off the 'read only'. Currently we have the libraries locked down with security and unless he finds the administrative password, this person cannot update information to the iSeries files.
SO....now my question: if this person did get this to work, would the journal show this change coming from outside the iSeries environment and actually tell us what application (MS Query or Access) changed it, would it reflect a user id? Or would no journal entry be created when this occurred because it was done outside of the iSeries environment? I just do not know enough about journals....I remember it was a nightmare to figure that problem out years ago but it was cool once we found it. Thanks to anyone who might be an expert on journaling and can answer this question.
Software/Hardware used:
iSeries files and MS Query or Access
Software/Hardware used:
iSeries files and MS Query or Access
ASKED:
February 4, 2010 1:57 PM
UPDATED:
February 8, 2010 2:31 PM
RELATED QUESTIONS
- Restored Journal receivers that are deleted by system
- When doing a chain the (HI) indicator return with '1'. But if I do a query on the same file I can see that the record is in the file.
- What can cause a journal entry where JOENTT = 'CR' (besides CLRPFM)?
- Missing Entry Specific Data in Journal Entries for database delete (RDL) operations
- DB Triggers and JPA
Answer Wiki:
Hi,
I'm not an expert in journals but I've used it. The most important thing here is that the access/update/delete doesn't occurs "outside" of iSeries. So, any configured action made over a journaled file will be reflected in journals but you can't know which app is, only an User Id.
Normally ODBC and external access to iSeries DB are made by a server job which has a specific User Id. I don't remember if the systems stores the generic User Id or the User Id used to log in to DB.
Hope it can help you.
Regards,
Wilson
To see all answers submitted to the Answer Wiki: View Answer History.
Wilson has got it. Any change to the date will be recorded in the journal.
This case may show the correct user or it may show a generic user such as QUSER and it will show a generic communication job. Nothing about what is running on the users desktop.
Phil
You are correct, the journal will pick up changes made from ODBC accesses. The user ID will be the one that ODBC connected with. I am not sure what will be listed and the program that accessed the file and performed the updates. It will probably be blank.
If you are just trying to catch updates done by a specific user, you might consider adding a trigger program to the file. (ADDPFTRG). This will allow you to perform a program call (RPG or CL) and have it track updates.
if this person did get this to work, would the journal show this change coming from outside the iSeries environment and actually tell us what application (MS Query or Access) changed it, would it reflect a user id?
First, the database journal will show the transaction, including that it was via ODBC and which user was involved. It might show the IP address of the originating system; that depends on how you configure the journal.
As for “what application (MS Query or Access)”, there is essentially no way for your iSeries to know what application is running on a remote system. All it can ever know is what packets come across the connection. You might have written a PC program that does the sockets work and talks to the SQL server. There’s no way to know if your program sends misleading info.
However, if you control your ODBC connections and applications, you might be able to tease some useful info out of the connection by having an exit program examine the server job environment where the connection is handled. That’s separate from the database journaling, but still an available direction to explore.
Tom
Minor correction… The journal won’t actually refer to “ODBC”. Instead it will name the server job that handles ODBC connections. Just to clarify.
Tom
Thanks to all who responded. I appreciate it and all the information gives me some direction. I have read various questions and answers of topics I found interesting in the past but this is first time I have asked a question. This works great.