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    Datamigrator newbie questions

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Datamigrator newbie questions
 Login/Join
 
ferran
Member
posted
Hi,

As I have mentioned on subject, I have just arrived into de iWay Datamigrator world. I need to migrate some SQL Server Stored Procedures into Datamigrator. Theses Stored Procedures are, in some cases, quite large and some of theme have a lot of complexity and, as I told, I'm just a Datamigrator beginner. I would read your recomendations about this situation based upon your experience, considering that not much information available at the business level.

I have more experience in WebFOCUS, as well as batch load processes, so I know what is a MODIFY FILE. In Datamigrator there are "Stored Procedures", focus processes. I thought that I could write the processes to perform all the tasks needed, until a temporaly file (HOLD FILE), and then bulk this into the final table.

But I'm not sure if this is possible, as well as if this is a good solution for this situation.

Thank's in advice,

Ferran.


WF 7.7.03,Win XP Pro.
HTML, PDF, EXCEL, AHTML, FLEX, APDF.
SQL Server, MySQL, DB2, FOCUS.
 
Posts: 19 | Registered: February 04, 2011Report This Post
Clif
Guru
posted Hide Post
There are several different ways to invoke MS SQL Server stored procedures from DataMigrator. It all depends on what you want to do with them.

If you just want to run them, you could indeed create a DataMigrator stored procedure and then issue the command SQL SQLMSS EX sp_name. Of course then you are not really using DataMigrator.

If a database stored procedure returns an answer set you could create a synonym for it and use it as a data source in a data flow. Alternately you could call the stored procedure in a DBMS SQL Flow.

On the other hand, if a database stored procedure returns a single value as a result, like a scalar function, you can call it in a source or target transformation.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
ferran
Member
posted Hide Post
Hi Clif,

The idea is migrate the actual SQL Server procedures into Data Migrator. We do not want to use any of the features of SQL Server, not stored procedures. The idea ultimately is to stop using SQL Server.

I need to translate the code implemented in these stored procedures to new Data Migrator flows. These SQL Server stored procedures are, as I mentioned before, quite extensive and complex. E.G. These stored procedures takes parameters to perform certain tasks or other, works with several datasources, .....

In short, I thought I could write the code as a normal .fex (like I did it so many times in BATCH-ORIENTED load processes), to perform all the tasks I need, until I could get a temporaly file to use as a source for destination table. But I don't know if this is possible....

Thanks!

Ferran.


WF 7.7.03,Win XP Pro.
HTML, PDF, EXCEL, AHTML, FLEX, APDF.
SQL Server, MySQL, DB2, FOCUS.
 
Posts: 19 | Registered: February 04, 2011Report This Post
Clif
Guru
posted Hide Post
If you just want to RUN the SQL Stored procedures, you can do so by copying them to DataMigrator Stored Procedure (focexec) and prefacing them with SQL SQLMSS and following them with ; END.

I'm sorry, there is no automatic translation from SQL stored procedures, SSIS packages, etc. into DataMigrator flows.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
AK
Member
posted Hide Post
quote:
Originally posted by Clif:
On the other hand, if a database stored procedure returns a single value as a result, like a scalar function, you can call it in a source or target transformation.


Clif, can you elaborate on how we can call a database stored procedure in a source or target transformation? Is it using the DBLOOKUP function?

Thanks.


iSM 616
iDM 7705
iDQC 802
WF 77
Windows 2008 Server
 
Posts: 29 | Registered: May 01, 2007Report This Post
ferran
Member
posted Hide Post
Thanks for your replies, Clif. I guess I would try to translate the SQL Stored Procedures code into DataMigrator flows.

If finally I can't do it, then I would try to copying them into focexec as you suggested.

Regards.


WF 7.7.03,Win XP Pro.
HTML, PDF, EXCEL, AHTML, FLEX, APDF.
SQL Server, MySQL, DB2, FOCUS.
 
Posts: 19 | Registered: February 04, 2011Report This Post
Clif
Guru
posted Hide Post
AK: You could use DB_LOOKUP but there is an easier way introduced in 7.7.03 for source transformations. In any event the source must be a table in the database and the stored procedure must returns a SINGLE result like a SCALAR function.

You call it like SQL.functionname(parameters....)

It's not a DataMigrator specific feature, you can find documentation for it here:

SQL.Function Syntax for Direct DBMS Function Calls


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
janaki_senthil
Member
posted Hide Post
Cliff,
I am trying to execute ms sql server 2008 stored procedure from iway data management console.But it not working.It is giving me a sql prepare error?

I need to execute the sql stored procedures in iway data migrator urgently.
Can you help?
 
Posts: 2 | Registered: September 20, 2012Report This Post
Clif
Guru
posted Hide Post
What are you trying to accomplish?

There are several ways to run a database stored procedure.

(1) you can create a DataMigrator stored procedure, for example:

ENGINE SQLMSS ex sp_help
END

(2) you can create a synonym for a database stored procedure, providing sample parameters. then use it as a source in a data flow and pass parameters with a WHERE condition or a JOIN.

This is documented in the DMUG, albeit using an ORACLE example.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report 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    Datamigrator newbie questions

Copyright © 1996-2020 Information Builders