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     [SOLVED] SQL Report running forever
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQL Report running forever
 Login/Join
 
Platinum Member
posted
I've created an SQL report that consists of three querys joined with UNION statements. This query supplies the data for a Pivot Table in an Excel template. When I run the report it takes Forever (Waiting for response...) and then times out/crashes.

Is there any way I can see what it's doing behind the scenes so I can trouble shoot this thing?

Thanks,
Pondog

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


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Gold member
posted Hide Post
Below command will give you the sql trace and no data will be retrieved. please extract and run the query in you native database and let us know the performance and volume of record it retrieved

SET XRETRIEVAL=OFF

-*** Deactivate SQL tracing
SET TRACEOFF = ALL

-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT

-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT

-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT

-*** Show SQL generated sub-statement trace
SET TRACEON = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132

-*** Activate SQL tracing
SET TRACEUSER = ON
 
Posts: 64 | Location: North Carolina | Registered: December 04, 2007Report This Post
Platinum Member
posted Hide Post
If I edit in text mode then the code below goes after the Comment and before the Engine?



quote:
SET XRETRIEVAL=OFF

-*** Deactivate SQL tracing
SET TRACEOFF = ALL

-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT

-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT

-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT

-*** Show SQL generated sub-statement trace
SET TRACEON = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132

-*** Activate SQL tracing
SET TRACEUSER = ON


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
We have had issues with the use of UNION in SQL calls.

SQL BYPASS is still OK.

The tracing options should still work, as long as they are before your SQL.

Check the SQL generated for any issues.


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
If the results of the separate queries in your UNION are distinct (no possibility that a record from one query is also returned by one of the other queries), then you're better off using UNION ALL.

UNION adds an extra step to the total query to filter out duplicates, which can be quite expensive and is just overhead if that cannot happen in a given set of queries.

Also check whether the timeout/crash happens in the SQL query or at creating the pivot, for example by dumping the results to a normal table for debugging.

The trace options go somewhere before the SQL PREPARE statement, whether that's before or after the engine does not make a difference. Those SET's set the behaviour for the entire report from the point where you insert them into your code.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
Waz, Wep5622,

I checked each of the SQL queries and they're all distinct so I went with the UNION ALL option.

I inserted the SQL tracing code and ran the process to an HTML page. It ran in a few seconds and presented just the column headings; nothing else. Should I be looking somewhere else for the SQL generated feedback?

If I remove the SQL tracing code and run it to HTML it runs in about a minute and a half. When I try running it to an .xltm file it runs forever, whether the actual file contains a macro or not, and I never see any SQL generted feedback.

Pondog


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
SET XRETRIEVAL=OFF
is not a SQL trace command - it suppresses retrieval of data. This, of course, improves the performance of the code, but is normally used only while developing or debugging a report - you get to see the execution of the code much quicker than running the code AND retrieving data.

Unfortunately, I don't see how turning SQL traces on helps in debugging SQL "passthru" - WebFOCUS SQL traces are used to debug and fine-tune WebFOCUS JOIN and TABLE requests.

If the report returns in a timely manner in a format other than an Excel Template, then the code is good and there's something wrong with the reference to the template file.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Is the call using SQL or SQL {adapter} ?

If its SQL {adapter} like SQLMSS, then Francis is right, the code should be just shipped to the SQL server, that ever it is. But if you are using SQL, then you should use the tracing to check the generated/converted SQL.


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

I'm too new to know what SQL{adapter} is referring to. I created an SQL Report and typed in [copied and pasted] the code.

I suspect it's the Excel since I can run it to an HTML page, but when I try to send it to an Excel template it times out.

Pondog


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
Ah, a key piece of info.

Works for HTML, doesn't for Excel.

Are you PCHOLDing the excel or HOLDing it ?


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Hey Waz,
That seemed to help out. At least I'm not timing out, but instead receiving something back in a reasonable time.

Now I have the error message below coming back. I figure I don't have something coded right somewhere. I'm looking. Thanks for the point in the right direction.



 0 NUMBER OF RECORDS IN TABLE=   155571  LINES= 155571
 (FOC1522) ntjprerr: Java heap space. CPJAVA: Error in processing EXECUTE
 (FOC1522)  command for Class ibi.jsexcel.JscomExcelUnzip
 0 ERROR AT OR NEAR LINE    158  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC007) THE REQUEST STATEMENT DOES NOT CONTAIN A VERB
 (FOC3317) Error processing template file.
  


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
My code is now back to running forever. It's supposed to return 212,529 rows into an Excel template that houses a pivot table. However, all it does is spin.

