IBM i Automation

Automate Excel and CSV Reporting Directly from IBM i

Build a controlled IBM i reporting workflow with SYSTOOLS.GENERATE_SPREADSHEET, including reusable SQL definitions, protected IFS output, scheduling, delivery, retention, privacy, diagnostics, and lifecycle tracking.

SA

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published July 26, 202610 min read
IBM iDb2 for iSQLGENERATE_SPREADSHEETExcelCSVIFSReportingAutomationACS

Many IBM i reporting processes still depend on a person running a query, exporting the result, renaming the file, moving it to a shared directory, and emailing it to someone. SYSTOOLS.GENERATE_SPREADSHEET makes it possible to move that work into a repeatable SQL-driven process—but the production design must manage more than file creation.

The service can generate:

csv
txt
ods
xls
xlsx

from either:

The output is written directly to the integrated file system.

The most important question is not:

Can IBM i create an Excel file?

It can.

The better question is:

How do we make the report repeatable, secure, supportable, and safe to deliver?

The basic function

A simple database-file export is:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME        => '/reports/customers.csv',
    LIBRARY_NAME     => 'MYLIB',
    FILE_NAME        => 'CUSTOMER',
    SPREADSHEET_TYPE => 'csv',
    COLUMN_HEADINGS  => 'COLUMN'
);

The function returns:

 1   Success
-1   Failure

A query-driven workbook can be generated with:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/reports/open_orders.xlsx',
    SPREADSHEET_QUERY =>
        'SELECT ORDER_NUMBER,
                CUSTOMER_NUMBER,
                ORDER_DATE,
                ORDER_TOTAL
           FROM MYLIB.ORDERS
          WHERE ORDER_STATUS = ''OPEN''
          ORDER BY ORDER_DATE',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'COLUMN',
    SHEET_NAME =>
        'Open Orders'
);

Supported output types

IBM documents the following lowercase values:

csv
ods
txt
xls
xlsx

The default is:

csv

Choose the format based on the receiving process.

Use CSV when:

Use XLSX when:

Use ODS when:

Use TXT when:

The file extension should match the requested type.

Column headings

The heading options are:

NONE
COLUMN
LABEL

NONE produces no heading row and is the default.

COLUMN uses SQL column names.

LABEL uses database column labels and falls back to the column name when no label exists.

Example:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/reports/customer_labels.xlsx',
    LIBRARY_NAME =>
        'MYLIB',
    FILE_NAME =>
        'CUSTOMER',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'LABEL'
);

For business-facing reports, explicit aliases in a query are often clearer than depending on physical column names.

SELECT
    ORDER_NUMBER AS "Order Number",
    CUSTOMER_NAME AS "Customer",
    ORDER_DATE AS "Order Date",
    ORDER_TOTAL AS "Order Total"
FROM MYLIB.ORDERS;

Store reusable queries in the IFS

An inline query is limited to 4,000 characters.

Longer or source-controlled report definitions can be stored in the IFS:

/reports/sql/monthly_sales.sql

Then run:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/reports/output/monthly_sales.xlsx',
    SPREADSHEET_QUERY_IFS =>
        '/reports/sql/monthly_sales.sql',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'COLUMN',
    SHEET_NAME =>
        'Monthly Sales'
);

IBM requires the IFS query file to:

Use CCSID 1208 / UTF-8
Contain a single supported query
Reference fully qualified database objects
Avoid QTEMP objects
Not end with a semicolon

An IFS query file is useful because it can be:

Use a report-definition table

Do not hard-code every output path and query inside a scheduler entry.

Create a controlled report catalogue:

