SAS EG - Need help on accessing data from two different database (AS400 and DB2)

[tsndrct1234]

Hi SAS Community!

Need your advise on this stored process changes I'm making. So, I am now trying to extract and merge the source of a list on my stored process and I used "union" for doing so. Now we noticed that when running the stored process, the list from LIB2.MASTER do not reflect but when we run the code manually (in a new program in SAS EG), it reflects successfully.

proc sql;
create table WORKSD7.EG_RCL_INST_X_FI_LIST_temp as
select distinct case
when XXXXXX in (0, 5) then 'Commercial Bank'
when XXXXXX in (1, 2, 3, 7) then 'Thrift Bank'
when XXXXXX in (4, 😎 then 'Rural Bank'
when XXXXXX = 57 then 'Corporation'
else 'Non-Bank Bank'
end as INDNAME length=35, INST as FINST, (XXXXXX * 10000) + ENTITY as BKCODE
from LIB1.INST
union
select distinct case
when XXXXXX = 31 then 'Pawnshop'
when XXXXXX = 60 then 'Money Service Businesses'
else 'Non-Bank Bank'
end as INDNAME length=35, FCONAME as FINST, (XXXXXX * 100000) + ENTITY as BKCODE
from LIB2.MASTER;
quit;

I noticed the difference between LIB1 and LIB2's DB connection. LIB1 is from AS400 and LIB2 is DB2. Now my question is, is the connection correct or is there a specific connection for DB2?

LIBNAME LIB1 DB2 Datasrc=XXXSYS2 SCHEMA=LIB1 USER=dwadmn PASSWORD="XXXXXXXXXXX" ;
LIBNAME LIB2 DB2 UTILCONN_TRANSIENT=YES Datasrc=XXXSYS2 SCHEMA=LIB2 AUTHDOMAIN="DB2Auth" ;

Really appreciate your answer. Thank you!

[SASKiwi]

Looks like you are using the SAS/Access DB2 engine for both tables. Are you reading a DB2 database on AS/400? Also your DATASRC is the same for both LIBNAMEs. That makes me suspicious that you are only reading from one database and not two.

[tsndrct1234]

Ohh I didn't noticed that, but yes, checking it again, the DATASRC is the same. Also, may I know what you meant by "Are you reading a DB2 database on AS/400?". I assumed it's DB2 because the tables under LIB1 is accessible on SAS EG, but for LIB2, I cannot view it and has to use proc sql or data step to check the contents of the table under LIB2.

[SASKiwi]

Also, may I know what you meant by "Are you reading a DB2 database on AS/400?" - I asked that question as you are using the DB2 engine on the AS400 LIBNAME and you didn't identify what database was on your AS400. Since you can see tables from that LIBNAME, I assume it is working with DB2. 

[Sajid01 · Accepted solution]

Hello @tsndrct1234 
Looks like you are accessing tables from two different schemas in a db2 database on an AS400 server.
For the issue with stored process accesing one of the libraries, this post is an excellent reference.
https://communities.sas.com/t5/Administration-and-Deployment/Stored-Process-Runs-on-SAS-EG-but-not-o....

Hopefully the solutions mention therein will help  resolve your issues.
This blog post helps debugging stored process issue https://blogs.sas.com/content/sgf/2021/10/20/debugging-a-stored-process-problem/.
If you stiil have issues please post the relevant sections of the logs.

[tsndrct1234]

Hello everyone just to close this thread, I discovered that the issue is not me accessing two different database. But I was actually trying to update the dynamic list of the Stored Process using the code inside it which is not feasible. As a resolution, I created a separate program that will update the data source of the dynamic list first so I can use it once I ran the Stored Process. Anyway, thank you so much for all of your help. I really appreciate this community. 🙂