Search code examples
db2oracle-sqldeveloper

SQL Developer and DB2 errors

I'd like to use SQL Developer with DB2, I was able to connect and I canned execute my queries, but when I have an error, I cannot know witch error is. SQL Developer shown me only the error code, not the message. There is the way to know the error I have?

EDIT:

For example, launching this query:

Select * from WrongTable

other programs says:

ERROR[42704][IBM][DB2/NT64] SQL0204N "USERNAME.WRONGTABLE" è un nome non definito

sqldeveloper limits its report to the error nr only:

Errore alla riga del comando : 1 colonna : 1
Report errori -
Errore SQL: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=USERNAME.WRONGTABLE, DRIVER=4.19.49

Thank you.

Solution

  • The URL syntax for connecting to Db2 with type-4 jdbc drivers is documented here.

    The property that controls how much information is returned with getMessage() is called retrieveMessagesFromServerOnGetMessage, and its default value is disabled ( false , 0 ). Set it to value 1 (or YES, or true )to enable more details on errors.

    You can append many properties after the database name in the Database field, on the Oracle SQL-Developer connection properties. Express each property in the form x=y , each x=y pair is separated by a semi-colon and the final one is terminated by a semi-colon, and the first property is prefixed by colon immediately after the database name.

    For example, suppose the database name is sample and I wanted three additional properties, the I would put this in the Database field in Oracle-SQL-developer:

    sample:useJDBC4ColumnNameAndLabelSemantics=No;securityMechanism=11;retrieveMessagesFromServerOnGetMessage=1;

    If value 1 does not give the expected result, use value YES although they should be equivalent. Remember to SAVE the setting change, disconnect from the database, reconnect , before retrying your queries to assess the change.

    Many other properties are available, see many related pages in the documentation , some properties are common to all target Db2 platforms, other properties are specific to Db2-LUW, or Db2-Z/OS, or Informix etc, so read the docs carefully. Some properties can be set by code after the connection is already established.