unit 7. table expressions and recursive sqlread.pudn.com/downloads138/ebook/592377/cf134m70.pdf ·...

Instructor Guide

Unit 7. Table Expressions and Recursive SQL

What This Unit Is About

This unit provides information on how to code nested and commontable expressions and recursive SQL statements. Tableexpressions are compared to views, and some coding techniquesfor avoiding excessive recursion or looping for recursive SQL arediscussed.

What You Should Be Able to Do

After completing this unit, you should be able to:

Identify reasons for using table expressions and recursive SQL

Use nested and common table expressions

Identify the difference between views and table expressions

Code recursive SQL

Control the depth of recursion when coding recursive SQL

How You Will Check Your Progress

Accountability:

Unit CheckpointMachine Lab

Labs

Nested table expressionsCommon table expressionsRecursive SQL

References

SC09-2847 IBM DB2 UDB for OS/390 V6 SQL Reference

SC09-2847 IBM DB2 UDB for UNIX, Windows and OS/2

SC09-2848 Version 6 SQL Reference Vol 1 & 2

Unit 7. Table Expressions/Recursive SQL 7-1 Copyright IBM Corp. 1998, 2001Course materials may not be reproduced in whole or in part

without the prior written permission of IBM.

Instructor Guide

Figure 7-1. Objectives (CF137000)

Notes:

Some business problems that once required complex application programming orthe extensive use of views can now be solved with far less effort via tableexpressions and recursive SQL.

7-2 DB2 SQL for Experienced Users Copyright IBM Corp. 1998, 2001Course materials may not be reproduced in whole or in part


Instructor Guide

Instructor Notes:

Purpose — Identify the objectives for the unit.

Details — DB2 for OS/390, Versions 4, 5 and 6 supports only nested table expressions.DB2 UDB for UNIX, Windows and OS/2 and DB2 UDB for AS/400 supports all forms oftable expressions and recursive SQL shown in this chapter.

Additional Information — None.

Transition Statement — Let us start by defining when nested table expressions areuseful.



Instructor Guide



Instructor Guide

7.1 Table Expressions and Recursive SQL

Instructor Topic Introduction

What students will do — Use table expressions, common table expressions, andrecursive SQL.

How students will do it — Listen to the lecture and perform lab exercises.

What students will learn — The syntax and functionality of table expressions, commontable expressions, and recursive SQL.

How this will help students on their job — These SQL statements can eliminate theneed for writing programs or using views for complex problems.



Instructor Guide

Figure 7-2. Table Expressions (CF137010)

Notes:

Application programmers can use Table Expressions to define and use"pseudo-views" or "inline-views".

You do not have to create a regular view.

Regular views require you to create them and to control who can use them, whichcan be seen as negative. However, on the positive side, you can use a view tocreate a logical table that users cannot change.

You do not create Table Expressions separately and they are not documented inthe DB2 Catalog tables. You cannot control access to a table expression itself bygrant/revoke, the table expression is just defined in the query which uses it andthe access given on the underlaying tables/views is checked.

In the example, everything in the parentheses following the first keyword FROM isa table expression. The name of the table expression is S. The table expression isobtaining data from the EMPLOYEE table. For each employee, it determines thedecade in which the employee was hired and the employee's salary. The resultcolumns for the table expression are called HIREDECADE and SALARY.

The column HIREDECADE contains the decade, during which the employee washired. For example, 1970 - 9 means that the employee was hired between January



Instructor Guide

1, 1970 and December 31, 1979. The hiring decade is determined by extracting thefirst three digits from the ISO format of the hiring date and appending thecharacter string '0 - 9'. The column SALARY is the salary for the employee.

For each hiring decade, the outer query determines the minimum salary paid toemployees hired during this decade. The outer query obtains its data from thetable expression by grouping the rows returned by the table expression by hiringdecade and determining the minimum salary for each distinct hiring decade. Theappropriate columns of the final result table are called HIREDECADE andMINIMUM_SALARY.

On DB2 OS/390, the query of the example could not be done by immediatelyaccessing the EMPLOYEE table and not using a table expression. This is becausein DB2 OS/390 only column names can be specified in the GROUP BY clause, andnot expressions.