Can I get an educated eye to peruse the code below and point out the obvious item I'm overlooking? I've become blind to my own errors. I'd really appreciate it.

 -* File: IBFS:/Dev/WFC/Repository/TonyTest/WrvusDeptDivProv/Report1.fex Created by WebFOCUS AppStudio
SET EXCELSERVURL = ''
SET WEBARCHIVE=ON

ENGINE DB2 SET DEFAULT_CONNECTION UABHS
SQL DB2 PREPARE SQLOUT FOR
SELECT 
	'Visit Encounters' AS DEPTID,
	PSTPDFY,
	POSTPERIOD,
	FYMTH,
	RVUSUMMARY.FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC AS LOCCAT2,
	PROVNUM,
	PROVNAME,
	CPTCODE,
	SUM( QTY ) AS "SumWrvu"
FROM
	WAREFOCUS.RPTPERIOD RPTPERIOD,
	WAREFOCUS.RVUSUMMARY RVUSUMMARY,
	WAREHOUSE.DEPARTMENT DEPARTMENT,
	WAREHOUSE.DIVISIONS DIVISIONS,
	WAREHOUSE.PROVIDERS PROVIDERS,
	WAREHOUSE.LOCATIONS LOCATIONS,
	WAREHOUSE.DEPARTMENT DEPARTMENT_1
WHERE
	RPTPERIOD.VPOSTPER >= RVUSUMMARY.POSTPERIOD
	AND RVUSUMMARY.DEPARTMENT = DEPARTMENT.DEPTNUM
	AND RVUSUMMARY.DIVISION = DIVISIONS.DIVNUM
	AND RVUSUMMARY.BILPROV = PROVIDERS.PROVNUM
	AND RVUSUMMARY.LOCATION = LOCATIONS.LOCNUM
	AND LOCATIONS.LOCCAT2 = DEPARTMENT_1.DEPTNUM
	AND ( MSOGRPNUM IN( 3, 6 )
	AND PSTPDFY >= 2015
	AND CPTCODE BETWEEN '90800' AND '99499')

GROUP BY
	PSTPDFY,
	POSTPERIOD,
	FYMTH,
	RVUSUMMARY.FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC,
	PROVNUM,
	PROVNAME,
	CPTCODE

UNION ALL

SELECT
	CASE
		WHEN TRANSTYPE = 'C' THEN 'Charges'
		ELSE 'Net Colls'
	END AS COLUMN0000,
	VPSTPDFY,
	POSTPERIOD,
	"MONTH" AS CMONTH,
	FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC AS LOCCAT2,
	PROVNUM,
	PROVNAME,
	' ' AS SVCCODE,
	SUM( AMT ) AS "SumAmt"
FROM
	WAREFOCUS.TRANSUM TRANSUM,
	WAREHOUSE.DEPARTMENT DEPARTMENT,
	WAREHOUSE.DIVISIONS DIVISIONS,
	WAREHOUSE.PROVIDERS PROVIDERS,
	WAREHOUSE.LOCATIONS LOCATIONS,
	WAREHOUSE.DEPARTMENT DEPARTMENT_1
WHERE
	TRANSUM.DEPARTMENT = DEPARTMENT.DEPTNUM
	AND TRANSUM.DIVISION = DIVISIONS.DIVNUM
	AND TRANSUM.BILPROV = PROVIDERS.PROVNUM
	AND TRANSUM.LOCATION = LOCATIONS.LOCNUM
	AND LOCATIONS.LOCCAT2 = DEPARTMENT_1.DEPTNUM
	AND ( VPSTPDFY >= 2015
	AND MSOGRPNUM IN( 3, 6 )
	AND TRANSTYPE IN( 'C', 'P', 'R' ))

GROUP BY
	CASE
	WHEN TRANSTYPE = 'C' THEN 'Charges'
	ELSE 'Net Colls'
	END,
	VPSTPDFY,
	POSTPERIOD,
	"MONTH",
	FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC,
	PROVNUM,
	PROVNAME

UNION ALL

SELECT
	'Work RVUs' AS COLUMN0000,
	PSTPDFY,
	POSTPERIOD,
	FYMTH,
	FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC AS LOCCAT2,
	PROVNUM,
	PROVNAME,
	' ' AS CPTCODE,
	SUM( WRVU ) AS "SumWrvu"
