SAS File System Full Error

tmm — Sat, 29 Sep 2012 21:30:50 GMT

What does SAS file system full error mean?

Re: SAS File System Full Error

Cynthia_sas — Sat, 29 Sep 2012 21:44:02 GMT

Hi:

What is the full text of the error message? Generally, if you enter the text into the search box at support.sas.com, you will find Tech Support notes such as this:

ERROR: Write to libref.sasdata.DATA failed. File is full and may be damaged. (http://support.sas.com/kb/31/401.html)

ERROR: Utility file write failed. Probable disk full condition. (http://support.sas.com/kb/14/705.html)

Otherwise, the message seems fairly self-explanatory. You might want to open a track with Tech Support to explore the possibilities. They could look at all the code that was executing when you received the error message, gather information from you about your configuration and operating system and help you resolve the issue.

cynthia

Re: SAS File System Full Error

tmm — Sun, 30 Sep 2012 02:28:13 GMT

ERROR: CLI cursor extended fetch error: [IBM][CLI Driver][DB2/AIX64] SQL0968C The file system is

full. SQLSTATE=57011

That is the error I get.

Re: SAS File System Full Error

jonam — Sun, 30 Sep 2012 04:42:45 GMT

Hi Tmm

This is a DB2 database error. This means

"The disk drive or the file system used by the db2 database server is full". Please contact your DB2 Database administrator to fix this issue or coordinate with your SAS admin to contact the DB2 admin to resolve this issue.

Before contacting them as a precautionary measure check your query to ensure your query is returning what you've asked for. I mean check your query to confirm you are not doing any cartesian product which results in returning huge results.

Re: SAS File System Full Error

tmm — Sun, 30 Sep 2012 12:48:10 GMT

I think it is the query. I added 1 field that connects to a join and that is when this started happening. Prior the query ran in just 20 mins. Adding this 1 field is when it started. I have used our data dictionary to get the join and so I am not sure why it would not work. Did a test of using 1 day pull instead of an entire year and using 1 day with this new field gave me 695 rows but the query took real time 3 hours, cpu time like 2 mins or something. So I am not sure. Will have to open a tkt and have them see if they can tell me another join to use besides what is in there or some other way of getting the data in this field.

Re: SAS File System Full Error

art297 — Sun, 30 Sep 2012 14:52:19 GMT

The following may help: http://support.sas.com/kb/12/402.html

Re: SAS File System Full Error

jonam — Sun, 30 Sep 2012 15:10:01 GMT

Hi ,

Could you please post the query before/after the error. or at least the joining conditions used in your query?

Re: SAS File System Full Error

tmm — Sun, 30 Sep 2012 17:19:12 GMT

Sure. It is a long query. The items that are not commented out have been tested by my Galaxy team who enhanced the query to make it run faster. Using the entire year from what you see in the startdt and enddt takes 20 minutes to run. When I add the cert_of_cov_fl_yr and then do any of 7 possible joins about 45mins to an hour and 15mins later it will say disk system full. So I changed my query to run just for 1 day. Using any of the 7 possible joins to the table that houses cert_of_cov_fl_yr, it runs about 3 hours on any of them and returns 695 rows. There are no other possible joins listed in our data dictionary. So I am sure since it takes 3 hours to return 695 rows that account for 1 day, trying to pull the whole year it just cannot handle because 365 days worth of data multiply by 3 hours would be a total runtime of 1080 hours. So not sure what the deal is with that one particular table. And the sad thing is I cannot take that field out because the PM needs it. I can also not just remove it for now and run the one that does run for 20minutes then do a group by the clm_aud_nbr and mbr_sys_id and do a left join to the contract plan coverage table because I am connecting via a DB2 and we cannot take data we extract onto our local machine and link it back to the server in SAS. I can do it in Access and have attempted but it is too large for Access to handle and Access will bomb.

options compress=yes reuse=yes;

%let pass='Year2012';

%let startdt='06/01/2011';

%let enddt='05/31/2011';

%let hgh=('80428','80429','80430','83003','84305','86277','J2940','J2941','S9558','Q0515');

proc sql;

connect to db2(database=dsn user=username password=&pass);

create table hgh.claims as

select * from connection to db2

(SELECT

a.clm_aud_nbr,

mbr_sys_id,

fst_srvc_dt,

sbscr_nbr,

policy_nbr,

mbr_lst_nm,

mbr_fst_nm,

adj_hccc_cd,

prov_tin,

proc_cd,

diag_1_cd,

diag_2_cd,

diag_3_cd,

diag_4_cd,

mpin,

full_nm,

prdct_cd,

company_cd,

grp_cust_nbr,

er_grp_id,

ben_pln_nbr,

cert_of_cov_fl_yr,

er_grp_id,

grp_cust_nbr,

ben_pln_nbr,

ben_strct_1_cd,

ben_strct_2_cd,

ben_cov_typ_cd,

mbr_cov_mo_row_eff_dt,

mbr_cov_mo_row_end_dt,

finc_arng_cd,

finc_arng_desc,

ama_pl_of_srvc_cd,

ama_pl_of_srvc_desc,

orig_srvc_cd,

member_mkt,

provider_mkt,

bil_typ_cd,

alloc_rvnu_cd,

fm_typ_cd,

fm_typ_desc,

sum(allw_amt) as alloc_allw_amt,

sum(elig_amt) as alloc_elig_amt,

sum(net_pd_amt) as alloc_net_pd_amt,

sum(sbmt_chrg_amt) as alloc_sbmt_chrg_amt

FROM

(

SELECT

DISTINCT

unet.clm_aud_nbr

FROM

galaxy.unet_claim_statistical_service unet

INNER JOIN

galaxy.unet_claim_header head

unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id AND

unet.mbr_sys_id = head.mbr_sys_id

INNER JOIN

galaxy.company_code co

ON head.co_cd = co.co_cd

INNER JOIN

galaxy.product prod

ON head.prdct_cd = prod.prdct_cd

INNER JOIN

galaxy.business_line_code blc

ON prod.bus_line_cd = blc.bus_line_cd

INNER JOIN

galaxy.member_coverage_month mem

unet.mbr_sys_id = mem.mbr_sys_id AND

unet.fst_srvc_dt BETWEEN mem.mbr_cov_mo_row_eff_dt AND mem.mbr_cov_mo_row_end_dt

WHERE

co.finc_arng_cd IN ('F', 'A') AND

mem.cob_ind = ('N') AND

blc.bus_line_desc = 'COMMERCIAL' AND

unet.fst_srvc_dt BETWEEN &startdt and &enddt AND

unet.chrg_sts_cd IN ('P') AND

unet.proc_cd IN &hgh and

unet.allw_amt >0

order by 1

)

INNER JOIN

(SELECT

unet.clm_aud_nbr,

unet.mbr_sys_id,

unet.sbscr_nbr,

head.co_cd as company_cd,

head.mbr_lst_nm,

head.mbr_fst_nm,

mem.grp_cust_nbr,

head.ben_strct_1_cd,

head.ben_strct_2_cd,

head.ben_cov_typ_cd,

unet.cust_seg_nbr AS policy_nbr,

unet.fst_srvc_dt,

unet.adj_hccc_cd,

unet.prov_tin,

unet.proc_cd,

unet.diag_1_cd,

unet.diag_2_cd,

unet.diag_3_cd,

unet.diag_4_cd,

prov.mpin,

prov.full_nm,

co.finc_arng_cd,

co.finc_arng_desc,

head.prdct_cd,

plc.ama_pl_of_srvc_cd,

plc.ama_pl_of_srvc_desc,

market.maj_mkt_nm AS member_mkt,

market2.maj_mkt_nm AS provider_mkt,

unet.orig_srvc_cd,

bill.bil_typ_cd,

mem.mbr_cov_mo_row_eff_dt,

mem.mbr_cov_mo_row_end_dt,

unet.rvnu_cd as alloc_rvnu_cd,

mem2.er_grp_id,

mem3.ben_pln_nbr,

cert.cert_of_cov_fl_yr,

head.fm_typ_cd,

frm.fm_typ_desc,

allw_amt,

elig_amt,

net_pd_amt,

sbmt_chrg_amt

FROM

galaxy.unet_claim_statistical_service unet

INNER JOIN

galaxy.unet_claim_header head

unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id AND

unet.mbr_sys_id = head.mbr_sys_id

INNER JOIN

galaxy.provider prov

unet.srvc_prov_row_eff_dt = prov.prov_row_eff_dt AND

unet.srvc_prov_sys_id = prov.prov_sys_id

INNER JOIN

galaxy.company_code co

ON head.co_cd = co.co_cd

inner join galaxy.product prod

ON head.prdct_cd = prod.prdct_cd

INNER JOIN

galaxy.place_of_service_code plc

ON unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id

INNER JOIN

galaxy.unet_market market

head.geo_area_nbr = market.mkt_nbr AND

head.geo_area_nbr_mkt_row_eff_dt = market.mkt_row_eff_dt

inner join galaxy.UNET_FACILITY_UB92 ub92

on head.mbr_sys_id=ub92.mbr_sys_id

and head.UNET_CLM_HEAD_SYS_ID =ub92.UNET_CLM_HEAD_SYS_ID

INNER JOIN

galaxy.cosmos_bill_type_code bill

ON ub92.bil_typ_cd = bill.bil_typ_cd

INNER JOIN

galaxy.unet_market market2

unet.prov_contr_mkt_nbr = market2.mkt_nbr AND

unet.prov_contr_mkt_row_eff_dt = market2.mkt_row_eff_dt

inner join galaxy.member_coverage_month mem

on unet.mbr_sys_id = mem.mbr_sys_id and

unet.fst_srvc_dt between mem.mbr_cov_mo_row_eff_dt and mem.mbr_cov_mo_row_end_dt

inner join galaxy.member mem2

on head.mbr_sys_id = mem2.mbr_sys_id

inner join galaxy.member_coverage mem3

on mem2.mbr_sys_id = mem3.mbr_sys_id

/*inner join galaxy.customer_segment cust

on unet.cust_seg_sys_id = cust.cust_seg_sys_id

and unet.cust_seg_row_eff_dt = cust.cust_seg_row_eff_dt

inner join galaxy.parent_customer parent

on cust.par_cust_nbr = parent.par_cust_nbr

and cust.src_sys_cd = parent.src_sys_cd

INNER JOIN galaxy.contract_plan_coverage cert

on parent.par_cust_sys_id = cert.par_cust_sys_id*/ /*first poss if using cust to parent*/

inner join galaxy.contract_plan_coverage cert

on prod.prdct_cd = cert.prdct_cd

and prod.src_sys_cd = cert.src_sys_cd

inner join galaxy.form_type_code frm

on head.fm_typ_cd = frm.fm_typ_cd

) a

ON a.clm_aud_nbr = b.clm_aud_nbr

group by

a.clm_aud_nbr,

mbr_sys_id,

sbscr_nbr,

policy_nbr,

mbr_lst_nm,

mbr_fst_nm,

fst_srvc_dt,

adj_hccc_cd,

prov_tin,

proc_cd,

diag_1_cd,

diag_2_cd,

diag_3_cd,

diag_4_cd,

mpin,

full_nm,

prdct_cd,

company_cd,

ben_strct_1_cd,

ben_strct_2_cd,

ben_cov_typ_cd,

mbr_cov_mo_row_eff_dt,

mbr_cov_mo_row_end_dt,

finc_arng_cd,

finc_arng_desc,

ama_pl_of_srvc_cd,

ama_pl_of_srvc_desc,

orig_srvc_cd,

member_mkt,

provider_mkt,

bil_typ_cd,

alloc_rvnu_cd,

er_grp_id,

grp_cust_nbr,

ben_pln_nbr,

cert_of_cov_fl_yr,

fm_typ_cd,

fm_typ_desc)

order by clm_aud_nbr;

disconnect from db2;

quit;

topic SAS File System Full Error in SAS Procedures

SAS File System Full Error

Re: SAS File System Full Error

Re: SAS File System Full Error

Re: SAS File System Full Error

Re: SAS File System Full Error

Re: SAS File System Full Error

Re: SAS File System Full Error

Re: SAS File System Full Error