How to get the current DB2 Version on v8 and higher.

[shorti]

I reviewed a similar post on an older version of DB2. I was wondering
if there was anything available now that is more precise in gathering
the db2 version for the new v8 calls.

I am running DB2 v8 FP9 and was wondering if anyone knows if there is
an existing API (or perhaps an easy way) to obtain the current DB2
version on the machine?

I have only found one way so far where you can use the statement as
follows:
"SELECT service_level, fixpack_num FROM TABLE
(sysproc.env_ge t_inst_info())
as INSTANCEINFO"

I can obtain values such as service_level = DB2 v8.1.1.88 and
fixpack_num
= 9.

These two fields combined tell me this is define "db2Version822" . I
need this
value in the new calls such as db2CfgGet().

This seems like a very tedious way to determine the predefined value as
it
took me some research to even relate the information in the system
table to
the DB2 defines.

Since DB2 now requires a version number to be passed in to these API
calls, does anyone know of a way to get the direct value? This could
be db2Version810 or SQL_REL8100 or even 8.1.0.0. Any of these would be
better than trying to determine which service level and fixpack relates
to the version.

FYI...here are the defines:

#define db2Version810 SQL_REL8100 //Version 8.1.0.0
#define db2Version812 SQL_REL8102 //Version 8.1.0.2
#define db2Version814 SQL_REL8104 //Version 8.1.0.4
#define db2Version820 SQL_REL8200 //Version 8.2.0.0
#define db2Version822 SQL_REL8202 //Version 8.2.0.2

Thanks

[Jan M. Nelken]

shorti wrote:

I reviewed a similar post on an older version of DB2. I was wondering
if there was anything available now that is more precise in gathering
the db2 version for the new v8 calls.

I am running DB2 v8 FP9 and was wondering if anyone knows if there is
an existing API (or perhaps an easy way) to obtain the current DB2
version on the machine?

I have only found one way so far where you can use the statement as
follows:
"SELECT service_level, fixpack_num FROM TABLE
(sysproc.env_ge t_inst_info())
as INSTANCEINFO"

I can obtain values such as service_level = DB2 v8.1.1.88 and
fixpack_num
= 9.

These two fields combined tell me this is define "db2Version822" . I
need this
value in the new calls such as db2CfgGet().

This seems like a very tedious way to determine the predefined value as
it
took me some research to even relate the information in the system
table to
the DB2 defines.

Since DB2 now requires a version number to be passed in to these API
calls, does anyone know of a way to get the direct value? This could
be db2Version810 or SQL_REL8100 or even 8.1.0.0. Any of these would be
better than trying to determine which service level and fixpack relates
to the version.

FYI...here are the defines:

#define db2Version810 SQL_REL8100 //Version 8.1.0.0
#define db2Version812 SQL_REL8102 //Version 8.1.0.2
#define db2Version814 SQL_REL8104 //Version 8.1.0.4
#define db2Version820 SQL_REL8200 //Version 8.2.0.0
#define db2Version822 SQL_REL8202 //Version 8.2.0.2

Thanks

Is output of db2level command sufficient for you?

D:\Working>db2l evel
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with
level identifier "03050106".
Informational tokens are "DB2 v8.1.11.958", "s060105", "WR21365", and FixPak
"11".
Product is installed at "D:\SQLLIB" .

[shorti]

db2level gives me the same information as the
sysproc.env_get _inst_info() stated above. The only difference is I can
use the select statement above to fetch the data into a host variable
so I can do a string compare or something similar. In your case, the
level is DB2 v8.1.11.958 with fixpak 11 is the same as which official
release listed above?? Is it the same as db2Version822 or is that now
a new level such as db2Version824? I would probably have to call DB2
to verify where that matches each time they came out with a new fixpak
or release level.

Many of the v8 API calls are expecting one of the db2Uint32 defines on
the list (db2Version810, db2Version812,d b2Version814, etc ). I would
think there would be an easier way to get the version since it is now
required but I cannot seem to find the information.

[shorti]

One thought from a coworker was, since the defines for the db2 Version
are placed in the db2ApiDf. h and sql.h, scan the header file for the
last db2Version and use it with the assumption that the most current
level must be what is on the machine otherwise it would not exist in
the header. I look at this as being potentially dangerous as I cannot
control the accuracy of the file. It is a possibility though.

