Data access paths

Edit online

Data can be accessed in or across hierarchical boundaries. For the DBD shown , all of the SELECT statements that are shown in this section are valid.

The database representation of the DBD shown can be combined with the PSB and divided into specific data paths.

Figure 1. Data access path 1

This figure shows the the hierarchical relationships for DI21PART and data access path 1.

The following SELECT statements are valid for the data access path that is shown in Figure 1:


SELECT * FROM IMS_PARTROOT
SELECT * FROM IMS_PARTROOT P, IMS_STANINFO C 
  WHERE P.CHILD_ID = C.PARENT_ID  
  AND P.PARTKEY='02AB960C11'
Figure 2. Data access path 2

This figure shows the the hierarchical relationships for DI21PART and data access path 2.

The following SELECT statements are valid for the data access path that is shown in Figure 2:

SELECT * FROM IMS_PARTROOT
SELECT * FROM IMS_STOKSTAT
SELECT * FROM IMS_CYCCOUNT
SELECT * FROM IMS_PARTROOT P, IMS_STOKSTAT C
  WHERE P.CHILD_ID = C.PARENT_ID  
  AND PARTKEY='02AB960C11'
SELECT * FROM IMS_PARTROOT P, IMS_STOKSTAT C1,
  IMS_CYCCOUNT C2
  WHERE P.CHILD_ID = C1.PARENT_ID 
  AND C1.CHILD_ID = C2.PARENT_ID
  AND PARTKEY='02AB960C11'
Figure 3. Data access path 3

This figure shows the the hierarchical relationships for DI21PART and data access path 3.

The following SELECT statements are valid for the data access path that is shown in Figure 3:

SELECT * FROM IMS_PARTROOT
SELECT * FROM IMS_STOKSTAT
SELECT * FROM IMS_BACKORDR
SELECT * IMS_PARTROOT P, IMS_STOKSTAT C
  WHERE  P.CHILD_ID = C.PARENT_ID 
  AND PARTKEY='02AB960C11'
SELECT * FROM IMS_PARTROOT P,
  IMS_STOKSTAT C1,IMS_BACKORDR C2
  WHERE P.CHILD_ID = C1.PARENT_ID 
  AND C1.CHILD_ID = C2.PARENT_ID
  AND PARTKEY=’02AB960C11’
The following statements are not valid because they produce a Cartesian product (or Cartesian join):
SELECT * FROM IMS_PARTROOT, IMS_STANINFO
SELECT * FROM IMS_PARTROOT, IMS_STOKSTAT,DI21PART.
  DFSSAM03_CYCCOUNT
Running a statement that produces a Cartesian product results in a 1002 error code.
Note: To select from two different tables, a WHERE clause must be specified.