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
-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
WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
January 24, 2017, 04:40 AM
Martin vK
Hi Glezo,
Did you try with a SQL Flow?
regards, Martin.
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
January 25, 2017, 07:15 AM
glezo
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 :
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.
WebFOCUS 8, Win/*NIX, B.I.Portal, *SQL, all outputs
January 25, 2017, 07:16 AM
glezo
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
January 25, 2017, 01:59 PM
Clif
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.
N/A
January 27, 2017, 01:47 PM
glezo
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
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
January 30, 2017, 10:01 AM
Clif
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.
N/A
February 27, 2017, 08:27 AM
John_EBPI
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.