How to commit after procedure run

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

How to commit after procedure run

Post by prasson_ibm »

Hi,

I have a requirement where i have been given a stored procedure (Transform) and i have been told that i need to commit the procedure once it finishes successfully.

Is there any way in Stored proc stage or any other way i can achieve this?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I guess you could encapsulate the SP in a script that included both the exec/call and the commit.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Problem is you've said you've set the SP stage to 'transform' which means you are calling it for every row through the job. If that's the case, when should the commit be executed? Row by row? Once at the end of the job?
-craig

"You can never have too many knives" -- Logan Nine Fingers
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

Post by prasson_ibm »

At end of the job.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

At the end of the job when the connections are closed any open transaction will be committed automatically.
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

Post by prasson_ibm »

Instead of calling stored procedure i am running PL/SQL block in Oracle connector stage and comming at the end.
Below is the code,how can i abort a job in case RetVal <>0?

Code: Select all

DECLARE 
  RetVal NUMBER;
  P_PAYLOAD str_payloads.payload%type;
  P_PAYLOADHASH VARCHAR2(200);
  P_TIMESTAMP VARCHAR2(32767);
  P_PAYLOADSIZE NUMBER;
  P_ERRORMSG VARCHAR2(32767);

BEGIN 
  P_PAYLOAD := EMPTY_BLOB();
  P_PAYLOADHASH := NULL;
  P_TIMESTAMP := TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss');
  P_PAYLOADSIZE := NULL;
  P_ERRORMSG := NULL;

  RetVal := AITBROKER.STR_TRANSMISSION.USF_LOG_PAYLOAD_V2 ( '#PYLDUUID#', P_PAYLOAD, P_PAYLOADHASH, P_TIMESTAMP, P_PAYLOADSIZE, P_ERRORMSG );
  IF(RetVal <> 0) THEN
    DBMS_OUTPUT.PUT_LINE('P_ERRORMSG: ' || P_ERRORMSG);
    ROLLBACK;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Success!');
    COMMIT; 
  END IF;
END;
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Raise an error.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply