1

I'm using db2 export to save data to a CSV file.

Ultimately I need a built a script that will take in a SQL, run it and export it out. I've got almost all of it in place, but certain queries cause errors.

Two cases cause errors at the moment:

1 - Using a wildcard. Shell expansion kills this.

Enclosing the whole SQL in ' seems to fix that, but what is the best practice? There seem to be some edge cases I haven't fixed.

2 - I can't use a string in a where clause:

bash-3.00$ db2 export to /tmp/text of DEL 'select * from SCHEMA.TABLE where COL='STRING''
SQL3022N  An SQL error "-206" occurred while processing the SELECT string in
the Action String parameter.

SQL0206N  "STRING" is not valid in the context where it is used.

Even changing to a specific column to select and removing the '' from the whole SQL doesn't change it.

Any ideas?

Thanks,

Alex

Alex
  • 113
  • 1
  • 1
  • 6
  • I could use straight db2 command, not db2 export, but that won't give me csv, I'll have to do something like , || "," || , between each field in the select to fake the CSV. Given that I'd like to use arbitrary SQL statements, I don't want to have to force that customisation. – Alex May 15 '12 at 17:09

2 Answers2

3

@mru is on the right track, but I think a working example will be more helpful:

 db2 "export to /tmp/text of DEL select * from SCHEMA.TABLE where COL='STRING' "

The important factor here is that the SQL select is not quoted, just the contents of a string literal.

Here is another way to escape the characters that don't play well with command-line shells:

db2 export to /tmp/text of DEL select \* from SCHEMA.TABLE where COL=\'STRING\'
Fred Sobotka
  • 5,252
  • 22
  • 32
0

You have to escape the single quotes in your query string as they close the quotation. Have a look at How to escape single-quotes within single-quoted strings? a solution. But in your case i think it should be enough to use double quotes instead of single quotes for your quotation.

Community
  • 1
  • 1
Ulrich Dangel
  • 4,515
  • 3
  • 22
  • 30
  • Beautiful - db2 export to /tmp/text of DEL 'select * from SCHEMA.TABLE where COL = '"'"'STRING'"'"'' works. Well, not that beautiful. So, I guess, I have to a function into my script to replace all instances of a ' with '"'"' – Alex May 15 '12 at 17:25
  • @Alex As mentioned in my last sentence or via the answer from Fred you just replace the single quotes surrounding your statement with `"` like `db2 export to /tmp/text of DEL "select * from SCHEMA.TABLE where COL='STRING'"` – Ulrich Dangel May 15 '12 at 17:30
  • That works - but, I'm having issues sourcing that into a variable to pass into a script. So, if I run the db2 from the command line, I'm fine. However, trying to do something like db2 export to /tmp/text of DEL "$SQL" where $SQL comes from SQL=`cat query.sql` I find it expands the *. If I escape the * with \* in the file, then the query fails, because it doesn't like the \* within the " – Alex May 15 '12 at 17:55