FROM
	WAREFOCUS.RVUSUMMARY RVUSUMMARY,
	WAREHOUSE.DEPARTMENT DEPARTMENT,
	WAREHOUSE.DIVISIONS DIVISIONS,
	WAREHOUSE.PROVIDERS PROVIDERS,
	WAREHOUSE.LOCATIONS LOCATIONS,
	WAREHOUSE.DEPARTMENT DEPARTMENT_1
WHERE
	RVUSUMMARY.DEPARTMENT = DEPARTMENT.DEPTNUM
	AND RVUSUMMARY.DIVISION = DIVISIONS.DIVNUM
	AND RVUSUMMARY.BILPROV = PROVIDERS.PROVNUM
	AND RVUSUMMARY.LOCATION = LOCATIONS.LOCNUM
	AND LOCATIONS.LOCCAT2 = DEPARTMENT_1.DEPTNUM
	AND ( MSOGRPNUM IN( 3, 6 )
	AND PSTPDFY >= 2015)

GROUP BY
	PSTPDFY,
	POSTPERIOD,
	FYMTH,
	FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC,
	PROVNUM,
	PROVNAME
END

TABLE FILE SQLOUT
PRINT
     DEPTID AS 'Metric'
     PSTPDFY AS 'FY'
     POSTPERIOD AS 'POST PERIOD'
     FYMTH AS 'Post Month'
     FYFM AS 'FY Period'
     DEPTNUM AS 'Dpt#'
     DEPTDESC AS 'Department'
     DIVNUM AS 'Div#'
     DIVDESC AS 'Division'
     LOCCAT2 AS 'Location Category'
     PROVNUM AS 'Prv#'
     PROVNAME AS 'Provider Name'
     CPTCODE AS 'CPT'
     SumWrvu AS 'Amt'
WHERE RECORDLIMIT EQ 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT XLSX TEMPLATE 'wrvus_bydept_div_prov.xltm' SHEETNUMBER 2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
Do you still get the errors above


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

I'm not getting anything now. I'm back to running forever with no results. I've tried simplifying my code to just see if I can get a simple template to run and it's just not working. I have a fex that pulls about 2,200 rows. Something small and simple. I can run it to an HTML page with no problem, however, when I try to send it to an Excel .xltm template it runs forever. I thought I was getting somewhere with the PCHOLD vs HOLD issue, but it looks like I'm back to square one. If I can get this simple fex to work, then I can move on to bigger templates. Here's what I'm currently working on

-* File: IBFS:/Dev/WFC/Repository/TonyTest/WrvusDeptDivProv/Report2.fex Created by WebFOCUS AppStudio
SET WEBARCHIVE=ON
ENGINE DB2 SET DEFAULT_CONNECTION UABHS
SQL DB2 PREPARE SQLOUT FOR
SELECT
	'Visit Encounters' AS DEPTID,
	PSTPDFY,
	POSTPERIOD,
	FYMTH,
	RPTPERIOD.FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC AS LOCCAT2,
	PROVNUM,
	PROVNAME,
	CPTCODE,
	SUM( QTY ) AS "SumWrvu"
FROM
	WAREFOCUS.RPTPERIOD RPTPERIOD,
	WAREFOCUS.RVUSUMMARY RVUSUMMARY,
	WAREHOUSE.DEPARTMENT DEPARTMENT,
	WAREHOUSE.DIVISIONS DIVISIONS,
	WAREHOUSE.PROVIDERS PROVIDERS,
	WAREHOUSE.LOCATIONS LOCATIONS,
	WAREHOUSE.DEPARTMENT DEPARTMENT_1
WHERE
	RPTPERIOD.VPOSTPER >= RVUSUMMARY.POSTPERIOD
	AND RVUSUMMARY.DEPARTMENT = DEPARTMENT.DEPTNUM
	AND RVUSUMMARY.DIVISION = DIVISIONS.DIVNUM
	AND RVUSUMMARY.BILPROV = PROVIDERS.PROVNUM
	AND RVUSUMMARY.LOCATION = LOCATIONS.LOCNUM
	AND LOCATIONS.LOCCAT2 = DEPARTMENT_1.DEPTNUM
	AND ( MSOGRPNUM IN( 3 )
	AND PSTPDFY >= 2015
	AND CPTCODE BETWEEN '99400' AND '99499')

