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    How to Call Dataflow in the procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to Call Dataflow in the procedure
 Login/Join
 
Platinum Member
posted
Hello,

Requirement - Initialization or rerun flow anytime with passing runtime parameters.

Created the procedure which has routine will will work according to parameter passed. But wondering is it possible to call data flow inside the routine each time (for example need to run for the 35 days) each day need to call the data flow.
Also question about - Is anyway can setup the transformation in data flow which can read the values set up in the procedure & how?

Any ideas will be great help !
Thanks in advance for your suggestions.

DM - 7.1


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Report This Post
Platinum Member
posted Hide Post
When you say 'procedure', if you mean a Data Migrator stored procedure, then I believe so. What I've done is call a stored procedure from another stored procedure, but I would think you could call a data flow too, or even process flow for that matter. And I also believe you can pass paramaters set in the stored procedure into the data flow and use them in your target transformations. For example, I have the months to be processed stored in a text file. I have a loop that reads through that text file and executes another stored procedure with the parms being passed in:

-*- the month(s) to be loaded are stored in this file
TABLE FILE LOAD_MONTHS
PRINT LOAD_MONTH
ON TABLE SAVE AS MONTH_SAVE
END
-RUN

-*- loop thru the values in the save file and run the process for each
-LOOP_START
-READ MONTH_SAVE NOCLOSE &LOAD_YYM.A6.
-IF &IORETURN EQ 1 GOTO END_LOOP;

-TYPE LOAD_YYM: &LOAD_YYM
-DEFAULT &&LOAD_YYM='&&LOAD_YYM'

EX CMASAP REQ_NAME=LOAD_MONTHS, CM_ASYNC=OFF, PARMS="&&LOAD_YYM=&LOAD_YYM"
-RUN

-GOTO LOOP_START

-END_LOOP


Then in the target transformations in your data flow, let's say you have a field in the target called LOAD_MONTH and you want the value of &&LOAD_YYM to go into that field. If it's defined as a numeric, you can set the target transformation expression for that field to &&LOAD_YYM. If it's alpha, then create a temporary field first that is numeric (we'll call it TEMP_LOAD_YYM), and make sure it's listed before LOAD_MONTH by moving it up in the list. Then set the target transformation expression for LOAD_MONTH to EDIT(TEMP_LOAD_YYM).

Good luck.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Platinum Member
posted Hide Post
Hi Jessica,

Thanks for your response.
Basically I want the similar one but instead of calling procedure need to call the dataflow.
Here is the procedure I have

-SET &&DAYSBACK = 35;
-SET &FDAYBACK = -&DAYSBACK;
-SET &TDAYBACK = -&DAYSBACK + 1;
-SET &CUTOFFDAYS = -&DAYSBACK;

-START

-* Get the current date
-SET &YR = EDIT(&DATEYYMD,'9999') ;
-SET &MO = EDIT(&DATEYYMD,'$$$$$99') ;
-SET &DD = EDIT(&DATEYYMD,'$$$$$$$$99') ;

