[z/OS] Longest Stored Procedure Variable?

Philip Sevetson

[z/OS] Longest Stored Procedure Variable?
Does anyone know what datatype/length represents the largest variable one
can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on
CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is
quite maddeningly vague. Has anyone used a CLOB datatype as a Stored
Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]


---------------------------------------------------------------------------------
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

Martin Kenney

Re: [z/OS] Longest Stored Procedure Variable?
(in response to Philip Sevetson)
We use VARCHAR(32000) for many stored procedure parameters with no
problems.



Marty Kenney

Sr. DB2 DBA

Railinc

(919) 651-5211



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?




Does anyone know what datatype/length represents the largest variable
one can pass to a DB2 Stored Procedure as a parameter? The SQL
Reference on CREATE PROCEDURE points to the "Built-in Datatypes"
discussion, which is quite maddeningly vague. Has anyone used a CLOB
datatype as a Stored Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

------------------------------------------------------------------------
--------- 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


---------------------------------------------------------------------------------
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

Georg Peter

AW: [z/OS] Longest Stored Procedure Variable?
(in response to Martin Kenney)
The highest number of bytes passed in a single parameter is 32.765 bytes
(non-lob) or 2 GB - 1 Byte (2.147.483.647 bytes) for a LOB..... as far as I
know.......

With kind regards - mit freundlichen Gruessen,
Georg
----------------------------------------------------------------------

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag
von Kenney, Marty
Gesendet: Mittwoch, 22. Dezember 2004 15:30
An: [login to unmask email]
Betreff: Re: [z/OS] Longest Stored Procedure Variable?



We use VARCHAR(32000) for many stored procedure parameters with no problems.



Marty Kenney

Sr. DB2 DBA

Railinc

(919) 651-5211



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?




Does anyone know what datatype/length represents the largest variable one
can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on
CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is
quite maddeningly vague. Has anyone used a CLOB datatype as a Stored
Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

----------------------------------------------------------------------------
----- 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

----------------------------------------------------------------------------
----- 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


---------------------------------------------------------------------------------
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

Philip Sevetson

[z/OS] Longest Stored Procedure Variable?
(in response to Georg Peter)
...but has anyone actually _TRIED_ a 2GB-or-smaller LOB, and what-if-any
issues are associated with it?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






"Peter, Georg" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/22/2004 09:49 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] AW: [z/OS] Longest Stored Procedure Variable?


The highest number of bytes passed in a single parameter is 32.765 bytes
(non-lob) or 2 GB - 1 Byte (2.147.483.647 bytes) for a LOB..... as far as
I know.......

With kind regards - mit freundlichen Gruessen,
Georg
----------------------------------------------------------------------
-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Kenney, Marty
Gesendet: Mittwoch, 22. Dezember 2004 15:30
An: [login to unmask email]
Betreff: Re: [z/OS] Longest Stored Procedure Variable?

We use VARCHAR(32000) for many stored procedure parameters with no
problems.

Marty Kenney
Sr. DB2 DBA
Railinc
(919) 651-5211

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?


Does anyone know what datatype/length represents the largest variable one
can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on
CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is
quite maddeningly vague. Has anyone used a CLOB datatype as a Stored
Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------
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


---------------------------------------------------------------------------------
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

Mike Vaughan

Re: [z/OS] Longest Stored Procedure Variable?
(in response to Philip Sevetson)
We did do some testing using CLOB's that were larger than 32k just to verify that we could. I don't recall the actual largest size we used - I know it was over 150k but definitely well short of 2GB and also involved using multiple CLOB parameters on a single SP call. I don't believe we encountered any issues with this, although at one point we did try to measure any performance implications of using multiple long-varchar parameters vs a single CLOB if the parameter needed to exceed 32k (for example, is a CLOB(64k) more efficient than 2 long-varchars that need to be concatenated together). I don't have the numbers in front of me, but if memory serves me correctly I believe there was a slight performance hit to using the CLOB over multiple long-varchars.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 10:29 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?



...but has anyone actually _TRIED_ a 2GB-or-smaller LOB, and what-if-any issues are associated with it?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]





"Peter, Georg" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


12/22/2004 09:49 AM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: [DB2-L] AW: [z/OS] Longest Stored Procedure Variable?



The highest number of bytes passed in a single parameter is 32.765 bytes (non-lob) or 2 GB - 1 Byte (2.147.483.647 bytes) for a LOB..... as far as I know.......

With kind regards - mit freundlichen Gruessen,
Georg
----------------------------------------------------------------------
-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Kenney, Marty
Gesendet: Mittwoch, 22. Dezember 2004 15:30
An: [login to unmask email]
Betreff: Re: [z/OS] Longest Stored Procedure Variable?

We use VARCHAR(32000) for many stored procedure parameters with no problems.

Marty Kenney
Sr. DB2 DBA
Railinc
(919) 651-5211

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?


Does anyone know what datatype/length represents the largest variable one can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is quite maddeningly vague. Has anyone used a CLOB datatype as a Stored Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

--------------------------------------------------------------------------------- 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

--------------------------------------------------------------------------------- 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
--------------------------------------------------------------------------------- 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

--------------------------------------------------------------------------------- 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



-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

---------------------------------------------------------------------------------
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

Mark Fleming

Re: [z/OS] Longest Stored Procedure Variable?
(in response to Mike Vaughan)
I've used a CLOB(700000) data type with no problems.

