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] extracting current year data to the hold file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] extracting current year data to the hold file
 Login/Join
 
Gold member
posted
Hello

I have a report which uses a table that contains over 25mill rows. Report runs really slow and sometime never process because of large amound of data it has to go through.
I would like to create some kind of temp or hold file and extract only this years data to that hold file before hand.
Then use the hold file to create my report. Is it possible.
Here is my current code:
-DEFAULT &From_Object_Account = 4000
-DEFAULT &To_Object_Account = 4999
-DEFAULT &Document_Type = JE
TABLE FILE F0911
PRINT F0911.F0911.DOCUMENT_NUMBER
F0911.F0911.BUSINESS_UNIT
F0911.F0911.OBJECT_ACCOUNT
F0911.F0911.SUBSIDIARY
F0911.F0911.AMOUNT
F0911.F0911.UNITS
F0911.F0911.UNIT_OF_MEASURE
F0911.F0911.EXPLANATION___NAME_ALPHA
F0911.F0911.EXPLANATION___REMARK
F0911.F0911.TRANSACTION_ORIGINATOR
BY F0911.F0911.DOCUMENT_NUMBER NOPRINT
BY F0911.F0911.BUSINESS_UNIT NOPRINT
BY F0911.F0911.OBJECT_ACCOUNT NOPRINT
BY F0911.F0911.SUBSIDIARY NOPRINT
WHERE (F0911.F0911.OBJECT_ACCOUNT GE '&From_Object_Account')
AND (F0911.F0911.OBJECT_ACCOUNT LE '&To_Object_Account')
AND (F0911.F0911.LEDGER_TYPE EQ 'AA')
AND (F0911.F0911.G_L_DATE GE &From_GL_DATE_MMDDYYYY)
AND (F0911.F0911.G_L_DATE LE &To_GL_DATE_MMDDYYYY)
AND (F0911.F0911.DOCUMENT_TYPE EQ '&Document_Type');
HEADING
"Hanson Aggregates and Subsidiaries"
"General Ledger Report"
"&Document_Type Document Type"
"Object Accounts &From_Object_Account TO &To_Object_Account"
"GL Date Range:  &From_GL_DATE_MMDDYYYY TO &To_GL_DATE_MMDDYYYY"
FOOTING
"Date Report Generated: &DATE"
ON TABLE SET STYLE *
-*-INCLUDE app/hsmalpdf.sty
TYPE=REPORT,
   GRID=OFF,
   ORIENTATION=LANDSCAPE,
 $
TYPE= HEADING,$
TYPE= FOOTING,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE NOTOTAL
END  

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
Platinum Member
posted Hide Post
Looks like the J.D. Edwards General Ledger - F0911.
I'm not sure if this would meet your requirements but make the initial report based on the F0902 - General Ledger Summed to month and then have a drill
down to the F0911.
 
Posts: 229 | Location: New York | Registered: July 27, 2004Report This Post
Expert
posted Hide Post
Other things to check is indexes on the tables, etc. These may increase the speed fo retrieval.

Also for speed of retrieval, USE TABLEF, adn don't use BY Fields, as this will probably be pushed back to the DB.

How many records are you expecting out of this request ?


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
Expert
posted Hide Post
It looks like you don't need a HOLD file, other then, as Waz put it, "TABLEF, adn don't use BY Fields" which may reduce the retrieval time a bit, then HOLD that and report from that HOLD file. You should gain something. Run it both ways and check the timestamps of execution...BTW: You could use:
AND (F0911.F0911.G_L_DATE FROM &From_GL_DATE_MMDDYYYYTO &To_GL_DATE_MMDDYYYY)
as well. It may gain a millisecond of two depending on the generated SQL which I'm thinnking (now that I wrote this) may be the same either way...




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
Just to emphasise, check if the WHERE clauses are working on indexed fields, and if the query is returning large amounts of data, then it will take time for the DB to return it.

This should be a tuning exercise. There will be a way to speed things up, it just depends on how much can be achieved.


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
Gold member
posted Hide Post
How can I check for indexes on the table?


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
Virtuoso
posted Hide Post
Is your database Oracle? If so, try this. Your DBA should also be able to help you with this information.

SET SQLENGINE = SQLORA
SQL
SELECT *
FROM user_ind_columns
WHERE table_name='F0911';
TABLE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
yes. It is oracle db2 but we use OBDC adapter to connect to it.

I tried using the query provide and got the following error:

(FOC1488) SQL INTERFACE HAS NOT BEEN INSTALLED OR COULD NOT BE INITIALIZED
: Subphysical interface not installed for SUFFIX=SQLORA. Module name: ORAPAS2
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
(FOC1488) SQL INTERFACE HAS NOT BEEN INSTALLED OR COULD NOT BE INITIALIZED
(FOC1479) ERROR CONNECTING TO SQL DATABASE


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
Virtuoso
posted Hide Post
Try changing SQLORA to SQLODBC.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report 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] extracting current year data to the hold file

Copyright © 1996-2020 Information Builders