Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator -- Can You Specify XFOCUS Temp Files Instead of FOCUS Temp Files?
Go
New
Search
Notify
Tools
Reply
  
Data Migrator -- Can You Specify XFOCUS Temp Files Instead of FOCUS Temp Files?
 Login/Join
 
Master
posted
I have some pretty big jobs (i.e. number of rows) and Data Migrator uses FOCUS files for temporary tables to build its joins. I'm running out of .FOC's 2Gig limit. Is there a setting for I-Way/Data Migrator to use XFOCUS for temporary files instead?

J.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Have you tried HOLD FORMAT XFOCUS?

I don't have data migrator but that syntax works in a TABLE command.

I built 2 files with identical data hold.foc and hold1.foc. hold.foc is the XFOCUS file and it has 34 pages. I can see this by using the ? FILE HOLD command. hold1.foc is a FOCUS file and it has 122 pages.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
I have two thoughts on how you might be able to avoid this issue, if they don't help could you open a hottrack case please.

(1) If both (or all) the sources are in a relational database or you can copy them there, then ensure the query can use automatic pass through, then the database does the join and no temporary files are needed.

(2) If not you can explicitly create a staging table and specify a target type of XFOC and then join to that.
 
Posts: 382 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Master
posted Hide Post
It doesn't work that way in Data Migrator. DM uses temporary files under the covers to manage its joins and it uses .foc format without asking.

It would likely be a option on the server console or in one of its xml configuration files. If it exists.

J.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
quote:
(1) If both (or all) the sources are in a relational database or you can copy them there, then ensure the query can use automatic pass through, then the database does the join and no temporary files are needed.


These are all SQL*Server tables in the same database. There's no reason whatsoever for it to create temporary files -- left-outer joins that de-star a star-schema and remove the lookup tables from the datamart.

I can run the entire thing in WebFOCUS instead and stop paying for I-Way entirely, but Data Migrator provides a more straightforward, more maintainable package and that's important long-term. However I can only use it if it has the capacity to do what I need to do.

J.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
These are all SQL*Server tables in the same database. There's no reason whatsoever for it to create temporary files


In that case I think the best approach is to figure out why the query isn't using APT and passed through to MS SQL Server.

Create a stored procedure with the following lines and add it on the process flow tab before the embedded (purple) data flow.

SET TRACEON=ALL
SET TRACEUSER=ON
SET TRACEON=NTFEXP/4/CLIENT


This little used trace setting would show the SQL sent to the RDBMS if the query can use APT, but if it can not, there is message as to why. It can be somewhat cryptic but if you provide a repro (data flow, source/target synonyms) to hotline they should be able to figure it out.
 
Posts: 382 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Thank you, I'll have a look.

I've opened a case on this, but they're pursuing the same concept -- look to solve my particular example instead of the greater issue involved. That's not going to aid me in the long-term.

J.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Alright well now I'm confused. I just examined one of my other routines and it's running a .foc beyond the 2G limit. So I don't know what's going on with this thing.

Thanks for tuning in.

J.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator -- Can You Specify XFOCUS Temp Files Instead of FOCUS Temp Files?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.