topic Re: How SAS EG connecting to DB2 in Administration and Deployment

DB2 Repointing to New DB

SS_B — Fri, 21 Oct 2022 21:22:47 GMT

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'

Re: DB2 Repointing to New DB

SASKiwi — Sat, 22 Oct 2022 03:53:28 GMT

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.

Re: DB2 Repointing to New DB

SS_B — Mon, 24 Oct 2022 00:48:06 GMT

@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;

Re: DB2 Repointing to New DB

SASKiwi — Mon, 24 Oct 2022 02:31:40 GMT

@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.

Re: DB2 Repointing to New DB

LinusH — Mon, 24 Oct 2022 06:58:17 GMT

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".

Re: DB2 Repointing to New DB

SS_B — Mon, 24 Oct 2022 14:12:00 GMT

@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();

Re: DB2 Repointing to New DB

SS_B — Mon, 24 Oct 2022 14:15:48 GMT

@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

Re: DB2 Repointing to New DB

SASKiwi — Mon, 24 Oct 2022 19:20:19 GMT

@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.

Re: DB2 Repointing to New DB

LinusH — Mon, 24 Oct 2022 20:40:55 GMT

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.

Re: DB2 Repointing to New DB

Sajid01 — Thu, 27 Oct 2022 23:39:00 GMT

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.

How SAS EG connecting to DB2

SS_B — Fri, 28 Oct 2022 16:40:31 GMT

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?

Re: How SAS EG connecting to DB2

RajaMarla — Fri, 28 Oct 2022 18:54:55 GMT

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

Re: How SAS EG connecting to DB2

Sajid01 — Sat, 29 Oct 2022 17:24:46 GMT

How is SAS Connecting to DB2?

Re: How SAS EG connecting to DB2

SASKiwi — Sat, 29 Oct 2022 23:28:05 GMT

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#M25361

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.

Re: How SAS EG connecting to DB2

Kurt_Bremser — Sun, 30 Oct 2022 07:38:10 GMT

I merged this back into the original question thread.

Re: How SAS EG connecting to DB2

SS_B — Mon, 31 Oct 2022 13:59:37 GMT

We have fixed the issue by updating the new DB name in catalog entry and also updated the SAS EG autoexec file with correct new DB name.

Re: How SAS EG connecting to DB2

SASKiwi — Mon, 31 Oct 2022 21:15:53 GMT

@SS_B - Please update your post as answered in that case.