Data store information from repo tables

[Former Member]

Hi,

i need to get the data stores names and type of connection ( SQL, Oracle.. etc) and user names from repository tables.

can i know from what tables i can get that info ? i have around 80 data stores.

TIA.

[former_member187605]

DS object definitions are stored in the repository in a proprietary format. There's no straightforward way to extract the information you're looking for.

As an example, run this SQL command (MS SQL Server syntax):

SELECT t.OBJECT_NORMNAME

     ,left(t.TEXT_VALUE+tt.TEXT_VALUE,charindex('</database_type>',t.TEXT_VALUE+tt.TEXT_VALUE,1)-1)

  FROM DM_DS_DIRK.dbo.AL_LANGXMLTEXT t

  JOIN DM_DS_DIRK.dbo.AL_LANG l

  on t.OBJECT_KEY = l.OBJECT_KEY

  join DM_DS_DIRK.dbo.AL_LANGXMLTEXT tt

  on t.OBJECT_KEY = tt.OBJECT_KEY

  and t.SEQNUM+1 = tt.SEQNUM

  where l.OBJECT_TYPE = 5

  and t.TEXT_VALUE+tt.TEXT_VALUE like '%</database_type>%'

  order by t.OBJECT_NORMNAME, t.SEQNUM

It returns the name and a string that ends with the database type for all datastores in the repository.

You may want to develop a data flow to extract the required information in a correct format .

[Former Member]

Thans Dirk, looks like i need to develop a big query to pull required info and i will do that.