How to store optimization profile definitions in a Db2 for z/OS table

Steps

----------------------- 
-- DDL - run only once 
------------------------- 
DROP DATABASE SAMPDB@ 
CREATE DATABASE SAMPDB@ 
DROP TABLE ACCEL.OPTIMIZATION_PROFILE@ 

CREATE TABLE ACCEL.OPTIMIZATION_PROFILE( 
    ACCELERATOR VARCHAR(128) NOT NULL, 
    LISTCAPTURETS TIMESTAMP WITH TIMEZONE NOT NULL, --OPTIONAL (REMOVE IF LIST CAPTURE TIMESTAMP IS NOT DESIRED) 
    SCHEMA VARCHAR(128) NOT NULL, 
    NAME VARCHAR(128) NOT NULL, 
    PROFILE CLOB (2M) NOT NULL)
    CCSID UNICODE IN DATABASE SAMPDB@ 

DROP PROCEDURE ACCEL.GET_OPTIMIZATION_PROFILE@ 

CREATE PROCEDURE ACCEL.GET_OPTIMIZATION_PROFILE(IN ACCEL VARCHAR(128), 
OUT RETURN_MESSAGE CLOB(64K)) 
VERSION V1 
ISOLATION LEVEL CS 
LANGUAGE SQL 
DISABLE DEBUG MODE 
SP: BEGIN 

-- VARIABLE 
DECLARATION DECLARE RETURN_TEXT VARCHAR(70); -- CONTAINS THE MESSAGE TEXT OF THE ERROR ENCOUNTERED RETURNED TO THE CALLER 
DECLARE ALREADY_EXIST INTEGER; -- CONTAINS THE RESULTS OF CHECK STATEMENTS

DECLARE LIST_XML CLOB(1K) CCSID UNICODE DEFAULT '<?xml version="1.0" encoding="UTF-8" ?><dwa:configOptimizationProfileCommand xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0" ><optimizationProfileCommand command = "LIST"/></dwa:configOptimizationProfileCommand>'; -- INPUT PARAMETER for ACCEL_CONFIG_OPTIMIZATION_PROFILE SP 

DECLARE RESULT_XML CLOB(128M) CCSID UNICODE; -- OUTPUT PARAMETER for ACCEL_CONFIG_OPTIMIZATION_PROFILE SP


-- internal variable for resultsets locator
DECLARE OPT_PROFILE_INFO_LOCATOR RESULT_SET_LOCATOR VARYING;
-- internal variables for OPT_PROFILE_INFO_CURSOR
DECLARE OPT_PROFILE_INFO_SEQID INTEGER;
DECLARE OPT_PROFILE_INFO_XML CLOB(32K) CCSID UNICODE;


-- INOUT PARAMETER PASSED TO ALL IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURES CALLED
DECLARE MESSAGE_XML CLOB(64K) DEFAULT '';

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RECEIVED_SOMETHING INTEGER DEFAULT 0;

-- HANDLER DECLARATION AND MANAGEMENT FOR IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURES CALL
-- DEFINING WARNING CONDITION THROWABLE BY IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURE
DECLARE AQTERR VARCHAR(10) DEFAULT 'UNKNOWN'; -- EVENTUALLY CONTAINS IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURE REASON CODE ERROR
DECLARE IDAAERRCODE VARCHAR(500);
DECLARE IDAAWARN1 CONDITION FOR SQLSTATE VALUE '01H08';
DECLARE IDAAWARN2 CONDITION FOR SQLSTATE VALUE '01H04';
DECLARE IDAAOK SMALLINT DEFAULT 0; -- CONTAINS THE SUCCESSFUL/UNSUCCESSFUL EXECUTION OF IDAAPART COMPUND STATEMENTS

DECLARE c1 CURSOR FOR SELECT ACCELERATORNAME FROM SYSACCEL.SYSACCELERATORS WHERE ACCELERATORNAME LIKE ACCEL FOR FETCH ONLY WITH UR;

DECLARE EXIT HANDLER FOR IDAAWARN1, IDAAWARN2

BEGIN
GET DIAGNOSTICS CONDITION 1 IDAAERRCODE = MESSAGE_TEXT;
SET RETURN_MESSAGE = MESSAGE_XML;
SET ALREADY_EXIST = LOCATE('AQT',IDAAERRCODE);
IF ALREADY_EXIST IS NULL OR ALREADY_EXIST = 0 THEN
SET AQTERR = 'UNKNOWN';
ELSE
SET AQTERR = SUBSTR(IDAAERRCODE,ALREADY_EXIST,9);
END IF;
SET IDAAOK = -1;
END;

SET OPT_PROFILE_INFO_XML = NULL; -- return all tables
SET RESULT_XML = '';
SET MESSAGE_XML = NULL;

----------------------------
-- CHECK INPUT PARAMETERS
----------------------------

