working with v8

Bill Brown

working with v8
we have v8 new function mode installed on a test lpar and i started playing
around with it. i updated my plan table for all of the new column lengths
and new columns and started playing around with explain. now the fun part.
just using one column in my example, progname, which changed from char(8)
to varchar(128).

i print out the plan table columns and progname is padded to the right with
spaces.

i do a select on progname with the length function and it has a length of 8.

i print the column using the strip function and the spaces are not removed.

i print the column using substr(progname,1,length(progname)) and the spaces
are not removed.

i knew that this type of thing was going to cause some interesting fun and
here it is. does anyone have any tips or tricks to resolve this type of
problem???

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

Re: working with v8
(in response to Bill Brown)
What I did was change all my selects to substr(progname,1,8) as progname
,etc
SPUFI and TEP2 all space the columns based on the maximum size defined for
the table. The SUBSTR changes the maximum size returned.
STRIP removes the trailing blanks (which mostly don't exist) but won't
change the maximum size. At some point, progname, creator id's etc will
exist with lengths greater than 8 but by then who knows what version of DB2
and Z/OS we will be on.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bill Brown
Sent: Wednesday, January 04, 2006 9:38 AM
To: [login to unmask email]
Subject: [DB2-L] working with v8

we have v8 new function mode installed on a test lpar and i started playing
around with it. i updated my plan table for all of the new column lengths
and new columns and started playing around with explain. now the fun part.
just using one column in my example, progname, which changed from char(8)
to varchar(128).

i print out the plan table columns and progname is padded to the right with
spaces.

i do a select on progname with the length function and it has a length of 8.

i print the column using the strip function and the spaces are not removed.

i print the column using substr(progname,1,length(progname)) and the spaces
are not removed.

i knew that this type of thing was going to cause some interesting fun and
here it is. does anyone have any tips or tricks to resolve this type of
problem???

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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


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

Agus Kwee

Re: working with v8
(in response to Mike Bell)
I think the reason for SPUFI for instance to define the output size
for substr(progname,1,length(progname)) as the maximum size (128)
is because it already has to reserve an output area to print the
columns of first result row, while it still doesn't know the length of
the values of progname column of the subsequent result rows
that it has not fetched yet.
Substr(progname,1,8) tells SPUFI that we are only interested to see
the first 8 characters of the progname column regardless of the actual
size in the progname column of all result rows.

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com

----- Original Message -----
From: Mike Bell <[login to unmask email]>
Date: Wednesday, January 4, 2006 11:08 am
Subject: Re: [DB2-L] working with v8

> What I did was change all my selects to substr(progname,1,8) as
> progname,etc
> SPUFI and TEP2 all space the columns based on the maximum size
> defined for
> the table. The SUBSTR changes the maximum size returned.
> STRIP removes the trailing blanks (which mostly don't exist) but won't
> change the maximum size. At some point, progname, creator id's
> etc will
> exist with lengths greater than 8 but by then who knows what
> version of DB2
> and Z/OS we will be on.
>
> Mike Bell
> HLS Technologies
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
> On Behalf
> Of Bill Brown
> Sent: Wednesday, January 04, 2006 9:38 AM
> To: [login to unmask email]
> Subject: [DB2-L] working with v8
>
> we have v8 new function mode installed on a test lpar and i
> started playing
> around with it. i updated my plan table for all of the new column
> lengthsand new columns and started playing around with explain.
> now the fun part.
> just using one column in my example, progname, which changed from
> char(8)to varchar(128).
>
> i print out the plan table columns and progname is padded to the
> right with
> spaces.
>
> i do a select on progname with the length function and it has a
> length of 8.
>
> i print the column using the strip function and the spaces are not
> removed.
> i print the column using substr(progname,1,length(progname)) and
> the spaces
> are not removed.
>
> i knew that this type of thing was going to cause some interesting
> fun and
> here it is. does anyone have any tips or tricks to resolve this
> type of
> problem???
>
> -------------------------------------------------------------------
> ---------
> -----
> 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]
> l.org. Find
> out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>
> -------------------------------------------------------------------
> --------------
> 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

David Seibert

Re: working with v8
(in response to Agus Kwee)
I created views with

substr(xxx,1,??) as xxx

as Mike mentioned for all the catalog tables I use heavily. You could do
the same for plan_table.

When I need to see > 8 or 30, I use the underlying tables.

Dave




-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mike Bell
Sent: Wednesday, January 04, 2006 11:09 AM
To: [login to unmask email]
Subject: Re: [DB2-L] working with v8


What I did was change all my selects to substr(progname,1,8) as progname
,etc
SPUFI and TEP2 all space the columns based on the maximum size defined for
the table. The SUBSTR changes the maximum size returned.
STRIP removes the trailing blanks (which mostly don't exist) but won't
change the maximum size. At some point, progname, creator id's etc will
exist with lengths greater than 8 but by then who knows what version of DB2
and Z/OS we will be on.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bill Brown
Sent: Wednesday, January 04, 2006 9:38 AM
To: [login to unmask email]
Subject: [DB2-L] working with v8

we have v8 new function mode installed on a test lpar and i started playing
around with it. i updated my plan table for all of the new column lengths
and new columns and started playing around with explain. now the fun part.
just using one column in my example, progname, which changed from char(8) to
varchar(128).

i print out the plan table columns and progname is padded to the right with
spaces.

i do a select on progname with the length function and it has a length of 8.

i print the column using the strip function and the spaces are not removed.

i print the column using substr(progname,1,length(progname)) and the spaces
are not removed.

i knew that this type of thing was going to cause some interesting fun and
here it is. does anyone have any tips or tricks to resolve this type of
problem???

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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


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



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

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