how to tell if database is activated?

[kenfar]

I'd like our daily quality & policy testing framework to also test if
our databases are activated. However, I haven't found a good way to do
this yet - every approach has issues:

1. get snapshot for database on [db] - I can't tell if the database is
activated - or if it is inactivated by has a connection

2. list active databases - same as above

3. activate database and check return code - but I don't necessarily
want to activate them - i just want to know if they aren't.

So, any suggestions for how to just tell if a database is activated or
not while others are on the system, and without actually activating it?

Thanks

Ken

[Knut Stolze]

Re: how to tell if database is activated?

kenfar wrote:

I'd like our daily quality & policy testing framework to also test if
our databases are activated. However, I haven't found a good way to do
this yet - every approach has issues:
>
1. get snapshot for database on [db] - I can't tell if the database is
activated - or if it is inactivated by has a connection
>
2. list active databases - same as above

That's the way to go. If a database is not activated, it does not have a
connection.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[kenfar]

Re: how to tell if database is activated?

Knut Stolze wrote:

kenfar wrote:
>

I'd like our daily quality & policy testing framework to also test if
our databases are activated. However, I haven't found a good way to do
this yet - every approach has issues:

1. get snapshot for database on [db] - I can't tell if the database is
activated - or if it is inactivated by has a connection

2. list active databases - same as above

>
That's the way to go. If a database is not activated, it does not have a
connection.

Unfortunately, this does not distinguish between a database that was
explicitely activated and one that merely has a connection:


---- test #1: deactivated db with no connection - should return rc of 2
1. db2 connect reset
2. db2 deactivate database rptmart
3. db2 list applications - confirm nobody else is on
4. db2 list active databases
SQL1611W No data was returned by Database System Monitor.
echo $?
2
RESULT = test passed

---- test #2: deactivated db with a connection - should return rc of 2
1. db2 connect reset
2. db2 deactivate database rptmart
3. db2 connect to rptmart
4. db2 list active databases
<snip>
echo $?
0
RESULT = test failed

---- test #3: activated db with no connection - should return rc of 0
1. db2 connect reset
2. db2 activate database rptmart
3. db2 list applications - confirm nobody else is on
4. db2 list active databases
<snip>
echo $?
0
RESULT = test passed

Can you think of a way of testing whether or not it was activated that
isn't confused by an active (but temporary) connection?


Thanks again,

ken

[Knut Stolze]

Re: how to tell if database is activated?

kenfar wrote:

Knut Stolze wrote:

>kenfar wrote:
>>

I'd like our daily quality & policy testing framework to also test if
our databases are activated. However, I haven't found a good way to do
this yet - every approach has issues:
>
1. get snapshot for database on [db] - I can't tell if the database is
activated - or if it is inactivated by has a connection
>
2. list active databases - same as above

>>
>That's the way to go. If a database is not activated, it does not have a
>connection.

>
Unfortunately, this does not distinguish between a database that was
explicitely activated and one that merely has a connection:

[...]

Can you think of a way of testing whether or not it was activated that
isn't confused by an active (but temporary) connection?

If I understand you correctly, you are not interested in the fact whether a
database was activated but was _explicitly_ activated? The thing is that a
connect to a database will automatically and implicitly activate it.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[kenfar]

Re: how to tell if database is activated?

[...]

Can you think of a way of testing whether or not it was activated that
isn't confused by an active (but temporary) connection?

>
If I understand you correctly, you are not interested in the fact whether a
database was activated but was _explicitly_ activated?

exactly

The thing is that a connect to a database will automatically and implicitly activate it.

right - but this can take a few seconds, and when the connection is
terminated db2 will then deactivate that database.

In this scenario every connection will have an extra 4 seconds of
start-up time (on my test server) unless it gets lucky and arrives
while another connection is still running.

By using the activate I'd like to reduce the time to start a new
connection to 0.1 second.

We normally activate all databases in production - but occasionally
slip up and one ends up running non-activated. I'd like our process to
alert us when this happens.

[p175]

Re: how to tell if database is activated?

We normally activate all databases in production - but occasionally
slip up and one ends up running non-activated. I'd like our process to
alert us when this happens.

Why can't you just issue periodic ACTIVATE commands. If it's already
activated it will simply say so and give you a SQL1490W, if not it will
activate it.

