About LaurieF

LaurieF

I disagree - that's not what the description of the parameter implies at all. As I said, it caches the connections, as many processes do, making the it more efficiently. Since I am calling proc http sometimes hundreds of thousands of times a day, including in a single job, I want it to run as efficiently as possible. In effect, turning it off hardly made a dent in my run-times, and according to SAS Global Hosting, the open connection counts from my jobs have dropped right off. I can imagine however situations where keeping the connections and credentials cached could be an advantage.

LaurieF

Yet a better fix. Changing the Linux setting fixed the problem, but adding no_conn_cache as a parameter to the proc http call stops the connections being retained. There's a small overhead apparently (When you enable NO_CONN_CACHE, you forgo all benefits of cached connections, including cached authentication. Each call that uses authentication must re-authenticate, which can take time) but the effect is empirically minimal. I'm still getting four gets a second as before.

LaurieF · ‎05-14-2025

After much discussion with SAS Global Hosting, they came up with a solution. We had originally discounted the -nofiles value, because both the service account and my userid had the default value of 350,000. But someone had the bright idea of checking what LSF was doing - and found that it was overriding the value either at (LSF) startup or when a job was being submitted. That value was 4,500. By removing that restriction and restarting LSF, the jobs now run to completion. Except for an API nextPage issue which has cropped up, which will keep me busy today. If it were easy, somebody would've already fixed it... Ngā mihi nui, Laurie

LaurieF · ‎05-14-2025

At that point, they are just files. It's not until two processes later that Snowflake/Iceberg attempts to read the files. They're a mixture of JSON and Parquet.

LaurieF · ‎05-12-2025

That's a bit harsh, and I won't respond, other than saying that the loop is where it is failing; all the file references are being closed, and I need to know what is causing the error. So far it appears to be a Linux nofiles setting which is particular to the service account.

LaurieF · ‎05-12-2025

It checks the maximum value of all extant return codes (sysrc, syscc, syserr, sqlrc, sqlxrc, syslibrc, and reports on error code values over 4.

LaurieF · ‎05-12-2025

This is just a partial bit of the code - %EndMac: is the penultimate line in the code. I can assure you that the file reference is being closed at the bottom of the loop.

LaurieF · ‎05-09-2025

As you can see from the code, I’m doing that. There is one filename reference which I clear at the bottom of the loop. Laurie

LaurieF · ‎05-09-2025

Yeah I know, but I have no influence over when it gets installed. I work for a very large organisation, with many SAS users, so arranging for even M8 to be installed is quite a thing. On top of that, my code is supposed to go live next month. Laurie

LaurieF · ‎05-08-2025

I have about forty jobs getting data from somewhere online (where? I don't know; online's everywhere). Most of them will pick up only a handle of incremental files each day and put them on our Linux terminal server; they then get pushed to an S3 bucket (somewhere else!), and from there read into Snowflake. As of today, it works perfectly, except for one job. We've ironed out almost all the procedural problems, except for one. When I run the jobs through DI (I've created a bespoke transformation which takes the name of the source table and automates the whole process through to Snowflake), they all run fine. But when they've been deployed and run under the service account, the big job (which reads roughly new 11.5k files a day) always crashes. Today's run was when it attempted file 3,574. Because the log becomes quickly unmanageable and for security reasons, I mask it by using option nomprint, but I expose where it's up to and the error messages. From the log: File 3,570: 07MAY2025:22:16:11 /org/warehouse/bin/gateway/edh/org_table_name/_change_data/cdc-00068-5dafcc5b-7512-4ecb-9175-91d01fb39600.c000.snappy.parquet File 3,571: 07MAY2025:22:16:11 /org/warehouse/bin/gateway/edh/org_table_name/part-00066-c7df53d0-0747-4e30-8c39-16c9ac9d075b.c000.snappy.parquet File 3,572: 07MAY2025:22:16:11 /org/warehouse/bin/gateway/edh/org_table_name/part-00067-e7b5f225-f351-448a-b5f9-4624b613fdc0.c000.snappy.parquet File 3,573: 07MAY2025:22:16:11 /org/warehouse/bin/gateway/edh/org_table_name/part-00068-f00a655d-10c3-4b17-93dd-2083d213618b.c000.snappy.parquet ERROR: tkzCapture() failed ERROR: tkzCapture() failed ERROR: tkzCapture() failed ERROR: Unable to establish an SSL connection. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0b4en" is not found. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0b4en" is not found. ERROR: Message file is not loaded. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0b4en" is not found. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0b4en" is not found. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0a2en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0a2en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0a2en" is not found. ERROR: Message file is not loaded. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0a2en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0a2en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0a2en" is not found. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0b4en" is not found. ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Extension Load Failure: OS Error: -1 (/sso/sfw/sas/940/SASFoundation/9.4/sasexe/t0b4en.so: cannot open shared object file: Too many open files) ERROR: Message file "t0b4en" is not found. ERROR: Message file is not loaded. WARNING: Apparent symbolic reference SYS_PROCHTTP_STATUS_CODE not resolved. WARNING: Apparent symbolic reference SYS_PROCHTTP_STATUS_CODE not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &sys_prochttp_status_code > 200 ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition. I think that sys_prochttp_status_code is destroyed at the top of each http call and created again very soon after, so I suspect that the error is being picked up at the procedure initialisation. I've checked both my and the service account's Linux ulimit values - both 350,000, so the Too many open files would appear to be a red herring. Here's the meat of the getfiles macro: %do i = 1 %to &files; %let rc = %sysfunc(fetchobs(&dsid, &i)); %let file = %sysfunc(strip(&file)); %if %eval(%sysfunc(indexc(&file, %str(/)))) %then %do; %let sub_directory = %sysfunc(scan(&file, 1, %str(/))); %if %eval(%sysfunc(fileexist(&parent_directory/&source/&sub_directory)) = 0) %then /* Create each non-existant directory */ %let rc = %sysfunc(dcreate(&sub_directory, &parent_directory/&source)); %end; %if %eval(%sysfunc(fileexist(&parent_directory/&source/&file)) = 1) %then /* Don't bother re-getting a file */ %goto EndLoop; filename source "&parent_directory/&source/&file"; %let url = https://&source_url/files/download?; %let url = &url.tableName=&source.%nrstr(&file=)&file; %let fail_count = 0; /* Every (hour - 500 seconds), get another bearer code. It is only valid for an hour, so 500 seconds short will (prob'ly) always work. If it doesn't, something else has gone wrong. This should be good for around 12-15,000 files at a time. */ %if %sysevalf(%sysfunc(datetime()) > "&bearer_expiry"dt) %then %renew_bearer; %do %until(%eval(&sys_prochttp_status_code) = 200); proc http url="&url" proxyhost="http://webproxy.vsp.sas.com:3128" oauth_bearer="&bearer" in='scope=urn://onmicrosoft.com/vcp/api/vbi/.default' out=source timeout=1000 /* How long to wait (seconds) */ method='get'; headers 'Accept' = 'application/json' 'consistencylevel' = 'eventual'; run; %if %eval(&sys_prochttp_status_code > 200) %then %do; %put %sysfunc(strip(%sysfunc(datetime(), datetime23.3))) HTTP Status code: &sys_prochttp_status_code %refnumv(val=&i) &=file; %let fail_count = %eval(&fail_count + 1); %if %eval(&fail_count > 5) %then %do; %check_status %goto EndMac; %end; %let rc = %sysfunc(sleep(30, 1)); %end; %end; %put File %refnumv(val=&i): %sysfunc(strip(%sysfunc(datetime(), datetime19.))) %sysfunc(strip(%sysfunc(putn(&lastmodified, datetime23.)))) &parent_directory/&source/&file; filename source clear; %EndLoop: %end; I could obviously check for the symbol existence of sys_prochttp_status_code before I check its contents - but its non-existence isn't something I had considered! I'm pretty much convinced that it's something specific with the service account, but my ingestion jobs run through it literally thousands of times a day without error, including many that use proc http, and I've never seen this before. Has anyone ever seen anything like this before? What is tkzCapture()? what are toa4en and t0b4en, and why can't they be (re-)opened? They do exist - seven years old. Maybe it's a factor of running M6; M8 may be getting installed mid-year.

LaurieF · ‎05-02-2024

I do this quite a lot, pulling data from Genesys Cloud in automated processes. It took me ages to get my ducks in a row; one of the major tools I used was examining the return code. After your call, check &sys_prochttp_status_code. If it's 200 or 202, you're getting close. Anything else and you'll get an indication of where your problem lies - see &sys_prochttp_status_phrase. Just an indication, mind. It's tricky.

LaurieF · ‎06-26-2023

I've been SASsplained! What an ignorant and patronising reply. And wrong. I've been using SAS for 45 years, and DI for twenty. I know what it can do. I know it can be used, at least as well as anybody else. Now if you aren't going to explain to me how I can use a facility built into a SAS/DI job, which doesn't appear to me to work, I suggest you delete what you just wrote and go and SASsplain to someone else.

LaurieF · ‎06-25-2023

A loop wouldn't be appropriate - each file has different metadata which the transformation acts upon. Remember the issue is the name of the json file, not the entries within it. When I open and run the job from within DI, I want to select the JSON file, as I would if I were running it from just a transformation. From a transformation I open it, click on Code options and select the file I want. From the job, I have to open the parameter, edit it and change the default to the file I want. There's no Code options at job level.

LaurieF · ‎06-25-2023

My latest DI transformation takes a JSON file as input, but there are potentially multiple files to read, from which I'm extracting just one dataset. The size of the file doesn't matter, because the JSON engine is very quick at reading even a multi-million observation file. For ease of management, I'm calling the transformation multiple times in a job, with the output work dataset from the transformation being the JSON entry. It works, and works very well. I previously had the JSON file as File or directory (input) option in the transformation, but with multiple transformation calls in a single job (I have reasons) I have put that file reference in as a job parameter. And that works fine. What doesn't is that I don't seem to. be able to reference that parameter from the job - I have to go into the parameter and change it instead of having it as a prompt that I can easily edit. Interestingly when I set up the parameter, I can use Test Prompts… in the same way as I would a transformation option. I just don't seem to be able to reference it any other way. Is there something obvious I have missed?

LaurieF · ‎09-12-2022

My user-written SAS Data Integration Studio code has to be able handle anything that gets thrown at it. And because I have no control over how the source or target tables have been defined, that includes source tables that have "Enable special characters within table or column object names" turned on. There's never any reason for this (as most source systems won't have column names with spaces or special characters), and it's probably because the developer who created the metadata for the table had their options set up poorly. Nevertheless the metadata has been constructed like that, and I have to cope with it. So I end up with a table name in the job defined in DI as: %let _source = srcref."accountType"n; When I compare that table against the target's target.acccounttype, even accounting for case, it fails because it's not expecting the quotes and n suffix. I thought I'd patched the code to account for all of these, but another one cropped up this morning with a new macro where I'd forgotten to include this little beauty: %let table = %sysfunc(dequote(%sysfunc(scan(&&&table, 2, %str(.))))); where table contains _source. The dequote function simply strips quotes off strings, but what I infer it's doing is taking off the first quote, walking along the string until it finds the last quote and stopping. So the result in table is simply: accountType This I can use, and the code no longer throws errors. Previously I'd created a DATA step where I used vname function to do the same thing (another handy side-effect), but that isn't available in %sysfunc. From the time the process specialist told me we had lots of jobs which had failed, until I'd changed the code, tested and pushed it for testing: 15 minutes.

Online Status	Offline
Date Last Visited	3 weeks ago