Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: 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 ;
    

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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,

  3. #3
    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 11:21.

  4. #4
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 12:13. Reason: Replace "CROSS JOIN" with ",". Replace VALUES with SELECT ... FROM SYSIBM.SYSDUMMY1

  6. #6
    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 View Post
    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.

  7. #7
    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 View Post
    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
        ;

  8. #8
    Join Date
    Nov 2004
    Posts
    54
    Dangit - we have DB2 v9.1.

    Quote Originally Posted by tonkuma View Post
    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
        ;

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 17:46. Reason: Add "Please change LATERAL with TABLE." and "after changing LATERAL with TABLE"

  10. #10
    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 View Post
    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

  11. #11
    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                       
    ---------+---------+---------+---------+---------+---------+---------+---------+

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)

  13. #13
    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               
               ;

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by goldfishhh View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •