 |
|||||
|
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. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to obtains number of inserts.
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to obtains number of inserts. Read-Only TopicGo | Search | Notify | Tools |
| How to obtains number of inserts. | Login/Join |
| Gold member |
Hi. I've been doing some reports with direct SQL, in my case, to DB2. I know that with maintain or modofy we can obtain the number of correct or erroneus inserts/deletetes/modifys with some variables like &INPUT, &CHNGD or &DELTD. I tried these variables with a direct SQL and always get the value 0, so I guess that only works with maintins/modifys... Is there any way to obtain with SQL the correct number of inserts/deletes/updates or eroneus in a table if my sgbd is DB2? Also, is there any way to limit the results with DB2 like the RECORDLIMIT command? Best Regards. ____________________________________________ Ruben Rueda Consultant Information Builders Iberica Web: http://www.eruben.biz Prod: WF 5.3.4 @ Red Hat Enterprise Linux ES 3 (Taroon Update 6) w/Oracle 9i Test: WF 5.3.3 @ SUN Solaris 7 w/Oracle 8i | ||
|
| Virtuoso |
Ruben, Doing direct sql into DB2 gives me a line specifying how many records the transaction affected. Don't know if this is being propagated to any variable though. In any case, this is the only thing I've seen on this subject. As far as limiting the number of records, at the bottom of your sql-request you can spe3cify something like FETCH FIRST 100 RECORDS ONLY. Hope this helps ... GamP
| |||||||||
| ||||||||||
| Virtuoso |
There is a setting to turn this on or off for the adapter (or for the specific query.) From the data administration guide: PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command. Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens. Syntax: How to Obtain the Number of Rows Updated or Deleted ENGINE [DB2] SET PASSRECS {ON|OFF} GamP has the correct answer to your question about limiting records. Use FETCH FIRST n RECORDS ONLY in your SQL. If you are using WF syntax and include a READLIMIT, this translates to a FETCH FIRST, but I don't think that RECORDLIMIT gets translated - I think it is only used internally by the FOCUS engine itself. Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Virtuoso |
Does this mean that you can find out how many records have been updated and also how many have been rejected in the transaction? Or is it just the number of succesful transactions? If the latter, can you also find out how many rejects there were? Would be nice to know..... GamP
| |||||||||
| ||||||||||
| Virtuoso |
This gives all of those statistics. Records processed, successful inserts, successful updates, successful deletes, rejected records. Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Master |
I think Darin is right about the READLIMIT and RECORDLIMIT. It is my understanding that a READLIMIT limits the input and a RECORDLIMIT limits the output. With a RECORDLIMIT most of your processing is done and then the report (or file) is limited. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Expert |
I don't think that PASSRECS options is all it's made out to be. Please see doing a Sql Pass thru Update and Getting a return Code 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 | |||
|
| Virtuoso |
It IS a very simple response. It does what it says (returns number of rows affected) and that's about it. Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to obtains number of inserts.
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to obtains number of inserts.Copyright © 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 |