Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] (FOC1400) SQLCODE 8632 error when migrated to WF v.8.1.03
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] (FOC1400) SQLCODE 8632 error when migrated to WF v.8.1.03
 Login/Join
 
Platinum Member
posted
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

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6275 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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 'Oil

This message has been edited. Last edited by: WF1326,
 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Yes, those passive-aggressive Defines can get quite unruly. Wink
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.

Good One


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6275 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
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.
 
Posts: 958 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
-*** 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] (FOC1400) SQLCODE 8632 error when migrated to WF v.8.1.03

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.