1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 2009, week 4
2 87 47_Re: Identifying client applications on DB2 z/OS11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Thu, 22 Oct 2009 07:52:02 +0000403_UTF-8 Hello,
if you are referring to JDBC JEE applications, in my opinion, you won't get much from setting these registers since you probably use connection pooling .... Basically we use end user authid for identifying different applications. Much better solution is using SQLJ and then you can use DB2 package/plan statistics and accounting... Also have a look on pureQuery technology .... [...]
90 29 25_Sliding secondary extents18_Traurig, Valerie J26_Valerie.J.Traurig@LMCO.COM31_Thu, 22 Oct 2009 17:32:54 +0000524_utf-8 We recently implemented sliding secondary extents when we migrated to a larger mod size. Things were fine until we had problems with a vendor's tools which are not compliant with this V8 feature (MGEXTSZ).
Has anyone else had problems with database tools and this feature?
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
120 39 62_LUW - clearing .tda files in temp tablespace (udb 9.5 dpf/aix)12_Pierre Evans19_pevansdba@YAHOO.COM31_Thu, 22 Oct 2009 21:58:18 +0000408_utf-8 Greetings all, We're running into occasional issues with our sms tempspace getting full. We identify and terminate the query causing it via 'get snapshot for tables', but inevitably the physical file that's created in the tempspace eg SQL00003.TDA remains there for hours even though the query no longer exists. occasionally we even have to shutdown and restart the db to get the file(s) cleared. [...]
160 66 33_v9 migration z/os 1.7 and RAD 6.00_21_BudGreenman@ONGOV.NET31_Wed, 21 Oct 2009 09:57:31 -0400369_US-ASCII We recently migrated from v8 to 9v on our development platform. When the developers tried to connect to their datasources on z/os they got the following error: [ibm][db2][jcc][10114][10305] Invalid operation: Explicit COMMIT or ROLLBACK is not allowed when in auto-commit mode
We tried falling back to v8 but they are still getting the same error. [...]
227 66 37_Fw: v9 migration z/os 1.7 and RAD 6.00_21_BudGreenman@ONGOV.NET31_Wed, 21 Oct 2009 10:10:37 -0400369_US-ASCII We recently migrated from v8 to 9v on our development platform. When the developers tried to connect to their datasources on z/os they got the following error: [ibm][db2][jcc][10114][10305] Invalid operation: Explicit COMMIT or ROLLBACK is not allowed when in auto-commit mode
We tried falling back to v8 but they are still getting the same error. [...]
294 64 31_Migrating to Db2v9 for z/0s 1.70_21_BudGreenman@ONGOV.NET31_Wed, 21 Oct 2009 10:38:10 -0400414_US-ASCII I tried sending this earlier and have not received a reply that it was sent, so my apologies if this is a duplicate
We recently migrated from v8 to 9v on our development platform. When the developers tried to connect to their datasources on z/os they got the following error: [ibm][db2][jcc][10114][10305] Invalid operation: Explicit COMMIT or ROLLBACK is not allowed when in auto-commit mode [...]
359 215 29_Re: Sliding secondary extents11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 23 Oct 2009 06:50:30 +0200661_ISO-8859-1 yes but I cant say what :) Just watch out for DDL extracts that look "odd"
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
"Traurig, Valerie J" Gesendet von: IDUG DB2 Discussion List 22.10.2009 19:32 Bitte antworten an IDUG DB2 Discussion list [...]
575 124 44_Ad - DB2 Performance Wizard at IOD next week35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 21 Oct 2009 18:27:35 -0400564_iso-8859-1 The DB2 Performance Wizard is coming... The first pieces of the Performance Wizard will roll out in next couple of weeks. It is an included function of the workstation component, of Buffer Pool Tool, and will provide a lot more insight into the performance of the pools, objects, memory utilization, paging, and object dasd performance. It will be V9.5 of the workstation code, and will be downloadable from the Website when available. If you are coming to the IOD conference, drop by for a demo of the beta code that is evolving on a daily basis. [...]
700 71 43_Re: What outage does "Create index" entail?19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Thu, 22 Oct 2009 05:44:06 -0400656_US-ASCII James is correct.
In V8, create the index with defer yes; then REORG the tablespace SHRLEVEL CHANGE. The only outage will be a brief one during the final logapply and switch phases.
In V9, you will be able to REBUILD the index SHRLEVEL CHANGE.
Larry Jardine Production DBA DBA Plan/Product Requests: https://wiki.aetna.com/confluence/display/dbaplan/Requests
-----Original Message----- From: IDUG DB2 Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Tonmoy Dasgupta Sent: Wednesday, October 21, 2009 8:59 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] What outage does "Create index" entail? [...]
772 31 27_Re: Stored Procedure Limits10_Todd Burch17_toddburch@MAC.COM31_Wed, 21 Oct 2009 20:18:11 -0500539_US-ASCII Just to follow up and close this out.
On LUW, the limit is 90 parms for a stored proc.
On z/OS, it's around 16K parms, give or take, per the field that holds the value, but no telling (without testing) what would come about when that limit started to be approached.
Todd
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
804 93 36_IDUG 2009 Europe - a personal thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 23 Oct 2009 13:36:05 +0200562_US-ASCII Esteemed listers
A personal thanks to all attendees of IDUG 2009 Europe in Rome (a little bit in late I know but these are busy days :-)) !
It was a long and hard work, expecially during these hard times, but giving great satisfactions. BUT the most important thing was I met great people and many DB2-L listers, so finally giving a 'face' to many names seen in DB2-L. And, incredibly, no one beat me ! I think it was due to chocolates and italian wines..... Anyway I hope you really enjoyed conference and be sure next CPC will make [...]
898 178 43_Re: What outage does "Create index" entail?16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG31_Fri, 23 Oct 2009 11:15:18 -0400
1077 39 29_Re: Sliding secondary extents16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 22 Oct 2009 15:00:21 -0400398_UTF-8 Hi Valerie - I don't know which vendor tools you are using, so I can only speak for CA. We released some PTF's early this year to EXPLOIT MGEXTSZ during reorg, load etc., so make sure you're current on maintenance.
Steen Rasmussen CA Sr Engineering Services Architect IBM Certified Database Associate - DB2 9 Fundamentals IBM Certified Database Administrator - DB2 9 DBA for z/OS [...]
1117 94 41_BLOB Exceeds 133 Volume Limit: Z/OS DB2V815_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 23 Oct 2009 14:31:25 -0400467_us-ascii
> Dear Listers... We have a major production problem. PLEASE, can anyone > suggest a workaround or solution. > > We have a BLOB "billing" table defined in a database that can no > longer extent. It's dead in the water. We believe that we've hit the > 133 volumes per storage group. We use MOD-3s. Is there any way around > this? Will reallocating the PRI/SEC help? Reorging? > > Thanks - Ray Gaston > ConEd - DBA [...]
1212 130 36_Re: db2/zos v9.1: new index features13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 21 Oct 2009 15:15:05 -0400551_iso-8859-1 Keep in mind that you may also use the larger page sizes without doing compression in DB2 9 which could help you solve an I/O problem without going through the overhead of compression.
______________________________________________________________________________ David Simpson | Senior Technical Advisor | Themis Education 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com For more information about Themis, visit www.themisinc.com [...]
1343 914 32_Re: (off topic) LISTSERV mystery13_Campbell, Jim26_Jim.Campbell@COURTS.WA.GOV31_Fri, 23 Oct 2009 08:45:47 -0700672_iso-8859-1 Looks like PGP(Pretty Good Privacy) Encryption - are you sending out encrypted emails?
Jim Campbell
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dell'Anno, Aurora Sent: Wednesday, August 12, 2009 10:50 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] (off topic) LISTSERV mystery
Dear lists,
I need your help, please!
you MAY have noticed that basically if you get the digest version of the list's day, my email come out in some mysterious language from outer space, as well as "plain" English (although of course my English is beautiful and most interesting, if I say so myself). [...]
2258 87 41_BLOB Exceeds 133 Volume Limit: Z/OS DB2V815_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 23 Oct 2009 08:48:07 -0400398_us-ascii
Dear Listers... We have a major production problem. PLEASE, can anyone suggest a workaround or solution.
We have a BLOB "billing" table defined in a database that can no longer extent. It's dead in the water. We believe that we've hit the 133 volumes per storage group. We use MOD-3s. Is there any way around this? Will reallocating the PRI/SEC help? Reorging? [...]
2346 90 41_BLOB Exceeds 133 Volume Limit: Z/OS DB2V815_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 23 Oct 2009 14:41:22 -0400451_us-ascii
> Dear Listers... We have a major production problem. PLEASE, can anyone > suggest a workaround or solution. > > We have a BLOB "billing" table defined in a database that can no > longer extent. It's dead in the water. We believe that we've hit the > 133 volumes per storage group. We use MOD-3s. Is there any way around > this? Will reallocating the PRI/SEC help? Reorging? > > Thanks - Ray Gaston > ConEd - DBA [...]
2437 36 43_Z/OS DB2V8... Limit of Disk Pack per SG ???15_Gaston, Raymond17_GastonRay@ORU.COM31_Thu, 22 Oct 2009 15:09:03 -0400628_us-ascii
Dear Listers... Forwarding this for a DBA colleague. Anyone have an idea?
We have a BLOB table defined in a database that can no longer extent. We believe that we've hit the 133 volumes per storage group. Is there any way around this?
Thanks - Ray Gaston ConEd - DBA
--------------------------------------------------------------------
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
2474 36 45_BLOB Table Hit Limit / Need Help / Z/OS DB2V815_Gaston, Raymond17_GastonRay@ORU.COM31_Thu, 22 Oct 2009 16:01:31 -0400414_us-ascii
Dear Listers... We have a major production problem. PLEASE, can anyone suggest a workaround or solution.
We have a BLOB "billing" table defined in a database that can no longer extent. It's dead in the water. We believe that we've hit the 133 volumes per storage group. We use MOD-3s. Is there any way around this? Will reallocating the PRI/SEC help? Reorging? [...]
2511 30 72_AUTO: Bud Greenman/IT/ONGOV is out of the office. (returning 11/02/2009)0_21_BudGreenman@ONGOV.NET31_Fri, 23 Oct 2009 20:59:16 -0400565_US-ASCII I am out of the office until 11/02/2009.
I will respond to your message when I return.
Note: This is an automated response to your message "Re: [DB2-L] db2/zos v9.1: new index features" sent on 10/21/09 3:15:05 PM.
This is the only notification you will receive while this person is away.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
2542 36 28_reverse engineering DSNTIDXx12_Roy Reynolds17_royr@BERKELEY.EDU31_Fri, 23 Oct 2009 16:48:18 +0000604_UTF-8 I use DSN8ED7 to list my running ZPARMs every day and archive them. The report is useful to identify the Install Panels used in creation of the DSNTIJUZ macros. When using the Install/Migration DSNTINST panels, a macro named whatever you choose is created. The default one fro IBM is DSNTIDXA as I recall. This DSNTINST-created macro is used as input to the next DSNTINST session. Some people here before me did not alway us DSNTINST to make changes to DSNTIJUZ. They just changed DSNTIJUZ and reassembled it. Here's the question: does anyone have a process that reads the DSNTIJUZ JCL/macros [...]
2579 121 31_Migrating to Db2v9 for z/0s 1.70_21_BudGreenman@ONGOV.NET31_Wed, 21 Oct 2009 10:38:10 -0400414_us-ascii I tried sending this earlier and have not received a reply that it was sent, so my apologies if this is a duplicate
We recently migrated from v8 to 9v on our development platform. When the developers tried to connect to their datasources on z/os they got the following error: [ibm][db2][jcc][10114][10305] Invalid operation: Explicit COMMIT or ROLLBACK is not allowed when in auto-commit mode [...]
2701 124 33_v9 migration z/os 1.7 and RAD 6.00_21_BudGreenman@ONGOV.NET31_Wed, 21 Oct 2009 09:57:31 -0400369_us-ascii We recently migrated from v8 to 9v on our development platform. When the developers tried to connect to their datasources on z/os they got the following error: [ibm][db2][jcc][10114][10305] Invalid operation: Explicit COMMIT or ROLLBACK is not allowed when in auto-commit mode
We tried falling back to v8 but they are still getting the same error. [...]
2826 33 24_List is not getting mail10_Mark Labby21_mlabby.idug@GMAIL.COM31_Fri, 23 Oct 2009 03:30:57 +0000418_UTF-8 If you have been trying to post to the list you probably have had your messages bounce back. We found there is a problem with the domain name server and it is being addressed as quickly as possible.
I apologize for the delay and thank you for your patience.
Mark
I am trying to see if messages can be posted directly from the website, so not sure you will ever see this. [...]
2860 25 41_Doug Holliday/Alcan is out of the office.13_Doug Holliday23_doug.holliday@ALCAN.COM31_Fri, 23 Oct 2009 22:09:02 -0400658_US-ASCII I will be out of the office starting 10/23/2009 and will not return until 10/27/2009.
I will respond to your message when I return.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups, there is probably one near you! Regional User Groups are your local connection to the Worldwide DB2 User Community _____________________________________________________________________ [...]
2886 69 45_Re: BLOB Exceeds 133 Volume Limit: Z/OS DB2V89_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 23 Oct 2009 23:03:19 -0500526_US-ASCII It would help if you gave a summary of the LISTCAT information - The rest of this is straight guess work. I think your problem is you have lots of volumes where the dataset only has 1 or 2 secondary extents and then moved to the next volume. It also sounds like your storage administrators don't consolidate extents for your DB2 datasets. The best solution is to create a dedicated SMS stogroup with volumes that can't be used for anything else. This will let you reach the DB2 limits without using 133 volumes. [...]
2956 150 32_Re: reverse engineering DSNTIDXx0_17_jtonchick@AOL.COM31_Sat, 24 Oct 2009 00:07:25 -0400460_us-ascii
Roy, the base copy of DSNTIDXA is in the SMP target library SDSNSAMP. If you don't trust that library, then go to the SMP dlib, ADSNSAMP. It should be easy enough to use your report and copy the default member to a new member in SDSNSAMP and edit the new member based on your report. We use member names with the following name mask - ZssidZnn where nn is a sequential assending number that preserves a zparm change history by subsystem. [...]
3107 32 42_BLOB tablespace Exceeds Limits: Z/OS DBAV815_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 23 Oct 2009 08:30:11 -0400382_us-ascii Dear Listers... We have a major production problem. PLEASE, can anyone suggest a workaround or solution.
We have a BLOB "billing" table defined in a database that can no longer extent. It's dead in the water. We believe that we've hit the 133 volumes per storage group. We use MOD-3s. Is there any way around this? Will reallocating the PRI/SEC help? Reorging? [...]
3140 85 46_Re: BLOB tablespace Exceeds Limits: Z/OS DBAV815_Schade, Jeffrey15_JSchade@ISO.COM31_Sat, 24 Oct 2009 02:34:23 -0400549_us-ascii Ray,
Since we converted to SMS we have not had this issue but before the conversion I was always concerned about this and I seem to remember that you could remove some of the existing volumes from the storage group definition in DB2 and add new empty volumes. The ones you removed do not need to be in the definition unless you are trying to do a reorg / load or some other utility that deletes and defines the underlying dataset. This is only a workaround and you need to research further to get a final solution but it should [...]
3226 64 46_Re: BLOB tablespace Exceeds Limits: Z/OS DBAV812_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 24 Oct 2009 09:02:54 +0200465_UTF-8 Hi,
Never encountered before. However - can you move it to mod-9 or mod-27 ...
Isaac Yassin
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gaston, Raymond Sent: Friday, October 23, 2009 2:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] BLOB tablespace Exceeds Limits: Z/OS DBAV8
Dear Listers... We have a major production problem. PLEASE, can anyone suggest a workaround or solution. [...]
3291 81 83_=?UTF-8?Q?=D7=AA=D7=A9=D7=95=D7=91=D7=94=3A_?= [DB2-L] reverse engineering DSNTIDXx14_Michael Kaplan25_micaelkp@NETVISION.NET.IL31_Sat, 24 Oct 2009 11:40:16 +0200425_UTF-8 Roy,
It is impossible to regenerate DSNTIDnn table only from DSNTIJUZ, because this module have't many installation panel's variables.
However, you can review those installation panels and accomplish needed variables to update you DSNTIADxx table.
After all, the value been specified, i.e., for number of databases may vary from your last installation ( it used to calculate EDM pool values). [...]
3373 89 41_Re: Fw: v9 migration z/os 1.7 and RAD 6.012_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 24 Oct 2009 07:46:42 -0700628_us-ascii Did you try upgrading their DB2 connect platforms to V9 as well or are they still on V8?
________________________________ From: "BudGreenman@ONGOV.NET" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wed, October 21, 2009 10:10:37 AM Subject: [DB2-L] Fw: v9 migration z/os 1.7 and RAD 6.0
We recently migrated from v8 to 9v on our development platform. When the developers tried to connect to their datasources on z/os they got the following error: [ibm][db2][jcc][10114][10305] Invalid operation: Explicit COMMIT or ROLLBACK is not allowed when in auto-commit mode [...]
3463 37 29_DB2 z/OS V8 log write problem12_Isaac Yassin22_isaac.yassin@GMAIL.COM31_Sun, 25 Oct 2009 17:43:38 +0000356_- Hi,
Upon moving to V8 the log write activity jumped 400%-500% . It's a 2-way data sharing. There was no other change in the system (DASD, VBP, ...) Has anyone encountered something like that?
For example - a batch job that used to do about 200k inserts in 1 hour, now takes 5 hours. We played with its' performance (WLM) for no avail. [...]
3501 26 29_DB2 z/OS V8 log write problem12_Isaac Yassin22_isaac.yassin@GMAIL.COM31_Sun, 25 Oct 2009 18:01:31 +0000591_- Hi,
Forgot to mention it's V8 CM !
Isaac Yassin
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now open and free for all. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...]
3528 26 29_DB2 z/OS V8 log write problem12_Isaac Yassin22_isaac.yassin@GMAIL.COM31_Sun, 25 Oct 2009 21:51:25 +0000608_- Hi,
Problem solved - IMMEDWRI was YES instead of NO
Isaac Yassin
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now open and free for all. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...]
3555 392 45_[LUW] DB2 9.5 and Netapp I/O performance help16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Mon, 26 Oct 2009 10:50:18 +0800366_us-ascii Calling all (any?) NetApp gurus.
We are testing our new DB2 servers using NetApp storage against our existing production servers which use rather older IBM DS4300 storage. We've been led to believe/expect that the NetApp storage should be significantly faster than the DS4300, but to date we are somewhat disappointed with read I/O performance. [...]
3948 556 49_Re: [LUW] DB2 9.5 and Netapp I/O performance help12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Mon, 26 Oct 2009 01:25:55 -0400471_us-ascii You need to experiment with prefetchsize to make sure you are driving all disks in the aggregate, and possibly set number of disks in aggregate to number of disks for DB2_PARALLEL_IO. This is not your typical storage setup. pg
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gregory Palgrave Sent: Sunday, October 25, 2009 10:50 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] [LUW] DB2 9.5 and Netapp I/O performance help [...]
4505 184 46_Susan Lawson and Dan Luksetich's presentations10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 26 Oct 2009 05:20:09 -0400474_iso-8859-1
Hi DB2 user,
Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com
Last update on Monday, October 26th 2009
Here are several DB2 z/OS presentations of Susan Lawson and Dan Luksetich, available on IDUG website:
- Quit Calling DB2 So Much! by Susan Lawson and Dan Luksetich
- DB2 9 for z/OS: A New Spin on a Classic Database by Susan Lawson [...]
4690 102 46_Re: BLOB tablespace Exceeds Limits: Z/OS DBAV815_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 26 Oct 2009 09:35:55 -0400575_us-ascii
Isaac...
YES! Thanks everyone for your response. We ALTER'd the BLOB tablespace to make the primary/secondary extents smaller. NO reorg necessary. This recovered some of the defragged space. That bought us some time for now. The long term solutions is were going from MOD-3 to MOD-9 or MOD-27's.
Ray
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Isaac Yassin Sent: Saturday, October 24, 2009 3:03 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] BLOB tablespace Exceeds Limits: Z/OS DBAV8 [...]
4793 158 46_Re: BLOB tablespace Exceeds Limits: Z/OS DBAV810_Roger Hecq18_Roger.Hecq@UBS.COM31_Mon, 26 Oct 2009 10:54:14 -0400470_US-ASCII A few thoughts. I too am plagued with several CLOB objects. I would strongly suggest that you transition to SMS managed storage groups, in conjunction with -9s or -27s. That will greatly facilitate your existence. We have also had good results with letting DB2 manage the size of the primary & secondary allocation by specifying -1 for both. Not having to manage storage allows us to make more effective use of our time and focus on more important issues. [...]
4952 189 46_Re: BLOB tablespace Exceeds Limits: Z/OS DBAV815_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 26 Oct 2009 12:15:35 -0400696_us-ascii
Thanks Roger...
Our SG are SMS managed. However, we have not utilized the "pri/sec -1" strategy yet. Something to consider.
- Ray Gaston
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq Sent: Monday, October 26, 2009 10:54 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] BLOB tablespace Exceeds Limits: Z/OS DBAV8
A few thoughts. I too am plagued with several CLOB objects. I would strongly suggest that you transition to SMS managed storage groups, in conjunction with -9s or -27s. That will greatly facilitate your existence. We have also had good results with letting DB2 manage the size of [...]
5142 257 53_Websphere Informaton Integrator / Federated migration18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 26 Oct 2009 12:25:00 -0400348_us-ascii We're currently running what use to be called 'DB2 Information Integrator' v8.2 Fixpak 16 on 2 AIX 64 bit servers. As usual IBM has rebranded, renamed, replaced, redone, re-confused, etc.. when it comes to figuring out what software to download to use for the migration.
One server is for test and the other is for production. [...]
5400 41 11_unsubscribe9_Dee Reins21_rcwingman@COMCAST.NET31_Mon, 26 Oct 2009 19:17:10 +0000628_utf-8
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
5442 375 31_DB2 V8 fail to start in CM Mode18_Ujjal Bhattacharya14_UjjalB@NBK.COM31_Tue, 27 Oct 2009 10:15:49 +0300434_us-ascii Hi there,
In our DR site I was trying to bring DB2 V8 CM mode up ( z/OS 1.9) and since last week I was unable to bring it up in CM mode.
It was working fine in DB2 v8 CM mode in DR site till last week. Basically we ship the Imagecopies and Archive Logs and try bringing it up in Cold Start after clean-up every week and restore the Catalogs , directories and then User tablespaces & Indexes in this order. [...]
5818 125 35_Re: DB2 V8 fail to start in CM Mode0_24_carol.sutfin@REGIONS.COM31_Tue, 27 Oct 2009 03:01:20 -0500487_US-ASCII Did you check your DB2 Loadlib and DSNEXIT Library.
Was there any change to those or your ZPARMS.
Carol Sutfin Corporate DBA Regions Financial Corp. (205)261-5214 carol.sutfin@regions.com
Ujjal Bhattacharya To Sent by: IDUG DB2-L@WWW.IDUGDB2-L.ORG DB2-L cc Subject [DB2-L] DB2 V8 fail to start in CM Mode 10/27/2009 02:15 AM
Please respond to IDUG DB2-L [...]
5944 50 12_(no subject)0_16_DBMSUser@AOL.COM29_Tue, 27 Oct 2009 06:41:43 EDT609_US-ASCII info
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
5995 50 11_Only a Test0_16_DBMSUser@AOL.COM29_Tue, 27 Oct 2009 06:44:17 EDT613_US-ASCII
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
6046 216 35_Re: DB2 V8 fail to start in CM Mode18_Ujjal Bhattacharya14_UjjalB@NBK.COM31_Tue, 27 Oct 2009 14:48:34 +0300529_us-ascii The libraries are unchanged and it worked the last week. Dependency on APF and Linklist is not there as well as our SDSNEXIT is part of the Steplib. Is there any parameter which decides New function Mode. Very unlikely but I am not sure.
Regards Ujjal
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of carol.sutfin@REGIONS.COM Sent: Tuesday, October 27, 2009 11:01 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8 fail to start in CM Mode [...]
6263 46 49_Re: [LUW] DB2 9.5 and Netapp I/O performance help9_Ray Houle32_raynald.houle@SCD.DESJARDINS.COM31_Tue, 27 Oct 2009 12:25:25 +0000611_utf-8 A few years ago, I had the opp. to work on DB2 AIX warehouse built on Net App. We also experienced very ordinary IO performance considering the published capabilities.
In our scenario we finally got the anticipated level of response once we went against the Net App recommendation of the day: 1 file system – 1 container (per TS per partition)... We carved up the appliance into multiple file systems, and definned 3 (for normal) to 5 (for temporary) containers per Tablespace partition. This seamed to enable substantial IO parallelism and finally approach the expected performance levels. [...]
6310 579 35_Re: DB2 V8 fail to start in CM Mode12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM31_Tue, 27 Oct 2009 08:33:36 -0400662_US-ASCII Do you have any other subsystems in NFM? Job DSNTIJNF has to be run to flip to NFM
Ujjal Bhattacharya Sent by: IDUG DB2-L 10/27/2009 08:24 AM Please respond to IDUG DB2-L
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: [DB2-L] DB2 V8 fail to start in CM Mode
The libraries are unchanged and it worked the last week. Dependency on APF and Linklist is not there as well as our SDSNEXIT is part of the Steplib. Is there any parameter which decides New function Mode. Very unlikely but I am not sure. [...]
6890 247 35_Re: DB2 V8 fail to start in CM Mode12_Asamoto, Roy25_Roy.Asamoto@STATEAUTO.COM31_Tue, 27 Oct 2009 08:46:21 -0400556_us-ascii You might check DSNHDECM in DSNTIJUZ. This has the NEWFUN parm.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ujjal Bhattacharya Sent: Tuesday, October 27, 2009 7:49 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8 fail to start in CM Mode
The libraries are unchanged and it worked the last week. Dependency on APF and Linklist is not there as well as our SDSNEXIT is part of the Steplib. Is there any parameter which decides New function Mode. Very unlikely but I am not sure. [...]
7138 154 26_Off topic question on IDMS13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM31_Tue, 27 Oct 2009 08:50:55 -0400725_us-ascii
I am looking at ordering a book on IDMS.
Can anyone advise which on Amazon would be the most helpful for a DBA?
Greatly appreciate any responses.
Bob Knight
________________________________
[http://www.idug.org/images/M_images/idug%20org.jpg]
The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
7293 288 35_Re: DB2 V8 fail to start in CM Mode18_Ujjal Bhattacharya14_UjjalB@NBK.COM31_Tue, 27 Oct 2009 16:20:22 +0300593_us-ascii Checked all of those and there it is NEWFUN=NO.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Asamoto, Roy Sent: Tuesday, October 27, 2009 3:46 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8 fail to start in CM Mode
You might check DSNHDECM in DSNTIJUZ. This has the NEWFUN parm.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ujjal Bhattacharya Sent: Tuesday, October 27, 2009 7:49 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8 fail to start in CM Mode [...]
7582 615 33_sql and error with nulls sql0407n11_Diana Nagel16_dknagel@BEPC.COM31_Tue, 27 Oct 2009 08:34:49 -0500443_us-ascii I am trying to run this update sql and keep getting error noted. Any thoughts on how to fix.
update pp.tidcncmf set (a.trans_date) = (select b.contr_start_dt from pp.tidcnmst b where pp.tidcncmf.contr_fc_code = b.contr_fc_code and pp.tidcncmf.contr_fc_version = b.contr_fc_version and pp.tidcncmf.trans_date < b.contr_start_dt and b.contract_release <> '00000' and b.contr_start_dt > ' ' and b.contract_status = 'ISSUED') [...]
8198 273 38_AW: [DB2-L] Off topic question on IDMS12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 27 Oct 2009 14:59:23 +0100722_iso-8859-1 Bob, take a look at this book:
http://idms-training.com/viewpage.php?page_id=9
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 ---------------------------------------------------------------------- P Think before you print. [...]
8472 576 35_Re: DB2 V8 fail to start in CM Mode28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM31_Tue, 27 Oct 2009 19:46:40 +0530687_US-ASCII Hi Ujjal,
Did you make sure you have the generated DSNHDECP on top in STEPLIB ?
Ujjal Bhattacharya Sent by: IDUG DB2-L 27/10/2009 17:18 Please respond to IDUG DB2-L
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: [DB2-L] DB2 V8 fail to start in CM Mode
The libraries are unchanged and it worked the last week. Dependency on APF and Linklist is not there as well as our SDSNEXIT is part of the Steplib. Is there any parameter which decides New function Mode. Very unlikely but I am not sure. [...]
9049 89 37_Re: sql and error with nulls sql0407n11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 27 Oct 2009 15:23:07 +0100382_ISO-8859-1 my take is that the select finds no matching row for one row in the UPDATE and therefore returns a NULL value from the sub-select and then the UPDATE dies...try adding a COALESCE to the sub-query so that it always returns a value which can hten be updated or make sure you add predicates to the outside so that only rows with "valid" values are found and processed! [...]
9139 83 42_Re: AW: [DB2-L] Off topic question on IDMS15_Chris Hoelscher21_choelscher@HUMANA.COM31_Tue, 27 Oct 2009 10:30:00 -0400663_US-ASCII Bob, if idms is in your future, please consider joining idms-l
subscribe at listserv@listserv.iuassn.com
you might get additional results to your question there
Chris Hoelscher Senior IDMS & DB2 Database Administrator Humana Inc 502-476-2538 choelscher@humana.com
you only need to test the programs that you want to work correctly
The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information. [...]
9223 769 37_AW: sql and error with nulls sql0407n35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 27 Oct 2009 15:41:20 +0100369_iso-8859-1 Diana
You are trying to update column TRANS_DATE with the NULL-value, because the select does not return a value for each row. But Null is not allowed for this column. I am facing often this error, because you forget to exclude all rows in a WHERE-clause, which you SET-assignment has no value for. Add to your update the following where-clause: [...]
9993 50 57_Re: Websphere Informaton Integrator / Federated migration9_Ray Houle32_raynald.houle@SCD.DESJARDINS.COM31_Tue, 27 Oct 2009 14:55:35 +0000408_utf-8 Last year we migrated from Data Joiner to II 9.5!!!
The gotchas we hit related to the new(er) attributes of a 9.5 database, specifically unicode by default. I beleive if you migrate the database this should not be an issue, as the existing code page will be kept. However if you migrate there are numerous 9.5 features that you will not be able to benefit from such as Automatic Storage... [...]
10044 67 26_DB2 Recover Pending Status0_16_DBMSUser@AOL.COM29_Tue, 27 Oct 2009 11:49:53 EDT741_US-ASCII Hello List, The DB2 Utility Guide reference a Recover-Pending (RECP) status, I can not find a Recover-Pending status column in the sysibm.syscopy or sysibm.systablespace or any other db2 catalog tables.
Can anyone help.
Lee
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________ [...]
10112 219 38_AW: [DB2-L] DB2 Recover Pending Status12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 27 Oct 2009 17:04:39 +01001334_iso-8859-1 Use the -DISPLAY DATABASE command to display the current status for an object......
And then take a look at
DB2 Version 9.1 for z/OS > DB2 reference information > DB2 utilities > Additional information for utilities > Advisory or restrictive states
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 ---------------------------------------------------------------------- P Think before you print. [...]
10332 71 30_Re: DB2 Recover Pending Status12_Cuneyt Goksu22_cuneyt.goksu@GMAIL.COM31_Tue, 27 Oct 2009 07:04:33 +0000540_Windows-1252
Its kept in directory and can visible only with db2 commands.
Regards, cuneyt
AVEA BlackBerry® Servisi ile gönderildi.
-----Original Message----- From: DBMSUser@AOL.COM Date: Tue, 27 Oct 2009 11:49:53 To: Subject: [DB2-L] DB2 Recover Pending Status
Hello List, The DB2 Utility Guide reference a Recover-Pending (RECP) status, I can not find a Recover-Pending status column in the sysibm.syscopy or sysibm.systablespace or any other db2 catalog tables. [...]
10404 184 30_Re: DB2 Recover Pending Status11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 27 Oct 2009 17:10:04 +0100464_ISO-8859-1 it's stored in hte DBET which is an internal structure in DB2 and is also written out at every checkpoint. If you do a -DISPLAY DB(xxx) SP(yyy) it will show you what statuses (statii?) that you have RECP, GRECP, LPL etc. etc.
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 [...]
10589 62 30_Re: DB2 Recover Pending Status0_22_DB2information@AOL.COM29_Tue, 27 Oct 2009 12:32:46 EDT674_US-ASCII Roy, Is this DBET anywhere in a IBM SYSIBM catalog/dir tables that we can read, or any way we can get to this information without doing a command -DIS command?
Lee
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________ [...]
10652 196 30_Re: DB2 Recover Pending Status11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 27 Oct 2009 17:52:21 +0100469_ISO-8859-1 sadly no. Your options are limited to these options
1) Write a DISPLAY xxxxx RESTRICT type parser (REXX is not bad but watch out as the EDM Pool might max out when DISPLAY'ing all databases) 2) Write a checkpoint finder/analyzer routine (Tricky...) 3) Write a cross memory APF authorized program to grab the contents and then try and decode what all the flags mean (Even trickier...) 4) Buy a bit of software that does either 1, 2 or 3 for you!! [...]
10849 269 30_Re: DB2 Recover Pending Status14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 27 Oct 2009 12:55:02 -0400425_us-ascii Lee,
You have to use DB2 commands to get that information; Directory (as opposed to Catalog) and Log/BSDS information isn't accessible with SQL.
What are you trying to accomplish? Is it something that you want to see with an application program? Have you considered using REXX and the REXX TSO command interface (OUTTRAP to capture the return from a TSO command like DB2 commands) for your goals? [...]
11119 397 30_Re: DB2 Recover Pending Status14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 27 Oct 2009 13:01:37 -0400501_iso-8859-1 Cuneyt: 1) It's in the DBD, right? From the DISPLAY command it looks like that's where it _has_ to be 2) Where is any of this documented? I'm tearing my hair out trying to find a manual that talks about the contents of the directory.
--Phil Sevetson
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu Sent: Tuesday, October 27, 2009 3:05 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Recover Pending Status [...]
11517 463 30_Re: DB2 Recover Pending Status10_Roger Hecq18_Roger.Hecq@UBS.COM31_Tue, 27 Oct 2009 13:18:02 -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. [...]
11981 119 30_Re: DB2 Recover Pending Status9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 27 Oct 2009 13:00:44 -0500399_iso-8859-1 um, no - it isn't in the DBD. What you are looking for is DBAT information. The only source that I know of is the diagnostics manual (you know, the one that isn't available for download). Short shrift - DBAT is created when an object is opened. DBAT is snapshoted to the log at every checkpoint and when the object is closed. DBAT is not externalized to either catalog or directory. [...]
12101 225 35_LOAD REPLACE, LOAD RESUME, and SORT14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 27 Oct 2009 14:09:53 -0400589_us-ascii Has anyone done any of the following in DB2V9?
1) Benchmark a large LOAD REPLACE to a tablespace with multiple indexes, comparing input sorted into cluster key order versus unsorted input 2) Done the same benchmark, to a tablespace with only a clustering index 3) Benchmark a large LOAD RESUME, to a tablespace with multiple indexes, comparing a load with input sorted into cluster key order versus a load with unsorted input 4) LOAD RESUME to a tablespace with only a clustering index, comparing (etc.) 5) Looked at DFSORT optimization in REORG and REBUILD utilities [...]
12327 160 30_Re: DB2 Recover Pending Status14_Peter Backlund21_BacklundDB2@TELIA.COM31_Tue, 27 Oct 2009 19:33:48 +0100
12488 861 69_Database Exception Table (was RE: [DB2-L] DB2 Recover Pending Status)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 27 Oct 2009 14:42:44 -0400606_iso-8859-1 And, found a manual entry for it in DB2V9.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.admin/db2z_exceptiontablerecord.htm
(http://tinyurl.com/DBET-Record for the email-challenged)
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Backlund Sent: Tuesday, October 27, 2009 2:34 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Recover Pending Status
Mike,
It isn't DBAT (any more) - it is DBET Database exception table
Peter
Mike Bell wrote: [...]
13350 105 30_Re: DB2 Recover Pending Status14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Tue, 27 Oct 2009 13:43:24 -0500558_us-ascii So where is the information about restricted state of an object kept when the corresponding DBAT does not exist e.g. while the object is not open or db2 is down ? Does db2 read the log to find the last DBAT snapshot ? TIA Khalid
um, no - it isn't in the DBD. What you are looking for is DBAT information. The only source that I know of is the diagnostics manual (you know, the one that isn't available for download). Short shrift - DBAT is created when an object is opened. DBAT is snapshoted to the log at every [...]
13456 1007 74_Re: Database Exception Table (was RE: [DB2-L] DB2 Recover Pend ing Status)13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 27 Oct 2009 21:07:27 GMT537_windows-1252 Anybody know if "Advisory" statuses are also stored in the DBET?
You know, like the ICOPY status you might see when you issue:
-DIS DB(*) SP(*) ADVISORY LIMIT(*)
Or are they stored elsewhere?
David Waugh DSW Consulting & Services
Please note: message attached
From: "Sevetson, Phil" To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Database Exception Table (was RE: [DB2-L] DB2 Recover Pending Status) Date: Tue, 27 Oct 2009 14:42:44 -0400 [...]
14464 107 30_Re: DB2 Recover Pending Status14_Peter Backlund21_BacklundDB2@TELIA.COM31_Tue, 27 Oct 2009 22:19:09 +0100
14572 191 24_Native Stored Procedures0_26_John.L.Miller@ATCOITEK.COM31_Tue, 27 Oct 2009 15:22:28 -0600575_US-ASCII Hi All,
My shop is currently running DB2 zOS V8 and are looking at upgrading to V9. One advantage we see is Native Stored Procedures. Unfortunately, I can't find a lot of information to help me estimate the performance improvements that we might see. Has anyone collected some statistics that they'd be willing to share? Things like CPU cost went down x% or zIIP eligible processing saved x% etc. I'd also be very interested to know if Native Stored Procedures relieves some of the overhead caused by the SYSSTAT package, which is a major cost we incur. [...]
14764 132 52_Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 27 Oct 2009 21:44:56 +0000303_utf-8 Hi,
We are currently porting a database from DB2 Aix to DB2 on z/Os (v8 NFM).
We have noticed some of the queries run a bit longer on z/OS than on Aix. For example, we have a query that completes in 6 seconds on Aix, but takes about 11-14 secs on z/OS. Is this to be expected ? [...]
14897 159 28_Re: Native Stored Procedures10_Todd Burch17_toddburch@MAC.COM31_Tue, 27 Oct 2009 16:50:32 -0500332_WINDOWS-1252 Hi John.
John Campbell has a very good Migration (to V9) presentation with an excellent slide that addresses your exact performance question. I don't have a copy of it, but perhaps there is an archived copy from a previous IDUG or IOD conference you can locate.
Not sure about your SYSSTAT question. [...]
15057 397 56_Re: Difference in query runtime between DB2 LUW and z/OS35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 27 Oct 2009 18:06:17 -0400320_utf-8 Well,
You have more than 1 million getpages, a lot of prefetch, a lot of activity in what is probably the sort pool BP7. The high CPU cost shows you are doing a lot of real processing in DB2.
How sure are you that the access path is the same as when on AIX? Compare your execution statistics... [...]
15455 244 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 27 Oct 2009 22:43:09 +0000423_utf-8 Thanks for the reply, Joel,.
I did notice that many getpages, and most of it is in the sort pool.
Now, the table U has about 1.8 million records, with npages about 32,000. The 3 subquery tables have about 3000 to 7000 records each.
The subqueries themselves seem to be OK, but I am trying to understand what happens after that, and why so many getpages are being done in the sort tempspace. [...]
15700 285 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 27 Oct 2009 22:50:09 +0000601_utf-8 Resending with attachment (not sure why it didn't come thru last time).
On Tue, 27 Oct 2009 22:43:09 +0000, Teldb2kals wrote:
>Thanks for the reply, Joel,. > >I did notice that many getpages, and most of it is in the sort pool. > >Now, the table U has about 1.8 million records, with npages about 32,000. >The 3 subquery tables have about 3000 to 7000 records each. > >The subqueries themselves seem to be OK, but I am trying to understand >what happens after that, and why so many getpages are being done in the >sort tempspace. > >(Attached [...]
15986 234 56_Re: Difference in query runtime between DB2 LUW and z/OS9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 27 Oct 2009 18:05:17 -0500432_us-ascii looks like it was a star join on DB2 LUW - you might want to check the access path on Z/os and also check the zparm for star join on DB2 z/os.
Mike HLS Technologies
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Teldb2kals Sent: Tuesday, October 27, 2009 5:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Difference in query runtime between DB2 LUW and z/OS [...]
16221 252 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Dave Nance16_dav1mo@YAHOO.COM31_Tue, 27 Oct 2009 16:31:58 -0700564_iso-8859-1 The tablespace scan on TAB1 is most likely due to the AND/OR condition between cols 1,2,3 or 4. This is assuming you have indexes to support those columns, one for cols 1,2,3 in some combination or at least on one of them and an index on col4, though if you had that combo of indexes, I would think that you would be getting multi-index access. You might want to consider using a union all and write them separately and ensure you have the appropriate indexes to support the SQL statement. Something like: select u.* from tab1 u where u.col1=? and [...]
16474 372 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 27 Oct 2009 22:54:25 +0000353_- QBK PlanNo Method TabName TableNo AccType Matchcols Indname IndexOnly SORTN_UNIQ SORTN_JOIN SORTN_ORDER SORTN_GROUP SORTC_UNIQ SORTC_JOIN SORTC_ORDER SORTC_GROUP PF 1 1 0 Tab1 1 R 0 N N N N N N N N N S 2 1 0 Tab2 2 I 1 IndA1 N N N N N N N N N 2 2 3 0 0 N N N N N Y N Y N 3 1 0 Tab3 3 I 1 IndA2 N N N N N N N N N 3 2 3 0 0 N N N N N Y N Y N 4 1 0 [...]
16847 246 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 28 Oct 2009 00:09:04 +0000375_utf-8 Thanks for that response, Dave. The union does work much faster, and we also investigate that possibility earlier. But, our main issue was, this is a query dynamically created by the application, and so any change would have to be done at the application level, which is our last alternative. (the project was intended to just move the database from Aix to z/OS). [...]
17094 148 30_Re: DB2 Recover Pending Status14_Peter Backlund21_BacklundDB2@TELIA.COM31_Wed, 28 Oct 2009 01:09:44 +0100
17243 257 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 27 Oct 2009 22:43:09 +0000423_utf-8 Thanks for the reply, Joel,.
I did notice that many getpages, and most of it is in the sort pool.
Now, the table U has about 1.8 million records, with npages about 32,000. The 3 subquery tables have about 3000 to 7000 records each.
The subqueries themselves seem to be OK, but I am trying to understand what happens after that, and why so many getpages are being done in the sort tempspace. [...]
17501 298 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 27 Oct 2009 22:50:09 +0000600_utf-8 Resending with attachment (not sure why it didn't come thru last time).
On Tue, 27 Oct 2009 22:43:09 +0000, Teldb2kals wrote:
>Thanks for the reply, Joel,. > >I did notice that many getpages, and most of it is in the sort pool. > >Now, the table U has about 1.8 million records, with npages about 32,000 >The 3 subquery tables have about 3000 to 7000 records each. > >The subqueries themselves seem to be OK, but I am trying to understand >what happens after that, and why so many getpages are being done in the >sort tempspace. > >(Attached [...]
17800 184 24_Native Stored Procedures0_26_John.L.Miller@ATCOITEK.COM31_Tue, 27 Oct 2009 15:22:28 -0600575_US-ASCII Hi All,
My shop is currently running DB2 zOS V8 and are looking at upgrading to V9. One advantage we see is Native Stored Procedures. Unfortunately, I can't find a lot of information to help me estimate the performance improvements that we might see. Has anyone collected some statistics that they'd be willing to share? Things like CPU cost went down x% or zIIP eligible processing saved x% etc. I'd also be very interested to know if Native Stored Procedures relieves some of the overhead caused by the SYSSTAT package, which is a major cost we incur. [...]
17985 291 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 28 Oct 2009 00:59:32 +0000630_utf-8 Mike, we haven't enabled starjoin on z/OS at present, but I will try with that enabled at some stage.
Thanks, Kals On Tue, 27 Oct 2009 18:05:17 -0500, Mike Bell wrote:
> looks like it was a star join on DB2 LUW - you might want to check the >access path on Z/os and also check the zparm for star join on DB2 z/os. > >Mike >HLS Technologies > >-----Original Message----- >From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Teldb2kals >Sent: Tuesday, October 27, 2009 5:43 PM >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] Difference in query runtime between DB2 LUW and [...]
18277 309 56_Re: Difference in query runtime between DB2 LUW and z/OS10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 28 Oct 2009 02:02:33 +0000637_utf-8 Hi Joel, below are some stats from the execution of the SQL on Aix, in case it is useful :
Exec Time: 6.331536 seconds Number of Agents created: 1 User CPU: 2.779256 seconds System CPU: 0.057604 seconds Fetch Count: 10 Sorts: 3 Total sort time: 0 Sort overflows: 0 Rows read: 1053312 Rows written: 7 Internal rows deleted: 0 Internal rows updated: 0 Internal rows inserted: 0 Bufferpool data logical reads: 1058239 Bufferpool data physical reads: 1846 Bufferpool temporary data logical reads: 6 Bufferpool temporary data physical reads: 0 Bufferpool index logical reads: 7 Bufferpool index physical reads: 0 Bufferpool [...]
18587 585 49_Re: [LUW] DB2 9.5 and Netapp I/O performance help16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Wed, 28 Oct 2009 10:18:45 +0800437_us-ascii Thanks, we'll do some testing on that.
Any rule-of-thumb suggestions/recommendations for prefetchsize - would AUTOMATIC be viable, or just experiment with incremental increases?
Cheers
Greg
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Gunning Sent: Monday, 26 October 2009 1:26 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [LUW] DB2 9.5 and Netapp I/O performance help [...]
19173 37 49_Re: [LUW] DB2 9.5 and Netapp I/O performance help16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Wed, 28 Oct 2009 10:32:57 +0800339_utf-8 Hi Ray,
I'll bear that in mind - the NetApp recommendation hasn't changed much...we have gone with recommended 1 file system (1 Home, 1 Data, 1 logs, 1 logs mirror) approach, but left our existing DB2 multiple containers/TS as a) consolidating them was more effort, and b) I also thought it would nobble DB2 parallelism. [...]
19211 2087 37_Re: sql and error with nulls sql0407n15_Coleman, Troy L19_Troy.Coleman@CA.COM31_Wed, 28 Oct 2009 00:03:35 -0400578_us-ascii The where clause is not finding any data. You could test it with a select *.
To correct the problem use the COALESCE or VALUE clause.
update pp.tidcncmf
set (a.trans_date) = (select VALUE(b.contr_start_dt,date,DATE('01/01/0001'))
from pp.tidcnmst b
where pp.tidcncmf.contr_fc_code = b.contr_fc_code
and pp.tidcncmf.contr_fc_version = b.contr_fc_version
and pp.tidcncmf.trans_date < b.contr_start_dt
and b.contract_release <> '00000'
and b.contr_start_dt > ' '
and b.contract_status = 'ISSUED') [...]
21299 778 49_Re: [LUW] DB2 9.5 and Netapp I/O performance help12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Wed, 28 Oct 2009 00:08:20 -0400470_us-ascii Bigger is better in my experience with this storage system. Set db2_parallel_io to the number of physical disks in the aggregate and review prefetch performance. Look at how much time spent waiting for prefetch before and after. If set to automatic, you will see the prefetch size increase on tablespace snapshot. Also, use iostat and determine how much mbytes per sec you are getting off the disk and through the adapters.That might shed some light..Phil [...]
22078 353 56_Re: Difference in query runtime between DB2 LUW and z/OS35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 28 Oct 2009 00:23:30 -0400371_utf-8 Kals,
Interesting, I'm not an LUW person.
The number of overall getpages is similar.
The really big difference seems to be the actual CPU time.
Were these both run on the same class machine? If not, a difference in the CPU engine speed.
Eliminating some of the scanning and getpage activity will greatly reduce the CPU cost. [...]
22432 47 28_Re: Native Stored Procedures4_Jeff18_azdb2dba@YAHOO.COM31_Wed, 28 Oct 2009 05:55:59 +0000591_utf-8 John,
We here at American Express have implemented the usage of Native SQL Stored Procedures under V9 NFM but don't have any real metrics in regards to performance as to whether they perform better under any other type of generation method I.E. DB2/COBOL stored procs. However, the benefit that we are seeing is that the usage of the Native SQL stored procs are better suited to take advantage of ziip processors which can be less costly in the overall scheme of your environment. From what we have seen most of the stored procs which had previously been coded using COBOL [...]
22480 512 74_Re: Database Exception Table (was RE: [DB2-L] DB2 Recover Pend ing Status)11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 28 Oct 2009 07:01:24 +0100333_UTF-8 yep they all are! Here is the list that I use
88 DBET-RESTRICTED VALUE 'ACHKP' 'CHKP ' 'COPY ' 'GRECP' 'LPL ' 'LSTOP' 'PSRBD' 'RBDP ' 'RBDP*' 'RECP ' 'RECP*' 'REORP' 'RESTP' 'RO ' 'STOP ' 'STOPE' 'STOPP' 'UT ' 'UTRO ' 'UTRW ' 'UTUT ' 'WEPR '. 88 DBET-ADVISORY VALUE 'AUXW ' 'ARBDP' 'AREO*' 'AREST' 'DBETE' 'ICOPY'. [...]
22993 410 52_Re: DB2 Recover Pending Status - now changed to [AD]11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 28 Oct 2009 08:26:01 +0100454_ISO-8859-1 I seem to be sending lots of adverts recently....
I notice that this thread has lots of interest...if anyone is interested in buying a small tool for reading and returning the DBET feel free to contact me....
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 [...]
23404 697 35_Re: DB2 V8 fail to start in CM Mode18_Ujjal Bhattacharya14_UjjalB@NBK.COM31_Wed, 28 Oct 2009 11:09:38 +0300411_us-ascii Hi Bala, DSNHDECP is an Assember program and Steplib shouldn't matter. All that should matter is the SYSLIB.Anyway we have copied the DSNHDECP from Production as well. Tried all sort of juggleries possible but the problem persists and seems to be interesting. We have never migrated to NF mode anytime possibly because all those people who matters think we will never need to go beyond DB2 V8 CM. [...]
24102 48 46_Data studio - pureQuery - Hibernate - DB2 z/OS11_Robert Vial21_robert.vial@ICU-IT.NL31_Wed, 28 Oct 2009 08:31:05 +0000389_utf-8 Hi,
For many jears Java/websphere applications are using Cobol stored procedures to retrieve data on the mainframe and the universal type-4 driver is used for this. Development now wants to use Hibernate for quick and flexible application development. As a DBA my main concern is performance and tuning. Has anyone experience with Hibernate and would like to share this? [...]
24151 30 74_Tool or Routine to convert simple Cursor program to Multi Row fetch Cursor14_Amit Chaudhari25_amit.chaudhari@IN.IBM.COM31_Wed, 28 Oct 2009 09:21:39 +0000446_utf-8 Hello,
I want to convert the simple cursor - Cobol DB2 program to multi row fetch cursor. Is there any tool or routine available to do this as there are huge numbers of programs to be converted?
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
24182 788 35_Re: DB2 V8 fail to start in CM Mode28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM31_Wed, 28 Oct 2009 14:58:02 +0530424_US-ASCII Hi Ujjal,
What I meant was, by any chance, do you have the IBM supplied DSNHDECP on top instead of DSNTIJUZ generated DSNHDECP ? i.e, the SYSLMOD dataset of the step, DSNTIZQ of job DSNTIJUZ should be in STEPLIB, if it is different from hlq.sdsnload. Because, hlq.SDSNMACS has default of "newfun=yes". So, I guess, unless you mention in the step DSNTIZP of the job DSNTIJUZ, NEWFUN would be set to YES. [...]
24971 31 28_Re: Native Stored Procedures11_Mike Bracey22_mike_bracey@UK.IBM.COM31_Wed, 28 Oct 2009 10:20:04 +0000555_UTF-8 Hello John You can find some comparative stored procedure costs for different languages including V9 native SQL in this presentation from the 2008 IOD conference: http://www.cdug.org/dec_11_2008_john_campbell_db2_9_migration.pdf The relative CPU figures are given with and without zIIP processors. Regards Mike
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
25003 41 37_Re: sql and error with nulls sql0407n7_Jan tje25_jan.moeyersons@ADELIOR.BE31_Wed, 28 Oct 2009 11:26:52 +0000600_utf-8 On Tue, 27 Oct 2009 08:34:49 -0500, Diana Nagel wrote:
>update pp.tidcncmf >set (a.trans_date) = (select b.contr_start_dt from pp.tidcnmst b >where pp.tidcncmf.contr_fc_code = b.contr_fc_code >and pp.tidcncmf.contr_fc_version = b.contr_fc_version >and pp.tidcncmf.trans_date < b.contr_start_dt >and b.contract_release <> '00000' >and b.contr_start_dt > ' ' >and b.contract_status = 'ISSUED') >
Is it really your intention to set that value (provided you find one -- which by the looks of that -407 doesn't seem to be the case) on ALL rows of pp.tidcncmf ? [...]
25045 52 45_APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW9_Ray Houle32_raynald.houle@SCD.DESJARDINS.COM31_Wed, 28 Oct 2009 12:21:44 +0000506_utf-8 I was horrified to learn that DB2 approximates Timestamp Arithmetics. For example calculating the duration between Feb 1st and Mar 1st should return either 28 or 29 days depending on the year… in this case it returns 30!!!
VALUES ( CHAR( TIMESTAMP ( '2008-03-01-01.01.01.000000') - TIMESTAMP ( '2008-02-01-01.01.01.000000')) -- returns 1 month.. , TIMESTAMPDIFF (16, CHAR( TIMESTAMP ( '2008-03-01-01.01.01.000000') - TIMESTAMP ( '2008-02-01-01.01.01.000000'))) -- returns 30 days !!! ) [...]
25098 56 78_Re: Tool or Routine to convert simple Cursor program to Multi Row fetch Cursor9_Don Leahy19_don.leahy@LEACOM.CA31_Wed, 28 Oct 2009 08:47:27 -0400622_ISO-8859-1 I seriously doubt it. Converting an old program to support multi-row fetch is not a trivial exercise. It is quite likely that the program's logic will be seriously affected by the change.
On Wed, Oct 28, 2009 at 5:21 AM, Amit Chaudhari wrote: > Hello, > > I want to convert the simple cursor - Cobol DB2 program to multi row fetch > cursor. Is there any tool or routine available to do this as there are huge > numbers of programs to be converted? > > _____________________________________________________________________ > > * IDUG North America * Tampa, Florida, * May [...]
25155 163 81_display the contents of the parameters of a stored procedure when they are called14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 28 Oct 2009 14:13:31 +0100412_us-ascii Hi,
We are on z/Os v1.10 and db2 v8.1 NFM
Does anyone know an automatic way to display the contents of the parameters of a stored procedure when they are called ? Instead of displaying, it could also be good to keep the contents in a file, ...
greetings,
Patrick Steurs Dba at Central Bank of Belgium - Eurosystem [...]
25319 118 57_AW: [DB2-L] APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 28 Oct 2009 14:18:07 +0100808_iso-8859-1 Expressing a duration resulting from DATE subtraction as a total number-of-days works like this:
SELECT DATE('03/01/2009') - DATE('12/01/2009')
And: Use the DAYS function to return the exact number of days between those two dates, like this:
SELECT DAYS('03/01/2009') - DAYS('12/01/2009')
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 ---------------------------------------------------------------------- [...]
25438 29 85_Re: display the contents of the parameters of a stored procedure when they are called14_Nancy Stafford24_nancy.stafford@AFBIC.COM31_Wed, 28 Oct 2009 13:28:23 +0000428_utf-8 If you display in the stored proc, it comes out in your SDSF started task. Our standard is that we only do that in test...it seems to cause contention. You could write to a db2 table.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
25468 153 49_Re: APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 28 Oct 2009 14:37:28 +0100288_UTF-8 it depends... If you want super precise then yes you must get the "duration" and calculate out from that however many "units" (Days, Minutes, Seconds, Lengths of String etc.) you are interested in. I do this all the time down to .000001 of a second as I *like* precise ! ! ! ! [...]
25622 33 49_Re: APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW9_Ray Houle32_raynald.houle@SCD.DESJARDINS.COM31_Wed, 28 Oct 2009 13:47:46 +0000882_utf-8 I found the following article, authored by Paul Yip in 2003 with valuable UDF suggestions to adress the issue.
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip 3.html?S_TACT=105AGX01&S_CMP=TOPTEN
Cheers,
Ray
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...]
25656 576 61_Re: AW: [DB2-L] APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 28 Oct 2009 15:14:50 +0100551_ISO-8859-1 yes, but if you want say seconds then when you subtract the two timestamps you get a field lke this
SELECT CURRENT TIMESTAMP , CURRENT TIMESTAMP - TIMESTAMP('2009-10-06-11.22.33.456789') FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+--------
---------+---------+---------+---------+---------+---------+-------- 2009-10-28-15.03.27.716809 22034054.260020
Now you must "decode" the duration to get the units you actually want by multiplying out bits of the duration.... [...]
26233 44 85_Re: display the contents of the parameters of a stored procedure when they are called10_Todd Burch17_toddburch@MAC.COM31_Wed, 28 Oct 2009 09:34:21 -0500310_US-ASCII I would 2nd the suggestion to write to a DB2 table.
You can get into all sorts of grief writing to a spooled dataset from a WLM started task when NUMTCB > 1.
DB2 takes care of the serialization issues and a ton more that you won't have to code (or code around) in your stored proc. [...]
26278 60 85_Re: display the contents of the parameters of a stored procedure when they are called14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 28 Oct 2009 16:17:21 +0100500_us-ascii Hi,
I was looking for an automatic solution , without additional coding. Otherwise I have a job until the end of this year :-)
greetings,
Patrick Steurs Dba at Central Bank of Belgium.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Todd Burch Sent: woensdag 28 oktober 2009 15:34 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] display the contents of the parameters of a stored procedure when they are called [...]
26339 101 49_Re: APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW0_26_John.L.Miller@ATCOITEK.COM31_Wed, 28 Oct 2009 09:26:11 -0600574_US-ASCII This UDF works well to give you the difference in seconds.
CREATE FUNCTION DURATION_SEC (start TIMESTAMP, end TIMESTAMP) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN (DAYS(end) - DAYS(start)) * 86400 + (MIDNIGHT_SECONDS(end) - MIDNIGHT_SECONDS(start));
Thanks, John Miller
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ray Houle Sent: Wednesday, October 28, 2009 6:22 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] APPROXIMATE Timestamp Arithmetics ?!? DB2 LUW [...]
26441 40 9_Imagecopy3_tom16_tabbott@HESC.COM31_Wed, 28 Oct 2009 16:10:42 +0000370_utf-8 We are at DB2 Version 8 on a Z/os mainframe.
An Imagecopy job aborted because of a hardware issue. The hardware issue was resolved. The Imagecopy utility sat in a stopped status. We want to continue with our Imagecopy.
Our (limited) testing showed that re-submitting the Imagecopy had the same results as restarting the Imagecopy. Is this true? [...]
26482 81 13_Re: Imagecopy13_Hampton, Kirk26_kirk.hampton@CAPGEMINI.COM31_Wed, 28 Oct 2009 12:29:04 -0400423_us-ascii I believe, Yes, automatic restart of failed utilities, without having to say RESTART in the JCL, is a new feature of V8
________________________________________ From: IDUG DB2-L [DB2-L@IDUGDB2-L.ORG] On Behalf Of tom [tabbott@HESC.COM] Sent: Wednesday, October 28, 2009 11:10 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Imagecopy
We are at DB2 Version 8 on a Z/os mainframe. [...]
26564 40 13_Re: Imagecopy13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 28 Oct 2009 17:38:10 +0100436_utf-8 Tom,
It's been awhile since I ran IBM utilities (can't think why...) but if I remember correctly the default utility behaviour on utility rerun is to attempt restart. So yes, this is new with DB2 V8.
Cheers,
Raymond
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of tom Sent: 28 October 2009 16:11 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Imagecopy [...]
26605 132 85_Re: display the contents of the parameters of a stored procedure when they are called10_Todd Burch17_toddburch@MAC.COM31_Wed, 28 Oct 2009 11:39:29 -0500336_US-ASCII I was just looking at SYSIBM.SYSPARMS. I'm thinking a small app could be written that parses SYSPARMS for a given stored procedure (or UDF) and it could generate both the call statement, the DDL, and the SQL procedure that would populate the created table.
The flow would be: ("app" = this program I am describing) [...]
26738 78 13_Re: Imagecopy11_Mike Turner19_mike.turner@GMX.COM31_Wed, 28 Oct 2009 16:45:44 -0000635_UTF-8 Hi Tom
From V8, when you re-submit a utility that previously failed it will automatically restart (unless you -TERM it first) without any JCL changes being needed.
Regards Mike Turner Email: mike.turner@gmx.com Tel: +44 (0)1565-873702 Web: www.michael-turner.ltd.uk Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44. ----- Original Message ----- From: "tom" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, October 28, 2009 4:10 PM Subject: [DB2-L] Imagecopy [...]
26817 264 67_DB2 V8: Need to Encrypt DB2 tapes using ASPG's Megacryption Product12_Clark, Kevin22_Kevin.Clark@BCBSDE.COM31_Wed, 28 Oct 2009 15:15:19 -0400656_us-ascii All,
Anyone using ASPG's MegaCryption product to encrypt DB2 image copy tapes?
We are using MERGECOPY to create the DR tapes now and need to add a step or exit.
I will also contact the vendor, but wanted any best practices from the distinguish list.
Kevin .
This e-mail message and any attachments transmitted with it are confidential and are intended solely for the use of its authorized recipient(s). If you are not an intended or authorized recipient, you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the information contained in this e-mail [...]
27082 78 50_Re: Data studio - pureQuery - Hibernate - DB2 z/OS12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Wed, 28 Oct 2009 15:25:18 -0400355_utf-8 What I find with Hibernate is that the actual SQL is hidden from the developers through fact of using Hibernate and it is then harder a DBA to associate troublesome SQL back to the appl, however it can be done but requires more coordination on both ends when it comes to problem determination. Lots of places using Hibernate, good or bad. Phil [...]
27161 299 71_Re: DB2 V8: Need to Encrypt DB2 tapes using ASPG's Megacryption Product12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Wed, 28 Oct 2009 15:54:40 -0500654_us-ascii Brief advertisement - the BMC Recovery Management for DB2 solution has the capability to encrypt/decrypt image copies using either DES (64bit) or AES (128bit). Please contact me directly at rick_weaver@bmc.com if you would like more information.
Regards,
Rick Weaver Product Manager DB2 z/OS Solutions BMC Software
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Clark, Kevin Sent: Wednesday, October 28, 2009 2:15 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8: Need to Encrypt DB2 tapes using ASPG's Megacryption Product [...]
27461 79 50_Re: Data studio - pureQuery - Hibernate - DB2 z/OS26_SUBSCRIBE DB2-L P. Titzler19_ptitzler@US.IBM.COM31_Wed, 28 Oct 2009 20:58:35 +0000526_utf-8 Hi Robert,
Let me start with a disclaimer. I am part of the technical enablement team for pureQuery.
With Hibernate, SQL is processed dynamically, unless of course the code calls stored procedures. You'll face the usual DBA challenges associated with that. The other aspect that is noteworthy is that Hibernate generates SQL on the fly. This essentially means that you may see SQL that nobody in development has ever coded or optimized. Tracking it back to the source code module can be quite tricky. [...]