Focal Point
[SOLVED] Update masterfile on the fly

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4577016826

February 08, 2013, 12:45 PM
Carlos Dias
[SOLVED] Update masterfile on the fly
Hello,

It's possible to update a masterfile at run time?

Just need a simple example. Something like, in run time adding a FIELDNAME to SEGMENT.
I already try to use a HOLD for that, but it doesn't work because in the new one the SEGMENTS information are lost. Can't find either a way for adding the new fields.

If there isn't a way to go this in WebFOCUS I'm thinking in using Java to parse the file and add a new line with the FIELDNAME. But this is not a simple solution.

Any help?

Thanks,
Carlos Dias

This message has been edited. Last edited by: Carlos Dias,


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 08, 2013, 01:10 PM
FrankDutch
You can not unless you do not mind to loose data.
What you need to do is make a backup, add the field and restore the data.
If you have datamigrator software from Ibi it helps you doing this.
If the data is in a SQL database you also need to have rights to do this. Else it should be done by the DBA




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

February 08, 2013, 01:16 PM
eric.woerle
Carlos,

Why would you need to add a segment at run time? I am confused on what you are trying to accomplish. Can you provide some more detail?


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
February 08, 2013, 01:27 PM
Carlos Dias
Thanks for the fast answers.

Ok I'm going to try to explain way I need this.
Our system is very dynamic. And we have tools for the end user to create new indicators (columns) in the fact tables of the database (Oracle). So we need to refresh the masterfiles in this case. For this I can use the refresh synonym tool.

We are building another tool where the user can associate columns to the report. In this case he can set the title of the column or define a new one. In this situation I need to add this definitions to the master, changing the title, or adding the virtual column.


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 08, 2013, 01:45 PM
ABT
quote:
Originally posted by Carlos Dias:
Our system is very dynamic. And we have tools for the end user to create new indicators (columns) in the fact tables of the database (Oracle).


Maybe it's just me, but these seems to be a poor database design choice. We have a data warehouse and our fields are as follows:

 FILENAME=  DMVALUES
 VALUEID       ValueID       I11
 CO            CO            A5
 VSTART        VStart        HYYMDs
 VEND          VEnd          HYYMDs
 VFREQ         VFreq         A50
 VINDICATOR    VIndicator    A10
 VVALUE        VValue        D20.2
 VTIMEVALUE    VTimeValue    HYYMDs
 VLEVEL        VLevel        A100
 VLEVELTYPE    VLevelType    A12
 VASOF         VAsOf         HYYMDs
 VPROCESS      VProcess      HYYMDs
 VCOMMENT      VComment      A50
 VS01          VS01          D20.2
 VS02          VS02          D20.2
 VS03          VS03          D20.2
 VS04          VS04          D20.2
 VS05          VS05          D20.2
 VT01          VT01          D20.2
 VT02          VT02          D20.2
 VT03          VT03          D20.2
 VT04          VT04          D20.2
 VT05          VT05          D20.2


This provides some future-proofing -- when new indicators are added, we simply create a new distinct value in the VINDICATOR column. In our set up, VFREQ + VINDICATOR + VLEVEL + VLEVELTYPE = Primary Key. This should also allow you the capability to refine what is already there (for sub-measures, for instance).

In short, we grow down and it sounds like you grow out.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
February 08, 2013, 02:38 PM
Dan Satchell
You can -READ/-WRITE a new synonym and add/change titles and virtual fields. But two issues should be considered: (1) If it's a multu-user app, what happens if two users make changes at the same time, and (2) virtual fields and titles may be lost when the synonym is refreshed.


WebFOCUS 7.7.05
February 09, 2013, 05:03 AM
<FreSte>
Carlos,

When creating a master file, there is an option in the reporting console "Dynamic columns" (at least in 7.7.04 there is).

When ticking this checkbox, a master is created like this:

  
/*NOCOLS*/
FILENAME=XXXYYY, SUFFIX=SQLODBC , $
  SEGMENT=XXXYYY, SEGTYPE=S0, $


