Re: SPOOLED_FILE_DATA table function

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Dan

Just curious, because I suspect you know that you can get the source 
using the ACS Schemas tool (some might not, and I haven't looked at 
Simon's article yet) - I went to the SYSTOOLS library, found the table 
function, right-clicked it, clicked on the *Generate SQL* option - there 
are options for saving to PC file or IFS or source file

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.7gD71REH.O5Bb0TNm@centurylink.net> CAAC 
(COMMON Americas Advisory Council) IBM Influencer 2023


On 12/31/2025 11:58 AM, Dan Bale via MIDRANGE-L wrote:
> Because it's slow around here today, I dug this up from Simon Hutchinson's blog.  Here is what I used to extract the SQL source from the SPOOLED_FILE_DATA table function.  I noticed that the CPYSPLF command used in the function did not specify the CTLCHAR parameter, so it must default to *NONE.
>
> CREATE TABLE QTEMP.OBJECTS (
>      OBJECT_SCHEMA VARCHAR(258),
>      OBJECT_NAME VARCHAR(258),
>      SQL_OBJECT_TYPE CHAR(10)
> ) ;
>
> CL: CLRPFM QTEMP/OBJECTS;
>
> INSERT INTO QTEMP.OBJECTS
>      VALUES('SYSTOOLS','SPOOL_FILE','FUNCTION')
> ;
>
> -- The output source file and member must already exist prior to calling GENERATE_SQL_OBJECTS:
> CL: CRTSRCPF FILE(DBALE/SQLGENSRC) RCDLEN(112) TEXT('SQL generated source');
> CL: ADDPFM   FILE(DBALE/SQLGENSRC) MBR(SPLFILDTAF) TEXT('SPOOLED_FILE_DATA table function source') SRCTYPE(SQLGENSRC) ;
>
> CALL QSYS2.GENERATE_SQL_OBJECTS(
>      SYSTEM_TABLE_NAME => 'OBJECTS',
>      SYSTEM_TABLE_SCHEMA => 'QTEMP',
>      DATABASE_SOURCE_FILE_NAME => 'SQLGENSRC',
>      DATABASE_SOURCE_FILE_LIBRARY_NAME => 'DBALE',
>      DATABASE_SOURCE_FILE_MEMBER => 'SPLFILDTAF',
>      STATEMENT_FORMATTING_OPTION => '1',
>      REPLACE_OPTION => '1',
>      NAMING_OPTION => 'SQL',
>      COMMENT_OPTION => '1',
>      LABEL_OPTION => '1',
>      HEADER_OPTION => '1',
>      TRIGGER_OPTION => '0',
>      CONSTRAINT_OPTION => '0',
>      CREATE_OR_REPLACE_OPTION => '1',
>      QUALIFIED_NAME_OPTION => '0'
> ) ;
>
> - Dan Bale
>
> -----Original Message-----
> From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Dan Bale via MIDRANGE-L
> Sent: Wednesday, December 31, 2025 12:04 PM
> To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxxxxxxxx>
> Cc: Dan Bale<dan.bale@xxxxxxxxxxxxxxxxxxxxx>
> Subject: RE: SPOOLED_FILE_DATA table function
>
> It's been so long since I've dealt with overprinting.  Isn't this the same behavior when using CPYSPLF?  Or maybe it's only DSPSPLF?
>
> Found this in the documentation:
> This function is provided in the SYSTOOLS schema as an example of how spooled file data can be returned by embedding the CPYSPLF CL command in an SQL table function. Similar to other Db2 for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.
>
> I don't have any quick means of generating a spool file with overprinting, so this is all WAG, but I wonder whether SPOOLED_FILE_DATA's usage of CPYSPLF uses CTLCHAR(*NONE) and this might cause overprinting to be ignored when copying the spool file.  My advice to you would be to find a spool file with overprinting and see if the CPYSPLF output is different using CTLCHAR(*NONE) vs. CTLCHAR(*FCFC).  If you get all the lines with the latter, extract the SQL source for SPOOLED_FILE_DATA into a newly named source, change CPYSPLF to use CTLCHAR(*FCFC), create the table function as SPOOLED_FILE_DATA_FCFC.
>
> - Dan Bale
>
> -----Original Message-----
> From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Ofsmith5646midrange@xxxxxxxxx
> Sent: Wednesday, December 31, 2025 11:41 AM
> To: 'Midrange Systems Technical Discussion'<midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: SPOOLED_FILE_DATA table function
>
> I have some reports that contain overstamped lines (same line printed twice) which is used for bolding or underlining certain fields in the line.  When I use the SQL function SPOOLED_FILE_DATA, it drops these lines.  Is this a flaw/bug in SPOOLED_FILE_DATA or am I doing something wrong with my SQL?  I am running 7.3.
>
> *** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***