On DB2 UDB for UNIX, Windows and OS/2, an expression or a scalar function canbe used in the GROUP BY clause.

Nested table expressions are available on DB2 UDB UNIX, Windows and OS/2,DB2 UDB AS/400 and DB2 UDB OS/390.

Common tables expressions and recursive SQL are available on DB2 UDB forUNIX, Windows and OS/2.

Note: The AS clause is used for two different purposes in this example in order tocreate temporary names that can be referenced elsewhere in the query: It is usedto name the derived column HIREDECADE of the table expression and also toname the table expression itself. The columns of the table expression arereferenced by the outer query. The outer query uses the AS clause as well toname the calculated column containing the minimum salary for the hiring decade.



Instructor Guide

Instructor Notes:

Purpose — Introduce the syntax and functionality of table expressions.

Details

A few key points are identified in the student notes. Grouping cannot beperformed by the outer query if only table EMPLOYEE is used. The tableexpression allows the derived columns to be referenced as if they are columns of abase table.

Another benefit of table expressions is that evaluation of the expression iscompleted at statement execution. Host variables can be used within the tableexpression. This is not possible when creating views since views must becompletely defined to the catalog. The program can also assign a value to thehost variable before executing the statement.

Additional Information — Some people use the term "in-line view" when discussingtable expressions.

DB2 UDB for UNIX, Windows and OS/2. SQL extension to support table expressionsprovides partial support for the full SQL92 requirement.

Transition Statement — Table expressions can also be used in the FROM clause of ajoin.



Instructor Guide



Instructor Guide

Figure 7-3. Nested Table Expressions in Joins (Part 1) (CF137020)

Notes:

An operand of a join can be more complex than the name of a single table. Youcan use a fullselect in parentheses follwed by a correlation name. This is called anested table expressions.

In the example on the visual, we want to list, for each employee in the EMPLOYEEtable, his/her employee number, last name, hiring decade, salary, and theminimum salary being paid to any employee hired during the same decade. Thisis illustrated by the result table on the visual.

The result cannot be obtained by only using the EMPLOYEE table. It can becreated by means of an inner join of two table expressions. As indicated on thevisual, both table expressions must have a column HIREDECADE, and the innerjoin is performed by using the columns in the ON condition.

One of the table expressions (on the visual, the first one) is called E and mustprovide employee number, last name, salary, and hiring decade for all employees.The other table expression, called M, must provide the minimum salary for eachhiring decade. Joining the two table expressions by means of their HIREDECADEcolumns yields the desired result. The subsequent visual illustrates the result setsof the two table expressions.



Instructor Guide

Instructor Notes:

Purpose — Introduce the syntax and functionality of table expressions in joins.

Details — Follow these rules when writing nested table expressions:

Enclose the table expression in parentheses.

Give a correlation name to the table expression. You can use the correlation nameas the qualifier of a column name, just like any other table or view name.However, you cannot refer to the correlation name within the FROM clause thatdefines it.

Provide unique names for the columns of the result table that you reference. If youreference those columns, their names need to be unique.

Additional Information — A nested table expression is a table expression in the FROMclause of a SELECT statement. DB2 UDB OS/390 supports only nested tableexpressions and not common table expressions. DB2 UDB for UNIX, Windows andOS/2 and DB2 UDB for AS/400 support both nested table expressions and commontable expressions.

Transition Statement — The next visual illustrates the temporary result tables for thetable expressions.



Instructor Guide


Notes:

This visual illustrates the temporary result tables to be created by the subselects ofthe table expressions.

The first table expression, called E, should provide, for each employee, his/heremployee number, last name, salary, and hiring decade (column HIREDECADE).

The second table expression, called M, should supply, for each decade, theminimum salary being paid to employees hired during this decade. This isprecisely the same result table as for the example on page 7-6.

By joining the two tables, we will get the desired result.



Instructor Guide

Instructor Notes:

Purpose — Illustrate the temporary tables for the table expressions.

Details — All relevant information has been provided in the student notes.


