RE: [DB2-L] VARCHAR columns & other stupid things Yes, AND in the days before Index Lookaside and before Check Constraints (now I feel REALLY old!!)
Phil [...]
7712 99 16_Re: CRCR problem11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Sat, 16 Feb 2002 10:34:12 -0500395_ISO-8859-1 Hi Leila
Do NOT just do another CRCR. This will only make the problem worse. As a wise man said, 'when you find yourself in a hole, the first thing to do is stop digging'!
You have a situation now in which the same range of Log RBAs have been created twice. Once before the Cold Start and once after the Cold Start. I think you have two possible courses of action. [...]
7812 27 27_LOAD WITH DEFER YES PROBELM8_duam lee20_duam_lee@HOTMAIL.COM31_Sun, 17 Feb 2002 02:11:08 +0000537_- Hi All, here is a confusion for me. Can any body help me out? I have a table with four indexes. One is partioning index and the other three are for primary as well unique constraints indexes. I defined the rest three index with DEFER YES option except the partioning index.When I load the table it says index entries are built for those all the four indexes. But it should reflect only the one and that to partioning index. I m confused here. Whether I need to run the rebuild index for those three or leave like that. Really the [...]
7840 57 21_Re: DB2 Unload Copies14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 17 Feb 2002 12:41:58 +1000556_US-ASCII Tony
Have you considered using IDCAMS REPRO instead of IEBGENER. IDCAMS allows more than one repro in a step.
James Campbell
On 14 Feb 2002 at 8:49, Moore, Tony wrote:
> Yo Lister's, > This is more an MVS question than DB2, but since it's dealing with DB2 > unloads and this is the only list server I'm on, I'll throw it out... > > At the end of each month, I unload all our production DB2 data to tape. I've > done this each month with four unload jobs, stacking unload datasets to 3490 > tape cartridges. We are [...]
7898 81 21_Re: Deadlock question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 17 Feb 2002 12:41:59 +1000630_US-ASCII Abhijit
Just as "yes it could happen", a WITH UR has locks on the table, tablespace and database definitions. If someone was doing an (in?)appropriate set of ALTER/CREATE/DROP commands, then the program with URs could be the deadlock victim.
Don't know if it's relevent here though,
James Campbell
> From: Sinha, Abhijit [mailto:Abhijit.Sinha@GS.COM] > Sent: Thursday, February 14, 2002 12:25 PM > To: DB2-L@RYCI.COM > Subject: Deadlock question > > > Hi, > > Is it possible that a program running a SELECT sql (inside a cursor) with > the "WITH UR" option can still be a candidate for a [...]
7980 103 25_Re: Claims/Locks Question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 17 Feb 2002 12:42:00 +1000346_US-ASCII Rusty
Seems like a bug somewhere. Txn 2 shouldn't be getting data until after its U lock has been obtained, which is after Txn1 has commited. (Unless DB2 is deciding the row satisfies predicates based on an index, but the actual data obtained is post Txn 1 update - does the data obtained by Txn 2 have unprocessed =y or n?) [...]
8084 60 80_Could some one play DB2 Parallelism Detective with me? I have a mystery to solve11_Linda Ayeni22_lindaayeni@HOTMAIL.COM31_Sun, 17 Feb 2002 01:32:09 -0700618_- Esteemed listers, We are DB2 v5 going to v6 soon, relatively unsophisticated with the tricks of the trade in DB2. THE BACKGROUND: I have several large tables (largest about 31.8 million all across 7 applications) and some of these tables are partitioned. Many of our applications with access to these large tables for sometime enjoyed one of the benefits of parallelism viz reduced elapsed time. Before we turned parallelism on, the worst of the application was experiencing about 8 hours elapsed time. When parallelism was turned on, we became estatic (it was magic!!)to find that the same batch job completed [...]
8145 53 41_Re: VARCHAR columns & other stupid things14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Sun, 17 Feb 2002 10:41:58 +0100
8199 39 31_Re: LOAD WITH DEFER YES PROBELM7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Sun, 17 Feb 2002 07:06:05 -0500563_ISO-8859-1 The defer option applies to when the index is defined. If you use this option the object is created, but no index entries are created. Normally you would then do a rebuild. I am assuming that you are performing a load replace. In that case the indexes will be populated. If you do not want to load the indexes during the load process you need to drop them, load your date, create them defer, and then you rebuild. > > From: duam lee > Date: 2002/02/16 Sat PM 09:11:08 EST > To: DB2-L@RYCI.COM > Subject: LOAD WITH DEFER YES [...]
8239 204 41_Re: VARCHAR columns & other stupid things13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Sun, 17 Feb 2002 06:34:40 -0800742_us-ascii Peter,
Good point. I originally thought of forcing one and only row on the table, and then turn off all insert and delete privileges, but I am sure that some hacker could get around that.
Question: Why won't SYSDUMM1 work?
Tink --- Peter Backlund wrote:
Tink,
thanks for your suggestion, but it only solves half the problem!
How do I guarantee that there always is a row there?
Probably I could have a delete trigger which disallows deletes
Peter
BL.Tink Tysor wrote:
Peter,
One could put a [...]
8444 120 60_Re: Could some one play DB2 Parallelism Detective with me? I10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Sun, 17 Feb 2002 14:13:35 -0500392_iso-8859-1
Linda,
Our current 8 member sysplex DB2 v6 and MVS 2.10. One thing that helped lessen the partitioned tablespace locking activity was selected partition locking (aka SPL), tablespace DDL parameter LOCKPART YES. What happens if your updating or inserting only the partition being worked on is locked and when your finished you have only the one lock to release. [...]
8565 63 41_Re: VARCHAR columns & other stupid things14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Sun, 17 Feb 2002 21:14:50 +0100
8629 67 26_Re: Primary Index - take 210_teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 18 Feb 2002 09:31:57 +1100273_us-ascii Rohn,
You don't need to create IXA1 as a primary index for it to be used in a RI. You need only a unique index, which u already have. So u should be able to just define two relationships, one between tabB and tabA, and the other between tabB and tabC. [...]
8697 158 41_Re: VARCHAR columns & other stupid things14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Mon, 18 Feb 2002 12:03:26 +1100342_iso-8859-1 Peter,
Low-tech and not totally bullet-proof, but create the table, insert the 1 row that is required, then ensure that the users/application has only SELECT and UPDATE (if required) access. No DELETE and no INSERT access since they are not required.
The escape clause is invoked if they have LOAD access.... :) [...]
8856 136 27_Re: INdex guidelines please13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 17 Feb 2002 19:13:05 -0600503_iso-8859-1 Jennifer,
I'd like to add to Phil's points.....
It's very difficult to make the call as to which indexes you need, since we do not have any knowledge of the SQL, column cardinalities, table sizes etc. But let me throw a few statements (just random thoughts) at you that may wish to consider. These of course are opinions only and can easily be taken out of context, since the objective is weighing up extra cost of index update activity (plus DASD) vs sort avoidance etc.: [...]
8993 108 40_Re: Table space scan due to large index?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 17 Feb 2002 19:13:09 -0600439_iso-8859-1 Andrew,
Most of the answers have covered your questions already, but I just wanted to summarize and add another point of interest.
The issue is that you do not have an index where the leading column is on A_IND. Therefore any index chosen has to be read in it's entirety. It's like looking through the telephone book and trying to find all people with a first initial of 'J'. You need to look at every entry. [...]
9102 162 40_Re: Table space scan due to large index?12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Sun, 17 Feb 2002 21:20:04 -0600452_iso-8859-1 Terry,
A clarification, please: You state that an index scan is less efficient than a tablespace scan.
If the index is in good reorg condition, wouldn't DB2 use prefetch on the index just as effectively as on the tablespace?
Does the optimizer take leafdist into consideration, or is index prefetch always dynamic?
How bad would the leafdist have to be to make the index scan worse than the tablespace scan? [...]
9265 88 55_Re: What should I do about a -904 reason code 00D70014?10_John Hardy25_john_hardy@ALLIANZ.COM.AU31_Mon, 18 Feb 2002 00:28:46 -0600615_- First you will have to recover the partition (number 8) on the table and then......
Ouch! Why?
On Fri, 15 Feb 2002 13:48:54 -0600, Glen Sanderson wrote:
>If you look up the reason code 00D70014 it is quite evident. > >The partition being inserted into has run out of room and has reached its maximum number of extents. Either enlarge the partition or make the SECQTY bigger so each extent that DB2 gets is >larger. > >First you will have to recover the partition (number 8) on the table and then alter the PRIQTY and SECQTY of the partition. [...]
9354 14 50_Wayne Goodwin/CIS/TJX/TJXCOS is out of the office.13_Wayne Goodwin21_Wayne_Goodwin@TJX.COM31_Mon, 18 Feb 2002 02:31:20 -0500365_us-ascii I will be out of the office from 02/18/2002 until 02/24/2002.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9369 21 29_Loading data from IDMS to DB214_Agrawal, Navin21_navin.agrawal@EDS.COM31_Mon, 18 Feb 2002 01:54:36 -0600489_iso-8859-1 Esteemed listers,
I'm looking for a tool which can help me in loading data from IDMS to DB2. Can any body help me in choosing the appropriate tool for this purpose ?
Thanks in advance
Regards Navin Agrawal EDS-India
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9391 41 84_Re: Could some one play DB2 Parallelism Detective with me? I have a mystery to solve15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 18 Feb 2002 01:57:30 -0600287_- Hi Linda
Before I can say anything to your problem, do you have additional information, such as accounting class 1, 2, and 3. There you can see, if DB2 used parallelism for that plan, what the degree was and if a reduction has occured. There a some reasons for the latter: [...]
9433 59 33_Re: Loading data from IDMS to DB218_Adrian Engelbrecht37_Adrian_Engelbrecht@WESTPACTRUST.CO.NZ31_Mon, 18 Feb 2002 21:32:39 +1300514_us-ascii EDBC from CA will probably do it.
"Agrawal, Navin" on 18/02/2002 20:54:36
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Adrian Engelbrecht/WestpacTrust/NZ) Subject: Loading data from IDMS to DB2
Esteemed listers,
I'm looking for a tool which can help me in loading data from IDMS to DB2. Can any body help me in choosing the appropriate tool for this purpose ? [...]
9493 22 43_Multiple transactions in a STORED PROCEDURE10_praveen_kj19_praveen_kj@INFY.COM31_Mon, 18 Feb 2002 14:11:59 +0530462_iso-8859-1 Hi,
Can we have multiple transactions in a STORED PROCEDURE ? HOW ? or a new transaction in the STORED PROCEDURE ? HOW ?
Any pointers would be appreciated.
Regards,
Praveen Kumar Jain
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9516 44 84_Re: Could some one play DB2 Parallelism Detective with me? I have a mystery to solve10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 18 Feb 2002 09:56:01 +0100564_us-ascii Hi
It's not so simple (at least for me uncertified useless DB2 sysprog) to answer why it happened but it sounds as a change in some system parameter (as I think tablespace structure remained the same): for instance someone could have set VPPSEQT = 0 (so no ||-lism). Is your SQL dyn or static ? How do you obtain ||-lism, via 'SET CURRENT DEGREE' or via BIND parameter ? No recent PTFs (as for instance PQ28414) installed in your DB2 that caused a (wrong) rebind of affected programs ? Is the EXPLAIN of that SQL showing any ||-lism (at least [...]
9561 61 51_triggers and stored procedures for db2 v6 for OS39016_Pary Alain (DBB)19_Alain.Pary@DEXIA.BE31_Mon, 18 Feb 2002 09:55:50 +0100574_iso-8859-1 > Hello, > > We have plans to use some new features from DB2 for OS390 Version 6 > > * Triggers > * Stored Procedures > * Recoverable Resource Manager Services attachment facility (RRSAF) > > The objective is to bring us the following functionnality without > impact on existing programs : > 1) referential integrity control between some tables (use of triggers > only) > 2) data replication between Information Systems : triggering of stored > procedure under WLM for put in MQM with RRS coordination > > Option 2) should be used as a temporary solution. > [...]
9623 22 42_reorg catalog/directory index V6 and later12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Mon, 18 Feb 2002 10:12:30 +0100297_- Hello everybody, We run since ages (V2 or V3) routinely reorg of catalog/directory indexes. What are the recommendations for V6? Regards Seppo Nurmi Sveriges Riksbank SE-10337 Stockholm, Sweden Tel: +46 8 787 07 31 Fax: + 46 8 21 05 31 Mob: + 46 70 344 38 84 Email: seppo.nurmi@riksbank.se [...]
9646 68 46_Re: reorg catalog/directory index V6 and later0_18_mebert@AMADEUS.NET31_Mon, 18 Feb 2002 10:47:28 +0100605_us-ascii I think the recommendation is, and always has been, to use it sparingly. We run it about once or twice a year, normally along with major service updates or a version upgrade.
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
Hello everybody, We run since ages (V2 or V3) routinely reorg of catalog/directory indexes. What are the recommendations for V6? Regards Seppo Nurmi Sveriges Riksbank SE-10337 Stockholm, Sweden Tel: +46 8 787 07 31 Fax: + 46 8 21 05 31 Mob: + 46 70 344 38 84 Email: seppo.nurmi@riksbank.se
9715 65 32_Re: DDF Threads in DB2 V5 OS/39012_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Mon, 18 Feb 2002 10:06:19 +0000363_- I do not know how to restrict the DDF Threads thru RMF . I know QMF queries can be restricted thru DSNRLST table with RLFFUNC as blank. Could someone tell me how to do it thru RMF ? Can I restrict some particular userids only ? Also how to restrict multiple panels open by a single userid.DB2 version we are using is DB2 V5.1 Any help will be appreciated. [...]
9781 153 54_Re: Problem with replication when using VARCHAR(32000)16_Anand Sundaresan25_Anand.Sundaresan@UBSW.COM31_Mon, 18 Feb 2002 09:56:17 +0000505_US-ASCII Hi
Can someone please let me know about this. Would appreciate some info on this puzzling situation we have at our center,
regards Anand UBS Warburg
-----Original Message----- From: Sundaresan, Anand Sent: 14 February 2002 14:12 To: 'DB2-L@RYCI.COM' Subject: Problem with replication when using VARCHAR(32000)
Hi
I am trying to set up DB2 Replication. When I define replication Source for this table IXN_EXPLOG2 with the following defined columns: [...]
9935 21 46_Re: reorg catalog/directory index V6 and later10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 18 Feb 2002 11:30:53 +0100460_iso-8859-1 Hi
As it's a critical activity on a DB2 subsystem it's better to 'clean' catalog/directory (see Andrè Goetschy of USB articles) before running reorgs , expecially in a test system.
HTH Max Scarpa
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9957 15 39_Frank Giguere/NUS is out of the office.16_Frank E. Giguere13_giguef@NU.COM31_Mon, 18 Feb 2002 05:35:21 -0500389_us-ascii I will be out of the office starting 02/18/2002 and will not return until 02/25/2002.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9973 47 33_Re: Loading data from IDMS to DB214_Kumar, Sandeep31_Sandeep.Kumar@BLR.HPSGLOBAL.COM31_Mon, 18 Feb 2002 16:41:37 +0500648_- Navin,
http://www.xitec-software.com/ is probably a better place for IDMS to DB2 tools.
Regards, Sandeep.
> -----Original Message----- > From: Agrawal, Navin [SMTP:navin.agrawal@EDS.COM] > Sent: Monday, February 18, 2002 1:25 PM > To: DB2-L@RYCI.COM > Subject: Loading data from IDMS to DB2 > > Esteemed listers, > > I'm looking for a tool which can help me in loading data from IDMS to DB2. > Can any body help me in choosing the appropriate tool for this purpose ? > > Thanks in advance > > Regards > Navin Agrawal > EDS-India > > ================================================ > To change your subscription options [...]
10021 22 54_Re: Problem with replication when using VARCHAR(32000)0_24_markus.schaub@SYSTOR.COM31_Mon, 18 Feb 2002 12:19:01 +0100434_us-ascii hi anand
if you define a table as a replication source db2 creates a 'changed data' table for you. this table contains some 'controling' columns and each of the source table's columns twice. i suppose the the sql0670n error you are encountering belongs to the create of the 'changed data' table where your varchar column will be doubled and therefore the rowsize will be longer than the max. allowed 32677 bytes. [...]
10044 120 16_Re: CRCR problem11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Mon, 18 Feb 2002 06:20:10 -0500408_ISO-8859-1 Hi Leila
A correction to something I said below. It appears that Cold Start does remove the archive log entries from the BSDS, therefore you cannot find the last archive from before the Cold Start by printing the BSDS.
Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk
Message text written by DB2 Data Base Discussion List >Hi Leila [...]
10165 103 21_Re: DSN1COPY question12_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Mon, 18 Feb 2002 12:01:33 +0000430_us-ascii Le, I used to get this problem when restoring from multiple tape volume image copies. The solution was to explicitly specify both volumes in the DD statement.
eg. //SYSUT1 DD DSN=xxx,VOL=(vol1,vol2)
Dave Wilson
Le BD Nguyen cc: Sent by: DB2 Subject: DSN1COPY question Data Base Discussion List [...]
10269 177 54_AW: Problem with replication when using VARCHAR(32000)15_Manus, Wolfgang22_wolfgang.manus@EDS.COM31_Mon, 18 Feb 2002 12:11:20 -0000563_- Anand,
you've requested to keep before- and after-images. The Change Data (CD-) table then contains both a 32K before-image column AND an a 32K after-image column (XEXP2_REQUEST VARCHAR ( 32000 ) and EXP2_REQUEST VARCHAR ( 32000 )) , which exceeds the maximum row length of the table (see SQL Reference, Appendix A - SQL Limits). I'm not aware of a simple solution for that except dropping before-image capturing for the 32K column. You may consider a trigger on the target table that saves the before-image, but this may not work in all scenarios. [...]
10447 105 57_Re: Increasing the size of the LOG in UDB DB2 5.1 for AIX15_Katharine Brown25_kathrinebrown@HOTMAIL.COM31_Mon, 18 Feb 2002 12:50:21 +0000593_- Thanks a lot to everyone who have helped me solve this.Now I have also come to the conclusion that 4GB is the limit in UDB DB2 V5.2. Thank You everyone once again for your responses.
Regards Katharine
>From: Chris Tee >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Increasing the size of the LOG in UDB DB2 5.1 for AIX >Date: Wed, 13 Feb 2002 15:03:00 +0000 > >Katharine, > >As far as I know, there is a 4GB limit on total log space which is >calculated as ( (logprimary + logsecond) * (logfilsiz + 2 [...]
10553 33 50_DB2 Abnormal Termination with Reason Code 00E507050_24_ujjwal@LOT.TATASTEEL.COM31_Mon, 18 Feb 2002 19:12:03 +0530479_us-ascii The Db2 in our Installation came down thrice with the same message yesterday.I could not come out with the proper reason as to why it has happened . I have checked the ECSA and it was not overloaded.In Omegamon I could see WSIZ too small with a Warning message. I have checked the threads which were active .There was just one thread which was taking more resources and it was a UNLOAD job with a subquery ( NOT EXISTS ) in it. The Message displayed is as follows: [...]
10587 81 21_Re: DSN1COPY question27_ROBINSON, Warwick, MANU ITI28_Warwick.Robinson@NATWEST.COM31_Mon, 18 Feb 2002 13:42:21 -0000581_windows-1252 Le, I believe that the maximum size for a VSAM linear dataset is 4Gb rather than 2Gb - hence the upper limit for a tablespace/index partition (DSSIZE excepted). Have you checked your output device capacity ? A 3390 m3 for example is good for about 2.8Gb. It's not normally a problem for a DB2 object size to exceed device capacity as DB2 does a VSAM ALTER "under the covers". This allows the underlying VSAM LDS to go multi-volume and a new extent is allocated on an alternate device. However, being a standalone utility, DSN1COPY lacks this feature. To the best [...]
10669 133 31_Re: LOAD WITH DEFER YES PROBELM14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 18 Feb 2002 13:50:26 -0000413_iso-8859-1 As an aside, be VERY careful of defining partitioning indexes DEFER YES and the only loading data into SOME of the partitions. DB2 has been known to get VERY confused when SOME of the partitioning index datasets are created and some are not. You can also have similar problems (usually with the utilities) if you define the tablespace partitions DEFER YES and only populate come of the partitions [...]
10803 73 55_Re: Tuning app_ctl_heap_sz on Solaris UDB v7.2 fixpack511_Hamar, Bela22_Bela.Hamar@VARETIS.COM31_Mon, 18 Feb 2002 14:56:47 +0100614_iso-8859-1 Hi Peter,
we have the same problem on Linux: we encountered SQL0973N with V7.1 (no fixpak), then we applied FP5, but it does not solve the problem, we are still receiving that error - although this error (or something very similar) is listed in the APAR-list of FP4...
The application is a simple JDBC app., inserting and retrieving rows containing CLOB values. The error is raised always when retrieving the rows (after cca. 1000 rows). The worst of it is, that the memory block "app_ctl_heap" is used only by EEE or intra-parallelism turned on. But we use EE, and intra-parallelism [...]
10877 233 58_Re: AW: Problem with replication when using VARCHAR(32000)16_Anand Sundaresan25_Anand.Sundaresan@UBSW.COM31_Mon, 18 Feb 2002 14:11:09 +0000587_US-ASCII Thanks a lot for this suggestion. It really helps creation of the replication sources for 32K Sizes but with the exception of before image copy. But there is one more problem associated with it, the Apply fails with the condition that the Page Size of the Temporary Table space (CD Table) is not sufficient, although the CD Table is a replica of the Source in terms of Table space definitions. This is also because when I create replication sources the default Tablespaces taken is the same as the source (can anyone tell me why is this). Secondly during incremental apply [...]
11111 40 32_CA/Platinum & Vsam Space Manager18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 18 Feb 2002 10:03:23 -0500529_iso-8859-1 We're in the process of upgrading our CA DB2 suite of tools to P99F. We are currently at DB2 V6 OS/390. We seem to be running into problems with this old vsam space manager (VSM) that we have running here. On the Platinum Rapid Reorgs we noticed it (VSM) starts kicking out system dumps during a reorg. It doesn't stop the job from running and it doesn't cause the reorg(s) to fail. Its just a message/dump that pops up and is a real pain. It happened on 3 out of 150 reorgs. Not alot but all of a sudden started [...]
11152 44 25_Re: IBM to ease DB2 admin0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 18 Feb 2002 09:06:22 -0600477_us-ascii Interesting article especially where they said ... DBAs can already run a utility that gathers statistics on the data geography and optimizes subsequent query requests, but the next release will perform these functions in real time.
I interpret that to mean that they are moving away from Runstats, "statistics on data geography", and Static Binds, "Optimizes subsequent query", to at least more dynamic binds and maybe dynamic runstats. [...]
11197 16 30_JAVA and DB2 Stored Procedures15_Louis.Commodore26_Louis.Commodore@TARGET.COM31_Mon, 18 Feb 2002 09:52:11 -0600345_iso-8859-1 I have the need to invoke a DB2 Stored Proc from java, which passes an input parm up to 1000 bytes in length. I define this as VARCHAR(1000) to my Stored Proc. I define it as a String object in Java. When I pass the input parm from Java to my Stored Proc, the Stored Proc only sees 256 bytes (the remaining bytes are truncated). [...]
11214 71 41_Re: VARCHAR columns & other stupid things9_Rob Crane22_racrane@CONCENTRIC.NET31_Mon, 18 Feb 2002 09:34:40 -0700359_us-ascii If SYSDUMMY1, and the security ideas mentioned don't fit the bill you could also set up the tablespace to ensure that only one row would ever fit.
Assuming 4K page size, make the row over 2,000 bytes, have secondary quantity 0, and primary quantity < 8K, this would not allow an extent to be taken, and 2nd insert would fail with a -904. [...]
11286 26 40_Re: Balancing INSERTs across partitions.8_Harish G24_hari_gangadhar@YAHOO.COM31_Mon, 18 Feb 2002 10:09:36 -0600429_- Hi,
Thank a lot for your replies. I had posted the reasons behind the exercise, but it appears it never made it to the list.
Main objective:
As per the current design, partitioning keys which prevents all the new rows from accumulating into the last partition do not exist on the table. Hence on the lookout for methods to avoid this with minimal or no impacts (changes) to the existing applications. [...]
11313 111 55_Re: What should I do about a -904 reason code 00D70014?14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Mon, 18 Feb 2002 10:59:35 -0600525_us-ascii It depends on what stage the load was in when it failed. The safest step would be to recover.
If you run out of room in the middle of a load; it could be in the tablespace itself or one of the indexspaces. If the tablespace was loaded properly (you will see x rows loaded message) then one of your indexes ran out of room and then you would have to rebuild just that index (after making it larger) and any other indexes that were not yet added to. If the tablespace ran out of room than no index entries [...]
11425 47 47_Re: Multiple transactions in a STORED PROCEDURE14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Mon, 18 Feb 2002 09:08:14 -0800334_iso-8859-1 That would depend on your code and how you define a transaction
If I recall correctly I have processed thousands of records with some of my stored procedures (they read from a table and insert altered records back into the table); but they only have a single COMMIT when the stored procedure ends successfully. [...]
11473 34 34_Re: JAVA and DB2 Stored Procedures9_Rob Crane22_racrane@CONCENTRIC.NET31_Mon, 18 Feb 2002 10:32:41 -0700488_us-ascii You could chunk (substr) the VARCHAR(1000) into 4 250 length strings then bring them back together in your stored procedure. The limitation you are running into deals with the max length one column that is passed via DDF can be. Typically tied to text boxes on the GUI side. I tend to favour using array type processing within the table, make 4 columns CHAR(250) and bring the columns together by concatenating them when you have to display the information as an aggregate. [...]
11508 53 23_DISPLAY THREAD question0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 18 Feb 2002 12:06:24 -0600446_us-ascii OS/390 DB2 V6.1 I did a -DISPLAY THREAD(*) on one of our systems and found a whole bunch of statuses (statii?) that are not in the manual. Below is an extract from that DISPLAY, and from the Command Reference Manual. I check the web version, and although it is 2 releases (Document Number SC26-9006-02 ) newer than our LAN copy, it is no help either. Where can I look to find these 'new and improved' status codes: "RA", "T" , "N". [...]
11562 147 55_Re: What should I do about a -904 reason code 00D70014?13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Mon, 18 Feb 2002 12:46:38 -0600470_- When did this become a load?? I thought the original post mentioned inserts? I am with J. Hardy. Why recover?
-----Original Message----- From: glen.sanderson@SAFEWAY.COM [mailto:glen.sanderson@SAFEWAY.COM] Sent: Monday, February 18, 2002 11:00 AM To: DB2-L@RYCI.COM Subject: Re: What should I do about a -904 reason code 00D70014?
It depends on what stage the load was in when it failed. The safest step would be to recover. [...]
11710 90 27_Re: DISPLAY THREAD question11_rick creech18_ykcirc@HOTMAIL.COM31_Mon, 18 Feb 2002 12:43:56 -0600376_- Hi,
The following is in the v6.1 messages manual under DSNV404I:
| DA The database access thread slot is currently not | associated with a remote connection and is available | to be assigned to a type 2 inactive thread.
N The thread is in either IDENTIFY or SIGNON status.
PT A parallel task thread was established (plan allocated). [...]
11801 130 46_Re: reorg catalog/directory index V6 and later12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 18 Feb 2002 20:52:48 +0200451_iso-8859-1 Hi, For indices on catalog/dir - we do it more often (depends on leafdist - not all of them, just what's needed). For the TS themselves - once / twice or while preparing for version migration.
Isaac Yassin yassini@bezeqint.net ----- Original Message ----- From: mebert@AMADEUS.NET Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Monday, February 18, 2002 11:47 AM Subject: Re: reorg catalog/directory index V6 and later [...]
11932 71 27_Re: DISPLAY THREAD question11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Mon, 18 Feb 2002 14:02:43 -0500635_ISO-8859-1 Rohn
Look in Messages & Codes under message DSNV404I.
Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk
Message text written by DB2 Data Base Discussion List >OS/390 DB2 V6.1 I did a -DISPLAY THREAD(*) on one of our systems and found a whole bunch of statuses (statii?) that are not in the manual. Below is an extract from that DISPLAY, and from the Command Reference Manual. I check the web version, and although it is 2 releases (Document Number SC26-9006-02 ) newer than our LAN copy, it is no help either. Where can I look to find these 'new and improved' [...]
12004 44 27_Re: DISPLAY THREAD question0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 18 Feb 2002 13:24:50 -0600371_us-ascii Great, thanks Rick and Mike.
Next question... I'm wondering yow you knew to look for message code DSNV404I. On my screen the "DSNV401I" and "DSNV402I" message codes are displayed, but not the DSNV403I, DSNV404I or DSNV405I codes, just their outputs. Did you simply do a search in Messages & Codes for "DISPLAY THREAD" or is there some other 'trick'? [...]
12049 138 21_Re: DSN1COPY question12_Le BD Nguyen20_lnguyen@AHOLDUSA.COM31_Mon, 18 Feb 2002 14:28:25 -0500327_us-ascii Thanks Warwick, David and Leila for your input.
Warwick, you are correct. DSN1COPY utility as a stand alone utility can not allocated additional extent whether it is on the same or on another volume. Following your sugestion, I allocated the 3G dataset on a larger dasd device and the utility worked fine. [...]
12188 27 46_Adding/subtracting dates in a SELECT statement16_Blumenthal, Gary21_GBlument@UNCH.UNC.EDU31_Mon, 18 Feb 2002 14:54:12 -0500408_iso-8859-1 All,
I am trying to calculate the current date +/- the value of an column. For example:
SELECT CURRENT DATE - C_MY_COLUMN FROM MY_TABLE WHERE MY_PK = 1
If the values of current date are 2002-02-18 and C_MY_COLUMN is a SMALLINT with a value of 5 I would expect to see 2002-02-13. Can this be done in SQL? If so, what is the syntax? I am getting no where with the manuals. [...]
12216 74 27_Re: DISPLAY THREAD question11_rick creech18_ykcirc@HOTMAIL.COM31_Mon, 18 Feb 2002 13:49:23 -0600736_- Hi,
The following is found in the message info for DSNV402I:
"The series of messages DSNV402I through DSNV405I provide the formatted report for the -DISPLAY THREAD TYPE(ACTIVE) when the status of active threads is requested."
Regards,
Rick Creech
>From: Rohn.Solecki@MTS.MB.CA >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DISPLAY THREAD question >Date: Mon, 18 Feb 2002 13:24:50 -0600 > >Great, thanks Rick and Mike. > >Next question... I'm wondering yow you knew to look for message code >DSNV404I. On my screen the "DSNV401I" and "DSNV402I" message codes are >displayed, but not the DSNV403I, DSNV404I or DSNV405I codes, [...]
12291 42 50_Re: Adding/subtracting dates in a SELECT statement16_Blumenthal, Gary21_GBlument@UNCH.UNC.EDU31_Mon, 18 Feb 2002 15:02:45 -0500455_iso-8859-1 All,
Don't bother. I figured it out. If you are curious:
SELECT CURRENT DATE - C_MY_COLUMN DAYS FROM MY_TABLE WHERE MY_PK = 1
-----Original Message----- From: Blumenthal, Gary Sent: Monday, February 18, 2002 2:54 PM To: 'DB2 Data Base Discussion List' Subject: Adding/subtracting dates in a SELECT statement
All,
I am trying to calculate the current date +/- the value of an column. For example: [...]
12334 133 42_federated database: authorization problems13_Igor Yakushin20_igory_1999@YAHOO.COM31_Mon, 18 Feb 2002 11:57:21 -0800455_us-ascii Hi,
I desperately need help to get started with federated databases. I am trying to configure the simplest case of a federated database on machine A (Solaris 8, DB2 7.1) using database on machine B (either Solaris 8, DB2 7.1 or Linux, RedHat 7.1, DB2 7.1).
I believe all nodes and databases are properly cataloged because I have no trouble connecting db2 client from either of my machine to some database on the other machine. [...]
12468 23 30_SWARUG meeting Thursday Feb 2110_Shery Hepp17_schepp@SRPNET.COM31_Mon, 18 Feb 2002 13:41:22 -0700634_iso-8859-1 FYI- SWARUG User's Group meeting -
Don't forget that our next meeting will be this Thursday (Feb 21) at Unity Software in Scottsdale. Unity is at 7000 E. Shea Blvd #310 which is on the north side of Shea just west of Scottsdale Rd. Phil Hartley, CEO of Unity will be giving the keynote - WebSphere for DB2 Administrators. There will also be Client Server, ERP, and Data Warehousing SIG sessions. Full details are on our website: http://www.lightyr.com/swarug/news.html Please RSVP to Kathy Erickson at (602) 537-9342 or mailto:Kathy.Erickson@aexp.com and indicate which SIG you plan to attend in the afternoon. [...]
12492 146 46_Re: federated database: authorization problems13_Leo Conchello17_lconche@QWEST.COM31_Mon, 18 Feb 2002 14:02:56 -0700552_us-ascii Maybe you need tp "map" the user id and password if it is not the same in both servers, you can look under Federated Database Objects - User Mappings.
I hope it helps!
Regards,
Leo
Igor Yakushin wrote:
> Hi, > > I desperately need help to get started with federated > databases. > I am trying to configure the simplest case of a > federated database on > machine A (Solaris 8, DB2 7.1) using database > on machine B (either Solaris 8, DB2 7.1 or Linux, > RedHat 7.1, DB2 > 7.1). > > I believe all nodes [...]
12639 64 54_Re: DB2 Abnormal Termination with Reason Code 00E5070513_LOMBARD,Shaun25_shaun.lombard@DEWR.GOV.AU31_Tue, 19 Feb 2002 08:19:07 +1100404_iso-8859-1 Was an SVC DUMP produced ? If so have a look at the dump title, it should contain more information regarding the thread that caused the failure and the reason.
Shaun
-----Original Message----- From: ujjwal@LOT.TATASTEEL.COM [mailto:ujjwal@LOT.TATASTEEL.COM] Sent: Tuesday, 19 February 2002 0:42 To: DB2-L@RYCI.COM Subject: DB2 Abnormal Termination with Reason Code 00E50705 [...]
12704 43 24_Remote SPUFI query hungs14_Michael Kaplan25_micaelkp@NETVISION.NET.IL31_Tue, 19 Feb 2002 00:16:18 +0100594_windows-1255 Hi List,
I would like to get your assistance for the following scenario :
Two DB2 SSID's resided in different MainFrame machines are connected via TCP/IP. DDF is started for both SSID's successfully. Simple SPUFI query works OK accessing remote SSID, but... not always.
And it depends on number of SQL columns to be fetched, f.e. , selecting 2/3 of SYSTABLES columns works and if I specify more columns ( or select * ), it hungs and after timeout sqlcode -30081 is accepted. And it is not matter of number of rows to be fetched. I have checked on "short" [...]
12748 179 46_Re: federated database: authorization problems15_Janardhan Ragam16_sragam@YAHOO.COM31_Mon, 18 Feb 2002 14:14:04 -0800551_us-ascii Before creating server,
connect to database specifying a user. Example: connect to sample1 user using
Sai J Ragam. Certified DB2 and Oracle DBA.
--- Leo Conchello wrote: > Maybe you need tp "map" the user id and password if > it is not the same in both servers, you can look > under Federated Database Objects - User Mappings. > > I hope it helps! > > Regards, > > Leo > > Igor Yakushin wrote: > > > Hi, > > > > I desperately need help to get started with > federated > > databases. [...]
12928 115 21_Data Sharing question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Mon, 18 Feb 2002 14:45:11 -0800574_iso-8859-1 OS/390 2.10, DB2 v6, single production LPAR, no data sharing (yet). Is there any benefit to implementing data sharing if you are only going to have two members running on the same MVS? The perception here is that generally, DB2 will only use one CP unless parallelism is invoked, and the way to make better use of our idle processors is to expand horizontally. Is anyone else running multiple members of the same data sharing group on a single MVS? Will CICS (multiple AOR's to one TOR) make use of both members, or favor one over the other? Central storage [...]
13044 36 46_Re: federated database: authorization problems13_Igor Yakushin20_igory_1999@YAHOO.COM31_Mon, 18 Feb 2002 14:51:09 -0800363_us-ascii Janardhan Ragam wrote: > Before creating server, > > connect to database specifying a user. > Example: > connect to sample1 user using
I tried that, it did not work either. Is there any difference in the authorization options that need to be enabled in federated database in comparison with just a db2 client? [...]
13081 42 46_Re: federated database: authorization problems13_Igor Yakushin20_igory_1999@YAHOO.COM31_Mon, 18 Feb 2002 14:57:42 -0800353_us-ascii --- Leo Conchello wrote: > Maybe you need tp "map" the user id and password if > it is not the same in both servers,
I tried to have the same ids and passwords on both servers, I tried different ids and passwords, I tried to 'mount' local database to federated database. I keep getting that authorization error. [...]
13124 92 40_Re: Balancing INSERTs across partitions.0_19_Tim.Lowe@STPAUL.COM31_Mon, 18 Feb 2002 17:01:41 -0600311_us-ascii Harish, If I can rephrase your last statement for clarity: Do you mean that you are trying to avoid all of the new rows being inserted into the last partition?
If so, why? Is the last partition filling up? What do your indexes look like? Is it some kind of ascending key, like a timestamp? [...]
13217 84 72_Press Release - Disaster Recovery Automated Tool from Recovery Knowledge20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM29_Mon, 18 Feb 2002 18:08:34 EST543_US-ASCII Recovery Knowledge is please to announce Version 3.0 of GENDB2. This new version of the software product will enhance the automated recovery of DB2/MVS resources Off-site Disaster Recovery (DR) as well as On-site.
The new enhanced features are: DR Archive Log Copy Recording Feature: This feature will record the output of your Archive Log Copy. At the DR site the Delete/Newlog control cards are automated to replace the BSDS information. Archive Log copy Tape to Disk is also automated for faster tablespace recoveries. [...]
13302 43 33_Re: Loading data from IDMS to DB214_Horst Hendrich22_horst.hendrich@ASG.COM31_Tue, 19 Feb 2002 10:05:58 +1100489_iso-8859-1 Hello Navin,
ASG-Replication Suite is a perfect tool for this task. Please contact me off line if you would like additional information or visit http://www.asg.com/products/product_details.asp?id=48 for a brief overview.
Cheers,
Horst
-----Original Message----- From: Agrawal, Navin [mailto:navin.agrawal@EDS.COM] Sent: Monday, February 18, 2002 6:55 PM To: DB2-L@RYCI.COM Subject: Loading data from IDMS to DB2
Esteemed listers, [...]
13346 90 25_Re: Data Sharing question0_19_Tim.Lowe@STPAUL.COM31_Mon, 18 Feb 2002 17:29:07 -0600586_us-ascii Cathy, In my opinion, there are little or no benefits to running multiple members of the same data sharing group on the same mvs image on a permanent basis.
There is a benefit to temporarily starting another data sharing member on the same mvs image to release locks in a data sharing environment when an mvs system cannot be restarted. But, I don't think that this is what you are asking. And, with a second member of the group, you might be able to set some zparms for star join performance differently, gaining a performance benefit with some applications. But, [...]
13437 33 21_DB2 Stored Procedures15_Russell Collins21_russell@ECTAUSTIN.COM31_Mon, 18 Feb 2002 17:41:16 -0800555_iso-8859-1 I have a question about DB2 Stored Procedures. Here are the Specs.
Windows NT4 DB2 V.7.1 for Windows Using Stored Procedure Builder
I created an SQLJ stored procedure with the stored procedure builder. It runs fine. Now I want to ad Java business logic to the program. I am trying to write to an output text file. When I try to add this logic, I am getting sqlj errors. My question, can business logic be added to an SQLJ stored procedure?? Do I have to write the Stored Procedure in java?? Is there a way that I can write a [...]
13471 57 46_Re: federated database: authorization problems13_Leo Conchello17_lconche@QWEST.COM31_Mon, 18 Feb 2002 16:45:19 -0700379_us-ascii You are right, you cannot create the user mapping until you have created the server. You may what to try "SAMPLE" in the Database name instead of "S1" which is the alias.
You command would look something like this:
CREATE SERVER "LnxTEST" type DB2/LINUX version 7.2 wrapper "DRDA" AUTHID dbadmin PASSWORD passw options (node 'TEST', dbname 'SAMPLE'') [...]
13529 186 25_Re: Data Sharing question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Mon, 18 Feb 2002 15:49:03 -0800540_iso-8859-1 Thanks for answering, Tim. This started because someone ran an SMF report that showed DB2 tasks waiting for CPU when there were idle processors. I too thought that DB2 would use multiple CP's, but the data seem to contradict this. We have been able to establish that this is definitely happening in CICS - that one CICS AOR will use one and only one CP. People here are now assuming that this is also happening to DB2, and the SMF reports seem to support it. I suppose this is what I get for not learning to code my own SAS [...]
13716 133 25_Re: Data Sharing question11_Tom Flesher13_TomF@ENET.COM31_Mon, 18 Feb 2002 15:59:07 -0800640_us-ascii One potential benefit of running multiple members on the same MVS-OS/390-z/OS image is transaction throughput. Sometimes DB2 throughput is constrained due to logging, especially during highly optimized batch episodes - e.g. parallel processing at the partition level. With multiple DB2s you have more active logs going in parallel and thus might achieve higher total throughput, enabling a batch suite to complete is less wall clock time. I've heard of one large Japanese DB2 user that went with 12-way data sharing, running on 6 MVS images, 2 members per MVS image, mainly for this reason. HTH, Tom Flesher E-Net Corporation [...]
13850 28 52_Ronald van Aalst/NL/ABNAMRO/NL is out of the office.16_Ronald van Aalst31_ronald.van.aalst@NL.ABNAMRO.COM31_Tue, 19 Feb 2002 01:08:42 +0100704_us-ascii I will be out of the office from 02/15/2002 until 02/25/2002.
vrij t/m 24 febr. --------------------------------------------------------------------------- This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorised use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. ABN AMRO Bank N.V. (including its group companies) shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any [...]
13879 126 25_Re: Data Sharing question14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Mon, 18 Feb 2002 16:27:52 -0800554_iso-8859-1 Tom, Unless the DB2 folks have changed the way that IDENTIFY processing occurs, if there are two or more members in a single image, all connections using the group attach name will be connected to the first subsystem located in the OS/390 SSCVT chain. I know this was true for V4 and V5, but I haven't had access to a DS system set up in this fashion on V6 or V7. However, for a definitive answer, we will need to see if Roger M. or Jim R. will be able to respond. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com [...]
14006 52 41_Re: VARCHAR columns & other stupid things14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Tue, 19 Feb 2002 11:43:05 +1100350_iso-8859-1 Hi Rob,
That won't quite work - you will end up with 1 row per page, but the minimum space allocation in MVS is 1 TRACK which is 10-12 4K pages depending on your DASD, so the insert won't fail until you have 10-12 rows.
If you make the PRIQTY < track size it still allocates 1 track when the dataset is created for DB2. [...]
14059 90 41_Re: VARCHAR columns & other stupid things12_Raymond Bell17_rbell@NZ1.IBM.COM31_Tue, 19 Feb 2002 14:24:44 +1300412_us-ascii Guys,
Don't think it was new with DB2 for OS/390 V5 ('cause the Web version of the manual doesn't have | beside it) but you can specify MAXROWS 1 on the Create Tablespace statement to limit the number of rows on a page to 1. No more column padding rubbish. But, like you say Greg, you'll still get a tracks' worth of pages allocated. Don't suppose V8 will have a 'Maxpages' parameter... ;o) [...]
14150 26 46_Re: reorg catalog/directory index V6 and later0_17_JTonchick@AOL.COM29_Tue, 19 Feb 2002 00:14:46 EST615_US-ASCII I agree with Isaac. An index is an index and if it's statistics indicate that it should be reorged, then by all means reorg (REBUILD) it.
As far as the tablespaces in DSNDB06 go, it depends on when I schedule reorgs. For me, update activity is the highest in our development subsystem, so I schedule reorgs of the catalog tablespaces often (monthly). In production, our update activity is minimal (except for tables affected by RUNSTATS and SYSCOPY), so I reorg infrequently. Usually I check all catalog tablespaces for reorg possibility as part of PUT maintenance cycles or release upgrade. I [...]
14177 120 25_Re: Data Sharing question13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 19 Feb 2002 07:20:17 +0000539_iso-8859-1 DB2 address spaces are the most multi-TCB and multi-SRB address spaces I have ever seen. For splitting a query you need to research Query Parallelism and meet the prereqs. Otherwise enjoy. :-)
Martin
Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584)
"Taddei, Cathy" cc: Sent by: DB2 Data Subject: Re: Data Sharing question Base Discussion List [...]
14298 42 22_DB2 SQL Analysis Tools14_Curran, John F29_john.f.curran@UK.FID-INTL.COM31_Tue, 19 Feb 2002 08:48:56 -0000699_iso-8859-1 Hi All
I would be grateful if any person or company could tell me about any DB2 SQL monitoring tools on the market the version of DB2 we run is DB2 Version 7.1 Running on AIX 4.3.3.
Thank you in Advance
John Curran
Fidelity Investments Limited
> Fidelity Investments Limited, Fidelity Investment Services Limited, > Fidelity Pensions Management and Fidelity Investments International are > all regulated in the UK by the FSA and have their registered offices at > Oakhill House, 130 Tonbridge Road, Hildenborough, Tonbridge, Kent TN11 > 9DZ. Tel 01732 361144. Fidelity only gives information on products and [...]
14341 182 46_Re: federated database: authorization problems0_24_markus.schaub@SYSTOR.COM31_Tue, 19 Feb 2002 10:04:07 +0100488_us-ascii hi igor
i ran into the same problem with databases on nt- servers a while ago. i have found a work-around and maybe it's working for you as well. i used the following work-around: (using the command line, not the gui tools !)
1. connect to database 2. create wrapper drda 3. create server test type db2/linux version 7.1 wrapper drda options ( ... ) i did not specify the authorization string as it's optional 4. create user mapping ... 5. create nickname ... [...]
14524 182 21_Re: DSN1COPY question27_ROBINSON, Warwick, MANU ITI28_Warwick.Robinson@NATWEST.COM31_Tue, 19 Feb 2002 09:26:12 -0000380_windows-1252 You're welcome Le,
I should mention that DSN1COPY should be able to handle VSAM LDS extend operations without difficulty where the new extent can be allocated to the same device as the primary allocation. I've used the utility in this way successfully many times. The problem only arises where an extend operation requires the use of an alternate device. [...]
14707 76 26_Re: DB2 SQL Analysis Tools19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Tue, 19 Feb 2002 09:54:36 +0000473_- John,
We use the suite of "Database Guys" from DGI Inc. (http://www.breakthroughdb2.com) and find them to be excellent.
The tools we have are -
SQL-GUY : my personal favourite : allows tracking of high cost SQL Pool-GUY : buffer pool monitoring Space-GUY : space monitoring (we have lots of tablespaces so this proves invaluable) Connection-MINER : monitors from a user (connection) point of view Wise-GUY : general system status and monitoring [...]
14784 15 17_Re: Primary Index15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Tue, 19 Feb 2002 03:36:30 -0600495_- Rohn
For RI it is sufficient to have a unique key, specified in a UNIQUE-clause. So you haven't to alter the primary key, which can only be done by ALTER DROP PK and ALTER ADD new PK, but then you loose the connection to your other Child-table.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14800 27 15_Encrypting data14_Kumar, Sandeep31_Sandeep.Kumar@BLR.HPSGLOBAL.COM31_Tue, 19 Feb 2002 17:03:34 +0500626_- Hi All,
Is there any function in DB2 version 6.1, for encrypting column data before storing in to table. Any help in this regard would be highly appreciated.
Regards, Sandeep
WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have [...]
14828 36 24_Automatic Rebind Problem10_Price, Ray18_Ray.Price@DRKW.COM31_Tue, 19 Feb 2002 12:25:53 -0000456_iso-8859-1 Hello.
We are running DB2 V6 on OS/390 2.8. If I issue a DROP/CREATE of some object (e.g. and index), DB2 normally does an automatic REBIND next time the package is used. However, it appears that if I DROP/CREATE a Stored Procedure, the automatic REBIND does not succeed. I get reason code 00E30305, and the row in SYSPACKAGE is set to OPERATIVE "N". But an explicit REBIND does work. Does anyone know if there is a reason for this? [...]
14865 214 28_Re: Automatic Rebind Problem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 19 Feb 2002 14:03:17 -0000417_iso-8859-1 Hi Ray,
Hmmm
There is obviously a difference between INOPERATIVE and INVALID and I have just been trying to think what that might be.
One idea I had was this:
If you drop an INDEX DB2 knows that the SQL in the plan WILL still be operable even if you don't replace the index. So, by making the plan INOPERATIVE he knows that a rebind WILL work (so an auto-rebind will too). [...]
15080 84 54_Re: DB2 Abnormal Termination with Reason Code 00E5070511_Hayden, Lee18_Lee_Hayden@CSX.COM31_Tue, 19 Feb 2002 09:03:43 -0500629_iso-8859-1 Usually the 04F indicates a previous error. Look for errors in other DB2 address spaces. Also check SYS1.LOGREC "EREP" reports. This will usually show the sequence of events and will help determine the "root" cause....
X'04F' An X'04F' abend completion code indicates that DB2 detected a severe error and abended the entire DB2 subsystem. When an X'04F' is issued, DB2 determined that continued operation could result in the loss of data integrity. Errors associated with an X'04F' abend completion code might be preceded by an MVS system error or by one or more DB2 X'04E' abend completion codes. [...]
15165 58 28_Re: Automatic Rebind Problem12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 19 Feb 2002 06:37:17 -0800541_us-ascii Yes, its a design flaw. There's an APAR out against this problem. See PQ48564 and PQ55581 to start. --- "Price, Ray" wrote: > Hello. > > We are running DB2 V6 on OS/390 2.8. > If I issue a DROP/CREATE of some object (e.g. and index), DB2 > normally does > an automatic REBIND next time the package is used. However, it > appears that > if I DROP/CREATE a Stored Procedure, the automatic REBIND does not > succeed. > I get reason code 00E30305, and the row in SYSPACKAGE is set to > OPERATIVE > "N". But an [...]
15224 14 20_ESS & UDB for OS/39010_Bill Trago33_William_D_Trago_Jr@FREDDIEMAC.COM31_Tue, 19 Feb 2002 09:56:37 -0500377_us-ascii Is there any good documentation out there on ESS (Shark) and UDB for OS/390?? Setup (DB2), performance, issues.......... Thanx
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
15239 54 19_Re: Encrypting data11_Kent Knight14_kentk@ASPG.COM31_Tue, 19 Feb 2002 09:58:55 -0500507_iso-8859-1 Sandeep,
I am new to the list, so please forgive any mistakes at this point.
Just wanted to say I work for a company that offers a product that will encrypt/decrypt DB2 columns. If you are interested you could email me directly.
Thanks, Kent Knight KentK@ASPG.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Kumar, Sandeep Sent: Tuesday, February 19, 2002 7:04 AM To: DB2-L@RYCI.COM Subject: Encrypting data [...]
15294 84 25_Re: DB2 Stored Procedures14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 19 Feb 2002 10:06:03 -0500498_iso-8859-1 As far as writing to an output text file is concerned, we use an output file to write trace messages from our SQLJ stored procs. We created a separate SP called Jsptrace as below. Now in our other SPs, when we wish to write any string to an output file, we just give a call like: Jsptrace.trace_out("This is a string"); This is of course, after importing the package (jsp.trace.*; in this example) in the other SPs. Works in both NT and Unix. Hope this is what you are looking for. [...]
15379 52 25_Re: DB2 Stored Procedures10_Tom Willis24_tom_r_willis@HOTMAIL.COM31_Tue, 19 Feb 2002 09:02:07 -0600
15432 165 55_Re: What should I do about a -904 reason code 00D70014?14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Tue, 19 Feb 2002 09:16:32 -0600626_us-ascii Ok, so senility is setting in early.
If you know what insert row was the last one inserted then you can start from the next row after you resize and reorg the tablespace and/or indexspaces.
John Arbogast wrote:
> When did this become a load?? I thought the original post mentioned > inserts? I am with J. Hardy. Why recover? > > -----Original Message----- > From: glen.sanderson@SAFEWAY.COM [mailto:glen.sanderson@SAFEWAY.COM] > Sent: Monday, February 18, 2002 11:00 AM > To: DB2-L@RYCI.COM > Subject: Re: What should I do about a -904 reason code 00D70014? > > It depends on what stage the [...]
15598 19 33_RUNSTATS on DB2 Catalog on OS/39012_Marc Matulis30_marc.matulis@OSFHEALTHCARE.ORG31_Tue, 19 Feb 2002 09:13:22 -0600562_- Does anyone know of a 'preferred' order in which to do the RUNSTATS on the DB2 Catalog? I've read the DB2 v7.1 Utility Guide and Reference, but it doesn't go into that detail.
I'm assuming that the order in which the DSNDB06 TableSpaces are RUNSTATed doesn't matter.
Any opinions welcomed.
Thanks.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
15618 102 25_Re: DB2 Stored Procedures15_Russell Collins21_russell@ECTAUSTIN.COM31_Tue, 19 Feb 2002 09:43:32 -0800600_iso-8859-1 Thank you. This is a start. I will try some of this logic to see it will work.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Sinha, Abhijit Sent: Tuesday, February 19, 2002 7:06 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Stored Procedures
As far as writing to an output text file is concerned, we use an output file to write trace messages from our SQLJ stored procs. We created a separate SP called Jsptrace as below. Now in our other SPs, when we wish to write any string to an output file, we just give a call [...]
15721 100 25_Re: Data Sharing question13_Gleason, Mike22_MGleason@UILLINOIS.EDU31_Tue, 19 Feb 2002 09:54:08 -0600591_iso-8859-1 Hi Cathy, Your LPAR isn't constrained to use only one CP, is it?
OS/390 2.10, DB2 v6, single production LPAR, no data sharing (yet). Is there any benefit to implementing data sharing if you are only going to have two members running on the same MVS? The perception here is that generally, DB2 will only use one CP unless parallelism is invoked, and the way to make better use of our idle processors is to expand horizontally. Is anyone else running multiple members of the same data sharing group on a single MVS? Will CICS (multiple AOR's to one TOR) [...]
15822 149 28_Re: Automatic Rebind Problem0_19_Tim.Lowe@STPAUL.COM31_Tue, 19 Feb 2002 10:07:44 -0600505_us-ascii Phil, I think you have this backwards. Dropping an index marks dependent packages as INVALID(not inoperative), and dynamic rebind can occur. Dropping a stored procedure, marks dependent packages as INVALID and INOPERATIVE, and dynamic rebinds will NOT occur.
And, your analagy about WHY this is true seems to be in question because if you drop a table, it marks dependent packages as invalid, not inoperative. I don't see why dropping one dependent structure causes a bigger problem. [...]
15972 83 28_Re: Automatic Rebind Problem10_Price, Ray18_Ray.Price@DRKW.COM31_Tue, 19 Feb 2002 16:16:24 -0000511_iso-8859-1 Hi Phil,
Thanks for the reply. I should have explained that after the DROP/CREATE, SYSPACKAGE showed VALID=N, and OPERATIVE=Y. After I tried to run the program, it changed OPERATIVE to N, presumably because the automatic REBIND failed.
Regards, Ray Price Database Manager Dresdner Kleinwort Wasserstein London
-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: 19 February 2002 14:03 To: DB2-L@RYCI.COM Subject: Re: Automatic Rebind Problem [...]
16056 87 58_Re: AW: Problem with replication when using VARCHAR(32000)15_Manus, Wolfgang22_wolfgang.manus@EDS.COM31_Tue, 19 Feb 2002 16:19:25 -0000484_- Anand,
looks like you're missing a 32 K temp tablespace for apply processing, maybe the queries you've been running so far didn't yet require one. Apply joins the CD table with the UOW table and reads the result in ascending log sequence. It obviously needs to materialize this join and needs a temp tablespace with the appropriate page size for that. So just create a 32 K temp tablespace, e.g. CREATE TEMPORARY TABLESPACE TEMPSPACE32 ..., and Apply should be working. [...]
16144 247 28_Re: Automatic Rebind Problem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 19 Feb 2002 16:35:17 -0000462_iso-8859-1 You know, Ray is right
Heaven knows what I was thinking earlier!
Apologies for misleading
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Price, Ray [mailto:Ray.Price@DRKW.COM] Sent: 19 February 2002 16:16 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Automatic Rebind Problem [...]
16392 134 25_Re: Data Sharing question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 19 Feb 2002 08:52:32 -0800525_iso-8859-1 Hi Mike. No, the LPAR has 10 CP's available. However, someone ran an SMF report showing that DB2 tasks were waiting for CPU, and there were processors available. There is a perception here that data sharing will help alleviate this particular situation, which I'm not really sure is really happening. At any rate, I'm happy to implement it, I just don't think running two members on the same LPAR is a good idea for us and I'm looking for information to back up my reservations about it. Thanks, Cathy Taddei [...]
16527 15 45_Randy Krueger/Schneider is out of the office.13_Randy Krueger22_KruegerR@SCHNEIDER.COM31_Tue, 19 Feb 2002 11:24:28 -0600389_us-ascii I will be out of the office starting 02/19/2002 and will not return until 02/20/2002.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16543 122 25_Re: Data Sharing question23_Tello Najera Juan Pablo26_jp.tello@BBVA.BANCOMER.COM31_Tue, 19 Feb 2002 11:27:23 -0600523_iso-8859-1 Hi Cathy: If DB2 is waiting for CPU maybe the WLM priority classes are not set as they should. It may be a good idea to look WLM before thinking in a data sharing solution. Other thing. As far as I know, DB2 is a multiple TCB address space and also a CICS Region that is connected to DB2 uses this multiple TCB feature of DB2. Using the RCT you can asign many different TCB's to many transactions at any time. In CICS-DB2 shops the CICS main TCB is only for CICS control and dispatching but the work in DB2 [...]
16666 199 28_Re: Automatic Rebind Problem14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 19 Feb 2002 12:34:54 -0500612_iso-8859-1 I ran into this sometime back. I believe the APAR that was mentioned previously focused on Stored Procedure Builder and limited the dropping/recreating for all SP changes. We used JCL to prepare our SPs...
Anyway, when the SP is dropped, the calling package is marked invalid. When AUTOBIND kicks in it fails because the timestamp of the calling package is older than the recently dropped SP (this is the rule that causes the hitch and DB2 says "I can't find this object"). When the autobind fails, the calling package is marked inoperative. You then have to physically rebind the calling [...]
16866 154 46_Pittsburgh DB2 User Group - March 6th - Agenda10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Tue, 19 Feb 2002 12:44:29 -0500374_iso-8859-1 Greetings everyone!
Below is the agenda for the upcoming FREE Pittsburgh PA DB2 Users Group on March 6th. If you would like to register & have not already done so, please RSVP to me at Cathy.Peck@Highmark.com.
Directions can also be found on the PA DB2 UG website: http://www.db2parug.org. The agenda will be posted on the website shortly too. [...]
17021 42 0_17_Alvarez, Oscar M.20_MAXIMILIANO@IADB.ORG31_Tue, 19 Feb 2002 13:31:29 -0500698_iso-8859-1 Anand
I believe you have to create the CD tablespace using 32K bufferpool.
Max.
-----Original Message----- From: Anand Sundaresan [mailto:Anand.Sundaresan@UBSW.COM] Sent: Monday, February 18, 2002 9:11 AM Subject: Re: AW: Problem with replication when using VARCHAR(32000)
Thanks a lot for this suggestion. It really helps creation of the replication sources for 32K Sizes but with the exception of before image copy. But there is one more problem associated with it, the Apply fails with the condition that the Page Size of the Temporary Table space (CD Table) is not sufficient, although the CD Table is a replica of the Source [...]
17064 17 48_Tammie Pollack/Boulder/IBM is out of the office.14_Tammie Pollack17_tammie@US.IBM.COM31_Tue, 19 Feb 2002 11:44:02 -0700527_us-ascii I will be out of the office starting February 19, 2002 and will not return until February 22, 2002.
I will be away from the office on Feb 19-22. My backup is Madhavi Durgam, durgam@us.ibm.com 303.924.2294 and my team lead is Velma Smith, velma@Us.ibm.com, 303.924.1713.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17082 26 39_Evaluating OR criteria in OS/390 DB2 V513_Judi Westcott23_judi_westcott@YAHOO.COM31_Tue, 19 Feb 2002 12:42:47 -0600403_- Quick question: Does anyone know if DB2 has the logic built in to it to quit evaluating OR criteria once one of the OR criteria is true?
For instance:
SELECT ... FROM table1 WHERE columna = 'x' OR columnb = 'y' OR columnc = 'z'
If any one of the criteria is true the row qualifies. Will DB2 evaluate all three criteria or will it stop after finding the first true condition? [...]
17109 32 28_Re: AUTOMATIC REBIND PROBLEM12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 19 Feb 2002 11:20:23 -0800573_us-ascii Actually APAR PQ55581 is for VALID(RUN) issues and the invalid settings for resetting SYSPACKDEP after a DROP/CREATE.
IBM (Chris Cotner) stated at the 2000 IBM DB2 conference that they had made a mistake on the issue of dropping SQL stored procedures and would correct it.
Part of the APARs that I've seen allow you to issue ALTERs of the SP when you change the SP code (not parameters). You can use the ALTER instead of DROP/CREATE to change the SP code and it won't cause the REBIND issues that it did with DROP/CREATE. AUTOREBIND then works. [...]
17142 110 25_Re: Data Sharing question11_rick creech18_ykcirc@HOTMAIL.COM31_Tue, 19 Feb 2002 13:20:55 -0600589_- Cathy,
The main reasons that I have heard for going to datasharing are as follows: 1. Need more CPU power. 2. Need more memory, because of constraints on the DBM1 limit. 3. Availability. If one machine is down, applications can still be processing on a different machine.
To accomplish any of the above, we are talking about running an additional datasharing member on a different LPAR at least, or on a different machine. Except for certain unique situations, you would not want to bring up more than 1 member on a given lpar. There is actually extra cpu overhead in [...]
17253 57 43_Re: Evaluating OR criteria in OS/390 DB2 V513_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 19 Feb 2002 11:35:00 -0800516_us-ascii Judi,
I believe that it will quit after the first.
Tink --- Judi Westcott wrote: > Quick question: Does anyone know if DB2 has the > logic built in to it to > quit evaluating OR criteria once one of the OR > criteria is true? > > For instance: > > SELECT ... > FROM table1 > WHERE columna = 'x' > OR columnb = 'y' > OR columnc = 'z' > > If any one of the criteria is true the row > qualifies. Will DB2 evaluate > all three criteria or will it stop after finding [...]
17311 40 10_ADD_MONTHS13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 19 Feb 2002 11:37:22 -0800540_us-ascii Hi all,
Has anyone else experienced problems with ADD_MONTHS.
SELECT ADD_MONTHS('2000-02-01',1) FROM SYSIBM.SYSDUMMY1;
yields
SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID
That date is valid for other date functions.
Tink
===== B.L. "Tink" Tysor Bayard Lee Tysor, Inc (401)965-2688 www.BLTysor.com
__________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com [...]
17352 38 28_Re: Automatic Rebind Problem14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 19 Feb 2002 14:52:08 -0500666_iso-8859-1 Myron,
Are you sure you can "alter" the procedure body? Is this a post-V7, S390 APAR? I found the following in the V7 for s390 Application Programming and SQL Guide:
For an external stored procedure, you define the stored procedure to DB2 by executing the CREATE PROCEDURE statement. You change the definition of the stored procedure by executing the ALTER PROCEDURE statement. For an SQL procedure, you define the stored procedure to DB2 by preprocessing a CREATE PROCEDURE statement, then executing the CREATE PROCEDURE statement statically or dynamically. As with an external stored procedure, you change the definition by executing [...]
17391 82 43_Re: Evaluating OR criteria in OS/390 DB2 V59_Euf Rosal18_eufrosal@YAHOO.COM31_Tue, 19 Feb 2002 12:04:01 -0800559_us-ascii The OR is basically equivalent to UNION/UNION ALL and not a Boolean operation. So, all OR predicates will have to be evaluated. If you intend boolean operation then, you may have to use EXISTS/NOT EXISTS constructs.
--- "BL.Tink Tysor" wrote: > Judi, > > I believe that it will quit after the first. > > Tink > --- Judi Westcott wrote: > > Quick question: Does anyone know if DB2 has the > > logic built in to it to > > quit evaluating OR criteria once one of the OR > > criteria is true? > > [...]
17474 45 31_What's your IRLM MAXCSA set to?13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Tue, 19 Feb 2002 15:13:07 -0500340_US-ASCII List,
As part of our new DB2 V7 upgrade we recently encountered the "DXR175E IRT1001 IRLM IS UNABLE TO OBTAIN STORAGE - MCSA" message. I checked the IRLM (MODIFY DST2IRLM,STATUS,STOR) and noticed that 15M was being used for storage!
Checking the IRLM Proc JCL I find the following: . . . PC=NO, MAXCSA=15, . . . [...]
17520 59 37_Re: RUNSTATS on DB2 Catalog on OS/39012_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 20 Feb 2002 09:13:35 +1300578_us-ascii Hi Marc,
I can't see how the order in which you do Runstats matters at all, certainly not to DB2. Each execution is atomic (I think the expression is), completely independent of any other Runstats execution. The only way I can see it maybe mattering (is that a word?) is for application tablespaces, if you're loading/inserting... nope, I take it back. I can't see any occurence where the order in which you run Runstats on objects, Catalog or application, matters. Order between 'update' processes, yes, but not between executions of the Runstats utility. [...]
17580 59 14_Re: ADD_MONTHS16_Herbold, John W.26_JWHERBOLD@ARKBLUECROSS.COM31_Tue, 19 Feb 2002 14:18:23 -0600551_iso-8859-1 Why not use
SELECT '2000-02-01' + 1 MONTHs FROM SYSIBM.SYSDUMMY1;
John
-----Original Message----- From: BL.Tink Tysor [mailto:tink@BLTYSOR.COM] Sent: Tuesday, February 19, 2002 1:37 PM To: DB2-L@RYCI.COM Subject: ADD_MONTHS
Hi all,
Has anyone else experienced problems with ADD_MONTHS.
SELECT ADD_MONTHS('2000-02-01',1) FROM SYSIBM.SYSDUMMY1;
yields
SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID
That date is valid for other date functions. [...]
17640 105 43_Re: Evaluating OR criteria in OS/390 DB2 V515_Jackson Reavill18_damcon2@US.IBM.COM31_Tue, 19 Feb 2002 15:24:05 -0500606_us-ascii Yes, it will quit after getting a hit on the first.
Cheers, Jay
Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 356-5317, Tie Line 8-697-5317
----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works -----------------------------------------------------
"BL.Tink Tysor" To: DB2-L@RYCI.COM Subject: Re: Evaluating OR criteria in OS/390 DB2 Sent by: DB2 V5 Data Base Discussion List [...]
17746 109 25_Re: Data Sharing question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 19 Feb 2002 12:23:57 -0800534_iso-8859-1 Thanks to all who responded, your feedback was most valuable. As a result, I've had discussions with our performance expert, and we decided to split a heavily used AOR, and then see what kind of bottleneck we do or don't have in DB2. I think the DBM1 delay for CPU that he was seeing is a WLM issue, and in fact we are achieving a high degree of parallelism. Part of my question was, would CICS be able to use multiple members if on the same MVS. I made some local inquiries, and was surprised to learn that CICS does [...]
17856 27 21_Re: Deadlock question14_Lisa Ouellette29_Lisa.Ouellette@FIRSTUNION.COM31_Tue, 19 Feb 2002 14:03:34 -0600505_- I believe I have seen this happen. When it happened to me I believe it was the following scenario:
The access path was via and index but it turned out the index really didn't filter many rows out and DB2 had to go to the data page to qualify the rows to be returned. So, when DB2 went to the datapage it tried to get a 'latch' - just to make sure the page wasn't in the middle of being re- arranged by another updater. It couldn't get the latch because in fact, one of the MANY pages it was [...]
17884 16 35_Re: What's your IRLM MAXCSA set to?12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Tue, 19 Feb 2002 15:00:43 -0600327_iso-8859-1 question 1: MAXCSA=6,(small shop) PC=NO, question 2: nope
hth, john
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17901 90 14_Re: ADD_MONTHS13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 19 Feb 2002 13:00:52 -0800633_us-ascii John,
I am testing out the ADD_MONTHS scalar function, since that gives different results for the last day of the month.
Tink --- "Herbold, John W." wrote: > Why not use > > SELECT > '2000-02-01' + 1 MONTHs > FROM SYSIBM.SYSDUMMY1; > > John > > -----Original Message----- > From: BL.Tink Tysor [mailto:tink@BLTYSOR.COM] > Sent: Tuesday, February 19, 2002 1:37 PM > To: DB2-L@RYCI.COM > Subject: ADD_MONTHS > > > Hi all, > > Has anyone else experienced problems with > ADD_MONTHS. > > SELECT > ADD_MONTHS('2000-02-01',1) > FROM SYSIBM.SYSDUMMY1; > > yields > > SQLCODE = -180, [...]
17992 118 43_Re: Evaluating OR criteria in OS/390 DB2 V513_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Tue, 19 Feb 2002 15:00:48 -0600537_iso-8859-1 I sort-of agree, but won't this really depend on the access path? Assuming no index-access on any of the three columns, I agree that it will quit after hitting the first one. But what if you are getting multiple-index access using 3 indexes (1 on columna, 1 on columnb, 1 on columnc)? In this case you are going to gather all of the rids that match the three pieces of criteria independently and the union of those lists is your final set of rids, so in this case it does not stop after finding the first true condition. [...]
18111 55 41_Re: VARCHAR columns & other stupid things9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 19 Feb 2002 14:11:37 -0700544_us-ascii Greg,
Good point I blew that off.
Maybe putting the object in RO status (-sta db(dbname) space(tsname) access(ro)) after the one row is populated would be the best approach to ensuring only 1 row would get into the table via DML.
-Rob
"Palgrave, Greg" wrote: > > Hi Rob, > > That won't quite work - you will end up with 1 row per page, but the minimum > space allocation in MVS is 1 TRACK which is 10-12 4K pages depending on your > DASD, so the insert won't fail until you have 10-12 rows. > > If you [...]
18167 71 35_Re: What's your IRLM MAXCSA set to?23_Tello Najera Juan Pablo26_jp.tello@BBVA.BANCOMER.COM31_Tue, 19 Feb 2002 15:10:21 -0600301_iso-8859-1 I have a data sharing group with 3 members and each IRLM has MAXCSA=24. If you are runing out of memory in IRLM maybe you have turned on ROW LEVEL LOCKING in many tables or you have your ZPARM NUMLKUS set to a very high number so you allow a user to have to many locks. It is an idea. [...]
18239 10 11_UNSUBSCRIBE0_19_gmabry@HARCOURT.COM31_Tue, 19 Feb 2002 16:07:34 -0500244_-
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18250 25 28_Re: Automatic Rebind Problem0_19_Tim.Lowe@STPAUL.COM31_Tue, 19 Feb 2002 15:19:01 -0600478_us-ascii Scott, I agree with you about what is occurring. But, I don't understand why. Why does automatic rebind check the stored procedure timestamp at all? Why does automatic rebind care what the package timestamp is in comparison to the create timestamp of the stored procedure?
It does not seem to make any more sense to me than if automatic rebind checked the package timestamp against the timestamp that the table was created. What are they trying to prevent? [...]
18276 175 43_Re: Evaluating OR criteria in OS/390 DB2 V515_Jackson Reavill18_damcon2@US.IBM.COM31_Tue, 19 Feb 2002 16:50:20 -0500655_us-ascii Mike,
Good point. I hadn't thought it thru that much. It's the classic "it depends".
Cheers, Jay
Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 356-5317, Tie Line 8-697-5317
----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works -----------------------------------------------------
"Vaughan, Mike" cc: Sent by: DB2 Data Subject: Re: Evaluating OR criteria in OS/390 DB2 Base Discussion V5 List [...]
18452 19 14_Re: ADD_MONTHS15_Craig Nicholson27_Marion_C._Nicholson@HUD.GOV31_Tue, 19 Feb 2002 17:00:34 -0500349_us-ascii Try
SELECT (DATE('2000-02-01') + 1 MONTH) FROM SYSIBM.SYSDUMMY1;
Thanks
Craig
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18472 26 21_Re: Deadlock question12_Jim Harrison12_jimh@QIS.NET31_Tue, 19 Feb 2002 17:03:57 -0800377_us-ascii Ok, you've got me curious now. I thought I once heard that latches were just that - latches. They didn't escalate to locks. You simply waited for them to go away. I even believe I heard there was no time-out period, but I could be wrong on that (seems like a prudent thing to have in extreme cases). Can't find my notes on it at the moment. Anyone know for sure? [...]
18499 38 11_UNSUBSCRIBE12_Collier, Mel21_MCollier@TXFB-INS.COM31_Tue, 19 Feb 2002 16:13:10 -060019_iso-8859-1
18538 18 21_Re: Deadlock question12_Jim Harrison12_jimh@QIS.NET31_Tue, 19 Feb 2002 17:14:38 -0800499_us-ascii Oops. Wrong wording there. Before I get hammered, what I meant was: you waited until you got one (a latch that is, since you are the one requesting it).
At 05:03 PM 2/19/2002 -0800, Jim Harrison said: > You simply waited for them to go away.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18557 67 14_Re: ADD_MONTHS13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 19 Feb 2002 16:14:05 -0600490_us-ascii Tink,
I found this APAR in a DB2-L post from Roger two weeks ago - APAR PQ53359.
Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of BL.Tink Tysor Sent: Tuesday, February 19, 2002 1:37 PM To: DB2-L@RYCI.COM Subject: ADD_MONTHS [...]
18625 113 14_Re: ADD_MONTHS13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Wed, 20 Feb 2002 09:25:08 +1100479_iso-8859-1 Hi Tink, I can't test this but I can guess from the error message that you need to supply a date to the ADD_MONTHS function and not a literal.
So you will have to wrap your literal with a DATE function (or similar) along the lines of ADD_MONTHS(DATE('.......'),n).
HTH
Steve T
-----Original Message----- From: BL.Tink Tysor [mailto:tink@BLTYSOR.COM] Sent: Wednesday, 20 February 2002 8:01 AM To: DB2-L@RYCI.COM Subject: Re: ADD_MONTHS [...]
18739 117 43_Re: Evaluating OR criteria in OS/390 DB2 V513_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 19 Feb 2002 16:24:32 -0600443_us-ascii Euf,
What you say applies for a false condition, that all ORs will need to be evaluated to determine if all are false. But this example asks for a true.
I'm just going to elaborate upon Mike Vaughan's answer......
If a non-matching index scan (single index) or tablespace scan - then first true will stop. Multi-index access - you need to gather all RIDs and UNION them. So you don't stop on the first true. [...]
18857 202 40_Re: Table space scan due to large index?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 19 Feb 2002 16:34:55 -0600326_iso-8859-1 John,
I haven't had the need to go into any further detail in attempting to understand this. So apart from knowing that a non-matching index scan will follow the pointers (which makes sense, otherwise it could not be used to avoid a sort), I just assume that the optimizer knows which is best and when. [...]
19060 119 15_Archive for DB213_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 19 Feb 2002 14:48:03 -0800605_iso-8859-1 Hello list. About a year ago, I asked how people were handling the archive of production data that was no longer needed online but not yet ready for the bit bucket. From the responses I got, it seemed that most people who were already archiving had built it into their applications, or developed some other home grown solution utilizing FileAid or other packages. At least one shop had purchased Archive for DB2 from Princeton Softech, and several others were evaluating it. Well, we evaluated it too, and purchased it in December. We're getting close to implementing our first pilot, and [...]
19180 61 28_Re: Automatic Rebind Problem10_Price, Ray18_Ray.Price@DRKW.COM31_Wed, 20 Feb 2002 07:13:25 -0000400_iso-8859-1 Hi Tim,
I had the same question in mind, I'd be interested to hear a reason, if anyone knows.
Additionally, I can't see why the automatic rebind differs from the manual rebind. Why should one work and the other fail - I thought they were essentially the same thing?
Regards,
Ray Price Database Manager Dresdner Kleinwort Wasserstein London [...]
19242 103 37_Re: RUNSTATS on DB2 Catalog on OS/3900_18_mebert@AMADEUS.NET31_Wed, 20 Feb 2002 09:30:47 +0100359_us-ascii RUNSTATS updates (changes) two tablespaces, SYSDBASE and SYSSTATS. So if you have a separate job for a Catalog RUNSTATS, you might want to put SYSDBASE next-to-last, and SYSSTATS last, to have the most up-to-date and consistent stats possible.
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...]
19346 144 16_Re: CRCR problem12_leila rahimi21_lshosaini@HOTMAIL.COM31_Wed, 20 Feb 2002 12:21:11 +0000741_- HI All Mike,thanks alot for your mails... I learnded many things from your mails.but unfortunately i had done,next CRCR ,before i receive your second mail.and my DB2 became normal till yesterday.operator mistaked and had done "load clear" on system before stop DB2 and other subsystems,then DB2 has problem now,and it can't start for this reason : $HASP373 DB2BDIST TARTED IEF403I DB2BDIST - STARTED -TIME=12.47.06 DSNR001I @ RESTART INITIATED DSNR003I @ RESTART...PRIOR CHECKPOINT RBA=00004B2054E4 DSNJ151I @ DSNJR003 IN READING RBA '00004B20552A' A317 NON ZERO REASON CODE WAS RETURNED. CONNECTIONID=DB2B, CORRELATION-ID=003.RCRSC 02, REASON-CODE=00D10327, MEMBERID=0 DSNV086E @ DB2 ABNORMAL TERMINATION REASON=00D96021 IEA794I SVC [...]
19491 91 35_Re: What's your IRLM MAXCSA set to?21_Tpg Sysdba department18_Sysdba.AHE@TNT.COM31_Wed, 20 Feb 2002 12:16:44 +0000440_us-ascii Take a look at APAR PQ37950 (fixed by IRLM PTF UQ43575). This could be your problem - it was certainly ours.
Neil Price Applications Support Team leader (IMS and DB2 Systems Programmer / DBA) IS Central Systems TNT Post Group, UK
"Lockwood Lyon" cc: Sent by: "DB2 Subject: What's your IRLM MAXCSA set to? Data Base Discussion List" [...]
19583 82 28_Re: Automatic Rebind Problem14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 20 Feb 2002 07:26:35 -0500284_iso-8859-1 Tim,
The short story - I agree, I don't know, I don't know, I don't know.
The long story - Back when this was a pressing issue for us, I tried to think of a reason why...but I couldn't come up with one. Maybe it was because I was too close my problem(?) [...]
19666 92 35_Re: What's your IRLM MAXCSA set to?9_Colin Fay13_cfay2@CSC.COM31_Wed, 20 Feb 2002 07:37:51 -0500368_us-ascii Hi
1) Smallinstallation :
MAXCSA=7, PC=NO,
2) We had to bump this up about 1 month after going to version 6 when a large 'runaway' SPUFI query with RR stability locked up the system. Hiw much its related to the new release , I dont know. We did copy DSNESPCS into plan DSNESPRR no one could not use RR through SPUFI. [...]
19759 13 16_DB2 Log question9_Don Cross20_Don.Cross@TC.FAA.GOV31_Wed, 20 Feb 2002 08:09:01 -0500287_us-ascii After a full image copy is made and inserts are made to a table what happens to the current log entrys if a table is dropped? Assume the most simple case- a table in an simple tablespace. Are these last inserted records 'available' from the log after recreating the table? [...]
19773 75 35_Re: What's your IRLM MAXCSA set to?14_Harvey Puckett19_hpuckett@GTA.GA.GOV31_Wed, 20 Feb 2002 08:18:16 -0500495_iso-8859-1 Hey LL ... howzit. Yeah, we use PC=YES and manage the storage vs letting the system manage it. We are OS/390 2.6 and DB2 5 and we find that we see a HWM in our production DB2s of 500-700K of storage being used by the IRLM. We tried PC=NO but found the beast gets too hungry at times ... so we went the other way and by so doing are better able to adjust the parms for concurrent users and max locks by those users ... [...]
19849 19 56_Shan Leatherman/MO/americancentury is out of the office.0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Wed, 20 Feb 2002 07:25:09 -0600547_us-ascii I will be out of the office starting 02/20/2002 and will not return until 02/25/2002.
If you need additional assistance, send a message to *DB or if you need immediate assistance page the primary DBA on call at (816) 292-5449 or contact the help desk (816) 340-4250.
Shan Leatherman
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
19869 52 28_Re: Automatic Rebind Problem12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 20 Feb 2002 05:34:22 -0800466_us-ascii Scott, Here's what I've found out. At the 2000 IBM DB2 Tech conference I asked Chris Cotner and the rest of the IBM gurus at the evening question and answer session why when you changed the code (not parms) on a Stored Procedure using the Stored Procedure Builder it dropped and re-created the procedure causing all the packages to be marked as INVALID and NON-OPERATIVE. The concensus answer I got back was that this was an error and they would this. [...]
19922 156 20_Re: DB2 Log question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 20 Feb 2002 13:42:52 -0000573_iso-8859-1 At last
A simple question that does have a simple answer!!
YES
NOTHING is EVER removed from the log.
So, if you drop an object then it IS possible to recover the missing data (I can sense a supplementary question looming in the wings!!)
BUT!!! (isn't there always a "but")
the log records will have all the internal ids (DBID, OBID etc.) associated with them AND when you recreate the object, even if you engineer all the IDs to be the same, you still CANNOT use the IBM recover utility to apply these log records. [...]
20079 94 35_Re: What's your IRLM MAXCSA set to?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 20 Feb 2002 08:49:46 -0500403_iso-8859-1 Harvey, I don't think LL has a V5 to V7 issue. We did just that and saw no appreciable CSA increase (MAXCSA=7,PC=NO on 3 prod subsys).
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Harvey Puckett [mailto:hpuckett@GTA.GA.GOV] Sent: Wednesday, February 20, 2002 8:18 AM To: DB2-L@RYCI.COM Subject: Re: What's your IRLM MAXCSA set to? [...]
20174 27 35_Re: What's your IRLM MAXCSA set to?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 20 Feb 2002 15:13:34 +0100515_us-ascii HI
1) MAXCSA=5 (nanosystem) (7 in some other DB2)
2) We monitor DB2 components memory usage, I did some tests in a 'naked' DB2 to see how and when DB2 uses memory but I never saw a high memory usage nor spikes (CSA) for IRLM (even for row locking, but it was a very small test DB2 probably in production an high usega and spikes are very probable, if not common) I'd curious to know what kind of lock size do you use (ROW, SYSTEM.....RR,CS) and if you are in a DS system. Just to learn. [...]
20202 16 35_Re: What's your IRLM MAXCSA set to?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 20 Feb 2002 15:16:34 +0100350_us-ascii Hi Lockwood
Another quick question ......did you (recently) migrate to S390 2.10 ?
MS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
20219 26 32_COMMIT/ROLLBACK commands in SP??31_Ramakrishnan Chandraseharapuram41_Ramakrishnan_Chandraseharapuram@USSWI.COM31_Wed, 20 Feb 2002 19:53:23 +0530529_iso-8859-1 Hi,
Is it possible to COMMIT or ROLLBACK DB operations from inside a stored procedure? If so, how to do it?
I am working with SPs written in C language on DB2.
the client calling the SP is also written in C using CLI
please help.
Regards, CP
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
20246 114 43_What's your IRLM MAXCSA set to? - Follow-up13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Wed, 20 Feb 2002 09:34:02 -0500392_US-ASCII List,
A quick review of responses shows that: Many shops are setting MAXCSA in the 5-8 Mb Range Some ("large") shops are setting MAXCSA to 24 Mb or higher
Sorry I didn't give complete info last time. Here's our environment.
OS/390 2.8 DB2 for z/OS and OS/390 V7 (PDO 0147)
Recently (Jan 2002) migrated Development DB2s to V7. Production is still V6. [...]
20361 173 36_Re: COMMIT/ROLLBACK commands in SP??14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 20 Feb 2002 15:36:20 -0000386_iso-8859-1 COMMIT? - No, not at present ROLLBACK? - No, not at present either
BUT....
if you issue an 'invalid' instruction from within a stored procedure (egg ROLLBACK) your current unit of work is rolled back and an error is returned to the caller.
So.....
You COULD do a rollback, but then you are relying on this working the same way going forward. [...]
20535 151 47_Re: What's your IRLM MAXCSA set to? - Follow-up16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 20 Feb 2002 10:36:24 -0500509_iso-8859-1 LL Does your dev environment include lots of (shudder!!!!) QMF usage? Do you have any SPUFI users with ISOLATION RR? Do you have high usage of vendor stuff (CA, BMC, Compuware) bound RR? Lots of stuff having RELEASE(DEALLOCATE)?
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Lockwood Lyon [mailto:Lockwood.Lyon@MEIJER.COM] Sent: Wednesday, February 20, 2002 9:34 AM To: DB2-L@RYCI.COM Subject: What's your IRLM MAXCSA set to? - Follow-up [...]
20687 151 47_Re: What's your IRLM MAXCSA set to? - Follow-up16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 20 Feb 2002 10:36:24 -0500509_iso-8859-1 LL Does your dev environment include lots of (shudder!!!!) QMF usage? Do you have any SPUFI users with ISOLATION RR? Do you have high usage of vendor stuff (CA, BMC, Compuware) bound RR? Lots of stuff having RELEASE(DEALLOCATE)?
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Lockwood Lyon [mailto:Lockwood.Lyon@MEIJER.COM] Sent: Wednesday, February 20, 2002 9:34 AM To: DB2-L@RYCI.COM Subject: What's your IRLM MAXCSA set to? - Follow-up [...]
20839 16 47_Re: What's your IRLM MAXCSA set to? - Follow-up10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 20 Feb 2002 16:45:16 +0100300_us-ascii Hi Lockwood
Thank you for ifnfos
MS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
20856 65 36_Re: COMMIT/ROLLBACK commands in SP??15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 20 Feb 2002 10:44:59 -0500486_iso-8859-1 You can use savepoints in OS/390 till Version DB2 6 to rollback, but commit is only done after exiting the SP. In version 7 Db2, I think commit and rollback is allowed. One thing I did not understand is about the syntax of savepoints
>>__SAVEPOINT__svpt-name___ ________ ________________________________> | # | |_UNIQUE_| | | | # | (1) | # | >__ON ROLLBACK RETAIN CURSORS________________________________________> | | | # | (1) | # | _ON ROLLBACK RETAIN LOCKS____ [...]
20922 46 35_Re: What's your IRLM MAXCSA set to?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 20 Feb 2002 10:48:40 -0500408_us-ascii Max:
Interesting !!! Sometime after we migrated to 2.10 we ran into a similar problem and had to increase MAXCSA from 8 to 24 on one of our subsystems .... never thought that OS upgrade had anything to do with it then, since around the same time an application started processing a lot more than its usual load - (updating multiple tablespaces with row level locking and not commiting). [...]
20969 22 35_Re: What's your IRLM MAXCSA set to?10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 20 Feb 2002 09:26:25 -0600420_us-ascii Large - very large system:
MAXCSA=20, PC=NO,
Thanks. Missy Case IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 FDR PFDBA 701-275-6358
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
20992 83 16_Re: CRCR problem11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Wed, 20 Feb 2002 11:11:58 -0500560_ISO-8859-1 Hi Leila
I think the only safe option you have is to perform disaster recovery as described in my previous email, using the last archive log from before the first CRCR as the base for recovery.
Before you start, when DB2 is shut down, use AMS to rename the two BSDS copies and all active log datasets. This is for safety, in case the recovery goes wrong you can always put things back the way they were and start again. Create new empty BSDS and active log datasets. Now find the last archive log created before you did the first [...]
21076 37 28_Re: Automatic Rebind Problem0_19_Tim.Lowe@STPAUL.COM31_Wed, 20 Feb 2002 09:47:53 -0600290_us-ascii Myron, I remember the question and the answer that you are referring to at the 2000 IBM DB2 Tech conference. At the time, I thought the answer did not really address the question. But, it was not my question. Since I did not ask it, I don't know what they really had in mind. [...]
21114 14 36_Re: COMMIT/ROLLBACK commands in SP??9_Jim Ruddy18_jaruddy@US.IBM.COM31_Wed, 20 Feb 2002 10:46:09 -0600438_- DB2 for z/OS and OS/390 Version 7 does allow COMMIT and ROLLBACK in stored procedures which have not been invoked by CICS, IMS, or a Trigger.
Jim Ruddy DB2 for z/OS and OS/390 Development
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
21129 26 36_Re: COMMIT/ROLLBACK commands in SP??14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Wed, 20 Feb 2002 09:20:47 -0800613_iso-8859-1 NO
I use DB2 V5 (OS/390 V2.6) Cobol stored Procedures
Susan A (who realizes that things may be different on other versions and platforms
-----Original Message----- From: Ramakrishnan
Is it possible to COMMIT or ROLLBACK DB operations from inside a stored procedure?
I am working with SPs written in C language on DB2.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
21156 28 32_Re: DDF Threads in DB2 V5 OS/3900_20_Jones.H@GRAINGER.COM31_Wed, 20 Feb 2002 11:30:00 -0500541_- If you want to control dynamic queries, RLF is the way to go. There is sufficient documentation in the Admin guide. It's not an easy read, but it's all there. You can limit CPU via ASU time, limit by plan or userid. You can restrict parallelism.
Somebody asked about a REXX to deal with distributed queries at the userid level. If they are dynamic, use RLF. You can also set up a clist/rexx to display the threads, sending output to a file, then read the file and cancel the threads you are looking for. We do this in batch at [...]
21185 30 15_Re: DSN Command12_Jim Harrison17_jharrison@GMX.NET31_Wed, 20 Feb 2002 12:35:48 -0500377_us-ascii Just as a follow up on this topic - I did dig a little deeper and actually created a TSO environment from within a COBOL program and tried to invoke DSN from there. Nice try, but I got message IKJ56637I.
"You attempted to run a command, program, CLIST, or REXX exec from an authorized environment. This is not supported under the Dynamic TSO Environment." [...]
21216 31 26_SYSUT1 and Partition Reorg0_20_Jones.H@GRAINGER.COM31_Wed, 20 Feb 2002 11:52:00 -0500492_- Version 5 OS/390
Big (not gigantic) partitioned table. 170M rows - 60 partitions. 4 NPI's. REORG single partition THE QUESTION: What's SYSUT1 used for?
Clearly, the NPI's are not reorged. But my reorg crapped out because SYSUT1 couldn't find more than 15,000 cylinders.
There are 5 indexes altogether. Index length varies from 3 to 28 bytes. There are 3 million rows in the partition. No way all the keys and rids for this partition add up to over 1000 cylinders. [...]
21248 30 26_Re: DB2 SQL Analysis Tools13_Jim Wankowski23_jim.wankowski@QUEST.COM31_Wed, 20 Feb 2002 11:26:10 -0600577_- Hi John Quest Software offers a complete integrated suite of database administration tools for DB2 on AIX. Quest Central for DB2 currently includes database admin, SQL Tuning, space management, and performance monitoring. The monitoring component in our current release provides real- time instance monitoring/diagnostics information. We are currently in beta with our 2.0 release which has a 5th component added for SQL collection. This components allows you to collect SQL history into a DB2 database and run detailed performance analysis against the historical data. [...]
21279 81 28_Re: Automatic Rebind Problem12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 20 Feb 2002 11:26:32 -0800339_us-ascii You've got a good point about the parms. It should work consistently but doesn't. But getting IBM to change or even admit they're wrong will be a major issue. Consider that they still (even 18 months later) haven't fixed the problem that I asked about at the meeting. Still outstanding APARs on the problems addressed there. [...]
21361 18 27_Quick help on Platinum Docs8_K.Balaji19_K.Balaji@TARGET.COM31_Wed, 20 Feb 2002 13:55:02 -0600505_iso-8859-1 Hi all, I am looking for Platinum Documentation for load utility in Pdf format for Release 3.1. Reading from mainframe-bookmanager doesnt give me the picture details. Is there a site from where I can download these docs ? TIA Balaji Ph # (510) 727-3259
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
21380 85 30_Re: SYSUT1 and Partition Reorg0_19_csutfin@AMSOUTH.COM31_Wed, 20 Feb 2002 14:02:04 -0600402_us-ascii Hayden
The manuals state for REORG TABLESPACE PARTn that Data for part n + part n of the partitioned index + index entries for part n in all nonpartitioned indexes.
It is reorging the index entries for the partition in the NPI.
I have used TAPE as SYSUT1 even multi-volume tape. It just takes longer but it will work. Helps to send "treats" to your operations staff. [...]
21466 26 12_SYS1 VS SYS212_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Wed, 20 Feb 2002 14:41:41 -0500303_us-ascii Is anyone out here using 'SYS2' as a HLQ for the SDSNLOAD, SDXRRESL and SDSNLINK datasets in a runtime environment. We have always maintained that these are TYPE 1 code datasets and that they come from IBM that way, so why change them. Would appreciate all responses positive or negative. [...]
21493 70 17_What's in a name?12_Raymond Bell17_rbell@NZ1.IBM.COM31_Thu, 21 Feb 2002 09:31:52 +1300370_us-ascii Max (or is it MS now?), are you so strapped for storage you've had to compress your mail footer? I thought the U in UDS was Uncertified, then I worked out the NC bit. For what it's worth, I don't think you're U-less at all; more like U-ful.
HTH, ;o)
RB DDBA ICSE - DU V7 4 U, W+OS/2 ICSE - DU V7 4 OS/390 (but I sometimes wonder how) [...]
21564 66 20_Re: DB2 Log question10_James Kwan19_JamesDB2Kwan@CS.COM29_Wed, 20 Feb 2002 15:45:48 EST290_US-ASCII Don,
These insert will be recorded in log as well as the log entry for drop table command. If you recreated the table, these logs will not be 'available' to the newly created table. Some vendors have tools that can be used to extra those logs and applied to the table. [...]
21631 39 31_Re: Quick help on Platinum Docs13_Thomas, Janis19_Janis.Thomas@CA.COM31_Wed, 20 Feb 2002 15:56:10 -0500284_iso-8859-1 If you have a StarTCC client id on the CA online support site (esupport.ca.com), you can sign on & download the manuals in PDF format. Just select Fast Load for DB2 under the 'please select a product' drop down box. If you run into problems, email me off of the list. [...]
21671 150 16_Re: SYS1 VS SYS213_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 20 Feb 2002 13:06:51 -0800383_iso-8859-1 We use SYSDB here, at other shops I've seen other hlq's used. Everywhere I've ever worked, the "online" side of the house (DB2, CICS, IMS) preferred to use our own, non-SYS1 hlq's so the MVS folks would keep their mitts off our datasets. Of course, using the IBM-supplied dataset names can sometimes make life a bit easier, so if it works for you, more power to you. [...]
21822 143 35_IRLM MAXCSA: Follow-up ... Answer?!13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Wed, 20 Feb 2002 16:27:59 -0500374_US-ASCII Eric, List:
First, answers to Eric's questions.
Well, our environment DOES have it's share of *power* QMF users, some of whom frequently are responsible for the famous "DSNR035I -DST1 DSNRPBCW WARNING - UNCOMMITTED UR AFTER n CHECKPOINTS". However (drat!) none of them were running at the time.
We have disabled the SPUFI RR Plan/Package. [...]
21966 40 26_Re: DB2 SQL Analysis Tools9_Mike King17_mlking2@FEDEX.COM31_Wed, 20 Feb 2002 15:31:26 -0600649_us-ascii Does it work with DB2 on OS/390?
Jim Wankowski wrote:
> Hi John > Quest Software offers a complete integrated suite of database > administration tools for DB2 on AIX. Quest Central for DB2 currently > includes database admin, SQL Tuning, space management, and performance > monitoring. The monitoring component in our current release provides real- > time instance monitoring/diagnostics information. We are currently in beta > with our 2.0 release which has a 5th component added for SQL collection. > This components allows you to collect SQL history into a DB2 database and > run detailed performance analysis against [...]
22007 18 15_IN LIST limits?14_Jessen Michael27_JessenMichael@JOHNDEERE.COM31_Wed, 20 Feb 2002 15:46:56 -0600406_iso-8859-1 Can anyone tell me the limit for in lists on DB2 V6 for OS/390?
(ie: how many values can be included within a single in list?)
Thanks! Mike
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
22026 54 49_Problem:Stored procedure linking to CICS program.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 20 Feb 2002 17:09:28 -0500421_iso-8859-1 I am getting the following failure in DB2MSTR log when I link DB2 stored procedure to a CICS program. This work 4 or 5 time fine and then suddenly we get this error.
16.23.08 STC26518 DSN3201I :DB2T ABNORMAL EOT IN PROGRESS FOR
USER=DB2TWLM1 CONNECTION-ID=RRSAF CORRELATION-ID=
JOBNAME=DB2TWLM1 TCB=009BE0F8
16.23.08 STC26518 DSNX905I :DB2T DSNX9CAC PROCEDURE OR FUNCTION TEST [...]
22081 67 41_Re: VARCHAR columns & other stupid things14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 21 Feb 2002 08:03:05 +1000526_US-ASCII But you could make it a 32K tablespace, with a CHAR(17000) column. Only 1 row will fit into a 1 track, uncompressed, tablespace.
James Campbell
On 19 Feb 2002 at 14:11, Rob Crane wrote:
> Greg, > > Good point I blew that off. > > Maybe putting the object in RO status (-sta db(dbname) space(tsname) > access(ro)) after the one row is populated would be the best approach to > ensuring only 1 row would get into the table via DML. > > -Rob > > "Palgrave, Greg" wrote: > > > > Hi Rob, > > > > [...]
22149 42 16_Re: SYS1 VS SYS214_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 21 Feb 2002 08:03:07 +1000572_US-ASCII Stan
Yes. And if you are running the ADCD system they are supplied with, for example, DSN610 as the hlq.
What do you mean by "TYPE 1"?
James Campbell
On 20 Feb 2002 at 14:41, Stan Goodwin wrote:
> Is anyone out here using 'SYS2' as a HLQ for the SDSNLOAD, SDXRRESL and > SDSNLINK datasets in a runtime environment. We have always maintained that > these are TYPE 1 code datasets and that they come from IBM that way, so why > change them. Would appreciate all responses positive or negative. > > > Thanks. > > Stan Goodwin [...]
22192 51 36_Re: COMMIT/ROLLBACK commands in SP??14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 21 Feb 2002 08:03:08 +1000592_US-ASCII Sibi
Possibly (and I don't have access to the appropriate manuals to confirm), the syntax of savepoints is an SQL standard thingy - which has different defaults. As DB2 does not allow those defaults, you are required to override them.
James Campbell
On 20 Feb 2002 at 10:44, Philip, Sibimon wrote:
> You can use savepoints in OS/390 till Version DB2 6 to rollback, but commit > is only done after exiting the SP. In version 7 Db2, I think commit and > rollback is allowed. One thing I did not understand is about the syntax of > savepoints > [...]
22244 19 16_Re: SYS1 VS SYS226_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Wed, 20 Feb 2002 16:08:23 -0600447_- Definitely not. Have used DSNxvrm since DB/2 1.3. where x is ssid, v is version of db/2, r is release, and m is mod. We define separate catalogs for each one, makes clean-up and maint a lot smoother too....
The only exception is SYS1.SDSNLINK for the erly code and is a carry over from the time when LINKLST datasets had to be in MCAT. Edward(Ed) J. Finnell, III Big Honkin' M/F Mgr. www.ua.edu or bama.ua.edu/archives/ibm-main.html [...]
22264 35 19_Re: IN LIST limits?10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 21 Feb 2002 09:18:37 +1100402_us-ascii Mike,
There is no limit on no. of IN-List elements. U r limited only by the max. statement length, which is 32765 bytes.
Cheers, Kals -----Original Message----- From: Jessen Michael [SMTP:JessenMichael@JOHNDEERE.COM] Sent: Thursday, February 21, 2002 8:47 AM To: DB2-L@RYCI.COM Subject: IN LIST limits?
Can anyone tell me the limit for in lists on DB2 V6 for OS/390? [...]
22300 73 36_Re: COMMIT/ROLLBACK commands in SP??15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 20 Feb 2002 18:59:20 -0500714_- Thanks James.
I am looking at internet version 6 sql reference book.
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnsq0g3/6.87
If we do not specify the ON clause in program, then we get a syntax error.
Thanks..sibi
-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Wednesday, February 20, 2002 04:03 PM To: DB2-L@RYCI.COM Subject: Re: COMMIT/ROLLBACK commands in SP??
Sibi
Possibly (and I don't have access to the appropriate manuals to confirm), the syntax of savepoints is an SQL standard thingy - which has different defaults. As DB2 does not allow those defaults, you are required to override them. [...]
22374 43 21_Data Sharing question12_Chris Munson18_cmunson@US.IBM.COM31_Wed, 20 Feb 2002 16:30:19 -0800530_us-ascii Cathy, I see that you have gotten an answer to your dilemma. I just wanted to add a few comments. Another reason that we have seen multiple members on the same LPAR is due to virtual storage constraints. This is a very uncommon reason to go to data sharing but we have seen it. Virtual storage constraint relief will be arriving in a future release of DB2. Next there was a comment about IDENTIFY and how all requests would end up on the first member if 2 were on the same LPAR. This would be true only if the group [...]
22418 72 16_run away process7_Sam Cho20_samuel.cho@VYKOR.COM31_Wed, 20 Feb 2002 16:21:06 -0800739_iso-8859-1 I need some help with the run away process problem!
I am experiencing occasional run away processes. I have started the audit and I got following messages:
timestamp=2002-02-20-15.58.12.030193;category=CONTEXT;audit event=EXECUTE; event correlator=6; database=VYKOR;userid=vykor;authid=DB2INST1; application id=*LOCAL.db2inst1.020220235813;application name=db2jd; package schema=NULLID;package name=SQLL9D07; package section=13;
timestamp=2002-02-20-15.58.12.032950;category=CONTEXT;audit event=EXECUTE; event correlator=7; database=VYKOR;userid=vykor;authid=DB2INST1; application id=*LOCAL.db2inst1.020220235813;application name=db2jd; package schema=NULLID;package name=SQLL9D07; package section=13; [...]
22491 111 16_Re: CRCR problem13_LOMBARD,Shaun25_shaun.lombard@DEWR.GOV.AU31_Thu, 21 Feb 2002 12:08:11 +1100569_iso-8859-1 I agree with the recovery option which I hope is possible because in the original email it says "8-I didn't have any backup of directory or catalog."
Shaun
-----Original Message----- From: Mike Turner [mailto:Michael_Turner@COMPUSERVE.COM] Sent: Thursday, 21 February 2002 3:12 To: DB2-L@RYCI.COM Subject: Re: CRCR problem
Hi Leila
I think the only safe option you have is to perform disaster recovery as described in my previous email, using the last archive log from before the first CRCR as the base for recovery. [...]
22603 42 32_Re: DDF Threads in DB2 V5 OS/39012_John Piccoli18_jpiccoli@SPRINT.CA31_Wed, 20 Feb 2002 22:29:12 -0500556_us-ascii Hayden,
Would you be willing to share the Rexx code?
John
Jones.H@GRAINGER.COM wrote:
> If you want to control dynamic queries, RLF is the way to go. There is > sufficient > documentation in the Admin guide. It's not an easy read, but it's all there. > You > can limit CPU via ASU time, limit by plan or userid. You can restrict > parallelism. > > Somebody asked about a REXX to deal with distributed queries at the userid > level. > If they are dynamic, use RLF. You can also set up a clist/rexx to display the > [...]
22646 57 36_Re: COMMIT/ROLLBACK commands in SP??14_GARIMA_BILWANI23_GARIMA_BILWANI@INFY.COM31_Thu, 21 Feb 2002 09:24:29 +0530396_iso-8859-1 If you need to have commit at the end of sp you should mention COMMINT ON RETURN YES option in the SP create script itself !
The following SQL also works in SP
EXEC SQL COMMIT END-EXEC.
But Ramakrishnan Please note that if you commit then any cursor that you opened in your sp for returning the result set will also close and no result sets will be returned. [...]
22704 41 16_Re: CRCR problem20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM29_Wed, 20 Feb 2002 23:25:35 EST171_US-ASCII
Regards, Ed. DB2 Disaster Recovery Off-site and On-site Recovery Automated and Simplified http://www.recoveryknowledge.com
22746 41 16_Re: CRCR problem20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM29_Wed, 20 Feb 2002 23:28:04 EST283_US-ASCII Hi Leila, If you are interested in an automated process visit: Recovery Knowledge
Regards, Ed. DB2 Disaster Recovery Off-site and On-site Recovery Automated and Simplified http://www.recoveryknowledge.com
22788 81 53_Re: Problem:Stored procedure linking to CICS program.14_GARIMA_BILWANI23_GARIMA_BILWANI@INFY.COM31_Thu, 21 Feb 2002 10:50:04 +0530299_iso-8859-1 Till version 6.1 DB2 does not support SP's being called from a CICS transaction. An alternative to this can be to call a COBOL program from a cics transaction which inturn will call a SP. Since cobol programs can very simply & definitely call SP's which we have already implemented. [...]
22870 99 25_Re: Data Sharing question14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Wed, 20 Feb 2002 21:23:17 -0800484_iso-8859-1
-----Original Message----- From: Chris Munson [mailto:cmunson@US.IBM.COM] Sent: Wednesday, February 20, 2002 6:30 PM To: DB2-L@RYCI.COM Subject: [DB2-L] Data Sharing question
Next there was a comment about IDENTIFY and how all requests would end up on the first member if 2 were on the same LPAR. This would be true only if the group attach name was being used. Chris Munson DB2 Development, IBM Silicon Valley Laboratory [...]
22970 19 59_Derez D. Lusk/Household International is out of the office.13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Thu, 21 Feb 2002 01:22:29 -0600541_us-ascii I will be out of the office starting 02/20/2002 and will not return until 02/25/2002.
I will be out of the office today . please call 847-824-2871 and leave detailed message or via HOUSEMAIL - page EMPZ59 Any critical issues Please notify Mars Primary Support HTSMARS - via Housemail
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
22990 16 45_Paolo Bruni/Almaden/IBM is out of the office.11_Paolo Bruni17_pbruni@US.IBM.COM31_Thu, 21 Feb 2002 00:46:01 -0700478_us-ascii I will be out of the office starting February 20, 2002 and will not return until March 1, 2002.
I will check my mail only occasionally. For personal matters contact Patrick Vabre, for DB2 projects contact Bart Steegmans.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
23007 37 47_Rectify mistakes if any for SRTPOOL estimation.17_jennifer jennifer22_jenni_jeni@HOTMAIL.COM31_Thu, 21 Feb 2002 08:13:46 +0000535_- Hi All experts , first of all beg sorry for not saying thanks to each questions I posted earlier due to lack of time and I do repent.Here is the need for me to explore before estimations.Hope you would all impart and rectify my mistakes. I have so many sql statements and when I captured everything for sort pool estimation per thread I have to take care of ORDER by , GROUP BY and DISTINCT. I feel after estimationg I have to change the DSNTEJUZ SRTPOOL parameter. If I m wrong please rectify me . Here for estimating I should [...]
23045 46 35_Re: What's your IRLM MAXCSA set to?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Feb 2002 09:20:25 +0100544_us-ascii Hi Manas
Even if I'm not a S390 sysprog I actively supported the migration from S390 2.5 to 2.10 (now I'm fighting with WLM to try to start WLM-established Stored Procedures - of course I'm losing) and what we noted was that many of S390 system parameters that worked well with version 2.5 were absolutely inadequate in version 2.10, in primis MIH (missing handler interrupts), we had many 'strange' errors using 3rd party toolz (mainly CA, even IBM utilities) that disappeared simply modifying (upwards) these parameters. [...]
23092 32 12_EXPLAIN INFO20_desiz db2 consultant20_desig@CONSULTANT.COM31_Thu, 21 Feb 2002 03:29:12 -0500594_iso-8859-1 Good Day all.Could any one help me out regarding the doubts I do have for EXPLAIN information. If Terry gets a chance to see this please issue as much as comments as possible. I learnt a lot after getting so many suggestions and thought form you all.
When I took explain information I got the folloing informations. QUERYBOLCK NO IS MISSING LIKE THE QUERYBLOCKNO IS FROM 1 TO 11 BUT ONLY i GET IS 1,2,3,4,8,9,10,11. FOR QUERYBLOCKNO 8,9,10,11 I GOT THE ACCESSTYPE AS R TABLE_TYPE AS Q WHICH MEANS THE INTERMEDIATE RESULT SET.FOR THE SAME QUERYBLOCKS I GOT THE SORTN_JOIN [...]
23125 17 16_04E000 hex error12_sushant dash23_dash_dba@REDIFFMAIL.COM31_Thu, 21 Feb 2002 08:38:24 -0000319_iso-8859-1 Hi All I got a typical error which is not availbale in manual messages also. I searched the list and found a question but with no answer for it. Has any body come across this kind of error in the past . I request him to share the mesures to resolve this kind of error. Thanking you all in advance. Dash [...]
23143 104 30_Re: SYSUT1 and Partition Reorg0_18_mebert@AMADEUS.NET31_Thu, 21 Feb 2002 09:49:20 +0100470_us-ascii Can you give us the Job statements, actual REORG SYSIN statement, and the job output (SYSPRINT, JESMSG...)? I use SORTKEYS, and never had a problem with this type of REORG.
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
Version 5 OS/390
Big (not gigantic) partitioned table. 170M rows - 60 partitions. 4 NPI's. REORG single partition THE QUESTION: What's SYSUT1 used for? [...]
23248 38 16_Re: SYS1 VS SYS210_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Feb 2002 09:59:49 +0100586_us-ascii Hi Stan
Of course 'It depends' but as far as I'm concerned I'd (try to ) avoid to use any IBM's standard naming, in my (personal) opinion 'one size fit all' doesn't work well and you must change HLQs to 'distinguish' your company's environment and products. I saw that in the long run this (personal experience, YMMV) it's the best choice and you can avoid some potential problems and helps other persons that do not work as sysprog for instance (even your CEO can easily understand that DB2PROD.SDSNLOAD is a library that deals with something called 'DB2' - if he [...]
23287 64 16_SV: SYS1 VS SYS212_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Thu, 21 Feb 2002 10:14:43 +0100431_iso-8859-1 I think the trouble is liklist and authorization, only you take care of that part the HLQ doesn't matter. Only SDSNLINK need to be on linklist (because it is early code), SDSNLOAD and ..RESL can also be STEPLIBbed (we put these two in the same library). Regards Seppo Nurmi Sveriges Riksbank SE-10337 Stockholm, Sweden Tel: +46 8 787 07 31 Fax: + 46 8 21 05 31 Mob: + 46 70 344 38 84 Email: seppo.nurmi@riksbank.se [...]
23352 30 18_collating sequence11_Daniel Adam16_dadam@GRATEX.COM31_Thu, 21 Feb 2002 03:17:44 -0600453_- Hi, This is DB2 v7r1 on OS/390 v2r10 and v2r8. DB2 is using EBCDIC codepage 870.
I noticed that selecting character column from a table using order by clause will not list the names in correct sequence. Our national characters have smaller weight than 'normal' characters (a-z, A-Z) and therefore will be listed before them. How can we modify sorting/collating sequence ? We need to achieve a sorting sequence that is used in our country. [...]
23383 229 25_Problem with replication!16_Anand Sundaresan25_Anand.Sundaresan@UBSW.COM31_Thu, 21 Feb 2002 09:47:25 +0000683_US-ASCII Hi all, We have db2 6.2 running on sun solaris and we try to set up replication between two servers. As it may have been expected it doesn't work and we are at a loss so any help would be highly appreciated. Firstly the output from the capture programs trace file. I had a look and everything seems to work ok up until the data below: ************************************************************************ ****** going to LOCK TABLE "DB2INST1"."CD20020219276380" SQLSTR = LOCK TABLE"DB2INST1"."CD20020219276380" IN EXCLUSIVE MODE SQLCODE 0 retry_limit 100 retry_ct_op 0 msgq_key mkey is 1224750758 before calling msgq_init retcode is 0 msgq_init msg_flag is 000003b6 [...]
23613 27 16_Re: CRCR problem11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Thu, 21 Feb 2002 04:54:48 -0500582_ISO-8859-1 Oops!!
Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk
Message text written by DB2 Data Base Discussion List >I agree with the recovery option which I hope is possible because in the original email it says=20 "8-I didn't have any backup of directory or catalog."
Shaun <
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
23641 164 32_Re: collating sequence / Unicode14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 21 Feb 2002 10:22:16 -0000437_iso-8859-1 This is one of the problems (I think) that Unicode is supposed to solve.
As you are on V7, you could try creating the table to store UNICODE rather than EBCDIC data
Supplementary question - Has ANYONE done ANYTHING with Unicode (for real) yet???
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...]
23806 23 20_Re: 04E000 hex error13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 21 Feb 2002 06:56:22 -0500597_iso-8859-1 Hello Dash,
This is a common abend for DB2.
It should be accompanied by an 8-character reason code. You need to find the reason code and look that up in the Msgs&Codes manual.
That should help identify and resolve the problem.
David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@compuware.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
23830 87 32_Re: collating sequence / Unicode11_Daniel Adam16_dadam@GRATEX.COM31_Thu, 21 Feb 2002 05:25:02 -0600536_- Hi Phil, thanks for the idea. We wanted to use the Unicode for this project. The reason why we chosed not to use it was that with the timeframe we have we cannot afford the resources for doing the 'first climb'. No unicode samples, very little information in DB2 manuals, no real experience with writing DB2 apps/stored procs using Unicode. Plus, information from IBM support indicated that DB2 is doing character conversion from Unicode to EBCDIC (and back) even when an unicode DRDA application is working with unicode tables. [...]
23918 48 22_Re: collating sequence14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 21 Feb 2002 22:58:52 +1000614_US-ASCII Daniel
Write a UDF to convert characters from 870 so that the converted characters are in your desired collating sequence, then
SELECT CONVERTED_STRING(dbcolumn), dbcolumn .... ORDER BY 1
On 21 Feb 2002 at 3:17, Daniel Adam wrote:
> Hi, > This is DB2 v7r1 on OS/390 v2r10 and v2r8. > DB2 is using EBCDIC codepage 870. > > I noticed that selecting character column from a table using order by > clause will not list the names in correct sequence. > Our national characters have smaller weight than 'normal' characters (a-z, > A-Z) and therefore will be listed before them. [...]
23967 117 22_Re: collating sequence14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 21 Feb 2002 13:16:18 -0000406_iso-8859-1 Problem with this approach is that all ORDER BY and GROUP BY clauses will cause sorts - indexes will NEVER be able to satisfy ordering..
If there aren't too many rows in the results set, this may not be an issue but .....
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...]
24085 73 22_Re: collating sequence14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Thu, 21 Feb 2002 14:18:12 +0100
24159 79 55_Re: What should I do about a -904 reason code 00D70014?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 21 Feb 2002 23:19:50 +1000535_US-ASCII Leslie,
Thanks for the rexx and here are our figures. James
DB2P (main production DB2 system)
tot 4k vp pages = 26300 tot 8k vp pages = 0 tot 16k vp pages = 0 tot 32k vp pages = 12 tot 4k hp pages = 0 tot 8k hp pages = 0 tot 16k hp pages = 0 tot 32k hp pages = 0 Max Keep Dynamic = 5000 Total threadstorage = 600.00Mb edmpool = 5.00Mb (Global stmt cache in here.) dynstmtcache = 118.16Mb (Max Local stmt cache) opendatasets = 2.93Mb compressdict = 8.75Mb (assume 64kb dicts) maxrblk = 4.28Mb [...]
24239 55 17_SQL Analysis Tool12_Phil Gunning17_db2jock@YAHOO.COM31_Thu, 21 Feb 2002 05:28:52 -0800416_us-ascii John, We just completed an extensive tuning of a DB2 V7.2 database running Siebel. Using one our tools, SQL GUY, we were able to reduce the cpu cost of the worst query by orders of magnitude. We were able to reduce the elapsed time for inserts by half, and reduce the elapsed time of a workload from 30 secs to .03 seconds. More information is available at http://www.breakthroughdb2.com. Cheers! Phil [...]
24295 73 36_V6 to V7 CATMAINT: Quiesce Activity?13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Thu, 21 Feb 2002 08:32:16 -0500448_US-ASCII List,
We are DB2 V6 for z/OS and OS/390, four-way data sharing. We're preparing to migrate our DB2 V6 for z/OS production subsystems to V7. The Install Guide notes, in the CATMAINT step, contains no warnings or recommendations regarding subsystem activity during this step.
Question #1: Did any of you who have done a V6 -> V7 migration either "quiesce" DB2 subsystem activity or bring other subsystems down altogether? [...]
24369 30 8_SQL1403N18_Eva Molina Jimenez20_evamolina@ES.IBM.COM31_Thu, 21 Feb 2002 14:42:07 +0100622_iso-8859-1 Hi List:
When I try to connect to my database, I get this error:
SQL1403N The username and/or password supplied is incorrect. SQLSTATE=08004
I'm running DB2 V7.1 on AIX 4.3.3.
TIA
Saludos/ Regards --------------------------------------------------- Eva Molina Jiménez. Automation/Tivoli. IBM Global Services. SPAIN Tlf: +34 91.397.94.18
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
24400 24 28_Opposite of an Inner Join!!!11_Dimoka Popy24_dimokap@INTERAMERICAN.GR31_Thu, 21 Feb 2002 16:17:53 +0200569_- Hello List!
Is there a way to achieve the opposite result of an inner join??? There is the alternative of coding :
select * from tb1 where c1 not in (select c1 from tb2)
but is there is something else more straightforward,maybe like a "Not inner join"?????!!!!!??????!!!!!!
TIA,
Popy Dimoka
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
24425 17 16_Re: SYS1 VS SYS20_17_JTonchick@AOL.COM29_Thu, 21 Feb 2002 10:02:14 EST420_US-ASCII In our shop only SDSNLINK uses SYS1 as a highlevel. All other DB2 libraries use the subsystem id as the highlevel, i.e. (DB2P.SDSNLOAD).
Jim Tonchick Fiserv, Inc.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
24443 21 40_Column function and indexable predicates14_Andrew Meddick27_andrew.meddick@MARRIOTT.COM31_Thu, 21 Feb 2002 09:00:04 -0600301_- When does a column function get applied to an indexable predicate?
During index access? During data access? On the returned rows?
The column functions are SUM on 1 column only and also a SUM on 2 columns with a '*' of the 2 columns.
This is with an inner join on 2 tables. [...]
24465 64 32_Re: Opposite of an Inner Join!!!0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Feb 2002 09:12:35 -0600458_us-ascii Popy, I don't think that this is the "opposite" of an inner join, but the result is the same : select * from tb1 outer join tb2 on tb1.c1 = tb2.c1 where tb2.c1 is null
But, performance is another issue entirely.
I hope this helps.
Thanks, Tim
Dimoka Popy cc: Sent by: DB2 Subject: Opposite of an Inner Join!!! Data Base Discussion List [...]
24530 41 32_Re: Opposite of an Inner Join!!!9_Ben Relle27_Ben.Relle@MORGANSTANLEY.COM31_Thu, 21 Feb 2002 15:14:26 +0000550_us-ascii Yep, but not pretty.
If tb1 has columns c1, and tb2 has columns c1 and c2
You can do a outer join on c1 and include a predicate on t2 testing for when c2 is null
thanks,
ben
Dimoka Popy wrote:
> Hello List! > > Is there a way to achieve the opposite result of an inner join??? > There is the alternative of coding : > > select * from tb1 where c1 not in (select c1 from tb2) > > but is there is something else more straightforward,maybe like a "Not inner > join"?????!!!!!??????!!!!!! > > TIA, [...]
24572 52 32_Re: Opposite of an Inner Join!!!12_McKown, John22_JMckown@HEALTHAXIS.COM31_Thu, 21 Feb 2002 09:17:17 -0600130_iso-8859-1 How about:
SELECT * FROM TB1 WHERE NOT EXISTS (SELECT C1 FROM TB2 WHERE TB2.C1=TB1.C1) ;
24625 140 32_Re: Opposite of an Inner Join!!!14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 21 Feb 2002 15:19:08 -0000379_iso-8859-1 Hi Popy,
Providing your SQL really IS as simple as your example and provided the select in the brackets does not return too large a result set, then what you have is probably fine the way it is!
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...]
24766 130 30_Re: SYSUT1 and Partition Reorg0_20_Jones.H@GRAINGER.COM31_Thu, 21 Feb 2002 09:54:00 -0500331_- Carol, The manual is a little sketchy on this. But the data goes to SYSREC rather than SYSUT1. The manual says to size SYSUT1 as
#rows in the table x #indexes x length-of-largest-key
For this table, that's about 30,000 cylinders. If that's true, then that explains why my SYSUT1 got a b37 at 15,000 cylinders. [...]
24897 12 26_Re: DB2 SQL Analysis Tools13_Jim Wankowski23_jim.wankowski@QUEST.COM31_Thu, 21 Feb 2002 09:44:49 -0600320_- Yes The beta release also has full OS/390 support for the 4 main components.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
24910 89 53_Re: Problem:Stored procedure linking to CICS program.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 21 Feb 2002 11:49:06 -0500608_iso-8859-1 DB2 stored procedure is calling a CICS program. And also we are on Version 6.1.
-----Original Message----- From: GARIMA_BILWANI [mailto:GARIMA_BILWANI@INFY.COM] Sent: Wednesday, February 20, 2002 11:20 PM To: DB2-L@RYCI.COM Subject: Re: Problem:Stored procedure linking to CICS program.
Till version 6.1 DB2 does not support SP's being called from a CICS transaction. An alternative to this can be to call a COBOL program from a cics transaction which inturn will call a SP. Since cobol programs can very simply & definitely call SP's which we have already implemented. [...]
25000 163 32_Re: DDF Threads in DB2 V5 OS/3900_20_Jones.H@GRAINGER.COM31_Thu, 21 Feb 2002 10:25:00 -0500820_- John, Here's the jcl and rexx code. The jcl has been "deproductionalized".
Hayden
//* //************************************************************* //* //* STEP ICJOB102 - DISPLAY ACTIVE REMOTE THREADS AND SAVE //* //* //************************************************************* //* //ICJOB102 EXEC PGM=IKJEFT1B,DYNAMNBR=20 //SYSTSIN DD * DSN SYSTEM(D2P3) -DIS THREAD(SERVER) END //SYSIN DD DUMMY //SYSTSPRT DD DSN=THREADDETAILDSN,DISP=OLD //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //* //************************************************************* //* //* STEP ICJOB103 - BUILD CONTROL CARDS TO 'KILL REMOTE THREADS' //* JOHN, THIS CODE WRITES THE DSN COMMAND WITH THE //* KILL THREAD COMMANDS TO THE KILLTREADCOMMANDDSN //* USED IN THE NEXT STEP. IF I WERE TO DO THIS AGAIN //* I WOULD [...]
25164 64 19_Re: IN LIST limits?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 21 Feb 2002 11:59:06 -0600303_us-ascii Although Kals is right that there is no set limit (that I am aware of), if the IN list is large, then you can receive a -101 (statement is too long or too complex) even though the statement is within the 32K limit.
APAR PQ45163 resolves this issue, but it's a V7 fix unfortunately. [...]
25229 102 16_Re: EXPLAIN INFO13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 21 Feb 2002 11:59:04 -0600358_iso-8859-1 Desiz,
You have good reason to have doubts with explain, especially since V7.
I must admit that I rarely use explain or visual explain (or any other tool) for anything other than knowing what indexes were used and what join methods are used. The rest I can ascertain from the way the query is written and the available indexes. [...]
25332 72 20_Rexx Select Question0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 21 Feb 2002 12:06:58 -0600337_us-ascii Hello, OS/390 2.10, DB2 6.1 here,
Can anyone see why I'm getting a -204, "undefined name" error on the following in my Rexx to DB2? I adapted another Rexx procedure I have which successfully executes a DB2 stored procedure with return values, so I think the connect stuff is all fine. Here's the crux of the matter: [...]
25405 66 44_Re: Column function and indexable predicates13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 21 Feb 2002 12:14:19 -0600405_iso-8859-1 Andrew,
The column function evaluation of the plan table will tell you whether is was applied during retrieval ('R'), during/after sort ('S'), or at stage 2 without sort (blank).
At data retrieval (data refers to either index or table data) can only occur if there is no sort required for grouping, and also if value within the parentheses is a column and not an expression. [...]
25472 62 32_Re: Opposite of an Inner Join!!!13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 21 Feb 2002 12:14:21 -0600359_iso-8859-1 Popy,
I would have thought that the opposite of an inner join would mean returning the exclusive rows from both tables, since an inner join returns the inclusive rows. Although this is not what you show in your example.
Therefore:
SELECT A.*, B.* FROM T1 FULL JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C1 IS NULL OR T2.C1 IS NULL [...]
25535 14 24_Re: Rexx Select Question0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 21 Feb 2002 12:19:50 -0600319_us-ascii Duh -- I forgot to qualify the table name. It works nows.
Stg
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
25550 225 54_Re: DB2 Abnormal Termination with Reason Code 00E5070515_Hagedorn, Linda20_lindah@EPOCRATES.COM31_Thu, 21 Feb 2002 11:12:48 -0800554_iso-8859-1 Remember to check the console log also. The prior error may be external, DASD related or some such.
-----Original Message----- From: Hayden, Lee [mailto:Lee_Hayden@CSX.COM] Sent: Tuesday, February 19, 2002 6:04 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Abnormal Termination with Reason Code 00E50705
Usually the 04F indicates a previous error. Look for errors in other DB2 address spaces. Also check SYS1.LOGREC "EREP" reports. This will usually show the sequence of events and will help determine the "root" cause.... [...]
25776 19 12_ODBA and IMS12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Thu, 21 Feb 2002 13:30:23 -0600391_us-ascii I currently am testing ODBA stored procedures in a WLM stored procedure address space. We have the AIB interface area defined in the procedure. Our problem comes when we call the procedure we knock down IMS. We added the current IMS610.RESLIB as a steplib in the test stored procedure WLM address space and we have added the DFSRESLB to this address space as well. I am stuck. [...]
25796 96 64_Re: March 14th - Central PA DB2 User Group Agenda - Registration15_Blaney, Bill L.21_Bill.L.Blaney@SSA.GOV31_Thu, 21 Feb 2002 14:31:56 -0500430_iso-8859-1 I can't remember whether or not I've already RSVP'ed. (Must be oldtimers disease acting up again!). Anyway, please list me as an attendee.
Thanks,
Bill Blaney (was Compuware; now: CPSI)
-----Original Message----- From: Cathy Peck [mailto:cathy.peck@HIGHMARK.COM] Sent: Monday, February 11, 2002 12:30 PM To: DB2-L@RYCI.COM Subject: March 14th - Central PA DB2 User Group Agenda - Registration [...]
25893 16 30_Re: SYSUT1 and Partition Reorg9_Jim Ruddy18_jaruddy@US.IBM.COM31_Thu, 21 Feb 2002 13:30:31 -0600325_- SYSUT1 is used to contain the keys for the part of the partitioning index and the keys for the logical partiitons of all the NPIs. When you REORG a partition, the partitioning index part gets rebuilt and the NPIs get updated with the new position of the data rows.
Jim Ruddy DB2 for z/OS and OS/390 Development [...]
25910 15 39_Stored procedure calling a CAF program?12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 21 Feb 2002 14:00:00 -0500503_- We have an existing program which uses CAF. We would like to call this program via a stored procedure. Anyone know if this will work? I'm thinking that there will be attach errors since we are already connected to a Database.
Thanks in advance.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
25926 23 60_Re: Could some one play DB2 Parallelism Detective with me? I12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 16:41:12 -0600341_- Linda,
I think the summary in the Administration Guide is still pretty good, beginning with page 5-294 in my V5 paper copy, or header Parallel Operations and Query Performance. It's about 14 pages, and I'd use the latest pdf to get the most recent changes. There is a big table about when parallelism is not used (page 5-302). [...]
25950 18 26_Re: DB2 Certifications ...12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 16:52:04 -0600597_- I think this web page is the best one for certification test information, books, classes, ... Certification is often part of the conference for IDUG, DB2 Technical Conferences, ...
http://www.ibm.com/software/data/db2/skills/cert.html
Roger Miller, DB2 for z/OS Have card saying I'm certified or certifiable, since I helped write the test.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
25969 27 20_Re: 04E000 hex error12_sushant dash23_dash_dba@REDIFFMAIL.COM31_Fri, 22 Feb 2002 00:03:34 -0000519_iso-8859-1 Good Day David/James thanks for helping me and the message I get is like this Abend 04E000 hex occured processing the command 'DSN '. I m looking at the system and checking if any other mesage code is there. I would definitely pass it to you all. Thanks in advance. Dash On Thu, 21 Feb 2002 Seibert, Dave wrote : > Hello Dash, > > This is a common abend for DB2. > > It should be accompanied by an 8-character reason code. > You need to find the reason code and look that up in > the Msgs&Codes manual. [...]
25997 32 45_Stored Procedure Builder and the gcc Compiler15_Stephen Poulsen36_stephen.r.poulsen@CO.MULTNOMAH.OR.US31_Thu, 21 Feb 2002 17:54:19 -0600423_ISO-8859-1 We are currently in “development only” mode with DB2/UDB on Unix. Our developers reported getting a SQL7032N error using the Stored Procedure Builder tool (we didn’t have a C compiler installed on the database server!). To make a long story short, we installed the “gcc” compiler and now we are trying to set up the environment to use this compiler. Any help you might provide would be greatly appreciated: [...]
26030 32 31_Re: DB2 I/O and CPU Parallelism12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 17:59:37 -0600498_- I don't qualify as a real user, but I talk to quite a few. Here is the list of problems that I remember. I looked on our Support page, querying for parallelism and got 22 hits. Customer experiences differ greatly.
1. Having resources needed to improve response time. http://www-1.ibm.com/support/manager.wss?rs=64&rt=0&org=SW&doc=1030793
2. Being current on service and Versions helps a lot. Read II12836. http://www-1.ibm.com/support/manager.wss?rs=64&rt=0&org=SW&doc=1030852 [...]
26063 16 20_Re: DB2 Log question12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 18:05:46 -0600334_- IBM DB2 Object Restore Tool is an affordable, robust tool that enables you to recover valuable data assets by quickly restoring dropped objects without downtime, even if they no longer exist in the DB2 catalog. Such dropped objects may include databases, tablespaces, tables, indexes and data, as well as table authorizations. [...]
26080 15 21_Re: DB2 Unload Copies12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 18:46:57 -0600502_- If you are running DFSORT, you can use the COPY option to have multiple copies per step. ICEGENER tends to be faster. If that does not work, you could use a clist or REXX to allocate the data sets and then invoke the *GENER.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
26096 19 27_Re: DB2 V6 and JDBC Install12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 18:59:04 -0600358_- For APARs, you will probably want the changes implemented in V5 PQ51845 V6 PQ51846 V7 PQ51847
For V7 documentation, the JDBC install was folded into the V7 Program Directory and Installation Guide for the SMP/E part, then in Chapter 6 of the Application Programming Guide and Reference for Java, dsnjvh11.pdf.
Roger Miller, DB2 for z/OS [...]
26116 19 18_Re: DB2 V6 Support12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 19:09:29 -0600470_- Did you notice that the availability summary points to a similar chart for DB2 UNIX & Windows?
For V6, the end of service date is not announced, and that's the only way we make formal commitments. We'll stop marketing new V6's soon (June). For a guess, V6 will probably be supported through the end of 2003, maybe a little more. The next question might be the ability to skip over V7 to vNext, and the answer is almost certainly no. Plan to migrate to V7. [...]
26136 14 15_Re: DSN Command12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 19:17:59 -0600493_- I'm pretty sure that the DSN command is not designed to run APF authorized. The TSO TMP IKJEFT02 is APF authorized. As I read that message, it seems to be saying that the environment is authorized and should not be.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
26151 21 13_Re: ESS & DB212_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 19:37:38 -0600420_- We also put more recent performance information into the more recent Redbooks:
V6 Technical Update, SG24-6108 V7 Performance Topics, SG24-6129
We will have a little more about working with FICON channels in the next Redpaper. There is a white paper about DB2 working with FICON. There were several presentations about DB2 and ESS at conferences, probably including Share, so they can be downloaded. [...]
26173 23 25_Re: IBM to ease DB2 admin12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 20:05:36 -0600428_- Warning: my opinion is more biased than usual. I'll try to stay with current information.
Real Time Statistics delivered on DB2 for z/OS and OS/390 V7. The easiest way to get the information is to look at appendix G and H of the V7 Administration Guide. There will be more in an upcoming Redpaper. There was a presentation at the DB2 Technical Conference. There will be a presentation at the next Share - March 4. [...]
26197 23 49_'An Introduction to DB2 for OS/390' in PDF format12_Raymond Bell17_rbell@NZ1.IBM.COM31_Fri, 22 Feb 2002 15:38:29 +1300391_us-ascii 'Guys',
Just going throught the 'what's new' PDF for DB2 for OS/390 V7 and saw mention of the above book. Several times people have asked me for a good intro book to DB2 and I haven't really been able to give them a good answer. Anyway, I've found it in BOO and HTML format but I'm quite keen on a PDF version. Anyone know if IBM do one and, if so, where I can find it? [...]
26221 16 53_Re: Overhead of using SYSIBM.SYSDUMMY1 for DB2 OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 20:33:09 -0600326_- If you have a long accounting report we'll know from the class 3 wait times. Until then we can guess. SYSDUMMY1 is a real table, but those few pages will fit into storage and probably stay there. Did an address space need to start for the stored procedure? Are we opening data sets? How about extend or formatting? ... [...]
26238 18 51_Re: Tool for simulating transactions in a subsystem12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 21:03:09 -0600464_- There are some other products on the market, such as
TPNS http://www-3.ibm.com/software/network/tpns/
LoadRunner http://www-svca.mercuryinteractive.com/products/loadrunner/
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
26257 15 40_Re: V6 to V7 CATMAINT: Quiesce Activity?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 21:06:35 -0600479_- I think that just about everyone is quiescing. We have enough evidence that there are some abends, and DB2 rollback works.
I'd like to hear about any experiences to the contrary on either point.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
26273 17 35_Re: What's your IRLM MAXCSA set to?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 21:18:30 -0600439_- I've been working on some very large situations, where the IRLM storage needed to be larger than 100 MB. We've had some customers switch over to PC=YES to help the 2 GB limit. Do you have a number of long-running tasks with lots of locks? I like to compare the CTHREAD times NUMLKUS numbers times 250 bytes per lock to see what could happen. For data sharing, the CF lock sizing generally relates to the lock space for each member. [...]
26291 27 55_Re: triggers and stored procedures for db2 v6 for OS39012_Roger Miller19_millerrl@US.IBM.COM31_Thu, 21 Feb 2002 21:35:26 -0600452_- We spent quite a bit of time working on referential integrity back as far as V2R1. For referential integrity, that is likely to be simpler and perform better. If you want "sort of referential integrity" or more general integrity, then triggers make more sense.
We've seen some cases of triggers used for data propagation, and recommend data replication solutions first. Triggers can be used where the need is simple and relatively light. [...]