IBM i Global

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

Dear Peter

I see a clear mistake in the first half of your post.  The SQL code you posted used *SQL naming by you used the parameter NAMING(*SYS) in RUNSQLSTM command which should likely be the cause of the error you encountered.  Please try NAMIMG(*SQL) to see if it solves your problem or not.

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

Dear Peter

I see a clear mistake in the first half of your post.  The SQL code you posted used *SQL naming by you used the parameter NAMING(*SYS) in RUNSQLSTM command which should likely be the cause of the error you encountered.  Please try NAMIMG(*SQL) to see if it solves your problem or not.

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

Satid,
actually IBM changed that in 2020:
"System naming convention expanded to permit (slash) and (dot) qualifiers"

https://www.ibm.com/support/pages/system-naming-convention-expanded-permit-slash-and-dot-qualifiers

so that didn't change the result.
The problem was the limitations of RUNSQLSTM which has been resolved by the suggestions in the posts below.

Thanks,
Peter

Dear Peter

I see a clear mistake in the first half of your post.  The SQL code you posted used *SQL naming by you used the parameter NAMING(*SYS) in RUNSQLSTM command which should likely be the cause of the error you encountered.  Please try NAMIMG(*SQL) to see if it solves your problem or not.

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

Hi Peter.

Your VALUES statements returns data like SELECT statements do - and RUNSQLSTM can't run such statements since it has nowhere to display the output.

The SYSTOOLS.GENERATE_SPREADSHEET and SYSTOOLS.SEND_EMAIL both are functions and return a value 1 when no error occurred, so you can encapsulate the functions in a compound SQL statement (BEGIN-END) like this:

--
-- Get PTF Currency and email as Excel file
--
begin
declare v_Sheet integer;
declare v_Email integer;

set v_Sheet =SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
set v_Email = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

-- Any errors?
if v_Sheet <> 1 or v_Email <> 1 then
  call SYSTOOLS.LPRINTF( 'Error mailing PTF currency spreadsheet in job ' concat QSYS2.JOB_NAME );
  call SYSTOOLS.LPRINTF( 'Spreadsheet: ' concat v_Sheet concat ', email: ' concat v_Email );
end if;
end

This will allow the statement to be run by RUNSQLSTM.

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

------------------------------
Peter Langhammer
------------------------------

Hi!

Instead of VALUES, try this:

BEGIN                                                                                                                
    DECLARE Status INTEGER DEFAULT 0;                                                                                
    SET Status = SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/wmbeprn/Precisely_Mimix_Keys',                    
                SPREADSHEET_QUERY => 'SELECT * from qgpl.mimlic',                                                    
                LIBRARY_NAME => NULL, FILE_NAME => NULL, SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'COLUMN');   
END;                                                                                                                 

/Bengt

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

------------------------------
Peter Langhammer
------------------------------

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

------------------------------
Peter Langhammer
------------------------------

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?

Thanks to everybody coming up so quick with help to solve the problem, that is why it is so great to be a member of the IBM i community.
Declaring a variable for the receiving of the return code did the job, RUNSQLSTM is no longer complaining.

I ran into another problem though, a problem with sql code -332 character conversion from CCSID 65535 to 1200 not valid.

Turns out on our box system value  QCCSID was never adjusted from 65535 to a correct 37 for our region,
that has been adjusted now.

Means we can now create spreadsheets in batch and mail them out on a scheduled job.
The PTF currency was more or less a POC, there are way more value generating cases where that makes sense.

Thanks for a quick solution!
Peter

I have this great sql script which works just wonderful when executing interactive in Run SQL Scripts:
Power10 V7R4 PTF's up to date.

--
-- Get PTF Currency and email as Excel file
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency',
        SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS (
        SELECT OS_VERSION, OS_RELEASE
            FROM sysibmadm.env_sys_info
    )
    SELECT P.*
        FROM iLevel, systools.group_ptf_currency P
        WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
               AND PTF_GROUP_CURRENCY IS NOT null
        ORDER BY ptf_group_level_available - ptf_group_level_installed DESC',
        SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL');

-- One time setup - Make sure the user running this SQL is an SMTP user
--call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');

-- Send the email with attachement
VALUES SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
                    FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT
            CURRENT TIMESTAMP, ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

Gets the PTF currency and emails as a spreadsheet, love it.

Wanted to run that as a scheduled job.

1st try from CLLE:

             PGM

             RUNSQLSTM  +
                          SRCSTMF('/SQL_Scripts/GeneralPurpose/GetPTFCur.sql+
                          ') COMMIT(*NONE) NAMING(*SYS)

             ENDPGM

Compiles nicely but bombs when running:

So RUNSQLSTM doesn't like that at all, no surprise, the documentation says it has limitations.

So the next thought was to put it into a SQLRPGLE and schedule that.
Like this:

**Free
// Get PTF Currrency and email it.

Dcl-s ReturnCode int(3);

Exec Sql
  SET :Returncode = SYSTOOLS.GENERATE_SPREADSHEET(
        PATH_NAME => '/home/peter/PTF_Currency', SPREADSHEET_QUERY => 'WITH iLevel (iVersion, iRelease) AS
       (SELECT OS_VERSION, OS_RELEASE
           FROM sysibmadm.env_sys_info)
   SELECT P.*
   FROM iLevel, systools.group_ptf_currency P
   WHERE ptf_group_release = ''R'' CONCAT iVersion CONCAT iRelease CONCAT ''0''
   AND PTF_GROUP_CURRENCY IS NOT null
   ORDER BY ptf_group_level_available - ptf_group_level_installed DESC', SPREADSHEET_TYPE => 'xlsx',
        COLUMN_HEADINGS => 'LABEL');

Exec Sql
  SET :ReturnCode = SYSTOOLS.SEND_EMAIL(
        TO_EMAIL => 'planghammer@usinger.com', SUBJECT => 'PTF Currency: ' CONCAT (SELECT host_name
              FROM sysibmadm.env_sys_info), BODY => 'PTF Currency on: ' CONCAT CURRENT TIMESTAMP,
        ATTACHMENT => '/home/peter/PTF_Currency.xlsx');

*InLr = *On;

The compiler and the edit checker don't like the Generate_Spreadsheet at all.

When this part gets commented out and only the Send_Email is left,
the program compiles :-)

But it fails when executing:

Did anybody get something like this  to work besides doing it interactive ?