Mark Fleming
Data Base Administration
847-667-5690

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Peter, Georg
Sent: Wednesday, December 22, 2004 8:49 AM
To: [login to unmask email]
Subject: AW: [z/OS] Longest Stored Procedure Variable?


The highest number of bytes passed in a single parameter is 32.765 bytes (non-lob) or 2 GB - 1 Byte (2.147.483.647 bytes) for a LOB..... as far as I know.......


With kind regards - mit freundlichen Gruessen,
Georg
----------------------------------------------------------------------

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Kenney, Marty
Gesendet: Mittwoch, 22. Dezember 2004 15:30
An: [login to unmask email]
Betreff: Re: [z/OS] Longest Stored Procedure Variable?



We use VARCHAR(32000) for many stored procedure parameters with no problems.



Marty Kenney

Sr. DB2 DBA

Railinc

(919) 651-5211



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?




Does anyone know what datatype/length represents the largest variable one can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is quite maddeningly vague. Has anyone used a CLOB datatype as a Stored Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

--------------------------------------------------------------------------------- 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

--------------------------------------------------------------------------------- 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

--------------------------------------------------------------------------------- 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


---------------------------------------------------------------------------------
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

Mark Fleming

Re: [z/OS] Longest Stored Procedure Variable?
(in response to Mark Fleming)
Phil,

The only problem I encountered was during the call. I used to be able to open a Command Line Processor (CLP) window, issue a connect, and fire off the stored procedure; but that was when I used a much smaller parm defined as VARCHAR. When I changed it to a CLOB, the CLP didn't support a call with a CLOB data type, so I had to switch to using Stored Procedure Builder to test it. Had no problems after that.

Mark Fleming
Data Base Administration
847-667-5690

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 10:29 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?



...but has anyone actually _TRIED_ a 2GB-or-smaller LOB, and what-if-any issues are associated with it?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]





"Peter, Georg" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


12/22/2004 09:49 AM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: [DB2-L] AW: [z/OS] Longest Stored Procedure Variable?



The highest number of bytes passed in a single parameter is 32.765 bytes (non-lob) or 2 GB - 1 Byte (2.147.483.647 bytes) for a LOB..... as far as I know.......

With kind regards - mit freundlichen Gruessen,
Georg
----------------------------------------------------------------------
-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Kenney, Marty
Gesendet: Mittwoch, 22. Dezember 2004 15:30
An: [login to unmask email]
Betreff: Re: [z/OS] Longest Stored Procedure Variable?

We use VARCHAR(32000) for many stored procedure parameters with no problems.

Marty Kenney
Sr. DB2 DBA
Railinc
(919) 651-5211

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?


Does anyone know what datatype/length represents the largest variable one can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is quite maddeningly vague. Has anyone used a CLOB datatype as a Stored Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

--------------------------------------------------------------------------------- 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

--------------------------------------------------------------------------------- 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
--------------------------------------------------------------------------------- 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

--------------------------------------------------------------------------------- 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


---------------------------------------------------------------------------------
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

Philip Sevetson

Re: [z/OS] Longest Stored Procedure Variable?
(in response to Mark Fleming)
Right, thanks. We're using Java to call the SPs through DB2/Connect
Enterprise, so we should be okay with the longer datatypes.

Thanks, all, for your quick responses on this.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]






"Fleming, Mark" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/22/2004 12:01 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] [z/OS] Longest Stored Procedure Variable?


Phil,

The only problem I encountered was during the call. I used to be able to
open a Command Line Processor (CLP) window, issue a connect, and fire off
the stored procedure; but that was when I used a much smaller parm defined
as VARCHAR. When I changed it to a CLOB, the CLP didn't support a call
with a CLOB data type, so I had to switch to using Stored Procedure
Builder to test it. Had no problems after that.
Mark Fleming
Data Base Administration
847-667-5690
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 10:29 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?


...but has anyone actually _TRIED_ a 2GB-or-smaller LOB, and what-if-any
issues are associated with it?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]





"Peter, Georg" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/22/2004 09:49 AM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] AW: [z/OS] Longest Stored Procedure
Variable?



The highest number of bytes passed in a single parameter is 32.765 bytes
(non-lob) or 2 GB - 1 Byte (2.147.483.647 bytes) for a LOB..... as far as
I know.......

With kind regards - mit freundlichen Gruessen,
Georg
----------------------------------------------------------------------
-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Kenney, Marty
Gesendet: Mittwoch, 22. Dezember 2004 15:30
An: [login to unmask email]
Betreff: Re: [z/OS] Longest Stored Procedure Variable?

We use VARCHAR(32000) for many stored procedure parameters with no
problems.

Marty Kenney
Sr. DB2 DBA
Railinc
(919) 651-5211

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Wednesday, December 22, 2004 9:23 AM
To: [login to unmask email]
Subject: [z/OS] Longest Stored Procedure Variable?


Does anyone know what datatype/length represents the largest variable one
can pass to a DB2 Stored Procedure as a parameter? The SQL Reference on
CREATE PROCEDURE points to the "Built-in Datatypes" discussion, which is
quite maddeningly vague. Has anyone used a CLOB datatype as a Stored
Procedure parameter? How about VARCHAR(32000)?


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------
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

---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------
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


---------------------------------------------------------------------------------
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