Why does Netbeans 8.1 database results not allow CrUD or "Show SQL" operations when using JTOpen on a DB2 database?

Question

Anyone know why the Netbeans IDE's Database result explorer disables CrUD operations and "Show SQL 'CrUD' Operation" when using a JDBC connection via JTOpen 9.1 driver to a DB2 for i database with Netbeans 8.1?

JTOpen is a open source JDBC driver to IBM i DB2 for i database in addition to bunch of Java Classes for interacting with the IBMi system. http://jt400.sourceforge.net/

I tried a few JDBC connection properties but no cigar...

I guess i'll have to keep browsing the IBM KB http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzahh/jdbcproperties.htm

and the JT400 source https://github.com/devjunix/libjt400-java/blob/master/src/com/ibm/as400/access/JDProperties.java

Answer 1

Add "extended metadata=true" in the connection properties fixed my issue.

https://godzillai5.wordpress.com/2016/08/21/jdbc-jt400-setting-to-get-crud-and-show-sql-features-added-in-netbeans-with-ibm-db2-for-i/

The IBM documentation here http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzahh/jdbcproperties.htm

"extended metadata"
Specifies whether the driver requests extended metadata from the server. Setting this property to true increases the accuracy of the information returned from the following ResultSetMetaData methods: getColumnLabel(int) isReadOnly(int) isSearchable(int) isWriteable(int)

apparently readonly for the result set is incorrectly assumed true, unless the ext. metadata comes back with the actual value for isReadOnly(int). I'm guessing that its assumed false because on the initial connection the connection property "Read Only" is true. It would be helpful to understand what setting on the system or the Library/Schema is causing the connection to have that property.

Answer 2

Many DB2 for i systems are configured to not use commitment control or journaling. This is not what many toolkits expect to see. Try changing the connection string to tell Netbeans that you don't want commitment control.

Answer 3

The most obvious reason for the original image showing just some read-only operations presented, would seem to have been the "access" attribute for the connection; i.e. if set to "read only", that would limit access to SELECT statements only. But with the new information showing the connection properties, seems the readOnly=false, so that "access" attribute should not be the origin for the issue.
I suspect that for any given TABLE, the issue might be for lack of a PRIMARY KEY CONSTRAINT; i.e. IIRC, some client database applications might prevent update-capable mode for a particular TABLE, if that TABLE is not known to have a PK.