CREATE TABLE REPORTING.REPORT_DEFINITION
(
    REPORT_ID              VARCHAR(40) PRIMARY KEY,
    REPORT_NAME            VARCHAR(256) NOT NULL,
    QUERY_IFS_PATH         VARCHAR(1024) NOT NULL,
    OUTPUT_DIRECTORY       VARCHAR(1024) NOT NULL,
    OUTPUT_TYPE            VARCHAR(10) NOT NULL,
    COLUMN_HEADINGS        VARCHAR(10) NOT NULL,
    SHEET_NAME             VARCHAR(128),
    OVERWRITE_POLICY       VARCHAR(10) NOT NULL,
    RETENTION_DAYS         INTEGER NOT NULL,
    CONTAINS_SENSITIVE_DATA CHAR(1) NOT NULL,
    DELIVERY_METHOD        VARCHAR(20) NOT NULL,
    IS_ENABLED             CHAR(1) NOT NULL,
    UPDATED_BY             VARCHAR(128) NOT NULL,
    UPDATED_TIMESTAMP      TIMESTAMP NOT NULL
);

The catalogue defines approved behavior.

A report request should reference:

REPORT_ID
REPORT_DATE
ENVIRONMENT
REQUESTED_BY

rather than accepting an unrestricted SQL query or arbitrary IFS path.

Keep report SQL separate from report execution

A clean design separates:

What data the report contains
How the report is generated
Where the file is written
Who receives it
How long it is retained

The SQL query belongs in a reviewed source file.

The report catalogue owns output and policy.

The scheduler or processor owns timing and lifecycle.

The delivery component owns transfer.

This separation makes changes easier to review and reduces accidental data exposure.

Build predictable file names

Avoid repeatedly overwriting a generic file when history matters.

A practical pattern is:

report-id_YYYYMMDD_HHMMSS.xlsx

Example:

open-orders_20260727_220500.xlsx

You can build the path in SQL:

VALUES
    '/reports/output/open-orders_'
    CONCAT VARCHAR_FORMAT(
        CURRENT TIMESTAMP,
        'YYYYMMDD_HH24MISS'
    )
    CONCAT '.xlsx';

For rerunnable reports, include a unique request identifier:

open-orders_20260727_220500_req10422.xlsx

This avoids two jobs silently writing to the same path.

Use protected IFS directories

Do not write business reports into a broadly accessible location merely because it is convenient.

Avoid defaulting to:

/tmp

for files containing business, personal, financial, health, or security information.

A controlled layout can be:

/reports/
    sql/
    work/
    outbound/
    archive/
    failed/

Example authorities:

/reports/sql       Report administrators and deployment process
/reports/work      Report service profile only
/reports/outbound  Delivery service and approved support profiles
/reports/archive   Retention process and auditors
/reports/failed    Restricted support access

The execution profile needs authority to traverse each parent directory and create or update the file.

Generate into a work directory first

Do not create the report directly in a directory watched by an SFTP process or downstream consumer.

Use:

/reports/work

for generation.

After successful validation, move the completed file into:

/reports/outbound

This prevents another process from reading a partially written workbook.

A safe lifecycle is:

REQUESTED
RUNNING
GENERATED
VALIDATED
READY_FOR_DELIVERY
DELIVERED
ARCHIVED
DELETED
FAILED

Track every report run

CREATE TABLE REPORTING.REPORT_RUN
(
    RUN_ID                BIGINT
                          GENERATED ALWAYS AS IDENTITY,
    REPORT_ID             VARCHAR(40) NOT NULL,
    REQUESTED_TIMESTAMP   TIMESTAMP NOT NULL,
    STARTED_TIMESTAMP     TIMESTAMP,
    COMPLETED_TIMESTAMP   TIMESTAMP,
    REQUESTED_BY          VARCHAR(128) NOT NULL,
    EXECUTION_JOB         VARCHAR(28),
    OUTPUT_PATH           VARCHAR(1024),
    OUTPUT_TYPE           VARCHAR(10),
    STATUS                VARCHAR(30) NOT NULL,
    FUNCTION_RESULT       INTEGER,
    ROW_COUNT             BIGINT,
    FILE_SIZE_BYTES       BIGINT,
    DELIVERY_STATUS       VARCHAR(30),
    DELIVERED_TIMESTAMP   TIMESTAMP,
    RETENTION_DATE        DATE,
    ERROR_SQLCODE         INTEGER,
    ERROR_SQLSTATE        CHAR(5),
    ERROR_MESSAGE         VARCHAR(2048)
);

