Topic
  • 4 replies
  • Latest Post - ‏2014-01-18T22:30:18Z by G.Paulus
MIAO
MIAO
3 Posts

Pinned topic Does UPDATE statement on a partitioned table change the physical storage of a record if partition key was changed?

‏2014-01-16T18:37:47Z |

If I have a partitioned table (range partition), does the UPDATE statement change the physical storage of a record if partition key was changed by the UPDATE statement? I.e., does the affected record physically move out the old page, and will be inserted into the new page which belongs to partition 2?

For example, if I create a table PRODUCT which is partitioned by product id and it has two partitions: partition 1 has product id from 0 to 100, and partition 2 has all the left. I have a record which has product id = 30 which should be in partition 1. I then update that record by change that product id to 200 which should bring the record into different partition. My question is: does the UPDATE statement will move the record from its original page and move it into a page which belongs to partition 2? Or DB2 have a different mechanism which do not need physically change this affected record?

Could anyone provide any link which has details or the way to verify it?

 

 

Thanks,

 

Miao

  • G.Paulus
    G.Paulus
    10 Posts
    ACCEPTED ANSWER

    Re: Does UPDATE statement on a partitioned table change the physical storage of a record if partition key was changed?

    ‏2014-01-18T16:54:17Z  
    • MIAO
    • ‏2014-01-18T16:03:58Z

     

    Hi Gerhard,

    Thanks for the information. I think I would more like to confirm the row is 'pysically' re-located to the new page. I.e., the sequence number of that partition (in your example) does indicate the 'pysical' location, not 'logical'.

    We actually did the similar test on ZOS, and we select on SYSIBM.SYSTABLEPART table and I got the same result as yours. But we are able to pin the physical page on the ZOS and our test shows a new recorded was inserted into new page 'physically'. I more like to do the same test on the UNIX (or Windows).

    But if DB2 implemented it the same way, then it would be more likely DB2 will reloate it physically as well on UNIX.

     

    Thanks,

     

    Miao

    Hi Miao,

     

    I did another test to find out if the updated row is physically moved to the new datapartition. I created the same table like in my first example but separating each data partition in several tablespaces TBSP_PART0, TBSP_PART1. I inserted the same two rows and then I dumped the content of the two tablespaces.

    db2dart /DDEL Tablepsace TBSP_PART0

    5,"I = 5"

    db2dart /DDEL Tablespace TBSP_PART1

    15,"I = 15"

     

    This shows after the insert the rows are physically located in the two tablespaces.

    So now I updated the row UPDATE ... SET i=11 WHERE i = 5

    After the update I dumped the two tablespaces again and this was the result:

    db2dart /DDEL Tablepsace TBSP_PART0

    db2dart /DDEL Tablespace TBSP_PART1

    11,"I = 5"

    15,"I = 15"

     

    So an update of the range partition column relocates the row physically to the target datapartition.

    So it looks like that DB2 LUW is implemented in the same way like DB2 z/OS.

     

    Gerhard

  • G.Paulus
    G.Paulus
    10 Posts

    Re: Does UPDATE statement on a partitioned table change the physical storage of a record if partition key was changed?

    ‏2014-01-17T17:31:29Z  

    Hello Miao,

    UPDATE the Range Distribution Key definitively move the updated row from the old page to the new page into the "target" data partition the row fits.

    You can simply check this with the funtion DATAPARTITIONNUM( <RangeKeyColumn> ).

    Look at this small examle.

     

    CREATE TABLE DB2INST2.TEST_RANGE_02 ( I SMALLINT NOT NULL, TEXT VARCHAR(24),PRIMARY KEY ( I ) ) PARTITION BY RANGE ( I NULLS LAST ) ( PARTITION PART0 STARTING FROM ( 1 ) INCLUSIVE ENDING AT ( 10 ), PARTITION PART1 STARTING FROM ( 11 ) INCLUSIVE ENDING AT ( 20 ) )
    DB20000I  The SQL command completed successfully.

    INSERT INTO DB2INST2.TEST_RANGE_02 (I, TEXT) VALUES (5, 'I = 5'), (15, 'I = 15')
    DB20000I  The SQL command completed successfully.

    SELECT DATAPARTITIONNUM(I) AS PART_ID, I, TEXT FROM DB2INST2.TEST_RANGE_02

    PART_ID     I      TEXT                    
    ----------- ------ ------------------------
              0      5 I = 5                   
              1     15 I = 15                  

      2 record(s) selected.


    UPDATE DB2INST2.TEST_RANGE_02 SET I = 11 WHERE I = 5
    DB20000I  The SQL command completed successfully.

    SELECT DATAPARTITIONNUM(I) AS PART_ID, I, TEXT FROM DB2INST2.TEST_RANGE_02

    PART_ID     I      TEXT                    
    ----------- ------ ------------------------
              1     11 I = 5                   
              1     15 I = 15                  

      2 record(s) selected.

     

    Here is a link to the function DATAPARTITIONNUM http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0021352.html

    There is a similar function for HASH partitioning DBPARTITIONNUM(<HashKeyColumn>) in a DPF database to determine the database partition a row resides: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000832.html

     

    Hope this helps and answers your question :)

     

    Best Regards,

    Gerhard Paulus

    Updated on 2014-01-17T17:38:37Z at 2014-01-17T17:38:37Z by G.Paulus
  • MIAO
    MIAO
    3 Posts

    Re: Does UPDATE statement on a partitioned table change the physical storage of a record if partition key was changed?

    ‏2014-01-18T16:03:58Z  
    • G.Paulus
    • ‏2014-01-17T17:31:29Z

    Hello Miao,

    UPDATE the Range Distribution Key definitively move the updated row from the old page to the new page into the "target" data partition the row fits.

    You can simply check this with the funtion DATAPARTITIONNUM( <RangeKeyColumn> ).

    Look at this small examle.

     

    CREATE TABLE DB2INST2.TEST_RANGE_02 ( I SMALLINT NOT NULL, TEXT VARCHAR(24),PRIMARY KEY ( I ) ) PARTITION BY RANGE ( I NULLS LAST ) ( PARTITION PART0 STARTING FROM ( 1 ) INCLUSIVE ENDING AT ( 10 ), PARTITION PART1 STARTING FROM ( 11 ) INCLUSIVE ENDING AT ( 20 ) )
    DB20000I  The SQL command completed successfully.

    INSERT INTO DB2INST2.TEST_RANGE_02 (I, TEXT) VALUES (5, 'I = 5'), (15, 'I = 15')
    DB20000I  The SQL command completed successfully.

    SELECT DATAPARTITIONNUM(I) AS PART_ID, I, TEXT FROM DB2INST2.TEST_RANGE_02

    PART_ID     I      TEXT                    
    ----------- ------ ------------------------
              0      5 I = 5                   
              1     15 I = 15                  

      2 record(s) selected.


    UPDATE DB2INST2.TEST_RANGE_02 SET I = 11 WHERE I = 5
    DB20000I  The SQL command completed successfully.

    SELECT DATAPARTITIONNUM(I) AS PART_ID, I, TEXT FROM DB2INST2.TEST_RANGE_02

    PART_ID     I      TEXT                    
    ----------- ------ ------------------------
              1     11 I = 5                   
              1     15 I = 15                  

      2 record(s) selected.

     

    Here is a link to the function DATAPARTITIONNUM http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0021352.html

    There is a similar function for HASH partitioning DBPARTITIONNUM(<HashKeyColumn>) in a DPF database to determine the database partition a row resides: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000832.html

     

    Hope this helps and answers your question :)

     

    Best Regards,

    Gerhard Paulus

     

    Hi Gerhard,

    Thanks for the information. I think I would more like to confirm the row is 'pysically' re-located to the new page. I.e., the sequence number of that partition (in your example) does indicate the 'pysical' location, not 'logical'.

    We actually did the similar test on ZOS, and we select on SYSIBM.SYSTABLEPART table and I got the same result as yours. But we are able to pin the physical page on the ZOS and our test shows a new recorded was inserted into new page 'physically'. I more like to do the same test on the UNIX (or Windows).

    But if DB2 implemented it the same way, then it would be more likely DB2 will reloate it physically as well on UNIX.

     

    Thanks,

     

    Miao

    Updated on 2014-01-18T16:08:56Z at 2014-01-18T16:08:56Z by MIAO
  • G.Paulus
    G.Paulus
    10 Posts

    Re: Does UPDATE statement on a partitioned table change the physical storage of a record if partition key was changed?

    ‏2014-01-18T16:54:17Z  
    • MIAO
    • ‏2014-01-18T16:03:58Z

     

    Hi Gerhard,

    Thanks for the information. I think I would more like to confirm the row is 'pysically' re-located to the new page. I.e., the sequence number of that partition (in your example) does indicate the 'pysical' location, not 'logical'.

    We actually did the similar test on ZOS, and we select on SYSIBM.SYSTABLEPART table and I got the same result as yours. But we are able to pin the physical page on the ZOS and our test shows a new recorded was inserted into new page 'physically'. I more like to do the same test on the UNIX (or Windows).

    But if DB2 implemented it the same way, then it would be more likely DB2 will reloate it physically as well on UNIX.

     

    Thanks,

     

    Miao

    Hi Miao,

     

    I did another test to find out if the updated row is physically moved to the new datapartition. I created the same table like in my first example but separating each data partition in several tablespaces TBSP_PART0, TBSP_PART1. I inserted the same two rows and then I dumped the content of the two tablespaces.

    db2dart /DDEL Tablepsace TBSP_PART0

    5,"I = 5"

    db2dart /DDEL Tablespace TBSP_PART1

    15,"I = 15"

     

    This shows after the insert the rows are physically located in the two tablespaces.

    So now I updated the row UPDATE ... SET i=11 WHERE i = 5

    After the update I dumped the two tablespaces again and this was the result:

    db2dart /DDEL Tablepsace TBSP_PART0

    db2dart /DDEL Tablespace TBSP_PART1

    11,"I = 5"

    15,"I = 15"

     

    So an update of the range partition column relocates the row physically to the target datapartition.

    So it looks like that DB2 LUW is implemented in the same way like DB2 z/OS.

     

    Gerhard

  • MIAO
    MIAO
    3 Posts

    Re: Does UPDATE statement on a partitioned table change the physical storage of a record if partition key was changed?

    ‏2014-01-18T22:25:52Z  
    • G.Paulus
    • ‏2014-01-18T16:54:17Z

    Hi Miao,

     

    I did another test to find out if the updated row is physically moved to the new datapartition. I created the same table like in my first example but separating each data partition in several tablespaces TBSP_PART0, TBSP_PART1. I inserted the same two rows and then I dumped the content of the two tablespaces.

    db2dart /DDEL Tablepsace TBSP_PART0

    5,"I = 5"

    db2dart /DDEL Tablespace TBSP_PART1

    15,"I = 15"

     

    This shows after the insert the rows are physically located in the two tablespaces.

    So now I updated the row UPDATE ... SET i=11 WHERE i = 5

    After the update I dumped the two tablespaces again and this was the result:

    db2dart /DDEL Tablepsace TBSP_PART0

    db2dart /DDEL Tablespace TBSP_PART1

    11,"I = 5"

    15,"I = 15"

     

    So an update of the range partition column relocates the row physically to the target datapartition.

    So it looks like that DB2 LUW is implemented in the same way like DB2 z/OS.

     

    Gerhard

    super!

    Thanks! :)