Month: May 2022

Posted on

Sequel | Getting the Most from Variables in Sequel

If you’re interested in learning more and would like to demo Sequel Data Access, click here: https://www.helpsystems.com/products/enterprise-data-access-software-ibm-i/demo

Do you have multiple, nearly identical queries that differ only in selection criteria like region, department, and date range? If this is the case, runtime prompts are for you. Runtime prompts help you stay organized by allowing different users and departments to run the same views, but return unique, tailored results.

One way to improve those runtime prompts is with variables. By using variables, you can narrow your scope when pulling data and be as specific as you want to be.

During this webinar, our product expert will share how variables add flexibility to your views, reports, tables and scripts to prompt for criteria at runtime. Beyond creating variables, we will cover:

-Using a default value both as a constant and as an expression
-Adding a filter to a drop-down list
-Demystifying the use of quotes and ampersands with variables

HelpSystems strives to develop powerful business intelligence tools for your organization. The more you know about the finer details, the better set up for success you and your business users will be.

Watch now!

Posted on

IBMi (AS400) fans only – How to send a spool file per Email as TXT attachment




This CLP sends a SpoolFile per Email as TXT attachment and need 4 parameters:
&SPOOLF Spool file name
&SUBJCT Subject
&BODY00 body
&MAIL01 Email

(Pay attention at the “Send Email” note at the bottom)

Compile this CLP source with:
CRTCLPGM PGM(myLib/SNDSPLF) SRCFILE(myLib/mySourceFile) SRCMBR(SNDSPLF)


SNDSPLF.CLP
 
 
 
             PGM        PARM(&SPOOLF &SUBJCT &BODY00 &MAIL01)
/*****************************************************************************/
/* Send a SpoolFile per Email as TXT attachment                              */
/* &SPOOLF Spool file name                                                   */
/* &SUBJCT Subject                                                           */
/* &BODY00 body                                                              */
/* &MAIL01 Email                                                             */
/* Try with:                                                                 */
/* CALL PGM(SNDSPLF) PARM((‘MySpoolfileName’) (‘MySubject’) (‘MyBody’) +     */
/*       (‘MyEmailAddress’))                                                 */
/*****************************************************************************/
             DCL        VAR(&SPOOLF) TYPE(*CHAR)  LEN(10)
             DCL        VAR(&SUBJCT) TYPE(*CHAR) LEN(048)
             DCL        VAR(&BODY00) TYPE(*CHAR) LEN(048)
             DCL        VAR(&MAIL01) TYPE(*CHAR) LEN(020)

             DCL        VAR(&JOBNUM) TYPE(*CHAR) LEN(   6)
             DCL        VAR(&JOBUSR) TYPE(*CHAR) LEN(  10)
             DCL        VAR(&JOBNAM) TYPE(*CHAR) LEN(  10)
             DCL        VAR(&USER  ) TYPE(*CHAR) LEN(010)
             DCL        VAR(&SDIR  ) TYPE(*CHAR) LEN(100)
/*****************************************************************************/
/* START                                                                     */
/*****************************************************************************/
             RTVJOBA    JOB(&JOBNAM) USER(&JOBUSR) NBR(&JOBNUM)
             CHGVAR     VAR(&SUBJCT) VALUE(%TRIM(&SUBJCT))
             CHGVAR     VAR(&BODY00) VALUE(%TRIM(&BODY00))
             CHGVAR     VAR(&MAIL01) VALUE(%TRIM(&MAIL01))
/*————————————————————–*/
/* if already exist, delete workfile SNDSPLTXT                  */
/*————————————————————–*/
             DLTF       FILE(QTEMP/SNDSPLTXT)
             MONMSG     MSGID(CPF0000)
/*————————————————————–*/
/* create workfile SNDSPLTXT 198 columns                        */
/*————————————————————–*/
             CRTPF      FILE(QTEMP/SNDSPLTXT) RCDLEN(198)
/*————————————————————–*/
/* Copy the spoolfile into workfile SNDSPLTXT                   */
/*————————————————————–*/
             CPYSPLF    FILE(&SPOOLF) TOFILE(QTEMP/SNDSPLTXT) JOB(&JOBNUM/&JOBUSR/&JOBNAM) +
                          SPLNBR(*LAST)
/*————————————————————–*/
/* build path &SDIR                                             */
/* Path looks like this:                                        */
/* /SNDSPLF/(username)                                          */
/* If not exist, the path will be created                       */
/*————————————————————–*/
             CRTDIR     DIR(‘/SNDSPLF’)
             MONMSG     MSGID(CPF0000)
             RTVUSRPRF  RTNUSRPRF(&USER)
             CHGVAR     VAR(&SDIR) VALUE(‘/SNDSPLF/’ *CAT &USER)
             CRTDIR     DIR(&SDIR)
             MONMSG     MSGID(CPF0000)
             CHGVAR     VAR(&SDIR) VALUE(‘/SNDSPLF/’ *CAT %TRIM(&USER) *CAT ‘/SNDSPLTXT.TXT’)
             RMVLNK     OBJLNK(&SDIR)
             MONMSG     MSGID(CPF0000)
/*————————————————————–*/
/* Copy spoolfile into IFS                                      */
/*————————————————————–*/
             CPYSPLF    FILE(&SPOOLF) TOFILE(*TOSTMF) JOB(&JOBNUM/&JOBUSR/&JOBNAM) +
                          SPLNBR(*LAST) TOSTMF(&SDIR)
/*————————————————————–*/
/* Send Email                                                   */
/*————————————————————–*/
/* Sorry, I have no permission on PUB400 to use SNDSMTPEMM, but the following */
/* must be the righ sintax. Contact me if not.                                */
         /*  SNDSMTPEMM RCP((&MAIL01)) SUBJECT(&SUBJCT) NOTE(&BODY00) ATTACH((%TRIM(&SDIR) +
                          *PLAIN *TXT)) CONTENT(*HTML)    */
/*————————————————————–*/
/* Clear IFS                                                    */
/*————————————————————–*/
             RMVLNK     OBJLNK(&SDIR)
             MONMSG     MSGID(CPF0000)

             ENDPGM   
https://www.linkedin.com/in/aldosucci/
Verified by MonsterInsights