tuxedo中不采用XA连接oracle数据库的例子 - Tuxedo (Chinese)

int tpsvrinit(int argc,char** argv)
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL END DECLARE SECTION;
strcpy(username.arr,"scott");
username.len = strlne(username.arr);
strcpy(password.arr,"tiger");
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
EXEC SQL CONNECT :username IDENTIFIED BY :password /* AT abc
USING :dbstring (if use dblink use it)*/;
sqlerror:
EXEC SQL WHENEVER SQLERROR CONTINUE;           
userlog("tpsvrinit connect db sqlcode=[%d] err=[%
s]",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
return(0);
}
void tpsvrdone()
{
EXEC SQL COMMIT WORK RELEASE;
if(sqlca.sqlcode)
userlog( " Close database failed sqlcode=[%d]",sqlca.sqlcode);
else
userlog( "tpsvrdone(): Shutdown server successful!" );
}
此文被wsywin在2003/01/18 09:44:26修改! 

你这个例子好像是一本关于Pro *C的书上的,不算厚,我看过。实际开发中,这个例
子实在是太文邹走了,我们现在也是直连的,简洁得多。不过这样也是很清晰的、呵
呵,不好意思,在高手面前乱说话,不要介意。 

qiupeng :
你说的:
你这个例子好像是一本关于Pro *C的书上的,不算厚,我看过。实际开发
中,这个例子实在是太文邹走了,我们现在也是直连的,简洁得多。不过这样
也是很清晰的、呵
呵,不好意思,在高手面前乱说话,不要介意。
---------------------------------------------------------
qiupeng:我也不是高手。我知道使用tuxedo有很多高手。qiupeng你的经验
也很丰富,也是一个高手。还需要向你学习呢~.大家的目的是把tuxedo论坛办好,大
家共同努力吧!tuxedo我也有不太明白的地方呢,还请您指教。
其实我发的只不过是一个例子而已。你说的那本书是清华大学培训的pro*C
书。我们以前开发的时候也看过。而且在网上也有电子pdf版的。
使用pro*C联接数据就是使用connect语句。我在开发过程中使用过这样的连接方
式。在tuxedo服务中不使用XA直接连接数据库也使用过。使用XA联接数据库也使用
过。我发这个例子是给nakie看得。
这种方法是tmboot时候,就已经和数据库连接上了。不用每次都连接了。也
就是常连接方式,不过挺浪费资源的。因为tmboot启动后就和数据库连接上
了。另外就是在tuxedo服务上连接,在服务完成后断开。例如:
SVCNAME(TPSVRINFO *rqst)
{
db_connect ();
............
main program
...............
db_disconnect();
tpreturn();
}
这样就是每次服务运行时都与数据库连接一次,处理完成后就断开。这样服务
端处理速度慢一些。因为每次都需要连接数据库。但是可以不浪费资源。
此文被wsywin在2003/01/19 10:06:16修改! 

通常情况下,每个SERVER中含有同类的多个SERVICE,也就是公用一个连接,每个
SERVER一个连接,这个连接可以被复用,可以节省连接数。
如果在每个SERVICE 中都去连接,就不只是有点慢的问题了,当并发量大时
这种方法使用的连接数,会比公用连接时多很多,而且连接费时,系统的整个
性能有很大下降(测试过)。
个人意见:在SERVICE 中连接数据库不好。 

谢谢ysywin兄对我不逊言论的宽容。我对这个问题一直有疑问,因为我们现在也是用
直连的方式,理论上说是每次service处理一个请求就产生一个Oracle(我们用
Oracle)连接,但是我做压力测试时,并发100个请求,在TOAD里并没有发现oracle
的连接数有大的变化,基本没有变化,也请教过DBA,查看连接的方式应该是没问题
的。why?还有ezp说的每个server保有一个连接是怎么做的,是connect直接连的
吗,请指教,3X先! 

是wsywin,呵呵 

