Solved

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

Posted on 2003-10-26
7
509 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IBM AS400 V6R1M0 ftp upload very slow to window machine 6 255
Installing a Printer on AS400 5 179
Free Form Definition specs 3 106
iSeries email authority 6 58
Adding Computers to AD groups through an SCCM Task Sequence
This problem is more common than not and I will show you some things to check to solve this problem.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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