On 26 Sep 2013 10:43, Robert Clay wrote:
<<SNIP>>
I have the logic for the grid that I want to return but I'm having
issues with the UDTF itself. It creates successfully, but returns no
rows. I'm missing something, I'm sure, but since this is the first
time doing this, I need an education.
Here is the current code:
<<SNIP>>
WHERE Sys_DName = UPPER( SCHEMA_NAME )
<<SNIP>>
The syntax checks out okay. Calling it results in a blank grid:
SELECT MYLIB.* FROM TABLE(QGPL.LIBSTATS('SomeLib')) AS MYLIB
The joblog shows SQL0100 - Row not found... so there is something
wrong in the syntax of the function itself.
I can run the SELECT statement for a given schema (replacing
SCHEMA_NAME in the WHERE clause with a literal) and it is
successful.
Any assistance is greatly appreciated.
I expect there is something wrong with the generated code; i.e. a
defect in the SQL processor, not the coded SQL. That Steve was able to
get the UDTF both to create and to function properly seems sufficient
evidence that there is nothing /apparently/ wrong with the source used
to CREATE or the SELECT FROM TABLE() when invoking the compiled UDTF.
There may be a missing PTF or a correction exists only on a newer
release; i.e. I do not recall any release mentioned by Steve, and the OP
states IBM i 6.1 is where the UDTF is not functioning as expected.
FWiW: The use of UPPER is not generally acceptable for the code,
given the system object naming rules; e.g. "LibrName" is different from
both LIBRNAME and "LIBRNAME". The /typical/ library name would not be
at odds with that [mis]coding however.
Again for the /typical/ library name, I would not expect any issues
per the CCSID, but... What is the CCSID and SRTSEQ of the job issuing
the CREATE FUNCTION? And the job invoking the UDTF? The parameter was
not declared with a CCSID specification, and I am unsure what would be
the default effect for the VARCHAR(10) parameter, if there is not an
explicit CCSID; i.e. I am unsure if the Job Default CCSID would be used
to assign as the attribute of the parameter SCHEMA_NAME or if it is
implied FOR BIT DATA given a job CCSID(*HEX). Yet in my experience the
UPPER [aka UCASE] scalar will effect the same result, even with a data
definition of FOR BIT DATA, so the CCSID should not matter. What is the
CCSID of the SYS_DNAME in the catalog VIEW [as adopted\adapted from the
column DBXLIB in QADBXREF]?.
An attempt to circumvent an [apparent defect] issue with the UPPER
scalar function and\or the type mismatch in that predicate could be
possible using a separate local variable... With both the addition of
DECLARE SCHEMA_NAME_C10 CHAR(10) CCSID(37) and the addition of a SET
SCHEMA_NAME_C10=UPPER(SCHEMA_NAME), and then changing the WHERE clause to:
WHERE Sys_DName = SCHEMA_NAME_C10
FWiW: I would also use that /same/ predicate, for the library name,
in each of the derived table expressions in order to maximize the intent
to avoid processing any more rows than necessary by the UDTF
QSYS2.PARTITION_STATISTICS in SYSPSTAT and hoping to minimize the
selection of rows from SYSTABLES. Although I probably also would
reverse the join and code the JOIN with the same LATERAL table-reference
as is defined in the [create view statement] source for
SYSPARTITIONSTAT, instead of referencing that SYSPSTAT catalog VIEW
which is already a JOIN of the same underlying data. And if I had
*ALLOBJ authority, I would reference QADBXREF directly with the
necessary selection to eliminate the join in SYSTABLES too; given the
MQTs are not of interest, and that program-described files are of interest.
midrange.com
