# DB2 Plugin

## Author

- Rob Manning

## Contributors

- Christoph Schmitz - custom exception formatting using DB2Diagnosable API
- Tilmann Brenk - OS/400 DB2 version of all SQL queries used by the plugin

## 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:

- SYSCAT.DATATYPES
- SYSCAT.INDEXES
- SYSCAT.PROCEDURES
- SYSCAT.SEQUENCES
- SYSCAT.TABLES
- SYSCAT.TABLESPACES
- SYSCAT.TRIGGERS
- SYSCAT.VIEWS

and the following system views on OS/400 DB2:

- QSYS2.SYSFUNCS
- QSYS2.SYSINDEXES
- QSYS2.SYSROUTINES
- QSYS2.SYSSEQUENCES
- QSYS2.SYSVIEWS

## 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 = ?