1

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/

Screenshot of missing insert CrUD operations and Show SQL Scripts

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

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

PHPDave
  • 904
  • 1
  • 7
  • 15

3 Answers3

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.

Buck Calabro
  • 7,558
  • 22
  • 25
  • Since it's a public machine, you must have created the database yourself. How did you do that? If CRTLIB, CRTPF, the defaults are no journalling. If CREATE SCHEMA, CREATE TABLE, then the defaults are to journal the tables in that schema. If you don't remember, start a green screen and use DSPFD. Or, just create a new schema and a new table and test against that. Or, STRJRNPF on the table you're trying to update. I don't /know/ that journalling is the problem but it won't be hard to check or test that hypothesis. – Buck Calabro Aug 21 '16 at 14:19
  • I ran CREATE TABLE sql statement. So I'm attempting to add the journal but running into: "CPF9803: Cannot allocate object MYTABLE in library PHP_DAVE1." https://gist.github.com/phpdave/124d5814121243555ad1dfb08054e435 – PHPDave Aug 21 '16 at 20:28
  • The Schema/Lib was created automatically when signing up for PUB400 – PHPDave Aug 21 '16 at 20:34
  • No other process can be using the table when attempting to add journalling. For example, if you have an SQL cursor/query open you will get this class of error. I personally see starting journalling as a 'setup' operation, and thus tend to do it in a CL program. I realise that scripting it in an SQL tool is just as useful, but what happens when the SQL tool holds a lock due to a pseudo-close of a cursor? You may be able to get around this with ALCOBJ OBJ((MYTABLE *FILE *EXCL)) CONFLICT(*RQSRLS) – Buck Calabro Aug 21 '16 at 20:39
  • that was it open result set - 1 of 1 files have started journaling. – PHPDave Aug 21 '16 at 20:45
  • The disabled items are still disabled :/ – PHPDave Aug 21 '16 at 20:48
  • Try the primary key constraint as suggested by @CRPence – Buck Calabro Aug 21 '16 at 20:49
  • Finally got it to work! Added "extended metadata=true" http://i.imgur.com/0xyOHLr.png – PHPDave Aug 21 '16 at 21:14
  • Don't forget to 'accept' an answer that worked for you. Also, consider writing up what you did in Documentation http://stackoverflow.com/documentation/ibm-midrange – Buck Calabro Aug 21 '16 at 21:18
2

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.

PHPDave
  • 904
  • 1
  • 7
  • 15
1

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.

CRPence
  • 1,259
  • 7
  • 12
  • I added the primary key and got. Error code -7008, SQL state 55019: [SQL7008] MYTABLE2 in PHP_DAVE1 not valid for operation. Cause . . . . . : The reason code is 3. 3 -- MYTABLE2 not journaled, no authority to the journal, or the journal state is *STANDBY. Seems to be what Buck is talking about with the journaling – PHPDave Aug 21 '16 at 20:18
  • Yes, the RC3 for the msg SQL7008 is suggesting that an operation that was attempted is disallowed for lack of journaling; usually, that is an isolation level [commitment control level] other than *NONE when some I\O was attempted against the TABLE; the connection properties shown seem to suggest that would not be an issue. But just as a client can demand a PK, so too could a client SET ISOLATION LEVEL other than *NONE, irrespective the originally established connection attributes. – CRPence Aug 21 '16 at 21:19
  • I tested this and no primary key was needed it was the extended metadata connection property. Thanks for the help https://godzillai5.files.wordpress.com/2016/08/screen-shot-2016-08-21-at-5-28-59-pm.png?w=1250 – PHPDave Aug 21 '16 at 22:12