This table answers:

Count the rows before generating

For an approved report definition, capture the expected row count before file creation.

Example:

SELECT COUNT(*)
FROM MYLIB.ORDERS
WHERE ORDER_STATUS = 'OPEN';

Store it in REPORT_RUN.

After generation, use the value as a reasonableness check.

A successful function result does not necessarily prove the report contains the intended business population.

Examples of problems that still require detection:

Zero rows when thousands were expected
A filter date resolved incorrectly
The wrong environment was queried
A join multiplied the row count
A source table was not refreshed
A report ran before the upstream process completed

Replace versus update

The OVERWRITE option supports:

REPLACE
UPDATE

REPLACE is the default.

It replaces the contents of the existing file.

UPDATE writes into an existing XLS or XLSX file using the configured starting sheet, row, and column without replacing existing formatting.

Example:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/reports/templates/monthly_template.xlsx',
    SPREADSHEET_QUERY_IFS =>
        '/reports/sql/monthly_sales.sql',
    SPREADSHEET_TYPE =>
        'xlsx',
    OVERWRITE =>
        'UPDATE',
    STARTING_SHEET =>
        2,
    STARTING_ROW =>
        5,
    STARTING_COLUMN =>
        'B'
);

UPDATE is supported only for:

xls
xlsx

Positioning options are supported for:

xls
xlsx
ods

Use workbook templates carefully

A reusable template can already contain:

The process should copy a protected master template to a unique work-file path before calling UPDATE.

Do not update the master copy directly.

Recommended flow:

1. Copy the approved template.
2. Generate into the copied workbook.
3. Validate the output.
4. Publish the completed file.
5. Preserve the unchanged master template.

Assign a sheet name

Current support includes:

SHEET_NAME

for:

xls
xlsx
ods

Example:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/reports/output/customer_export.xlsx',
    SPREADSHEET_QUERY =>
        'SELECT CUSTOMER_NUMBER,
                CUSTOMER_NAME,
                CUSTOMER_STATUS
           FROM MYLIB.CUSTOMER
          ORDER BY CUSTOMER_NUMBER',
    SPREADSHEET_TYPE =>
        'xlsx',
    COLUMN_HEADINGS =>
        'COLUMN',
    SHEET_NAME =>
        'Customers'
);

The sheet-name enhancement is delivered at:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

Earlier positioning, IFS-query, update, and daemon options were delivered at the immediately preceding Db2 Group PTF levels.

LOB values require special handling

CLDownload does not directly support:

CLOB
BLOB
DBCLOB
XML

A small character LOB can be cast:

SELECT
    MESSAGE_ID,
    CAST(MESSAGE_TEXT AS VARCHAR(32000))
        AS MESSAGE_TEXT
FROM MYLIB.MESSAGE_STORE;

This introduces a maximum length.

The cast can truncate the value if it is too small.

For large text, XML, or binary data:

Character encoding and delimiters

A CSV file that looks correct in one desktop application may be interpreted differently by another system.

Confirm:

For machine integration, define a data contract.

Do not assume that a file called CSV is self-describing.

Use explicit SQL formatting when necessary:

SELECT
    VARCHAR_FORMAT(
        ORDER_DATE,
        'YYYY-MM-DD'
    ) AS ORDER_DATE,
    DECIMAL(
        ORDER_TOTAL,
        15,
        2
    ) AS ORDER_TOTAL
FROM MYLIB.ORDERS;

Prevent spreadsheet-formula injection

Text beginning with characters such as:

=
+
-
@

can be interpreted as a formula by spreadsheet software.

This matters when exported values contain user-supplied text.

Examples include:

Assess whether the receiving workbook software can execute formulas or external references.

For high-risk data, sanitize or prefix values according to the organization’s reporting standard.

Do not modify legitimate numeric values blindly.

