Help with CLOBs z/os 1.4 db2v7

[login to unmask email]

Help with CLOBs z/os 1.4 db2v7
We are having problems with loading a CLOB in a pl/1 cics program.
The data coming in is about 32000 and when it is loaded into the table it
comes out as 15243, apparently being truncated.
The host variable is declared as char(32000) varying.
The data is also being passed to an xml parser and there it is working
correctly, so the error appears to be in the db2 process
The clob is being brought in and stored as ascii (ccsid of 1208)
We need this to be working correctly in a few hours.
tia

Bud Greenman
Applications Programming Manager

---------------------------------------------------------------------------------
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: Help with CLOBs z/os 1.4 db2v7
(in response to BudGreenman@ONGOV.NET)
Ah, the things that can go wrong.

The first thing I notice is that ccsid=1208 is not an ascii ccsid - it is UTF-8
(u+0000 TO U+007F are stored in a single byte which correspond to the
ASCII characters; U+0080 upwards are stored as 2, 3 or even 4 bytes). It
is possible that you've messed up the ccisd's - perhaps, depending on your
actual data you are reading 32000 bytes of data, presenting it to DB2
saying "this is in utf-8", db2 decodes the data as utf-8, and decides that
there are actually 15243 unicode characters.

I simply have no idea.

James Campbell

On 6 Jun 2007 at 8:16, [login to unmask email] wrote:

> We are having problems with loading a CLOB in a pl/1 cics program.
> The data coming in is about 32000 and when it is loaded into the table it
> comes out as 15243, apparently being truncated.
> The host variable is declared as char(32000) varying.
> The data is also being passed to an xml parser and there it is working
> correctly, so the error appears to be in the db2 process
> The clob is being brought in and stored as ascii (ccsid of 1208)
> We need this to be working correctly in a few hours.
> tia
>
> Bud Greenman
> Applications Programming Manager
>
> ---------------------------------------------------------------------------------
> 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

[login to unmask email]

Re: Help with CLOBs z/os 1.4 db2v7
(in response to James Campbell)
Well, we did find out what the problem is. The data that is being sent is
XML and the first 2 characters are <? or hex '3f3c' in utf-8 which is also
equal to 15243 in decimal, ie: the first two char are being interpreted as
the length field of the varying length string. I think that this clob
should really have been created as a blob, but we are still not sure how to
get around this issue.


Bud Greenman
Applications Programming Manager



James Campbell
<[login to unmask email]
NK.NET.AU> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] Help with CLOBs z/os
1.4 db2v7

06/06/2007 09:10
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Ah, the things that can go wrong.

The first thing I notice is that ccsid=1208 is not an ascii ccsid - it is
UTF-8
(u+0000 TO U+007F are stored in a single byte which correspond to the
ASCII characters; U+0080 upwards are stored as 2, 3 or even 4 bytes). It
is possible that you've messed up the ccisd's - perhaps, depending on your
actual data you are reading 32000 bytes of data, presenting it to DB2
saying "this is in utf-8", db2 decodes the data as utf-8, and decides that
there are actually 15243 unicode characters.

I simply have no idea.

James Campbell

On 6 Jun 2007 at 8:16, [login to unmask email] wrote:

> We are having problems with loading a CLOB in a pl/1 cics program.
> The data coming in is about 32000 and when it is loaded into the table it
> comes out as 15243, apparently being truncated.
> The host variable is declared as char(32000) varying.
> The data is also being passed to an xml parser and there it is working
> correctly, so the error appears to be in the db2 process
> The clob is being brought in and stored as ascii (ccsid of 1208)
> We need this to be working correctly in a few hours.
> tia
>
> Bud Greenman
> Applications Programming Manager
>
>
---------------------------------------------------------------------------------

> 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

James Campbell

Re: Help with CLOBs z/os 1.4 db2v7
(in response to BudGreenman@ONGOV.NET)
I would look very closely at the actual data structure used for this. Is
someone using an SQLDA to declare the data structure, using a varchar
type structure for this data
ie 2 byte binary length of data
actual char data
, but moving 408 to the SQLDA SQLTYPE. 408 defines the host data as a
clob - but this requires a 4 byte length (not 2 as for varchar).
ie 4 byte binary length of data
actual char data
Having forgotten to move the actual length into the 2 byte length, DB2 now
sees the 4 byte length as x'00003f3c'.

James Campbell

On 6 Jun 2007 at 9:40, [login to unmask email] wrote:

