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,

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

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

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.

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.

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

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.

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.

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

Ah, a key piece of info.

Works for HTML, doesn't for Excel.

Are you PCHOLDing the excel or HOLDing it ?

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.
  

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 

Do you still get the errors above

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

Have you checked the Excel file ?

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

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.

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 ?

How do I check to see if the file is in the path and accessible?

Sorry for the newbie questions.

Where is the file located ?

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

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.

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.

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.

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.

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,
. . .

The hidden dialogue box has bit me so many times. You wait and wait and wait ...

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,

Will it complete if you just HOLD the output? You may be overwhelming HTML and EXCEL.

quote:

100,000 rows = 51 seconds150,000 rows = no return; just keeps processing.

Dies at 60 seconds, huh? Cf. this posting

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.

Pondog -

Look for a 60-second timeout setting somewhere along the chain of servers (as I suggested above).