error when running code in job enviroment

umarigann — Wed, 18 Oct 2023 08:20:16 GMT

I have a code where i do few data engineering and create new tables in public from some postgres databases.

It runs fine when i run it on develop code platform however I am running into many errors when i schedule it on job.

I believe that the main reason is following:

error: ERROR: CLI describe error: SSL SYSCALL error: Connection timed out

Besides those errors I am getting errors such as

note: 
source: 268  proc fedsql sessref=casauto;
source: 269  drop table PUBLIC.TABLE;
normal: ERROR: CASDAL driver. DROP TABLE failed. Table TABLE_ could not be located in caslib Public.
normal: ERROR: The action stopped due to errors.
error: ERROR: The FedSQL action was not successful.
note: NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
source: 270  quit;
note: 
note: NOTE: PROCEDURE FEDSQL used (Total process time):
note:       real time           1.24 seconds
note:       cpu time            0.01 seconds
note:

source: 272  proc sql;
source: 273  create table public.table as 
source: 274  SELECT *
source: 275  FROM db.main_table;
error: ERROR: CLI prepare error: SQLPrepare unable due to the connection lost

source: 278  data public.another_table;
source: 279     set public.another_table_;
note: NOTE: The table PUBLIC.another_table_ does not exist in Cloud Analytic Services.
error: ERROR: File PUBLIC.another_table_.DATA does not exist.

There are many errors like this, for CLI describe error, the table creation just before this error, I changed all tables name to small letters and it worked as it does in develope code section but same code with capital letter took 4 hours to run.

I also asked for postgres logs during that 4 hour working but we still couldn't figure out the reason.

Re: error when running code in job enviroment

JosvanderVelden — Wed, 18 Oct 2023 09:03:12 GMT

Could the time it takes between running the libname statement and running the proc fedsql in the job be an issue?
Have you used DEFER=YES in the libname statement? See: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1aukl464makuln1mszw8l045xgy.htm

Re: error when running code in job enviroment

umarigann — Wed, 18 Oct 2023 09:15:36 GMT

there are some other sql queries that work between libname statement and proc fedsql errors

topic Re: error when running code in job enviroment in Administration and Deployment

error when running code in job enviroment

Re: error when running code in job enviroment

Re: error when running code in job enviroment