SQL0104N SQLSTATE=42601

[TecZen]

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.

 

[Mercury2]

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

Philippe

 

[TecZen]

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

 

[Mercury2]

ok. I think I get it. Check out "SQL0104N error in DB2 Command Editor due to incorrect statement termination character" @

https://www-304.ibm.com/support/docview.wss?uid=swg21224723

Philippe

 

[TecZen]

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

 

[Mercury2]

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

SET v2_ukey = MAX(ukey);

You have to do

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

2/ SET MyVar = MyVar + 1;

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

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

 

[TecZen]

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!

 

[MarcLodge]

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