MS SQL Server excecution stored procedure (why rollback?)

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
JackStrong
Participant
Posts: 8
Joined: Mon Apr 09, 2018 2:47 am

MS SQL Server excecution stored procedure (why rollback?)

Post by JackStrong »

Hi All.

At the beginning I would like to mention that I am new here :) .
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
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Does the delete_data stored procedure include a COMMIT (or GO) statement?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
JackStrong
Participant
Posts: 8
Joined: Mon Apr 09, 2018 2:47 am

Post by JackStrong »

No, there is no commit or GO statement between BEGIN and END.
Do you suggest anything? :)
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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 after SQL.
JackStrong
Participant
Posts: 8
Joined: Mon Apr 09, 2018 2:47 am

Post by JackStrong »

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! :)
Post Reply