Solved

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

Posted on 2003-10-26
7
504 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 32

Accepted Solution

by:
shalomc earned 75 total points
Comment Utility
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
Comment Utility
Hi
is this question still open?
0
 

Author Comment

by:TRIANGLE
Comment Utility
Yes - while I kindly thank ShalomC for his answer - the question is still open.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now