DB2 Repointing to New DB

[SS_B]

Could you please lets us know what change are required if we want to repoint the DB2 DB to new DB?

Currently we are using SAS, its connecting to DB2 DB called 'ABC' , Now we have created another DB 'XYZ' and we want to repoint the DB to 'XYZ' instead of 'ABC'

When we access the UI and submit the jobs it will point to the new DB 'XYZ'

[SASKiwi]

So how are you pointing to ABC now? Using a LIBNAME statement? Defining data libraries pointing to DB2 in SAS metadata? Please post an example LIBNAME if that is the way you connect to DB2.

[SS_B]

@SASKiwi 

Libname is defined in one of sas config file. We have not defined Libname in metadata.

For example. 

libname  DB_schema &DBFlavor user=abc
password=?????
&DBConnOpts
&DBLibOpts;

[SASKiwi]

@SS_B  - Looks like you are using macro variables in your LIBNAME. Can you tell me what the values of those variables are? I have no idea where in the LIBNAME you are pointing to the ABC database. Need to know the value of both DBConnOpts and DBLibOpts.

[SS_B]

@SASKiwi  Please see below details. Just want to let you know we have hardcoded DB name in more then 100 codes so its not practical change the DB name in sasconfig or all all these sas codes.

Can we repoint DB name change outside of sas codes ? like JDBC level?

 %let DBConnOpts = %str(database=DBname);
%let DBLibOpts= %str();

[SASKiwi]

@SS_B  - No, you can't as far as I know. It is a pity you didn't just define the LIBNAME in just one place like the SAS AUTOEXEC, or in SAS metadata. That's the way we do it. It will be a pain to fix 200 programs but that will make maintenance a lot easier afterwards.

Does the user name and password change between users or is it static? If they change you might have to take a different approach. 

[LinusH]

If XYZ have the same schema (table structures) and you are only querying, you probably only have to redirect the LIBNAME.

If XYZ is not DB" and you do table updates, I would analyze the code and to some testing to be sure everything work as they should before "going live".

[SS_B]

@LinusH  New DB (XYZ) is clone of existing of DB(ABC) its has same schema name and other attributes. Can we change anything outside of sas codes? like JDFC 

[LinusH]

I don't about JDBC, but for ODBC you can fool around hand have an alias that point to new name.

But I don't recommend it, it can be confusing if you try to understand an environment and then the server name doesn't match the reality.

Given you example hopefully you only need to change in one place, given that you have a centralized setup with a shared autoexec.

[Sajid01]

The exact solution depends on whether you are using SAS Access Interface to Terradata or SAS Access interface to ODBC.
In a typical scenario where  ODBC  is being used  for the change mentioned in the post, changes need, changes need to be made in odbc.ini,
If there are changes to the driver too, them one needs to update the odbcinst.ini too.
In most scenarios the DBA's take care of this.
                                           

[SS_B]

We have recently repoint the DB from abc to xyz by updating the DB2 agent catalog entry and we keep alias as a same as we have used alias in most of codes and also defined in WebSphere to connect the UI.

Database alias                       = DB_connect (Not changed as same alias defined in sas codes)
Database name                        = XYZ (*****changed from abc to XYZ*****)

After updating the catalog entry in DB and compute server, all the UI (websphere) connecting to new DB(XYZ) but when we run the sas code in SAS EG its still connecting to old DB (ABC) Please advise how I can troubleshoot?

[RajaMarla]

It would help to see the code or the log.  Are you using a permanent library defined in SAS Management console?  If so - you may need to make the change in the SAS Management console/Data library

[Sajid01]

How is SAS Connecting to DB2?

[SASKiwi]

Not sure why you are asking the same question again that you asked in this earlier post: https://communities.sas.com/t5/Administration-and-Deployment/DB2-Repointing-to-New-DB/m-p/840033#M25...

We were still waiting for you to reply on the original post. It appears you still haven't fully explained how your SAS DB2 connection is setup and configured so that's why it is not progressing. It would help if you provided a complete LIBNAME statement that works correctly with your old database, one without macro variables so we can see which LIBNAME engine you are using and so on. You can mask any sensitive stuff like user and password, but we need to see all of the connection options.

[Kurt_Bremser]

I merged this back into the original question thread.