How to Update Multiple Queries in optimized way in DB2

Question

Please find the following code -

UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 1245789633 AND TENDOR_DATE = 2012-01-02;   
UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 1235853861 AND TENDOR_DATE = 2011-02-22;     
UPDATE TENDORS SET TENDORS_VALUE = 0A0 WHERE ID = 2565854283 AND TENDOR_DATE = 2013-11-19;    
UPDATE TENDORS SET TENDORS_VALUE = 0A0 WHERE ID = 0565853935 AND TENDOR_DATE = 2012-02-02;    
UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 5565854148 AND TENDOR_DATE = 2013-07-02;    
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565850296 AND TENDOR_DATE = 2012-12-20;     
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565850507 AND TENDOR_DATE = 2012-10-12;     
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565850644 AND TENDOR_DATE = 2012-09-02;    
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565851488 AND TENDOR_DATE = 2012-08-02;    
UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 4610849238 AND TENDOR_DATE = 2012-07-02;    
UPDATE TENDORS SET TENDORS_VALUE = 4A0 WHERE ID = 4610849238 AND TENDOR_DATE = 2012-06-02;    
UPDATE TENDORS SET TENDORS_VALUE = EB0 WHERE ID = 4610849238 AND TENDOR_DATE = 2012-05-02;    
UPDATE TENDORS SET TENDORS_VALUE = EA0 WHERE ID = 4610850154 AND TENDOR_DATE = 2012-04-02;     
UPDATE TENDORS SET TENDORS_VALUE = EA0 WHERE ID = 4610850154 AND TENDOR_DATE = 2012-03-02;    
UPDATE TENDORS SET TENDORS_VALUE = EA0 WHERE ID = 4610850154 AND TENDOR_DATE = 2012-12-02;     
UPDATE TENDORS SET TENDORS_VALUE = CA0 WHERE ID = 4610851768 AND TENDOR_DATE = 2012-08-02;     
UPDATE TENDORS SET TENDORS_VALUE = CA0 WHERE ID = 4610851768 AND TENDOR_DATE = 2012-07-02;    
UPDATE TENDORS SET TENDORS_VALUE = CA0 WHERE ID = 4610851768 AND TENDOR_DATE = 2012-06-02;    
UPDATE TENDORS SET TENDORS_VALUE = 8B0 WHERE ID = 4565853935 AND TENDOR_DATE = 2012-05-02;    
UPDATE TENDORS SET TENDORS_VALUE = 8B0 WHERE ID = 4565854146 AND TENDOR_DATE = 2009-04-02;     
UPDATE TENDORS SET TENDORS_VALUE = 8B0 WHERE ID = 4565854146 AND TENDOR_DATE = 2009-03-02;    
UPDATE TENDORS SET TENDORS_VALUE = 9B0 WHERE ID = 4565854283 AND TENDOR_DATE = 2010-02-02;    
UPDATE TENDORS SET TENDORS_VALUE = 9B0 WHERE ID = 4565854283 AND TENDOR_DATE = 2011-01-02;

for up to 1000 Update statements

When we are executing above query (i.e. Executing 1000 Update Queries) it is taking too much time. I want to boost the performance.

Which is best way to optimized it in terms of code and Time ? Any class like DB2BulkCopy for Update ? or Any Built in class for update in DB2 or SQL?

Answer 1

How about this

WITH A(A,B,C) AS (VALUES
     ('080','1245789633','2012-01-02')   
    ,('080','1235853861','2011-02-22')     
    ,('0A0','2565854283','2013-11-19')    
    ,('0A0','0565853935','2012-02-02')    
    ,('080','5565854148','2013-07-02')    
    ,('480','4565850296','2012-12-20')     
    ,('480','4565850507','2012-10-12')     
    ,('480','4565850644','2012-09-02')    
    ,('480','4565851488','2012-08-02')    
    ,('080','4610849238','2012-07-02')    
    ,('4A0','4610849238','2012-06-02')    
    ,('EB0','4610849238','2012-05-02')    
    ,('EA0','4610850154','2012-04-02')     
    ,('EA0','4610850154','2012-03-02')    
    ,('EA0','4610850154','2012-12-02')     
    ,('CA0','4610851768','2012-08-02')     
    ,('CA0','4610851768','2012-07-02')    
    ,('CA0','4610851768','2012-06-02')    
    ,('8B0','4565853935','2012-05-02')    
    ,('8B0','4565854146','2009-04-02')     
    ,('8B0','4565854146','2009-03-02')    
    ,('9B0','4565854283','2010-02-02')    
    ,('9B0','4565854283','2011-01-02')
    )
