vvdatasrc/getData: API Failure

[gwilburn]

I modified a data source I'm using for a grid... Now I cannot open the grid in NAB - I get the message above after 30 seconds or so.
However, I can open the app and it works just fine. When I edit the data source itself, everything seems ok and it's relatively fast.

I saw the other post related to this error. I'm in the Portal Logs, but I can't seem to click on the log and see the SQL statement.

[Johnny Major]

Do you have the logging turned on?

Screen Shot 2023-05-12 at 9.33.21 AM.png

[gwilburn]

Yes... but mine doesn't indicate that it's "running"..

Correction... NOW, it does indicate that it's running... HMM.
Logging.png

[gwilburn]

I found a statement with Duration 444477...

SQL is

SELECT count(1) FROM icprtmia JOIN v_pltqty ON v_pltqty.com_nbr = iacom# and v_pltqty.part_nbr = iaprt# JOIN v_dftwhs ON v_dftwhs.com_nbr = iacom# LEFT JOIN v_lastrcpt ON v_lastrcpt.com_nbr = iacom# and v_lastrcpt.part_nbr = iaprt# and v_lastrcpt.whs_nbr = v_dftwhs.dft_whs WHERE (iaactf = '+')

I run this in RUNSQL Scripts and it takes 522 ms

[robert.swanson]

What happens if you change the data source to use a sort sequence of *HEX? This is in the "Advanced" section of the save window...
sort_sequence.jpg

https://www.cnxcorp.com/blog/your-users-need-speed

[gwilburn]

That worked!
I'm going to also sort one of my views... it contains aggregate (max) function.
[Update] - that is not allowed.

So is it OK to leave it with this sort?

[robert.swanson]

You should be fine -- the linked blog post explains what's going on with that setting.

TLDR: For very large files, if you don't care about case-sensitivity in any character fields in your WHERE or ORDER BY clauses, *HEX will typically run faster. The main downside to *HEX is that "aaAA" and "AAaa" are treated as distinctly different values and thus sorted apart from each other, whereas with *LANGIDSHR they're treated as identical.

[gwilburn]

Robert - I'm experiencing a problem with my grid search when using *HEX. The search only seems to work with numbers (even if I type the text observing upper/lower case).
If I change back to *LANGIDSHR my search works perfectly.

Example... my grid shows part numbers with descriptions. One of the descriptions is

Mega Cyborg Hand

With *HEX, I search for "Cyb" and it finds nothing.
With *LANGIDSHR, I search for "cyb" and find two results.

This occurs on 6.1 and 6.2

[robert.swanson]

Can you look at the log and post what the SQL select statement shows for the grid load that's not working?

[gwilburn]

COUNT statement: SELECT count(1) FROM icprtmia JOIN v_dftwhs ON v_dftwhs.com_nbr = iacom# JOIN icbalmie ON iecom# = iacom# and iewhs# = dft_whs and ieprt# = iaprt# JOIN v_pltqty ON v_pltqty.com_nbr = iacom# and v_pltqty.part_nbr = iaprt# LEFT JOIN v_lastrcpt ON v_lastrcpt.com_nbr = iacom# and v_lastrcpt.part_nbr = iaprt# and v_lastrcpt.whs_nbr = v_dftwhs.dft_whs WHERE (iaactf = '+') AND ( (ICPRTMIA.IACOM# = '046') AND ( ( IACOM# LIKE '%CYB%' OR IAPRT# LIKE '%CYB%' OR IA101 LIKE '%CYB%' OR IELOC1 LIKE '%CYB%' OR IELOC2 LIKE '%CYB%' OR IELOC3 LIKE '%CYB%' ) ) )

