Follow up on DB2 Tip 17.

Previous topicNext topic
User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2618
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Follow up on DB2 Tip 17.

Post by Anuj Dhawan » Wed Mar 12, 2014 12:49 pm

Subject: DB2 Tips.
Quasar Chunawala wrote:17. An SQL join on most occasions out-performs a programmatic join. Programmatic joins are bad and should be avoided at all times.

Programmatic join

Code: Select all

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
           EXEC SQL
             OPEN C1
           END-EXEC
       
           PERFORM UNTIL END-OF-C1
             EXEC SQL
                FETCH C1 INTO :HV-A, :HV-B, :HV-C
             END-EXEC
             EVALUATE SQLCODE
                WHEN 0
                   PERFORM 1000-OPEN-C2  THRU 1000-EXIT
                   PERFORM 2000-FETCH-C2 THRU 2000-EXIT
                   UNTIL END-OF-C2
                   PERFORM 3000-CLOSE-C2 THRU 3000-EXIT
                WHEN +100
                   MOVE SPACES TO :HV-A, :HV-B, :HV-C
                   SET END-OF-C1 TO TRUE
                WHEN OTHER
                    PERFORM 9000-PROCESS-ERROR THRU 9000-EXIT
              END-EVALUATE
           END-PERFORM

           EXEC SQL
              CLOSE C1
           END-EXEC
Hi Quasar,

I might have not understood it well, however, you show a COBOL code - which is neither a "programmatic join" nor a "SQL-Join" -- so what does it convey. Possibly I've misunderstood. Please guide.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.


Quasar Chunawala
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: Follow up on DB2 Tip 17.

Post by Quasar Chunawala » Wed Mar 12, 2014 8:51 pm

Anuj,

Let's say, one had to produce a report of Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME) earning more than 5k and their respective JOB_HISTORY(EMPLOYEE_ID,JOB_ID, JOB_NAME). One algorithm might be, for each candidate row of Employee, (i) Retrieve the set of job-histories (ii) Loop through the job-history records for the given employee(iii) Clean up. You could call this a programmatic join.

Pseudo-Code

Code: Select all

open employee-csr
Loop until End-Of-Employees
    Move E.Employee-Id To H.Employee-id
    open Job-History-csr
    Loop until End-of-job-history-rows
        Display h.job_name
     end
end
Another algorithm would be take an SQL join between EMPLOYEES and JOB_HISTORY.

Pseudo-Code

Code: Select all

open employee-job-history-csr
Loop until End-Of-cursor
        Display h.job_name
end
The trade-off here is, you require extra programming time for coding one/multiple SQL statements to perform SQL joins versus the diminished performance of
programmatic joins. In almost every single situation in which a SQL join can be coded instead of a programmatic join, found the SQL join out-performs the programmatic join(while I worked on an IT transformation project).

Hope it conveys the point!




Quasar Chunawala
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: Follow up on DB2 Tip 17.

Post by Quasar Chunawala » Wed Mar 12, 2014 9:07 pm

Do you need me to put the follow-on note there?




zprogrammer
Global Moderator
Global Moderator
Posts: 604
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars
Zodiac: Libra

Re: Follow up on DB2 Tip 17.

Post by zprogrammer » Wed Mar 12, 2014 9:46 pm

Ofcourse I feel SQL join is better by any mean as you atleast reduce the number of DB2 calls

Also as things in DB2 evolved there came the Multi row fetch which even reduces the number of calls further down


zprogrammer


Quasar Chunawala
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: Follow up on DB2 Tip 17.

Post by Quasar Chunawala » Wed Mar 12, 2014 11:04 pm

While it may seem common-sense, I found a few old programs at my shop that actually use programmatic joins.



User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2618
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: Follow up on DB2 Tip 17.

Post by Anuj Dhawan » Thu Mar 13, 2014 9:40 am

Quasar Chunawala wrote:Do you need me to put the follow-on note there?
No Quasar, that's not needed - we've this separate section for the follow-ups, to keep the "Tips Section" clean.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

Previous topicNext topic

Return to “Follow up on Tips”