I finally figured out how you can create a stored procedure or run Dynamic compound statements in Netbean’s Database service on the IBMi.  My problem was that whenever I tried to run a create stored procedure script the SQL editor didn’t know how to parse it correctly so it would send the command line by line to the database using the semi colon as the delimiter instead of as one contiguous command.

The key to getting this to work in Netbeans is to create a Delimiter that marks the end of the SQL that you want to send to the system.  In the example below I send a request that creates 2 temp tables, and then 2 requests to pull the data from those tables.  This example is simple and doesnt show the full power of a DCS.  The dynamic compound statements allow you to use variables, control logic, cursors, etc… to do database specific tasks.  Its like a stored procedure except you don’t have to create it first.   see:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Dynamic%20Compound%20statement

and

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafydyncompound.htm

It did seem that a dynamic compound statement can’t return a result set for some reason (at least when i tried).

DELIMITER //
BEGIN
CREATE TABLE QTEMP.TEST1 AS (SELECT * FROM MYLIB.MYTABLE)
WITH DATA;
CREATE TABLE QTEMP.TEST2 AS (SELECT * FROM MYLIB.MYTABLE)
WITH DATA;
END //
DELIMITER ;
SELECT * FROM QTEMP.TEST2;
SELECT * FROM QTEMP.TEST3;

DELIMITER //
CREATE PROCEDURE MYSTOREDPROC (IN MYPARAM VARCHAR(20))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE c1 CURSOR FOR
SELECT *
FROM MYATABLE
WHERE FIELD = MYPARAM;
OPEN c1;
END //
DELIMITER ;
Call MYSTOREDPROC('test');

Below is an example of the error that I was getting when i was running into this issue where the database service was not parsing the CREATE PROCEDURE statement as one contiguous chunk.

Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Line 2, column 1

Below is me trying DCS in different IBMi tools

cimmfjkweaexyhx