07-24-2014 10:59 AM
As a part of project requirement,we are trying to connect the DB2 database and creating the DB2 Server for the same.Please let me know what are the exact requirements in this case.
After research.I came to know about the Database name,Schema name,User credentials are main requirements which have been collected.Which kind of user credentials are needed in this case?We just want to connect to database and extra data.No manipulation is to be done on the DB2 tables.Do separate user needs to be created in this case?
Now during server creation,after datasource(database) details,how do I use the authentication domain?How do we create new one with those user credentials?
Next when creating new library,schema is used along with above server.But we are facing the error when we are trying to register the tables as below :
"SQL1013N The database alias name or database name "XXX" could not be found.
Thinking that,it might be due to some drivers problem,we tried to see the ODBC connectivity and added db2 client driver with same database in SYSDSN. Then it started giving below error during register tables
"SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID"). SQLSTATE=08001"
Not able to understand,where are we going wrong and what is the exact procedure to be followed to connect to Db2.Any reference or explanation will be of great help.
07-24-2014 11:50 AM
Maddy, the decision wheter a group account or a personal key is required depends on the business policies on that with a data-classification.
See: https://communities.sas.com/thread/59551 for same kind of questions just translate oracle/db2.
I am associating DB2 wit a IBM mainframe running z/OS. In that case the security is often based on personal keys having propagated the whole way with RACF.
Do you have new users that are not having any DB2 access and the data-classification is that low you are allowed to used group-accounts you have to organize those.
You are accessing DB2 from a Windows (desktop) perspective as you are mentioning the SYSDSN with ODBC.
A keyfactor with db2 is the ssid/db2plan this must be defined correctly. The naming is determined by the DB2 DBA. You should verify this with him
https://communities.sas.com/message/218658 Firewalls are possible a disturbing factor but as you are getting that SQL30082N error is assume that part is ok.
Getting a connection is the easiest way first is trying a libname in a sas program. you can code the user/pswd there (temporary)
when this is succesfull you can proceed in getting that to sas-metadata (several pitfalls there). How far did you come?
07-24-2014 12:56 PM
If you remember,I have asked few doubts related to Interface to SAP BW long back.Through the data surveyor tool,we were able to extract the cube/dso data successfully based on the RFC user created.But the project required some additional data which is in the PSA (Persistent Staging Area) layer of SAP BW which my SAP connector is not able to connect.Upon enquiry,we came to know PSA tables reside in DB2 (unix platform) and now want to connect to them from SAS environment (DI Studio and SMC ).Not sure how to proceed and whether my same RFC user will work for this connection too or do I need to have some other user credentials.
Also,in the ODBC connectivity part,I see IBM Client Db2copy1 installed in my windows machine.
Once I get this user credentials right,I can run the LIBNAME function in base SAS as I have datasource and schema details and check it.Later move like what you said.How hostname,port number,DB2 Alias etc are used as part of ODBC connectivity.Need more information.
07-24-2014 02:03 PM
Maddy, of course I remember your recent post about SAP integration. That other guy has a lot of useful information given.
Proceeding with this one:
- you can copy and note all db/2 connection information from there. I think the DB2 user is a dedicated one hardcoded hidden in some configuration file.
- Copying all that db2 information out of the SAP defined environment and you are doing the same kind of work, no one could have objections to that.
The first goal is getting that db2 client with your SAS installation working.
There are some postinstallation sometimes needed when installing a dedicated client.
Did you check you SAS license has a SAS/Access DB2 part? (proc setinit ; run
When you are only having a ODBC SAS/Access you can focus on that. Functionality will not cover all db2 topics but will do for data-retrieval.