1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2009, week 2 2 32 37_total CPU utilization for DDF threads6_Iriani20_menik.ri89@YAHOO.COM30_Fri, 8 May 2009 00:42:24 +0000434_ISO-8859-1 Hello,

Any body can share on how to calculate total CPU utilization for threads that used DB2 distributed data facility? Can we obtain the number of total CPU usage from nnnnDIST only? I mean we get this number from RMF.

Thank you

Regards Iriani

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 35 102 24_Re: SORT COMPONENT RC=1611_Nick Cianci19_ncianci@AU1.IBM.COM30_Fri, 8 May 2009 11:50:11 +1000563_UTF-8 Hi Agreed, whilst not related to RTS, we had problem in our test subsystems where we refresh (recover / rebuild) the environments data to a baseline.

We also apply Production stats for binds! With Syncsort no problems ... works beautifully using dynamic allocation. Along came DB2 v8 & DFSORT and we get massive space allocations and x37 abends left right & centre on the rebuilds. There is just no way to win here; if we Runstats the environment we don't get indicative APs, we use prod stats and we either need to buy or a new DASD farm or go [...] 138 81 61_Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)11_Roy Boxwell16_R.Boxwell@SEG.DE30_Fri, 8 May 2009 07:42:31 +0200594_ISO-8859-1 although I am still waiting to see if there even *is* 2.5 hours of kiwi music..... :)





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 220 97 41_Antwort: Re: [DB2-L] SORT COMPONENT RC=1611_Roy Boxwell16_R.Boxwell@SEG.DE30_Fri, 8 May 2009 07:53:54 +0200361_ISO-8859-1 I also still have my "DFSORT survival guide" culled from various sources and my every handy "RTS and RUNSTATS APAR reference sheet" that I keep updated every month so that I know where all the nasties are and how to correct 'em Has saved my life a few times...

as usual if anyone is interested drop me a mail and I will send 'em all out! [...] 318 254 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 8 May 2009 10:17:29 +0200447_iso-8859-1 Well, I did struggle after 30 minutes... (pun unintended)

Happy Friday!



Raymond

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: 08 May 2009 06:43 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)



although I am still waiting to see if there even *is* 2.5 hours of kiwi music..... :) [...] 573 61 21_Re: copytocopy advice12_Tal Lifshitz27_Tal.Lifshitz@UBS-HAINER.COM30_Fri, 8 May 2009 11:29:00 +0300590_ISO-8859-1 Nick, If the table had no changes the image copy is allocated and is left empty but it is not recorded in SYSCOPY. I am guessing you are using COPYTOCOPY with the FROMCOPY option and specify the recently allocated empty image copy. This is causing COPYTOCOPY to fail with an error message that indicate that the image copy is not recorded in SYSCOPY. To avoid this problem you can do the following: 1) Use real time statistics to identify which tablespaces have been changed and copy only these tablespaces. This way you never allocate obsolete image copies. 2) Use SYSCOPY [...] 635 145 31_Accessing DB2 z/OS from Eclipse9_Stan Hoey22_shoey@CIRCLE-GROUP.COM30_Fri, 8 May 2009 10:00:51 +0100345_us-ascii I am being dragged, kicking and screaming, into Aldous Huxley's Brave New World. I promise to wash my mouth out immediately after I ask this question:

"Has anyone successfully accessed a DB2 z/OS system from an Eclipse platform using the SQL Explorer plugin?". If so, can you please tell me how to configure the connection. [...] 781 240 35_Re: Accessing DB2 z/OS from Eclipse25_Sorensen Henrik (KCAA 32)33_henrik.sorensen@CREDIT-SUISSE.COM30_Fri, 8 May 2009 11:26:30 +0200543_us-ascii Hi Stan,

No need to turn this into a drama or even soap opera.

Do you already have the DB2 Connect installed ? You will need to have a proper license key to access DB2 z/OS via JDBC.

Other than that its completely trouble free.

Good luck Henrik



________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Stan Hoey Sent: Friday, May 08, 2009 11:01 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Accessing DB2 z/OS from Eclipse [...] 1022 315 35_Re: Accessing DB2 z/OS from Eclipse9_Stan Hoey22_shoey@CIRCLE-GROUP.COM30_Fri, 8 May 2009 10:56:43 +0100456_us-ascii Hi Henrik

I don't have DB2 Connect installed - I guess this is where I go next. Many thanks

BTW - are you using SQLExplorer/Eclipse to access z/OS?

Stan

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sorensen Henrik (KCAA 32) Sent: Friday, May 08, 2009 10:27 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Accessing DB2 z/OS from Eclipse [...] 1338 286 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)14_Leblanc, Fritz27_fritz.leblanc@SALLIEMAE.COM30_Fri, 8 May 2009 07:47:23 -0400410_iso-8859-1 Just imagine 2.5 hours of didgeridoo music.

:)

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Friday, May 08, 2009 1:43 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)



although I am still waiting to see if there even *is* 2.5 hours of kiwi music..... :) [...] 1625 245 82_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)11_Roy Boxwell16_R.Boxwell@SEG.DE30_Fri, 8 May 2009 13:51:17 +0200628_ISO-8859-1 the horror .. the horror...





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert







"Leblanc, Fritz" Gesendet von: DB2 Data Base Discussion List 08.05.2009 13:47 Bitte antworten an DB2 Database Discussion list at IDUG [...] 1871 97 35_Re: Accessing DB2 z/OS from Eclipse10_Todd Burch17_toddburch@MAC.COM30_Fri, 8 May 2009 07:26:10 -0500366_US-ASCII Stan, I wrote a Java app in Eclipse, under Windows XP, that accesses z/ OS using the Type 4 driver. I do not have DB2 Connect installed, or DB2 LUW either. Work's great. I used the sample app from the front of the DB2 Application Programming Guide for Java as a basis to get started. I've used it as the basis for several different recreate scenarios. [...] 1969 397 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)10_Roger Hecq18_Roger.Hecq@UBS.COM30_Fri, 8 May 2009 08:54:01 -0400382_us-ascii Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...] 2367 687 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)10_John Abell36_john.abell@INTNLSOFTWAREPRODUCTS.COM30_Fri, 8 May 2009 09:04:28 -0400738_iso-8859-1 Just wait till all of those people that hang by their feet wake up and read all of this. :-)









Yours truly,

John Abell

International Software Products

North America: 1-800-295-7608 Ext: 224

International: 1-416-593-5578 Ext: 224





This email may contain confidential and privileged material for the sole use of the intended recipient(s). Any review, use, retention, distribution or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive on behalf of the named recipient), please contact the sender by reply email and delete all copies of this message. Also, email is susceptible to [...] 3055 354 26_Re: Insert BLOB using REXX14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 8 May 2009 23:26:07 +1000567_US-ASCII The normal way to read data into a REXX program is execio.

REXX variables are limited to 16MB in size. This might be an issue with BLOBs.

This is coded directly into this response - you may have to debug it:

sd.sqld = 3 sd.1.sqltype = 496 /* integer ? */ sd.2.sqltype = 448 /* varchar ? */ sd.3.sqltype = 404 /* blob */ sd.1.sqldata = cdv sd.2.sqldata = fin sd.3.sqldata = con sd.2.sqllen = length(sd.1.sqldata) sd.2.sqllen = length(sd.2.sqldata) sd.2.sqllen = length(sd.3.sqldata) ... "execsql execute s3 using descriptor :sd" ... [...] 3410 666 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 8 May 2009 15:35:40 +0200423_iso-8859-1 You're all toe rags of the highest order. ;o) I mean, I try to help a fellow IT professional and you extract the Michael on the vibrant and thriving Austra.., er, New Zealand music scene. Disbelief, as my pro-pat (is that a word?) friends might say. Mind you, feel free to ridicule my trans-Tasman (aka upside-down hanging?) friends. It is the national sport in NZ, after all. And you thought it was rugby. [...] 4077 360 26_Re: Insert BLOB using REXX14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Fri, 8 May 2009 15:41:11 +0200472_us-ascii I use another way :





Address DSNREXX "EXECSQL SET CURRENT PACKAGESET='DSNREXUR'"

if RC<0 Then CALL #FIN 'd2ax0014.08 - ' TSOMSG1

if sqlcode <> 0 then call sqlca 'PACKSET' 'PACK'





Selectstatement = '' ,

"INSERT INTO SYSIBM.SYSCOLDIST ",

"( FREQUENCYF, FREQUENCY, IBMREQD, TBOWNER, TBNAME, NAME , ",

" COLVALUE, TYPE ) ",

"VALUES ( 0,01 , 0,01 , 'N', 'GGR', 'GGRT_ISIO', ", [...] 4438 958 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 8 May 2009 14:54:50 +0100577_iso-8859-1 Anyway, isn't the digeridoo a native of AUSTRALIA ????





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





2009 IBM Data Champion





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179. [...] 5397 313 65_Re: Antwort: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 8 May 2009 14:55:57 +0100592_us-ascii PS PMR raised with IBM (75627,7TD,000) and they have recreated my abend





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





2009 IBM Data Champion





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179. [...] 5711 29 14_SQL0976N Error8_Ward Fry12_fryw@USA.NET30_Fri, 8 May 2009 14:06:08 +0000612_windows-1252 We are running DB2 LUW 9.5 on Windows. We are getting the following message:

SQL0976N The diskette drive is open. SQLSTATE=57021

Messages manual gives no further explanation. Have never seen this message before.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 5741 22 41_Re: total CPU utilization for DDF threads10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 May 2009 16:51:40 +0000677_windows-1252 In SMF you can aggregate the data by connection type.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "The most important thing I always find at IDUG is the revisiting of old assumptions, with new functions and features." _____________________________________________________________________ 5764 84 35_Re: Accessing DB2 z/OS from Eclipse10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 May 2009 16:55:29 +0000318_windows-1252 Hi Stan,

As has already been said the first thing you need to do is get a conenction to z/os workign using the universal driver and license jar. Once that works yo need to add those two files to your eclipse configuration. I have done that in the past but I don't have a current installation. [...] 5849 232 31_Re: ENCRYPTION of Credit number9_Raj Ghose17_raj.ghose@RBC.COM30_Fri, 8 May 2009 17:08:13 +0000350_windows-1252 Hi List, First of all I would like to thank you all who took their time to reply to my request. Sorry for the delay in replying.

