ALLBASE/SQL Reference Manual (36216-90216)

434 Chapter11
SQL Statements E - R
GENPLAN
2. SQL statement simulating use of host variables in an application for the following
query taken from an application:
EXEC SQL SELECT PartName, VendorNumber, UnitPrice
INTO :PartName, :VendorNumber, :UnitPrice
FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
WHERE p.PartNumber = sp.PartNumber
AND p.PartNumber = :PartNumber
Remove INTO clause when placing the statement into GENPLAN.
Generate the plan in ISQL:
Define input host variable names and compatible SQL data types in WITH clause.
isql=> GENPLAN WITH (PartNumber char(16)) FOR
> SELECT PartName, VendorNumber, UnitPrice
> FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
> WHERE p.PartNumber = sp.PartNumber
> AND p.PartNumber = :PartNumber;
Display the plan:
isql=> SELECT * FROM System.Plan;
SELECT * FROM System.Plan;
-----------+-----------+-----------+--------------------+---------------
QUERYBLOCK |STEP |LEVEL |OPERATION |TABLENAME
-----------+-----------+-----------+--------------------+---------------
1| 1| 2|index scan |PARTS
1| 2| 2|serial scan |SUPPLYPRICE
1| 3| 1|nestedloop join |
------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], *lt;n>, or e[nd] >r
+--------------------+----------------
|OWNER |INDEXNAME
+--------------------+----------------
|PURCHDB |PARTNUMINDEX
|PURCHDB |
| |
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >e
3. Example of GENPLAN for a MODULE SECTION.
GENPLAN FOR MODULE SECTION MyModule(10);