How to change the schema name in a convenient way

[Former Member]

Hi all,

Currently when I need to change the schema names for all tables in a job, I need to export to the .atl/.xml file, correct the schema name and re-import the file manually. Or I need to export to another repository and change the schema name meanwhile. (I mean schema name, not the owner name of the tables).

I think these methods are quite troublesome and unreasonable because I need to export/import the jobs.

Are there any convenient way to change the schema names of the tables? For instance using a parameter or variable like the other ETL tools do.

[Former Member · Accepted solution]

Hello

What you are trying to achieve is provided by Datastore aliases.  Its best to define them before you import any metadata, but you can retrospectively use them with existing schema names.

Michael

[Former Member · Accepted solution]

Hi Michael,

Does database aliases works for the schema name written in sql(..) function in script / SQL transformation as well?

Thank you for your advise.

[Former Member · Accepted solution]

Hello

Good question!  As always, Data Services has the answer!  A function called db_owner() is provided to return the alias value at runtime, so you can make your SQL dynamic, for example

$L_MinConvSQL = 'select min("ConversationID") from ' ||

  db_owner('DS_CASH_FORECASTING', 'ETL_INTF') || '.FX_INBOUND ' ||

  'where "SessionID" = {$G_SessionID}';

Michael

[Former Member · Accepted solution]

Lookup_ext uses Datastore name, Database Owner name and table name by default. Example: DS_EMP_DW.WRK.PLAN_CD.  This works just fine between DEV, PT, Regression and Production. Recently we created staging environment in the production box and it is called WRK_ST. We do not want to create a separate version where we have to change code to reflect as DS_EMP_DW.WRK_ST.PLAN_CD.  What are our options?  Is there a way we can obtain Database Owner name and modify the code to use it ? Example:

$db_owner_name = db_owner('DS_EMP', 'WRK_ST');

lookup_ext([DS_EMP.$db_owner_name.PLAN_CD ....

If I were to export this code to staging and run it in staging I would expect to interpret it as below.

looup_ext([DS_EMP.WRK_ST.PLAN_CD .....

I know above syntax is wrong. Just trying to get an idea from you on how to plug in made up qualified table name in the lookup_ext function.

I would like to get a reply fast. Please do what you can to get back to me.