This means that run-time all columns are read form the cataloque.

-Fred-
February 09, 2013, 07:55 AM
Carlos Dias
quote:
Originally posted by Dan Satchell:
You can -READ/-WRITE a new synonym and add/change titles and virtual fields. But two issues should be considered: (1) If it's a multu-user app, what happens if two users make changes at the same time, and (2) virtual fields and titles may be lost when the synonym is refreshed.


This is a multi-user app so It my be a problem.
Maybe the best solution is to create the masterfile using Java for controlling this kind of issue.


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 09, 2013, 07:59 AM
Carlos Dias
quote:
Originally posted by FreSte:
Carlos,

When creating a master file, there is an option in the reporting console "Dynamic columns" (at least in 7.7.04 there is).

When ticking this checkbox, a master is created like this:

  
/*NOCOLS*/
FILENAME=XXXYYY, SUFFIX=SQLODBC , $
  SEGMENT=XXXYYY, SEGTYPE=S0, $


This means that run-time all columns are read form the cataloque.

-Fred-


Looks a nice feature. But I need to add virtual columns.


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 09, 2013, 09:41 AM
<FreSte>
You need to add virtual columns run-time ??

like DEFINE FILE XXXYYY ??
February 09, 2013, 10:05 AM
Alex
When you create you synonym check the dynamic columns box to specify that the Master File created for the synonym should not contain column information.

If this option is selected, column data is retrieved dynamically from the data source at the time of the request.


WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
February 10, 2013, 11:45 AM
Carlos Dias
When I mean on the fly I mean that I want a process to create dynamical columns, defines and computes in the masterfiles.
I'm creating a user interface where a admin user can create or associate columns to reports. This process creates the columns in the masterfiles.

So I need way, without using READ or WRITE to create/recreate the masterfiles. Is there any Java API or actions with FOCUS language for this?

Thanks,
Carlos Dias


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 10, 2013, 03:58 PM
Waz
How are these dynamic columns defined by the users ?

If I understand what you need, I would suggest you programatiacally copy the master/ACX to a session version, and then add what ever is needed.

But this depends on where these dynamic columns come from.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 11, 2013, 05:13 AM
Carlos Dias
quote:
Originally posted by Waz:
How are these dynamic columns defined by the users ?

Like I said we are creating a user interface where the user can set the columns. For this process I need a way to create this columns in the masterfiles.

quote:
Originally posted by Waz:
If I understand what you need, I would suggest you programatiacally copy the master/ACX to a session version, and then add what ever is needed.

What do you mean with session version?


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 11, 2013, 02:55 PM
Carlos Dias
I'm going to try the READ/WRITE option.


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 11, 2013, 03:45 PM
Waz
How will the columns be passed to WebFOCUS ?

If you know where the maste exists, then you can use APP COPYFILE to copy the master to the WebFOCUS sessions temp directory.

This can then be easlity added to.

e.g.
-SET &EDATEMP = FGETENV(7,'EDATEMP',70,'A70');
APP MAP EDATEMP &EDATEMP
-RUN
APP COPYFILE IBISAMP CAR MASTER EDATEMP CAR MASTER
-RUN
EX -LINES 2 EDAPUT MASTER,CAR,A,FILE
DEFINE CUSTOM_FIELD/A10 WITH COUNTRY= 'MY Test';$
-RUN
TABLE FILE CAR
PRINT CUSTOM_FIELD
END


This means that the changes only exist for the current users connection, and are lost when the report is finished.

If the changes need to be permanent, then the original master will need to be aded to.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 11, 2013, 06:39 PM
Carlos Dias
The changes need to be permanent.

What I really need is:
- For an ALIAS change the FIELDNAME;
- Add a COMPUTE field.

Suppose that I received this in parameteres.

Something like (original masterfile):
FILENAME=MYHOLD_RESULT_TABLE2, SUFFIX=FIX , $
SEGMENT=EMPINFO, SEGTYPE=S0, $
FIELDNAME=RECTYPE, ALIAS=R ALIAS=R, USAGE=A3, ACTUAL=A3, $
FIELDNAME=LAST_NAME, ALIAS=LN, USAGE=A15, ACTUAL=A15, $
END


- Receive a parameter with &alias1=R and &newfieldname1=NEW_RECTYPE
Change to:
FILENAME=MYHOLD_RESULT_TABLE2, SUFFIX=FIX , $
SEGMENT=EMPINFO, SEGTYPE=S0, $
FIELDNAME=NEW_RECTYPE, ALIAS=R ALIAS=R, USAGE=A3, ACTUAL=A3, $
FIELDNAME=LAST_NAME, ALIAS=LN, USAGE=A15, ACTUAL=A15, $
END

- Receive a parameter with &compute1=COMPUTE NEWFIELD/D20 = XPTO;
Add at the END of the masterfile:
FILENAME=MYHOLD_RESULT_TABLE2, SUFFIX=FIX , $
SEGMENT=EMPINFO, SEGTYPE=S0, $
FIELDNAME=NEW_RECTYPE, ALIAS=R ALIAS=R, USAGE=A3, ACTUAL=A3, $
FIELDNAME=LAST_NAME, ALIAS=LN, USAGE=A15, ACTUAL=A15, $
COMPUTE NEWFIELD/D20 = XPTO;, $
END


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 11, 2013, 07:06 PM
Waz
This raises alot of issues with editing a possibly active master on the fly.

But.

You should be able to do this, it will be tricky.

Here is a sample that will allow you to TABLE FILE a master file description.
FILEDEF READMAS DISK IBISAMP/car.mas

EX -LINES 4 EDAPUT MASTER,READMAS,CV,FILE
FILENAME=READMAS, SUFFIX=FIX,$
SEGNAME=READMAS, $
  FIELD=LINE,ALIAS=  ,A80 ,A80 ,$

-RUN

TABLE FILE READMAS
PRINT LINE
END


You should be able to change the FIELDNAME easy enough, but the addition of the COMPUTE will be a little more difficult.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 11, 2013, 07:47 PM
Carlos Dias
Wow... this is complex stuff!!!
I don't understand the "EX -LINES 4 EDAPUT" part.

But from what I understand I can change the FIELDNAME in the LINE of the TABLE FILE right?

Then I think I can add the COMPUTE at the end.


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 11, 2013, 08:00 PM
Waz
Then you have the issue of writing the new master back to where it came from.

This raises questions of whether there are problems, like the fex crashes for some reason during the write back, what should be done ?

Should you be backing up the original prior to the write, so it can be recovered.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 11, 2013, 08:01 PM
Waz
The EDAPUT command is an internal command for writing info to a file.

I posted something a couple of years ago about this.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 11, 2013, 08:21 PM
Carlos Dias
quote:
Originally posted by Waz:
Then you have the issue of writing the new master back to where it came from.

This raises questions of whether there are problems, like the fex crashes for some reason during the write back, what should be done ?

Should you be backing up the original prior to the write, so it can be recovered.


My idea is to use one master as base in a particular app. Then create the new one in another app.


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 11, 2013, 08:22 PM
Carlos Dias
quote:
Originally posted by Waz:
The EDAPUT command is an internal command for writing info to a file.

I posted something a couple of years ago about this.


This is the link? http://forums.informationbuild...061076282#8061076282


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel
February 12, 2013, 10:07 AM
Kevin W
What you are attempting sounds a bit like what this WebFocus user driven group has constructed. I think they have done so in consultation with IBI. http://www.dartg.com. The presentation I saw at Summit was for a pretty robust user driven system.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
February 15, 2013, 02:14 PM
Carlos Dias
The tip of Waz seems to be way to solve my problem.

Thanks


WebFOCUS version: 7.6
Linux/Windows
HTML, Excel