Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Custom SQL in iway DataMigrator
Go
New
Search
Notify
Tools
Reply
  
Custom SQL in iway DataMigrator
 Login/Join
 
Member
posted
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


WebFOCUS 7.7, iWay Data Migrator, Windows
Excel, PDF, HTML
 
Posts: 12 | Registered: December 27, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Why don't you just use a normal data flow to perform the delete?


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1100 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 386 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 7.7, iWay Data Migrator, Windows
Excel, PDF, HTML
 
Posts: 12 | Registered: December 27, 2011Reply With QuoteReport This Post
Guru
posted Hide Post
Please change SQ DELETE to SQL DB2 DELETE so that you are using Direct Pass Through.
 
Posts: 386 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 7.7, iWay Data Migrator, Windows
Excel, PDF, HTML
 
Posts: 12 | Registered: December 27, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Custom SQL in iway DataMigrator

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.