The requirement is that the CRC (Credit Card #) must be encrypted on DASD. Nobody should have SPUFI access to display this column unless authorized. This means that DB2 compression will not work. [...] 6082 565 28_Inactive DBATs - DB2 z/OS V812_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Fri, 8 May 2009 12:28:47 -0500432_ISO-8859-1 Hello everyone,





We're seeing what we believe to be a high number of inactive DBATs hanging around in our production subsystem and we're curious as to why, and what we can do to reduce them (assuming we should).





Here are our zparm settings:





Commit Status (CMTSTAT) = INACTIVE

Idle Timeout (IDTHTOIN) = 300

Max Type1 Inactive Thds = 0 [...] 6648 236 25_DIAGPATH filesystem setup0_34_ravi.khandelwal@THOMSONREUTERS.COM30_Fri, 8 May 2009 13:42:58 -0500436_us-ascii Hello Listers,





This is a question for DB2 DBAs on Linux and Unix.





We have a db2 environment with multiple instances per server. Each instance home is on a 500 MB filesystem and shares space with the DB2DUMP directory. We are thinking of moving the DB2DUMP directories to a centralized filesystem (2GB size) so that each instance will have more space to write diagnostic data. [...] 6885 176 39_Re: R: Rotate Partition & Compression %16_mocion@libero.it16_mocion@LIBERO.IT30_Fri, 8 May 2009 14:11:53 -0500297_windows-1252 Which could be not good enough if dictionary was built in the past. As said I'd reorg without keepdictionary after a significant number of rows have been inserted. I think % compression should be higher but you can use DSN1COMP to evaluate % of compression using an IC as input. [...] 7062 176 40_allocationg SMS managed VSAM datasets ??9_Dee Reins21_rcwingman@COMCAST.NET30_Fri, 8 May 2009 19:42:07 +0000310_utf-8



I am trying to allocate a DB2 VSAM Catalog dataset so its initial allocation will be on 1 volume but it will extend across multiple volumes when it needs the space. The dataset is SMS managed and directed to a SMS storage group. I have done the following with the results indicated: [...] 7239 60 31_Re: ENCRYPTION of Credit number13_mohammad_khan24_mohammad_khan@BCBSIL.COM30_Fri, 8 May 2009 14:32:45 -0500531_iso-8859-1 You have received an important delivery from mohammad_khan

Please pick up the package at the following Web address: https://tmail.hcsc.net/ime?x=4-9377523-7153755-Q7NYK6J2

The package will expire on Sunday June 07, 2009 at 14:33:41 CDT5



......................................................................

Need help picking up your package?

* If the Web address above is highlighted, click on it to open a browser window. You will automatically be taken to the package. [...] 7300 45 51_[Adv] Advance SQL Training - join me in NYC 5/27-2914_Larry Kintisch17_LKint@VERIZON.NET30_Fri, 8 May 2009 15:55:53 -0400649_us-ascii Hi Listers,

I've been teaching DB2 classes as a contract instructor for IBM for 17 years and hope this IBM workshop will help you and some of your developers catch up with some advanced techniques. Without your quick registrations the class will be cancelled!

May 27-29 "CE130DB2 SQL Workshop for Experienced Users" has been updated to cover some new features of DB2 9. If you've taken "CE120 DB2 SQL Workshop" this is the follow-up class. For all platforms of DB2 it covers referential integrity, check constraints and triggers; outer joins and joining tables to themselves; CASE expressions; Materialized Query Tables; [...] 7346 65 44_Re: allocationg SMS managed VSAM datasets ??15_Lizette Koehler23_starsoul@MINDSPRING.COM30_Fri, 8 May 2009 15:57:41 -0400 7412 12 44_Re: allocationg SMS managed VSAM datasets ??11_Ted MacNEIL18_eamacneil@YAHOO.CA30_Fri, 8 May 2009 20:06:30 +0000173_Windows-1252 > VOLUMES(*) -

VOL(*) states 1 volume

You need:

VOLUMES(* * * * * * * *)



Up to 59

- Too busy driving to stop for gas! 7425 414 44_Re: allocationg SMS managed VSAM datasets ??13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Fri, 8 May 2009 13:07:23 -0700524_us-ascii Hi Dee. Were you able to get a primary extent to go to more than one volume? The last time I tried to do that I was on z/OS 1.7, so maybe things have changed since then. In my experience the first extent was restricted to one volume. What I would have expected in scenario 2 is that you would allocate 200 cylinders in up to 5 extents on the first volume, with the option to expand to two additional volumes later. As the dataset grows, it should get up to 123 extents on the current volume before looking for [...] 7840 545 44_Re: allocationg SMS managed VSAM datasets ??13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Fri, 8 May 2009 13:26:04 -0700557_us-ascii Additionally, if you have an existing object that you wish to make multi-volume, or that has only a few volumes and you want to add more, do this:

ALTER DB2F.DSNDBD.DSNDB01.SPT01.I0001.A002 - ADDVOLUMES(* * * * * * *)

Note that you alter the data component.

Regards, Cathy

________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Taddei, Cathy Sent: Friday, May 08, 2009 1:07 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: allocationg SMS managed VSAM datasets ?? [...] 8386 136 44_Re: allocationg SMS managed VSAM datasets ??19_Peter X. DeFabritus22_pxdefabr@EARTHLINK.NET30_Fri, 8 May 2009 20:32:23 +0000434_ISO-8859-1 The best way to do this is to have your SMS folks set up a data class with the following characteristics:

Space Constraint Relief=Y Dynamic Volume Count=10

and associate your data set with this data class either explicitly or implicitly. If this is done, the data set will be catalogued with a single volume (VOLUMES(*)), no candidate volumes are necessary, and up to 10 volumes will be added as needed. [...] 8523 123 26_Re: Insert BLOB using REXX11_Akash Pilot13_akash@USA.COM30_Fri, 8 May 2009 21:16:38 +0000284_windows-1252 Thanks for the suggestion. Tried it but didn't work. Works for other data types but not for LOBs. I am getting SQL error -804 (rc 09). Looks like it didn't like the length of the LOB column. Tried without specifying a length and it still failed with the same error. [...] 8647 46 28_z/OS DB2 V8. Unload question0_22_ray_in_doubt@YAHOO.COM30_Fri, 8 May 2009 14:27:27 -0700383_iso-8859-1 Dear Listers,

When I use the IBM Unload (IKJEFT01) via SYSIN statement which has a simple SELECT clause, I get the data unloaded as packed. I believe this is the internal data format.



SELECT C1, C2, C3 from Table1 Where C5 > :H (C1 = INTEGER, C2 = SMALLINT)



What is the correct way to display the data unloaded so it is readable. [...] 8694 43 18_UDB v9.x - PureXML0_27_michael.j.lynch@US.HSBC.COM30_Fri, 8 May 2009 17:34:51 -0400313_US-ASCII Hello all,

We're exploring the possibility of using a UDB v9.x database as an XML transformation medium for incoming data. I've seen that the state of NY has employed UDB for their solution, but haven't had any luck soliciting them for info. Anyone on the list have any experience with this? [...] 8738 83 32_Re: z/OS DB2 V8. Unload question9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 8 May 2009 16:52:31 -0500308_iso-8859-1 Depends what you are going to use the data for. If you just want it to be display format SELECT CHAR(C1), CHAR(C2), C3 etc, will work. If you want comma delimited for pc work SELECT CHAR(C1), ',',CHAR(C2), ',', C3 etc, will work. Floating point and decimal fields require a little more work. [...] 8822 87 32_Re: z/OS DB2 V8. Unload question14_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Fri, 8 May 2009 16:59:33 -0500462_utf-8 See DB2 function DIGITS Faz

----- Original Message ----- From: DB2 Data Base Discussion List To: DB2-L@www.idugdb2-l.org Sent: Fri May 08 16:27:27 2009 Subject: [DB2-L] z/OS DB2 V8. Unload question

Dear Listers,

When I use the IBM Unload (IKJEFT01) via SYSIN statement which has a simple SELECT clause, I get the data unloaded as packed. I believe this is the internal data format. [...] 8910 180 35_Re: Accessing DB2 z/OS from Eclipse13_Jose de Bedos20_josedebedos@LIVE.COM30_Fri, 8 May 2009 20:17:04 -0300687_iso-8859-1

Hi,





I was going to begin a project using Java to access DB2 on z/OS, so I'd really like to jump start with your code.

Could it be possible?





Thanks in advance,





Jose





Date: Fri, 8 May 2009 07:26:10 -0500 From: toddburch@MAC.COM Subject: Re: Accessing DB2 z/OS from Eclipse To: DB2-L@WWW.IDUGDB2-L.ORG

Stan, I wrote a Java app in Eclipse, under Windows XP, that accesses z/OS using the Type 4 driver. I do not have DB2 Connect installed, or DB2 LUW either. Work's great. I used the sample app from the front of the DB2 Application Programming Guide for Java as [...] 9091 120 32_Re: z/OS DB2 V8. Unload question3_Ray22_ray_in_doubt@YAHOO.COM30_Fri, 8 May 2009 16:48:47 -0700617_iso-8859-1 Hi Mike,

Thanks for the tip. However, when you use CHAR function, the value obtained is left justified.

Since, C1 is defined as INTEGER and on CHAR(C1) becomes 10. Is there any way to make it left justfied and of 9 characters.

Cheers, Ray



--- On Fri, 5/8/09, Mike Bell wrote:

> From: Mike Bell > Subject: Re: [DB2-L] z/OS DB2 V8. Unload question > To: DB2-L@WWW.IDUGDB2-L.ORG > Date: Friday, May 8, 2009, 5:52 PM > Depends what you are going to use the > data for. > If you just want it to be display format > [...] 9212 154 32_Re: z/OS DB2 V8. Unload question3_Ray22_ray_in_doubt@YAHOO.COM30_Fri, 8 May 2009 16:52:15 -0700615_iso-8859-1 Hi Faz,

The DIGITS function on C1 does make it visible but adds unnecessary zeroes in front of the data element.



Cheers, Ray



--- On Fri, 5/8/09, Fazio, Richard wrote:

> From: Fazio, Richard > Subject: Re: [DB2-L] z/OS DB2 V8. Unload question > To: DB2-L@WWW.IDUGDB2-L.ORG > Date: Friday, May 8, 2009, 5:59 PM > > > > > > Re: [DB2-L] z/OS DB2 V8. Unload question > > > > > See DB2 function DIGITS > > Faz > > > > ----- Original Message ----- > > From: DB2 Data Base Discussion List > > [...] 9367 183 35_Re: Accessing DB2 z/OS from Eclipse10_Todd Burch17_toddburch@MAC.COM30_Fri, 8 May 2009 19:08:17 -0500422_US-ASCII Sure. So this doesn't get out of hand, I'll package it all up, warts and all, and post it to the DB2 Exchange web site. After I get it uploaded, I'll post back here with a link.

Todd



On May 8, 2009, at 6:17 PM, Jose de Bedos wrote:

Hi,

I was going to begin a project using Java to access DB2 on z/OS, so I'd really like to jump start with your code. Could it be possible? [...] 9551 66 18_Re: SQL0976N Error16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU30_Sat, 9 May 2009 09:27:03 +0800584_us-ascii Not an exact much, but possibly a clue in this: http://publib.boulder.ibm.com/infocenter/wchelp/v5r6/index.jsp?topic=/com.ibm.commerce.esupport.doc/html/Configuration/swg21245463.html



"Cause The drive that you are creating the tablespace in is not correct. "





Cheers,

Greg Senior Database Administrator



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ward Fry Sent: Friday, 8 May 2009 10:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL0976N Error [...] 9618 23 34_Smith, Rusty is out of the office.11_Rusty Smith24_rusty.smith@ZURICHNA.COM30_Fri, 8 May 2009 21:05:25 -0500708_US-ASCII I will be out of the office starting 05/08/2009 and will not return until 05/11/2009.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG DB2-L FAQ and e-mail settings are located on the IDUG.org Listserv page. If you are not already an IDUG.org member, please register at http://www.idug.org/register _____________________________________________________________________ 9642 51 45_Mike Dupuis/Ontario/IBM is out of the office.11_Mike Dupuis18_mdupuis@CA.IBM.COM30_Fri, 8 May 2009 22:20:51 -0400304_US-ASCII

I will be out of the office starting 05/08/2009 and will not return until 05/19/2009.

I will respond to your message when I return. If this is an urgent matter please call the Heldesk and ask that an IGS DB2 support person be paged. For less urgent matters pls email SLCDB2. [...] 9694 162 31_Re: ENCRYPTION of Credit number14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 9 May 2009 13:28:38 +1000401_ISO-8859-1 Have you considered writing a table UDF to access the credit card information? This function will be the gate-keeper doing all required security checks and the only thing that knows the encrytion key.

Perhaps the correct reference number is the key associated with whatever you are storing the card number for. Yes, you might then be storing the same card number several times. [...] 9857 33 33_Re: choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Sat, 9 May 2009 05:39:30 +0000445_utf-8 Dear Larry & Peter,

The problem was solved via running the Runstats with Multi column options ,

Roy suggested to try this option and It worked. You can see the mails from him also on listserv as my responses. Many Thanks for your help too.

Best Regards Sanaz P.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 9891 97 21_Re: copytocopy advice10_Nicola Nur17_nick_nur@YAHOO.CA30_Sat, 9 May 2009 05:46:18 -0700629_us-ascii Thank you Tal. Nick



--- On Fri, 5/8/09, Tal Lifshitz wrote:

> From: Tal Lifshitz > Subject: Re: [DB2-L] copytocopy advice > To: DB2-L@WWW.IDUGDB2-L.ORG > Received: Friday, May 8, 2009, 4:29 AM > Nick, > If the table had no changes the image copy is allocated and > is left empty but it is not recorded in SYSCOPY. I am > guessing you are using COPYTOCOPY with the FROMCOPY option > and specify the recently allocated empty image copy. This is > causing COPYTOCOPY to fail with an error message that > indicate that the image copy is [...] 9989 43 96_Re: Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Sat, 9 May 2009 12:49:59 +0000580_utf-8 Roy ,

As I found , I need to specify the option REOPT(Vars) as Bind option to have the advantage of collected data on SYSCOLDIST via the multi column option of RUNSTATS. In the book , there was explanation about this option which has an overhead on cpu because of trying to guess the access path at runtime each time and It's better to have the sql statements with poor performance separated in a package binded with this option, right? As I've tried, the access path is different in online transactions according to the values being passed to host variables,Is [...] 10033 24 39_Re: R: Rotate Partition & Compression %9_Dan Chatt15_dchatt@FNNI.COM30_Sat, 9 May 2009 13:06:28 +0000530_windows-1252 Max was right, the new data was different enough that it did not compress well. With a new dictionary the compression is back to the 45% range.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 10058 85 29_Re: DIAGPATH filesystem setup13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM30_Sat, 9 May 2009 10:18:37 -0700498_windows-1252 While it's a good idea to provide more space for diag logs, I don't think you have enough space for either instances or diagpath.

512 Mb shared for *multiple* DB2 instances is not enough. A single 9.5 instance uses 121 Mb in ~/sqllib. I would look at expanding this particular file system. If the file system fills up, this can create problems. With a small file system, you have much less time to react to a monitor warning you of an impending file system full condition. [...] 10144 39 33_Re: choose wrong index in a query14_Peter Vanroose17_pvanroose@ABIS.BE30_Sat, 9 May 2009 18:35:18 +0000446_ISO-8859-1 > I need to specify the option REOPT(Vars) as Bind option to have > the advantage of collected RUNSTATS multicolumn statistics.

Are you already in v8? (Don't remember from your earlier posts.) In that case, consider using REOPT(ONCE) instead. This won't have the runtime overhead while still turning on the correct access path (that is, assuming that your first time execution has reasonable values in the host variables). [...] 10184 29 32_Re: z/OS DB2 V8. Unload question14_Peter Vanroose17_pvanroose@ABIS.BE30_Sat, 9 May 2009 18:38:01 +0000420_ISO-8859-1 > when you use CHAR function, the value obtained is left justified. > >Since, C1 is defined as INTEGER and on CHAR(C1) becomes 10. Is >there any way to make it left justfied and of 9 characters.

Try VARCHAR(C1) instead.

-- Peter Vanroose.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 10214 67 29_Re: DIAGPATH filesystem setup12_Phil Gunning22_pgunning@GUNNINGTS.COM30_Sat, 9 May 2009 19:07:52 +0000563_Windows-1252 I would put ea diagpath on separate filesystem. Copy the db2diag.log daily and delete it or weekly to retain for pd purposes if needed, db2 will recreate it if it needs it this keeps size down for pd viewing purposes. The real issue here is not the db2diag.log but potential space needed for dumps, traps, etc. In a clean system these should be very infrequent, but you need to allow for them. 8 GB should work but have separate ones, you wouldn't want all instances hosed (a technical term :)) due to one filling up diagpath. Also, if size not [...] 10282 60 57_z/OS DB2 V8. How to supress Leading zeroes/STRIP function0_22_ray_in_doubt@YAHOO.COM30_Sat, 9 May 2009 17:57:06 -0700516_iso-8859-1 Dear listers,

Is there a way to remove the leading zeroes from the output of the following SQL in the SYSIN cards for IKJEFT01B

SQL: SELECT DIGITS(T1.C1), DIGITS(T1.C2), DIGITS(T1.C3) FROM T1 WHERE T1.C5 > :H Output: ========================= 0000000003000010001943401 0000000002001420990943401 0000000738000010002133001



I tried using STRIP function but the result was far from desired: STRIP(DIGITS(T1.C1),L,'0'), STRIP(DIGITS(T1.C2),L,'0'), STRIP(DIGITS(T1.C3),L,'0'), [...] 10343 70 32_Re: z/OS DB2 V8. Unload question3_Ray22_ray_in_doubt@YAHOO.COM30_Sat, 9 May 2009 18:13:17 -0700357_iso-8859-1 Hi Peter,

I tried with VARCHAR(C1). The result is no different. It is left justified and has a bigger LRECL probably due to the extra bytes that VARCHAR produced.

I need it as right justified. I also tried with DIGITS(C1) but I get additional issue of zeroes being padded in front and my inability to remove them successfully. [...] 10414 63 61_Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function15_Kalena, Michael27_Michael.Kalena@JPMORGAN.COM30_Sat, 9 May 2009 21:28:20 -0400313_iso-8859-1 Could it be the implied plus sign in column one that's giving you grief? It shows up as a space so you don't see it.

If you add a substr, starting in column 2, inside the Strip, that might do it.

For example, we used this against syssequences to get the value without leading zeros: [...] 10478 226 61_Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function13_Horacio Villa17_hvilla@AR.IBM.COM30_Sat, 9 May 2009 22:43:01 -0300630_US-ASCII Could you please post the desired output? It will help to figure out the answer.

Cheers,

Horacio





ray_in_doubt@YAHOO.COM Sent by: DB2 Data Base Discussion List 09/05/2009 21:57 Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject z/OS DB2 V8. How to supress Leading zeroes/STRIP function











Dear listers,

Is there a way to remove the leading zeroes from the output of the following SQL in the SYSIN cards for IKJEFT01B [...] 10705 31 24_Re: SORT COMPONENT RC=165_Steve21_malutjuta@HOTMAIL.COM31_Sun, 10 May 2009 01:42:27 +0000588_windows-1252 Nicola,

After importing the Prod stats run Runstats Updating Space stats only in your test environ and then re-apply the Prod CARDF's. That should get the DB2 Utils closer.

regards, Steve

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 10737 79 73_AUTO: Chris Eaton is out of the office on business (returning 05/19/2009)11_Chris Eaton17_ceaton@CA.IBM.COM30_Sat, 9 May 2009 22:01:44 -0400429_US-ASCII



I am out of the office until 05/19/2009.

I am participating in the IDUG North American conference this week. I will be checking email in the evenings so I may not be able to respond to your request in a timely fashion. I will respond when able.



Note: This is an automated response to your message "DB2-L Digest - 9 May 2009 to 10 May 2009 (#2009-138)" sent on 5/9/09 21:00:01. [...] 10817 29 46_Rich Janni/CISD/WAKEFERN is out of the office.0_23_Rich.Janni@WAKEFERN.COM30_Sat, 9 May 2009 22:03:04 -0400387_US-ASCII I will be out of the office starting 05/09/2009 and will not return until 05/18/2009.

I will respond to your message when I return. If you need immediate assistance, please email Bill Kebea, Dale Froriep, or the group named db2dbas. Or, you can leave me a voice mail (732-225-8101) which I'll be checking periodically, and I'll return your call as soon as possible. [...] 10847 185 61_Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function3_Ray22_ray_in_doubt@YAHOO.COM30_Sat, 9 May 2009 19:03:36 -0700913_iso-8859-1 Hi Horacio,

After using the STRIP function, the output appears as :

...3...........1......1943401.. ...2...........142....990943401 ...738.........1......2133001..

In HEX the above output shows as: ...3...........1......1943401.. 00F00000000000F000000FFFFFFF00 013000000000011000007194340100 ----------------------------- ...2...........142....990943401 00F00000000000FFF0000FFFFFFFFF 012000000000031420009990943401 ----------------------------- ...738.........1......2133001.. 00FFF000000000F000000FFFFFFF00 037380000000011000007213300100



My desired Output is :

100990 32608415001 3 1 1943401 2 1429909434015

100990 32608415001 444FFFFFF444FFFFFFFFFFF 00010099000032608415001 ---------------------- 3 1 1943401 44444444F444F444FFFFFFF 00000000300010001943401 ---------------------- 2 1429909434015 44444444F4FFFFFFFFFFFFF 00000000201429909434015 [...] 11033 208 61_Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 10 May 2009 12:57:22 +1000676_ISO-8859-1 cast( case when substr(digits(T1.C1),1,9) = '000000000' then ' ' || substr(digits(T1.C1),10,1) when substr(digits(T1.C1),1,8) = '00000000' then ' ' || substr(digits(T1.C1),9,2) ... end as char(10))

(adjust lengths etc as actually required for the length of the various columns)

As someone else pointed out, DIGITS strips the sign character off the result.

"SYSIN cards for IKJEFT01B"??? IKJEFT01B is not a program name - program names are limited to 8 characters. Perhaps you mean IKJEFT1B or IKJEFT01? These are the TSO program (yes, the same program), not a DB2 program. It reads data from SYSTSIN, not SYSIN. In this context it is used [...] 11242 480 61_Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function13_Horacio Villa17_hvilla@AR.IBM.COM31_Sun, 10 May 2009 00:19:46 -0300529_US-ASCII CAST(RIGHT(' ' || STRIP(DIGITS(C1), L, '0'),10) AS CHAR(10)), -- for Integer CAST(RIGHT(' ' || STRIP(DIGITS(C2), L, '0'),5) AS CHAR(5)) -- for Smallint

Cheers,

Horacio





Ray Sent by: DB2 Data Base Discussion List 09/05/2009 23:03 Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function [...] 11723 248 61_Re: z/OS DB2 V8. How to supress Leading zeroes/STRIP function3_Ray22_ray_in_doubt@YAHOO.COM30_Sat, 9 May 2009 23:38:56 -0700712_iso-8859-1 Hi James,

Sorry for the confusion brought about by my typo. I am using DB2 program DSNTIAUL with the TSO program IKJEFT01

I will try out your suggestion

Cheers, Ray



--- On Sat, 5/9/09, James Campbell wrote:

> From: James Campbell > Subject: Re: [DB2-L] z/OS DB2 V8. How to supress Leading zeroes/STRIP function > To: DB2-L@WWW.IDUGDB2-L.ORG > Date: Saturday, May 9, 2009, 10:57 PM > cast( case > when substr(digits(T1.C1),1,9) = '000000000' then ' > ' || > substr(digits(T1.C1),10,1) > when substr(digits(T1.C1),1,8) = '00000000' then ' > ' || substr(digits(T1.C1),9,2) > ... > end as char(10)) > [...] 11972 22 33_Re: choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM31_Sun, 10 May 2009 06:47:08 +0000629_utf-8 Peter, we're working on db2 v7.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "The round table SIGs I attended were great. The IBM presenters were very helpful and professional." _____________________________________________________________________ 11995 93 33_Re: choose wrong index in a query14_Peter Backlund21_BacklundDB2@TELIA.COM31_Sun, 10 May 2009 15:29:48 +0200 12089 207 31_Re: ENCRYPTION of Credit number9_Raj Ghose17_raj.ghose@RBC.COM31_Sun, 10 May 2009 21:52:41 +0000660_windows-1252 Hi James, Thanks for your input. Could you please send me an example of UDF, it will be much appreciated.

Thanks!

Raj



On Sat, 9 May 2009 13:28:38 +1000, James Campbell wrote:

>Have you considered writing a table UDF to access the credit card information? This >function will be the gate-keeper doing all required security checks and the only thing that >knows the encrytion key. > >Perhaps the correct reference number is the key associated with whatever you are storing >the card number for. Yes, you might then be storing the same card number several times. > >James Campbell [...] 12297 39 44_Devyani R Sahasrabudhe is out of the office.22_Devyani R Sahasrabudhe22_devyani.sah@IN.IBM.COM31_Mon, 11 May 2009 08:01:18 +0530419_US-ASCII I will be out of the office starting 11/05/2009 and will not return until 01/06/2009.

Hello all,

I will be out of office starting from 11/05 till 29/05.

Please contact Randall H Ness and Rohan D Dhekane for DB2 and boulder security related issues. Please contact Michael Barrett-Lennard for Infrastructure related issue.

Please contact Lan-huong for Workbench related queries. [...] 12337 36 71_Difference between DSNTIAUL and UNLOAD utility wrt CLOB column handling15_Sabarish Kannan26_sabarishkannan71@GMAIL.COM31_Mon, 11 May 2009 06:51:46 +0000314_windows-1252 Hi All

I want details on the differences between DSNTIAUL and UNLOAD utility with regard to CLOB column handling . One difference i could find is, DSNTIAUL supports unloading up to 32KB of data from a LOB column. For UNLOAD utility , it supports more than 32 KB of data from a LOB column. [...] 12374 267 44_Re: allocationg SMS managed VSAM datasets ??13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 11 May 2009 10:01:23 +0200580_us-ascii Hi Cathy,

Maybe just a preference thing but if your site is well SMS-managed you can get away from all this additional asterisk nonsense. It's been awhile since I tinkered with ACS routines (or rather, stood over the shoulder of the lovely lady wot used to tinker with them for me), data classes and all those other wonderful SMS constructs but if you want datasets to go multi-volume I'd leave it to SMS. Can't remember the last place I worked that had the old VOLUMES(*,*,*) stuff. IIRC, 3 candidate volumes used to be common but one 'Asterix' (as a former [...] 12642 155 47_Presentations from Midwest Database Users Group10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 11 May 2009 04:27:27 -0400523_Windows-1252



Hi DB2 user,



Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com

Last update on Monday, May 11th 2009



Here are several presentations from Midwest Database Users Group, available on MWDUG website:



- Recursive SQL – Unleash the Power! by Suresh Sane





- More Batch, Less Time by Troy Coleman





- Performance Topics for DB2 Application Tuners by Sheryl Larsen [...] 12798 83 32_DB2 z/OS V9 CM : SQLCODE -4700 ?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Mon, 11 May 2009 08:54:49 +00001025_ISO-8859-1 Hello,

wee are getting SQLCODE -4700 on DB2 z/OS V9 CM and can't identify new V9 functionality in the following query :

---------+---------+---------+---------+---------+---------+---------+---------+ SELECT H.NOME AS NOME_VER ,H.COGNOME AS COG_VER ,G.* 00010027 FROM ( SELECT C.NOME AS NOME_PROP ,C.COGNOME AS COG_PROP ,F.* 00020027 FROM ( SELECT D.NOME AS NOME_DEL ,D.COGNOME AS COG_DEL ,E.* 00030027 FROM ( SELECT DISTINCT A.BANCA ,A.PROPOSTA ,A.STATO ,A.FASE ,A.PASSO 00040027 ,A.TIPO_PROPOSTA ,A.PROCESSO ,A.NDG_RICH ,A.NOME_RICH ,A.DATA_CREAZIONE 00050027 ,A.UT_PROPONENTE ,A.FIL_PROPONENTE ,A.DATA_CHIUSURA ,A.UT_CHIUSURA 00060027 ,A.DATA_DELIBERA ,A.UT_DELIBERA ,A.FIL_DELIBERA ,A.FIL_COMPETENZA 00070027 ,A.DATA_RIUNIONE ,A.DATA_UL_AGGIO ,A.DESCERR ,A.FTIPDEL 00080027 ,A.UFF_PROPONENTE ,A.UFF_COMPETENZA ,A.UTENTE_COMPETENZA 00090027 ,A.DATA_COMPETENZA ,A.LIVGER ,B.NOME ,B.COGNOME ,A.FIL_VERIFICA 00100027 ,A.UFF_VERIFICA ,A.UTENTE_VERIFICA ,A.DATA_VERIFICA ,A.DATA_UL_AGGIO 00110027 [...] 12882 238 113_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 May 2009 11:21:34 +0200673_ISO-8859-1 no that the way the REOPT option works. But only for dynamic SQL...





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert







"SUBSCRIBE DB2-L sn.pr" Gesendet von: DB2 Data Base Discussion List 09.05.2009 14:49 Bitte antworten an DB2 Database Discussion list at IDUG [...] 13121 324 49_Antwort: [DB2-L] DB2 z/OS V9 CM : SQLCODE -4700 ?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 May 2009 12:06:48 +0200390_ISO-8859-1 strange. I couldnt get your query to work in V8 due to the "ORDER BY" within the sub-select. Are you sure that this query ever actually *ran* in V8 ???





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...] 13446 54 35_Re: Accessing DB2 z/OS from Eclipse7_Jan tje25_jan.moeyersons@ADELIOR.BE31_Mon, 11 May 2009 10:07:43 +0000354_windows-1252 On Fri, 8 May 2009 10:00:51 +0100, Stan Hoey wrote:

>I am being dragged, kicking and screaming, into Aldous Huxley's Brave >New World. I promise to wash my mouth out immediately after I ask this >question:

Well, I think there's really no need for kicking nor screaming. Eclipse is not that bad... [...] 13501 323 49_Antwort: [DB2-L] DB2 z/OS V9 CM : SQLCODE -4700 ?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 May 2009 12:09:22 +0200479_ISO-8859-1 in V9 NF it works fine with the ORDER BY (To be expected of course) and in V9 CM bombs with th -4700 again as expected...





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...] 13825 123 44_AW: [DB2-L] DB2 z/OS V9 CM : SQLCODE -4700 ?0_26_Walter.Janissen@ITERGO.COM31_Mon, 11 May 2009 12:25:33 +0200764_iso-8859-1 Hi

ORDER BY in a subquery is only allowed in V9 NFM.



Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40198 Düsseldorf mailto:walter.janissen@itergo.com

Vorsitzender des Aufsichtsrats: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Nenad Vidak Gesendet: Montag, 11. Mai 2009 10:55 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: [DB2-L] DB2 z/OS V9 CM : SQLCODE -4700 ? [...] 13949 31 117_Re: Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM31_Mon, 11 May 2009 10:31:16 +0000420_windows-1252 Roy,

I mean without specifying this option reopt(vars) for Bind , the online transactions (static sql) will not take effect of the statistics gathered in Syscoldist and there is no difference.Do we have to consider this option for static ones?

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 13981 243 134_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 May 2009 12:38:16 +0200555_ISO-8859-1 no. You just have to make "sure" that all the stats you need are in the SYSCOLDIST when you do either a BIND, REBIND or any dynamic SQL. This can be quite a "challenge". That's why IBM have now got VOLATILE as a table option (force index access when possible) and REOPT(VARS/ONCE) for dynamic SQL so that the prepare either re-runs with every execution (not good!) or only ONCE when it is first hit and then the path stays in the DSC until it is flushed out or killed by RUNSTATS, DROP INDEX or any other method... I've held presentations [...] 14225 461 44_Re: allocationg SMS managed VSAM datasets ??0_19_mike.holmans@BT.COM31_Mon, 11 May 2009 12:54:03 +0100572_us-ascii Raymond and all,

Define "well SMS-managed".

My storage admin guys would protest quite a bit if you said that they did not manage our SMS well, but our setup requires that you code two volids/asterisks (even if they are the same) if you want to allow the dataset to go multi-volume. I suspect that it's set up that way because we had certain problems when datasets went multi-volume unexpectedly - something to do with runaway processes, I believe - and they wanted to make sure that things only went multi-volume when people were expecting it. [...] 14687 197 31_Re: ENCRYPTION of Credit number14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Mon, 11 May 2009 22:06:46 +1000561_ISO-8859-1 I am unable to send you any sample UDF. They are not hard to write - parameters much like stored procedures.

James Campbell

On 10 May 2009 at 21:52, Raj Ghose wrote:

> Hi James, > Thanks for your input. Could you please send me an example of UDF, > it will be much appreciated. > > Thanks! > > Raj > > > On Sat, 9 May 2009 13:28:38 +1000, James Campbell > wrote: > > >Have you considered writing a table UDF to access the credit card > information? This > >function will be the gate-keeper doing [...] 14885 34 41_Grant access to all tables in db2 V8 z/OS29_SUBSCRIBE DB2-L Krishnanwipro23_krishnanwipro@GMAIL.COM31_Mon, 11 May 2009 12:25:27 +0000320_windows-1252 Hi ALL,

I want to give access to the tables created with particular schema, to all my team mates . Is there any SQL command. in DB2 V8 z/OS where i can give SELECT access to all the tables created with the particular schema, since i have more than 80 tables scattered in more than one database. [...] 14920 134 45_Re: Grant access to all tables in db2 V8 z/OS13_arlen stovall22_arlenstovall@GMAIL.COM31_Mon, 11 May 2009 08:30:53 -0400426_ISO-8859-1 You could use SQL to create the grant statements or use a REXX exec.

SELECT 'GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE '|| STRIP(CREATOR,T,' ') ||'.'|| STRIP(NAME,T,' ') ||' TO PUBLIC ; COMMIT ; ' FROM SYSIBM.SYSTABLES WHERE DBNAME LIKE 'xxx%' GROUP BY CREATOR,NAME FOR READ ONLY WITH UR ;



On Mon, May 11, 2009 at 8:25 AM, SUBSCRIBE DB2-L Krishnanwipro < krishnanwipro@gmail.com> wrote: [...] 15055 31 45_Re: Grant access to all tables in db2 V8 z/OS29_SUBSCRIBE DB2-L Krishnanwipro23_krishnanwipro@GMAIL.COM31_Mon, 11 May 2009 12:36:35 +0000347_windows-1252 Yes...This should work fine, when we are done with table declarations.

Actually, we are in the design phase and i expect additional delaration 30-40 tables in this region six months down the line, and i dont want the users to keep calling requesting access to these new tables !

Are there any other possibitlities? [...] 15087 50 45_Re: Grant access to all tables in db2 V8 z/OS13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Mon, 11 May 2009 08:45:18 -0400562_us-ascii You could write a Rexx program using the DSNREXX interface to loop through all the tables meeting your criteria and have the Rexx program issue the GRANTs. But there's nothing simpler than Arlen's suggestion.

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. [...] 15138 32 15_Unions in views29_SUBSCRIBE DB2-L Krishnanwipro23_krishnanwipro@GMAIL.COM31_Mon, 11 May 2009 13:18:44 +0000320_windows-1252 Hi All,

I have learned that VIEWS cannot support UNIONS atleast in ealier versions of DB2 in mainframe environment. Does this rule still hold good? We are in DB2 V8 ... and i have some buiz requirements to create views with UNION s ....Is it possible?



Thank you all for your help [...] 15171 31 38_Loading CQM Z/OS data into DB2 tables?30_edward.krisiewicz@jpmorgan.com30_edward.krisiewicz@JPMORGAN.COM31_Mon, 11 May 2009 13:41:04 +0000325_windows-1252 Does anyone have JCL or can point me to a manual that shows how (or if) data stored in IBM DB2 Query Monitor for Z/OS (CQM V2R3M0) can be inserted into DB2 tables?

Every other monitor has this option (some are simple, some are "what were they thinking"), and we'd like to see how it's done with CQM. [...] 15203 56 19_Re: Unions in views16_Proctor, William25_william.proctor@TGSLC.ORG31_Mon, 11 May 2009 08:47:36 -0500628_us-ascii I have had unions in several views since V7. Be careful they can really hurt performance if your not careful.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of SUBSCRIBE DB2-L Krishnanwipro Sent: Monday, May 11, 2009 8:19 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Unions in views

Hi All,

I have learned that VIEWS cannot support UNIONS atleast in ealier versions of DB2 in mainframe environment. Does this rule still hold good? We are in DB2 V8 ... and i have some buiz requirements to create views with UNION s ....Is it possible? [...] 15260 464 60_[fluff] RE: [DB2-L] allocationg SMS managed VSAM datasets ??14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 11 May 2009 09:58:44 -0400484_us-ascii Raymond,





No doubt Albert Uderzo would also agree that one "Asterix" is enough. Since we're doing obscure references and he holds the relevant copyright.





--Phil

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: Monday, May 11, 2009 4:01 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] allocationg SMS managed VSAM datasets ?? [...] 15725 31 45_Re: Grant access to all tables in db2 V8 z/OS30_edward.krisiewicz@jpmorgan.com30_edward.krisiewicz@JPMORGAN.COM31_Mon, 11 May 2009 14:06:20 +0000436_windows-1252 This might work:

GRANT SYSADM TO PUB..... okay, I can't even type it.



Seriously, take the GRANT SQL that was previously listed and place it in a job that runs nightly. You can even code "AND NOT EXISTS" logic to not get any +562 error messages.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 15757 72 42_db2 z/os(v8 nfm) - question re access path13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Mon, 11 May 2009 14:41:16 +0000328_windows-1252 I have a piece of SQL which I am currently tuning (the SQL is not great as it has been produced by a SQL generator). The main query returns a count, whilst the sub-query feeds the count (but requests multiple redundant columns). When I explain the query I see index-only access for table = GEMSEVTPUBLISHSUBS. [...] 15830 131 37_DB2 Connect upgrades - best practices14_Blowers, Nancy23_nancy_blowers@MEDCO.COM31_Mon, 11 May 2009 10:52:38 -0400347_us-ascii Fellow DB2 Listers:

I'm looking for best practices options for upgrading DB2 Connect client end users, particularly when those end users are 1. Not located in an office with onsite technical support 2. For whom just transmitting/downloading the new client code is a challenge 3. Who don't have admin rights to their computers [...] 15962 56 16_Re: DB2 LUW logs13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Mon, 11 May 2009 15:25:59 +0000401_windows-1252 Jim

I just encountered the same problem. After stopping all update activity for some weekly REORG's. I had to restart Q-replication (DR tool), and noticed the log numbering was NOT sequential - log S0217336 (May 9), S0217337 (May 8) ??

-rw------- 1 db2dwh dbadmin 314580992 May 8 20:55 S0217234.LOG

-rw------- 1 db2dwh dbadmin 314580992 May 9 14:05 S0217336.LOG [...] 16019 680 32_Re: Inactive DBATs - DB2 z/OS V819_Thomas, Steven (UK)21_Steven_Thomas@BMC.COM31_Mon, 11 May 2009 17:35:58 +0200593_us-ascii Jay,

As you're probably aware, Type 2 Inactive DBATs aren't really DBATs at all but inactive connections tied to a specific remote requestor. The reason they're kept is that it speeds up the connection process if another remote connection is requested from the same requestor. They use a very limited amount of storage - I believe it's about 7.5Kb in the DIST address space - and so they provide a relatively efficient method of improving the response time of subsequent requests. It's probably not as good as a Pooled DBAT, but these use up a lot more storage, and count [...] 16700 189 42_Re: Loading CQM Z/OS data into DB2 tables?15_Stephen Vagnier17_sjvagnier@AEP.COM31_Mon, 11 May 2009 11:37:36 -0400636_US-ASCII Hi Ed,

Look at member CQM@LDB2 in ..SCQMSAMP library and in the user's guide under title "Offload Formats" in Appendix C.



Regards,



**************************************** Steve Vagnier American Electric Power One Riverside Plaza - 7th Floor Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677







"edward.krisiewicz@jpmorgan.com" Sent by: DB2 Data Base Discussion List 05/11/2009 09:41 AM Please respond to DB2 Database Discussion list at IDUG [...] 16890 788 32_Re: Inactive DBATs - DB2 z/OS V819_Thomas, Steven (UK)21_Steven_Thomas@BMC.COM31_Mon, 11 May 2009 18:13:17 +0200609_us-ascii Jay,

Having read this back I don't think I was very clear in my description:

You're using CMTSTAT=INACTIVE which is what's recommended. When a thread issues a COMMIT or a ROLLBACK, the accounting record is cut, the DBAT goes into the Pool unless you used KEPDYNAMIC(YES) and the Connection goes Inactive. Once the DBAT goes into the Pool it can be reused by anyone, saving you the (relatively high) cost of re-establishing a DBAT from scratch. The Connection from the client stays open until the client frees (DISCONNECTs) it, so if the same client issues some more SQL and starts [...] 17679 278 35_Re: Accessing DB2 z/OS from Eclipse15_Leon Katsnelson15_leon@CA.IBM.COM31_Mon, 11 May 2009 12:20:15 -0400489_US-ASCII

It is true that you do not need to install DB2 Connect on each developer workstation that uses Data Studio as Data Studio includes restricted use license for DB2 Connect and the JDBC Type 4 driver. The use is restricted to application development and test activities. To deploy finished applications in production a full unrestricted license for DB2 Connect will be required. In other words, you can't just copy db2jcc_license_cisuz.jar in to production environment. [...] 17958 124 75_Re: Difference between DSNTIAUL and UNLOAD utility wrt CLOB column handling14_Matthew Donald26_matthew.b.donald@GMAIL.COM31_Tue, 12 May 2009 04:45:36 +1000369_ISO-8859-1 Another important difference is that UNLOAD always does a tablespace scan, while DSNTIAUL executes a dynamic SQL statement and thus the optimiser may be able to choose a better access path.

As to the 32KB limit for DSNTIAUL - since the source is available (its an assembler sample program), it's easy enough to modify it to handle larger CLOB's. [...] 18083 164 35_Re: Accessing DB2 z/OS from Eclipse14_Matthew Donald26_matthew.b.donald@GMAIL.COM31_Tue, 12 May 2009 04:52:12 +1000420_ISO-8859-1 Nobody seems to have mentioned that IBM Data Studio(which is the replacement for Control Center) is built on Eclipse. You can use it to access either DB2 z/OS or DB2 LUW without DB2 Connect being installed.

Its a free download. The 10% which handle PureXML are trial-ware, but the other 90% is free-ware. [...] 18248 148 75_Re: Difference between DSNTIAUL and UNLOAD utility wrt CLOB column handling14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Mon, 11 May 2009 13:56:07 -0500509_us-ascii Unless one is unloading only a part of the data, tablespace scan is (probably) the best access path. Khalid







"Matthew Donald" Sent by: "DB2 Data Base Discussion List" 05/11/2009 01:45 PM Please respond to "DB2 Database Discussion list at IDUG"



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: [DB2-L] Difference between DSNTIAUL and UNLOAD utility wrt CLOB column handling [...] 18397 116 16_Re: DB2 LUW logs12_tim malamphy20_timalamphy@YAHOO.COM31_Mon, 11 May 2009 12:14:59 -0700509_us-ascii Jack-

I've just happen to be playing with this a lot lately. From what I've observed, All the primary logs are allocated at the same time when db2 is started and used in some sort of round-robin scheme that's not completely balanced space-wise. As each one fills, it gets closed individually and the date is updated. So you're looking at the time each one filled. Depending on the size/number of the logs and the amount of logging, this time difference can be minutes or even days apart. [...] 18514 559 41_Re: DB2 Connect upgrades - best practices11_Smock, Dale22_dsmock@RANDOMHOUSE.COM31_Mon, 11 May 2009 16:26:08 -0400451_US-ASCII The install can be greatly simplified if you can place the client directories on a file server shared by the users, include a response file to set the parameters to your preference so the user doesn't need to specify anything, create a profile for the DB2 subsystem connections, and code a .bat file to initiate the install and provide progress displays. The details with samples are documented in the DB2 Connect Client install guides. [...] 19074 59 16_Re: DB2 LUW logs12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Mon, 11 May 2009 20:27:52 +0000590_- An archive log can be recalled if needed for a rollback. Suggest you check right after db2start or activate or first connect and I'd bet the primairies will be sequential. In the example you sent it looks like the log out of sequence was not caused by last disconnect since it is full size so that can be ruled out. Pg ------Original Message------ From: Jack Campbell Sender: DB2 Data Base Discussion List To: DB2-L@WWW.IDUGDB2-L.ORG ReplyTo: DB2 Database Discussion list at IDUG Subject: Re: [DB2-L] DB2 LUW logs Sent: May 11, 2009 11:25 AM Jim I just encountered the same problem. [...] 19134 41 19_z/OS - DDF SRB Time12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Tue, 12 May 2009 06:24:33 +0000499_windows-1252 Can anyone confirm whether the SRB Time attributed to the DDF Address Space is mainly included in the DMB1 TCB Time?

In the following example are the Database Services TCB Time and DDF SRB Time mutually exlcusive?:

CPU Times TCB SRB Total Time Time Time System Services . . . . . . . . . . : 53:51.820 22:45:25. 23:39:17. Database Services . . . . . . . . . : 5:09:11.5 2 18:46:4 2 23:55:5 IRLM . . . . . . . . . . . . . . . : 0.37467 6:19.4236 6:19.7983 DDF address [...] 19176 28 75_Re: Difference between DSNTIAUL and UNLOAD utility wrt CLOB column handling15_Sabarish Kannan26_sabarishkannan71@GMAIL.COM31_Tue, 12 May 2009 07:45:29 +0000802_windows-1252 Hi Matthew

Thanks for giving me another point of difference between UNLOAD and DSNTIAUL.

Sabarish

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "I made a number of contacts with other people having similar experiences to mine in some of our current initiatives, giving me additional resources to discuss issues and problems with." _____________________________________________________________________ 19205 24 36_Re: DB2 z/OS V9 CM : SQLCODE -4700 ?14_Peter Vanroose17_pvanroose@ABIS.BE31_Tue, 12 May 2009 12:11:09 +0000772_ISO-8859-1 Remove the "ORDER BY PROPOSTA DESC" in the nested select.

-- Peter Vanroose.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "I made a number of contacts with other people having similar experiences to mine in some of our current initiatives, giving me additional resources to discuss issues and problems with." _____________________________________________________________________ 19230 36 19_Re: Unions in views14_Peter Vanroose17_pvanroose@ABIS.BE31_Tue, 12 May 2009 12:23:52 +0000565_ISO-8859-1 A possible alternative to a design with tbl1, tbl2, and vw0 as "tbl1 UNION ALL tbl2" could be a partitioned table: the original vw0 will become the table, and the original tbl1 and tbl2 become views, but all three are updatable and the access paths to the two views will be identical to the table access paths in the earlier design. DB2 v8 allows all indexes (except the UNIQUE ones) to be partitioned, which brings the physical design even closer to the earlier one. The only thing that would no longer be possible: a unique index on just tbl1 and [...] 19267 186 23_Re: z/OS - DDF SRB Time14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Tue, 12 May 2009 08:12:30 -0500580_us-ascii No, SRB time for DDF is not included in DBM1 times ( TCB or SRB ). Yes, these two times are mutually exclusive. DBM1 times comprise of work not directly attibuted to client threads i.e. opening / closing of datasets, prefetch etc. On the other hand DDF SRB time is comprised of most of the work done for remote applications i.e. creating connections and threads, SQL execution, handling result sets etc. I said most because some of the work for these applications is not attributed here e,g, external stored procedure or UDF execution is accounted in TCB of the WLM [...] 19454 515 64_Re: [fluff] RE: [DB2-L] allocationg SMS managed VSAM datasets ??10_Roger Hecq18_Roger.Hecq@UBS.COM31_Tue, 12 May 2009 09:39:26 -0400382_us-ascii Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...] 19970 198 40_Materialized query using left outer join14_Carol Goldberg19_puli@MINDSPRING.COM31_Tue, 12 May 2009 13:48:21 +0000289_windows-1252 I have read most of the docs on the web and still do not seem to have the answer to this question. The error always talks about a FULLSELECT.

I verified that the unix index structures are the same. There is no order by, can there be? Got errors when I tried that. [...] 20169 66 90_[FLUFF] Database quiz campaign that's been launched to find the World's Smartest Databaser20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Tue, 12 May 2009 16:56:48 +0300732_us-ascii http://snipurl.com/hemfj-4040





_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "I made a number of contacts with other people having similar experiences to mine in some of our current initiatives, giving me additional resources to discuss issues and problems with." _____________________________________________________________________ 20236 161 52_AW: [DB2-L] Materialized query using left outer join0_26_Walter.Janissen@ITERGO.COM31_Tue, 12 May 2009 16:03:06 +0200436_iso-8859-1 Hi Carol

I am not sure, but do you have to repeat the column names in the create table? And if so, have the names to be the same? If also so, then there is a difference for one name: APPLIED_AMT and APPLIED_AMounT.

Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40198 Düsseldorf mailto:walter.janissen@itergo.com [...] 20398 165 57_Antwort: [DB2-L] Materialized query using left outer join11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 12 May 2009 16:05:31 +0200476_ISO-8859-1 from the SQL Guide for MQTs:

When a materialized query table is defined with ENABLE QUERY OPTIMIZATION specified, the following additional restrictions apply:

The fullselect must be a subselect.

The subselect cannot include a function that is nondeterministic or has external actions. For example, a user-defined function that is defined with either EXTERNAL ACTION or NOT DETERMINISTIC or the RAND built-in function cannot be referenced. [...] 20564 75 45_Re: Grant access to all tables in db2 V8 z/OS25_Hall, Robert (Contractor)25_Robert.Hall@OCC.TREAS.GOV31_Tue, 12 May 2009 10:16:57 -0400599_us-ascii Have you considered secondary authids?

I have used this approach in the past to give certain groups of developers access to specific tables across databases, and it worked well. You grant access to the secondary authid for any tables that you want a given secondary authid to have, e.g., grant select on table a.b to devlabc. Security then connects users to the appropriate group (secondary authid). The nice part is that when you create a new table, all you have to do is grant access to the secondary authid, and everyone in the group automagically gets access to the tables. [...] 20640 29 23_Re: z/OS - DDF SRB Time12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Tue, 12 May 2009 14:31:51 +0000306_windows-1252 Thanks for that Khalid....I didn't have my head screwed on earlier.... I had missed the "days" count in the DBM1 SRB Time which confused the issue for me as the DDF SRB Time seemed very high in comparison to the DBM1 SRB Time. Factor in the "missing" 2 days... and the figures work out!! [...] 20670 26 61_Re: Antwort: [DB2-L] Materialized query using left outer join14_Carol Goldberg19_puli@MINDSPRING.COM31_Tue, 12 May 2009 14:47:51 +0000543_windows-1252 I believe you may be correct, its hard to believe that an outer join is not supported, guess it's time for plan B. Is this supported in DB2 V9.

Carol

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 20697 33 61_Re: Antwort: [DB2-L] Materialized query using left outer join14_Peter Vanroose17_pvanroose@ABIS.BE31_Tue, 12 May 2009 15:01:14 +0000463_ISO-8859-1 Or just go for plan A', which is to disable query optimisation:

CREATE TABLE name (fields) AS (fullselect with left outer join) DATA INITIALLY DEFERRED REFRESH DEFERRED DISABLE QUERY OPTIMIZATION MAINTAINED BY USER IN tsname

This works for me, even in v8.

-- Peter Vanroose.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 20731 36 45_Re: Grant access to all tables in db2 V8 z/OS14_Peter Vanroose17_pvanroose@ABIS.BE31_Tue, 12 May 2009 15:14:24 +0000505_ISO-8859-1 Robert.Hall@OCC.TREAS.GOV wrote:

>Have you considered secondary authids?

This still doesn't avoid the (apparently) cumbersome aspect of issuing grants for every newly created table, instead of having those grants automagically in place even for tables that did not exist at the moment of the GRANT.

What the original poster seemed to be asking for was a kind of "pass-on" SELECT grant at the SCHEMA level. Are there plans in this direction for future versions of DB2? [...] 20768 157 45_Re: Grant access to all tables in db2 V8 z/OS14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Tue, 12 May 2009 11:16:44 -0500443_US-ASCII Peter, The solution to the issue of having to re-issue grants whenever new objects are created can be resolved with the RACF interface to DB2, which uses RACF profiles, so as long as you have good naming conventions, generic profiles will support this.

=============================================== Wayne Driscoll Omegamon DB2 L3 Support/Development wdrisco(AT)us.ibm.com =============================================== [...] 20926 47 20_Counting LOB columns12_Denise Gantz22_denise.m.gantz@EDS.COM31_Tue, 12 May 2009 17:05:17 +0000379_windows-1252 I have been asked by a WAS administrator if it's possible to count the bytes for individual LOB columns in a table, i.e.

"I want size in bytes, like size of record 1 payload_data is 2 kb, size of record 2 payload_data is 34 kb ---etc. Sizes in any half hour span is ok, as we are trying to figure out average sizes of data flowing through our systems. " [...] 20974 37 56_Re: AW: [DB2-L] Materialized query using left outer join14_Carol Goldberg19_puli@MINDSPRING.COM31_Tue, 12 May 2009 17:19:28 +0000625_windows-1252 Taking the advice within the thread I just changes the one parameter from

enable QUERY OPTIMIZATION



to

DISABLE QUERY OPTIMIZATION



Low and behold it worked.

Thanks for your help.

:)

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 21012 90 45_Re: Grant access to all tables in db2 V8 z/OS25_Hall, Robert (Contractor)25_Robert.Hall@OCC.TREAS.GOV31_Tue, 12 May 2009 13:32:09 -0400631_us-ascii The problem is that some DBMS software is geared toward the database being the logical grouping of tables, so since the access we are discussing crosses the database boundary, that complicates the problem somewhat. I would personally prefer 1 grant per table to a RACF (or similar) group as opposed to potentially tens or hundreds of grants to individual userids, which have to be cleaned up manually when someone leaves the organization. RACF has additional features for DB2 access control, but those really only work well with tight naming standards, and most places I've worked, naming standards have evolved with [...] 21103 98 24_Re: Counting LOB columns14_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Tue, 12 May 2009 13:40:17 -0400452_us-ascii I used the LENGTH built-in function:

