Focal Point
A few words of warning for those planning to upgrade to 7706

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1147058376

December 08, 2014, 09:40 AM
Wep5622
A few words of warning for those planning to upgrade to 7706
Last Thursday afternoon we decided to upgrade our test/development server from 7704M (Windows) to 7706 and boy did we run into a load of troubles!
Thankfully, most of the problems boiled down to only a few core problems, but this is still a show-stopper for us until we can get these fixed.

The cases are all quite particular to our environment, but we're probably not unique in this respect so see if anything applies to your environment (and whether they've been fixed in the meantime):

1) String concatenations in TABLE requests (in a DEFINE in the case we found) to an DB2/AS400 database are now done in SQL.
That is the good news part. They make use of the TRIM(TRAILING ' ' FROM yourfield) function. Now, the database does indeed understand that bit of SQL, so there's no problem there.

The bad news is that in our case, we connect to a remote reporting server on the AS400 that's still at WF 7.6.8 and it does not understand that what it receives is valid SQL! It responds with:
 (FOC14069) SYNTAX ERROR ON LINE 1 AT '('  -- Column name expected
 (FOC1400) SQLCODE IS 14069 (HEX: 000036F5)


2) Master files, or access files rather, created in 7706 are not compatible with a 7704M server. Where 7704M uses KEYS=1 to specify a (primary) key, 7706 uses KEY=FOO_NO. That is a good improvement, but not in a bugfix release, please!. The result of performing a TABLE request on such a master deployed to 7704 is:
 (FOC1135) INVALID KEYWORD KEY. foo/bar LN 5
 ---vvv
 KEY=FOO_NO, $
 (FOC1354) ACCESS FILE RECORD ABSENT, WRONG OR INCOMPLETE FOR SEGMENT  :
 BAR


3) It appears that it's no longer valid to specify database CONNECTION_ATTRIBUTES in a procedure. We use something like this to easily switch a number of reports to use either the production databases or the test databases. For that purpose we include a file like below, but that now causes database connection timeouts after a while - presumably because it doesn't connect to the correct database anymore.
-DEFAULTH &DATABASE = 'FOOBAR_PRD';
-SET &FOOBAR_CONN = DECODE &DATABASE(
- 'FOOBAR_PRD' '&DATABASE/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809',
- 'FOOBAR_TST' '&DATABASE/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809'
-);

ENGINE SQLORA SET CONNECTION_ATTRIBUTES FooBarDb &FOOBAR_CONN


4) Lastly, there are a few places where code tightening uncovered a few syntax errors. I consider that a good thing, but it's worth mentioning because it will cause you problems if you just blindly upgrade your server. For example don't use things like
-SET &TIME=HHMMSS(&TOD);



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 :
December 09, 2014, 11:54 PM
Ram Prasad E
Thanks for sharing ...


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
December 12, 2014, 09:29 AM
Wep5622
It turns out that there is a knob to turn off the new and improved string concatenation behaviour. We added this to our edasprof.prf:
ENGINE EDA SET FEATOPT S_CONCAT OFF


And apparently 7706 is not a bugfix release, but a full-blown new release including new features (and new bugs). IBI does not officially do bugfix release-branches (with the exception of tagging an 'M' onto fixed releases such as 7704M) - IMNSHO, it is time that they do!

We also found another issue:
5) When joining a FOCUS file to an Oracle table, WHERE field NE ''; no longer yields results where it used to in earlier releases.
Instead, using WHERE field IS NOT MISSING seems to get the original results again although it's not necessarily semantically equivalent.

I suspect that this is due to smarter SQL generation and the fact that in Oracle NULL and '' are equivalent (which is totally wrong, but fixing that will probably gain Oracle a boatload of angry customers). Perhaps there's a knob for this too, time will tell (or tech support, rather).


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 :
December 12, 2014, 10:19 AM
susannah
holy cow
if #3 doesn't have a w/a, we're screwed.

Wep, i just tested and it seems to work ok. of course i didn't push it, just printed 10 records... oracle connection string. commented it out in the edasprof, and copied into the test fex.
Can you tell me more details about this issue?

ps: i was 'told', ahem, that 7706 server was 8, in fact.

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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
December 12, 2014, 11:56 AM
j.gross
But not screwed royally. Use symbolic values (=&&vars) (declared in the .mas, referenced in the .acx, and -SET in some level of profile fex) for the connection name.
December 15, 2014, 04:46 AM
Wep5622
We get this (using the command console):
-DEFAULTH &DATABASE = 'FOOBAR_PRD';
-SET &FOOBAR_CONN = DECODE &DATABASE(
- 'FOOBAR_PRD' '&DATABASE/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809',
- 'FOOBAR_TST' '&DATABASE/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809'
-);

