1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2006, week 4
2 22 35_Move a db2 db from Windows to Linux16_Squeak Smalltalk20_wallenberg@GMAIL.COM31_Wed, 22 Nov 2006 09:20:45 +0100622_ISO-8859-1 Hi,
Any suggestions to copy a db2 db (350 Gb) from windows to Linux ?
We will probably use a load tool.
Best Regards W.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
25 57 45_Invoking legacy cobol - db2 program from JAVA14_Senthilkumar K30_senthilkumar.kaliappan@TCS.COM31_Wed, 22 Nov 2006 02:29:14 -0600336_ISO-8859-1 Hi All ,
We are getting a SQLCODE of - 981 when trying to execute EXEC SQL inside COBOL program which is invoked from Java.
Our requirement is to use a Database connection that is initiated by Java program to run SQL statements written in legacy COBOL as EXEC SQL statements.
Error message: [...]
83 192 12_STOPP Status37_Mohamed Arif (HCL Financial Services)18_mohamedarif@HCL.IN31_Wed, 22 Nov 2006 14:49:45 +0530729_iso-8859-1
Hi,
When I tried to copy production tablespaces to test tablespaces using DSN1COPY, it gave "CONTENTION WITH DB2DDBM1". So I issued the STOP DATABASE command for that test database. But it is in STOPP status. when I tried of -DIS DATABASE USE ,it shows
DSNT360I -DB2D *********************************** DSNT361I -DB2D * DISPLAY DATABASE SUMMARY * GLOBAL USE DSNT360I -DB2D *********************************** DSNT362I -DB2D DATABASE = IKHDB05 STATUS = STOPP DBD LENGTH = 92864 DSNT397I -DB2D NAME TYPE PART STATUS CONNID CORRID USERID -------- ---- ---- ------------------ -------- ------------ -------- IKHTS01 TS RW DB2D 020.STOPDB09 SYSOPR IKHTS02 TS RW DB2D 020.STOPDB09 SYSOPR IKHTS03 [...]
276 227 16_Re: STOPP Status13_Michael Ebert18_mebert@AMADEUS.COM31_Wed, 22 Nov 2006 10:51:09 +0100562_US-ASCII Once all cqw32.exe instances releases their resource (do a COMMIT), the STOPP should be converted into a STOP. However, in the meantime, all other threads trying to access the TS will get a "resource unavailable". Having some experience with the way that servers are programmed, I predict that those two threads will never go away on their own. So you can a) kill the threads, or b) cancel your STOP command. That's easy if you've issued it from a batch, it just takes a long time. If you've issued it interactively, it will take a very long time. [...]
504 389 16_Re: STOPP Status11_Ivan Losada22_ivan.losadajuan@BDE.ES31_Wed, 22 Nov 2006 10:56:51 +0100349_iso-8859-1
Hi,
The DB2 command timeout is 10 times IRLMWAIT (using default this will be 10 min.) DB2 will retry to stop the object 15 times (every 10 min.) If cannot stop the object(cause of the locks) within this time (default 2h30m) the obejct will remain in STOPP status although cqw32.exe instances releases their resources. [...]
894 14 73_Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 22 Nov 2006 04:13:10 -0600581_- Hi
Do you have the explain-output for the statement and the cardinality for the table and column COL?
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
909 25 37_DSNUTILU - NOT INVOKED APF AUTHORIZED14_Senthilkumar K30_senthilkumar.kaliappan@TCS.COM31_Wed, 22 Nov 2006 05:43:24 -0600770_- Hi All,
I am getting the following error during execution of DB2v8 IVP Job DSNTEJ6R.
DSNU003I DSNUTILU - NOT INVOKED APF AUTHORIZED
I checked that joblib and steplib libraries were APF authorized only. But ia mgettig the above error .
Could u please pass some light on this issue?
Regards Senthil K
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on [...]
935 303 16_Re: STOPP Status37_Mohamed Arif (HCL Financial Services)18_mohamedarif@HCL.IN31_Wed, 22 Nov 2006 17:16:41 +0530465_iso-8859-1 Hi,
How can I cancel the STOP command..? When I issued -START DATABASE (dbname) ACCESS (RW), I got the message DSN9023I -DB2D DSNTDDIS 'START DATABASE' ABNORMAL COMPLETION
Pls suggest.
Thanks, Arif
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Michael Ebert Sent: Wednesday, November 22, 2006 3:21 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] STOPP Status [...]
1239 139 16_Re: STOPP Status13_Michael Ebert18_mebert@AMADEUS.COM31_Wed, 22 Nov 2006 13:03:42 +0100452_US-ASCII Hi,
if you issued it interactively, you can try to cancel it through a monitor product, like Omegamon. I don't remember whether this actually worked; if yes, you'll still have to wait for up to 10 times the IRLM timeout value (in batch, you'd only have to wait for at most one IRLM timeout value). If this doesn't work, I think your only option is to wait for the command to time out, which takes 150 times the IRLM timeout value. [...]
1379 244 16_Re: STOPP Status13_Bell, Raymond22_raymond.bell@LANDG.COM31_Wed, 22 Nov 2006 12:07:48 -0000515_- Guys,
Pretty sure there's no way out, other than to kill the tasks holding the locks stopping your -STO command from executing. So if it's CICS transactions (release(deallocate)?) they'll need to be cancelled from within CICS first.
Sorry.
Raymond Bell Database Administrator
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Michael Ebert Sent: 22 November 2006 12:04 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] STOPP Status [...]
1624 49 73_Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files31_Douwe van Sluis, Infodemic B.V.26_d.b.van.sluis@INFODEMIC.NL31_Wed, 22 Nov 2006 13:51:10 +0100579_iso-8859-1 The (Visual) EXPLAIN output shows a tablespace-scan with sequential prefetch. Cardinality of the COL is very high (COL is actually a weigth in kilo's, which needs to become grams). Rowcount is about 2 M.
Vriendelijke groet, Douwe van Sluis, Infodemic B.V.
-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Walter Jani ßen Verzonden: woensdag 22 november 2006 11:13 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: Re: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement using the work files [...]
1674 43 41_Re: DSNUTILU - NOT INVOKED APF AUTHORIZED13_Palko, George16_gpalko@OPERS.ORG31_Wed, 22 Nov 2006 07:57:20 -0500422_iso-8859-1 Hi Senthil, If I'm not mistaken, ALL the libraries within the steplib will have to be APF'd. Try it. It should resolve your issue.
Regards, George
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Senthilkumar K Sent: Wednesday, November 22, 2006 6:43 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DSNUTILU - NOT INVOKED APF AUTHORIZED [...]
1718 185 58_Antwort: Re: [DB2-L] DSNUTILU - NOT INVOKED APF AUTHORIZED11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 22 Nov 2006 14:06:13 +0100580_us-ascii perhaps the RUNLIB.LOAD as well ?!?!?
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de
"Palko, George" Gesendet von: DB2 Data Base Discussion List 22.11.2006 13:57 Bitte antworten an DB2 Database Discussion list at IDUG
An: DB2-L@WWW.IDUGDB2-L.ORG Kopie: Thema: Re: [DB2-L] DSNUTILU - NOT INVOKED APF AUTHORIZED [...]
1904 128 25_Comment out SQL Statement14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Wed, 22 Nov 2006 15:28:45 +0200394_us-ascii Dear listers hi
I have in a program an SQL statement comment out , but it is written in our Plan_Table and in SYSIBM.SYSPACKSTMT . I also run a query against Plan_Table in order to find the queries within our Cobol programs that do Table Space Scan and I get this comment out SQL statement .Is there a way to exclude all comment out statements? We are in DB2 V7.1 for z/OS [...]
2033 237 16_Re: STOPP Status11_Hugh Lapham26_hugh.lapham@RCMP-GRC.GC.CA31_Wed, 22 Nov 2006 09:59:48 -0500627_US-ASCII Hi all,
We have been experiencing this recently. My observations indicate that a thread may start up and process a few SQL statements and then sit there doing nothing at all (sometimes for hours?) before finally terminatiing. The first question I have is Is there a legitimate reason for servers to leave things hanging in this manner -- particularly considering that locks seem to be retained? Assuming that there is no legitimate reason (or no GOOD reason ;-), I would like to make an attempt to prevent this situation in our production environments. Manually killing threads hardly seems appropriate as [...]
2271 27 38_DECLARED GLOBAL TEMP TABLE and INDEXES10_John Lantz20_john.p.lantz@FRB.GOV31_Wed, 22 Nov 2006 13:14:10 -0600455_- I've got a query that uses a temp table that is creaed within a stored procedure. It's JOIN'ed several times in a query so an index was created on the temp table. However, looking at the execution via Apptune as well as doing an explain - we see that the optimizer is not using the index that was created. In trying various things, I explained the same query using a actual defined table and index. That explain shows that the index would be used. [...]
2299 66 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Wed, 22 Nov 2006 19:33:01 +0000358_- Hi,
Have you tried creating a static table instead of a delcared temp table and seeing the effect in the store proc.
You need to check allso the index colnames you are using as the optimizer probably thinks it's cheaper not using the index.
You could also up the optimisation level when creating the proc or possibly lowering it. [...]
2366 20 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES10_John Lantz20_john.p.lantz@FRB.GOV31_Wed, 22 Nov 2006 13:29:20 -0600423_- For the purposes of the explain, I did create a static table/index and saw that the index would be used.
Unfortunately, the procedure can't use a static table in real life. We want each execution of the routine to see only it's own rows, so a temp table is the perfect solution. We would have to add considerable logic into the routine to isolate the cursor to it's own rows if a static table was being used. [...]
2387 27 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES15_Patrick Bossman18_bossman@US.IBM.COM31_Wed, 22 Nov 2006 13:32:32 -0600369_- Hello, If you create an index on DGTT I recommend creating the index as clustering. Clusterratiof is not one of the statistics collected for indexes on DGTTs, and the default clusterratiof for the clustering index is favorable (0.95) while the clusterratiof for non-clustering indexes is not (0.0). Of course, for index only access, clusterratiof is irrelevant. [...]
2415 17 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES17_Chris Worthington20_cworthi@AGRIBANK.COM31_Wed, 22 Nov 2006 13:56:26 -0600287_- Pat, When you refer to the "prepare of the SQL which references the DGTT" are you suggesting that there may be some situations where (even in a statically bound package like a stored procedure in this case) it may be better to use dynamic SQL for statements referencing the DGTT? [...]
2433 19 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES15_Patrick Bossman18_bossman@US.IBM.COM31_Wed, 22 Nov 2006 14:22:15 -0600354_- Hi Chris, SQL which references DGTTs is incrementally bound at initial execution time. Make the open of the statement which references the DGTT occur after the population of the DGTT.
One other note, if you create or drop an index on the DGTT, this will drive any SQL which references that DGTT to be incrementally bound at next execution. [...]
2453 353 16_Re: STOPP Status13_Kenny Fogarty23_kenny.fogarty@GMAIL.COM31_Wed, 22 Nov 2006 21:20:33 -0000425_iso-8859-1 Doesn't the CTHREAD DSNZPARM control how long these inactive threads hang about in a system? I recently worked at a site where CTHREAD wasn't active, and we had instances of distributed threads that were hanging about, and caused a few batch failures because jobs couldn't get locks on the objects. We ended up cancelling the threads and restarting the batch jobs, which ran to completion shortly afterwards. [...]
2807 397 16_Re: STOPP Status13_Kenny Fogarty23_kenny.fogarty@GMAIL.COM31_Wed, 22 Nov 2006 21:26:03 -0000685_iso-8859-1 Sorry folks,
For CTHREAD replace with IDTHTOIN! I'm tired.
_____
From: Kenny Fogarty [mailto:kenny.fogarty@gmail.com] Sent: 22 November 2006 21:21 To: 'DB2 Database Discussion list at IDUG' Subject: RE: [DB2-L] STOPP Status
Doesn't the CTHREAD DSNZPARM control how long these inactive threads hang about in a system? I recently worked at a site where CTHREAD wasn't active, and we had instances of distributed threads that were hanging about, and caused a few batch failures because jobs couldn't get locks on the objects. We ended up cancelling the threads and restarting the batch jobs, which ran to completion shortly afterwards. [...]
3205 179 34_Re: estimating real storage for V810_IBMsysProg25_IBMsysProg@GEEK-SITES.COM31_Wed, 22 Nov 2006 17:49:39 -0500586_iso-8859-1 With the exception of very small DB2 systems there is a very easy way to do this storage estimate. The real storage requirement for a DB2 System (not including applications, DDF, WLM) is 2 gig.
This is not exactly rocket science. Consider the following, One of the biggest benefits of V8 is virtual storage constraint relief. The pre V7 world was constrained by 31 bit addressing and the victim was the DBM1 address space. Moving from V7 to V8 allowed the DBM1 to fit! DB2 system address spaces are paging intolerant as the address spaces are multi tasking and a [...]
3385 44 29_Re: Comment out SQL Statement10_Dave Nance16_dav1mo@YAHOO.COM31_Wed, 22 Nov 2006 17:17:42 -0800369_iso-8859-1 I think you may mean that you have commented out the call to this sql statement, so it will not be executed. There is still an exec sql and a valid sql statement. To not have it in plan table and sysstatement, the entire statement must be commented out. Like I said this is a guess, but something I have answered for developers a few times in the past. [...]
3430 195 73_Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM31_Thu, 23 Nov 2006 09:57:23 +0100574_iso-8859-1 Are there any triggers defined on the table? Transition variables for the trigger are stored in work files.
Mauro Moschelli SanPaoloIMI S.p.A.
IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Douwe van Sluis, Infodemic B.V. Sent: Tuesday, November 21, 2006 10:15 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement using the work files [...]
3626 116 19_Re: DB2 Z/OS V8 NFM12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 23 Nov 2006 08:47:59 -0800416_iso-8859-1 Tom, That may be true that there are uses for MQTs for non-accurate data. But you can't use them to improve performance on any tables where you might potentially need accurate current data. So the utility of them is nowhere near what IBM has been promoting.
And the user doesn't know that there is a MQT. All he knows is that he's getting different answers on a working query for some reason. [...]
3743 261 73_Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files31_Douwe van Sluis, Infodemic B.V.26_d.b.van.sluis@INFODEMIC.NL31_Thu, 23 Nov 2006 18:59:20 +0100553_us-ascii We run RUNSTATS and there was no use of the work files. I am still puzzled why the work files ever got used.
The table is not using any triggers.
Vriendelijke groet, Douwe van Sluis, Infodemic B.V.
-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Douwe van Sluis, Infodemic B.V. Verzonden: dinsdag 21 november 2006 22:15 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: [DB2-L] DB2 V8 NFM - Why is this simple UPDATE statement using the work files [...]
4005 102 73_Re: DB2 V8 NFM - Why is this simple UPDATE statement using the work files15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Thu, 23 Nov 2006 20:30:35 +0100410_us-ascii Hi Douwe,
from the SQL Reference (UPDATE statement):
"Result table. After the base table is updated, the row is re-evaluated and updated in the temporary result table. At this time, it is possible that the positioned update changed the data such that the row does not qualify the search condition, in which case the row is marked as an update hole for subsequent FETCH operations." [...]
4108 187 19_Re: DB2 Z/OS V8 NFM11_Suresh Sane21_data_arch@HOTMAIL.COM31_Thu, 23 Nov 2006 17:45:05 -0600521_- Myron,
I think you are misunderstanding the intent of MQTs. They work well in a warehouse environment, the main reason for their existence. In an OLTP environment, you have static & dynamic sql. Static never gets auto-query-reqwrite. You get what you asked for - real table or mqt as you sepcifiy (knowing the data may be inaccurate). For dynamic again, you declare your intentions that you can tolerate data that is inaccuare by specifying current refresh age = any. If you don't, DB2 honors your request. [...]
4296 232 19_Re: DB2 Z/OS V8 NFM11_Tom Moulder30_tom.moulder@TREXASSOCIATES.COM31_Thu, 23 Nov 2006 22:57:46 -0600320_US-ASCII Let's take these statements in order.
First, you use the term "non-accurate" data and I have a hard time with that description. It may not be the results that you were looking for because it is not current; however, according the description of an MQT when using DB2 for z/OS, the data is accurate. [...]
4529 31 30_[DB2 Z/OS] Bufferpool clusters0_19_mike.holmans@BT.COM31_Fri, 24 Nov 2006 13:46:46 -0000382_utf-8 OK, so I use my Bufferpool Tool and I come up with an arrangement of objects in various pools, using the helpful groupings provided by the cluster analysis.
And lo and behold, the I/O rates drop and the CPU comes down and everyone is happy.
My only problem is that cluster analysis is just so much mumbo-jumbo to me. I don't really understand why it works. [...]
4561 108 34_Re: [DB2 Z/OS] Bufferpool clusters11_Hugh Lapham26_hugh.lapham@RCMP-GRC.GC.CA31_Fri, 24 Nov 2006 09:00:37 -0500397_US-ASCII One quick and easy answer . . . Do they have one or more express lines at the grocery store?Do they have a dedicated business teller or two at the bank?Similarly with traffic lanes .... separate lanes for trucks / cars / buses / bicyclesIf you want something more scientific, I'm sure there are several people on the list who could write (and have written) books on the subject ;-)) [...]
4670 106 40_Stored procedure/host variable/substring15_Perry O'Connell28_Perry.O'Connell@TOYOTAUK.COM31_Fri, 24 Nov 2006 14:55:22 +0000420_US-ASCII Does anyone know why this doesn't work?
its in a SQL Stored Procedure , we run DB2 V7 Zos 1.4
e.g.
create procedure sysproc.foo (in partkey char(5))
-- - - - - - -
gubbins
- - - - - - -- -- - -
Select foobar1 from foobar where
foobar1 like concat ( substr(partkey,1,5), '%' )
--more gubbins --- [...]
4777 53 44_Re: Stored procedure/host variable/substring0_18_IWANT2BEME@AOL.COM29_Fri, 24 Nov 2006 10:25:17 EST638_US-ASCII
Have you tried adding additional parenthesis?:
foobar1 like concat ((substr(partkey,1,5)), '%' )
Jacquie
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
4831 37 62_DFSORT Parameter DYNAUTO=IGNWKDD DB2 8.1 in Compatibility Mode7_Ale Eba16_cielid@YAHOO.COM31_Fri, 24 Nov 2006 07:33:43 -0800621_iso-8859-1 Hello, Is anyone using DYNAUTO=YES for DFSORT module ICEAM2? This is recommended by APAR II14047. According to the APAR this parameter will allow dynamic allocation of work datasets even if JCL work datasets are present.
Is there any real benefit of DFSORT using dynamically allocated work datasets compared to JCL defined work datasets? I ran REORG SHRLEVEL NONE for a tablespace with one table and one index. The table has twenty million rows. One run was with SORTDEVT SYSDA SORTNUM 10 and without JCL defined work datasets and the second was with JCL defined work dataset. I didn’t observe any [...]
4869 146 44_Re: Stored procedure/host variable/substring9_Agus Kwee19_askwe@OPTONLINE.NET31_Fri, 24 Nov 2006 16:03:58 +0000546_us-ascii Perry,
According to the SQL Reference Manual about LIKE predicate, you cannot use nested function in the predicate: LIKE concat(substr(partkey,1,5),'%') try to change it to: LIKE substr(partkey,1,5) concat '%' It worked for me when I tested it using SPUFI.
Regards, Agus Kwee Themis Training http://www.themisinc.com
----- Original Message ----- From: Perry O'Connell Date: Friday, November 24, 2006 10:06 am Subject: [DB2-L] Stored procedure/host variable/substring To: DB2-L@WWW.IDUGDB2-L.ORG [...]
5016 448 34_Re: [DB2 Z/OS] Bufferpool clusters35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 24 Nov 2006 11:36:35 -0500425_UTF-8 Hi Mike,
I wish there was a simple one or two liner that would make everything crystal clear, but it's a bit more complex than that. So - let's give it shot and see how it sits with you, and we can go around a few more times if necessary. Just for other readers edification here, Mike's question is directed specifically about the Buffer Pool Tool cluster analysis feature of simulation/prediction results. [...]
5465 81 34_Re: [DB2 Z/OS] Bufferpool clusters9_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 24 Nov 2006 10:57:53 -0600564_US-ASCII Maybe Joel will reply also but there is a fair amount of mathmatics involved here.
The non-math answer is you try to keep the favorite mice away from the elephants. When a program hitting a big table gets active, you want it to compete with other tables that have similar access requirements. The second target is to keep pages in the buffer pool long enough to reuse them. You choose the tables to group together to reduce the probability of flushing a page from the buffer pool a few milliseconds before another transaction needs that page. [...]
5547 156 62_DFSORT Parameter DYNAUTO=IGNWKDD DB2 8.1 in Compatibility Mode17_McCormack, Mark A27_mamccormack@STATESTREET.COM31_Fri, 24 Nov 2006 12:44:56 -0500398_us-ascii Ale,
If you choose to use explicitly allocated sortworks via dd stmts in jcl, you must answer two questions. How many sortworks are needed? How big should they be? If you underallocate them, you may experience B37s and DB2 utility failure. If you overallocate them, you waste dasd space. Will using too much space conflict with other concurrently executing users of that dasd? [...]
5704 51 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES11_Mike Kalena16_mkalena@BEAR.COM31_Sat, 25 Nov 2006 11:14:34 -0600393_- Hi Pat,
For the index statistics that are collected for DGTT, does it make any diffenrence when the index is created? You can create the index before the rows are inserted or after the table has been populated, would that make a difference?
We've always created the index first, before the inserts, but if you can/do create the index after, would statistics be collected? [...]
5756 22 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES15_Patrick Bossman18_bossman@US.IBM.COM31_Sun, 26 Nov 2006 12:51:25 -0600568_- Hello, When the index is created doesn't matter from index creation time. Statistics are collected whether the index is created when empty, and also when the index is created on a table with rows.
One customer I worked with only created the index after the DGTT exceeded a certain threshold of rows. This worked well for them, as sometimes the DGTT was extremely small and they avoided the index creation overhead. It also became important that when an index was created (or dropped) that SQL which referenced the DGTT then required an incremental bind. [...]
5779 59 39_Re: Move a db2 db from Windows to Linux12_tim malamphy20_timalamphy@YAHOO.COM31_Sun, 26 Nov 2006 12:54:17 -0800315_iso-8859-1 No problem-
1.db2look... to extract the DDL 2.db2move -- export... to extract the data 3.ftp the output from 1 and 2 to new machine 4.create an empty database on new machine 5.use output of db2look to create the rest of the structures 6.db2move -- import (load or insert) to restore the data [...]
5839 82 39_Re: Move a db2 db from Windows to Linux18_kishore lingamallu21_kishorelj@HOTMAIL.COM31_Sun, 26 Nov 2006 20:59:51 -0500661_- Creating foreign keys and indexes after loading the data will be good approach
Kishore LJ
>From: tim malamphy >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] Move a db2 db from Windows to Linux >Date: Sun, 26 Nov 2006 12:54:17 -0800 > >No problem- > >1.db2look... to extract the DDL >2.db2move -- export... to extract the data >3.ftp the output from 1 and 2 to new machine >4.create an empty database on new machine >5.use output of db2look to create the rest of the >structures >6.db2move -- import (load or insert) to restore the [...]
5922 159 12_Sizing Query13_Roberts Colin32_colin.roberts@BRITISH-ENERGY.COM31_Mon, 27 Nov 2006 11:01:59 -0000562_us-ascii Morning Gurus,
We run the following SQL to get an idea of the size of an underlying Tablespace dataset, in this case, for a partitioned Table. The result it returns is 26gb, which we know to be a quarter of the correct figure. We suspect that this may be because the Tablespace is in a 16k pool, as opposed to the, more normal, 4k pool. All the manuals, however, say that the SPACE figure is in kilobytes, rather than pages. We ran the STOSPACE utility prior to running the query. The Tablespace in not compressed. Can anyone shed any light [...]
6082 427 34_Re: estimating real storage for V820_Barth, Stephen (IHG)29_Steve.Barth@ICHOTELSGROUP.COM31_Mon, 27 Nov 2006 07:21:41 -0500469_us-ascii Thanks. Any idea how much more real storage would be required for MSTR and IRLM?
________________________________
From: IBMsysProg [mailto:IBMsysProg@Geek-Sites.com] Sent: Wednesday, November 22, 2006 5:50 PM To: Barth, Stephen (IHG); DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: estimating real storage for V8
With the exception of very small DB2 systems there is a very easy way to do this storage estimate. [...]
6510 17 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES10_John Lantz20_john.p.lantz@FRB.GOV31_Mon, 27 Nov 2006 06:26:08 -0600386_- Even with all of the suggestions, I was never able to get the optimizer to act on the index on the DGTT in the same manner as the "static" table. I suspect it was due to the limited number of rows that actually was INSERT'ed in the DGTT. The good news is that I was able to get around the original issue be tweaking the SQL a little bit which caused the JOIN sequence to change. [...]
6528 409 16_Re: Sizing Query14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Mon, 27 Nov 2006 15:00:48 +0200422_us-ascii Just a thought
When you run runstats against these tablespaces ? The other is if you are in version 7.1 you have to use the SPACEF instead of SPACE
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roberts Colin Sent: Monday, November 27, 2006 1:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Sizing Query [...]
6938 214 42_Re: Authenticating MSSQL queries on db2 SP12_Rosina Porco17_rporco@UOTTAWA.CA31_Mon, 27 Nov 2006 09:12:40 -0500398_us-ascii Hi all,
We are DB2 V7 on Z/os / racf,
My question is on security measures in place when MS sql queries or Oracle queries call a DB2 storedprocedure .
In our environment , presently the queries pass the userid and password and we allow execute SP privileges to that userid .
This makes me nervous, I don't really like userids and password being hard coded. [...]
7153 114 34_Re: [DB2 Z/OS] Bufferpool clusters0_19_mike.holmans@BT.COM31_Mon, 27 Nov 2006 14:42:02 -0000568_us-ascii Hi Joel,
Thanks for taking the cue.
That reply has cleared up quite a few things for me, but there's one bit which is still a bit murky to me.
You mention systems with almost wholly random access as being slightly weird: naturally, I'm currently tuning a system where that appears to be the case. And that was what I was driving at with the combined pool question: if all the access is random (but includes dynamic prefetch), why wouldn't it be sensible to put all the indexes into one big pool? And if it wouldn't, why would you do [...]
7268 153 44_Re: Stored procedure/host variable/substring15_Perry O'Connell28_Perry.O'Connell@TOYOTAUK.COM31_Mon, 27 Nov 2006 14:42:56 +0000341_US-ASCII Thanks for you help Jackie and Agus
I did spot the reference in the SQL manual to the LIKE predicate, in that you cannot use nested function in the predicate, but the original SQL works ok when run on a Windows DB2 environment.
Agus, your solution works fine, but Jackie I cant get yours to work, same problem :( [...]
7422 321 52_AW: [DB2-L] Stored procedure/host variable/substring12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 27 Nov 2006 16:10:28 +01001030_iso-8859-1
Perry
please take a look at "The SQL Reference for Cross-Platform Development"
under
http://www-106.ibm.com/developerwor...0206sqlref.html
This reference may answer your question(s).
Hope that helps !
With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r Vice Chair GSE Workinggroups DB2 Nord und Sued ------------------------------------------------------------------- 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 ---------------------------------------------------------------------- »"Next time you think you're perfect - try walk on water!"« [...]
7744 34 42_Re: DECLARED GLOBAL TEMP TABLE and INDEXES15_Patrick Bossman18_bossman@US.IBM.COM31_Mon, 27 Nov 2006 10:06:08 -0600535_- Hello, I'll be working with the writers to improve the manuals in this area. Until that's complete, here's the information available to optimizer for DGTTs:
Table level: CARDF NPAGESF
Index level: FULLKEYCARDF NLEVELS NLEAF
CLUSTERING INDEX CLUSTERRATIOF = 0.95 NON-CLUSTERING INDEX CLUSTERRATIOF = 0
When a single column index is available, DB2 generates single column information for the column. So the following column statistics are available for single column indexes: COLCARDF LOW2KEY HIGH2KEY [...]
7779 387 40_Re: [DB2 Z/OS] Bufferpool clusters part235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 27 Nov 2006 11:25:07 -0500370_iso-8859-1 Hi Mike,
Perhaps this can clear up some of the murkiness.
Working set size, and use frequency, may not have any relationship at all.
Let's take Object A that has 10,000,000 pages, and Object B that has 1,000,000 pages.
All GP requests will be random. These two objects are the top 2 in the pool, in terms of getpage activity. [...]
8167 219 42_Re: Authenticating MSSQL queries on db2 SP12_Rosina Porco17_rporco@UOTTAWA.CA31_Mon, 27 Nov 2006 09:12:40 -0500398_us-ascii Hi all,
We are DB2 V7 on Z/os / racf,
My question is on security measures in place when MS sql queries or Oracle queries call a DB2 storedprocedure .
In our environment , presently the queries pass the userid and password and we allow execute SP privileges to that userid .
This makes me nervous, I don't really like userids and password being hard coded. [...]
8387 92 24_Extract of EDM-Pool SKPT12_Armin Hanika22_armin.hanika@ARZ.CO.AT31_Tue, 28 Nov 2006 14:30:09 +0100472_ISO-8859-1 Hi all,
does anybody know, if there is a way to extract the SKPT-entries residing actually in the EDM-Pool? I want to know, which packages are submitted over a period of time.
Regards Armin Hanika
Allgemeines Rechenzentrum GmbH Datenbankadministration DB2 z/OS & Oracle
A-6020 Innsbruck, Tschamlerstraße 2 Tel.: +43 / (0)504009-1165 Fax: +43 / (0)504009-71165 E-Mail: armin.hanika@arz.co.at http://www.arz.co.at [...]
8480 214 42_DB2 V8 NFM Cobol SP called from VB.net sql12_Asamoto, Roy25_Roy.Asamoto@STATEAUTO.COM31_Tue, 28 Nov 2006 08:41:20 -0500326_us-ascii Hi,
We are trying to use VB.net to call a mainframe COBOL stored procedure that will return a result set to the client. The developer wants to be able to just call the SP and have it return the result set. We set up a simple SP that has a cursor that it opens and then returns to the calling application. [...]
8695 61 35_Full imagecopy with shrlevel change12_Smith, Allan23_Allan_Smith@KYFBINS.COM31_Tue, 28 Nov 2006 09:46:27 -0500733_us-ascii Can anyone tell me what impact on the system (response times/CPU times) would occur if we run a full image copy with shrlevel change on some very large tables? The tables in question are both 4k pages and 32k pages.
Thanks
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
8757 98 52_Antwort: [DB2-L] Full imagecopy with shrlevel change11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 28 Nov 2006 15:47:47 +0100497_us-ascii I would sincerely hope that it would not be measurable!
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de
"Smith, Allan" Gesendet von: DB2 Data Base Discussion List 28.11.2006 15:46 Bitte antworten an DB2 Database Discussion list at IDUG [...]
8856 123 39_Re: Full imagecopy with shrlevel change16_Burnette, George27_George_Burnette@KYFBINS.COM31_Tue, 28 Nov 2006 10:52:01 -0500515_us-ascii Related to below: If a full image copy is created with shrlevel change (no existing backups), is there a way to recover from the copy and apply log records to bring the table to current state with no quiesce having been performed ?
Thanks, George
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Smith, Allan Sent: Tuesday, November 28, 2006 9:46 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Full imagecopy with shrlevel change [...]
8980 36 36_DB2 V7 peoplesoft optimization hints12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Tue, 28 Nov 2006 10:56:44 -0500261_US-ASCII Hi, We are looking at using optimization hints for peoplesoft. Is that possible with peoplesoft? if so any good sites or papers on how to do this?
TIA, Jeff
---------------------------------------------------------------------------------
9017 116 40_Re: [DB2 Z/OS] Bufferpool clusters part20_19_mike.holmans@BT.COM31_Tue, 28 Nov 2006 16:01:34 -0000346_us-ascii Hi Joel,
It cleared up much of the murkiness. Indeed, I even begin to think I understand. I hope some other people have benefited from those clear explanations.
Now to run another couple of simulations, because now I think I can do better (and I've already done quite well).
Thanks very much for your patience. [...]
9134 206 39_Re: Full imagecopy with shrlevel change14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Tue, 28 Nov 2006 10:12:14 -0600388_windows-1250 Just do an IBM recover to current (the default). You only need a QUIESCE if you need to do a PiT recovery.
Andy Lankester
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Burnette, George Sent: Tuesday, November 28, 2006 3:52 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Full imagecopy with shrlevel change [...]
9341 145 39_Re: Full imagecopy with shrlevel change13_Michael Ebert18_mebert@AMADEUS.COM31_Tue, 28 Nov 2006 17:07:23 +0100454_US-ASCII a) the performance impact is negligible b) yes, that's the normal M/O. You need a QUIESCE only if you want to do a Point-in-time (PiT-) recovery.
Dr. Michael Ebert DB2 & Oracle Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
"Burnette, George" To DB2-L@WWW.IDUGDB2-L.ORG cc
bcc
Subject Re: [DB2-L] Full imagecopy with shrlevel change [...]
9487 80 39_Re: Full imagecopy with shrlevel change9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 28 Nov 2006 10:09:58 -0600488_us-ascii Bringing the table to current is the default and does not require a QUIESCE. QUIESCE is required to recover a set of tables to a consistent logrba. This will let you recover multiple tables with related data (DB2 RI or not) to a state where there are no partial updates. The normal use of this is either at a DR site where you don't want partial data or to recover to a point before current because something happened (bad software update, errors in application code, etc). [...]
9568 249 40_Re: DB2 V7 peoplesoft optimization hints9_Doty, Don25_Don.Doty@INFARMBUREAU.COM31_Tue, 28 Nov 2006 10:59:45 -0500578_US-ASCII You might try to contact Craig Patton.
He had a lot of good ideas along those lines.
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jeff Frazier Sent: Tuesday, November 28, 2006 10:57 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V7 peoplesoft optimization hints
Hi, We are looking at using optimization hints for peoplesoft. Is that possible with peoplesoft? if so any good sites or papers on how to do this? [...]
9818 148 40_Re: DB2 V7 peoplesoft optimization hints35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 28 Nov 2006 11:15:07 -0500730_iso-8859-1 While it's from an older version of PeopleSoft, the basics still apply.
Look in the Papers/Presentations section of our website, and scroll down to the Papers, Full Text area. See the paper by Martin Hubel - Tuning PeopleSoft Applications for DB2 z/OS
Regards, Joel.
Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com (732) 972-1261 ----- Original Message ----- From: Jeff Frazier Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, November 28, 2006 10:56 AM Subject: [DB2-L] DB2 V7 peoplesoft optimization hints [...]
9967 114 40_Re: DB2 V7 peoplesoft optimization hints14_Clayton, Colin23_Colin.Clayton@RBS.CO.UK31_Tue, 28 Nov 2006 16:09:19 -0000587_- Sorry Jeff, don't think that's possible with PeopleSoft using dynamic SQL.
Colin
(my opinion etc...etc...)
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jeff Frazier Sent: 28 November 2006 15:57 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V7 peoplesoft optimization hints
*** WARNING : This message originates from the Internet ***
Hi, We are looking at using optimization hints for peoplesoft. Is that possible with peoplesoft? if so any good sites or papers on how to do this? [...]
10082 204 40_Re: [DB2 Z/OS] Bufferpool clusters part235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 28 Nov 2006 11:22:46 -0500539_iso-8859-1 My pleasure Mike.
Ask whatever comes to mind, at any time.
Regards, Joel
Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com (732) 972-1261 ----- Original Message ----- From: mike.holmans@BT.COM Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, November 28, 2006 11:01 AM Subject: Re: [DB2-L] [DB2 Z/OS] Bufferpool clusters part2 [...]
10287 236 39_Re: Full imagecopy with shrlevel change12_Cianci, Nick19_nick.cianci@EDS.COM31_Wed, 29 Nov 2006 10:02:05 +1100494_us-ascii Allan, generally there shouldn't be a significant impact with the SHRLEVEL(CHANGE).
However:
1. If you are copying these tables with a LISTDEF using a high degree of parallelism against highly active tables, you could suffer some IOSQ degradation, unless you have PAV enabled DASD. Note parallelism is controllable \ tunable, so monitor and ramp up or down as required. 2. You will still have the same serialisation consideration when you Quiesce a RI-set of tables [...]
10524 26 58_DB2 UDB on AIX - How to interpret db2diag.log error codes?9_Lydia Lee20_Lydia.Lee@SCO.CA.GOV31_Tue, 28 Nov 2006 16:33:47 -0800559_- We are running DB2 UDB 8.1.1 fix pack 11 on AIX 5.3. Where can I go to look up error codes found in db2diag.log? For the error listed below, I tried searching for ZRC=0xFFFFFB38=-1224 on http://www-306.ibm.com/software/data/db2/udb/support/ but I could not find anything.
2006-11-26-23.02.17.900260-480 I2065156A321 LEVEL: Error PID : 843786 TID : 1178 PROC : db2hmon 0 INSTANCE: db2dss NODE : 000 APPID : *LOCAL.db2dss.061127070111 FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::iter_action, probe:610 MESSAGE : ZRC=0xFFFFFB38=-1224 [...]
10551 41 62_Re: DB2 UDB on AIX - How to interpret db2diag.log error codes?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 29 Nov 2006 13:17:44 +1100635_US-ASCII Possibly sqlcode = -1224 "A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. "
Does this seem likely?
James Campbell
On 28 Nov 2006 at 16:33, Lydia Lee wrote:
> We are running DB2 UDB 8.1.1 fix pack 11 on AIX 5.3. Where can I go to look > up error codes found in db2diag.log? For the error listed below, I tried > searching for ZRC=0xFFFFFB38=-1224 on > http://www-306.ibm.com/software/data/db2/udb/support/ but I could not find > anything. > > 2006-11-26-23.02.17.900260-480 I2065156A321 LEVEL: Error > [...]