# Select/Substring  Processing Question

**URL:** <https://techcommunity.softwareag.com/t/select-substring-processing-question/102550>\
**Category:** Forum\
**Tags:** Adabas-Natural, Adabas, Adabas-Add-on-Products, SQL-Access, Mainframe-Integration, CONNX\
**Created:** [March 8, 2013, 9:55pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550 "2013-03-08T21:55:49Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\
**Post date:** [March 8, 2013, 9:55pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/1 "2013-03-08T21:55:49Z")

</div>

We have an sql that looks like this:

select \* from TABLE\_Col where TABLE\_Col= ‘95’ and SUBSTRING (TABLE\_Col.KEY\_Col, 5, 8) = ‘ZZZZ’;.

In the jdbc server log we see the following trace lines:

Mar 08 08:42:10.424 2013@LM\_DEBUG @ UPTIME:(00 09:09:54.491 ) IP:(172.27.58.208 ) TID:(4500 )==\> 2012(W): SQL: select \* from TABLE\_Col where TABLE\_Col= ‘95’ and SUBSTRING (TABLE\_Col.KEY\_Col, 5, 8) = ‘ZZZZ’;.  
Mar 08 08:42:10.424 2013@LM\_DEBUG @ UPTIME:(00 09:09:54.491 ) IP:(172.27.58.208 ) TID:(4500 )==\> 2013(W): Begin Executing Query.  
Mar 08 08:42:10.470 2013@LM\_DEBUG @ UPTIME:(00 09:09:54.537 ) IP:(172.27.58.208 ) TID:(4500 )==\> 2014(W): Query Execution Complete  
Mar 08 08:42:10.502 2013@LM\_DEBUG @ UPTIME:(00 09:09:54.569 ) IP:(172.27.58.208 ) TID:(4500 )==\> 2018(W): Processing CDO: (StatementGetRows).  
Mar 08 08:42:12.236 2013@LM\_DEBUG @ UPTIME:(00 09:09:56.303 ) IP:(172.27.58.208 ) TID:(4500 )==\> 2015(W): Row Fetch Completed - End of Recordset. Rows Retrieved (10).

Please advise as to which step does the substring function is processed:

begin query execution - query execution completed or

StetementsGetRows - Rows Fetch Completed.

We need to study the perfromance of our application.

Thanks

---

<div class="post-metadata">

**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\
**Post date:** [March 8, 2013, 10:04pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/2 "2013-03-08T22:04:04Z")

</div>

Hi Min,

I would recommend that you open a support issue - so we can collect the relevant metadata to answer your question.

The query does not seem to make sense unless your table called “TABLE\_Col” also has a column called “TABLE\_Col”.

In general, the way you are using SUBSTRING disqualifies that part of the expression for optimization.

Assuming the clause in the original query was : SUBSTRING (TABLE\_Col.KEY\_Col, 5, 4)  
It looks like you are trying to filter on a portion of the key without specifying the starting segment of the key.

Its not really possible to take advantage of the descriptor in this way. Are the first 5 bytes of KEY\_Col unknown?

---

<div class="post-metadata">

**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\
**Post date:** [March 11, 2013, 12:32pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/3 "2013-03-11T12:32:12Z")

</div>

Larry,

Yes, the table name is table\_col and the column name is also called table\_col. It is a legacy system that has been around a long time. Not the best at this point but It is what it is.

Yes, the 1st part of the key\_col is unknown. I am not looking into using key\_col as an index. The table\_col column is an index and that is fine in this case to limit the number of rows. If I want to use key\_col as an index then I can build a sub-index for it. No problem here.

My question is where the substring is executed: o the jdbc server or the sql gateway.

If jdbc server then from the log I gather, I like to know where the time spent on te substring is recorded: between begin query execution - excution completed or between statement getrows and fetch rows completed.

We are trying to explain what we see in the jdbc server log.

Thanks.

---

<div class="post-metadata">

**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\
**Post date:** [March 11, 2013, 1:25pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/4 "2013-03-11T13:25:02Z")

</div>

Min,

The substring filtering happens in the JDBC server. In the log, it would be in between Processing CDO: (StatementGetRows) and Row Fetch Completed - End of Recordset. If the criteria was a simple equal comparison, it would occur in the Data Server (what you are calling the SQL Gateway).

You can shift the processing from the JDBC server to the Data Server by using a like clause instead of substring - like this:  
TABLE\_Col like “\_\_\_\_\_ZZZZ%”

This should also give you better performance.

---

<div class="post-metadata">

**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\
**Post date:** [March 11, 2013, 6:16pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/5 "2013-03-11T18:16:49Z")

</div>

Larry,

One last question: the StatementGetRows checkpoint is written when the jdbc server is retrieving the rows from the data server rather than the application server fetching the rows from the jdbc server, right?

Thanks for your ifnormation.  
Min

---

<div class="post-metadata">

**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\
**Post date:** [March 11, 2013, 6:20pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/6 "2013-03-11T18:20:08Z")

</div>

Min,

No, the StatementGetRows is written to the log when the application starts retrieving the rows from the JDBC server.

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\
**Post date:** [April 2, 2021, 3:02pm UTC](https://techcommunity.softwareag.com/t/select-substring-processing-question/102550/7 "2021-04-02T15:02:59Z")

</div>


