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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Running a Native Teradata SQL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Running a Native Teradata SQL
 Login/Join
 
Member
posted
I'm trying to run a Teradata query using the
SQL keyword in WebFOCUS.

..............
SQL
[My Query]
;
TABLE
ON TABLE HOLD AS HOLD1

TABLE HOLD1
PRINT COL1 AND COL2
END
..............

The same query if run in Queryman(SQL client software that comes with Teradata) takes about 3 seconds to run. If I put this in FOCUS it takes about 90 seconds.

I'm not sure if FOCUS runs queries within the SQL key word like a native SQL.

How do I enable this? Any pointers would be greatly appreciated.

Thanks in advance
 
Posts: 4 | Location: Topeka | Registered: September 01, 2004Report This Post
<chris>
posted
I'm not sure why you have the first HOLD rather than just reporting off the original SQLOUT. Unless I am missing something, you are doing SQL to get a dataset. Then you have a TABLE request to HOLD that data. Then you report off the HOLD file. I think the second step is unecessary. Have you tried:

SQL [My Query];
TABLE FILE SQLOUT
PRINT E01 E02
END

Also be aware that any SQL you run on the client supplied software will be the fastest. It has limited GUI and no translation. I hope this helps.
 
Report This Post
Member
posted Hide Post
Chris
Thanks for the response.
Actually my procedure would not just stop here. I would need to join the HOLD file output I get from the SQL with another datasource and then get the resultant output for my report.

The option suggested by you does not give any difference in performance.

I agree with you on the fact that client supplied software will be fast. But underlying WebFOCUS also should be using the same drivers provided by the same client. Plus if 3 seconds performance became 10 seconds I can agree but here it becomes 90 seconds.

Basically I want to know if I can instruct WebFOCUS to pass on the query to the DB to execute it directly.

Thanks!
 
Posts: 4 | Location: Topeka | Registered: September 01, 2004Report This Post
<Grzegorz>
posted
Deepak,

The suffix for Teradata is SQLDBC, so the syntax of passing native query to Teradata looks like this:

SQL SQLDBC
-- Everything from this point to the ';' is sent to the database interface "as it is".
--It is not interpreted by WebFOCUS/iWay Engine in any way
--(you should report the case to IBI Support if it is).

SELECT ... native Teradata statements ...
;
TABLE
ON TABLE HOLD AS hold_file_name FORMAT FOCUS INDEX field_for_join_to
END


Ask your IBI representative about the manual "iWay Adapter Administration ..." which describes more options for Teradata (and for many other DBMS platforms).

Hope this helps
Grzegorz
 
Report This Post
Member
posted Hide Post
Grzegorz
Thanks a bunch for that. It works great now. The syntax that I'm using now is

REMOTE DEST=MYSQLENGINE
-REMOTE BEGIN
SQL SQLDBC
[My Query];
TABLE FILE SQLOUT
PRINT E01 E02
END
 
Posts: 4 | Location: Topeka | Registered: September 01, 2004Report 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     Running a Native Teradata SQL

Copyright © 1996-2020 Information Builders