Forums | Admin Discussion Forums: open-discussion Start New Thread Nested Flat Threaded Ultimate Show 25 Show 50 Show 75 Show 100   By: Alex Pitigoi RE: DB2 on ISeries Connection String [ reply ]   2007-08-31 20:08 Ashikaga, can you please verify again your DB2 client level ($ db2level). IBM_DB support for DB2 on i5 requires a DB2 9 FP2 client due to the underlying DB2 CLI dependencies fixes at that level (you seem to be saying it's only DB2 Express 9 FP1 if we understood correctly). Also, to debug a terminated connection (if this reoccurs while using the minimum 9.1.2 client level) you can trace at the client/CLI level and also at the server level: 1. Enable client CLI level trace: http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.pd.doc/doc/c0020795.htm $ db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1 $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName <path> $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1 $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1 $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimeStamp 3 $ db2 GET CLI CFG FOR SECTION COMMON 2. i5 data sever: change the job description of the DRDA server jobs to spool logs for connections (messages will be visible in the joblog) To change job description: CHGJOBD JOBD(QDFTSVR) LOG(4 00 *SECLVL) This needs the stop and restart of the jobs ENDPJ SBS(QUSRWRK) PGM(QRWTSRVR) OPTION(*IMMED) ... And here the job is restarted STRPJ SBS(QUSRWRK) PGM(QRWTSRVR) When that's set any time a connections started one would see a log either under the user connecting or QUSER if it fails before it's started servicing the profile: WRKSPLF SELECT(QUSER *ALL *ALL QRWTSRVR) After finishing the investigation the logging may need to be reversed to initial values: CHGJOBD JOBD(QDFTSVR) LOG(4 00 *NOLIST) I hope this helps clarify requirements and debugging. Please let us know how it goes. Thanks. By: Ashikaga Mitsuguri RE: DB2 on ISeries Connection String [ reply ]   2007-08-31 18:12 Thanks so much for your help. We discovered that the DRA service was disabled on our machine. Once enabled we were able to connect, however my program terminates immediately. We perform a trace on the Iseries to see what is going on. And we can see my client connecting but immediately being dropped. Further checking with a sniffer did not reveal anything network related. Do you have any ideas on why the connection would terminate so abruptly? Like I said previously, it does connect to the server (full TCP handshake) then it terminates without any error message. I installed the driver on a Fedora Core 6 machine running DB2 Expresss v9.1_01. Regards By: Alex Pitigoi RE: DB2 on ISeries Connection String [ reply ]   2007-08-30 21:58 Hi Ashikaga, As Donald mentioned in his previous post, you can safely assume PORT=446 while connecting to DB2 for i5 data servers, but you also need to know the database name which can either be found from the * LOCAL entry in the DSPRDBDIRE, or even better, in case IASP is used for multiple databses in i5/OS by using a local SQL query to retrieve all DBs available: SELECT CATALOG_NAME FROM QSYS2/LOCATIONS WHERE CATALOG_TYPE ='LOCAL' That being said, here's a simple example of using the connection string from a Windows XP client (from Linux/Unix > require 'ibm_db') D:\>irb irb(main):001:0> require 'mswin32/ibm_db' => true irb(main):002:0> conn = IBM_DB::connect 'DRIVER={IBM DB2 ODBC DRIVER};DATABASE=kp01n7;HOSTNAME=kp01n7.test.com;PORT=446;PROTOCOL=TCPIP;UID=alexp;PWD=secret;', '','' => #<IBM_DB::Connection:0x2f759d4> irb(main):003:0> stmt = IBM_DB::exec conn,"SELECT CATALOG_NAME FROM QSYS2.LOCATIONS WHERE CATALOG_TYPE ='LOCAL'" => #<IBM_DB::Statement:0x2f705c4> irb(main):004:0> while n = IBM_DB::fetch_assoc(stmt) irb(main):005:1> p n irb(main):006:1> end {"CATALOG_NAME"=>"KP01N7"} => nil Note: Mind the empty strings passed after the connection string I hope this clarifies a bit, but feel free to let us know if otherwise. Thanks. By: Donald Parish RE: DB2 on ISeries Connection String [ reply ]   2007-08-30 20:27 For the i5/iSeries, use Port: 446 instead of 50000 (LUW). Another item to check is the Database field. This is not the Schema. You can get this by getting your iSeries person to enter the command DSPRDBDIRE. This will display a list. The line containing *LOCAL in the Remote Location column identifies the RDBNAME (Database). By: Ashikaga Mitsuguri DB2 on ISeries Connection String [ reply ]   2007-08-30 19:55 My company is running DB2 on the Iseries running V5R4M0 and we are wishing to utilize active record for some web applications. I have been able to compile the driver on Fedora Core 6 and have written a small test script. I am not using a DB2 client catalog and instead using the connection string in this form: DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password; In the past we have utilized JDBC to connect and we have never encountered the use of port values. So when we had to specify a port to connect we were somewhat dumbfounded. We began using 50000 and that seemed to work but it just connects and hangs. It never returns the connection back to the program to initiate queries. In fact when we check on the Iseries, the connection seems to be just a standard socket connection but its not recognized by the iseries. I am wondering if my connection string is wrong and if maybe someone can point me to the proper way of setting it up. Please forgive me for I am not very familiar with the Iseries.