| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-21-11, 08:59
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
|
Need advice optimizing query....
|
|
I've got a 'make table' (MS Access term) query which first deletes all records in the target table -
Code:
delete * from tst_cyc_suc_tot
(about 6 million records) and then queries the main table for various details and inserts them into the freshly cleaned out table. The insert time is just shy of 25mins on ZOS DB2 v9.5 (pretty sure). What changes you would make to optimize this query?
Code:
DELETE
FROM TOTDB01.TST_CYC_SUC_TOT
WHERE
TST_CYC_SUC_TOT_ID >= 1
;
COMMIT ;
---------- ---------------------------------------------
INSERT
INTO TOTDB01.TST_CYC_SUC_TOT
SELECT
TS.TST_SUC_ID
-- TST_CYC_SUC_TOT_ID,
, SMALLINT(1) -- JOBNUM_NBR,
, STRT_DT_DT -- STRT_DT_DT,
, JOB_TXT -- JOB_TXT,
, OWNR_TXT -- OWNR_TXT,
, OWNR_TXT -- APPLCTN_PFX_TXT,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -22))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TWENTYTWO_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -21))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TWENTYONE_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -20))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TWENTY_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -19))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_NINTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -18))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_EIGHTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -17))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_SEVENTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -16))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_SIXTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -15))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_FIFTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -14))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_FOURTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -13))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_THIRTEEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -12))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TWELVE_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -11))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_ELEVEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -10))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -9))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_NINE_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -8))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_EIGHT_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -7))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_SEVEN_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -6))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_SIX_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -5))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_FIVE_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -4))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_FOUR_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -3))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_THREE_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -2))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TWO_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -1))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_ONE_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) ))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_CURRENT_NBR
FROM
TOTDB01.TST_SUC TS
WHERE
TS.TST_SUC_ID IS NOT NULL
;
COMMIT ;
|
|
09-21-11, 09:35
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
First of all, I want to change like this, then consider other possibilities.
Code:
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) =
(
SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -22))
FROM SYSIBM.SYSDUMMY1
)
THEN 1 ELSE 0 END -- CNT_TWENTYTWO_NBR,
Code:
, CASE YEAR(STRT_DT_DT) *12 + MONTH(STRT_DT_DT)
WHEN YEAR(CURRENT DATE)*12 + MONTH(CURRENT DATE) - 22
THEN 1 ELSE 0 END -- CNT_TWENTYTWO_NBR,
|
|
09-21-11, 09:51
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
|
|
Here is another idea I had - doing some benchmark on runtime now...
Code:
---------- ---------------------------------------------
DELETE
FROM TOTDB01.TST_CYC_SUC_TOT
WHERE
TST_CYC_SUC_TOT_ID >= 1
;
COMMIT ;
---------- ---------------------------------------------
DECLARE ZERO INTEGER ;
DECLARE ONE INTEGER ;
DECLARE TWO INTEGER ;
DECLARE THREE INTEGER ;
DECLARE FOUR INTEGER ;
DECLARE FIVE INTEGER ;
DECLARE SIX INTEGER ;
DECLARE SEVEN INTEGER ;
DECLARE EIGHT INTEGER ;
DECLARE NINE INTEGER ;
DECLARE TEN INTEGER ;
DECLARE ELEVEN INTEGER ;
DECLARE TWELVE INTEGER ;
DECLARE THIRTEEN INTEGER ;
DECLARE FOURTEEN INTEGER ;
DECLARE FIFTEEN INTEGER ;
DECLARE SIXTEEN INTEGER ;
DECLARE SEVENTEEN INTEGER ;
DECLARE EIGHTEEN INTEGER ;
DECLARE NINTEEN INTEGER ;
DECLARE TWENTY INTEGER ;
DECLARE TWENTYONE INTEGER ;
DECLARE TWENTYTWO INTEGER ;
SET ZERO = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE))))FROM SYSIBM.SYSDUMMY1);
SET ONE = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -1))FROM SYSIBM.SYSDUMMY1);
SET TWO = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -2))FROM SYSIBM.SYSDUMMY1);
SET THREE = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -3))FROM SYSIBM.SYSDUMMY1);
SET FOUR = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -4))FROM SYSIBM.SYSDUMMY1);
SET FIVE = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -5))FROM SYSIBM.SYSDUMMY1);
SET SIX = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -6))FROM SYSIBM.SYSDUMMY1);
SET SEVEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -7))FROM SYSIBM.SYSDUMMY1);
SET EIGHT = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -8))FROM SYSIBM.SYSDUMMY1);
SET NINE = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -9))FROM SYSIBM.SYSDUMMY1);
SET TEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -10))FROM SYSIBM.SYSDUMMY1);
SET ELEVEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -11))FROM SYSIBM.SYSDUMMY1);
SET TWELVE = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -12))FROM SYSIBM.SYSDUMMY1);
SET THIRTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -13))FROM SYSIBM.SYSDUMMY1);
SET FOURTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -14))FROM SYSIBM.SYSDUMMY1);
SET FIFTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -15))FROM SYSIBM.SYSDUMMY1);
SET SIXTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -16))FROM SYSIBM.SYSDUMMY1);
SET SEVENTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -17))FROM SYSIBM.SYSDUMMY1);
SET EIGHTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -18))FROM SYSIBM.SYSDUMMY1);
SET NINTEEN = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -19))FROM SYSIBM.SYSDUMMY1);
SET TWENTY = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -20))FROM SYSIBM.SYSDUMMY1);
SET TWENTYONE = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -21))FROM SYSIBM.SYSDUMMY1);
SET TWENTYTWO = (SELECT (INT(YEAR(CURRENT DATE))*12+
(INT(MONTH(CURRENT DATE)) -22))FROM SYSIBM.SYSDUMMY1);
INSERT
INTO TOTDB01.TST_CYC_SUC_TOT
SELECT
TS.TST_SUC_ID
-- TST_CYC_SUC_TOT_ID,
, SMALLINT(1) -- JOBNUM_NBR,
, STRT_DT_DT -- STRT_DT_DT,
, JOB_TXT -- JOB_TXT,
, OWNR_TXT -- OWNR_TXT,
, OWNR_TXT -- APPLCTN_PFX_TXT,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = TWENTYTWO
THEN 1 ELSE 0 END -- CNT_FEB_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = TWENTYONE
THEN 1 ELSE 0 END -- CNT_MAR_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = TWENTY
THEN 1 ELSE 0 END -- CNT_APR_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = NINTEEN
THEN 1 ELSE 0 END -- CNT_MAY_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = EIGHTEEN
THEN 1 ELSE 0 END -- CNT_JUN_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = SEVENTEEN
THEN 1 ELSE 0 END -- CNT_JUL_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = SIXTEEN
THEN 1 ELSE 0 END -- CNT_AUG_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = FIFTEEN
THEN 1 ELSE 0 END -- CNT_SEP_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = FOURTEEN
THEN 1 ELSE 0 END -- CNT_OCT_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = THIRTEEN
THEN 1 ELSE 0 END -- CNT_NOV_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = TWELVE
THEN 1 ELSE 0 END -- CNT_DEC_2009_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = ELEVEN
THEN 1 ELSE 0 END -- CNT_JAN_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = TEN
THEN 1 ELSE 0 END -- CNT_FEB_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = NINE
THEN 1 ELSE 0 END -- CNT_MAR_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = EIGHT
THEN 1 ELSE 0 END -- CNT_APR_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = SEVEN
THEN 1 ELSE 0 END -- CNT_MAY_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = SIX
THEN 1 ELSE 0 END -- CNT_JUN_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = FIVE
THEN 1 ELSE 0 END -- CNT_JUL_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = FOUR
THEN 1 ELSE 0 END -- CNT_AUG_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = THREE
THEN 1 ELSE 0 END -- CNT_SEP_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = TWO
THEN 1 ELSE 0 END -- CNT_OCT_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = ONE
THEN 1 ELSE 0 END -- CNT_NOV_2010_NBR,
, CASE WHEN (INT(YEAR(STRT_DT_DT))*12+
(int(MONTH(STRT_DT_DT)))) = ZERO
THEN 1 ELSE 0 END -- CNT_DEC_2010_NBR
FROM
TOTDB01.TST_SUC TS
WHERE
TS.TST_SUC_ID IS NOT NULL
;
COMMIT ;
|
Last edited by goldfishhh; 09-21-11 at 10:21.
|
09-21-11, 09:56
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
WHOWA! Time on what I just posted - the query went from 16mins to 4. I'm also going to try what you suggested and benchmark that too.
|
|
09-21-11, 10:51
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Why did you surrounded functions/expressions with unneccesary phrases/parentheses?
Examples of unnecessary phrases/parentheses in your code.
1) "(SELECT" and "FROM SYSIBM.SYSDUMMY1)"
2) "INT(" and ")"
3) parentheses surrounding INT, YEAR, and MONTH
Another example:
Code:
INSERT
INTO TOTDB01.TST_CYC_SUC_TOT
SELECT
TS.TST_SUC_ID
-- TST_CYC_SUC_TOT_ID,
, SMALLINT(1) -- JOBNUM_NBR,
, STRT_DT_DT -- STRT_DT_DT,
, JOB_TXT -- JOB_TXT,
, OWNR_TXT -- OWNR_TXT,
, OWNR_TXT -- APPLCTN_PFX_TXT,
, 1 - ABS( SIGN(month_diff - 22) ) -- CNT_TWENTYTWO_NBR
/* CASE month_diff WHEN 22 THEN 1 ELSE 0 END */
, 1 - ABS( SIGN(month_diff - 21) ) -- CNT_TWENTYONE_NBR
, 1 - ABS( SIGN(month_diff - 20) ) -- CNT_TWENTY_NBR
, 1 - ABS( SIGN(month_diff - 19) ) -- CNT_NINTEEN_NBR
, 1 - ABS( SIGN(month_diff - 18) ) -- CNT_EIGHTEEN_NBR
, 1 - ABS( SIGN(month_diff - 17) ) -- CNT_SEVENTEEN_NBR
, 1 - ABS( SIGN(month_diff - 16) ) -- CNT_SIXTEEN_NBR
, 1 - ABS( SIGN(month_diff - 15) ) -- CNT_FIFTEEN_NBR
, 1 - ABS( SIGN(month_diff - 14) ) -- CNT_FOURTEEN_NBR
, 1 - ABS( SIGN(month_diff - 13) ) -- CNT_THIRTEEN_NBR
, 1 - ABS( SIGN(month_diff - 12) ) -- CNT_TWELVE_NBR
, 1 - ABS( SIGN(month_diff - 11) ) -- CNT_ELEVEN_NBR
, 1 - ABS( SIGN(month_diff - 10) ) -- CNT_TEN_NBR
, 1 - ABS( SIGN(month_diff - 09) ) -- CNT_NINE_NBR
, 1 - ABS( SIGN(month_diff - 08) ) -- CNT_EIGHT_NBR
, 1 - ABS( SIGN(month_diff - 07) ) -- CNT_SEVEN_NBR
, 1 - ABS( SIGN(month_diff - 06) ) -- CNT_SIX_NBR
, 1 - ABS( SIGN(month_diff - 05) ) -- CNT_FIVE_NBR
, 1 - ABS( SIGN(month_diff - 04) ) -- CNT_FOUR_NBR
, 1 - ABS( SIGN(month_diff - 03) ) -- CNT_THREE_NBR
, 1 - ABS( SIGN(month_diff - 02) ) -- CNT_TWO_NBR
, 1 - ABS( SIGN(month_diff - 01) ) -- CNT_ONE_NBR
, 1 - ABS( SIGN(month_diff ) ) -- CNT_CURRENT_NBR
FROM
TOTDB01.TST_SUC TS
, LATERAL
(SELECT YEAR(CURRENT DATE) * 12 + MONTH(CURRENT DATE)
- YEAR(STRT_DT_DT) * 12 + MONTH(STRT_DT_DT)
FROM SYSIBM.SYSDUMMY1
) p(month_diff)
WHERE
TS.TST_SUC_ID IS NOT NULL
;
|
Last edited by tonkuma; 09-21-11 at 11:13.
Reason: Replace "CROSS JOIN" with ",". Replace VALUES with SELECT ... FROM SYSIBM.SYSDUMMY1
|
09-21-11, 11:07
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
too quick to answer... the DECLARE didn't work. Need to rework it so it actually works.
Quote:
Originally Posted by goldfishhh
WHOWA! Time on what I just posted - the query went from 16mins to 4. I'm also going to try what you suggested and benchmark that too.
|
|
|
09-21-11, 11:22
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
Looks like it didn't like the 'CROSS'
Code:
SQLERROR ON INSERT COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "CROSS". SOME SYMBOLS THAT MIG
HAVING WHERE GROUP ORDER INTERSECT
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 990 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF' X'00
INFORMATION
Quote:
Originally Posted by tonkuma
Why did you surrounded functions/expressions with unneccesary phrases/parentheses?
Examples of unnecessary phrases/parentheses in your code.
1) "(SELECT" and "FROM SYSIBM.SYSDUMMY1)"
2) "INT(" and ")"
3) parentheses surrounding INT, YEAR, and MONTH
Another example:
Code:
INSERT
INTO TOTDB01.TST_CYC_SUC_TOT
SELECT
TS.TST_SUC_ID
-- TST_CYC_SUC_TOT_ID,
, SMALLINT(1) -- JOBNUM_NBR,
, STRT_DT_DT -- STRT_DT_DT,
, JOB_TXT -- JOB_TXT,
, OWNR_TXT -- OWNR_TXT,
, OWNR_TXT -- APPLCTN_PFX_TXT,
, 1 - ABS( SIGN(month_diff - 22) ) -- CNT_TWENTYTWO_NBR
/* CASE month_diff WHEN 22 THEN 1 ELSE 0 END */
, 1 - ABS( SIGN(month_diff - 21) ) -- CNT_TWENTYONE_NBR
, 1 - ABS( SIGN(month_diff - 20) ) -- CNT_TWENTY_NBR
, 1 - ABS( SIGN(month_diff - 19) ) -- CNT_NINTEEN_NBR
, 1 - ABS( SIGN(month_diff - 18) ) -- CNT_EIGHTEEN_NBR
, 1 - ABS( SIGN(month_diff - 17) ) -- CNT_SEVENTEEN_NBR
, 1 - ABS( SIGN(month_diff - 16) ) -- CNT_SIXTEEN_NBR
, 1 - ABS( SIGN(month_diff - 15) ) -- CNT_FIFTEEN_NBR
, 1 - ABS( SIGN(month_diff - 14) ) -- CNT_FOURTEEN_NBR
, 1 - ABS( SIGN(month_diff - 13) ) -- CNT_THIRTEEN_NBR
, 1 - ABS( SIGN(month_diff - 12) ) -- CNT_TWELVE_NBR
, 1 - ABS( SIGN(month_diff - 11) ) -- CNT_ELEVEN_NBR
, 1 - ABS( SIGN(month_diff - 10) ) -- CNT_TEN_NBR
, 1 - ABS( SIGN(month_diff - 09) ) -- CNT_NINE_NBR
, 1 - ABS( SIGN(month_diff - 08) ) -- CNT_EIGHT_NBR
, 1 - ABS( SIGN(month_diff - 07) ) -- CNT_SEVEN_NBR
, 1 - ABS( SIGN(month_diff - 06) ) -- CNT_SIX_NBR
, 1 - ABS( SIGN(month_diff - 05) ) -- CNT_FIVE_NBR
, 1 - ABS( SIGN(month_diff - 04) ) -- CNT_FOUR_NBR
, 1 - ABS( SIGN(month_diff - 03) ) -- CNT_THREE_NBR
, 1 - ABS( SIGN(month_diff - 02) ) -- CNT_TWO_NBR
, 1 - ABS( SIGN(month_diff - 01) ) -- CNT_ONE_NBR
, 1 - ABS( SIGN(month_diff ) ) -- CNT_CURRENT_NBR
FROM
TOTDB01.TST_SUC TS
, LATERAL
(SELECT YEAR(CURRENT DATE) * 12 + MONTH(CURRENT DATE)
- YEAR(STRT_DT_DT) * 12 + MONTH(STRT_DT_DT)
FROM SYSIBM.SYSDUMMY1
) p(month_diff)
WHERE
TS.TST_SUC_ID IS NOT NULL
;
|
|
|
09-21-11, 11:37
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
Dangit - we have DB2 v9.1.
Quote:
Originally Posted by tonkuma
Why did you surrounded functions/expressions with unneccesary phrases/parentheses?
Examples of unnecessary phrases/parentheses in your code.
1) "(SELECT" and "FROM SYSIBM.SYSDUMMY1)"
2) "INT(" and ")"
3) parentheses surrounding INT, YEAR, and MONTH
Another example:
Code:
INSERT
INTO TOTDB01.TST_CYC_SUC_TOT
SELECT
TS.TST_SUC_ID
-- TST_CYC_SUC_TOT_ID,
, SMALLINT(1) -- JOBNUM_NBR,
, STRT_DT_DT -- STRT_DT_DT,
, JOB_TXT -- JOB_TXT,
, OWNR_TXT -- OWNR_TXT,
, OWNR_TXT -- APPLCTN_PFX_TXT,
, 1 - ABS( SIGN(month_diff - 22) ) -- CNT_TWENTYTWO_NBR
/* CASE month_diff WHEN 22 THEN 1 ELSE 0 END */
, 1 - ABS( SIGN(month_diff - 21) ) -- CNT_TWENTYONE_NBR
, 1 - ABS( SIGN(month_diff - 20) ) -- CNT_TWENTY_NBR
, 1 - ABS( SIGN(month_diff - 19) ) -- CNT_NINTEEN_NBR
, 1 - ABS( SIGN(month_diff - 18) ) -- CNT_EIGHTEEN_NBR
, 1 - ABS( SIGN(month_diff - 17) ) -- CNT_SEVENTEEN_NBR
, 1 - ABS( SIGN(month_diff - 16) ) -- CNT_SIXTEEN_NBR
, 1 - ABS( SIGN(month_diff - 15) ) -- CNT_FIFTEEN_NBR
, 1 - ABS( SIGN(month_diff - 14) ) -- CNT_FOURTEEN_NBR
, 1 - ABS( SIGN(month_diff - 13) ) -- CNT_THIRTEEN_NBR
, 1 - ABS( SIGN(month_diff - 12) ) -- CNT_TWELVE_NBR
, 1 - ABS( SIGN(month_diff - 11) ) -- CNT_ELEVEN_NBR
, 1 - ABS( SIGN(month_diff - 10) ) -- CNT_TEN_NBR
, 1 - ABS( SIGN(month_diff - 09) ) -- CNT_NINE_NBR
, 1 - ABS( SIGN(month_diff - 08) ) -- CNT_EIGHT_NBR
, 1 - ABS( SIGN(month_diff - 07) ) -- CNT_SEVEN_NBR
, 1 - ABS( SIGN(month_diff - 06) ) -- CNT_SIX_NBR
, 1 - ABS( SIGN(month_diff - 05) ) -- CNT_FIVE_NBR
, 1 - ABS( SIGN(month_diff - 04) ) -- CNT_FOUR_NBR
, 1 - ABS( SIGN(month_diff - 03) ) -- CNT_THREE_NBR
, 1 - ABS( SIGN(month_diff - 02) ) -- CNT_TWO_NBR
, 1 - ABS( SIGN(month_diff - 01) ) -- CNT_ONE_NBR
, 1 - ABS( SIGN(month_diff ) ) -- CNT_CURRENT_NBR
FROM
TOTDB01.TST_SUC TS
, LATERAL
(SELECT YEAR(CURRENT DATE) * 12 + MONTH(CURRENT DATE)
- YEAR(STRT_DT_DT) * 12 + MONTH(STRT_DT_DT)
FROM SYSIBM.SYSDUMMY1
) p(month_diff)
WHERE
TS.TST_SUC_ID IS NOT NULL
;
|
|
|
09-21-11, 16:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
Dangit - we have DB2 v9.1.
|
I'm not so well at English. So, I couldn't understand what do you want to say by "Dangit".
Sorry! I forgot one more thing.
Please change LATERAL with TABLE.
My question is are there any problem in my query after changing LATERAL with TABLE on your DB2 v9.1?
My updated query:
Last edited by tonkuma; Today at 00:13. Reason: Replace "CROSS JOIN" with ",". Replace VALUES with SELECT ... FROM SYSIBM.SYSDUMMY1
|
Last edited by tonkuma; 09-21-11 at 16:46.
Reason: Add "Please change LATERAL with TABLE." and "after changing LATERAL with TABLE"
|
09-22-11, 08:47
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
Very sorry about casual English phrases... When I run the query (below), I get this error below.
From what I can see, YEAR() is used correctly.
Code:
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD YEAR. TOKEN , ) WAS
EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 2970 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'00000B9A' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
Code:
INSERT
INTO TOTDB01.TST_CYC_SUC_TOT
SELECT
TS.TST_SUC_ID
-- TST_CYC_SUC_TOT_ID,
, SMALLINT(1) -- JOBNUM_NBR,
, STRT_DT_DT -- STRT_DT_DT,
, JOB_TXT -- JOB_TXT,
, OWNR_TXT -- OWNR_TXT,
, OWNR_TXT -- APPLCTN_PFX_TXT,
, 1 - ABS( SIGN(month_diff - 22) ) -- CNT_TWENTYTWO_NBR
/* CASE month_diff WHEN 22 THEN 1 ELSE 0 END */
, 1 - ABS( SIGN(month_diff - 21) ) -- CNT_TWENTYONE_NBR
, 1 - ABS( SIGN(month_diff - 20) ) -- CNT_TWENTY_NBR
, 1 - ABS( SIGN(month_diff - 19) ) -- CNT_NINTEEN_NBR
, 1 - ABS( SIGN(month_diff - 18) ) -- CNT_EIGHTEEN_NBR
, 1 - ABS( SIGN(month_diff - 17) ) -- CNT_SEVENTEEN_NBR
, 1 - ABS( SIGN(month_diff - 16) ) -- CNT_SIXTEEN_NBR
, 1 - ABS( SIGN(month_diff - 15) ) -- CNT_FIFTEEN_NBR
, 1 - ABS( SIGN(month_diff - 14) ) -- CNT_FOURTEEN_NBR
, 1 - ABS( SIGN(month_diff - 13) ) -- CNT_THIRTEEN_NBR
, 1 - ABS( SIGN(month_diff - 12) ) -- CNT_TWELVE_NBR
, 1 - ABS( SIGN(month_diff - 11) ) -- CNT_ELEVEN_NBR
, 1 - ABS( SIGN(month_diff - 10) ) -- CNT_TEN_NBR
, 1 - ABS( SIGN(month_diff - 09) ) -- CNT_NINE_NBR
, 1 - ABS( SIGN(month_diff - 08) ) -- CNT_EIGHT_NBR
, 1 - ABS( SIGN(month_diff - 07) ) -- CNT_SEVEN_NBR
, 1 - ABS( SIGN(month_diff - 06) ) -- CNT_SIX_NBR
, 1 - ABS( SIGN(month_diff - 05) ) -- CNT_FIVE_NBR
, 1 - ABS( SIGN(month_diff - 04) ) -- CNT_FOUR_NBR
, 1 - ABS( SIGN(month_diff - 03) ) -- CNT_THREE_NBR
, 1 - ABS( SIGN(month_diff - 02) ) -- CNT_TWO_NBR
, 1 - ABS( SIGN(month_diff - 01) ) -- CNT_ONE_NBR
, 1 - ABS( SIGN(month_diff ) ) -- CNT_CURRENT_NBR
FROM
TOTDB01.TST_SUC TS
, LATERAL
(SELECT YEAR(CURRENT DATE) * 12 + MONTH(CURRENT DATE)
- YEAR(STRT_DT_DT) * 12 + MONTH(STRT_DT_DT)
FROM SYSIBM.SYSDUMMY1
) p(month_diff)
WHERE
TS.TST_SUC_ID IS NOT NULL
;
Quote:
Originally Posted by tonkuma
I'm not so well at English. So, I couldn't understand what do you want to say by "Dangit".
Sorry! I forgot one more thing.
Please change LATERAL with TABLE.
My question is are there any problem in my query after changing LATERAL with TABLE on your DB2 v9.1?
My updated query:
Last edited by tonkuma; Today at 00:13. Reason: Replace "CROSS JOIN" with ",". Replace VALUES with SELECT ... FROM SYSIBM.SYSDUMMY1
|
|
|
09-22-11, 09:20
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
Tonkuma - I think I figured it out. In one of your older posts, you also helped someone with nested table queries. I read that post and then read my DB2 9.1 cookbook and it suggested that I change "LATERAL" to "TABLE".
I'm not getting any errors, just a warning message with the query - going to check it to see if it is actually properly populating the correct values.
Code:
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT404I SQLCODE = 12, WARNING: THE UNQUALIFIED COLUMN NAME STRT_DT_DT WAS
INTERPRETED AS A CORRELATED REFERENCE
DSNT418I SQLSTATE = 01545 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 1190606649 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'46F73739'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNE615I NUMBER OF ROWS AFFECTED IS 4011947
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
|
|
09-22-11, 12:11
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Code:
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT404I SQLCODE = 12, WARNING: THE UNQUALIFIED COLUMN NAME STRT_DT_DT WAS
INTERPRETED AS A CORRELATED REFERENCE
...
|
I think the warning shall disappear by adding the qualifier " TS.", like ...
Code:
, TABLE
(SELECT YEAR(CURRENT DATE) * 12 + MONTH(CURRENT DATE)
- YEAR(TS.STRT_DT_DT) * 12 + MONTH(TS.STRT_DT_DT)
FROM SYSIBM.SYSDUMMY1
) p(month_diff)
|
|
09-23-11, 09:13
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 54
|
|
Worked very well!
Ok, one last question. I have a delete statement which takes about 5 minutes to complete. Can you think of a faster way to delete all the records in a table?
Here is the SQL:
Code:
DELETE
FROM TOTDB01.TST_CYC_SUC_TOT
;
|
|
09-23-11, 12:32
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
Quote:
Originally Posted by goldfishhh
Worked very well!
Ok, one last question. I have a delete statement which takes about 5 minutes to complete. Can you think of a faster way to delete all the records in a table?
Here is the SQL:
Code:
DELETE
FROM TOTDB01.TST_CYC_SUC_TOT
;
|
With DB2 LUW, you can do import from /dev/null (or empty file). I think there is truncate statement as well.
With DB2 zOS, load from an empty file, I guess. Not sure if this will put the tablespace into backup pending mode, but you can force it to go back to normal? Check if there is truncate similar to LUW
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Need advice optimizing query....
