How to check db2 version

Question

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

Thanks, Melita

score 65 · Answer 1 · answered Sep 29 '10 at 16:34

65

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.

answered Sep 29 '10 at 16:34

CanSpice

34,814
10
72
86

3

Damn and blast. I wish things would work the same between the two. – CanSpice Sep 29 '10 at 19:46
Example of execution:`SELECT service_level, fixpack_num FROM TABLE(sysproc.env_get_inst_info()) as INSTANCEINFO ` `DB2 v11.1.4.4','4'` – Rudy Vissers Dec 17 '19 at 12:58

score 23 · Answer 2 · answered Oct 11 '10 at 05:46

23

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

answered Oct 11 '10 at 05:46

Christian Maslen

1,100
9
13

1

For me, it is SYSIBMADM.ENV_SYS_INFO. Since I cannot find the table you provided, I just look at the tables under SYSIBMADM that looks like it. Thanks for this answer I found mine. – Jin Ginusuke Sep 16 '20 at 07:54

bugs · Answer 3 · 2011-11-23T08:57:57.507

22

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

edited Nov 23 '11 at 08:57

answered Nov 22 '11 at 16:03

bugs

237
2
3

3

This returns a value like 'DSN090100'. To see what that means checkout the link in [@Michael Sharek's answer](http://stackoverflow.com/a/3825610/152061). – Brad Cupit Mar 20 '13 at 14:23
2

Link in answer referenced above currently redirects [here](http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.sqlref/src/tpc/db2z_refs2builtinsessionvars.htm). Adding for future reference, in case IBM deprecates the original link. – transistor1 Jun 18 '13 at 16:50
3

I have `SQL0440N No authorized routine named "GETVARIABLE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884` on this – bbozo Feb 25 '16 at 16:22

score 10 · Answer 4 · edited Sep 29 '15 at 19:56

10

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!!

edited Sep 29 '15 at 19:56

apesa

12,163
6
38
43

answered Jul 19 '13 at 16:49

CCRDad

125
1
4

11

This will *not* work on z/OS. You are running on DB2 for Linux, UNIX or Windows to get this result. – Ian Bjorhovde Jul 22 '13 at 19:24
1

Works for me! I get: |INST_NAME|IS_INST_PARTITIONABLE|NUM_DBPARTITIONS|INST_PTR_SIZE|RELEASE_NUM|SERVICE_LEVEL|BLD_LEVEL|PTF|FIXPACK_NUM|NUM_MEMBERS |xxx|0|1|64|060A010E|DB2 v10.5.0.9|s170908|IP24025|9|1 – Frans Dec 15 '18 at 10:55
I confirm that this is not working on z/OS for me too. – Rahul Khimasia Dec 03 '19 at 16:29

score 9 · Answer 5 · edited Sep 30 '15 at 17:45

9

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

edited Sep 30 '15 at 17:45

rogerdeuce

1,471
6
31
48

answered Sep 30 '15 at 17:04

Shariq A

91
1
1

1

I get, "Qualified object name SYSDUMMY1 not valid" on As/400 – eaglei22 May 10 '17 at 14:47

score 8 · Answer 6 · edited Nov 09 '18 at 08:56

8

Both worked for me.

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

or

SELECT * FROM SYSIBMADM.ENV_INST_INFO;

edited Nov 09 '18 at 08:56

Suraj Rao

29,388
11
94
103

answered Nov 08 '18 at 06:03

ramit girdhar

2,272
1
25
26

thanks, the first query gives SERVICE_LEVEL and RELEASE_NUM – sdykae Aug 12 '22 at 17:35

score 7 · Answer 7 · edited Nov 09 '18 at 08:57

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"

score 4 · Answer 8 · answered Feb 14 '17 at 18:44

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)

score 3 · Answer 9 · answered Apr 24 '13 at 08:23

3

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());

answered Apr 24 '13 at 08:23

Alberto

2,881
7
35
66

Works on Ubuntu though – bbozo Feb 25 '16 at 16:28

score 2 · Answer 10 · answered Jul 17 '20 at 13:58

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

score 1 · Answer 11 · edited Mar 24 '19 at 13:07

1

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.

edited Mar 24 '19 at 13:07

double-beep

5,031
17
33
41

answered Mar 24 '19 at 12:49

salva carrasco

21
4

score 0 · Answer 12 · edited Feb 03 '17 at 08:10

0

For DB2:

"SELECT * FROM SYSIBMADM.ENV_INST_INFO" - SERVICE_LEVEL

edited Feb 03 '17 at 08:10

Matteo Baldi

5,613
10
39
51

answered Feb 03 '17 at 07:18

Sarang Golwalkar

11

score 0 · Answer 13 · edited Nov 09 '18 at 08:57

0

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".

edited Nov 09 '18 at 08:57

Suraj Rao

29,388
11
94
103

answered May 09 '17 at 19:25

Doug

1

score 0 · Answer 14 · answered Nov 09 '18 at 08:49

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

score -1 · Answer 15 · edited Mar 31 '15 at 14:58

-1

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

edited Mar 31 '15 at 14:58

Minar Mahmud

2,577
6
20
32

answered Mar 31 '15 at 14:40

Gerald Hodge

1

How to check db2 version

15 Answers15

Linked