Re: Proc fedsql does not find the correct schema

klroesner

Hello World,

I want to rewrite a simple proc sql into a proc fedsql but I fail already when accessing the SAP HANA Table. Apparently fedsql can't extract the correct schema from the libname. Instead of UPW_EGUIDE, my personal schema P226526 is used. Does anyone have any ideas?

libname UPWEGUID saphana dsn=PDWDM SCHEMA=UPW_EGUIDE CONNECTION=Global AUTOCOMMIT=NO INSERTBUFF=32767 READBUFF=32767 DBCOMMIT=50000 DIRECT_EXE=DELETE CHAR_AS_NCHAR=YES 
TABLE_TYPE=COLUMN SUB_CHAR=SPACE authdomain='P-USER' DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1;

/* this works well */
proc sql;
create table erg as
 SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ;
quit;

/* doesnt work */

proc fedsql _DIAG;
create table erg as
 SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ;
quit;

        
29         
30         libname UPWEGUID saphana dsn=PDWDM SCHEMA=UPW_EGUIDE CONNECTION=Global AUTOCOMMIT=NO INSERTBUFF=32767 READBUFF=32767
30       ! DBCOMMIT=50000 DIRECT_EXE=DELETE CHAR_AS_NCHAR=YES
31         TABLE_TYPE=COLUMN SUB_CHAR=SPACE authdomain='P-USER' DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1;
NOTE:  Credential obtained from SAS metadata server.
NOTE: Libref UPWEGUID was successfully assigned as follows: 
      Engine:        SAPHANA 
      Physical Name: PDWDM
32         
33         /* works fine */


34         proc sql;
35         create table erg as
36          SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ;
NOTE: Compressing data set WORK.ERG increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.ERG created, with 3 rows and 3 columns.

37         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

38         
39         /* doesnt work */
40         
41         proc fedsql _DIAG;
2                                                          The SAS System                           16:07 Saturday, December 7, 2024

42         create table erg as
43          SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ;
Table "UPWEGUID.DIM_TIME" does not exist or cannot be accessed
ERROR: [42S02]Table "UPWEGUID.DIM_TIME" does not exist or cannot be accessed (0x81bfc10c)
ERROR: [42S02]ERROR: [SAP AG][LIBODBCHDB SO][HDBODBC] Base table or view not found;259 invalid table name:  Could not find 
       table/view DIM_TIME in schema P226526: line 1 col 25 (at pos 24) (0x103)
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
44         quit;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FEDSQL used (Total process time):
      real time           0.26 seconds
      cpu time            0.02 seconds

Patrick

You could try and define the connection directly in Proc FedSQL

PROC FEDSQL <SAS-connection-option | CAS-connection-option> <processing-options>;

But... like you I'd expect the libname to also work for Proc FedSQL. I suggest you raise this with SAS Tech Support. If you do so please update the discussion here as well so we can learn something.

klroesner

Thanks Patrik, I reported this to the technical support 2 weeks ago, but have not yet received a solution. If I get one then I will share it … Klaus

Patrick

@klroesner

From experience with SAS TS (but same applies also to many other support orgs): Whenever you get an email from them even if it's only some "we're still looking into it" info always send a reply back. The reason being: If you receive an email then the ticket status goes to something like "waiting for input from customer" which can lead to your case not being worked on. If you reply then the status goes back to something where TS needs to take the next action.

klroesner

SAS Tech Support was able to reproduce the error and has forwarded it to R&D ... I'm curious ....

klroesner

Hi all,

It works with a three-part name: <libref>.<schema>.<table>:

libname PKMDWH saphana dsn=PDWDM schema=DM_PKMDM_BASIS &sapopt.;

proc fedsql _method libs=(PKMDWH) ;
create table work.test2 as
select * from PKMDWH.DM_PKMDM_BASIS.DIM_ORGE;
quit;

Fancy! 🙂

Proc fedsql does not find the correct schema