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.

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.

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.

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.

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.

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.

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.

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.