Generate SQL from SPL

Hi, I am trying to develop one function to generate one script from database contents, to be more specific I am trying to generate one load to my tables from the following SQL: output to "load.sql" without headings select "load from " || tabname || " insert into " || tabname || ";" from systables where tabid > 99 and tabtype = "T" As I don't know too much about this can someone give me some help please? And also tell where I can found some practical documentation about this? Thanks, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

Hi Sergio, It seems that using UNLOAD or OUTPUT in the routine causes an error. So I searched on Google and used the external table as below. https://stackoverflow.com/questions/31992388/using-an-unload-statement-in-an-informix-stored-procedure Perhaps soon someone else will come up with another great solution. /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]echo "execute procedure myproc()" | dbaccess> Database selected. Routine executed. 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 sysblderrorlog insert into sysblderrorlog; load from sysbldobjects insert into sysbldobjects; load from sysbldobjdepends insert into sysbldobjdepends; load from sysbldobjkinds insert into sysbldobjkinds; load from sysbldregistered insert into sysbldregistered; load from sysbldirequired insert into sysbldirequired; load from sysbldiprovided insert into sysbldiprovided; 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;------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Thanks for reply, That can help me, I also have seen there is one command FOREACH that maybe can serve for this also. There are lots of features that we can use regarding optimization, but I feel a great lack of practical information with examples on this matter... SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Just for clarification: apparent SQL syntax elements LOAD, UNLOAD, OUTPUT TO are understood by dbaccess only and aren't really sent to the server or part of the SQL the server implements. That's why you cannot use them within SPL either. ------------------------------ Andreas Legner ------------------------------

Thanks for reply, Yes I have already seen that, my problem is that I can't find some examples and documentation isn't very enlightening. Also I have tryed to use debug option but I am getting errors and could't find explanations... Now my code is this and the result file is always empty: FOREACH SELECT TRIM(tabname) INTO tabela FROM systables WHERE tabid > 99 AND tabtype = 'T' LET comando = "echo "||TRIM(string)||" "||tabela||" load from "|| tabela||"; >> "||airc_dir||"/load.sql"; SYSTEM comando; END FOREACH and if I add this optin before, I get 1213 error A character to numeric conversion process failed: SET DEBUG FILE TO "||TRIM(airc_dir)||"/"errlogspl"; TRACE ON; SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Example of setting a dynamic debug file: SET DEBUG FILE TO trim(MYDEBUGVAR) || '.out'; LET comando = "echo "||TRIM( string )||" "||tabela||" load from "|| tabela||"; >> "||airc_dir||"/load.sql"; Is string a variable you are setting above? String is a poor choice for variable name as I think it is a reserved word. https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1818.htm When you are concatenating strings you always have to be careful that none of the values are NULL. If you concatenate a NULL to any of it, the result is NULL. You can get around this by doing like the below leveraging the NVL function. TRIM(NVL(MYSTRING, ' ')) This will replace a NULL value with a single space which is between the single quotes. Let me know if this helps. ------------------------------ David Link ------------------------------

