[healey]

I don’t think the AS400 originally had a catalog concept and now really just substitutes the “system name” as a catalog. I could be wrong on this but that is where the problems probably originate.

1) With the HIT driver, I assume the catalogs and schema names displayed are correct?

As far as I can tell the catalog is displaying correctly with the Hit driver. The name displayed matches the name in the IBM DB Browser tree view.

2) With the regular driver, are the schemata from the catalog you are interested in?

I think you may have meant: With the regular driver, are the schemata from the catalog you are interested in being displayed?
They are displayed. If I specify which schemata to display with MyEclipse (on page 2) then only those schemata are displayed. Otherwise all schemata are displayed.

3) With AS400, difficulty in getting a list of catalogs seems to be a common issue. Sometimes a connection string might constrain the connection to a specific catalog. In other cases, it may be looking for a catalog but if one is not specified, it will just use the default catalog (which in some databases is the same as your username). What connection string are you using exactly? Is the name of the catalog you need to work with the same as your username?

It does not look like there is a way to specify which catalog the connection will use for the AS400. The url I am using is jdbc:as400://as400systemt.companname.com/ With the AS400 I think there is only one catalog. The catalog name is not the same as my user name. I tried using a user name that matches our catalog name and that did not work either.

4) If you edit your connection in the DB Browser, go to page 2 and select the 3rd option, does that give you a list of catalogs (not schema)? If not, try the default catalog option and click finish. Does that help?

The list contains a list of schemata. The default catalog option does not help (it is actually default schema).

5) The point at which the failure occurs is this:

The failure happens when I double click on the “Table” folder that is displayed under the schema folder in the DB Browser.

I have a test program to recreate the failure and if I pass in the schema name as the second parameter in the call to getTables the driver fails with the same error message. The getTables method is expecting a “schema pattern” not an exact schema name. So the only way to get the method call not to fail is to add a % in the string that gets passed in for the schema parameter. So if I pass in getTable(null, “schemaName”, “%”, new String[] {“TABLE”}); it fails. If I pass in getTable(null, “schemaName%”, “%”, new String[] {“TABLE”}); it works. Do you think this is the problem?

[Brian Fernandes]

healey,

Thanks for the detailed response. So with the HIT driver (and the IBM DB Browser view) you see a single catalog (with name matching System name) and schemata under that?

So if I pass in getTable(null, “schemaName”, “%”, new String[] {“TABLE”}); it fails. If I pass in getTable(null, “schemaName%”, “%”, new String[] {“TABLE”}); it works. Do you think this is the problem?

That is definitely a good lead. But first we need to make sure that the default catalog is retrievable and displayed.

Do any of the following statements return true?

connection.getMetaData().supportsCatalogsInTableDefinitions();
connection.getMetaData().supportsCatalogsInProcedureCalls();
connection.getMetaData().supportsCatalogsInDataManipulation();

Subsequently, does
connection.getMetaData().getCatalogs(); return your catalog (this will be a result set)?

Finally, if you call
connection.getMetaData().getTables(catalog, schema, “%”, new String[] {“TABLE”});
with catalog actually set to the name of your default catalog (and not null) does that return the list of tables?

[healey]

With the HIT driver (and the IBM DB Browser view) you see a single catalog (with name matching System name) and schemata under that?

That is the case with the MyEclipse IDE Db Browser (with the HIT driver) but with the IBM Db Browser it first shows a “Schemas” folder under ther system name and under the Schemas folder it shows all the schemas.

Do any of the following statements return true?
Code:
connection.getMetaData().supportsCatalogsInTableDefinitions();
connection.getMetaData().supportsCatalogsInProcedureCalls();
connection.getMetaData().supportsCatalogsInDataManipulation();

No all return false.

Does connection.getMetaData().getCatalogs(); return your catalog (this will be a result set)?

Yes. It returns the system name which is apparently the catalog name.

Finally, if you call
connection.getMetaData().getTables(catalog, schema, “%”, new String[] {“TABLE”});
with catalog actually set to the name of your default catalog (and not null) does that return the list of tables?

No. I get the same general 901 error message , here are the details :
SQL system error. Cause . . . . . : An SQL system error has occurred. The current SQL statement cannot be completed successfully. The error will not prevent other SQL statements from being processed. Previous messages may indicate that there is a problem with the SQL statement and SQL did not correctly diagnose the error. The previous message identifier was CPF4204. Internal error type 3107 has occurred. If precompiling, processing will not continue beyond this statement. Recovery . . . : See the previous messages to determine if there is a problem with the SQL statement. To view the messages, use the DSPJOBLOG command if running interactively, or the WRKJOB command to view the output of a precompile. An application program receiving this return code may attempt further SQL statements. Correct any errors and try the request again.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
at com.ibm.as400.access.AS400JDBCDatabaseMetaData.getTables(AS400JDBCDatabaseMetaData.java:4170)

Hope this sheds soom light into the solution of the problem.

Thanks for all your help so far…

[Brian Leathem]

We filed a PMR with IBM about this one. The solution for us was found in the KB “Deleting the Database Host Server’s SQL Package”.

Goto the support search at:
http://www-912.ibm.com/ImprovedSearch/searchoptions.jsp
and search for document number 377117296

Fixed the problem for us.
Brian

[healey]

@bleathem wrote:

We filed a PMR with IBM about this one. The solution for us was found in the KB “Deleting the Database Host Server’s SQL Package”.

Goto the support search at:
http://www-912.ibm.com/ImprovedSearch/searchoptions.jsp
and search for document number 377117296

Fixed the problem for us.
Brian

This fixed the problem for us as well!

Thank you very much bleathem.

Thank you for your time and effort Support-Brian in resolving the problem.

[OldSchool]

anyone have any luck getting a solution to this?

I tried with sql explorer in ganymede r2 as well, just recently, and it has the same problem (the 901 error). However some of the db2 libraries table nodes are viewable while others in different libraries are not. I tried having the various settings compared between the libraries and tables but was told there was no significant difference. Not being an iSeries literate person, I could not refute.

[OldSchool]

sorry, I missed the last 2 messages for some reason. I see the solution now. thanks!

[Author]

Posts