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

so i went thought like this to solve my problem

sequence
Start transaction
Adapter serv-1
commit transaction
Start transaction
Adapter serv-2
commit transaction Start transaction
Adapter serv-3
commit transaction
Rolleback(catch blk)

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.

Kitt,

When you are Inserting data to Oracle using LOCAL_TRANSACTION then code your (Try/Catch)processing sequence like this way

StartTransaction(transactionName=tmp1)
TrySequence (SUCCESS)
----Sequence(FAILURE)Containls whole logic for inserting
perform Mapping
Loop records
Insert AdapterService(ROWS in Oracle)
CommitTransaction(transactionName=tmp1)
-----Sequence(DONE)
getLastError(grab Error)
RollbackTransaction(transactionName=tmp1)

So create one transaction object and use the same further downstream for commit and rollback.

And i have some sugesstions in your Connection Pooling settings make this change if it is feasible for you.

Minimum Pool Size 0
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 10000
Expire Timeout (msec) 10000
Startup Retry Count 0
Startup Backoff Timeout (sec) 10

HTH,
RMG

THANKS RMG
I really appreciate your response,

i did just i guess its correct ,

–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

Kitt,

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.

Please do this change and test it.

HTH,
RMG

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.

HTH,
RMG.

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.

Thanks,

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.

Its all depends on your requirments.

HTH,
RMG

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

Kitt,

Sorry i have tried my best to elaborate the solution,Anyways if your requirement works ok with NO_TRANSACTION,proceed further as you said.

Regards,
RMG

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 .

Kitt,

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.

Just my thoughts,

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.

Regards,

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?

Thanks,
Rajesh

Rajesh,

we can set any value in the startTransactionOutput/transactionName,but make sure the same value will be driven to to commitTransaction aswell RollbackTrasaction.

We have set that value manually in the transactionName object as a initial flow step.

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?

Rajesh,

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)

Hope we both are in same page.

HTH,
RMG.

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.

Regards

RMG - I was not aware that startTransactionInput\transactionName was copied to startTransactionOutput\transactionName. I must have missed that in the documentation. Thanks.

I’m working on webMethods 4.1.
I did an enhancement in an existing working integration . Addition of a field corresponding to target database.While testing I’m getting the following error through Adapter:notify event in the Document Tracker for an insert event (GBL_EOM_ORDER_COPY::eom::oracle::order_Header_I) -

#1-73 Received transaction document of type
“GBL_EOM_ORDER_COPY::eom::oracle::order_Header_I” outside of an active
transaction.

unicode_string adapterType = “oracle”;
unicode_string errorCategory = “Adapter”

The strange thing is that when i run the target OracleDb adapter in Debug mode, i dun get any error, but when i run the dapter in normal mode i again get the same error.

Please help me out.

hi,

I’m working on webMethods 4.1. I’m doing an enhancement in which a 40 digit STRING comes from the source and it needs to be converted into number on the target side . Is it psoosible to do the conversion anyhow.

thanks

Hi Ratnesh,

I guess u wrote it in a wrong thread. Please create a new one. Anyway write a java service for STRING to Number conversion and in a MAP step do a Transformer.

Thanks
Bibek

Hi Ratnesh,
There is a special sample package called “WmTransformationServices” available on advantage website under ‘samples’. This package has a lot of built-in services for data conversion between a lot of data types.
I hope this will help you. Also download the PSUtilies package which has a lot of useful services.

Regards,
Ramnish.

Hi All,

The same issue is happening with my code also.

I hardcoded the “transactionName” in the startTransactionInput and later I just mapped the startTransactionOutput–>transactionName to commitTransactionInput and rollBackTransactionInput.

While running the service, I get the error: “Unable to commit transaction. A transaction name must be specified”.
When I checked the pipeline, I could not find the startTransactionOutput though I did not drop that anywhere in the service.

After that I hardcoded the transaction name in all the 3 services, and it was working fine.

Could someone tell me why the startTransactionOutput was not showing up in the pipeline even when I did not drop that.

Regards
Jyoti

OK…Where did you put the startTransaction step in the very beginning of the flow before Try/catch sequence or inside the block?

HTH,
RMG

I assume the issue with the place holder of startTransaction. Make sure it is outside your main sequence and also I do not prefer hard coding the transactionName just map it at the document level and it should work like a charm.

Yes, its outside the MAIN sequence.
The problem that I observed while debugging, is that the startTransactionOutput is getting dropped when the control comes to MAIN sequence.
I tried to map the transactionName value to a temporary variable and use it in the commit and rollback, even that is getting dropped.
Tried to fetch the transaction name from config file, that is also getting dropped.
Hardcoding is only working perfectly.
Please comment.

Regards
Jyoti

Hi Jyoti,

please note/remember that explicit transactions cannot be debugged in Developer/Designer as this will break the transaction context.

Regards,
Holger

Hi Holger,

Yes, I am aware of that.
Here, by debugging I meant, while running the service in one go and checking the pipeline values, I could not find the transactionName variable.
Sorry for the confusion.

Regards
Jyoti

Do you mind sharing your code snippet package? to have a look at?

Looks strange to me, can you map it to a temporay variable and print it out using debugLog BIS or use tracePipeline service to see what is the result.