QSH to Environment Vars

[FaStOnE]

I know you can call QSH from a CLLE command and have it redirect the output to a .TXT file in the IFS, etc... But, what if I'm just needing a "count" of records based on a where clause to determine if I need to process a file or not?

Something like ...

QSH( cmd "Select count(*) from XXX" ) > &Count

Anyone have something similar that could be used?

-Rick

[Marc_d]

Re: QSH to Environment Vars

try:

DCL &RECORDS *DEC (10 0)

RTVMBRD FILE(mylib/myfile) NBRCURRCD(&RECORDS)

If (&RECORDS < 1) goto endpgm

If you want something more advanced? Look at this link:

www.mcpressonline.com -> programming -> CL -> Display record count

[gcraill]

Re: QSH to Environment Vars

Marc's link is blocked - I hope it is not holiday snaps from AS400Pro ?

Anyways this works, I couldn't see any way to pipe the result to a variable - well in CL anyways, from RPG you can I think but then from RPG you don't need QSH.

This does require files to be created and read, and not sure if you need to "repeat" this process thereby causing all sorts of file opens etc, although you could APPEND to the outfile and then just issue 1 RCVF for each iteration ???

GC

             PGM                                                              
             DCL        VAR(&CNTSTR) TYPE(*CHAR) LEN(11)                      
             DCLF       FILE(TEMPFILES/OO)                                    
             CRTPF      FILE(TEMPFILES/OO) RCDLEN(300)                        
             MONMSG     MSGID(CPF0000)                                        
             CLRPFM     FILE(TEMPFILES/OO)                                    
             ADDENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT) +                         
                          VALUE('FILE=/qsys.lib/tempfiles.lib/oo.file/oo.mbr')
             QSH        CMD('db2 "select count(*) from +                      
                          tempfiles.mytolc where tolcyc != 1"')               
             RCVF                                                             
             CHGVAR     VAR(&CNTSTR) VALUE(%sst(&OO 26 11))                   
             SNDPGMMSG  MSG('Count=(' *TCAT &CNTSTR *TCAT ')')                
             RMVENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT)                           
             ENDPGM 

I googled here for hints http://search400.techtarget.com/tip/...23206,00.html#

[FaStOnE]

Re: QSH to Environment Vars

Ya know... I knew we had you around for some reason. That's exactly what I was looking for!

But question: What's the significance of the "QIBM_QSH_CMD_OUTPUT" Env Var? And where did you find this reference? That's intriguing...

[gcraill]

Re: QSH to Environment Vars

Ya know... I knew we had you around for some reason. That's exactly what I was looking for!

And I thought it was cos no one else was gonna support the Niners !!!

Which by the way won their last 2 games, so that means that seeing as they don't play until september they're gonna be on almost a 9 month winning streak - LOL

[gcraill]

Re: QSH to Environment Vars

But question: What's the significance of the "QIBM_QSH_CMD_OUTPUT" Env Var? And where did you find this reference? That's intriguing...

I got it from here http://search400.techtarget.com/sear...Series_ch2.pdf, go down in the article to "Redirecting QSHELL Output", I couldn't see anyway to send it to a parm in the CLLE pgm, so it had to go to file for my tests.

Anyone know a way to zang this straight to a parameter/variable in the CL ?

GC

[Denny]

Re: QSH to Environment Vars

Here is an alternative way to run simple SQLs from CL. (No QSH). It is the LOOKUP command and a RPGLE command processing program that executes dynamic SQL. Very handy utility that I have used for years.

Command Prompt ...

                           Lookup Data Value (LOOKUP)                          
                                                                               
 Type choices, press Enter.                                                    
                                                                               
 SQL Expression to retrieve . . .                                              
                                                                               
                                                                               
                                                                               
 Table Name . . . . . . . . . . .                                              
                                                                               
                                                                               
 SQL WHERE clause . . . . . . . .                                              
                                                                               
                                                                               
                                                                               
 Return Value   (*Char 128) . . .                 Character value

Sorry the above does not show the underlines for the input fields.

Note that SQL expression must return exactly one field and exactly one row. SQL casts the epression as character with a lenght of 128 and returns it to the CL.

DCL &COUNT *CHAR 128
LOOKUP LOOKUPEXP('count(*)') TABLE(MSPMP100)
WHERE('ACTIV= ''1'' ') RETURNVAR(&COUNT)

&COUNT would contain 0 or the zero suppressed number of records in the file.

You can also extract data:

LOOKUP LOOKUPEXP('SUBSTRING(WCDTA,3,2)') +
TABLE(MSWCP100) WHERE('WCKEY= ''APPERD'' AND +
WCPNO = 25') RETURNVAR(&OPEN2)

See the article at

http://www.mcpressonline.com/programming/sql/using-dynamic-sql-in-cl-part-2retrieving-a-data-value.html

[gcraill]

Re: QSH to Environment Vars

And I thought it was cos no one else was gonna support the Niners !!!

Which by the way won their last 2 games, so that means that seeing as they don't play until september they're gonna be on almost a 9 month winning streak - LOL

Say how bout that, The Niners are on another winning streak, 29 days and counting ...

[FaStOnE]

Re: QSH to Environment Vars

Ya know .. I've actually become a pseudo Niners fan since they picked up my boy from Ole Miss at Middle Linebacker... Patrick Willis.

He's a stud!

[gcraill]

Re: QSH to Environment Vars

Hes definately a handy chap to have at linebacker. Had a good season overall.