Can I leverage relations in COBOL query?

 7 Replies
 1 Subscribed to this topic
 17 Subscribed to this forum
Sort:
Author
Messages
Woozy
Veteran Member
Posts: 709
Veteran Member

    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
    Veteran Member
    Posts: 164
    Veteran Member
      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
      Veteran Member
      Posts: 709
      Veteran Member
        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
        Veteran Member
        Posts: 164
        Veteran Member
          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
          Posts: 3353
            @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
            Veteran Member
            Posts: 164
            Veteran Member
              @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
              Posts: 3353
                @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
                Veteran Member
                Posts: 709
                Veteran Member
                  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