Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-10-26
7
Medium Priority
?
521 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 300 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 300 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

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!

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 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