Re: QSH DB2 Count(*) with Datarea

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?