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?

0
19 comments
Avatar
Alexander Chernikov

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.

0

Hello,

Thanks for your answer.

See details in attach.



Attachment(s):
connect_Db2_as400.jpg
0

I've tried also different versions of
jt400.jar
(last 10.2)

The result is the same. :-(

0
Avatar
Alexander Chernikov

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.

0

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?

0
Avatar
Alexander Chernikov

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.

0

Hello Alexander!
Thanks for answer.
I'm on vacation till 20th of July.
Can we back to the problem after that?

0
Avatar
Alexander Chernikov

Yes, of course.

Regards,
Alexander.

0

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"...

0
Avatar
Alexander Chernikov

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.

0
Avatar
Alexander Chernikov

Oops, sorry. I've missed the fact that you tried that in the first iteration.
I'll continue to search the solution.

Alexander.

0
Avatar
Alexander Chernikov

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.

0

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.

0
Avatar
Alexander Chernikov

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.

0

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.

0
Avatar
Alexander Chernikov

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.

0

Good to see it.
Nice number (666). :-)

Hope on it.

Thank you for your participation!

0

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
0

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.

0

Please sign in to leave a comment.