DB2 – consulta de metadatos
Catalog
Schema: Doc original / standard name: syscat à DB2 alias: ibm
triggers
select text
from sysibm.sysTRIGGERS where tbname
secuencias
select *
from sysibm.syssequences where owner
stored
procedure / function
select *
from SYSIBM.sysroutines where owner
PK, unique
constraint, FK,
select *
from SYSIBM.syskeycoluse where constname
select *
from SYSIBM.SYSRELS where RELNAME
select *
from SYSIBM.SYSFOREIGNKEYS where relname
tables
FROM
SYSIBM.SYSTABLES
FROM
SYSIBM.SYSCOLUMNS where tbname and tbcreator
Dummy
sysibm.sysdummy1
Consultas recursivas en DB2 (jerarquía jerárquicas hierarchy hierarchial recursive)
/************************************************************************/
/************************************************************************/
/******************* COMPARE THE
FOLLOWING 2 QUERIES *******************/
/************************************************************************/
/************************************************************************/
-- TOP-DOWN
with MY_HIERARCHY (MY_NODE, MY_PARENTNODE) as (
-- non-recursive exit
select ROOT.MY_NODE, ROOT.MY_PARENTNODE
from THETABLE ROOT where MY_NODE='value' -- the root node in the tree
union all
-- recursive loop (it needs the
non-recursive part in order not to be an empty set)
select CHILD.MY_NODE, CHILD.MY_PARENTNODE
from MY_HIERARCHY PARENT, THETABLE CHILD
where CHILD.MY_PARENTNODE = PARENT.MY_NODE -- top-down join
)
select * from MY_HIERARCHY
-- BOTTOM-UP
with MY_HIERARCHY (MY_NODE, MY_PARENTNODE) as (
-- non-recursive exit
select LEAF.MY_NODE, LEAF.MY_PARENTNODE
from THETABLE LEAF where MY_NODE='value' -- the leaf node in the tree
union all
-- recursive loop (it needs the
non-recursive part in order not to be an empty set)
select CHILD.MY_NODE, CHILD.MY_PARENTNODE
from MY_HIERARCHY PARENT, THETABLE CHILD
where CHILD.MY_NODE = PARENT.MY_PARENTNODE -- bottom-up join
)
select * from MY_HIERARCHY