Args for PARTITION_STATISTICS seemingly VARCHAR(10); Oh! If only.

When the PARTITION_STATISTICS first became available, I recall being frustrated that many times I received no output; dismissing the ill-effect without much thought as to why. I recall mostly just deferring to querying SYSPSTAT [SYSPARTITIONSTAT] VIEW to get what I wanted.

Having just had recent dealings with that UDTF again, I realized what had been the cause of the inconsistent effects for my invocations; this time, rather than ignoring the incorrect output and moving-on by using the VIEW instead, I decided to investigate. I found that, almost positively, there is a defect in the procedure that implements the SQL external routine, per an improper treatment of the data received by the SQL for the two input parameters. Note: I am not reporting a defect via this clearly improper channel [i.e. I know this forum is not a means to report a defect; esp. if there is a desire to get a fix\PTF], I am merely informing anyone who [perhaps sometimes already] encounters the same issue with that UDTF. And by reading what I offer, perhaps can learn the cause and how to be sure [to know how] to circumvent, as well as perhaps even something about routine parameters more generally and querying the catalogs about the routines.

For anyone interested:

The constant [aka literal] specification of an apostrophe-delimited character-string is recognized by the SQL as data-type VARCHAR(length); with the length being the number of non-escape characters between the apostrophes as delimiters. For the library name QGPL as a parameter value, for example, if I specified that value as the string-constant 'QGPL' for an argument of a routine [procedure or function] that was declared as VARCHAR(10), then that would be a compatible specification; the compatible argument would be VARCHAR(04).

Thus if the invoked routine [was not overloaded and] accepted a VARCHAR(10) for the first parameter, then the routine should have no difficulty with the VARCHAR(04) input; i.e. function-resolution would find that routine with the compatible parameter. If the clause LANGUAGE C had been specified when creating that PROCEDURE, then the SQL would pass the by-reference value as a C-style null-terminated string, rather than passing the by-reference value as a 2-byte integer prefixed string as is the representation used by the database for the VARCHAR data-type, and similarly used for some other language(s) such as RPG.

So I verified the definitional properties of the procedure PARTITION_STATISTICS; the interface to the external procedure as defined to the SQL by the SQL, first for the input parameters, then for the routine, and then for the external module of the external service program:

select char(p.PARAMETER_NAME, 19) PARAMETER_NAME
, char(p.DATA_TYPE, 17) DATA_TYPE
, dec (p.CCSID, 5) CCSID
, dec (p.CHARLEN, 2) LEN
from sysroutines r join sysparms p
using (specific_schema, specific_name)
WHERE routine_type ='FUNCTION'
and routine_schema='QSYS2'
and routine_name='PARTITION_STATISTICS'
and parameter_mode='IN' /* input data-types */
order by ordinal_position
; -- Report\Output from above query:
-- PARAMETER_NAME DATA_TYPE CCSID LEN
-- SYSTEM_TABLE_SCHEMA CHARACTER VARYING 0 10
-- SYSTEM_TABLE_NAME CHARACTER VARYING 0 10
-- ******** End of data ********

select language as lang
, char(specific_name, 18) as specific_name
, char(external_name, 24) as external_name
from sysroutine a
where routine_schema='QSYS2'
and routine_name='PARTITION_STATISTICS'
; -- Report\Output from above query:
-- LANG SPECIFIC_NAME EXTERNAL_NAME
-- C QDBPSTAT QSYS/QDBSSUDF2(QDBPSTAT)
-- ******** End of data ********

call qcmdexc('dspsrvpgm QSYS/QDBSSUDF2 /*see: QDBPSTAT*/')
; -- not CLE module, but that's just a fib between friends

So best I can infer, irrespective the PARTITION_STATISTICS routine being invoked with 'QGPL' or 'QGPL␠␠␠␠␠␠' [<-- as delimited QGPL blank-padded to ten bytes; shown with six /space/ character-representation glyphs], one should expect the external routine should be able to distinguish betwixt those two constants, yet recognize them as essentially the *same literal\constant specification* with regard to intention\function.

However, example invocations show that not to be the case; i.e. for the CALL to function, the argument when specified as a varying-character-string-constant must be blank-padded to 10-chars. At first blush, I thought odd, that the constant being cast to a fixed-length-character-string of 10-chars with CHAR('QGPL', 10); I realized that is just the /promotion of data-types/ whereby a character string as fixed-length is deemed compatible with a varying-length character string:

