SQL and DB2 create command

Question

I am going through a tutorial on how to use DB2 which is in a linux environment.

I am supposed to connect to a database, create a table, insert some data under db2 shell:

db2 connect to c3421m

db2

db2 => update command options using z ON Assignment_0.txt

db2 => update command options using v ON

db2 => CREATE TABLE BAND_2015 // gives error I got stuck here

// here is where I get stuck i am supposed to create a table and execute the follwing command under DB2 shell: CREATE TABLE BAND_2015

Code given:

create table band_2015 ( \

band_no integer not null primary key, \
band_name varchar(25) not null, \
band_home varchar(25) not null, \
band_type varchar(10) check (band_type in (‘concert’,’rock’,’jazz’,’military’)), \
b_start_date date not null, \
band_contact varchar(10) not null )

So how do I create this table? I was told to copy it to a text editor(do i save it as band_2015.sql ?). I am completely new to this but i have a lot of experience in other programming languages...

What happens if you just try a one-row command, like "create table t1 (c1 integer not null primary key)"? — jarlh
– jarlh, Commented Jan 7, 2015 at 14:52
db2 => create table t1 gives this error -> "DB21034E the command was processed as an SQL statement because it was not a valid command line processor command. During SQL processing it returned: SQL0104N An unexpected token "table" was found following "create". Expected tokens may include: "TABLESPACE". SQLSTATE=42601" — geforce
– geforce, Commented Jan 7, 2015 at 14:56
You don't have to start the clp and do stuff from inside it. An alternative is to execute sql commands directly from the sh. I.e. db2 "create table band_2015 ( \ band_no integer not null primary key, \ band_name varchar(25) not null, \ band_home varchar(25) not null, \ band_type varchar(10) check (band_type in ('concert','rock','jazz','military')), \ b_start_date date not null, \ band_contact varchar(10) not null )" You can also put your sql statement in a file and run them as db2 -tf myfile.sql — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Jan 7, 2015 at 19:24

AngocA · Accepted Answer · 2015-01-07 15:07:38Z

2

The problem is the terminating character. By default in the carriage return (enter). However, for your tutorial, you should type multi-line commands. For this case, you change the terminator character by defining another

For semi-colon

db2 -t

select *
from table;

For at sign or any other character.

db2 -td@

select *
from table @

For no character:

db2

select * from table

answered Jan 7, 2015 at 15:07

AngocA

7,7046 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mustaccio · Accepted Answer · 2015-01-07 14:53:03Z

1

In the DB2 command-line processor by default commands and statements cannot span multiple lines, so it treats CREATE TABLE BAND_2015 as a complete statement, which is of course not the case. In the code given to you those backslashes appear for a reason -- they indicate to the CLP that the statement continues on the next line.

Alternatively, you can start the CLP with the command line option -t, which will designate the semicolon, instead of the new line, as the statement terminator. You can then type the statement as you did, without the backslashes, and terminate it with the ";".

answered Jan 7, 2015 at 14:53

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

2 Comments

geforce Over a year ago

Ok so basically i just copy each line like db2 => create table band_2015 ( \ and so on in the terminal?

geforce Over a year ago

Actually nevermind, I found it out now thanks a lot for mentioning the backslash and terminate character

Collectives™ on Stack Overflow

SQL and DB2 create command

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related