> Well, we did find out what the problem is. The data that is being sent is
> XML and the first 2 characters are <? or hex '3f3c' in utf-8 which is also
> equal to 15243 in decimal, ie: the first two char are being interpreted as
> the length field of the varying length string. I think that this clob
> should really have been created as a blob, but we are still not sure how to
> get around this issue.
>
>
> Bud Greenman
> Applications Programming Manager
>
>
>
> James Campbell
> <[login to unmask email]
> NK.NET.AU> To
> Sent by: DB2 Data [login to unmask email]
> Base Discussion cc
> List
> <[login to unmask email] Subject
> ORG> Re: [DB2-L] Help with CLOBs z/os
> 1.4 db2v7
>
> 06/06/2007 09:10
> AM
>
>
> Please respond to
> DB2 Database
> Discussion list
> at IDUG
> <[login to unmask email]
> 2-L.ORG>
>
>
>
>
>
>
> Ah, the things that can go wrong.
>
> The first thing I notice is that ccsid=1208 is not an ascii ccsid - it is
> UTF-8
> (u+0000 TO U+007F are stored in a single byte which correspond to the
> ASCII characters; U+0080 upwards are stored as 2, 3 or even 4 bytes). It
> is possible that you've messed up the ccisd's - perhaps, depending on your
> actual data you are reading 32000 bytes of data, presenting it to DB2
> saying "this is in utf-8", db2 decodes the data as utf-8, and decides that
> there are actually 15243 unicode characters.
>
> I simply have no idea.
>
> James Campbell
>
> On 6 Jun 2007 at 8:16, [login to unmask email] wrote:
>
> > We are having problems with loading a CLOB in a pl/1 cics program.
> > The data coming in is about 32000 and when it is loaded into the table it
> > comes out as 15243, apparently being truncated.
> > The host variable is declared as char(32000) varying.
> > The data is also being passed to an xml parser and there it is working
> > correctly, so the error appears to be in the db2 process
> > The clob is being brought in and stored as ascii (ccsid of 1208)
> > We need this to be working correctly in a few hours.
> > tia
> >
> > Bud Greenman
> > Applications Programming Manager
> >
> >
> ---------------------------------------------------------------------------------
>
> > 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

[login to unmask email]

Re: Help with CLOBs z/os 1.4 db2v7
(in response to James Campbell)
That is basically what the problem was. The data was being assigned
incorrectly and the length field was incorrect.
Thanks for all the help.

Bud Greenman
Applications Programming Manager



James Campbell
<[login to unmask email]
NK.NET.AU> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] Help with CLOBs z/os
1.4 db2v7

06/07/2007 08:11
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






I would look very closely at the actual data structure used for this. Is
someone using an SQLDA to declare the data structure, using a varchar
type structure for this data
ie 2 byte binary length of data
actual char data
, but moving 408 to the SQLDA SQLTYPE. 408 defines the host data as a
clob - but this requires a 4 byte length (not 2 as for varchar).
ie 4 byte binary length of data
actual char data
Having forgotten to move the actual length into the 2 byte length, DB2 now
sees the 4 byte length as x'00003f3c'.

James Campbell

On 6 Jun 2007 at 9:40, [login to unmask email] wrote:

> Well, we did find out what the problem is. The data that is being sent is
> XML and the first 2 characters are <? or hex '3f3c' in utf-8 which is
also
> equal to 15243 in decimal, ie: the first two char are being interpreted
as
> the length field of the varying length string. I think that this clob
> should really have been created as a blob, but we are still not sure how
to
> get around this issue.
>
>
> Bud Greenman
> Applications Programming Manager
>
>
>

> James Campbell

> <[login to unmask email]

> NK.NET.AU>
To
> Sent by: DB2 Data [login to unmask email]

> Base Discussion
cc
> List

> <[login to unmask email]
Subject
> ORG> Re: [DB2-L] Help with CLOBs z/os

> 1.4 db2v7

>

> 06/06/2007 09:10

> AM

>

>

> Please respond to

> DB2 Database

> Discussion list

> at IDUG

> <[login to unmask email]

> 2-L.ORG>

>

>

>
>
>
>
> Ah, the things that can go wrong.
>
> The first thing I notice is that ccsid=1208 is not an ascii ccsid - it is
> UTF-8
> (u+0000 TO U+007F are stored in a single byte which correspond to the
> ASCII characters; U+0080 upwards are stored as 2, 3 or even 4 bytes). It
> is possible that you've messed up the ccisd's - perhaps, depending on
your
> actual data you are reading 32000 bytes of data, presenting it to DB2
> saying "this is in utf-8", db2 decodes the data as utf-8, and decides
that
> there are actually 15243 unicode characters.
>
> I simply have no idea.
>
> James Campbell
>
> On 6 Jun 2007 at 8:16, [login to unmask email] wrote:
>
> > We are having problems with loading a CLOB in a pl/1 cics program.
> > The data coming in is about 32000 and when it is loaded into the table
it
> > comes out as 15243, apparently being truncated.
> > The host variable is declared as char(32000) varying.
> > The data is also being passed to an xml parser and there it is working
> > correctly, so the error appears to be in the db2 process
> > The clob is being brought in and stored as ascii (ccsid of 1208)
> > We need this to be working correctly in a few hours.
> > tia
> >
> > Bud Greenman
> > Applications Programming Manager
> >
> >
>
---------------------------------------------------------------------------------

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