The rule should be applied only to columns intended to be plain text.

Protect personal and regulated information

A database table may be properly secured while its exported workbook is not.

Before generating a report, review:

Common sensitive content includes:

Personal identifiers
Payment information
Health information
Employee data
Customer contact details
Security configuration
Maintenance gaps
Authentication data

The generated file must be governed as a new copy of the data.

Do not email unrestricted attachments by default

Email is convenient but can create uncontrolled copies in:

Alternatives include:

The report catalogue should define the approved method for each report.

Separate generation from delivery

GENERATE_SPREADSHEET creates the file.

It does not provide a complete delivery, retry, confirmation, or retention workflow.

Use separate components:

Report generator
File validator
Delivery worker
Delivery confirmation
Archive process
Retention cleanup
Monitoring and alerting

A delivery failure should not force the report query to be rerun automatically if the already-generated file is valid.

Retry the failed stage.

Validate the generated file

Minimum validation can include:

Additional validation can include:

Understand the ACS dependency

GENERATE_SPREADSHEET uses the CLDownload feature in:

/QIBM/proddata/Access/ACS/Base/acsbundle.jar

IBM delivers this JAR through the IBM HTTP Server for i Group PTF.

This means the report process depends on more than the Db2 Group PTF.

Review:

As of July 2026, IBM identifies ACS base package version:

1.1.9.13

with a May 2026 build.

The host JAR and desktop ACS packages should be maintained through the organization’s approved IBM i maintenance process.

QJVAEXEC daemon jobs

ACS Java support can leave a service daemon visible as:

QJVAEXEC

This can be normal and allows later requests by the same user to reuse the service.

Use:

KILL_DAEMON => 'YES'

when the daemon threads should be ended after the request:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
    PATH_NAME =>
        '/reports/work/daily.csv',
    SPREADSHEET_QUERY_IFS =>
        '/reports/sql/daily.sql',
    SPREADSHEET_TYPE =>
        'csv',
    KILL_DAEMON =>
        'YES'
);

The default is:

NO

Keeping the daemon can help repeated execution.

Ending it can be useful for isolated batch runs or controlled job cleanup.

Failure diagnostics

When no existing STDOUT override is present, CLDownload output is redirected to:

QTEMP/QGENSPREAD

When the function returns -1, inspect that file in the same job.

Because QTEMP belongs to the job, a support person in another job cannot directly see it.

A production processor should copy or summarize diagnostics before the generating job ends.

If STDOUT is already overridden, the output is not redirected to QTEMP/QGENSPREAD.

This should be included in support documentation.

Capture SQL diagnostics

A SQL procedure can capture the failure state:

BEGIN
    DECLARE V_RESULT INTEGER DEFAULT 0;
    DECLARE V_SQLCODE INTEGER DEFAULT 0;
    DECLARE V_SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE V_MESSAGE VARCHAR(2048) DEFAULT '';

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            V_MESSAGE = MESSAGE_TEXT;
        SET V_SQLCODE = SQLCODE;
        SET V_SQLSTATE = SQLSTATE;
    END;

    SET V_RESULT =
        SYSTOOLS.GENERATE_SPREADSHEET(
            PATH_NAME =>
                '/reports/work/open_orders.xlsx',
            SPREADSHEET_QUERY_IFS =>
                '/reports/sql/open_orders.sql',
            SPREADSHEET_TYPE =>
                'xlsx',
            COLUMN_HEADINGS =>
                'COLUMN',
            SHEET_NAME =>
                'Open Orders',
            KILL_DAEMON =>
                'YES'
        );

    INSERT INTO REPORTING.REPORT_RUN
    (
        REPORT_ID,
        REQUESTED_TIMESTAMP,
        COMPLETED_TIMESTAMP,
        REQUESTED_BY,
        OUTPUT_PATH,
        OUTPUT_TYPE,
        STATUS,
        FUNCTION_RESULT,
        ERROR_SQLCODE,
        ERROR_SQLSTATE,
        ERROR_MESSAGE
    )
    VALUES
    (
        'OPEN_ORDERS',
        CURRENT TIMESTAMP,
        CURRENT TIMESTAMP,
        SESSION_USER,
        '/reports/work/open_orders.xlsx',
        'xlsx',
        CASE
            WHEN V_RESULT = 1
             AND V_SQLSTATE = '00000'
              THEN 'GENERATED'
            ELSE 'FAILED'
        END,
        V_RESULT,
        V_SQLCODE,
        V_SQLSTATE,
        V_MESSAGE
    );
