How to get number of records processed by SQL query in CL/CLLE?

[hrishikesh]

Hi All,

I want to get the number of records processed by a query in CL. How can I get it?

I am executing below query through RUNSQLSTM in CL. I want to get the number of records deleted by the query from file ABC.

DELETE FROM ABC WHERE DATE =20091112 ;

Any idea?

Thanks in advance!

[chrisr]

Re: How to get number of records processed by SQL query in CL/CLLE?

You could change the listing output to *print and then read the spool file.

[jamief]

Re: How to get number of records processed by SQL query in CL/CLLE?

do a rtvmbrd &numdltrec first to get total deleted records...
then run your delete sql
do rtvmvrd &numdltrec
then do math

[chrisr]

Re: How to get number of records processed by SQL query in CL/CLLE?

do a rtvmbrd &numdltrec first to get total deleted records...
then run your delete sql
do rtvmvrd &numdltrec
then do math

I had thought about checking the file before and after number of records or running a select insert to a temp file before the delete but there is always the possibility that this isn't accurate, depending upon the file use by other processes.

[as400Pro]

Re: How to get number of records processed by SQL query in CL/CLLE?

alcobj obj((*n *n *excl)) 

[chrisr]

Re: How to get number of records processed by SQL query in CL/CLLE?

True, if it is possible in your system to lock everyone else out of a file for you to execute that transaction.

[jamief]

Re: How to get number of records processed by SQL query in CL/CLLE?

I would just fire them all....They probubly suck anyway.

Okay Im just kidding

[chrisr]

Re: How to get number of records processed by SQL query in CL/CLLE?

Users huh, who needs them.

[hrishikesh]

Re: How to get number of records processed by SQL query in CL/CLLE?

Chrisr,

How do I read a spool file?

[kausix777]

Re: How to get number of records processed by SQL query in CL/CLLE?

I guess he means to read it using WRKSPLF manually. Or you can code DSPSPLF into the program.

By the way, while running delete operations, it is actually better to have an exclusive lock, assuming the file is important enough for you to want to extract the number of records deleted.

[jamief]

Re: How to get number of records processed by SQL query in CL/CLLE?

crtf qtemp/spoolfile rcdlen(198)

CPYSPLF  blah blah blah! to spoolfile

dclf   spoolfile

loop:
rcvf  spoolfile
monmsg cpf0864   exit

do stuff with the spooled file

goto loop

exit: endpgm 

[jamief]

Re: How to get number of records processed by SQL query in CL/CLLE?

After writing a CL with subroutines I felt POWERFUL so I thought Id share.

             PGM


   dcl var(&chr4) type(*char) len(4)
   dcl var(&user) type(*char) len(10)
   dclf file(spool)

   monmsg cpf0000
   rtvjoba user(&user)

   crtpf qtemp/spool rcdlen(198)

   ovrprtf file(QPDSPAJB) hold(*YES)
   wrkactjob *print
   cpysplf file(QPDSPAJB) tofile(QTEMP/SPOOL)
   dltsplf file(QPDSPAJB) splnbr(*LAST)
   callsubr subr($Readfile)


   /*------------------------------------------------------*/
   /*  $Readfile - read the spooled file                   */
   /*------------------------------------------------------*/
    read:    subr       subr($readfile)
             rcvf rcdfmt(spool)
               monmsg  msgid(CPF0864) exec(goto cmdlbl(EXIT))
               callsubr subr($Process)
               goto read
    exit:
             endsubr

   /*------------------------------------------------------*/
   /*  $Process - process one record at a time             */
   /*------------------------------------------------------*/
             subr       subr($process)

              chgvar var(&chr4) value(%sst(&spool 116 4))
              if cond(&chr4 = 'MSGW') then(do)
               sndmsg msg('Big Trouble Little China Town') +
                          tousr(&user)
              enddo

             endsubr
   /*-----------------------------------------------------------*/

   endpgm 

[Mercury]

Re: How to get number of records processed by SQL query in CL/CLLE?

Jamie,
I thought that you hated CL

[jamief]

Re: How to get number of records processed by SQL query in CL/CLLE?

I do....
It smells worse than a trunk full of andouillette sausage's on a hot afternoon..

[chrisr]

Re: How to get number of records processed by SQL query in CL/CLLE?

What he said.

hrishikesh the cpysplf command is relatively easy to use to copy to a physical file which you can read in CL or RPG or however you wish. Jamie's example code is ideal (even if it means reading a file in CL) and would meet your needs. The second more detailed code shows how to read the output from a wrkactjob command. You could adapt this for the runsqlstm spool file which when you prompt the command shows a default of QSYSPRT.

hth