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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
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.
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....
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: