db2 sql scripts and newlines

Question

Just getting started with db2. Quick question about giving it SQL commands from a file.

If I run:

db2 -f mySQLcommands

and all my commands are one line sql commands they execute with no problems. if I put newlines in the middle of my sql statements then db2 comes back with "expected such and such a character after" Here is an example with new lines:

CREATE TABLE Example (
              id int)

If there was no newline it would run. I tested quickly in nano, so that means it's using the \n character probably.

Answer 1

Not a direct answer to your question, but an alternative is to use something like PyDB2 and let Python manage the interface with DB2.

Something like this should do:

import pydb2

mysqlstmt = open('create_table_example.sql', 'r').read() # multi-line SQL is ok

conn = pydb2.connect(database="db", host="hst", user="usr", password="pswd")
c = conn.cursor()

c.execute(mysqlstmt)

conn.commit()
c.close()
conn.close()

mysqlstmt.close()

Answer 2

Use the -t command line switch to enable multi-line statements that are by default terminated with a semi-colon ;

e.g.

db2 -t -f mySQLcommands

CREATE TABLE Example (
              id int);