Sas 9.4 TS1M3 - Loading Sas Dataset to MySQL using bulkload is not working

[TanQF]

Dear Sir,

I have a large sas dataset which I want to upload to MySql. I have verified that I can write a small sas dataset to it. However, when I try to write the big dataset to it, it takes forever.

So I try to use bulkload but encountered the following error. I have tried a few ways to use bulkload but no success. I have increased my memsize to max. How much more memory do I need?

Hope you can help or advice me

Tan

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 proc append data=dp base=mysqllib.dp(bulkload=yes);
25 run;

NOTE: Appending WORK.DP to MYSQLLIB.dp.
WARNING: Variable barrid has different lengths on BASE and DATA files (BASE 10 DATA 8).
MYSQL: Can't create bulkload temporary file: bl_dp_5

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 2 observations read from the data set WORK.DP.
NOTE: There were 2 observations read from the data set MYSQLLIB.dp.
26

27 data mysqllib.dp5 (BULKLOAD=yes BL_DEFAULT_DIR="c:\temp\");
28 set dp;
29 run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
MYSQL: Can't create bulkload temporary file: c:/temp//bl_dp5_6

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 2 observations read from the data set WORK.DP.
WARNING: The data set MYSQLLIB.dp5 may be incomplete. When this step was stopped there were 1 observations and 4 variables.

30
31 data mysqllib.dp6 (BULKLOAD=yes);
32 set dp;
33 run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
MYSQL: Can't create bulkload temporary file: bl_dp6_7
2 The SAS System 18:29 Friday, June 3, 2016

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 2 observations read from the data set WORK.DP.
WARNING: The data set MYSQLLIB.dp6 may be incomplete. When this step was stopped there were 1 observations and 4 variables.

34
35 proc options group=memory; run;

SAS (r) Proprietary Software Release 9.4 TS1M3

Group=MEMORY
SORTSIZE=1073741824
Specifies the amount of memory that is available to the SORT procedure.
SUMSIZE=0 Specifies a limit on the amount of memory that is available for data summarization procedures when class
variables are active.
MAXMEMQUERY=0 Specifies the maximum amount of memory that is allocated for procedures.
MEMBLKSZ=16777216 Specifies the memory block size for Windows memory-based libraries.
MEMMAXSZ=2147483648
Specifies the maximum amount of memory to allocate for using memory-based libraries.
LOADMEMSIZE=0 Specifies a suggested amount of memory that is needed for executable programs loaded by SAS.
MEMSIZE=9854127360
Specifies the limit on the amount of virtual memory that can be used during a SAS session.
REALMEMSIZE=0 Specifies the amount of real memory SAS can expect to allocate.
NOTE: PROCEDURE OPTIONS used (Total process time):

[SASKiwi]

I suggest you try without the BULKLOAD option to start with and experiment with the INSERTBUFF and DBCOMMIT options:

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p06u7eb5ienq5in1wq...

Also try loading to a new temporary first as this should be the least problematic.

[TanQF]

Hi Sir,

1. Could you teach me how to load to a new temporary first?

I have used the following:

proc append data=dp base=mysqllib.dp (dbcommit=1000 insertbuff=100000);
run;

It is very fast. 

proc append data=dp base=mysqllib.dp(bulkload=yes BL_METHOD=CLILOAD);
run;

--> this is very slow but bulkload is working.

2. How do I check the data in mysql is same as Sas dataset?

Thank you

[LinusH]

For the bulk load part I think you need to discuss your error/warning messages and performance with your MySQL DBA.

2. For quality assurance? Trust the logs written during load. Try to match samples. Full scale comparisons on large data sets are expensive and not feasible over time if you are building history records.

[TanQF]

Dear Linus,

I am building my own database. Hence there is no IT department to discuss MySQL with.

I am scratching my head with the previous comments about using temporary files. Certainly his suggestions on DBcommit and buffer helps overcome bulkload. 

Thanks for the suggestions on using samples to check. 

[ballardw]

First thing I will preface my comment with: I am not a MySql admin or use the program so specific details (options and parameters) I do not have on hand.

A friend of my was recently working with SAS connected to MySql and they had some very poor performance issues with time of program running. After an amount of sleuthing they found out that there were apparently 3 data engines involved and the "default" installation picked one that optimizes for some other type of operation. When they switched to use a different MySql engine the process times went from 10 hours to 30 minutes for some SAS related operations.

I can't say which to look for or where to find it, but it may be default MySql setting that is making things run slow.

[LinusH]

If you don't have a MySQL DBA, why use it? Any specific requirement, from whom ?

Keep the data in SAS if you want performance...

[TanQF]

Hi Linus

1. Sas dataset has a limitation. If a dataset is more than 5G, it becomes very slow in reading and writing to it. 

2. I would be using other programming languages to process the data because I am looking at their toolboxes. Hence, ideally, I would need a database to store the data and retrieve it from sas dataset.

Hi Ballard

I will take note of engines when it becomes too slow.

Just that I read bulkload is very good but I cant use it. Its disappointing

Thanks for your great advices