Thank you all for responses. I haven't had much time to test different setups so maybe the answer hides here somewhere. @LinusH Log hasn't revealed anything so far, but then again I'm not that good interpreting it. @Kurt_Bremser I tried using proc sql create table. Now that you suggestes using data step I tried it too, but the performance was the same. @SASKiwi Tried to fiddle with these settings as you suggested, but didn't get any observable differences yet. @ChrisNZ (and also @LinusH) couldn't get BULKLOAD to work yet. This is probably the root cause for this issue. Need to dig if my driver supports it. I'm afraid it might not. Will take quite a while though when I can next look into this so the answer needs to wait for now. ... View more

Thank you for the ideas. So far nothing "wrong" has been found on DB2 side. The alternate HADR server is configured there but everything there seems to be as they should be. By logging network traffic we were able to confirm that only the first connection attempt (that is successful also) goes to primary IP 111. The second attempt and each attempt after that go towards IP 110 already from the SAS server. Thus the redirection happens at the SAS server.   We have DB2 client on SAS server and that can connect to the primary 111 just fine. Though I don't even know how to actually replicate this situation on anything else than SAS as it is unclear to me how this "second connection" works technically compared to other clients.   On DB2 client connecting the first time to primary server will return the address of HADR server and that is stored in DB2 client. One theory I have is that when using SAS this same happens, but SAS doesn't know how to interpret this newly received address and instead of storing it as a backup option it stores is a primary option. So in a sense SAS tells it self that from now on anytime the user tries to connect to 111 I will redirect it to this new 110. ... View more

Thanks. I have been thinking of similiar solutions but instead so that I would create a subfolder inside SASUSER so that the datasets would remain on the SAS-server and they would not need to travel between networks. ... View more

There was a miscommunication between me and support. This feature will not work any differently on newest SAS versions, so these workarounds are still valid. ... View more

After discussing with support I got a solution that by defining W with the following statement should work with the newest SAS server version. I will have to wait for our company to update to that to test this. If someone has that and cares to try then go ahead. %let work_path=%sysfunc(pathname(WORK)); libname W "&work_path"; Meanwhile I have been able to user workaround by not defining W to be exatcly the same as the target library like SASUSER or WORK. In SAS user case I define W to be subfolder WORK in SASUSER. In WORK this couldn't be done as I have no rights to create folder inside the sessios specific WORK folder However I realized that the WORK folder is always a two level structure. So if C:\SASWORKS is the main folder where all WORK folder are generated then WORK physically points to C:\SASWORKS\Temp-session\WORK. Now I can define W to point at C:\SASWORKS\Temp-session, which still works like C:\SASWORKS\Temp-session\WORK in the sense that it gets erased after session is closed. ... View more

W is just an example name. The main point is to have a library that is usually same as WORK, but which I can change to some permanent library like SASUSER if needed. And I'm kinda of just started this "transition" to this new system so anything can be still changed if a better solution is found 🙂 OPTIONS USER=library; doesn't unfortunately help in this case. It is the same as using W except instead of writing W. in front of your table names you instead write nothing. The final result remains the same: defining USER=WORK results datasets in the process flow to point at WORK, not USER or "nothing", and then changing USER=SASUSER results datasets in the process flow to point at SASUSER, which breaks the link chain, since your program was assuming the source data of each program block to be from WORK. Another downside is that EG built-in blocks like query builder do not support "nothing" as library reference. You will have to specifically name the library where you want to put the dataset in. Though you can of course tell it to go to USER-library. ... View more

Similiar, but I think these things are fundamentally rather different since I'm creating datasets with PROC SQL while you are creating files with PROC EXPORT. This this you mentioned haven't given me problems, but now that you mention it I have encountered the same thing. Of course you can manually add the file and do the link if you always create the file with the same name, but if you create files with different names then this can be a problem. ... View more

I did figure out a workaround solution. I have a startup code that writes down the WORK library physical location for each session into SASUSER. In case of a session crash I can use that information to define OLDWORK-library and then move all data from that library into the current work. Not ideal since I need to dig out the old library location from SASUSER manually, run the code to move the data and wait for it to finish before I can continue, but at least it works. ... View more