I have a db2 procedure which detaches a partition into temp table and then attaches back the partition. When I execute the detach, drop and attach from rapid sql it works perfectly fine but when I execute it from the stored procedure I see this error.
The object "udbadm.table_name" of type table cannot be altered because it is currently in use by the same application process. Please advice. Below is my stored procedure.
Edited the code to include dbms_alert.sleep(120); Seeing the error A null value was specified in a context where null value is not allowed.sqlstate 22004
db2 version is 10.5. I am executing this from rapid sql but when invoked from line processor as db2 => call OSL_CLNUP_MNTHLY_SBLDGR_DET(98,56,'08/31/2018',5) I received same error.
DROP SPECIFIC PROCEDURE OSLD02.OSL_CLNUP_MNTHLY_SBLDGR_DET
;
CREATE PROCEDURE OSLD02.OSL_CLNUP_MNTHLY_SBLDGR_DET(IN
IN_ID_BUS_PROCSS SMALLINT,
IN IN_ID_RUN SMALLINT,
IN IN_DT_EOP DATE,
IN IN_BATCH_SIZE INTEGER)
SPECIFIC OSLD02.OSL_CLNUP_MNTHLY_SBLDGR_DET
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN
-------------------------------------------------------------------------- ---------------------------------------
-------------------- Declarations
-------------------------------------------------------------------------- ---------------------------------------
declare v_id_task_log integer;
declare v_cnt_deleted integer;
declare v_cnt_initial integer;
declare v_count integer;
declare v_cd_acctg_purp character(10);
declare v_month_eop integer;
declare SQLCODE int default 0;
declare v_sqlcode int;
declare v_partitionName character(20);
declare v_lowValue character(10);
declare v_highValue character(10);
declare v_detachStmt varchar(512);
declare v_dropTable varchar(512);
declare SQL_STMT varchar(512);
declare v_exist int;
declare v_stagingSchemaTable varchar(512);
DECLARE v_outmessage VARCHAR(32672);
DECLARE v_outstatus integer default 0;
DECLARE v_seconds INTEGER default 300;
declare wait_until timestamp;
declare detach_complete_flg character(2) default 'N';
declare loop_limit int default 0;
declare global temporary table session.detail_tmp
( partition character(10), start character(30), end character(30) ) on
commit preserve rows with replace not logged;
BEGIN
DECLARE C1 CURSOR WITH HOLD FOR
select DATAPARTITIONNAME, LOWVALUE, HIGHVALUE
from syscat.DATAPARTITIONS where tabname='SBLDGR_DET'
and (SUBSTR(HIGHVALUE, 2,LOCATE(',',HIGHVALUE)-3)
,SUBSTR(HIGHVALUE, LOCATE(',',HIGHVALUE)+1) ) in (
select CD_ACCTG_PURP, mo_eop
from SBLDGR_DET
where mo_eop< cast (month(IN_DT_EOP) as char(2))
group by CD_ACCTG_PURP, mo_eop
having count(*)>0
)
with ur;
OPEN C1;--
set v_sqlcode=0;--
L1: LOOP
FETCH C1 INTO v_partitionName, v_lowValue,v_highValue;
IF SQLCODE<> 0 THEN
LEAVE L1;
END IF;
set v_detachStmt = 'ALTER TABLE SBLDGR_DET DETACH PARTITION '
||v_partitionName|| ' INTO TABLE OSLSTG.SL_DET_'||v_partitionName;
EXECUTE IMMEDIATE v_detachStmt ;
commit;
while (loop_limit < 10000 and detach_complete_flg = 'N') DO
call dbms_alert.sleep(60);
IF EXISTS (SELECT 1 FROM SYSCAT.DATAPARTITIONS WHERE TABSCHEMA='OSLSTG'
AND
TABNAME='SL_DET_'|| v_partitionName AND STATUS=' ')
THEN
set detach_complete_flg = 'Y';
END IF;
set loop_limit = loop_limit + 1;
end while;
set v_dropTable = 'drop table OSLSTG.SL_DET_'||v_partitionName;
EXECUTE IMMEDIATE v_dropTable ;
commit;
SET SQL_STMT = 'ALTER TABLE sbldgr_det ADD
PARTITION '||v_partitionName||' STARTING FROM (' || v_lowValue|| ' )
ENDING AT (' || v_highValue|| ')';
EXECUTE IMMEDIATE SQL_STMT;
commit;
END LOOP L1;
CLOSE C1;--
END
;
END
;
commit;
There is a number of problems in your code. I'm not going to provide you a complete solution, just some notes.
1) If you commit during a cursor processing, you must DECLARE CURSOR with the WITH HOLD clause.
2) The following cursor processing is not correct.
WHILE (v_sqlcode = 0) DO
FETCH C1 INTO v_partitionName, v_lowValue,v_highValue;
set v_sqlcode=sqlcode;
set v_detachStmt = 'ALTER TABLE SBLDGR_DET DETACH PARTITION'
||v_partitionName||' INTO TABLE OSLSTG.SL_DET_'||v_partitionName;
EXECUTE IMMEDIATE v_detachStmt;
...
END WHILE;
You still try to process data even you got the END OF CURSOR code after FETCH.
You must check SQLCODE immediately after FETCH. Use something like this:
L1: LOOP
FETCH C1 INTO v_partitionName, v_lowValue,v_highValue;
IF SQLCODE<>0 THEN LEAVE L1; END IF;
...
END LOOP L1;
3) You should do something like below.
After:
EXECUTE IMMEDIATE v_detachStmt;
commit;
You should implement:
-- Check in loop (sleep, let's say N sec, after each check)
-- until you get a row with the following statement
-- (construct your table name dynamically):
SELECT 1
FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA='OSLSTG' AND TABNAME='SL_DET_v_partitionName' AND STATUS=''
WITH UR;
Look at the Detaching data partitions topic for more detail.