AW: JDBC and MTS
Hi Mladen We started evaluating MTS and JDBC Thin Driver (newest version for JDK 1.4, running on Win2k against a test db on win2k, test db is oracle 9.2.0.3.0). I never used SRVR=DEDICATED. I have a dispatcher running on port 5000 and a listener on port 1521. The only thing I do to direct the connection to either use MTS or dedicated server is setting the port it connects to to either 1521 or 5000. That works just fine. Stefan -Ursprüngliche Nachricht- Von: Mladen Gogala [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 17. Juni 2003 14:25 An: Multiple recipients of list ORACLE-L Betreff: Re: JDBC and MTS OK. Let me get some things straight: a) I'm not a developer. I'm one of those vile creatures called DBAs. I don't program. I troubleshoot other people's programs. Other people program, I nag. When developers start writing bug-free software which uses the database in the most optimal way, my job is gone. b) The whole problem is that even after specifying SRVR=DEDICATED in the connect string, the darned thing still connects as an MTS connection. c) What was the issue that you faced? That was my original question. On 2003.06.17 07:09, Regis Biassala wrote: Richard is right...If ur Java application uses it own connection pooling...then do not use MTS...it slows down connections and more...We faced the same issue here Our configuration allows DBA to choose weather connection pooling should handled by the app or the database... Use dedicated servers if there's noway you can disable the application connection pooling... Regis -Original Message- Sent: Tuesday, June 17, 2003 6:05 AM To: Multiple recipients of list ORACLE-L I used to seen problems with JDBC Thin with MTS on Linux and switching to a dedicated connection seemed to fix the problem. But JDBC Thin and MTS worked fine on my Solaris box. Not sure with HP-UX. Is the Java application running on an Application Server? Richard Ji -Original Message- Sent: Monday, June 16, 2003 11:25 PM To: Multiple recipients of list ORACLE-L I'm not a Java expert so please forgive me my ignorance. JDBC application is facing very strange performance problems during connect. Every now and then everything appears to be hung and then, 10 minutes later, users proceed normally but with the elevated blood pressure and serious lack of patience. I was told that JDBC has it's own connection pooling mechanism and that it will start it's own dedicated server connection. It seems though that the string SRVR=DEDICATED has been ignored and that users are acquiring a shared server connection. Does anybody in this group have any experience with JDBC and MTS? Version is 8.1.7.1, 64 bit on HP-UX 11 with OPS. Dispatchers are cross-registered with listeners on all 4 nodes for load balancing purposes. I found surprisingly little material on the Metalink. No network collisions, no retransmits, no timeouts can be seen from netstat -i and netstat -s. The NIC is 1GB Ethernet and I would be very surprised if approximately 100 users could kill it with a JDBC application. They could use DBA as a human sacrifice, though. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender
AW: JDBC and MTS
Hi Regis That's basically right. As I just posted, we use MTS for development, so that not every developer starts up a local connection pool on their workstation and we also don't end up with too many dedicated server processes. But I think you're right saying that MTS and application side connection pooling is pretty much redundant, if not even the source for trouble. a) the app server should start and monitor the connection pool. b) the client sessions (or app threads within the app server or whatever) request connections from that pool. c) important aspect here is the fact that the app server thinks that it maintains a pool of readily available, permanent connections. The clients (or whoever) will just be passed a reference to a certain connection. After they are finished with what they want to do, they put the connection back into the pool (plus some connection re-aquiring mechanism for dead clients and the like). d) That means, that with MTS, a new player shows up, as in the connections held in the connection pool are not permanent (or dedicated in oracle terms). That might lead to some latency while fetching a valid connection from the pool and using it, because when you actually start using it, you end up with some overhead since the dispatcher has to give you a connection. e) I don't know for sure, but has anybody ever testet some of the new features like sharing prepared statements between connections (and the possibility to tag a name to a statement for further information / statement fetching) with MTS ? So, I think it's either MTS OR Java Connection Pooling, not BOTH. Open for bashing, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Regis Biassala [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 17. Juni 2003 16:15 An: Multiple recipients of list ORACLE-L Betreff: RE: JDBC and MTS We add: 1. Application (Java) Using a JDBC Connection Pooling (CP)feature... 2. Database Oracle in MTS, pooling or not pooling enabled This was not good for us...so we kept the Java pooling side of the app and Oracle configured to run in dedicated mode. We'got a XML policy file which disables JDBC connection pooling for our application and gives the DBA a choice to configure a MTS... So in our case we two scenarios: 1. DB(MTS) and app(without CP) this is OK 2. DB(dedicated) and app(with CP) Regis -Original Message- Sent: Tuesday, June 17, 2003 1:25 PM To: Multiple recipients of list ORACLE-L OK. Let me get some things straight: a) I'm not a developer. I'm one of those vile creatures called DBAs. I don't program. I troubleshoot other people's programs. Other people program, I nag. When developers start writing bug-free software which uses the database in the most optimal way, my job is gone. b) The whole problem is that even after specifying SRVR=DEDICATED in the connect string, the darned thing still connects as an MTS connection. c) What was the issue that you faced? That was my original question. On 2003.06.17 07:09, Regis Biassala wrote: Richard is right...If ur Java application uses it own connection pooling...then do not use MTS...it slows down connections and more...We faced the same issue here Our configuration allows DBA to choose weather connection pooling should handled by the app or the database... Use dedicated servers if there's noway you can disable the application connection pooling... Regis -Original Message- Sent: Tuesday, June 17, 2003 6:05 AM To: Multiple recipients of list ORACLE-L I used to seen problems with JDBC Thin with MTS on Linux and switching to a dedicated connection seemed to fix the problem. But JDBC Thin and MTS worked fine on my Solaris box. Not sure with HP-UX. Is the Java application running on an Application Server? Richard Ji -Original Message- Sent: Monday, June 16, 2003 11:25 PM To: Multiple recipients of list ORACLE-L I'm not a Java expert so
AW: JDBC
Hi Antje Eventhough I never migrated Sybase (or SQL Server) code (JDBC/Java) to Oracle, maybe that helps anyway: In Transact SQL, you can actually return data from an anonymous block, that's correct. An equivalent to do this in PL/SQL might be the use of OUT parameters within stored procedures. That means that you just have to write wrappers for your T-SQL blocks (put them into PL/SQL stored procs). Done that, you can communicate from Java with the procedure through the OUT parameter, once you registered the proc with it's OUT parameters within the Java context. After binding the parameter to Java variables, the data will be transferred to those variables (when the proc is executed) for your further use within the Java context. Nothing new, actually. A problem might be the syntactical difference between T-SQL and PL/SQL, eventhough Oracle is able to handle more of the syntax you encounter in T-SQL like CASE or ANSI SQL joins. Hope that helps a little bit. If not, pipe it to /dev/null ;) Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto:[EMAIL PROTECTED] visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Behalten Sie den Ueberblick - mit dem neuen BasicOverView, unserer Seminaruebersicht fuer das 2. Halbjahr 2002. Sie haben noch kein Exemplar? Schreiben Sie eine E-Mail an mailto:[EMAIL PROTECTED] oder rufen Sie uns an unter 0 18 03 / 73 64 62 73! Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Sackwitz, Antje [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 5. Juli 2002 17:13 An: Multiple recipients of list ORACLE-L Betreff: JDBC Hi, gurus, collegues of mine told me, that Sybase can return information from anonymous blocks. When I do not want to use SQLJ, but only JDBC what is the best way to migrate the Sybase code to Oracle. Any tips appreciated, Regards, Antje Sackwitz Antje Sackwitz ppi Media GmbH Deliusstraße 10 D-24114 Kiel phone +49 (0) 43 1-53 53-2 16 fax +49 (0) 43 1-53 53-2 22 email mailto:[EMAIL PROTECTED] web www.ppi.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sackwitz, Antje INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: JDBC
Hi Richard You can do that. But I think it is recommandable to wrap up the PL/SQL and put it into packages. This way, your code is much better organized than having anonymous blocks throughout your Java code. Regards, Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto:[EMAIL PROTECTED] -Ursprüngliche Nachricht- Von: Ji, Richard [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 8. Juli 2002 17:38 An: Multiple recipients of list ORACLE-L Betreff: RE: JDBC Maybe I misunderstood what you guys are talking about. And sorry if that's the case. But you can call anonymous block and return data from Oracle to Java: CallableStatement stmt = conn.prepareCall( begin + open ? for select ename from emp; + end;); stmt.registerOutParameter(1,OracleTypes.CURSOR); stmt.execute(); Richard -Original Message- Sent: Monday, July 08, 2002 6:23 AM To: Multiple recipients of list ORACLE-L Hi Antje Eventhough I never migrated Sybase (or SQL Server) code (JDBC/Java) to Oracle, maybe that helps anyway: In Transact SQL, you can actually return data from an anonymous block, that's correct. An equivalent to do this in PL/SQL might be the use of OUT parameters within stored procedures. That means that you just have to write wrappers for your T-SQL blocks (put them into PL/SQL stored procs). Done that, you can communicate from Java with the procedure through the OUT parameter, once you registered the proc with it's OUT parameters within the Java context. After binding the parameter to Java variables, the data will be transferred to those variables (when the proc is executed) for your further use within the Java context. Nothing new, actually. A problem might be the syntactical difference between T-SQL and PL/SQL, eventhough Oracle is able to handle more of the syntax you encounter in T-SQL like CASE or ANSI SQL joins. Hope that helps a little bit. If not, pipe it to /dev/null ;) Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto:[EMAIL PROTECTED] visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Behalten Sie den Ueberblick - mit dem neuen BasicOverView, unserer Seminaruebersicht fuer das 2. Halbjahr 2002. Sie haben noch kein Exemplar? Schreiben Sie eine E-Mail an mailto:[EMAIL PROTECTED] oder rufen Sie uns an unter 0 18 03 / 73 64 62 73! Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Sackwitz, Antje [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 5. Juli 2002 17:13 An: Multiple recipients of list ORACLE-L Betreff: JDBC Hi, gurus, collegues of mine told me, that Sybase can return information from anonymous blocks. When I do not want to use SQLJ, but only JDBC what is the best way to migrate the Sybase code to Oracle. Any tips appreciated, Regards, Antje Sackwitz Antje Sackwitz ppi Media GmbH Deliusstraße 10 D-24114 Kiel phone +49 (0) 43 1-53 53-2 16 fax +49 (0) 43 1-53 53-2 22 email mailto:[EMAIL PROTECTED] web www.ppi.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sackwitz, Antje INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also