qiupeng :你是谁?我没有别的意思,只是大家互相取长补短,共同把tuxedo论
坛办好。
ezip所说的:
通常情况下,每个SERVER中含有同类的多个SERVICE,也就是公用一个连接,每
个
SERVER一个连接,这个连接可以被复用,可以节省连接数。
---------------------------------
他这么说我的理解是在tpsvrinit()连接数据库,在tpsvrdone断开数据库连接,
而且在一个 server中含有多于一个的services.就是:
SERVICESNAME1(TPSVCINFO *rqst)
{
......
}
SERVICESNAME2(TPSVCINFO *rqst)
{
......
}
............
SERVICESNAMEn(TPSVCINFO *rqst)
{
......
}
这种方法把不同类型的service作了合并。那么各个service就可以共享数据库连
接了。
------------------------------
如果在每个SERVICE 中都去连接,就不只是有点慢的问题了,当并发量大时
这种方法使用的连接数,会比公用连接时多很多,而且连接费时,系统的整个
性能有很大下降(测试过)。
个人意见:在SERVICE 中连接数据库不好。
--------------------------------
他的这个说法就是每一个serve中包含一个service,那么数据库连接就多了,而
且是在serice中连接数据库。这样占用的资源就大了。
此文被wsywin在2003/01/21 10:18:34修改! 

呵呵,我是谁,为什么这么问呢?
ezp兄的话关键就在于是在server中连接还是在service中连接的问题,他的建议是在
server中连接。这是我的理解。现在一个server中如果有多个service,假设每个
service现在都接收到请求,那个这一个连接怎么处理呢?一个service处理一个请求
的时候可以理解为是一个事务,会有完整性控制。我有点不理解,请详述。 

还以为找我算帐呢 -:)
我在广州,注册时把名字都泄漏了。现在做电信方面的开发。以前用过CICS,不过对
TUXEDO一见钟情,还在学习中。大家多交流,这么多东西,每一个弄个例子试一下没
那么多时间,还要干活啊,呵呵。 

