How to check db2 version

Sql Problem Overview

How to check db2 version on Z/OS using only SQL commands?

Thanks, Melita

Sql Solutions

Solution 1 - Sql

You can try the following query:

SELECT service_level, fixpack_num FROM TABLE
  (sysproc.env_get_inst_info())
  as INSTANCEINFO

It works on LUW, so I can't guarantee that it'll work on z/OS, but it's worth a shot.

Solution 2 - Sql

SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1

Solution 3 - Sql

There is also the env_inst_info admin view. As with CanSpice I can only vouch for LUW, but there should at least be a similar view available for Z/OS.

SELECT * FROM SYSIBMADM.ENV_INST_INFO

Solution 4 - Sql

I used

SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO());

from tyranitar and that worked on Z/OS. Here's what I got:

SERVICE_LEVEL
DB2 v9.7.0.6

I'd vote up if I could! Thanks!!

Solution 5 - Sql

There is a typo in your SQL. Fixed version is below:

SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1;

I ran this on the IBM Mainframe under Z/OS in QMF and got the following results. We are currently running DB2 Version 8 and upgrading to Ver 10.

DSN08015  -- Format seems to be DSNVVMMM
-- PPP IS PRODUCT STRING 'DSN'
-- VV IS VERSION NUMBER E.G. 08
-- MMM IS MAINTENANCE LEVEL E.G. 015

Solution 6 - Sql

To find out the fixpak information using command prompt: db2level

To find out the version and license information using command prompt: db2licm -l

C:\Users\Administrator>db2level
DB21085I  This instance or install (instance name, where applicable: "DB2")
uses "64" bits and DB2 code release "SQL10051" with level identifier
"0602010E".
Informational tokens are "DB2 v10.5.100.63", "s130816", "IP23521", and Fix Pack

"1".
Product is installed at "C:\SQLLIB" with DB2 Copy Name "DB2COPY1".


C:\Users\Administrator>db2licm -l
Product name:                     "IBM Data Server Client"
Product identifier:               "db2client"
Version information:              "10.5"

Solution 7 - Sql

Both worked for me.

SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO());

SELECT * FROM SYSIBMADM.ENV_INST_INFO;

Solution 8 - Sql

You can query for the built-in session variables with SQL. To identify the version of DB2 on z/OS, you need the SYSIBM.VERSION variable. This will return the PRDID - the product identifier. You can look up the human-readable version in the Knowledge Center.

SELECT GETVARIABLE('SYSIBM.VERSION')
FROM SYSIBM.SYSDUMMY1;

-- for example, the above returns DSN10015
-- DSN10015 identifies DB2 10 in new-function mode (see second link above)

Solution 9 - Sql

Try the first or the second:

SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO());
SELECT * FROM TABLE(SYSPROC.ENV_GET_PROD_INFO());
SELECT * FROM TABLE(SYSPROC.ENV_GET_SYS_INFO());

Solution 10 - Sql

Another one in v11:

select CURRENT APPLICATION COMPATIBILITY from sysibm.sysdummy1

Result:

V11R1

It's not the current version, but the current configured level for the application.

Solution 11 - Sql

In z/OS while on version 10, use of CURRENT APPLICATION COMPATIBILITY is not allowed. You will have to resort to:

SELECT GETVARIABLE('SYSIBM.VERSION') AS VERSION,
       GETVARIABLE('SYSIBM.NEWFUN')  AS COMPATIBILITY
FROM SYSIBM.SYSDUMMY1;

Here is a link to all the variables available: https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_refs2builtinsessionvars.html#db2z_refs2builtinsessionvars

Solution 12 - Sql

For DB2:

"SELECT * FROM SYSIBMADM.ENV_INST_INFO" - SERVICE_LEVEL

Solution 13 - Sql

In AIX you can try:

db2level

Example output:

db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL09077" with level
identifier "08080107".
Informational tokens are "DB2 v9.7.0.7", "s121002", "IP23367", and Fix Pack
"7".
Product is installed at "/db2_09_07".

Solution 14 - Sql

db2ls command will display the db2level along with the install path and install date.

To determine the specific product installed:

db2ls -p -q -b <installpath>

on db2ls command.

The following will appear:

Install Path       Level   Fix Pack   Special Install Number   Install Date    Installer UID
--------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.7  9.7.0.7        7                      Thu Aug  1 12:25:53 2013 CDT     0

visit IBM Website

Solution 15 - Sql

SELECT GETVARIABLE(('SYSIBM.VERSION')
 FROM SYSIBM.SYSDUMMY1;
-- PPP IS PRODUCT STRING 'DSN'
-- VV IS VERSION NUMBER E.G., 10, 11
-- M IS MAINTENANCE LEVEL E.G. 5

-DISPLAY GROUP
 THIS WILL DISPLAY THE LEVEL CM, ENFM, N

Content Type	Original Author	Original Content on Stackoverflow
Question	gizmo	View Question on Stackoverflow
Solution 1 - Sql	CanSpice	View Answer on Stackoverflow
Solution 2 - Sql	bugs	View Answer on Stackoverflow
Solution 3 - Sql	Christian Maslen	View Answer on Stackoverflow
Solution 4 - Sql	CCRDad	View Answer on Stackoverflow
Solution 5 - Sql	Shariq A	View Answer on Stackoverflow
Solution 6 - Sql	Ramesh John	View Answer on Stackoverflow
Solution 7 - Sql	ramit girdhar	View Answer on Stackoverflow
Solution 8 - Sql	orbanbalage	View Answer on Stackoverflow
Solution 9 - Sql	Alberto	View Answer on Stackoverflow
Solution 10 - Sql	salva carrasco	View Answer on Stackoverflow
Solution 11 - Sql	Darek	View Answer on Stackoverflow
Solution 12 - Sql	Sarang Golwalkar	View Answer on Stackoverflow
Solution 13 - Sql	Doug	View Answer on Stackoverflow
Solution 14 - Sql	Suliman Farzat	View Answer on Stackoverflow
Solution 15 - Sql	Gerald Hodge	View Answer on Stackoverflow