DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
JackStrong
Participant



Joined: 09 Apr 2018
Posts: 3

Points: 31

Post Posted: Mon Apr 09, 2018 3:48 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi All.

At the beginning I would like to mention that I am new here Smile .
I was looking for answer but I couldn't find nothing, that's why I'm writting here.

I execute stored procedure from ODBC Connector stage using Before SQL Statements:
execute delete_data @vDays=#P_NumberOfDays#
This procedure should delete a lot of data (~200mln records) from a lot of tables (~500 tables). In the biggest table is about 100 mln records to delete.

Job is simple:
ODBC Connector (Select statement: select 1 as row; Before SQL Statements: execute delete_data @vDays=#P_NumberOfDays#) ---> Copy Stage

After executing above command job is running but finally there are no correct results. When I checked sessions on database (exec sp_who2), it looks like procedure is done (I can see delete commands) but after this appear command TM REQUEST and everything is rollbacked (status = ROLLBACK).
Finally it looks like nothing is done (records are not deleted). Additionally there are no any errors or warnings in Run Director logs. I am sure that procedure is executed because during execution database needs a lot of disk space for transaction logs.

When I execute this procedure using Microsoft SQL Server Management Studio, everything is ok (records are deleted).
But when I execute by DataStage job (ODBC Connector stage) there is no right results.

When I changed the stage from Copy to Peek, results are the same (records are no deleted).


Could you help me or give me a clue why I can't delete data using stored procedure in ODBC stage? Maybe i should use different stage?


Thanks in advance,
Jack
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54395
Location: Sydney, Australia
Points: 295036

Post Posted: Mon Apr 09, 2018 4:08 am Reply with quote    Back to top    

Does the delete_data stored procedure include a COMMIT (or GO) statement?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
JackStrong
Participant



Joined: 09 Apr 2018
Posts: 3

Points: 31

Post Posted: Mon Apr 09, 2018 4:17 am Reply with quote    Back to top    

No, there is no commit or GO statement between BEGIN and END.
Do you suggest anything? Smile
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Mon Apr 09, 2018 5:03 am Reply with quote    Back to top    

As Ray has hinted already with his question, you will need to add your commit or "GO". There is an "autocommit mode" that you can specify in the ODBC but I don't think that it applied to the before or ...

_________________

Image
Rate this response:  
Not yet rated
JackStrong
Participant



Joined: 09 Apr 2018
Posts: 3

Points: 31

Post Posted: Mon Apr 09, 2018 6:37 am Reply with quote    Back to top    

Thanks a lot guys! Now is ok.
It was stupied from my site to ask about such easy thing.
I don't know why I didn't think about it before. Maybe because using Server MS SQL Server Management Studio everything works.

Thanks again! Smile
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours