1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2004, week 5
2 29 38_Re: Explain shows two additional sorts33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 29 Sep 2004 02:21:07 -0500458_- Isaac
You wanted to see the definition of index OBIX1707:
column name COLCARDF Type Length collating sequence ------------------ ------------ -------- ------ ------------------ F_OE_ART_SL 6 CHAR 1 A F_OE_NR 385.024 INTEGER 4 A ABLOESE_HIST_LNR 2 SMALLINT 2 A GE_DAT 3.776 DATE 4 D A_OE_NR 729.088 INTEGER 4 A A_OE_ART_SL 4 CHAR 1 A A_OE_KURZ_NAME 9.728 CHAR 10 A OEBZA_ART_SL 62 CHAR 2 A AUFG_SPEZI_SL 24 CHAR 5 A GB_DAT 4.416 DATE 4 A [...]
32 107 47_AW: Sample DDL for a DSN_STATEMENT_TABLE wanted41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Tue, 28 Sep 2004 11:00:26 +0200655_iso-8859-1 Hi Georg,
here goes ...
CREATE TABLESPACE TMDBN006 IN DT7DB001 USING STOGROUP SG000000 PRIQTY 288 SECQTY 288 ERASE NO FREEPAGE 63 PCTFREE 10 GBPCACHE CHANGED COMPRESS YES BUFFERPOOL BP10 MAXROWS 255 LOCKSIZE PAGE TRACKMOD YES CCSID EBCDIC CLOSE YES SEGSIZE 64 ; CREATE TABLE T7.DSN_STATEMNT_TABLE (QUERYNO INTEGER NOT NULL WITH DEFAULT, APPLNAME CHAR (8) FOR SBCS DATA NOT NULL WITH DEFAULT, PROGNAME CHAR (8) FOR SBCS DATA NOT NULL WITH DEFAULT, COLLID CHAR (18) FOR SBCS DATA NOT NULL WITH DEFAULT, GROUP_MEMBER CHAR (8) FOR SBCS DATA NOT NULL WITH DEFAULT, EXPLAIN_TIME TIMESTAMP NOT NULL WITH DEFAULT, STMT_TYPE CHAR (6) [...]
140 91 41_Re: DB2 PM output to a dataset and layout13_Mertens, Bart18_bart.mertens@CZ.NL31_Wed, 29 Sep 2004 12:03:41 +0200662_- Ray,
IBM supplies sample audit tables with db2pm, search for the following tables in your 'SDGOSAMP' library (ddl + load statements): DB2PMFAUDT_BINDNR DB2PMFAUDT_AUTHCHG DB2PMFAUDT_AUTHCTR DB2PMFAUDT_DDL DB2PMFAUDT_DML DB2PMFAUDT_AUTHFAI DB2PMFAUDT_SQLTEXT DB2PMFAUDT_UTILITY
met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl
-----Oorspronkelijk bericht----- Van: Janes, Ray H {PBSG} [mailto:Ray.H.Janes@PBSG.COM] Verzonden: Tuesday, September 28, 2004 23:04 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: DB2 PM output to a dataset and layout [...]
232 12 8_DSNT501I11_Lori Bewley28_lorinda.l.bewley@US.HSBC.COM31_Wed, 29 Sep 2004 05:48:25 -0500635_- Why does DSNT501I sometimes give names and sometimes give IDs? I haven't been able to figure out the pattern, but it sure is more convenient when I get the names!
--------------------------------------------------------------------------------- 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
245 76 12_Re: DSNT501I21_Attuluri Vidya, Sagar32_Sagar.AttuluriVidya@SISCLEAR.COM31_Wed, 29 Sep 2004 14:14:16 +0200577_us-ascii DSNT501I message gives IDs when involved object is Table. Gives names when involved object is Table space (row or page).
Assume that there is a cursor declared for update on table T1.
Program1 bound with isolation of RR opens and fetches from this cursor (db2 will take a SHARED lock on table T1, if there is no supporting index for where clause of the cursor). Program2 bound with isolation of CS opens and fetches from the same cursor (db2 will try to take Intent Exclusive lock on TS and Table T1, and if Program1 is still holding SHARED lock on [...]
322 29 14_DB2 V7 Upgrade6_Shauna24_shauna.hadden@BCBSKS.COM31_Wed, 29 Sep 2004 07:14:38 -0500568_- We recently upgraded production to DB2 Version 7. We have been experiencing two things....hitting the max number of DDF threads and unexplainable rollbacks from client server applications.
We have a BMC (mainview) summary trace running and we can see a lot of hits. I turned on a BMC detail trace (to get more information) with just SQL and saw no hits. When I turned on the BMC DDF trace, I starting seeing the rollbacks. Unfortunately, this is all it showed me. We are guessing the rollbacks are on the connect since no SQL is showing up in the trace. [...]
352 47 18_Re: DB2 V7 Upgrade18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 29 Sep 2004 08:26:47 -0400416_utf-8 Have you checked your max threads zparm setting on the DB2 v7 OS/390 side. We have our threads set to go 'inactive' and the max threads set at 200 at this time. The querytimeoutinterval is just going to let the SQL run until it hits the idle-thread-timeout value on the mainframe. On our DB2 Connect v7.2 Enterprise Edition side we have our maxagents set to 200 also. You may be hitting these thresholds. [...]
400 112 38_Re: Additional problems with deadlocks17_Michaelis, Daniel25_dcmichaelis@EORIGINAL.COM31_Wed, 29 Sep 2004 08:31:18 -0400371_- Richard,
Thanks for the response. Unfortunately, I believe that I am using row locks, instead of table locks. That's what's really got me messed up over this; I would expect this behavior with a table or a page lock (I don't know if DB2 even does page locking; I've only seen options in the alter table statement for table and row), but not with row locks. [...]
513 121 38_Re: Additional problems with deadlocks17_Michaelis, Daniel25_dcmichaelis@EORIGINAL.COM31_Wed, 29 Sep 2004 08:33:16 -0400513_- Ian,
I hate to be obtuse, but this doesn't make sense to me. Certainly, you can't be implying that if ANY row in a table is locked, then subsequent operations have to wait for that lock to be released, even if the operation refers to a different row? That would imply that every lock is a table lock, rather than a page or row lock. I would have to assume that the engine is "smart" enough to look at the affected set of rows, and determine that there are no conflicting locks placed on those rows. [...]
635 45 31_Re: Content Management question12_Kornelis Abe26_a.kornelis@PINKROCCADE.COM31_Wed, 29 Sep 2004 14:49:19 +0200397_iso-8859-1 Linda, Mark,
thanks a lot for your help.
I have a remaining question, though. What is a reasonable size for a partition, in order to keep the partitions manageable.
1GB would be too small - too many partitions required. 64GB would be too much - not manageable during reorg, image copying, recovery, etc. What is a reasonable figure? What are your experiences? [...]
681 209 38_Re: Additional problems with deadlocks15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM31_Wed, 29 Sep 2004 14:56:50 +0200558_iso-8859-1 Hi, I can be wrong because I came from DB2 z/OS so maybe there are differences but: You said there are not indexes on the 2 tables, so db2 has to do tablespace scan to resolve the where conditions, looking every row of the table. This is why you get a deadlock: If a row is locked your process must wait the lock is released in order to determine if the row satisfies the query. I'm not completely shure but having indexes could help because you read (and therefore lock) only pages/rows that satisfy the where condition. However, as Richard [...]
891 33 50_Authorizations needed to create federated mappings13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Wed, 29 Sep 2004 08:13:42 -0500567_iso-8859-1 Hi list,
I posted this question a couple of weeks ago, but got no responses. Sorry for the repost, but I really need to get this figured out.
Z/os DB2 v7.1 and UDB v8.2 (beta right now).
When mapping a UDB user id to a mainframe id, I'm trying to determine the minimum authorization needed on the mainframe. I tried mapping to a mainframe user id that basically has just select authority on tables. The actual mapping part causes no errors. When I try to create a nickname, I get a message saying I don't have the authority to do [...]
925 33 38_Re: Additional problems with deadlocks19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Wed, 29 Sep 2004 09:17:58 -0500688_us-ascii Hi Dan,
Your right; although UDB's "CREATE TABLESPACE" has the PAGESIZE parameter which defaults to 4096 it only uses row and/or table locking.
Could anything else be interfering with this like RI and/or triggers?
Rich Humphris
E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain confidential and/or legally privileged information. If you are not the intended recipient of this message or if this message has been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this message and any attachments. If [...]
959 71 18_Re: DB2 V7 Upgrade0_15_Ale.Eba@CGI.COM31_Wed, 29 Sep 2004 10:43:03 -0400412_US-ASCII What is the setting of your CMTSTAT. It is related to connection pooling. ACTIVE means DBATs are not freed.I would suggest make it INACTIVE. The consequence of which will be more accounting SMF records.
Ale
Shauna cc: Sent by: DB2 Data Subject: DB2 V7 Upgrade Base Discussion List [...]
1031 16 15_Concurrent Copy16_Philippe Godfrin26_philippe_godfrin@MEDCO.COM31_Wed, 29 Sep 2004 10:24:55 -0500589_- Greetings,
Am I behind the times, or does anybody actually use DB2 concurrent copy with SMS?
thanx, pg
--------------------------------------------------------------------------------- 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
1048 73 38_Re: Explain shows two additional sorts12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 29 Sep 2004 18:50:07 +0200463_utf-8 Hi Walter,
I asked for the column definition to see if I can understand better the access path. It helped - What happens is that the NCOSUB is executed 1st. The result of the NCOSUB is used together with F_OE_ART_SL to read the data from OBTB0707 via the index (flip of access) - this is explained from the access of N (in-list) and MATCHCOLS = 2 on the outer table. The "group by" is really not needed but as it is written, it is performed :-( [...]
1122 85 31_Re: Content Management question15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US31_Wed, 29 Sep 2004 11:54:19 -0500389_- Abe, We have three different customer agencies that store data in these systems. Each has 10 OAM storage groups defined. The agency storing the most (so far) has each 32K group tablespace divided into 40 partitions. Each partition has somewhere around 2G allocated. The rest of the 32k tablespaces are about half that size. Of course, the 4K tablespaces are even smaller than that. [...]
1208 132 40_Re: Online REORG restart in SWITCH phase9_Dan Lamas22_dlamas@DUKE-ENERGY.COM31_Wed, 29 Sep 2004 13:06:34 -0500384_US-ASCII I have had the same problem. The switch phase is one of the only phases that online reorg has a problem with. It will cause your tables to be unavailable. You must restart the utility with restart(phase)
To avoid this problem, you can specify in your parms TIMEOUT TERM. This will terminate your utility with cond code 008. But you will have to rerun your reorg. [...]
1341 84 18_Re: DB2 V7 Upgrade6_Shauna24_shauna.hadden@BCBSKS.COM31_Wed, 29 Sep 2004 13:06:47 -0500580_- We have it set to inactive.
On Wed, 29 Sep 2004 10:43:03 -0400, Ale.Eba@CGI.COM wrote:
>What is the setting of your CMTSTAT. It is related to connection pooling. >ACTIVE means DBATs are not freed.I would suggest make it INACTIVE. The >consequence of which will be more accounting SMF records. > >Ale > > > > > Shauna > BSKS.COM> cc: > Sent by: DB2 Data Subject: DB2 V7 Upgrade > Base Discussion > List > ORG> > > > 09/29/2004 08:14 > AM > Please respond to > DB2 Database > Discussion list [...]
1426 18 18_Re: DB2 V7 Upgrade6_Shauna24_shauna.hadden@BCBSKS.COM31_Wed, 29 Sep 2004 13:09:42 -0500430_- We have bumped up our max threads, decreased the timeout seconds and played with active and inactive counts. We are getting the message where we have exceeded the thread count less (but still seeing it), but it has had no impact on all of the rollbacks we are seeing.
Strangely enough, the clients aren't seeing the rollbacks (assuming the developers coded for them). They do see the thread count maximum being hit. [...]
1445 18 27_PSP buckets for datasharing14_Matthews, John25_JMatthews@MEDNET.UCLA.EDU31_Wed, 29 Sep 2004 11:31:52 -0700673_iso-8859-1 We are planning to implement DB2 for OS390 V7 datasharing and I've been told to look at the appropriate PSP buckets. How do I identify these buckets and how do I access them?
---------------------------------------------------------- IMPORTANT WARNING: This email (and any attachments) is only intended for the use of the person or entity to which it is addressed, and may contain information that is privileged and confidential. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. Unauthorized redisclosure or failure to maintain confidentiality may subject you to federal and state penalties. If you are not [...]
1464 38 53_Identity columns and end of the table insert activity11_Mike Jessen22_jessenmike@HOTMAIL.COM31_Wed, 29 Sep 2004 13:33:56 -0500643_- Environment: DB2 V7 on Z/os.
We are planning to implement identity columns using an 'identity column' table to generate keys.
Program logic (I believe) would be as follows. Insert into 'identity column' table. Set host variable using identity_val_local function. Commit Use value on 'real table' Insert continue processing
Do I need the commit immediately after the function call? If I don't have the commit, can I potentially cause contention on the 'identity column' table? If I have hundreds of inserts into this table, am I always gaurenteed I'll get back the identity column value that I just inserted and not [...]
1503 51 31_Re: PSP buckets for datasharing9_Chu, Pius14_ChuP@CONED.COM31_Wed, 29 Sep 2004 14:48:34 -0400538_iso-8859-1 Logon to IBMLINK and go to Preventive Service Planning (PSP). Key in "DB2710" as the Upgrade name and submit. Since data sharing is in FMID HDB7710, go down to the bottom where you see HDB7710-0203. Open that file and search on "data sharing", you should find these HIPER PTF's.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Matthews, John Sent: Wednesday, September 29, 2004 2:32 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: PSP buckets for datasharing [...]
1555 71 20_reaching 4 GB limit.16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Wed, 29 Sep 2004 14:40:40 -0400476_us-ascii We have a 6 partition tablespace that twice this year has gotten an insert error 00D70011. To the best of my knowledge this has reached the 4 gb limit. Partition has 104 million rows, we resized and reorg'd.
This is from mvs log DSNP007I +P DSNPXTN0 - EXTEND FAILED FOR 530 D97L.DSNDBD.DCBS0002.SCST0276.I0001.A006. RC=00D70011 CONNECTION-ID=BATCH, CORRELATION-ID=CR9360JD, LUW-ID=* IEC205I SCRZOUT,CR9360JD,S9357A,FILESEQ=0001, COMPLETE VOLUME LIST, 531 [...]
1627 181 24_Re: reaching 4 GB limit.11_David Nance16_DWNance@FHSC.COM31_Wed, 29 Sep 2004 14:57:49 -0400335_US-ASCII We wrote up a rexx exec that does the calculations for us. This is pretty simple calculation based on number of tracks used and bytes per track to see how many GB we have in the partition or table. Currently, we run it online, but plan to move it into a batch job that will e-mail us if there are any limits approaching. [...]
1809 43 52_Recruiting ASG-TMON for DB2 beta - v8.1 exploitation14_Gary Henderson22_gary.henderson@ASG.COM31_Wed, 29 Sep 2004 15:04:04 -0400353_us-ascii I apologize for abusing this forum in advance, so I'll make this brief.
If any TMON for DB2 customers are interested in beta for our next release that exploits new capabilities in DB2 v8.1, please contact me offline.
TMON DB2 v4.0 has toleration support for DB2 v8.1
Feel free to contact me offline for any questions. [...]
1853 167 40_Re: Online REORG restart in SWITCH phase14_Flatley, Lynne31_Lynne.Flatley@LIBERTYMUTUAL.COM31_Wed, 29 Sep 2004 15:30:30 -0400410_us-ascii By searching the list archives, I found Dr Ebert's recommendations for the various online reorg parms. Here are our group's specifications,
REORG TABLESPACE databasename.tsname SHRLEVEL CHANGE MAPPINGTABLE tbcreator.mappingtable DRAIN ALL MAXRO 15 DELAY 90 DRAIN_WAIT 3 RETRY 5 RETRY_DELAY 15 DEADLINE CURRENT_TIMESTAMP + 3 HOURS LONGLOG TERM TIMEOUT TERM STATISTICS TABLE(ALL) INDEX(ALL) [...]
2021 61 18_Re: DB2 V7 Upgrade0_15_Ale.Eba@CGI.COM31_Wed, 29 Sep 2004 16:00:43 -0400484_US-ASCII Do you see rollbacks in monitor reports. I think a connection termination at the requester results in a rollback at the server. For further explanation you can open an ETR.
Ale
Shauna cc: Sent by: DB2 Data Subject: Re: DB2 V7 Upgrade Base Discussion List
09/29/2004 02:09 PM Please respond to DB2 Database Discussion list at IDUG [...]
2083 42 18_Re: DB2 V7 Upgrade33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Wed, 29 Sep 2004 22:25:24 +0200484_iso-8859-1 Any entries in the DB2diag.log?
-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Shauna Gesendet: Mittwoch, 29. September 2004 14:15 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: DB2 V7 Upgrade
We recently upgraded production to DB2 Version 7. We have been experiencing two things....hitting the max number of DDF threads and unexplainable rollbacks from client server applications. [...]
2126 16 8_Re: TMOM41_Poston, William L (GE Commercial Finance)21_william.poston@GE.COM31_Wed, 29 Sep 2004 16:30:17 -0400598_iso-8859-1 anyone out there know how to get start and end time through the batch report writer on the accounting detail report?
--------------------------------------------------------------------------------- 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
2143 62 20_LISTDEF and TEMPLATE15_McCardle, Corey23_CMcCardle@AMERISURE.COM31_Wed, 29 Sep 2004 16:32:48 -0400737_iso-8859-1 Good Afternoon,
We are trying to setup an Unload job using LISTDEF and TEMPLATE control statements from Image copy. Is there a way to unload from a generation dataset for example (0) is current (-1) previous run and so on. Please help
Thanks
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
2206 32 18_Re: DB2 V7 Upgrade19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Wed, 29 Sep 2004 15:45:28 -0500682_us-ascii You may want to start traces for ifcid's 83, 314 and/or 319 and see if there is anything interesting going on there. The main documentation for ifcid's is in hlq.SDSNMACS(DSNDQW01) for ifcid 83 info; hlq.SDSNMACS(DSNDW04) for 314+319 info.
Maybe mainview can trace/format the trace records for you.
Rich Humphris
E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain confidential and/or legally privileged information. If you are not the intended recipient of this message or if this message has been addressed to you in error, please immediately alert the [...]
2239 35 26_PeopleSoft on ZOS V7.1 DB20_21_James_Parr@JBHUNT.COM31_Wed, 29 Sep 2004 16:52:04 -0500404_US-ASCII Greetings list,
I have two questions for all of you out there that have implementations of PeopleSoft on DB2 ZOS.
We are currently evaluating our test architecture and thinking about changing it. Currently our architecture is set up as a one db2 subsystem for one instance of PeopleSoft (FIN) in production, in test we have one db2 subsystem and many instances of PeopleSoft. [...]
2275 198 38_Re: Additional problems with deadlocks12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 29 Sep 2004 14:07:16 -0700571_us-ascii Daniel-
Try creating each table in a separate tablespace and see if you get different results.
tim
--- "Michaelis, Daniel" wrote:
> Ian, > > I hate to be obtuse, but this doesn't make sense to > me. Certainly, you > can't be implying that if ANY row in a table is > locked, then subsequent > operations have to wait for that lock to be > released, even if the operation > refers to a different row? That would imply that > every lock is a table > lock, rather than a page or row lock. I would [...]
2474 152 38_Re: Additional problems with deadlocks12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 29 Sep 2004 15:07:19 -0700514_us-ascii Daniel, Not quite, what it really means is if a row on a page is locked then all rows on that page are locked, unless you have row level locking or get into lock escalation. Since they can be up to 255 rows on a page that means that there can be up to 255 rows locked by a single row update. For extremely small tables, this is a problem as all the rows typically are only on one page. So what you're getting in this situation is essentially table level locking because there is only one page in the [...]
2627 87 57_Re: Identity columns and end of the table insert activity11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Thu, 30 Sep 2004 09:48:55 +0100345_iso-8859-1 Hi Mike
You do not need to commit immediately after the function call. There will be no lock contention on either the 'identity column' table or the DB2 Catalog table that holds the next available value. However, if you do not commit then the rows in the 'identity column' table may be spread over more pages (see below). [...]
2715 12 38_Re: Explain shows two additional sorts33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 30 Sep 2004 05:42:20 -0500564_- Hi Isaac
Unfortunatly not. We are short on money, so I'm not allowed to participate.
--------------------------------------------------------------------------------- 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
2728 18 57_Re: Identity columns and end of the table insert activity33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 30 Sep 2004 05:52:03 -0500305_- Hi Mike
I think some time ago, we had a thread dealing with inserts and deadlock or timeout. For identity-tables your replay holds, but if you have a table with a unique index an inserter may deadlock, if he tries to insert a row with a duplicate key. We recently experienced such problems. [...]
2747 187 30_Re: PeopleSoft on ZOS V7.1 DB20_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 30 Sep 2004 07:29:33 -0400412_US-ASCII James,
My experiences at two previous employers and several consulting assignments was always to have all 'development' PSoft instances in the same DB2 subsystem. A few exceptions occurred, but these always involved either security/privacy issues (like the instance we loaded with production data), or the DBA sandbox system (where we tested PSoft against new versions of DB2, for example). [...]
2935 121 30_Re: PeopleSoft on ZOS V7.1 DB20_26_Jeffrey_Frazier@WENDYS.COM31_Thu, 30 Sep 2004 07:40:28 -0400417_us-ascii James, we run many instances within 1 DB2 subsystem for test and so forth. We build our test db2's for migrations using flashcopy.
James_Parr@JBHUNT.COM Sent by: DB2 Data Base Discussion List 09/29/2004 05:52 PM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@WWW.IDUGDB2-L.ORG cc: Subject: PeopleSoft on ZOS V7.1 DB2 [...]
3057 16 24_Re: LISTDEF and TEMPLATE33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 30 Sep 2004 06:40:44 -0500676_- Hi
What did you try already? Did you try something like that:
TEMPLATE ... DSN 'VTSDB.IC1.VADB0007.VATSP007(0)'
I ran it only with option PREVIEW without errors. May be it helps you.
--------------------------------------------------------------------------------- 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
3074 34 28_DPMODE & Priority of db2 tcb30_zg=?ISO-8859-1?Q?=FCr_=D6zen?=13_oozen@YKB.COM31_Thu, 30 Sep 2004 07:54:54 -0500546_- Hi all, in peak interval we live having too much class1 time according to class2 and in cics the time is seen as redispatch time.
normal time ;
AVERAGE APPL(CL.1) DB2 (CL.2) ------------ ---------- ---------- ELAPSED TIME 0.193433 0.123429
problem :
AVERAGE APPL(CL.1) DB2 (CL.2) ------------ ---------- ---------- ELAPSED TIME 0.559050 0.213288
I tried to use dpmode=high for one of the high volume rct entries , because I use equal for all.But the redispatch time incresed instead of decrease. [...]
3109 114 32_Re: DPMODE & Priority of db2 tcb13_Martin Packer24_martin_packer@UK.IBM.COM31_Thu, 30 Sep 2004 14:03:56 +0100321_ISO-8859-1 Assuming we're talking about elapsed times Thread Reuse could cause this. In my code I test the difference between Cl1 and Cl2 Elapsed Time to determine whether Cl1 is worth displaying. In the case where Cl1 is too high the thread is idle, waiting to be reused.
At least at a simplified level. :-) [...]
3224 37 24_Re: LISTDEF and TEMPLATE15_McCardle, Corey23_CMcCardle@AMERISURE.COM31_Thu, 30 Sep 2004 09:09:11 -0400415_- The problem that I think I'm having is that the UNLOAD utility with FROMCOPY/FROMCOPYDDN requires the dataset name for the specific IC and you can't use (0), TEMPLATE, or LIST.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Walter Janißen Sent: Thursday, September 30, 2004 7:41 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: LISTDEF and TEMPLATE [...]
3262 12 32_Re: DPMODE & Priority of db2 tcb30_zg=?ISO-8859-1?Q?=FCr_=D6zen?=13_oozen@YKB.COM31_Thu, 30 Sep 2004 08:07:26 -0500637_- I stopped using protected thread before trying dpmode. and also cics response times incresing because of redispatch wait time nearly %75 of the total response time.
--------------------------------------------------------------------------------- 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
3275 20 20_TCP/IP access to UDB0_69_Steve_Domarski/Marion_County_Property_Appraiser@PROPAPPR.MARION.FL.US31_Thu, 30 Sep 2004 09:17:10 -0400318_US-ASCII Anyone ever tried to access DB2 directly from TCP/IP. I'm running in VM and I want to access tables in DB2 UDB. I can write rexx based TCP/IP servers but I can't find the protocol's to talk to UDB. I know about Connect. We have DRDA running but its all from VM to UDB. I have a need to read UDB from VM. [...]
3296 89 32_Re: DPMODE & Priority of db2 tcb13_Martin Packer24_martin_packer@UK.IBM.COM31_Thu, 30 Sep 2004 14:32:01 +0100308_ISO-8859-1 Redispatch Wait Time is a CICS term. Sounds very much like you're queuing on the CICS QR TCB. That could either be because you've genuinely filled an engine or else because other things (such as LPAR setup) prevent you from getting a full engine. Do examine the CPU time for the CICS region. [...]
3386 11 32_Re: DPMODE & Priority of db2 tcb30_zg=?ISO-8859-1?Q?=FCr_=D6zen?=13_oozen@YKB.COM31_Thu, 30 Sep 2004 09:48:46 -0500574_- We plan to open a new aor on each system,do you think this can help to solve the cics-engine problem?
--------------------------------------------------------------------------------- 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
3398 86 32_Re: DPMODE & Priority of db2 tcb13_Martin Packer24_martin_packer@UK.IBM.COM31_Thu, 30 Sep 2004 15:54:29 +0100386_ISO-8859-1 I'd really need to see your performance data but another AOR balances the load across (potentially) 2 engines. But if they were fighting over the same capacity you could still see the same effect as you do now.
Cheers, Martin
Martin Packer, MBCS CITP Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584) [...]
3485 307 19_More deadlock stuff17_Michaelis, Daniel25_dcmichaelis@EORIGINAL.COM31_Thu, 30 Sep 2004 11:07:33 -0400684_- Folks,
Version/Platform/Other information:
DB2 V8.1.0.16, fixpack 2
O/S version: Solaris 5.9
Websphere running EJB code (don't know the version)
My apologies... I'm looking for a reference manual, and I can't seem to find what I want. I'm looking for something that describes the output of the deadlock event monitor when written to a file using the db2evmon formatter. I'm looking through the output now, and I'm struggling to connect the statement that is holding the lock that I'm deadlocking on, with the statement that is requesting the lock. This is made more difficult by the fact that we're using JDBC [...]
3793 12 50_Finding last occurrence of a substring in a string22_KenParis@austin.rr.com22_KenParis@AUSTIN.RR.COM31_Thu, 30 Sep 2004 11:29:29 -0500637_- POSSTR will find the first occurrence of a substring in a string - does anyone know of a function or concatenation of functions which will find the last occurrence?
--------------------------------------------------------------------------------- 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
3806 14 19_Re: Concurrent Copy16_Philippe Godfrin26_philippe_godfrin@MEDCO.COM31_Thu, 30 Sep 2004 11:41:21 -0500580_- duh - it's always with SMS. I meant - anybody have an opinion about using concurrent copy?
thanx, pg
--------------------------------------------------------------------------------- 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
3821 108 49_IDUG 2004 - Europe Hosts Industry Leading Vendors62_Marcel L=?ISO-8859-1?Q?=E9vy?= - Conference Planning Committee24_marcel_idug_db2@YAHOO.FR31_Thu, 30 Sep 2004 11:50:24 -0500398_ISO-8859-1 Dear DB2 user,
I received a few emails about Registration deadline: it's not too Late to Register! http://conferences.idug.org/europe/2004/index.cfm
Don't miss out on the premier European DB2 event this autumn - make plans today to attend IDUG 2004 - Europe, 11-14 October, Prague Hilton, Prague, Czech Republic and One-Day Educational Seminars on Friday, 15 October. [...]
3930 21 31_Re: Content Management question7_Do Park19_doparkdba@YAHOO.COM31_Thu, 30 Sep 2004 13:27:09 -0500768_- Hello all
We use ImagePlus v3.1.
I am wondering why you use ImagePlus and Content Manager for z/OS V8.2 both. I haven't used Content Manager for z/OS V8.2. what is difference between Imageplus and Content Manager for z/OS V8.2? What are cons and pros?
Thanks in advance, Do.
--------------------------------------------------------------------------------- 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
3952 35 54_Re: Finding last occurrence of a substring in a string13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Thu, 30 Sep 2004 14:01:03 -0500422_iso-8859-1 how about setting up a loop using the LOCATE function. Use the locate to find the position of the first occurrence, then move that value (maybe +1) to the start point. Repeat until no more occurrences are found, and that leaves your last start point as the last occurrence
Off the top of my head I can't think of a way of doing it all in SQL, but I'm sure someone will be able to make a suggestion. [...]
3988 37 32_Re: DPMODE & Priority of db2 tcb19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 30 Sep 2004 14:07:41 -0500412_us-ascii We've seen this when we don't have enough CPU and with some of our heavy duty CICS transactions are doing a lot of fetches. Our definition of a heavy transaction is one which has to do several hundred thousand fetches (if not several million). A cics transaction which runs sql that uses a lot of getpages but only returns a few rows will run fine. It's the fetches that seem to cause the problem. [...]
4026 60 32_Re: DPMODE & Priority of db2 tcb11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US31_Thu, 30 Sep 2004 14:32:21 -0500432_- Another option is to offload work from the QR tcb. You may want to look into running the cics transaction as threadsafe. An excellent writeup is the redbook "selected performance topics for DB2 version 7" sg24-6894-00
-----Original Message----- From: Humphris,Richard P. [mailto:Richard.Humphris@CNA.COM] Sent: Thursday, September 30, 2004 2:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DPMODE & Priority of db2 tcb [...]
4087 27 54_Re: Finding last occurrence of a substring in a string16_Philippe Godfrin26_philippe_godfrin@MEDCO.COM31_Thu, 30 Sep 2004 14:33:28 -0500468_- Hi Ken,
How's about something like:
srch_length = LENGTH() start loop until found end_search = RIGHT(,srch_length); if POSSTR(end_search) <> 0 then found else srch_length = srch_length + 1 ; end loop
Couldn't test this, but the idea is that you keep increasing the length in the RIGHT function until the entire variable is returned in the RIGHT function. I think that'll give you the rightmost occurrence. [...]
4115 63 38_Reorg Unload Only / Load Format Unload14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 30 Sep 2004 15:45:47 -0400332_US-ASCII DB2v7 on z/OS 1.4
We have run into a problem that has us stumped. If anyone can help us, we would appreciate it. We are trying to port an application from another company into one of our subsystems.
1. The other company sent us their DDL (about 400 tables, one table per tablespace), and we have run it. [...]
4179 117 42_Re: Reorg Unload Only / Load Format Unload12_michael bell21_mbell11a1@VERIZON.NET31_Thu, 30 Sep 2004 15:09:27 -0500581_Windows-1252 From the manual UNLOAD Specifies whether the utility job is to continue processing or end after the data is unloaded. Unless you specify UNLOAD EXTERNAL, data can be reloaded only into the same table and table space (as defined in the DB2 catalog) on the same subsystem. (This does not preclude VSAM redefinition during UNLOAD PAUSE.) You must specify UNLOAD ONLY for the data set to be in a format that is compatible with the FORMAT UNLOAD option of LOAD. However, with LOAD you can load the data only into the same object from which it is unloaded. This option [...]
4297 45 25_Re: DSMAX & DBM1 Activity19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 30 Sep 2004 15:20:15 -0500412_us-ascii Hi all,
Just thought I'd bring this up again. Even though logically there should be "no correlation" between getpages and DBM1 cpu that's not what I see. Granted I'm only using a limited sample but I've seen this before. The following statistical correlation came from 39 half hour observations I used over the first three "working" days of this week. And they show the following behaviour: [...]
4343 34 32_Re: DPMODE & Priority of db2 tcb19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 30 Sep 2004 15:53:50 -0500642_us-ascii Hi Rich,
At my shop I'm on the DB2 side. And after talking to the performance and cics groups I found out they are already aware of threadsafe threads. But you're right the redbook is excellent information and is really the way to go.
Thanks for the info.
Rich Humphris
E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain confidential and/or legally privileged information. If you are not the intended recipient of this message or if this message has been addressed to you in error, please immediately alert [...]
4378 40 54_Re: Finding last occurrence of a substring in a string19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 30 Sep 2004 16:38:56 -0500496_us-ascii I can think of one way, but it's probably terribly inefficient.
If you had a character string 30 long and you want to match 3 characters you'd have to do 30-3+1=28 locate commands like: Select max(locate('ABC',string,1), locate('ABC',string,2), locate('ABC',string,3), .... Locate('ABC',string,28)) from ... Where posstr('ABC',string) > 0
But if this is more than a quick and dirty exercise, maybe you should write a user defined function to do this more efficiently. [...]
4419 17 42_Re: Reorg Unload Only / Load Format Unload9_Jim Ruddy18_jaruddy@US.IBM.COM31_Thu, 30 Sep 2004 16:46:58 -0500696_- The output from REORG UNLOAD ONLY is useful for loading back into the exact same table ONLY.
Have them send you REORG UNLOAD EXTERNAL output and the generated LOAD statement.
Jim Ruddy DB2 for z/OS Development
--------------------------------------------------------------------------------- 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
4437 142 42_Re: Reorg Unload Only / Load Format Unload13_Bright, Randy20_Randy_Bright@BMC.COM31_Thu, 30 Sep 2004 16:58:57 -0500393_- Mark, if you happen to have LoadPlus for DB2, you can use the WHEN TABLE=obid clause in the LOAD control statement to load the FORMAT UNLOAD data into another table defined EXACTLY like the original. If you have LoadPlus, you can contact me offline about specifics, if you want.
Randy Bright Architect, DB2 Utilities BMC Software, Inc. Randy_Bright@BMC.COM (800) 841-2031 x26014 [...]
4580 91 54_Re: Finding last occurrence of a substring in a string14_Peter Backlund21_BacklundDB2@TELIA.COM30_Fri, 1 Oct 2004 00:10:05 +0200441_- Here is a function which returns the position of the last numeric character in a string. It only works in DB2 for Linux, UNIX, Windows
Peter
create function ln(in varchar(255)) returns smallint begin atomic declare i smallint; set i=length(rtrim(in)); while locate(substr(in, i, 1),'0123456789')=0 and i >1 do set i=i-1; end while; if i=1 and locate(substr(in, 1, 1),'0123456789')=0 then set i=0; end if; return(i); end [...]
4672 87 54_Re: Finding last occurrence of a substring in a string10_Shyam Peri24_shyamperi@OFSYONLINE.COM30_Fri, 1 Oct 2004 04:36:25 +0530
4760 65 25_Re: DSMAX & DBM1 Activity35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 30 Sep 2004 21:05:49 -0400449_iso-8859-1 Rich,
This is some interesting data, and certainly some of the correlations raise a few questions. I don't know if all your data is from production systems, or you also have a sandbox for testing things. If you have a sandbox, can you test the correlations for getpage cpu costs at the application level - where no data is returned to the application program? We used to have a very direct, and easily measurable, cost there. [...]
4826 80 24_Re: LISTDEF and TEMPLATE22_Technoville Consulting26_db2dba_certified@YAHOO.COM31_Thu, 30 Sep 2004 20:27:46 -0700469_us-ascii This is true. Please remember that "TEMPLATE" dataset is good to replace the DDNAME that is used in utility control cards but not for the DSN NAME. Same is the case with RECOVER TOCOPY.
--Database Solutions Port.
"McCardle, Corey" wrote: The problem that I think I'm having is that the UNLOAD utility with FROMCOPY/FROMCOPYDDN requires the dataset name for the specific IC and you can't use (0), TEMPLATE, or LIST. [...]
4907 35 30_-430 when executing a function13_Arthur Sannen32_arthur.sannen@SYDNEYWATER.COM.AU31_Thu, 30 Sep 2004 23:09:47 -0500472_- Dear Listers,
Env: DB2 v7.1, zOS 1.4
I have created a function which retrieves a particular code for a property number. When I execute it for one property number it works fine eg SELECT N_PROP, Z309.FGETPROPSYSTAREA(N_PROP,'P') FROM Z309.TPROPERTY WHERE N_PROP = 3100005;
However, when the result set is greater than one row, I get a -430. SELECT N_PROP, Z309.FGETPROPSYSTAREA(N_PROP,'P') FROM Z309.TPROPERTY WHERE N_PROP in (3100005,3100045); [...]