I am using DB2 9.7.0.441 version on a Windows XP 2002 SP3

I have a few .SQL files that are given to me. I have created a database (COGOPS) and using the command editor I am trying to create tables, views, triggers in the COGOPS database. I was able to create the tables using one of the .sql files. However when running the following:

BEGIN
DECLARE Rec_Count, v_curr_category_num, v_dept_compl_ind, v_curr_dept_compl_ind, v_station_compl_ind, v_curr_station_compl_ind INTEGER;

it gives me the following 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 "END-OF-STATEMENT" was found following
"_compl_ind INTEGER". Expected tokens may include: "<psm_semicolon>".
LINE NUMBER=10. SQLSTATE=42601

What could be the problem and solution?

It is your statement delimiter. It normally defaults to the semicolon, but when you use compound statements you cannot use the semicolon as the statement delimiter. The entire statement is the BEGIN through the END, so the statement delimiter needs to be something else. Most people use an @.

Andy

Ok. I have replaced the semicolons with the @ sign everywhere. and my scripts looks like:
SET CURRENT SCHEMA = "COGOPS";

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","COGOP S";

CREATE OR REPLACE TRIGGER "COGOPS"."DVI_TRANSACTIONS_POST_INS_TRG"
AFTER INSERT
ON "COGOPS"."DVI_TRANSACTIONS"
REFERENCING
NEW AS new
FOR EACH ROW
--================================================== ================================================== ===============================

BEGIN
DECLARE Rec_Count, v_curr_category_num, v_dept_compl_ind, v_curr_dept_compl_ind, v_station_compl_ind, v_curr_station_compl_ind INTEGER@
DECLARE v_Destination, v_Curr_queue, v_prev_destination, v_curr_dept VARCHAR (5)@

DECLARE v_invoice_num, v_ACCT_NUM, v_SQR, v_FQR, v_OQR, v_EDGE_POLISH_IND, v_CRITICAL_REDO_IND, v_DIGITAL_STYLE_IND INTEGER@

DECLARE v_RX_NUM, v_RECD_DT, v_COMPL_DT, v_FACTORY_COAT_TYPE, v_PROMISE_DT, v_SC_SENT_DT, v_SC_RECD_DT, v_FR_RECD_DT VARCHAR (10)@
DECLARE v_SHIPPING_BIN, v_FRAME_MFG, v_REDO_CODE VARCHAR (10)@

DECLARE v_JOB_TYPE, v_JOB_CODE, v_PRICE_LIST, v_EDGE_TYPE, v_FRAME_STATUS, v_LENS_MTL, v_LENS_PICK, v_LENS_COLOR VARCHAR (5)@
DECLARE v_FRAME_MTL, v_TINT, v_AR_COAT, v_COLOR_COAT, v_FACTORY_COAT, v_SC_VENDOR, v_APFO_CTGRY VARCHAR (5)@
DECLARE v_PATTERN_TRACE_TYPE, v_DIGITAL_MFG, v_REDO_CTGRY VARCHAR (5)@
.............
........
........



And here is the error now:

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" was found following "ompl_ind
INTEGER@ ". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=10.
SQLSTATE=42601

SQL0104N An unexpected token "DECLARE" was found following "ompl_ind INTEGER@
". Expected tokens may include: "<psm_semicolon> ".

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

Please advise.

You did it backwards. Internal statements in a compound statement have to be terminated with a semicolon. So you terminate the other statements with @. So for you posted code, replace the @ with semicolons and replace the semicolons with @ and try it again.

Andy

Still no luck. I will have to paste my entire script here. Also just so u know, I also tried changing the statement termination character at the bottom of the command editor window to @. Here is my script with the changes suggested by you.

SET CURRENT SCHEMA = "COGOPS"@

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","COGOP S"@

CREATE OR REPLACE TRIGGER "COGOPS"."DVI_TRANSACTIONS_POST_INS_TRG"
AFTER INSERT
ON "COGOPS"."DVI_TRANSACTIONS"
REFERENCING
NEW AS new
FOR EACH ROW
--================================================== ================================================== ===============================