SELECT SUM(LENGTH(LOB_COLUMN)) FROM TABLE_HAVING_LOBS WHERE WITH UR;

In actuality, I needed to figure out "How many rows containing LOB columns were of what length?", in order to determine a 'better' SEGSIZE. I used a subquery like this:

SELECT AVG(32 + LENGTH(LOB_COLUMN)) FROM TABLE_CONTAINING_LOBS WHERE MOD(IDENTITY_COLUMN,1000) = 371 --< SAMPLE 0.01 % WITH UR ; [...] 21202 182 24_Re: Counting LOB columns12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 12 May 2009 17:42:26 -0700669_iso-8859-1 Lockwood, Perhaps I'm missing something but why are you worrying about the length of the non-lob columns and the segsize of the LOB tablespace. Remember, LOBs are stored in a completely separate tablespace from the prime table and columns. Therefore, you don't need to worry about the segsize for the LOB data and worry about the mixing. You need to worry about the pagesize for the LOB data and which buffer pool. Myron ________________________________ From: "Lyon, Lockwood" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, May 12, 2009 1:40:17 PM Subject: Re: [DB2-L] Counting LOB columns I used the LENGTH built-in function: SELECT [...] 21385 106 24_Re: Counting LOB columns12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 12 May 2009 17:45:52 -0700656_iso-8859-1 Why not select length(payload_data) from TABLE A where timesspan between x and y? Or something like that, That gives for each record. Average is just select avg(length(payload_data) from table A where ,.,,,, Myron ________________________________ From: Denise Gantz To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, May 12, 2009 1:05:17 PM Subject: [DB2-L] Counting LOB columns I have been asked by a WAS administrator if it's possible to count the bytes for individual LOB columns in a table, i.e. "I want size in bytes, like size of record 1 payload_data is 2 kb, size of record 2 payload_data is 34 kb ---etc. Sizes in [...] 21492 273 94_Re: [FLUFF] Database quiz campaign that's been launched to find the World's Smartest Databaser16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Wed, 13 May 2009 09:14:21 +0800587_us-ascii *Aaack!* Sponsored by 'Orrible.... (but not bad anyway...) From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (Gmail) Sent: Tuesday, 12 May 2009 9:57 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] [FLUFF] Database quiz campaign that's been launched to find the World's Smartest Databaser

http://snipurl.com/hemfj-4040





________________________________

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 21766 964 32_Re: Inactive DBATs - DB2 z/OS V820_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Wed, 13 May 2009 11:04:49 +0300608_US-ASCII Jay,

In our shop the value of CONDBAT is high and when we issued -DIS THREAD(*) ... TYPE(INACTIVE) we receive DSNV423I -PR4B DISPLAY THREAD MESSAGE POOL SIZE EXCEEDED

That's why we sometimes check the status of Inactive threads from TCP/IP with NETSTAT CON Command.

FYI, in case needed...

Regards, Cuneyt





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Thomas, Steven (UK) Sent: Monday, May 11, 2009 7:13 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Inactive DBATs - DB2 z/OS V8 [...] 22731 80 34_Re: CPU Impact on Getpages and i/o15_Douwe van Sluis21_d.b.van.sluis@HOME.NL31_Wed, 13 May 2009 11:20:08 +0200565_us-ascii Hi Jorg,

I am a little behind on the posts, but since no one reacted, I'll give it a shot.

The number of Getpages is an important figure for a DB2 workload. If the getpages are the same, the workload is the same. So my first reaction would be, that the workload is not the same. A lower hitratio would not account for the higher number of getpages, just for the higher number of sync i/o's. DB2 will do a getpage for every page, regardless of whether that page resides in the bufferpool. Even you would suffer from bufferpool thrashing, [...] 22812 44 35_Re: Accessing DB2 z/OS from Eclipse7_Jan tje25_jan.moeyersons@ADELIOR.BE31_Wed, 13 May 2009 10:03:28 +0000578_windows-1252 On Mon, 11 May 2009 12:20:15 -0400, Leon Katsnelson wrote:

