Passing Parameter in QSH / QZDFMDB2

[union123]

Dear Superiors,
need help on this problem.
I need to perform an 'INSERT INTO' using parm that has been defined in another files.
Please take a look below for the source
-----------------------------------------------------
DSPUSRPRF USRPRF(Q*) OUTPUT(*OUTFILE) +
OUTFILE(TEMPLIB/USRINFO) /* LIST ALL USER +
IN MACHINE */
SGNCHCK: RCVF
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(END))
IF COND(&UPPSOD *EQ ' ') THEN(DO)
CHGVAR VAR(&CMD) VALUE('INSERT INTO +
TEMPLIB.USROUT1 (UPUPRF, UPUSCL, UPPSOD) +
VALUES($UPUPRF, $UPUSCL, $UPPSOD) '')')
CALL PGM(QZDFMDB2) PARM(&CMD)
-----------------------------------------------------

The problem is when I executed this it says Operand not valid.
How to fix it?

Also everytime it execute QZDFMDB2, it open CLI.
Can I bypass the CLI?

Thank you

Br,
Union123

[Mercury]

Re: Passing Parameter in QSH / QZDFMDB2

All variables used in a CL program must be prefixed by an ampersand ( & ). The chgvar below is corrected.

chgvar var(&cmd) value('db2 "insert into templib.usrout1 (upuprf, upuscl, uppsod) values(&upuprf, &upuscl, &uppsod)"')

qsh cmd(&cmd) 

Seems a better solution to use QSH (shown above) instead of QZDFMDB2. QSH interface is published, QZDFMDB2 is an IBM internal tool that can change without advertissement.

To avoid the C runtime display when run interactively, you need to assign the value NONE to environment variable QIBM_QSH_CMD_OUTPUT

Addenvvar  Envvar(QIBM_QSH_CMD_OUTPUT) Value(NONE) Replace(*Yes) 

In addition, the QIBM_QSH_CMD_ESCAPE_MSG variable tells Qshell that if there is an error, an escape error message will be sent to your CL program. This can be set to N if you want to ignore errors (the default), or Y (shown below) to receive the escape message.

Addenvvar  Envvar(QIBM_QSH_CMD_ESCAPE_MSG)   Value(Y) Replace(*Yes) 

[union123]

Re: Passing Parameter in QSH / QZDFMDB2

Hi Philippe,
The Variable that want to be passed is (in my example) $UPUPRF, $UPUSCL, $UPPSOD.
I try running the program
and the error I got is
--------------------------------------------------------------------------------------
**** CLI ERROR *****
SQLSTATE: 42601
NATIVE ERROR CODE: -104
Token & was not valid. Valid tokens: ( + - ? : DAY RRN CASE CAST CHAR DATE DAYS HASH HOUR LEFLL.
Press ENTER to end terminal session.
--------------------------------------------------------------------------------------

Need help thank you

Br,
Union123

[Mercury]

Re: Passing Parameter in QSH / QZDFMDB2

I jumped the gun. My previous posting should have read

chgvar var(&cmd) value('db2 "insert into templib.usrout1 (upuprf, upuscl, uppsod) values(' *cat &$upuprf *tcat ', ' *cat &$upuscl *tcat ', ' *cat &$uppsod *tcat ')"') 

instead of posted.

(I forgot the *cat's).

[union123]

Re: Passing Parameter in QSH / QZDFMDB2

Hi Philippe,
I have tried the command you gave me but when I copied to my pgm, the result is 40 since no &$UPUSCL not declared.
I try to change the &$UPUSCL using &UPUSCL, the command goes smooth but no entry goes to USROUT1
Please advice,

(Here is my complete program)

PGM 
DCL VAR(&DATEFRM) TYPE(*CHAR) STG(*AUTO) LEN(6) 
DCL VAR(&DATECVT) TYPE(*CHAR) STG(*AUTO) LEN(7) 
DCL VAR(&TODATE) TYPE(*CHAR) STG(*AUTO) LEN(6) 
DCL VAR(&TODTCVT) TYPE(*CHAR) STG(*AUTO) LEN(7) 
DCL VAR(&TODTINT) TYPE(*DEC) STG(*AUTO) LEN(7 0) 
DCL VAR(&DATEINT) TYPE(*DEC) STG(*AUTO) LEN(7 0) 
DCL VAR(&CMD) TYPE(*CHAR) LEN(512) 
DCLF FILE(TEMPLIB/USRINFO) 
 
DSPUSRPRF USRPRF(Q*) OUTPUT(*OUTFILE) + 
OUTFILE(TEMPLIB/USRINFO) /* LIST ALL USER + 
IN MACHINE */ 
 
SGNCHCK: RCVF 
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(END)) 
 
IF COND(&UPPSOD *EQ ' ') THEN(DO) 
 
    ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE) + 
        REPLACE(*YES) 
    CHGVAR VAR(&CMD) VALUE('DB2 "INSERT INTO + 
        TEMPLIB.USROUT1 (UPUPRF, UPUSCL, UPPSOD) + 
        VALUES(' *CAT &UPUPRF *TCAT ', ' *CAT + 
        &UPUSCL *TCAT ', ' *CAT &UPPSOD *TCAT ')"') 
    QSH CMD(&CMD) 
    RMVENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) 
 
