Data Migrator -- Can You Specify XFOCUS Temp Files Instead of FOCUS Temp Files?

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: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Clif

Guru

posted

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.

N/A

Posts: 397 | Location: New York City | Registered: May 03, 2007

Ignored post by Clif posted

Virtuoso

posted

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: 1012 | Location: At the Mast | Registered: May 17, 2007

Ignored post by John_Edwards posted

Virtuoso

posted

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: 1012 | Location: At the Mast | Registered: May 17, 2007

Ignored post by John_Edwards posted

Clif

Guru

posted

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.

N/A

Posts: 397 | Location: New York City | Registered: May 03, 2007

Ignored post by Clif posted

Virtuoso

posted

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: 1012 | Location: At the Mast | Registered: May 17, 2007

Ignored post by John_Edwards posted

Virtuoso

posted

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: 1012 | Location: At the Mast | Registered: May 17, 2007

Ignored post by John_Edwards posted