On a warehouse app, our Java clients [can] constantly load/insert rows
into the db. I would like to throttle these inserts (1.5 million rows per
hr) from the Java app, based on current 'busy state' of the server.
It would be nice if I can get the server CPU utilization (and other key
factors) from db2 via jdbc. However, it needs to be low cost, since it
would likely run every x seconds.
System: db2 v 9.1 64bit on Centos 4.3 64 bit.

Thank you.
nat

natG wrote:

> On a warehouse app, our Java clients [can] constantly load/insert rows
> into the db. I would like to throttle these inserts (1.5 million rows per
> hr) from the Java app, based on current 'busy state' of the server.
> It would be nice if I can get the server CPU utilization (and other key
> factors) from db2 via jdbc. However, it needs to be low cost, since it
> would likely run every x seconds.

I don't think that DB2 provides an interface to retrieve that kind of
information. But you could resort to a user-defined function (UDF). The
function can be called using "VALUES <fct>()", which is as light-weight as
it can possible get.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

On Mon, 28 Aug 2006 12:19:27 +0200, Knut Stolze wrote:

> natG wrote:
>
>> On a warehouse app, our Java clients [can] constantly load/insert rows
>> into the db. I would like to throttle these inserts (1.5 million rows per
>> hr) from the Java app, based on current 'busy state' of the server.
>> It would be nice if I can get the server CPU utilization (and other key
>> factors) from db2 via jdbc. However, it needs to be low cost, since it
>> would likely run every x seconds.
>
> I don't think that DB2 provides an interface to retrieve that kind of
> information. But you could resort to a user-defined function (UDF). The
> function can be called using "VALUES <fct>()", which is as light-weight as
> it can possible get.
hmmm... But I have no idea what the function needs to do. Is there a db2
function that reports server load? Or how many inserts per second its
doing *now*, or how many tps its doing.

Thanks
nat

natG wrote:

> On Mon, 28 Aug 2006 12:19:27 +0200, Knut Stolze wrote:
>
>> natG wrote:
>>
>>> On a warehouse app, our Java clients [can] constantly load/insert rows
>>> into the db. I would like to throttle these inserts (1.5 million rows
>>> per hr) from the Java app, based on current 'busy state' of the server.
>>> It would be nice if I can get the server CPU utilization (and other key
>>> factors) from db2 via jdbc. However, it needs to be low cost, since it
>>> would likely run every x seconds.
>>
>> I don't think that DB2 provides an interface to retrieve that kind of
>> information. But you could resort to a user-defined function (UDF). The
>> function can be called using "VALUES <fct>()", which is as light-weight
>> as it can possible get.
>
> hmmm... But I have no idea what the function needs to do. Is there a db2
> function that reports server load? Or how many inserts per second its
> doing *now*, or how many tps its doing.

I thought you were interested in the CPU utilization. For the
inserts/transactions per second thing, you could employ the DB2 snapshot
and event monitor facilities.

If it is the CPU load you're after, we are really talking about a system
call. The question is then which OS platform your DB2 server is running
on.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

On Tue, 29 Aug 2006 00:34:48 +0200, Knut Stolze wrote:

> natG wrote:
>
>> On Mon, 28 Aug 2006 12:19:27 +0200, Knut Stolze wrote:
>>
>>> natG wrote:
>>>
>>>> On a warehouse app, our Java clients [can] constantly load/insert rows
>>>> into the db. I would like to throttle these inserts (1.5 million rows
>>>> per hr) from the Java app, based on current 'busy state' of the server.
>>>> It would be nice if I can get the server CPU utilization (and other key
>>>> factors) from db2 via jdbc. However, it needs to be low cost, since it
>>>> would likely run every x seconds.
>>>
>>> I don't think that DB2 provides an interface to retrieve that kind of
>>> information. But you could resort to a user-defined function (UDF). The
>>> function can be called using "VALUES <fct>()", which is as light-weight
>>> as it can possible get.
>>
>> hmmm... But I have no idea what the function needs to do. Is there a db2
>> function that reports server load? Or how many inserts per second its
>> doing *now*, or how many tps its doing.
>
> I thought you were interested in the CPU utilization. For the
> inserts/transactions per second thing, you could employ the DB2 snapshot
> and event monitor facilities.
>
> If it is the CPU load you're after, we are really talking about a system
> call. The question is then which OS platform your DB2 server is running
> on.
Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)
The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
Thank you much.
nat

natG wrote:

> Indeed, it's the cpu utilization I'm after. (But if that's too tough or
> costly to get, inserts /ps or tps will do.)

Getting the CPU load would be _much_ easier than the other metrics you
mentioned. The CPU load can be retrieved with a system call; the others
require some accounting work to be done by DB2.

> The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.

So you're on Linux. I'll see if I can figure out how the CPU load can be
collected on that platform and get back to you in a couple days...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

On Wed, 30 Aug 2006 00:51:09 +0200, Knut Stolze wrote:

> natG wrote:
>
>> Indeed, it's the cpu utilization I'm after. (But if that's too tough or
>> costly to get, inserts /ps or tps will do.)
>
> Getting the CPU load would be _much_ easier than the other metrics you
> mentioned. The CPU load can be retrieved with a system call; the others
> require some accounting work to be done by DB2.
>
>> The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
>
> So you're on Linux. I'll see if I can figure out how the CPU load can be
> collected on that platform and get back to you in a couple days...
Thats very nice of you. Thank you much.
nat