GROUP BY
	PSTPDFY,
	POSTPERIOD,
	FYMTH,
	RPTPERIOD.FYFM,
	DEPARTMENT.DEPTNUM,
	DEPARTMENT.DEPTDESC,
	DIVNUM,
	DIVDESC,
	DEPARTMENT_1.DEPTDESC,
	PROVNUM,
	PROVNAME,
	CPTCODE

END
TABLE FILE SQLOUT
PRINT
     DEPTID AS 'Metric'
     PSTPDFY AS 'FY'
     POSTPERIOD AS 'POST PERIOD'
     FYMTH AS 'Post Month'
     FYFM AS 'FY Period'
     DEPTNUM AS 'Dpt#'
     DEPTDESC AS 'Department'
     DIVNUM AS 'Div#'
     DIVDESC AS 'Division'
     LOCCAT2 AS 'Location Category'
     PROVNUM AS 'Prv#'
     PROVNAME AS 'Provider Name'
     CPTCODE AS 'CPT'
     SumWrvu AS 'Amt'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT XLSX TEMPLATE 'wrvuS_bydept_div_prov.xltm' SHEETNUMBER 2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END  


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
Have you checked the Excel file ?

I haven't used templates with XSLX before, hope it works.


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Checked the Excel file? Normally when I run it; and I've only had this happen once a month ago, it would open up a dialogue box and ask if I wanted to Open or Save the file. Now all it does is sit there an process.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
I think you are going to have to open a case with Tech Support.

I assume the xltm file is in the path and accessible ?


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
How do I check to see if the file is in the path and accessible?

Sorry for the newbie questions.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
Where is the file located ?

And if you issue an APP SHOWPATH or ? PATH, is it in a directory in the list.


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Yep, the file is in the path.

A ? PATH resulted in APPPATH = /opt/webfocus/ibi/apps/uabserver which is were the .xltm file is located.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
OK,

As this seems to be Unix or Linux, have you checked the case of the file ?, there is a capital S in you posted code.


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Yes, I did catch that and changed it to a lowercase. I heard someone once say that templates needed to be in lowercase to work.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Gold member
posted Hide Post
Most of the time when this happens to me or my customers, the dialogue box to open or save the file is hidden behind other windows. Now when it happens, I start by minimizing every window until I find the dialogue box. I would say this occurs 95% of the time. I have not been able to figure out what is going on the other 5% of the time.


WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively;
excel, html, pdf
 
Posts: 89 | Registered: March 19, 2011Report This Post
Virtuoso
posted Hide Post
Does your three-select union-all SQL pass-thru request work when you simply report in HTML format? If not, it may be because the names (as well as the lengths of the literal values) for first column are inconsistent.


quote:
SELECT
'Visit Encounters' AS DEPTID,
. . .
SELECT
CASE
WHEN TRANSTYPE = 'C'
THEN 'Charges'
ELSE 'Net Colls'
END AS COLUMN0000,
. . .
SELECT
'Work RVUs' AS COLUMN0000,
. . .


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
The hidden dialogue box has bit me so many times. You wait and wait and wait ...


WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
 
Posts: 115 | Location: Seattle, WA | Registered: April 07, 2015Report This Post
Platinum Member
posted Hide Post
Hey Jack,

If I put a RECORDLIMIT EQ 100 and run it to HTML then it returns a product in about 7 seconds. Normally, the query will return around 212,629 rows. I tested out the SQL before putting it in the report.

I did a time test and increased the RECORDLIMIT to HTML;
100 rows = 7 seconds
1000 rows = 14 seconds
10,000 rows = 17 seconds
50,000 rows = 31 seconds
100,000 rows = 51 seconds
150,000 rows = no return; just keeps processing.

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


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
Will it complete if you just HOLD the output? You may be overwhelming HTML and EXCEL.


WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
 
Posts: 115 | Location: Seattle, WA | Registered: April 07, 2015Report This Post
Virtuoso
posted Hide Post
quote:
100,000 rows = 51 seconds150,000 rows = no return; just keeps processing.



Dies at 60 seconds, huh? Cf. this posting
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Hey Michael,

The process will complete up to around 150,000 rows. After that it times out. I plan on talking with my local tech manager. I think it may have something to do with the SQLOUT and the Excel template. I can get the template to work without the SQLOUT, but when I invoke the SQL it doesn't work.

I'll let you know what I find.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
Pondog -

Look for a 60-second timeout setting somewhere along the chain of servers (as I suggested above).
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Report running forever

Copyright © 1996-2020 Information Builders