Tag Archives: DB2 for i

JDBC JT400 setting to get CrUD and Show SQL features added in Netbeans with IBM DB2 for i

I finally figured out the missing connection property needed so that Netbeans will allow you to add, edit, and delete rows with inline editors and selected rows.  In addition to showing SQL Scripts for Create,Insert,Delete and Update.  It is the extended metadata property which is by default set to false and needs to be set to true.  I found this out by looking the jdbc properties at http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzahh/jdbcproperties.htm and looking for properties effecting read only status, which is why those features were blocked on Netbeans.

In your connection properties add

extended metadata=true

by right clicking your connection in the database panel of netbeans while disconnected and going to properties

Screen Shot 2016-08-21 at 5.45.29 PM.png

Click the ellipses …) next to connection properties
Screen Shot 2016-08-21 at 5.45.53 PM.png

Click under the property column and type extended metadata.  Click under Value and type true

Screen Shot 2016-08-21 at 5.46.28 PM.png

Now run a SELECT statement and you’ll be able to Create, Update and Delete records by using the icons above the result set or by right clicking the result set area and using the context menu.  Which also had the Show SQL Scripts for CrUD.

Screen Shot 2016-08-21 at 5.28.59 PM.png

DB2 for i in the Cloud – Connecting to PUB400 via JDBC in Netbeans

1.Sign up for an account at http://pub400.com/signup.html

2. Download and extract jtopen which contains lib/jt400.jar driver for connecting to an IBM i http://jt400.sourceforge.net/

3. Download and install Netbeans https://netbeans.org/ .  I do the PHP bundle, but i believe they all have the Database module.

4. Go into the “services” tab under database and right click the Drivers folder and click “New Driver”Screen Shot 2016-08-20 at 7.30.57 PM.png

Find where you extracted JT400 and select lib/jt400.jar

Screen Shot 2016-08-20 at 7.32.19 PM.png

Press the “Find” button so it can find the AS400JDBCDriver class in the jar file and give the driver a name like “JT400 JDBC Driver”

Screen Shot 2016-08-20 at 7.34.55 PM.png

Right click the driver and click “connect using” Screen Shot 2016-08-20 at 7.36.33 PM.png

Fill out the connection wizard with your information given to you from PUB400.com

User Name: <Your username for PUB400>
Password: <Your password for PUB400>
JDBC URL: jdbc:as400://pub400.com/{replace curly bracket and this with your schema name}

Screen Shot 2016-08-20 at 7.09.37 PM.png

After clicking next, Select your Schema

Screen Shot 2016-08-20 at 7.10.21 PM.png

Give the connection a name

Screen Shot 2016-08-20 at 7.10.38 PM.png

Now you can connect to the DB2 for i in the cloud by right clicking the connection and pressing connect

Screen Shot 2016-08-20 at 7.46.26 PM.png

Now you have an easy to get to database connection to a sandbox DB2 for i!  Time to play with the temporal table support and other new enhancements for DB2!

Screen Shot 2016-08-20 at 7.48.05 PM.png

After connecting, your connection properties should show what settings are being used on the connection and even what version of the DB2.  In this case the server is on V7R2m0 :

Screen Shot 2016-08-20 at 7.13.29 PM.png

Step2 add CrUD features by modifying the connection property




Kill all jobs with a record lock on a specific file via “DB2 for i” SQL

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.


Rename a Column in DB2 for IBM i

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

You would just need to create the DDS in the source file member MYTABLE that changes the column

Run #DB2 SQL Scripts added to #IBMi Access

The latest version of IBMi Access released today (Dec 9 2015) now allows you to run SQL scripts!  It also has an improved UI with color coded SQL!


You can download the latest IBM i Access Client Solutions here: https://t.co/jInoj0OtDX

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!


Protecting against SQL Injection in PHP and DB2 on IBM i

With many IBMi developers new to PHP, SQL and the web environment its important to cover a common mistake people make. The mistake is concatenating a value from $_REQUEST, $_GET or $_POST with their SQL statement string. This opens up the possibility for a SQL injection which allows someone to retrieve other data, bypass certain logic by making the statement always true, or worse (dropping a table, altering data, anything you can do in SQL). Below is how you can use a prepared SQL statement to safely execute SQL.

PHP DB2 on IBMi error: MYTABLE in MYFILE not valid for operation. SQLCODE=-7008

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


I got

MYTABLE in MYFILE not valid for operation. SQLCODE=-7008

Its a new file created via SQL
When I added WITH NONE it works


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.