IBMI Iseries - Can't drop Index's in stored procedure

Question

Hey I'm trying to recreate a table with ~9 million records, when doing this, I'd like to drop/pause the index' on the table first, and then recreate/start them afterwards.

We are running IBMI 7.3.

I've created the stored procedure below to do this. But as soon as it hits the Drop index statement, it "Crashes/stops" but gives no error.

Anyone have any idea how to do this?

CREATE PROCEDURE YXDB.GENERATE_SEARCH_DATA()
LANGUAGE SQL MODIFIES SQL DATA
SET OPTION DBGVIEW=*SOURCE,COMMIT=*NONE,OUTPUT=*PRINT
P1: BEGIN
    DECLARE is_there_index INT;
    DECLARE zero_index INT;
    DECLARE first_index INT;
    DECLARE next_index INT;
    DECLARE pre_phonetic_string varchar(100);
    DECLARE post_phonetic_string varchar(100);
    DECLARE search_string_actionable varchar(100);

    DECLARE END_TABLE INT DEFAULT 0;
    DECLARE ADDRESS_KEY INT;
    DECLARE ADDRESS_INFO VARCHAR(500);
    DECLARE ACTIVE INT;

    DECLARE C1 CURSOR FOR
           select distinct faadrnr,
            info, 
            (case 
                WHEN FKRELTYP like 'FFADRESSE%' AND FKKNTNR = 0
                THEN (select 1 from dual)
                WHEN FKRELTYP like 'SMOLEVADR%' AND FKPRDREF = 0
                THEN (ifnull((select 0 from somstp where sostat = 9 and sokunr = fkkntnr), ifnull((select 0 from frnkkop where (fkstatus in (2,9) or fklukdat <> '01.01.0001') and frnkkop.fkkntnr = alldata.fkkntnr),1)))
                WHEN FKRELTYP like 'SMOLEVADR%' AND FKPRDREF <> 0
                THEN (ifnull((select 0 from lsmstp where lsstat = 9 and lskunr = fkkntnr and lslvnr=fkprdref), ifnull((select 0 from frnkkop where (fkstatus in (2,9) or fklukdat <> '01.01.0001') and frnkkop.fkkntnr = alldata.fkkntnr),1)))
                WHEN FKRELTYP like 'TNKLEVADR%' 
                THEN (ifnull((select 0 from tkmstp where tkvol < 2 and tkknnr = fkkntnr and tknr = fkprdref), ifnull((select 0 from frnkkop where (fkstatus in (2,9) or fklukdat <> '01.01.0001') and frnkkop.fkkntnr = alldata.fkkntnr),1)))
                WHEN (FKRELTYP like 'FAKTURAADR%' OR FKRELTYP like 'PDFFAKTURA%' OR FKRELTYP like 'KONTOUDTOG%' OR FKRELTYP like'PRISMED%' OR FKRELTYP like  'FFADRESSE%' OR FKRELTYP like 'KKLEVADR%' OR FKRELTYP like 'KKFADRESSE%') and fkkntnr <> 0
                THEN (ifnull((select 0 from frnkkop where (fkstatus in (2,9) or fklukdat <> '01.01.0001') and frnkkop.fkkntnr = alldata.fkkntnr),1)) END
            ) active
        from (
            select TRIM(TRIM(FANAVN1) ||' ' || TRIM(FANAVN2) ||' ' || TRIM(FAADR1) ||' ' || TRIM(FAADR2) ||' ' || TRIM(FASTED) ||' ' || TRIM(FAPOSNUM) ||' ' || TRIM(zipcodes.PNBYNAVN)) AS INFO, FKRELTYP, FKPRDREF, FKKNTNR, faadrnr
            from NHODATA.FRNADRP adresses 
            join NHODATA.POSNUMP zipcodes on zipcodes.PNLANDEKOD= adresses.FALANKOD AND adresses.FAPOSNUM = zipcodes.PNPOSTNUM 
            join FRNKONP stakeholderTypes on stakeholderTypes.FKKONIDN = adresses.FAADRNR and TRIM(FKRELTYP) in ('FFADRESSE','SMOLEVADR','TNKLEVADR','FAKTURAADR','KONTOUDTOG','KKLEVADR','KKFADRESSE','PDFFAKTURA')
          ) allData;
        DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET END_TABLE = 1;
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SET END_TABLE = 1;


        -------- DELETE TEMP DATA
        DELETE FROM YXDB.SEARCH_ACTIVE_TEMP_TABLE;
        DELETE FROM YXDB.SEARCH_QUERY_TEMP_TABLE;
        ------------GENERATE DATA
        OPEN C1;
        FETCH C1 INTO ADDRESS_KEY, ADDRESS_INFO, ACTIVE;
            WHILE END_TABLE = 0 DO
                SET first_index = 1;
                SET zero_index = 1;
                SET first_index = LOCATE(' ',ADDRESS_INFO,1);

                SET ADDRESS_INFO = TRIM(REPLACE(ADDRESS_INFO,'/',''));

               --// INSERTING DATA - removed for stackoverflow \\ --
                FETCH C1 INTO ADDRESS_KEY, ADDRESS_INFO, ACTIVE;
            END WHILE;
        CLOSE C1;
        -------- DELETE BLANKS
        delete from yxdb.search_query_temp_table where search_word = '';
        delete from yxdb.search_active_temp_table where search_word = '';  
        -------- DROP INDEX
        DROP INDEX yxdb.SQD_SEARCH_WORD_EVI;
        DROP INDEX yxdb.SQD_FAADRNR_I;
        DROP INDEX yxdb.SAD_SEARCH_WORD_EVI;
        DROP INDEX yxdb.SAD_FAADRNR_I;
        -------- DELETE DATA
        DELETE FROM YXDB.SEARCH_ACTIVE_DATA;
        DELETE FROM YXDB.SEARCH_QUERY_DATA;
        -------- COPY TEMP DATA TO DATA
        INSERT INTO YXDB.SEARCH_ACTIVE_DATA (select distinct * from yxdb.search_active_temp_table);
        INSERT INTO YXDB.SEARCH_QUERY_DATA (select distinct * from yxdb.search_query_temp_table);
        -------- RE-CREATE INDEXs
        CREATE ENCODED VECTOR INDEX SQD_SEARCH_WORD_EVI
           ON yxdb.SEARCH_QUERY_DATA ( SEARCH_WORD ) ;
        CREATE INDEX SQD_FAADRNR_I
            ON yxdb.SEARCH_QUERY_DATA (FAADRNR);
        CREATE ENCODED VECTOR INDEX yxdb.SAD_SEARCH_WORD_EVI
           ON yxdb.SEARCH_ACTIVE_DATA ( SEARCH_WORD );
        CREATE INDEX yxdb.SAD_FAADRNR_I
            ON yxdb.SEARCH_ACTIVE_DATA (FAADRNR); 
     END P1;

Answer 1

The reason why your PL/SQL terminates is that you are using an EXIT handler. When you try to drop and index that not exists yet, then your EXIT handler kicks in. It will then run the code in the exit handler and then exit the program.

I will suggest that you use yet another continue handler in this particular case, where you don't care if the index exists or not - before you drop it. And if you decide to do so, then remember to SET END_TABLE = 0 just before the loop begins, otherwise it might be set by the continue handler and you will never enter the loop.