informix jdbc stuck connecting

Question

I'm trying to connect to a Informix database server with jdbc using the standard way :

connection = DriverManager.getConnection("jdbc:informix-sqli://"+ip+
/"+sid+":INFORMIXSERVER="+server+";user="+user+";password="+pass+"");

But it keeps trying to connect and does not throw a error message (I suppose it tries to connect because it does not show anything). I'm using IBM Informix driver 4.10.00.1534 and Java 1.7. I have been using this method to connect to Informix servers until now, in fact it only fails with one server. I can connect to this server through Informix clients with odbc but it keeps failing with jdbc with no error message.

Is there any method to verbose the jdbc connection? Any suggestion about why it fails?

UPDATE: The sqlidebug trace:

C->S (4)            
    SQ_VERSION
    SQ_EOT

S->C (14)           
    SQ_VERSION
        "7.31.TD6" [8]
    SQ_EOT

C->S (66)           
    SQ_INFO
        INFO_ENV
            Name Length = 12
            Value Length = 8
            "DBTIME"="%d/%M/%Y"
            "DBTEMP"="/tmp"
            "SUBQCACHESZ"="10"
        INFO_DONE
    SQ_EOT

S->C (2)            
    SQ_EOT

C->S (16)           
    SQ_DBOPEN
         "database" [8]
        NOT EXCLUSIVE
    SQ_EOT

S->C (28)           
    SQ_DONE
        Warning..: 0x15
        # rows...: 0
        rowid....: 0
    serial id: 0
SQ_COST
    estimated #rows: 1
    estimated I/O..: 1
SQ_EOT

C->S (78)           
    SQ_PREPARE
        # values: 0
        CMD.....: "select site from informix.systables where tabname = '   GL_COLLATE'" [65]
    SQ_NDESCRIBE
    SQ_WANTDONE
    SQ_EOT

And the jdbctrace.log says:

 trying com.informix.jdbc.IfxDriver
    SQLWarning: reason(Database selected) SQLState(01I04)
    SQLWarning: reason(Float to decimal conversion has been used) SQLState(01I05)
    SQLWarning: reason(Database has transactions) SQLState(01I01)
    SQLWarning: reason(Database selected) SQLState(01I04)
    SQLWarning: reason(Database has transactions) SQLState(01I01)
    SQLWarning: reason(Database selected) SQLState(01I04)

Solution

Try to run code that connects do Informix database but also shows full exception info and create trace files. One trace file is for JDBC, one is for Informix. Change URL to database, username and password, and run it. You will probably see the problem on screen or in trace file:

import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

class informix_trace
    {
    public static void main(String[] args)
        {
        try
            {
            Class.forName("com.informix.jdbc.IfxDriver");
            FileWriter fwTrace = new FileWriter("c:\\JDBCTrace.log");
            PrintWriter pwTrace = new PrintWriter(fwTrace);
            DriverManager.setLogWriter(pwTrace);
            String debug_url = "SQLIDEBUG=C:\\sqlidebug.trace";
            String url = "jdbc:informix-sqli://1.2.3.4:9088/test_db:informixserver=ol_testifx;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250;" + debug_url
            Connection connection = DriverManager.getConnection(url, "user", "passwd");
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT FIRST 1 DBINFO('version','full') FROM systables;");
            while (resultSet.next())
                System.out.println(resultSet.getObject(1));
            }
        catch (Exception e)
            {
            e.printStackTrace();
            }
        }

    } // class informix_trace

Informix trace file will be with some postfix (timestamp or similar info) and in my case it was something like sqlidebug.trace1391758523500.0. It is binary but you can analyze it using sqliprt utility.

Example of my session with wrong database name:

c:\>sqliprt  sqlidebug.trace1391758523500.0
SQLIDBG Version 1
    ...
S->C (12)
                SQ_ERR
                                SQL error..........: -329
                                ISAM/RSAM error....: -111
                                Offset in statement: 0
                                Error message......: "" [0]
                SQ_EOT

In JDBCTrace.log I can found more interesting info (I see it also on my screen):

SQLState(IX000) vendor code(-111)
java.sql.SQLException: ISAM error: no record found.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3412)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2324)
    ....
    at java.sql.DriverManager.getConnection(Unknown Source)
    at informix_trace.main(informix_trace.java:20)
getConnection failed: java.sql.SQLException: No database found or wrong system privileges.

(I have translated it from Polish so it can be little different)

Comments

[xacy]

I have tried your suggestion about the 3 parameter version of getConnection() and the netstat (i can't install wireshark here). The 3 paremeter version doesn't change anything, it keeps trying to connect and doesn't return an error code (the url builded is with the ip:port standard) and the netstat info gathered only says that the connection is established with the server but doesn't seems to respond. Thank you very much for your time.

[xacy]

I have tried the debug statements you suggest and it didn't finish printing the debug file, at a certain points it freezes and didn't keep printing. I have updated the answer with the results. Thanks again

[Neeraj Sharma]

I am getting exception "Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class net.snowflake.client.jdbc.SnowflakeDriver". Any one can suggest something.