RE: SPOOLED_FILE_DATA table function -- MIDRANGE-L

Rob,

Again, recalling from vague memory, when printer files use underline attributes for certain text, DSPSPLF and, apparently, CPYSPLF CTLCHAR(*NONE) will show the line with the underline attributes and not the line with the text. Rereading the OP, I'm not certain what the poster intended to say and I wonder if the goal was to actually retrieve the line with the text and not the line with the underline attributes. On that part, I agree with you and don't see any value with extracting the text attributes.

- Dan Bale

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Wednesday, December 31, 2025 2:15 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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. ***