ENDDO 
 
GOTO CMDLBL(SGNCHCK)
 
END: 
ENDPGM

Br,
Union123

[Mercury]

Re: Passing Parameter in QSH / QZDFMDB2

You don't need all this stuff to pull out the Q user profiles'. A simple "INSERT INTO WHERE" can do the trick as shown below.

PGM 

ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE) REPLACE(*YES)         
 
DSPUSRPRF USRPRF(Q*) OUTPUT(*OUTFILE) + 
OUTFILE(TEMPLIB/USRINFO) /* LIST ALL USER Q* + 
IN MACHINE */ 

CHGVAR     VAR(&CMD) VALUE('db2 "INSERT INTO +    
           TEMPLIB.USROUT1 (UPUPRF, USCL, UPPSOD) + 
           SELECT UPUPRF, UPUSCL, UPPSOD FROM +   
           TEMPLIB.USRINFO WHERE UPPSOD = '' ''"') 
QSH CMD(&CMD) 

RMVENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) 
 
 
ENDPGM 

[union123]

Re: Passing Parameter in QSH / QZDFMDB2

Hi Philippe,
Yes thank you...

What if I want to calculate the last signon date say not signon more than 90 days. I do have to calculate then INSERT INTO, is it?
--------------------------------------------------------
IF COND(&UPPSOD *NE ' ') THEN(DO)
CHGVAR VAR(&DATEFRM) VALUE(&UPPSOD)
RTVJOBA DATE(&TODATE)
CVTDAT DATE(&DATEFRM) TOVAR(&DATECVT) FROMFMT(*YMD) +
TOFMT(*LONGJUL) TOSEP(*NONE)
CVTDAT DATE(&TODATE) TOVAR(&TODTCVT) FROMFMT(*DMY) +
TOFMT(*LONGJUL) TOSEP(*NONE)
CHGVAR VAR(&TODTINT) VALUE(&TODTCVT)
CHGVAR VAR(&DATEINT) VALUE(&DATECVT)
IF COND((&TODTINT-&DATEINT) >= 180) THEN(DO)
CHGVAR VAR(&CMD) VALUE('INSERT INTO +
TEMPLIB.USROUT1 (UPUPRF, UPUSCL, UPPSOD) +
VALUES(&UPUPRF, &UPUSCL, &UPPSOD) '')')
CALL PGM(QZDFMDB2) PARM(&CMD)
ENDDO
ENDDO
-----------------------------

Please help

[kitvb1]

Re: Passing Parameter in QSH / QZDFMDB2

Here is another example of calculating a date in CLP.

DCL        VAR(&DLTDATE) TYPE(*CHAR) LEN(6) 
DCL        VAR(&LILIAN) TYPE(*CHAR) LEN(4)  
DCL        VAR(&WORK1) TYPE(*CHAR) LEN(8)   
DCL        VAR(&WORK2) TYPE(*CHAR) LEN(23)  
DCL        VAR(&WDATE) TYPE(*CHAR) LEN(8)                                                                           
/* Get local time from system                                   */      
            CALLPRC    PRC(CEELOCT) PARM(&LILIAN &WORK1 &WORK2 *OMIT)   
 
/*  Subtracting 90 from &LILIAN will produce about 3 months ago */      
            CHGVAR     VAR(%BIN(&LILIAN)) VALUE(%BIN(&LILIAN) - 90)     
            CALLPRC PRC(CEEDATE) PARM(&LILIAN 'YYYYMMDD' &WDATE *OMIT)  
            CHGVAR     VAR(&DLTDATE) VALUE(%SST(&WDATE 3 6))

&wdate will contain 20080905
&dltdate will contain 080905