1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2006, week 1
2 80 27_Betr.: [DB2-L] SQL question17_Marc van der Valk34_mw.van.der.valk@BELASTINGDIENST.NL30_Wed, 1 Nov 2006 07:38:11 +0100525_utf-8 I think you're looking for this:
WHERE SUBSTR(veh_id_num, 4, 1) = 'S'
where 4 is the starting position and 1 is the length
Regards,
Marc
"Sniatecki, Jim" Aan Verzonden door: DB2-L@WWW.IDUGDB2-L.ORG "DB2 Data Base Cc Discussion List" [DB2-L] SQL question
31-10-2006 23:06
Antwoord a.u.b. aan "DB2 Database Discussion list at IDUG" [...]
83 168 31_Re: Betr.: [DB2-L] SQL question13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Wed, 1 Nov 2006 07:59:39 -0600455_us-ascii Just in case your example was just an example and not completely reflective of your requirements -- I believe the DB2 POSSTR function is pretty close to the Oracle INSTR function you mentioned.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Marc van der Valk Sent: Wednesday, November 01, 2006 12:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Betr.: [DB2-L] SQL question [...]
252 22 48_ENFM - NFM limitations for application migration17_McElhaney, Robert32_robert.mcelhaney@TWC.STATE.TX.US30_Wed, 1 Nov 2006 07:55:13 -0600318_- Greetings DB2-L listers:
We are planning migration to new function mode. We are now at compatibility mode on all of our DB2 systems. My question is this. Are there any limitations on migrating applications across datasharing groups, when the source group is in ENFM or NFM and the target group is in CM? [...]
275 90 40_Re: Exit Cleanly from a Stored Procedure13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Wed, 1 Nov 2006 08:12:10 -0600562_us-ascii This is exactly the reason that I've never been a big fan of having stored procedures stop on the first error. I know the arguments in favor of this practice and in a perfect world you always code for the exception conditions, but in real life there's always some that get by, and having the stored procedure stop after the first error can be devastating. My opinion is exactly what you mentioned -- make it so the SP does not actually stop. Under V8 this is controlled by the "STOP AFTER nn failures" (or "Continue after failure") setting on each [...]
366 85 29_Re: Archiving Historical Data12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM30_Wed, 1 Nov 2006 08:13:44 -0600397_us-ascii What is the DASD team's objection ? For years now we have routinely let
HSM automatically migrate DB2 linear VSAM files to tape after they have
not been accessed for more than 120 days. If a query comes along, the
datasets are then recalled from tape automatically. Note: if you are
periodically touching them with RUNSTATS or Image Copy, they will never [...]
452 43 25_z/OS DB2 V8 upgrade notes14_Paul Peplinski24_paul.peplinski@WPSIC.COM30_Wed, 1 Nov 2006 12:40:55 -0600336_ISO-8859-1 We are behind many of you but thought I'd share experiences for those starting a V8 upgrade. All in all ours was smooth but there were a couple gotchyas.
Non-Data Sharing DB2
SMP/E install was pretty clean, needed to apply UK14072 for a CATMAINT update issue and UK15266 for an ifcid247 trace record S0C4. [...]
496 23 17_Refresh immediate19_Ayalew "Mark" Kassa20_ayalakassa@YAHOO.COM30_Wed, 1 Nov 2006 13:25:53 -0600464_- Hi Lists,
I am having conflicting information on refresh immediate & refresh deferred parameters of MQTs. At some article I read, I have a choice to use either refresh deferred or refresh immediate.
However, the red book says it is mandatory to specify DATA INITIALLY DEFERRED followed by REFRESH DEFERRED when creating MQTs in DB2 v8. If the information in the red book is true, why do we need to mention those parameters when we create MQT? [...]
520 146 101_Fwd: FW: REMINDER: the next NEDB2UG meeting, Thursday November 9, 2006 - Publick House, Sturbridge MA7_Ed Long19_rdhm99a@PRODIGY.NET30_Wed, 1 Nov 2006 12:36:52 -0800374_iso-8859-1 Good seats still available; but they are going fast. Call now to hear who our mystery guest will be.
"Long, Edward" wrote: Subject: FW: REMINDER: the next NEDB2UG meeting, Thursday November 9, 2006 - Publick House, Sturbridge MA Date: Wed, 1 Nov 2006 15:18:41 -0500 From: "Long, Edward" To: [...]
667 18 39_z/OS DB2 V8 - DSNPC or integrated COBOL14_Paul Peplinski24_paul.peplinski@WPSIC.COM30_Wed, 1 Nov 2006 14:57:49 -0600371_- Listers:
We are looking at activating DB2 V8 precompiler. One thought is to use the integrated compiler of IBM Enterprise COBOL for z/OS and OS/390 3.2.0. Any comments from a DB2 perspective, especially if we do the same for CICS? I recall the integrated compiler was notorious for issues and there may still be some with testing products (i.e. Intertest). [...]
686 42 21_Re: Refresh immediate14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 2 Nov 2006 08:42:03 +1100307_US-ASCII IBM likes to have a common syntax between the various flavours of DB2. DB2 LUW requires a choice between IMMEDIATE and DEFERRED - that is, neither is a default. So DB2 zOS requires you to specify the only option it allows - because, to keep the common syntax, it cannot have a default either. [...]
729 40 21_Re: Access path in V833_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Wed, 1 Nov 2006 23:43:18 +0100454_iso-8859-1 Paul,
If OPTIXIO is on please track
APAR Identifier ...... PK32293 Last Changed ........ 06/10/23 INDEX IO PROBE COST TOO HIGH WHEN NLEVELS >= 4
Still waiting for a ptf
Roland
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of PAUL WALTERS Sent: Wednesday, October 25, 2006 1:21 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Access path in V8 [...]
770 54 31_[AD] NEODBUG meeting 11/16/200611_James Szabo18_jim.szabo@CORE.COM30_Wed, 1 Nov 2006 20:51:52 -0500329_iso-8859-1 Neodbug Meeting ReminderThe next Northeast Ohio Database Users Group (NEODBUG) meeting will be held on Thursday, November 16, 2006 starting at 9am, at the Doubletree Cleveland South hotel.
The agenda is listed below. Sign up early and save the $10 and help us better judge how many attendees we will have. [...]
825 27 32_Uncommitted UR - Inflight status5_Logaa22_lthangaraj@INAUTIX.COM30_Wed, 1 Nov 2006 23:57:40 -0600398_- Hi List,
I get the following warning in a CICS-DB2 Program.
08.05.38 STC23438 DSNR035I -xxx DSNRPBCW WARNING - UNCOMMITTED UR 784 784 AFTER 10 CHECKPOINTS - 784 CORRELATION NAME = POOLxxxxxxxx 784 CONNECTION ID = CICSxxxx 784 LUWID = NETPRG.xxxx.BFA3E364A8AD = 22244 784 PLAN NAME = xxxx 784 AUTHID = xxxxx 784 END USER ID = * 784 TRANSACTION NAME = * 784 WORKSTATION NAME = * [...]
853 129 36_Re: Uncommitted UR - Inflight status14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 2 Nov 2006 19:42:12 +1100491_US-ASCII How to avoid this? Easy, just get your application program to issue COMMITs.
INFLIGHT = DB2 thinks the application is still executing UNCOMMITED UR = "a Unit of Recovery (UR) that hasn't issued a COMMIT or ROLLBACK"
Is this really a problem. Depends. The application program might be holding an ever incresaing number of locks, causing other programs to wait, and possibly timeout. The time, and number of logs needed to rollback (if that happens) is increasing. [...]
983 59 36_Re: Uncommitted UR - Inflight status14_Dash, Sushanta21_Sushanta.Dash@FMR.COM30_Thu, 2 Nov 2006 03:55:54 -0500527_us-ascii This is for detecting long reader. Only thing you need to do is issue commits and this would die down.
Thanks Sushant Dash Lead DBA Fidelity Business Services India Pvt. Ltd., Embassy Golf Links Business Park, Off Intermediate Ring Road, Bangalore - 560 071.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Logaa Sent: Thursday, November 02, 2006 12:58 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Uncommitted UR - Inflight status [...]
1043 84 29_Re: convert Timestamp to LRSN0_20_Gerhard.Heiss@RUV.DE30_Thu, 2 Nov 2006 11:31:00 +0100474_iso-8859-1 /* REXX */ /* CONVERT MICROSECONDS SINCE JAN/01/1900 TO LRSN */ /* F.E. 2006-01-01-00.00.00.123456 CORRESPONDS TO 3345062400123456 MICROSECONDS */ NUMERIC DIGITS 31 TS='2006-01-01-00.00.00.123456' MS = 3345062400123456 SAY TS SAY MS LRSN=SUBSTR(D2X(MS), 1, 12) SAY LRSN
/* BACK TO TIMESTAMP USING BLSUXTOD */ SAY STCK(LRSN) EXIT
STCK: PROCEDURE ACC=ARG(1) ACC=X2C(ACC) TDATE = COPIES('0' , 26) ADDRESS LINKPGM "BLSUXTOD ACC TDATE" RETURN TDATE [...]
1128 130 15_Timestamp Value14_Galeos Antonis19_AGaleos@EUROBANK.GR30_Thu, 2 Nov 2006 14:33:27 +0200611_us-ascii Hello listers
I insert the current timestamp value in a table , The program commits after 3 seconds , what value I will get , the value when I issue the insert or the value when the program commits
We are in DB2 V7 for z/OS
Disclaimer: This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system. EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, [...]
1259 53 21_Re: Access path in V812_PAUL WALTERS23_Paul.A.Walters@SLMA.COM30_Thu, 2 Nov 2006 07:59:34 -0500541_US-ASCII Thanks for the information.
>>> SchiradinR@ALTE-LEIPZIGER.DE 11/01/06 5:43 PM >>> Paul,
If OPTIXIO is on please track
APAR Identifier ...... PK32293 Last Changed ........ 06/10/23 INDEX IO PROBE COST TOO HIGH WHEN NLEVELS >= 4
Still waiting for a ptf
Roland
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of PAUL WALTERS Sent: Wednesday, October 25, 2006 1:21 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Access path in V8 [...]
1313 188 19_Re: Timestamp Value0_20_Gerhard.Heiss@RUV.DE30_Thu, 2 Nov 2006 15:05:29 +0100548_US-ASCII it is the point in time when the insert is executed.
regards gerhard
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Galeos Antonis Sent: Thursday, November 02, 2006 1:33 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Timestamp Value
Hello listers
I insert the current timestamp value in a table , The program commits after 3 seconds , what value I will get , the value when I issue the insert or the value when the program commits [...]
1502 45 17_Stored Procedures14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET30_Thu, 2 Nov 2006 06:21:51 -0800549_iso-8859-1 Does anyone know how to convert a DB2 Cobol Stored Procedure into Oracle version?
CREATE PROCEDURE CATT6210.xxxx079A ( IN IP_REQ_DT DATE, IN IP_REQ_IND CHAR(1), IN IP_REQ_SEQ INTEGER, IN IP_REQ_RPT_NUM SMALLINT, IN IP_REQ_CLIENT CHAR(2), IN IP_REQ_SUB_CLIENT SMALLINT, IN IP_REQ_ACCT DECIMAL(15, 0), IN IP_REQ_FROM_DT DATE, IN IP_REQ_TO_DT DATE ) DYNAMIC RESULT SET 10 LANGUAGE COBOL EXTERNAL NAME xxxx079A COLLID CATT_FD_M6210_BTCH ASUTIME LIMIT 500000 PARAMETER STYLE GENERAL WLM ENVIRONMENT DBT1CU71 COMMIT ON RETURN YES ; [...]
1548 109 36_Re: Uncommitted UR - Inflight status12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM30_Thu, 2 Nov 2006 09:14:17 -0600390_us-ascii I don't believe that is correct. A reader is not considered a Unit of
Recovery (UR), and is not logged. This transaction has made changes
(UPDATE, INSERT, DELETE) but is taking longer than it should to COMMIT.
It has held locks on uncommitted changes while DB2 went on through 10
checkpoints. You need to look at this transaction with your monitor [...]
1658 17 49_Re: Determining partition number from page number13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM30_Thu, 2 Nov 2006 09:15:42 -0600670_- Mark,
Thanks. My problem was that the table was defined as LARGE and I didn't include the DSSIZE or LARGE parameter. Once I added that, the results turned out correctly.
Thanks, Donna
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
1676 17 47_DB2 Developer Workbench - Launch Sample Gallery11_Greg Wilson23_greg_wilson@CONSECO.COM30_Thu, 2 Nov 2006 10:50:02 -0600708_- I downloaded the developer workbench yesterday. theres a link on the welcome page to Samples. This leads you to a Launch Samples Gallery link but when selected nothing happens.
Has anybody gotten this to work?
Thanks Greg
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
1694 24 19_Re: Timestamp Value14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK30_Thu, 2 Nov 2006 18:39:38 +0100354_iso-8859-1 > I insert the current timestamp value in a table , The program commits > after 3 seconds , what value I will get , the value when I issue the > insert or the value when the program commits
The value is really physically written at INSERT time, not at COMMIT time: this is e.g. clear when reading the data with "uncommitted read". [...]
1719 14 27_DB2 V7 z/OS Reading with UR15_Tami Van Dreese27_tami.vandreese@LANDSEND.COM30_Thu, 2 Nov 2006 14:13:53 -0600650_- According to the manual, when you read a table using the "with UR" option, you may get uncommitted rows. But will you get ALL uncommitted rows?
Thanks for your help, Tami
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
1734 55 37_DB2 z/OS Calling DSNTIAR from DSNREXX9_Don Leahy19_don.leahy@LEACOM.CA30_Thu, 2 Nov 2006 18:32:24 -0600422_- From time to time I have seen questions posted on this topic, and various solutions have been offered. Most, if not all of them involve some pretty hairy looking code such as:
if length(sqlerrd.4) > digits() then numeric digits length(sqlerrd.4)
SQLC = D2X(SQLCODE,8) SQLC = X2C(SQLC) SQLCA = 'SQLCA 'X2C(00000088)SQLC||X2C(RIGHT(LENGTH(SQLERRM),4,'0')), ||LEFT(SQLERRM,70,' '), ||LEFT(SQLERRP,8,' ') [...]
1790 178 37_DB2 z/OS Calling DSNTIAR from DSNREXX9_Don Leahy19_don.leahy@LEACOM.CA30_Thu, 2 Nov 2006 19:51:20 -0500431_iso-8859-1 From time to time I have seen questions posted on this topic, and various solutions have been offered. Most, if not all of them involve some pretty hairy looking code such as:
if length(sqlerrd.4) > digits() then numeric digits length(sqlerrd.4)
SQLC = D2X(SQLCODE,8) SQLC = X2C(SQLC) SQLCA = 'SQLCA 'X2C(00000088)SQLC||X2C(RIGHT(LENGTH(SQLERRM),4,'0')), ||LEFT(SQLERRM,70,' '), ||LEFT(SQLERRP,8,' ') [...]
1969 19 31_Re: DB2 V7 z/OS Reading with UR33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 3 Nov 2006 02:06:28 -0600567_- Tami
I don't know, what you mean with ALL uncommited rows. If you are in data sharing, it is possible that an uncommited reader, say on member 2 does not see uncommited rows, an aplication has inserted or updated on member 1. The reason for this is, that pages in general will be forced to group buffer pool at commit, so member 2 is not aware of these updates. If you choose the bind-parameter IMMEDWRITE YES, updated pages are written to the group buffer pool immediately and member 2 will see "ALL uncommitted rows". Does this describe your problem? [...]
1989 29 7_SQL nut15_Haldur Johansen18_haldur@ELEKTRON.FO30_Fri, 3 Nov 2006 02:43:32 -0600335_- Hi,
I am on db2 v7 on os/390.
Index: (c1, c2, c3, c4)
Query: select something from t1 where c1 = ? and c2 = ? and c3 = (select coalesce(max(c3), current date) as c3 from t2) and c4 between ? and ?
The Problem is that in my explain matchcols = 2! Is it possible to make matchcols = 4 with this index? [...]
2019 62 66_[DB2 V8 NFM] different matching columns in application and explain33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 3 Nov 2006 02:56:13 -0600670_- Hi listers
I don't understand, why DB2 chooses 3 matching columns (table AGTB0003) for the following statement when used in an application and 4 matching columns, when I explain it using QMF:
DECLARE C_TYP_AS CURSOR WITH ROWSET POSITIONING FOR SELECT A.UPDATE_NR, A.ADRESSAT_KZ, A.ADRESSAT, A.SATZART, .... FROM AGTB0003 A, VATB0406 B, VATB0406 C WHERE A.ADRESSAT_KZ = :H AND A.ADRESSAT = :H AND A.SATZART = :H AND A.TERMIN < :H AND A.POSTK_TYP = :H AND B.VORG_MAND_SL = "1" AND B.ZUST_SL = A.ADRESSAT_KZ AND B.ADRESSAT = A.ADRESSAT AND B.VERW_SYS_SL = A.VERW_SYS_SL AND B.ORDBEG = A.ORDBEG AND B.VORG_LNR = A.VORG_LNR AND B.AKT_HIST_STAND_KZ = "1" [...]
2082 33 11_Re: SQL nut33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 3 Nov 2006 03:29:12 -0600435_- Haldur
Is column c3 nullable?
If column C3 is not nullable and your subquery is not correlated (as you wrote it), you should have got matching columns 4.
If column C3 is nullable, omit the coalesce-function and try again.
With V8 I get 4 matching columns for
SELECT * FROM T1 WHERE C1 = ? AND C2 = ? AND C3 = (SELECT COALESCE(MAX(SUBSTR(C1, 1, 1)), 'A') AS C3 FROM T2 ) AND C4 BETWEEN ? AND ? [...]
2116 111 11_Re: SQL nut15_Haldur Johansen18_haldur@ELEKTRON.FO30_Fri, 3 Nov 2006 09:46:31 +0000485_ISO-8859-1 Hi,
I get MC2 with and without coalesce! C3 is not nullable, therefor I use coalesce because max can be nullable.
Haldur
"Walter Janißen" To Sent by: "DB2 Data Base cc Discussion List" Re: [DB2-L] SQL nut
03-11-2006 09:29
Please respond to "DB2 Database Discussion list at IDUG" [...]
2228 32 31_DB2 Training/Introductory docs.14_Nuttall, Peter27_peter.nuttall@CITIGROUP.COM30_Fri, 3 Nov 2006 10:24:18 -0000452_iso-8859-1 Hi All,
Save me re-inventing the wheel, does anybody have any training/introductory docs for DB2.
A bit of background, as the ex DB2 DBA in the Source management team (Serena's changeman administration) I have been tasked with doing a small introduction to DB2 (maximum 2 hours).
Obviously, I will be targeting it towards things pertinent to Changeman's interaction with DB2, but would appreciate a starting point. [...]
2261 23 11_Re: SQL nut33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 3 Nov 2006 04:52:06 -0600334_- Hi
And your subquery is really non-correlated? Is the subquery without any where-clauses? If the subquery is non-correlated I have no idea, why you don't get 4 matching columns.
How many MC did you get if youi explain:
SELECT .. FROM T1 WHERE C1 = ? AND C2 = ? AND C3 = CURRENT DATE AND C4 BETWEEN ? AND ? [...]
2285 39 11_Re: SQL nut15_Haldur Johansen18_haldur@ELEKTRON.FO30_Fri, 3 Nov 2006 11:20:29 +0000351_US-ASCII Hi,
My actual query looks like this:
SELECT .... FROM T1, (SELECT C1, C2 FROM T - -(small table) WHERE C2 BETWEEN ? AND ? AND C1 = ?) AS T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T1.C3 = (SELECT COALESCE(MAX(C3), CURRENT DATE) - -(c3 is not null) FROM T4 WHERE T4.C1 = T1.C1 AND T4.C5 = T1.C5) AND T1.C4 BETWEEN ? AND ? [...]
2325 25 11_Re: SQL nut33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 3 Nov 2006 05:26:38 -0600303_- Hi
SELECT .... FROM T1, (SELECT C1, C2 FROM T - -(small table) WHERE C2 BETWEEN ? AND ? AND C1 = ?) AS T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T1.C3 = (SELECT COALESCE(MAX(C3), CURRENT DATE) - -(c3 is not null) FROM T4 WHERE T4.C1 = T1.C1 AND T4.C5 = T1.C5) AND T1.C4 BETWEEN ? AND ? [...]
2351 21 20_DB2 V8 and z/OS V1.715_Lizette Koehler23_starsoul@MINDSPRING.COM30_Fri, 3 Nov 2006 08:07:08 -0500342_UTF-8 V1.7 > >A few of the applications programmers and DBAs recently went to IDUG and are now asking about z/OS V1.7. I cannot seem to find any dialogue on any concerns with this combo. > >Are there any current concerns with DB2 V8 nfm and z/OS V1.7 other than the normal issues with the VSAM changes in DFSMS??? > >Thanks.. > >Lizette [...]
2373 44 11_Re: SQL nut15_Haldur Johansen18_haldur@ELEKTRON.FO30_Fri, 3 Nov 2006 13:07:56 +0000533_US-ASCII Thx. Walter!
I should have figured that out :) What I really am curious about is if there is a way to make the subquery stage1, or rewrite the whole SQL to make it stage1
Any ideas? You are all welcome! :-)
My actual query looks like this:
SELECT .... FROM T1, (SELECT C1, C2 FROM T - -(small table) WHERE C2 BETWEEN ? AND ? AND C1 = ?) AS T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T1.C3 = (SELECT COALESCE(MAX(C3), CURRENT DATE) - -(c3 is not null) FROM T4 WHERE T4.C1 = T1.C1 AND T4.C5 [...]
2418 76 11_Re: SQL nut33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 3 Nov 2006 07:16:04 -0600345_- On Fri, 3 Nov 2006 13:07:56 +0000, Haldur Johansen wrote:
Hi
You can code T4.C1 = ?, because T4.C1 = T1.C1 = T2.C1 = ?, but there is no way to get rid of T4.C5 = T1.C5, because you are not interested in the maximum of C3 for a given C1, but you are interested in the maximum of C3 for each different C5 [...]
2495 60 21_Re: Stored Procedures11_Suresh Sane21_data_arch@HOTMAIL.COM30_Fri, 3 Nov 2006 09:52:15 -0600679_- Ramon,
No, I don't know how to convert but just make sure you save the original DDL - it will be handy when you come back to the REAL database some day (:
Thx Suresh
>From: Ramon Santiago >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] Stored Procedures >Date: Thu, 2 Nov 2006 06:21:51 -0800 > >Does anyone know how to convert a DB2 Cobol Stored Procedure into Oracle >version? > > CREATE PROCEDURE CATT6210.xxxx079A > ( IN IP_REQ_DT DATE, > IN IP_REQ_IND CHAR(1), > IN IP_REQ_SEQ INTEGER, > IN IP_REQ_RPT_NUM SMALLINT, > IN IP_REQ_CLIENT [...]
2556 208 36_Re: Uncommitted UR - Inflight status14_Dash, Sushanta21_Sushanta.Dash@FMR.COM30_Fri, 3 Nov 2006 21:55:39 -0500375_us-ascii I don't mean it as long running reader in fact. I looked at the message and dsnr305i and understood what you had explained, but I categorize these as long reader problem(lrdrthld),long updator (urlgwth) and urchkth ur is active than more number of check points system has taken. So the terminology I might have used is in correct in a hurry, but don't mean it. [...]
2765 36 11_Re: SQL nut14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK30_Sat, 4 Nov 2006 18:48:12 +0100308_iso-8859-1 > I get MC2 with and without coalesce!
This seems to indicate that the subquery was rewritten as a join; you can check this in your PLAN_TABLE in column QBLOCKNO: if T2 has blockno=2 it's a subquery, otherwise it's a join. (In which case there should be a 1, 2 or 4 in column METHOD.) [...]
2802 20 70_Re: [DB2 V8 NFM] different matching columns in application and explain14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK30_Sat, 4 Nov 2006 23:56:25 +0100608_iso-8859-1 > Do I have any option to explain a statement which uses rowset positioning?
Try using Visual Explain.
-- Peter.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
2823 28 10_DB2 SQL PL14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET30_Sun, 5 Nov 2006 17:38:49 -0800615_iso-8859-1 Does anyone know of any redbook that show the programming syntax of DB2 SQL Procedure Lanaguage.
Some type of reference guide.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
2852 76 14_Re: DB2 SQL PL14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 6 Nov 2006 13:38:15 +1100543_US-ASCII No free Redbook. If you just want the syntax, see the SQL Reference Manual for your version of DB2.
There's also: DB2(R) SQL PL: Essential Guide for DB2(R) UDB on on Linux(TM), UNIX(R), Windows(TM), i5/OS(TM), and z/OS(R) ISBN: 0131477005
James Campbell
On 5 Nov 2006 at 17:38, Ramon Santiago wrote:
> > Does anyone know of any redbook that show the programming syntax of DB2 SQL Procedure > Lanaguage. > > Some type of reference guide. > ------------------------------------------------------------- [...]
2929 51 30_DB2 V7 z/OS Primary Allocation12_Manash Dutta22_dutta.manash@GMAIL.COM30_Mon, 6 Nov 2006 16:09:45 +0530792_ISO-8859-1 We have a situation where we need to Allocate more than 10,000 cylinders in the primary allocation space.
We are on DB2 V7,SMS Managed datasets,z/OS,3390-Mod 9.
Our TS is partitioned and is defined as 64G.
Can I allocate more than 10,000 cylinders as primary quantity?
Thanks Manash
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
2981 28 14_Re: DB2 SQL PL12_Robert Smith24_robert.smith@NCFBINS.COM30_Mon, 6 Nov 2006 08:04:07 -0500761_us-ascii Try: http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp use the search on the left side to find commands, the search on the right in the black area is for documents.
Ramon Santiago wrote:
> Does anyone know of any redbook that show the programming syntax of > DB2 SQL Procedure Lanaguage. Some type of reference > guide.--------------------------------------------------------------------------------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and > home page at http://www.idugdb2-l.org/archives/db2-l.html. From that > page select "Join or Leave the list". The IDUG DB2-L FAQ is at > http://www.idugdb2-l.org. The IDUG List Admins can be reached at > DB2-L-REQUEST@www.idugdb2-l.org. Find out [...]
3010 416 34_Re: DB2 V7 z/OS Primary Allocation14_Dash, Sushanta21_Sushanta.Dash@FMR.COM30_Mon, 6 Nov 2006 08:16:20 -0500488_us-ascii Hi Manas,
I am not sure whether you need the 10000 cylinders distributed across all your partitions you are having or single partition. If I recall correctly the DFP of db2 media manager is responsible for allocating datasets. It needs contiguous space for allocating primary and if don't get then it gives you more of -904 with the reason code is like 00D70025. Also at the same time you need to see that only one dataset can span max of 59 volumes. [...]
3427 102 12_DB2 Magazine18_Marcelino Arconada17_marconada@EPO.ORG30_Mon, 6 Nov 2006 15:00:24 +0100481_ISO-8859-1 Hi everybody,
Is it just me or does anybody else here have multiple subscriptions to DB2 Magazine ? Every time I try to get them to make a change in my postal adres I end up with yet another subscription. Currently I receive three copies. No matter how much I like the magazine in itself, I really don't need three :-) My collegue in the room next to me ... same problem. I don't dare to send another email to them as I don't want to receive my fourth copy. [...]
3530 201 16_Re: DB2 Magazine15_Foweather, Iris30_Iris.Foweather@UK.EXPERIAN.COM30_Mon, 6 Nov 2006 14:13:16 -0000658_iso-8859-1 I feel left out, I only get two copies.
Iris
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Marcelino Arconada Sent: 06 November 2006 14:00 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Magazine
Hi everybody,
Is it just me or does anybody else here have multiple subscriptions to DB2 Magazine ? Every time I try to get them to make a change in my postal adres I end up with yet another subscription. Currently I receive three copies. No matter how much I like the magazine in itself, I really don't need three :-) My collegue in the room [...]
3732 209 67_AW: [[SPAM]] - [DB2-L] DB2 Magazine - Bayesian Filter detected spam12_Peter, Georg15_G.Peter@DZBW.DE30_Mon, 6 Nov 2006 15:18:12 +0100333_iso-8859-1 >> Is it just me or does anybody else here have multiple subscriptions to DB2 Magazine ?<<
Marcelino,
you are not alone ;-)). Some days ago I received two copies of the last DB2 Magazine too.
I have the substriction for some years now, no changes, no new address.... But they send two copies.... [...]
3942 116 16_Re: DB2 Magazine14_Martin Flavell18_mflavell@I-TCS.COM30_Mon, 6 Nov 2006 14:25:35 +0000
4059 354 16_Re: DB2 Magazine13_Bell, Raymond22_raymond.bell@LANDG.COM30_Mon, 6 Nov 2006 14:29:05 -0000561_iso-8859-1 Ditto on the 'two copies' count.
Raymond
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Foweather, Iris Sent: 06 November 2006 14:13 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Magazine
I feel left out, I only get two copies.
Iris
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Marcelino Arconada Sent: 06 November 2006 14:00 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Magazine [...]
4414 192 16_Re: DB2 Magazine23_Parvathavardhini Kannan19_pkannan@INAUTIX.COM30_Mon, 6 Nov 2006 20:08:33 +0530387_ISO-8859-1 I receive only one copy that too not regularly. Some quarter I receive & in some Quarter I do not receive
Thanks, Vardhini Technical Leader DB2 DBA Group - INDIA, iNautix Technologies India Private Limited, Tel: 22546000 VOIP : 3156
DBA VOIP HOTLINE : 3100
DB2 Data Base Discussion List wrote on 11/06/2006 07:59:05 PM: [...]
4607 226 16_Re: DB2 Magazine11_Jay Reavill23_Jay.Reavill@CERTEGY.COM30_Mon, 6 Nov 2006 09:47:25 -0500674_iso-8859-1 Maybe it's a marketing scheme.... The more "subscribers", the more they can charge advertisers! ;-))
I also started receiving 2 for no apparent reason.
------------------------------------------------------------------ Jay Reavill DBA Fidelity National Information Services, Inc. 11601 Roosevelt Blvd. St. Petersburg, FL. 33716 Office (727) 227-2144 Jay.Reavill@Certegy.com ------------------------------------------------------------------
"Bell, Raymond" cc: Sent by: "DB2 Subject: Re: [DB2-L] DB2 Magazine Data Base Discussion List" [...]
4834 84 16_Re: DB2 Magazine11_Dirk Johann15_D.Johann@SEG.DE30_Mon, 6 Nov 2006 15:56:36 +0100417_iso-8859-1 That's why they call it "DB two Magazine(s)" ...
(also getting two magazines, usually one or two weeks later another one - just if I forgot to read ;-) )
Dirk
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: Monday, November 06, 2006 3:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Magazine [...]
4919 64 41_Re: [Maybe Spam] Re: [DB2-L] DB2 Magazine13_Bell, Raymond22_raymond.bell@LANDG.COM30_Mon, 6 Nov 2006 15:02:22 -0000448_- Oh, very good Dirk. See, your English is coming along very well. Much better than that numpty colleague of yours, Roy. Dirk, ask him how many cooked breakfasts he got thru the 2nd morning of GSE. Bet the answer's > 1...
Raymond PS. Sorry I missed your last-day presentation. I suspect I already know how to crash a subsystem, but any pointers you have would be gratefully received. That way I can always say I meant to do it. [...]
4984 264 24_AW: [DB2-L] DB2 Magazine35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Mon, 6 Nov 2006 16:10:54 +0100559_iso-8859-1 Same here ...
________________________________
Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Foweather, Iris Gesendet: Montag, 6. November 2006 15:13 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] DB2 Magazine
I feel left out, I only get two copies.
Iris
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Marcelino Arconada Sent: 06 November 2006 14:00 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Magazine [...]
5249 225 28_Re: AW: [DB2-L] DB2 Magazine9_Ford Wong14_fordie@SHAW.CA30_Mon, 6 Nov 2006 08:22:19 -0700663_utf-8 Hey, here in Canada we get two copies.
Consider it special if you get three copies.
Ford
----- Original Message ----- From: Kurtz, R�diger Date: Monday, November 6, 2006 8:10 am Subject: [DB2-L] AW: [DB2-L] DB2 Magazine
> Same here ... > > ________________________________ > > Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im > Auftrag von Foweather, Iris > Gesendet: Montag, 6. November 2006 15:13 > An: DB2-L@WWW.IDUGDB2-L.ORG > Betreff: Re: [DB2-L] DB2 Magazine > > > I feel left out, I only get two copies. > > Iris > > -----Original Message----- > From: DB2 Data Base [...]
5475 27 28_Re: AW: [DB2-L] DB2 Magazine11_Grant Allen17_gxallen@GMAIL.COM30_Tue, 7 Nov 2006 02:27:17 +1100377_ISO-8859-1 On 11/7/06, Ford Wong wrote: > Hey, here in Canada we get two copies. > > Consider it special if you get three copies.
It's just IBM taking HADR / HACMP to it's logical extreme. Honest, who wouldn't want a backup copy, or an off-site DR copy? Personally, I get four copies, which let's me run two-node magazine clusters on each site :-) [...]
5503 37 16_Re: DB2 Magazine14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Mon, 6 Nov 2006 10:00:03 -0600333_US-ASCII I guess not everything is bigger in Texas. I get one, sometimes. I will have to keep note, maybe it is every other quarter ? I have only been a subscriber for 6 years in the US, possibly copies are proportional to subscription period ?
---------------------------------------------------------------------------------
5541 303 16_Re: DB2 Magazine12_Haak, Enrico19_Enrico_Haak@BMC.COM30_Mon, 6 Nov 2006 17:28:46 +0100854_us-ascii Keep these two (or more) issues.
Don't throw them away.
Maybe it's a double-header
;-)
I love this thread
Regards,
Enrico
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark E Vickers Sent: Monday, November 06, 2006 5:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Magazine
I guess not everything is bigger in Texas. I get one, sometimes. I will have to keep note, maybe it is every other quarter ? I have only been a subscriber for 6 years in the US, possibly copies are proportional to subscription period ? ------------------------------------------------------------------------ --------- Welcome to the IDUG [...]
5845 107 29_Re: convert Timestamp to LRSN17_Karthik Vinayagam18_vkarthik@YAHOO.COM30_Mon, 6 Nov 2006 12:29:05 -0600345_ISO-8859-1 DB2 also maintains a offset to Timestamp in order to compute correct LRSN. Usually this offset is zero, but there is a possibility this offset is not zero, I guess depending on the RBA at the time of conversion to data sharing.
we got hit by this recently in a replication product. in our case the offset was weeks ahead. [...]
5953 27 19_Revoking DB2 access14_Peggy Chillman26_peggy.chillman@PHOENIX.GOV30_Mon, 6 Nov 2006 13:09:18 -0700222_US-ASCII If SYSADM authority is revoked from a user, do the objects such as tablespaces, plans, and tablescreated by the user remain?
---------------------------------------------------------------------------------
5981 59 23_Re: Revoking DB2 access15_Chris Hoelscher21_choelscher@HUMANA.COM30_Mon, 6 Nov 2006 15:27:27 -0500552_US-ASCII my understanding (from the manual) is that only VIEWs created by the ex-SYSADM (which he/she was SYSADM) would be deleted when SYSADM is revoked
Chris Hoelscher IDMS & DB2 Database Administrator Humana Inc 502-476-2538 choelscher@humana.com
The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information. [...]
6041 76 20_DB2 V8 and z/OS V1.717_McCormack, Mark A27_mamccormack@STATESTREET.COM30_Mon, 6 Nov 2006 15:31:27 -0500705_us-ascii Lizette,
We are running DB2 v8 NFM and z/OS v1.7 without any problems caused by the z/OS version. Our prod conversion to NFM occurred this summer. Z/os v1.7 was installed on our prod lpars in Sept. and Oct.
Mark
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
6118 123 16_Re: DB2 Magazine13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Mon, 6 Nov 2006 14:56:08 -0800461_iso-8859-1 Same here, I only get one copy, sometimes. I haven't received one in quite a while, I think I may have somehow requested the electronic copy which might have discontinued my paper copy.
Thanks, Cathy
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Parvathavardhini Kannan Sent: Monday, November 06, 2006 6:39 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Magazine [...]
6242 239 16_Re: DB2 Magazine35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Mon, 6 Nov 2006 18:02:03 -0500559_iso-8859-1 That's exactly what it is....... magazines base their ad rates on the number of subscribers they have.
Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com (732) 972-1261 ----- Original Message ----- From: "Jay Reavill" Newsgroups: bit.listserv.db2-l To: Sent: Monday, November 06, 2006 9:47 AM Subject: Re: [DB2-L] DB2 Magazine [...]
6482 61 37_DB2zos SAP & PeopleSoft Table counts.23_BOLEY Maurice C * Cliff32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Mon, 6 Nov 2006 16:20:24 -0800660_US-ASCII I've heard the number of tables/tablespaces PeopleSoft and SAP have but have forgotten. Could someone refresh my memory. (ok, a complete re-install)
thanks, cliff:-)
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
6544 276 16_Re: DB2 Magazine0_28_steve.tennant@CUSTOMS.GOV.AU30_Tue, 7 Nov 2006 12:00:29 +1100297_iso-8859-1 Well I used to get one, then two. Then none. Then, for a while, each issue contained a form asking me to resubscribe. I think I'm back to one now, but that's after they stopped arriving and so I subscribed on-line at the same time as sacrificing a goat during the winter solstice. [...]
6821 14 41_Re: DB2zos SAP & PeopleSoft Table counts.47_Luis Miguel Marti=?ISO-8859-1?Q?=ADnez?= Chavez20_luismmch66@YAHOO.COM30_Mon, 6 Nov 2006 19:36:27 -0600643_- TOTALLY depending on the module you have purchased.
Approximately, the number of tablespaces, indexspaces and tables are 10,000+,20,000+,20,000 for both solutions.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
6836 160 41_Re: DB2zos SAP & PeopleSoft Table counts.3_Les21_db2dba@BTINTERNET.COM30_Tue, 7 Nov 2006 07:33:44 -0000508_windows-1250 Hi Cliff,
it depends on the modules you have installed ... but it can be into the 100,000's quite easily.
(note once it was 1 per tablespace, but now they do at least use multi-table tablespaces).
regards
Leslie Pendlebury-Bowe
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of BOLEY Maurice C * Cliff Sent: 07 November 2006 00:20 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2zos SAP & PeopleSoft Table counts. [...]
6997 132 16_Re: DB2 Magazine15_Andersen, Cindy18_candersen@FNNI.COM30_Tue, 7 Nov 2006 01:46:41 -0600532_iso-8859-1 I can top you all for number of copies I receive -- 40. Yes, 40 delivered to my home. I'm President of a RUG and am sent copies of the magazine to distribute at our meetings. I used to receive the 40 copies in one box with no address label on the copies. This year I started receiving the 40 copies as 40 individual pieces of mail, each with my name & home address on them. The mailman just loves delivering these -- they barely fit in the mailbox. I don't appreciate having my personal information on the 40 copies [...]
7130 236 72_Re: DB2 Magazine - Off topic attack of Raymond "go for the jugular" Bell11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 7 Nov 2006 09:01:40 +0100329_us-ascii Bet that got you reading this post....Just to answer the question "Yes it was more than 1 but at least it was also less than 3" that makes it stage 1 and indexable I think.....
Great meeting fellow listers, including Raymond!, at the GSE and I believe a great time was had by all (except Dirk of course....) [...]
7367 176 16_Re: DB2 Magazine0_23_j.p.slot@RN.RABOBANK.NL30_Tue, 7 Nov 2006 09:05:16 +0100309_us-ascii I get the regular DB2 2 copies (no stuttering) and during time another three on a non-regular basis. Some of them have a questionable adress on the label. Our postman must be very creative or have a medical background (reading recepies), because in mysterious ways they keep ariving at my home. [...]
7544 192 73_Re: DB2 Magazine - Off topic attack of Raymond "go for th e jugular" Bell13_Bell, Raymond22_raymond.bell@LANDG.COM30_Tue, 7 Nov 2006 10:35:18 -0000441_- I suspected the answer was 2, but feared it might be 3. Good to hear you stopped before your Mr. Creosote impersonation took flight.
As requested:
"Security is important for any application. It becomes more important as services and business processes become accessible from many different locations.'
I'm so glad we've got SOA to point out these obscure facts. I mean, who'd have thought security was important? [...]
7737 244 90_Antwort: Re: [DB2-L] DB2 Magazine - Off topic attack of Raymond "go for th e jugular" Bell11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 7 Nov 2006 11:59:34 +0100472_us-ascii great quote.....and yes I do indeed wish you luck with your V8. It should be a "walk-in-the-park" but you never know who else is walking their pit bull terriers in the park at the same time..... drop us all a line on Monday about how it went please!
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de [...]
7982 84 73_Re: DB2 Magazine - Off topic attack of Raymond "go for th e jugular" Bell10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Tue, 7 Nov 2006 12:14:06 +0100345_US-ASCII
Migrating to V8 ? Good Luck.
I think I'll migrate to V8 next year. Maybe. If I'll be a DB2 sysprog (actually I'm the shop boy & byte cleaner even my JCL are checked by The Big Brother). If I'll be working of mainframe. If I'll have a work. If the weather will be fine and full moon high in the sky. Too many if..... [...]
8067 210 94_Re: Antwort: Re: [DB2-L] DB2 Magazine - Off topic attack of Raymond "go for th e jugular" Bell10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Tue, 7 Nov 2006 13:22:16 +0100501_US-ASCII
A walk in the park ? in Jurassic Park with T-REX and V8RAPTOR ? Call it a 'walk-in-the-park'......someone call it Russian roulette...:-)))
Max Scarpa From Jurassic Pork
Roy Boxwell To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List [DB2-L] Antwort: Re: [DB2-L] DB2 Magazine - Off topic attack of Raymond "go for th e jugular" Bell 07/11/2006 11.59 [...]
8278 61 41_Re: DB2zos SAP & PeopleSoft Table counts.13_Carol Broyles21_clbroyles55@YAHOO.COM30_Tue, 7 Nov 2006 04:48:09 -0800792_ascii Our 4.6C system has about 7600 tablespaces. It increases with each new release. Carol ----- Original Message ---- From: BOLEY Maurice C * Cliff To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, November 6, 2006 7:20:24 PM Subject: [DB2-L] DB2zos SAP & PeopleSoft Table counts. I've heard the number of tables/tablespaces PeopleSoft and SAP have but have forgotten. Could someone refresh my memory. (ok, a complete re-install) thanks, cliff:-) --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. [...]
8340 95 46_[z/OS v7] - 0% hit ratio on DSNDB07 Bufferpool14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 7 Nov 2006 07:37:02 -0600434_US-ASCII HIT RATIO PUZZLE:
Two bufferpools used for sorting: I am getting 99.6% on BP10 and 0.0% on BP11
These are the only TS/IX in these two pools: BP10 DSNDB07 DSN4K01 BP10 DSNDB07 DSN4K02 BP10 DSNDB07 DSN4K03 BP11 DSNDB07 DSN4K04 BP11 DSNDB07 DSN4K05
Monitoring these over the last two days, BP10 BP11 Getpages 737M - 266M 3M - 3M Sync I/O 81338 - 7948 80228 - 29577 Async I/O req. 77M - 21M 772K - 624K [...]
8436 141 23_Re: Revoking DB2 access13_Robert Tilkes22_TILKESR@NATIONWIDE.COM30_Tue, 7 Nov 2006 07:42:12 -0600536_US-ASCII If you want to revoke sysadm auth. without cascading occurring your will need to do the following. The ID that you want to revoke the auth. make it install sysadm in you zparms. If you are V.7 you will have to recycle DB2, but if you are v.8 this zparm is now dynamic so all you need to do is reload you current sysparm for that subsystem. Once that has been completed revoke sysadm authority from the user you put in SYSADM. Then update your zparms again to remove user from install sysadm. Again if you are v.7 you will [...]
8578 138 41_Re: DB2zos SAP & PeopleSoft Table counts.14_Andy Lankester26_alankester@CDBSOFTWARE.COM30_Tue, 7 Nov 2006 08:12:20 -0600473_windows-1250 Don't forget the indexespaces - in SAP it's 30-40K and rising, each of which needs monitoring for space/disorganisation, though many will be DEFINE NO depending on the modules you have.
Andy Lankester
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of BOLEY Maurice C * Cliff Sent: Tuesday, November 07, 2006 12:20 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2zos SAP & PeopleSoft Table counts. [...]
8717 220 24_Fw: [DB2-L] DB2 Magazine0_22_BRIAN_GOLDBERG@QVC.COM30_Tue, 7 Nov 2006 09:23:26 -0500392_ISO-8859-1 As I have 2 pet goats, I ask that we all refrain from the practice of goat sacrifice. Laverne and Shirley (the goats) thank you.
Brian ----- Forwarded by BRIAN GOLDBERG/QVC on 11/07/2006 09:22 AM -----
steve.tennant@CUS TOMS.GOV.AU Ext: To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List Re: [DB2-L] DB2 Magazine [...]
8938 209 50_Re: [z/OS v7] - 0% hit ratio on DSNDB07 Bufferpool0_19_mike.holmans@BT.COM30_Tue, 7 Nov 2006 14:22:29 -0000469_us-ascii I don't think DB2 uses the 4k datasets on an equal round-robin basis. It will always try and use DSN4K01 first. Thus you will always see a skewing of the usage towards that. It won't have to write most of the workfiles for small sorts to disk so the data will mostly be in memory for those and you will get a very high hit rate. When you get the occasional very large sort it will need to use the other datasets, which won't be primed the way DSN4K01 is. [...]
9148 58 50_Re: [z/OS v7] - 0% hit ratio on DSNDB07 Bufferpool10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Tue, 7 Nov 2006 16:17:19 +0100482_US-ASCII
It's the first time (which doesn't mean it's wrong) I see more than 1 BP for DSNDB07, literature talk about 1 BP per sort tablespaces
Work file are used on a least recently used basis, so it could happen that the last two are used only by very large sort but I think that the fact of having 2 bufferpools isn't a good idea as you could have (as you have) one BP over-utilized used by a part (a small part) of logical work files created by sort process. [...]
9207 325 50_Re: [z/OS v7] - 0% hit ratio on DSNDB07 Bufferpool35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 7 Nov 2006 10:19:19 -0500418_iso-8859-1 Mike is right on there, and I'll put part of Mike's statement a bit stronger - the DB07 objects should NOT be split into multiple pools.
Initially, pages are created in the pool, not read from dasd
Also, pages that are written out often don't have to be read back if they are still in the pool. You will also find someplace between a few %, to 50%, of your getpage activity to be Random. [...]
9533 313 50_Re: [z/OS v7] - 0% hit ratio on DSNDB07 Bufferpool14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 7 Nov 2006 11:12:46 -0600465_US-ASCII Thank you all, I am just starting to tune our buffer pools, so this is a good place to start. Definitely going to roll DSNDB07 into one BP and then I will get into the analysis a lot deeper.
Joel Goldstein - Responsive Systems Sent by: DB2 Data Base Discussion List 11/07/2006 09:19 AM Please respond to Joel Goldstein - Responsive Systems [...]
9847 34 23_Re: Revoking DB2 access13_Wayne Stevens25_twstevens@DHR.STATE.AL.US30_Tue, 7 Nov 2006 11:15:16 -0600399_us-ascii And all packages and plans invalidated that were bound by the SYSADM or by people who bound that were given priviledges by the revoked SYSADM.
If you have Catalog Manager from BMC, you can get a report that details everything. -----Original Message----- From: Peggy Chillman [SMTP:peggy.chillman@PHOENIX.GOV] Sent: Monday, November 06, 2006 2:09 PM Subject: Revoking DB2 access [...]
9882 94 26_db2 buffer pool allocation14_Sniatecki, Jim31_Jim.Sniatecki@ERIEINSURANCE.COM30_Tue, 7 Nov 2006 13:20:31 -0500738_us-ascii Question, If I allocate 100 buffers to my BP8k0 pool am I allocating as far as OS memory
8192*100 = 819,200 bytes of memory or,
am I getting 4096*100 = 409,600 bytes of memory allocated for the 8k buffer pool
since the operating system (z/OS) works in 4k memory pages.
Jim Sniatecki
Erwin Insurance
----------------------------------------- Disclaimer: This message (and any attachments) is confidential and is intended only for the addressee(s). This message may contain information that is protected by one or more legally recognized privileges. If the reader of this message is not the intended recipient, I did not intend [...]
9977 267 30_Re: db2 buffer pool allocation3_Les21_db2dba@BTINTERNET.COM30_Tue, 7 Nov 2006 18:36:23 -0000273_windows-1250 Hi Jim you will have 100 x 8192 pages in the bufferpool that will require 200 4096 frames from z/os (and it is important to make sure z/os has this available - i.e do not let it page well not much).
In Mb this is 100 x 8192 / 1048576 = 0.78125 Mb. [...]
10245 241 30_Re: db2 buffer pool allocation35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 7 Nov 2006 13:37:49 -0500509_iso-8859-1 Hi Jim,
It's the number of buffers * 8K.
Regards, Joel
Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com (732) 972-1261 ----- Original Message ----- From: Sniatecki, Jim Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, November 07, 2006 1:20 PM Subject: [DB2-L] db2 buffer pool allocation [...]
10487 140 34_SQLJ static package execute issues26_Tanner, Jim, YES Solutions27_Jim.Tanner@YESSOLUTIONS.COM30_Tue, 7 Nov 2006 13:38:41 -0500452_us-ascii
Hello Listers,
We have a Java SQLJ application with which we are having authority problems. The application is compiled on the client workstation....connected to mainframe Z/OS DB2(V7) via DB2 Connect(V8.1.13 ) and bound (static package) on the mainframe.
The problem we are having is that user ids that do not have execute authority on the package are still able to logon to the application and execute it. [...]
10628 14 24_Alias on the DB2 Catalog10_Mike Frame30_mike_frame@AMERICANCENTURY.COM30_Tue, 7 Nov 2006 12:49:48 -0600303_- The DBA team would like to get rid of our CLONE catalog and just create ALIAS to the real catalog using the old CLONE catalog name. 1) Does anyone use ALIAS or SYNONYM on the CATALOG? 2) Could this introduce problems/issue with Catalog migration. 3) Is this a potential back door to the Catalog. [...]
10643 228 30_Re: db2 buffer pool allocation14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 7 Nov 2006 13:19:10 -0600465_US-ASCII A possible diversion, but does an 8/16/32K pagesize tablespace need one or more workfile tablespaces in a relative bufferpool in DSNDB07 ?
Les Sent by: DB2 Data Base Discussion List 11/07/2006 12:36 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: db2 buffer pool allocation [...]
10872 53 32_Finding out who issued a command11_Hugh Lapham26_hugh.lapham@RCMP-GRC.GC.CA30_Tue, 7 Nov 2006 14:29:00 -0500722_US-ASCII I'm trying to find out who issued a "-STOP DB" command a few days ago. The syslog shows me when the stop pending showed up and when the stop finally failed, but doesn't ask the original question.
Where should I be looking?
TIA!!
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
10926 316 30_Re: db2 buffer pool allocation14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 7 Nov 2006 14:10:16 -0600469_US-ASCII I answered it myself - YES for 4K & 32K else
-20004, ERROR: 8K OR 16K BUFFERPOOL PAGESIZE INVALID FOR A WORKFILE OBJECT
Mark E Vickers Sent by: DB2 Data Base Discussion List 11/07/2006 01:19 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: db2 buffer pool allocation [...]
11243 38 28_Re: Alias on the DB2 Catalog18_Leblanc, Francis C27_Leblanc.Francis@CON-WAY.COM30_Tue, 7 Nov 2006 12:13:26 -0800636_us-ascii 1) Yes, we have several aliases on our catalog 2) Haven't noticed any problems 3) Don't think so
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Frame Sent: Tuesday, November 07, 2006 10:50 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Alias on the DB2 Catalog
The DBA team would like to get rid of our CLONE catalog and just create ALIAS to the real catalog using the old CLONE catalog name. 1) Does anyone use ALIAS or SYNONYM on the CATALOG? 2) Could this introduce problems/issue with Catalog migration. 3) Is this a potential back door to [...]
11282 242 38_Re: SQLJ static package execute issues23_BOLEY Maurice C * Cliff32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Tue, 7 Nov 2006 15:11:41 -0800479_US-ASCII Jim, did you check to make sure that the package is only bound and authorized to that one collection. Also check the authorizations on the plan the collection is bound to, that could be your hole.
A bit of advice, don't use or grant SYSADM for mundane uses like binding and owning DB2 objects. A SYSADM can do pretty most anything they want. An Install SYSADM can do anything. Granting SYSADM is a quick way to get thing done but it can bite you in the end. [...]