BEGIN
DECLARE Rec_Count, v_curr_category_num, v_dept_compl_ind, v_curr_dept_compl_ind, v_station_compl_ind, v_curr_station_compl_ind INTEGER;
DECLARE v_Destination, v_Curr_queue, v_prev_destination, v_curr_dept VARCHAR (5);

DECLARE v_invoice_num, v_ACCT_NUM, v_SQR, v_FQR, v_OQR, v_EDGE_POLISH_IND, v_CRITICAL_REDO_IND, v_DIGITAL_STYLE_IND INTEGER;

DECLARE v_RX_NUM, v_RECD_DT, v_COMPL_DT, v_FACTORY_COAT_TYPE, v_PROMISE_DT, v_SC_SENT_DT, v_SC_RECD_DT, v_FR_RECD_DT VARCHAR (10);
DECLARE v_SHIPPING_BIN, v_FRAME_MFG, v_REDO_CODE VARCHAR (10);

DECLARE v_JOB_TYPE, v_JOB_CODE, v_PRICE_LIST, v_EDGE_TYPE, v_FRAME_STATUS, v_LENS_MTL, v_LENS_PICK, v_LENS_COLOR VARCHAR (5);
DECLARE v_FRAME_MTL, v_TINT, v_AR_COAT, v_COLOR_COAT, v_FACTORY_COAT, v_SC_VENDOR, v_APFO_CTGRY VARCHAR (5);
DECLARE v_PATTERN_TRACE_TYPE, v_DIGITAL_MFG, v_REDO_CTGRY VARCHAR (5);

DECLARE v_LENS_OPC, v_TRAY_NUM VARCHAR (12);
DECLARE v_LENS_STYLE, v_LENS_STYLE_TYPE VARCHAR (50);
DECLARE v_FRAME_NAME VARCHAR (40);

DECLARE v_SPHER_PWR, v_CYLINDER_PWR DECIMAL (10, 3);
DECLARE v_DAYS_IN_LAB, v_DAYS_IN_PROCESS DECIMAL (10, 3);

DECLARE v_hr_ts varchar (20);

DECLARE v_curr_Station_num Integer;
DECLARE v_curr_station, v_curr_category Varchar (25);
DECLARE v_curr_destination Varchar (5);

-- adding these for the dept compl caclulation
DECLARE v_first_daily_rec Varchar (100);
DECLARE v_incr_rec_count, v_total_rec_count, v_prev_station_num Integer;
DECLARE v_process_flag, where_id Varchar(1);



--set v_curr_station_compl_ind = 1;
--set v_dept_compl_ind = 0;

-- a order state record should exist for each --
---->>> will need to deal with those Z and 0 temporary tray/invoice values


-- process only the incremental records:
--set v_hr_ts = (select param_char_value from rtm_params where PARAM_NAME = 'MAX_REC_TS');

--======================
SET CURRENT ISOLATION = CS;

Set Rec_Count = (select count(*) from RX_ORDER_STATE where INVOICE_NUM = new.INVNUM with UR);

If Rec_count > 0 then -- have a matching record in RX_Order_State,
-- in RX_Order_State, move current queue values to previous queue values

-- Get existing sacrxrpt values for the invoive in this trnasaction
--if new.hr_ts >= v_hr_ts then

SET (v_TRAY_NUM,
v_RX_NUM,
v_RECD_DT,
v_COMPL_DT,
v_ACCT_NUM,
v_JOB_TYPE,
...........
........
FRAME_MTL,
PATTERN_TRACE_TYPE,
DIGITAL_MFG,
EDGE_POLISH_IND,
FRAME_MFG,
FRAME_NAME,
DAYS_IN_LAB,
DAYS_IN_PROCESS,
SPHER_PWR,
CYLINDER_PWR,
REDO_CTGRY,
REDO_CODE,
CRITICAL_REDO_IND,
DIGITAL_STYLE_IND,
dept_compl_ind,
station_compl_ind,
curr_destination
, hr_ts
, first_daily_rec
, incr_rec_count
, total_rec_count
, curr_station_num
, curr_station_num
, curr_category_num
, curr_station
, curr_category
, curr_destination
, curr_queue
FROM RX_Order_State
WHERE invoice_num = new.invnum with UR);

