프로시저로 UNLOAD 실행하기

인포믹스 프로시저에서는 UNLOAD나 OUTPUT 문장을 실행할 수 없습니다. 이를 대체하려면 External Table 기능을 사용해 데이터를 파일로 내려받는 방법이 있습니다.

참고로 External Table 기능은 인포믹스 11.5 버전부터 사용할 수 있습니다.

/work2/INFORMIX/1210FC13/skjeong]cat myproc.sql
DROP PROCEDURE MYPROC();
CREATE PROCEDURE MYPROC()

CREATE EXTERNAL TABLE load_tmp
(
   load_stmt char(1024)
)
USING (
DATAFILES    ("DISK:/tmp/load.sql"),
DELIMITER ";"
);

INSERT INTO load_tmp SELECT 'load from ' || trim(tabname) || ' insert into ' || trim(tabname) FROM systables WHERE tabid > 99 AND tabtype = 'T';
DROP TABLE load_tmp;

END PROCEDURE;
/work2/INFORMIX/1210FC13/skjeong]dbaccess stores_demo myproc.sql

Database selected.


Routine dropped.


Routine created.


Database closed.

/work2/INFORMIX/1210FC13/skjeong]cat /tmp/load.sql
load from customer insert into customer;
load from orders insert into orders;
load from manufact insert into manufact;
load from stock insert into stock;
load from items insert into items;
load from state insert into state;
load from call_type insert into call_type;
load from cust_calls insert into cust_calls;
load from catalog insert into catalog;
...
load from calendarpatterns insert into calendarpatterns;
load from calendartable insert into calendartable;
load from tsinstancetable insert into tsinstancetable;
load from tscontainertable insert into tscontainertable;
load from tscontainerusageactivewindowvti insert into tscontainerusageactivewindowvti;
load from tscontainerusagedormantwindowvti insert into tscontainerusagedormantwindowvti;
load from tscontainerwindowtable insert into tscontainerwindowtable;
load from ts_data insert into ts_data;
load from customer_ts_data insert into customer_ts_data;
load from ts_data_v insert into ts_data_v;
load from ts_data_multiplier_v insert into ts_data_multiplier_v;
load from spatial_references insert into spatial_references;
load from geometry_columns insert into geometry_columns;
load from st_units_of_measure insert into st_units_of_measure;
load from se_metadatatable insert into se_metadatatable;
load from se_views insert into se_views;
load from ts_data_location insert into ts_data_location;
load from tab insert into tab;
load from warehouses insert into warehouses;
load from classes insert into classes;
load from dbms_alert_events insert into dbms_alert_events;
load from dbms_alert_registered insert into dbms_alert_registered;
load from dbms_alert_signaled insert into dbms_alert_signaled;
load from employee insert into employee;
load from employee3 insert into employee3;
load from tab0 insert into tab0;
load from l_nextval insert into l_nextval;
load from stock3 insert into stock3;
load from test_bk insert into test_bk;
load from test4 insert into test4;
load from test1 insert into test1;
load from stock2_trans insert into stock2_trans;
load from catcopy insert into catcopy;
load from test insert into test;
load from test2 insert into test2;
load from test3 insert into test3;
load from stock2 insert into stock2;

 

프로시저 작성은 아래 문서를 참고했습니다.

https://stackoverflow.com/questions/31992388/using-an-unload-statement-in-an-informix-stored-procedure