 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] DB2 SQL Passthru' | Login/Join |
| Member |
Novice - Apologies for being stupid .. Iam trying to replace native DB2 TABLE access with a SQL Passthru' which seems to be performing better. Question is, will there be any issues using SQL passthru' over native access ? Any reasoning to make me feel better (or worse) will definitely help ! thanks VjThis message has been edited. Last edited by: Kerry, FOCUS 7.6.5/MVS all output | ||
|
| Virtuoso |
Pass-thru SQL is usually reserved for situations where the WebFOCUS translator cannot or will not process the request efficiently. If you are an expert SQL coder, you may be able to get slightly better performance with pass-thru SQL. However, if you use TABLEF (instead of TABLE) and avoid any DEFINEs, COMPUTEs, or other constructs that WebFOCUS cannot pass directly to the database, DB2 will perform all selection, aggregation, and sorting tasks, and performance should be comparable to pass-thru SQL. You should always turn on the WebFOCUS SQL trace facilities when testing WebFOCUS code against relational databases to ensure the WebFOCUS translator is generating a single SELECT statement and that the database is doing as much of the work as possible.This message has been edited. Last edited by: Dan Satchell, WebFOCUS 7.7.05 | |||
|
| Expert |
To supplement Dan's excellent advice, I would add that, if you have greater expertise in DB2 SQL then pass thru will be easier for you. Combine that with the fact that you can embed DM variables into the SQL then you have very flexible code. Also, do as Dan suggests, trap the SQL from your WF requests, but then put it through DB2EXPLAIN (if it's still about on M/F DB2) to understand what the SQL is doing. If you are not a DB2 DBA then talk to yout local friendly(!) DB2 DBA to ask their advice on the SQL produced. If you need to progress to writing your code in WF/FOCUS then make use of READLIMIT, RECORDLIMIT and XRETRIEVAL to test the FOCUS code against what SQL it produces. Ultimately it will help you refine your coding style to get the best out of the DB2 adapter. T
| |||||||||
|
| Member |
Vj, One more thing... Do not use RECORDLIMIT as this might not restrict the DB2 data coming back, use READLIMIT instead. Also for tracing... 1. SET XRETRIEVAL=OFF - so no actual DB2 records are retrieved 2. Set up the correct trace level. Here is an MVS DB2 example to see the SQL code produced: SET TRACEUSER=ON SET TRACEOFF=ALL SET TRACEON=STMTRACE/DB2/FSTRACE Trace documentation is found in Chapter 7 of the "FOCUS for S/390 Relational Data Adaper Users's Manual". Good luck! Michelle WebFOCUS Production: UNIX 7.7.03M WebFOCUS Test: UNIX EDASERVE 7.7.05 Client 8.0.01 | |||
|
| Expert |
Something else to keep in mind, would be the ongoing support of the code your are writing. If the difference in performance is negligable, then which is the easier to support. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Expert |
The one that is documented   T
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |