Hi there

I made a simple partitioned table (like a federated original):
CREATE TABLE schema.home_table LIKE schema.remote_table
PARTITION BY RANGE(id) (
PARTITION dummy_partition STARTING 0 ENDING 0,
);

Next i try to create/refresh the data within the home partition with a stored procedure.
Creating a new table like the federated, catching the information i want and put it into the home table as a partition is no problem.
But at the moment i try to refresh the data with a new partition i get this error:
SQL20285N The statement or command is not allowed because the table named
"SCHEMA.HOME_TABLE_BACKUP" has detached dependents or the asynchronous
partition detach operation is not complete. Reason code = "2". SQLSTATE=55057

Now i wonder why this happens and hope you can help me here.

Here is the procedure:
CREATE PROCEDURE IPMS.RELOAD_TABLE_SP_EXT (IN source VARCHAR(261),IN destination VARCHAR(261),IN column_to_use VARCHAR(261), in id_to_use BIGINT) SPECIFIC TABLE_RELOAD_SP_EXT
P1: BEGIN ATOMIC
DECLARE create_temp_tbl VARCHAR(1000);
DECLARE drop_temp_tbl VARCHAR(1000);
DECLARE insert_data VARCHAR(1000);
DECLARE drop_backup_tbl VARCHAR(1000);
DECLARE name_ptn VARCHAR(1000);
DECLARE detach_ptn VARCHAR(1000);
DECLARE attach_ptn VARCHAR(1000);
DECLARE integrity_ptn VARCHAR(1000);
DECLARE temp_tbl_count INTEGER DEFAULT 0;
DECLARE ptn_count INTEGER DEFAULT 0;
DECLARE backup_tbl_count INTEGER DEFAULT 0;

DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE stmt_string VARCHAR(1000);
DECLARE stmt VARCHAR(1000);
DECLARE result_cursor CURSOR WITH RETURN FOR stmt;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

CASE UPPER(destination)
WHEN 'SCHEMA.HOME_TABLE' THEN
SET name_ptn = 'NEW_PARTITION_';
WHEN 'SCHEMA.OTHER_TABLE' THEN
SET name_ptn = 'ANOTHER_NEW_PARTITION_';
WHEN ...;
ELSE
SET name_ptn = 'unknown';
END CASE;
-- get ids as partition ranges:
SET stmt_string = 'SELECT DISTINCT COALESCE((SELECT DISTINCT ' || column_to_use || ' FROM ' || source || ' WHERE ' || column_to_use || ' = ' || id_to_use || '), ' || column_to_use || ') AS id FROM ' || source || ' WHERE ' || column_to_use || ' > 0 ORDER BY 1 DESC';
PREPARE stmt FROM stmt_string;
OPEN result_cursor;

-- loop through the ids and create new data on the home system:
fetch_loop: REPEAT
FETCH result_cursor INTO id_to_use;
SET create_temp_tbl = 'CREATE TABLE SCHEMA.' || name_ptn || id_to_use || '_temp like ' || source;
SET drop_temp_tbl = 'DROP TABLE SCHEMA.' || name_ptn || id_to_use || '_temp';
SET insert_data = 'INSERT INTO SCHEMA.' || name_ptn || id_to_use || '_temp SELECT * FROM ' || source || ' where ' || column_to_use || ' = ' || id_to_use || ' with ur';
SET drop_backup_tbl = 'DROP TABLE SCHEMA.' || name_ptn || id_to_use || '_backup';
SET detach_ptn = 'ALTER TABLE ' || destination || ' DETACH PARTITION ' || name_ptn || id_to_use || ' INTO SCHEMA.' || name_ptn || id_to_use || '_backup';
SET attach_ptn = 'ALTER TABLE ' || destination || ' ATTACH PARTITION ' || name_ptn || id_to_use || ' STARTING ' || id_to_use || ' ENDING ' || id_to_use || ' FROM TABLE SCHEMA.' || name_ptn || id_to_use || '_temp';
SET integrity_ptn = 'SET INTEGRITY FOR ' || destination || ' IMMEDIATE CHECKED';

-- lookup if temp table (new partition) exists and delete if it is so:
SELECT COUNT(*) INTO temp_tbl_count FROM SYSCAT.TABLES WHERE TABNAME = upper(substr(name_ptn || id_to_use || '_temp', locate('.', name_ptn || id_to_use || '_temp')+1));
IF (temp_tbl_count > 0) THEN
EXECUTE IMMEDIATE drop_temp_tbl;
END IF;

-- create new temp table and fill it with remote data:
EXECUTE IMMEDIATE create_temp_tbl;
EXECUTE IMMEDIATE insert_data;

-- lookup if backup table (old partition) exists and delete if it is so:
SELECT COUNT(*) INTO backup_tbl_count FROM SYSCAT.TABLES WHERE TABNAME = upper(substr(name_ptn || id_to_use || '_backup', locate('.', name_ptn || id_to_use || '_backup')+1));
IF (backup_tbl_count > 0) THEN
EXECUTE IMMEDIATE drop_backup_tbl;
END IF;

-- lookup if new partition name exists and detach if it is so:
SELECT count(*) INTO ptn_count FROM SYSCAT.DATAPARTITIONS WHERE TABSCHEMA = 'SCHEMA' AND TABNAME = upper(substr(destination, locate('.', destination)+1)) AND datapartitionname = name_ptn || id_to_use;
IF (ptn_count > 0) THEN
EXECUTE IMMEDIATE detach_ptn;
END IF;

-- attach new partition into home table and setting integrity:
EXECUTE IMMEDIATE attach_ptn;
EXECUTE IMMEDIATE integrity_ptn;
UNTIL at_end > 0
END REPEAT fetch_loop;
CLOSE result_cursor;
END P1

The calling is like this:
call schema.reload_table_sp_ext('schema.remote_table', 'schema.home_table', 'filter_id', 5)
filter_id: the column name for the ids/partition range
5: the id that should get refreshed

If you need more information please don't hesitate to write about it :-)

Detach/Attach is an asynchronous command, sort of like inplace (online) backup. This is mainly due to global indexes, which need to modified for each detach/attach. Even if all indexes are partitioned, you should wait a few seconds for the command to complete before you assume it is done. Please see other thread about the complexity of making sure all indexes can be partitioned (the partitioning column must be a part of all unique indexes).

At first: Thanks for reply

Year i know that's asynchronous.
But when i am using a for loop with a fixed select statement with one id (range) the refresh goes into the table.
Therefore i think the must be something i didn't notice. Maybe i am blind because of several days of scripting

I try to make some kind of batch to refill the complete table WITH the advantage of replacing one range in a sec.
- I am not a database administrator and that is the main reason why i am here.

Hello again :-)

I've found a good way by myself:
CREATE PROCEDURE IPMS.RELOAD_TABLE_SP (IN source VARCHAR(261),IN destination VARCHAR(261),IN column_to_use VARCHAR(261), IN id_to_use INTEGER) SPECIFIC TABLE_RELOAD_SP_EXT
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.RANGES (id INTEGER) ON COMMIT DELETE ROWS WITH REPLACE;
P2: BEGIN ATOMIC
DECLARE create_dest_tbl VARCHAR(1000);
DECLARE create_temp_tbl VARCHAR(1000);
DECLARE drop_temp_tbl VARCHAR(1000);
DECLARE insert_data VARCHAR(1000);
DECLARE drop_backup_tbl VARCHAR(1000);
DECLARE name_ptn VARCHAR(1000);
DECLARE detach_ptn VARCHAR(1000);
DECLARE attach_ptn VARCHAR(1000);
DECLARE integrity_ptn VARCHAR(1000);
DECLARE counter INTEGER DEFAULT 0;
DECLARE insert_rng VARCHAR(1000);
DECLARE name_sma VARCHAR(261);

SET name_ptn = column_to_use || '_';
SET name_sma = UPPER(SUBSTR(destination, 1, LOCATE('.', destination)- 1));

SET insert_rng = 'insert into SESSION.RANGES SELECT DISTINCT COALESCE((SELECT DISTINCT ' || column_to_use || ' AS id FROM ' || source || ' WHERE ' || column_to_use || ' = ' || id_to_use || '), ' || column_to_use || ') AS id FROM ' || source || ' WITH UR';
EXECUTE IMMEDIATE insert_rng;

SET create_dest_tbl = 'CREATE TABLE ' || destination || ' LIKE ' || source || ' PARTITION BY RANGE(' || column_to_use || ') (PARTITION dummy_partition STARTING 0 ENDING 0)';
SELECT COUNT(*) INTO counter FROM SYSCAT.TABLES WHERE TABNAME = UPPER(SUBSTR(destination, LOCATE('.', destination)+1));
IF (counter = 0) THEN EXECUTE IMMEDIATE create_dest_tbl; END IF;

FOR result AS SELECT * FROM session.ranges DO
SET id_to_use = result.id;
SET create_temp_tbl = 'CREATE TABLE ' || name_sma || '.' || name_ptn || id_to_use || '_temp LIKE ' || source;
SET drop_temp_tbl = 'DROP TABLE ' || name_sma || '.' || name_ptn || id_to_use || '_temp';
SET insert_data = 'INSERT INTO ' || name_sma || '.' || name_ptn || id_to_use || '_temp SELECT * FROM ' || source || ' WHERE ' || column_to_use || ' = ' || id_to_use || ' WITH UR';
SET drop_backup_tbl = 'DROP TABLE ' || name_sma || '.' || name_ptn || id_to_use || '_backup';
SET detach_ptn = 'ALTER TABLE ' || destination || ' DETACH PARTITION ' || name_ptn || id_to_use || ' INTO ' || name_sma || '.' || name_ptn || id_to_use || '_backup';
SET attach_ptn = 'ALTER TABLE ' || destination || ' ATTACH PARTITION ' || name_ptn || id_to_use || ' STARTING ' || id_to_use || ' ENDING ' || id_to_use || ' FROM TABLE ' || name_sma || '.' || name_ptn || id_to_use || '_temp';
SET integrity_ptn = 'SET INTEGRITY FOR ' || destination || ' IMMEDIATE CHECKED';

SELECT COUNT(*) INTO counter FROM SYSCAT.TABLES WHERE TABNAME = UPPER(SUBSTR(name_ptn || id_to_use || '_temp', LOCATE('.', name_ptn || id_to_use || '_temp')+1));
IF (counter > 0) THEN EXECUTE IMMEDIATE drop_temp_tbl; END IF;
EXECUTE IMMEDIATE create_temp_tbl; EXECUTE IMMEDIATE insert_data;

SELECT COUNT(*) INTO counter FROM SYSCAT.TABLES WHERE TABNAME = UPPER(SUBSTR(name_ptn || id_to_use || '_backup', LOCATE('.', name_ptn || id_to_use || '_backup')+1));
IF (counter > 0) THEN EXECUTE IMMEDIATE drop_backup_tbl; END IF;

SELECT COUNT(*) INTO counter FROM SYSCAT.DATAPARTITIONS WHERE TABSCHEMA = name_sma AND TABNAME = UPPER(SUBSTR(destination, LOCATE('.', destination)+1)) AND datapartitionname = UPPER(name_ptn || id_to_use);
IF (counter > 0) THEN EXECUTE IMMEDIATE detach_ptn; END IF;
EXECUTE IMMEDIATE attach_ptn;
EXECUTE IMMEDIATE integrity_ptn;
END FOR; END P2; END P1

With this code is it possible to create partitioned tables like a source table and reload the partitions in a totally generic way

The only thing i need to know is how to handle negative calling parameter values because id_to_use could be negative (e.g. -1) and the execution ends in:
SQL0103N The numeric literal "1_temp" is not valid. SQLSTATE=42604

EDIT:
I was wrong. The error occurs in creating tables because of the minus of the negative value.
My workarround was to add the following and use the new variable everywhere instead of 'id_to_use' except the ranges in the attach part and the source-select in 'insert_data':
DECLARE string_id VARCHAR(10);

SET string_id = id_to_use;
IF (id_to_use < 0) THEN SET string_id = REPLACE(string_id, '-', 'n'); END IF;

This is the needed code:
CREATE PROCEDURE IPMS.RELOAD_TABLE_SP (IN source VARCHAR(261),IN destination VARCHAR(261),IN column_to_use VARCHAR(261), IN id_to_use INTEGER) SPECIFIC TABLE_RELOAD_SP_EXT
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.RANGES (id INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE;

P2: BEGIN ATOMIC
DECLARE create_dest_tbl VARCHAR(1000);
DECLARE create_temp_tbl VARCHAR(1000);
DECLARE drop_temp_tbl VARCHAR(1000);
DECLARE insert_data VARCHAR(1000);
DECLARE drop_backup_tbl VARCHAR(1000);
DECLARE name_ptn VARCHAR(1000);
DECLARE detach_ptn VARCHAR(1000);
DECLARE attach_ptn VARCHAR(1000);
DECLARE integrity_ptn VARCHAR(1000);
DECLARE counter INTEGER DEFAULT 0;
DECLARE update_all INTEGER DEFAULT -9999;
DECLARE insert_rng VARCHAR(1000);
DECLARE name_sma VARCHAR(261);
DECLARE string_id VARCHAR(10);
DECLARE error_msg VARCHAR(261);

SET error_msg = 'ID_TO_USE doesn''t extst! Use a valid id or ' || update_all || ' for complete update.';
SET name_ptn = column_to_use || '_';
SET name_sma = UPPER(SUBSTR(destination, 1, LOCATE('.', destination)- 1));
SET insert_rng = 'insert into SESSION.RANGES SELECT DISTINCT COALESCE((SELECT DISTINCT ' || column_to_use
|| ' AS id FROM ' || source || ' WHERE ' || column_to_use || ' = ' || id_to_use || '), ' || column_to_use || ') AS id FROM ' || source || ' WITH UR';
EXECUTE IMMEDIATE insert_rng;

SELECT COUNT(*) INTO counter FROM SESSION.RANGES;
IF (id_to_use = update_all OR counter = 1) THEN
SET create_dest_tbl = 'CREATE TABLE ' || destination || ' LIKE ' || source || ' PARTITION BY RANGE(' || column_to_use
|| ') (PARTITION dummy_partition STARTING 0 ENDING 0)';

SELECT COUNT(*) INTO counter FROM SYSCAT.TABLES WHERE TABNAME = UPPER(SUBSTR(destination, LOCATE('.', destination)+1));
IF (counter = 0) THEN
EXECUTE IMMEDIATE create_dest_tbl;
END IF;

FOR result AS SELECT * FROM session.ranges DO
SET id_to_use = result.id;
SET string_id = id_to_use;
IF (id_to_use < 0) THEN
SET string_id = REPLACE(string_id, '-', 'n'); END IF;
SET create_temp_tbl = 'CREATE TABLE ' || name_sma || '.' || name_ptn || string_id || '_temp LIKE ' || source;
SET drop_temp_tbl = 'DROP TABLE ' || name_sma || '.' || name_ptn || string_id || '_temp';
SET insert_data = 'INSERT INTO ' || name_sma || '.' || name_ptn || string_id || '_temp SELECT * FROM ' || source || ' WHERE '
|| column_to_use || ' = ' || id_to_use || ' WITH UR';
SET drop_backup_tbl = 'DROP TABLE ' || name_sma || '.' || name_ptn || string_id || '_backup';
SET detach_ptn = 'ALTER TABLE ' || destination || ' DETACH PARTITION ' || name_ptn || string_id || ' INTO '
|| name_sma || '.' || name_ptn || string_id || '_backup';
SET attach_ptn = 'ALTER TABLE ' || destination || ' ATTACH PARTITION ' || name_ptn || string_id || ' STARTING ' || id_to_use
|| ' ENDING ' || id_to_use || ' FROM TABLE ' || name_sma || '.' || name_ptn || string_id || '_temp';
SET integrity_ptn = 'SET INTEGRITY FOR ' || destination || ' IMMEDIATE CHECKED';

SELECT COUNT(*) INTO counter FROM SYSCAT.TABLES WHERE TABNAME = UPPER(SUBSTR(name_ptn || string_id
|| '_temp', LOCATE('.', name_ptn || string_id || '_temp')+1));
IF (counter > 0) THEN
EXECUTE IMMEDIATE drop_temp_tbl;
END IF;

EXECUTE IMMEDIATE create_temp_tbl;
EXECUTE IMMEDIATE insert_data;

SELECT COUNT(*) INTO counter FROM SYSCAT.TABLES WHERE TABNAME = UPPER(SUBSTR(name_ptn || string_id
|| '_backup', LOCATE('.', name_ptn || string_id || '_backup')+1));

IF (counter > 0) THEN
EXECUTE IMMEDIATE drop_backup_tbl;
END IF;

SELECT COUNT(*) INTO counter FROM SYSCAT.DATAPARTITIONS WHERE TABSCHEMA = name_sma
AND TABNAME = UPPER(SUBSTR(destination, LOCATE('.', destination)+1))
AND datapartitionname = UPPER(name_ptn || string_id);

IF (counter > 0) THEN
EXECUTE IMMEDIATE detach_ptn;
END IF;

EXECUTE IMMEDIATE attach_ptn;
EXECUTE IMMEDIATE integrity_ptn;
END FOR;
ELSE
SIGNAL SQLSTATE '01H01' SET MESSAGE_TEXT = error_msg;
END IF;
END P2;
END P1

I posted this code here because of the possibility that someone else could need it too