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>,

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

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

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.

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.

Have you filedef'ed table2? Just from the original post, it looked like the transaction file simply could not be located.

A thought...

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.

With MODIFY or MAINTAIN, coded correctly you can.

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?

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.

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.

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...

Thanks a lot from your help and thoughts.

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 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?

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, you're so right!

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

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.

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.