Transition Statement — From the first example of this unit, we already know the SQLstatement for the temporary result table for table expression M. Thus, we only needto determine the SQL statement for table expression E. Then, we can plug the SQLstatements into the framework shown on the visual.



Instructor Guide


Notes:

The visual shows the SELECT statement for retrieving employee number, lastname, salary, and hiring decade for all employees.

The hiring decade is determined in the same was as for the first example of theunit to ensure that the HIREDATE columns of the two table expressions can beused for the inner join operation of the outer query. The hiring decade is derivedby appending the character string '0 - 9' to the first three digits of the year whenthe appropriate employee was hired.

An AS clause is used to give the derived column the name HIREDECADE.



Instructor Guide

Instructor Notes:

Purpose — Explain the SQL statement needed for table expression E.



Transition Statement — Now, that we know the SQL statements for both tableexpressions, we can complete the original problem. We just need to plug the two SQLstatements for the table expressions into the skeleton we have seen on the earliervisuals. This is illustrated on the next visual.



Instructor Guide


Notes:

Now, we can complete the original problem, that is, determine, for each employee,his/her employee number, last name, hiring decade, salary, and the minimumsalary being paid to employees of his/her hiring decade.

To complete the query, we must replace the boxes labeled subselect in thepreceding visuals by the SQL statements for the table expressions. The first boxmust be replaced by the SELECT statement for table expression E, shown on theprevious visual. The second box must be replaced by the SQL statement for tableexpression M, shown on page 7-6. For each hiring decade, this SQL statementdetermined the minimum salary paid to people hired during the respective decade.



Instructor Guide

Instructor Notes:

Purpose — Illustrate how the original query can be solved by plugging the tableexpressions into the skeletons shown on prior visuals.


Additional Information — Some students may ask you why table expression E is used,and not table EMPLOYEE by specifying the following ON condition:

ON SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9' = M.HIREDECADE

This is not possible because ON clauses do not allow you to specify such expressions.You can only use column names or the COALESCE/VALUE scalar function.

This demonstrates an advantage of the DB2 join syntax over the INNER JOIN syntax.With the DB2 join syntax, the solution could be formulated as follows:

SELECT E.EMPNO, E.LASTNAME, M.HIREDECADE, E.SALARY, M.MINIMUM_SALARYFROM

EMPLOYEE E,(

SELECT S.HIREDECADE, MIN(S.SALARY) AS MINIMUM_SALARYFROM

(SELECT SUBSTR(CHAR(HIREDATE,ISO),1,3)

CONCAT '0 - 9' AS HIREDECADE, SALARYFROM EMPLOYEE

) AS SGROUP BY S.HIREDECADE

) AS MWHERE SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9'

= M.HIREDECADE

Transition Statement — Structuring the SQL statement as we have done on the visualhelps to understand how the SQL statement is developed and how the result isderived. Nevertheless, the SQL statement is fairly complex because the tableexpressions are part of the FROM clause. Perhaps, you are wondering whether thequery can be made more comprehensible. Yes, there is an easier way: common tableexpressions.



Instructor Guide

Figure 7-7. Common Table Expressions (CF137060)

Notes:

The table expressions we have seen so far created temporary result tables withinthe FROM clause of an outer query. These table expressions could not bereferenced elsewhere in the query, although their result columns could.

Common table expressions can be referenced elsewhere in the query, even byother common table expressions within the same query. They are introduced bythe keyword WITH and occur at the beginning of the query, not within the FROM ofthe outer query. They are separated from each other by commas. Every referenceto a specific common table expression within the same query uses the same resultset.

The example on the visual reformulates the query that we used in the previousexample, that is, the example for nested table expressions. It uses common tableexpressions which are named E and M as the nested table expressions werebefore.

The first common table expression is the SQL statement for table expression Ewhich determines employee number, last name, salary, and hiring decade for allemployees of the EMPLOYEE table. The common table expression is again calledE. The columns of the associated result table are those named in the SELECTstatement.



Instructor Guide

