Re: Top 3 Reasons to Avoid Mixing SQL with QTEMP

Hi Peter

Thanks for pointing CTE out (guess I missed it)

As to the performance hit due to lack of indices on the QTEMP temporary
tables:

Well, the usual scenario here of temp SQL tables in QTEMP is:

a. Create some (1-4) temp tables in QTEMP using several SQL statements
b. Join/format/ those into an amalgamated table (for a report/Excel etc.)

The performance issue Kent refers to pertains (I think) to step b.

As those tables are relatively small (a few thousand records at the most) ,
lacking indices has a negligible impact (to my mind) since the SQL
optimizer tends
to use table scan for small tables anyway.

Gad





Hi Gad,

The With clause is a Common Table Expression, which Kent does mention:

"These physical copies of the data can often be avoided by using SQL
constructs to accomplish the same goal with logical data sets. Those SQL
features include Views, Common Table Expressions, and Derived Tables."

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>

/
On 12/12/2020 2:55 AM, Gad Miron wrote:

Just seen Kent Milligan's excellent tips regarding the use of QTEMP with

SQL

https://www.mcpressonline.com/analytics-cognitive/db2/techtip-top-3-reasons-to-avoid-mixing-sql-with-qtemp

I wonder why not also use the With clause
like

with tt as (select Cust_Id, sum(Ord_Amount) as OrdAmount from cust_ord

group

by Cust_Id ) select tt.Cust_Id, Custfile.CustName, tt.OrdAmount from tt
left outer join Custfile on Custfile.Cust_Id = tt.Cust_Id
What am I missing/misunderstanding ?

TIA
Gad