SELECT COUNT(*) FROM NEW TABLE (
    UPDATE TENDORS
    SET TENDORS_VALUE = (SELECT A FROM A WHERE ID = B AND TENDOR_DATE = C)
    WHERE EXISTS        (SELECT A FROM A WHERE ID = B AND TENDOR_DATE = C)
) 

Answer 2

The only way I know of to do multiple updates with a single statement is like this:

UPDATE TENDORS T
SET TENDORS_VALUE = (
    SELECT TVC.A FROM (VALUES
         ('080','1245789633','2012-01-02')
        ,('080','1235853861','2011-02-22')
        ,('0A0','2565854283','2013-11-19')
        ,('0A0','0565853935','2012-02-02')
        ,('080','5565854148','2013-07-02')
        ,('480','4565850296','2012-12-20')
        ,('480','4565850507','2012-10-12')
        ,('480','4565850644','2012-09-02')
        ,('480','4565851488','2012-08-02')
        ,('080','4610849238','2012-07-02')
        ,('4A0','4610849238','2012-06-02')
        ,('EB0','4610849238','2012-05-02')
        ,('EA0','4610850154','2012-04-02')
        ,('EA0','4610850154','2012-03-02')
        ,('EA0','4610850154','2012-12-02')
        ,('CA0','4610851768','2012-08-02')
        ,('CA0','4610851768','2012-07-02')
        ,('CA0','4610851768','2012-06-02')
        ,('8B0','4565853935','2012-05-02')
        ,('8B0','4565854146','2009-04-02')
        ,('8B0','4565854146','2009-03-02')
        ,('9B0','4565854283','2010-02-02')
        ,('9B0','4565854283','2011-01-02')
    ) AS TVC(A, B, C)
    WHERE (T.ID, T.TENDOR_DATE) = (TVC.B, TVC.C)
) WHERE (T.ID, T.TENDOR_DATE) IN (
    SELECT B, C FROM (VALUES
         ('080','1245789633','2012-01-02')
        ,('080','1235853861','2011-02-22')
        ,('0A0','2565854283','2013-11-19')
        ,('0A0','0565853935','2012-02-02')
        ,('080','5565854148','2013-07-02')
        ,('480','4565850296','2012-12-20')
        ,('480','4565850507','2012-10-12')
        ,('480','4565850644','2012-09-02')
        ,('480','4565851488','2012-08-02')
        ,('080','4610849238','2012-07-02')
        ,('4A0','4610849238','2012-06-02')
        ,('EB0','4610849238','2012-05-02')
        ,('EA0','4610850154','2012-04-02')
        ,('EA0','4610850154','2012-03-02')
        ,('EA0','4610850154','2012-12-02')
        ,('CA0','4610851768','2012-08-02')
        ,('CA0','4610851768','2012-07-02')
        ,('CA0','4610851768','2012-06-02')
        ,('8B0','4565853935','2012-05-02')
        ,('8B0','4565854146','2009-04-02')
        ,('8B0','4565854146','2009-03-02')
        ,('9B0','4565854283','2010-02-02')
        ,('9B0','4565854283','2011-01-02')
    ) AS TVC(A, B, C)
)

Yes, that requires you to put the exact same tuples in two different places to accomplish it.

The first place is joining your Table Values Constructor (TVC) against TENDORS, to ensure the right value is updated to each record.

The second place is ensuring that your update statement ONLY updates records that you want to update.

If you exclude the second part, it will attempt to update EVERY record in your database, assigning a null value to TENDORS_VALUE if there's no matching tuple in the TVC. Three guesses how I know about that.

Technically, you only need the second and third values in the tuples for the lower TVC but doing it this way means you can create one set of tuples and just paste it into two places. Alternately, if there is some simpler criteria you can apply to the second part, such that it selects only the records you want to update, you could do that.

And yes, you do need to define the TVC twice. If you define it in the outer where clause, it isn't visible in the inner select clause and vice versa.

Ideally, you could put the TVC into a Common Table Expression (CTE), as Paul Vernon suggests, but the versions of DB2 I'm messing with do NOT appear to support that.