Can anyone let know how is the below query is efficient because its creating slow performance issues and also good to inform about improve the sql query found below(tuning)
INSERT INTO VHUPC
SELECT G.TRANS_DATE, G.UPC, G.DEPT_NUM, G.STROKE_NUM
, G.COLOUR_CODE, G.PRIM_SIZE_DESC
, G.SEC_SIZE_DESC, G.RETAIL_PRICE
, G.COST_PRICE, G.ORIG_UK_PRICE, G.TAX_CODE
, CASE WHEN F.UPC IS NOT NULL
THEN F.DECL_AVAIL_SGLS ELSE G.DECL_AVAIL_SGLS END
, CASE WHEN F.UPC IS NOT NULL
THEN F.FREE_AVAIL_SGLS ELSE G.FREE_AVAIL_SGLS END
, CASE WHEN F.UPC IS NOT NULL
THEN F.ORDERED_SGLS ELSE G.ORDERED_SGLS END
, CASE WHEN F.UPC IS NOT NULL
THEN F.RESERVED_SGLS ELSE G.RESERVED_SGLS END
, G.CNCT_SOURCE
, G.CNCT_PAYMENT
, G.CNCT_DELIVERY
, G.REDUCED_IND
, CASE WHEN H.UPC IS NOT NULL
THEN H.BOOKED_IN_SGLS ELSE 0 END
FROM VHUPCT1 G LEFT OUTER JOIN
(SELECT E.UPC AS UPC, E.RND_DECLD_AV_QTY AS DECL_AVAIL_SGLS
, E.RND_FREE_AVAIL_S AS FREE_AVAIL_SGLS
, CASE WHEN D.UPC IS NOT NULL
THEN D.ORDERED_QTY ELSE 0 END AS ORDERED_SGLS
, CASE WHEN D.UPC IS NOT NULL
THEN D.RESERVED_QTY ELSE 0 END AS RESERVED_SGLS
FROM VNBORD D
RIGHT OUTER JOIN
(
SELECT UPC,
RND_DECLD_AV_QTY, RND_FREE_AVAIL_S
FROM PNB001.VDEPT A INNER JOIN VNBWAY B
ON A.PART_ID=B.PART_ID
INNER JOIN VSTCOL C
ON A.DEPARTMENT_NUMBER=C.DEPT_NUM
AND B.STROKE_NUMBER=C.STROKE_NUM
AND B.COLOUR_CODE=C.COLOUR_CODE) E ON D.UPC=E.UPC) F
ON F.UPC = G.UPC
LEFT OUTER JOIN VBOOKD H ON H.UPC=G.UPC

table structures are provided below


VNBORD
EXEC SQL DECLARE VNBORD TABLE
( RF_OWNER_CODE CHAR(2) NOT NULL,
UPC CHAR(8) NOT NULL,
ORDERED_QTY INTEGER NOT NULL,
FULFILLED_QTY INTEGER NOT NULL,
RESERVED_QTY INTEGER NOT NULL
) END-EXEC.
*******
01 DCLVNBORD.
10 RF-OWNER-CODE PIC X(2).
10 UPC PIC X(8).
10 ORDERED-QTY PIC S9(9) USAGE COMP.
10 FULFILLED-QTY PIC S9(9) USAGE COMP.
10 RESERVED-QTY PIC S9(9) USAGE COMP.
------
EXEC SQL DECLARE DB2DFD1.VSTCOL TABLE
( DEPT_NUM CHAR(4) NOT NULL,
RANGE_CODE CHAR(3) NOT NULL,
STROKE_NUM CHAR(6) NOT NULL,
COLOUR_CODE CHAR(3) NOT NULL,
LAST_UPDATE TIMESTAMP NOT NULL,
USERID CHAR(8) NOT NULL
) END-EXEC.
**********
01 DCLVSTCOL.
10 DEPT-NUM PIC X(4).
10 RANGE-CODE PIC X(3).
10 STROKE-NUM PIC X(6).
10 COLOUR-CODE PIC X(3).
10 LAST-UPDATE PIC X(26).
10 USERID PIC X(8).



EXEC SQL DECLARE PDF001.VHSTCOL TABLE
( DEPT_NUM CHAR(4) NOT NULL,
STROKE_NUM CHAR(5) NOT NULL,
COLOUR_CODE CHAR(3) NOT NULL,
CNCT_QTY INTEGER NOT NULL,
ADDED_DATE DATE NOT NULL,
DELETED_DATE DATE,
ARCHIVED_DATE DATE,
SUSPENDED_DATE DATE,
SPM_UNCATLG_DATE DATE,
LAST_USERID CHAR(8) NOT NULL,
LAST_UPDATE TIMESTAMP NOT NULL,
USERID CHAR(8) NOT NULL
) END-EXEC.
***** 01 DCLVHSTCOL.
10 DEPT-NUM PIC X(4).
10 STROKE-NUM PIC X(5).
10 COLOUR-CODE PIC X(3).
10 CNCT-QTY PIC S9(9) USAGE COMP.
10 ADDED-DATE PIC X(10).
10 DELETED-DATE PIC X(10).
10 ARCHIVED-DATE PIC X(10).
10 SUSPENDED-DATE PIC X(10).
10 SPM-UNCATLG-DATE PIC X(10).
10 LAST-USERID PIC X(8).
10 LAST-UPDATE PIC X(26).
10 USERID PIC X(8).
***** EXEC SQL DECLARE PDF001.VHUPC TABLE
( TRANS_DATE DATE NOT NULL,
UPC CHAR(8) NOT NULL,
DEPT_NUM CHAR(4) NOT NULL,
STROKE_NUM CHAR(5) NOT NULL,
COLOUR_CODE CHAR(3) NOT NULL,
PRIM_SIZE_DESC CHAR(5) NOT NULL,
SEC_SIZE_DESC CHAR(3) NOT NULL,
RETAIL_PRICE DECIMAL(7, 2) NOT NULL,
COST_PRICE DECIMAL(7, 2) NOT NULL,
ORIG_UK_PRICE DECIMAL(7, 2) NOT NULL,
TAX_CODE CHAR(1) NOT NULL,
DECL_AVAIL_SGLS INTEGER NOT NULL,
FREE_AVAIL_SGLS INTEGER NOT NULL,
ORDERED_SGLS INTEGER NOT NULL,
RESERVED_SGLS INTEGER NOT NULL,
CNCT_SOURCE CHAR(1) NOT NULL,
CNCT_PAYMENT CHAR(1) NOT NULL,

CNCT_DELIVERY CHAR(1) NOT NULL,
REDUCED_IND CHAR(1) NOT NULL,
BOOKED_IN_SGLS INTEGER NOT NULL
) END-EXEC.
******
VHUPC

NAME
--------------------------
XDF013A
XDF013B
XDF013C
XDF013D

TDF013HUPC01
TDF013HUPC01
TDF013HUPC01
TDF013HUPC01

UNIQUERULE COLCOUNT CLUSTERING CLUSTERED DBID
---------- -------- ---------- --------- ------
D 2 Y Y 372
D 2 N N 372
D 4 N N 372
D 4 N N 372





NAME
----------------------------
XDFHUPCT1A
XDFHUPCT2A
XDFHUPCT3A
XDFHUPCT4A


TBNAME
-----------------------------
TDF045HUPCT101
TDF046HUPCT201
TDF047HUPCT301
TDF048HUPCT401



UNIQUERULE COLCOUNT CLUSTERING CLUSTERED DBID OBID ISOBID DBNAME
---------- -------- ---------- --------- ------ ------ ------ ----------
D 2 Y Y 372 174 189 BDF001
D 1 Y Y 372 210 211 BDF001
D 1 Y Y 372 222 225 BDF001
D 1 Y Y 372 227 228 BDF001





Sathya
mum

I would say it is rather slow, due to not having a where clause that limits the data in any way. So, on your main table you are performing a tablespace scan and then all of the left and right joins. This would seem to be a one time run type of thing, but from your question it sounds like you are running this more often than that. What is it you are trying to do and how often?

Dave

All the records fetched from the above select will be inserted into vhupc table, is there any way this querry can be remodified to get the same result...



below are are two more queries for which a modified query is required.

UPDATE VSTCOL X
SET RED_SUFF_IND="Y"
WHERE "Y" =
(SELECT MIN(CASE WHEN RED_SUFF = "@"
THEN "Y"
ELSE "N"
END)
FROM VRDWAY Y
WHERE X.DEPT_NUM = Y.DEPT_NUM
AND X.STROKE_NUM = Y.STROKE_NUM
AND X.COLOUR_CODE = Y.COLOUR_CODE)
AND "Y" =
(SELECT MIN(CASE WHEN PRICE = 1
THEN "Y"
ELSE "N"
END)
FROM VRDWAY Z
WHERE X.DEPT_NUM = Z.DEPT_NUM
AND X.STROKE_NUM = Z.STROKE_NUM
AND X.COLOUR_CODE = Z.COLOUR_CODE)



EXEC SQL
DECLARE READNEXT_VHSTCOL CURSOR FOR
SELECT A.DEPT_NUM,A.STROKE_NUM,A.COLOUR_CODE,UPC_NUM
FROM VHSTCOL A
, VRDWAY B
, VCODES C
, (SELECT DEPT_NUM, STROKE_NUM, COLOUR_CODE
, MAX(ADDED_DATE) ADDED_DATE
FROM VHSTCOL
GROUP BY DEPT_NUM, STROKE_NUM, COLOUR_CODE) D
WHERE A.DEPT_NUM = B.DEPT_NUM
AND A.STROKE_NUM = B.STROKE_NUM
AND A.COLOUR_CODE = B.COLOUR_CODE
AND C.FIELD_ID = "US"
AND A.SUSPENDED_DATE + INTEGER(CODE_VALUE_1) MONTHS
<= CURRENT DATE
AND SPM_UNCATLG_DATE IS NULL
AND A.DEPT_NUM = D.DEPT_NUM
AND A.STROKE_NUM = D.STROKE_NUM
AND A.COLOUR_CODE = D.COLOUR_CODE
AND A.ADDED_DATE = D.ADDED_DATE
ORDER BY UPC_NUM
END-EXEC.



allthe three are embedded in programs
thanks dave foryour reply....