Although the second table expression looks different, it provides the same resultas the SQL statement for table expression M of the previous query: for the variousdecades, it determines the minimum salary being paid to the employees hiredduring the appropriate decade. The basic differences are:

− The SELECT statement now uses common table expression E, defined in front ofcommon table expression M, instead of table EMPLOYEE.

− The columns of the common table expression are named by specifying theirnames in parentheses following the name of the common table expression.This is the same technique as naming the columns of a view. No AS clause isneeded for the calculated column in the SELECT statement.

As a main select, the former outer query follows the common table expressions.Since it now can refer to the common table expressions, it becomes very easy.



Instructor Guide

Instructor Notes:

Purpose — Explain the purpose and syntax of common table expressions.

Details — The example shows the solution to an application requirement that issomewhat common: listing aggregate information on the same output row as detailedinformation. In the past, this sort of problem would generally have been handled withviews. In fact, this problem could also have been solved with views, as follows:

CREATE VIEW E (EMPNO, LASTNAME, SALARY, HIREDECADE)AS SELECT EMPNO, LASTNAME, SALARY,

SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9'FROM EMPLOYEE;

CREATE VIEW M (HIREDECADE, MINIMUM_SALARY)AS SELECT HIREDECADE, MIN(SALARY)

FROM EGROUP BY HIREDECADE;

SELECT E.EMPNO, E.LASTNAME, E.DECADE, E.SALARY, M.MINIMUM_SALARYFROM E INNER JOIN MWHERE E.HIREDECADE = M HIREDECADE;

Remember that, if views are used, each view needs to be defined and, then, access toit has to be granted. This can be a tedious procedure if the solution requires lots ofviews.

Make sure that students understand that the common table expression M is based onthe common table expression E. This is by no means a requirement for usingcommon table expressions, but is certainly possible and useful.

Additional Information — The SQL Reference provides information concerning"exposed" names.

DB2's SQL extension to support common table expressions partially supports a fullSQL92 requirement.

Transition Statement — Recursive SQL is a special use of common table expressions.



Instructor Guide



Instructor Guide

Figure 7-8. Recursive SQL (CF137070)

Notes:

Recursive SQL is used to work on tables that contain component breakdownswhere each component is broken down into subcomponents and eachsubcomponent is broken down again into sub-subcomponents, etc. Problemsinvolving these kinds of tables are often called "Bill of Materials" problems. Atable that represented the parts in a computer would be an example of a Bill ofMaterials: the major components, the monitor, system unit, and printer, all containsubassemblies like the hard drive, the mother board, and the print head, each ofwhich is composed of other subassemblies, etc., etc.

Recursive SQL involves defining a common table expression that references itself.The common table expression consists of two distinct components, an initializationselect and an iterative select . The initialization select is the first SELECT in thetable expression and the iterative select is the second SELECT in the tableexpression. The iterative select is combined with the initialization select by meansof UNION ALL.

The recursive common table expression in the example is named RPL . It isdefined within the parentheses.



Instructor Guide

The common table expression in a recursive SQL statement is followed by a mainselect . The main select identifies the columns which are obtained from the resultset of the common table expression.

The example on the visual builds a final result set that identifies all the parts andsubparts needed to build Part 01 (WHERE clause of initialization select) in a partstable called PARTLIST. We will see the PARTLIST table when stepping through thevarious "phases" by means of the subsequent visuals.



Instructor Guide

Instructor Notes:

Purpose — Define the syntax and components of a recursive SQL statement.

Details — This visual is intended to illustrate the concept of recursive SQL. Youshould highlight the components of the recursive SQL statement identified in thestudent notes. However, do not attempt to make the students understand thecomplete concept based on this introductory visual. After identifying the componentsof a recursive SQL statement, move on to the next set of visuals which will explain thesteps and iterations performed when executing a recursive SQL statement.

Note: If you have a second overhead projector, you can leave the current visual upon the second overhead projector.

Additional Information — Recursive SQL statements can also be used for courseprerequisites and airline connections.

Given a table of courses containing course codes, course names, andprerequisites, determine all the courses which are a prerequisite to course S6898.

Course Table

Recursive SQL Statement

