hi , i am working on jdbc adapter on WM6.
I am using 2 databases for my integration
1.Select from SQL db
2.Select from second database(Oracle)
3.do mapping and then Insert into second database(Oracle)
my connection properties for Oracle is as follows :
Enable Connection Pooling true
Minimum Pool Size 1
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 1000
Expire Timeout (msec) 1000
Startup Retry Count 0
Startup Backoff Timeout (sec) 10
for SQL db i am using NO_TRANSACTION (buz of only Select st)in my configuration
AND for oracle i am using LOCAL_TRANSACTION (select and insert) .
When i run it i can successfully insert but when i run whole program
i cant run its giving me error like it cannot Commit the transaction
Its working but its taking long time to process it like 1200 sec for one transaction that insert around 100 rows.
is there any other way to do make this more efficient.If i try to do some other way its give me errors like “unable to close connection” and something like that
I will appreciate ,
thanks.
–sequence(success)
Select from SQL(NO_TRANSACTION)
map
–sequence(sub srvc success)
StartTransaction(transactionName=tmp1)
–sequence( sub srv try)
select Adapter srvc (LOCAL_TRANSACTION)
map
loop
loop
Insert Adapter Srvc batch INSERT st
CommitTransaction(transactionName=tmp1)
–seq(sub srv catch)
rollback transaction
get last error
–sequence(catch)
get last error
But i am getting following error when I run this service,and i changed the pool setting as u said in your last post.
server log:--------
Exception –> com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is active .
ERROR log:------------
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is active . Stack trace data … f1933b60066211d99a86c62bfd00b2c1 NULL f1933b60066211d99a86c62bfd00b2c1
2004-09-14 11:30:02 XXXXXXXXXXXXXXXXX[ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is active . Stack trace data … f1933b60066211d99a86c62bfd00b2c1 NULL f1933b60066211d99a86c62bfd00b2c1
2004-09-14 11:28:07
Keep the StartTransaction(transactionName=tmp1) outside the Success Sequence(before first sequence as i mentioned above) and also make sure the mapping for CommitTransactiontransactionName=tmp1),looks transaction object name is missing in the pipeline and breaking the commit that is creating the error.
What you do is map the same startTransactionOutput/transactionName to commitTransactionInput/transactionName and similarly for rollback too.
So that StratTransaction transaction object will have access to commit as well rollback.
Yeah,the process is working fine with around 1/3 of the time i.e around 60sec for each transaction and the data is successfully inserted in database,the thing is I am getting error in error log.
I mapped startTransactionOutput/transactionName to commitTransactionInput/transactionName and similarly for rollback, But till I am unable to remove the following error,
com.wm.pkg.art.error.DetailedServiceException: [ART.114.304] Adapter Runtime (Transaction): Unable to rollback transaction. A transaction name must be specified.
[ART.114.304] Adapter Runtime (Transaction): Unable to rollback transaction. A transaction name must be specified.
com.wm.pkg.art.error.DetailedServiceException: [ART.114.301] Adapter Runtime (Transaction): Unable to commit transaction. A transaction name must be specified.
[ART.114.301] Adapter Runtime (Transaction): Unable to commit transaction. A transaction name must be specified.
I enabled my service to monitor the transaction in WM Monitor
I can see my service is successfully completed with out any errors.
thanks.
I am doing something wrong ?
Kitt,
If you still see that error while commit trasaction then how the data gets inserted in the DB?
Also please make sure the startTrasaction/tranactionName object is in the pipeline and further down it will used by commit/rollback.Since i have seen this errror before and resolved it with mapping change.
So keep the StartTransaction as the first step in the service outside all the existing sequences.
hi RMG,
one quick question ,
can we use NO_TRANSACTION for Select, Insert adapter service for Oracle database,
so i need not use the start transaction & commit transaction in my flow service.
yes we can use NO_TRANSACTION for Insert/Update too…but only constraint is you dont have choice of rollback the trasaction if any kind of business validation,data,transient error occurs.
Thanks RMG
I am planning to go with No_transaction.
buz I changed the mapping steps and mapping correctly from
start tracsaction/temp1 to commit transaction/temp1
I am still getting that error,
I appreciate your help.
thanks
hi,
i was just going through the JDBC Documentation(pdf)
for NO_TRANSACTION according to to that we must use
LOCAL_TRANSACTION for Batch Insert for Oracle db ,
but my service is working fine for Batch Insert for NO_TRANSACTION.
Do I need to change the service to LOCAL_TRANSACTION .
NO_Transaction will work for BatchInsert as well normal Inserts too.
Only back drop as i told befor you will not have choice to rollback the transaction if any error occurs.So think about it.
I prefer LOCAL_TRANSACTION for realtime,batch inserts since customers always prefer commit/rollback thing.
Thanks RMG,
Finally I am able to use LOCAL_TRANSACTION .
I did change my mapping steps ,the most important thing was
I didn’t map from startTransactionOutput/transactionName to commitTransactionInput/transactionName Instead I hard coded the value in startTransactionOutput/transactionName (say temp1) And again hard coded the same value to commitTransactionInput/transactionName (temp1).
I don’t know this work for me
Thank ,
In general you dont need to hardcode in the commitTransaction and have to map it from the startTransactionOutput/transactionName.This works for me and similarly for rollbackTransaction too.Which will be available in the pipeline.
Anyways you resolved with hardcoding and satisfied.
KITT - In your second post, I think the “Unable to commit transaction. Transaction state:Transaction is active” is caused by running the flow in debug mode (trace or step). I was doing the same thing. When using explicit transactionality, the service has to be “run”, not traced, or stepped into. You will have to restart your IS though to clear out the active transaction.
RMG - How did you set the initial value of startTransactionOutput/transactionName?
we can set any value in the startTransactionOutput/transactionName,but make sure the same value will be driven to to commitTransaction aswell RollbackTrasaction.
RMG - In the “Service In” section of the pub.art.transaction:startTransaction service, I set the value of startTransactionInput/transactionName. I then pass that transactionName to the subsequent commit and rollback service and this works fine.
But I am not able to set a value for startTransactionOutput/transactionName since it’s in the “Service Out” part of the startTransaction service. How did you do this?
when you set the value to the startTransactionInput/transactionName then same value will be carried out by the startTransactionOutput/transactionName.
Then why you want to set a new value?If you want so then add a map step after startTransaction and set the value in the startTransactionOutput/transactionName(this will overwrite the original value then you can’t use this for commit/rollback in the downstream)
HI Rajes,
what i did before is i just hard code all the values for startTransactionInput/transactionName and same value for commit and rollback.
we cant set the value for startTransactionOutput/transactionName.
but there will be same value present .
RMG is trying to tell the same thing…
and regarding the Active Transaction, there is no need to start the Integration server buz there will be no transaction takes place.