Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Automatically passing the program name as a Comment?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] Automatically passing the program name as a Comment?
 Login/Join
 
Gold member
posted
While monitoring the SQL executing on our database from WebFOCUS, I had a idea that it would be really nice if IBI embedded the program generating the SQL as a Comment so when looking at a bad query, you know where it came from?

For example, I would like to see:

SELECT /* mywebfocus.fex */ field1, field2, field3
FROM mytable
WHERE field4 = 123;

-or-

SELECT /* WFC/Repository/mywebfocus.fex */ field1, field2, field3
FROM mytable
WHERE field4 = 123;

When doing SQL PassThrough, you can already do this. The messages display in v$sqlarea like a charm.

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


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Platinum Member
posted Hide Post
If I understand what you are asking for would below work for you?

[CODE]
SELECT '&FOCFEXNAME' , A.*
FROM dual A;

TABLE FILE SQLOUT
PRINT *
END
-EXIT
[/CODE


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Gold member
posted Hide Post
Yes. that is what I'm looking for WebFOCUS to automatically do:

ENGINE SQLORA SET DEFAULT_CONNECTION ODS
SQL SQLORA PREPARE SQLOUT FOR

SELECT /* '&FOCFEXNAME' */ A.*
FROM dual A;
END

TABLE FILE SQLOUT
PRINT *
END
-EXIT


when a FOCEXEC is run and the SQL is created on the fly to be passed to the Data Adpaters and run on the source database. In addition, it would also be great to embed the user's logon as well.

As you pointed out, we can already do these things with SQL PassThru - but, to be complete, it would be a great feature if IBI implemented this as well for non-SQL PassThu FOCEXEC code.


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Gold member
posted Hide Post
I also added the User name:

SELECT /* Run from: '&FOCFEXNAME' Run by: '&IBIMR_user' */ A.*
.....



So, in a Non-SQLPassThru report (using MAS files), is it possible to do this as well?

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


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
do this at the top of the program:
-? &

This will give you all the amper variables you need.
You can then add them into you code...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
Thanks Tom, but not all FEX code/variables are passed to the Data Adapters into SQL to be executed on the DB within SELECT, FROM, WHERE statements when not using SQL PassThru?

I'm looking for the same result (comments displayed in v$sqlarea view) as demonstrated above while using MAS files in WebFOCUS.


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
quote:
So, in a Non-SQLPassThru report (using MAS files), is it possible to do this as well?

This was what I was addressing...
You use Dialogue Manager variables, they can be passed to SQL, and, used in WebFOCUS reports.

"Programming" is a profession. A computer does what the programmer tells it to do. Does COGNOS, MicroStrategy, Business Objects, SAS applications do things automatically???

I don't think so...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
I guess I'm confused Tom because IBI has told us that if you want to pass any comments (hints) in your SQL - you must use SQL PassThru.

So if you are saying we can us DM to do this (great!), I will open a ticket with support.


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
Are you referring to these types of Oracle Hints? Are are you referring to actual -* Comments go here
?


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
Hints (comments) so any DBA looking at a bad query running on their DB can know who executed it and where (what focexec) it came from. The Data Adpater user_name is always generic (webfocus in our case) and not informative when looking in OEM/Toad.

Hints/comments are always preserved in the SQLTEXT column in v$sqlarea performance views making tracing and troubleshooting much easier.


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Expert
posted Hide Post
Opening a case with IBI is the way-to-go...
Since I've never seen that(FOCUS since 1983), I wonder if they'll recommend Resource Analyser...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
So - if you're saying you want it to be automatic e.g. even if WebFOCUS translates TABLE FILE syntax to SQL SELECT syntax, then it sounds like quite a useful NFR (New Feature Request).
So, as Tom suggested, you would need to open a HOTTRACK case with IBI to describe what you have in mind to get the ball rolling.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Gold member
posted Hide Post
Hi Twanette, that's exactly what I'm looking for to see. Seems like all the information is already accessible when translating the TABLE FILE cmds into SQL SELECT statements and what a great place to display such information as a Oracle /* ... */ comment (or similar in other DB platforms).


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Automatically passing the program name as a Comment?

Copyright © 1996-2020 Information Builders