Hi All,

Before loading the target I need to delete records based on date. So I have use Stored Procedure to run the delete, but I am receiving the following error message.

02/28/2012 11:10:47 SERVER1 (ICM18122) Request - processflows/pf_fdp_stg (Owner: user1) submitted.
02/28/2012 11:10:47 SERVER1 (ICM18015) DEP_0: procedure sp_deletefdp started.
02/28/2012 11:10:47 SERVER1 (FOC1400) SQLCODE IS -5016 (HEX: FFFFEC68)
02/28/2012 11:10:47 SERVER1 (FOC1414) EXECUTE IMMEDIATE ERROR.
02/28/2012 11:10:47 SERVER1 (ICM18039) DEP_0 sp_deletefdp Return Code = 0

I am using DB2, AS400 and the stored procedure as follows

SQL
DELETE FROM DB1.FDP_STG
WHERE DATEFD >= ((40927) - DAYS(CAST('1899-12-31' AS DATE)));

END

-RUN



SQL DB2

COMMIT WORK;

END

-RUN

Why don't you just use a normal data flow to perform the delete?

The SQLCODE IS -5016 comes from IBM DB2 and means:

The qualified object name is inconsistent with the naming option.

I note that you are using SQL (automatic pass through) for the DELETE so DB1.FDP_STG refers to the app dir DB1 and synonym FDB_STG. Possibly you wanted to use SQL DB2 (direct pass through)?

Also the expression in the parenthesis returns a number, so DATEFD must be a number not a DATE.

Thanks for your reply guys.

Clif: The DB1 refers to the database name and FDB_STG is the table name.

When I run the delete query in the database its working fine and when I run the following stored procedure directly it works, but when I pull the stored procedure to process flow it is not working.

SQL
DELETE FROM DB1.FDP_STG
WHERE DATEFD >= ((40927) - DAYS(CAST('1899-12-31' AS DATE)));

Please let me know what keywords am I missing in the stored procedure.

Dhagen: Am not sure, but I know that normal data flow will delete or truncate all the records based on the key column, but in this scenario I have to delete based on date.

Please change SQ DELETE to SQL DB2 DELETE so that you are using Direct Pass Through.

quote:

Originally posted by Clif:
Please change SQ DELETE to SQL DB2 DELETE so that you are using Direct Pass Through.

I tried using SQL DB2 DELETE, but still it throws the same error message.