1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2008, week 5
2 105 38_Re: Strange -805 after V7/V8 migration10_Meir Zohar19_zmeyer@BEZEQINT.NET31_Mon, 29 Dec 2008 07:48:05 +0200360_us-ascii Thanks James,
I've also seen that Willie Favero wrote a piece about this same zparm being "the one that will ruin your v8 migration" about three years ago.
Happy holidays
Meir Zohar CISSP, IBM Certified DBA for DB2 for z/OS V8/V9
Tel: +972 3 5747860 Fax: +972 3 5747864 Mob: +972 54 5747350 email: zmeyer@bezeqint.net [...]
108 50 22_Quiesce time in DB2 v814_Renzo razzetti24_renzo.razzetti@GMAIL.COM31_Mon, 29 Dec 2008 17:27:58 +0800792_ISO-8859-1 Hello
Have anyone experience a longer time in the Quiesce utility after upgrade to v8 ? Prior v8, our Quiesce took 10 seconds, but after upgrade it take 1 minute. Any idea ?
Regards
RR
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * 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
159 94 57_Re: Cardinality and distribution statistics DB2 v8.1 z/OS11_Diana Nagel16_dknagel@BEPC.COM31_Mon, 29 Dec 2008 10:29:51 -0600436_us-ascii Me too. Diana dknagel@bepc.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Friday, December 26, 2008 1:57 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS
Roger, I'd like a copy of that -- would you be willing to post it in the Code Place? Or just email it to me here? [...]
254 47 50_[LUW] db2pd: Rapidly Evolving DB2 Performance Tool23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Mon, 29 Dec 2008 18:10:52 -0600420_ISO-8859-1 http://www.ebenner.com/db2dba_blog/?p=442
db2pd is a relatively new DB2 LUW performance monitoring and troubleshooting tool introduced with DB2 version 8.2. Because it retrieves information from DB2 memory sets, it does not issue latches and is therefore a very low-cost means of monitoring DB2. I present an overview of new features that have been rolled out steadily since db2pd was introduced. [...]
302 56 26_Re: Quiesce time in DB2 v814_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Tue, 30 Dec 2008 01:24:29 +0000717_utf-8 Perhaps something to do with DIST roll up accounting meaning threads do not end as quickly?
Regards Av Friedman
On Mon, 29 Dec 2008 17:27:58 +0800, Renzo razzetti wrote:
>Hello > >Have anyone experience a longer time in the Quiesce utility after upgrade to >v8 ? >Prior v8, our Quiesce took 10 seconds, but after upgrade it take 1 minute. >Any idea ? > >Regards > >RR > >_________________________________________________________________ _____ > >* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events * >_________________________________________________________________ _____ > > > > >IDUG.org was recently updated requiring members to use a [...]
359 39 54_Re: [LUW] db2pd: Rapidly Evolving DB2 Performance Tool0_22_pgunning@GUNNINGTS.COM31_Tue, 30 Dec 2008 01:44:25 +0000416_- For a good presentation on this checkout my IDUG NA and EU presentations given over 3 yrs ago. There is also a section on it in my DB2 9 book. ------Original Message------ From: Jeffrey Benner, DB2 DBA Sender: DB2 Data Base Discussion List To: DB2-L@www.idugdb2-l.org ReplyTo: DB2 Database Discussion list at IDUG Sent: Dec 29, 2008 7:10 PM Subject: [DB2-L] [LUW] db2pd: Rapidly Evolving DB2 Performance Tool [...]
399 35 44_z-OS - Limiting the length of column results12_Mike Johnson26_mike.johnson@CONVERGYS.COM31_Tue, 30 Dec 2008 09:23:53 -0500262_US-ASCII 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
435 64 48_Re: z-OS - Limiting the length of column results14_Renico, Eugene20_Eugene.Renico@53.COM31_Tue, 30 Dec 2008 09:33:25 -0500535_us-ascii SELECT CAST(NAME AS CHAR(18)) AS NAME FROM SYSIBM.SYSROUTINES
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Johnson Sent: Tuesday, December 30, 2008 9:24 AM To: DB2-L@www.idugdb2-l.org 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? [...]
500 85 48_Re: z-OS - Limiting the length of column results13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 30 Dec 2008 09:48:31 -0500402_us-ascii 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 [...]
586 45 44_z-OS - Limiting the length of column results12_Mike Johnson26_mike.johnson@CONVERGYS.COM31_Tue, 30 Dec 2008 09:53:50 -0500404_US-ASCII 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
632 88 43_How to Remove Carriage Return from a Column13_Robert Badger24_robert.badger@AURORA.ORG31_Tue, 30 Dec 2008 09:57:27 -0600503_US-ASCII I have a column (HOURS) created as varchar(256) and it has multiple X'0D' carriage return). I am trying to remove the carriage return values from this column with the replace command.
Here is my SQL select a.location_id, replace(a.hours, X'0D25',X'4040') from s05dtdb.veap_location a
I am getting the following error:
Error: SQL0171N The data type, length or value of argument "1" of routine "REPLACE" is incorrect. SQLSTATE=42815 (State:42815, Native Code: FFFFFF55) [...]
721 153 48_Re: z-OS - Limiting the length of column results9_Ford Wong14_fordie@SHAW.CA31_Tue, 30 Dec 2008 09:47:26 -0700620_us-ascii Mike
Instead of using SUBSTR, use CHAR(column, n)
Happy New Year to all
Ford
----- Original Message ----- From: Mike Johnson Date: Tuesday, December 30, 2008 9:34 am Subject: [DB2-L] z-OS - Limiting the length of column results To: DB2-L@www.idugdb2-l.org
> 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 [...]
875 26 47_Re: How to Remove Carriage Return from a Column24_SUBSCRIBE DB2-L Muthuraj24_muthuraj1983@YAHOO.CO.IN31_Tue, 30 Dec 2008 17:59:15 +0000693_UTF-8 Hi Robert,
Please try Translate function in place of Replace. Hope it might work.
Regards, Muthu
______________________________________________________________________
* 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
902 475 48_Re: z-OS - Limiting the length of column results14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 30 Dec 2008 13:30:32 -0500372_us-ascii 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. [...]
1378 527 47_Re: How to Remove Carriage Return from a Column14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 30 Dec 2008 13:44:11 -05001021_us-ascii Bob,
I can't reproduce your error. Can you reproduce these results below with SPUFI or some other SQL processor?
********************************* Top of Data *********************
---------+---------+---------+---------+---------+---------+-------
SELECT REPLACE(HOURS,X'0D25',X'4040')
FROM
(
SELECT CAST('XXXX' || X'0D25' || 'XXXX' AS VARCHAR(256)) AS HOURS
FROM SYSIBM.SYSDUMMY1
) AS T1
;
---------+---------+---------+---------+---------+---------+-------
---------+---------+---------+---------+---------+---------+-------
XXXX XXXX
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
--Phil
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Badger Sent: Tuesday, December 30, 2008 10:57 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] How to Remove Carriage Return from a Column [...]
1906 148 47_Re: How to Remove Carriage Return from a Column9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 30 Dec 2008 13:33:57 -0600443_US-ASCII one of the restrictions of REPLACE and TRANSLATE is they won't take a null string. Does one of your rows have null value for that column?
Mike HLS Technologies
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Tuesday, December 30, 2008 12:44 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] How to Remove Carriage Return from a Column [...]
2055 400 48_Re: z-OS - Limiting the length of column results14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 30 Dec 2008 20:15:46 -0000573_iso-8859-1 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: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] z-OS - Limiting the length of column results [...]
2456 76 48_Re: z-OS - Limiting the length of column results13_Robert Badger24_robert.badger@AURORA.ORG31_Tue, 30 Dec 2008 15:08:26 -0600480_US-ASCII 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 robert.badger@aurora.org [...]
2533 143 16_Indexes not used19_Vanitha_Subramaniam30_Vanitha_Subramaniam@SATYAM.COM31_Wed, 31 Dec 2008 12:42:11 +0530357_us-ascii Hi all,
I am running Explain on all the queries accessing the table and found that some of the indexes are not used. We are on V8. In V9 we can assure this by SYSIBM.SYSINDEXSPACESTATS. LASTUSED column.
Is there a way that we can assure that the indexes are not used for accessing , then we can recommend for deleting the index. [...]
2677 172 48_Re: z-OS - Limiting the length of column results13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 31 Dec 2008 08:05:33 -0500499_us-ascii 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. [...]
2850 38 20_Re: Indexes not used24_SUBSCRIBE DB2-L Muthuraj24_muthuraj1983@YAHOO.CO.IN31_Wed, 31 Dec 2008 13:20:27 +0000478_UTF-8 Actually the table you have mentioned is used for RTS. Even in V8, you can enable the RTS and you can use the table.
But I have some questions about using this column. Definitely this column is useful. But to what extend?
If an index is not used for last 6 months, does it mean that it wont be used forever? Not at all. If you extract the information from mentioned table after 6 months, it will say you whether the index is used in last 6 months only. [...]
2889 88 20_Re: Indexes not used14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 31 Dec 2008 17:16:32 +0000599_utf-8 The short answer is no. Your analysis just provides information on most (not all) static access to the indexs. In addition provides no information on dynamic access.
There are things you can do too improve your comfort level about droping these indexs.
1. is the high level key in the index the same as any other high level key for indexes on the same table. Unless you jump through hoops with RUNSTATS and most people dont many optioizer funtions only liik at the high level key. 2. scan your application source libs including those on distributed platforms for the column [...]
2978 274 12_[FLUFF] Argh14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 31 Dec 2008 13:13:30 -0500424_us-ascii This is actually a test to see whether I'm still signed on to DB2-L. I was testing the sign-on, sign-off facilities and it looks like my signoff request was processed _after_ my subscribe request, which is the opposite of the order I sent them in.
On the other hand, if you're reading this, then the signoff must have been processed first, even if it was responded to second. Go figure. [...]
3253 117 20_Re: Indexes not used13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 31 Dec 2008 13:37:43 -0500408_UTF-8 I do not believe this information is limited to static SQL. My testing shows the date updated for dynamic access. The doc on the column says:
The date when the index is used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints
David Simpson Senior Technical Advisor Themis Training dsimpson@themisinc.com http://www.themisinc.com [...]
3371 88 16_Re: [FLUFF] Argh11_Mark (work)21_mlabby@AESSUCCESS.ORG31_Wed, 31 Dec 2008 14:07:55 -0500
3460 253 16_Re: [FLUFF] Argh13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 31 Dec 2008 14:37:09 -0500510_us-ascii Whom are you trying to kid, Mark??
Aren't you in cahoots with the list police?
All the best to all in the coming year.
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. [...]
3714 101 16_Re: [FLUFF] Argh7_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 31 Dec 2008 11:40:58 -0800614_us-ascii Happy New Year Phil and to everyone else.
Edward Long
--- On Wed, 12/31/08, Sevetson, Phil wrote:
> From: Sevetson, Phil > Subject: [DB2-L] [FLUFF] Argh > To: DB2-L@www.idugdb2-l.org > Date: Wednesday, December 31, 2008, 1:13 PM > This is actually a test to see whether I'm still signed > on to DB2-L. I > was testing the sign-on, sign-off facilities and it looks > like my > signoff request was processed _after_ my subscribe request, > which is the > opposite of the order I sent them in. > > > > On the other hand, if [...]
3816 24 48_Re: z-OS - Limiting the length of column results14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 31 Dec 2008 19:58:20 +0000802_UTF-8 > 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
3841 173 20_Re: Indexes not used14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 31 Dec 2008 20:06:42 +0000414_utf-8 As I read the original posting The lister 'determined' an index was not used by looking at explains for index references. When finding none we wanted to know if the RTS Lastused column could be used to verify that the index was not used.
My controburtion was the explain (or sysplandep or syspackdep) can not prove an index is not used because it only mesures SOME static SQL and no dynamic SQL. [...]
4015 198 20_Re: Indexes not used13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 31 Dec 2008 16:54:30 -0500307_UTF-8 Sorry, I misread your post. Certainly the plan table is of limited value in making this determination, especially if you have packages that haven't been bound in a long time or without EXPLAIN(YES) or ANY dynamic SQL. The RTS column should be helpful in filling some (but not all) of these gaps. [...]
4214 231 16_Re: [FLUFF] Argh13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 31 Dec 2008 16:12:43 -0600578_ISO-8859-1 I'm pretty sure the list police are out celebrating the coming of the New Year...
Enjoy
Willie
Mark (work) wrote: > > That is kinda like not knowing if you are coming or going? :-) > > I know the list police don't like too much [FLUFF] chatter, but > couldn't help responding... besides, it's a great time to send out > this little message... > > *Happy New Year!* > > ************************************************************************** > > > > > > > > > > This is actually a test to see whether I'm still signed on to DB2-L. > I was [...]
4446 28 18_The Coming of 200913_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 31 Dec 2008 16:14:18 -0600119_ISO-8859-1 To everyone a very Happy and Prosperous New Year.
Thanks for participating in the DB2-L list.