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, PondogThis message has been edited. Last edited by: Pondog,
WebFOCUS 8.1.05 Windows, All Outputs
October 21, 2015, 04:35 PM
krish
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
October 21, 2015, 05:08 PM
Pondog
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
October 21, 2015, 05:28 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 22, 2015, 04:11 AM
Wep5622
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 :
October 22, 2015, 11:02 AM
Pondog
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
October 22, 2015, 11:13 AM
Francis Mariani
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
October 22, 2015, 04:36 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 23, 2015, 12:07 PM
Pondog
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
October 25, 2015, 05:29 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 26, 2015, 09:46 AM
Pondog
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
October 26, 2015, 03:46 PM
Pondog
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
October 26, 2015, 05:07 PM
Waz
Do you still get the errors above
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 26, 2015, 05:26 PM
Pondog
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
October 26, 2015, 05:34 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 26, 2015, 05:45 PM
Pondog
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
October 26, 2015, 05:53 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 26, 2015, 05:55 PM
Pondog
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
October 26, 2015, 06:01 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 26, 2015, 06:16 PM
Pondog
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
October 26, 2015, 06:37 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 26, 2015, 06:43 PM
Pondog
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
October 27, 2015, 10:38 AM
Deana
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
October 27, 2015, 11:28 AM
j.gross
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
October 27, 2015, 11:28 AM
Michael L Meagher
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
October 27, 2015, 02:43 PM
Pondog
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
October 28, 2015, 02:30 PM
Michael L Meagher
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
October 28, 2015, 03:25 PM
j.gross
quote:
100,000 rows = 51 seconds150,000 rows = no return; just keeps processing.
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
November 01, 2015, 05:44 AM
j.gross
Pondog -
Look for a 60-second timeout setting somewhere along the chain of servers (as I suggested above).