[Knut Stolze]

shorti wrote:

One thought from a coworker was, since the defines for the db2 Version
are placed in the db2ApiDf. h and sql.h, scan the header file for the
last db2Version and use it with the assumption that the most current
level must be what is on the machine otherwise it would not exist in
the header. I look at this as being potentially dangerous as I cannot
control the accuracy of the file. It is a possibility though.

Now that will be rather troublesome!!! The db2Version that you specify for
the API calls identifies the data structures send to the function. If the
structure changes and you specify a newer db2Version, you will have the
older structure as prepared by your code being interpreted as the newer
one. Then you have "garbage in - garbage out".

I guess the basic question is here: what exactly do you need the version
information for. Do you have to deal with different DB2 versions? If yes,
can't you stick with the oldest one like db2Version80 or so? Or are there
any changes to the APIs in db2Version82 that you like to exploit?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

shorti wrote:

One thought from a coworker was, since the defines for the db2 Version
are placed in the db2ApiDf. h and sql.h, scan the header file for the
last db2Version and use it with the assumption that the most current
level must be what is on the machine otherwise it would not exist in
the header. I look at this as being potentially dangerous as I cannot
control the accuracy of the file. It is a possibility though.

Hmm.. why would one want to auto-fill-in these values?
The reason behind them is that it allows IBM to evolve the API without
breaking existing apps.
There is nothing wrong with being on connecting to DB2 V8.2 and using
the V8.1 API level.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[shorti]

Version 8 has new API calls that require the db2Uint32 db2Version
number. I need the current db2Version number to pass into these calls.
One example I noted above was db2CfgGet ( db2Uint32 versionNumber,
void *pParmStruct, struct sqlca *pSqlca);

The format for this db2Version number is 8010000 for v8.1. db2level
will give me a release level of DB2 8.1.1.88 with fixpak 9. This
equates to db2Version number 8020200. I Only know this from the many
hours of research I have done to figure it out.

Jan above gave an example of a release newer than mine: "DB2
v8.1.11.958", Fixpak "11". So what db2Version is that?

Is this also part of db2Version number 8020200 or is this in some new
db2Version...pe rhaps 80204000? I would have to call DB2 every time
there was a new release to determine what to set my global to.

The reason I would want to update the global each time there was a db2
version change is to take advantage of the changes in the new release
or perhaps pick up a fix that I am using in my code. Also, I would
have to make sure I dont use functionality if the machine has a
downlevel version of db2 on it.

Here is an example: Prior to fixpack 7 you could not update the
database configuration parameter STMTHEAP while online to the database.
I added code to do this now. If I hard coded to version 8.1 instead
of 8.2 my call the db2CfgSet fails. With the correct version of db2 it
works. In the future I want to take advantage of new functionality
without having to change the hard coded value. Also, if a machine has
downlevel db2 code I want to have code in place to eliminate new
functionality so it wont fail. In this example, if I know this machine
is at version 8.1 I do not want to attempt to adjust the setting to
STMTHEAP so it wont fail and take a customer down.

[Knut Stolze]

shorti wrote:

The reason I would want to update the global each time there was a db2
version change is to take advantage of the changes in the new release
or perhaps pick up a fix that I am using in my code.
But the version passed to the API functions doesn't have anything to do with
DB2 fixes. It only defines which version of the data structures are being
passed to DB2 so that it can do the right thing. If you change the version
in your code, you have to verify that none of the data structures have
changed. If you keep everything as it is, you don't have to change the
version - regardless of the current version of the DB2 server you are
using.
Here is an example: Prior to fixpack 7 you could not update the
database configuration parameter STMTHEAP while online to the database.
I added code to do this now. If I hard coded to version 8.1 instead
of 8.2 my call the db2CfgSet fails.
No. The call fails if you're connected to an 8.1 DB2 server that doesn't
allow such an update. When you are connected to a 8.2 server using
db2Version81, the call will succeed. The db2Version for the APIs and the
actual version/release of the database server are two different things.
With the correct version of db2 it
works. In the future I want to take advantage of new functionality
without having to change the hard coded value. Also, if a machine has
downlevel db2 code I want to have code in place to eliminate new
functionality so it wont fail. In this example, if I know this machine
is at version 8.1 I do not want to attempt to adjust the setting to
STMTHEAP so it wont fail and take a customer down.

