Release Notes

50.10 Federated Limitations with MPP Partitioned Tables

When you attempt to use one SQL statement to select data from a data source and insert, update, or delete the data directly in an MPP partitioned table on your DB2 federated server, you will receive the SQL0901N error. The federated functionality does not allow you to select from a nickname and insert into an MPP partitioned table.

Once you apply FixPak 4 (or above), you can use these steps to select data and insert the data into an MPP partitioned table:

1. In the customer application environment, export the DB2NODE environment variable to designate the node to which the application should always connect.

          EXPORT DB2NODE=x 
   

   where x is a node number.

2. Create a nodegroup which contains only the designated node.

          CREATE NODEGROUP nodegroup_name ON NODE(x)
   

   where x is the node number.

3. Create a tablespace in the nodegroup.

   CREATE TABLESPACE tablespace_name IN NODEGROUP nodegroup_name
   

4. Create a temporary table in the tablespace.

   CREATE TABLE temp_table_name IN tablespace_name
   

5. Divide the INSERT operation in the application into two steps:
   • INSERT INTO temp_table_name SELECT * FROM nickname
   • INSERT INTO MPP_partitioned_table SELECT * from temp_table_name

Dividing the INSERT statement into two statements changes the statement level commit and rollback semantics. For example, instead of rolling back one statement, you will now have to rollback two statements. Additionally, if you change the node number associated with the DB2NODE environment variable, you must invalidate the application package and rebind.

These steps allow you to select data from data sources and insert the data into an MPP partitioned table. You will still receive the SQL0901N error when you attempt to use one statement to select data from a data source and update or delete the data in an MPP partitioned table. This restriction will be elimiated in DB2 Universal Database Version 8.