One way I can think of executing this is:
In the first procedure 'which will execute the insert statement in source', change the Target transaction to 'Transaction 0' and target commit to 'No Commit'. Execute the package again and see if the data gets populated.
I think it will not work since the mapping will not execute the insert statement in the same transaction.
In that case, is it possible for you to write an Insert command to replace the Mapping which you have created?
If yes, then in the same procedure which inserts the statement in source, add another command for inserted the data from this source to target. Choose the proper technology and select the Target transaction to 'Transaction 0' and target commit to 'Commit'.
Your procedure will look something like this for the first and second command:
Now, the package will only have the procedure. Since both the commands will be executed in the same transaction, the data should get populated in the target table.
Thanks for the reply
Actually my package contains 1 procedure which executes insert statement at source and 1 interface.
As you said to make it in one session i changed LKM and added a new step at beginning which executes insert statement in the inteface itself.
Also changed the Source transaction to 'Transaction 0' and Source commit to 'Commit' as the insert statement is executing at source.
Now i executed the interface but its the same result as before.
any idea if interface creates different sessions during execution..i guess no.
I had understood that the package had a procedure and an interface.
You need to chose 'No Commit' for the first command which inserts into (initializes) the source table. If commit is done, then the transaction is already completed.
The commit should happen after the insert into the target table.
Please change the source commit to 'No commit' and test again. If it doesn't work then paste the snapshots of the changes.
Since the command is on source, please change the Source Transaction from Autocommit to Transaction 0. Also change the Source Commit to 'No Commit.
Also, check that the command for the insert into the I$ table (or the target table whichever happens first) is in the same transaction.
If this doesn't work, then check the command in IKM which inserts data. It will probably be the insert into I$ table. Add this insert into source statement in the command on source in the same command, and keep transaction as Transaction 0 and commit as 'No Commit'.
Whenever you make a change in a KM, look at other commands and follow the same standard.
I think the best thing to do will be to use the original procedure which you had created, and in that keep transaction as Transaction 0 and commit as 'No Commit'. And execute the Interface after removing the new step which has been added in the LKM. In my project, when ever we have such requirement, we have such commands in two transactions (0 and 1), with 'No commit', followed by the Interfaces.
Till now I can understand that the commit is being done to close the transaction before the data is read.
I think you are right.
I think the best thing to do will be to use the original procedure which you had created.
In procedure i have created 2 steps.
Step1: Executes insert statement.--Commit as you suggested( Transaction 0 'No Commit' in both source and target)
Step2: Executes a count(*) statement.
But Step2 does not return any record which tells that the session is not created properly or as you said transaction is closed before executing Step2
any suggestion how to debug it !!
Not sure why this isn't working.
Are you inserting the result of the count(*) statement into a table? Can you try doing this to see if the result is still 0.
Also, can you do both these steps in the same command. "insert of the count(*)" in the 'command on target' and the "insert for initializing" in the 'command on source'.
Sorry for replying so late. I have inserted count(*) and found the following at IKM
Scenario 1: User1.View1(created at source by LKM)--Getting some count
Scenario 2: User2(Traget DWH).Scenario(scenario of View1)--Count is 0
this is because insert access is provided to User1 at source. And during select at IKM ODI is generating a select query as select <column name> User2.Scenario
Any idea how to provide insert access to User2 which is in a different environment.