..............

................................

................


If v_process_flag = 'Y' then

/* combined w/other update statemen
UPDATE RX_Order_State
SET prev_station_num = curr_Station_num,
prev_station = curr_station,
prev_category_num = curr_category_num,
prev_category = curr_category,
prev_destination = curr_destination,
prev_queue = curr_queue
WHERE invoice_num = new.invnum;
*/
-- Determine Destination Queues based on Scanned scanned station and Destination lookup table
-->>> For those with multiple possible destination, this is where it will be determined
-- *** Remove these from the Destination Table as they are covered here
-- Rules implemented from SacTransMap File

If new.station_num not in (38, 47, 39, 40, 41, 42, 43, 44, 45, 46, 99, 127, 124, 121, 116, 117, 129, 115, 137, 126, 125,
397, 399, 406, 402, 33, 149, 150, 152, 147, 148, 484, 388, 232, 432, 310, 400, 430, 428, 390,
225, 226, 227, 271, 270, 214, 215, 216, 415, 416, 417, 411, 412, 413, 243, 244, 245, 246, 249,
291, 272, 337, 349, 350, 345, 346, 348, 347, 54, 50 ) then -- verify 46, 99, 129, 152,

SET (v_Destination, v_curr_dept, v_curr_queue) =
(SELECT destination, substr (destination, 1, 1), curr_queue
FROM DESTINATIONS
WHERE STATION_NUM = new.station_num with UR);

Else

-- *** NOTE IN EACH CASE THERE IS A CRITERIA -- IF NOT MATCHED THEN NO DESTINATION IS ASSIGNED!

If new.station_num in (38, 47, 39, 40, 41, 42, 43, 44, 45) Then
If new.invnum is null Then
set v_Destination = '1A';
ElseIF new.invnum is not null Then
set v_Destination = '1D';
End If;
End If;

................
........................
.......................

elseif where_id = 'C' Then
UPDATE RX_Order_State
SET prev_station_num = v_curr_Station_num,
prev_station = v_curr_station,
prev_category_num = v_curr_category_num,
prev_category = v_curr_category,
prev_destination = v_curr_destination,
prev_queue = v_curr_queue,
curr_Station_num = new.Station_num,
curr_station = new.station,
curr_category = new.category,
curr_category_num = new.category_num,
hour_ts = new.hr_ts,
curr_destination = v_Destination,
curr_queue = v_Curr_Queue,
dept_compl_ind = v_curr_dept_compl_ind + v_dept_compl_ind,
station_compl_ind = v_curr_station_compl_ind + 1
, incr_rec_count = incr_rec_count + 1
WHERE invoice_num = new.invnum;
elseif where_id = 'D' Then
UPDATE RX_Order_State
SET prev_station_num = v_curr_Station_num,
prev_station = v_curr_station,
prev_category_num = v_curr_category_num,
prev_category = v_curr_category,
prev_destination = v_curr_destination,
prev_queue = v_curr_queue,
curr_Station_num = new.Station_num,
curr_station = new.station,
curr_category = new.category,
curr_category_num = new.category_num,
hour_ts = new.hr_ts,
curr_destination = v_Destination,
curr_queue = v_Curr_Queue,
dept_compl_ind = v_curr_dept_compl_ind + v_dept_compl_ind,
station_compl_ind = v_curr_station_compl_ind + 1
, incr_rec_count = incr_rec_count + 1
, total_rec_count = total_rec_count + 1
WHERE invoice_num = new.invnum;

End if;

End if;

End if; -- From PRocess Flag

END
@

How are you trying to run the script?

Andy

Well.I am not a frequent DB2 user. So, I am just copying the script in the command editor window and hitting the green arrow in the toolbar.

Any help in doing it the right way is appreciated.

There should be a field on the Command Editor that determines the statement termination character. It normally is set to semicolon. Replace it with @ and retry.

Andy