If you simply want to KNOW, then unless you get a SQL1490W response,
issue a further DEACTIVATE when you get a DB20000I response.

Not sure I see a problem where to be honest with you as if you issue an
ACTIVATE when it has NOT been activated but a user is logged on, then
you will get a SQL1494W rather than a SQL1490W.

D:\TEMP>db2 ? SQL1494W


SQL1494W Activate database is successful, however, there is
already a connection to the database.

Explanation:

There is already database connection on one or more nodes.

User Response:

No action required.


D:\TEMP>db2 ? SQL1490W


SQL1490W Activate database is successful, however, the database
has already been activated on one or more nodes.

Explanation:

Database has already been explicitly started (activated) on one
or more nodes.

User Response:

No action required.

[kenfar]

Re: how to tell if database is activated?

p175 wrote:

We normally activate all databases in production - but occasionally
slip up and one ends up running non-activated. I'd like our process to
alert us when this happens.

>
Why can't you just issue periodic ACTIVATE commands. If it's already
activated it will simply say so and give you a SQL1490W, if not it will
activate it.

That's a worst-case scenario: as a rule our policy, data quality, and
consistency-checking tool tests our requirements and environment - and
doesn't change anything.

The activate command will give a rc of 2 on two different situations:
- SQL1490W - already activated
- SQL1494W - wasn't activated before, but a user was connected

and if it was successful with no users on board it returns an rc of 0
with the following msg:
- DBI0000I

So, it can work - i just need to parse the results for a SQL1490W
rather than just check return code. But again, if I could test it
without changing anything that would be preferable.

ken

[Knut Stolze]

Re: how to tell if database is activated?

kenfar wrote:

We normally activate all databases in production - but occasionally
slip up and one ends up running non-activated. I'd like our process to
alert us when this happens.

I don't know if and how you can extract the information whether a DB was
activated explicitly. The health monitor may have this information. You
can probably configure it in such a way that each database has to be
activated and if it is not, a task can be automatically executed to
activate it.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[p175]

Re: how to tell if database is activated?

Try this as a test:

I've had a look at the DB CFG 'database_consi stent' flag before, and
noticed that it 'usually' only returns to a YES state when the database
has been either restarted following all applications having
disconnected, or the database being 'deactivated' or NOT activated.

If that is indeed the case, a consistency state of YES would indicate a
non-active database or one that has had no activity. I just did a few
very simple tests, by logging on and updating a single field, the state
changes from the initial idle state of YES to NO, after commiting the
transaction and logging off, it remained NO. The only way I could get
it back to YES was to deactivate the database.

So, I deactivated and logged on again, made the same change, checked
the state, set to NO, committed and logged off and it's back to YES
again. Stands to reason therefore that if you find it in a YES state
after user activity then the database has not been activated ? You
could use an activity monitor such as evmon_activates to follow
activity and check the consistency flag from the DB CFG afterwards.

Might be flawed logic but .. hey it works .. heh

[Knut Stolze]

Re: how to tell if database is activated?

kenfar wrote:

p175 wrote:

We normally activate all databases in production - but occasionally
slip up and one ends up running non-activated. I'd like our process to
alert us when this happens.

>>
>Why can't you just issue periodic ACTIVATE commands. If it's already
>activated it will simply say so and give you a SQL1490W, if not it will
>activate it.

>
That's a worst-case scenario: as a rule our policy, data quality, and
consistency-checking tool tests our requirements and environment - and
doesn't change anything.
>
The activate command will give a rc of 2 on two different situations:
- SQL1490W - already activated
- SQL1494W - wasn't activated before, but a user was connected
>
and if it was successful with no users on board it returns an rc of 0
with the following msg:
- DBI0000I
>
So, it can work - i just need to parse the results for a SQL1490W
rather than just check return code. But again, if I could test it
without changing anything that would be preferable.

I would write a small C application that evaluates the SQLCODE instead of
the return code of the DB2 CLP.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Re: how to tell if database is activated?

p175 wrote:

