insert into oracle table with fedsql

[acordes]

In order to update an oracle table from SAS Viya I have found a strategy that works for me.

But it's slow.

I hope that fixing the fedsql option the performance gets better. Here comes the error log.

76 proc fedsql sessref=mysession _method exec;

NOTE: FEDSQL: Running on CAS due to "sessref".

77 insert into MYORA.TEST_UPLOAD

78 select * from AKAIKE.TEST_DOWNLAOD1;

NOTE: Added action set 'fedsql'.

action returned string actionset = fedsql

NOTE: CAS action completed [OKAY]

FEDSQL: load of action set returned rc=00000000

ERROR: Unsupported SQL statement.

ERROR: The action stopped due to errors.

ERROR: The FedSQL action was not successful.

FEDSQL: The fedsql.execDirect action returned rc=0x000003f4

NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.

cas mySession sessopts=(caslib=akaike timeout=1800 locale="en_US");
caslib _all_ assign;

data TEST_DOWNLAOD1;
set AKAIKE.TEST_DOWNLAOD(obs=5000);
run;

data AKIAKE.TEST_DOWNLAOD1;
set AKAIKE.TEST_DOWNLAOD(obs=5000);
run;

/* works for leaving the table structure with 0 rows */
proc sql;
   connect to oracle as dbcon
       (user=SAS password='xxxxxxxxxx' readbuff=50000
        PATH=xxxxxx);
execute (truncate table TEST_UPLOAD)
BY DBCON;
quit;

LIBNAME myora ORACLE PATH=xxxxx USER=SAS PASSWORD='xxxxxxxxxxxx';
/* WORKS for append  */
proc append base=MYORA.TEST_UPLOAD 
data=AKAIKE.TEST_DOWNLAOD(OBS=10000); 
run;

/* WORKS for append  */
proc FEDsql;
  insert into MYORA.TEST_UPLOAD 
  select * from TEST_DOWNLAOD1;
QUIT;

/* does not WORK for append  */
proc fedsql sessref=mysession  _method exec;
  insert into MYORA.TEST_UPLOAD 
  select * from AKAIKE.TEST_DOWNLAOD1;
QUIT;

[jimbarbour]

Interesting behavior.  Apparently SAS (or Oracle) doesn't like how things are working when going directly against the CASLIB.

Have you tried creating a SAS view of AKAIKE.TEST_DOWNLAOD?  Maybe if you used a view as an intermediary, it would iron out the odd problem.  I don't know that this will work, but it's worth a shot.

When you run, would you try turning on the following options?  These are 9.4 options; I'm not sure they'll all work with Viya, but let's give it a shot.  This will add additional messages to the log which may give us a clue as to what's going on.  If one option or another doesn't work, comment it out and use the rest.

OPTIONS 	SASTRACE		=	",,,ds";
OPTIONS 	SASTRACELOC		=	SASLOG;
OPTIONS 	NOSTSUFFIX;
OPTIONS 	MsgLevel		=	I;
OPTIONS 	DEBUG			=	DBMS_SELECT;

Jim

[Tom]

Does it help to tell it which variables you actually are using?

proc fedsql sessref=mysession  _method exec;
  insert into MYORA.TEST_UPLOAD (var1, var2) 
  select var1,var2 from AKAIKE.TEST_DOWNLAOD1;
QUIT;

[acordes]

thanks, but doesn't work for me neither (replacing var1 with an existing varname)

[jimbarbour]

Have you tried creating a SAS view of AKAIKE.TEST_DOWNLAOD?  Maybe if you used a view as an intermediary, it would iron out the odd problem.  I don't know that this will work, but it's worth a shot.

When you run, would you try turning on the following options?  These are 9.4 options; I'm not sure they'll all work with Viya, but let's give it a shot.  This will add additional messages to the log which may give us a clue as to what's going on.  If one option or another doesn't work, comment it out and use the rest.

OPTIONS 	SASTRACE		=	",,,ds";
OPTIONS 	SASTRACELOC		=	SASLOG;
OPTIONS 	NOSTSUFFIX;
OPTIONS 	MsgLevel		=	I;
OPTIONS 	DEBUG			=	DBMS_SELECT;

Jim