SELECT iacom# AS com_nbr, iaprt# AS part_nbr, dft_whs, ia101 AS part_desc, iacnv4 AS case_qty, iaum4 AS case_uom, iaptw AS case_wid, iaptl AS case_len, iaptv AS case_vol, iapth AS case_hgt, iaptw AS part_wid, iaptl AS part_len, iapth AS part_hgt, iaptwt AS part_wgt, round(iaptwt * iacnv4, 2) AS case_wgt, ieloc1 AS aisle, ieloc2 AS bin, ieloc3 AS level, rec_date, plt_qty, CASE WHEN ( iaptw <= 0.01 or iaptw <= 0.01 or iaptl <= 0.01 or iaptwt <= 0.01 or rec_date is NULL ) and iarcc8 <> 'RPL' THEN 'Y' ELSE 'N' END AS need_dims, iarcc8, rrn(ICPRTMIA) AS rrn1, rrn(ICBALMIE) AS rrn3 FROM icprtmia JOIN v_dftwhs ON v_dftwhs.com_nbr = iacom# JOIN icbalmie ON iecom# = iacom# and iewhs# = dft_whs and ieprt# = iaprt# JOIN v_pltqty ON v_pltqty.com_nbr = iacom# and v_pltqty.part_nbr = iaprt# LEFT JOIN v_lastrcpt ON v_lastrcpt.com_nbr = iacom# and v_lastrcpt.part_nbr = iaprt# and v_lastrcpt.whs_nbr = v_dftwhs.dft_whs WHERE (iaactf = '+') AND ( (ICPRTMIA.IACOM# = '046') AND ( ( IACOM# LIKE '%CYB%' OR IAPRT# LIKE '%CYB%' OR IA101 LIKE '%CYB%' OR IELOC1 LIKE '%CYB%' OR IELOC2 LIKE '%CYB%' OR IELOC3 LIKE '%CYB%' ) ) ) fetch first 25 rows only optimize for 25 rows

[robert.swanson]

Looks like it's upper-casing "CYB" in the WHERE clause, so no surprise it's returning nothing in *HEX sort.

Per chance do you have the filter field set to upper case the value?

[gwilburn]

This isn't a filter... it is the "Search" field in the grid. I have it set to "contains". And no, it is not uppercase. I typed "cyb".
search.png

[gwilburn]

The log statement looks the same if i change back to *DEFAULT

SELECT iacom# AS com_nbr, iaprt# AS part_nbr, dft_whs, ia101 AS part_desc, iacnv4 AS case_qty, iaum4 AS case_uom, iaptw AS case_wid, iaptl AS case_len, iaptv AS case_vol, iapth AS case_hgt, iaptw AS part_wid, iaptl AS part_len, iapth AS part_hgt, iaptwt AS part_wgt, round(iaptwt * iacnv4, 2) AS case_wgt, ieloc1 AS aisle, ieloc2 AS bin, ieloc3 AS level, rec_date, plt_qty, CASE WHEN ( iaptw <= 0.01 or iaptw <= 0.01 or iaptl <= 0.01 or iaptwt <= 0.01 or rec_date is NULL ) and iarcc8 <> 'RPL' THEN 'Y' ELSE 'N' END AS need_dims, iarcc8, rrn(ICPRTMIA) AS rrn1, rrn(ICBALMIE) AS rrn3 FROM icprtmia JOIN v_dftwhs ON v_dftwhs.com_nbr = iacom# JOIN icbalmie ON iecom# = iacom# and iewhs# = dft_whs and ieprt# = iaprt# JOIN v_pltqty ON v_pltqty.com_nbr = iacom# and v_pltqty.part_nbr = iaprt# LEFT JOIN v_lastrcpt ON v_lastrcpt.com_nbr = iacom# and v_lastrcpt.part_nbr = iaprt# and v_lastrcpt.whs_nbr = v_dftwhs.dft_whs WHERE (iaactf = '+') AND ( (ICPRTMIA.IACOM# = '046') AND ( ( IACOM# LIKE '%CYB%' OR IAPRT# LIKE '%CYB%' OR IA101 LIKE '%CYB%' OR IELOC1 LIKE '%CYB%' OR IELOC2 LIKE '%CYB%' OR IELOC3 LIKE '%CYB%' ) ) ) fetch first 25 rows only optimize for 25 rows

This statement really shouldn't be considered "slow"... It is very fast in the app when I use *DEFAULT

[robert.swanson]

Okay, it appears the global search is upper casing all character strings. We're looking into this.

As a workaround, perhaps you could create a filter over that specific column containing the "Cyborg" text? It would be a bit more efficient to not force the SQL engine to look in every single character column for that string.

[gwilburn]

That works for now... the only problem is that the filter is case-sensitive. The search option is not case-sensitive. With the item description field that can be a problem.