back porting from DB2 UDB V8 Unix to DB2 v7.2 on zOS

Dev Shed Forums Sponsor:

AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th-1:00PM EST. Register Today!

November 17th, 2004, 10:37 AM

davidg01

Registered User

Join Date: Nov 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

back porting from DB2 UDB V8 Unix to DB2 v7.2 on zOS

Hi,

We performing backporting of one of our applications from
DB2 UDB V8 Unix to DB2 v7.2 on zOS.

Can somebody say, if the following queries make sense on
DB2 v7.2 on zOS? At least several of syscat views we are
using seems not to be available on DB2 7.2 on zOS.

There is a full list of queries we need:

1) select schemaname from syscat.schemata order by schemaname

2) select tbl.tabname, tbl.tabschema, 'BASE TABLE', tbs.tbspace
from syscat.tables tbl, syscat.tablespaces tbs
where type = 'T' and tbl.tbspaceid = tbs.tbspaceid and tbl.tabschema = :user
order by tbl.tabname"

3) select colno, colname, typename, length
from syscat.columns
where tabname = :table and tabschema = :user
order by colon

4) select constname, tabschema, tabname, definer, refkeyname,
reftabschema, reftabname, deleterule, updaterule, fk_colnames, pk_colnames
from syscat.references where tabname = :table and tabschema = :user

5) ALTER TABLE ownerName.tableName ADD CONSTRAINT Constname
FOREIGN KEY Fk_colnames REFERENCES Reftabschema.Reftabname
(Pk_colnames)
ON DELETE NO ACTION ON UPDATE NO ACTION;

6) ALTER TABLE ownerName.tableName DROP FOREIGN KEY FK_Constname;

Thanks.
David.

November 18th, 2004, 08:08 AM

Onslaught

/(bb|[^b]{2})/

Join Date: Nov 2001

Location: Somewhere in the great unknown

Posts: 4,834

Time spent in forums: 1 Day 23 h 30 m 30 sec
Reputation Power: 88

The views may not be in place for the zOS instance.

The definition of syscat.schemata is:

Code:

create view syscat.schemata 
(schemaname, owner, definer, create_time, remarks)  
as select name, owner, definer, create_time, remarks  
from sysibm.sysschemata

syscat.tablespaces is:

Code:

create view syscat.tablespaces
(tbspace, definer, create_time, tbspaceid, tbspacetype,
datatype, extentsize, prefetchsize, overhead, transferrate,
pagesize, ngname, bufferpoolid, drop_recovery, remarks)  as select 
tbspace, definer, create_time, tbspaceid, tbspacetype,      
datatype, extentsize, prefetchsize, overhead, transferrate,      
pagesize, ngname, bufferpoolid, drop_recovery, remarks  from 
sysibm.systablespaces

syscat.columns is:

Code:

create view syscat.columns      
(tabschema, tabname, colname, colno, typeschema,      
typename, length, scale, default, nulls,      codepage, logged, 
compact, colcard,      high2key, low2key, avgcollen,      keyseq, 
partkeyseq, nquantiles, nmostfreq,      numnulls,      
target_typeschema, target_typename,      scope_tabschema, 
scope_tabname,      source_tabschema, source_tabname,      
dl_features, special_props,      hidden, inline_length, identity, 
generated, text, remarks)  as select      c.tbcreator, c.tbname, 
c.name, c.colno, c.typeschema,      c.typename, c.longlength, 
c.scale, c.default, c.nulls,      c.composite_codepage, c.logged, 
c.compact, c.colcard,      c.high2key, c.low2key, c.avgcollen,     
 c.keyseq, c.partkeyseq, c.nquantiles, c.nmostfreq,      
c.numnulls,      (select p.target_typeschema       from 
sysibm.syscolproperties p       where c.tbcreator = p.tabschema        
 and c.tbname = p.tabname         and c.name = p.colname),      
(select p.target_typename       from sysibm.syscolproperties p      
 where c.tbcreator = p.tabschema         and c.tbname = 
p.tabname         and c.name = p.colname),      (select 
p.scope_tabschema       from sysibm.syscolproperties p       
where c.tbcreator = p.tabschema         and c.tbname = 
p.tabname         and c.name = p.colname),      (select 
p.scope_tabname       from sysibm.syscolproperties p       where 
c.tbcreator = p.tabschema         and c.tbname = p.tabname        
 and c.name = p.colname),      c.source_tabschema, 
c.source_tabname,      case         when c.coltype = 'DATALINK' 
then           (select p.dl_features            from 
sysibm.syscolproperties p            where c.tbcreator = 
p.tabschema              and c.tbname = p.tabname              and 
c.name = p.colname)         else null      end,      case         
when c.coltype = 'REF' then         (select p.special_props          
 from sysibm.syscolproperties p            where c.tbcreator = 
p.tabschema              and c.tbname = p.tabname              and 
c.name = p.colname)         else null      end,      c.hidden, 
c.inline_length, c.identity, c.generated,      case         when 
c.generated = ' ' then null         else           (select case                     
 when posstr(ch.text, ' =  ') = 0                           then ch.text                     
 else 'AS' concat                           substr(ch.text,                                 
 posstr(ch.text, ' =  ') + 3)                   end            from 
sysibm.syschecks ch, sysibm.syscolchecks cc            where 
c.tbcreator = cc.tbcreator              and c.tbname = cc.tbname             
 and c.name = cc.colname              and cc.usage = 'T'             
 and cc.constname = ch.name              and cc.tbcreator = 
ch.tbcreator              and cc.tbname = ch.tbname)      end,     
 c.remarks  from sysibm.syscolumns c

syscat.references is:

Code:

create view syscat.references     
(constname, tabschema, tabname, definer, refkeyname,      
reftabschema, reftabname, colcount, deleterule, updaterule,      
create_time, fk_colnames, pk_colnames)  as select      relname, 
creator, tbname, definer, refkeyname,      reftbcreator, 
reftbname, colcount, deleterule, updaterule,      timestamp, 
fkcolnames, pkcolnames  from sysibm.sysrels

You can find the definitions for all of the views in sysibm.sysviews

__________________
Joe

How To Ask FAQ | How To Be A Hacker | Hacker Mindset | A FreeBSD Diary

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: