Ok, I had a hell of a time trying to get this figured out. It seems like my Ingres 2006 install environment did not match the documentation on the ingres website, which looks to be docos for the older r3 product. This seriously sucked. C’mon guys – get the docos right.

Anway, here’s what I had to do.

I didn’t install this ingres instance, but I did realize that a process named ‘iijdbc’ was running. Apparently this is what allows jdbc to connect to an Ingres 2006 installation. We do not have Data Access Server running (iigdc).

When doing a process listing, this is what I get:

> ps aux | grep ingres
ingres 3903 0.0 0.0 11140 2228 pts/0 S 15:50 0:00 /ingres/application/ingres/bin/iigcn II gcn
ingres 4038 0.0 1.0 145164 55068 pts/0 Sl 15:50 0:00 /ingres/application/ingres/bin/iidbms recovery (dmfrcp) II
ingres 4156 0.0 0.0 86212 3748 pts/0 S 15:50 0:00 /ingres/application/ingres/bin/dmfacp II
ingres 4177 0.0 0.7 322120 37260 pts/0 Sl 15:50 0:00 /ingres/application/ingres/bin/iidbms dbms (default) II
ingres 4335 0.0 0.0 59244 2768 pts/0 S 15:50 0:00 /ingres/application/ingres/bin/iijdbc II jdbc

And here is the corresponding output from the ‘ingstatus’ command (corroborates the above, just less verbose):

> ingstatus
Ingres II name server (iigcn) - running
Ingres II recovery server (dmfrcp) - running
Ingres II DBMS server (iidbms) - 1 running
Ingres II Net server (iigcc) - not active
Ingres II Data Access server (iigcd) - not active
Ingres II JDBC server (iijdbc) - 1 running
Ingres II archiver process (dmfacp) - running

So, given this starting point, here’s what I had to do:

As the ingres user, we had created a ‘cls’ database via the createdb command. Then we used accessdb to create the ‘cls’ user account inside ingres. Then we loaded the sql schema via accessdb.

So now, I had a cls user account and a schema with tables, but I couldn’t connect for the life of me over JDBC using Ingres 2006’s provided jdbc driver (found at $II_SYSTEM/ingres/lib/edbc.jar).

It turns out this is because Ingres uses Operating System user accounts for logging in over tcp. So, this meant I had to create a cls user as root:

# adduser cls

and gave it a suitable password:

# passwd cls

(this OS level password is then used for the JDBC connection later on).

Now that there is an OS user called ‘cls’ – exactly the same as my previously created Ingres database – I then had to let ingres know about this os user account and password:

(as root):

# . /home/ingres/.ingIIbash
# mkvalidpw

This command compiles and installs a program called ‘ingvalidpw’ that is not is not included in the standard Ingres2006 installation. This allows you to validate if a password is valid against an operating system user account known to Ingres.

Once that’s done, you’ll have to restart Ingres (as root):

# /etc/init.d/ingresII restart

To validate that Ingres password checking works, you should see that the Ingres env var II_SHADOW_PWD is set to the location of that compiled ingvalidpw executable:

# su - ingres
> ingprenv

In the list that is generated, you should see the II_SHADOW_PWD var set.

Now, you can verify that the username and password you would use for JDBC for your user will work:

echo | $II_SYSTEM/ingres/bin/ingvalidpw ; echo $?

If you see any value other than 0 returned, then the username/password entered doesn’t match the OS username/password. If you do see non-zero, then you can look in the $II_SYSTEM/ingres/files/iipwd/ingvalidpw.c file and see what the integer corresponds to find out what went wrong.

I then fired up DB Visualizer, pointed it to the edbc.jar file I specified earlier, and then used this url:

jdbc:edbc://:21071/

So, in my case it looked like this:

jdbc:edbc://localhost:21071/cls

I clicked ‘test connection’ in DbVisualizer, and lo and behold! I was able to access all my tables!

I hope this is of use for someone – it took me close to 3 or 4 hours to get this working.

Here is the original Ingres forums thread that led me to my posting (you may need an Ingres account to access it, but its free):

http://opensource.ingres.com/projects/ingres/forum/30/121418044682?b_start:int=0#235464875124