Hi..I have a flow and on the success and failure of the flow i log flow details like start_date,End_date,status,rows_updated,rows rejected etc.
On success or failure i run a stored procedure to insert values into my own log table.
I used &ACCEPTS variable ,but i am getting 0 as value.

This is my procedure..How can i get Rows_Updated?

-SET &&STATUS='SUCCESS';
-SET &&ROWS_UPDATED = &ACCEPTS;
ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS
INSERT INTO [dbname].[dbo].[audit_table] ([Flow_Name],[Start_Time],[End_Time],[Status],[Records_Inserted])
VALUES ('&&DATA_FLOW_NAME','&&DATA_FLOW_START_TIME','&&DATA_FLOW_END_TIME','SUCCESS','&&ROWS_UPDATED');
END
-RUN

This message has been edited. Last edited by: FP Mod Chuck,

Obviously this is not the whole flow. You must have a MODIFY before this. &ACCEPTS would have to be saved immediately after a MODIFY completes. If you execute any command that is loaded into FOCSTACK, &ACCEPTS will be reset to 0. Same goes for any of these variable (&LINES, &DELTD, &INPUT, ect.). For example:

MODIFY FILE XYZ
FIXFORM FROM HOLD
MATCH KEYS
ON NOMATCH INCLUDE
ON MATCH UPDATE FIELDA FIELDB
DATA ON HOLD
END
-*
FILEDEF HOLD CLEAR
-RUN
-SET &INSERTED=&INPUT;
-SET &DELETES=&DELTD;

&INPUT and &DELTD will be 0 because of the FILEDEF command. To get counts, the FILEDEF has to be moved to after the -SET commands.

Hi..
I don't have a modify before this procedure.
I have a simple data flow before this procedure and the output of this dataflow branches to success and failure.
I that success procedure i want to use get number of rows updated?
Is there any way i can get no of rows accepted other than &ACCEPTS.?

Assuming you are using load type such as Insert/Update and your stored procedure is running in the same agent as the flow as I explained in my answer to your question in REG:Rows Updated and Rejected the total number of rows accepted into the target table is from &ACCEPTS. If it's zero (and rows were indeed accepted) then please check that those conditions are met and that you didn't issue a command that resets the statistical variables.

The number of rows UPDATED can be found in the variable &CHNGD.

I am a beginner in Iway DM that is why i am not understanding your answers completely.
I checked for FILEDEF and i couldn't find FILEDEF in my process flow.

I found the below lines of codes..

-*DM_JOB_TYPE=13
-*DM_USERID=admin
-*DM_CLIENT_RELEASE_GEN=R728200D_1060
-*DM_SERVER_RELEASE_GEN=M728200D_1102

-***************************************************

-:START_PRC
SET PANEL=9999
SET MORE=OFF
SET 2PARTNAME=ON
-RUN

-*[Variables to Control Request]
-SET &&CM__TARGET = 'fact_table1';
-SET &&CM__AUTHOR = 'admin';
-SET &&CM__REQUEST = '&FOCFEXNAME.EVAL';
-SET &&CM__RETURN = 0;
-SET &&CM__FOCCPU = &FOCCPU.EVAL;
-DEFAULT &DBMSERROR = 10000000
-DEFAULT &STARTAT = 0
-DEFAULT &STOPAT = 1000000000

-TYPE (ICM18122) Request - &FOCFEXNAME (Owner: admin) submitted.
-GOTO EP_MAIN;

-EP_MAIN
-TYPE (ICM18742) fact_table1 type MS SQL Server JDBC Existing target

SET CASESTAT=EXTENDED

-TYPE (ICM18743) Starting Load

MODIFY FILE fact_table1
FIXFORM FROM MED_DETAILS ALIAS PROPAGATE
GOTO MATCHIT1
CASE MATCHIT1
COMPUTE
PATIENT_STATE/A50 MISSING ON=Patient_State;
LAST_CREATED/YYMD MISSING ON=DT_CURRENT_DATE();
LAST_UPDATED/YYMD MISSING ON=DT_CURRENT_DATETIME(MILLISECOND);
FLOW_NAME/A50V MISSING ON='&&CM__REQUEST';
MATCH ID
ON MATCH INCLUDE
ON NOMATCH INCLUDE
GOTO TOP
ENDCASE

