MVSFORUMS.com

nivedita · Beginner Joined: 04 Nov 2003 Posts: 22 Topics: 15

BMC unload executes ADUUMAIN.

UNLOAD SHRLEVEL CHANGE CONSISTENT NO QUIESCE
UNLOADDN(SYSRE)
USELRECL YES
SORTDEVT SYSDA
SORTNUM 12
FORMAT EXTERNAL
SELECT A
, B
, C
, D
, E
INTO A CHAR (2)
, B CHAR (4)
, C CHAR (6)
, D CHAR (1)
, E INTEGER EXTERNAL (4)
IF NULL VALUE(0)
FROM WW.TPRDIST
WHERE X = 'Y'
AND Y = '03'
AND Z IN ('005','010','015','020')

In the existing control card hard coding isused on certain fields because incorporating Joins in a BMC unload utility will reduce the performance and it will act like a normal DB2 DSNTIAUL utility.

My queries are:
1. Please clarify in what aspects the BMC unload process will be affected if we introduce a Join.
2. How will the performance impact by introducing Joins /sub selects than
hard coding for the same Volume of data.

kolusu · Posted: Wed Nov 26, 2003 5:45 am Post subject:

Nivedita,

In general, Joins involve more work than regualar selects as they can lead to a tablespace scan. If the join involves a column which is not a part of the index , the performance is degraded. The hard coding of values on a select statement will remove the extra burden of scanning the tablespace.

The best way to see the compare the queries is to run EXPLAIN for both the queries.

check this topic which discusses FAQ on SQL Statement Performance and Evaluation

http://www.mvsforums.com/helpboards/viewtopic.php?t=587

check this link for Using EXPLAIN to improve SQL performance

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/6.4?DT=20010710165542

Hope this helps...

cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu