Hi, I am running UDB 7.2. While running the stored procedure below it runs for about 4 min then gives this error:
SQL0289N Unable to allocate new pages in table space "SIEBEL_32K". SQLSTATE=57011

table SIEBEL.EIM_CONTACT uses table space SIEBEL_32K. I can do inserts manually. My question is this: is there something wrong with my logic that could be causing this, or is there a table space setting that needs to be changed? There are only a few records in DO_NOT_CALL_LIST table and there are just one or two records in the inner cursor during each loop thru the outer cursor. v_SQL_STMT returns 68,885 records if you leave off the where clause.

Thanks for the help

See next msg for SP----->

CREATE PROCEDURE CRMVIEW.DO_NOT_CALL()
LANGUAGE SQL
BEGIN

------------------------------------------------
--------------DECLARE and SET
------------------------------------------------
DECLARE v_counter INT DEFAULT 0;
DECLARE v_PARTY_UID VARCHAR(100) default 'test';
DECLARE v_PARTY_TYPE_CD VARCHAR(30) default 'test';
DECLARE v_PERSON_UID VARCHAR(100);
DECLARE v_BU_ID VARCHAR(15);
DECLARE v_HOME_PH_NUM VARCHAR(40);
DECLARE v_REMOVE_THIS_NUM VARCHAR(40);
DECLARE v_SQL_STMT VARCHAR(200);
DECLARE v_ROW_ID VARCHAR(15) default '1';

DECLARE at_end INT DEFAULT 0;
--DECLARE at_end_inner INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE OUTER_CURSOR CURSOR FOR
SELECT PHONE_NUMBER FROM CRMVIEW.DO_NOT_CALL_LIST;

DECLARE INNER_CURSOR CURSOR FOR s1;

DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

SET v_SQL_STMT = 'SELECT P.PARTY_UID, P.PARTY_TYPE_CD, C.PERSON_UID, C.BU_ID, C.HOME_PH_NUM ' ||
'FROM SIEBEL.S_PARTY P INNER JOIN SIEBEL.S_CONTACT C ON P.ROW_ID = C.PAR_ROW_ID ' ||
'WHERE C.HOME_PH_NUM = ?';

-------------------------------------------------
-----------------LOOPS
-------------------------------------------------
OPEN OUTER_CURSOR;
WHILE at_end = 0 DO
FETCH OUTER_CURSOR INTO v_REMOVE_THIS_NUM;
PREPARE s1 FROM v_SQL_STMT;
WHILE at_end = 0 DO
OPEN INNER_CURSOR USING v_REMOVE_THIS_NUM;
FETCH INNER_CURSOR INTO v_PARTY_UID, v_PARTY_TYPE_CD, v_PERSON_UID, v_BU_ID, v_HOME_PH_NUM;
INSERT INTO SIEBEL.EIM_CONTACT(ROW_ID, IF_ROW_BATCH_NUM, IF_ROW_STAT, PARTY_UID, PARTY_TYPE_CD, CON_PERSON_UID, CON_BU, CON_HOME_PH_NUM, CON_SUPPRESSCALLFL) VALUES (v_ROW_ID, 1, '1', v_PARTY_UID, v_PARTY_TYPE_CD, v_PERSON_UID, v_BU_ID, v_HOME_PH_NUM, 'Y');
CLOSE INNER_CURSOR;
SET v_ROW_ID = CHAR(INTEGER(v_ROW_ID) + 1);
END WHILE;
--CLOSE INNER_CURSOR;
END WHILE;
CLOSE OUTER_CURSOR;
-------------------------------------------------
END

$ db2 "? SQL0289N"

SQL0289N Unable to allocate new pages in table space
"<tablespace-name>".

Explanation: One of the following conditions is true:

1. One of the containers assigned to this SMS table space has reached the maximum file size. This is the likely cause of the error.

2. All the containers assigned to this DMS table space are
full. This is the likely cause of the error.

3. The table space object table for this DMS table space is full.

4. A rebalance is in progress, but has not progressed far enough to enable the newly added space to be used.

5. A redirected restore is being done to containers that are too small.

6. A rollforward is being done following a redirected restore and all the containers assigned to this tablespace are full.

7. A rollforward skipping add containers is being done and all the containers assigned to this tablespace are full.

Details can be found in the system error log and/or the database manager error log.

User Response: Perform the action corresponding to the cause of the error:

1. either switch to a DMS TABLESPACE or recreate the SMS TABLESPACE with more directories (PATHs) such that: (number of directories) >= (max tablesize / maxfilesize). Note that maximum file size is operating system dependent.

2. add new container(s) to the DMS table space and try the operation again, after the rebalancer has made the new pages available for use.

3. drop unnecessary tables from this DMS table space.

4. wait for the rebalancer to make more progress.

5. perform the redirected restore again to larger containers.

6. perform the redirected restore again to larger containers.

7. perform the rollforward again allowing add containers, or perform a redirected restore to larger containers.

sqlcode: -289

sqlstate: 57011