CREATE PROCEDURE MySchema.P_GET_NEXT_SEQUENCE ( out Sequence varchar(30),
                                             OUT SQLSTATE_OUT CHAR(5),
                                             OUT SQLCODE_OUT INT,
                                             OUT MESSAGE_TEXT_OUT VARCHAR(1000))
    SPECIFIC LCADUSER.P_GET_NEXT_SEQ
    DYNAMIC RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL STORED PROCEDURE
------------------------------------------------------------------------
P1: BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE z_current_alpha char(1) default 'A';
    DECLARE z_current_number INT DEFAULT 1;

    DECLARE CURSOR1 CURSOR  FOR
       SELECT current_alpha,current_number FROM MySchema.MySequence FOR UPDATE WITH RS;

    -- DECLARE HANDLER
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
          GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
          SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
              FROM SYSIBM.SYSDUMMY1;
          SET MESSAGE_TEXT_OUT = MESSAGE_TEXT_OUT || ' [IN P_GET_NEXT_SEQ]';
       END;

    DECLARE EXIT HANDLER FOR SQLWARNING
       BEGIN
          GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
          SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
              FROM SYSIBM.SYSDUMMY1;
          SET MESSAGE_TEXT_OUT = MESSAGE_TEXT_OUT || ' [IN P_GET_NEXT_SEQ]';
       END;


    ROLLBACK; -- MUST START A NEW UOW

    OPEN CURSOR1;    -- SET UPDATE LOCK ON ROW
    FETCH CURSOR1 INTO z_currrent_alpha,z_current_number;

    -- If the alpha need to change based on some criteria, then you would do that logic here
    
    SET z_current_number = z_current_number + 1;

    IF (z_current_number = 1)  -- THIS WILL INSERT IF THERE WAS NO ROW 
        THEN 
           set z_current_alpha = 'A';
          INSERT INTO MySchema.MySequence  VALUES (z_current_alpha,z_current_number);
        ELSE UPDATE MySchema.MySequence SET (current_alpha,current_number) = (z_current_alpha,z_current_number);
     END IF;

    CLOSE CURSOR1;
    COMMIT WORK;

    SET Sequence = z_current_alpha||char(z_current_number);

    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    SET MESSAGE_TEXT_OUT = NULL;


END P1