by Paul C. Zikopoulos
In late April 2005, IBM released an update, called Version 8.2.2, of the IBM DB2 Universal Database product (DB2 UDB). This update was inspired by functional enhancements for SAP environments, but the reality is that everyone can benefit from the works that has gone into this update.
Traditionally, database administrators (DBAs) can get frustrated when trying to retrieve information about their database servers. On the one hand, they can use graphical tools such as the DB2 UDB Control Center to help them, but many experienced DBAs do not like to use graphical interfaces. Indeed, experienced DBAs tend to administer and maintain their databases using command-line scripts and so on.
On the other hand, the typical method is not necessarily appealing either. Sure, many of the utilities that DB2 UDB exposes for the retrieval of system information are based on a C API, or some sort of DB2 UDB CLP command. This solves the problem of retrieving information from a graphical user interface (GUI), but involves programmatic or syntactical complexities. For example, if you want to get a snapshot of your database using a C API, you have to know how to programmatically call this API. Suppose you had development skills in the Java language, for example; you would have to use a JNI wrapper to call the C APIs provided by DB2 UDB. This involves yet another level of complexity.
Assuming that as a DBA you are neither a programmer nor someone who likes to use a GUI, what options do you have? The SQL API, which DBAs are typically well versed in, represents a common ground by which DBAs can leverage the productivity of a command-line environment–without the complexity.
Since the DB2 UDB V8.1 release, IBM has been working hard to expose more and more features through simple SQL that were once only invoked by a command-line or C API.
DB2 UDB V8.2.2 adds three new user-defined functions (UDFs) that allow authorized remote clients to get details about a DB2 UDB server, including machine, product, and instance information, and so on. Before DB2 UDB V8.2.2, this information was only available via administrative APIs. These news UDFs are added to an ever-expanding group of table functions that return information about the operations of your system (for example, you can use a table UDF to return snapshot monitor information).
These new UDFs are created when a new database is created at the DB2 UDB V8.2.2 level. If you already have an existing database and you have applied the maintenance stream for DB2 UDB V8.2.2 to your existing server, you can use the db2updt command to create these new routines. These new routines are defined in sqlescat.h and placed in the new db2envinfo library located in the sqllib/bin/routine directory.
In this article, I will take you through the details of these new system information UDFs and give you examples of how to use them.
Use SQL to Get Details of Your Server
The new SYSPROC.ENV_GET_SYS_INFO UDF returns information that relates to the server where your DB2 UDB product is installed. This UDF returns the following information:
| OS_NAME | The name of the O/S software |
| OS_VERSION | The version of the O/S |
| OS_RELEASE | The release level of the O/S |
| HOST_NAME | The TCP/IP network name of the system |
| TOTAL_CPUS | The total number of physical CPUs on the system |
| CONFIGURED_CPUS | The number of configured physical CPUs on the system |
| TOTAL_MEMORY | The total size of memory on the system (in MB) |
The following figure shows an example of how to use this UDF:
The results of this UDF tell me that I have my DB2 UDB server installed on a workstation that is running a 32-bit version of Microsoft Windows 5.1 (which is Windows XP technology) and I have installed Service Pack 1. The host name of my machine, the number of CPUs (both physical and enabled with a partition if you are using one), and total memory (in MB) is also shown.
Use SQL to Get Details of the DB2 UDB Product Installed on Your Server
The new SYSPROC.ENV_GET_PROD_INFO UDF can be used to return information about the actual DB2 UDB product installed on your server.
The following information is returned from this UDF:
| INSTALLED_PROD | The ID of the DB2 product (for example, ESE) |
| IS_LICENSED | Is the installed product licensed? (0=No. 1=Yes.) |
| PRODUCT_RELEASE | The release level of the DB2 UDB product |
An example of using this UDF is shown below:
You can see in this figure that I have DB2 UDB Enterprise Server Edition (ESE) installed on my server, it is correctly licensed, and it is installed at the V8.2 release level.
For completeness, here are all the product codes:
| EXP | > | DB2 UDB Express Edition |
| ESE | > | DB2 UDB Enterprise Server Edition |
| WSE | > | DB2 UDB Workgroup Server Edition |
| WSUE | > | DB2 UDB Workgroup Server Unlimited Edition |
| PE | > | DB2 UDB Personal Edition |
| ADMCL | > | DB2 Administration Client |
| ADCL | > | DB2 Application Development Client |
| RTCL | > | DB2 Run-Time Client |
| DLM | > | DB2 Data Links Manager |
| GSE | > | DB2 Spatial Extender |
| WM | > | DB2 Warehouse Manager |
| QP | > | DB2 Query Patroller |
| CUBE | > | DB2 Cube Views |
| CONPE | > | DB2 Connect Personal Edition |
| CONEE | > | DB2 Connect Enterprise Edition, DB2 Connect Unlimited Edition, or DB2 Connect Application Server Edition. |
Use SQL to Get Details of the DB2 UDB Instance Running on Your Server
The final new UDF, called SYSPROC.ENV_GET_INST_INFO, returns information about the instance of the DB2 UDB product installed on your server.
Specifically, the following information is returned by this UDF:
| INST_NAME | The name of the instance |
| IS_INST_PARTITIONABLE | Can you partition the instance? (0=No. 1=Yes.) |
| NUM_DBPARTITIONS | The number of partitions (for a non-DPF instance, this is always 1) |
| INST_PTR_SIZE | The bit level of the instance (32- or 64-bit) |
| RELEASE_NUM | The internal release number of the DB2 UDB software |
| SERVICE_LEVEL | The service level of the DB2 UDB software |
| BLD_LEVEL | The build level of the DB2 UDB software |
| PTF | The PTF number applied to the DB2 UDB software |
An example of using this UDF to retrieve information is shown below:
You can see that a lot of information is returned with this UDF. I will not get into the specifics in this example, but compare the output of the SYSPROC.ENV_GET_INST_INFO UDF with the output of the db2level command that I have included in this figure. This gives you a good indication of the type of information you can get through a simple SQL statement. (Note that DB2 UDB V8.1.9 is the maintenance stream service level for the DB2 UDB V8.2.2 update; in other words, they are the same thing.)
Wrapping it up
So there you have it: some easy ways to retrieve all types of system-related information with the ease of an SQL statement. There are other new UDFs in DB2 UDB V8.2.2 that can return information in the DB2 History file and the DB2 Registry. Moreover, don’t forget all those table functions that IBM has been adding since the generable availability date of DB2 UDB V8.1.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA’s Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.
Trademarks
DB2, DB2 Universal Database, and IBM are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2005. All rights reserved.
Disclaimer
The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.