WITHPREREQS (level, ccode, cname, pcode) AS

(SELECT 0, root.course_code, root.course_name, root.prereq_code

FROM courses rootWHERE root.course_code = 'S6898'

UNION ALLSELECT parent.level + 1, child.course_code, child.course_name,

child.prereq_codeFROM PREREQS parent, courses childWHERE parent.pcode = child.course_code

)SELECT level, ccode, cname, pcode

FROM PREREQSORDER BY level, ccode;

COURSE_CODE COURSE_NAME PREREQ_CODECF03 DB2 FAMILY FUNDAMENTALS

CF12 DB2 SQL WORKSHOP CF03

CF13 SQL ADVANCED WORKSHOP CF12

S9081 DB2 FOR OS/390 PROGRAMMING CF13

S6898 DB2 FOR OS/390 DATABASEADMINISTRATION

CF13



Instructor Guide

Statement Output

LEVEL CCODE CNAME PCODE----------- ----- --------------------------------------- -----SQL0347W The recursive common table expression "USERID.PREREQS" maycontain an infinite loop. SQLSTATE=01605

0 S6898 DB2 FOR OS/390 DATABASE ADMINISTRATION CF131 CF13 SQL ADVANCED WORKSHOP CF122 CF12 DB2 SQL WORKSHOP CF033 CF03 DB2 FAMILY FUNDAMENTALS -

Given a table of airline connections containing an originating airport, a destinationairport, and a distance, determine all of the places you can go to and how distanteach destination is from the originating point. (You can find an example like this inthe UDB Certification Guide.)

Transition Statement — The next visual shows the PARTLIST table and illustrates whathappens as the consequence of the initialization select.



Instructor Guide

Figure 7-9. Recursive SQL - Initialization Select (CF137080)

Notes:

The initialization select is executed only once. In the example, it reads thePARTLIST table.

The WHERE clause of the initialization select controls the starting point of therecursion. In the example, the starting point is all rows with a part number of '01'.

The right-hand side of the visual displays the four rows placed in the temporarytable RPL as the consequence of the initialization select. Parts 02, 03, 04, and 06are the assemblies that directly make up Part 01. The first column (PART) of theinterim result identifies the major part. The second column (SUBPART) identifiesthe subparts that make up the major part. The third column (QUANTITY) identifiesthe quantity of the subpart needed to construct one complete major part. Forexample, it takes three units of Part 06 to construct Part 01.



Instructor Guide

Instructor Notes:

Purpose — Describe the result of the initialization select.

Details — Mention that the SELECT statement on the top of the visual is only theinitialization select of the previous visual. If you have a second overhead projectoravailable, you can leave the complete recursive SQL statement up on this projector.


Transition Statement — The result of the initialization select is used for the recursivecycle. Let us see what happens as a consequence of the first iteration.



Instructor Guide

Figure 7-10. Recursive SQL - First Iteration (CF137090)

Notes:

Unless it is limited by control variables, the iterative select is executed until allsubparts of all parts have been broken down into their subparts, no matter howmany repetitions are required. In our example, there are no control variables sothe iteration will continue until all parts are completely resolved.

Note: It is very easy to write a recursive SQL statement incorrectly and initiate aninfinite loop. Control variables are very useful for limiting the number of iterationsand are discussed later in this unit.

The iterative select in the example was the part of the recursive SQL statementbetween the UNION ALL and the parenthesis that closed the common tableexpression named RPL. Only the iterative select is repeated on this visual.

During the first iteration, each row from the initialization select is joined to all rowsin the PARTLIST table that meet the join criteria. The result rows are added to thetemporary table RPL. The rows that are added to RPL indicate that Parts 05through 09 and 12 through 13 make up the parts returned by the initializationselect:

Part 02 consists of Parts 05 and 06;Part 03 consists of Part 07;



Instructor Guide

Part 04 consists of Parts 08 and 09;and Part 06 consists of Parts 12 and 13.



Instructor Guide

Instructor Notes:

Purpose — Illustrate the function of the recursive cycle. Explain the result of the firstiteration.

