?
Solved

Newbie Stored procedure question

Posted on 2011-10-24
3
Medium Priority
?
389 Views
Last Modified: 2012-05-12
I have never written a stored procedure before, and am having difficulty.   (Actually I did not write this procedure, I am just trying to get it read into DB2)

First I connect to my DB:

[db2inst1@sandbox1 ddl]$ db2 connect to IPC

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = IPC

Open in new window


Then I see if my table is there:

[db2inst1@sandbox1 ddl]$ db2 list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
CATALOG                         DB2INST1        T     2011-10-24-14.05.46.039412

  1 record(s) selected.

Open in new window


Then I show the stored procedure I placed into a file on Linux named hello.sql:

[db2inst1@sandbox1 ddl]$ cat hello.sql
CREATE procedure DropIt ()
  LANGUAGE SQL
  modifies sql data
BEGIN
   drop table deleteme cascade;
   commit;
END

Open in new window


Then I try to execute the SQL file in an attempt to read the stored procedure into DB2

[db2inst1@sandbox1 ddl]$ db2 -tvsf hello.sql
CREATE procedure DropIt ()
  LANGUAGE SQL
  modifies sql data
BEGIN
   drop table deleteme cascade
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 "drop table" was found following "fies sql data
BEGIN ".  Expected tokens may include:  "<space>".  LINE NUMBER=5.
SQLSTATE=42601

Open in new window


I have no idea why this does not work.  Any ideas.  Thanks
0
Comment
Question by:Los Angeles1
  • 2
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37020820
you have semi colons in your procedure code so db2 thinks it multiple commands and gets syntax errors
try to invoke the db2 clp with the -t parameter
0
 

Author Comment

by:Los Angeles1
ID: 37020945
I thought that's what I did, I used

db2 -tvsf hello.sql

Open in new window


I performed the following

[db2inst1@sandbox1 ddl]$ db2 -t hello.sql
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 "END-OF-STATEMENT" was found following
"hello.sql".  Expected tokens may include:  "JOIN <joined_table>".

Open in new window


and also performed:

[db2inst1@sandbox1 ddl]$ db2 -tvsf hello.sql
CREATE procedure DropIt ()
  LANGUAGE SQL
  modifies sql data
BEGIN
   drop table deleteme cascade
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 "drop table" was found following "fies sql data
BEGIN ".  Expected tokens may include:  "<space>".  LINE NUMBER=5.
SQLSTATE=42601

Open in new window


Still no luck
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 37020961
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question