SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

ERROR: Insufficient space in file WORK

Reply
Contributor
Posts: 25

ERROR: Insufficient space in file WORK

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

Super User
Posts: 9,932

Re: ERROR: Insufficient space in file WORK

Posted in reply to amitvermajhs

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,698

Re: ERROR: Insufficient space in file WORK

Posted in reply to amitvermajhs

@amitvermajhs

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.

Contributor
Posts: 25

Re: ERROR: Insufficient space in file WORK

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;

Super User
Posts: 9,932

Re: ERROR: Insufficient space in file WORK

Posted in reply to amitvermajhs

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 Smiley Happy

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,698

Re: ERROR: Insufficient space in file WORK

[ Edited ]
Posted in reply to amitvermajhs

@amitvermajhs

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(*) ?

Ask a Question
Discussion stats
  • 5 replies
  • 112 views
  • 0 likes
  • 3 in conversation