Work with IBM DB2 AS400 (errors)
Hello!
I try to use 0xDBE (version 138.551) for work with IBM DB2 as400 series.
It is very special product but your programm works with it with success.
I can execute SQL via editor but I have got a problem with any function or procedure edit.
Error is:
-- Definition loading failed:
-- java.sql.SQLException: [SQL0204] PROCEDURES in SYSCAT type *FILE not found.
-- at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
-- at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
-- at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1557)
-- at com.ibm.as400.access.AS400JDBCStatement.executeQuery(AS400JDBCStatement.java:2115)
-- at com.intellij.persistence.jdbc.impl.RemoteStatementImpl.executeQuery(RemoteStatementImpl.java:141)
-- at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
-- at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
-- at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
-- at java.lang.reflect.Method.invoke(Method.java:606)
-- at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:322)
-- at sun.rmi.transport.Transport$1.run(Transport.java:177)
-- at sun.rmi.transport.Transport$1.run(Transport.java:174)
-- at java.security.AccessController.doPrivileged(Native Method)
. . .
Could you to help me with subject?
Please sign in to leave a comment.
Hello.
Looks like incompatibility between your database version and JDBC driver. Could you do Test Connection and post the whole text from that dialog?
Thank you,
Alexander.
Hello,
Thanks for your answer.
See details in attach.
Attachment(s):
connect_Db2_as400.jpg
I've tried also different versions of
jt400.jar
(last 10.2)
The result is the same. :-(
Have you tried the driver automatically downloaded by the IDE? It seems to be different.
I'll try to find some solution in any case, but it's important to know does the driver we suggest work.
Regards,
Alexander.
The working computer has not Internet connection (by security reason).
I download drivers manually and then copy them into ..\JetBrains\0xDBE\lib\.
Could you help me with correct DB2 drivers for IBM as400?
Hi.
Sorry for delay. I'm not very familiar with DB2 actually. I've found the table of suggested JDBC drivers for various DB2 versions:
http://www-01.ibm.com/support/docview.wss?uid=swg21363866
However, at the moment I have not yet parsed the specification "DB2 UDB for AS, 05.04.0000" returned by the driver. What is "05.04.0000"? Can you map it to a version in the table referred above?
Thank you,
Alexander.
Hello Alexander!
Thanks for answer.
I'm on vacation till 20th of July.
Can we back to the problem after that?
Yes, of course.
Regards,
Alexander.
Hello Alexander!
I'm ready to continue...
The result of
java com.ibm.db2.jcc.DB2Jcc -version
is
IBM DB2 JDBC Universal Driver Architecture 2.11.65.
And I don't understand what does it mean "05.04.0000"...
Update: this post does not help the topic starter. I'm just keeping it for all those links.
-----
Hello.
I've found some infromation. (Sorry for uncertainty, the documentation I find is scatterred across the web.)
Here are some explanations: http://www-03.ibm.com/systems/power/software/i/toolbox/faq/jdbc.html
It looks that DB2 for AS/400 in our case requires "Toolbox" driver.
The downloads are here: http://sourceforge.net/projects/jt400/files/JTOpen-full/
The downloaded zip includes some jars, and jt400.jar seems to contain required classes.
The driver you need is *com.ibm.as400.access.AS400JDBCDriver*.
The URL is like: jdbc:as400://server-name/default-schema;properties (see http://publib.boulder.ibm.com/html/as400/java/com.ibm.as400.access.AS400JDBCDriver.html)
It's not the one suggested by 0xDBE. So, you have to define new driver in Data Sources and Drivers dialog, and create the data source from that.
Please try. Please, write back if something is wrong, it doesn't look like the end.
Regards,
Alexander.
Oops, sorry. I've missed the fact that you tried that in the first iteration.
I'll continue to search the solution.
Alexander.
Sorry again. Hope this will be more relevant.
1. Can you see the content of SYSCAT schema, for example, in Database tool window? (After including it in Schemas and Tables tab.)
2. Is PROCEDURES view or table there?
3. If not, is FUNCTIONS view or table there?
Thank you,
Alexander.
Hello Alexander!
Sorry for long pause...
1) I don't see SYSCAT schema.
I have only
SYSIBM
SYSABMIDM
SYSPROC
SYSTOOLS
2) I see PROCEDURES and FUNTIONS in common list in left with TABLES.
But I don't see bodies of them.
3) TABLES view has columns and I see its in left tree.
Thank you.
Please try to execute a statement like:
> SELECT ROUTINENAME, TEXT FROM SYSIBM.SYSROUTINES WHERE ROUTINENAME LIKE 'your_procedure_name';
Where "your_procedure_name" is your procedure name: do you get the code in TEXT column?
NB: procedure name is case-sensitive.
Regards,
Alexander.
Hi Alexander!
Thanks for answer.
In my case your select is
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM SYSIBM.ROUTINES WHERE ROUTINE_NAME LIKE 'your_procedure_name';
The code of procedure is contained in ROUTINE_DEFINITION field.
Thank you for collaboration. I liked to know that procedure body is available via any query - so it is.
Created http://youtrack.jetbrains.com/issue/DBE-666 (lucky number). Hope developer will follow up.
Regards,
Alexander.
Good to see it.
Nice number (666). :-)
Hope on it.
Thank you for your participation!
The problem is still active (1 year already)!
You already ship new product with old errors!
https://youtrack.jetbrains.com/issue/DBE-666
This is a critical problem for our team.
It stopped us from buying DataGrip.
Please correct this terrible error at last!
Attachment(s):
db2_procs_error1.jpg
Hello Viktor,
Please follow this YouTrack issue: https://youtrack.jetbrains.com/issue/DBE-666 . I've pinged the developer. Please see https://intellij-support.jetbrains.com/hc/en-us/articles/206827497 if you are not familiar with YouTrack.