Running DSPTAP command using SQL

DspTap.sql

-- Purpose: Running DSPTAP command using SQL
-- Version: 1.0
-- Date 17/04/2026
-- Author: Andrea Buzzi

CREATE OR REPLACE FUNCTION SQLTOOLS.DISPLAY_TAPE (
            INDEV VARCHAR(10),
            INTAPE CHAR(6),
            INSEQ_START VARCHAR(10) DEFAULT '*FIRST',
            INSEQ_END VARCHAR(10) DEFAULT '*LAST'
        )
    RETURNS TABLE (
        TAPE CHAR(6),
        SEQUENCE_LABEL VARCHAR(17),
        SEQUENCE_NUMBER INTEGER,
        BLOCKS INTEGER,
        WRITE_DATE VARCHAR(10),
        EXP_DATE VARCHAR(10)
    )
    LANGUAGE SQL
    SPECIFIC SQLTOOLS.DSPTAP
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    NOT FENCED
    SET OPTION ALWBLK = *ALLREAD,
               ALWCPYDTA = *OPTIMIZE,
               COMMIT = *NONE,
               DBGVIEW = *LIST,
               DECRESULT = (31,
               31,
               00),
               DYNDFTCOL = *NO,
               DYNUSRPRF = *USER,
               SQLCURRULE = *STD,
               SRTSEQ = *HEX
    BEGIN
        DECLARE THERESULT INT;
        DECLARE CMD VARCHAR(200);
        CALL QSYS2.END_IDLE_SQE_THREADS();
        VALUES
            'QSYS/DSPTAP DEV(' CONCAT TRIM(INDEV) CONCAT ') VOL(' CONCAT TRIM(INTAPE) CONCAT ') SEQNBR(' CONCAT TRIM(INSEQ_START)
                CONCAT ' ' CONCAT TRIM(INSEQ_END) CONCAT
                ') DATA(*LABELS) OUTPUT(*OUTFILE) ENDOPT(*UNLOAD) OUTFILE(QTEMP/DSPTAP) OUTMBR(*FIRST *REPLACE)' INTO CMD;
        CALL SYSTOOLS.LPRINTF(
            TRIM(CMD)
        );
        VALUES
            QSYS2.QCMDEXC(TRIM(CMD)) INTO THERESULT;
        RETURN
            (
                SELECT RDCTGI,
                       TRIM(SUBSTR(RDTRL1, 5, 17)) AS LABEL,
                       INT(RDLFSQ) AS SEQNBR,
                       INT(SUBSTR(RDTRL1, 54, 7)) AS SIZE,
                       CHAR(DATE('20' CONCAT SUBSTR(RDTRL1, 43, 2) CONCAT '-01-01') + (INT(SUBSTR(RDTRL1, 45, 3)) - 1) DAYS)
                           AS WRITEDATE,
                       CASE
                           WHEN SUBSTR(RDTRL1, 49, 5) = '99999' THEN '*PERM'
                           ELSE
                               CHAR(DATE('20' CONCAT SUBSTR(RDTRL1, 49, 2) CONCAT '-01-01') + (INT(SUBSTR(RDTRL1, 51, 3)) - 1) DAYS)
                       END AS EXPDATE
                    FROM QTEMP.DSPTAP
                    WHERE THERESULT = 1);
    END;
    
    stop;
    
    --example of display entire tape
    SELECT *
    FROM TABLE (
            SQLTOOLS.DISPLAY_TAPE(INDEV => 'FST06', INTAPE => 'EPW60A')
        );
        
    stop;
    
    --example of display tape from sequence number 10 to the last sequence
    SELECT *
    FROM TABLE (
            SQLTOOLS.DISPLAY_TAPE(INDEV => 'FST06', INTAPE => 'EPW60A', INSEQ_START => '10', INSEQ_END => '*LAST')
        )
        
    stop;