> >It is true that you do not need to install DB2 Connect on each developer >workstation that uses Data Studio as Data Studio includes restricted use >license for DB2 Connect and the JDBC Type 4 driver. The use is restricted >to application development and test activities. To deploy finished >applications in production a full unrestricted license for DB2 Connect will >be required. In other words, you can't just copy db2jcc_license_cisuz.jar >in to production environment. [...] 22857 170 41_R: [DB2-L] CPU Impact on Getpages and i/o15_Moschelli Mauro34_mauro.moschelli@INTESASANPAOLO.COM31_Wed, 13 May 2009 12:06:32 +0200383_iso-8859-1 Hi, I agree that the CPU constraint does not account for a higher number of getpage, unless this slowdown causes some Bufferpool threshold hits (DM Critical IIRC) that causes getpage-relpage for each row instead of each page.

HTH



Mauro Moschelli Intesa Sanpaolo Group Services

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS [...] 23028 139 34_Re: CPU Impact on Getpages and i/o23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 13 May 2009 10:44:17 -0400362_iso-8859-1 I certainly agree here.

A constrained CPU will not have any effect on the number of getpages.

If the getpages shows a significant variation, it is not the same workload. Pool thrashing will not affect the number of getpages, only the number of synch IOs.

You need to look at the application actg detail to find your problem. [...] 23168 43 45_Re: R: [DB2-L] CPU Impact on Getpages and i/o16_mocion@libero.it16_mocion@LIBERO.IT31_Wed, 13 May 2009 10:21:48 -0500516_ISO-8859-1 Personally I saw no getpages problems with constrained CPU (but I didn't analyze that part of the problem too much).

What I saw with a costrained CPU is 'general elongation' like locks and all DB2 services (all address spaces I mean) are delayed, like DDF (could appear as waiting in network), some log activities and so on, but the most evident - at least for me - was increase in deadlocks expecially for application using gross locks. Things are worse if IRLM isn't well classified in WLM. [...] 23212 1460 32_Re: Inactive DBATs - DB2 z/OS V812_Reavill, Jay22_Jay.C.Reavill@FNIS.COM31_Wed, 13 May 2009 12:39:50 -0500491_ISO-8859-1 Thanks Cuneyt. And thanks to Steve, he's provided a great deal of insight. I think the basic question we need answered is... Does anyone know what causes the following error? We know it has to do with the KEEPALIVE parm in TCP/IP, but we're not sure what the cause is or possible causes are. We only seem to see them when our number of active + inactive DBATs gets suspiciously close to the CONDBAT limit of 400 which makes us think they are timing out waiting for a thread. [...] 24673 611 51_FW: SQLAdria Conference, Dubrovnik, 14-18 June 200914_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 13 May 2009 18:45:02 +0100374_iso-8859-1 This may be of interest to folks in Europe

I just received information on this years SQL Adria 4-day conference in Dubrovnik

Please read on for more details - I can highly recommend not just the conference but also the conference location!

If you also look at their agenda there are some excellent speakers and topics lined up this year [...] 25285 69 52_Interesting behaviour of Omegamon/XE v4.1 GUI Client18_Sandra Lakenburger27_sandra.lakenburger@USDA.GOV31_Wed, 13 May 2009 18:17:12 +0000503_windows-1252 Good afternoon,

I'd like to know if anyone else has seen similar behaviour from the GUI client for Omegamon/XE v4.1. I've got customers that once they change their ACF2 password and then try to signon to the GUI client using the new password, are getting an error indicating that their password is invalid for a given subsystem. I'm assuming folks using RACF might see something similar since it doesn't actually seem to make it over to the frame to even validate the password. [...] 25355 270 56_Re: Interesting behaviour of Omegamon/XE v4.1 GUI Client15_Stephen Vagnier17_sjvagnier@AEP.COM31_Wed, 13 May 2009 14:26:51 -0400505_US-ASCII Hi Sandra,

we had a similar problem after applying Omegamon/XE V4.1 maintenance. The issue was resolved by always entering the password in all CAPS. I believe there was an APAR (?) applied that allow the GUI client to be case sensitive to passwords.



Hope this helps,

**************************************** Steve Vagnier American Electric Power One Riverside Plaza - 7th Floor Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677 [...] 25626 1522 32_Re: Inactive DBATs - DB2 z/OS V811_Ron Johnson25_rjohnson@DATA-TRONICS.COM31_Wed, 13 May 2009 13:41:00 -0500631_us-ascii Jay,

We were having -30081 problems earlier and here is what I derived. Some of this is described by Cuneyt below.

MAXDBAT is the maximum number of concurrently active remote threads. What I've seen when watching CA-Insight and displaying threads is a widely varying number of threads showing a connection name of DISCONN (indicating that no connection is currently associated with this DBAT). Those are what are referred to as pooled DBATs. At commit, DB2 decides if the connection can become inactive (and the DBAT pooled). A pooled DBAT is available for a new/resumed request but it is still counted [...] 27149 83 21_Question about rebind0_18_RENUSHARMA@AOL.COM29_Wed, 13 May 2009 21:10:57 EDT680_US-ASCII

Hello List

I have an urgent requirement to run rebind using stored procedure. I cannot use the sysproc.rebind_routine_package as these packages are not from store procedure.Urgent help is rqeuired. Please guide me towards any documentation

Thanks in advance Renu

**************Dell Mini Netbooks: Great deals starting at $299 after instant savings! (http://pr.atwola.com/promoclk/100126575x1221972443x1201442012/aol?redir=http:%2F%2Fad.doubleclick.net%2Fclk%3B214819441%3B36680237%3Bi)

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 27233 38 44_DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Thu, 14 May 2009 07:22:50 +0000415_ISO-8859-1 Hello, V9 SMP/E installation produced a very small SDSNEXIT PDS - only 6 BLOCKS primary quant. and 0 secondary. Since we share that library among more than one DB2 subsystem, I often end with D37 when assembling z-parm modules that also reside there. Compressing SDSNEXIT, on the other hand, can produce system-wide problem since the library is on the link list and refreshing LLA may be necessary. [...] 27272 95 28_Looking for some information13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 14 May 2009 02:41:12 -0500439_ISO-8859-1 Hi all,

I'm working on a paper and I could use some information to substantiate a few of my assumptions.

If you can share, here is what I need...

* the size of your DSNDB07 (or equivalent if DS) database * the number of 4K workfiles (table spaces) defined in DSNDB07 * the number of 32K workfiles (table spaces) defined in DSNDB07 * the size of the buffer pool supporting DSNDB07 * the size of SRTPOOL [...] 27368 64 56_SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Thu, 14 May 2009 10:20:35 +0200478_iso-8859-1 We feel comfortable with a separate SDSNEXIT and we allocate SDSNEXIT with 3 CYL and 1 secondary. We have no reason to put SDSNEXIT into the linklist. We only put SDSNLOAD into the linklist.

Regards, Olle



-----Ursprungligt meddelande----- Från: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] För Nenad Vidak Skickat: den 14 maj 2009 09:23 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ? [...] 27433 26 48_Re: DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Thu, 14 May 2009 10:26:52 +0200718_us-ascii Not in linklist here either and a seperat for each one db2-system. Allocate trks 15 0

Hanne

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "The most important thing I always find at IDUG is the revisiting of old assumptions, with new functions and features." _____________________________________________________________________ 27460 72 48_Re: DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?16_mocion@libero.it16_mocion@LIBERO.IT31_Thu, 14 May 2009 03:57:40 -0500325_ISO-8859-1 Nenad

No in linklist (only SDSNLINK for ERLY code) where I work(ed), separated for each subsystem (for instance for PTF not to be applied to all DB2s), 75 ,0 trks; it's big as SDSNEXIT usually doesn't contain many modules but nowadays 75 tracks are nothing. If needed SDSNLINK is in DB2 starting proc. [...] 27533 110 48_Re: DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Thu, 14 May 2009 11:22:44 +0000646_ISO-8859-1 Hello Max,

in that case shouldn't you add SDSNEXIT and SDSNLOAD to STEPLIB concatenation in all your TSO procedures and BATCH JCLs ? Do you use version specific first qualifiers for DB2 libraries ?

Regards, Nenad

On Thu, 14 May 2009 03:57:40 -0500, mocion@libero.it wrote:

>Nenad > >No in linklist (only SDSNLINK for ERLY code) where I work(ed), >separated for each subsystem (for instance for PTF not to be applied to >all DB2s), 75 ,0 trks; it's big as SDSNEXIT usually doesn't contain many >modules but nowadays 75 tracks are nothing. If needed SDSNLINK is in DB2 >starting proc. [...] 27644 116 20_[DB2 V9] TOTAL_COSTS0_26_Walter.Janissen@ITERGO.COM31_Thu, 14 May 2009 13:29:04 +0200461_iso-8859-1 Hi

Does anybody know, what are the units of TOTAL_COSTS in DSN_STATEMNT_TABLE? Is it only a number, which I can use to compare two different access paths for the same query? Or does it represent seconds? minutes? of estimated elapsed time?

Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40198 Düsseldorf mailto:walter.janissen@itergo.com [...] 27761 247 32_AW: [DB2-L] [DB2 V9] TOTAL_COSTS12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 14 May 2009 13:46:04 +0200828_iso-8859-1 From the CA reference Guide DB2 9 for z/OS:





TOTAL_COSTS in DSN_STATEMNT_TABLE represents the overall estimated cost of the statement. This should only be used for references purposes (what ever that means.....).



HTH.

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- THINK GREEN - DON'T PRINT THIS EMAIL UNLESS YOU REALLY NEED TO ! [...] 28009 240 24_Re: [DB2 V9] TOTAL_COSTS11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Thu, 14 May 2009 12:47:36 +0100652_iso-8859-1 [DB2 V9] TOTAL_COSTSHi Walter

From the Redbook 'IBM DB2 9 for z/OS: New Tools for Query Optimization' SG24-7421, section 10.3.4:

>>>>>>> Note: The estimated elapsed time for each SQL statement is retrieved from the column TOTAL_COST in the DB2 V9 table DSN_STATEMNT_TABLE. This column is populated by the DB2 optimizer, during Explain, with the estimated elapsed time of the SQL statement based on CPU and I/O cost. The value that is placed in the floating point column represents milliseconds elapsed time. The DB2 optimizer uses this value when it compares the cost of different access paths before selecting the lowest [...] 28250 270 60_Re: SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM31_Thu, 14 May 2009 08:10:20 -0400524_ISO-8859-1 We have a sdsnexit for each subsystem. the exit lib is allocated 2,1 and has 10 members in it. sdsnload and sdsnlink are in lnklst. As sdsnexit usually contains dsnzparm and dsnhdecp which have subsystem specific parms, how do you share a dsnexit between subsystems? Regards, Jeff





Olle Brostrom Sent by: DB2 Data Base Discussion List 05/14/2009 04:24 AM Please respond to DB2 Database Discussion list at IDUG [...] 28521 325 68_SV: [DB2-L] SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Thu, 14 May 2009 15:13:01 +0200535_iso-8859-1



Hi Jeff, we do not share SDSNEXITs between subsystems, only sharing of SDSNEXITs between members in data sharing groups. We are lucky to have the same content for all of our DSNHDECP modules which is shared by users in SDSNLOAD in the linklist. //Olle ________________________________ Från: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] För Jeff Frazier Skickat: den 14 maj 2009 14:10 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: Re: [DB2-L] SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ? [...] 28847 151 60_Re: SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Thu, 14 May 2009 13:21:02 +0000337_ISO-8859-1 Jeff, we run our production on two z10 machines with 8 LPARs all connected in one sysplex. DB2 subsystems running in LPARS on the same machine share DB2 libraries ( ICF catalogs are shared among those systems). So we don't have to maintain to many copies of DB2 libs. and still have a opportunity to roll-in maintenance. [...] 28999 26 60_Re: SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Thu, 14 May 2009 15:32:22 +0200514_us-ascii We do a copy of: DSNHDECP and DSNHMCID to SDSNLOAD who is in the lnklst. DSNHDECP is also the same all over as of now.

hanne

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 29026 135 32_Re: Looking for some information14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 14 May 2009 15:40:53 +0100507_iso-8859-1 Thinking ahead Willie, it might also be interesting to see who has secondary allocations on their work file data sets and who doesn't.....

Phil G

________________________________

From: DB2 Data Base Discussion List on behalf of Willie Favero Sent: Thu 14/05/2009 08:41 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Looking for some information



Hi all,

I'm working on a paper and I could use some information to substantiate a few of my assumptions. [...] 29162 79 27_SQL Server DB2 Connect -80511_Gerald Chao22_gerry.chao@DAIMLER.COM31_Thu, 14 May 2009 14:52:52 +0000552_windows-1252 Z/OS DB2 V8 NFM on the mainframe. SQL Server runs two types of queries. The "open ended" query works. It is a simple query of the traditional type that simply submits SQL to be executed on the mainframe via DB2 Connect. I'm not sure what to call the failing query. The SQL Server DBA I am trying to assist describes it as: SQL server submits a number of charactersitics it is looking for and data, column and row information etc. are returned to SQL Server where the actual query then runs. This second type of query used to work but [...] 29242 106 31_Re: SQL Server DB2 Connect -80514_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Thu, 14 May 2009 10:02:49 -0500494_us-ascii You should have a package called SYSIBM.SYSTABLES in collection DSNASPCC. You are getting an error on this package.

Wayne Stevens Wayne.Stevens@dhr.alabama.gov



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gerald Chao Sent: Thursday, May 14, 2009 9:53 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SQL Server DB2 Connect -805

Z/OS DB2 V8 NFM on the mainframe. SQL Server runs two types of queries. [...] 29349 107 31_Re: SQL Server DB2 Connect -80514_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Thu, 14 May 2009 10:07:58 -0500434_us-ascii I meant You should have a Stored Procedure called SYSIBM.SYSTABLES in collection DSNASPCC. You are getting an error on this procedure.

Wayne Stevens Wayne.Stevens@dhr.alabama.gov



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gerald Chao Sent: Thursday, May 14, 2009 9:53 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SQL Server DB2 Connect -805 [...] 29457 97 32_Re: Looking for some information12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 14 May 2009 18:42:03 +0300559_iso-8859-1 Hi

In V7/V8

32 * 4K * 1100 Cyls 2 * 32K * 600 Cyls

BP7 = 50000 pages SRTPOOL = 2MB

In V9

2 * 4k * 1100 Cyls 16 * 32k * 1100 Cyls

Isaac Y assin

---- Original message ---- >Date: Thu, 14 May 2009 02:41:12 -0500 >From: Willie Favero >Subject: [DB2-L] Looking for some information >To: DB2-L@WWW.IDUGDB2-L.ORG > > Hi all, > > I'm working on a paper and I could use some > information to substantiate a few of my assumptions. > > If you can share, here is what I need... > > [...] 29555 245 31_Re: SQL Server DB2 Connect -80512_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Thu, 14 May 2009 17:49:48 +0200595_Windows-1252

Gerry,

for ODBC and JDBC access, there is a set of objects that needs to be bound on the DB2 z/OS side. "DSNASPCC" actually refers to the Stored Procedures Collection and there's a variety of packages in there.

SLQCODE -805 tells you that the correct version of the package was not found. Therefore, your z/OS DBA will have to bind the correct version of this package (the one that is consistent with the driver you are using). Your DBA will do best to not only bind the package mentioned here (DSNATBL8) into collection DSNASPCC, but also a few more. If [...] 29801 169 31_Re: SQL Server DB2 Connect -80512_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Thu, 14 May 2009 10:55:30 -0500 29971 280 32_Re: Looking for some information9_Ford Wong14_fordie@SHAW.CA31_Thu, 14 May 2009 10:21:06 -0600588_utf-8 We are at DB2 V8 NFM. 1. the size of your DSNDB07 (or equivalent if DS) database DSN32Kxx are defined PRIQTY 351616 SECQTY 70016 DSN4Kxx are defined PRIQTY 1727500 SECQTY 71300 32K VSAM dsns are defined: CYLINDERS(264 100) 4K VSAM dsns are defined CYLINDERS(2406 100) ⇠approx 2. the number of 4K workfiles (table spaces) defined in DSNDB07 5 3. the number of 32K workfiles (table spaces) defined in DSNDB07 5 4. the size of the buffer pool supporting DSNDB07 VPSIZE = 30000 ⇠not fixed. 5. the size of SRTPOOL 2000K 6. secondary allocations on workfile datasets: All have [...] 30252 51 48_Re: DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?16_mocion@libero.it16_mocion@LIBERO.IT31_Thu, 14 May 2009 12:02:51 -0500552_ISO-8859-1 Hello Nenad

1) Only SDSNLOAD but it's only one proc to be changed when you migrate DB2. Or you can use an alias for DB2 library names in TSO proc so it'll remain untouched (only alias have to be 'switched' during DB2 migration). 2) In batch we use INCLUDE jcl statement to include a member in system proc library with DD cards and DB2 library names inside. Or you can use ALIAS even here, to be changed when you migrate DB2 (in a snap). I know there's some old MVS/Z/OS system programmer that doesn't like aliases (in the past - [...] 30304 356 60_Re: SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 14 May 2009 10:54:04 -0700451_iso-8859-1 Hi Olle. Have you upgraded to DB2 9 yet? DSNHDECP is not compatible between 8 and 9. That is when we allocated separate SDSNEXITs for each member.

Cathy

________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Olle Brostrom Sent: Thursday, May 14, 2009 6:13 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SV: [DB2-L] SV: [DB2-L] DB2 z/OS V9 - To small SDSNEXIT and LNKLST ? [...] 30661 56 20_revoke sysadm impact14_Renzo razzetti24_renzo.razzetti@GMAIL.COM31_Thu, 14 May 2009 15:37:25 -0300439_ISO-8859-1 Hello



I have to revoke a sysadm authorization from a DBA who work 10 years old in the company, that´s means he has created a lot of objects in the system. I want to know if you have some experience of revoke sysadm you can tell me.

Regards

RR

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 30718 72 24_Re: revoke sysadm impact12_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Thu, 14 May 2009 13:50:33 -0500 30791 194 24_Re: revoke sysadm impact15_Degtyarev, Lucy24_ldegtyarev@DIVINVEST.COM31_Thu, 14 May 2009 13:53:54 -0500458_iso-8859-1

Renzo, I actually was able to REVOKE SYSADM authority without suffering the cascading effect. The method is as follows: 1.Reassemble the ZPARM, for the DB2 subsystem , to allow SYSADM ID , you wish to revoke , as one of the INSTALL SYSADM ID's ( keep in mind only two INSTALL SYSADM ID's are allowed) 2.Bounce DB2 3. Revoke SYSADM from the desired ID 4.Reassemble ZPARM again to remove the ID as INSTALL SYSADM ID 5.Bounce DB2 again. [...] 30986 427 24_Re: revoke sysadm impact0_27_Jeff.D.Jerry@WELLSFARGO.COM31_Thu, 14 May 2009 13:58:17 -0500347_iso-8859-1 You may also want to substitute using the SET SYSPARM option to save cycling your DB2 subsystem. Or if you have BMC's Opertune product you can use it as well.

We have also resolved this need by forcing all our DB2 systems folks and DBAs to use a secondary authid. Then we never have to revoke SYSADM form their personal id. [...] 31414 25 26_Bob Mathews is on vacation0_24_Bob_Mathews@MANULIFE.COM31_Thu, 14 May 2009 15:32:22 -0400581_US-ASCII I will be out of the office starting 05/14/2009 and will not return until 05/19/2009.

For database issues please contact Dave Carman @827161 or Chris Tapping @824514 or Chi Ming Tseng @826403

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 31440 382 24_Re: revoke sysadm impact14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 14 May 2009 21:15:12 +0100542_iso-8859-1 or if you have ANY CA DB2 product, you can use the Value Pack to change your dsnzparm "on the fly"

Phil Grainger CA

________________________________

From: DB2 Data Base Discussion List on behalf of Jeff.D.Jerry@WELLSFARGO.COM Sent: Thu 14/05/2009 19:58 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] revoke sysadm impact





You may also want to substitute using the SET SYSPARM option to save cycling your DB2 subsystem. Or if you have BMC's Opertune product you can use it as well. [...] 31823 295 30_DB2 Sequence objects; V8, z/OS13_Fleming, Mark21_MFLEMING@ALLSTATE.COM31_Thu, 14 May 2009 15:17:47 -0500378_US-ASCII As you know DB2 Sequences are touted as superior to IDENTITY columns for assigning sequence values. I've played with IDENTITY columns and SEQUENCES, comparing the performance, and do find them faster.