Knut Stolze wrote:
> natG wrote:
>
>> Indeed, it's the cpu utilization I'm after. (But if that's too tough or
>> costly to get, inserts /ps or tps will do.)
>
> Getting the CPU load would be _much_ easier than the other metrics you
> mentioned. The CPU load can be retrieved with a system call; the others
> require some accounting work to be done by DB2.
>
>> The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
>
> So you're on Linux. I'll see if I can figure out how the CPU load can be
> collected on that platform and get back to you in a couple days...

You can just read /proc/loadavg.

[ianbjor@kwaiken ~]$ cat /proc/loadavg
0.18 0.07 0.02 1/133 20942

The first 3 columns are the 1, 5 and 15 minute load averages.

On Tue, 29 Aug 2006 20:00:43 -0700, Ian wrote:

> Knut Stolze wrote:
>> natG wrote:
>>
>>> Indeed, it's the cpu utilization I'm after. (But if that's too tough or
>>> costly to get, inserts /ps or tps will do.)
>>
>> Getting the CPU load would be _much_ easier than the other metrics you
>> mentioned. The CPU load can be retrieved with a system call; the others
>> require some accounting work to be done by DB2.
>>
>>> The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
>>
>> So you're on Linux. I'll see if I can figure out how the CPU load can be
>> collected on that platform and get back to you in a couple days...
>
> You can just read /proc/loadavg.
>
> [ianbjor@kwaiken ~]$ cat /proc/loadavg
> 0.18 0.07 0.02 1/133 20942
>
> The first 3 columns are the 1, 5 and 15 minute load averages.

Aye. Thanks for the tip. Three questions.

1. Is there something more fine-grained, closer to the last few seconds or
preferably at the exact moment it was called.
2. How do I wrap it in a UDF in a way that I don't need to echo the cat to
a temp file? Writing to a temp file every few seconds will in itself cause
overhead.
3. Would you know, if in a dual-processor system, the loadavg is a
combination of both processors?

Thank you much.
-nat

Ian wrote:

> Knut Stolze wrote:
>> natG wrote:
>>
>>> Indeed, it's the cpu utilization I'm after. (But if that's too tough or
>>> costly to get, inserts /ps or tps will do.)
>>
>> Getting the CPU load would be _much_ easier than the other metrics you
>> mentioned. The CPU load can be retrieved with a system call; the others
>> require some accounting work to be done by DB2.
>>
>>> The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
>>
>> So you're on Linux. I'll see if I can figure out how the CPU load can be
>> collected on that platform and get back to you in a couple days...
>
> You can just read /proc/loadavg.

Exactly...

So here is the code to do that:
--------------------------------------------------------------------
#include <sqludf.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <errno.h>

/*
* Function to extract the CPU load for the past 1 minute (Linux-specific).

CREATE FUNCTION getCPULoad()
RETURNS DOUBLE
EXTERNAL NAME 'load_udf!getCPULoad'
LANGUAGE C
PARAMETER STYLE SQL
SPECIFIC getCPULoad
DETERMINISTIC
NO EXTERNAL ACTION
NOT FENCED
NO FINAL CALL
RETURNS NULL ON NULL INPUT
ALLOW PARALLEL@
*/
void SQL_API_FN getCPULoad(
SQLUDF_DOUBLE *load,
SQLUDF_NULLIND *load_ind,
SQLUDF_TRAIL_ARGS)
{
char str[100] = { '\0' };
char *endPtr = NULL;
FILE *f = NULL;

/* initialize output parameters */
*load = 0.0;
*load_ind = -1;

/* open file and read its contents */
f = fopen("/proc/loadavg", "r");
if (!f) {
memcpy(SQLUDF_STATE, "38L01", SQLUDF_SQLSTATE_LEN);
snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN,
"Could not open file '/proc/loadavg");
SQLUDF_MSGTX[SQLUDF_MSGTEXT_LEN-1] = '\0';
goto exit;
}
fread(str, 1, sizeof str, f);
if (ferror(f)) {
memcpy(SQLUDF_STATE, "38L02", SQLUDF_SQLSTATE_LEN);
snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN,
"Failure reading file '/proc/loadavg");
SQLUDF_MSGTX[SQLUDF_MSGTEXT_LEN-1] = '\0';
goto exit;
}

/* extract the CPU load information */
*load = strtod(str, &endPtr);
if (endPtr == str || errno == ERANGE) {
memcpy(SQLUDF_STATE, "38L03", SQLUDF_SQLSTATE_LEN);
snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN,
"Failure extracting CPU load from file '/proc/loadavg");
SQLUDF_MSGTX[SQLUDF_MSGTEXT_LEN-1] = '\0';
goto exit;
}
*load_ind = 0;

exit:
if (f != NULL) {
fclose(f);
}
}
--------------------------------------------------------------------

This can be compiled with the "bldrtn" script shipped with the DB2 samples
and then the above CREATE FUNCTION statement registers the UDF in the
database. Then you just have to call:

VALUES getCPULoad()

and process the output like any other result set.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany