#413 z/OS DB2 catalog support

open
Rob Manning
DB2 Plugin (12)
5
2014-04-15
2010-12-11
Joel Korhonen
No

On z/OS DB2 (V9), the CREATOR prefix of objects is SYSIBM instead of SYSCAT. Each system resource is further prefixed by the word SYS. I.e. the resources are: SYSIBM.SYSDATATYPES, SYSIBM.SYSINDEXES, SYSIBM.SYSSEQUENCES, SYSIBM.SYSTABLES, SYSIBM.SYSTABLESPACE (n.b.: not "SYSIBM.SYSTABLESPACES"), SYSIBM.SYSTRIGGERS, SYSIBM.SYSVIEWS. I didn't find an entry corresponding to SYSCAT.PROCEDURES.

I have attached the column names and types in the beforementioned tables. I'll try to check the details. The column names seem to vary at least. E.g. for views they are PATHSCHEMAS and NAME.

Discussion

<< < 1 2 3 4 > >> (Page 2 of 4)
  • Joel Korhonen
    Joel Korhonen
    2012-09-17

    It turned out DB2 10.1 for Windows reports getDatabaseProductName: 'DB2/NT'. So it seems that at least for the 10.x series, plain 'DB2' stands for z/OS.

    There are some other versions which I'm not able to test such as DB2 for AIX and for Solaris.

    I attached the full Windows v. metadata.

     
  • Joel Korhonen
    Joel Korhonen
    2012-09-17

    DB2 Win 10.1 metadata

     
  • Rob Manning
    Rob Manning
    2012-09-17

    Its ok for the same driver to be used with different platforms. The driver has its own name and version which should be distinct from that returned by getDatabaseProductName and getDatabaseProductVersion. Its good enough to know what the (hopefully) distinct values are for those two items for each platform. It sounds so far that this is the case. I'll work with whats reported here.

    Rob

     
  • Rob Manning
    Rob Manning
    2012-09-29

    Cristiano,

    Could you provide the z/OS SQL for querying what UDFs are available. For example, on LUW, this is the SQL:

    SELECT name
    FROM SYSIBM.SYSFUNCTIONS
    WHERE schema = ?
    AND name like ?
    AND implementation is null

    Rob

     
  • Rob Manning
    Rob Manning
    2012-09-29

    Actually, using your query for UDF source, I derived the following:

    select routine_name
    from SYSIBM.SYSROUTINES
    where routine_schema = ?
    and routine_name like ?

    I'll assume this works until I hear otherwise.

    Rob

     
  • Rob Manning
    Rob Manning
    2012-09-29

    Just checked in all of the changes required to implement Cristiano's contributed SQL for the z/OS platform. This will be available in the next snapshot release scheduled for tomorrow (Sept. 30). Many thanks to Cristiano!

    Rob

     
  • Rob Manning
    Rob Manning
    2012-09-29

    ...and apologies for the ~2 year delay :)

    Rob

     
  • Rob, the query for the UDF source I originally posted is wrong. I don't know why I posted those column names.
    You have to change "routine_schema" to "schema" and "routine_name" to "name".
    The UDF Details query was instead correct.

    Bye
    Cris

     
<< < 1 2 3 4 > >> (Page 2 of 4)