Re: UDTF (or similar) to retrieve data from SYSPARTITIONSTAT

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.