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  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Direct update and include

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Direct update and include
 Login/Join
 
Silver Member
posted
Hi all,

I want to direct update and include data from one table to another table. My code is like the following:

MODIFY FILE TABLE1
 FIXFORM FROM TABLE2
  MATCH ID NFL
   ON MUTCH UPDATE FLD1 FLD2
  ON NOMATCH INCLUDE
 DATA ON TABLE2
END


I am getting the following error:
(FOC403) THE TRANSACTION DATA FILE CANNOT BE LOCATED: @0000007

Please help me.
Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.67 & WebFOCUS 80 windows
 
Posts: 45 | Location: Kabul, Afghanistan | Registered: August 07, 2011Report This Post
Virtuoso
posted Hide Post
A FIXFORM or FREEFORM describe fixed or comma delimited data files.

Look up COMBINE FILE and NEXT so that you can NEXT through source table and then MATCH into the product table.

You will also need to look up CASE logic.

Something like, very pseudo,:
COMBINE FILES table1 table2 as twotables
MODIFY FILE twotables
NEXT table1.fld
ON NONEXT GOTO EXIT
ON NEXT GOTO CASE1

CASE CASE1
COMPUTE table2.field = table1.fields
.
.
.
MATCH table2.fld
ON NOMATCH INCLUDE
ON MATCH UPDATE . . . 
ENDCASE
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
You caould try a 2-phase update:
  
TABLE FILE TABLE2
PRINT ID NFL FLD1 FLD2
ON TABLE HOLD
END
MODIFY FILE TABLE1
FIXFORM FROM HOLD
MATCH ID NFL
  ON MATCH UPDATE FLD1 FLD2
  ON NOMATCH INCLUDE
DATA ON HOLD
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
Alan B/Danny-SRL,

thanks a lot from your answers.
yes it is possible we can use MAINTAIN language to update/insert or at first we can table out then update/insert as well, cause of the huge number of records that cause me stack or taking long time to update/insert data. for this i want to direct load data from one table to another table without any table out or stack.


WebFOCUS 7.67 & WebFOCUS 80 windows
 
Posts: 45 | Location: Kabul, Afghanistan | Registered: August 07, 2011Report This Post
Virtuoso
posted Hide Post
If you use MAINTAIN, you can control the stack sizes using:
.
.
.
 stack clear instack outstack; 
 for next nnnnn table.fld into instack;
$$ if tables are the same layout
 for all copy from instack to outstack;
 for all revise table.fld table.fld . . . from outstack;
.
.
.
to obtain the best performance.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Have you filedef'ed table2? Just from the original post, it looked like the transaction file simply could not be located.

A thought...


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Report This Post
Silver Member
posted Hide Post
Yes, the FILEDEF is missing for table2. Because It iis not extract to define a FILEDEF. I have two table in my database and cause of huge records that took me hours to load in two phase which Danny-SRL direct me.


For this wanted to load data from one table to another table in one phase.

It is looks like we canot use this method.


WebFOCUS 7.67 & WebFOCUS 80 windows
 
Posts: 45 | Location: Kabul, Afghanistan | Registered: August 07, 2011Report This Post
Virtuoso
posted Hide Post
With MODIFY or MAINTAIN, coded correctly you can.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
FRA,

Start from the beginning:
What type of file is TABLE1? Focus? SQL? Other?
Same for TABLE2? Focus? SQL? Sequential?
Are the field names the same? And their formats?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
Alan B,

I tried with MODIFY in one phase that is give me error which is pasted at the first time.
also used the MAINTAIN it is working, but it is taking long time than two phase of MODIFY.

Danny-SRL,

sorry i ddin't understan what do you mean,
I have two table in MS-SQL which is TABLE1 and TABLE2:
i have already Master file and access file for both table.

just wants to load data in one phase as the following:
MODIFY FILE TABLE1
 FIXFORM FROM TABLE2
  MATCH ID TDATE
  ON MATCH UPDATE FLD1 FLD2
  ON NOMATCH INCLUDE
 DATA ON TABLE2
END


which is mentioned in the first that generate the following error:
(FOC403) THE TRANSACTION DATA FILE CANNOT BE LOCATED: @0000007


Why the above MODIFY is not working.

Thanks.


WebFOCUS 7.67 & WebFOCUS 80 windows
 
Posts: 45 | Location: Kabul, Afghanistan | Registered: August 07, 2011Report This Post
Virtuoso
posted Hide Post
FRA,

Here is the reason.
When you use MODIFY with FIXFORM, the file which is used as input MUST be a sequential file, hence the error you receive.

