What I encountered

I'm programming with Flows in SAS Studio in the ViyaCloud. While trying to load large Tables from a PostgreSQL Database I encountered the following error combination:

Two Error Pop-Ups. One stating that a problem with the SAS Session is detected. One "Run Flow" is blank.

I narrowed it down to too large Postgres tables conflicting with SAS Work library, as the problem also appears within 'normal' SAS programms and mindless mundane queries.

Basic example - Try it yourself

I tried different things but it narrows down to this simple list:

• With python I created myself data for a dummy DB table with 20 columns (1 'ID', 1 Date, 3 Character, 15 Numeric (Precision 8. if not Integers)) and 2.000.000 rows of random data
• I connected the PostgreSQL Database with a SAS Studio library using SAS/ACCESS to Postgres
  • I'm no admin, so I can't view detailed logs in the DB and have no details about the environment/database configurations
• In SAS Studio I created a new SAS Program and tested the connection successfully with a PROC SQL:

/* 1x ID, 10 Observations - Successfull */
PROC SQL OUTOBS=10;
    CREATE TABLE WORK.Test_Select_All AS SELECT t1.* FROM POSTGRES.RAND_DUMMY_DATA t1
    WHERE t1.id <= 1
    ;
QUIT;
RUN;

• Afterwards I removed the filter which lead to the described error/problem

/* All, 10 Observations - Fails */
PROC SQL OUTOBS=10;
    CREATE TABLE WORK.Test_Select_All AS SELECT t1.* FROM POSTGRES.RAND_DUMMY_DATA t1
    ;
QUIT;
RUN;

• If I use this PROC SQL to create a new Table not in WORK but in Postgres itself, it works
• When only 14 or less columns are selected, it works
  • If I only use one half (5 Info Cols with 7 Value Cols), then the other half (5 Info Cols with the other 8 Value Cols), it works for both
• With 15 or more columns it also fails

Regarding the connection:

• I tried using the 'Use DBMS specific tool/interface to retrieve data' and 'Enable bulk load' option, that didn't help either

PROC CONTENTS of DB Table

What I expected

That the DB table would be loaded into the Work Library without any errors, as runs with less data worked fine.

Update - Connection Issue?

After I added CONOPTS="UseDeclarefetch=1;FetchSize=250000" to my libname statement, my minimal example I posted works. But for other reasons I currently can't try more complex / the original ones.

I suspect that it's a RAM issue o.eq. of the Workspace and I need to adjust the connection to the DB further to not kill the process. Or request more RAM I guess. Keeping you updated.

I found a fix for this, even if I quite not understand the details.

It seems to be a memory/caching problem, because our default was so that everything was loaded at once. The following helped us to prevent this from happening:

1. Connection Options

Add the specific connection option to fetch the data 'batchwise' to the library definition like:

libname YOURNAME libdef='defSource' CONOPTS="UseDeclareFetch=1;FetchSize=500000";

2. Environment Variables

Let your Admin declare the fetch size in the environmentvariables that are called SAS_PG_USEDECLAREFETCH and SAS_PG_FETCHSIZE like above...