RPG - get the most recent record and write to a file

Hello,
I'm new to RPG so I have no clue how to go about doing this but I’m sure it will be easy for most.
I need to read a file, keying on the user name. Once the first record is read with each user name, I need the program to write that record to a file.   Now if you simply display the file, the entry with the oldest date appears first. However if you DBU the file or RUNQRY *N and select a specific user to key on, the output is sorted by the most recent date/time or the date/time is in descending order.
So when the program runs, hopefully the first record of each user would show the most recent date of access.

Sample Output from DBU:
USER       SYSTEM      DATETIME                   SERVERID    REASON
BARRY      MYSYSTEM   2010-03-23-15.01.54.000000  *SQL          1    
BARRY      MYSYSTEM   2010-03-23-14.55.59.000000  *SQL          1    
BARRY      MYSYSTEM   2010-01-22-09.31.16.000000  *SQL          1    
BARRY      MYSYSTEM   2009-08-31-14.45.16.000000  *SQL          1    

Source file:
A          R ODBC                                              
A            USER          10A         TEXT('USER PROFILE')    
A            SYSTEM         8A         TEXT('#SYSNAME')        
A            DATETIME        Z         TEXT('LOGIN DATE/TIME')  
A            SERVERID      10A         TEXT('SERVER IDENTIFIER')
A            REASON         1A                                  
A          K DATETIME                  DESCEND                  

The logical:
A          R ODBC                      PFILE(*LIBL/ODBCLOG)
A          K USER                                          
A          K DATETIME                  DESCEND              

Any help would be greatly appreciated.

Thanks,
Barry
LVL 2
bggauthAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
Barry,

I think teaching you RPG or writing an entire application for you is asking a bit much for a forum like this.  Plus, based on your description, I'm not sure what it is you want to create:
  • Is this an interactive program, where the user is prompted on the screen for a user name or other parameters?
  • Is this a batch program where the user is passed as a command-line parameter?
  • Is this a batch program that processes an entire file, summarizing information for each user?
  • Do you need to create a screen to enter parameters?  If so, shat does it look like?
  • What does the output file need to look like?
  • If there is summarization involved, what?
As far as RPG programming goes, there are lots of other things that you have to know other than just the RPG, for example:
  • You need to know how to use an editor to enter our source code (SEU or WDSC are common).  Do you know either of these?
  • You need to understand source physical files.
  • You need to know how to compile (and possibly bind) a program.
  • If you are creating programs with screens, you need to know DDS and/or SDA, or another screen editor.
I can point you to some great references for learning RPG, and provide some sample code once you explain the application you want to create better.  I'll do that in another post.

- Gary




0
Gary PattersonVP Technology / Senior Consultant Commented:
Scott Klement has a nice page on "How to learn RPG".  Rather than echo his thoughts, I'll just link.  I've used both of the books that he recommends (I've used the Meyers/Buck book as a textbook for teaching "Intro to RPG Programming").

http://www.scottklement.com/rpg/howtolearn.html

- Gary Patterson
0
bggauthAuthor Commented:
Thanks for your response Gary.
I am advanced in CL Programming and am an i5 admin. I'm just not familiar with RPG.
The input file in simply a log with the date / time a user attempted to access the system via ODBC and whether that connection was successful.  Being successful or not is dependant on an exit program and an authority file.

I simply want to read that file and extract the most recent attempt to connect to the system and output that to another file.

The program will be run by myself on an ad-hoc basis.

I wasn't expecting someone to do the work for me, but point me to some pertinent code or give some ideas.

To answer your questions:
Is this an interactive program, where the user is prompted on the screen for a user name or other parameters?  No
Is this a batch program where the user is passed as a command-line parameter? No
Is this a batch program that processes an entire file, summarizing information for each user? Yes partialy.
Do you need to create a screen to enter parameters?  If so, shat does it look like? No
What does the output file need to look like? the same format as the input file
If there is summarization involved, what?  No
As far as RPG programming goes, there are lots of other things that you have to know other than just the RPG, for example:

You need to know how to use an editor to enter our source code (SEU or WDSC are common).  Do you know either of these?  I am familiar with SEU.
You need to understand source physical files. I am familiar with PF's
You need to know how to compile (and possibly bind) a program.  I know how to cimpile programs.
If you are creating programs with screens, you need to know DDS and/or SDA, or another screen editor. I know DDS and SDA but a dispaly file is not necessary.

I'll see if I can pick up some pointers from the link you sent.

Thanks.
Barry
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

tliottaCommented:
Are you intending to use this as a way to become familiar with RPG? Or are you primarily interested in extracting the data?

This is a fairly easy task with a single SQL statement if data extraction is what you're after. It's also a pretty basic RPG program, so it's a reasonable example either way. The keys for the LF help significantly.

Tom
0
bggauthAuthor Commented:
Hi Tom,

My primary interest is in extracting the data.

I hadn't thought of SQL and am a little familiar with it    
I will attempt to figure that one out.

Thanks,
Barry
0
bggauthAuthor Commented:
Well unfortunately I can't figure it out the SQL statement.  So it's not so simple for me :)


Barry
0
Gary PattersonVP Technology / Senior Consultant Commented:
Well the hurdle of learning RPG isn't as big when you are already a CL programmer.  Check out the resources I mentioned earlier.  In the meantime, SQL is definitely the way to go.  Wish I'd thought of suggesting SQL!

The SQL required is a little tricky:

select * from odbclog where datetime in (select max(datetime) from odbclog)
select clause descrives which columns you want
from describes the tables that you want to query
where describes the rows that you want

This particular query also uses a subquery to identify the largest timestamp in the file, and uses that value for record selection in the main query.