Try this as a test:
>
I've had a look at the DB CFG 'database_consi stent' flag before, and
noticed that it 'usually' only returns to a YES state when the database
has been either restarted following all applications having
disconnected, or the database being 'deactivated' or NOT activated.
>
If that is indeed the case, a consistency state of YES would indicate a
non-active database or one that has had no activity. I just did a few
very simple tests, by logging on and updating a single field, the state
changes from the initial idle state of YES to NO, after commiting the
transaction and logging off, it remained NO. The only way I could get
it back to YES was to deactivate the database.
>
So, I deactivated and logged on again, made the same change, checked
the state, set to NO, committed and logged off and it's back to YES
again. Stands to reason therefore that if you find it in a YES state
after user activity then the database has not been activated ? You
could use an activity monitor such as evmon_activates to follow
activity and check the consistency flag from the DB CFG afterwards.
>
Might be flawed logic but .. hey it works .. heh

I don't think this is going to work reliably. At least I would not rely on
it at all. The description of the "database_consi stent" information can be
found here:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000282.htm

It is pretty clear in that this information only shows if at least one
transaction has not yet committed its changes. What is not so clear in the
description, however, is what the term "database" means. Here it can only
refer to the data on disk. So if all transactions are ended _and_ if their
data is written to disk already, then you should see the 'YES'. Otherwise,
you have running transactions or finished transactions with changes still
pending to be flushed. (Such a reading would be consistent with the
sentence "If the system "crashes" while the database is consistent, you do
not need to take any special action to make the database usable." In
particular, no crash recovery would be needed.)

Your observations regarding activating/deactivating and the value of
the 'consistent' db cfg parameter nicely line up with the above
description. Once you disconnect from a database that was not explicitly
activated, the database will be deactivated implicitly. This deactivation
will cause the remaining data changes to be flushed to disk. With an
explicitly activated database, the database remains active. So there is no
need that DB2 writes the changes to disk when you log of. Thus, the
consistency state has to be 'NO'.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[kenfar]

Re: how to tell if database is activated?

I would write a small C application that evaluates the SQLCODE instead of

the return code of the DB2 CLP.

Thanks, but I can handle that. I just disappointed that db2 doesn't
provide a way of finding this out without doing it.


ken

[Raj]

Re: how to tell if database is activated?

Use db2mtrk to see if the database bufferpools are activated, and see
if these is a connection to the database to differential between
explicit activate & activation by connection...
Thanks,
Raj
kenfar wrote:

I would write a small C application that evaluates the SQLCODE instead of
the return code of the DB2 CLP.

>
Thanks, but I can handle that. I just disappointed that db2 doesn't
provide a way of finding this out without doing it.
>
>
ken

[Jayesh Thakrar]

Re: how to tell if database is activated?

You can try the db2pd command instead (see db2pd -h and Developerworks
article for details).

Essentially you can query db2pd for bufferpools as follows -

$ db2pd -alldatabases -bufferpools | grep Database
Database Partition 0 -- Database MKBLDRT -- Active -- Up 11 days
15:49:21
Database Partition 0 -- Database IRIT -- Active -- Up 14 days 23:40:33
Database Partition 0 -- Database WPS51 -- Active -- Up 15 days 01:26:15

Hopefully you can parse the above info and compare the active databases
with your list of all databases that should be active and take action
accordingly.

-- Jayesh

Raj wrote:

Use db2mtrk to see if the database bufferpools are activated, and see
if these is a connection to the database to differential between
explicit activate & activation by connection...
Thanks,
Raj
kenfar wrote:

I would write a small C application that evaluates the SQLCODE instead of
the return code of the DB2 CLP.

Thanks, but I can handle that. I just disappointed that db2 doesn't
provide a way of finding this out without doing it.


ken

[Knut Stolze]

Re: how to tell if database is activated?

Jayesh Thakrar wrote:

You can try the db2pd command instead (see db2pd -h and Developerworks
article for details).
>
Essentially you can query db2pd for bufferpools as follows -
>
$ db2pd -alldatabases -bufferpools | grep Database
Database Partition 0 -- Database MKBLDRT -- Active -- Up 11 days
15:49:21
Database Partition 0 -- Database IRIT -- Active -- Up 14 days 23:40:33
Database Partition 0 -- Database WPS51 -- Active -- Up 15 days 01:26:15
>
Hopefully you can parse the above info and compare the active databases
with your list of all databases that should be active and take action
accordingly.

The thing is that you still can't distinguish between explicitly and
implicitly activated databases.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany