PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 12/12/2014 10:39 AM by  Woozy
Can I leverage relations in COBOL query?
 7 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Woozy
Private
Private
Veteran Member
(3487 points)
Veteran Member
Posts:707


Send Message:

--
12/10/2014 5:06 PM

    Is table relationship data available after performing a COBOL find?  

    For example, if I do an 840-FIND-EMPSET1, is there a way to retrieve data from a related table (i.e PAEMPLOYEE) without having to do an 840-FIND-PAESET1?  Something like EMP-PAEMPLOYEE-PEM-LOCAT-CODE

    I don't think this is possible, but I'd be very happy to be wrong.

    Thanks!

    Kelly

    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    12/11/2014 9:56 AM
    Hey Kelly.... you will need to do another find for the PAEMPLOYEE table using the example you describe above. In that example, I usually set my code up to find the PAEMPLOYEE entry right after the EMPLOYEE table... especially since you are using the same key values.

    Jim
    Woozy
    Private
    Private
    Veteran Member
    (3487 points)
    Veteran Member
    Posts:707


    Send Message:

    --
    12/11/2014 10:14 AM
    Thanks Jim. That's what I did, but I thought I'd check to see if there was a shortcut.

    I used the PAEMPLOYEE for simplicity in asking the question, but in this case I'm actually doing a FIND-NEXT loop with PRTIME and need to query PAYMASTR for each PRTIME record to get the Deduction Cycle. I just added the 840-FIND-PYMSET1 within the PERFORM loop. As long as I reset the PYM DB-x values from the PRT with each loop it should work just fine, right?

     
    MOVE ZZVACA-COMPANY         TO DB-COMPANY                
    MOVE ZZVACA-EMPLOYEE        TO DB-EMPLOYEE               
    MOVE ZZVACA-FR-DATE         TO DB-PER-END-DATE           
                                                             
    INITIALIZE ZZVACA-LAST-PPE-PROC-DATE                     
    PERFORM 850-FIND-NLT-PRTSET3                             
                                                             
    IF (PRTIME-FOUND)                                        
        PERFORM                                              
        UNTIL (PRTIME-NOTFOUND)                              
        OR (PRT-COMPANY NOT = ZZVACA-COMPANY)                
        OR (PRT-EMPLOYEE NOT = ZZVACA-EMPLOYEE)              
        OR ((ZZVACA-TO-DATE-NBR NOT = ZEROES)                
        AND (PRT-PER-END-DATE > ZZVACA-TO-DATE-NBR))         
            RFC 1025329 - 12/11/2014 - Kelly Meade           
            Get the deduction cycle and skip if > 7          
            in order to skip special payments                
            MOVE PRT-COMPANY             TO DB-COMPANY       
            MOVE PRT-EMPLOYEE            TO DB-EMPLOYEE      
            MOVE PRT-CHECK-ID            TO DB-CHECK-ID      
            PERFORM 840-FIND-PYMSET1                         
                                                             
            IF (PRT-PER-END-DATE > ZZVACA-LAST-PPE-PROC-DATE)
            AND (PYM-DED-CYCLE-NBR < 7)                      
                MOVE PRT-PER-END-DATE                        
                               TO ZZVACA-LAST-PPE-PROC-DATE  
            END-IF                                           
            PERFORM 860-FIND-NEXT-PRTSET3                    
        END-PERFORM                                          
    END-IF                                                       
    
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    12/11/2014 12:55 PM
    OK... Figured it was just and example...

    I don't really like using the -NLT- and avoid them when I can. When you go after your PAYMASTER, why don't you use index filtering instead. Would be a lot faster as well.
    John Henley
    Private
    Private
    Senior Member
    (9629 points)
    Senior Member
    Posts:3227


    Send Message:

    --
    12/11/2014 1:30 PM
    @jaherb: Assuming he's using period end date on his input form, he's going about it in the correct way; if he drove it from PAYMASTR there is not an index that includes PER-END-DATE.
    Thanks for using the LawsonGuru.com forums!
    John
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    12/11/2014 2:29 PM
    @John... That is the beauty of Index Filtering... the index does not have to have the field defined in it. He can limit his search time by using the index filter where he can add PER-END-DATE.
    John Henley
    Private
    Private
    Senior Member
    (9629 points)
    Senior Member
    Posts:3227


    Send Message:

    --
    12/11/2014 4:38 PM
    @jaherb, while I'll agree that index filters might increase performance, they're not a silver bullet, and depending on your architecture and what you're doing, it also might be slower...

    The way index filters are implemented is that a set of filter fields/values are passed from the COBOL program to the API, which translates them into a WHERE clause that is sent to the database. Where it differs from "traditional" Lawson 4GL is that the filter is done in the database rather than using IF statements in the COBOL. If the fields included in the index filter *really* exist in the index, that's great. But if they don't, the database is essentially using index whatever it can--but has to use a scan when the desired filter isn't part of the index. Just like if you do it directly in a SQL query.

    Where index filters can help performance is in transfering the data between the database and the COBOL program. While traditional FIND-XXXSET1/NEXT logic transfers the data row-by-row from the database to the API to the COBOL program, the index filter API receives an entire resultset from the database, and then loops thru and returns records row-by-row to the COBOL program. So, you remove round-tripping to the database for long-running loops, and theoretically it will be faster.

    So, when applied to Woozy's question...

    Based on his code, he's using a pretty narrow index (company/employee/per-end-date) that already exists on the PRTIME detail table, and using FINDs to access the PAYMASTR header records. So he's already limiting the number of records being retrieved from PRTIME to only those for a given employee and time period, and the APIs are smart enough to cache the PAYMASTR FINDs.

    Changing that use index filter on PAYMASTR to return only those checks for a particular per end date, and the doing another index filter on PRTIME for the time records associated with each check on that particular date would actually increase the physical number of records read from PAYMASTR, since it would use the index with COMPANY/EMPLOYEE to look for a given employee's payments, but would then have to use a scan on those records to filter by period end date, since that's not in the index.

    That's where it gets interesting. Even though more physical reads are done, with index filters it would result in fewer round trips to the database. So, the answer is of course, it depends....
    Thanks for using the LawsonGuru.com forums!
    John
    Woozy
    Private
    Private
    Veteran Member
    (3487 points)
    Veteran Member
    Posts:707


    Send Message:

    --
    12/12/2014 10:39 AM
    Thanks Jim and John. Very interesting information. I do use index filtering occasionally, but it didn't occur to me in this case. I always have to dig up an example, because I can never remember how to do it. For what I'm doing in this particular case, performance isn't an issue, and I was just "adding" the PAYMASTR piece into an existing piece of code so it seemed to be the simplist solution.

    However, if I'm understanding Jim correctly, my other option would have been to do the PAYMASTR query first using the index filtering, and then do looping targeted finds against PRTIME to pull that detail? I can see that - particularly since PRTIME can be so gigantic. I'll keep that in mind for the future.

    Thanks again!
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    You are not authorized to post a reply.