Re: SQLGETWHERE and SQLGETINCEXPR

On 06-Jun-2011 05:17 , Joe Pluta wrote:

Using Chuck's input as guidance, I was doing a little more research
on the QADBXREF file and its associated views. One of those is a
view called SYSINDEXES. For fun and excitement, I tried to duplicate
the view (CRTDUPOBJ) and not surprisingly I can only duplicate it
using a profile with *ALLOBJ authority, not with normal *PUBLIC
authority. But then I saw something interesting. When I created the
view with the right authority and then took a look at the created
object with DSPFD, the associated SQL has an unusual piece to it. You
can do it yourself by doing a CRTDUPOBJ and then doing a DSPFD, but
the relevant bit is this part:

CREATE VIEW SYSINDEXES (INDEX_NAME FOR "NAME" ...
... CASE WHEN IX.DBXIDXSEL <> 'Y' THEN NULL WHEN IX.DBXIDXSELX IS NOT
NULL THEN IX.DBXIDXSELX ELSE TESTLIB.SQLGETWHERE(IX.DBXLIB,IX.DBXFIL)
END ...

Presumably then the CRTDUPOBJ was TOLIB(TESTLIB)? If so, I believe that action may have exposed a minor defect with that catalog VIEW definition, for having failed to library qualify the function. Odd if so, since in the source I seem to recall clearly documenting the need to library qualify any function references; Hmmm. That plus the "view update" code probably improperly updated the library qualification, apparently because the list of "system functions" known to that code does not include SQLGETWHERE; i.e. having been qualified with "TESTLIB" instead of with "QSYS2" or just left unqualified like VARCHAR, INTEGER, STRIP, etc.. I would need to see the original VIEW and then run some tests to be sure about what does and should happen, but I have no access to do so.

Note the call to procedure TESTLIB.SQLGETWHERE. I didn't create that
function! It doesn't actually seem to exist; if I try to call it
myself I get SQLGETWHERE in TESTLIB not found. If I call it
unqualified I get results. This works fine:

SELECT SQLGETWHERE(DBXLIB,DBXFIL) from QADBXREF

Which brings me to my primary question. Maybe the function is in the
system library list, but I can't find it on the system anywhere.
Where would I look to find more information about this function? I
haven't been able to find it anywhere. I looked in SYSFUNCS, but no
luck. Is it some sort of undocumented built-in function?

Hopefully the function by that name is recorded as residing in QSYS2 as Shawn noted is seen on another system; per above, not in TESTLIB due to what I presume to be "defects". FWiW [to include kwds mostly] the physical data is in SYSROUTINE [SYSROUTINES] in the unlikely event the SYSFUNCS VIEW were somehow preventing a query seeing the data.

And the minor corollary question is that the generated VIEW in the
DSPFD doesn't seem to be quite right: certainly as written the query
wouldn't seem to work since there is no SQLGETWHERE in TESTLIB. Maybe
that's just an artifact of DSPFD, though.

DSPFD just shows what the SQL VIEW text\definition updater stored; and that same data should also be in stored in DBXDFN of QADBXREF manifest as VIEW_DEFINITION FOR "TEXT" in SYSVIEWS for that file and library name.

That the VIEW functions is most likely due to the mismatch between the actual definition and the "VIEW definition" shown. However a VIEW can still execute any functional THEN of a case while ignoring others, so given the type\attributes from an existing function were already established, then the VIEW could process rows until the missing function was required [for the THEN path that invokes the system-supplied UDF]. If truly qualified with TESTLIB however, I would expect the CREATE VIEW [as effected by CRTDUPOBJ] would have failed for being unable to effect function resolution; a -204 I suppose, and thus "file not created" as the final error preceding "not duplicated".

Regards, Chuck