Consider the following SQL

SELECT * FROM A-LF LEFT JOIN B-PF ON (A-LF.pkey = B-PF.pkey)
LEFT JOIN C-PF ON (B-PF.pkey=C-PF.pkey)

Where: A-LF is the logical file created and compiled from DDS source. B-PF and C-PF are the physical files on the database

he above SQL will be slow based upon the logical file size, the reason being running the above SQL will create the copy of the file in the data library.

It is always recommended to run the complete SQL by replacing the DDS built logical A-LF with the equivalent explicit SQL or use the views instead.. This basically gave the following equivalent SQL:

SELECT * FROM (SELECT * FROM A-PF) LEFT JOIN B-PF ON (A-LF.pkey = B-PF.pkey)
LEFT JOIN C-PF ON (B-PF.pkey=C-PF.pkey)

or

SELECT * FROM A-LV LEFT JOIN B-PF ON (A-LF.pkey = B-PF.pkey)
LEFT JOIN C-PF ON (B-PF.pkey=C-PF.pkey)

where A-LV is created using Create View command.

The above queries will not create the copy of the file and will be a lot faster.

So in summary avoid using DDS built logicals in SQL joins.

Hope this is useful.

-NKJ

NitiN Kumar Jain

Nitin works in an IT MNC professionally but blogs and owns NKJ Live. He is also the co-owner of a professional start-up ARGHAM BYTES

Website - Twitter - Facebook - More Posts