|
From: <man...@us...> - 2012-09-29 19:41:47
|
Revision: 6665
http://squirrel-sql.svn.sourceforge.net/squirrel-sql/?rev=6665&view=rev
Author: manningr
Date: 2012-09-29 19:41:37 +0000 (Sat, 29 Sep 2012)
Log Message:
-----------
Feature #3134781: (z/OS DB2 catalog support) Incorporated SQL contributed by Cristiano Guadagnino to support DB2 on the z/OS platform.
Modified Paths:
--------------
trunk/sql12/doc/src/main/resources/changes.txt
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/DB2Plugin.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableIndexExtractorImpl.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableTriggerExtractorImpl.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SchemaExpander.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SequenceParentExpander.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/UDFParentExpander.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/IndexDetailsTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/ProcedureSourceTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/SequenceDetailsTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/TableSourceTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/TriggerDetailsTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/TriggerSourceTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/UDFDetailsTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/UDFSourceTab.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/ViewSourceTab.java
trunk/sql12/plugins/db2/src/main/resources/doc/changes.txt
trunk/sql12/plugins/db2/src/main/resources/net/sourceforge/squirrel_sql/plugins/db2/tab/I18NStrings.properties
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableIndexExtractorImplTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableTriggerExtractorImplTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SchemaExpanderTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SequenceParentExpanderTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/exp/UDFParentExpanderTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/IndexDetailsTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/ProcedureSourceTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/SequenceDetailsTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/TableSourceTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/TriggerDetailsTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/TriggerSourceTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/UDFDetailsTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/UDFSourceTabTest.java
trunk/sql12/plugins/db2/src/test/java/net/sourceforge/squirrel_sql/plugins/db2/tab/ViewSourceTabTest.java
Added Paths:
-----------
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2PlatformType.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2Sql.java
trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2SqlImpl.java
trunk/sql12/plugins/db2/src/main/resources/net/sourceforge/squirrel_sql/plugins/db2/sql/
trunk/sql12/plugins/db2/src/main/resources/net/sourceforge/squirrel_sql/plugins/db2/sql/I18NStrings.properties
Modified: trunk/sql12/doc/src/main/resources/changes.txt
===================================================================
--- trunk/sql12/doc/src/main/resources/changes.txt 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/doc/src/main/resources/changes.txt 2012-09-29 19:41:37 UTC (rev 6665)
@@ -8,6 +8,9 @@
Enhancements:
+Cristiano Guadagnino contributed all of the SQL required for the DB2Plugin to properly support the DB2 z/OS
+platform. Many thanks to Cristiano Guadagnino.
+
Overview tab: Now offers functionality to view query result data in bar charts. (Uses JFreeChart: http://www.jfree.org/jfreechart/)
Overview tab: Now has a button to copy a HTML-report of an overview to clipboard
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/DB2Plugin.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/DB2Plugin.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/DB2Plugin.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -44,6 +44,8 @@
import net.sourceforge.squirrel_sql.plugins.db2.exp.DB2TableIndexExtractorImpl;
import net.sourceforge.squirrel_sql.plugins.db2.exp.DB2TableTriggerExtractorImpl;
import net.sourceforge.squirrel_sql.plugins.db2.exp.SchemaExpander;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2SqlImpl;
import net.sourceforge.squirrel_sql.plugins.db2.tab.IndexDetailsTab;
import net.sourceforge.squirrel_sql.plugins.db2.tab.ProcedureSourceTab;
import net.sourceforge.squirrel_sql.plugins.db2.tab.SequenceDetailsTab;
@@ -65,9 +67,6 @@
public static final String JCC_DRIVER_NAME = "IBM DB2 JDBC Universal Driver Architecture";
- /** The product name that indicates we need to use os/400 queries */
- private static final String OS_400_PRODUCT_NAME = "DB2 UDB for AS/400";
-
private static final StringManager s_stringMgr = StringManagerFactory.getStringManager(DB2Plugin.class);
/** Logger for this class. */
@@ -119,7 +118,7 @@
*/
public String getVersion()
{
- return "0.04";
+ return "0.05";
}
/**
@@ -185,8 +184,7 @@
super.initialize();
// register custom DataTypeComponent factory for DB2 XML
- CellComponentFactory.registerDataTypeFactory(new DB2XmlTypeDataTypeComponentFactory(),
- Types.OTHER,
+ CellComponentFactory.registerDataTypeFactory(new DB2XmlTypeDataTypeComponentFactory(), Types.OTHER,
"XML");
}
@@ -213,10 +211,7 @@
public PluginSessionCallback sessionStarted(final ISession session)
{
- if (!isPluginSession(session))
- {
- return null;
- }
+ if (!isPluginSession(session)) { return null; }
GUIUtils.processOnSwingEventThread(new Runnable()
{
public void run()
@@ -232,7 +227,8 @@
{
session.setExceptionFormatter(new DB2JCCExceptionFormatter());
}
- } catch (SQLException e)
+ }
+ catch (SQLException e)
{
s_log.error("Problem installing exception formatter: " + e.getMessage());
}
@@ -248,82 +244,73 @@
private void updateTreeApi(ISession session)
{
+ String databaseProductName = getDatabaseProductName(session);
+ final DB2Sql db2Sql = new DB2SqlImpl(databaseProductName);
String stmtSep = session.getQueryTokenizer().getSQLStatementSeparator();
- boolean isOS400 = isOS400(session);
+
_treeAPI = session.getSessionInternalFrame().getObjectTreeAPI();
_treeAPI.addDetailTab(DatabaseObjectType.PROCEDURE, new ProcedureSourceTab(i18n.SHOW_PROCEDURE_SOURCE,
- isOS400,
- stmtSep));
- _treeAPI.addDetailTab(DatabaseObjectType.VIEW, new ViewSourceTab( i18n.SHOW_VIEW_SOURCE,
- stmtSep,
- isOS400));
+ stmtSep, db2Sql));
+ _treeAPI.addDetailTab(DatabaseObjectType.VIEW, new ViewSourceTab(i18n.SHOW_VIEW_SOURCE, stmtSep,
+ db2Sql));
_treeAPI.addDetailTab(DatabaseObjectType.INDEX, new DatabaseObjectInfoTab());
- _treeAPI.addDetailTab(DatabaseObjectType.INDEX, new IndexDetailsTab(isOS400));
+ _treeAPI.addDetailTab(DatabaseObjectType.INDEX, new IndexDetailsTab(db2Sql));
_treeAPI.addDetailTab(DatabaseObjectType.TRIGGER, new DatabaseObjectInfoTab());
_treeAPI.addDetailTab(DatabaseObjectType.TRIGGER_TYPE_DBO, new DatabaseObjectInfoTab());
_treeAPI.addDetailTab(DatabaseObjectType.SEQUENCE, new DatabaseObjectInfoTab());
- _treeAPI.addDetailTab(DatabaseObjectType.SEQUENCE, new SequenceDetailsTab(isOS400));
+ _treeAPI.addDetailTab(DatabaseObjectType.SEQUENCE, new SequenceDetailsTab(db2Sql));
_treeAPI.addDetailTab(DatabaseObjectType.UDF, new DatabaseObjectInfoTab());
- _treeAPI.addDetailTab(DatabaseObjectType.UDF, new UDFSourceTab(i18n.SHOW_UDF_SOURCE, stmtSep, isOS400));
- _treeAPI.addDetailTab(DatabaseObjectType.UDF, new UDFDetailsTab(isOS400));
+ _treeAPI.addDetailTab(DatabaseObjectType.UDF, new UDFSourceTab(i18n.SHOW_UDF_SOURCE, stmtSep, db2Sql));
+ _treeAPI.addDetailTab(DatabaseObjectType.UDF, new UDFDetailsTab(db2Sql));
- _treeAPI.addDetailTab(DatabaseObjectType.TABLE, new TableSourceTab("Show MQT Source", stmtSep, isOS400));
+ _treeAPI.addDetailTab(DatabaseObjectType.TABLE, new TableSourceTab("Show MQT Source", stmtSep, db2Sql));
// Expanders - trigger and index expanders are added inside the table
// expander
- _treeAPI.addExpander(DatabaseObjectType.SCHEMA, new SchemaExpander(isOS400));
+ _treeAPI.addExpander(DatabaseObjectType.SCHEMA, new SchemaExpander(db2Sql));
// Expanders - trigger and index expanders are added inside the table
// expander
TableWithChildNodesExpander tableExpander = new TableWithChildNodesExpander();
// tableExpander.setTableIndexExtractor(extractor);
- ITableIndexExtractor indexExtractor = new DB2TableIndexExtractorImpl(isOS400);
+ ITableIndexExtractor indexExtractor = new DB2TableIndexExtractorImpl(db2Sql);
tableExpander.setTableIndexExtractor(indexExtractor);
- ITableTriggerExtractor triggerExtractor = new DB2TableTriggerExtractorImpl(isOS400);
+ ITableTriggerExtractor triggerExtractor = new DB2TableTriggerExtractorImpl(db2Sql);
tableExpander.setTableTriggerExtractor(triggerExtractor);
_treeAPI.addExpander(DatabaseObjectType.TABLE, tableExpander);
- _treeAPI.addDetailTab(DatabaseObjectType.TRIGGER, new TriggerDetailsTab());
- _treeAPI.addDetailTab(DatabaseObjectType.TRIGGER, new TriggerSourceTab( i18n.SHOW_TRIGGER_SOURCE,
- isOS400,
- stmtSep));
+ _treeAPI.addDetailTab(DatabaseObjectType.TRIGGER, new TriggerDetailsTab(db2Sql));
+ _treeAPI.addDetailTab(DatabaseObjectType.TRIGGER, new TriggerSourceTab(i18n.SHOW_TRIGGER_SOURCE,
+ stmtSep, db2Sql));
}
/**
- * Determines whether or not we've connected to DB2 on OS/400.
+ * Gets the database product name.
*
* @param session
* @return
*/
- private boolean isOS400(ISession session)
+ private String getDatabaseProductName(ISession session)
{
- boolean result = false;
+ String result = null;
try
{
- String prodName = session.getMetaData().getDatabaseProductName();
- if (prodName == null || prodName.equals(""))
- {
- s_log.info("isOS400: product name is null or empty. " + "Assuming not an OS/400 DB2 session.");
- } else if (prodName.equals(OS_400_PRODUCT_NAME))
- {
- s_log.info("isOS400: session appears to be an OS/400 DB2");
- result = true;
- } else
- {
- s_log.info("isOS400: session doesn't appear to be an OS/400 DB2");
- }
- } catch (SQLException e)
+ result = session.getMetaData().getDatabaseProductName();
+ }
+ catch (SQLException e)
{
- s_log.error("isOS400: unable to determine the product name: " + e.getMessage(), e);
+ s_log.error(
+ "getDatabaseProductName: unable to determine the product name (assuming LUW): " + e.getMessage(),
+ e);
}
return result;
}
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableIndexExtractorImpl.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableIndexExtractorImpl.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableIndexExtractorImpl.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -25,68 +25,54 @@
import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
/**
* Provides the query and parameter binding behavior for DB2's index catalog.
- *
+ *
* @author manningr
*/
-public class DB2TableIndexExtractorImpl implements ITableIndexExtractor {
+public class DB2TableIndexExtractorImpl implements ITableIndexExtractor
+{
- /** Logger for this class */
- private final static ILogger s_log =
- LoggerController.createLogger(DB2TableIndexExtractorImpl.class);
-
- /** The query that finds the indexes for a given table */
- private static final String query =
- "select INDNAME from SYSCAT.INDEXES " +
- "where TABSCHEMA = ? " +
- "and TABNAME = ? ";
-
- /** The query that finds the indexes for a given table on OS/400 */
- private static final String OS_400_SQL =
- "select " +
- "index_name " +
- "from qsys2.sysindexes " +
- "where table_schema = ? " +
- "and table_name = ? ";
-
- /** boolean to indicate whether or not this session is OS/400 */
- private boolean isOS400 = false;
-
- /**
- * Ctor.
- *
- * @param isOS400 whether or not the session is OS/400
- */
- public DB2TableIndexExtractorImpl(boolean isOS400) {
- this.isOS400 = isOS400;
- }
-
- /**
- * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableIndexExtractor#bindParamters(java.sql.PreparedStatement, net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo)
- */
- public void bindParamters(PreparedStatement pstmt, IDatabaseObjectInfo dbo)
- throws SQLException
- {
- if (s_log.isDebugEnabled()) {
- s_log.debug("Binding schema name "+dbo.getSchemaName()+
- " as first bind value");
- s_log.debug("Binding table name "+dbo.getSimpleName()+
- " as second bind value");
- }
- pstmt.setString(1, dbo.getSchemaName());
- pstmt.setString(2, dbo.getSimpleName());
- }
+ /** Logger for this class */
+ private final static ILogger s_log = LoggerController.createLogger(DB2TableIndexExtractorImpl.class);
- /**
- * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableIndexExtractor#getTableIndexQuery()
- */
- public String getTableIndexQuery() {
- if (isOS400) {
- return OS_400_SQL;
- }
- return query;
- }
+ /** Object that contains methods for retrieving SQL that works for each DB2 platform */
+ private final DB2Sql db2Sql;
+ /**
+ * Ctor.
+ *
+ * @param db2Sql
+ * Object that contains methods for retrieving SQL that works for each DB2 platform
+ */
+ public DB2TableIndexExtractorImpl(DB2Sql db2Sql)
+ {
+ this.db2Sql = db2Sql;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableIndexExtractor#bindParamters(java.sql.PreparedStatement,
+ * net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo)
+ */
+ public void bindParamters(PreparedStatement pstmt, IDatabaseObjectInfo dbo) throws SQLException
+ {
+ if (s_log.isDebugEnabled())
+ {
+ s_log.debug("Binding schema name " + dbo.getSchemaName() + " as first bind value");
+ s_log.debug("Binding table name " + dbo.getSimpleName() + " as second bind value");
+ }
+ pstmt.setString(1, dbo.getSchemaName());
+ pstmt.setString(2, dbo.getSimpleName());
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableIndexExtractor#getTableIndexQuery()
+ */
+ public String getTableIndexQuery()
+ {
+ return db2Sql.getTableIndexListSql();
+ }
+
}
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableTriggerExtractorImpl.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableTriggerExtractorImpl.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/DB2TableTriggerExtractorImpl.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -25,69 +25,55 @@
import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
/**
* Provides the query and parameter binding behavior for DB2's trigger catalog.
- *
+ *
* @author manningr
*/
-public class DB2TableTriggerExtractorImpl implements ITableTriggerExtractor {
+public class DB2TableTriggerExtractorImpl implements ITableTriggerExtractor
+{
- /** Logger for this class */
- private final static ILogger s_log =
- LoggerController.createLogger(DB2TableTriggerExtractorImpl.class);
-
- /** The query that finds the triggers for a given table */
- private final static String SQL =
- "select TRIGNAME from SYSCAT.TRIGGERS " +
- "where TABSCHEMA = ? " +
- "and TABNAME = ? ";
-
- /** The query that finds the triggers for a given table in DB2 on OS/400 */
- private final static String OS400_SQL =
- "select trigger_name " +
- "from qsys2.systriggers " +
- "where trigger_schema = ? " +
- "and event_object_table = ? ";
-
- /** boolean to indicate whether or not this session is OS/400 */
- private boolean isOS400 = false;
-
- /**
- * Ctor.
- *
- * @param isOS400 whether or not the session is OS/400
- */
- public DB2TableTriggerExtractorImpl(boolean isOS400) {
- this.isOS400 = isOS400;
- }
-
- /**
- * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableTriggerExtractor#bindParamters(java.sql.PreparedStatement, net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo)
- */
- public void bindParamters(PreparedStatement pstmt, IDatabaseObjectInfo dbo)
- throws SQLException
- {
- if (s_log.isDebugEnabled()) {
- s_log.debug("Binding schema name "+dbo.getSchemaName()+
- " as first bind value");
- s_log.debug("Binding table name "+dbo.getSimpleName()+
- " as second bind value");
- }
- pstmt.setString(1, dbo.getSchemaName());
- pstmt.setString(2, dbo.getSimpleName());
-
- }
+ /** Logger for this class */
+ private final static ILogger s_log = LoggerController.createLogger(DB2TableTriggerExtractorImpl.class);
- /**
- * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableTriggerExtractor#getTableTriggerQuery()
- */
- public String getTableTriggerQuery() {
- String result = SQL;
- if (isOS400) {
- result = OS400_SQL;
- }
- return result;
- }
+ /** Object that contains methods for retrieving SQL that works for each DB2 platform */
+ private final DB2Sql db2Sql;
+ /**
+ * Ctor.
+ *
+ * @param db2Sql
+ * Object that contains methods for retrieving SQL that works for each DB2 platform
+ */
+ public DB2TableTriggerExtractorImpl(DB2Sql db2Sql)
+ {
+ this.db2Sql = db2Sql;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableTriggerExtractor#bindParamters(java.sql.PreparedStatement,
+ * net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo)
+ */
+ public void bindParamters(PreparedStatement pstmt, IDatabaseObjectInfo dbo) throws SQLException
+ {
+ if (s_log.isDebugEnabled())
+ {
+ s_log.debug("Binding schema name " + dbo.getSchemaName() + " as first bind value");
+ s_log.debug("Binding table name " + dbo.getSimpleName() + " as second bind value");
+ }
+ pstmt.setString(1, dbo.getSchemaName());
+ pstmt.setString(2, dbo.getSimpleName());
+
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.ITableTriggerExtractor#getTableTriggerQuery()
+ */
+ public String getTableTriggerQuery()
+ {
+ return db2Sql.getTableTriggerListSql();
+ }
+
}
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SchemaExpander.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SchemaExpander.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SchemaExpander.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -27,6 +27,7 @@
import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
/**
* This class is an expander for the schema nodes. It will add Sequence and UDF Object
@@ -37,16 +38,19 @@
public class SchemaExpander implements INodeExpander
{
- /** whether or not we are connected to OS/400 */
- private boolean isOS400 = false;
+ /** Object that contains methods for retrieving SQL that works for each DB2 platform */
+ private final DB2Sql db2Sql;
/**
* Ctor.
+ *
+ * @param db2Sql
+ * Object that contains methods for retrieving SQL that works for each DB2 platform
*/
- public SchemaExpander(boolean isOS400)
+ public SchemaExpander(DB2Sql db2Sql)
{
super();
- this.isOS400 = isOS400;
+ this.db2Sql = db2Sql;
}
/**
@@ -75,7 +79,7 @@
DatabaseObjectType.SEQUENCE_TYPE_DBO,
md);
ObjectTreeNode node = new ObjectTreeNode(session, seqInfo);
- node.addExpander(new SequenceParentExpander(isOS400));
+ node.addExpander(new SequenceParentExpander(db2Sql));
childNodes.add(node);
IDatabaseObjectInfo udfInfo =
@@ -85,7 +89,7 @@
DatabaseObjectType.UDF_TYPE_DBO,
md);
ObjectTreeNode udfnode = new ObjectTreeNode(session, udfInfo);
- udfnode.addExpander(new UDFParentExpander(isOS400));
+ udfnode.addExpander(new UDFParentExpander(db2Sql));
childNodes.add(udfnode);
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SequenceParentExpander.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SequenceParentExpander.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/SequenceParentExpander.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -1,4 +1,5 @@
package net.sourceforge.squirrel_sql.plugins.db2.exp;
+
/*
* Copyright (C) 2007 Rob Manning
* man...@us...
@@ -33,50 +34,38 @@
import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
+
/**
- * This class handles the expanding of the "Sequence Group"
- * node. It will give a list of all the Sequences available in the schema.
- *
+ * This class handles the expanding of the "Sequence Group" node. It will give a list of all the Sequences
+ * available in the schema.
+ *
* @author manningr
*/
public class SequenceParentExpander implements INodeExpander
{
- /** SQL used to load sequence names */
- private static final String SQL =
- "select SEQNAME " +
- "from SYSCAT.SEQUENCES " +
- "WHERE SEQSCHEMA = ? " +
- "AND SEQNAME like ? ";
-
- /** SQL used to load sequence names on OS/400 */
- private static final String OS_400_SQL =
- "select sequence_name " +
- "from qsys2.syssequences " +
- "where sequence_schema = ? " +
- "and sequence_name like ? ";
-
- /** whether or not we are connected to OS/400 */
- private boolean isOS400 = false;
-
+
+ /** Object that contains methods for retrieving SQL that works for each DB2 platform */
+ private final DB2Sql db2Sql;
+
/**
* Default ctor.
*/
- public SequenceParentExpander(boolean isOS400)
+ public SequenceParentExpander(DB2Sql db2Sql)
{
super();
- this.isOS400 = isOS400;
+ this.db2Sql = db2Sql;
}
/**
- * Create the child nodes for the passed parent node and return them. Note
- * that this method should <B>not</B> actually add the child nodes to the
- * parent node as this is taken care of in the caller.
- *
- * @param session Current session.
- * @param node Node to be expanded.
- *
- * @return A list of <TT>ObjectTreeNode</TT> objects representing the child
- * nodes for the passed node.
+ * Create the child nodes for the passed parent node and return them. Note that this method should
+ * <B>not</B> actually add the child nodes to the parent node as this is taken care of in the caller.
+ *
+ * @param session
+ * Current session.
+ * @param node
+ * Node to be expanded.
+ * @return A list of <TT>ObjectTreeNode</TT> objects representing the child nodes for the passed node.
*/
public List<ObjectTreeNode> createChildren(ISession session, ObjectTreeNode parentNode)
throws SQLException
@@ -87,36 +76,33 @@
final SQLDatabaseMetaData md = session.getSQLConnection().getSQLMetaData();
final String catalogName = parentDbinfo.getCatalogName();
final String schemaName = parentDbinfo.getSchemaName();
- final ObjFilterMatcher filterMatcher = new ObjFilterMatcher(session.getProperties());
+ final ObjFilterMatcher filterMatcher = new ObjFilterMatcher(session.getProperties());
- String sql = SQL;
- if (isOS400) {
- sql = OS_400_SQL;
- }
+ String sql = db2Sql.getSequenceListSql();
final PreparedStatement pstmt = conn.prepareStatement(sql);
- ResultSet rs = null;
+ ResultSet rs = null;
try
{
pstmt.setString(1, schemaName);
pstmt.setString(2, filterMatcher.getSqlLikeMatchString());
rs = pstmt.executeQuery();
- while (rs.next())
- {
- IDatabaseObjectInfo si = new DatabaseObjectInfo(catalogName,
- schemaName, rs.getString(1),
- DatabaseObjectType.SEQUENCE, md);
+ while (rs.next())
+ {
+ IDatabaseObjectInfo si =
+ new DatabaseObjectInfo(catalogName, schemaName, rs.getString(1), DatabaseObjectType.SEQUENCE,
+ md);
- if(filterMatcher.matches(si.getSimpleName()))
- {
- childNodes.add(new ObjectTreeNode(session, si));
- }
- }
+ if (filterMatcher.matches(si.getSimpleName()))
+ {
+ childNodes.add(new ObjectTreeNode(session, si));
+ }
+ }
}
finally
{
- SQLUtilities.closeResultSet(rs);
- SQLUtilities.closeStatement(pstmt);
+ SQLUtilities.closeResultSet(rs);
+ SQLUtilities.closeStatement(pstmt);
}
return childNodes;
}
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/UDFParentExpander.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/UDFParentExpander.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/exp/UDFParentExpander.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -1,4 +1,5 @@
package net.sourceforge.squirrel_sql.plugins.db2.exp;
+
/*
* Copyright (C) 2007 Rob Manning
* man...@us...
@@ -33,52 +34,38 @@
import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
+
/**
- * This class handles the expanding of the "UDF"
- * node. It will give a list of all the User-Defined Functions in the schema that match the
- * object tree filter criteria.
- *
+ * This class handles the expanding of the "UDF" node. It will give a list of all the User-Defined Functions
+ * in the schema that match the object tree filter criteria.
+ *
* @author manningr
*/
public class UDFParentExpander implements INodeExpander
{
- /** SQL used to load UDF names */
- private static final String SQL =
- "SELECT name " +
- "FROM SYSIBM.SYSFUNCTIONS " +
- "WHERE schema = ? " +
- "AND name like ? " +
- "AND implementation is null";
-
- /** SQL used to load UDF names on OS/400 systems */
- private static final String OS_400_SQL =
- "select routine_name " +
- "from QSYS2.SYSFUNCS " +
- "where routine_schema = ? " +
- "and routine_name like ? ";
-
- /** whether or not we are connected to OS/400 */
- private boolean isOS400 = false;
-
+
+ /** Object that contains methods for retrieving SQL that works for each DB2 platform */
+ private final DB2Sql db2Sql;
+
/**
* Default ctor.
*/
- public UDFParentExpander(boolean isOS400)
+ public UDFParentExpander(DB2Sql db2Sql)
{
super();
- this.isOS400 = isOS400;
+ this.db2Sql = db2Sql;
}
/**
- * Create the child nodes for the passed parent node and return them. Note
- * that this method should <B>not</B> actually add the child nodes to the
- * parent node as this is taken care of in the caller.
- *
- * @param session Current session.
- * @param node Node to be expanded.
- *
- * @return A list of <TT>ObjectTreeNode</TT> objects representing the child
- * nodes for the passed node.
+ * Create the child nodes for the passed parent node and return them. Note that this method should
+ * <B>not</B> actually add the child nodes to the parent node as this is taken care of in the caller.
+ *
+ * @param session
+ * Current session.
+ * @param node
+ * Node to be expanded.
+ * @return A list of <TT>ObjectTreeNode</TT> objects representing the child nodes for the passed node.
*/
public List<ObjectTreeNode> createChildren(ISession session, ObjectTreeNode parentNode)
throws SQLException
@@ -89,35 +76,31 @@
final SQLDatabaseMetaData md = session.getSQLConnection().getSQLMetaData();
final String catalogName = parentDbinfo.getCatalogName();
final String schemaName = parentDbinfo.getSchemaName();
- final ObjFilterMatcher filterMatcher = new ObjFilterMatcher(session.getProperties());
+ final ObjFilterMatcher filterMatcher = new ObjFilterMatcher(session.getProperties());
+ String sql = db2Sql.getUserDefinedFunctionListSql();
- String sql = SQL;
- if (isOS400) {
- sql = OS_400_SQL;
- }
final PreparedStatement pstmt = conn.prepareStatement(sql);
- ResultSet rs = null;
+ ResultSet rs = null;
try
{
pstmt.setString(1, schemaName);
pstmt.setString(2, filterMatcher.getSqlLikeMatchString());
rs = pstmt.executeQuery();
- while (rs.next())
+ while (rs.next())
+ {
+ IDatabaseObjectInfo si =
+ new DatabaseObjectInfo(catalogName, schemaName, rs.getString(1), DatabaseObjectType.UDF, md);
+ if (filterMatcher.matches(si.getSimpleName()))
{
- IDatabaseObjectInfo si = new DatabaseObjectInfo(catalogName,
- schemaName, rs.getString(1),
- DatabaseObjectType.UDF, md);
- if(filterMatcher.matches(si.getSimpleName()))
- {
- childNodes.add(new ObjectTreeNode(session, si));
- }
- }
+ childNodes.add(new ObjectTreeNode(session, si));
+ }
+ }
}
finally
{
- SQLUtilities.closeResultSet(rs);
- SQLUtilities.closeStatement(pstmt);
+ SQLUtilities.closeResultSet(rs);
+ SQLUtilities.closeStatement(pstmt);
}
return childNodes;
}
Added: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2PlatformType.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2PlatformType.java (rev 0)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2PlatformType.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -0,0 +1,40 @@
+package net.sourceforge.squirrel_sql.plugins.db2.sql;
+
+/**
+ * An ENUM type to represent the various platforms that DB2 can be served from. The String returns from
+ * DatabaseMetaData.getDatabaseProductName() can have various values. This ENUM encapsulates this fact, and
+ * has a static method (getDB2PlatformTypeByName) that understands how to convert the String into a platform
+ * ENUM value.
+ */
+public enum DB2PlatformType
+{
+ LUW, OS400, ZOS;
+
+ /**
+ * Returns an ENUM value that corresponds with the specified databaseProductName.
+ *
+ * @param databaseProductName
+ * the value returned from DatabaseMetaData.getDatabaseProductName();
+ * @return an ENUM value representing the specific DB2 platform that corresponds to the specified
+ * databaseProductName.
+ */
+ public static DB2PlatformType getDB2PlatformTypeByName(String databaseProductName)
+ {
+ // See: http://tinyurl.com/95poonn for z/OS
+ if (databaseProductName.equals("DB2")) { return ZOS; }
+
+ if (databaseProductName.equals("DB2 UDB for AS/400")) { return OS400; }
+
+ // I decided against this implementation since I may not have all of the platforms listed here.
+ // Since my former logic determined that everything but AS/400 is LUW, and I am pretty sure that
+ // "DB2" means z/OS, it seems safe to conclude that if its not one of those, then it must be
+ // LUW.
+ // if (databaseProductName.startsWith("DB2/LINUX")
+ // || databaseProductName.equals("DB2/NT")
+ // || databaseProductName.equals("DB2/AIX64")) {
+ // return LUW;
+ // }
+ return LUW;
+ }
+
+}
Added: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2Sql.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2Sql.java (rev 0)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2Sql.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -0,0 +1,30 @@
+package net.sourceforge.squirrel_sql.plugins.db2.sql;
+
+public interface DB2Sql
+{
+
+ String getUserDefinedFunctionSourceSql();
+
+ String getUserDefinedFunctionDetailsSql();
+
+ String getTriggerDetailsSql();
+
+ public abstract String getViewSourceSql();
+
+ public abstract String getSequenceDetailsSql();
+
+ public abstract String getProcedureSourceSql();
+
+ public abstract String getUserDefinedFunctionListSql();
+
+ public abstract String getSequenceListSql();
+
+ public abstract String getTableIndexListSql();
+
+ public abstract String getTableTriggerListSql();
+
+ public abstract String getIndexDetailsSql();
+
+ public abstract String getTriggerSourceSql();
+
+}
\ No newline at end of file
Added: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2SqlImpl.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2SqlImpl.java (rev 0)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/sql/DB2SqlImpl.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -0,0 +1,736 @@
+package net.sourceforge.squirrel_sql.plugins.db2.sql;
+
+import net.sourceforge.squirrel_sql.fw.util.StringManager;
+import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
+
+
+/**
+ * There are at least three different DB2 platforms (LUW, OS/400, z/OS) that require different SQL for
+ * accessing the data dictionary. This class provides a place to locate all of this DB2-specific SQL.
+ *
+ */
+public class DB2SqlImpl implements DB2Sql
+{
+
+ private static interface i18n
+ {
+ StringManager s_stringMgr = StringManagerFactory.getStringManager(DB2SqlImpl.class);
+
+ // i18n[ProcedureSourceTab.cLanguageProcMsg=This is a C-language routine. The
+ // source code is unavailable.]
+ String C_LANGUAGE_PROC_MSG = s_stringMgr.getString("ProcedureSourceTab.cLanguageProcMsg");
+ }
+
+ private final DB2PlatformType db2Type;
+
+ public DB2SqlImpl(String databaseProductName) {
+ db2Type = DB2PlatformType.getDB2PlatformTypeByName(databaseProductName);
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getUserDefinedFunctionSourceSql()
+ */
+ @Override
+ public String getUserDefinedFunctionSourceSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "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 = ? ";
+ break;
+ case LUW:
+ result =
+ "SELECT " +
+ "case " +
+ " when body is null then 'No source available' " +
+ " else body " +
+ "end " +
+ "FROM SYSIBM.SYSFUNCTIONS " +
+ "WHERE schema = ? " +
+ "AND name = ? " +
+ "AND implementation is null ";
+ case ZOS:
+ result =
+ "select " +
+ "case " +
+ "when origin = 'E' and external_name is not null then external_name " +
+ "when origin = 'S' and external_name is null then 'system-generated " +
+ "function' " +
+ "else 'no source available' " +
+ "end as definition " +
+ "from SYSIBM.SYSROUTINES " +
+ "where routine_schema = ? " +
+ "and routine_name = ? " +
+ "and ROUTINETYPE = 'F' ";
+ }
+
+ return result;
+
+ }
+
+
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getUserDefinedFunctionDetailsSql()
+ */
+ @Override
+ public String getUserDefinedFunctionDetailsSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "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 = ? ";
+ break;
+ case LUW:
+ result =
+ "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 = ? ";
+ break;
+ case ZOS:
+ result =
+ "select " +
+ "name, " +
+ "schema, " +
+ "createdby as definer, " +
+ "routineid, " +
+ "parm_count, " +
+ "case external_action " +
+ "when 'E' then 'has external side effects' " +
+ "when 'N' then 'has no external side effects' " +
+ "else 'ORIGIN is not E or Q' " +
+ "end as side_effects, " +
+ "fenced, " +
+ "language, " +
+ "sql_data_access as contains_sql, " +
+ "result_cols, " +
+ "class, " +
+ "jar_id " +
+ "from sysibm.SYSROUTINES " +
+ "where schema = ? " +
+ "and name = ? " +
+ "and ROUTINETYPE = 'F' ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getViewSourceSql()
+ */
+ @Override
+ public String getViewSourceSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select mqt_definition " +
+ "from qsys2.systables " +
+ "where table_schema = ? " +
+ "and table_name = ? ";
+ break;
+ case LUW:
+ result =
+ "SELECT text " +
+ "FROM SYSCAT.VIEWS " +
+ "WHERE viewschema = ? " +
+ "and viewname = ? ";
+ break;
+ case ZOS:
+ result =
+ "SELECT TEXT " +
+ "FROM SYSIBM.SYSVIEWS " +
+ "WHERE CREATOR = ? " +
+ "AND NAME = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ @Override
+ public String getTriggerDetailsSql() {
+ String result = null;
+
+ final String genericTriggerDetailsSql =
+ "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 = ? ";
+
+ switch(db2Type) {
+ case OS400:
+ result = genericTriggerDetailsSql;
+ break;
+ case LUW:
+ result = genericTriggerDetailsSql;
+ break;
+ case ZOS:
+ result =
+ "SELECT T1.schema AS trigger_schema, " +
+ "T1.name 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, " +
+ "t1.tbowner AS table_owner, " +
+ "T1.TBNAME 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, " +
+ "T1.REMARKS as comment " +
+ "FROM SYSIBM.SYSTRIGGERS T1, " +
+ "SYSIBM.SYSTABLES T2 " +
+ "WHERE T2.NAME = T1.TBNAME " +
+ "and T2.CREATOR = T1.TBOWNER " +
+ "and T1.SCHEMA = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getSequenceDetailsSql()
+ */
+ @Override
+ public String getSequenceDetailsSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "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 " +
+ "where sequence_schema = ? " +
+ "and sequence_name = ?";
+ break;
+ case LUW:
+ result =
+ "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 = ? ";
+ break;
+ case ZOS:
+ result =
+ "SELECT T1.schema AS sequence_owner, " +
+ "T1.createdby AS sequence_definer, " +
+ "T1.NAME AS sequence_name, " +
+ "case t1.SOURCETYPEID " +
+ "when 0 then char(t1.DATATYPEID) " +
+ "else t2.name " +
+ "end 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.CREATEDTS AS create_time, " +
+ "T1.ALTEREDTS AS last_alter_time, " +
+ "case T1.SEQTYPE " +
+ "when 'I' then 'Identity column' " +
+ "when 'S' then 'User defined' " +
+ "when 'X' then 'Implicit DOCID for XML data' " +
+ "end AS origin, " +
+ "T1.REMARKS AS comment " +
+ "FROM sysibm.SYSSEQUENCES T1 left outer join sysibm.SYSDATATYPES T2 " +
+ "on T1.DATATYPEID = T2.DATATYPEID " +
+ "where T1.SEQSCHEMA = ? " +
+ "and T1.SEQNAME = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getProcedureSourceSql()
+ */
+ @Override
+ public String getProcedureSourceSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select routine_definition from qsys2.sysroutines " +
+ "where routine_schema = ? " +
+ "and routine_name = ? ";
+ break;
+ case LUW:
+ result =
+ "select " +
+ " case " +
+ " when language = 'C' then '" +i18n.C_LANGUAGE_PROC_MSG+"' " +
+ " else text " +
+ " end as text " +
+ "from SYSCAT.PROCEDURES " +
+ "where PROCSCHEMA = ? " +
+ "and PROCNAME = ? ";
+ break;
+ case ZOS:
+ result =
+ "select text " +
+ "from sysibm.SYSROUTINES " +
+ "where schema = ? " +
+ "and name = ? " +
+ "and routinetype = 'P' ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getUserDefinedFunctionListSql()
+ */
+ @Override
+ public String getUserDefinedFunctionListSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select routine_name " +
+ "from QSYS2.SYSFUNCS " +
+ "where routine_schema = ? " +
+ "and routine_name like ? ";
+ break;
+ case LUW:
+ result =
+ "SELECT name " +
+ "FROM SYSIBM.SYSFUNCTIONS " +
+ "WHERE schema = ? " +
+ "AND name like ? " +
+ "AND implementation is null";
+ break;
+ case ZOS:
+ result =
+ "select routine_name " +
+ "from SYSIBM.SYSROUTINES " +
+ "where routine_schema = ? " +
+ "and routine_name like ? ";
+ result = "";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getSequenceListSql()
+ */
+ @Override
+ public String getSequenceListSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select sequence_name " +
+ "from qsys2.syssequences " +
+ "where sequence_schema = ? " +
+ "and sequence_name like ? ";
+ break;
+ case LUW:
+ result =
+ "select SEQNAME " +
+ "from SYSCAT.SEQUENCES " +
+ "WHERE SEQSCHEMA = ? " +
+ "AND SEQNAME like ? ";
+ break;
+ case ZOS:
+ result =
+ "select SEQNAME " +
+ "from sysibm.SYSSEQUENCES " +
+ "WHERE SEQSCHEMA = ? " +
+ "AND SEQNAME like ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getTableIndexListSql()
+ */
+ @Override
+ public String getTableIndexListSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select " +
+ "index_name " +
+ "from qsys2.sysindexes " +
+ "where table_schema = ? " +
+ "and table_name = ? ";
+ break;
+ case LUW:
+ result =
+ "select INDNAME from SYSCAT.INDEXES " +
+ "where TABSCHEMA = ? " +
+ "and TABNAME = ? ";
+ break;
+ case ZOS:
+ result =
+ "select name " +
+ "from sysibm.sysindexes T1 " +
+ "where creator = ? " +
+ "and TBNAME = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getIndexDetailsSql()
+ */
+ @Override
+ public String getIndexDetailsSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "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 = ? ";
+ break;
+ case LUW:
+ result =
+ "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 = ? ";
+ break;
+ case ZOS:
+ result =
+ "select t1.CREATOR as index_creator, " +
+ "T1.NAME as index_name, " +
+ "t1.TBCREATOR as table_creator, " +
+ "t1.TBNAME as table_name, " +
+ "t1.INDEXSPACE as index_space, " +
+ "t1.COLCOUNT as index_columns, " +
+ "case T1.INDEXTYPE " +
+ "when '2' then 'Type 2 index' " +
+ "when ' ' then 'Type 1 index' " +
+ "when 'D' then 'Data-partitioned secondary index' " +
+ "when 'P' then 'Partitioning index' " +
+ "end AS index_type, " +
+ "case t1.UNIQUERULE " +
+ "when 'P' then 'UNIQUE (Implements PK)' " +
+ "when 'U' then 'UNIQUE' " +
+ "when 'D' then 'NON-UNIQUE' " +
+ "when 'C' then 'UNIQUE (enforces unique constraint)' " +
+ "when 'N' then 'UNIQUE WHERE NOT NULL' " +
+ "when 'R' then 'UNIQUE (enforces uniqueness of non primary parent key)' " +
+ "when 'G' then 'UNIQUE (enforces uniqueness of ROWID GENERATED BY DEFAULT " +
+ "column)' " +
+ "when 'X' then 'UNIQUE (enforces uniqueness of XML column)' " +
+ "end as uniqueness, " +
+ "t1.CLUSTERING as clustering, " +
+ "t1.CLUSTERED as clustered, " +
+ "case t1.CLUSTERRATIO " +
+ "when 0 then 'No statistics gathered' " +
+ "when -2 then 'Auxiliary table index' " +
+ "else char(t1.CLUSTERRATIO) " +
+ "end as cluster_ratio, " +
+ "case t1.PGSIZE " +
+ "when 4096 then '4K' " +
+ "else strip(char(t1.PGSIZE))||'K' " +
+ "end as pagesize, " +
+ "case T1.NLEAF " +
+ "when -1 then 'No statistics gathered' " +
+ "else char(t1.nleaf) " +
+ "end AS number_of_leaf_pages, " +
+ "case T1.NLEVELS " +
+ "when -1 then 'No statistics gathered' " +
+ "else char(t1.nlevels) " +
+ "end AS number_of_levels, " +
+ "case t1.FULLKEYCARD " +
+ "when -1 then 'No statistics gathered' " +
+ "else char(t1.FULLKEYCARD) " +
+ "end as full_key_cardinality, " +
+ "T1.CREATEDTS as create_time, " +
+ "T1.STATSTIME AS last_statistics_update, " +
+ "t1.REMARKS as comment " +
+ "from sysibm.sysindexes T1 " +
+ "where creator = ? " +
+ "and name = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getTableTriggerListSql()
+ */
+ @Override
+ public String getTableTriggerListSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select trigger_name " +
+ "from qsys2.systriggers " +
+ "where trigger_schema = ? " +
+ "and event_object_table = ? ";
+ break;
+ case LUW:
+ result =
+ "select TRIGNAME from SYSCAT.TRIGGERS " +
+ "where TABSCHEMA = ? " +
+ "and TABNAME = ? ";
+ break;
+ case ZOS:
+ result =
+ "select NAME " +
+ "FROM SYSIBM.SYSTRIGGERS " +
+ "where SCHEMA = ? " +
+ "and TBOWNER = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ /**
+ * @see net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql#getTriggerSourceSql()
+ */
+ @Override
+ public String getTriggerSourceSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result =
+ "select action_statement " +
+ "from qsys2.systriggers " +
+ "where trigger_schema = ? " +
+ "and trigger_name = ? ";
+ break;
+ case LUW:
+ result =
+ "select TEXT from SYSCAT.TRIGGERS " +
+ "where TABSCHEMA = ? " +
+ "and TRIGNAME = ? ";
+ break;
+ case ZOS:
+ result =
+ "select TEXT " +
+ "FROM SYSIBM.SYSTRIGGERS " +
+ "where TBOWNER = ? " +
+ "and NAME = ? ";
+ break;
+ }
+
+ return result;
+ }
+
+ public String templateSql() {
+ String result = null;
+ switch(db2Type) {
+ case OS400:
+ result = "";
+ break;
+ case LUW:
+ result = "";
+ break;
+ case ZOS:
+ result = "";
+ break;
+ }
+
+ return result;
+ }
+
+}
Modified: trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/IndexDetailsTab.java
===================================================================
--- trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/IndexDetailsTab.java 2012-09-28 16:42:27 UTC (rev 6664)
+++ trunk/sql12/plugins/db2/src/main/java/net/sourceforge/squirrel_sql/plugins/db2/tab/IndexDetailsTab.java 2012-09-29 19:41:37 UTC (rev 6665)
@@ -1,4 +1,5 @@
package net.sourceforge.squirrel_sql.plugins.db2.tab;
+
/*
* Copyright (C) 2007 Rob Manning
* man...@us...
@@ -27,132 +28,64 @@
import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
+import net.sourceforge.squirrel_sql.plugins.db2.sql.DB2Sql;
+
/**
* This class will display the details for an DB2 index.
- *
*/
public class IndexDetailsTab extends BasePreparedStatementTab
{
- /** i18N strings for this class */
+ /** i18N strings for this class */
private static final StringManager s_stringMgr =
StringManagerFactory.getSt...
[truncated message content] |