I've run a routine based on your code. I hope the results below will help. /work2/INFORMIX/1210FC13/skjeong]cat myproc1.sql DROP PROCEDURE MYPROC1(); CREATE PROCEDURE MYPROC1()DEFINE tabela VARCHAR(20); DEFINE comando VARCHAR(100); DEFINE string VARCHAR(100); DEFINE airc_dir VARCHAR(100); LET string="string_test : "; LET airc_dir="/tmp/"; SET DEBUG FILE TO airc_dir||"errlogspl"; TRACE ON; FOREACH SELECT TRIM(tabname) INTO tabela FROM systables WHERE tabid > 99 AND tabtype = 'T' LET comando = "echo "||string||"load from "||tabela||" insert into "||tabela|| "\\; >> "||airc_dir||"load.sql"; SYSTEM comando; END FOREACH END PROCEDURE; /work2/INFORMIX/1210FC13/skjeong]dbaccess stores_demo myproc1.sql Database selected. Routine dropped. Routine created. Database closed. /work2/INFORMIX/1210FC13/skjeong]echo "execute procedure myproc1()" | dbaccess stores_demo Database selected. Routine executed. Database closed. /work2/INFORMIX/1210FC13/skjeong]head -20 /tmp/load.sql string_test : load from customer insert into customer; string_test : load from orders insert into orders; string_test : load from manufact insert into manufact; string_test : load from stock insert into stock; string_test : load from items insert into items; string_test : load from state insert into state; string_test : load from call_type insert into call_type; string_test : load from cust_calls insert into cust_calls; string_test : load from catalog insert into catalog; string_test : load from sysblderrorlog insert into sysblderrorlog; string_test : load from sysbldobjects insert into sysbldobjects; string_test : load from sysbldobjdepends insert into sysbldobjdepends; string_test : load from sysbldobjkinds insert into sysbldobjkinds; string_test : load from sysbldregistered insert into sysbldregistered; string_test : load from sysbldirequired insert into sysbldirequired; string_test : load from sysbldiprovided insert into sysbldiprovided; string_test : load from calendarpatterns insert into calendarpatterns; string_test : load from calendartable insert into calendartable; string_test : load from tsinstancetable insert into tsinstancetable; string_test : load from tscontainertable insert into tscontainertable; /work2/INFORMIX/1210FC13/skjeong]head -20 /tmp/errlogspl trace on start select cursor. select TRIM ( BOTH ' ' FROM tabname) from systables where (and (> tabid, 99), (= tabtype, "T")) select cursor iteration. select cursor returns customer expression:(|| (|| (|| (|| (|| (|| (|| (|| "echo ", string), "load from "), tabela), " insert into "), tabela), "\\; >> "), airc_dir), "load.sql") evaluates to echo string_test : load from customer insert into customer\\; >> /tmp/load.sql let comando = echo string_test : load from customer insert into customer\\; >> /tmp/load.sql expression:comando evaluates to echo string_test : load from customer insert into customer\\; >> /tmp/load.sql system : echo string_test : load from customer insert into customer\\; >> /tmp/load.sql select cursor iteration. select cursor returns orders expression:(|| (|| (|| (|| (|| (|| (|| (|| "echo ", string), "load from "), tabela), " insert into "), tabela), "\\; >> "), airc_dir), "load.sql") evaluates to echo string_test : load from orders insert into orders\\; >> /tmp/load.sql let comando = echo string_test : load from orders insert into orders\\; >> /tmp/load.sql ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Thanks for reply and help, Well I forgot to look for the reserved words, my mistake! I have tried with your suggestions and finally I got logs to look but result file still empty. I don't have worried about the empty field as all the values are filled, I just pretend to create one load file for all my tables on database. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Thanks for your reply and help, Concerning your experience and my empty result file load.sql, I have tested with your code and result keeps empty and on log file is like yours... I am using 12.10.FC14 version maybe better open one support case? SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

That's weird. In Informix 12.10.FC14, it works well under locale en_us.819, en_us.utf8. If the routine runs successfully on a different version of Informix on the same host, I think you need to open the case. /work2/INFORMIX/1210FC14/skjeong]oninit -V IBM Informix Dynamic Server Version 12.10.FC14 Software Serial Number AAA#B000000 Fri Feb 14 16:14:43 CST 2020 /work2/INFORMIX/1210FC14/skjeong]cat myproc1.sql DROP PROCEDURE MYPROC1(); CREATE PROCEDURE MYPROC1()DEFINE tabela VARCHAR(20); DEFINE comando VARCHAR(100); DEFINE string VARCHAR(100); DEFINE airc_dir VARCHAR(100); LET string="string_test : "; LET airc_dir="/tmp/"; SET DEBUG FILE TO airc_dir||"errlogspl"; TRACE ON; FOREACH SELECT TRIM(tabname) INTO tabela FROM systables WHERE tabid > 99 AND tabtype = 'T' LET comando = "echo "||string||"load from "||tabela||" insert into "||tabela|| "\\; >> "||airc_dir||"load.sql"; SYSTEM comando; END FOREACH END PROCEDURE; /work2/INFORMIX/1210FC14/skjeong]export CLIENT_LOCALE=en_us.819 /work2/INFORMIX/1210FC14/skjeong]export DB_LOCALE=en_us.819 /work2/INFORMIX/1210FC14/skjeong]rm /tmp/load.sql /work2/INFORMIX/1210FC14/skjeong]echo "execute procedure myproc1()" | dbaccess stores_demo Database selected. Routine executed. Database closed. /work2/INFORMIX/1210FC14/skjeong]cat /tmp/load.sql string_test : load from customer insert into customer; string_test : load from orders insert into orders; string_test : load from manufact insert into manufact; string_test : load from stock insert into stock; string_test : load from items insert into items; string_test : load from state insert into state; string_test : load from call_type insert into call_type; string_test : load from cust_calls insert into cust_calls; string_test : load from catalog insert into catalog; string_test : load from sysblderrorlog insert into sysblderrorlog; string_test : load from sysbldobjects insert into sysbldobjects; string_test : load from sysbldobjdepends insert into sysbldobjdepends; string_test : load from sysbldobjkinds insert into sysbldobjkinds; string_test : load from sysbldregistered insert into sysbldregistered; string_test : load from sysbldirequired insert into sysbldirequired; string_test : load from sysbldiprovided insert into sysbldiprovided; string_test : load from calendarpatterns insert into calendarpatterns; string_test : load from calendartable insert into calendartable; string_test : load from tsinstancetable insert into tsinstancetable; string_test : load from tscontainertable insert into tscontainertable; string_test : load from ts_data insert into ts_data; string_test : load from customer_ts_data insert into customer_ts_data; string_test : load from ts_data_v insert into ts_data_v; string_test : load from spatial_references insert into spatial_references; string_test : load from geometry_columns insert into geometry_columns; string_test : load from st_units_of_measure insert into st_units_of_measure; string_test : load from se_metadatatable insert into se_metadatatable; string_test : load from se_views insert into se_views; string_test : load from ts_data_location insert into ts_data_location; string_test : load from tab insert into tab; string_test : load from warehouses insert into warehouses; string_test : load from classes insert into classes; string_test : load from employee insert into employee; ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Thanks again for your care and help, I think I have found my problem, I was using one sub-directory under informix home to do it. Even having full permissions it returns 668:1 Not owner error! I have tried with /tmp and works fine. (I have to find another structure to do the job) Best regards, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Sergio: You cannot use UNLOAD or OUTPUT in any stored procedure because these are verbs built into dbaccess not the engine. Try using a shell script using dbaccess or my dbscript utility instead. Art

Thanks for reply, My problem is with echo over system call, I use the same directory that are used on other parts of the script to write. But it is strange it doesn't give any error but the file don't get filled, even if I previously create it empty. More strange is that log file is on the same place and get filled... SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Try using /bin/echo since there is an "echo" command built-into ksh and bash and the built-in's don't always work with SYSTEM. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

My point was to not use SPL at all, but use dbscript to do what you want. Two steps: write a shell script (call it load_table.ksh) to issue a load command that takes the database name and table name as arguments" #! /usr/bin/ksh dbs=$1 tbl=$2 fil=/some/path/${tbl}.unl dbaccess $dbs - <<EOF load from $fil insert into $tbl;EOF run dbscript to execute the script for every table: dbscript -d mydatabase -c "load_table.ksh mydatabase %s" dbscript will, by default, execute the command passed to the -c option for every normal user table (so same filter you are using) replacing %s with the actual table names. Jobs like this one is why I wrote dbscript! Art ------------------------------ Art Kagel ------------------------------

Hi Sergio, Isn't the SQL statement executed by the system command truncated? If the length of the variable set as VARCHAR (100) below is exceeded, the 668 error occurs. /work2/INFORMIX/1210FC14/skjeong]cat myproc1.sql DROP PROCEDURE MYPROC1(); CREATE PROCEDURE MYPROC1()DEFINE tabela VARCHAR(20); DEFINE comando VARCHAR(100); DEFINE string VARCHAR(100); DEFINE airc_dir VARCHAR(100); LET string="string_test : "; --LET airc_dir="/tmp/"; LET airc_dir="/work2/INFORMIX/1210FC14/skjeong/"; SET DEBUG FILE TO airc_dir||"errlogspl"; TRACE ON; FOREACH SELECT TRIM(tabname) INTO tabela FROM systables WHERE tabid > 99 AND tabtype = 'T' LET comando = "echo "||string||"load from "||tabela||" insert into "||tabela|| "\\; >> "||airc_dir||"load.sql"; SYSTEM comando; END FOREACH END PROCEDURE; /work2/INFORMIX/1210FC14/skjeong]echo "execute procedure myproc1()" | dbaccess stores_demo Database selected. 668: The system command cannot be executed or it exited with a non-zero status.1: Not owner Error in line 1Near character position 27 Database closed. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Please use a shell script - it would fix the issue. Regards, ------------------------------ Anna Le ------------------------------

Thanks for help, Yes I am going to use a script to do the job, I just was trying to avoid use shell so I can do all tasks from informix. As I have different environments windows and linux. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Thanks for reply, As I said to Art I was trying to do all job from informix, so I can use the same routine over linux and windows. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Hi SanGyu, I have comando defined with varchar(250)... I think that can be related with permissions also! SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------