Mainframe, MVS and zOS Discussion

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.

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 Another algorithm would be take an SQL join between EMPLOYEES and JOB_HISTORY.

Pseudo-Code 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!

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

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

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

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.