VARCHAR REXX

Loet polkamp

VARCHAR REXX
I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

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

Steen Rasmussen

Re: VARCHAR REXX
(in response to Loet polkamp)
It's a limitation of Dynamic SQL and strings.
You will need to INSERT the first 256 byte, and then execute a number of
UPDATE statements concatenating the string like this:

UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
.......


Kind regards
Steen Rasmussen
CA
Senior Consultant



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Loet Polkamp
Sent: Monday, December 19, 2005 5:36 AM
To: [login to unmask email]
Subject: [DB2-L] VARCHAR REXX

I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the
field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

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

Richard Fazio

Re: VARCHAR REXX
(in response to Steen Rasmussen)
You can also concatenate several small strings (up to 256 bytes)
together in one SQL stmt.

SQLSTMT = "INSERT INTO creator.table (COLUMN1) VALUES
('string1'CONCAT'string2'CONCAT'string3')"

string1, string2, string3 are all short strings

Change these values to Rexx variables and you can string together a
series of Rexx concatenations and SQL concatenations.

i.e. "VALUES('" || string1 || "' CONCAT '" || string2 || "' CONCAT '"
string3 "'"

A small DO loop would do nicely for dividing up the big string into
some small ones.

Best of luck,
faz


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 6:52:01 AM >>>
It's a limitation of Dynamic SQL and strings.
You will need to INSERT the first 256 byte, and then execute a number
of
UPDATE statements concatenating the string like this:

UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
.......


Kind regards
Steen Rasmussen
CA
Senior Consultant



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Loet Polkamp
Sent: Monday, December 19, 2005 5:36 AM
To: [login to unmask email]
Subject: [DB2-L] VARCHAR REXX

I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the
field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

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

Richard Fazio

Re: VARCHAR REXX
(in response to Richard Fazio)
You can also concatenate several small strings (up to 256 bytes)
together in one SQL stmt.

SQLSTMT = "INSERT INTO creator.table (COLUMN1) VALUES
('string1'CONCAT'string2'CONCAT'string3')"

string1, string2, string3 are all short strings

Change these values to Rexx variables and you can string together a
series of Rexx concatenations and SQL concatenations.

i.e. "VALUES('" || string1 || "' CONCAT '" || string2 || "' CONCAT '"
string3 "'"

A small DO loop would do nicely for dividing up the big string into
some small ones.

Best of luck,
faz


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 6:52:01 AM >>>
It's a limitation of Dynamic SQL and strings.
You will need to INSERT the first 256 byte, and then execute a number
of
UPDATE statements concatenating the string like this:

UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
......


Kind regards
Steen Rasmussen
CA
Senior Consultant



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Loet Polkamp
Sent: Monday, December 19, 2005 5:36 AM
To: [login to unmask email]
Subject: [DB2-L] VARCHAR REXX

I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the
field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

------------------------------------------------------------------------
---------
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: VARCHAR REXX
(in response to Richard Fazio)
Ok, I'll admit to being a little confused here. Unless I'm mistaken,
the 254 character limit is associated with the use of a literal, not the
use of dynamic SQL, correct? At least I know I can use a parameter
marker in dynamic SQL with a string well over 254 bytes. From the code
you passed along it would appear that you are using a parameter marker
so it seems like you should not need to jump through the hoops of
concatenating multiple 254-byte strings.
I do recall having a similar issue in the past with a different
product, but the details I can recall are pretty fuzzy -- Something (I
don't recall what, although I do know it was not DSNREXX) was not
recognizing the length of a varchar column and was truncating at 254
bytes unless the column was defined as a long varchar and we experienced
similar truncation (at the time we redefined the table in question to
use a long-varchar to work around the issue). You obviously could go
with the suggestions previously mentioned and concatenate multiple
254-byte chunks, but before going down that route I would be tempted to
try to get to the bottom of what's broke. Could be something obvious
like just verifying the lengths being set in the code, or just for fun
you could also try defining the column as a long-varchar just to see if
it's a similar issue (which at that point may indicate a defect). I
know I'm not being much help here, but it doesn't seem like you should
have to split this string up.

Mike.
________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Richard Fazio
Sent: Monday, December 19, 2005 7:59 AM
To: [login to unmask email]
Subject: Re: [DB2-L] VARCHAR REXX


You can also concatenate several small strings (up to 256 bytes)
together in one SQL stmt.

SQLSTMT = "INSERT INTO creator.table (COLUMN1) VALUES
('string1'CONCAT'string2'CONCAT'string3')"

string1, string2, string3 are all short strings

Change these values to Rexx variables and you can string together a
series of Rexx concatenations and SQL concatenations.

i.e. "VALUES('" || string1 || "' CONCAT '" || string2 || "' CONCAT '"
string3 "'"

A small DO loop would do nicely for dividing up the big string into some
small ones.

Best of luck,
faz


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 6:52:01 AM >>>
It's a limitation of Dynamic SQL and strings.
You will need to INSERT the first 256 byte, and then execute a number of
UPDATE statements concatenating the string like this:

UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
.......


Kind regards
Steen Rasmussen
CA
Senior Consultant



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Loet Polkamp
Sent: Monday, December 19, 2005 5:36 AM
To: [login to unmask email]
Subject: [DB2-L] VARCHAR REXX

I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the
field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

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

Richard Fazio

Re: VARCHAR REXX
(in response to Mike Vaughan)
Mike,

Good point. I was offering another dynamic solution. I'm not big on
parameter markers, it's more of a pain to set up (my opinion). So, I
recommended the loop.

However, you stressed "254" bytes. I new short strings had a limit in
V7...but, in the original note, the reference was to "256" bytes. I
went and looked and in the V7 reference it lists the restriction at
255...which is not really the point. "256" is really an odd number.

Mike - you're correct. It's a bug.
faz

I checked on IBMLink and found PQ74802
PTF List:
Release 61H : UQ79184 available 03/09/04 (F309 )
Release 71H : UQ79202 available 03/09/04 (F309 )

This looks like a hit...although, it's rather old.
PROBLEM SUMMARY:
****************************************************************
* USERS AFFECTED: DB2 for z/OS and OS/390 REXX Language *
* Support users *
****************************************************************
* PROBLEM DESCRIPTION: VARSTRING, LONG VARSTRING, VARGRAPHIC, *
* and LONG VARGRAPHIC length were set *
* to 256 when inserting data with *
* length > 256 using SQLDA. *
****************************************************************
* RECOMMENDATION: Apply corrective PTF when available *
****************************************************************
The length of the VARSTRING, LONG VARSTRING, VARGRAPHIC, and
LONG VARGRAPHIC were not set properly when using SQLDA to
insert VARSTRING or VARGRAPHIC longer than 256.


PROBLEM CONCLUSION:
Code was modified to set the correct length.


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 4:00:21 PM >>>

Ok, I'll admit to being a little confused here. Unless I'm
mistaken, the 254 character limit is associated with the use of a
literal, not the use of dynamic SQL, correct? At least I know I can use
a parameter marker in dynamic SQL with a string well over 254 bytes.
From the code you passed along it would appear that you are using a
parameter marker so it seems like you should not need to jump through
the hoops of concatenating multiple 254-byte strings.
I do recall having a similar issue in the past with a different
product, but the details I can recall are pretty fuzzy -- Something (I
don't recall what, although I do know it was not DSNREXX) was not
recognizing the length of a varchar column and was truncating at 254
bytes unless the column was defined as a long varchar and we experienced
similar truncation (at the time we redefined the table in question to
use a long-varchar to work around the issue). You obviously could go
with the suggestions previously mentioned and concatenate multiple
254-byte chunks, but before going down that route I would be tempted to
try to get to the bottom of what's broke. Could be something obvious
like just verifying the lengths being set in the code, or just for fun
you could also try defining the column as a long-varchar just to see if
it's a similar issue (which at that point may indicate a defect). I
know I'm not being much help here, but it doesn't seem like you should
have to split this string up.

Mike. From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Richard Fazio
Sent: Monday, December 19, 2005 7:59 AM
To: [login to unmask email]
Subject: Re: [DB2-L] VARCHAR REXX



You can also concatenate several small strings (up to 256 bytes)
together in one SQL stmt.

SQLSTMT = "INSERT INTO creator.table (COLUMN1) VALUES
('string1'CONCAT'string2'CONCAT'string3')"

string1, string2, string3 are all short strings

Change these values to Rexx variables and you can string together a
series of Rexx concatenations and SQL concatenations.

i.e. "VALUES('" || string1 || "' CONCAT '" || string2 || "' CONCAT '"
string3 "'"

A small DO loop would do nicely for dividing up the big string into
some small ones.

Best of luck,
faz


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 6:52:01 AM >>>
It's a limitation of Dynamic SQL and strings.
You will need to INSERT the first 256 byte, and then execute a number
of
UPDATE statements concatenating the string like this:

UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
.......


Kind regards
Steen Rasmussen
CA
Senior Consultant



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Loet Polkamp
Sent: Monday, December 19, 2005 5:36 AM
To: [login to unmask email]
Subject: [DB2-L] VARCHAR REXX

I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the
field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

------------------------------------------------------------------------
---------
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 orentity to which it is addressed, and may contain
information that isprivileged, confidential and exempt from disclosure
under applicable law.If you are not the intended recipient, any
dissemination, distribution orcopying of this communication is strictly
prohibited. If you havereceived this communication in error, please
notify us immediately byreply email to [login to unmask email] and delete
or destroy all copies ofthe original message and attachments thereto.
Email sent to or from thePrincipal Financial Group or any of its member
companies may be retainedas required by law or regulation.Nothing in
this message is intended to constitute an Electronic signaturefor
purposes of the Uniform Electronic Transactions Act (UETA) or
theElectronic 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

---------------------------------------------------------------------------------
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: VARCHAR REXX
(in response to Richard Fazio)
Oh yeah, I meant 256. Thanks for the clarification (I would say my reference to 254 was just a type-o, but it would have been a type-o 5 times and even I can pay more attention than that). Personally I like to encourage parameter markers due to dynamic caching implications (which may or may not matter in this particular case).

________________________________

From: DB2 Data Base Discussion List on behalf of Richard Fazio
Sent: Mon 12/19/2005 9:52 PM
To: [login to unmask email]
Subject: Re: [DB2-L] VARCHAR REXX


Mike,

Good point. I was offering another dynamic solution. I'm not big on parameter markers, it's more of a pain to set up (my opinion). So, I recommended the loop.

However, you stressed "254" bytes. I new short strings had a limit in V7...but, in the original note, the reference was to "256" bytes. I went and looked and in the V7 reference it lists the restriction at 255...which is not really the point. "256" is really an odd number.

Mike - you're correct. It's a bug.
faz

I checked on IBMLink and found PQ74802 < http://www-306.ibm.com/ibmlink/link2/sis/sisPage.jsp?applJsp=documentBrowse.jsp&navItem=sis.jsp&lc=en&cc=US&docNumber=PQ74802 >
PTF List:
Release 61H : UQ79184 < http://www-306.ibm.com/ibmlink/link2/sis/sisPage.jsp?applJsp=documentBrowse.jsp&navItem=sis.jsp&lc=en&cc=US&docNumber=UQ79184 > available 03/09/04 (F309 )
Release 71H : UQ79202 < http://www-306.ibm.com/ibmlink/link2/sis/sisPage.jsp?applJsp=documentBrowse.jsp&navItem=sis.jsp&lc=en&cc=US&docNumber=UQ79202 > available 03/09/04 (F309 )

This looks like a hit...although, it's rather old.
PROBLEM SUMMARY:
****************************************************************
* USERS AFFECTED: DB2 for z/OS and OS/390 REXX Language *
* Support users *
****************************************************************
* PROBLEM DESCRIPTION: VARSTRING, LONG VARSTRING, VARGRAPHIC, *
* and LONG VARGRAPHIC length were set *
* to 256 when inserting data with *
* length > 256 using SQLDA. *
****************************************************************
* RECOMMENDATION: Apply corrective PTF when available *
****************************************************************
The length of the VARSTRING, LONG VARSTRING, VARGRAPHIC, and
LONG VARGRAPHIC were not set properly when using SQLDA to
insert VARSTRING or VARGRAPHIC longer than 256.


PROBLEM CONCLUSION:
Code was modified to set the correct length.


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 4:00:21 PM >>>

Ok, I'll admit to being a little confused here. Unless I'm mistaken, the 254 character limit is associated with the use of a literal, not the use of dynamic SQL, correct? At least I know I can use a parameter marker in dynamic SQL with a string well over 254 bytes. From the code you passed along it would appear that you are using a parameter marker so it seems like you should not need to jump through the hoops of concatenating multiple 254-byte strings.
I do recall having a similar issue in the past with a different product, but the details I can recall are pretty fuzzy -- Something (I don't recall what, although I do know it was not DSNREXX) was not recognizing the length of a varchar column and was truncating at 254 bytes unless the column was defined as a long varchar and we experienced similar truncation (at the time we redefined the table in question to use a long-varchar to work around the issue). You obviously could go with the suggestions previously mentioned and concatenate multiple 254-byte chunks, but before going down that route I would be tempted to try to get to the bottom of what's broke. Could be something obvious like just verifying the lengths being set in the code, or just for fun you could also try defining the column as a long-varchar just to see if it's a similar issue (which at that point may indicate a defect). I know I'm not being much help here, but it doesn't seem like you should have to split this string up.

Mike.
________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Richard Fazio
Sent: Monday, December 19, 2005 7:59 AM
To: [login to unmask email]
Subject: Re: [DB2-L] VARCHAR REXX


You can also concatenate several small strings (up to 256 bytes) together in one SQL stmt.

SQLSTMT = "INSERT INTO creator.table (COLUMN1) VALUES ('string1'CONCAT'string2'CONCAT'string3')"

string1, string2, string3 are all short strings

Change these values to Rexx variables and you can string together a series of Rexx concatenations and SQL concatenations.

i.e. "VALUES('" || string1 || "' CONCAT '" || string2 || "' CONCAT '" string3 "'"

A small DO loop would do nicely for dividing up the big string into some small ones.

Best of luck,
faz


Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-19 6:52:01 AM >>>
It's a limitation of Dynamic SQL and strings.
You will need to INSERT the first 256 byte, and then execute a number of
UPDATE statements concatenating the string like this:

UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
UPDATE table SET VC-COLUMN = VC-COLUMN-256 concat next-256-byte;
.......


Kind regards
Steen Rasmussen
CA
Senior Consultant



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Loet Polkamp
Sent: Monday, December 19, 2005 5:36 AM
To: [login to unmask email]
Subject: [DB2-L] VARCHAR REXX

I'm trying to insert a row into a VARCHAR(4000)-column within a REXX
program. I do it like this:

INSQLDA.1.SQLDATA=inputdata /* Value in variable */
INSQLDA.SQLD=1 /* SQLDA contains one variable */
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
INSQLDA.1.SQLIND=0 /* Input variable is not null */
INSQLDA.1.SQLLEN=length(inputdata)
SQL_stmt="Insert into TABLE ( COLUMN1 ) values ( ? )"
ADDRESS DSNREXX "EXECSQL PREPARE S100 FROM :SQL_stmt"
ADDRESS DSNREXX "EXECSQL EXECUTE S100 USING DESCRIPTOR :INSQLDA"

In the result column (COLUMN1) only the first 256 characters of the
field
INUPTDATA are inserted. Can someone tell me what I do wrong?

TIA

Loet Polkamp.

------------------------------------------------------------------------
---------

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

Antoon Vekeman

Re: VARCHAR REXX
(in response to Mike Vaughan)
INSQLDA.1.SQLTYPE=449 /* VARCHAR datatype */
What would be the effect of setting the SQLTYPE to 457 (long VARCHAR)?

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