DB2 for z/OS Version 7 - converting Oracle PL/SQL

Jim McAlpine

DB2 for z/OS Version 7 - converting Oracle PL/SQL
We have the following function written in Oracle PL/SQL which we need to implement in DB2. What options do we have to convert PL/SQL to provide the same functionality in DB2.

CREATE FUNCTION E5CONCAT (SYSREF NUMBER) RETURN CLOB AS
STR CLOB;
CURSOR C1 IS
SELECT TRIM(TXT) FROM TXEDLINE WHERE DIARY_SYSREF=SYSREF ORDER BY LINE_SEQ;
TXT TXEDLINE.TXT%TYPE;
X NUMBER;
BEGIN
STR := '';
X := 0;
OPEN C1;
LOOP
FETCH C1 INTO TXT;
X := X + 1;
EXIT WHEN C1%NOTFOUND;
IF X = 1
THEN
STR := TXT;
ELSE
DBMS_LOB.APPEND(STR, CHR(13) || TXT);
END IF;
END LOOP;
RETURN STR;
END E5CONCAT;

Jim McAlpine

This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity to
whom they are addressed. Any unauthorised distribution or
copying is strictly prohibited. Whilst COA takes steps to
prevent the transmission of viruses via e-mail,
we cannot guarantee that any email or attachment is free from
computer viruses and you are strongly advised to undertake your
own anti-virus precautions. COA grants no
warranties regarding performance, use or quality of any e-mail
or attachment and undertakes no liability for loss or damage,
howsoever caused.

COA is a trading name of Cedar Software Ltd, OpenAccounts Ltd,
Open People Ltd and Strata Ltd.
Cedar Software Ltd is registered in England and Wales No:561244.

Registered Office Munroe House, Portsmouth Road, Cobham, Surrey,
KT11 1TF

For more information on COA and our products,
please visit our web site at http://www.cedaropenaccounts.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DB2 for z/OS Version 7 - converting Oracle PL/SQL
(in response to Jim McAlpine)
DB2 V7 - you will have to write a UDF in COBOL, Assembler, C, etc to do this.

DB2 V8 - you might be able to conjure up some nested (ie WITH .... SELECT .... type code)
to do this

DB2 V9 - not enough information in the public arena at this time to know if V9 will have
other options.

James Campbell

On 18 Jan 2007 at 15:31, Jim McAlpine wrote:

> We have the following function written in Oracle PL/SQL which we need to implement in DB2. What options do we have to convert PL/SQL to provide the same functionality in DB2.
>
> CREATE FUNCTION E5CONCAT (SYSREF NUMBER) RETURN CLOB AS
> STR CLOB;
> CURSOR C1 IS
> SELECT TRIM(TXT) FROM TXEDLINE WHERE DIARY_SYSREF=SYSREF ORDER BY LINE_SEQ;
> TXT TXEDLINE.TXT%TYPE;
> X NUMBER;
> BEGIN
> STR := '';
> X := 0;
> OPEN C1;
> LOOP
> FETCH C1 INTO TXT;
> X := X + 1;
> EXIT WHEN C1%NOTFOUND;
> IF X = 1
> THEN
> STR := TXT;
> ELSE
> DBMS_LOB.APPEND(STR, CHR(13) || TXT);
> END IF;
> END LOOP;
> RETURN STR;
> END E5CONCAT;
>
> Jim McAlpine
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jim McAlpine

Re: DB2 for z/OS Version 7 - converting Oracle PL/SQL
(in response to Jim McAlpine)
Thanks James. One thought we has was that we could implement this as a stored procedure instead of a function. In the DB2 Version 7 Introduction to DB2 manual it has a sample of a procedure written with SQL Procedure Language like this -

CREATE PROCEDURE ITERATOR() LANGUAGE SQL
BEGIN
..
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR ....;
DECLARE CONTINUE HANDLER FOR not_found (2)
SET at_end = 1;
OPEN c1;
ftch_loop1: LOOP
FETCH c1 INTO v_dept, v_deptname, v_admdept; (1)
IF at_end = 1 THEN
LEAVE ftch_loop1; (3)
ELSEIF v_dept = 'D01' THEN
ITERATE ftch_loop1;
END IF;
INSERT INTO department (deptno, deptname, admrdept)
VALUES ( 'NEW', v_deptname, v_admdept);
END LOOP;
CLOSE c1;
END

But nowhere in the manuals can I find a description of this language syntax.

Anyone know where this is documented.

Jim McAlpine

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of James Campbell
Sent: 19 January 2007 01:34
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS Version 7 - converting Oracle PL/SQL


