Follow up on DB2 Tip 17.

Post Reply
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Follow up on DB2 Tip 17.

Post by Anuj Dhawan »

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: 34
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune

Re: Follow up on DB2 Tip 17.

Post by Quasar Chunawala »

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: 34
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune

Re: Follow up on DB2 Tip 17.

Post by Quasar Chunawala »

Do you need me to put the follow-on note there?
zprogrammer
Global Moderator
Global Moderator
Posts: 588
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars

Re: Follow up on DB2 Tip 17.

Post by zprogrammer »

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: 34
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune

Re: Follow up on DB2 Tip 17.

Post by Quasar Chunawala »

While it may seem common-sense, I found a few old programs at my shop that actually use programmatic joins.
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: Follow up on DB2 Tip 17.

Post by Anuj Dhawan »

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.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “Follow up on Tips”