Hi team,
I am trying to run a SAS DI job with user written code which joins approx 4 table with some conditions.
But I am having the error :
Line 220: ERROR: Insufficient space in file WORK.W17G15B3.DATA.
Line 220: ERROR: File WORK.W17G15B3.DATA is damaged. I/O processing did not complete.
I tried logging off the session to empty the work space, but with no luck.
When I am trying the same query in SQL developer, it is working fine, and gives the results in no time.
Request your support on the same.
Regards
Amit
Compare the logs of the failed and successful attempts; also check the configurations of the respective SAS processes to see where the WORK is created. You might have WORKs on different phyiscal volumes of different size/free space left.
SQL Developer doesn't necessarily process all the data but stops once it can return some 100 rows or so. If you scroll down in the data grid you'll observe that at certain points there will be short waits - that's when the next slice of data gets queried.
If you execute the query out of SAS then all data will get transferred to the SAS side so here you'll experience the full run-time.
The general approach is always: Try to formulate your query in a way that data volumes get reduces as early as possible.
If you need further input from our side: Post the SQL code so we get an idea what you're doing and if there are "obvious" performance tweaks.
Thanks for the guidance.
I am using the below query to join 5 tables, and don't want to make a temporary table in between.
Is it necessary to break it in between, or making some changes in the local of log can be useful.
Query is as follows :-
SELECT
A.SERIAL_NUM,A.PAR_ASSET_ID,A.POLICY_SOURCE_ID,A.REASON_CD,A.ROOT_ASSET_ID,A.ROW_ID AS ASSET_ID,A.X_MVPN_FLG,A.STATUS_CD,A.END_DT,A.INTEGRATION_ID,A.NAME,A.START_DT,
/*
--A.SRV_REGN_ID,A.X_GROUP_MEM_FLG,A.X_MNP_PORTOUT_FLAG,A.X_STC_IMSI,A.X_STC_PLATE_ID,A.X_STC_PORT_IN_FLG,A.X_STC_PROM_ID,A.X_STC_SIM_NUM,A.X_STC_SIM_SRV_SUB_TYPE2,
*/
A.X_STC_SIM_TYPE,A.CREATED,A.ASSET_NUM,A.PROD_ID,
/*
--A.OWNER_ACCNT_ID,A.SERV_ACCT_ID,A.BILL_ACCNT_ID,
*/
B.STC_CONTACT_NUMBER,
A1.CHAR_VAL AS CATEGORY,A2.CHAR_VAL AS NETWORK_SUPPORT,A3.CHAR_VAL AS MVPN_FLAG,A4.CHAR_VAL AS GROUP_ID,A5.CHAR_VAL AS SUB_GROUP_ID,A6.CHAR_VAL AS CUSTOMER_ID,
A7.CHAR_VAL AS MULTI_SIM_FLAG,A8.CHAR_VAL AS RATE_PLAN_VALUE,
/*
--C.ABA_NUMBER,C.ACCNT_TYPE_CD,C.OU_NUM,C.OU_TYPE_CD,C.PR_CON_ID,C.TICKER_SYM,C.X_ACCOUNT_SUB_TYPE,C.X_MIN_RATE_PLAN_ALLOWED,
*/
C.OU_NUM AS CUSTOMER_NUMBER ,E.OU_NUM AS BILLING_ACCOUNT, F.OU_NUM AS SERVICE_NO,
C.X_STC_BLACKLIST,C.X_COMMERCIAL_REG_NUM,
D.PRODUCT_ID , D.NAME AS PRODUCT_NAME,D.ALIAS_NAME,D.PROD_STATUS,D.PROD_CATG_CD
FROM
EBUSTG.CRMG_M_S_ASSET_ACTIVE A
LEFT OUTER JOIN
EBUSTG.CRMG_M_X_ASSET_X B
ON
A.ROW_ID =B.ROW_ID
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A1
ON
A.ROW_ID = A1.ASSET_ID
AND A1.ATTR_NAME = 'Category'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A2
ON
A.ROW_ID = A2.ASSET_ID
AND A2.ATTR_NAME = 'Network Support'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A3
ON
A.ROW_ID = A3.ASSET_ID
AND A3.ATTR_NAME = 'MVPN Flag'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A4
ON
A.ROW_ID = A4.ASSET_ID
AND A4.ATTR_NAME = 'Group Id'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A5
ON
A.ROW_ID = A5.ASSET_ID
AND A5.ATTR_NAME = 'Sub Group Id'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A6
ON
A.ROW_ID = A6.ASSET_ID
AND A6.ATTR_NAME = 'Customer Id'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A7
ON
A.ROW_ID = A7.ASSET_ID
AND A7.ATTR_NAME = 'Multi SIM Flag'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ASSET_XA A8
ON
A.ROW_ID = A8.ASSET_ID
AND A8.ATTR_NAME = 'Rate Plan Value'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ORG_EXT C
ON
A.OWNER_ACCNT_ID = C.PAR_ROW_ID
-- AND C.ACCNT_TYPE_CD ='Customer'
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ORG_EXT E
ON
A.BILL_ACCNT_ID = E.PAR_ROW_ID
--AND E.ACCNT_TYPE_CD IN ( 'Billing','Prepaid Billing')
LEFT OUTER JOIN
EBUSTG.CRMG_M_S_ORG_EXT F
ON
A.SERV_ACCT_ID = F.PAR_ROW_ID
--AND F.ACCNT_TYPE_CD ='Service'
LEFT OUTER JOIN
(SELECT ROW_ID AS PRODUCT_ID,NAME,ALIAS_NAME,EFF_END_DT,PROD_CATG_CD,'Active' as PROD_STATUS FROM EBUSTG.CRMG_M_S_PROD_INT WHERE EFF_END_DT IS NULL) D
ON
A.PROD_ID =D.PRODUCT_ID
WHERE
(TRIM(LEADING '0' FROM A.SERIAL_NUM ) LIKE '5%' OR TRIM(LEADING '0' FROM A.SERIAL_NUM ) LIKE '8%')
AND A.ROW_ID = A.ROOT_ASSET_ID;
That looks to me like a lot of lookups with values from the same dataset (CRMG_M_S_ASSET_XA) that has name/value pairs.
If you transpose that first, you only need one join along asset_id/row_id.
And try to write code in lowercase, it makes code much more readable.
All uppercase is sooo 1950's 🙂
Looking at your code and that you can execute this fully via SQL Developer I'd assume that also out of SAS everything runs fully in-database. Are you using explicit pass-through?
IF everything executes in-database then I don't really understand why you should be running out of WORK on the SAS side unless your WORK is really filled up (whole disk or your quota bit) OR you've got 1:M relationships in your lookups resulting in huge amounts of data.
Do you know how many rows your query actually creates? Have you ever run it directly out of SQL Developer with a simple SELECT COUNT(*) ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.