create table qtemp.intcol like qsys2.qsqptabl
;
insert into qtemp.intcol
select intcol
from qsys2.qsqptabl
where exists
( SELECT X.* FROM TABLE
( QSYS2.PARTITION_STATISTICS('QGPL ', 'QDDSSRC ')
) AS X )
-- 1 rows inserted in INTCOL in QTEMP.
;
insert into qtemp.intcol
select intcol
from qsys2.qsqptabl
where exists
( SELECT X.* FROM TABLE
( QSYS2.PARTITION_STATISTICS('QGPL' , 'QDDSSRC ')
) AS X )
-- Row not found for INSERT.
;
insert into qtemp.intcol
select intcol
from qsys2.qsqptabl
where exists
( SELECT X.* FROM TABLE
( QSYS2.PARTITION_STATISTICS( VARCHAR('QGPL', 10)
, 'QDDSSRC ' )
) AS X )
-- Row not found for INSERT.
;
insert into qtemp.intcol
select intcol
from qsys2.qsqptabl
where exists
( SELECT X.* FROM TABLE
( QSYS2.PARTITION_STATISTICS( CHAR('QGPL', 10)
, 'QDDSSRC ')
) AS X )
-- 1 rows inserted in INTCOL in QTEMP.
;

So to help explain the issue, I will write my own non-C program [no need to do module+srvpgm for this purpose] to exhibit the actual data passed by the SQL into an external executable, but as external stored procedure versus external user defined table function (UDTF); using Naming(*SYS), default schema and path, the following script for which the routine created can only be referenced\invoked interactively:

call qcmdexc('crtsrcpf qtemp/tmpclsrc mbr(varchararg)')
;
insert into qtemp/tmpclsrc
(srcseq, srcdat, srcdta)
select srcseq, srcdat, srcdta
from table
( values
( 01 , 160526 , 'pgm (&pSysTblSch &pSysTblNam) ' )
, ( 02 , 160526 , 'dcl &pSysTblSch *char 11 ' )
, ( 03 , 160526 , 'dcl &pSysTblNam *char 11 ' )
, ( 04 , 160526 , ' dmpclpgm ' )
, ( 05 , 160526 , ' dspsplf qppgmdmp splnbr(*last)' )
, ( 06 , 160526 , ' dltsplf qppgmdmp splnbr(*last)' )
) as s (srcseq, srcdat, srcdta)
order by srcseq
;
call qcmdexc('crtclpgm varchararg qtemp/tmpclsrc')
;
create procedure varchararg
( SYSTEM_TABLE_SCHEMA IN VARCHAR(10)
, SYSTEM_TABLE_NAME IN VARCHAR(10)
) external name varchararg
no external action not deterministic
language c parameter style general
; -- routine VARCHARARG created into *CURLIB
call varchararg ( 'QGPL ' , 'QDDSSRC ' )
-- '1234567890' , '1234567890'
; -- Output from above CALL
-- Variable … Value Value in Hexadecimal
-- … *...+....1.. * . . . + . . . . 1 .
-- &PSYSTBLNAM … 'QDDSSRC ' D8C4C4E2E2D9C340404000
-- &PSYSTBLSCH … 'QGPL ' D8C7D7D340404040404000
; -- Notice each 11th byte is the x'00' null-character
call varchararg ( 'QGPL' , 'QDDSSRC ' )
-- '1234' , '1234567890'
; -- Output from above CALL
-- Variable … Value Value in Hexadecimal
-- … *...+....1.. * . . . + . . . . 1 .
-- &PSYSTBLNAM … 'QDDSSRC ' D8C4C4E2E2D9C340404000
-- &PSYSTBLSCH … 'QGPL ' D8C7D7D300404040404000
; -- Notice above 5th byte is the x'00' null-character
call varchararg (char('QGPL', 10) , 'QDDSSRC ' )
-- '1234' , '1234567890'
; -- Output from above CALL
-- Variable … Value Value in Hexadecimal
-- … *...+....1.. * . . . + . . . . 1 .
-- &PSYSTBLNAM … 'QDDSSRC ' D8C4C4E2E2D9C340404000
-- &PSYSTBLSCH … 'QGPL ' D8C7D7D340404040404000
; -- Notice each 11th byte is the x'00' null-character