END;

Adapt handler behavior to the local SQL procedure standard.

MFA considerations

IBM documents two restrictions:

Do not weaken MFA for interactive users to make scheduled reporting work.

Use an approved non-interactive service profile designed for automation.

The profile should have:

Avoid QTEMP dependencies

Queries supplied through SPREADSHEET_QUERY or SPREADSHEET_QUERY_IFS cannot reference QTEMP objects.

This affects report designs that first populate a temporary table.

Alternatives include:

Clean up persistent staging data after the report lifecycle completes.

Schedule only after upstream readiness

Running at 2:00 a.m. does not guarantee that the source data is ready at 2:00 a.m.

Use a dependency condition:

Source batch completed
Control total accepted
Business date closed
Upstream interface reconciled
Required table refresh completed

The scheduler should evaluate a readiness table or lifecycle record before generating the report.

This avoids distributing incomplete data successfully.

Prevent duplicate scheduled runs

Use a unique key:

REPORT_ID + BUSINESS_DATE + SCHEDULE_INSTANCE

Example:

CREATE UNIQUE INDEX
    REPORTING.REPORT_RUN_U1
ON REPORTING.REPORT_RUN
(
    REPORT_ID,
    REPORT_DATE,
    SCHEDULE_INSTANCE
);

If REPORT_DATE and SCHEDULE_INSTANCE are needed, add them to the table definition.

A rerun should require:

Retention and deletion

Each report definition should have a retention policy.

Example:

Work files        Delete after delivery confirmation
Outbound files    Retain 2 days
Archived reports  Retain 30 days
Failed files      Retain 7 days for support
Audit metadata    Retain according to policy

Deletion should be recorded.

A report marked DELETED should contain:

Deletion timestamp
Deletion job
Deletion result
Approved retention rule

Do not delete a file still required by an investigation, legal hold, audit, or reconciliation process.

Monitoring and alerting

Useful alerts include:

Avoid repeated alerts for the same unchanged failure.

Link every alert to:

RUN_ID
REPORT_ID
OUTPUT_PATH
EXECUTION_JOB
ERROR_MESSAGE
OWNER

A practical architecture

Scheduler or request
        |
        v
Report-definition lookup
        |
        v
Upstream readiness check
        |
        v
Create REPORT_RUN row
        |
        v
Build unique protected work path
        |
        v
GENERATE_SPREADSHEET
        |
        v
Validate file and row expectations
        |
        v
Move to outbound directory
        |
        v
Deliver through approved channel
        |
        v
Confirm delivery
        |
        v
Archive and retain
        |
        v
Delete according to policy

When GENERATE_SPREADSHEET is a strong fit

Use it when:

When another approach is better

Use a document-generation tool when:

Use an API when:

Use an ETL or data-platform tool when:

Detailed SQL reference

For a parameter-by-parameter implementation guide, see:

Generate Excel, CSV, ODS, and Text Files from IBM i SQL

Final takeaway

SYSTOOLS.GENERATE_SPREADSHEET solves the file-generation step.

A production reporting process must also solve:

Definition
Approval
Scheduling
Data readiness
Security
Output naming
Validation
Delivery
Retry
Retention
Deletion
Monitoring
Auditability

The modernized process is not:

Run SQL and email a spreadsheet.

It is:

Generate the right report, from the right data, under the right authority, deliver it through an approved channel, prove what happened, and remove it when it is no longer needed.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.