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

Facebook Twitter LinkedIn
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] DataMigrator: parametric dataflow
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] DataMigrator: parametric dataflow
 Login/Join
 
Member
posted
Hi!!

First of all, here you have a link to a image resuming my issue:


So, the scenario is as follows:
I'd like to develop a parametric dataflow with DataMigrator, so that the origin source would point to tables in different schemas in the same Oracle instance: pre_webfocus.prueba_simultanear or webfocus.prueba_simultanear.

In order to achieve this, I make the following proof of concept:
.MAS
  
FILENAME=PRUEBA_SIMULTANEAR, SUFFIX=SQLORA  , $
  SEGMENT=PRUEBA_SIMULTANEAR, SEGTYPE=S0, $
    FIELDNAME=IDENTIFICADOR, ALIAS=IDENTIFICADOR, USAGE=A255, ACTUAL=A255,
      MISSING=ON, $
VARIABLE NAME=&&MUTABLE_SCHEMA, DEFAULT=pre_webfocus, $


.ACX
  
SEGNAME=PRUEBA_SIMULTANEAR, TABLENAME=&&MUTABLE_SCHEMA..PRUEBA_SIMULTANEAR, 
   CONNECTION=pre_sisaadn, KEYS=0, $


.FEX
  
-SET &MUTABLE_SCHEMA='pre_webfocus';
TABLE FILE PRUEBA_SIMULTANEAR
PRINT *
END
-RUN


And this works like a charm, being able to point to different tables in runtime.

Yet, when developing a dataflow with DataMigrator, the produced source code contains
  
...
-SET &ECHO = 'ALL'; 
-SET &&MUTABLE_SCHEMA = pre_webfocus; 
...
SQL PREPARE SQLIN FROM 
SELECT 
   T1.IDENTIFICADOR 
FROM 
   sisaad_carga.prueba_simultanear T1 
END 
-RUN 


which will fail due to a : ORA-00903: nombre de tabla no válido (Invalid table name).
It seems that a master with variable won't work when used insed a SQL PREPARE SQLIN.
Is there any workaround to achieve my goal? [Pointing to different tables in runtime]

Thank u all!

This message has been edited. Last edited by: Tamra,


WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
 
Posts: 20 | Registered: April 27, 2016Reply With QuoteReport This Post
Member
posted Hide Post
Well, it seems the image is not working. Here u have the url:
https://postimg.org/image/63lgsrcmv/


WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
 
Posts: 20 | Registered: April 27, 2016Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi Glezo,

Did you try with a SQL Flow?

regards, Martin.


WebFocus 7.6.9/7.7.06M, iWay DataMigrator, ISM, Windows, DB2 Windows V10.5, MS SQL Server, Hyperstage
 
Posts: 119 | Registered: March 29, 2013Reply With QuoteReport This Post
Member
posted Hide Post
By doing so (DBMS SQL Flow) we would lose all the metdata enrichment, something we'd like to avoid.

Yet, I tried after your suggestion, with quite unpleasant results Frowner :

I configured the sql flow as follows:
-Set variables node, performing
  
-SET &SCHEMA='webfocus'

-SQL node with following embedded content:
  
select * from &SCHEMA.EVAL.prueba_simultanear


Then, when I'm prompted for a value for variable &SCHEMA, I enter 'webfocus' (table webfocus.prueba_simultanear certainly exists under the used adapter), and DM Client crashes and hangs

We're running 8.0.08 in both client and server side.

Picture of it all:
https://postimg.org/image/htiwq7lx3/


WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
 
Posts: 20 | Registered: April 27, 2016Reply With QuoteReport This Post
Member
posted Hide Post
btw, I also tried to replace the embedded sql code to
  
select * from &SCHEMA.prueba_simultanear 

with same result


WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
 
Posts: 20 | Registered: April 27, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
First of all DataMigrator is designed to be used from the graphical editors and should always generate valid syntax. If you edit in a text editor there's no guarantee.

The variables in a synonym are always GLOBAL variables, for example TABLENAME=&&TNAME
So the name in the SET command must have TWO ampersands (not one as in most of your examples).

In this case its value should be a fully qualified table name. For example:

-SET &&TNAME='sisaad_carga.prueba_simultanear' ;


In the Data Flow you select a synonym so the generated SQL ... SELECT statement should only reference a SYNONYM name, for example:

SQL PREPARE SQLIN FROM 
SELECT 
   T1.IDENTIFICADOR 
FROM 
   PRUEBA_SIMULTANEAR T1 
END 


It would not generate a variable in place of s table name. Also you would only see a two-part name (with a period) is if you had checked in Options, Data Flow Designer the checkbox to "Use application directory names with flow components" and means that the first part of the name is an application directory.

You may want load the tutorial "DataMigrator - Iterator" to see an example of how a parametrized synonym can be iterated over multiple values, such as table names or connections.

Finally 8.0 is based on the 7.7.05 server release which first shipped in January 2013. I would strongly advise upgrading to the current production 8.1/7.7.06 release if not the forthcoming 8.2/7.7.07.
 
Posts: 333 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
First of all, thanks for your answer, Clif.


I've never, ever, edited a dataflow by means of text. I only open it as text when I need to figure what code is being executed, as now.
In my case it's generating a valid, non-working (due to my misunderstanding, surely) syntax.

Despite my wrong examples, as you can see in the picture url I posted, I certainly used a global variable in the dataflow.

Indeed, we use the setting "Use application directory names with flow components". I don't like it this way, since we lose the power of the application path, but my boss chose so.
I'll try to execute the very same flow without that setting, and, for sure, I'll study the tutorial you mention if I can find it.

In a different, parallel task, we're migrating to a 8.1.05 release Wink


As a quick workaround, we finally did not use parametric flows, but "static" masters pointing to Oracle synonyms, which we dinamically change by means of a stored procedure.
I would've liked to achieve this by DataMigrator, but we were running out of time. Yet, I'll keep on trying.

thank you so, so much!!!


WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
 
Posts: 20 | Registered: April 27, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
If the focexec is valid it should work!

Using application directory names is fine; I wanted to be sure you knew that's what they were and not a schema or user name.

You can find the tutorial in the DataMigrator User's Guide, Chapter 11, "Iterating Through Multiple Tables." As the name implies instead of just assigning a value to a global variable in a synonym it shows you how to set up a file with a list of values and then iterate through them. You can load the tutorial files from the DMC by right clicking on an application directory, selecting New, Tutorials and then DataMigrator - Iterator from the pull-down menu.
 
Posts: 333 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
I solved a simmilar problem by running de dataflows from an procesflow. In this procesflow i use CMASAP to run the dataflow with the desired params.

When i run the datafllow in a 'normal' way i indeed get a simmilair message that de database can not be found. Seems some parameters only work when calling from another procedure with these global vars.

When you need more info, please contact me.


Webfocus 8.03
Iway 81M
Windows, All Outputs
 
Posts: 4 | Registered: December 10, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] DataMigrator: parametric dataflow

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