Solved

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

Posted on 2003-10-26
7
514 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ED710 -->  UADDMBX 10 296
iSeries - Cancel Stagnant Jobs 5 132
Free Form Definition specs 3 128
SQL400 max size 5 104
Enabling the Skype for Business Meeting Scheduler in Hybrid OWA
Building a cohesive image for your brand is vital to making an impression on consumers. When the economy is tough, brands do better than unbranded  products. This can have a huge impact on your long-term profits, as the economy goes up and down.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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