ENGINE SQLORA SET CONNECTION_ATTRIBUTES FooBarDb &FOOBAR_CONN

TABLE FILE FOO
PRINT *
WHERE RECORDLIMIT EQ 10;
END

 (FOC1400) SQLCODE IS 12154 (HEX: 00002F7A)
 (FOC1394) CONNECT FAILURE
 : ORA-12154: TNS:could not resolve the connect identifier specified
 L    (FOC1406) SQL OPEN CURSOR ERROR.


Our edasprof.prf contains:
ENGINE SQLORA SET CONNECTION_ATTRIBUTES FooBarDb FOOBAR_PRD/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809
ENGINE SQLORA SET CONNECTION_ATTRIBUTES FooBarDbTst FOOBAR_TST/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809


I wonder what's different in your case?

P.S. Those connection attributes are anonymised. of course - we don't have actual foobar databases Wink


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 :
December 15, 2014, 10:01 AM
j.gross
Check your tnsnames.ora file
December 15, 2014, 11:43 AM
susannah
Wep, try this , first, would you?
comment out the connection in your edasprof
copy the connection, as is, no &vars, into your fex
and run it.
for me, that works.
if it works for you, then its something about evaluating the &FOOBAR_CONN
that may the the troublesome bit
Lemme know...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
December 16, 2014, 10:28 AM
susannah
Have you noticed how IBI is hiding all the manuals now? whats up with that?
you can't go sign in to the tech support site and navigate anywhere to get your manuals anymore.
and when i ask them why? their response is very 'take-it-or-leave-it'




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
December 16, 2014, 11:47 AM
Wep5622
@j.gross:
The tnsnamses.ora contains all the necessary entries.
The same references are in our edasprof.prf, although without the toggle - if there would be an issue with our TNSNAMES, none of our Oracle reports would be able to connect and that's not the case.
Only the reports that use above code to toggle between databases is having these timeout issues. The settings in the code above were copy/pasted from our edasprof.prf and those settings haven't changed.


@susannah:
I could disable those lines in edasprof for a test case, but we only use this include file in a few cases. All the other cases need the CONNECTION_ATTRIBUTES from our edasprof.prf.

I also doubt that it's a matter of evaluation, as that ENGINE setting is a FOCUS statement, while the &FOOBAR_CONN is dialog manager and therefore evaluated first.
One of the things I tried was using an ENGINE SQLORA SET CONNECTION_ATTRIBUTES line with hardcoded adapter-name from the command console and that caused the same timeout problem.


And... I found another issue!

6) If a request containing an Oracle date-time (HYYMDS) results in 0 rows and is used as input for a GRAPH request, the agent performing that request crashes. If there is data, the graph is shown as normal (the agent doesn't crash).
We didn't have this problem in 7704M (or before).

I'm not quite clear on the exact circumstances that trigger this, but it was quite easy to reproduce in a report I'm working on. Naturally I reported the issue with IBI, I'll followup with their response/solution.


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 :
December 16, 2014, 12:55 PM
susannah
hmmm
how did the timeout problem manifest itself?

6) is the datetime used as a filter? or is it a variable being graphed?




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
December 17, 2014, 04:56 AM
Wep5622
The timeout manifested itself as a typical timeout. The browser was waiting for a while and then it showed the error. I suspect that there is some way to find out to what database(-server) it's trying to connect and as whom?

Regarding 6), in our case the datetime is converted to a smart-date as a DEFINE field that's being used for the ordinal axis. It was something like this, except that the below does not crash the agent - interesting!:
SQL SQLORA PREPARE SQLOUT FOR
SELECT 1 as counts, 'foo' as category, trunc(current_date, 'MM') AS monthdate
 FROM dual
 WHERE 1 = 0;
END

DEFINE FILE SQLOUT
 YEARMONTH/YYM	= HDATE(MONTHDATE, 'YYMD');
END
GRAPH FILE SQLOUT
 SUM COUNT/D12SC
 BY CATEGORY
 ACROSS YEARMONTH

ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET 3D OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
END


I'm not sure the above is a correct example though; the code has evolved in a way that does no longer trigger the agent crash


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 :
January 06, 2015, 10:49 AM
Wep5622
And we found more! Frankly, 7706 looks like it's a WRECK.

