INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL0104N SQLSTATE=42601

SQL0104N SQLSTATE=42601

(OP)
I am new to DB2 and have been following directions from a book for creating a stored procedure. Things LOOK like they should work, but I am getting errors.  What am I missing?  I've worked with MS SQL, so the syntax on this is very different..   Any help would be appreciated!

CREATE PROCEDURE san.MeetingInsert ()
LANGUAGE SQL
im: BEGIN
  DECLARE v1_alias varchar(25);
  DECLARE v1_T_30 varchar(10);
  DECLARE v1_T_14 varchar(10);
  DECLARE v1_T_3 varchar (10);
  DECLARE v2_ukey smallint;
  DECLARE v2_alias varchar(25);
  DECLARE v2_T_30 varchar(10);
  DECLARE v2_T_14 varchar(10),
  DECLARE v2_T_3 varchar (10);
  DECLARE V2_RS_Status char(1);
  DECLARE v2_RS_Date DATE;
  DECLARE v2_RS_Time TIME;  

DECLARE IMC CURSOR FOR
 SELECT t1.ALIAS, t1.T_30, t1.T_14, t1.T_3, t2.ALIAS, t2.T_30, t2.T_14, t2.T_3,t2.RS_STATUS,t2.RS_Date,t2.RS_Time
 from SAN.FS_MEETING_STG t1
 left join SAN.FS_MEETING_DIM t2
 on t1.ALIAS = t2.ALIAS;
OPEN IMC;
------------------------------------------------------------------------------
CREATE PROCEDURE san.MeetingInsert ()
LANGUAGE SQL
im: BEGIN
  DECLARE v1_alias varchar(25)
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 "v1_alias
varchar(25)".  Expected tokens may include:  "<psm_semicolon>".  LINE
NUMBER=4.  SQLSTATE=42601

DECLARE v1_T_30 varchar(10)
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 "DECLARE v1_T_30 varchar" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE v1_T_14 varchar(10)
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 "DECLARE v1_T_14 varchar" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE v1_T_3 varchar (10)
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 "DECLARE v1_T_3 varchar" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE v2_ukey smallint
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 "smallint" was found following "DECLARE v2_ukey
".  Expected tokens may include:  "END-OF-STATEMENT".  LINE NUMBER=1.  
SQLSTATE=42601

DECLARE v2_alias varchar(25)
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 "DECLARE v2_alias varchar" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE v2_T_30 varchar(10)
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 "DECLARE v2_T_30 varchar" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE v2_T_14 varchar(10), DECLARE v2_T_3 varchar (10)
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 "DECLARE v2_T_14 varchar" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE V2_RS_Status char(1)
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 "DECLARE V2_RS_Status char" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  LINE
NUMBER=1.  SQLSTATE=42601

DECLARE v2_RS_Date DATE
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 "DATE" was found following "DECLARE v2_RS_Date
".  Expected tokens may include:  "END-OF-STATEMENT".  LINE NUMBER=1.  
SQLSTATE=42601

DECLARE v2_RS_Time TIME
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 "TIME" was found following "DECLARE v2_RS_Time
".  Expected tokens may include:  "END-OF-STATEMENT".  LINE NUMBER=1.  
SQLSTATE=42601

DECLARE IMC CURSOR FOR SELECT t1.ALIAS, t1.T_30, t1.T_14, t1.T_3, t2.ALIAS, t2.T_30, t2.T_14, t2.T_3,t2.RS_STATUS,t2.RS_Date,t2.RS_Time from SAN.FS_MEETING_STG t1 left join SAN.FS_MEETING_DIM t2 on t1.ALIAS = t2.ALIAS
DB20000I  The SQL command completed successfully.

OPEN IMC
DB20000I  The SQL command completed successfully.
 

RE: SQL0104N SQLSTATE=42601

Perharps the SQL processor doesn't like underscore ( _ ) as it exists on every variable name ?

Philippe

RE: SQL0104N SQLSTATE=42601

(OP)
I removed underscores and found that I had the same error. It was worth a try.  Still looking for a solution.  Thanks!

RE: SQL0104N SQLSTATE=42601

(OP)
I am experiencing one further error on this procedure. I've been researching, but haven't found anything specific as a solution.  Most descriptions of the problem are too broad.  Below is the sql:

DECLARE IMC CURSOR FOR
 SELECT t1.ALIAS, t1.T_30, t1.T_14, t1.T_3, t2.ukey, t2.ALIAS, t2.T_30, t2.T_14, t2.T_3,t2.RS_STATUS,t2.RS_Date,t2.RS_Time
 from SAN.FS_MEETING_STG t1
 left join SAN.FS_MEETING_DIM t2
  on t1.ALIAS = t2.ALIAS
  where t2.ALIAS is null;
OPEN IMC;
FETCH FROM IMC INTO v1_alias, v1_t_30, v1_t_14, v1_t_3, v2_ukey, v2_alias, v2_t_30, v2_t_14, v2_t_3, v2_rs_status, v2_RS_Date, v2_RS_Time;
WHILE (SQLSTATE = '00000') DO
    SET v2_ukey = MAX(ukey);
    INSERT INTO SAN.FS_MEETING_DIM (UKEY, ALIAS, T_30, T_14, T_3, RS_STATUS, RS_DATE, RS_TIME)
    VALUES ((v2_ukey+1), v1_alias, v1_t_30, v1_t14, v1_t_3, 'A', TODAY, TIME);
  FETCH NEXT IMC INTO v1_alias, v1_t_30, v1_t_14, v1_t_3, v2_ukey, v2_alias, v2_t_30, v2_t_14, v2_t_3, v2_rs_status, v2_RS_Date, v2_RS_Time;
END WHILE;
Close IMC;
END im
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 "IMC" was found following "TIME);   FETCH NEXT".
Expected tokens may include:  "<space>".  LINE NUMBER=30.  SQLSTATE=42601

SQL0104N  An unexpected token "IMC" was found following "TIME);
  FETCH NEXT".  Expected tokens may include:  "<space>                                       ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

 sqlcode: -104

 sqlstate: 42601

 

RE: SQL0104N SQLSTATE=42601

What are you wanting to do exactly coz the procedure doesn't look right in the sequence.
F.E.
You can NOT do

CODE

SET v2_ukey = MAX(ukey);
You have to do

CODE

1/ SELECT MAX(ukey) into MyVar From ...

CODE

2/ SET MyVar = MyVar + 1;

CODE

3/ If you are on release V5R3+ create a Sequence Object and use it to insert into your file :
CREATE SEQUENCE MySchema/MySequence
    AS INTEGER
    START WITH MyVar
    INCREMENT BY 1
    MINVALUE MyVar
    MAXVALUE 2147483647
    NO CYCLE CACHE 20 ORDER;
and then
    INSERT INTO SAN.FS_MEETING_DIM (UKEY, ALIAS, T_30, T_14, T_3, RS_STATUS, RS_DATE, RS_TIME)
    VALUES (Next value for MySequence, ...

Also, the second FETCH stm has nothing to do here. SQL WHILE-END WHILE stm are not RPG DoWhile-Enddo stm.

However, if you just want to insert the T1 records that do not exist in T2, you could write

CODE

INSERT INTO SAN.FS_MEETING_DIM (
      SELECT Next value for MySequence, t1.alias, t1.t_30, t1.t14, t1.t_3, 'A', TODAY, TIME
        FROM SAN.FS_MEETING_STG t1
            EXCEPTION JOIN SAN.FS_MEETING_DIM t2
            ON t1.ALIAS = t2.ALIAS );

You'd be better off if you d/l and read the System i DB2 for i SQL Reference from here.
 

Philippe

RE: SQL0104N SQLSTATE=42601

(OP)
Thanks for the additional comments. I am actually going to be writing two procedures with cursors, one to update records that have changed to "inactive" and the second to insert new records or new lines for records that have been updated, which will be active records - thus maintaining history.  Since this is my first DB2 procedure/cursor I was trying to create a simple cursor that would insert records that do not exist yet so that I could get the syntax down and have a format for expanding the sql to create the more complex procedures with more complex cursors. I simply need to get the format/syntax down so that I can expand on it. Unfortunately, I am looking at lots of books and resources and most of them do not have a lot of detail information on cursors. I am working with DB2 9.7.2.  Thanks!

RE: SQL0104N SQLSTATE=42601

Hi TecZen,
From what you have said that you are trying to do, I would suggest a possible alternative method that does not involve the complex cursors that you are attempting.

Have a look at the excellent DB2 cookbook by Graeme Birchall which can be found here. Page 335 describes DB2 triggers, which can be set up on a table. When an update/delete/insert has been made to the table these triggers kick in and can perform further amendments to the existing table, insert rows to a shadow or audit table, or call a stored procedure.

Apologies if you have already considered this and ruled it out, but it seemed to me from your posts that this might be a path you could take.

Hope this helps.
Marc

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close