Database metadata retrieval functions in PHP (ibm_db2)

You can use functions in the ibm_db2 API to retrieve metadata for databases served by Db2®, IBM® Cloudscape, and, through Db2 Connect, Db2 for z/OS® and Db2 for i.

Some classes of applications, such as administration interfaces, must dynamically reflect the structure and SQL objects contained in arbitrary databases. One approach to retrieving metadata about a database is to issue SELECT statements directly against the system catalog tables; however, the schema of the system catalog tables might change between versions of Db2, or the schema of the system catalog tables on Db2 might differ from the schema of the system catalog tables on Db2 for z/OS. Rather than laboriously maintaining these differences in your application code, you can use PHP functions available in the ibm_db2 extension to retrieve database metadata.

Before calling these functions, you must set up the PHP environment and have a connection resource returned by the db2_connect or db2_pconnect function.

Important: Calling metadata functions uses a significant amount of space. If possible, cache the results of your calls for use in subsequent calls.

Table 1. ibm_db2 metadata retrieval functions
Function	Description
db2_client_info	Returns a read-only object with information about the IBM data server client
db2_column_privileges	Returns a result set listing the columns and associated privileges for a table
db2_columns	Returns a result set listing the columns and associated metadata for a table
db2_foreign_keys	Returns a result set listing the foreign keys for a table
db2_primary_keys	Returns a result set listing the primary keys for a table
db2_procedure_columns	Returns a result set listing the parameters for one or more stored procedures
db2_procedures	Returns a result set listing the stored procedures registered in the database
db2_server_info	Returns a read-only object with information about the database management system software and configuration
db2_special_columns	Returns a result set listing the unique row identifiers for a table
db2_statistics	Returns a result set listing the indexes and statistics for a table
db2_table_privileges	Returns a result set listing tables and their associated privileges in the database

Most of the ibm_db2 database metadata retrieval functions return result sets with columns defined for each function. To retrieve rows from the result sets, use the ibm_db2 functions that are available for this purpose.

The db2_client_info and db2_server_info functions directly return a single object with read-only properties. You can use the properties of these objects to create an application that behaves differently depending on the database management system to which it connects. For example, rather than encoding a limit of the lowest common denominator for all possible database management systems, a Web-based database administration application built on the ibm_db2 extension could use the db2_server_info()->MAX_COL_NAME_LEN property to dynamically display text fields for naming columns with maximum lengths that correspond to the maximum length of column names on the database management system to which it is connected.

For more information about the ibm_db2 API, see http://www.php.net/docs.php.

Examples

Example 1: Display a list of columns and associated privileges for a table

<?php
$conn = db2_connect('sample', 'db2inst1', 'ibmdb2');

if ($conn) {
	$stmt = db2_column_privileges($conn, NULL, NULL, 'DEPARTMENT');
	$row = db2_fetch_array($stmt);
	print $row[2] . "\n";
	print $row[3] . "\n";
	print $row[7];
	db2_close($conn);
}
else {
	echo db2_conn_errormsg();
	printf("Connection failed\n\n");
}
?>

Example 2: Display a list of primary keys for a table

<?php
$conn = db2_connect('sample', 'db2inst1', 'ibmdb2');

if ($conn) {
	$stmt = db2_primary_keys($conn, NULL, NULL, 'DEPARTMENT');
	while ($row = db2_fetch_array($stmt)) {
		echo "TABLE_NAME:\t" . $row[2] . "\n";
		echo "COLUMN_NAME:\t" . $row[3] . "\n";
		echo "KEY_SEQ:\t" . $row[4] . "\n";
	}
	
	db2_close($conn);
}
else {
	echo db2_conn_errormsg();
	printf("Connection failed\n\n");
}
?>

Example 3: Display a list of parameters for one or more stored procedures

<?php
$conn = db2_connect('sample', 'db2inst1', 'ibmdb2');

if ($conn) {
	$stmt = db2_procedures($conn, NULL, 'SYS%', '%%');

	$row = db2_fetch_assoc($stmt);
	var_dump($row);
	
	db2_close($conn);
}
else {
	echo "Connection failed.\n";
}
?>

Example 4: Display a list of the indexes and statistics for a table

<?php
$conn = db2_connect('sample', 'db2inst1', 'ibmdb2');

if ($conn) {
	echo "Test DEPARTMENT table:\n";
	$result = db2_statistics($conn, NULL, NULL, "EMPLOYEE", 1);
	while ($row = db2_fetch_assoc($result)) {
		var_dump($row);
	}

	echo "Test non-existent table:\n";
	$result = db2_statistics($conn,NULL,NULL,"NON_EXISTENT_TABLE",1);
	$row = db2_fetch_array($result);
	if ($row) {
		echo "Non-Empty\n";
	} else {
		echo "Empty\n";
	}
	
	db2_close($conn);
} 
else {
	echo 'no connection: ' . db2_conn_errormsg();	
}
?>

Example 5: Display a list of tables and their associated privileges in the database

<?php
$conn = db2_connect('sample', 'db2inst1', 'ibmdb2');

if ($conn) {
	$stmt = db2_table_privileges($conn, NULL, "%%", "DEPARTMENT");
	while ($row = db2_fetch_assoc($stmt)) {
		var_dump($row);
	}
	db2_close($conn);
}
else {
	echo db2_conn_errormsg();
	printf("Connection failed\n\n");
}
?>