What about intercepting the error messages? After all, you do need error
handling anyways...

And why do you think it would "take a customer down"?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[shorti]

>Here is an example: Prior to fixpack 7 you could not update the

database configuration parameter STMTHEAP while online to the database.
I added code to do this now. If I hard coded to version 8.1 instead
of 8.2 my call the db2CfgSet fails.
No. The call fails if you're connected to an 8.1 DB2 server that doesn't
allow such an update. When you are connected to a 8.2 server using
db2Version81 , the call will succeed. The db2Version for the APIs and the
actual version/release of the database server are two different things.
Then DB2 has a problem. I am running db2Version822. I call to bump
the STMTHEAP parameter if I received a certain SQLCODE. In my test
function I set the db2 version to db2Versoin810 because it was in the
example in the DB2 Info Center description of the function and I did
not change it to the true version because I am still trying to
determine how to easily get the current version (and is the reason for
this post).

The call failed and I could not update the setting. I opened a pmr
with DB2 thinking it was broken (I forgot I had the db2Version set
wrong in the call). After a couple of discussions with DB2 support we
found the problem. I changed the version to db2Version822, which is
the correct version that contained the change, and it works. If you
are telling me it should have worked no matter which version I passed
in then db2 has a bug. However, I dont see the purpose of requiring
the caller to pass in the db2Version if DB2 does not intend to use it.
What about intercepting the error messages? After all, you do need error
handling anyways...

I have plenty of error handling. If fact, this very function is all
about error handling. If I get an SQL error from any command this
function does various things to deal with it. Why provoke an error
when I can avoid it? If I know the call wont work because I am on the
wrong db2 version then why make the call? I am error handling so I
should be smart enough to know I cannot perform that action. If I do
not verify the version I am using then I am suddenly the cause of the
error.

'Taking the customer down'...There is much more to this then I have
time to really explain. Whenever you do something you are not suppose
to you could potentially hit a problem that brings down the
machine....this is the worse case of course. But, even in the simplest
case you can create problems unnecessarily. For instance, many of the
database and Database Manager configures will not take affect unless
you start and stop the database instance. To do this all processes are
forced off the database. This sounds simple but how do those processes
recover? Do they spin and wait for you to allow them to reconnect? Do
they error on that database operation and keep trying the next
operation in thier queue? When will the database become available?
At any rate, if the processes cannot connect to the database to do work
your customer is down. Let me say, changing the STMTHEAP would not be
an example where I would do a force stop but some of the settings, like
out of memory, would be. If I know there is a way to cure the problem
without doing this then I want to attempt it.

The point of this post is not how I can avoid giving the API calls the
true version but how can I get the correct version so I can determine
the best course of recovery. I realize there may not be many using the
new API calls because they are running existing code with the old APIs
that have already been established. This is new developement and we
are trying to utilize the new API calls...though some days I have to
resist the urge to just go back to the old!

Last, I am not a DB2 expert. In fact, DB2 is very new to me so if I am
missing some point I appreciate the patience and explanation. I am not
really sure what the purpose is for the addition of the version in
these calls other then in case someone wants to use older functionality
even if they are running new code. I would think anything new would be
an improvement to the old but what do I know!?

[Knut Stolze]

shorti wrote:

No. The call fails if you're connected to an 8.1 DB2 server that doesn't
allow such an update. When you are connected to a 8.2 server using
db2Version8 1, the call will succeed. The db2Version for the APIs and the
actual version/release of the database server are two different things.
Then DB2 has a problem. I am running db2Version822. I call to bump
the STMTHEAP parameter if I received a certain SQLCODE. In my test
function I set the db2 version to db2Versoin810 because it was in the
example in the DB2 Info Center description of the function and I did
not change it to the true version because I am still trying to
determine how to easily get the current version (and is the reason for
this post).

