Db2 procedure error

[bikash_karan]

Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.

Below is one such procedure that i have written ,

pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

open new_cursor;
loop
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datb i,
H.datab=va_data b
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end loop ;
CLOSE new_cursor;
commit;
end

When I try and execute it I get the following error .but i am unable to
rectify this , tonight is the deadline for me to submit it into
production, can anyonle please help me out with this.my mail id is
bi******@in.ibm .com

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 "UPDATE SAPR3.KONH H SET H.datbi=va_"
was found
following "h_datbi , vh_datab; ". Expected tokens may include:
"<psm_retur n>". LINE NUMBER=27. SQLSTATE=42601

pads@disrs16:/home/pads/temp>

thanks a lot......

[Mark A]

<bi**********@h otmail.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.

Below is one such procedure that i have written ,

pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

open new_cursor;
loop
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datb i,
H.datab=va_data b
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end loop ;
CLOSE new_cursor;
commit;
end

When I try and execute it I get the following error .but i am unable to
rectify this , tonight is the deadline for me to submit it into
production, can anyonle please help me out with this.my mail id is
bi******@in.ibm .com

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 "UPDATE SAPR3.KONH H SET H.datbi=va_"
was found
following "h_datbi , vh_datab; ". Expected tokens may include:
"<psm_retur n>". LINE NUMBER=27. SQLSTATE=42601

pads@disrs16:/home/pads/temp>

thanks a lot......

What happens when your cursor hits EOF (+100) on the fetch? There is no
current of cursor at that point. I don't know if that is directly related to
the -104 error you are receiving, but it appears to be a problem.

[bikash_karan]

Hi Mark,

Thanks afor ur response ,
I have included the
Do until sqlcode=100 as shown below :

CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

OPEN new_cursor;
DO UNTIL SQLCODE = 100;
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datb i,
H.datab=va_data b
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end do;
CLOSE new_cursor;
commit;
end

But again i have received this error .....i am at loss.....please guide
me...
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 "DO" was found following " OPEN
new_cursor; ".
Expected tokens may include: "<psm_repea t>". LINE NUMBER=21.
SQLSTATE=42601

Best Regards
Bikash

[Mark A]

<bi**********@h otmail.com> wrote in message
news:11******** ************@f1 4g2000cwb.googl egroups.com...

Hi Mark,

Thanks afor ur response ,
I have included the
Do until sqlcode=100 as shown below :

CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

OPEN new_cursor;
DO UNTIL SQLCODE = 100;
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datb i,
H.datab=va_data b
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end do;
CLOSE new_cursor;
commit;
end

But again i have received this error .....i am at loss.....please guide
me...
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 "DO" was found following " OPEN
new_cursor; ".
Expected tokens may include: "<psm_repea t>". LINE NUMBER=21.
SQLSTATE=42601

Best Regards
Bikash

The "do until" condition will not be checked (and will not terminate the
loop) until the last statement in the loop. You need some basic programming
lessons.

It would also be better if you used SQL condition handlers.

[Knut Stolze]

Mark A wrote:

<bi**********@h otmail.com> wrote in message
news:11******** ************@f1 4g2000cwb.googl egroups.com...

Hi Mark,

Thanks afor ur response ,
I have included the
Do until sqlcode=100 as shown below :

CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

OPEN new_cursor;
DO UNTIL SQLCODE = 100;
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datb i,
H.datab=va_data b
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end do;
CLOSE new_cursor;
commit;
end

Why don't you simply use SQL to solve that problem?

UPDATE sapr3.konh
SET ( datbi, datab ) =
( SELECT a.datbi, a.datab
FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )
WHERE knumh BETWEEN '0000000000' AND '0090000000' AND
kotabnr = '030' AND
( datbi, datab ) IN
( SELECT h.datbi, h.datab
FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )

I don't know exactly what you want to do, but it might very well be possible
to further simplify the statement.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Serge Rielau]

bi**********@ho tmail.com wrote:

Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.

Below is one such procedure that i have written ,

pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

open new_cursor;
loop
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datb i,
H.datab=va_data b
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end loop ;
CLOSE new_cursor;
commit;
end

When I try and execute it I get the following error .but i am unable to
rectify this , tonight is the deadline for me to submit it into
production, can anyonle please help me out with this.my mail id is
bi******@in.ibm .com

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 "UPDATE SAPR3.KONH H SET H.datbi=va_"
was found
following "h_datbi , vh_datab; ". Expected tokens may include:
"<psm_retur n>". LINE NUMBER=27. SQLSTATE=42601

pads@disrs16:/home/pads/temp>

thanks a lot......

2 things:
This:
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
.... belongs into the cursor declaration. Don't select rows you don't
plan to work with.
If you will do different things with these rows use an IF statement to
control execute the UPDATE WHERE CURRENT OF.

Add thsi variable:
DECLARE SQLCODE INTEGER;

Name the loop:
myloop: LOOP

and test after the FETCH:
IF SQLCODE = 100 -- End of cursor?
THEN
LEAVE myloop;
END IF;

Call me or send email if you get stuck.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[bikash_karan]

Hi Knut ,

I had earlier the same query with me , but I tried the sql given by you
, the result were as per the expected line .

Here is what the output say :
pads@disrs16:/home/pads/temp>cat new_sql.log
SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '03
0'

1
-----------
215

1 record(s) selected.
UPDATE sapr3.konh SET ( datbi, datab ) = ( SELECT a.datbi, a.datab
FROM sa
pr3.A957 AS a, sapr3.konh AS h WHERE (a.datbi <> h.datbi OR a.datab <>
h.datab
) AND a.mandt = h.mandt AND a.knumh = h.knumh AND a.mandt = '030' )
WHERE knumh
BETWEEN '0000000000' AND '0090000000' AND kotabnr = '030' AND ( datbi,
datab )
IN ( SELECT h.datbi, h.datab FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.d
atbi <> h.datbi OR a.datab <> h.datab ) AND a.mandt = h.mandt AND
a.knumh = h.kn
umh AND a.mandt = '030' )
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '0
3 0'

1
-----------
0

1 record(s) selected.
pads@disrs16:/home/pads/temp>

To rectify the error message :
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

I wanted to modify this sql to a PL SQL block, some how I am still
stuck into this as the PLSQL for DB2 is quiet different from the one I
had in Oracle.

Thanks

[Knut Stolze]

bi**********@ho tmail.com wrote:

Hi Knut ,

I had earlier the same query with me , but I tried the sql given by you
, the result were as per the expected line .

What exactly is your UPDATE supposed to be doing? You want to set some
values in SAPR3.KONH to what? And which rows shall be modified? I've just
been guessing a lot on the semantics of your code.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Serge Rielau]

bi**********@ho tmail.com wrote:

Hi Knut ,

I had earlier the same query with me , but I tried the sql given by you
, the result were as per the expected line .

Here is what the output say :
pads@disrs16:/home/pads/temp>cat new_sql.log
SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '03
0'

1
-----------
215

1 record(s) selected.
UPDATE sapr3.konh SET ( datbi, datab ) = ( SELECT a.datbi, a.datab
FROM sa
pr3.A957 AS a, sapr3.konh AS h WHERE (a.datbi <> h.datbi OR a.datab <>
h.datab
) AND a.mandt = h.mandt AND a.knumh = h.knumh AND a.mandt = '030' )
WHERE knumh
BETWEEN '0000000000' AND '0090000000' AND kotabnr = '030' AND ( datbi,
datab )
IN ( SELECT h.datbi, h.datab FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.d
atbi <> h.datbi OR a.datab <> h.datab ) AND a.mandt = h.mandt AND
a.knumh = h.kn
umh AND a.mandt = '030' )
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '0
3 0'

1
-----------
0

1 record(s) selected.
pads@disrs16:/home/pads/temp>

To rectify the error message :
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

I wanted to modify this sql to a PL SQL block, some how I am still
stuck into this as the PLSQL for DB2 is quiet different from the one I
had in Oracle.

Thanks

You got confused on the SET clause. MERGE is easier to understand:

MERGE INTO sapr3.konh as h
USING sapr3.A957 AS a
ON a.mandt = h.mandt AND a.knumh = h.knumh AND a.mandt = '030'
and a.datbi <> h.datbi OR a.datab <> h.datab
and knumh BETWEEN '0000000000' AND '0090000000' AND kotabnr = '030'
WHEN MATCHED THEN UPDATE
SET ( datbi, datab ) = (a.datbi, a.datab)

The ON clause is a best guess, but I hope you'll figure it out from here

Cheers
Serge

PS: I'm serious about you calling me to help if needed.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tonkuma]

How about this?

UPDATE sapr3.konh h
SET ( datbi, datab ) =
( SELECT a.datbi, a.datab
FROM sapr3.A957 AS a
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )
WHERE knumh BETWEEN '0000000000' AND '0090000000' AND
kotabnr = '030' AND
( datbi, datab ) IN
( SELECT h.datbi, h.datab
FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )