Reference internal case #219.

User is trying to connect to an Informix SE DB but can't.  Even when the connection test is successful no tables can be seen.

I talked with Tom concerning this last week and have started getting information in from the customer.  My comments to the customer are hi-lighted in green.

I'm running Aqua Data Studio 20.0.4 from a Centos 7 box.

Here's what I get when I test the connection:

Your server is not listening or accepting connections on crown4:1603.
Verify host and port information is correct.  Try telnet crown4 1603
from command line. If you are not able to connect using telnet, Aqua
Data Studio will not be able to connect either.


Here's what I get when I try to telnet:

telnet crown4 1603
Trying 192.168.10.4...
telnet: connect to address 192.168.10.4: Connection refused
telnet: Unable to connect to remote host: Connection refused


This is my first time connecting. I haven't been able to get it to
connect. I'm connecting via an Informix connection. When I try to
connect via an Informix - ODBC connection, I get this message:
You must specify a ODBC datasource to create this connection

When I try to add a datasource, I get this message:

ODBC Data Sources are only available on Windows.

The first thing you need to do is find a port to work with. Informix usually uses port 1526 as its default.

To find out what ports are open please check the link below.

https://www.thegeekdiary.com/centos-rhel-how-to-find-if-a-network-port-is-open-or-not/

Once you have a port then registering a serer should be fairly simple.

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation20.5/page/176/Informix

I started the daemon on the server. Now the server is listening on port
1526.

When I test the connection, this is what I get:

Your server is accepting socket connections on crown4:1526.

Database not found or no system permission.

Steve,

I have found the following concerning this message and Informix. Hopefully one of these links will help you. Also, make sure you are not using Informix ODBC.

http://wikis.openlinksw.com/UdaWikiWeb/DatabaseNotFoundOrNoSystemPermissio

https://www.ibm.com/support/pages/onbar-returns-329-database-not-found-or-no-system-permission

http://www.oninit.com/errorcode/index.php?pageid=-329

I don't think that the 3 links apply to me. I was able to get some help

from IBM Informix. They helped me connect via the generic-jbdc
connection. However, I couldn't get my tables to display. You can see
that on the left of my screenshot. What should I do to view my tables?

In the attached screenshot, I'm trying to configure the Informix
connection. Aqua Data is using sysmaster for the database. I'm running
Informix Standard Engine, not Online or Dynamic Server. There is no
sysmaster database in Standard Engine. Is there a way that I can change
that?

Connection to DB failed.  Details below.

Title: Connection Test - FAILED

Summary: Unable to Create a Database Connection

==================================== Start Message
====================================

Your server is accepting socket connections on crown4:1526.

Database not found or no system permission.

==================================== End Message
====================================

==================================== Start Stack Trace
====================================
java.sql.SQLException: Database not found or no system permission.
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3130)
    at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3412)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2325)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2250)
    at com.informix.jdbc.IfxSqli.executeOpenDatabase(IfxSqli.java:1829)
    at com.informix.jdbc.IfxSqliConnect.<init>(IfxSqliConnect.java:1801)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
    at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:243)
    at \\.\\.\\हिñçêČάй語简�?한\\.Üꈗꇽ͕gotoprivate.b(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.ii(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.s(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.getConnection(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.getConnection(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Kꎢꌿᜬ⠬else.eC(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Kꎢꌿᜬ⠬else.b(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.O⡀ꀹꀹꉻbreak.b(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.O⡀ꀹꀹꉻbreak.c(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.I∕ᚯꏩꆉvoidimport$1.runTest(Unknown
Source)
    at
com.aquafold.library.desktop.diagnostics.PingDialog$1.run(Unknown Source)
Caused by: java.sql.SQLException: No such file or directory
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3417)
    ... 20 more

Next Exception:
java.sql.SQLException: No such file or directory
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3132)
    at com.informix.jdbc.IfxSqli.D(IfxSqli.java:3416)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2325)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2250)
    at com.informix.jdbc.IfxSqli.executeOpenDatabase(IfxSqli.java:1829)
    at com.informix.jdbc.IfxSqliConnect.<init>(IfxSqliConnect.java:1801)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
    at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:243)
    at \\.\\.\\हिñçêČάй語简�?한\\.Üꈗꇽ͕gotoprivate.b(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.ii(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.s(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.getConnection(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Aꁲᢙꑱ⣗.getConnection(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Kꎢꌿᜬ⠬else.eC(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.Kꎢꌿᜬ⠬else.b(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.O⡀ꀹꀹꉻbreak.b(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.O⡀ꀹꀹꉻbreak.c(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.I∕ᚯꏩꆉvoidimport$1.runTest(Unknown
Source)
    at
com.aquafold.library.desktop.diagnostics.PingDialog$1.run(Unknown Source)


==================================== End Stack Trace
====================================

The Connection Test worked okay and showed that ADS could connect to the DB.  However, the user said they still couldn't see tables.  Below is additional information he sent.

The query is coming from ADS. I ran a debug that was decoded by IBM
Informix tech support.

Here's what IBM Informix tech support says:

"The reason it cannot get the information for the tables is because of
this query which fails because of the CASE statement:

select tabname, CASE WHEN tabtype = 'T' AND tabid <= 99 THEN 'SYSTEM
TABLE' WHEN tabtype = 'T' AND tabid > 99 THEN 'TABLE' WHEN tabtype = 'V'
THEN 'VIEW' WHEN tabtype = 'S' OR tabtype = 'P' THEN 'SYNONYM' WHEN
tabtype = 'L' THEN 'LOG' WHEN tabtype = 'E' THEN 'TABLE' ELSE 'UNKNOWN'
END TABLE_TYPE , owner from informix.systables where CASE WHEN tabtype =
'T' AND tabid <= 99 THEN 'SYSTEM TABLE' WHEN tabtype = 'T' AND tabid >
99 THEN 'TABLE' WHEN tabtype = 'V' THEN 'VIEW' WHEN tabtype = 'S' OR
tabtype = 'P' THEN 'SYNONYM' WHEN tabtype = 'L' THEN 'LOG' WHEN tabtype
= 'E' THEN 'TABLE' ELSE 'UNKNOWN' END in ('TABLE') and tabname like '%'
and owner like 'steve' order by 2, 3, 1"

"The reason this SQL is failing is Informix SE does not support the
"CASE" statement. That is only available with Informix Online Dynamic
Server.

I figured that SQL Log would capture a query like that but do not see it in the SQL Log.

I had him try to connect again and make sure he used the parameters that successfully connected during the connection test.  It still shows connecting but still shows no DB is found.  I asked him to also open SQL Log and View Log to capture anything there.  The SQL Log just shows DATABASE sysmaster.  According to the user. "The first error had DBPATH set, the second had database set."  Using DBPATH does not seem to work at all.