As I said, this won't be necessary.
The call failed and I could not update the setting. I opened a pmr
with DB2 thinking it was broken (I forgot I had the db2Version set
wrong in the call). After a couple of discussions with DB2 support we
found the problem. I changed the version to db2Version822, which is
the correct version that contained the change, and it works. If you
are telling me it should have worked no matter which version I passed
in then db2 has a bug. However, I dont see the purpose of requiring
the caller to pass in the db2Version if DB2 does not intend to use it.
You _did_ not set a wrong version by using "db2Version810" . And as was
explained before, the version information tells DB2 which data structure
you are providing so that it knows how to take it apart and get the proper
values. That way the structure can change without impacting your
application - which is apparently exactly what you need. There is no point
in interpreting the version number in any other way.

If I got your above description right, then you're doing pretty much this:
---------------------------- snip ----------------------------------
#include <stdio.h>
#include <db2ApiDf.h>

int main()
{
struct db2Cfg cfg;
struct db2CfgParam param;
struct sqlca sqlca;
sqlint32 stmtHeapSize = 2048;

memset(&cfg, 0x00, sizeof cfg);
memset(&param, 0x00, sizeof param);
memset(&sqlca, 0x00, sizeof sqlca);

cfg.numItems = 1;
param.token = SQLF_DBTN_STMT_ HEAP;
param.ptrvalue = &stmtHeapSiz e;
param.flags = 0;
cfg.paramArray = &param;
cfg.flags = db2CfgDatabase | db2CfgImmediate ;
cfg.dbname = "TEST";

int rc = db2CfgSet(
db2Version810,
&cfg,
&sqlca);
printf("rc = %d\n", rc);
printf("sqlcode = %d\n", sqlca.sqlcode);
}
---------------------------- snip ----------------------------------

This works quite well for me against a DB2 V8.2.3 server (DB2 v8.1.2.96).
So I would still say that there is some particular problem in your
environment (maybe your code?) but DB2 does work as was stated here before,
i.e. you can (and should) match the version number with the data structures
you are providing.
I have plenty of error handling. If fact, this very function is all
about error handling. If I get an SQL error from any command this
function does various things to deal with it. Why provoke an error
when I can avoid it? If I know the call wont work because I am on the
wrong db2 version then why make the call?
Because it will be the exception that the call won't work. Otherwise you
would need a matrix that says which version of DB2 did not support a
particular configuration parameter.
For instance, many of the
database and Database Manager configures will not take affect unless
you start and stop the database instance. To do this all processes are
forced off the database. This sounds simple but how do those processes
recover?
A few comments:
(1) You only need to restart the instance if the configuration parameter
could be set successfully, and
(2) many parameters can be set on the fly and take effect on the fly.
(3) Database configuration parameters do _not_ require an instance restart.
You might have to deactivate/activate the database unless you're changing
an online parameter.
(4) An application should be able to deal with outages anyway.
Last, I am not a DB2 expert. In fact, DB2 is very new to me so if I am
missing some point I appreciate the patience and explanation. I am not
really sure what the purpose is for the addition of the version in
these calls other then in case someone wants to use older functionality
even if they are running new code. I would think anything new would be
an improvement to the old but what do I know!?

Let me try again. Let's say DB2 V8.1 has an API "db2SomethingIm portant",
which takes a data structure like this as input:

struct params {
int value1;
int value2;
};

Now some new logic is added in DB2 V9.1, which has an impact on
"SomethingImpor tant". In particular, another parameter can be specified in
the parameter structure:

struct newParams {
int value1;
int value2;
int newValue;
};

How would DB2 V9 be able to distinguish whether a pointer to the new
structure is given or a pointer to the old one? Three options:
(1) Don't support the additional parameter in the API - not a really good
idea as DB2 would be stuck again with the problems of (current) the old
API.
(2) Use a new function with a different name. Not so great either because
it clutters the API in the long run, given that the old variations need to
be kept araund for backwards compatibility.
(3) Use a number that describes which version of the structure was provided
by the application. If the number is V8, then the new attribute is not
there and will not be accessed. If it is V9, then it is there and DB2 can
use it.

Option (3) was chosen by the DB2 development. Now, you see that setting the
newest version is a particular bad idea if the structure provided doesn't
match it as DB2 would attempt to access the "newValue" attribute which is
not there if your code does not set it up. Basically, you would hit either
a segmentation violation or DB2 would read another memory area, resulting
in bogus values. The second will probably cause even more harm as it is
not as easily noticable.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany