Greetings Focus Gurus...
We are using the following sql to extract data from webfocus into .csv format.. (we have no way of bypassing the focus layer and getting to the RDBMS directly)
I have some questions
[1] Is this the fastest way to dump out pass thru SQL
[2] Is there a different file format we could use, that could be read by an external database engine (e.g. SQL Server/Oracle)
[3] Is there a way of speeding up the I/O performance of the ON TABLE SAVE (as it writes very slowly)?

Thanks in advance for your advice

SQL SQLORA

-* &TABLES.&I is an array of tables we are extracting
SELECT * FROM &TABLES.&I;


TABLEF FILE SQLOUT
PRINT *

ON TABLE SAVE AS HOUTPUTFILE1 FORMAT COMT
END

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

How many records are you extracting ?
How long does it take ?

there are about 35 base tables we are extracting.
it takes hours.
In particular the I/O of ON TABLE SAVE is extremely slow.
Is there an alternate way?

How large are the tables ?

If they are all millions of records and a large record length, then it will take time.

Also where are you storing the data is it on a fast storage location ?

The output speed is about 1MB/sec for a .csv file. That is pretty slow.
I am not aware of what the storage hardware is.

Really the code is all we have control of - is there a more efficient way to write our code to produce extracts?

I'm just going to ask the base question real quick, why are you extracting the entire table? What types of tables are these? Lists of values or transactional? If transctional again I ask, why are you extracting the entire table.

When pulling data across you are going to have to pay attention to table width as well as the amount of records. Using hold format alpha will probably be your quickest hold file because it will just save everything as text. But it sounds like it's the extraction that's your issue. That is probably more related to the size of your connection between the dB and your reporting server.

If your issue is actually more on write side of things, maybe your issue is how often you are committing your data. You might need to adjust how often you commit back to the database.

We are extracting the data for importation into a relational database and we dont have access to the underlying database behind Focus.

I haven't tried this myself, but there is a bulk data export utility for SQL Server named bcp. First, I wonder if Oracle has a similar utility. If so, I wonder if there is a way you could call this utility via SQL Passthru from WebFOCUS. If so, using such a utility should eliminate WebFOCUS from the equation in the extract/file creation process and potentially speed it up significantly. Finally, my limited research indicates that CSV files are NOT the best format in terms of performance for importing data into relational tables.
bcp utility

What is your OS ?

There is a BCP equivalent in oracle.

You could use this if its available via the client, and also from an OS call from FOCUS.

You could also use a java program to do the same, if you think that FOCUS is taking too long. There will be many on the internet, just google it.

You say you are on FOCUS 7, is it FOCUS or WebFOCUS ?