DB2 V7 - you will have to write a UDF in COBOL, Assembler, C, etc to do this.

DB2 V8 - you might be able to conjure up some nested (ie WITH .... SELECT .... type code)
to do this

DB2 V9 - not enough information in the public arena at this time to know if V9 will have
other options.

James Campbell


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity to
whom they are addressed. Any unauthorised distribution or
copying is strictly prohibited. Whilst COA takes steps to
prevent the transmission of viruses via e-mail,
we cannot guarantee that any email or attachment is free from
computer viruses and you are strongly advised to undertake your
own anti-virus precautions. COA grants no
warranties regarding performance, use or quality of any e-mail
or attachment and undertakes no liability for loss or damage,
howsoever caused.

COA is a trading name of Cedar Software Ltd, OpenAccounts Ltd,
Open People Ltd and Strata Ltd.
Cedar Software Ltd is registered in England and Wales No:561244.

Registered Office Munroe House, Portsmouth Road, Cobham, Surrey,
KT11 1TF

For more information on COA and our products,
please visit our web site at http://www.cedaropenaccounts.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jim McAlpine

Re: DB2 for z/OS Version 7 - converting Oracle PL/SQL
(in response to James Campbell)
OK, forget that. The syntax is described in chapter 6 of the SQL Reference Manual.

Jim McAlpine

-----Original Message-----
From: Jim McAlpine
Sent: 19 January 2007 11:23
To: 'DB2 Database Discussion list at IDUG'
Subject: RE: [DB2-L] DB2 for z/OS Version 7 - converting Oracle PL/SQL


Thanks James. One thought we has was that we could implement this as a stored procedure instead of a function. In the DB2 Version 7 Introduction to DB2 manual it has a sample of a procedure written with SQL Procedure Language like this -

CREATE PROCEDURE ITERATOR() LANGUAGE SQL
BEGIN
..
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR ....;
DECLARE CONTINUE HANDLER FOR not_found (2)
SET at_end = 1;
OPEN c1;
ftch_loop1: LOOP
FETCH c1 INTO v_dept, v_deptname, v_admdept; (1)
IF at_end = 1 THEN
LEAVE ftch_loop1; (3)
ELSEIF v_dept = 'D01' THEN
ITERATE ftch_loop1;
END IF;
INSERT INTO department (deptno, deptname, admrdept)
VALUES ( 'NEW', v_deptname, v_admdept);
END LOOP;
CLOSE c1;
END

But nowhere in the manuals can I find a description of this language syntax.

Anyone know where this is documented.

Jim McAlpine

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of James Campbell
Sent: 19 January 2007 01:34
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS Version 7 - converting Oracle PL/SQL


DB2 V7 - you will have to write a UDF in COBOL, Assembler, C, etc to do this.

DB2 V8 - you might be able to conjure up some nested (ie WITH .... SELECT .... type code)
to do this

DB2 V9 - not enough information in the public arena at this time to know if V9 will have
other options.

James Campbell


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity to
whom they are addressed. Any unauthorised distribution or
copying is strictly prohibited. Whilst COA takes steps to
prevent the transmission of viruses via e-mail,
we cannot guarantee that any email or attachment is free from
computer viruses and you are strongly advised to undertake your
own anti-virus precautions. COA grants no
warranties regarding performance, use or quality of any e-mail
or attachment and undertakes no liability for loss or damage,
howsoever caused.

COA is a trading name of Cedar Software Ltd, OpenAccounts Ltd,
Open People Ltd and Strata Ltd.
Cedar Software Ltd is registered in England and Wales No:561244.

Registered Office Munroe House, Portsmouth Road, Cobham, Surrey,
KT11 1TF

For more information on COA and our products,
please visit our web site at http://www.cedaropenaccounts.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DB2 for z/OS Version 7 - converting Oracle PL/SQL
(in response to Jim McAlpine)
The problem with using a SP is, while it would "work", the user SQL is still
expecting a result of a function - not something returned by a call to a
procedure; so you also have to change that.

Until DB2 V9, the SQL Language code is translated into C code - so you
still have the overheads of the trip to a WLM address space.

James Campbell

On 19 Jan 2007 at 11:22, Jim McAlpine wrote:

> Thanks James. One thought we has was that we could implement this as a stored procedure instead of a function. In the DB2 Version 7 Introduction to DB2 manual it has a sample of a procedure written with SQL Procedure Language like this -
>
> CREATE PROCEDURE ITERATOR() LANGUAGE SQL
> BEGIN
> ..
<rest snipped>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm