[SOLVED] (FOC1400) SQLCODE 8632 error when migrated to WF v.8.1.03
We have recently upgraded from v7.6.11 to v8.1.03 and have been testing our queries. One of the query outputs the following error
(FOC1400) SQLCODE IS 8632 (HEX: 000021B8) XOPEN: 42000 : Microsoft OLE DB Provider for SQL Server: [42000] Internal error: An exp : ression services limit has been reached. Please look for potentially com : plex expressions in your query, and try to simplify them. [] Deferred pr : epare could not be completed
The query worked fine previously. Is there a work around to ignore the limitations?.
Thank you in advance.This message has been edited. Last edited by: WF1326,
WebFOCUS 7703/7705/8105m/8201m/8202m
May 30, 2015, 08:15 AM
eric.woerle
There were a number of adapter improvements between 7.7 and 8. It could be that the sql translation has changed, now causing the sql error you are getting. I would run a sql trace on your query to see what is being generated. If that doesn't help get you some answers, post the trace with your code so we can help look at it with you.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
May 31, 2015, 06:38 PM
Waz
Assuming it got that far, can you turn on SQL tracing to see what is being generated. May give a hint to where to look.
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!
June 01, 2015, 11:55 AM
WF1326
Thank you for replies Eric and Waz....
I have used the following trace commands
SET TRACEOFF = ALL SET TRACEON = SQLAGGR//CLIENT SET TRACEON = STMTRACE//CLIENT SET TRACEON = STMTRACE/2/CLIENT SET TRACEUSER = ON SET XRETRIEVAL = OFF
In the SQL statements, i could not see any possible errors; except for complex and huge case statements being generated. No joins involved.
I had to mention that inside the fex, we were first defining several columns and then getting the data from SQL Server. I am able to get the required result, no errors, with one possible workaround i.e) by holding the required data first and then defining the columns later. This improved the performance as well.
Sadly, our previous way of querying had been used in several fex's and it is hard to find each one and make changes. So, i am looking for an alternate way of looking into the issue.
Please let me know if there are any issues with my trace statements or needed to try a different way.
WebFOCUS 7703/7705/8105m/8201m/8202m
June 01, 2015, 01:49 PM
Francis Mariani
Please post the result of the SQL trace (and probably the WebFOCUS code) so we can help you further...
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
June 01, 2015, 02:17 PM
WF1326
13.00.32 AE SELECT 13.00.32 AE (CASE ((CASE (SUBSTRING((CASE WHEN ((CASE WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '101') AND (T1."STATPORT_CDE" = 'DCSH')) 13.00.32 AE THEN '10Stock Futures' WHEN ((T1."IP_PORT_CDE" = '101') AND 13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."STATPORT_CDE" = 'BOND')) 13.00.32 AE THEN '00TCIs' WHEN ((T1."IP_PORT_CDE" IN('101', '102')) AND 13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."PORT_CDE" NOT IN('NON$', 13.00.32 AE 'FS1', 'FS2')) AND (T1."STATPORT_CDE" = 'TCI')) THEN '00TCIs' 13.00.32 AE WHEN (T1."IP_PORT_CDE" = '202') THEN '00Misc. Accts. 13.00.32 AE Receivable*' WHEN (T1."IP_PORT_CDE" = '134') THEN '00Cash in 13.00.32 AE Bank*' WHEN ((T1."INVESTOR_CDE" = 'NLTC') AND (T1."IP_PORT_CDE" 13.00.32 AE = '102') AND (T1."PORT_CDE" = 'MGD') AND (T1."TAX_EXEMP_TXT" = 13.00.32 AE 'Taxable/US Treas Instrumnt')) THEN '04Government' WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '102') AND (T1."PORT_CDE" NOT IN('CNVF', 13.00.32 AE 'FS1', 'FS2'))) THEN '01Public Bonds' WHEN ((T1."IP_PORT_CDE" = 13.00.32 AE '103') AND (T1."PORT_CDE" NOT IN('FS1', 'FS2'))) THEN '02Public 13.00.32 AE Preferred Stock' WHEN (T1."IP_PORT_CDE" = '104') THEN 13.00.32 AE '03Private Placement Notes' WHEN (T1."IP_PORT_CDE" = '105') 13.00.32 AE THEN '04Private Preferred Stock' WHEN (T1."IP_PORT_CDE" 13.00.32 AE IN('106', '107')) THEN '05Non J.V. Mortgage Loans' WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '123') AND (T1."PORT_CDE" <> 'EMER')) THEN 13.00.32 AE '16Public High Yield Bonds' WHEN ((T1."IP_PORT_CDE" IN('303', 13.00.32 AE '304')) AND (T1."INVESTOR_CDE" = 'NLTC')) THEN '17Private 13.00.32 AE Mezzanine' WHEN (T1."PORT_CDE" = 'CNVF') THEN '21Public 13.00.32 AE Converts Fixed' WHEN (T1."IP_PORT_CDE" = '303') THEN 13.00.32 AE '19Mezzanine' ELSE '????' END) <> '????') THEN (CASE WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '101') AND (T1."STATPORT_CDE" = 'DCSH')) 13.00.32 AE THEN '10Stock Futures' WHEN ((T1."IP_PORT_CDE" = '101') AND 13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."STATPORT_CDE" = 'BOND')) 13.00.32 AE THEN '00TCIs' WHEN ((T1."IP_PORT_CDE" IN('101', '102')) AND 13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."PORT_CDE" NOT IN('NON$', 13.00.32 AE 'FS1', 'FS2')) AND (T1."STATPORT_CDE" = 'TCI')) THEN '00TCIs' 13.00.32 AE WHEN (T1."IP_PORT_CDE" = '202') THEN '00Misc. Accts. 13.00.32 AE Receivable*' WHEN (T1."IP_PORT_CDE" = '134') THEN '00Cash in 13.00.32 AE Bank*' WHEN ((T1."INVESTOR_CDE" = 'NLTC') AND (T1."IP_PORT_CDE" 13.00.32 AE = '102') AND (T1."PORT_CDE" = 'MGD') AND (T1."TAX_EXEMP_TXT" = 13.00.32 AE 'Taxable/US Treas Instrumnt')) THEN '04Government' WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '102') AND (T1."PORT_CDE" NOT IN('CNVF', 13.00.32 AE 'FS1', 'FS2'))) THEN '01Public Bonds' WHEN ((T1."IP_PORT_CDE" = 13.00.32 AE '103') AND (T1."PORT_CDE" NOT IN('FS1', 'FS2'))) THEN '02Public 13.00.32 AE Preferred Stock' WHEN (T1."IP_PORT_CDE" = '104') THEN 13.00.32 AE '03Private Placement Notes' WHEN (T1."IP_PORT_CDE" = '105') 13.00.32 AE THEN '04Private Preferred Stock' WHEN (T1."IP_PORT_CDE" 13.00.32 AE IN('106', '107')) THEN '05Non J.V. Mortgage Loans' WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '123') AND (T1."PORT_CDE" <> 'EMER')) THEN 13.00.32 AE '16Public High Yield Bonds' WHEN ((T1."IP_PORT_CDE" IN('303', 13.00.32 AE '304')) AND (T1."INVESTOR_CDE" = 'NLTC')) THEN '17Private 13.00.32 AE Mezzanine' WHEN (T1."PORT_CDE" = 'CNVF') THEN '21Public 13.00.32 AE Converts Fixed' WHEN (T1."IP_PORT_CDE" = '303') THEN 13.00.32 AE '19Mezzanine' ELSE '????' END) WHEN ((CASE WHEN 13.00.32 AE (T1."IP_PORT_CDE" = '207') THEN '65Private Equity' WHEN 13.00.32 AE (T1."IP_PORT_CDE" = '210') THEN '62Limited Partnerships' WHEN 13.00.32 AE (T1."IP_PORT_CDE" IN('214', '216', '209')) THEN '65Private 13.00.32 AE Equity' WHEN ((T1."IP_PORT_CDE" = '304') AND (T1."INVESTOR_CDE" 13.00.32 AE <> 'NLTC')) THEN '67Private Mezzanine' WHEN (T1."IP_PORT_CDE" = 13.00.32 AE '137') THEN '66Currency Swaps' WHEN (T1."IP_PORT_CDE" = '217') 13.00.32 AE THEN '64Sec. Dept. Pub Stock' WHEN (T1."IP_PORT_CDE" = '219') 13.00.32 AE THEN '08Fixed Income Partnerships' WHEN ((T1."IP_PORT_CDE" = 13.00.32 AE '222') AND (T1."PORT_CDE" IN('HYLD', 'HY2'))) THEN '16Public 13.00.32 AE High Yield Bonds' ELSE '????' END) <> '????') THEN (CASE WHEN 13.00.32 AE (T1."IP_PORT_CDE" = '207') THEN '65Private Equity' WHEN 13.00.32 AE (T1."IP_PORT_CDE" = '210') THEN '62Limited Partnerships' WHEN 13.00.32 AE (T1."IP_PORT_CDE" IN('214', '216', '209')) THEN '65Private 13.00.32 AE Equity' WHEN ((T1."IP_PORT_CDE" = '304') AND (T1."INVESTOR_CDE" 13.00.32 AE <> 'NLTC')) THEN '67Private Mezzanine' WHEN (T1."IP_PORT_CDE" = 13.00.32 AE '137') THEN '66Currency Swaps' WHEN (T1."IP_PORT_CDE" = '217') 13.00.32 AE THEN '64Sec. Dept. Pub Stock' WHEN (T1."IP_PORT_CDE" = '219') 13.00.32 AE THEN '08Fixed Income Partnerships' WHEN ((T1."IP_PORT_CDE" = 13.00.32 AE '222') AND (T1."PORT_CDE" IN('HYLD', 'HY2'))) THEN '16Public 13.00.32 AE High Yield Bonds' ELSE '????' END) WHEN ((T1."IP_PORT_CDE" = 13.00.32 AE '206') AND (T1."PORT_CDE" NOT IN('FN', 'FS1', 'FS2', 'XXXX', 13.00.32 AE 'GTE', 'ME1', 'SE1', 'EMER', 'CNVE'))) THEN '31Public Stock' 13.00.32 AE WHEN ((T1."IP_PORT_CDE" = '206') AND (T1."PORT_CDE" IN('GTE', 13.00.32 AE 'ME1', 'SE1'))) THEN '32Tactical Stock' WHEN ((T1."IP_PORT_CDE" 13.00.32 AE IN('206', '218')) AND (T1."DER_TYPE_CDE" = '03')) THEN 13.00.32 AE '33Covered Calls**' WHEN ((T1."IP_PORT_CDE" IN('206', '218')) 13.00.32 AE AND (T1."DER_TYPE_CDE" = '39') AND (T1."EFF_CNTRY_CDE" = 13.00.32 AE 'USA')) THEN '37Equity Swaps-Replication' WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '218') AND (T1."PORT_CDE" IN('LC', 'MID', 13.00.32 AE 'SC'))) THEN '34Futures' WHEN (T1."IP_PORT_CDE" = '137') THEN 13.00.32 AE '66Curency Swaps' WHEN (T1."PORT_CDE" = 'CNVE') THEN '35Public 13.00.32 AE Converts Equity' WHEN ((T1."IP_PORT_CDE" = '206') AND 13.00.32 AE (T1."PORT_CDE" = 'FN')) THEN '56Foreign - Developed Markets' 13.00.32 AE WHEN ((T1."IP_PORT_CDE" IN('222', '206', '123')) AND 13.00.32 AE (T1."PORT_CDE" = 'EMER')) THEN '57Foreign - Emerging Markets' 13.00.32 AE WHEN (T1."PORT_CDE" IN('FS1', 'FS2')) THEN '58Foreign - 13.00.32 AE Sub-advised' WHEN ((T1."IP_PORT_CDE" IN('206', '218')) AND 13.00.32 AE (T1."DER_TYPE_CDE" = '39') AND (T1."EFF_CNTRY_CDE" <> 'USA')) 13.00.32 AE THEN '59Equity Swaps-Replication' WHEN (T1."IP_PORT_CDE" 13.00.32 AE IN('201', '203')) THEN '61Real Estate Equities' WHEN 13.00.32 AE (T1."IP_PORT_CDE" = '205') THEN '41REIT Stock' WHEN 13.00.32 AE ((T1."IP_PORT_CDE" = '213') AND ((CASE ((CASE WHEN 13.00.32 AE (T1."PORT_CDE" IN('XX', 'SML')) THEN 'SC' WHEN (T1."PORT_CDE" = 13.00.32 AE 'BASE') THEN 'B ' WHEN (T1."PORT_CDE" = 'REIT') THEN 'RE' WHEN 13.00.32 AE (T1."IP_PORT_CDE" = '205') THEN 'RE' ELSE T1."PORT_CDE" END)) 13.00.32 AE WHEN ' ' THEN 'No STKPORT_CODE' WHEN 'AG' THEN 'Agressive 13.00.32 AE Growth' WHEN 'ASA' THEN 'Asian Stock' WHEN 'B ' THEN 'Base' 13.00.32 AE WHEN 'CNVE' THEN 'Convertibles - Equity' WHEN 'COMD' THEN 13.00.32 AE 'Strategic Commodities' WHEN 'DET' THEN 'Directed Equity 13.00.32 AE Tactical' WHEN 'EM' THEN 'Emerging Market' WHEN 'EMER' THEN 13.00.32 AE 'Emerging Market' WHEN 'FN' THEN 'Foreign' WHEN 'FS1' THEN 13.00.32 AE 'Foreign' WHEN 'FS2' THEN 'Foreign' WHEN 'GTE' THEN 'General 13.00.32 AE Tactical Equity' WHEN 'IN' THEN 'S & P 500 Index' WHEN 'LC' 13.00.32 AE THEN 'Large Cap' WHEN 'ME1' THEN 'MSA Directive Equity 1' WHEN 13.00.32 AE 'ME2' THEN 'MSA Directive Equity 2' WHEN 'MID' THEN 'Mid Cap' 13.00.32 AE WHEN 'MLPT' THEN 'Master Limited Partnerships' WHEN 'MLRE' THEN 13.00.32 AE 'Mortgage Loan Real Estate' WHEN 'MM' THEN 'Multifactor Market' 13.00.32 AE WHEN 'MP' THEN 'Multifactor Plus' WHEN 'MS' THEN 'Morgan 13.00.32 AE Stanley Loan' WHEN 'NIS' THEN 'Foreign' WHEN 'OS' THEN 'OilThis message has been edited. Last edited by: WF1326,
June 01, 2015, 02:20 PM
Francis Mariani
That certainly doesn't look efficient! Let's see the WF code...
Also, here are the trace commands I use. SET TRACESTAMP to OFF so that the time is not added to the trace - makes it a lot easier to copy/paste the SQl into a SQL client.
-*** Set up SQL tracing
-*** Deactivate SQL tracing
SET TRACEOFF = ALL
-*** Show Commands and data exchange between thephysical and
-*** the logical layers of the data adapter
-*SET TRACEON = SQLCALL
-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS
-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT
-*** Show Optimization information
SET TRACEON = SQLAGGR//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
-*** Set trace to output file
-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt
-*** Activate SQL tracing
SET TRACEUSER = ON
-RUN
This message has been edited. Last edited by: Francis Mariani,
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
June 01, 2015, 02:31 PM
j.gross
As the message says, you need to simplify the SQL query.
You didn't post your WF code, and your trace is truncated. It appears you issue a set of DEFINEs against your SQL Server source, and your TABLE request pulls columns that are the result of a complex cascade of defined items. (Hence the heavily nested, repetitious CASE structures in the generated SQL)
See whether you can just pull the underlying real fields, hold them, and then issue the defines against the HOLD file. -- Much depends on whether you are utilizing the defined fields for screening and aggregation. Since the code you posted is cut off before the FROM clause, we cannot really tell.
- Jack Gross WF through 8.1.05
June 01, 2015, 02:54 PM
WF1326
Jack,
I agree with you and, as mentioned previously, i actually got it working by holding the required data first and then defining the columns later. This improved the performance as well.
Sadly, our old way of querying had been used in several fex's and it is hard to find each one of them and make changes.
Also, the program did not return an errors until we switched to WebFocus V8. So just wondering whether there is a quick fix , like a set statement, to ignore any limitations.
Sorry for the trace being truncated. The trace is like 3500 lines and I think there is a limit to the number of lines being posted in a message.
WebFOCUS 7703/7705/8105m/8201m/8202m
June 01, 2015, 04:30 PM
WF1326
Hey.. i found a solution to my issue.
Just need to add an optimization setting for aggression control on DEFINE statements
SQL SQLMSS SET OPT NOAGGR
Thank you for your replies and suggestions.
WebFOCUS 7703/7705/8105m/8201m/8202m
June 01, 2015, 06:19 PM
j.gross
Yes, those passive-aggressive Defines can get quite unruly.
June 01, 2015, 06:37 PM
Waz
Francis,
Thanks for the SQL Tracing code.
Never can find it when you need it.
Also good to know that you can turn off the timestamp on the 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!
June 02, 2015, 08:46 AM
jgelona
quote:
Originally posted by Waz: Francis,
Thanks for the SQL Tracing code.
Never can find it when you need it.
Also good to know that you can turn off the timestamp on the SQL.
I have those stored in a file name sqltrace.fex and use a -INCLUDE whenever I need them.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
June 02, 2015, 09:29 AM
Francis Mariani
-*** Set trace to output file
-SET TRACEUSER=C:\ibi\apps\baseapp\my_trace_file.txt
This is something I learned about only recently - you can redirect the SQL trace to a file on the reporting server.
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