DB2/UDB - Admin notes

Tablespaces

For DB2/UDB Version 7 or earlier, information on Tablespace Containers cannot be obtained using SQL. This means that:

when the Properties of a Tablespace is displayed, the Container information is not shown.
DDL for tablespaces cannot be generated

These restrictions do not apply to DB2 v8 and higher, for which container information can be obtained using SQL.

Functions and Procedures

Functions and Procedures are awkward to deal with, as there can be multiple functions/procedures with the same name. What distinguishes these multiple objects is the specific name, which must be unique. AQT uses the specific name when displaying the information on the object.
For this reason, when you create a new function or procedure, AQT will require you to give the specific name for the procedure / function. Without this it is difficult for AQT to manage the object.
Table and row functions have not been implemented.

Function authorities

Authorities cannot be granted or revoked from system functions (SYSFUN and SYSPROC). Consequently, when you using Manage Authorities for a user or group, AQT will exclude the system functions from the list of functions.

Compiling Stored Procedures

For DB2/UDB (prior to version 9), if you had an error trying to create a Stored Procedure you often got a message such as:

SQL7032N SQL Procedure 'xxxx' not created. Diagnostic file is "P1234567.log"

DB2 creates Stored Procedures by building and compiling a C program. This has failed to compile. The P1234567.log file has the compile errors which generally point to the cause of the problem.

The log file is held in a very obscure place - this is:

%SQLLIB%\FUNCTION\ROUTINES\sqlproc\database\username\tmp

where database is your database name, username is your user name and %SQLLIB% is your DB2 install directory (normally C:\Program Files\IBM\SQLLIB\).

Advanced Query Tool

https://www.querytool.com