#413 z/OS DB2 catalog support

open
Rob Manning
DB2 Plugin (12)
5
2014-11-02
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 (Page 2 of 2)
  • Rob Manning
    Rob Manning
    2012-10-30

    Yes, this went onto the back-burner as other things took priotity. So I was looking at the Sequence Details tab stack trace, and the problematic SQL appears to be :

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

    Error being: T1.SEQSCHEMA IS NOT VALID IN THE CONTEXT

    Can you verify that this query works with string-literals ?

    Rob

     
  • Rob Manning
    Rob Manning
    2012-10-30

    I just found the UDF list bug (copy/paste error). Also, I fixed the trigger query according to your advice. I'll have another snapshot for you to try soon.

    Rob

     
  • Rob, the error T1.SEQSCHEMA IS NOT VALID IN THE CONTEXT is once again a goof on my part.

    You have to change this part of the query:

    where T1.SEQSCHEMA = ?
    and T1.SEQNAME = ?

    with this:

    where T1.SCHEMA = ?
    and T1.NAME = ?

    Bye
    Cris

     
  • Rob Manning
    Rob Manning
    2012-10-31

    Ok - I'll fix it. No worries. Have you had a chance to try out last night's snapshot ?

    Rob

     
  • Rob Manning
    Rob Manning
    2012-11-05

    OK, Cris - I just checked in the last fix according to your feedback. I don't believe there are any other issues that I haven't addressed. Let me know if that is not the case. The next snapshot should go out tonight. Also, let me know if everything works well, so I can close this ticket.

    Rob

     
  • Rob, I just finished trying out the latest snapshot (20121105_2000).
    Everything is working well, at least for what concerns the DB2 for z/OS catalog changes we've been doing.

    I've noticed that views and MQTs show an empty "source" panel. Also, the source panel for ALIASes shows the definition of the table, not that of the alias. I don't know if this is related to the changes we've been doing or if it is a generic issue. I did not test with other DBMSes.
    If you prefer, I'll open another bug for these issues.

    Bye
    Cris

     
  • On DB2 10 I have and getDatabaseProductName = DB2 for DB2 UDB for z/OS
    I've attached the entire metadata tab contents in my environment.

     
    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
<< < 1 2 (Page 2 of 2)