Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] improve performance of data extracts

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] improve performance of data extracts
 Login/Join
 
jashwood
Member
posted
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>,


Focus 7
 
Posts: 13 | Registered: May 17, 2015Report This Post
Waz
Expert
posted Hide Post
How many records are you extracting ?
How long does it take ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
jashwood
Member
posted Hide Post
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?


Focus 7
 
Posts: 13 | Registered: May 17, 2015Report This Post
Waz
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
jashwood
Member
posted Hide Post
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?


Focus 7
 
Posts: 13 | Registered: May 17, 2015Report This Post
eric.woerle
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
jashwood
Member
posted Hide Post
We are extracting the data for importation into a relational database and we dont have access to the underlying database behind Focus.


Focus 7
 
Posts: 13 | Registered: May 17, 2015Report This Post
Dan Satchell
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Waz
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] improve performance of data extracts

Copyright © 1996-2020 Information Builders