Re: SPOOLED_FILE_DATA table function

Dan,
I think a fair summary is that it's highly unlikely that one is going to
EASILY retrieve underlined characters. Instead it's going to involve
taking multiple rows and deriving whether or not the character is
underlined.

I wonder how one is using this function and why do they care a rat's
posterior if the character is underlined? Perhaps there is a better
alternative, like converting to PDF, HTML, changing from generating a spool
file to something better handled by alternative reporting tools, etc.

On Wed, Dec 31, 2025 at 12:58 PM Dan Bale via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> 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 Of
smith5646midrange@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. ***
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.