-* Compute for the To Date
-SET &TOX1 = &YR || &MO || &DD ;
-SET &TOX = AYMD(&TOX1 , &TDAYBACK, 'I8YYMD');
-SET &TY = EDIT(&TOX,'9999');
-SET &TM = EDIT(&TOX,'$$$$99');
-SET &TD = EDIT(&TOX,'$$$$$$99');
-SET &TOX2 = &TM || &TD || &TY ;
-SET &TODATE = EDIT(&TOX2, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');

-* Compute for the From Date
-SET &FOX = AYMD(&TOX1 , &FDAYBACK, 'I8YYMD');
-SET &FY = EDIT(&FOX,'9999');
-SET &FM = EDIT(&FOX,'$$$$99');
-SET &FD = EDIT(&FOX,'$$$$$$99');
-SET &FOX1 = &FM || &FD || &FY ;
-SET &FROMDATE = EDIT(&FOX1, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');

-SET &CUTOFFDT = AYMD(&YYMD , &CUTOFFDAYS, 'I8YYMD');
-TYPE &CUTOFFDT

-SET &CUTOFFDAYS = &CUTOFFDAYS + 1;
-SET &FDAYBACK = &FDAYBACK + 1;
-SET &TDAYBACK = &TDAYBACK + 1;
-IF &CUTOFFDAYS NE 0 GOTO START ELSE GOTO EXITPROC;

-EXITPROC

-EXIT

---------------
From above procedure Can I use the &CUTOFFDT and &FROMDATE and &TODATE in dataflow in source transformation which can be call inside this routine and do you what will be code looks like to call the dataflow ?


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Report This Post
Platinum Member
posted Hide Post
Like I said, while what I did called a stored procedure, I believe you can use the same technique and call a data flow instead. Where I have

-DEFAULT &&LOAD_YYM='&&LOAD_YYM'

EX CMASAP REQ_NAME=LOAD_MONTHS, CM_ASYNC=OFF, PARMS="&&LOAD_YYM=&LOAD_YYM"
-RUN


Copy that and change it around for what you need. Where I only had one variable, you will have three, and where you see 'REQ_NAME=', put your data flow name there. Then give it a shot.

Good Luck.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Platinum Member
posted Hide Post
Thanks Jessica,

I tried and it works for calling the dataflow.
But had question for parameter CUTOFFDT is the parameter passed in the dataflow and it shows the value but only last value of my loop. For example I have two cutoffdt '20070911' and '20070912' in my loop. I gets the data in my target for only '20070912' any idea where to look for that ?

-TYPE CUTOFFDT: &CUTOFFDT
-DEFAULT &&CUTOFFDT='&&CUTOFFDT'

EX CMASAP REQ_NAME=FOCUS_TO_ORACLE, CM_ASYNC=OFF, PARMS="CUTOFFDT=&CUTOFFDT"
-RUN

-SET &CUTOFFDAYS = &CUTOFFDAYS + 1;
-SET &FDAYBACK = &FDAYBACK + 1;
-SET &TDAYBACK = &TDAYBACK + 1;
-IF &CUTOFFDAYS NE 0 GOTO START ELSE GOTO EXITPROC;

-EXITPROC

-EXIT


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Report This Post
Platinum Member
posted Hide Post
Assuming I'm following your correctly, then look back at my original post. If you have a loop where &CUTOFFDT gets set differently with each loop, then you'll need to execute your data flow within the loop, just like my example. For you, &CUTOFFDT gets set to 20070911, execute your data flow, loop around, set &CUTOFFDT to 20070912, execute your data flow again, etc., etc., until you've exhausted all the values that &CUTOFFDT can be set to. If you're executing your data flow outside the loop, then you will only get the last value of &CUTOFFDT. But if what you need is to have both 20070911 and 20070912 passed into a single execution of your data flow, then you'll need two variables, like &CUTOFFDT1 and &CUTOFFDT2.

I also noticed that your EX CMASAP doesn't look quite right. You have:

EX CMASAP REQ_NAME=FOCUS_TO_ORACLE, CM_ASYNC=OFF, PARMS="CUTOFFDT=&CUTOFFDT"
-RUN

I believe the PARMS= should be PARMS="&&CUTOFFDT=&CUTOFFDT"

Also make sure what you're referencing in your data flow is &&CUTOFFDT and not &CUTOFFDT.

This message has been edited. Last edited by: Jessica Bottone,


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Virtuoso
posted Hide Post
Viral

quote:
-* Get the current date
-SET &YR = EDIT(&DATEYYMD,'9999') ;
-SET &MO = EDIT(&DATEYYMD,'$$$$$99') ;
-SET &DD = EDIT(&DATEYYMD,'$$$$$$$$99') ;

-* Compute for the To Date
-SET &TOX1 = &YR || &MO || &DD ;


I saw this part of your procedure and I wondered why you do this.

The first input you start with is a value like '20070915' (today), then you split it in a year value '2007' a month value '09' and a day value '15'.
the last step is concatenate it in the same order so the end result is '20070915'.
I'm sure it works, but it was not needed.
If you take it out, the report wont be faster (not that anybody can measure it) but effective programming is important...

the other thing that I see is that you first put your daysback value into a '&&' constant. the double should only be used if you want it global used and not just in this program. (it might be a typo).




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
Hi Frank,

Yeah you are right to compute Todate and daysback initially use 35 days &daysback , but later want use as default and prompt for it so can override so just change to &&daysback.

Thanks,
Viral.


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Report This Post
Platinum Member
posted Hide Post
Hi All,

Able to resolve the issue. On my dataflow target was new.So every time when call the dataflow from the procedure it drop and create the target. Change to existing target and it works.

Thanks all again for your inputs.


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Report 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    How to Call Dataflow in the procedure

Copyright © 1996-2020 Information Builders