7) Agents crash when attempting to manipulate a VARCHAR(2048), for example to cut it down to a reasonable size using:
COMPUTE SHORTFLD/A150 = SUBSTR(150, LONGFLD, 1, 150, 150, 'A150');


8) Having a SUM field with NOPRINT and the same field in a SUMMARIZE causes an error about the NOPRINTed field not being recognized (in the SUMMARIZE).
TABLE FILE GGSALES
SUM
	AVE.UNITS NOPRINT
	AVE.DOLLARS
	AVE.BUDUNITS NOPRINT
	AVE.BUDDOLLARS

BY CATEGORY
BY PRODUCT
ON CATEGORY SUMMARIZE
	AVE. UNITS
	AVE. DOLLARS
	AVE. BUDUNITS
	AVE. BUDDOLLARS
END


Remove the NOPRINTs and the TABLE-request suddenly works. Fun if you planned to parametrize the fields you intend to print!

9) Since there doesn't appear to be a Dev Studio 7706 yet, so you're stuck connecting to a 7706 server using Dev Studio 7705. With default styles (as defined in the "Options.../Reporting" tab in DS), when trying to open the Report Painter, you get an error saying: "Unknown keyword (endeflt.sty): ARICONSET".

Fun if you're supposed to use the Report Painter instead of our (preferred) old-fashioned hand-coding!


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 :
January 06, 2015, 11:41 AM
David Briars
quote:
...you can't go sign in to the tech support site and navigate anywhere to get your manuals anymore...
Getting to the manuals is harder than I remember it used to be, but is still possible. Here is how I get to the manuals today:

1. Log into Tech Support

2. Click 'Publications' menu option.

3. Click 'Technical Documentation Library Home' link.

4. Click 'WebFOCUS 8 Technical Library' link under the 'Content Search' heading.

5. In the new window/tab that opens, click any of the 'collection' links, under the 'Collections' heading.

6. Click the 'visit the Bookstore' link.

7. Feast your eyes on all the wonderful PDF versions of your favorite WebFOCUS manuals.
quote:
...and when i ask them why? their response is very 'take-it-or-leave-it'
I hope the person you spoke to was mistaken. I always thought documentation (PDF manuals) was one of the strengths of WebFOCUS.

This message has been edited. Last edited by: David Briars,
January 07, 2015, 03:31 PM
George Patton
quote:
I always thought documentation (PDF manuals) was one of the strengths of WebFOCUS


In the old days IBI actually printed manuals ! I still treasure - and use - my PC FOCUS version 6 manuals. Lots of later stuff missing of course, but the basics are still there ...


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
January 09, 2015, 05:42 AM
Wep5622
quote:
3) It appears that it's no longer valid to specify database CONNECTION_ATTRIBUTES in a procedure. We use something like this to easily switch a number of reports to use either the production databases or the test databases. For that purpose we include a file like below, but that now causes database connection timeouts after a while - presumably because it doesn't connect to the correct database anymore.

-DEFAULTH &DATABASE = 'FOOBAR_PRD';
-SET &FOOBAR_CONN = DECODE &DATABASE(
- 'FOOBAR_PRD' '&DATABASE/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809',
- 'FOOBAR_TST' '&DATABASE/FOOUSR,1A2B3C4D5E6F708192A3B4C5D6E7F809'
-);

ENGINE SQLORA SET CONNECTION_ATTRIBUTES FooBarDb &FOOBAR_CONN


This turned out to be our own error. Obviously, &DATABASE within the &FOOBAR_CONN doesn't get evaluated and hence the connector tries to connect to a database named literally &DATABASE.

Why this worked in 7704M is curious, but probably that was the bug and not 7706 no longer behaving like that.


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 :
January 09, 2015, 07:28 AM
Ram Prasad E
regarding documentation. Yes, we need to navigate multiple pages to reach documentations.
But you can also bookmark this link, no need to login tech support.

http://documentation.informati....com/collections.asp

Thanks,
Ram


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
January 09, 2015, 09:39 AM
susannah
I thought it might be the &vars causing issues
we seem to be working fine, in 7706, using comment switches in front of the ENGINE strings
-SET &cmt_PROD = IF &&server CONTAINS 'PROD' THEN ' ' ELSE '-*';
-SET &cmt_PRODx = IF &cmt_PROD.QUOTEDSTRING EQ '-*' THEN ' ' ELSE '-*';
&cmt_PROD.EVAL ENGINE SQLORA ...etc
&cmt_PRODx.EVAL ENGINE SQLORA ...something else




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID