Skip to Content
0
Aug 01, 2016 at 12:59 AM

What's the difference between in-doc sql and sql file?

37 Views

Dear All,

I'm facing an extracting issue now... While I'm using in-doc sql to extract a table, it fails to create extract file, but if I use a sql file instead, it will succeed.. Here's my sample file --

asiq160@bigDataIQ:/iq_data/bcp> cat ex.sh

#!/bin/bash

echo `date +%F' '%T`:started

dbisql -c "uid=dba;pwd=sql" -host 10.128.244.161 -port 2641 -nogui << EOF

set temporary option temp_extract_name1='export.dat';

set temporary option temp_extract_directory='/iq_data/bcp';

set temporary option isql_show_multiple_result_sets='on' ;

SET temporary OPTION ISQL_PRINT_RESULT_SET = 'ALL';

select * from STOCK;

set temporary option temp_extract_name1='';

EOF

echo `date +%F' '%T`:ended

asiq160@bigDataIQ:/iq_data/bcp> ./ex.sh

2016-07-26 03:00:12:started

(DBA)> Execution time: 0.003 seconds

(DBA)> 2016-07-26 03:00:13:ended

asiq160@bigDataIQ:/iq_data/bcp> ls

ex.sh

=======================================================================================

then if I wrote the sql to a separated file --

asiq160@bigDataIQ:/iq_data/bcp> cat ex.sql

set temporary option temp_extract_name1='export.dat';

set temporary option temp_extract_directory='/iq_data/bcp';

set temporary option isql_show_multiple_result_sets='on' ;

SET temporary OPTION ISQL_PRINT_RESULT_SET = 'ALL';

select * from STOCK;

set temporary option temp_extract_name1='';

and using dbisql to call it ---

asiq160@bigDataIQ:/iq_data/bcp> dbisql -c "uid=dba;pwd=sql" -host 10.128.244.161 -port 2641 -nogui ex.sql

Then we can see the '/iq_data/bcp/export.dat' was generated....

=======================================================================================

Would anyone please tell what's the difference between them?

Thanks in advance for any ideas.

Regards

Eisen