#413 z/OS DB2 catalog support

[Joel Korhonen]

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.

[Rob Manning]

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]

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

[Cristiano Guadagnino]

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]

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

Rob

[Rob Manning]

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

[Cristiano Guadagnino]

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

[Mikhail Sviridov]

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.

[Mikhail Sviridov]

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