Note that if there is a tie for the maximum timestamp, then you'll get multiple records.

Now, if you want to take the results and drop them into a second file, you'll need to turn it into an "insert into".  Assuming "lastodbc" has the same layout as odbclog:

insert into lastodbc
select *
 from odbclog
 where datetime in (select max(datetime)  from odbclog)

-Gary
0
bggauthAuthor Commented:
Thanks Gary,

I just noticed your comment.

I had created a QM Query, created a LASTUSED  Date field from the DATETIME timestamp, selected distinct records, sorted by DATETIME in Descending order and selcted records greater than 12/10/2009
This gave me more than what I needed.  I then transferred that data into excel sheet b.  I had a list of user names on sheet A and did a vlookup to sheet B to get the most recent date.  

This is the SQL statement from the QM QUERY

SELECT DISTINCT                                                          
-- Columns                                                                
      A.SYSTEM, A."USER", DATE(DATETIME) AS LASTUSED, A.SERVERID, A.REASON
-- Tables                                                                
      FROM "ODBCLOG"/"ODBCLOG" A                                          
-- Row Selection                                                          
      WHERE ((DATE(DATETIME)) > '12/10/2009')                            
-- Sort Columns                                                          
      ORDER BY LASTUSED DESC                              

I tested your statement and it's good except it only reads the most recent record.  It stops after it reads that record.  

Barry
               
0
Gary PattersonVP Technology / Senior Consultant Commented:
Yes, exactly what you asked for:

... I simply want to read that file and extract the most recent attempt to  connect to the system and output that to another file.


Do you mean that you want the last attempt to connect PER USER?

If so, then

select distinct *
from odbclog
where (user || char(datetime)) in (select user || char(max(datetime)) from  odbclog group by user)

You could also accomplish the same thing several other ways, but I wanted to stick as close to the original example as possible.

- Gary

0
bggauthAuthor Commented:
yes that is correct.  As in my initial post "... I need to read a file, keying on the user name. Once the first record is read with each user name..."
Sorry I was not consistent in my request.

Still only returns the very first record Gary. I can do simple SQL but this statement is a little beyond me so unfortunately at this time I cannot modify it.

Barry
0
bggauthAuthor Commented:
Here's an SQL statement created by a colleague that works as I need it.
You all can probably follow the logic better than I.

insert into bggodbc                                        
select distinct system, profile, maxdate, b.reason        
from (   SELECT "USER" as profile, max(DATETIME) as maxdate
FROM odbclog/odbclog                                      
GROUP BY "USER", serverid ) as max                        
join odbclog/odbclog as B                                  
on max.profile = B."USER" and max.maxdate = B.datetime    
order by reason desc, maxdate desc                        
459 rows inserted in BGGODBC.                

Barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tliottaCommented:
insert into bggodbc  
   with t1 as (SELECT USER as profile, serverid, MAX(DATETIME) as maxdate
           FROM odbclog/odbclog
           GROUP BY USER, serverid
        )
   select system, profile, maxdate, reason from odbclog/odbclog AS t2 , t1
       where t1.profile = t2.USER and t1.serverid = t2.serverid and t1.maxdate = t2.DATETIME

Hard to say how close the statement above is. It's more like what I was thinking originally, but now be off the mark. I might have really messed it up while trying to apply all of your table and column names as well as the logic in your example statement.

I don't quite grasp the logic that you show, particularly because I don't see where you use serverid anywhere except in the GROUP BY clause. But your final line seems to indicate that you did an actual copy/paste from a working example. I'm also not clear on why reason is in the ORDER BY clause. (Nor MAXDATE, though you might want the ordering in the output file for some purpose.)

Tom

0
bggauthAuthor Commented:
Tom,

We kept the serverid out of the logic as it's really not needed due to being the same on all records.
Yes I did a copy/paste from a working example.

After seeing preliminary results, I did want it sorted a particular way.I  tried many different combinations in the order by statement and the combination above worked best.  The reason field really does not need to be there, I just didn't want the 'reasons' lumped together.
The reason field is simply stating the result of the attemped access.   1 =Allowed   0 = Not allowed  X = not enrolled at all.

I tried your statement Tom but it returned no records... "No data selected for output"

Barry
0
Gary PattersonVP Technology / Senior Consultant Commented:
Hmm.  I tested this when I created it and it works fine when I run it.  Sure you got the statement right?

- Gary
0
bggauthAuthor Commented:
I just did a copy/past of your statement Gary.

select distinct *
from odbclog
where (user || char(datetime)) in (select user || char(max(datetime)) from odbclog
group by user)

Only 1 record is returned however it is the more recent entry.
0
Gary PattersonVP Technology / Senior Consultant Commented:
That should only happen if there was only one user in the file you are querying.

No real point troubleshooting further, though, if you already have the SQL you need.

- Gary

0
bggauthAuthor Commented:
There are 460 unique user names in that file.
The most recent 5 records have different user names.

I would agree on the troubleshooting aspect unless you wish to.

Barry
0
tliottaCommented:
And even though I ran a test of my statement, I didn't test it after pasting your variable names into it. I see that I left USER as an unquoted name. Since that's a SQL reserved word, it needs the quotes just like the example that you received from your co-worker.

Tom
0
bggauthAuthor Commented:
Tom,

Your statement does appear to work fine when surrounding USER with quotes.

Barry
0
Gary PattersonVP Technology / Senior Consultant Commented:
Ah - USER is a reserved word!  In my test file I used "USR" to avoid it,and translated the SQL statement here to remain consistent with his field names.

Thanks, Tom!  I hate mysteries.

- Gary
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.