In my Tibco process I have logic that map SQL query output only if the query returns less than 1000 records from Tibco "Direct SQL" or Tibco "JDBC Query" activity.
For now I am just running same query 2 times:
1. Select count(*) AS Count FROM my_table WHERE my_table.foo = 'bar'
2. if query 1 result less than 1000 I call same query for getting all the rows Select my_table.* FROM my_table WHERE my_table.foo = 'bar'
The query is pretty heavy and I want to run it only once for performance purpose.
I found the solution from SQL side in the https://stackoverflow.com/questions/243782/need-a-row-count-after-select-statement-whats-the-optimal-sql-approach
I can use query like:
SELECT my_table.my_col, count(*) OVER() AS Count FROM my_table WHERE my_table.foo = 'bar'
The problem is that adding count(*) to the query is also affecting performance.
I also can map result to "Map Data" activity and then use count($Map-Data/pfx:my_element/) but I prefer to avoid additional not required maping for performance purpose.
The Tibco "Direct SQL" and Tibco "JDBC Query" are using Oracle (ojdbc7.jar) and DB2 (jt400.jar) drivers.
Is there any way to get the query output row count from tibco side without adding count to the query output?
I have posted same question in stackoverflow https://stackoverflow.com/questions/49882100/how-to-get-row-count-from-tibco-direct-sql-or-jdbc-query-activity