By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Reading sql script - newbe question

P: n/a
Hello

DB2/NT 8.1.3

Sorry for stupid questions. I am newbe in DB2.
1. How can I read *.sql script (with table and function definitions) into a
database? Tool, command...
2. In Project Center I created funcion

CREATE FUNCTION DB2ADMIN.xxx( )
RETURNS INTEGER
F1: BEGIN ATOMIC
RETURN 41;
END

When I put this body into script field in Command Center and click Execute I
am getting a message:
SQL0104N Founded unexpected element "END-OF-STATEMENT"
which appear after "END" text . Acceptable elements are: "JOIN
<joined_table>
(sorry for translate)
What is wrong? This is wrongly tool? I cannot create functions, triggers in
this tools?

Thanks in advance
Yaro
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a

"Yaro" <ya*************@op.pl> wrote in message
news:cf**********@atlantis.news.tpi.pl...
Hello

DB2/NT 8.1.3

Sorry for stupid questions. I am newbe in DB2.
1. How can I read *.sql script (with table and function definitions) into a database? Tool, command...
This is normally done by executing the script from the DB2 Command Window.

For example, I have a short script called temp.sql that contains this
script:

---
connect to contacts;

drop table temp;
create table temp
(id integer not null,
name char(10) not null,
primary key (id));

insert into temp values (1, 'Adams');
insert into temp values (2, 'Bailey');

select * from temp;

select id, digits(id) as digits, rtrim(char(id)) || ' ' || name as strings
from temp;
---

To execute it, I launch the DB2 Command Window from my Windows XP Program
menu: start/All Programs/IBM DB2/Command Window.

Then, I change directories until I am in the directory containing my script:
C:\PROGRAM~1\SQLLIB\BIN> cd \
C:\> cd Program Files\SQLLIB\Scripts

Then, I execute the script:
C:\Program Files\SQLLIB\Scripts> db2 -tvf temp.sql

When I execute the script this way, it displays the statements and the
output from the statements in the Command Window below the prompt. However,
if the script is long, the output will scroll off the screen very quickly
and will also 'wrap' so that you can't read it very well. Therefore, I
usually add the -z option to the command like this:

C:\Program Files\SQLLIB\Scripts> db2 -tvf temp.sql -z temp.out

This does the same thing as the previous version of the command but also
writes both the commands and the output from the commands to a file, named
temp.out in this case. You can then view the temp.out file to see anything
that scrolled by too quickly on the command window. The output in temp.out
won't wrap so you can scroll to the right to see the ends of longer lines.

2. In Project Center I created funcion

CREATE FUNCTION DB2ADMIN.xxx( )
RETURNS INTEGER
F1: BEGIN ATOMIC
RETURN 41;
END

When I put this body into script field in Command Center and click Execute I am getting a message:
SQL0104N Founded unexpected element "END-OF-STATEMENT"
which appear after "END" text . Acceptable elements are: "JOIN
<joined_table>
(sorry for translate)
What is wrong? This is wrongly tool? I cannot create functions, triggers in this tools?

I am not familiar with the Project Center but I don't have DB2 8.1.x yet
since I am still running DB2 V7.2. I *think* that you are supposed to create
functions in the Development Center, which is new to DB2 V8. Perhaps they
can be created in the Project Center or the Command Center but I've never
tried that. The documentation for the Project Center should tell you what it
does but it can be difficult to find this kind of information in the
documentation sometimes.

I am not sure if triggers are also supposed to be created in the Development
Center or if they are still supposed to be created in the Command Center or
Project Center. I have created many triggers in the Command Center in V7.2
and earlier versions without difficulty. However, there *is* one thing you
need to do before most triggers will be created successfully: you need to
set your statement delimiter for the Command Center to some value other than
a semicolon (';'). This is because the syntax of the triggered statement
requires that it end with a semicolon. That means that the CREATE TRIGGER
statement must end with some *other* delimiter. To change your delimiter to
some other value in the Command Center, click on Tools/Tool Settings, go to
the General tab, and set the "use statement termination character" value to
something other than a semicolon, such as %.

For example, this is a trigger I created recently:

create trigger sfl_Jun_2004
after insert on db2admin.sfl_attend_2004
referencing new as n
for each row mode db2sql
when (n.jun_2004 = 1)
begin atomic
insert into db2admin.attendance values (n.member, '2004-06-05');
end%

Notice that the triggered statement, the INSERT, ends with a semicolon but
the CREATE TRIGGER statement ends with a percentage sign ('%'). The
percentage sign is the statement termination character that I set in the
Tool Settings of the Command Center.

Rhino
Nov 12 '05 #2

P: n/a
Thank you. Your post was very helpful for me.
Yaro
Nov 12 '05 #3

P: n/a
I have 2 scripts : "script1.sql" and "script2.sql"

Is this possible to read (db2 -tvf ...) third script "script3.sql" which
contain commands (??) to reading "script1" and "script2"?

e.g. script3.sql

........(read).... script1.sql;
........(read).... script2.sql;
........(read)....
........(read)....

Thanks in advance
Yaro
Nov 12 '05 #4

P: n/a
Yaro wrote:
You need to define a delimiter, e.g, '@'
CREATE FUNCTION DB2ADMIN.xxx( )
RETURNS INTEGER
F1: BEGIN ATOMIC
RETURN 41;
END

@

You ca set the delimiter in tools-settings

Cheers
Serge
Nov 12 '05 #5

P: n/a
Thak you

Can you ask me next question?

I have 2 scripts : "script1.sql" and "script2.sql"

Is this possible to read (db2 -tvf ...) third script "script3.sql" which
contain commands (??) to reading "script1" and "script2"?

e.g. script3.sql

........(read).... script1.sql;
........(read).... script2.sql;
........(read)....
........(read)....
Yaro
Nov 12 '05 #6

P: n/a
Yaro wrote:
Thak you

Can you ask me next question?

I have 2 scripts : "script1.sql" and "script2.sql"

Is this possible to read (db2 -tvf ...) third script "script3.sql" which
contain commands (??) to reading "script1" and "script2"?

e.g. script3.sql

.......(read).... script1.sql;
.......(read).... script2.sql;
.......(read)....
.......(read)....


You can add external commands in a SQL script. In "script3.sql", add the
following:

-------------------------
SELECT ...;

! db2 -tvf script1.sql;

INSERT ...;

! db2 -tvf script2.sql;

-------------------------

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #7

P: n/a
> -------------------------
SELECT ...;

! db2 -tvf script1.sql;

INSERT ...;

! db2 -tvf script2.sql;

-------------------------


That works. Thanks a lot.
Yaro
Nov 12 '05 #8

P: n/a
Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<8P******************@news04.bloor.is.net.cab le.rogers.com>...
Yaro wrote:
You need to define a delimiter, e.g, '@'
CREATE FUNCTION DB2ADMIN.xxx( )
RETURNS INTEGER
F1: BEGIN ATOMIC
RETURN 41;
END @

You ca set the delimiter in tools-settings
any idea why CC locks up solid when i try this??
UDB 8.1.6 win2000

thanks,
robert

Cheers
Serge

Nov 12 '05 #9

P: n/a
Beats me. From CC you simply update Tools->Setting->delimiter (tick it
and specify @)

Cheers
Serge
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.