Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Custom SQL in iway DataMigrator
 Login/Join
 
Ramanathan
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, 2011Report This Post
dhagen
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: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Clif
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.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Ramanathan
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, 2011Report This Post
Clif
Guru
posted Hide Post
Please change SQ DELETE to SQL DB2 DELETE so that you are using Direct Pass Through.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Ramanathan
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, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders