Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#245 DB2 on z/OS is different

open
Rob Manning
DB2 Plugin (12)
5
2014-04-16
2007-06-12
Anonymous
No

It would be helpful if there were config files for the databases, rather than code. In this case, SYSCAT is not always (perhaps rarely) defined for z/OS DB2. I don't know why. In order to get the information, one has to read the base tables in SYSIBM. Is it feasible to SQuirreL to distinguish?

Discussion

  • Rob Manning
    Rob Manning
    2007-08-18

    • labels: --> DB2 Plugin
    • assigned_to: nobody --> manningr
     
  • Rob Manning
    Rob Manning
    2007-08-18

    Logged In: YES
    user_id=1287991
    Originator: NO

    I've just recently added support for OS/400. You should take a look at the updated documentation (in SQuirreL Uunder Help -> Help -> DB2Plugin ) and see if the OS/400 queries will work on z/OS DB2. If they do not, then I would need someone who has access to a z/OS system to provide alternative queries to the ones we now have for LUW and OS/400 DB2. Then, I need the string value that is returned for getDatabaseProductName (click on the root node, then Metadata tab and look for getDatabaseProductName in the left column of the table). With those two pieces of information, I can update the DB2 plugin and then someone could verify that the changes work.

    Rob

     
  • Kos
    Kos
    2007-08-22

    Logged In: YES
    user_id=1567812
    Originator: NO

    I can access to DB2 for z/OS. Indexes, functions, source code of stored procedures, source code of view does not show. getDatabaseProductName return "DB2". It may be helpful - http://www.koders.com/java/fid2B9F717EBEBE6FB6E6D0B5D5676992D9395FECD2.aspx

    Indexes can be retrived by SQL "select NAME from sysibm.SYSINDEXES where TBCREATOR = ? and TBNAME = ?".

    Index details (not full, for test only): "select NAME as index_name, CREATOR as index_owner, TBNAME as table_name, TBCREATOR as table_owner from sysibm.SYSINDEXES where TBCREATOR = ? and NAME = ?".

    Functions: "select NAME from SYSIBM.SYSROUTINES where ROUTINETYPE = 'F' and SCHEMA = ?".

    Function details(not full, for test only): "SELECT OWNER AS function_owner, SCHEMA AS function_schema, NAME AS function_name, CREATEDBY AS function_creator FROM SYSIBM.SYSROUTINES where ROUTINETYPE = 'F' and SCHEMA = ? and NAME = ?".

    Procedure source code: "select CREATESTMT from sysibm.SYSROUTINES_SRC where SCHEMA = ? AND ROUTINENAME = ? order by SEQNO".

    View source code:
    "select TEXT from sysibm.SYSVIEWS " +
    "where CREATOR = ? AND " +
    "NAME = ? " +
    "order by SEQNO";

    Triggers: "select NAME from SYSIBM.SYSTRIGGERS where TBOWNER = ? and TBNAME = ?".

    Trigger details(not full, for test only):
    "SELECT CREATEDBY AS trigger_definer, " +
    " NAME AS trigger_name, " +
    " SCHEMA AS trigger_schema, " +
    " case TRIGTIME " +
    " when 'A' then 'AFTER' " +
    " when 'B' then 'BEFORE' " +
    " end AS trigger_time, " +
    " case TRIGEVENT " +
    " when 'I' then 'INSERT' " +
    " when 'U' then 'UPDATE' " +
    " when 'D' then 'DELETE' " +
    " else TRIGEVENT " +
    " end AS triggering_event, " +
    " TBOWNER AS table_definer, " +
    " TBNAME AS table_name, " +
    " case GRANULARITY " +
    " when 'R' then 'ROW' " +
    " when 'S' then 'STATEMENT' " +
    " else GRANULARITY " +
    " end AS granularity, " +
    " REMARKS comment " +
    "FROM SYSIBM.SYSTRIGGERS " +
    "WHERE TBOWNER = ? AND NAME = ?" +
    "FETCH FIRST 1 ROWS ONLY";

     
  • eagle79
    eagle79
    2007-11-01

    Logged In: YES
    user_id=1300245
    Originator: NO

    I don't know if it helps, but I'd like to second this request. I'd be willing to test if needed. Also I can provide a limited amount of help with the queries.

    You may want to look here as well: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp

     
  • eagle79
    eagle79
    2009-11-04

    If possible, I would like to renew this request. I would be more than happy to provide any information I can, and I do have access to a DB2/390 (i.e. z/OS) database where I can do some testing (though perhaps not of every functionality). More than anything I would REALLY like the object browser to work in certain cases (i.e. sequences).

    Noticing the details on the request, I can say that we have both a SYSCAT and a SYSIBM schema. I don't know what is default. Generally, however, we use SYSIBM for looking at database metadata. I have no idea whether that is common practice or not. It also appears that some of the tables used are named differently in z/OS... For example, Squirrel is attempting to read sequence information from SYSCAT.SEQUENCES, when it appears the information is available in SYSIBM.SYSSEQUENCES.

    Anyhow, please let me know if I can help in any way.

     
  • P. Labonte
    P. Labonte
    2012-04-02

    The value for getDatabaseProductName is "DB2" when I connect to the DB2 on z/OS. I can support with tests and information.

     
  • Is there any progress with Z/OS?

    I tried the snapshot from 20140329 and it doesn't work for me. The same problems with SYSCAT and so on.

    It's so sad that such small problems as incorrect table and schema those are used in the queries to list indexes, triggers, stored procedures and so on still exist in DB2 driver and prevent me and other DB2 for Z/OS users from usage of full power of squirrel.

    I can help with testing as well as P. Labonte. Feel free to contact me for testing purposes or even development.

    PS. What is the difference between DB2 App driver and DB2 net driver?

     
    Last edit: Mikhail Sviridov 2014-04-07
  • eagle79
    eagle79
    2014-04-07

    DB2 App driver is JDBC Type 2 (goes through an intermediate native client installed on the machine). DB2 Net driver is JDBC Type 4 (direct network connection to the database).

     
  • There is an idea to review DB2 plugin source code, fix problems by myself, though I have very small experience of java. Do somebody know where I can find information for how to build plugins for squirrel?

     
  • My driver returns a getDatabaseProductName = DB2 for DB2 UDB for z/OS
    I've attached an entire metadata tab contents. Maybe this will help developer.

     
    Attachments
  • I've fixed it by replacing
    "if (databaseProductName.equals("DB2")) { return ZOS; }"
    with
    "if (databaseProductName.matches("DB2.*z/OS.*")) { return ZOS; }"

     
    Last edit: Mikhail Sviridov 2014-04-15
  • eagle79
    eagle79
    2014-04-15

    Sviridov, is there some way I can get a test build of that from you? I'd like to test it in my environment.

     
  • Here is my version. I've just replaced the DB2PlatformType.class file with the fixed one. But my environment is Linux Mint. So I can't guarantee that it will work well on Windows. Although, java should be crossplatform.
    Place attached file to plugins folder of squirrel. Be notified that it's required to move out of this folder or remove the old one, because squirrel will try to load both of them and it will lead to a conflict at application startup.

     
    Last edit: Mikhail Sviridov 2014-04-16
    Attachments
  • eagle79
    eagle79
    2014-04-16

    Hmmm... it won't start in mine. I'm using snapshot 20140329_1150

    I get this:

    java.lang.NoSuchMethodError: net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.registerDataTypeFactory(Lnet/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/IDataTypeComponentFactory;ILjava/lang/String;)V
    at net.sourceforge.squirrel_sql.plugins.db2.DB2Plugin.initialize(DB2Plugin.java:189)
    at net.sourceforge.squirrel_sql.client.plugin.PluginManager.initializePlugins(PluginManager.java:575)
    at net.sourceforge.squirrel_sql.client.Application.executeStartupTasks(Application.java:803)
    at net.sourceforge.squirrel_sql.client.Application.startup(Application.java:225)
    at net.sourceforge.squirrel_sql.client.Main$2.run(Main.java:118)
    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$200(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at net.sourceforge.squirrel_sql.client.Main$1.dispatchEvent(Main.java:99)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

     
  • This is a windows 8 version. Try it, but it checks exact string "DB2 for DB2 UDB for z/OS" in my case. It's some problem with java in windows - it don't want to eat String.match(...) function. You can use a source code DB2PlatformType.java from your snapshot that can be located in the plugins/db2/db2-sources.jar archive (in the net/sourceforge/squirrel_sql/plugins/db2/sql directory inside the archive) to modify, compile it for your case if needed and put to db2.jar that resides in the squirrel's plugin directory.

     
    Attachments
  • There is some inconvenience when trying to view source of the system stored procedure - it's empty. For example, you could show stored procedure DDL without a body to make it easy to find out which parameters and of which type you have to provide to it to perform a call. Now the only info we have is a Columns tab for the selected stored procedure. It would be great if someone can add an ability to show stored procedure's DDL fragment in the source tab.
    BTW, I'll try to find it in the sources and implement.
    Am I right, that the developer stoped maintaining of the plugin? I think so as no comments from him here for a long time.

     
    Last edit: Mikhail Sviridov 2014-04-16
  • eagle79
    eagle79
    2014-04-16

    Actually, I was just playing around in the snapshot (without the patch Sviridov offered), and I noticed that most of the stuff that used to work incorrectly is now working; I'm not sure what changed. I do know that we recently upgraded to DB2 v10, so perhaps during that upgrade, something got fixed. It's also possible that the SYSCAT stuff got defined somewhere along the way.

    I do get the same thing with Stored Procedure source tabs... they all just show a semicolon. No errors in the log either, AFAIK.