Author Topic: ODWek REST - Search with SQL - Segment start and stop date  (Read 1147 times)

Andreas Baaserud Hauge

  • Jr. Member
  • **
  • Posts: 46
    • View Profile
ODWek REST - Search with SQL - Segment start and stop date
« on: March 08, 2023, 01:28:38 AM »
Hi,

In the documentation https://cmod.wiki/dox/RESTapiDoc/CMOD_10_5_REST_API_Documentation.html chapter 3.5.2. Search with SQL it says
Quote
When using search with SQL, it is imperative you include a segment start and stop date when available. The format of the start/stop dates is %Y-%m-%d. Failure to include a segment feild will result in a search across all application group data tables severly impacting system performance.
My question is, how do I know what segment start and stop date to use?

Quote
Failure to include a segment feild will result in a search across all application group data tables severly impacting system performance.
Hence that quote, it seems like I should be able to know what segment start and stop date I can use for a query.

In this sample request, the startdate and stopdate spans over 27 years. 27 years of records seems a bit odd to search through if the point of segment startdate and stopdate is to narrow the search down to increase performance.
Code: [Select]
POST /cmod-rest/v1/hits/ HTTP/1.1
usi-date: 2020-02-25T00:31:30Z
Authorization: CMODSharedKey externpool1-P0mFoCU5H83lN9uQcRUA:5au9U9epefygVH22mKfR8yGkS+XiA0D8CDmcOvZrt58=
Content-Type: application/json; charset=UTF-8
Host: localhost
Content-Length: 191

{
  "startdate" : "1993-01-01",
  "stopdate" : "2020-02-11",
  "query" : "where name like 'VILL%'",
  "folder" : "Baxter Bay CC Statements",
  "applicationgroup" : "Baxter Bay Credit"
}
ABH

rjrussel

  • Full Member
  • ***
  • Posts: 137
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #1 on: March 08, 2023, 09:15:01 AM »
Andreas,

This is a bad example. Will update the docs to make this more meaningful.

Thank you,
Rob

Andreas Baaserud Hauge

  • Jr. Member
  • **
  • Posts: 46
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #2 on: March 08, 2023, 12:23:15 PM »
Thanks Russel,

Do you mind also explain how we can use the segment startdate and stopdate?
I dont get how I should get that value to make the search more efficient.
ABH

rjrussel

  • Full Member
  • ***
  • Posts: 137
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #3 on: March 09, 2023, 08:32:49 AM »
Why do you need to search with SQL?

Andreas Baaserud Hauge

  • Jr. Member
  • **
  • Posts: 46
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #4 on: March 09, 2023, 11:32:03 AM »
Good question, is it more efficient to not use the Search with SQL and rather use /cmod-rest/v1/hits/<folder> ?

Cant see we specify segment startdate and stopdate in /cmod-rest/v1/hits/<folder> search.
in the background maybe the segment startdate and stopdate are used ? do you know what those values would typically be?
ABH

rjrussel

  • Full Member
  • ***
  • Posts: 137
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #5 on: March 09, 2023, 11:46:08 AM »
Sounds like you don't search with SQL. Just user /cmod-rest/v1/hits/<folder> and specify a date field (that is defined as a segment field in the CMOD admin) as part of your search criteria and you should be good. CMOD will handle the rest for you.

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #6 on: March 09, 2023, 11:49:10 AM »
I'd like to request robust SQL support with access to all fields in the table, for those of us who do "interesting" things.  :)

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

Andreas Baaserud Hauge

  • Jr. Member
  • **
  • Posts: 46
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #7 on: March 09, 2023, 12:08:54 PM »
CMOD will handle the rest for you.

Thats fine, I just dont get how this work.
I have a segment date field, when I use startdate = 2023-02-25 I still get hits where I have records contains date = 2021-08-04

Searching without SQL seems to be extremely slow compared to Search with SQL.
Did a few test request now.
  • Search with SQL = 59ms
  • Search without SQL = 1677ms. Even up to 3s
Total number of hits = 1

If you know how we can use segment startdate and stopdate in Search with SQL - I'd very much like to know :D
« Last Edit: March 09, 2023, 01:12:30 PM by Andreas Baaserud Hauge »
ABH

rjrussel

  • Full Member
  • ***
  • Posts: 137
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #8 on: March 10, 2023, 09:00:41 AM »
I'd like to request robust SQL support with access to all fields in the table, for those of us who do "interesting" things.  :)

-JD.

So not just SQL support. You want it robust? :)

I hereby certify all current SQL support as robust. There you go. Ask and you shall receive.

BTW: All kidding aside. What fields do you not believe you have access to via SQL?

rjrussel

  • Full Member
  • ***
  • Posts: 137
    • View Profile
Re: ODWek REST - Search with SQL - Segment start and stop date
« Reply #9 on: March 10, 2023, 09:09:20 AM »
CMOD will handle the rest for you.

Thats fine, I just dont get how this work.
I have a segment date field, when I use startdate = 2023-02-25 I still get hits where I have records contains date = 2021-08-04

Searching without SQL seems to be extremely slow compared to Search with SQL.
Did a few test request now.
  • Search with SQL = 59ms
  • Search without SQL = 1677ms. Even up to 3s
Total number of hits = 1

If you know how we can use segment startdate and stopdate in Search with SQL - I'd very much like to know :D

The short answer is CMOD uses the segment startdate and stopdate to limit what tables it searches across. If an AG has 100 tables in it, using a segment field can help significantly. If you only have one table for a given application group this will not of course help much.

So, searching a folder with a date field that is also defined as a segment field will cause cmod to first identify what data tables fall in the given range and then performs a search only against those tables.