Solved

How can you list all database tables with recent record additions ?

Posted on 2003-10-26
7
517 Views
Last Modified: 2012-06-27
New to As400.

Is there a way to list the database files (tables) in a library that had records added to them within a certain time period ?

Basically, we have a database which consist of 600 data tables. I am attempting to write a program to append orders from our website to our AS400 database. What I want to do is enter an order using our standard order-entry programs and then list all the tables that were
updated.

0
Comment
Question by:TRIANGLE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 33

Accepted Solution

by:
shalomc earned 75 total points
ID: 9622655
Hey,
I use journaling and auditing as database research tools.
In your case, I would recommend the following method:

1. Create a journal and journal receiver to hold the data. You can use an existing library if you want to.
CRTLIB jrnlib
CRTJRNRCV JRNRCV(jrnlib/MYJRNRC) TEXT('Order entry journal receiver')
CRTJRN JRN(jrnlib/MYJouRNal) JRNRCV(jrnlib/MYJRNRC) TEXT('Order entry journal') MNGRCV(*system)

2. Add all of your files to the journal. Run the following command for each file you want to investigate.
STRJRNPF FILE(LIB/FILE01) JRN(jrnlib/MYJouRNal) IMAGES(*BOTH) OMTJRNE(*OPNCLO)

3. Enter your orders and od whatever the application does.

4. Run the following command:
DSPJRN JRN(jrnlib/MYJouRNal) ENTTYP(*RCD) OUTPUT(*OUTFILE) OUTFILE(JRNLIB/JRNOUTFILE)

5. Investigate the result file with whatever tools you like: Query, Excel, SQL....
The file will contain information about added records, deleted records, and updated records. In the case of update operations, the journal holds images of the record before and after the update.

You may have questions about the meaning of the different codes in the journal. Try to run the DSPJRN command to screen and go through the online help to understand what you see. The iSeries Infocenter has a lot of information as well.  http://publib.boulder.ibm.com
Of course, you could just ask a question in this forum....
0
 
LVL 14

Expert Comment

by:daveslater
ID: 9779574
Hi
is this question still open?
0
 

Author Comment

by:TRIANGLE
ID: 10242067
Yes - while I kindly thank ShalomC for his answer - the question is still open.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 75 total points
ID: 10251181
Hi

An alternate way the does not use journaling but DSPFD with the *MBR over all the libraries that contain the database objects.
DSPFD FILE(DATALIB/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(QGPL/DSPFDB)

perform your updates then run the command again to a different output file (DSPFDA)

the run the following sql

SELECT a.MBFILE,a.MBLIB, a.MBWROP, b.MBWROP FROM dspfdb a,  
dspfda b WHERE a.MBFILE=b.MBFILE and a.MBLIB=b.MBLIB and    
a.MBWROP<> b.MBWROP                                              

This will show any physical file that has had records added. The the file also holds update, delete and clear operations aswell as open and close detauils.

Regards


Dave
0
 
LVL 3

Expert Comment

by:Mind_nl
ID: 10616301
but that would only work if your order entry program would be the only program running at at that time...
0
 
LVL 27

Expert Comment

by:tliotta
ID: 10780217
I'm not clear on why the question is still open. ShalomC's answer is precisely correct, although I'd probably go with only IMAGES(*AFTER) rather than IMAGES(*BOTH) if all that's needed are the PT entries. And of course if journalling is already active, as it sure should be with an application with 600 tables, then the answer is essentially already implemented. And Dave's answer works perfectly well as long as it's a controlled test where no other applications are active that might skew the results.

Perhaps it just needs some refinement. How about going in stages?

Start with something like this:

 ==>  chgobjaud obj(mylib/*ALL) objtype(*FILE) +
             objaud(*CHANGE)

Turn auditing on and run your application test. Then reset object auditing back to its initial level. The audit journal should then provide you with a potentially small subset of all your tables. Only those that actually changed need be looked into farther.

If current answers aren't sufficient, I'm sure not clear on what the question is.

Tom

0
 

Author Comment

by:TRIANGLE
ID: 10799250
I split the 150 points evenly between shalomc ad daveslater.
Both answers were very usefull to me.
I thank you both very kindly.

Thanks - Richard
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After hours on line I found a solution which pointed to the inherited Active Directory permissions . You have to give/allow permissions to the "Exchange trusted subsystem" for the user in the Active Directory...
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question