coldfuion mx7 to AS400 DB2 via ODBC - connection but no data

I am trying to use Coldfusion to access a DB2 database on an AS400 via ODBC. I can verify the ODBC from Coldfusion MX7, but cannot access the database in coldfusion. It times out. I am monitoring the network traffic and can see the verification traffic. The actual data access from a coldfusion page does not generate any traffic. I have opened up all ports hoping I would see the traffic and discover what ports are being used. Nothing.  
LVL 1
larksysAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
hi
were you able to access the db2 not through the coldfusion ?
did you try to operate the db2 configurration assistant and check the odbc connectivity ?
you would be able to see which port it tries to access as well

what is the platform of the coldfusion server ?
what version of db2 connect are you using ?
0
larksysAuthor Commented:
I'm running CF MX7 on Win 2003. I can connect and verify the ODBC DB2 connection from it. I can connect to the DB2 from my remote PC via ODBC and VPN connection to the network. I can connect and use the DB2 ODBC from a remote server using CF MX7 that connects via VPN. I can import DB2 data into an Excel worksheet from a remote PC using VPN and the ODBC.
On the Win 2003 server, I can map a drive to the AS400. I just get timeouts when run a CF page using cfquery and the ODBC. Recently (after allowing UDP traffic) I get JRun servlet error 500 null.
0
SidFishesCommented:
have you set up the jdbc (not odbc) datasource in CFADMin? (other datasource type)

http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=80121c8
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

larksysAuthor Commented:
Yes. I've spent many hours trying to research it. All kinds of soultions. Nothing works. For that matter, nothing makes any sense to me. IBM referrences documents that are 7 years old saying that they have been updated but they don't referrence any of the updated guides. Frustrating....
If there is a solutions out there, I will gladly try it. Even Adobe is vague at best.
0
larksysAuthor Commented:
Current settings (after many many versions);

CF Data Source Name         AS400JDBC
JDBC URL       jdbc:AS400://192.168.1.50/qibm/proddata/http/public/jt400
Driver Class       x:/proddata/http/public/jt400.as400jdbcdriver
Driver Name       jt400
User Name       
Password       (16-character limit)
Description
0
SidFishesCommented:
did you have a look at the link i provided?

I'm not familiar with db2 but have used the OTHER Driver type for many different dbms'... none look like the settings you've got...

you're not providing a port in the jdbc url

should be like

jdbc:db2://host_name:port/database_instance

(and you may need to specifiy a collection)

jdbc:db2://host_name:port/database_instance:jdbcCollection=whateveryourJDBCPKG; >

and your driver class should look like

com.ibm.db2.jcc.DB2Driver


0
larksysAuthor Commented:
You're defining a DB2 UDB scenario. I tried to set up IBM's DB2 Express client. That's not really what I want to use.  In fact, all I originally wanted to use was ODBC. Anyway, I'm lost when it comes to JDBC. I have been thouroghly confused. I don't know what port to use. All I have read says that my database instance is, by default, the name of the AS400 (which is the serial number). All the java I need is located on the AS400 at qibm/proddata/http/public/jt400 but there are no conventional setup exe's. I feel like a monkey playing with a football. All the tutorials, whitepapers, redbooks, and guru cookbooks appear to be written by other monkies.
0
SidFishesCommented:
my understanding is you have 2 choices

jdbc
odbc socket

for the odbc socket the set up is simple...Select ODBC SOCKET,  CFAdmin will show you the current odbc connections created on the local machine...select the one you want, enter username and pwd and you're done...

as for jdbc...the link provides simple step by step set up...you don't need to worry about java, exe's or anything... CF comes with the jdbc driver...

copy the 2 files restart the cf Service, submit the params and you're done... the default port for db2 is 50000...note the driver info is case sensitive...

should look something like when done

CF Data Source Name         AS400JDBC
JDBC URL       jdbc:db2:192.168.1.50:50000/database_instance
Driver Class       com.ibm.db2.jcc.DB2Driver
Driver Name       DB2Driver
User Name       xxxxxx
Password      xxxxxx

0
larksysAuthor Commented:
The jar files referred to in the Adobe article do not exist either on the AS400 or the cf server. There has to be something missing. If CF MX7 supplies those files, I cannot find them.
0
SidFishesCommented:
cf doesnt supply the jar file... i was referring to your comment about setup exe etc...

the jar file is installed when you select jdbc support on install

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/t0010264.htm

you get them after the fact from http://www-128.ibm.com/developerworks/db2/downloads/jcc/

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
larksysAuthor Commented:
I went to the IBM site and downloaded the 2 files and licenses. I placed them in the folders per Adobe instructions. I stopped and started the CF app server service. Then I submitted the data source for verification from the admin page. Here's what I got;

 Connection verification failed for data source: AS400JDBC
com.ibm.db2.jcc.c.SqlException: Failure in loading T2 native library db2jcct2
The root cause was that: com.ibm.db2.jcc.c.SqlException: Failure in loading T2 native library db2jcct2
0
momi_sabagCommented:
The Failure in loading message indicates one of two things:
Usually this happens when the machine was not rebooted after installing DB2. Reboot the machine getting the error and try it again.
The DB2 context is not getting set up correctly for the user running the test. Source the db2profile file on the machine, and ensure that the environment contains pointers to the DB2 native libraries.
0
larksysAuthor Commented:
You have been very very patient and informative. I really appreciate it. But, I have no idea what "source the profile" or "ensure the environment contains" means. I don't have a UDB running on the AS400. Just a plain ol' vanilla AS400 database. It is a DB2 database. Just not UDB. I downloaded and installed the DB2 client again. I rebooted the server. Now I can't even verify the ODBC connection much less get the JDBC to work. So, I am going to back off the DB2 client and concentrate on the ODBC. Everywhere I look people mention that they had no trouble getting their JDBC to work. I think maybe they mean they initally set up their servers to run the JDBC. Anyway, anyone looking at this thread might get the point that setting up a Windows 2003 server with Coldfusion MX7 to communicate with an AS400 through a firewall might prove to be a lengthy task for someone not versed in Java.
0
momi_sabagCommented:
The DB2 context is not getting set up correctly for the user running the test. Source the db2profile file on the machine, and ensure that the environment contains pointers to the DB2 native libraries.
 this means that you need to check your windows machine
and make sure that the user that runs the odbc test has the db2 envrionment set up correctly
that involves a couple of environment variables
but if you don't want to use jdbc, then it is of no concern to you
0
larksysAuthor Commented:
You might want to read my initial post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.