Originally posted by: SystemAdmin

Hi Experts,

I am facing a strange problem in one of my maps.

The scenario is like :

A main map calling a run map. Run map takes the records from a text file and loading to a database table.

After run map execution, the main map calls a procedure which takes data from the table and inserts to another table and delete the data from source table.

I have created the main map and run map. At the first time , the map behaved very strangely... the data got loaded to the first table, but data didn't got loaded

to the target table...

When the map was run second time... procedure got executed and data loaded to target table.

From this, what i understood is the data got loaded to the first table, but was not visible to the procedure in the first case... correct me, if i m wrong.

As far as my understanding, if the main opens a database session, the run map will share the same connection.

I have created the run map again with the same settings and used in main map, then the map behaves as expected.

i.e, on the first run itself, the data got loaded to 1st table, procedure takes the data and loaded to target table.

I have compared the 2 run maps .mms files, but no difference at all.

But the 2 maps behaving in 2 different ways.

The only difference i could see is the .dbl file created for the procedure execution.

Please find below the log created , when i executed with the run map, which functions as expected:

<3388-3492>: Validating the adapter command...
<3388-3492>: Database type is Oracle
<3388-196>: Participating in active transaction.
<3388-196>: Interface library version 8.0(81)
<3388-196>: Map: C:\Source1\Mapsrc\GBS_WRP_HUB_AU_BITJ01.mmc, Timestamp: 11/26/09 19:04:27.
<3388-196>: Data being retrieved for DBLOOKUP function.
<3388-196>: Database adapter: Oracle9i Version 8.0(81)
<3388-196>: Starting a database unload...
<3388-196>: Query : call SPCMNL.hsbc_mediation('BITJ01','GBS.AUHUB.DSTX.BITJ0C0801.INPUT')
<3388-196>: DBLOOKUP Function, Transaction scope: Map
<3388-196>: TRACE command specified, append mode, file: C:\Source1\Mapsrc\..\test\log\HSBC_MEDIATION_PROC_AU_BITJ01.dbl
<3388-196>: The columns are of the following types:
<3388-196>: Column 1 (V_FILE_ID) type is VARCHAR(4000).
<3388-196>: Column 2 (V_FILE_NM) type is VARCHAR(4000).
<3388-196>: Number of buffers in fetch array = 8
<3388-196>: The procedure statement to be executed is:
<3388-196>: begin SPCMNL.hsbc_mediation(:a00,:a01); end;
<3388-196>: Writing results to a buffer.
<3388-196>: Retrieved 1 records (0 bytes).
<3388-196>: Returned status: (0) Success
Please find below the log created, with the map which functions wrongly.

<3388-3492>: Validating the adapter command...
<3388-3492>: Database type is Oracle
<3388-1696>: Comparing connection: Rollback option differs.
<3388-964>: Participating in active transaction.
<3388-964>: Interface library version 8.0(81)
<3388-964>: Map: C:\Source1\Mapsrc\GBS_WRP_HUB_AU_BITJ01.mmc, Timestamp: 11/26/09 19:02:27.
<3388-964>: Data being retrieved for DBLOOKUP function.
<3388-964>: Database adapter: Oracle9i Version 8.0(81)
<3388-964>: Starting a database unload...
<3388-964>: Query : call SPCMNL.hsbc_mediation('BITJ01','GBS.AUHUB.DSTX.BITJ0C0801.INPUT')
<3388-964>: DBLOOKUP Function, Transaction scope: Map
<3388-964>: TRACE command specified, append mode, file: C:\Source1\Mapsrc\..\test\log\HSBC_MEDIATION_PROC_AU_BITJ01.dbl
<3388-964>: The columns are of the following types:
<3388-964>: Column 1 (V_FILE_ID) type is VARCHAR(4000).
<3388-964>: Column 2 (V_FILE_NM) type is VARCHAR(4000).
<3388-964>: Number of buffers in fetch array = 8
<3388-964>: The procedure statement to be executed is:
<3388-964>: begin SPCMNL.hsbc_mediation(:a00,:a01); end;
<3388-964>: Writing results to a buffer.
<3388-964>: Retrieved 1 records (0 bytes).
<3388-964>: Returned status: (0) Success

The only difference i got is the one mentioned in bold.

I could find the same difference in the logs created for the data insertion part also.

Can any one of U tell me whats the reason for this.

Please find attached the maps for which I m facing the problem.

Thanks,
Deepthi
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: Subh

I dont have access to design studio for now and I could not check your maps.
But I suspect the transaction level set in the card.
Did you try commit by card option. (-CCARD).

Thanks,
Subha
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: LaurentB

If rollback options are not the same, then the connection cannot be reused. I did not open the zip, but it would be worth investigating this area (card settings).
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Hi Laurent,

Thanks a lot for the suggestion.

I have done a bit more investigation on this area.

During the testing, I had changed the output card setting of Run map to

Adapter - File
OnSuccess - !create
OnFailure - commit

After that changed the Adapter to Sink.

And the OnSuccess , OnFailure settings were not displayed in the mms. Thats the reason Y i didn't find any difference in the Map source comparison.

As far as my understanding, these settings wont have any effect if the Adapter is Sink.

But in the scenario which i explained, these settings caused the problem.

In the main map card, File_Receipt , a run map is getting called which uses a database connection... card settings default. (OnSuccess - Create, OnFailure - RollBack)

During execution, the map first established a connection to database here,

In the next card, thats the transform card, calling the run map (GBS_HUB_GOLD_BITJ01), its Output card setting (Adapter -Sink as per MMS).

But it was taking the Onsuccess, OnFailure settings which I used while used the adapter as File, even though its not visible in the map source file.

Since the card settings here was (Adapter -Sink , OnSuccess-!Create, OnFailure - Commit), map established another session here.

Because of these separate connections, the procedure was not able to access the data.

Thanks,
Deepthi
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender