z-OS - Limiting the length of column results

Mike Johnson

z-OS - Limiting the length of column results
Is there a way to limit the selected results from a column to just the
first x-number of characters from that column? For example, just the first
18 characters from the NAME column in SYSIBM.SYSROUTINES?

Thanks.

Mike Johnson
Convergys Corp
--
"NOTICE: The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or
entity to which it is directed and may contain information that is
privileged or otherwise confidential. If you have received this electronic
mail transmission in error, please delete it from your system without
copying or forwarding it, and notify the sender of the error by reply email
or by telephone (collect), so that the sender's address records can be
corrected."

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Eugene Renico

Re: z-OS - Limiting the length of column results
(in response to Mike Johnson)
SELECT CAST(NAME AS CHAR(18)) AS NAME
FROM SYSIBM.SYSROUTINES

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Mike Johnson
Sent: Tuesday, December 30, 2008 9:24 AM
To: [login to unmask email]
Subject: z-OS - Limiting the length of column results

Is there a way to limit the selected results from a column to just the
first x-number of characters from that column? For example, just the
first
18 characters from the NAME column in SYSIBM.SYSROUTINES?

Thanks.

Mike Johnson
Convergys Corp
--
"NOTICE: The information contained in this electronic mail transmission
is intended by Convergys Corporation for the use of the named individual
or entity to which it is directed and may contain information that is
privileged or otherwise confidential. If you have received this
electronic mail transmission in error, please delete it from your system
without copying or forwarding it, and notify the sender of the error by
reply email or by telephone (collect), so that the sender's address
records can be corrected."

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events
* ______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Seibert

Re: z-OS - Limiting the length of column results
(in response to Eugene Renico)
Good morning Mike.

Are you looking for something more than the SUBSTR function?
Below is an example of the use SUBSTR to do just what you want, I
think.

When V8 came along with the long names, to accommodate my laziness and
impatience, I created several views of catalog tables to only return a
portion of the long names.
Here's the beginning of the SYSROUTINES CREATE VIEW

CREATE VIEW S.SROUTINES AS
SELECT
SUBSTR(SCHEMA ,1,30) SCHEMA ,
SUBSTR(OWNER ,1,30) OWNER ,
SUBSTR(NAME ,1,30) NAME ,
ROUTINETYPE ,
SUBSTR(CREATEDBY ,1,30) CREATEDBY ,
SUBSTR(SPECIFICNAME,1,30) SPECIFICNAME ,
ROUTINEID ,
RETURN_TYPE ,
ORIGIN ,
FUNCTION_TYPE ,
PARM_COUNT ,
LANGUAGE ,
SUBSTR(COLLID ,1,30) COLLID ,
SUBSTR(SOURCESCHEMA ,1,30) SOURCESCHEMA ,
SUBSTR(SOURCESPECIFIC ,1,30) SOURCESPECIFIC ,
DETERMINISTIC ,
EXTERNAL_ACTION ,
NULL_CALL ,
CAST_FUNCTION ,
SCRATCHPAD ,
SCRATCHPAD_LENGTH ,
FINAL_CALL ,
PARALLEL ,
PARAMETER_STYLE ,
FENCED ,
SQL_DATA_ACCESS ,
DBINFO ,
STAYRESIDENT ,
ASUTIME ,


Dave


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.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Mike Johnson
Sent: Tuesday, December 30, 2008 9:24 AM
To: [login to unmask email]
Subject: [DB2-L] z-OS - Limiting the length of column results

Is there a way to limit the selected results from a column to just the
first x-number of characters from that column? For example, just the
first
18 characters from the NAME column in SYSIBM.SYSROUTINES?

Thanks.

Mike Johnson
Convergys Corp
--

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mike Johnson

z-OS - Limiting the length of column results
(in response to David Seibert)
All,

Found the answer. It's the SUBSTR clause.

Regards and Happy New Year.

Mike Johnson

__________________

Is there a way to limit the selected results from a column to just the
first x-number of characters from that column? For example, just the first
18 characters from the NAME column in SYSIBM.SYSROUTINES?

Thanks.

Mike Johnson
Convergys Corp
--
"NOTICE: The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or
entity to which it is directed and may contain information that is
privileged or otherwise confidential. If you have received this electronic
mail transmission in error, please delete it from your system without
copying or forwarding it, and notify the sender of the error by reply email
or by telephone (collect), so that the sender's address records can be
corrected."

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Ford Wong

Re: z-OS - Limiting the length of column results
(in response to Mike Johnson)
Mike

Instead of using SUBSTR, use CHAR(column, n)

Happy New Year to all

Ford

----- Original Message -----
From: Mike Johnson <[login to unmask email]>
Date: Tuesday, December 30, 2008 9:34 am
Subject: [DB2-L] z-OS - Limiting the length of column results
To: [login to unmask email]

> All,
>
> Found the answer. It's the SUBSTR clause.
>
> Regards and Happy New Year.
>
> Mike Johnson
>
> __________________
>
> Is there a way to limit the selected results from a column to
> just the
> first x-number of characters from that column? For
> example, just the first
> 18 characters from the NAME column in SYSIBM.SYSROUTINES?
>
> Thanks.
>
> Mike Johnson
> Convergys Corp
> --
> "NOTICE: The information contained in this electronic mail
> transmission is
> intended by Convergys Corporation for the use of the named
> individual or
> entity to which it is directed and may contain information that is
> privileged or otherwise confidential. If you have received
> this electronic
> mail transmission in error, please delete it from your system without
> copying or forwarding it, and notify the sender of the error by
> reply email
> or by telephone (collect), so that the sender's address records
> can be
> corrected."
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March *
> http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new
> password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your
> member profile. If you are not already an IDUG.org member,
> please register at http://www.idug.org/component/juser/register.html
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: z-OS - Limiting the length of column results
(in response to Ford Wong)
CHAR(column, n) works better for me. When I use anything except SUBSTR
or CHAR to limit length or otherwise construct a character-type result
column, I find that the datatype is cast as VARCHAR. This can be
inconvenient when I'm trying to use the SQL to construct, say, a SQL or
JCL statement using DSNTIAUL to write an output file of executable
statements.



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ford Wong
Sent: Tuesday, December 30, 2008 11:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] z-OS - Limiting the length of column results



Mike



Instead of using SUBSTR, use CHAR(column, n)



Happy New Year to all



Ford

----- Original Message -----
From: Mike Johnson <[login to unmask email]>
Date: Tuesday, December 30, 2008 9:34 am
Subject: [DB2-L] z-OS - Limiting the length of column results
To: [login to unmask email]

> All,
>
> Found the answer. It's the SUBSTR clause.
>
> Regards and Happy New Year.
>
> Mike Johnson
>
> __________________
>
> Is there a way to limit the selected results from a column to
> just the
> first x-number of characters from that column? For
> example, just the first
> 18 characters from the NAME column in SYSIBM.SYSROUTINES?
>
> Thanks.
>
> Mike Johnson
> Convergys Corp
> --
> "NOTICE: The information contained in this electronic mail
> transmission is
> intended by Convergys Corporation for the use of the named
> individual or
> entity to which it is directed and may contain information that is
> privileged or otherwise confidential. If you have received
> this electronic
> mail transmission in error, please delete it from your system without
> copying or forwarding it, and notify the sender of the error by
> reply email
> or by telephone (collect), so that the sender's address records
> can be
> corrected."
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March *
> http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new
> password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your
> member profile. If you are not already an IDUG.org member,
> please register at http://www.idug.org/component/juser/register.html
>



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========



______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Phil Grainger

Re: z-OS - Limiting the length of column results
(in response to Philip Sevetson)
Notice we have fallen into the trap of thinking "characters = bytes".....

if the OPs data is EBCDIC or ASCII, then any of the suggested options will work

BUT if anyone is looking to subset UNICODE characters strings by using SUBSTR might be in for some surprises!!

Happy New Year

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Sevetson, Phil
Sent: Tue 30/12/2008 18:30
To: [login to unmask email]
Subject: Re: [DB2-L] z-OS - Limiting the length of column results



CHAR(column, n) works better for me. When I use anything except SUBSTR or CHAR to limit length or otherwise construct a character-type result column, I find that the datatype is cast as VARCHAR. This can be inconvenient when I'm trying to use the SQL to construct, say, a SQL or JCL statement using DSNTIAUL to write an output file of executable statements.



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ford Wong
Sent: Tuesday, December 30, 2008 11:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] z-OS - Limiting the length of column results



Mike



Instead of using SUBSTR, use CHAR(column, n)



Happy New Year to all



Ford

----- Original Message -----
From: Mike Johnson <[login to unmask email]>
Date: Tuesday, December 30, 2008 9:34 am
Subject: [DB2-L] z-OS - Limiting the length of column results
To: [login to unmask email]

> All,
>
> Found the answer. It's the SUBSTR clause.
>
> Regards and Happy New Year.
>
> Mike Johnson
>
> __________________
>
> Is there a way to limit the selected results from a column to
> just the
> first x-number of characters from that column? For
> example, just the first
> 18 characters from the NAME column in SYSIBM.SYSROUTINES?
>
> Thanks.
>
> Mike Johnson
> Convergys Corp
> --
> "NOTICE: The information contained in this electronic mail
> transmission is
> intended by Convergys Corporation for the use of the named
> individual or
> entity to which it is directed and may contain information that is
> privileged or otherwise confidential. If you have received
> this electronic
> mail transmission in error, please delete it from your system without
> copying or forwarding it, and notify the sender of the error by
> reply email
> or by telephone (collect), so that the sender's address records
> can be
> corrected."
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March *
> http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new
> password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your
> member profile. If you are not already an IDUG.org member,
> please register at http://www.idug.org/component/juser/register.html
>



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >

________________________________

=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========




________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Rob Badger

Re: z-OS - Limiting the length of column results
(in response to Phil Grainger)
Thanks all for your time and assistance.

I finally looked at the data and found the field I needed to replace was a
hex(25).

Here is my update SQL:

select location_id, replace(cast(COALESCE(HOURS, ' ') as char(250)),
X'25',X'40')
from s05dtdb.veap_location a
fetch first 100 row only


Robert Badger
Database Administrator
Aurora Health Care Inc.
3031 West Montana Street
Milwaukee, WI 53215
(414) 389-2589
[login to unmask email]

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Seibert

Re: z-OS - Limiting the length of column results
(in response to Rob Badger)
Hello Ford

Are there reasons other than unicode for your recommendation to use the
CHAR function rather than SUBSTR?

Thanks

Dave


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.


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ford Wong
Sent: Tuesday, December 30, 2008 11:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] z-OS - Limiting the length of column results


Mike

Instead of using SUBSTR, use CHAR(column, n)

Happy New Year to all

Ford

----- Original Message -----
From: Mike Johnson <[login to unmask email]>
Date: Tuesday, December 30, 2008 9:34 am
Subject: [DB2-L] z-OS - Limiting the length of column results
To: [login to unmask email]

> All,
>
> Found the answer. It's the SUBSTR clause.
>
> Regards and Happy New Year.
>
> Mike Johnson
>
> __________________
>
> Is there a way to limit the selected results from a column to
> just the
> first x-number of characters from that column? For
> example, just the first
> 18 characters from the NAME column in SYSIBM.SYSROUTINES?
>
> Thanks.
>
> Mike Johnson


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Vanroose

Re: z-OS - Limiting the length of column results
(in response to David Seibert)
> Are there reasons other than unicode for your recommendation to
> use the CHAR function rather than SUBSTR?

That wouldn't make a difference: "CHAR(...,n)" is actually returning n
bytes, not characters (with e.g. UTF-8).


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html