Re: QSH DB2 Count(*) with Datarea

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

Mike,

The output from db2 will look something like this:

  00001
  -----------
          103

    1 RECORD(S) SELECTED.


The reason for the error you're getting is that there's a blank line 
first.  But, even if there weren't, the other lines are going to get in 
your way.  The 'db2' command is really awful to use in scripts and stuff 
like that... this is why I wrote my own SQL tool for QShell, so I didn't 
have to use the ugly db2 tool.

But, if you do want to use db2, one trick is to ask SELECT to output 
something else on the line with the result, so you can filter by that 
string.

For example, you could do



The output will, of course, now have == in front of the number 
containing the count.  So you can use 'grep' to filter the results to 
get only that number by doing this:

db2 "select '==' concat count(*) from puidemo.categp" | grep ==

Or maybe you want leading zeros (depending on what you're using the 
result for)

db2 "select '==' concat digits(count(*)) from puidemo.categp" | grep ==

So in either case, you now have one line of output, but unfortunately, 
it now has == at the front of it.  So you can use 'cut' to remove those 
two bytes.

db2 "select '==' concat digits(count(*)) from puidemo.categp" | grep == 
| cut -b 3-

Then you could send the output of THAT to datarea if you want...


On 10/28/2013 1:14 PM, Michael Ryan wrote:
> Hi there -
>
> I'm doing this:
>
> QSH        CMD('db2 "select count(*) from +
>               michael.mblsesp where session_end is +
>               null" | datarea -lw phpsess')
>
> in a CL program, and I'm getting this:
>
> *CPF1062*Null string not valid as character value.
> I don't know why it would be a null string. I would think it would be zero
> or some number. I'm missing something. Anyone do this?