Desktop productivity for business analysts and programmers

Reading Oracle tables using PC Files Server

Reply
Occasional Contributor
Posts: 6

Reading Oracle tables using PC Files Server

I'm having trouble reading Oracle tables into Enterprise Guide using SAS PC Files Server (installed locally). The ODBC driver I'm using is 'Oracle in OraClient11g_home1'.

The connection seem to be ok, there are no errors in the libname assign, I can see the tables, open them and query them so long as I don't ask SAS to read any of the text fields. The character fields I'm working with in Oracle tend to be VARCHAR2(100).

I've tried various flavours of formatting the field, specifying the option DBMAX_TEXT on the libname statement, but I can't seem to get it to work. It wouldn't be so bad if I was given an error message, but it just hangs - it’s like control never gets returned to EG, so there are no errors in the log for me to work with. My only way of exiting each time is to close EG from Task Manager. Incidentally, it also does this if I have any errors at all in my code, even when querying fields it can handle if my code is perfect.

Since EG can open the the tables and display columns that I want, I thought perhaps I would be able to use the EG Query Builder, and EG would apply whatever options it needed to query the table (as it can obviously open it) but, again, it just hung at 'validating query'.

I'm not sure if I should be looking to change options on the ODBC driver, in EG... or maybe both, but I feel like I've tried everything!

Hope that makes sense.

Does anyone have any ideas?

Many thanks!

Community Manager
Posts: 2,697

Re: Reading Oracle tables using PC Files Server

When I've done work like this, I've used SAS/ACCESS to ODBC.  I described that process as an answer to this thread.

If you don't have SAS/ACCESS to ODBC, then perhaps you're using SAS/ACCESS to PC Files as an end-run to get to the data from SAS.  If you do get it to work it won't be very efficient, as all of the data will pull through the PC Files Server client.  You could achieve the same with a less Rube-Goldberg-style setup by using File->Open->ODBC from within SAS Enterprise Guide -- still slower, but easier to validate your connection.

Chris

Occasional Contributor
Posts: 6

Re: Reading Oracle tables using PC Files Server

Hi Chris,

Thanks for your reply. I should've probably also mentioned that I'm forced down the PC Files route because SAS/ACCESS Interface to Oracle/ODBC isn't licensed on the server.

I've tried 'File > Open > ODBC' as you mentioned, and I could see the Oracle tables, but when I chose one to open, not too much happens. I waited it out and EG moved to 'Not Responding', then eventually I got the error: 'Not enough storage is available to process this command'.

Thanks,
Jane

Esteemed Advisor
Posts: 6,685

Re: Reading Oracle tables using PC Files Server

"Validating query" involves reading quite a large part of our data. Since you have

- one physical network connection on your PC

- the connection to Oracle

- the connection to SAS via the PC File server

you get a parallel transfer to/from oracle and to/from SAS, which drowns your network connection with packet collisions. This leads to a performance very close to watching paint dry. Or grass growing.

Try to set up a direct connection from SAS to Oracle with the proper SAS/ACCESS module, or have the Oracle data unloaded into flat files and read those into SAS.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Reading Oracle tables using PC Files Server

Hi Kurt,

Thanks for your reply. I should've probably also mentioned that I'm forced down the PC Files route because SAS/ACCESS Interface to Oracle/ODBC isn't licensed on the server.

Agreed, this isn't the most efficient solution, but unfortunately it seems its the one I'm stuck with Unless I include a step to export the Oracle data first, as you suggest. Which I'd like to avoid if possible.

The volume of data I'm trying to pull for this test is small - approx 250 country codes and their associated country name. When I pull just the codes it's pretty much instant (then I'm not asking a lot!), but when I add the country names into the query, it never ends. I've left it running all night.

So do you think it just can't cope with this extra request, and I should probably give up on this as an option? I would like to get this to work, even if it took all day/night to run then I could happily write it off as a viable solution because of run time, but since it never finishes I feel like I might just be missing something and it could actually work.

Thanks,
Jane

Esteemed Advisor
Posts: 6,685

Re: Reading Oracle tables using PC Files Server

250 records of almost anything can't be a problem sizewise.

Maybe it is something in the text data that brings the ODBC route out of step.

Like some random succession of characters that are mistaken for a special code.

OTOH, that "not enough storage is available" seems to point in the direction of too much intermediate data.

Over here, we stopped using SAS/ACCESS to the database when we migrated SAS from the mainframe to UNIX (the DB people did not want to open an external port). Since then, all database data is unloaded into flat files and read from there, earlier on using the FTP access method, and now by external SFTP (called from the SAS program) and local read.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Reading Oracle tables using PC Files Server

I think perhaps doing that is certainly looking like the easiest way forward. I seem to have invested a lot of time so far and not really achieved too much! So I think exporting to flat files and reading them in is the way I'll go.

Thanks for your help Smiley Happy

Esteemed Advisor
Posts: 6,685

Re: Reading Oracle tables using PC Files Server

Of course it is a major PITA to do that complex transfer if all you need is, say, a lookup table with a few records (like you seem to do).

But since we do the complete ETL process over the Unload - SFTP - Data Step route, we decouple all data warehouse operations from the production servers. The unloads are done when the production servers have resources free (night, weekend), and under full DC control. All DWH work is done without affecting the main DB servers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 464 views
  • 1 like
  • 3 in conversation