Retrieving data from DS8.1 xmeta tables

[prasad4004]

Can any one help me out in retrieving data from DS8.1 xmeta tables similar to retrieving data from UV tables in DS7.5

Thanks

[ray.wurlod]

The XMETA database is not meant to be used in this way. It is accessed via services such as the metadata delivery service and metadata analysis service.

[JoshGeorge]

Tables and columns in XMETA are reasonably meaningfully named which will make it easy for you to figure it out by yourself. There are few queries and hints already posted here.

Refer these links:

http://dsxchange.com/viewtopic.php?t=13 ... f67093a044

http://dsxchange.com/viewtopic.php?t=13 ... f67093a044

http://dsxchange.com/viewtopic.php?t=12 ... f67093a044

[prasad4004]

http://knol.google.com/k/seedhu/datasta ... v5dqqgym/1#

Prior to Datastage version 8, the datastage repository information was stored in files(uv). From version 8, the repository information is also stored in database. Since the information is available in database, it is easier to write queries and find the details. I think you can install the repository either in DB2 or in Oracle.IBM doesn't provide much information about the XMETA repository.

In my project, the datastage repository was built on DB2 database. In fact, datastage installation comes with DB2 database software.
I found that the table names and column names are bit weird with some funky characters & numerals at the end may be that's IBM strategy to keep XMETA information away from the developers..:)

A word of caution: Do not try to alter the XMETA repository contents as it may have adverse effects.

Some sample XMETA queries
1. Query to list Projects
SELECT * FROM XMETA.DATASTAGEX_XMETAGEN_DSPROJECTC2E76D84;

2. List the folders within a project
SELECT * FROM XMETA.DATASTAGEX_XMETAGEN_DSFOLDERC2E76D84
WHERE XMETA_CREATED_BY_USER_XMETA != 'DataStageSystemUser'
AND projectNAMESPACE_XMETA = 'COMMDSTAGEDEV:salesandadjustment';

3. Query to retrieve the DS JOB information
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 = 'mydsjob';

4. Query to list the jobs that are locked
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. Query to list the stages within the job
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 -- S.OF_JOBDEF_XMETA = A.XMETA_REPOS_OBJECT_ID_XMETA
AND A.NAME_XMETA = 'mydsjob';

6. Query to show the job run report by folder
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) - 8 HOURS AS XMETA_CREATION_TIMESTAMP_XMETA ,XMETA_MODIFIED_BY_USER_XMETA, (TIMESTAMP('01/01/1970', '00:00:00') + (XMETAMODIFICATIONTIMESTAMPXMET / 1000) SECONDS) - 8 HOURS AS XMETAMODIFICATIONTIMESTAMPXMET,NAME_XMETA,SHORTDESCRIPTION_XMETA,JOBTYPE_XMETA,CATEGORY_XMETA,DSNAMESPACE_XMETA FROM DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 WHERE upper(DSNAMESPACE_XMETA) = upper(:')
and upper(category_xmeta) like upper('\\Jobs\\');

[vmcburney]

The tricky part is joining tables - there are no easy foreign keys. Anyone figured that one out yet? I think the plan from IBM is to open up API calls to XMETA so you get info that way instead of against the tables.

[chulett]

Things like this make me smile:

Code: Select all

WHERE upper(DSNAMESPACE_XMETA) = upper(:') and upper(category_xmeta) like upper('\\Jobs\\');

[JoshGeorge]

Most of the job log information is in a table named LOGGING%*%LOGGINGEVENTxxxxxxxx which makes job reports / statistics easy to generate.
Where clause examples:
CATEGORYNAME_XMETA = 'IIS-DSTAGE-RUN' (For Datastage jobs)
LOG_SEQ=1 (To pick the latest run report)

For design related information / report generation here is an example:
Ex: Tables
DATASTAGEX%*%DSJOBDEFC2E76D84 J (For Job related information)
and
DATASTAGEX%*%DSSTAGEC2E76D84 S (For Stage related information)
can be joined using
J.OF_JOBDEF_XMETA = S.XMETA_REPOS_OBJECT_ID_XMETA

[carselid]

XMETA is disappointingly hard to decipher.

Wondering if anyone figured out a way to join the XMETA table "DATASTAGEX_XMETAGEN_DSCOLUMNDEFINITIONC2E76D84" (provides column names and properties ) to a table that provides JOB NAME and/or STAGE NAME.

I am on lookout for list of jobs that has columns of a particular data type.

The objective is something like being able to connect the following sqls

1)
select a.COLUMNREFERENCE_XMETA, a.SOURCECOLUMNID_XMETA, a.TABLEDEF_XMETA,a.NAME_XMETA
from DATASTAGEX_XMETAGEN_DSFLOWVARIABLEC2E76D84 a
where COLUMNREFERENCE_XMETA ='.. column name ..'

2)
select distinct J.CATEGORY_XMETA,j.NAME_XMETA as job_name, s.NAME_XMETA as stage_name
from DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 J
, DATASTAGEX_XMETAGEN_DSSTAGEC2E76D84 S
where
J.XMETA_REPOS_OBJECT_ID_XMETA = S.CONTAINER_RID
and j.NAME_XMETA ='...job name...'

3)
select NAME_XMETA,NATIVETYPE_XMETA,SHORTDESCRIPTION_XMETA,TYPECODE_XMETA
from DATASTAGEX_XMETAGEN_DSCOLUMNDEFINITIONC2E76D84
where 1=1
and TYPECODE_XMETA in (9,14)
and NATIVETYPE_XMETA is not null
and NAME_XMETA='...column name..'

Appreciate any help.

[eostic]

That's a fairly straightforward query using Metadata Workbench...

Alas, going directly after xmeta is sometimes useful, but can be difficult because of the relationships within, and some of the datatypes that are used. The most interesting thing I've done with it was to write an RSS query against the Business Glossary tables in xmeta so that I would have immediate awareness of when a BG term was edited or changed. Interesting, but the use case wasn't strong enough to pursue it on a regular basis. Great queries up above. Use them with caution when they are useful for you, don't expect them to always work from release to release, and and don't change anything!

Ernie

[carselid]

Thanks much Ernie for the advice.

Somehow building up sqls to dig ourselves is much flexible than using tools i guess. I am a novice when it comes to various informationServer peripheral tools .... i hope i figure out what Metadata Workbench has to offer.

[chanaka]

Hi Experts,
Is there any way for us to link XMETA.DATASTAGEX_XMETAGEN_DSPROJECTXXXXXXXX and DATASTAGEX_XMETAGEN_DSJOBDEFXXXXXXXX?

Cheers!

Chanaka

[stuartjvnorton]

It's a bit dodgy, but it looks like

XMETA.DATASTAGEX_XMETAGEN_DSPROJECTXXXXXXXX.HOSTNAME_XMETA || ':' || XMETA.DATASTAGEX_XMETAGEN_DSPROJECTXXXXXXXX.NAME_XMETA = DATASTAGEX_XMETAGEN_DSJOBDEFXXXXXXXX.DSNAMESPACE_XMETA

would work (in 8.7 at least).

One way to find a more kosher link (if one exists) would be to take the XMETA_REPOS_OBJECT_ID_XMETA for a project you use and use the catalog to do a brute-force search for it.

[chanaka]

Thanks Stuart!!!