Datastage自打被IBM收购以后,就慢慢的不再用原来的Universal DB存东西了,而改用DB2存储元数据的信息。再通过Information Analyzer, Business Glossary, FastTrack, Metadata Workbench等功能组件将BI相关的各个组件当成资产管理起来,算是业界比较完整的元数据库,而对于企业而言,还是非常有价值的。比如,当一个表结构发生变动时,可以通过元数据库来获得其影响的ETL Jobs, Reports等等,本人最近刚刚开始对该数据库的研究,现总结一些研究结果如下,未来会持续更新。
1. 目前使用的Version 9.1,在Infosphere套系各路工具都安装了的情况下,一共1485张表,都放在schema 'XMETA'下面
select count(*) from sysibm.systables where creator='XMETA'
2. Naming Convention
....真是非常奇葩的Naming Convention,最长的表名居然达到86!!!如下所示:
DATASTAGEX_XMETAGEN_DSEXTERNALDEPENDENCY_RUNS_DSJOB_RUNBY_DSEXTERNALDEPENDENCYC2E76D84
组成基本上是:组件名称+"_"+表生成模式+“_”+实体本身的含义+“版本号”构成
比如DATASTAGEX_XMETAGEN_DSOUTPUTPINC2E76D84,可以拆解成
DATASTAGEX: 组件名称,此处为DataStage
XMETAGEN:这个是我个人猜想的,几乎所有的表都存着这么一段东西,我猜它的含义是XMETA Generated,代表XMETA生成的,可能是用于区别一些定制化的其他生成表。
DSOUTPUTPIN:这个才是真正的表名
C2E76D84:看起来像是产品号之类的东西,可能是为了便于区分多种产品的相关系统表存在一个库里面的东西
3. 一些有用的Query
来自:http://www.dsxchange.com/viewtopic.php?p=428876
1) 列出所有项目
SELECT *
FROM
XMETA.DATASTAGEX_XMETAGEN_DSPROJECTC2E76D84;
2) 列出一个项目下的Folder
SELECT *
FROM
XMETA.DATASTAGEX_XMETAGEN_DSFOLDERC2E76D84
WHERE
XMETA_CREATED_BY_USER_XMETA !='DataStageSystemUser'
AND NAMESPACE_XMETA ='<project_name>';
3) DS Job的相关信息查询
SELECT
(TIMESTAMP('01/01/1970', '00:00:00') +(XMETA_CREATION_TIMESTAMP_XMETA / 1000) SECONDS) AS CREATION_TIME,
(TIMESTAMP('01/01/1970', '00:00:00') +(XMETAMODIFICATIONTIMESTAMPXMET / 1000) SECONDS) AS MODIFIED_TIME, A.*
FROM
XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 AS A
WHERE
NAME_XMETA = '<job_name>';
4) 查查哪些Job被锁住了
SELECT
A.NAME_XMETA, B.LOCKED_BY_USER,
(TIMESTAMP('01/01/1970', '00:00:00') +(B.LOCKED_AT / 1000) SECONDS) AS LOCKED_AT
FROM
XMETA.XMETALOCKINFO B,
XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 A
WHERE
A.XMETA_REPOS_OBJECT_ID_XMETA = B.REPOSITORY_ID;
5)某个Job中的所有Stage信息
SELECT A.NAME_XMETA JOB_NAME,S.NAME_XMETA STAGE_NAME,
S.STAGETYPE_XMETA STTAGE_TYPE,S.*
FROM
XMETA.DATASTAGEX_XMETAGEN_DSSTAGEC2E76D84 S,
XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 A
WHERE
S.CONTAINER_RID =A.XMETA_REPOS_OBJECT_ID_XMETA
AND A.NAME_XMETA = '<job_name>';
6) 所有ETL Job的全路径
SELECT CONTAINER_RID,XMETA_REPOS_OBJECT_ID_XMETA, XMETA_CREATED_BY_USER_XMETA,
(TIMESTAMP('01/01/1970', '00:00:00') +(XMETA_CREATION_TIMESTAMP_XMETA / 1000) SECONDS) ASXMETA_CREATION_TIMESTAMP_XMETA , XMETA_MODIFIED_BY_USER_XMETA,(TIMESTAMP('01/01/1970', '00:00:00') + (XMETAMODIFICATIONTIMESTAMPXMET / 1000)SECONDS) AS XMETAMODIFICATIONTIMESTAMPXMET,NAME_XMETA,SHORTDESCRIPTION_XMETA,
JOBTYPE_XMETA,CATEGORY_XMETA,DSNAMESPACE_XMETA
FROM
DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84
WHERE
upper(DSNAMESPACE_XMETA) =upper('<project_name>')
and upper(category_xmeta) likeupper('\\Jobs\\');
4. 各个表的详细信息,内容太多,可能要分很多BLOG
Analysis tables start with“DATASTAGEX_XMETA_”
Table count = 36
1) DATASTAGEX_XMETAGEN_DSPROJECTC2E76D84
Short Name: DSPROJECT
Long Name: Datastage Project
Row Counts: 10
Comment: 某DS Server下所有Project
Table Understanding:
DS project, XMETA_REPOS_OBJECT_ID_XMETA is the PK, and the
HOSTNAME_XMETA+NAME_XMETA is unique
HOSTNAME_XMETA = The host name of the ETL server
NAME_XMETA = The project Name
2) DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84
Short Name: DSJOBDEF
Long Name: Datastage Job Definition
Row Counts: 5904(是我这边的环境,只是作为参考)
Comment: 所有Job的定义
Table Understanding:
DS ETL job definition, XMETA_REPOS_OBJECT_ID_XMETA is thePK, and the
DSNAMESPACE_XMETA+NAME_XMETA is unique
DSNAMESPACE_XMETA = Datastage Namespace,it is HOSTNAME + Project Name
Sample Data: DST34LP12.BLD.DST.IBM.COM:EIWDEV
NAME_XMETA = The job name
3) DATASTAGEX_XMETAGEN_DSSTAGEC2E76D84
Short Name: DSSTAGE
Long Name: Datastage Job Stages
Row Counts: 40977
Comments:Job中的Stage定义
Table Understanding:
PK: XMETA_REPOS_OBJECT_ID_XMETA
如上3张表的DDL均已上传
广告区:
本人2016-1-17开始推出的数据仓库建模指南系列课程,链接如下
http://www.hellobi.com/course/54
上线日期:2016-01-17
上线10天,410名观众
目前上线11天,451名观众