Solved

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

Posted on 2007-04-08
15
1,318 Views
Last Modified: 2013-12-24
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.  
0
Comment
Question by:larksys
  • 8
  • 4
  • 3
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18873062
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
 
LVL 1

Author Comment

by:larksys
ID: 18873271
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 18877392
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
 
LVL 1

Author Comment

by:larksys
ID: 18877463
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
 
LVL 1

Author Comment

by:larksys
ID: 18877499
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 18877605
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
 
LVL 1

Author Comment

by:larksys
ID: 18877833
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 36

Expert Comment

by:SidFishes
ID: 18877984
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
 
LVL 1

Author Comment

by:larksys
ID: 18878573
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
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 18878623
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
 
LVL 1

Author Comment

by:larksys
ID: 18878774
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18880924
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
 
LVL 1

Author Comment

by:larksys
ID: 18882901
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18883070
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
 
LVL 1

Author Comment

by:larksys
ID: 18883172
You might want to read my initial post.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now