batch update a column using sql

I have a database table (db2) with a very large number of rows (a couple million). I need to change the datatype of one of the columns.

In DB2 LUW there does not seem to be a way to directly change the datatype of a column (ALTER TABLE ALTER COLUMN SET DATA TYPE does not work). So I am creating a new column, copying data to it and dropping the old column.

Since its not a good idea to do a direct update on the table, I'm creating a procedure which will update and commit 10000 rows at a time.

Given this, I have the following questions:

• What is the best way to carry out the update here? - as far as I can tell, the cursor allows iteration over 1 row at a time. Is updating 10000 rows one at a time, then committing, and repeating until the table is updated the correct way to do it?
• Is there any better way to handle the original issue of changing the data type of a column in a simpler way?

DB2 has a feature called LOAD FROM CURSOR that allows for fast migration of data.

The following is an example of this using LOAD FROM CURSOR:

-- this is the original table
CREATE TABLE TEST (
    ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TEXT VARCHAR(50)
)@

CREATE TABLE TEST_NEW (
    ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TEXT CLOB(5000000)
)@

DECLARE C1 CURSOR FOR SELECT ID, TEXT FROM TEST@

LOAD FROM C1 OF CURSOR INSERT INTO TEST_NEW (ID,TEXT)@ 

DROP TABLE TEST@
RENAME TABLE TEST_NEW TO TEST@

In addition, the following procedure can also be used (commit after every 10000 records):

-- this is the original table
CREATE TABLE TEST (
    ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TEXT VARCHAR(50)
)@

CREATE OR REPLACE PROCEDURE MIGRATE_TEST()
LANGUAGE SQL
BEGIN

    DECLARE EOF INT DEFAULT 0;

    DECLARE CUR_COUNT INT DEFAULT 0;
    DECLARE CUR_ID INT DEFAULT 0;

    DECLARE C CURSOR WITH HOLD FOR 
        SELECT ID FROM TEST WHERE TEXT_NEW IS NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET EOF = 1;

    OPEN C;

    FETCH_LOOP: LOOP
        FETCH FROM C INTO CUR_ID; 
        IF EOF <> 0 THEN
                LEAVE FETCH_LOOP;
        END IF;

        UPDATE TEST 
        SET TEXT_NEW = TEXT
        WHERE ID = CUR_ID ;

        SET CUR_COUNT = CUR_COUNT + 1;

        IF CUR_COUNT >= 10000 THEN
            CALL DBMS_OUTPUT.PUT_LINE('COMMITTING');
            COMMIT WORK;
            SET CUR_COUNT = 0;
        END IF;

    END LOOP FETCH_LOOP;

    COMMIT WORK;

    CLOSE C;
END@