写了大半天居然在提交时出错,我最近在这里总有这样的问题,:(
通常一个server中可以包含多个service
tpsvrinit()
youservice1()
youservice2()
youservice3()
tpsvrdone()
这是使用的连接
tpsvrinit()--------------------------------tpsvrdone ()
↑            ↑
每个service 每个service
使用连接的入口 使用连接的出口
每个service 必须设置事务起始点,这样就与其他service相独立
service在使用连接时必须判断连接是否可用,否则会被当掉。可以自试。
这样你写的系统,在数据库重启,或故障时,就不会出现service
被当掉。
同时稍加改动,不用考虑数据库重启。后期维护,也简单,
这样添加代码不多,而且与数据库独立,在上线运行时,数据库重启,
tuxedo就不用了,不是省了好多事。

怎样判断数据库连接是否可用 

tmboot后,看看TMS进程能否正常启动? 

我和ezp 的看法相似!
保持长连接的方式对于并发性高的系统速度可能快不了,而且数据库重起了的
话,tuxedo的服务也要重起! 

高手很多啊。我们的系统中非XA方式就是采用在SERVER中去连接。 

这种不采用xa连接oracle数据库的方式有一个缺点:
如果你的一个操作(一个事务)分布在两个service中,但是这两个
service不在一个进程内,即使操作的是同一个数据库,也无法作
事务控制。

Related

pro*c multithreaded  Connection pool

Hi,
I don't know where to post a message about pro*c, so i post it here. I am trying to use connection spool method using Pro*c. But it is not working. I created code as mentioned following site
http://oraclelon1.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_11thr.htm#i1000292
code is
---------------------------------
#include <stdio.h>
#include <sqlca.h>
#ifdef DCE_THREADS
#include <pthread.h>
#else
#include <thread.h>
#endif
#define CONNINFO "hr/hr"
#define THREADS 40
#define MAX_ROWS 256
#define BUFF_LEN 21
/***** prototypes ************** */
#ifdef DCE_THREADS
void selectFunction();
void updateFunction();
#else
void *selectFunction();
void *updateFunction();
#endif
void err_report(struct sqlca sqlca);
/* ************************* */
/***** parameter to the function selectFunction, updateFunction */
struct parameters
{
sql_context ctx;
char connName[20];
char dbName[20];
int thread_id;
};
typedef struct parameters parameters;
/*******************************************/
parameters params[THREADS];
struct timeval tp1;
struct timeval tp2;
int main()
{
int i, status;
thread_t thread_id[THREADS];
int thrNos[THREADS];
for(i=0; i<THREADS; i++)
thrNos[i] = i;
EXEC SQL ENABLE THREADS;
/* Time before executing the program */
if(gettimeofday(&tp1) == -1){
perror("First: ");
exit(0);
}
EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
/* connect THREADS times to the data base */
for(i=0; i<THREADS; i++)
{
strcpy(params.dbName, "inst1");
sprintf(params[i].connName,"conn%d", i);
params[i].thread_id = i;
/* logon to the data base */
EXEC SQL CONTEXT ALLOCATE :params[i].ctx;
EXEC SQL CONTEXT USE :params[i].ctx;
EXEC SQL CONNECT :CONNINFO
AT :params[i].connName USING :params[i].dbName;
}
/* create THREADS number of threads */
for(i=0;i<THREADS;i++)
{
printf("Creating thread %d \n", i);
if(i%2)
{
/* do a select operation if the thread id is odd */
#ifdef DCE_THREADS
if(pthread_create(&thread_id[i],pthread_attr_default,
(pthread_startroutine_t)selectFunction,
(pthread_addr_t) ¶ms[i]))
#else
if(thr_create(NULL, 0, selectFunction,
¶ms[i], 0, &thread_id[i]))
#endif
printf("Cant create thread %d \n", i);
}
else
{
/* othewise do an update operation */
#ifdef DCE_THREADS
if(pthread_create(&thread_id[i],pthread_attr_default,
(pthread_startroutine_t)updateFunction,
(pthread_addr_t) ¶ms[i]))
#else
if(thr_create(NULL, 0, updateFunction,
¶ms[i], 0, &thread_id[i]))
#endif
printf("Cant create thread %d \n", i);
}
}
for(i=0; i<THREADS; i++)
{
#ifdef DCE_THREADS
if(pthread_join(thread_id[i],&status))
printf("Error when waiting for thread % to terminate\n", i);
if(pthread_detach(&thread_id[i]))
printf("Error detaching thread! \n");
#else
if(thr_join(thread_id[i], NULL, NULL))
printf("Error waiting for thread i(%d) to terminate\n", i);
#endif
}
if(gettimeofday(&tp2) == -1){
perror("Second: ");
exit(0);
}
printf(" \n\nTHE TOTAL TIME TAKEN FOR THE PROGRAM EXECUTION = %f \n\n",
(float)(tp2.tv_sec - tp1.tv_sec) + ((float)(tp2.tv_usec -
tp1.tv_usec)/1000000.0));
/* free the context */
for(i=0; i<THREADS; i++)
{
EXEC SQL CONTEXT USE :params[i].ctx;
EXEC SQL AT :params[i].connName COMMIT WORK RELEASE;
EXEC SQL CONTEXT FREE :params[i].ctx;
}
return 0;
}
#ifdef DCE_THREADS
void selectFunction(parameters *params)
#else
void selectFunction(parameters params)
#endif
{
char empName[MAX_ROWS][BUFF_LEN];
printf("Thread %d selecting .... \n", params->thread_id);
EXEC SQL CONTEXT USE :params->ctx;
EXEC SQL AT : params->connName
SELECT FIRST_NAME into empName from EMPLOYEES;
printf("Thread %d selected ....\n", params->thread_id);
return 0;
}
#ifdef DCE_THREADS
void updateFunction(parameters *params)
#else
void updateFunction(parameters params)
#endif
{
printf(" Thread %d Updating ... \n", params->thread_id);
EXEC SQL CONTEXT USE :params->ctx;
EXEC SQL AT :params->connName update EMPLOYEES
set SALARY = 4000 where DEPARTMENT_ID = 10;
/* commit the changes */
EXEC SQL AT :params->connName COMMIT;
printf(" Thread %d Updated ... \n", params->thread_id);
return 0;
}
/*********** Oracle error ***********/
void err_report(struct sqlca sqlca)
{
if (sqlca.sqlcode < 0)
printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);
exit(0);
}
---------------------------------------------------------------------
I compiled that code with CPOOL=yes CMIN=5 CINCR=2 CMAX=14
but still 40 connections are created ,
In that documentaion,specifies that only 14 connections are created.
If anyone know about this please reply
Thanks & Regards
Umarali 
If you haven't already got this figured out, I'll get on a box that I can try it on. 
hi,
Thanks for your reply. Have u think any code is available for connection pool ..? 
Just the OCI demos. When I get a few free minutes today, I'll give this a try and see what I get. 
Your total threads are already 40 ....?
The context allocation are called 40 times.
Does this ring any bell......??
Please check the code again. 
thanks for your reply. when CPOOL=NO, the application will create 40 dedicated connections. When CPOOL=YES, it will create 2 connections initially and only if 2 threads access the connections concurrently, it will create more connections. Otherwise, all threads will share those 2 connections. So the application avoids 38 connections which in turn avoids 38 round trips to the server to establish connections.
as per Oracle documentation. but it is not working 
I experianced same problem. I tried pro*c connection pooling in both 10g R2 and 11g R1 without success. If I set CMAX=4 and CMIN=2 on compile time, and start 10 threads connecting to DB with their own sql_context, then total number of connections is not 4 but 14. I also tried connecting through listener but failed. I felt like fooled by Oracle document.. :-| Anyone succeeded in pro*c connection pooling?
My platform is 64-bit Linux (CentOS5).
Message was edited by:
chanik 
How are you checking the actual number of connections created? Did you see the server processes spawned? 
Hello kmohan, I saw your reply today..
I used the following simple query to check the number of connection.
SQL> connect /as sysdba
SQL> select count(*) from v$session;
When I disable connection pooling, this query gives the exact number of additional connections I make. And when this count reaches some limit, oracle won't allow new connections. But when I enable the connection pooling, in any of my oracle installations, number of connections doesn't match as stated in the document. It simply doesn't work.
I was kind of surprised that I couldn't find any useful info to resolve this issue. Pro*C connection pooling has been supported since around oralce 9.2! It's so easy to deploy (though it doesn't seem to work). Nobody's using this nice feature? Do I have to subscribe to metalink to resolve this problem? Sorry I talked too much.. 
Problem solved. Actually there's no real problem at all except for my poor understanding on connections and sessions. I was counting number of sessions instead of physical connections while trying to control total number of connections..
Here's my findings;
- session count :
This can be checked by "select count(*) from v$session;".
Total session count increased as "number of sql contexts + CMAX".
- connection count :
This may be checked by "select count(*) from v$process;"?
as long as you're using oracle in dedicated mode.
(I have no experience with shared mode, so I don't know about that mode)
Total connection count increased as expected with connection pool.
In CMIN=2 CMAX=4 configuration, initial number of physical connections
are 2, and as I create more sql contexts and connect to db,
it increased at most up to 4.
Result is simple, though I spent much time in doing tons of tests to figure out what's what.
I hope this helps anyone in trouble like I was.

java grants not active on both RAC nodes

Hello, we started observing a problem related to java grants on a system running Oracle 12.1.0.2.0 (exadata). It seems java grants there are often not effective on both RAC nodes, eg one will execute the codethat depends on the grants without problem, the other will not, and the ORA-29532 errorwe are getting actually suggest the very statement we are using when deploying! So let's say my db is TESTDB, served by nodes testdb01 and testdb02,then invoking the same bit of java code will work or fail depending on whichnode handles the requestHas anyone observed anything similar ? I have to stress we have another database running the same SW release that does *not* exhibit this behaviour.We have been unable to pinpoint differences in parameters that may explain this. Please find below a simple test case -- to be executed as SYS
CREATE USER TMP_TEST_CASE IDENTIFIED by "DumbPwd123";
GRANT CREATE SESSION       TO TMP_TEST_CASE;
GRANT CREATE TABLE         TO TMP_TEST_CASE;
GRANT CREATE PROCEDURE     TO TMP_TEST_CASE;
GRANT UNLIMITED TABLESPACE TO TMP_TEST_CASE;
 java code below, contained in a file named FileDirTest.java
package filedir;
import java.io.File;
public class FileDirTest {
public static void main(String[] args) {
  System.out.println (FileDirTest.getFiles(args[0]));
}
public static String getFiles ( String dirname )
{
  String result = "";
  try
  {
   File dir = new File ( dirname );
   File files [] = dir.listFiles();
   for (int i = 0; i < files.length; i++)
   {
    if (i == 0) result += files[i].getName();
    else result += ";"+files[i].getName();
   }
  }
  catch (Exception e)
  {
   e.printStackTrace();
   result = e.getMessage();
  }
  return result;
}
}
 from the command-line, upload the java class into the test schema:
loadjava -resolve -u  TMP_TEST_CASE/DumbPwd123#TESTDB FileDirTest.java
 while connected as SYS:
ALTER SESSION SET CURRENT_SCHEMA = TMP_TEST_CASE;
DROP FUNCTION listdir;
CREATE FUNCTION listdir (dirname VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'filedir.FileDirTest.getFiles(java.lang.String) return java.lang.String';
/
exec dbms_java.grant_permission( 'TMP_TEST_CASE', 'SYS:java.io.FilePermission', '/tmp', 'read' );
then connected as TMP_TEST_CASE (needs a fresh session to ensure the grants are supposedly effective)
select listdir ('/tmp') from dual;
 on one of our nodes this correclty return the concatenated list of filnames under /tmp, but on the other 
the Permission (java.io.FilePermission /tmp read) has not been granted to TMP_TEST_CASE. The PL/SQL to grant this is dbms_java.grant_permission( 'TMP_TEST`CASE', 'SYS:java.io.FilePermission', '/tmp', 'read' )
since i cannot seem to be able to edit my own post, after the loadja invocation the definition of the PL/SQL wrapper procedure was missing, thus: while connected as SYS:
ALTER SESSION SET CURRENT_SCHEMA = TMP_TEST_CASE;
DROP FUNCTION listdir;
CREATE FUNCTION listdir (dirname VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'filedir.FileDirTest.getFiles(java.lang.String) return java.lang.String';
/
exec dbms_java.grant_permission( 'TMP_TEST_CASE', 'SYS:java.io.FilePermission', '/tmp', 'read' );

Problems with Pro*C and the double datatype

Hi!
I'm working with Pro*C 8.1.7 on HPUX 11.0, but I've a problem
with the datatype double.
On runtime, I got a Bus Error (core dumped) when try assign a
double bind_variable (Pro*C) with a double variable (ANSI_C)
For example:
typedef struct xxxx
{
...
...
float item_ANSI_C;
double variable_ANSI_C;
...
} xxxx;
void procedure_name()
{
EXEC SQL BEGIN DECLARE SECTION;
xxxx *POINTER;
...
double double_bind_variable_PRO*C;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO (...);
(...)
for (i = 0; ; i++)
{
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH cursor INTO (...),
(...),
:double_bind_variable_PRO*C,
(...);
(...)
POINTER.item_ANSI_C = other_bind_variable_PRO*C;
(***)POINTER[i].variable_ANSI_C = double_bind_variable_PRO*C;
(...)
}
(...)
}
(***): In this point the program throw BusError(core dumped).
However, the float datatype does not.
------- End Example -------------
Somebody have any solution for this problem? Please.
Thanks, in advance.
Alex.

ORA-02035 error when execute storedproc

I'm using Oracle817 release 3.
when I use proc/c++ program execute a storedproc ,occur error "ORA-02035: illegal bundled operation combination". Program source:
...connect to server8.0.5
     for(i=0;i<20;i++)
     {
          EXEC SQL EXECUTE
               BEGIN
                    ABC(:s,:s,:j:i_ind);
               END;
          END-EXEC;
          printf("execute succ[%d]i[%s]\n",i,s);          
     }
...disconnect from server
errexit:
     sql_error();
     return 1;
void sql_error()
{
...
}
Error String:
connect succ
execute succ[0]i[2 ]
error:ORA-02035: illegal bundled operation combination
I rewrite program by using OCI,but error idem.
when I connect to Oracle8.1.6,Error is not appear.
procedure ABC source:
create or replace procedure abc(a varchar2,b varchar2,c out number) is
begin
c:=2;
return;
end abc;
if "c" is not a out parameter,error do not appear.

Pro*C to Java Question

Is there a Java equivalent for the following Pro*C code:
EXEC SQL BEGIN DECLARE SECTION;
CHAR oracleid = '/';
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT :oracleid;
I.e., if the operating system username is RHILL and OPS$RHILL is a valid Oracle username, is it possible in Java to fetch the username from the OS and login to Oracle as it currently is in Pro*C?
null 
Try this:
public class me {
public static void main(String argv[]) {
System.out.println ("name " + System.getProperty("user.name"));
}
}
This should print the user name: Then you can pass this to getConnection to connect to the database.

Categories

Resources