Details — Ensure that the transition from the prior visual is clear. The white rows inRPL are the rows resulting from the initialization select. The subparts from which theyare assembled are shaded in the PARTLIST table. The first iteration adds thesesubparts to the RPL temporary table. The appropriate rows in the RPL table areshaded as well.


Transition Statement — The rows added from the first iteration are used to drive thesecond iteration.



Instructor Guide



Instructor Guide

Figure 7-11. Recursive SQL - Second Iteration (CF137100)

Notes:

The second iteration joins the rows added by the first iteration to the PARTLISTtable. The result rows of the second iteration are again added to RPL. Thesecond iteration indicates that Part 05 consists of Parts 10 and 11, Part 06 consistsof Parts 12 and 13, and that part 07 consists of parts 12 and 14.

Since there are no correspondences for the subparts of Parts 04 and 06, added bythe first iteration, in the PARTLIST table, parts are not added for them to the RPLtemporary table.

Note: RPL now contains two occurrences each of the rows that define thesubparts of Part 06, namely, Parts 12 and 13. The first occurrence of these rowswas contributed by the first iteration and the second occurrence of these rowscame from the second iteration. The UNION ALL preceding the iterative selectprevents the duplicate removal.

The recursion will not yield additional rows after the second iteration becausethere are no further subparts for the parts added by the second iteration.However, if the PARTLIST table contained additional levels of subparts, therecursion would continue since the current example does not limit the depth of therecursion.



Instructor Guide

Instructor Notes:

Purpose — Continue the example showing the growth of the intermediate result setwithin the recursive cycle.

Details — At this point in time, the students should be gaining an understanding ofhow recursion works. Ask the students if they understand the function at this point.


Transition Statement — Now, let us run the main select against the result of thecommon table expression.



Instructor Guide

Figure 7-12. Main Select (CF137110)

Notes:

After the recursive common table expression has been evaluated completely, themain select is evaluated. The main select references the result of RPL, thecommon table expression.

The main select summarizes the total quantity of all parts needed to build Part 01.The grouping and the SUM() function ensure that the quantities of the respectivesubparts of Part 06 are added together. In other words, the two rows for Part 06,Subpart 12, will be combined to make a single row. So will the two rows for Part06, Subpart 13. A user who wishes to verify that the warehouse contains enoughof each of the components needed to make Part 01 can execute this query and,then, check existing stocks against the result of the query.



Instructor Guide

Instructor Notes:

Purpose — Complete the example by applying the main select against the results ofthe recursive cycle.



Transition Statement — The nature of recursive SQL makes it prone to infinite cycles,but you can control the number of recursions as illustrated by the next visual.



Instructor Guide

Figure 7-13. Controlling Depth of Recursion (Part 1) (CF137120)

Notes:

Recursion normally continues until all parts have been resolved into theircomponents. However, the depth of the recursion can be controlled by simulatingthe addition of a control column to the table.

This control column is initialized arbitrarily to 0 in the initialization select andincreased by one on every execution of the iterative select. A condition in theWHERE clause of the iterative select is used to ensure that the iteration onlycontinues for a fixed number of levels.

In the example, the control column is named LEVEL. It is set to an integer value of0 in the initialization select. The WHERE clause of the initialization selectdetermines the value in the PART column with which the table expression begins.This time, we are interested in the breakdown of Part 00, but we could have startedwith any part number we were interested in. The initial value of LEVEL would stillbe 0, regardless of the starting part number.

The iterative select increments the LEVEL value by adding 1 on each iteration. Thecondition

PARENT.LEVEL < 2



Instructor Guide

in the WHERE clause of the iterative select is used to limit the number of iterations:simply set the constant to the number of iterations which are desired.

The main select displays the result of the table expression. The LEVEL column inthe final result makes the origin of each result row clear: rows that came from theinitialization select have a level of 0, rows from the first iteration have a level of 1,rows from the second iteration have a level of 2, and so on. The ORDER BY putsthe result in a convenient sequence.

Note: LEVEL is not a column of table PARTLIST. It does not have to be added totable PARTLIST via an ALTER TABLE statement. It is a "virtual" column created bythe SQL statement.

