DB2 Plugin

Author

Contributors

Introduction

The DB2 plugin adds DB2-specific functionality to the SQuirreL SQL Client for IBM DB2 hosted on LUW (Linux, Unix, Windows) and OS/400. Read access is required to the following system views in order for this additional functionality to work correctly on LUW DB2:

and the following system views on OS/400 DB2:

New Tabs

Stored Procedures, Triggers, Views and User-defined Functions (UDFs) are shown in the object tree and have a "Source" tab which displays the source of the selected object and a "Details" tab which gives DB2-specific information about the object. Tables and Materialized Query Tables are shown in the object tree and have a "Source" tab which displays the source of the selected object. Sequences and Indexes are also shown in the object tree and have a details tab giving DB2-specific information about them.

Index Details Tab

The index details tab can be accessed by navigating the object tree to the INDEX folder beneath any table that has one or multiple indexes. The index(es) will be listed by name under the table folder, and selecting one will place a details tab in the right-hand view of the session window.

The information provided by the details tab for indexes is derived by the following query on LUW DB2: 

	SELECT T1.IID as index_identifier, 
	       T1.DEFINER AS index_owner, 
	       T1.INDNAME AS index_name, 
	       T2.DEFINER AS table_owner, 
	       T2.TABNAME AS table_name, 
	       T3.TBSPACE AS table_space, 
	       case T1.INDEXTYPE 
	         when 'BLOK' then 'Block Index' 
	         when 'CLUS' then 'Clustering Index' 
	         when 'DIM' then 'Dimension Block Index' 
	         when 'REG' then 'Regular Index' 
	         when 'XPTH' then 'XML Path Index' 
	         when 'XRGN' then 'XML Region Index' 
	         when 'XVIL' then 'Index over XML column (Logical)' 
	         when 'XVIP' then 'Index over XML column (Physical)' 
	       end AS index_type, 
	       case T1.UNIQUERULE 
	         when 'U' then 'UNIQUE' 
	         when 'D' then 'NON-UNIQUE' 
	         when 'I' then 'UNIQUE (Implements PK)' 
	       end AS uniqueness, 
	       T1.NLEAF AS number_of_leaf_pages, 
	       T1.NLEVELS AS number_of_levels, 
	       T1.CREATE_TIME, 
	       T1.STATS_TIME AS last_statistics_update, 
	       case T1.REVERSE_SCANS 
	         when 'Y' then 'Supported' 
	         when 'N' then 'Not Supported' 
	       end AS reverse_scans 
	FROM    SYSCAT.INDEXES   AS T1, 
	        SYSCAT.TABLES    AS T2, 
	        SYSCAT.TABLESPACES as T3 
	WHERE  T3.TBSPACEID = T1.TBSPACEID 
	and T2.TABNAME = T1.TABNAME 
	and T2.TABSCHEMA = T1.TABSCHEMA 
	AND     T1.TABSCHEMA = ? 
	AND     T1.INDNAME = ?
and the following query on OS/400 DB2: 

	select index_owner, 
	index_name, 
	index_schema, 
	table_owner, 
	table_name, 
	table_schema, 
	case is_unique 
	    when 'D' then 'No (duplicates are allowed)' 
	    when 'V' then 'Yes (duplicate NULL values are allowed)' 
	    when 'U' then 'Yes' 
	    when 'E' then 'Encoded vector index' 
	end as uniqueness, 
	column_count, 
	system_index_name, 
	system_index_schema, 
	system_table_name, 
	system_table_schema, 
	long_comment, 
	iasp_number, 
	index_text, 
	is_spanning_index 
	from qsys2.sysindexes 
	where table_schema = ? 
	and index_name = ?

Stored Procedure Source Tab

The Stored Procedure Source tab can be accessed by navigating to a catalog, then to a schema and finally to the PROCEDURE folder beneath the schema. In the following picture, the schema is "dbcopy"

The source code for stored procedures is derived by the following query on LUW DB2: 

	select text 
	from SYSCAT.PROCEDURES 
	where PROCSCHEMA = ? 
	and PROCNAME = ?

and the following query on OS/400 DB2: 

	select routine_definition from qsys2.sysroutines 
	where routine_schema=? 
	and routine_name = ?

Sequence Details Tab

Sequences will appear in the object tree under the SCHEMA folder. The details tab for sequences displays information about the selected sequence according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a sequence is derived from the following query on LUW DB2: 

	SELECT  T1.OWNER     AS sequence_owner, 
	        T1.DEFINER   AS sequence_definer, 
	       T1.SEQNAME   AS sequence_name, 
	       T2.TYPENAME AS data_type, 
	       T1.MINVALUE   AS min_value, 
	       T1.MAXVALUE   AS max_value, 
	       T1.INCREMENT   AS increment_by, 
	       case T1.CYCLE 
	         when 'Y' then 'CYCLE' 
	         else 'NOCYCLE' 
	       end AS cycle_flag, 
	       case T1.ORDER 
	         when 'Y' then 'ORDERED' 
	         else 'UNORDERED' 
	        end AS order_flag, 
	       T1.CACHE AS cache_size, 
	       T1.CREATE_TIME AS create_time, 
	       T1.ALTER_TIME AS last_alter_time, 
	       case T1.ORIGIN 
	         when 'U' then 'User' 
	         when 'S' then 'System' 
	       end AS origin, 
	       T1.REMARKS AS comment 
	FROM    SYSCAT.SEQUENCES AS T1, 
	        SYSCAT.DATATYPES AS T2 
	WHERE T1.DATATYPEID = T2.TYPEID 
	and T1.SEQSCHEMA = ? 
	and T1.SEQNAME = ?

and the following query on OS/400 DB2: 

	select sequence_schema, 
	sequence_name, 
	sequence_definer, 
	data_type as type_name, 
	minimum_value as min_value, 
	maximum_value as max_value, 
	increment as increment_by, 
	case cycle_option 
	 when 'YES' then 'CYCLE' 
	 else 'NOCYCLE' 
	end as cycle_flag, 
	case order 
	 when 'YES' then 'ORDERED' 
	 else 'UNORDERED' 
	end as order_flag, 
	cache as cache_size, 
	sequence_created as create_time, 
	last_altered_timestamp as last_alter_time, 
	long_comment as comment 
	from qsys2.syssequences

Trigger Source Tab

The source tab for triggers displays the source for a table trigger and can be accessed by navigating to the "TRIGGER" folder beneath a table object in the object tree. Currently, trigger features of this plugin are only supported on LUW DB2. An example of this is shown in the following picture:

The source code for triggers is derived from the following query: 

	select TEXT from SYSCAT.TRIGGERS 
	where TABSCHEMA = ? 
	and TRIGNAME = ?

Trigger Details Tab

The details tab for triggers displays information about the selected trigger according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a trigger is derived from the following query: 

	SELECT  T1.DEFINER     AS trigger_definer, 
	       T1.trigname  AS trigger_name, 
	       case T1.TRIGTIME 
	         when 'A' then 'AFTER' 
	         when 'B' then 'BEFORE' 
	         when 'I' then 'INSTEAD OF' 
	       end AS trigger_time, 
	       case T1.TRIGEVENT 
	         when 'I' then 'INSERT' 
	         when 'U' then 'UPDATE' 
	         when 'D' then 'DELETE' 
	         when 'S' then 'SELECT' 
	         else T1.TRIGEVENT 
	       end AS triggering_event, 
	       T2.DEFINER     AS table_definer, 
	       T2.TABNAME   AS table_name, 
	       case T2.TYPE 
	         when 'T' then 'TABLE' 
	         when 'V' then 'VIEW' 
	         else T2.TYPE 
	       end AS table_type, 
	       case T1.GRANULARITY 
	         when 'R' then 'ROW' 
	         when 'S' then 'STATEMENT' 
	       else T1.GRANULARITY 
	       end AS granularity, 
	       case T1.VALID 
	         when 'Y' THEN 'VALID' 
	         when 'N' THEN 'INVALID' 
	         when 'X' THEN 'INOPERATIVE' 
	       end AS validity, 
	       T1.REMARKS comment 
	FROM    SYSCAT.TRIGGERS  AS T1, 
	       SYSCAT.TABLES    AS T2 
	WHERE   T2.TABNAME = T1.TABNAME 
	and T2.TABSCHEMA = T1.TABSCHEMA 
	and T1.TRIGSCHEMA = ? 
	and T1.trigname = ?

View Source Tab

The source tab for views and MQTs (Materialized Query Tables) displays the source for a view/MQT and can be accessed by navigating to the "VIEW"/"MQT" folder beneath a schema object in the object tree. An example of view source is shown in the following picture:

An example of MQT source is shown in the following picture:

The source code for views/MQTs is derived from the following query on LUW DB2: 

	SELECT TEXT 
	FROM SYSCAT.VIEWS 
	WHERE VIEWSCHEMA = ? 
	AND VIEWNAME = ?
and the following query on OS/400 DB2: 

	select view_definition 
	from qsys2.sysviews 
	where table_schema = ? 
	and table_name = ?

UDF Source Tab

The source tab for UDFs displays the source for a User-defined function and can be accessed by navigating to the "UDF" folder beneath a schema object in the object tree. An example of this is shown in the following picture:

The source code for a UDF is derived from the following query on LUW DB2: 

	SELECT 
	case 
	    when body is null then 'No source available' 
	    else body 
	end 
	FROM SYSIBM.SYSFUNCTIONS 
	WHERE schema = ? 
	AND name = ? 
	AND implementation is null
and the following query on OS/400 DB2: 

	select 
	case 
	    when body = 'SQL' and routine_definition is not null then routine_definition 
	    when body = 'SQL' and routine_definition is null then 'no source available' 
	    when body = 'EXTERNAL' and external_name is not null then external_name 
	    when body = 'EXTERNAL' and external_name is null then 'system-generated function' 
	end as definition 
	from QSYS2.SYSFUNCS 
	where routine_schema = ? 
	and routine_name = ?

UDF Details Tab

The details tab for UDFs displays information about the selected UDF according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a UDF is derived from the following query on LUW DB2: 

	select 
	name, 
	schema, 
	definer, 
	function_id, 
	parm_count, 
	side_effects, 
	fenced, 
	language, 
	contains_sql, 
	result_cols, 
	class, 
	jar_id 
	from sysibm.SYSFUNCTIONS 
	where schema = ? 
	and name = ?

and the following query on OS/400 DB2: 

	select 
	routine_name as name, 
	routine_schema as schema, 
	routine_definer as definer, 
	in_parms as parm_count, 
	case external_action 
	    when 'E' then 'has external side effects' 
	    when 'N' then 'has no external side effects' 
	end as side_effects, 
	fenced, 
	external_language as language, 
	sql_data_access as contains_sql, 
	number_of_results as result_cols, 
	external_name 
	from qsys2.SYSFUNCS 
	where routine_schema = ? 
	and routine_name = ?