Repointing to another NEW DB

[jbond007]

Hi Communities,

If you need to repoint the database connection of an existing library from DBX to DBY (New DB hostname and new user). Do you need to update all the registered tables inside the existing library? If yes, how to update all?

[Sajid01]

Hello @jbond007 
When the database name has changed , then in essence it is a new connection. The connection is being made to a new server/database. So prima facie yes.
Details of registering tables could be found here https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/n0g5t6jbzorstsn18d5e9h7ngdkw.htm
Typically SAS Administrators would be able to handle this situation with ease.

[Patrick]

Assuming you're talking about SAS9.4 metadata:

If the tables on the new server are identical (name and structure) then I believe the only thing you would need to do is amend the metadata library definition and point it to the new server. As long as you don't create new metadata objects (like a new metadata library definition) then everything else should just continue to work.

If the metadata library is not pre-assigned and you've got DIS jobs then you would also need to re-deploy these jobs so the amended libname syntax gets generated in the .sas file. If the metadata library is pre-assigned then the DIS generated code only contains the libref which doesn't change and though you also don't need to re-deploy the jobs.

[jbond007]

Hello Patrick, yes, it's on SAS Metadata 9.4. I tried to repoint last night but it says the table doesn't exist even the table is there. I think need to update the metadata.

[Patrick]

---

@jbond007 wrote:

Hello Patrick, yes, it's on SAS Metadata 9.4. I tried to repoint last night but it says the table doesn't exist even the table is there. I think need to update the metadata.

---

This could also be due to some missing permissions on the DB. 

Have you already tried to create the new libname in SAS code and then run a Proc Contents? And if that works then a data step that reads one of the table (a small one) into SAS. 

If above works then the DB permissions should be sufficient. 

Next steps would be:

1. Using SMC take a metadata backup (so you can roll back if you did too much try and error with metadata changes).

2. Modify the libname metadata definition. 

- If you've got DI Studio with a job that uses a table belonging to this libref then make the metadata library definition not pre-assigned and then look what libname code a DIS job using one of the tables generates. This libname statement should now be the same like the one you've already tested earlier with code only.  

What you really need to avoid is to create new metadata objects as else anything that currently uses the existing table metadata won't work anymore because within a metadata repository objects are linked via metadata id and not via metadata name (and a new object like table metadata will get a new id even if it's stored under the same metadata name).