CODEWITHSUNDEEP
stored-proceduresdb2
Yebach
Yebach

Reputation: 1691

db2 from procedure run file

using IBM DB2 i would like to create a procedure that in case a table does not exist goes to file and start with create and insert statements written in that file. I cannot put all statements inside a procedure because there is just too many of them

my semi-pseudo code so far is 

 CREATE PROCEDURE KLEMENTEST.create_table 
()
LANGUAGE SQL
begin atomic

 if (not exists(select 'A' from syscat.tables where tabschema = 'TESTSCHEMA' and tabname='TESTTABLE')) then

 --- run queries from file located in "c:\path\to\file.txt"

 end if
end ;

any suggestions ??

thank you

Upvotes: 0

Views: 1519

Answers (1)

AngocA
AngocA

Reputation: 7693

There are several options:

1) Create an external stored procedure in Java or C that will execute the command that you want. For example

db2 -tf file.sql

Remember to execute DB2 with a profile (. db2profile). You will call this stored procedure after having test that the table does not exist.

2) Create the stored procedure, and read the content of the file that contains the ddl, then with the content, you create a dynamic SQL. You can read files via UTL_FILE module. However, this module is not available in Express-C edition. http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0053689.html

The general problem of this approach is the location of the file to "execute" in the file system, and also the stored procedure becomes platform dependent.

Upvotes: 1

Related Questions