CASE AT START
START &STARTAT
STOP &STOPAT
STOP DBMSERRORS &DBMSERROR

LOG DBMSERR MSG OFF
LOG DUPL MSG OFF
LOG INVALID MSG OFF
LOG NOMATCH MSG OFF
LOG FORMAT MSG OFF
LOG ACCEPT MSG OFF
LOG TRANS MSG OFF
CHECK 1000
ENDCASE
DATA ON MED_DETAILS
END
-RUN

-TYPE (ICM18744) Ending Load
-SET &&CM__RETURN = IF &TRANS EQ 0 THEN 18708 ELSE &FOCERRNUM;
-SET &&CM__RETURN = IF (&&CM__RETURN EQ 1416) AND (&DBMSERR LT &DBMSERROR)
- THEN 0 ELSE &&CM__RETURN;

-:ENDJOB
-TYPE (ICM18040) Return Code = &&CM__RETURN

SET CASESTAT=OFF

SET EMGSRV=ON
SQL SQLMSS
COMMIT WORK;
END
-RUN

-TYPE (ICM18076) Request: &FOCFEXNAME - finished processing

-SET &&CM__FOCCPU = &FOCCPU.EVAL - &&CM__FOCCPU;
-TYPE (ICM18007) CPU Time : &&CM__FOCCPU

-*[Main Condition]
-*[Main End]

-*[Dependence]
-:ENDDEP
SET PANEL=0
SET MORE=ON
SET 2PARTNAME=OFF
-RUN


Data flow code shows Modify like you said.

I don't have any FILEDEF commands in the MODIFY.
I added -SET command in the previously posted MODIFY and used that -SET variable in the success procedure.
But the log says value for variable &ROWS_UPDATED is missing.

   
-SET &ROWS_UPDATED = &ACCEPTS;

I added the above command inside the modify.
and used &ROWS_UPDATED in the below procedure.

 
-SET &&STATUS='SUCCESS';
ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS 
INSERT INTO [dbname].[dbo].[audit_table] ([Flow_Name],[Start_Time],[End_Time],[Status],[Records_Inserted])
VALUES ('&&DATA_FLOW_NAME','&&DATA_FLOW_START_TIME','&&DATA_FLOW_END_TIME','SUCCESS','&ROWS_UPDATED');
END
-RUN
This  

@Clif.My load type is Insert/Update.
I haven't explicitly issued any command to reset statistical variables.
Rows are actually getting accepted in the target table as well.
Can you tell what are the conditions to be met?

From the data flow you uploaded I can see that the load type is indeed insert/update. That's good. I can also see (from ON NOMATCH INCLUDE) that you specified "If the record exists" then "Include the record" which means no records will be updated so you'll never see a value of &CHNGD other than zero.

I can also see that while your server is 82M gen 1102 the client is 82 gen 1060. It's best if you keep client and server gens in sync. Please update the client.

While you should not make any changes to the generated data flow you do need to ensure that the data flow and the stored procedure you wrote are run in the same agent. Please review the image I uploaded to your earlier question. If you can't confirm that then upload the Process Flow here.

srajeevan, the FILEDEF was just an example of a command that get loaded into the FOCSTACK. Every line in your program that does not begin with a - is loaded into the FOCSTACK. Do you see where you have SET CASESTAT=OFF? As soon as that command is executed, the & variable set by TABLE or MODIFY are reset to 0.

Do you see the -RUN after the MODIFY? You need to your "-SET &ROWS_UPDATED = &ACCEPTS;" right after the -RUN. Here are some of the amper variables that will be set by the MODIFY:
&ACCEPTS - # of transactions
&CHNGD - rows updated
&DELTD - rows deleted
&INPUT - rows inserted
&REJECTS - rows rejected by ON MATCH REJECT or ON NOMATCH REJECT

there are others as well and there are those set by TABLE and TABLEF. Search for the MODIFY REFERENCE manual on IBI's website. All of the & variable set by MODIFY are documented there.