There are then 2 possible solutions:
1. Use a 2-phase TABLE + MODIFY
2. Use the COMBINE command to get data from TABLE2 and insert it into TABLE1.

For (1), you might make things faster if instead of using TABLE, you use TABLEF.

For (2), here is a general idea of how to go about inserting records from TABLE2 into TABLE1:
  
COMBINE FILES TABLE1 TAG T1 AND TABLE2 TAG T2 AS FRA
MODIFY FILE FRA
NEXT T2.ID T2.TDATE
ON NONEXT GOTO EXIT
ON NEXT GOTO ADDT1
CASE ADDT1
COMPUTE 
T1.ID=D.T2.ID;
T1.TDATE=D.T2.TDATE;
T1.FLD1=D.T2.FLD1;
T1.FLD2=D.T2.FLD2;
MATCH T1.ID T1.TDATE
ON MATCH UPDATE T1.FLD1 T1.FLD2
ON NOMATCH INCLUDE
ENDCASE
DATA
END

I don't know which is faster.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Okay, I thought I would get some comparison figures together for extracting and loading data.

These figures were taken using WebFOCUS 7.703 on a VM with a single CPU of a 2.8GHz dual core processor and 2Gb Memory with SQL 2008 Server on XP. Each run started with a clean, restarted, machine; figures are for an average of 5 runs. The task to extract from one SQL table into a separate but equivalent table. Source table has about 2 million rows, with a width of 210 bytes. All processes were tuned to obtain best performance I could from them.

MAINTAIN - single process read source and write out new table -> 21 minutes 22 seconds.
MODIFY - single process read source and write out new table -> 18 minutes 10 seconds.

TABLE/MODIFY - extract from source into HOLD file and write out new table -> 21 minutes 4 seconds
TABLE/MAINTAIN - extract from source into HOLD file and write out new table -> 11 minutes 34 seconds (Yes you read that correctly and I double checked!!!).


As a further comparison I took the worst performing process, single process MAINTAIN and ran into a FOCUS db, rather than SQL Server.

MAINTAIN - single process read source and write out new focus db -> 3 minutes 58 seconds.

Just some food for thought...


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Silver Member
posted Hide Post
Thanks a lot from your help and thoughts.


WebFOCUS 7.67 & WebFOCUS 80 windows
 
Posts: 45 | Location: Kabul, Afghanistan | Registered: August 07, 2011Report This Post
Virtuoso
posted Hide Post
Alan,

In your MAINTAIN single process, are you creating stacks or are you using single records, read - write?

Same question when you use the HOLD file. What do you suppose gives MAINTAIN such an advantage in this case?

When you write to a FOCUS db, that is quite fascinating. What happens when you use MODIFY instead of MAINTAIN?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Daniel

In the Maintain I was using Stacks each time. Single read/write doesn't work there very well.

I am not sure why MAINTAIN came through so fast, though the CPU used was slightly heavier, as well as the memory usage due to stacks. The MODIFY did not appear to utilise constant CPU, which was up and down like a yo-yo. sqlserver appeared to be using much more than half CPU in nearly all cases, just watching task manager.

Also remember that this is a small VM. Different machines will give different results I am sure.

I ran a MODIFY into FOCUS db, just a single run for you, and it came out at 2 minutes 47 seconds.

As you know, reading from (X)FOCUS is also much faster than SQL, so why are they not used more for Data Marts?


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Why?

It is the same tune all over again.
In the 1960's it was "nobody ever got fired for buying IBM"
In the 1980's it was "nobody ever got fired for buying Microsoft"
And now it is "nobody ever got fired for using SQL"

I have a customer who was using FOCUS files on an IBM M/F VM. His FOCUS application used -WINDOW and -CRTFORM and gave users the possibility to build their own reports.
The company decided to leave the M/F for an SAP environment and its data warehouse. They suffer.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
Daniel, you're so right!

And the best part is that companies spend HUGE amounts of money to suffer ;-)


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Virtuoso
posted Hide Post
Twanette,
A-b-s-o-l-u-t-e-l-y!
Still our client does use WebFOCUS and with our front end WrapApp, their users have had little trouble adapting.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
At least in the 60's there were other good data file structure from IBM, IMS and VSAM. Both fast and reliable. (Is there anything that can beat a CICS IMS System?).

I suspect that most people do not understand the hierarchical structure and have never tested the Intelligent Partitions, External Index or MDI. I created a test 1TB XFOCUS structure with IP, EI and MDI. Really fast response and great for selection population with MDI Encoding.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Direct update and include

Copyright © 1996-2020 Information Builders