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:
Below is a stored procedure that uses the DB2 for i service QSYS2.RECORD_LOCK_INFO to get all the record locks on a specific file. It then iterates over all the records and passes the JOB_NAME to ENDJOB to kill the jobs that are creating the lock on the file. Of course this is very dangerous and should be only used in certain instances where you are sure that the jobs you’ll be killing will not matter.
Below is some SQL that can be used to rename a column by dropping the column and adding the new column name. Do realize that this will make you lose the data in the OLDCOLUMNNAME field so you’ll want to create a backup and re-populate the new field using the backup.
Note: If you get Error code -952, SQL state 57014: [SQL0952] Processing of the SQL statement ended. Reason code 10. Its because the ALTER TABLE will result in data loss. The database is trying to warn you and wants you to confirm deletion but jdbc can’t handle the prompt. I’m unsure if there is a JDBC option to always say yes to the prompt. You may have to run this in a 5250 session with STRSQL
Alternatively you could also use the command below CHGPF FILE(MYLIB/MYTABLE) SRCFILE(QDDSSRC)
You would just need to create the DDS in the source file member MYTABLE that changes the column
Big thanks to Jesse Gorzinski’s team and everyone at IBM that made this possible! We finally aren’t married to the Windows iSeries Navigator for running SQL! We can now use this tool on Mac, Linux or Windows!
UPDATE: Make sure to check if i5_allow_commit is not 0 (No Commit *NC *NONE). You can check by running this in your PHP code
If the value is not 0 you might fix the problem by setting it to 0 in /usr/local/zendsvr/etc/conf.d/ibm_db2.ini and doing an Apache restart on Zend Server going to the 5250 typing “GO ZENDSVR/ZSMENU” option 6, option 5.
In case you run into the issue where your SQL updates and inserts statements aren’t processing you may want to try appending “WITH NONE” to the SQL statement.
When I ran the update statement
UPDATE MYFILE.MYTABLE SET FIELD1 = ? WHERE FIELD2 = ?
MYTABLE in MYFILE not valid for operation. SQLCODE=-7008
Its a new file created via SQL
When I added WITH NONE it works
UPDATE MYFILE.MYTABLE SET FIELD1 = ? WHERE FIELD2 = ? WITH NONE
Even hard coding the values created the same error. The problem appears to be a commitment control / journaling issue with IBMi DB2 connection. You could try to change the settings of your connection.