Re: QSQPTABL usage as a 1-record table for SQL functions

The SYSDUMMY1 is really much the same as QSQPTABL. AFaIK neither has a "system" delete trigger which prevents CLRPFM from wreaking havoc on any SQL dependent on them; as a system trigger, RMVPFTRG would be prevented. They are AFaIK only protected from deleting the one row, from adding any new rows, and from updating the value in the one field. Thus each TABLE would\should, short of a guarantee, always have just their one row.

On all releases since its origin, the file in QSYS2 is part of OPTION(01) of the OS. On some older releases the objects in SYSIBM are part of PTF(s), and clearing that library to refresh by calling the program that re-creates the objects was somewhat common for me in resolving some issues. That program at some point, started being called as part of the "Extended Base Option" OPTION(01) install processing; the *PGM QSQSYSIBM in QSYS.

The QSQPTABL in QSYS2 served as the implementation object for work like SET and VALUES INTO which evaluate expressions such as SQL scalar and user defined scalar functions, whereby the result was generated as a FETCH INTO of the expression with a SELECT of the single row. However most if not all of the utilization of QSQPTABL by the SQL engine has been replaced by methods which do not require a cursor with a single row TABLE; the opening and closing of the file as a query to evaluate each expression is too expensive as compared to the alternate implementation(s) that now perform that work. On any release using the cursor implementation, having a user-defined query dependent on the row was assured to work as long as the SQL itself was functional for its use; i.e. there would be massively more problems with the SQL on the system than just some user query failing if the one row in QSQPTABL went missing.

Examples use these tables in queries to show how a UDF or other scalar function might evaluate, in a manner which is visible to a typical user; i.e. a SELECT result set displayed by a report writer. That is used in examples because suggesting how to get the data into a variable requires writing a program, and a means to get the variable data to be visualized, and that is just not as generally available to any user as would be a report writer.

BTW if you think the definition of QSQPTABL is odd, just look at field IBMREQD for both that stupid column name with no explanatory comment or a better long column name\alias, and worse, that it is a VARCHAR(1) which takes three bytes to implement, to store the value 'Y' which apparently stands for the English word 'Yes' so it is not even culturally sensitive. ;-)

Regards, Chuck

Alan Campin wrote:

What about SYSDUMMY1 in SYSIBM? Is always there. Just a dummy
table for query.

On Fri, Feb 19, 2010 at 11:06 AM, Dan wrote:

I am looking at using an SQL function on a working field that
is not in a table. I've found some references in the archive
about using QSQPTABL as the from-table to accomplish this.

Because I am looking to use this in a production application, I
am trying to determine how reliable it is for me to assume
that:

1) the QSQPTABL will always be available, and 2) it will always
have ONLY one record.

I searched for QSQPTABL in the v5r4 InfoCenter, and came up
with zero results. When I searched using the "global" search
box in InfoCenter, the results were PSP (Preventive Service
Planning) notices or PTF cover letters that use QSQPTABL in
example code to demonstrate how an SQL function is used, with
no explanation of what QSQPTABL is or why it is used. Of course, the implicit reason is that it is presumed to always
exist on every system and always has one record.

And what a weird table! One record, with one field, a 4-byte
binary having a value of 1. There's no description on the file
object.

Is this table documented by IBM anywhere that indicates its
purpose?