-- CHECK IF THE INPUT PARAMETER ACCEL REALLY EXISTS AS ACCELERATOR
SELECT COUNT(*) INTO ALREADY_EXIST
FROM SYSACCEL.SYSACCELERATORS
WHERE ACCELERATORNAME=ACCEL WITH UR;
IF ALREADY_EXIST = 0 THEN
IF ACCEL IS NOT NULL THEN
SET RETURN_TEXT = 'ERRC 001 - ACCELERATOR "'|| ACCEL ||'" IS AN UNDEFINED NAME';
ELSE
SET RETURN_TEXT = 'ERRC 001 - ACCELERATOR "NULL" IS AN UNDEFINED NAME';
END IF;
SIGNAL SQLSTATE VALUE '7UA00' SET MESSAGE_TEXT=RETURN_TEXT;
END IF;

----------------------------
-- GET OPTIMIZATION PROFILE FROM ACCELERATOR
----------------------------

CALL SYSPROC.ACCEL_CONFIG_OPTIMIZATION_PROFILE(ACCEL,LIST_XML,'',MESSAGE_XML);

IF IDAAOK = 0 THEN
SET RETURN_MESSAGE = 'ERRC 000 - SUCCESSFUL EXECUTION';
ELSE
SIGNAL SQLSTATE VALUE '01UA0' SET MESSAGE_TEXT= 'ERRC 100 - ACCEL_CONFIG_OPTIMIZATION_PROFILE ERROR: '|| AQTERR ||'. MORE DETAILS IN OUTPUT PARAMETER';
END IF;

--------------------------------------------------------------
-- FETCH OPT_PROFILE_INFO_CURSOR RESULTSET of ACCEL_CONFIG_OPTIMIZATION_PROFILE SP
-- PARSE OUTPUT XML and WRITE INTO TABLE
--------------------------------------------------------------
ASSOCIATE RESULT SET LOCATOR ( OPT_PROFILE_INFO_LOCATOR ) WITH PROCEDURE SYSPROC.ACCEL_CONFIG_OPTIMIZATION_PROFILE;
ALLOCATE OPT_PROFILE_INFO_CURSOR CURSOR FOR RESULT SET OPT_PROFILE_INFO_LOCATOR;

fetch_loop:
LOOP
SET OPT_PROFILE_INFO_XML = '';
FETCH OPT_PROFILE_INFO_CURSOR INTO OPT_PROFILE_INFO_SEQID, OPT_PROFILE_INFO_XML;
IF SQLCODE != 0 THEN
-- do not append the content again!
LEAVE fetch_loop;
-- it may be necessary to check for other SQL codes
ELSE
-- append fetched content to temp variable
SET RECEIVED_SOMETHING = 1;
set RESULT_XML = CONCAT(RESULT_XML, OPT_PROFILE_INFO_XML);
END IF;

END LOOP fetch_loop;

CLOSE OPT_PROFILE_INFO_CURSOR;

----------------------------------------
-- PARSE OUTPUT XML and WRITE INTO TABLE
----------------------------------------
IF RECEIVED_SOMETHING != 0 THEN

--format the return
INSERT INTO ACCEL.OPTIMIZATION_PROFILE (
ACCELERATOR,
LISTCAPTURETS,    --OPTIONAL (REMOVE IF LIST CAPTURE TIMESTAMP IS NOT DESIRED)
SCHEMA,
NAME,
PROFILE) 
SELECT ACCEL AS ACCELERATOR, 
CURRENT_TIMESTAMP,   --OPTIONAL (REMOVE IF LIST CAPTURE TIMESTAMP IS NOT DESIRED)
SCHEMA, NAME, PROFILE FROM
(SELECT XMLPARSE(DOCUMENT RESULT_XML) AS HISTORY_XML FROM SYSIBM.SYSDUMMY1) P,
XMLTABLE(
XMLNAMESPACES('http://www.ibm.com/xmlns/prod/dwa/2011' AS "dwa"),
'$d/dwa:optimizationProfileList/optimizationProfile' PASSING P.HISTORY_XML AS "d"
COLUMNS
SCHEMA VARCHAR(128) PATH 'optimizationProfileSchema',
NAME VARCHAR(128) PATH 'optimizationProfileName',
PROFILE CLOB(2M) PATH 'optimizationProfileXml'
);

COMMIT;

END IF;


END SP
@

----------------------------------------
-- Calling the SP
----------------------------------------
CALL ACCEL.GET_OPTIMIZATION_PROFILE('ZGRYPHON', cast(? as CLOB(64K)))@

----------------------------------------
-- Selecting the profiles from list
----------------------------------------
SELECT * FROM ACCEL.OPTIMIZATION_PROFILE 
ORDER BY LISTCAPTURETS DESC  --OPTIONAL (REMOVE IF LIST CAPTURE TIMESTAMP IS NOT DESIRED)
@