I have searched the archives and manuals and don't see a solution to my situation. I'm probably not alone in wanting a solution. Read on... [...] 32119 121 34_Re: DB2 Sequence objects; V8, z/OS14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 14 May 2009 21:47:19 +0100440_iso-8859-1 OK, I don't have access to my DB2s right now, so I can't check this out but what about.....

1. Create a SEQUENCE for one of your sequence attributes (called SCHEMA1.SEQUENCE1 perhaps) 2. Now create a view selecting the next sequence value form sysibm.sysdummy1 (CREATE VIEW SCHEMA1.VIEW1 (COLNAME) AS SELECT NEXT VALUE FOR SCHEMA1.SEQUENCE1 FROM SYSIBM.SYSDUMMY1) 3. Now you can create all your alisases on this view [...] 32241 317 34_Re: DB2 Sequence objects; V8, z/OS14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Thu, 14 May 2009 15:51:49 -0500529_iso-8859-1 I don't think there is a way to share sequences across schemas. But why do you even bother ? Keep in mind 2592 sequences won't be anywhere as resource intensive as 2592 tables. There are no open datasets, buffers or disk space to worry about for sequences. HTH Khalid









"Fleming, Mark" Sent by: "DB2 Data Base Discussion List" 05/14/2009 03:17 PM Please respond to "DB2 Database Discussion list at IDUG" [...] 32559 147 34_Re: DB2 Sequence objects; V8, z/OS13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 14 May 2009 15:08:28 -0600417_iso-8859-1 Sorry Phil, I tried that a few weeks ago. NEXT VALUE is not allowed in a view. Bummer.

It would be nice to be able to alias schema objects like sequences, SPs and the like.

-----Original Message----- From: Grainger, Phil Sent: Thursday, May 14, 2009 2:55 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Sequence objects; V8, z/OS [...] 32707 177 34_Re: DB2 Sequence objects; V8, z/OS13_Fleming, Mark21_MFLEMING@ALLSTATE.COM31_Thu, 14 May 2009 17:26:40 -0500456_US-ASCII Yes, I think I tried that, too; or read that it can't be used in a CREATE VIEW. I could create a UDF that is referenced in the view. Thanks for the idea.

Mark Fleming Database Administration x-23889 -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson Sent: Thursday, May 14, 2009 4:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Sequence objects; V8, z/OS [...]