The actual result of the recursive SQL statement is illustrated on the next visual.



Instructor Guide

Instructor Notes:

Purpose — Show the students how some small SQL changes can let them control thedepth of recursion.

Details — Both the starting point of the recursion and the depth of the recursion canbe controlled:

The starting point of the recursion is set in the WHERE clause of the initializationselect. In the example, ROOT.PART = '00' says that we should begin with Part 00,but we could have started with any part number at all.

The depth of recursion, which is the same thing as the number of iterations, ismanaged by a "virtual" control variable. In the example, it is called LEVEL. It isset to 0 in the initialization select, regardless of the starting point for the recursion.LEVEL is increased in the SELECT clause of the iterative select. A predicate in theWHERE clause of the iterative select is used to limit the number of iterations. Inthe example, PARENT.LEVEL < 2 indicates that we want to perform only twoiterations.

Additional Information — See SQL Reference.

Transition Statement — The next visual illustrates the result of the limited recursionfor Part 00.



Instructor Guide



Instructor Guide

Figure 7-14. Controlling Depth of Recursion (Part 2) (CF137130)

Notes:

This visual displays the result of the breakdown for Part 00 if the recursion islimited to two iterations.

Subparts 05 and 06 of Part 02 and Subpart 07 of Part 03 could be furtherdecomposed if the number of iterations was not limited to two.



Instructor Guide

Instructor Notes:

Purpose — Illustrate the result of the limited recursion for Part 00.



Transition Statement — Let us consider a few suggestions that will minimize the riskof endless loops caused by recursive SQL statements.



Instructor Guide

Figure 7-15. Recursive SQL - Recommendations (CF137140)

Notes:

Recursive SQL is cyclical by definition. This means that it is easy to cause loops ifthe SQL is coded incorrectly or if the data itself is cyclical. For example, if the joinin the iterative select in the earlier examples was coded as

PARENT.SUBPART = CHILD.SUBPART

there would be an infinite recursion if there was even one row where the Part andSubpart values were the same. By the same token, a loop can occur if the datawas illogical. For example, if the PARTLIST table had a row where the Part was 05and the Subpart was 01, a loop would occur. To prevent this sort of problem, deskcheck all recursive SQL. Also, test it against small tables before implementing it inproduction.

Any recursive SQL statement that does not use a control variable will receive anSQL warning (SQL0347W in UDB). Although this is not a serious problem, you canuse techniques shown on the preceding visuals to avoid it.



Instructor Guide

Instructor Notes:

Purpose — Caution the students against uncontrolled usage of recursive SQL.


Additional Information — See SQL Reference.

Transition Statement — Now, let us perform the checkpoint quiz for this unit.



Instructor Guide



Instructor Guide

Checkpoint

Exercise — Unit 7 Checkpoint

T F 1. Table expressions have many similarities to views.

Correct Answer

1. True

Ref: Topic on table expressions

2. List three ways in which table expressions can be moreconvenient than views.

Correct Answer

2. Not necessary to CREATE table expressions.

Table expressions can use host variables.

Not necessary to grant access to table expressions.

Ref: Topic on table expressions

T F 3. If your data is invalid and you code a recursive SQLstatement, it is possible to start an infinite loop.

Correct Answer

3. True

Ref: Recursive SQL topic.

4. What key element in the syntax of an SQL statement makes itrecursive?



Instructor Guide

Correct Answer

4. It contains a common table expression that refers to itself

within its definition.

Ref: Table expressions and recursive SQL topic. Other parts ofthe syntax are also important, such as the UNION ALL, but theprovided answer is probably the "key" component.



Instructor Guide



Instructor Guide

Figure 7-16. Summary (CF137160)

Notes:



Instructor Guide

Instructor Notes:

Purpose — Review the unit objectives.

Details — Question the students on what they have learned.


Transition Statement — Now, you have the opportunity to use your knowledge of tableexpressions and recursive SQL in a lab.



Instructor Guide



unit 7. table expressions and recursive sqlread.pudn.com/downloads138/ebook/592377/cf134m70.pdf ·...

Documents