1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l February 2001, week 2
2 23 52_Reg. Dropping a Partitioned tables Clustering Index.15_Kannan, Perumal22_perumal.kannan@EDS.COM30_Wed, 7 Feb 2001 23:58:31 -0600290_- Hello All, How can I alter a partitioned tables cluster index? Shall I use direct alter? When I try to drop the partitioned table space tables cluster index I am getting -667. What should I do if I want to alter this index? Thanks for your input in advance.
Have a nice time! [...]
26 128 43_Re: DB2EUG: Buffer Pool Tuning / UDB on AIX11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Thu, 8 Feb 2001 02:14:22 -0500719_iso-8859-1 Bill, Pierre,
I agree with putting SYSCATSPACE into its own CATLGBP bufferpool. There's very detailed paper on DB2 UDB Bufferpool Tuning at: http://www.idug.org/member/journal/mar00/optimize.cfm
In the above article, I strongly advocated putting TEMPSPACE1 into its own bufferpool, and I believe this is still a very good practice for OLTP applications. While preparing "SORT is a FOUR Letter Word" for IDUG North America ( http://www.idug.org/ ), we conducted several benchmark performance tests. One test showed a 5% increase in CPU burn with 5% slower performance by having TEMPSPACE1 in its own bufferpool. Having the right physical tablespace, bufferpool, and heap configurations can [...]
155 192 43_Re: regarding load.... need immediate reply15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 8 Feb 2001 08:45:58 +0100536_iso-8859-1 Hello Kumar,
Consider this, if the table you want to load already has data and you want to replace this, you need to delete this first and then do the LOAD RESUME YES. It's the safest option to use RESUME YES. When your table is empty there is no need to use RESUME NO as Steve mentioned, what doesn't say it cannot be used. Rule of thumb; More than one table in a tablespace and willing to replace data in one table - delete the data and use LOAD RESUME YES. Only one table per tablespace and willing to replace [...]
348 56 56_AW: Reg. Dropping a Partitioned tables Clustering Index.12_Thomas Weber23_thomas.weber@LDS.NRW.DE30_Thu, 8 Feb 2001 08:52:45 +0100649_iso-8859-1 Hi Kannan,
A partitioned index can only by dropped by dropping the tablespace (see SQL Reference chap. 6). Why don't you use the ALTER INDEX statement. As far as I know you can even rebalance partitions by altering the index since v6.
HTH
Thomas
> -----Ursprüngliche Nachricht----- > Von: Kannan, Perumal [mailto:perumal.kannan@EDS.COM] > Gesendet am: Donnerstag, 8. Februar 2001 06:59 > An: DB2-L@RYCI.COM > Betreff: Reg. Dropping a Partitioned tables Clustering Index. > > Hello All, > How can I alter a partitioned tables cluster index? Shall I use direct > alter? > When I try to drop the partitioned [...]
405 160 20_AW: Dynamic prefetch0_20_Gerhard.Heiss@RUV.DE30_Thu, 8 Feb 2001 09:33:57 +0100392_iso-8859-1 Terry, Tim, Sanjeev,
Accesspath is: ACCESSTYPE MATCHCOLS PREFETCH --+---------+-------- -----+--- I 1
The intension for optimize for 1 rows was to get rid off seq. prefetch, what worked obviously at bindtime. But at runtime, DB2 doesn't care, so i would like to turn off dyn. prefetch, but I don't know how. Maybe Tims method (separate BP) is the only solution. [...]
566 56 56_Re: Reg. Dropping a Partitioned tables Clustering Index.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 8 Feb 2001 14:28:38 +0530453_- Kannan, For DB2 for OS390. You can only use ALTER for partitioning key if u are in V6 or V5 (with some APAR, u can find in Manuals). The new ZPARM parameter(PARTKEYU-->Yes/No) is also added to enable this feature. You can not drop the clustering index of a partitioned tablespace. You need to drop the tablespace.This is the reason for -667. If you are not in V6 or V5 (with required APAR), you have to drop and recreate/redefine the tablespace. [...]
623 210 20_Re: Dynamic prefetch16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 8 Feb 2001 14:38:44 +0530560_iso-8859-1 Could you check the value of hostvar for the runtime ?.... the case for which the query is doing sequential detection. Is this the case for all the values. What is the number of qualifying records for that particular value of the host variable.
If at all you are moving it to separate BP and disabling the sequential detection by making VPSEQT to 0, please check the synchronous I/O. There are 3 chances of high getpages ....1) It is required 2) It is redundant 3) required but can't be derived without redundant for better performance. [...]
834 27 39_Re: Retrieving a ROW ID after an insert15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Thu, 8 Feb 2001 03:03:40 -0600558_- Rick
We are using 3 tables with IDENTITY GENERATED BY DEFAULT since a couple of months in production using RI with these columns without problems. We selected BY DEFAULT, because we need the index on that identity columns for access, so it doesn't hurt as. Well I know, that if we have to unload, drop, recreate an load these tables, we must first look up the MAXASSIGNEDVAL in the appropriate catalog table and use this for the START option. I hope that in the near future a third party tool as PLATINUM'S catalog manager will do that for us. [...]
862 55 32_Re: Performance Text Suggestions11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Thu, 8 Feb 2001 09:19:38 -0000417_- Gene,
Personally I think you cannot go far wrong with 'DB2 High Performance Design and Tuning' by the esteemed Richard Yevich and Susan Lawson, ISBN 0-13-203795-5. As they say in the adverts ..... available in all good bookshops.
-----Original Message----- From: GENE TILELLI [mailto:geetee52@USA.NET] Sent: 07 February 2001 18:43 To: DB2-L@RYCI.COM Subject: Performance Text Suggestions [...]
918 18 27_Re: Recover tablespace.....15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Thu, 8 Feb 2001 03:38:47 -0600408_- My suggestions are (supposed you know when the delete took place):
1. Run the REPORT RECOVER Utility and find the UOW just before the time the user deleted the rows 2. Insert the rows of TABL1 into an intermediate table 3. Recover the tablespace to the point you extracted from the REPORT- Utility. 4. Delete the rows from table TABL1. 5. Insert the rows into TABL1 from the intermediate table [...]
937 118 55_Re: Unsuccessful update of a varchar column through ADO11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Thu, 8 Feb 2001 11:50:11 +0200628_iso-8859-7 Andy, We used the code you supplied us and everything worked fine!!!!However,it was not neccessary to break the large string of data into smaller. Thanks a lot for the immediate reply and the actual help!!! (I love that list!!!!)
Regards,
Popy A. Dimoka Athens,Greece
> ---------- > From: Andy Seuffert[SMTP:aseuffert@NEONSYS.COM] > Reply To: DB2 Data Base Discussion List > Sent: ÔåôÜñôç, 7 Öåâñïõáñßïõ 2001 6:54 ìì > To: DB2-L@RYCI.COM > Subject: Re: Unsuccessful update of a varchar column through ADO > > Popy, > as you provided in your explanation, DB2 has a limit of 254 bytes for a > [...]
1056 164 24_Re: AW: Dynamic prefetch10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Thu, 8 Feb 2001 04:07:57 -0600437_ISO-8859-1 Gerhard,
If you succeed in turning off asynchronous prefetch, then it is likely that (in instances like the one you provided the BP report for) your query will require a synchronous read of every leaf page in your index. Is this what you want?
I really think you need to investigate the contents of your host variable when the execution of your query produces results such as those shown in your BP report. [...]
1221 23 41_"Messy" query results from Command Center11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Thu, 8 Feb 2001 12:25:33 +0200337_- Hello List, we are using DB2 UDB for NT V6 and after apllying FP6,the results of queries on Command Center seem rather "messy",they cannot be read.The same problem also appears with Version 7 FP1 ,Version 7 FP2.V6 workstations with older FPs,FP4 for example ,don't have that problem.Has anybody come across with such a problem??? [...]
1245 194 29_Re: DB2 Connect - TCPIP Error11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Thu, 8 Feb 2001 12:53:15 +0200652_iso-8859-7 Hello Jaap, i read your mail and it was just the exact description of the problem we faced yesterday!From the AdminTool on the Host(DB2 for OS/390 v6),i could see no distributed threads,but from the Control Center of the NT Machine running a Java application,there were many incative threads,which we cancelled,but after that,they were still there (shown from the Control Center)and couldn't be cancelled-the message was saying that those threads are already cancelled!As the users running the Java App couldn't connect to the Database on the Host,we finally had to stop DDF mode(Force) and start it again.So,i guess the workaround you [...]
1440 239 20_AW: Dynamic prefetch0_20_Gerhard.Heiss@RUV.DE30_Thu, 8 Feb 2001 13:08:40 +0100633_iso-8859-1 I can hardly believe that any programmer allows wildcards at the beginning of a hostvariable. But this would be a simple explanation.
Thanks to all
Gerhard
-----Ursprüngliche Nachricht----- Von: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Gesendet am: Donnerstag, 8. Februar 2001 10:09 An: DB2-L@RYCI.COM Betreff: Re: Dynamic prefetch
Could you check the value of hostvar for the runtime ?.... the case for which the query is doing sequential detection. Is this the case for all the values. What is the number of qualifying records for that particular value of the host variable. [...]
1680 13 56_Re: Reg. Dropping a Partitioned tables Clustering Index.14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Thu, 8 Feb 2001 06:45:51 -0600372_iso-8859-1 You cannot drop the partitioning index. If you want to change the columns in the index you have to drop the tablespace.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1694 76 27_Re: Recover tablespace.....14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 8 Feb 2001 12:52:42 -0000505_iso-8859-1 ... and next time someone asks about multi-table tablespaces versus single table tablespaces you will have a compelling answer for them!!
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Di Carlo, Donna [mailto:Donna_Bermender@BMC.COM] Sent: 07 February 2001 19:02 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Recover tablespace..... [...]
1771 96 27_Re: Recover tablespace.....14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 8 Feb 2001 12:54:21 -0000469_iso-8859-1 Shouldn't there be a DELETE FROM TBL1 before the re-insert of the data from the temporary table??
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Sent: 08 February 2001 05:05 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Recover tablespace..... [...]
1868 48 39_Re: DB2 Active logs-greater than 2 gigs23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 8 Feb 2001 13:01:48 -0000572_iso-8859-1 have 2 gig logs here .. but go to dasd then tape ..
sorry
Les
-----Original Message----- From: KOA DAVID S. (dbm1dxk) [mailto:dbm1dxk@UPS.COM] Sent: Wednesday, February 07, 2001 7:33 PM To: DB2-L@RYCI.COM Subject: DB2 Active logs-greater than 2 gigs
I am currently in the process of testing DB2 active logs sized between 2-4 gigs archiving to tape(yes it's possible). I have been told that other customers have successfully done this. If you are one of them, can you let me know your experience and any recommendations. [...]
1917 47 17_Direct Access Row16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 8 Feb 2001 17:50:09 +0530534_- Hi All,
I was going thru the thread ' Retrieving a ROW ID after an insert' and found it quite interesting and it really explained good concept and clarified doubts(esp. by James). It raised one quesiton in my mind which i thought i would put in some different thread rather continuing in the same. 1) What does DB2 brings in the buffer pool when Direct Access Row access path is used ? I am sure it is not the row....but if it is still page the only difference i find is the processing time. However that is not less. I [...]
1965 41 31_Definition Transition Variables12_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 8 Feb 2001 14:36:43 +0100761_iso-8859-1 Hello DB2-Listers.
I am just dealing with Triggers in DB2 for OS/390 and I have to declare this powerful feature to our programmers....
And therefore I am looking for a good and simple definition for the term "Transition Variables".
Anyone out there with an advice ?
Thanks in advance.
With kind regards - mit freundlichen Grüssen, G e o r g H . P e t e r DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german [...]
2007 88 21_Re: Direct Access Row20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 8 Feb 2001 08:52:19 -0500552_us-ascii Hi Sanjeev
If you look the way events takes places, there is no extra ordinary processing DB2 has to do to bring the row using direct access.
I would expect the following to happen. DB2 would issue request to Buffer Manager. BM would search for the page (it is always page not a row) in VP (GBP, if page is invalidated), HP and then DASD. Locking is not affected by access path but by the parameters LOCKSIZE & LOCKMAX (apart from it special conditions may apply such as latches, lock escalation or lock promotion etc.etc.) [...]
2096 217 29_Re: DB2 Connect - TCPIP Error15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 8 Feb 2001 14:59:31 +0100519_iso-8859-7 Hi Popy,
What happened at our shop was the following; At the NT-macine we got a communication error becauase a thread remained idle. We were not able to find out why this thread remained idle. After 1800 secs the thread is cancelled by DB2 because of zparm-settings. OS/390 signals the NT with a message (FIN) and closes his (the sending) part of the full-duplex connection. NT does receive this message but doesn't act upon it. NT doesn't close his (sending) part of the full-duplex connection. [...]
2314 91 35_Re: Definition Transition Variables14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 8 Feb 2001 14:30:16 -0000566_iso-8859-1 A transition variable is a little like a correlation name. It gives you an 'alias' for a table or column name.
In the case of a trigger, you code transition variable names to be able to refer to both the OLD and NEW columns/rows in the same SQL statement.
e.g.:
CREATE TRIGGER TRIGB_01 AFTER UPDATE OF ROW_COUNT ON TABLE_B REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW MODE DB2SQL INSERT INTO TABLE_C VALUES (CURRENT TIMESTAMP, OLD.STATEMENT_COUNT, NEW.STATEMENT_COUNT, OLD.ROW_COUNT, NEW.ROW_COUNT, 'UPDATE OF ROW_COUNT'); [...]
2406 104 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 8 Feb 2001 10:07:32 -0500557_- My posting was rejected yesterday because of its size, so I am trying to send it again
> James/Venkat, > > I have been doing some additional testing and you rightly pointed out that > 'one can use the old ROWID value to access a row, even after the table has > been reorged'. But, the ROWID value does change. What changes is the 5 > right most bytes of the ROWID which is nothing but the RID. For example > take a look at following sample ROWID value from one of my test table > tb11. > X''275AA6647FA2D83B4E69012A108C01 0005 [...]
2511 21 28_oracle 2 db2 via db2 connect10_Shery Hepp17_schepp@SRPNET.COM30_Thu, 8 Feb 2001 08:20:51 -0700317_ISO-8859-1 Greetings fellow list serve members- We currently have a project in oracle that needs to talk to db2 Os/390. We would like to use DB2 connect for this rather than install an additional gateway. Are they any other shops out there successfully communicating from oracle to db2 Os/390 using DB2 connect? [...]
2533 131 39_Re: Retrieving a ROW ID after an insert13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Thu, 8 Feb 2001 09:21:54 -0600572_iso-8859-1 Sanjay,
You are correct in your assumptions regarding ROWID (ie. uniquely generated key + the RID). This is based on the information we have received from IBM, and have subsequently been presenting in our V6 transition courses for some time.
Regards Terry Purcell Yevich Lawson & Assoc www.ylassoc.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Jain, Sanjay (Exchange) Sent: Thursday, February 08, 2001 9:08 AM To: DB2-L@RYCI.COM Subject: Re: Retrieving a ROW ID after an insert [...]
2665 79 31_Buffer Pool Tuning / UDB on AIX11_James Drewe20_James.Drewe@AEXP.COM30_Thu, 8 Feb 2001 08:26:03 -0700551_iso-8859-1 Bill
I have had the same question as well. Based on what I have received back from IBM'ers, I think it is a marketing issue, not a technical issue. If your company has the staff to modify the settings and manage buffer pool usage on an on-going basis (not a trivial task), then you will derive significant benefit. However, if you are part of a "mom and pop" company trying to decide on SQL-Server, Oracle, or UDB, there isn't a question of whether you would need a bigger staff to handle UDB: when it comes out of the box, one [...]
2745 213 20_Re: Dynamic prefetch20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 8 Feb 2001 10:46:02 -0500444_iso-8859-1 I think, you missed the point.
Why does any one want to turn off sequential prefetch? - Usually a scan (tablespace or index) is reflected as sequential or list prefetch, hence prefetch appears bad not because it is bad but because scan is sometimes considered bad (there are always exceptions). When scan is chosen by optimizer, prefetch is a reflection. So people generally think prefetch is bad instead of scan is bad. [...]
2959 222 43_Re: regarding load.... need immediate reply17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Thu, 8 Feb 2001 09:46:45 -0600643_- Hello Folks,
Thank you very much for your help. just i followed the steps send by all of you to load the table. I used RESUME YES option and it works fine.
Thanks Kumar
>From: "Slot, JP (Jaap)" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: regarding load.... need immediate reply >Date: Thu, 8 Feb 2001 08:45:58 +0100 > >Hello Kumar, > >Consider this, if the table you want to load already has data and you want >to replace this, you need to delete this first and then do the LOAD RESUME >YES. >It's the safest [...]
3182 64 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 8 Feb 2001 11:01:40 -0500612_- Walter,
Here is a sample partitoning scenario using ROWID from the manual.
REATE INDEX EMPARTIX ON EMP(Emp_Rowid) CLUSTER (PART 1 VALUES(X'3FFF'), PART 2 VALUES(X'7FFF'), PART 3 VALUES(X'BFFF'), PART 4 VALUES(X'FFFF'));
As long as the the partitioning index is created on the first 15 bytes of ROWID column, the reorg will keep them in the same partition. The above example only used the first 2 bytes of ROWID column for partitioning. As my testing has shown that reorg does not change the first 15 bytes of the ROWID, so the answer is NO, the reorg will not scamble the rows across [...]
3247 205 20_Re: Dynamic prefetch24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Thu, 8 Feb 2001 08:38:19 -0800589_iso-8859-1 Hi Gerhard!
While I agree that it is generally a bad idea to turn off pre-fetch, there are occasions where it may be necessary. One alternative to isolating the tablespace into a separate buffer pool is to issue commits frequently enough that sequential detection is disabled. A commit resets the counters used for sequential detection, which requires that at least 5 of the last 8 pages appear in sequence (more or less; details are in the manuals). So, in order for this to work, you would need to commit once for every 8 pages referenced. A bit tricky to adjust, [...]
3453 139 39_Re: Retrieving a ROW ID after an insert20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 8 Feb 2001 11:39:29 -0500649_iso-8859-1 Sanjay
You mentioned about those 15 bytes and remember recalling one IBM folk telling that they are generated from STCK but shuffled using some algorithm (something like CONTOKEN, which is SLL 3 bits for a full word value). it would be interesting to see how they are reshuffled.
Regards, -Venkat Pillay
> -----Original Message----- > From: Jain, Sanjay (Exchange) [SMTP:sanjayjain@BEAR.COM] > Sent: Thursday, February 08, 2001 10:08 AM > To: DB2-L@RYCI.COM > Subject: Re: Retrieving a ROW ID after an insert > > My posting was rejected yesterday because of its size, so I am trying to > send it [...]
3593 109 16_Trigger Creation13_Brunner Don J21_Don.J.Brunner@IRS.GOV30_Thu, 8 Feb 2001 11:59:20 -0500558_iso-8859-1 One of our developers was having a problem creating a Trigger. I've since resolved the problem but I'd like to have someone explain why the particular resolution was necessary.
The trigger involved is an AFTER INSERT that contains three update statements. The developer executed the CREATE TRIGGER statement in SPUFI and received an SQLCODE of 0, the same is true on the commit statement that followed. A check of SYSIBM.SYSTRIGGERS revealed that the Trigger didn't get created. Needless to say, that lead to a lot of head scratching. [...]
3703 84 16_Re: Schema names13_Umair Hussain25_umair_hussain@HOTMAIL.COM30_Thu, 8 Feb 2001 11:11:05 -0600431_- Could create an alias for each table with the correct schema name ... this is just a work around ... or you could export and import with the correct schema name.
regards Umair
Umair Hussain******************************** DBA Consultant * DB2 UDB, Oracle, Sybase, DataJoiner * IBM Certified Solution Expert - DB2 UDB DBA * IBM Certified AIX System Administrator * ********|All Disclaimers Apply|************** [...]
3788 25 25_DDF Abend 0C4 - Version 611_Paul Cullen24_PCULLEN@FRANKLINMINT.COM30_Thu, 8 Feb 2001 11:02:21 -0600628_- Our installation recently converted to Release 6 of DB2. Since that time one set of jobs, utilizing DDF, have abended with an S0C4 in the requesting address space. The abend is intermittent, and seems to result from submitting more than one job at a time. Following the failure, the job is submitted later without a problem. In researching the MVS trace, the S0C4 appears to occur in VTAM code, redispatching the work that was suspended. Specifically, it seems to address modules ISTAPCSU, at UW55797, where it picks up the 0C4, then DSNLVRPL. The formal symptom shows: Title : DB2E, ABND=0C4,LOC=DSNLILLM.DSNLVRPL+05F8, [...]
3814 15 11_Who did it?15_Wayne Arrington33_WAYNE.ARRINGTON@CUSTOMS.TREAS.GOV30_Thu, 8 Feb 2001 12:17:41 -0500312_US-ASCII Hey group,
How are I found out who dropped a database?
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3830 47 15_Help on a query20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM30_Thu, 8 Feb 2001 12:34:19 -0500362_iso-8859-1 Ok I'm new to this so don't kill me. Please help. I have this query
SELECT POLICY_TRANSACTION FROM DEVL.TW4T0 WHERE POLICY_TRANSACTION < '9999999999' AND POLICY_NUMBER = '0070003369' AND TERM_IDENT = '001' AND ( ( EFFECTIVE_DATE = '1999-01-01' AND SEQUENCE_ALPHA <= '001' ) OR EFFECTIVE_DATE < '1999-01-01' ) AND POLICY_TRAN_STATUS <> 'R' [...]
3878 24 22_DB2 Estimator Question10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Thu, 8 Feb 2001 11:38:51 -0600315_us-ascii All,
We use DB2 Estimator V 7 and were looking to give estimates with & without RI. We ran into a snag trying to find a way to emulate referential integrity in Estimator? As far as we can see there are no facilities to place foreign keys on tables available. Is it a hidden feature or a trick? [...]
3903 48 15_Re: Who did it?12_Susan Lawson22_lawson_susan@YAHOO.COM30_Thu, 8 Feb 2001 09:41:47 -0800563_us-ascii Wayne,
I believe Audit trace class 3 IFCID 142 should give you the information regarding who dropped a database.
Regards, Susan Lawson www.ylassoc.com
--- Wayne Arrington wrote: > Hey group, > > How are I found out who dropped a database? > > ================================================ > To change your subscription options or to cancel > your subscription visit the DB2-L webpage at > http://www.ryci.com/db2-l. The owners of the list > can be reached at DB2-L-REQUEST@RYCI.COM. > > > [...]
3952 17 27_on line reorg mapping table14_Chris Michaels35_Christopher_A_Michaels@VANGUARD.COM30_Thu, 8 Feb 2001 12:53:15 -0500461_us-ascii DB2 V6, OS/390... Does anyone have any experience with online reorg (shrlevel change), specifically with the mapping table. My question really is how large to make it. IBM gives no recommendations.
Chris
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3970 33 31_Re: on line reorg mapping table15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US30_Thu, 8 Feb 2001 12:06:20 -0600544_iso-8859-1 Refer to "Before running REORG TABLESPACE" Chapter 2-16 REORG TABLESPACE in the manual Utility Guide and Reference.
-----Original Message----- From: Chris Michaels [mailto:Christopher_A_Michaels@VANGUARD.COM] Sent: Thursday, February 08, 2001 11:53 AM To: DB2-L@RYCI.COM Subject: on line reorg mapping table
DB2 V6, OS/390... Does anyone have any experience with online reorg (shrlevel change), specifically with the mapping table. My question really is how large to make it. IBM gives no recommendations. [...]
4004 62 19_Re: Help on a query15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US30_Thu, 8 Feb 2001 12:12:10 -0600372_iso-8859-1 If you are wondering why all columns of index#2 is not being used, it is the OR on the EFFECTIVE_DATE....any OR on a column does not encourage its use in an index...
-----Original Message----- From: Westcott-Dryer, Lisa [mailto:Lisa_Westcott-Dryer@FOREMOST.COM] Sent: Thursday, February 08, 2001 11:34 AM To: DB2-L@RYCI.COM Subject: Help on a query [...]
4067 41 31_Re: on line reorg mapping table16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 8 Feb 2001 13:27:37 -0500494_iso-8859-1 Size the tablespace minimum. Use the index space formulas in the admin guide for a Unique index with a keylength of 21. A one million row table would need the mapping table index to be about 570 tracks of 3390.
regards,
eric pearson NS ITO Database Support
-----Original Message----- From: Chris Michaels [mailto:Christopher_A_Michaels@VANGUARD.COM] Sent: Thursday, February 08, 2001 12:53 PM To: DB2-L@RYCI.COM Subject: on line reorg mapping table [...]
4109 85 19_Re: Help on a query0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 8 Feb 2001 14:35:17 -0600557_us-ascii Why would someone want to 'kill' you for asking a question on the list ... ?
A 'simple' explanation is that the LESS THAN condition on POLICY_TRANSACTION means that DB2 would have to look at very many (every ?) INDEX 1 entry when trying to find exact matches for the next two fields in the index. On the other hand, because you provide only one value (the EQUALS check) for both of the first 2 columns in INDEX 2, DB2 can go directly to those desired entries via INDEX 2, and then filter out any that don't match the POLICY_TRANSACTION [...]
4195 66 19_Re: Help on a query10_Shery Hepp17_schepp@SRPNET.COM30_Thu, 8 Feb 2001 12:20:36 -0700379_iso-8859-1 Lisa- I'll take a stab at this- my quess is you want to use index #1 instead of #2. I think that the optimizer is choosing index #2 due to the = predicates in the first 2 columns. Have you thought of changing the < on policy_tran to a between with a low and high value? Also you don't mention what version of DB2 you are on- also I'm assuming you are db2 os/390. [...]
4262 111 19_Re: Help on a query14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT30_Thu, 8 Feb 2001 19:21:05 +0100577_ISO-8859-1 Hi Lisa.
Don't worry about to be new DBA, the problems are the same both new and old DBAs.
In this case you have:
POLICY_TRANSACTION < '9999999999' not good because all the POLICY_TRANSACTION are less than 9999999999
POLICY_NUMBER = '0070003369' ok you target one POLICY_NUMBER
TERM_IDENT = '001' ok you target one TERM_IDENT
( ( EFFECTIVE_DATE = '1999-01-01' AND SEQUENCE_ALPHA <= '001' ) OR EFFECTIVE_DATE < '1999-01-01' ) not good because EFFECTIVE_DATE can't be used and less SEQUENCE_ALPHA (OR plus another field) [...]
4374 79 47_VSAM files being created in noncontiguous space0_32_Gordon.P.PROUTY@ODOT.STATE.OR.US30_Thu, 8 Feb 2001 11:08:07 -0800547_iso-8859-1 We are on DB2-V6 for OS/390. After upgrading to the OS390 V-10. We have discovered what appears to be the loss of not being able to allocate a DB2 VSAM file in contiguous space. Our experience in the past is that, whenever we load or re-org a DB2 table, if there is not enough contiguous space to allocate the file, the job fails immediately. Now it appears that if there is enough space on the volume(Not contiguous) it will split the VSAM file to a maximum of three times. We are not sure of the consequences of this, other than [...]
4454 59 31_Re: on line reorg mapping table12_Susan Lawson22_lawson_susan@YAHOO.COM30_Thu, 8 Feb 2001 10:11:59 -0800455_us-ascii Chris,
Only the index over the mapping table is filled, therefore it is sufficient to allocated as little space as possible to the mapping table. For this index you should assign at least 1.1 * Number of row in TS * 27 bytes assuming that the entire table space is being reorganized. For only reorgs of partitions of the table space the number of rows in the partitions is to be used instead of the number of rows in the table space. [...]
4514 109 19_Re: Help on a query0_24_db46@DAIMLERCHRYSLER.COM30_Thu, 8 Feb 2001 15:52:25 -0500276_us-ascii Well because you give two 'equal' value predicates so DB2 can go right to those rows. Unique #1 first column is a '<' with seemingly all the values. So DB2 decided to do a index lookup on the first two columns of Unique #2 instead of an index scan on Unique #1. [...]
4624 89 19_Re: Help on a query14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU30_Fri, 9 Feb 2001 07:55:18 +1100678_iso-8859-1 **************************************************************** IMPORTANT
The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...]
4714 69 19_Re: Help on a query14_Greg DiGiorgio29_gdigior@CI.NEWPORT-NEWS.VA.US30_Thu, 8 Feb 2001 14:02:54 -0800591_us-ascii "Westcott-Dryer, Lisa" wrote:
> Ok I'm new to this so don't kill me. Please help. I have this query > > SELECT POLICY_TRANSACTION > FROM DEVL.TW4T0 > WHERE POLICY_TRANSACTION < '9999999999' > AND POLICY_NUMBER = '0070003369' > AND TERM_IDENT = '001' > AND ( ( EFFECTIVE_DATE = '1999-01-01' > AND SEQUENCE_ALPHA <= '001' ) > OR EFFECTIVE_DATE < '1999-01-01' ) > AND POLICY_TRAN_STATUS <> 'R' > > The table in question TW4T0 has two indexes set on it. > They are: > > #1 Unique > POLICY_TRANSACTION A > POLICY_NUMBER A > TERM_IDENT A > EFFECTIVE_DATE A > SEQUENCE_ALPHA A [...]
4784 19 18_Candle QuickChange14_Toppins, Smike21_smike.toppins@GWL.COM30_Thu, 8 Feb 2001 14:18:45 -0700448_- Would someone be willing to answer some questions (off-line) about some Candle tools usage; specifically QuickChange? TIA.
SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
4804 47 15_Re: Who did it?13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Thu, 8 Feb 2001 12:49:35 -0600548_iso-8859-1 Hi Wayne,
If this is after the fact... when the DROP was executed, all of the rows in the DB2 Catalog used to describe the database and its objects are deleted (SYSIBM.SYSDATABASE, SYSTABLESPACE, SYSTABLESPACEPART, SYSTABLES, SYSCOLUMNS, etc.). You can use DSN1LOGP to print the log records associated with the deletes. Each one of these records have a Unit Of Recovery RBA/LRSN associated with the DROP. You can then print the Unit Of Recovery record which contains the auth id, correlation id (job name), etc. of the DROP. [...]
4852 192 15_Re: Who did it?14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Thu, 8 Feb 2001 14:37:43 -0800657_iso-8859-1 Wayne, if you are using BMC or CA tools you can check the drop recovery log. Just one possible solution. --Phil
-----Original Message----- From: McDonald, Ken To: DB2-L@RYCI.COM Sent: 2/8/01 10:49 AM Subject: Re: Who did it?
Hi Wayne,
If this is after the fact... when the DROP was executed, all of the rows in the DB2 Catalog used to describe the database and its objects are deleted (SYSIBM.SYSDATABASE, SYSTABLESPACE, SYSTABLESPACEPART, SYSTABLES, SYSCOLUMNS, etc.). You can use DSN1LOGP to print the log records associated with the deletes. Each one of these records have a Unit Of Recovery RBA/LRSN associated with [...]
5045 101 20_Re: Trigger Creation14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 9 Feb 2001 10:07:31 +1100781_iso-8859-1 PQ40202: "PROBLEM SUMMARY: **************************************************************** * USERS AFFECTED: All DB2 users of triggers. * **************************************************************** * PROBLEM DESCRIPTION: When the bind of an implicitly * * created trigger package fails, * * SQLCODE = 0 even though the * * trigger was not created. * **************************************************************** * RECOMMENDATION: BLANKS * **************************************************************** The CREATE TRIGGER statement causes DB2 to automatically create a trigger package. When the bind of a trigger package failed, the SQLCA was not being formatted with the error so the user received an SQLCODE = 0 even though the trigger was not created. [...]
5147 47 31_Re: on line reorg mapping table12_Troy Coleman19_Colematr@MEIJER.COM30_Thu, 8 Feb 2001 13:06:50 -0500272_US-ASCII I set the mapping table up with one for each database. I sized it to support the largest table in my database. So if my database has a table with 5 Million rows then my mapping table is sized to support 5 Million Rids.
Troy Coleman Coleman Consulting, Inc.
5195 53 19_Re: Help on a query12_Troy Coleman19_Colematr@MEIJER.COM30_Thu, 8 Feb 2001 13:14:02 -0500411_US-ASCII The DB2 optimizer will take a look at each index and see how many pages I/Os are need. Since you are not matching on POLICY_TRANSACTION this would be a non-matching index scan. The POLICY_NUMBER index has matching columns on two columns. This maybe enough to reduce the number of index I/O's. You may find by changing POLICY_TRANSACTION from < '99' to <= '99' may influence the use of this index. [...]
5249 49 51_Re: VSAM files being created in noncontiguous space14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 9 Feb 2001 10:26:07 +1100564_iso-8859-1 Is it possible that there was an SMS rule that forced the single extent (normally VSAM can use up to 5 extents for an allocation) and that this rule no longer works in R10? Just guessing.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Gordon.P.PROUTY@ODOT.STATE.OR.US [mailto:Gordon.P.PROUTY@ODOT.STATE.OR.US] Sent: Friday, February 09, 2001 6:08 AM To: DB2-L@RYCI.COM Subject: [DB2-L] VSAM files being created in noncontiguous space [...]
5299 25 22_Re: 15-255 Table Joins12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 8 Feb 2001 17:06:50 -0600545_- This discussion has been held with the key partners like SAP, PeopleSoft and Siebel multiple times. There are many thousands of employees in each of the companies, and we haven't talked to all of them, and I'm sure that not everyone agrees, even within one wing here. Most of the PeopleSoft restrictions, for example are caused by subselects, so that the join restriction is not too bad. The nVision queries are an exception. SAP SQL does not have many joins that exceed 15 ways outside of the BIW, and most of those fit the star schema. [...]
5325 18 35_Re: Definition Transition Variables12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 8 Feb 2001 17:14:49 -0600349_- I decided to try the glossary of the SQL Reference for V6.
transition variable: A variable that contains a column value of the affected row of the triggering table in its state before or after the triggering event occurs. Triggered SQL statements in the trigger definition can reference the set of old values or the set of new values. [...]
5344 100 51_Re: VSAM files being created in noncontiguous space15_Sydney Brereton21_sbrereton@MMAL.COM.AU30_Fri, 9 Feb 2001 10:14:59 +1030334_US-ASCII My understanding of VSAM is that it has always been possible to have non-contiguous primary allocation. If the primary allocation can be found within 5 extents then the allocation will be successful, otherwise the allocation fails for the volume. This has been true for as many years as I remember (1980?). Cheers, Syd. [...]
5445 63 15_Re: Who did it?9_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 8 Feb 2001 11:46:15 -0700496_us-ascii If you have Log Analyzer (CA) you can generate a DML activity report looking for only deletes tied to the database in question. You can set it up to look only for deletes against sysibm.sysdatabase. I would imagine other vendor products like BMC's Log Master would have similar capabilities.
If you don't have the log from that time period you could always generate a list of potential offenders by building an appropriate query against sysibm.sysdbauth, sysibm.sysuserauth. [...]
5509 103 19_Re: Help on a query14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 9 Feb 2001 11:15:23 +1100568_iso-8859-1 Lisa,
By now I assume you understand why index #2 was selected. But there is one thing I'ld like to add to the other responses.
The 'two columns' in your second question (which I am presuming comes from MATCHCOLS=2) does not mean "DB2 uses only two columns from the index". It means "DB2 uses the first two columns of the index to select a range of entries to search for qualifying rows". Once an index entry has been found, DB2 can use the values in the other indexed columns to filter out rows it knows cannot match the predicates. But [...]
5613 19 24_Log Analyzer for UDB/AIX9_Bob Lewis23_BLewis@MICROGENERAL.COM30_Thu, 8 Feb 2001 16:30:21 -0800434_iso-8859-1 Does anyone know of a product similar to Log Analyzer that would give us audit-type off of DB2 logs -- on UDB/AIX?
Vendors, don't be shy now.
Thanks -
Bob Lewis
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
5633 74 19_Re: Help on a query24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Thu, 8 Feb 2001 10:17:01 -0800438_- Hi Lisa!
DB2 can use a 2-column match using the second index because you specify an equal condition on columns POLICY_NUMBER and TERM_IDENT. Since the first column of the #1 index is POLICY_TRANSACTION, for which you've specified a less than condition, DB2 would not be able to exclude (filter out) very many rows based on that condition. So index #2 has the best chance of limiting the scan to the smallest number of rows. [...]
5708 37 35_LoadPlus and EA-enabled tablespaces15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Fri, 9 Feb 2001 12:14:24 +1100620_- Colleagues,
An 'eyes-up' for those of you that have large tablespaces (i.e. DSSIZE > 4Gb) and use BMC's LoadPlus utility.
If your tablespace has been defined with DSSIZE > 4Gb and it's under the management of an SMS dataclass that enables extended addressability you have no problems. As I've mentioned before, I've loaded around 13Gb into one partition of a 9-partition tablespace. Nice. However, if your tablespace _hasn't_ been defined with a DSSIZE clause (or with DSSIZE <= 4Gb) and it's under the management of an SMS dataclass that enables extended addressability then you may have problems. [...]
5746 51 36_Help needed..in UDF(UDB/AIX)..urgent8_bharat r20_bharat_321@YAHOO.COM30_Thu, 8 Feb 2001 18:32:31 -0800370_us-ascii Hi.. My UDF is not working for integers and decimal parameter values.. I tried with many combinations between application program and create function definition.. If any one has executed for decimal, integer please give me the Syntax in the create Function statement and Signature of the Java Program. If I use varchar instead of int it is working fine... [...]
5798 132 27_Re: Recover tablespace.....16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 9 Feb 2001 09:43:43 +0530610_- Yeah, it should be. I missed it somehow. So the rexx is of 7 lines( not so easy)
Regards, Sanjeev
> -----Original Message----- > From: Grainger, Phil [SMTP:Phil.Grainger@CA.COM] > Sent: Thursday, February 08, 2001 6:24 PM > To: DB2-L@RYCI.COM > Subject: Re: Recover tablespace..... > > Shouldn't there be a DELETE FROM TBL1 before the re-insert of the data > from the temporary table?? > > Phil Grainger > Computer Associates > Product Manager, DB2 > Tel: +44 (0)161 928 9334 > Fax: +44 (0)161 941 3775 > Mobile: +44 (0)7970 125 752 > phil.grainger@ca.com > > > -----Original Message----- [...]
5931 66 26_Re: DB2 Estimator Question15_Jackson Reavill18_damcon2@US.IBM.COM30_Thu, 8 Feb 2001 15:12:20 -0500327_us-ascii Hi Missy,
I haven't used Estimator in a while, but from memory I would think that you would add the execution of a select, delete, or update statement depending on the rule. Then tell Estimator how many times this SQL will be executed based on the number of rows manipulated that would cause them to fire. [...]
5998 20 17_Owner Vs. Creator16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Fri, 9 Feb 2001 02:41:27 -0500384_iso-8859-1 Hey Folks.. We have a case where a tso userid is a creator, but not an owner. If the tso userid is deleted and I revoke authority from the tso userid, does that invalidate the privileges of the owner? In other words, in syspackage, the creator is user1, but the owner is prod1. What will happen to the package that prod1 owns if I revoke sysadm from user1? Thanks.... [...]
6019 41 31_Re: on line reorg mapping table0_18_mebert@AMADEUS.NET30_Fri, 9 Feb 2001 09:20:10 +0100567_us-ascii Hi Chris,
this is one of those questions that get discussed periodically, always starting from square one... to summarise:
1) The mapping table/TS is not an issue. No rows get inserted into the table (it's a "phantom" table). So use PRIQTY 720 SECQTY 720 for the TS. Put as many mapping tables in there as you like. 2) The mapping index is the important space. After OLR has finished, it is always resized to the PRIQTY. So use PRIQTY 720 SECQTY 72000 for this one. It will take up negligible space when not in use and can support OLRs of [...]
6061 30 42_Stored proc Builder and C-compiler problem14_Per Callerstal22_per.callerstal@MIPS.SE30_Fri, 9 Feb 2001 02:27:42 -0600367_- Hi All,
This problem relates to DB2 UDB 7.1 on Sun / Solaris. We are starting to convert an application from Microsoft SQL-Server to Sun / Solaris and DB2. When converting stored procedures we get an error message during build of the procedure (in C). The procedures are converted to SPL (SQL Procedures Language) and then they should be compiled in C. [...]
6092 18 20_Re: Dynamic prefetch15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 9 Feb 2001 02:29:01 -0600435_- Francis
This would only work, if you bound your plans with RELEASE(COMMIT). But in a data sharing environment it is recommended to bind the plans with DEALLOCATE, hence the counters will not be reset.
I think some other things must be considered, if you commit so often (and you bind with COMMIT): all those little procedure (like insert and update procedure) will be distroyed and that will be a bad side effect. [...]
6111 136 39_Re: Retrieving a ROW ID after an insert15_Douwe van Sluis20_d.b.vansluis@KPN.COM30_Fri, 9 Feb 2001 10:38:56 +0100581_iso-8859-1 Sanjay,
I did a test with the same results. The confusing part is that the SQL Reference mentions in chapter 3, "Row ID comparisons" that the first 17 bytes are used for comparison.
>> A value with a row ID type can only be compared to another row ID value.The comparison of the row ID values is based on their internal representations.The maximum number of bytes that are compared is 17 bytes,which is the number of bytes in the internal representation.Therefore,row ID values that differ in bytes beyond the 17th byte are considered to be equal. >> [...]
6248 22 39_Re: Retrieving a ROW ID after an insert15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 9 Feb 2001 03:58:05 -0600491_- I think this thread can continue forever. I have two additional questions to complete my understandings of ROWID. (BTW I found this thread very interesting and instructively especially James's and Sanjay's proceedings)
1. If I use a ROWID-column with RI and I have a big parent table and a very small child table and if I insert a row in my small table using a ROWID- value with a RID-part of say page no 100,000. Would that row be placed at page no 100,000 in my child table? [...]
6271 71 44_AIX,NT/UDB-6.1/DataLink Manager Product Info15_Agarwal, Ashish28_Ashish.Agarwal@HPSGLOBAL.COM30_Fri, 9 Feb 2001 16:01:04 +0530379_- Hi All, Can someone please tell me the pricing for Data Links Manager product or give me the correct link for it. We have UDB 6.1 on both NT and AIX. We came to know that Datalinks Manager is a separate product but we cant find its costs. Further, if any downloadable version( Trial Basis or ..:-) ) is available, then do tell the link. Thanks in advance, Regards Aashish [...]
6343 54 39_Re: DB2 Active logs-greater than 2 gigs15_Douwe van Sluis20_d.b.vansluis@KPN.COM30_Fri, 9 Feb 2001 11:40:57 +0100314_iso-8859-1 David,
Below is a snap from an email discussion we had with IBM in early 1999. Don't know if this is still the case. The official limit in the SQL Reference is still 2G. We decided the limit our logs to 2G, just in case. We actualy decreased the size even more. We fit 3 logs on one 3390-3. [...]
6398 47 15_UDB Performance0_18_dsi.listsrv@DB.COM30_Fri, 9 Feb 2001 15:28:00 +0530591_us-ascii Hi
We have developed an Application using 3 tier architecture technology. The details are given below :
1) The Frontend is developed in JAVA. 2) The application server developed using C++ ( all the business validations are done here) 3) The database used for storing the data is UDB 6.1 with FP 4 on NT. We are using 60+ tables ( 30+ tables contains static data and the remaining are business tables tables which gets updated by the application server. These tables / indexes/ Lobs are placed in 8 different table spaces and are 'managed by Database'. We are not [...]
6446 73 57_SQLCODE -206 received but the column is still there .....12_Peter, Georg15_G.Peter@DZBW.DE30_Fri, 9 Feb 2001 12:23:00 +0100567_iso-8859-1 Hi listers,
while binding a package one of our programmers received the following SQLCODE -206:
BIND PACKAGE (E0OSSYENTW) OWNER(LEWIS) QUALIFIER(LEWIS) MEMBER(EA8KXE01) ..... DSNX200I - BIND SQL ERROR
USING LEWIS AUTHORITY SQLCODE=-206 SQLSTATE=42703 TOKENS=DEW64_DIGSTATUS CSECT NAME=DSNXORSO RDS CODE=-100 DSNT233I - UNSUCCESSFUL BIND PACKAGE =
DERRZD1DB21.E0OSSYENTW.EA8KXE01.(2001-02-01-17.55.25.462480) DSN
After this SQLCODE we asked the catalog, if column DEW64_DIGSTATUS in table LEWIS.DEW64 does not exist: [...]
6520 78 61_Re: SQLCODE -206 received but the column is still there .....0_18_mebert@AMADEUS.NET30_Fri, 9 Feb 2001 12:32:46 +0100317_iso-8859-1 Probably you're accessing the table through a view that does not contain the column. For example, if the view is SELECT * but the column was later added to the table, it will not be part of the view.
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...]
6599 87 61_Re: SQLCODE -206 received but the column is still there .....13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM30_Fri, 9 Feb 2001 06:37:42 -0500685_iso-8859-1 Are you sure that a hard-coded qualifier different from "LEWIS" has not been coded on the FROM clause??
Scott Lindsey Sr. DBA, Highlights for Children
Peter, Georg wrote: > > Hi listers, > > while binding a package one of our programmers received the following > SQLCODE -206: > > BIND PACKAGE (E0OSSYENTW) OWNER(LEWIS) QUALIFIER(LEWIS) MEMBER(EA8KXE01) > ..... > DSNX200I - BIND SQL ERROR > > USING LEWIS AUTHORITY > SQLCODE=-206 > SQLSTATE=42703 > TOKENS=DEW64_DIGSTATUS > CSECT NAME=DSNXORSO > RDS CODE=-100 > DSNT233I - UNSUCCESSFUL BIND PACKAGE = > > DERRZD1DB21.E0OSSYENTW.EA8KXE01.(2001-02-01-17.55.25.462480) > DSN > > After this SQLCODE we asked [...]
6687 85 61_Re: SQLCODE -206 received but the column is still there .....10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Fri, 9 Feb 2001 05:30:46 -0600802_ISO-8859-1 Hi,
What does the offending query look like?
On Fri, 9 Feb 2001 12:23:00 +0100, Peter, Georg wrote:
>Hi listers, > >while binding a package one of our programmers received the following >SQLCODE -206: > >BIND PACKAGE (E0OSSYENTW) OWNER(LEWIS) QUALIFIER(LEWIS) MEMBER(EA8KXE01) >..... >DSNX200I - BIND SQL ERROR > >USING LEWIS AUTHORITY >SQLCODE=-206 >SQLSTATE=42703 >TOKENS=DEW64_DIGSTATUS >CSECT NAME=DSNXORSO >RDS CODE=-100 >DSNT233I - UNSUCCESSFUL BIND PACKAGE = > >DERRZD1DB21.E0OSSYENTW.EA8KXE01.(2001-02-01-17.55.25.462480) >DSN > >After this SQLCODE we asked the catalog, if column DEW64_DIGSTATUS in table >LEWIS.DEW64 does not exist: > >select colno, name > from sysibm.syscolumns > where tbname = 'DEW64' > and tbcreator [...]
6773 28 61_AW: SQLCODE -206 received but the column is still there .....12_Peter, Georg15_G.Peter@DZBW.DE30_Fri, 9 Feb 2001 13:29:59 +0100383_iso-8859-1 Hi Scott, Dr. Mebert, John.
The reason was that the column DEW64_DIGSTATUS was later added to the table LEWIS.DEW64. And yesterday someone dropped a view and re-created this view. But unfortunately he works with an older version of the view and so the column was not part of the view. And the program EA8KXE01 already works with the column DEW64_DIGSTATUS.... [...]
6802 26 53_The use of SQL Performance Hints in DB2 V6 and beyond12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Fri, 9 Feb 2001 07:24:05 -0600301_us-ascii I am working on a presentation about the use of Hints in the DB2 V6 and later. The target audience is User Groups across the US. I would like to exchange our experiences with others who have used hints. We are especially interested in what others regard as the "gotchas" in the process. [...]
6829 75 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 9 Feb 2001 08:39:59 -0500513_- Walter,
1. No, When you insert a ROWID value into a table, the RID part of the rowid will change and reflect the actual location of that row in the child table. Existing RID value does dictate where the row will be placed. 2. This will be just a guess. I think you will be able to access the row from parent table. Here is what may be required though. In COBOL a rowid variable is defined as a VARCHAR(40). i.e. it has a length part S9(4) COMP and a data part X(40). One should be able to store the [...]
6905 56 56_Re: Reg. Dropping a Partitioned tables Clustering Index.15_Douwe van Sluis20_d.b.vansluis@KPN.COM30_Fri, 9 Feb 2001 14:40:28 +0100437_iso-8859-1 Kannan,
If you just want to change a field in the partioning index that does not influence the physical partitioning you could consider the following.
1.Rename the tablespace VSAM's. 2.Drop the tablespace. 3.Delete the tablespace VSAM's (the ones that are empty). 4.Rename the 'old' VSAM's back. 5.Rebuild the index.
This is an action that I have carried out succesfully in a 1,5 Terrabyte database. [...]
6962 64 20_Re: Dynamic prefetch16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 9 Feb 2001 10:55:14 +0530578_- "Reposting it because it was rejected due to maximum number of lines"
Fritz, > Not a bad approach but could be proved dangerous. There are chances that > the commit frequency is not proper and sequential detection counter is > reset and cache is cleared before the actual data which we want is > retrieved by DB2. Look at the article from Bonnie Baker (The Wooes of > Commitment ) in DB2 Mag for this > > Regards, > Sanjeev > > -----Original Message----- > From: Leblanc, Francis C - CNF [SMTP:Leblanc.Francis@CNF.COM] > Sent: Thursday, February 08, 2001 10:08 PM [...]
7027 234 20_AW: Dynamic prefetch0_20_Gerhard.Heiss@RUV.DE30_Fri, 9 Feb 2001 14:51:20 +0100444_iso-8859-1 I thought the hostvariable would always look like 'ABC%'. Actually it contained '%ABC' as well as 'AB%C' where dyn. prefetch is absolutely correct. So I have to apologize for wasting your time.
Thanks again
Gerhard Heiss
-----Ursprüngliche Nachricht----- Von: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] Gesendet am: Donnerstag, 8. Februar 2001 16:46 An: DB2-L@RYCI.COM Betreff: Re: Dynamic prefetch [...]
7262 14 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 9 Feb 2001 07:35:03 -0600422_- Gerald
I am not so profient in the English language. I don't know what are "gotchas" are and I didn't find them in my dictionary. Are you interested to hear some problems?
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7277 15 39_Re: Retrieving a ROW ID after an insert15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 9 Feb 2001 07:47:15 -0600299_- Sanjay
Well, I understand the answer to my second question and I agree, that it is not a truly RI-implementation, but the answer to my first questions brings us to another problem. How can I use the ROWID-value of my child table to direct access the referenced row in my parent table? [...]
7293 202 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 9 Feb 2001 09:09:06 -0500463_iso-8859-1 Douwe,
I hate to contradict what the manual says(I have great respect for IBM DB2 manuals !!), but my testing proves otherwise. For example following are sample rowids from source and target table representing the same row
Source table - X 'E00EA6647FA2D9224E69012A108201 0005 0000000904' Target table - X 'E00EA6647FA2D9224E69012A108201 0000 0000000231' <-----first 15 bytes ----------------------------> ------- ------------------ [...]
7496 20 19_Re: Help on a query20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM30_Fri, 9 Feb 2001 09:22:12 -0500483_iso-8859-1 Thanks for all the input on this query.
Since it is index only and the two matching columns of index #2 get to the needed set of indexed columns. It's a fine performer and shall leave it this way.
thanks to all, Lisa
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7517 10 39_Re: Retrieving a ROW ID after an insert15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 9 Feb 2001 08:11:05 -0600290_- The rowid is 17 Bytes + 2 Bytes for the length
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7528 36 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 9 Feb 2001 09:32:02 -0500304_iso-8859-1 Guten Tag Walter,
Gotcha is American slang -- a contraction of Got you. We mercans frequently contract "you" with other verbs as "ya" and when the verb ends in the "t" sound is pronounced as cha -- as in "I betcha you're sorry you asked..." would mean "I bet you you're sorry ..." [...]
7565 36 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Fri, 9 Feb 2001 08:26:27 -0600469_- Walter,
'Gotcha' is a contracted form of the expression "I've got you!". It's an exclamation of triumph uttered after overpowering or outsmarting something or somebody.
In this context a 'gotcha' is an unexpected and disadvantageous consequence of carrying out a particular course of action. In this case then, as you guessed, Gerald is interested in hearing of any problems or drawbacks that others have encountered in using V6 Performance Hints. [...]
7602 26 55_Comparisons between SAP on Oracle (AIX) and DB2(OS/390)10_Robert Ord21_robertord@HOTMAIL.COM30_Fri, 9 Feb 2001 14:43:03 -0000672_- Dear All,
Does anyone have or know of any papers on comparisons between SAP on Oracle (AIX) and SAP on DB2(OS/390).
I tried the cost of ownership tack, now I need something else.
Obviously I am looking for objective viewpoint.
Regards
Rob
_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7629 40 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 9 Feb 2001 09:49:39 -0500658_- Ah...it indeed is stored 'like' a VARCHAR. I should have figured it out from the COBOL variable declaration. Thank you
Sanjay Jain
> -----Original Message----- > From: Walter Janissen [SMTP:walter.janissen@VICTORIA.DE] > Sent: Friday, February 09, 2001 9:11 AM > To: DB2-L@RYCI.COM > Subject: Re: Retrieving a ROW ID after an insert > > The rowid is 17 Bytes + 2 Bytes for the length > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can > be reached at DB2-L-REQUEST@RYCI.COM. [...]
7670 16 17_Stored Procedures12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Fri, 9 Feb 2001 09:54:11 -0500513_us-ascii Hi All. We are just getting into stored procedures and have a question about using DSNALI. How can we make this accessible to all stored procedures without including it in the link of everyones link? We are using the DB2 SPA address space at this time. TIA, Jeff
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7687 17 12_SQL UPDATE ?15_Neff, Stephen R16_NeffSR@STATE.GOV30_Fri, 9 Feb 2001 10:03:02 -0500459_iso-8859-1 Hello,
I would like to update a column in table A with a column in Table B where Table A key = Table B key. Is this possible using SQL from db2 VERSION 5 on OS390. Any suggestion greatly appreciated.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7705 57 39_Re: Retrieving a ROW ID after an insert13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 9 Feb 2001 09:07:55 -0600466_iso-8859-1 Walter,
You cannot access the parent table with a the same ROWID stored in the child. The key for RAD (or Row Access Direct) is the RID. They are different between tables. The only other place to get the RID is in an index. You need to create an index on ROWID so that you can search on the 17 bytes (which as you said is VARCHAR, hence the 2 byte length). This can be either 17 bytes or 19 bytes in the index dependant on ZPARM RETVLCFK=YES. [...]
7763 51 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)5_chris22_hhardy1@HOUSTON.RR.COM30_Fri, 9 Feb 2001 09:21:03 -0800560_iso-8859-1 there is so much to take into consideration, workload, data volume, # users (200 vs. 4,000), remote access, db size, recover window, performance, batch load, OLTP, static vs dynamic SQL impact, Basis team skills, dba skill set, tuning requirements, sub second response vs. >2 second response, memory, dasd-disk vendor, cpu-mhz rating, i/o channel design, if certain pre-qualifications (criteria) are not clearly established up-front its like comparing apples to oranges..... OS/390 & DB2 has a solid track record of 20+ years and counting..... [...]
7815 205 20_Re: Dynamic prefetch13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 9 Feb 2001 09:12:14 -0600310_iso-8859-1 Gerhard,
I think sometimes time gets wasted also as the replies are not always read entirely by some (due to sheer size I'm sure). I had suggested this as a possibility in the first reply.
But no, you didn't waste our time. It's often a good lesson to learn that we often assume. [...]
8021 40 21_Re: Stored Procedures13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Fri, 9 Feb 2001 09:15:37 -0600329_iso-8859-1 Hi,
I did this by copying the module, DSNALI, into its own library, db2.dbxx.dsnali.load, and giving DSNALI an alias of DSNHLI. This library is then the first in the SPAS steplib.
I got this route from this group sometime back. If you need more info. and past threads .. try the archives. hth Glenn [...]
8062 72 16_Bad table design16_Martin Berchtold31_Martin.Berchtold@SYSTEMATICS.DE30_Fri, 9 Feb 2001 08:56:55 -0600294_- Hi List,
I have the following problem with the design of a table.
Fristly I would like to tell you a few details about the design of this table. It belongs to a partitioned tablespace, with 30 partitions. Only Part 1 to 17 are populated with data, part 18 to 30 are empty. [...]
8135 55 16_Re: SQL UPDATE ?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 9 Feb 2001 09:28:38 -0600334_iso-8859-1 Stephen,
You need to wait until V6, which provides the capability to code a scalar subquery in an UPDATE SET clause. Otherwise you need to code a SELECT then UPDATE in a program.
If this is a once off, and not in a program. A common approach is to generate the UPDATE statements within a SELECT such as: [...]
8191 58 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Fri, 9 Feb 2001 08:20:40 -0700442_iso-8859-1 Howdy Rob,
Attached is the SAP website that has some very interesting articles that explains many reasons why the Oracle Solution for SAP is a (BaaaaaaaDD!!!!) decision. Not only does DB2 "significantly" outperform Oracle but if you read the artical from Karl Hess (SAP's head man) you will find that SAP is => REPLACING <== the Oracle solution with DB2 UDB. I would think this fact alone would make the decision easy. [...]
8250 37 0_11_Tom Marcano23_marcano_db2@HOTMAIL.COM30_Fri, 9 Feb 2001 15:35:51 -0000330_- Hello,
I wonder if somebody has ran over this problem before:
I am using FASTUNLOAD (Platinum Tool) to download data from some Image Copies, when I use an Image Copy generated by the COPY utility this unload works just fine. But, when I use an Image copie generated by the REORG I got the following message : [...]
8288 39 51_Re: VSAM files being created in noncontiguous space11_Jeff Brokaw20_jeffbrokaw@YAHOO.COM30_Fri, 9 Feb 2001 09:25:53 -0600510_- Previous posters are correct, any space allocation request can use up to 5 extents to satisfy it. The terms "extent" and "allocation" are not always interchangable, although in a perfect world (non-fragmented) they can appear to be the same thing. They aren't.
Allocations (primary and secondary space) are logical requests for space from an application point of view; extents are physically contiguous chunks of non-volatile storage out on some piece of hardware (who knows here, these days :). [...]
8328 58 0_9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Fri, 9 Feb 2001 08:41:54 -0700461_iso-8859-1 Howdy Tom,
Looks like you used an IBM Reorg with Inline Image Copy. Unfortunately the IBM Inline I/C is NOT compatible for FASTUNLOAD. This is one of those Platinum "gotchas".
Cheers, Don Alden
-----Original Message----- From: Tom Marcano [mailto:marcano_db2@HOTMAIL.COM] Sent: Friday, February 09, 2001 8:36 AM To: DB2-L@RYCI.COM Subject:
Hello,
I wonder if somebody has ran over this problem before: [...]
8387 19 20_Downloading datasets7_Sri Sri20_sri_db2l@HOTMAIL.COM30_Fri, 9 Feb 2001 09:46:22 -0600414_- Dear List,
I need to download a lot of PDS datasets to a zip drive on my PC. Downloading them one by one using file transfer is a pain. Is there any way to download them easily all at once. Also if there is a way to concatenate all the members in a PDS into one single member with a separator between each member, then I can download the single concatenated member for each PDS. Thanks for any help. [...]
8407 93 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Fri, 9 Feb 2001 11:08:26 -0500358_us-ascii The reason SAP is moving their application packages to DB2 is simply because Oracle has invaded their space (i.e., in terms of competing application business process solutions).
SAP does not want to give Oracle business when Oracle is trying to eat their lunch. SAPs decision was purely a tactical business decision not a technical one. [...]
8501 90 0_0_22_BILL_GALLAGHER@PHL.COM30_Fri, 9 Feb 2001 11:21:11 -0500466_us-ascii Tom,
Was the reorg a SHRLEVEL(REFERENCE) or SHRLEVEL(CHANGE) reorg? Those can produce duplicate pages and/or pages out of order in the copy data set. In either case, the copy data set is still logically correct, but it is just a function of how inline image copies during REFERENCE or CHANGE reorgs works. You can refer to the section called "Using inline COPY with REORG TABLESPACE" in Chapter 2-16 of the "Utility Guide and Reference" manual. [...]
8592 40 24_Re: Downloading datasets17_McGee, Luke (ECS)23_LuMcGee@EXCHANGE.ML.COM30_Fri, 9 Feb 2001 11:21:04 -0500300_iso-8859-1 I used a product called Reflections FTP (nice GUI Interface) and it was easy to drag and drop an entire PDS or selected members to a floppy or removable disk or HD. It is obviously doing regular FTP commands in the background. Any good FTP product will probably be able to do this... [...]
8633 24 20_Downloading datasets19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Fri, 9 Feb 2001 11:23:27 EST504_us-ascii Sri,
If you have an FTP server set up on your LAN then it is very fast and easy using FTP.
You can run FTP as a Batch job on your mainframe.
Regards,
Rajeev P. Dhanawade Phone: 302-577-4659 ext. 221 Fax : 302-577-4454
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
8658 108 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Fri, 9 Feb 2001 09:57:50 -0600403_iso-8859-1 I used Oracle (not SAP) on AIX all through the early '90's.
I used SAP (HP) on Oracle (HP K450's) from 1996 through 2000. I found HP-UX very similar to AIX.
We migrated to SAP (AIX SP Silver and Winterhawk nodes) on DB2 V6 (OS390) last Labor Day, so we now have 5+ months on the new platform. Our fiscal year started 1 Nov 2000, so we have already been through a year-end. [...]
8767 71 24_Re: Downloading datasets13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 9 Feb 2001 11:34:06 -0500536_iso-8859-1 Hi Sri, As long as these are DB2 PDSes, I can help you. Otherwise this would be off-topic and of course illegal.
I recommend you use the TSO Xmit command which invokes IEBCopy and builds a sequential file from your pds containing all members (or only those selected). You can download that. To extract the members from the sequential file, you need to use the other half of Xmit, RECEIVE. So if you wish to download them and keep them on the PC, ie not upload them to some other TSO environment, this won't work. [...]
8839 86 0_15_Toine Michielse18_vndobtm@US.IBM.COM30_Fri, 9 Feb 2001 17:29:14 +0100422_us-ascii Hello Tom,
The reason you see those page sequence errors is because you are generating the image copy during an ONLINE reorg (shrlevel=change). As a result the same page may be present multiple times in an image copy dataset, the last one being the most current page.
Recover will simple copy the most recent page over an older version similar to merging an incremental with a full image copy. [...]
8926 59 24_Re: Downloading datasets15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Fri, 9 Feb 2001 10:44:49 -0600280_iso-8859-1 I have a program, I think it originally came from the CBT tape, called IEBDNDTE. It reads a PDS and builds an output stream that can be used by IEBUPDTE to build a PDS. If you would like it, please send a private e-mail to me so we do not clog up the list server. [...]
8986 148 101_fighting with a long-running unit-of-recovery that is causing other update jobs to time out and abend5_chris22_hhardy1@HOUSTON.RR.COM30_Fri, 9 Feb 2001 10:58:08 -0800469_iso-8859-1 "long-running unit-of-recovery that is causing other update jobs to time out and abend"
Platinum has tools which may assist with this per DB2, detector, thread terminator & something else is included in the solution, good recovery stuff ..... BMS has a strong tool set as well addressing long running instance, process, thread, task if back-out is needed, semi-back-out as well.......... Not sure about the SAP product vendor being certified.... [...]
9135 77 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM30_Fri, 9 Feb 2001 10:51:36 -0600545_iso-8859-1 When I installed SAP I looked at the skills at our company. Mostly we are a mainframe shop with IMS and DB2. We do have a little SQL server but not much. We also have a great deal invested in the architecture (support software) to support DB2. The BMC products like Reorg Plus, Image Copy, Recover Manager and Omegamon are part of our legacy environment. By placing the database on DB2 to entire back end process was a copy of what is already used. I was able to leverage all these skill sets and just concentrate on the SAP R/3 [...]
9213 50 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Fri, 9 Feb 2001 10:56:15 -0600347_iso-8859-1 Walter:
I apologize to all! I did not intend to start a thread on the American mauling of the English language. I did a presentation in Paris with simultaneous translation. I began by saying that when I spoke in the UK they too required simultaneous translation. Everyone but the Brits in the audience thought it was funny. [...]
9264 86 39_Repost: DevX Launches IBM Database Zone13_Morrill, John12_JohnM@VP.NET30_Fri, 9 Feb 2001 10:10:09 -0700681_- Dear DB2 Interested Parties,
Visit the new IBM Data Management Pavilion area on DevX at http://www.devx.com/dbzone/ibmpavilion/. Below is a letter from DevX, Inc. President and CEO, Peter C. Horan, introducing the IBM Database Zone.
This DB2 developer resource center connects developers into a community of expert advice and peer discussion from a trusted source. This community is also a forum to allow developers to share insights and expertise, as well as learn from colleagues around the globe through external special interest groups. Developers can tap into this resource to build skills, awareness and knowledge about DB2 and data management products. [...]
9351 81 59_Repost: DB2 Magazine Q1, 2001: Focus on Tools and Utilities13_Morrill, John12_JohnM@VP.NET30_Fri, 9 Feb 2001 10:10:55 -0700331_- Dear DB2 Interested Parties,
The new issue of DB2 Magazine has been mailed to subscribers and is now available on the Web. Below is a bulletin about the new issue.
To subscribe to DB2 Magazine, go to the web site at http://www.DB2mag.com/ and complete the subscription form.
Regards, Mike + + + + + + + [...]
9433 156 57_FW: Database Tools Solutions for DB2 UDB for AIX, Windows13_Morrill, John12_JohnM@VP.NET30_Fri, 9 Feb 2001 10:12:02 -0700609_- Dear DB2 Interested Parties,
Yesterday, BMC announced new recovery tools for the DB2 for AIX and DB2 for Windows NT/2000 environments. Below is the press release.
As DB2 continues to pick up market share on Unix and Windows NT/2000 platforms, tools for these environments are gaining momentum. A recent META Group research report, "Finding Database Tools for DB2 on NT and Unix" (Date: January 5, 2001, File: 884, Author: Mike Shainman) assesses this trend and the vendors with offerings in this market (BMC Software, Quest Software, Computer Associates, Database-GUYS, and Embarcadero). [...]
9590 96 71_Repost: New DB2 for Linux Versions Available for Download from IB M.Com13_Morrill, John12_JohnM@VP.NET30_Fri, 9 Feb 2001 10:04:48 -0700604_- Dear DB2 Interested Parties,
As Linux grows in popularity, IBM software, lead by DB2, is available to support your production-strength Linux implementations. Below is a bulletin about the availability of new DB2 for Linux versions available for 90-day trial via download at http://www6.software.ibm.com/dl/db2udbdl/db2udbdl-p .
For general information about IBM hardware and software support of Linux, see http://www.ibm.com/software/is/mp/linux/. Developers on the Linux platform will want to visit the IBM developerWorks Linux zone at http://www-106.ibm.com/developerworks/linux/. [...]
9687 64 44_FW: Call for IDUG Solutions Journal Articles13_Morrill, John12_JohnM@VP.NET30_Fri, 9 Feb 2001 10:25:27 -0700520_- -----Original Message----- From: Michael Swift [mailto:dbswift@us.ibm.com] Sent: Tuesday, January 23, 2001 12:28 PM To: DB2 Interested Parties Subject: Call for IDUG Solutions Journal Articles
Dear DB2 Interested Parties,
Dave Beulke, IDUG Board member and editor of the IDUG Solutions Journal (and DB2 consultant extrodinaire), asked me to pass along the following note regarding the opportunity to write an article for the IDUG SJ. Please contact him directly for any further information. [...]
9752 62 105_Re: fighting with a long-running unit-of-recovery that is causing other update jobs to time out and abend12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Fri, 9 Feb 2001 11:55:15 -0600427_us-ascii Thanks.
I chose my words carefully when I said a "long-running unit-of-recovery" rather than a "long running instance, process, thread, task".
SAP acts as a transaction manager and multiplexes many users into a few, persistent DB2 threads. Persistent as in days or weeks. Just to keep things interesting, all the threads have the same primary authorization, correlation name, collection, plan, etc. [...]
9815 112 59_(SAP) I've written a book of procedures for the Admin Staff5_chris22_hhardy1@HOUSTON.RR.COM30_Fri, 9 Feb 2001 12:32:46 -0800522_iso-8859-1 Is it possible to share this ("book of procedures") with us??? Can you share this with the group????
this may / might be a BIG help.......... If you can't share it we understand.....
Delete the company specific stuff etc.... thanks in advance
----- Original Message ----- From: "Zobjeck, A. J." Newsgroups: bit.listserv.db2-l To: Sent: Friday, February 09, 2001 8:51 AM Subject: Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390) [...]
9928 60 38_Re: CA Fast Unload Page Sequence Error9_Rob Crane22_racrane@CONCENTRIC.NET30_Fri, 9 Feb 2001 11:32:33 -0700578_us-ascii Few things to try.
1) What are the EXCP options you are using (YES or NO) for the unload from imagecopy and unload from imagcopy generated from reorg?
2) If using EXCP YES try running EXCP NO and see if you get the same PTFU217 error.
3) Have you opened up a star issue? (800-833-7528). Include version of products, DB2, MVS, job output, DDL of object. Once you have a star issue opened up you can easily email information to the star system without having to ftp, ect. Just send note to star.operator@ca.com have the subject line be the star [...]
9989 87 105_Re: fighting with a long-running unit-of-recovery that is causing other update jobs to time out and abend5_chris22_hhardy1@HOUSTON.RR.COM30_Fri, 9 Feb 2001 12:50:11 -0800442_iso-8859-1 SAP should have (provide) check-point (re-start) recovery for long running units... CICS, VSAM utilized check-point restart (strategy) journals per application and long duration tasks about 15 years ago....
this would facilities re-start without starting the unit of work from the original beginning / start...
Like what was stated below the process (business & IT workflow) needs to be analyzed or reviewed.... [...]
10077 125 63_Re: (SAP) I've written a book of procedures for the Admin Staff14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM30_Fri, 9 Feb 2001 13:05:47 -0600448_iso-8859-1 call me 312-984-3890
-----Original Message----- From: chris [mailto:hhardy1@HOUSTON.RR.COM] Sent: Friday, February 09, 2001 2:33 PM To: DB2-L@RYCI.COM Subject: (SAP) I've written a book of procedures for the Admin Staff
Is it possible to share this ("book of procedures") with us??? Can you share this with the group????
this may / might be a BIG help.......... If you can't share it we understand..... [...]
10203 20 18_'Large' tablespace19_Michael Robaczewski25_Mrobaczewski@CAREMARK.COM30_Fri, 9 Feb 2001 13:38:39 -0600372_- We are planning to create our 1st tablespace defined as a 'Large' tablespace using DB2 Version 5. So I would like to ask if anyone has any tips, hints or has had any problems with tablespaces defined as 'Large'
Thank You for any help.
Michael G. Robaczewski Database Administrator (847) 634-5069 (voice) (847) 821-4101 (fax) mrobaczewski@caremark.com [...]
10224 53 22_Re: 'Large' tablespace16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 9 Feb 2001 15:23:07 -0500376_iso-8859-1 Considerations: 1) The SWITCH phase of ONLINE REORG may take a long time due to more datasets being renamed/deleted. 2) Some vendor utilities have been known to have problems loading/unloading more than perhaps 30 or so partitions at a time (Getmain failures). 3) More datasets means you may hit DSNZPARM value DSMAX more often and/or may need to increase it. [...]
10278 12 38_Re: CA Fast Unload Page Sequence Error12_Stacia Moore26_Stacia_Moore@HAM.HONDA.COM30_Fri, 9 Feb 2001 15:18:11 -0500255_us-ascii o.k.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10291 39 22_Re: 'Large' tablespace9_Rob Crane22_racrane@CONCENTRIC.NET30_Fri, 9 Feb 2001 14:31:41 -0700337_us-ascii Be aware that the limit keys when going to large are true limits. In the past the last partition would hold any overflow rows that went above your limit key, with large tablespace if you have rows that go beyond your last limit key they will error off. Make your last limit key the largest value your data would represent. [...]
10331 51 30_DB2 Utility Problem (00E70005)12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Fri, 9 Feb 2001 18:25:55 -0500589_iso-8859-1 Hello list,
Today would not appear to be my lucky day. I was loading a table, and I had the input data file under the SYSIN ddname and the load control cards under the SYSRECxx dataset name (the input file was fairly large). Note: never do this(!) The job ran for about 30 minutes and was eating an enormous amount of CPU until I cancelled it. Then I noticed the JCL error, so I figured I would just correct and resubmit. Nothing doing. DB2 terminated the utility with an internal inconsistancy error(00E70005). I re-cycled DB2. I recreated the database, tablespace [...]
10383 18 82_Help for DB2 Connect -- Problems with WebSphere on NT talking to DB2 6.1 on OS/39014_Bernie OConnor26_Bernie.OConnor@ANIXTER.COM30_Fri, 9 Feb 2001 21:45:06 -0600556_- We are using DB2 Connect to talk to DB2 6.1 for OS/390 from a JAVA language environment under WebSphere 3.52 on NT 4.0. We are getting a number of problems. The most important problem is we seem to have is hanging transactions. Some of this is attributable to JAVA coding techniques we have corrected. Others are a mystery. The symptom is that a JAVA JDBC call using the SLQ CALL verb to execute a DB2 Stored Procedure on OS/390 is preceded by a series of metadata calls made by the ODBC driver. The metadata calls do not always complete. Sometimes [...]
10402 14 55_Daryl G Spletzer/Pr/Consumers/CMS is out of the office.16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Sat, 10 Feb 2001 02:14:46 -0500365_us-ascii I will be out of the office from 02/10/2001 until 02/15/2001.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10417 15 11_set no-mail12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Sat, 10 Feb 2001 10:22:04 +0200310_iso-8859-1 Isaac Yassin DBMS & IT Consultant yassin@netvision.net.il
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10433 23 20_Downloading datasets12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 10 Feb 2001 11:05:51 +0200516_iso-8859-1 Hi,
A small REXX that uses LM sevices to read library members (one by one) and write them to seq. file is all you need. The rest is just transfer (with ascii crlf).
Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10457 22 28_oracle 2 db2 via db2 connect12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 10 Feb 2001 13:33:53 +0200487_iso-8859-1 Hi, You can even use JDEVELOPER on a DB2 table via DB2 CONNECT. You just need to implement some views needed by ORACLE in the DB2 environment.
Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10480 165 63_Re: (SAP) I've written a book of procedures for the Admin Staff23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Sat, 10 Feb 2001 20:09:58 -0000508_iso-8859-1 Can I call you aswell?
what is your release of SAP?
I am interested to see you have just 3 people managing all that lot.
How big is your system? how many DB2 installs do you have?
all the best
Leslie DB2 Certified (mad) DB2 SAP (4.C)
-----Original Message----- From: Zobjeck, A. J. [mailto:Al_Zobjeck@TTX.COM] Sent: Friday, February 09, 2001 7:06 PM To: DB2-L@RYCI.COM Subject: Re: (SAP) I've written a book of procedures for the Admin Staff [...]
10646 16 86_Re: Help for DB2 Connect -- Problems with WebSphere on NT talking to DB2 6.1 on OS/3909_Eric Kwai18_Eric_Kwai@DTCC.COM31_Sat, 10 Feb 2001 21:30:45 -0600448_- Bernie: What is your current release of DB2 Connect software. The latest DB2 Connect FP6 for V6 fixed many Java problems with DB2 OS/390. Verify if your code is using the jdbc driver. Hope this helps Eric
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10663 140 20_Re: Bad table design13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 11 Feb 2001 10:37:57 -0600553_iso-8859-1 Martin,
It is difficult to give any specific answers without a more intimate knowledge of the data, insert/update activity, and the data retrieval requirements. The first question is: What is the problem? The table design or the SQL. I guess they both need some work.
I'm sure that SELECT statement is not a true indication of a typical statement. If so, I would firstly improve the statement. By the way, that SQL statement will return one row (DISTINCT ColC WHERE ColC = 9879523098123). Anyway here is the first attempt: [...]
10804 179 20_Re: Bad table design5_chris22_hhardy1@HOUSTON.RR.COM31_Sun, 11 Feb 2001 15:26:54 -0800691_iso-8859-1 "If our customers start the queries, they get hugh response times, sometimes more than 12 hours".
Contact one of the vendors (CA, BMC, Quest Software etc) and trial for 30 days database-SQL analyzer (whatever they call it) to interrogate your schema, index & columns for usage (RI), optimization and performance..... based on your skill-set within three-four days of the install and configuration the vendor product (solution) will/should resolve your immediate (response) issue/s and other degradation concerns not yet discovered.... certain queries are batch candidates vs online real time response, what's your IT to business unit service level response state????? [...]
10984 48 28_Re: Log Analyzer for UDB/AIX11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sun, 11 Feb 2001 16:45:35 -0500400_us-ascii Bob,
If you want to know which users accessed which tables and/or columns, when, and from where, we can help you with this. We can also help identify most frequently referenced columns and tables, indexes used, and, in a round-about way, which indexes are not likely to be used. We can show you the highest cost users and/or programs, resource utilization trends, and much more. [...]
11033 48 22_Re: 'Large' tablespace15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 12 Feb 2001 10:30:02 +1100515_- I was going to, but my experience is in V6. Don't know if you need special SMS data classes to allow this (my feeling is no) but you do in V6. Anyway, be aware that the 'LARGE' clause is only tolerated in V6. The preferred method then is to use the DSSIZE clause. Fancy 255 64Gb partitions? The only hiccup I had was when loading data into a 'large' tablespace when it wasn't really large. See if you can get a load to make a tablespace (or index) go multi-volume and stay under 4Gb. If you were V6, I'd say [...]
11082 171 20_Re: Bad table design15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 12 Feb 2001 10:56:43 +1100391_iso-8859-1 Hi Martin,
Good to see you're still knee-deep in DB2. I would offer advice but, as that young whipper-snapper Terry pointed out, I'm far too old to be useful in that regard. Besides, he's already made some good suggestions. I taught him well... ;o)
Good luck,
Raymond PS. Terry, the Para's are standing by (troopers, not medics; they're for me). [...]
11254 93 34_Re: DB2 Utility Problem (00E70005)14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 12 Feb 2001 11:31:57 +1100419_iso-8859-1 Robert
Have you reported this to IBM? The only alternative to a full system restore, would be a recovery of the catalog and directory to some suitable point in time.
I presume you realise that OS/390 V1.3 is no longer supported, and that PUT9909 is rather old.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au [...]
11348 97 56_Re: Reg. Dropping a Partitioned tables Clustering Index.14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 12 Feb 2001 12:03:52 +1100412_iso-8859-1 Twice lucky! Once to get the same ts OBID and once to get the same table OBID when you you re-created the tablespace and table. (BTW, I presume that recreating the tablespace and table is a hidden trick that you forgot to include at 2a.)
Most people who try this have to use DSN1COPY XLATE to copy data from the 'renamed' VSAM clusters to the clusters appropriate to the newly created ts. [...]
11446 74 21_Re: Owner Vs. Creator14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 12 Feb 2001 12:03:46 +1100429_iso-8859-1 Michael,
Read the bit in the SQL Reference manual about cascade revoke. This bit appears especially pertinant: "Invalidation of plans and packages: A revoke or cascaded revoke of any privilege, excluding the EXECUTE privilege on a user-defined function, that was exercised to create a plan or package makes the plan or package invalid when the revokee no longer holds the privilege from any other source." [...]
11521 75 19_Re: UDB Performance11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sun, 11 Feb 2001 21:31:05 -0500365_us-ascii Without doing a detailed analysis of performance data, I would suspect it is the BLOBs that are hurting you the worst.
DB2 UDB uses direct I/O for these, there are no bufferpool benefits. Put your BLOB data in SMS LONG Tablespaces with multiple containers spread across *different* disks-- disks that do not contain other DB2 UDB data or logs. [...]
11597 109 39_Re: Retrieving a ROW ID after an insert14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 12 Feb 2001 14:05:17 +1100292_iso-8859-1 Just a few of points: - it's "direct row access" in the manuals - My reading of Terry's response doesn't make it clear (ie I think he meant to write, but I didn't see where he _did_ write) that, if you were storing a parent table's rowid in a child, you would have code like: [...]
11707 19 0_12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Mon, 12 Feb 2001 05:47:19 -0000549_- All, How would I delete rows from sysibm.sysstmt in Test Environment for those redundant plans ? Anyone volunteering to explain.
Regards Rakesh
_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
11727 45 0_16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 12 Feb 2001 11:52:06 +0530717_- FREE PLAN is the only NICE and CLEAN way. You can't just delete it if the plan is there.
Regards, Sanjeev
> -----Original Message----- > From: Rakesh Kumar [SMTP:rakesh457@HOTMAIL.COM] > Sent: Monday, February 12, 2001 11:17 AM > To: DB2-L@RYCI.COM > Subject: > > All, > How would I delete rows from sysibm.sysstmt in Test Environment for > those > redundant plans ? Anyone volunteering to explain. > > Regards > Rakesh > > _________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > ================================================ > To change your subscription options or to cancel your subscription [...]
11773 121 20_Re: Bad table design16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 12 Feb 2001 11:55:20 +0530575_- Martin, It seems the table is made partitioned because of the amount of data it contains. I think choosing the partitioning key and different indexes are the most important factor for designing the partitioned tablespace. These factors depends on the kind of data it is going to have and the insertion pattern. In your case, if you think that timestamp/date range can be made as the partitioning key then it will let you add one more column in your WHERE predicate which is a part of partitioning key. This will help you to do the limited partitioned scan rather than [...]
11895 212 49_Re: Result Set Not returned from Stored Procedure18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Mon, 12 Feb 2001 00:21:37 -0600703_ISO-8859-1 Howzit Folks?
Thanks for the suggestions to my problem.
I turns out that once I coded a call to DSNTIAR I got the SQL diagnostic information I required to solve the problem.
Cheers
Bruce
On Wed, 7 Feb 2001 08:00:42 -0500, db46@DAIMLERCHRYSLER.COM wrote:
>---------------------- Forwarded by Dean J Burchill/TCC/DCC/DCX on >02/07/2001 07:59 AM --------------------------- > > >Dean J Burchill >02/06/2001 03:32 PM > >To: DB2 Data Base Discussion List >cc: > >Subject: Re: Result Set Not returned from Stored Procedure (Document > link: Dean J Burchill) > > >Okay, what is meant by 'successful' return? If you defined one [...]
12108 55 26_Locating a RID - OS/390 V510_Joan Green22_Joan.Green@BOIMAIL.COM31_Mon, 12 Feb 2001 11:21:41 +0000410_us-ascii Hi Folks,
Does anyone know if it is possible to locate a specific row in a table from the RID provided in the SQLCA e.g. I get the message :
DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE INDEX IN INDEX SPACE IXABC001 CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'0000000201. [...]
12164 38 52_24x7 DB2 V6 Catalog Housekeeping (with Data Sharing)19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Mon, 12 Feb 2001 06:14:53 -0600343_us-ascii We are currently implementing DB2 V6 with Data Sharing as the first stage of going towards "24x7" availability for our web-based systems.
We currently run housekeeping on the catalog. We have a job which -
Quiesce and copy catalog and directory Forces an archive log
The latter of these two processes does - [...]
12203 83 56_Re: 24x7 DB2 V6 Catalog Housekeeping (with Data Sharing)0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Mon, 12 Feb 2001 07:08:13 -0600548_us-ascii Hi Philip, We are also V6 and data sharing.
As for us, we don't stop DSNDB06 or DSNDB01. we have a rexx exec that attempts to issue a nightly -ARC LOG MODE(QUIESCE) WAIT(YES) TIME(60) Our rexx exec checks the resulting return code. If the process was not successful, it will try again. If after 5 attempts we are unable to quiesce all activity on the system, we will then issue an ARC LOG without quiesce and send a TSO message to the DBAs to look into what process was holding locks and then we follow up with the offender if [...]
12287 75 0_11_David Lewis20_David_Lewis@GLIC.COM31_Mon, 12 Feb 2001 08:43:38 -0500762_us-ascii David T. Lewis II
330 Portside Dr. Edgewater N.J. 07020
201 313-8343
"S, Sanjeev (CTS)" on 02/12/2001 01:22:06 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: David Lewis/TheGuardian)
Subject:
FREE PLAN is the only NICE and CLEAN way. You can't just delete it if the plan is there.
Regards, Sanjeev
> -----Original Message----- > From: Rakesh Kumar [SMTP:rakesh457@HOTMAIL.COM] > Sent: Monday, February 12, 2001 11:17 AM > To: DB2-L@RYCI.COM > Subject: > > All, > How would I delete rows from sysibm.sysstmt in Test Environment for > those > redundant plans ? Anyone [...]
12363 17 30_Re: Locating a RID - OS/390 V515_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 12 Feb 2001 08:10:14 -0600280_- Joan
It seems that your RID is 5 bytes long. The first 4 Bytes are the page number the fifth is the ID map entry. DB2 Diagnosis Guide handles all Rid formats in detail.
So in your case it is page number 3 (because page number 0 ist the first page) first row. [...]
12381 101 30_Re: Locating a RID - OS/390 V511_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Mon, 12 Feb 2001 09:19:18 -0500391_ISO-8859-1 Hi Joan
One way is to use the DSN1PRNT utility to print the page containing the row. The RID is made up of the tablespace page number (all except last byte, so page 2 in your case) followed by a 1-byte ID Map number. Print page 2:
//PRNT EXEC PGM=DSN1PRNT,PARM='PRINT(02,02),FORMAT' ...
The output for page 2 will look like (sorry about loss of format): [...]
12483 18 24_Re: Downloading datasets10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 12 Feb 2001 15:29:56 +0100433_us-ascii Try to use S390 UNIX system services, they work well.
Max Scarpa
Non-certified DB2 sysprog Certified DB2 V 69 for HAL 900 sysprog Certified Playboy's Centerfolds expert
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
12502 55 30_DB2 Utility Problem (00E70005)17_Erkki Søndergaard20_esonderg@SCA.CSC.COM31_Mon, 12 Feb 2001 15:38:37 +0100468_us-ascii If you don't have any other utilities active reset SYSUTILX by running the appropriate step of DSNSAMP job DSNTIJIN. After that recover all the indexes on SYSUTILX. Apperently your big SYSIN managed to hose SYSUTILX quite effectively.
-----Original Message----- From: Hall, Robert [mailto:Robert.Hall@OCC.TREAS.GOV] Sent: Saturday, February 10, 2001 10:26 AM To: DB2-L@RYCI.COM Subject: [DB2-L] DB2 Utility Problem (00E70005) Sensitivity: Private [...]
12558 62 86_Re: Help for DB2 Connect -- Problems with WebSphere on NT talking to DB2 6.1 on OS/39012_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 12 Feb 2001 06:38:50 -0800561_us-ascii What version of DB2 Connect are you running?
My client has Version 7 fix pack 2 and version 6 fixpack 4 both running Websphere 3.52 on both AIX and NT with no problems accessing DB2 6.1 OS/390 from JAVA and EJBs. --- Bernie OConnor wrote: > We are using DB2 Connect to talk to DB2 6.1 for > OS/390 from a JAVA language environment under > WebSphere 3.52 on NT 4.0. We are getting a number > of problems. The most important problem is we seem > to have is hanging transactions. Some of this is > attributable to [...]
12621 17 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 12 Feb 2001 08:28:51 -0600558_- One experience we made: It took a long time (approximately 30 minutes) to bind a package using optimizer hints, because we didn't create that strange index recommended in the red manual "DB2 UDB for OS/390 Version 6 Performance topics". The recommended Index is: QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID and OPTHINT. I didn't test that index, but we have a similar index on our plan_table: PROGNAME, COLLID, VERSION, BIND_TIME, QUERYNO. This index was not taken, because I saw in Omegamon a huge number of getpage requests against the tablespace. [...]
12639 27 38_Re: DB2 Connect MTS and 2-Phase Commit9_Ken Hynes17_khynes@TRIGON.COM31_Mon, 12 Feb 2001 08:31:12 -0600590_- My thanks to those who reponded to the requast for information on MTS and 2- Phase commit. We have gotten it working although I'm sure we will encounter a few more problems as we move forward. Essentially we needed to go back as much as possible to "default" settings. Set up as follows: Install DB2 Connect, apply fix pack 2, reboot, and define the databases using the Client Configuration Assistant. Then start the DB2 Command Window and enter the following command:>db2 update dbm cfg using spm_name "your machine name". After this you should be able to right-click on your local [...]
12667 120 56_Re: Reg. Dropping a Partitioned tables Clustering Index.15_Douwe van Sluis20_d.b.vansluis@KPN.COM31_Mon, 12 Feb 2001 15:47:59 +0100425_iso-8859-1 But of course, creating the tablespace, table and index is quit handy..... And, yes indeed, twice lucky, because we had decided earlier on that all tablespace, tables and indexes in one database would produce a DBD of hudge size. The other end was to put one table, one tablespace in one database. There was (almost) nothing against this options and it gave us the flexibility to alter per table. Good catch. [...]
12788 39 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond14_Scott Saunders20_ssaunders@SIEBEL.COM31_Mon, 12 Feb 2001 06:58:35 -0800417_iso-8859-1 Hello all,
I have been under the impression that DB2 did not support the use of hints (like Oracle does for example). Can DB2 use hints for dynamic SQL?
Scott Saunders
-----Original Message----- From: Walter Janissen [mailto:walter.janissen@VICTORIA.DE] Sent: Monday, February 12, 2001 6:29 AM To: DB2-L@RYCI.COM Subject: Re: The use of SQL Performance Hints in DB2 V6 and beyond [...]
12828 82 30_Re: Locating a RID - OS/390 V516_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 12 Feb 2001 17:21:32 +0530537_- I think it can be done with DSN1PRNT on the index. Just missing it to recall so can't confirm. Its better if you can go thru the DSN1PRNT utility and run it on the index.
HTH Regards, Sanjeev
> -----Original Message----- > From: Joan Green [SMTP:Joan.Green@BOIMAIL.COM] > Sent: Monday, February 12, 2001 4:52 PM > To: DB2-L@RYCI.COM > Subject: Locating a RID - OS/390 V5 > > Hi Folks, > > Does anyone know if it is possible to locate a specific row in a table > from > the RID provided in the SQLCA > e.g. I get the [...]
12911 85 4_-92513_Tim K. Larsen23_tim-kent.larsen@UBS.COM31_Mon, 12 Feb 2001 18:01:31 +0100343_US-ASCII Hi there!
Recently I've been experiencing -925's from an application that used to run without problems! (and "nothing special" have been changed recently :-)
I have verified that the program is linked with DSNELI, so I can't see how COMMIT processing can get the idea that I'm running under IMS or CICS :-( [...]
12997 23 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 12 Feb 2001 11:12:01 -0600560_- Performance hints are provided in Version 6 of DB2 for OS/390, with the primary intent of helping customers resolve performance regression quickly by dropping back to the access path that worked last week. It's called preserving the access path in a number of places. For more detail, the V6 Administration Guide, under chapter 5-8, Tuning Your Queries, subsection Special Techniques to Influence Access Path Selection, or the minor heading, Preserving a Prior Access Path. It's page 899 by the page numbers or 909 by Acrobat in the pdf file, dsnag0g2. [...]
13021 26 36_DB2 Connect v5.2 AIX- license daemon14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 12 Feb 2001 12:25:38 -0500419_iso-8859-1 Hi:
On one of our DB2 Connect gateway boxes we're using v5.2 EE fixpack 11. We notice that about a couple of times a week all the applications using the connections start to slow down considerably. It turns out that at such time db2licd process starts eating up all the cpu time. Once we kill the daemon everything is back to normal. Now, why would the license daemon become such a huge CPU hog? [...]
13048 124 8_Re: -92513_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Mon, 12 Feb 2001 11:11:31 -0600553_us-ascii Looks like someone changed a cics program and added a commit instead of a syncpoint
"Tim K. Larsen" cc: Sent by: DB2 Subject: -925 Data Base Discussion List
02/12/2001 11:01 AM Please respond to DB2 Data Base Discussion List
Hi there!
Recently I've been experiencing -925's from an application that used to run without problems! (and "nothing special" have been changed recently :-) [...]
13173 39 57_Re: The use of SQL Performance Hints in DB2 V6 and beyond12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Mon, 12 Feb 2001 11:38:31 -0600586_iso-8859-1 Scott:
Check section 5.8.6.6.4 in the Administration guide. This gives an example of using hints for dynamic SQL. This is a mainframe function. I do not know of its use off of the mainframe.
The most common response to my questions is "accidentally resetting previous hints."
Gerald Hodge 888-494-9019
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Scott Saunders Sent: Monday, February 12, 2001 8:59 AM To: DB2-L@RYCI.COM Subject: Re: The use of SQL Performance Hints in DB2 V6 and beyond [...]
13213 23 39_Identity Columns - Reset starting value9_Bob Lewis23_BLewis@MICROGENERAL.COM31_Mon, 12 Feb 2001 09:33:52 -0800591_iso-8859-1 Hi Listers -
Good news for you Identity columns users. In v7.1-fixpak2, you can now reset the 'sequence number' of an identify column via an Alter statement. Here's an example of the command:
db2 alter table . alter column restart with ;
Thanks, IBM...
Bob Lewis
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13237 22 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 12 Feb 2001 11:27:41 -0600522_- There are a number of papers by consultants available for downloading on the web page www.s390.ibm.com/marketing/position.html
For example there are several by D.H. Andrews and several from ITG. ITG Management Brief: Large-Scale SAP/R3 System Development (GF22-5130-00)
The ITG papers are the result of talking to a number of customers who were making or had made their decision. I'm biased, but you probably need to know why about 500 customers made this decision in the past three and a half years. [...]
13260 18 43_Re: Identity Columns - Reset starting value13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 12 Feb 2001 13:00:08 -0500444_iso-8859-1 Let's hope OS/390, z/OS follow quickly with this enhancement.
Thanks for the news, Bob.
David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13279 57 39_Identity Columns - a question for users12_Cook, Nicole19_Nicole_Cook@BMC.COM31_Mon, 12 Feb 2001 12:18:01 -0600590_iso-8859-1 Hi All.
I'm trying to get some other identity column users opinions on the following:
Let's say I make a change to the generation expression of a generated column in an existing table. I have to turn integrity checking off on the table before issuing the ALTER TABLE statement, and then turn it back on again once the change is done. When I use the SET INTEGRITY statement to turn integrity checking back on again, I have two choices: I can either tell it to check the existing values in that generated column against the value that the new generation [...]
13337 21 8_Re: -92513_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 12 Feb 2001 13:23:44 -0500568_iso-8859-1 Tim, Have a look at APARs PN29254, PQ04854, and PQ44788. They all describe -925 being issued incorrectly.
PN29254 is pretty old and not too likely to apply. Read the other 2 and see if they fit your environment.
David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13359 34 65_Executing Multiple Versions of DB2 Connect on the Same AIX Server14_Philip Trbovic19_TRBOVICP@BCBSIL.COM31_Mon, 12 Feb 2001 12:24:35 -0600368_US-ASCII Dear fellow DB2 users, I hope someone out here can help me. I have the need to have two versions of DB2 Connect executing on the same AIX server. We need to have version 5.2 and 6.1 executing at the same time.
We installed the software with no problems, but when we try to create any instance under version 6.1 we get the following error message: [...]
13394 30 38_Generation of sequence number.. Urgent8_bharat r20_bharat_321@YAHOO.COM31_Mon, 12 Feb 2001 10:45:31 -0800289_us-ascii Hi .. Is there any way to generate a sequence no. for the exported data from the table in UDB/AIX. I came to know that this facility is there in Orace by creating the object like.. CREATE SEQUENCE SEQ_NO START WITH ...
Please let me know if any one knows about this.. [...]
13425 36 0_15_Ferrari, Denise17_DFerrari@FGCU.EDU31_Mon, 12 Feb 2001 14:21:31 -050035_ISO-8859-1 SET DB2-L NOMail
13462 15 17_Incremental Binds11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Mon, 12 Feb 2001 13:04:48 -0600468_us-ascii I have a program that the packages and plan are bound validate(bind), Isolation(RR) and it does 100,000 incremental binds at run time according to the PM reports. Does anyone know why so many incremental binds?... : )
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13478 19 31_DB2 Connect and Data Encryption12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Mon, 12 Feb 2001 13:18:00 -0500578_- In the following Scenarios, what options do we have to encrypt data?
1.Windows NT Webserver accessing DB2/OS390 data via DB2 connect on the NT Webserver. NT ====> DB2/OS390
2.Windows NT Webserver accessing DB2/OS390 data via DB2 connect on AIX server. NT ====> AIX (DB2 connect) ====> DB2/OS390
Thanks in advance.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13498 49 59_Discrepancy between DB2PM On-line monitor and Batch reports6_Chenny39_solaiyappan_chenniappan@MAIL.AMSINC.COM31_Mon, 12 Feb 2001 13:57:01 -0600441_- Hi All,
Since accounting reports are near and dear to my heart to monitor the performance of our departmental warehouse, this is something i need to resolve ASAP.
Our DB2PM Accounting trace report giving us either the incorrect information or incomplete information with refernec to Class1 and Class2 Elapsed & CPU times. Whereas, DB2PM online monitor seems to give us the correct information (or complete information). [...]
13548 85 34_Re: DB2 Utility Problem (00E70005)12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Mon, 12 Feb 2001 16:26:19 -0500310_iso-8859-1 Thanks to all who replied.
I tried to restore SYSUTILX and recover with no luck. I finally gave up and restored all the DB2 datasets from the prior days FDR backup. Imagine my surprise when I received the same error. It turns out that the error occurs under the following circumstances: [...]
13634 44 21_Re: Incremental Binds14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Tue, 13 Feb 2001 08:23:51 +1100678_iso-8859-1 **************************************************************** IMPORTANT
The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...]
13679 16 51_Is there a utility for reading DB2 UDB log records?15_Lynette Roberts21_llroberts@KSCABLE.COM31_Mon, 12 Feb 2001 16:29:18 -0600312_- We have a need to gain visibility into our DB2 UDB V6.1 Fix Pack 6 database log records. On our DB2/OS390 system, we can print out log records using DSN1LOGP. Does anyone know if DB2/UDB has something similar? Barring this, does anyone know of any third party tools that can be used to read DB2/UDB logs? [...]
13696 19 27_Date conversion SQL needed!13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Mon, 12 Feb 2001 19:05:21 -0500575_- Hi Listers, A very while back, someone put out a useful SQL that does julian conversion. I need the services of just such a code. Could someone share an example if they have one handy? Thanks Carlton _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13716 68 22_High Service Wait Time5_Pearl17_panidak@SCB.CO.TH31_Tue, 13 Feb 2001 09:15:45 +0700203_windows-874 Hi List, I read Omegamon for DB2 report and found some program had high service wait time. How to solve this wait time or find cause of problem? I used DB2 V 5.1 on OS/390 V 2.5. Thanks Pearl
13785 55 31_Re: Date conversion SQL needed!16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Mon, 12 Feb 2001 21:37:48 -0500692_us-ascii Carlton,
Here you go..... Hope it's what you had in mind!
---------+---------+---------+---------+---------+---------+-- SELECT (DAYS(CURRENT_DATE) - DAYS(SUBSTR(CHAR(CURRENT_DATE),1,4)||'-01-01') + 1), YEAR(CURRENT_DATE) FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+-- 43 2001 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Carlton Enuda on 02/12/2001 07:05:21 PM
Please respond to DB2 Data Base Discussion List [...]
13841 96 31_Re: Date conversion SQL needed!13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 12 Feb 2001 21:05:53 -0600510_us-ascii Carlton,
This syntax should be independant of your site standard date format:
V5 syntax:
SELECT YEAR(CURRENT DATE) * 1000 + DAYS(CURRENT DATE) - DAYS(SUBSTR(DIGITS(YEAR(CURRENT DATE - 1 YEAR)),7,4) CONCAT '-12-31') FROM SYSIBM.SYSDUMMY1;
Or the V6 syntax:
SELECT YEAR(CURRENT DATE) * 1000 + DAYOFYEAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1;
Both return 2001043
Note: V6 also has the JULIAN_DAY function which returns the number of days since 4712 BC [...]
13938 29 34_Plan DISTSERV Locks whole Database11_Dimoka Popy24_dimokap@INTERAMERICAN.GR31_Tue, 13 Feb 2001 10:44:12 +0200668_- Hello List,
we are testing a new Java Application on a NT Machine having DB2 Connect V7 FP2 on a Database located on DB2 UDB V6 for 0S/390 (recently migrated from V5 our test environment only).Ever since,we have been experiencing some "strange" timeout conditions.Looking through the system logs,i found out that the java application ,through Plan DISTSERV, was holding locks on the WHOLE database (REASON 00C9008E,TYPE 00000100,NAME TDBAUTO),so we couldn't create a new table,alter an existing one etc.I thought i could change the options under which plan DISTSERV was bound,but that plan doesn't really exist....The programs do contain explicit commit [...]
13968 29 26_Re: High Service Wait Time14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Tue, 13 Feb 2001 03:47:03 -0600480_iso-8859-1 There are a number of common reasons for high service wait time. You should look at what your program is doing to see if any (or all) of these apply...
1) Waiting dataset recall from HSM. You should see messages in the DBM1 JES log about this.
2) High insert activity with small secondary extent size is causing long waits while VSAM gets another extent. You can check this by doing a LISTC ENT(???) ALL on the physical objects used by your program. [...]
13998 41 26_Re: High Service Wait Time16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 13 Feb 2001 17:05:34 +0530577_- Hi Pearl, Look at the Archive for related thread during August/Sept.'2000. If i can recall correctly, there were some good discussion at that time and some 8-10 replies are there.
Regards, Sanjeev
> -----Original Message----- > From: Pearl [SMTP:panidak@SCB.CO.TH] > Sent: Tuesday, February 13, 2001 7:46 AM > To: DB2-L@RYCI.COM > Subject: High Service Wait Time > > Hi List, > I read Omegamon for DB2 report and found some program had high service > wait time. How to solve this wait time or find cause of problem? I used > DB2 V 5.1 on OS/390 V 2.5. > > [...]
14040 72 44_NT, AIX/UDB-6.1/Storing Images in a database15_Agarwal, Ashish28_Ashish.Agarwal@HPSGLOBAL.COM31_Tue, 13 Feb 2001 17:31:57 +0530467_- Hi All, we are planning to store images(JPEG/GIF) inside the database(BLOB) itself instead of on the operating system. Our image size at max will be 64K. I want to know what recovery and tuning measures should I keep in mind for storing the images. Further , should we keep the images table in a separate tablespace as we expect quite a large no of images. Do we need to change the buffer pool size too in this case. Hope the problem is clear. Regards Aashish [...]
14113 102 55_R: 24x7 DB2 V6 Catalog Housekeeping (with Data Sharing)18_Mandolini, Roberto25_roberto.mandolini@EDS.COM31_Tue, 13 Feb 2001 09:34:33 -0000559_iso-8859-1 Hi Philip, We are runnig in a 3 way Db2 datasharing enviromnent (IMS e CICS) - Db2 V5.1 We use a tool like !candle workbench (but there are similar product from CA, BMC and IBM) to get a footprint of catalog for DDL, plan, packages, etc. Then we perform an IBM image copy with shrlevel change option to copy catalog and directory (during the night) followed with a command of: -archive log scope(group) This command is enough to get a syncpoint for consistency. At this time we dont need to get an outage for DB2 for houskeeping Cat. and Dir. [...]
14216 57 48_Re: NT, AIX/UDB-6.1/Storing Images in a database16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 13 Feb 2001 18:08:43 +0530563_- Hi Aashish,
BLOB,CLOB etc data types are in its own tablespace with separate BP defined. Only the references are kept in the table columns. The way of accessing these data types are also different.
The informations related to recovery and tuning is bit different. If you do have IBM manuals for DB2 V6 or above then you can refer for all the details. As you did not specify the platform, so i will considering it OS/390. You have also not specified which version of DB2 you are mentioning. I assume it to minimum with this facilities i.e V6. [...]
14274 49 48_Re: NT, AIX/UDB-6.1/Storing Images in a database16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 13 Feb 2001 18:09:51 +0530627_- I am sorry , you did specify the platform in your subject. Anyway, i am sure i am not that much comfortable with NT/AIX, so choosen my own interests platform. Everything else is same i think.
My opologies !
Regards, Sanjeev
> -----Original Message----- > From: Agarwal, Ashish [SMTP:Ashish.Agarwal@HPSGLOBAL.COM] > Sent: Tuesday, February 13, 2001 5:32 PM > To: DB2-L@RYCI.COM > Subject: NT, AIX/UDB-6.1/Storing Images in a database > > Hi All, > we are planning to store images(JPEG/GIF) inside the > database(BLOB) itself instead of on the operating system. Our image size > at max will be 64K. I [...]
14324 210 12_Re: 00E3008311_Pedro Cunha18_pplfilho@IG.COM.BR31_Tue, 13 Feb 2001 07:04:03 -0600428_- Hello Listers,
I have seen this before in the list as you can see below. I would like to re-open the thread. We are trying to bind a package, release(Deallocate) in a datasharing environment, and we are at V.5.1 DB2 OS390:
DSNT500I -DB2P DSNTBCM2 RESOURCE UNAVAILABLE REASON 00E30083 TYPE 00000801 NAME MYCOLL .PROGRAM .167E7B580A25DC9C DSNT233I -DB2P UNSUCCESSFUL BIND FOR PACKAGE = DB2P.MYCOLL.PROGRAM.() [...]
14535 62 34_Oracle G'Way & DB2 Data Sharing???16_Charles Valentin15_VALENCH@AOL.COM29_Tue, 13 Feb 2001 08:46:53 EST572_US-ASCII Hi List, Anyone out there using Oracle G'Way V8.0.4 from Sun Solaris to DB2/OS 390 V6.1 in Data Sharing mode? We recently renamed our test DB2T for Data Sharing, Oracle G'way Started Task on OS/390 says it connected to DB2T but when client tries to connect to Group Attach Name(DB2T), he gets error saying it can't connect. Group Attach Name is: DB2T and member names are: DTI1 & DTI2. Sun Client is setup to point to DB2T. Do they need to change Oracle Application to point to DTI1 or DTI2? Any recommendations will be greatly appreciated. We have open TAR [...]
14598 92 38_Re: Oracle G'Way & DB2 Data Sharing???17_Massimo Biancucci19_mbiancucci@DEBIS.IT31_Tue, 13 Feb 2001 15:05:18 +0100392_us-ascii
I don't have a good knowledge of OTG but i'm still using it (V4 on production with D/S and V8 on development) with DB/2 V5.1 . Since the beginning the only thing client knows is the OTG applid and not the DB2 but probably my environment is too different from your; Anyway, in the OTG parameter I indicate the DB/2 name and, for D/S environment, the member name. [...]
14691 59 59_Re: Comparisons between SAP on Oracle (AIX) and DB2(OS/390)23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 13 Feb 2001 14:17:22 -0000407_- Robert
I am back in the UK next Monday (in the US at the moment).
mail me then and I will see if I can get any papers from when we did the transition.
regards
Leslie
-----Original Message----- From: Robert Ord [mailto:robertord@HOTMAIL.COM] Sent: Friday, February 09, 2001 2:43 PM To: DB2-L@RYCI.COM Subject: Comparisons between SAP on Oracle (AIX) and DB2(OS/390) [...]
14751 123 31_Re: Date conversion SQL needed!0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 13 Feb 2001 08:54:13 -0600443_us-ascii V6 also has 'DAYOFYEAR' function: The DAYOFYEAR function returns an integer in the range of 1 to 366 that represents the day of the year where 1 is January 1.
Terry Purcell @RYCI.COM> on 2001/02/12 09:05:53 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List [...]
14875 14 26_Re: High Service Wait Time0_26_truman.g.brown@VERIZON.COM31_Tue, 13 Feb 2001 09:55:38 -0500339_us-ascii Check commit frequency and number of updates - it could be due to logging.
George
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14890 14 26_Re: High Service Wait Time0_26_truman.g.brown@VERIZON.COM31_Tue, 13 Feb 2001 09:57:04 -0500315_us-ascii Also could be page splits if updating/inserting...
George
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14905 118 31_Re: Date conversion SQL needed!13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 13 Feb 2001 09:10:21 -0600509_US-ASCII Ron,
That is the function that was used in the V6 SQL below......
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Rohn.Solecki@MTS.MB.CA Sent: Tuesday, February 13, 2001 8:54 AM To: DB2-L@RYCI.COM Subject: Re: Date conversion SQL needed!
V6 also has 'DAYOFYEAR' function: The DAYOFYEAR function returns an integer in the range of 1 to 366 that represents the day of the year where 1 is January 1. [...]
15024 27 38_Re: Plan DISTSERV Locks whole Database14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US31_Tue, 13 Feb 2001 10:34:03 -0500383_US-ASCII Hey Popy:
No real need to hoss around with DISTSERV, although I've seen people go ahead and explicitly bind it to a DB2 subsystem in the hopes of better performance. It seems to me that when you bind to the OS/390 AS from your NT AR that all packages belong to COLLID "NULLID". Take a look on the AS to see what bind parms are in force there for a few of these. [...]
15052 37 23_Trigger Question in UDB0_24_db46@DAIMLERCHRYSLER.COM31_Tue, 13 Feb 2001 10:52:40 -0500472_us-ascii Can anyone help me on a trigger question in UDB.
We have a trigger on almost every table in this particular database. They are all defined as
CREATE TABLE "TableX" ( "TableX_ID" CHARACTER (13) FOR BIT DATA, COL1... COL2... CONSTRAINT "TABLEXIDPK" PRIMARY KEY ("TableX_ID") );
CREATE TRIGGER TableXID_UNIQUE NO CASCADE BEFORE INSERT ON TableX REFERENCING NEW AS NEW_UPD FOR EACH ROW MODE DB2SQL SET NEW_UPD.TableX_ID = GENERATE_UNIQUE(); [...]
15090 22 20_Urgent help needed..8_bharat r20_bharat_321@YAHOO.COM31_Tue, 13 Feb 2001 07:59:25 -0800538_us-ascii Is there any function to generate Seq.no while selecting a Table.. Pl.help me immediately..
Bharat
__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
15113 58 26_Re: High Service Wait Time10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Tue, 13 Feb 2001 10:04:07 -0600582_us-ascii Please don't overlook the DEGREE(ANY) bind option. We had this and rebound the packages with DEGREE(1) and our service wait times went from hours to seconds or minutes....
What we found was:::::::::;For every SQL that comes thru it (DB2) needs to evaluate whether or not the query is a good candidate for parallelism, each of these 'checks' and 'wait for response' logs as a service wait count & adds to the total service wait time. You can match your entry/exit events pretty closely with your SQL * 2 (entry 1 exit 1) and your service wait count will be very [...]
15172 60 38_Re: Plan DISTSERV Locks whole Database22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Tue, 13 Feb 2001 14:22:07 -0300320_iso-8859-1 We have been facing the same problem here. ODBC threads put an IS lock on the database, so it is released only when the application issues an SQL COMMIT. You can try working it out by setting "autocommit on" and "commit after closing cursor" on ODBC Client configuration.
Here is DB2 V5.1 CAE V5. [...]
15233 173 26_Re: High Service Wait Time10_Keith Kuhn21_Keith_Kuhn@WENDYS.COM31_Tue, 13 Feb 2001 11:32:08 -0500589_us-ascii Missy, What version of DB2 has this problem, or is this a problem in any version? (DEGREE(ANY))? Thanks, Keith
Missy Case Sent by: DB2 Data Base Discussion List 02/13/01 11:04 AM Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: Re: High Service Wait Time
Please don't overlook the DEGREE(ANY) bind option. We had this and rebound the packages with DEGREE(1) and our service wait times went from hours to seconds or minutes.... [...]
15407 40 31_Re: Date conversion SQL needed!15_Lynette Roberts21_llroberts@KSCABLE.COM31_Tue, 13 Feb 2001 10:21:02 -0600740_- We've used a very simple conversion on julian dates for years:
SELECT DATE (DIGITS(the_julian_column_name)) FROM table
Our julian date columns are defined as DECIMAL(7,0). It produces output like the following:
SELECT COMP_INSTALL_DATE, DATE (DIGITS(COMP_INSTALL_DATE)) FROM DB2PP.VHAATAJ WHERE COMP_AIRCRAFT_TYPE = 'CJ' AND COMP_MODEL = '650' AND COMP_SERIAL = '-0001' ---------+---------+---------+--- COMP_INSTALL_DATE ---------+---------+--------- 1983305. 1983-11-01 2000273. 2000-09-29 2000060. 2000-02-29 2000326. 2000-11-21 2000345. 2000-12-10 2000273. 2000-09-29 1999042. 1999-02-11 1999180. 1999-06-29 1999042. 1999-02-11 1997107. 1997-04-17 1995177. 1995-06-26 1997197. 1997-07-16 2000216. 2000-08-03 [...]
15448 127 26_Re: High Service Wait Time10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Tue, 13 Feb 2001 11:00:13 -0600376_us-ascii Keith,
We were V6 (OS/390) when we found this issue. I think they used the 'working as designed' caveat when we called it in. If you have all the stuff in place for parallelism, it's a good feature, if you bind pkgs in one place and run them in another and DB2 needs to decide at run time, it got expensive for us. As always, YMMV (Your Mileage May Vary) [...]
15576 72 48_Re: NT, AIX/UDB-6.1/Storing Images in a database13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Tue, 13 Feb 2001 09:18:19 -0800588_us-ascii Hi Aashish (Is that how you spell it!!),
Following snippet might help:
If a database contains large amounts of long field and LOB data, backing up the database could be very time-consuming. The BACKUP command provides the capability to back up selected table spaces. If you use DMS table spaces, you can store different types of data in their own table spaces to reduce the time required for backups. You can keep table data in one table space, the LONG and LOB data in another table space, and the INDEX data in another table space. By storing long field and [...]
15649 55 24_Decimal parsing in SQLDA25_Loganathan M Ramachandran41_loganathan.m.ramachandran@MAIL.SPRINT.COM31_Tue, 13 Feb 2001 11:13:42 -0600341_ISO-8859-1 Hi list,
Ours is DB2 V6/COBOL.
We are trying to dynamically retrieve rows from tables and do processing. We have two decimal fields (6,5) and (10,3) in one of our tables and we are facing problems when we try to stored the values in these columns.
For byte manipulation we are using the following logic: [...]
15705 36 72_Central PA DB2 Users Group - Susan Lawson, YL&A - March 28th-29th - $15010_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Tue, 13 Feb 2001 12:27:50 -0500569_us-ascii Hi everyone! It is my pleasure to be hosting Susan Lawson of YL&A for 2 days of DB2 classes right here in Central PA!! Although this will be Susan's first visit to our User Group, many of you are probably already very familiar w/Susan as the heart & soul behind the successful world-wide DB2 Symposium classes. At numerous IDUG events, she's been named Best Overall Speaker. She's also an IBM Gold Consultant who has authored books such as the popular DB2 Answers & DB2 High Performance Design and Tuning. I am positive that you will find her & the class [...]
15742 52 24_Re: Urgent help needed..15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Tue, 13 Feb 2001 11:31:36 -0600541_iso-8859-1 There have been several discussions about this in the past. You should be able to find something in the archives at www.ryci.com/DB2-L.
Chris Blaicher
BMC Software, Inc. Austin Research Labs 10415 Morado Circle Austin, TX 78759 512/340-6154
BMC Software, Inc. makes no representations or promises regarding the reliability, completeness, or accuracy of the information provided in this discussion; all readers agree not to rely on or take any action against BMC Software in response to this information. [...]
15795 78 35_Re: DB2 Connect and Data Encryption10_Park, Stan17_SPark@STATE.NM.US31_Tue, 13 Feb 2001 10:49:00 -0700612_iso-8859-1 We are interested in this also. Here is a dated response from Toronto (from a search on www.deja.com)
From: Brad BARCLAY (bbarclay@ca.ibm.com) Subject: Re: How to encrypt a connection to a DB2 database? (SSL??) [view thread] Newsgroups: comp.databases.ibm-db2 Date: 2000-10-05 15:21:10 PST
Marcus Wirth wrote: > > Hello, > > I'd like to connect users (e.g. at home) with our DB2 database > via the internet. It's quite easy to set up a server to listen for > requests on port xxx and make a client connect to that port > but as our data should be carried across the [...]
15874 37 49_Invitation: Quest Central for DB2 Conference Call13_Jim Wankowski23_jim.wankowski@QUEST.COM31_Tue, 13 Feb 2001 11:40:53 -0600681_- Satisfy your Hunger for DB2 Tools with Quest Central for DB2!
Quest Central for DB2 combines the four essential food groups for today's DBA - performance monitoring, database administration, SQL tuning and space management - in one compact, easy-to-use interface. Quickly identify and resolve performance bottlenecks Save time and eliminate errors associated with DB2 UDB object management Identify and resolve potential space and performance related problems within your database Ensure optimal database performance by tuning SQL statements Find out more about what Quest Software is cooking up for DB2! Join the Quest Central product management team to see how the [...]
15912 19 17_Reactive Governor11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Tue, 13 Feb 2001 14:03:00 -0600388_us-ascii We are a DB2 6.1, OS390 2.6, TCPIP shop. Does anyone use the Reactive Governor to limit every query to say 5 min (for example) of CPU by default. What I have discovered is that you must explicitly specify either the Auth ID or Plan Name to get the Governor to work. There doesn't seem to be a way to stop queries without first knowing either the Plan Name or Auth ID... : ( [...]
15932 30 25_Statement Cost estimation11_David Nance16_DWNance@FHSC.COM31_Tue, 13 Feb 2001 15:44:18 -0500407_US-ASCII Is anyone using the dsn_statemnt_table to decipher the cost of an sql statement. I have a query that runs subsecond, but then when I add an order by the cpu time goes up to almost 25 secs. When I explained the two queries they are pretty similar, I've been doing some reading, but IBM's website has been extremely slow today. We are V6 on OS390. Here is the result from my stmt table. Thanks. [...]
15963 73 28_Re: Decimal parsing in SQLDA11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 13 Feb 2001 16:10:48 -0600406_- Loga,
The calculations for precision and scale are correct (the divide by 256 in a cobol program has bemome visible fo the first time in V6 manuals - all examples prior to it were in PL/I, Assembler or C). A few things to check:
(1) Does the column allow nulls? If so, has this been handled? (2) Have you tried moving to 999999 display field first followed by a move to 9(1)v9(5) etc? [...]
16037 37 26_db2 data modeling question10_Chris Kern23_Chris_Kern@VANGUARD.COM31_Tue, 13 Feb 2001 17:10:54 -0500313_us-ascii Hi list, This is a data modeling type question and any assistance would be great.
Can a primary key also be set up to be a foreign from a another table and is this an acceptable model for design .
Example: Table 1 has SSN_COL as primary key Table 2 has COURSE_ NUM_COL as primary key. [...]
16075 52 43_Error SLQ20204N with Java SP in DB2 7.1 UDB14_Harold Trammel19_hlt@NAPCC.ASPCA.ORG31_Tue, 13 Feb 2001 16:31:07 -0600474_us-ascii Good day, all.
I am having a problem migrating Java Stored Procedures from a development computer to a production server. The SP's were built using Stored Procedure Builder and work quite well on the development computer. Both computers are running Windows2000 and both have fixpack2 applied.
I have copied the appropriate JAR file from the SQLLIB\function\jar\antox directory on the devel machine to the same directory on the production server. [...]
16128 25 16_DSNTEP2 question11_Tom Marcano23_marcano_db2@HOTMAIL.COM31_Tue, 13 Feb 2001 22:38:45 -0000414_- Hello All,
I am pretty sure that anyone knows the answer for this questions. I am selecting some data using DSNTEP2 , I already know that the record length for the SYSPRINT could not be more than 133 bytes. I wonder if somebody knows a way to change this length of this file to be more than 133 bytes, because I like to get the report with the spufi's format(length=4092, format=vb, blocksize=4096). [...]
16154 74 30_Re: db2 data modeling question14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 13 Feb 2001 16:43:01 -0600706_iso-8859-1 Yes, actually you are defining the PURE relational model. Don't change it.
Regards, Richard
Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Chris Kern > Sent: Tuesday, February 13, 2001 4:11 PM > To: DB2-L@RYCI.COM > Subject: db2 data modeling question > > > Hi list, > This is a data modeling type question and any assistance would be great. > > Can a primary key also be set up to be a [...]
16229 28 0_14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 13 Feb 2001 16:51:14 -0600518_iso-8859-1 Toine,
We need to talk IMMEDIATELY when you return from the mountains in Tibet! That is, if you want to have fun doing what you enjoy, rather than what you are being pushed to do now. Enjoy Himalayas!! Talk in a month or so...
Regards, Richard cell: 1-217-553-5535
Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums [...]
16258 70 38_Re: Plan DISTSERV Locks whole Database12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Wed, 14 Feb 2001 09:55:09 +1100360_- Popy, I've never actually played with it but there is a Z-parm "Idle Timeout (IDTHTOIN)" for distributed threads that MAY help.
Of course the assumption that I'm making is that the Distributed transaction hasn't committed properly, and it's sitting idle. Like everything else though the application would need to able to handle a dis-connection. [...]
16329 45 0_14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 13 Feb 2001 17:15:22 -0600586_iso-8859-1 Ignore below - private joke email went wrong way...oh well!!!!!!!!!!!!!!!!!!
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Richard Yevich > Sent: Tuesday, February 13, 2001 4:51 PM > To: DB2-L@RYCI.COM > Subject: > > > Toine, > > We need to talk IMMEDIATELY when you return from the mountains in Tibet! > That is, if you want to have fun doing what you enjoy, rather > than what you > are being pushed to do now. Enjoy Himalayas!! Talk in a month or so... > > Regards, > Richard > cell: 1-217-553-5535 > [...]
16375 75 30_Re: db2 data modeling question11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 13 Feb 2001 17:04:06 -0600559_- Chris,
The "associative" entity you describe should nearly always be created to have a primary that is a composite of the 2 primary entities it associates.
To introduce a system-assigned artficial number means that you end up with an extra index (just to support the PK). From a theoretical perspective, the reason for not using a column with a business meaning as part of PK is that it can change. However, for associative entities that cannot exist without the primary entities being present, this (in my opinion) should NEVER be done. [...]
16451 74 30_Re: db2 data modeling question19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Tue, 13 Feb 2001 16:41:43 -0800441_- The only concern I'd express is the apparent use of SSN (presumably as issued by the Social Security Administration) as a primary key.
1. Only if you are making taxable payments (e.g. payroll or interest) can you REQUIRE someone to give you their SSN (per Federal law). 2. Any external data source introduces the risk of being miskeyed (i.e. you have to code for key changes). 3. Over time, SSN is not guaranteed to be unique. [...]
16526 85 28_Re: Decimal parsing in SQLDA14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 14 Feb 2001 12:16:04 +1100298_iso-8859-1 You have to add 2 before dividing by 2 to get the byte length. That is DEC(6,5) requires 4 bytes: x'0dddddds' - where dddddd are the six digits and s is the sign. (It seems you made two errors which cancelled each other out to get the correct length of 6 bytes to store DEC(10,3).) [...]
16612 204 20_Re: DSNTEP2 question14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 14 Feb 2001 12:26:14 +1100467_iso-8859-1 Tom
Unlike SPUFI, the code's available in SDSNSAMP(DSNTEP2). Why don't you modify it? There are specific preprocessor variables to do what you want.
Alternatively, you could try running SPUFI in batch. Here's a posting of a few years ago: -----Original Message----- From: Ralph Alcorn [mailto:Ralph.Alcorn@NCAL.KAIPERM.ORG] Sent: Thursday, September 03, 1998 3:36 AM To: DB2-L@AMERICAN.EDU Subject: Re: Wot no DSNTEP2 [...]
16817 57 53_Re: Invitation: Quest Central for DB2 Conference Call5_chris22_hhardy1@HOUSTON.RR.COM31_Tue, 13 Feb 2001 21:15:15 -0800392_iso-8859-1 Is it possible to pur your presentaiton in the run time format for viewing.... some of our stations don't support the PPT viewer thanks....
----- Original Message ----- From: "Jim Wankowski" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, February 13, 2001 9:40 AM Subject: Invitation: Quest Central for DB2 Conference Call [...]
16875 51 21_Re: Reactive Governor16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 14 Feb 2001 11:03:07 +0530399_- As you said, "every query", i understand the query can run from QMF, SPUFI, Application programs, command center etc etc etc... Every query is associated with a plan. It can either be the plan of QMF, Spufi or any other query running products. You can specify PLAN NAME as blank if you want every query not to go up 5 mins. If you are using the package then its altogether a different story. [...]
16927 72 24_Re: Urgent help needed..13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 13 Feb 2001 23:53:56 -0600389_us-ascii Bharat,
I know we've been over "selecting the top x rows" many times, but this is slightly different, since those examples generally do not require a row number to be retrieved.
On non-OS390, this is quite simple, and performs well using the V7 ROW_NUMBER function.
SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY EMPNO) AS SEQNO FROM EMPLOYEE ORDER BY EMPNO [...]
17000 9 23_Test.. Please Ignore...32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Wed, 14 Feb 2001 11:13:03 +0200244_-
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17010 68 28_Re: Decimal parsing in SQLDA14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Wed, 14 Feb 2001 03:58:24 -0600302_iso-8859-1 If you are using COBOL then you should use DCLGEN to generate your COBOL copybooks. This will give you a COBOL layout that matches the table definition. If you do not use the DCLGEN copybook for a supported language, then you are trying very hard to make things difficult for yourself. [...]
17079 115 26_AW: High Service Wait Time12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 14 Feb 2001 11:53:46 +0100766_iso-8859-1 Missy
are you now binding all your packages with DEGREE(1) ? Or do you have some rules established when to bind with DEGREE(1) and when to bind with DEGREE(ANY) ?
With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Disclaimer: All thoughts are mine and not of the company. [...]
17195 111 38_Re: Plan DISTSERV Locks whole Database15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Wed, 14 Feb 2001 12:09:00 +0100442_iso-8859-1 Popy,
This happened to me too a while ago. DISTSERV is the plan for the dynamic SQL. The JAVA application probably uses dynamic SQL and while the SQL is PREPARED (BIND), it locks database resources (DBD). A solution might be using stored procedures, which is static SQL and BINDed. Thus you won't have trouble with locks on the database. Another way is to use the JAVA-CICS gateway, using 'normal' Static SQL programs. [...]
17307 23 14_UDF / Resident13_Wolfgang Wolf17_wolferl69@GMX.NET31_Wed, 14 Feb 2001 12:29:56 +0100259_us-ascii Hi, I am starting with UDF on DB2 R610 for MVS; I have problems when creating these functions with 'STAY RESIDENT YES'; What have I to do when I have to change the UDF? Stop / Start UDF does not load the new UDF?!
thanks for your help!
17331 91 21_Re: Reactive Governor10_CEI, Bruno20_Bruno.CEI@SEMA.CO.UK31_Wed, 14 Feb 2001 13:14:39 -0000423_iso-8859-1 Jeff,
The RLF works only for the dynamic SQL run in DB2. Therefore you cannot stop "every query" that has been running for more than 5 minutes, since anything running static SQL won't be controlled.
Bruno
-----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Sent: 14 February 2001 05:33 To: DB2-L@RYCI.COM Subject: Re: Reactive Governor [...]
17423 61 52_Enterprise JAVA BEAN find by primary key extra calls12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 14 Feb 2001 05:48:04 -0800591_us-ascii There's a development team at my current client that's using EJB and websphere to access OS/390 DB2 V6. We're having a bit of discussion about the use of the EJBs and the use of the findbyprimary key bean usage.
What is currently happening is that for each primary key in each table, a SELECT statement is being passed to DB2 which looks something like this -- SELECT primary key from table where primary key = 'value'; (Obviously the primary key could be multiple columns and then the where would have multiple column equals). Sample: SELECT cola, colb from taba where [...]
17485 112 30_Re: db2 data modeling question19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 14 Feb 2001 08:05:40 -0600461_ISO-8859-1 kdog?, I agree with Tim. One way to get around the SSN problem would be to use something like a studentNumber as the PK in Table 1, e.g. Table 1, studentNumber, SSN, ... where PK = studentNumber Table 2, courseNumber, ... where PK = courseNumber Table 3, studentNumber, courseNumber, ... where PK = studentNumber, courseNumber In this manner you can change SSN without changing Tables 2 and 3. Choosing a PK can be a very interesting challenge! [...]
17598 27 71_Need Ideas/suggestions ... loading ASCII data into a DB2 for NT (v.6.1)15_Joel Zigelstein20_jzigel1@HALLMARK.COM31_Wed, 14 Feb 2001 07:58:19 -0600482_- HI everyone ...
My experience mostly exists with DB2 for VM and VSE but I have a UDB qustion.
I have a project upcoming in which we are going to receive POS data from our stores. This data is in a record format that is custom to the POS system. This data is to be loaded to the mainframe and validated and fed into our in-house legacy systems. This validation will then produce a text file which will be sent back to the NT box and used to load the UDB tables. [...]
17626 43 47_Java Stored proc problem after applying Fixpak214_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Wed, 14 Feb 2001 09:41:21 -0500577_iso-8859-1 Hi,
First of all, many thanks to the people in this discussion group who have provided and continue to provide useful information and solutions.
This is a problem we are getting in our Stored Procedure Builder during building Java stored procs, AFTER applying Fixpak 2. Environment: DB2 V7.1 on NT.
All of our Java SPs are part of Java packages. For e.g. a SP called JSPMYPROCEDURE with a class-name as Jspmyprocedure has a package statement: package com.sp.abc.db.jsp.data; Now when I try to build this in the SPB, it gives back an error: [...]
17670 50 75_Re: Need Ideas/suggestions ... loading ASCII data into a DB2 for NT (v.6.1)14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Wed, 14 Feb 2001 09:51:03 -0500363_iso-8859-1 Did you look at the 'Import' utility in the Control Center ? From the list of tables, right click on a table name, then click on 'Import'. This lets you specify a file from where you can load data into the table, with differennt import modes (insert, replace etc.). You can specify one of four import file types (ASC, DEL, WSF, IXF), accordingly. [...]
17721 48 13_sqlcode - 54014_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Wed, 14 Feb 2001 16:01:15 +0100565_us-ascii Hello everyone out there,
we're facing a problem here none of us understands, perhaps someone out there can shed some light on it. We have two tables, tab1 and tab2, where tab 2 is a dependent table from tab1. Tab2 has a primary key alright, tab1 hasn't, and my understanding of db2 for os/390 v5 is that you don't need a primary key for relationships, you do need a unique index on the respective columns to ensure uniqueness, however. Now, tab1 does have a unique index on the columns the foreign key is defined on; when we're trying to bind [...]
17770 27 51_Package Cache Overflow UDB 6.1/AIX - Urgent Problem13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Wed, 14 Feb 2001 09:33:52 -0600612_us-ascii Hi, We are having a problem with a package cache overflow error that escalates to lock escalation and out of memory errors which essentially stops the DB2 system. The pckcachesz was set at (8*MAXAPPLS) 4K pages with MAXAPPLS being set to 800 on both test and production. We were doing finally testing on the test system before moving a applications upgrade to production this weekend when we started getting these errors. Snapshot monitors on both systems showed the package cache highwater mark at 26 million bytes (8*800*4096) on the production system and 39 milliion bytes on the test system. I [...]
17798 19 20_DB2 MVSScript to AIX14_busayo adebayo15_bayo9@YAHOO.COM31_Wed, 14 Feb 2001 07:50:56 -0800512_us-ascii Hi All, Does anyone know of a tool that can convert DB2 MVSscript to AIX script? Thanks
__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17818 16 17_Re: sqlcode - 54015_Walter Janissen27_walter.janissen@VICTORIA.DE31_Wed, 14 Feb 2001 09:25:49 -0600456_- Hello Ruediger
Only some guesses:
1. Can you do successful selects against the table? 2. What is the qualifier during bind? Did the package work on the same table are another which you didn't check?
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17835 63 51_FW: Java Stored proc problem after applying Fixpak223_Scarcella, Gary V (ING)23_ScarcellaGV@ING-AFS.COM31_Wed, 14 Feb 2001 11:33:36 -0500453_iso-8859-1 FYI. I also noticed the same problem AFTER applying Fixpak 2. Environment: DB2 V7.1 on NT. So you're not alone, but I don't have any solutions to offer.
Gary Scarcella Internet address: ScarcellaGV@ING-AFS.com
-----Original Message----- From: Sinha, Abhijit [mailto:Abhijit.Sinha@GS.COM] Sent: Wednesday, February 14, 2001 9:41 AM To: DB2-L@RYCI.COM Subject: Java Stored proc problem after applying Fixpak2 [...]
17899 50 22_DB2EUG: DB2 SPB Config8_DL Ragar28_nrragad@MAIL.DNR.STATE.MO.US31_Wed, 14 Feb 2001 11:10:04 -0600362_us-ascii How/where do you configure the DB2 Stored Procedure Builder to use a specific Schema (instead of defaulting to the UserId) for the Procedure name? Also, can you configure it to store the output files ("JAR", "CLASS", etc.) to a different directory/folder then the "Function" subdirectory under the Instance Directory? Our environment: DB2/NT V6.1. [...]
17950 20 22_DB2EUG: DB2 SPB Config12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 14 Feb 2001 19:55:48 +0200443_iso-8859-1 Hi, At the SPB / sql statement / sql assist / tables / filter schemas you see a list of available schemas.
Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17971 96 30_Re: db2 data modeling question12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 14 Feb 2001 20:06:16 +0200433_iso-8859-1 Hi, That's why we resorted to using an OID (ObjectID) which is "randomly" generated from the original number and keep a translation table (for possible duplicates in the randomizer).
Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com
The only concern I'd express is the apparent use of SSN (presumably as issued by the Social Security Administration) as a primary key. [...]
18068 199 30_Re: AW: High Service Wait Time10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 14 Feb 2001 10:11:35 -0600596_iso-8859-1 Georg,
We have a tickets system that runs with about 1 billion rows that we run with parallelism. There are a handful of other systems we bind some batch programs as DEGREE(ANY) which is why we didn't allow/disallow parallelism with the ZPARM settings. It is just one more place where the developers, data modelers & DBA's have to be & keep informed of the data structures, user requirements & the newest options of DB2 - and ATTEMPT to find a meeting place somewhere in the middle ground where you get functionality & some kind of decent performance while actually giving [...]
18268 16 18_SQL reference book13_Johnson, Bill18_bjo23@ALLSTATE.COM31_Wed, 14 Feb 2001 12:38:39 -0600380_- I'm looking for any recommendations for a mainframe SQL reference book. Any suggestions? Thanks, Bill Johnson DBA Allstate Insurance Co.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18285 16 32_RAID for table and index spaces?17_Wood, Christopher17_cwood@CAYENTA.COM31_Wed, 14 Feb 2001 10:38:28 -0800473_ISO-8859-1 We are running on an F50 with 4 processors and 6 internal disks. Should we use RAID for tablespaces and indexspaces?, and if so what level?
I know the answer is "it depends", but could you throw some issues at me?
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18302 23 24_One Process - Many Users32_=?iso-8859-1?Q?St=E9phane?= COEZ23_scoez@HARRYSOFTWARE.COM31_Wed, 14 Feb 2001 19:50:15 +0100194_iso-8859-1 Hi, DB2 UDBV6.1 on OS/390 V2R8
I need to be able to make several queries for several users in the same process. I try to use SQLID without success. Any ideas ? Regards.
18326 41 40_table space partitioning on a Unix (AIX)15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Wed, 14 Feb 2001 14:00:59 -0500335_iso-8859-1 Listserv UDB gurus! We have experience with DB2 on 390, but less than little on other platforms
Does anyone have any rules of thumb on table space partitioning on a Unix (AIX) computer? The kinds of questions we are asking are generally about the relationship between performance and physical placement on disk. [...]
18368 22 22_Re: SQL reference book11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET31_Wed, 14 Feb 2001 11:17:33 -0800562_US-ASCII Our application developers use DB2 for the COBOL programmer Part 1 and 2 by Garvin and Prince published by Murach. I also use DB2 Developer's Guide by Craig S Mullins.
Kathy Jones Central Information Services Clark County School District Senior Database Analyst 702-799-5040 x366 jonesks@GroupWise.ccsd.net
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18391 14 22_Re: SQL reference book15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM31_Wed, 14 Feb 2001 13:12:17 -0600418_us-ascii Gabrielle Wiorkowski's "DB2 for OS/390 Development for Performance" book has a good chapter on SQL for the mainframe. Here's her web site: http://www.gabrielledb2.com/
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18406 21 25_SyBase to UDB conversions0_24_db46@DAIMLERCHRYSLER.COM31_Wed, 14 Feb 2001 14:19:00 -0500372_us-ascii Okay folks, we've been tasked with changing a Sybase 11.0 database into a UDB database.
I took a quick look at the Sybase database and there are 844 stored procedures. Now Sybase stored procedures are basically SQL statements, but in UDB they are programs (at least for OS390 version 5).
Are there any SyBase to UDB conversion tools out there? [...]
18428 105 24_Re: Urgent help needed..8_bharat r20_bharat_321@YAHOO.COM31_Wed, 14 Feb 2001 11:21:57 -0800566_us-ascii Hi Terry.. thank you for the solution.. If I use ROWNUMBER() function seq no starts from 1. Is there any way I can start from a particular no?
Bharat --- Terry Purcell wrote: > Bharat, > > I know we've been over "selecting the top x rows" > many times, but this is > slightly different, since those examples generally > do not require a row > number to be retrieved. > > On non-OS390, this is quite simple, and performs > well using the V7 > ROW_NUMBER function. > > SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY EMPNO) [...]
18534 48 22_Re: SQL reference book9_Cam White20_cwhite@REVEALNET.COM31_Wed, 14 Feb 2001 14:42:28 -0500714_iso-8859-1 Bill,
The Knowledge Base for DB2 is an excellent resource for mainframe SQL, with examples you can copy and paste.
You can download a free 30-day trial from http://www.revealnet.com
Best wishes,
Cam White RevealNet
...do IT smarter (tm)
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Johnson, Bill > Sent: Wednesday, February 14, 2001 1:39 PM > To: DB2-L@RYCI.COM > Subject: SQL reference book > > > I'm looking for any recommendations for a mainframe SQL reference > book. Any suggestions? > Thanks, > Bill Johnson DBA > Allstate Insurance Co. > > ================================================ [...]
18583 14 22_Re: SQL reference book0_26_truman.g.brown@VERIZON.COM31_Wed, 14 Feb 2001 14:39:29 -0500333_us-ascii "SQL Unleashed", published by SAMS, is a good general SQL reference.
George
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18598 39 29_Re: SyBase to UDB conversions17_Wood, Christopher17_cwood@CAYENTA.COM31_Wed, 14 Feb 2001 11:38:14 -0800267_ISO-8859-1 There is a vendor called Mantech or Manteck, I know one of their products is called "Sprokets". Is your UDB on OS/390 or UNIX? Version 7 of UDB supports PSM which is very similar to Transact-SQL. I think you save a lot of grief if you can wait for 7. [...]
18638 31 22_Re: SQL reference book18_Richard Mccutcheon32_Richard_McCutcheon@HCM.HONDA.COM31_Wed, 14 Feb 2001 14:46:48 -0500449_us-ascii depending on what version of UDB you are working with there are a couple of DB2 UDB Vx.x SQL CookBooks available at the following site (free for download).
http://ourworld.compuserve.com/homepages/Graeme_Birchall
There is one for V5.2, V6.1, V7.0. These books are in PDF format which means you need the Acrobat Reader to view them (this is also a free download on the web...............). [...]
18670 46 29_FW: SyBase to UDB conversions17_Wood, Christopher17_cwood@CAYENTA.COM31_Wed, 14 Feb 2001 11:47:10 -0800451_ISO-8859-1 -----Original Message----- From: Wood, Christopher Sent: Wednesday, February 14, 2001 2:38 PM To: 'DB2 Data Base Discussion List' Subject: RE: SyBase to UDB conversions
There is a vendor called Mantech or Manteck, I know one of their products is called "Sprokets". Is your UDB on OS/390 or UNIX? Version 7 of UDB supports PSM which is very similar to Transact-SQL. I think you save a lot of grief if you can wait for 7. [...]
18717 22 44_Re: table space partitioning on a Unix (AIX)18_Gert van der Kooij15_geko@WANADOO.NL31_Wed, 14 Feb 2001 20:59:03 +0100408_iso-8859-1 ----- Original Message ----- From: "McClendon, Rick"
> pointers to meaningful documentation, etc.!! >
The delivered documentation contains all you need, probably to much :) Check the Administration Guide and the Replication Guide and Reference. For performance issues, take a look at the redbook DB2 UDB V7.1 Performance Tuning Guide ( SG24-6012-00). [...]
18740 19 38_How to get the time stamp from DBRMLIB21_Devineni, KishoreBabu29_KishoreBabu.Devineni@NIKE.COM31_Wed, 14 Feb 2001 11:57:24 -0800401_us-ascii Hi All,
Can anybody tell me how to get the time stamp from DBRMLIB to match with the one in SYSIBM.SYSPACKAGES
Thanks In advance Kishore
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18760 128 30_Re: db2 data modeling question9_Tim Simon22_tsimon@TOTALSYSTEM.COM31_Wed, 14 Feb 2001 15:40:44 -0500599_us-ascii Keep in mind that in some States they are reusing SSN.
Isaac Yassin @RYCI.COM> on 02/14/2001 01:06:16 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: Re: db2 data modeling question
Hi, That's why we resorted to using an OID (ObjectID) which is "randomly" generated from the original number and keep a translation table (for possible duplicates in the randomizer). [...]
18889 133 24_Re: Urgent help needed..13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 14 Feb 2001 14:43:55 -0600539_US-ASCII Bharat,
The simplest method is to simply add that value to the end result. Therefore, if you wish to begin at 21, rather than 1, then code:
SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY EMPNO) + 20 AS SEQNO FROM EMPLOYEE ORDER BY EMPNO
Regards Terry Purcell Yevich Lawson & Assoc www.ylassoc.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of bharat r Sent: Wednesday, February 14, 2001 1:22 PM To: DB2-L@RYCI.COM Subject: Re: Urgent help needed.. [...]
19023 73 29_Re: SyBase to UDB conversions0_24_db46@DAIMLERCHRYSLER.COM31_Wed, 14 Feb 2001 15:46:53 -0500418_us-ascii Yes, I have heard about version 7. But right now we have version six on a unix box.
Found out that there are only 24 of those stored procedures presently used, so the conversion may not be that bad.
But we cannot wait for version 7 (groan). Thanks for the infor.
Dean
"Wood, Christopher" @RYCI.COM> on 02/14/2001 02:38:14 PM [...]
19097 29 12_Java drivers7_TSVANDR20_TSVANDR@LANDSEND.COM31_Wed, 14 Feb 2001 14:47:07 -0600576_iso-8859-1 I have a developer who is trying to use java to access DB2 databases on different platforms. He started out with a db2java.zip file dated 7/28/00 (not sure if the date is significant)on Windows 2000. This allowed him to successfully connect to a DB2 EE database on AIX at v6.1.0.21 (DBASE1). He was also able to successfully connect to a DB2 OS390 V5 subsystem (DBASE2) . However he couldn't connect to a DB2 EE database on AIX at V6.1.0.31 (DBASE3). So, he copied the db2java.zip file from the v6.1.0.31 install down to his pc. This allowed him to connect to [...]
19127 47 42_Re: How to get the time stamp from DBRMLIB15_Fred Occhipinti23_Fred_Occhipinti@FPL.COM31_Wed, 14 Feb 2001 16:11:13 -0500445_us-ascii If you look at the 8 bytes(with hex on) following the package ID in the DBRM(first line), you will have the same consistency token(CONTOKEN column) that will be found in the corresponding syspackage row. Best Regards, Fred O.
"Devineni, KishoreBabu" @RYCI.COM> on 02/14/2001 02:57:24 PM
Please respond to "DB2 Data Base Discussion List" [...]
19175 43 42_Re: How to get the time stamp from DBRMLIB9_Wu, James13_jwu@KRAFT.COM31_Wed, 14 Feb 2001 15:14:36 -0600492_ISO-8859-1 1). Select CONTOKEN field from sysibm.syspackage (in HEX format); 2). Browse the DBRM, the 8 bytes right after the DBRM name should be identical to CONTOKEN from 1). (all in hex)
James Wu:-)
(847)646-5548 jwu@kraft.com
-----Original Message----- From: Devineni, KishoreBabu [mailto:KishoreBabu.Devineni@NIKE.COM] Sent: Wednesday, February 14, 2001 1:57 PM To: DB2-L@RYCI.COM Subject: How to get the time stamp from DBRMLIB [...]
19219 15 21_Re: Reactive Governor11_Fred Nijdam20_f.nijdam@SYNTEGRA.NL31_Wed, 14 Feb 2001 14:50:33 -0600394_- We are using RLF for within V5 very succesfully with rows without autid or plan, only making a difference for local (blanks) en PUBLIC. It worked very succesfull. Unfortunately IBM put a index on the RLF which made the logic of RLF in error, there is a recent PTF available which correct this (just search for -905 sqlcode). Maybe this is causing you the problem and the fix might help? [...]
19235 160 55_Re: Package Cache Overflow UDB 6.1/AIX - Urgent Problem13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Wed, 14 Feb 2001 15:16:32 -0600565_us-ascii Phil, All of the dynamic SQL is in a purchased application so we have no way of knowing what is happening inside the application. The only time we can make this error occur is when a certain batch program is running at the same time. Helen
Philip Gunning wrote:
> > > Helen, Sounds like your dynamic sql statement is being prepared > over and over again,as a different statement, filling up the cache. > Take a look at the program and see if it has changed drastically. > I've seen this other places. Mark?:). HTH Phil > > -----Original [...]
19396 50 17_peoplesoft tools-10_Shery Hepp17_schepp@SRPNET.COM31_Wed, 14 Feb 2001 14:24:59 -0700588_iso-8859-1 Please disregard this if you already rec'd this on PSDB2OS390 list serve-
Greetings to my fellow Peoplesoft DB2 DBA's-
Once again I call on you for assistance. We are once more in the process of discussing with our developers why we manage our peoplesoft databases the way we do. In other words they want to know why we deviate from using the peoplesoft generated tools to manage changes in db2. We have a meeting scheduled to go over this, but my manager would like me to pull together a list of how other db2 peoplesoft shops migrate/manage changes. It is [...]
19447 17 22_Re: SQL reference book13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Wed, 14 Feb 2001 15:16:19 -0600356_- I have two personal favorites. They are generic SQL books, so you have to check the OS/390 book to verify what works and what doesn't....but they give you great ideas. They are "SQL for Dummies" by Allen G. Taylor and "SQL for Smarties" by Joe Celko. When I want to use one of them, I generally have to go find them because they get borrowed a lot. [...]
19465 30 20_DB2 MANAGED BY DFSMS14_jack fernicola20_bowling730@YAHOO.COM31_Wed, 14 Feb 2001 13:57:57 -0800367_us-ascii Since DFSMS in relative new in our shop, our DASD group is now recommending that we use DFSMS to manage ALL system datasets for all software products along with the DB2 datasets (BSDS, LOGS, Catalog,Directory software, etc....) into ONE SMS managed POOL.
Can anyone that is using DFSMS for DB2 willing to share (pros and Cons) their experiences. [...]
19496 124 21_Re: peoplesoft tools-12_Troy Coleman19_Colematr@MEIJER.COM31_Wed, 14 Feb 2001 17:08:39 -0500530_ISO-8859-1 Hi Sherry, I've been supporting PeopleSoft for the past 4 ½ going on 5 years and can tell you that the delivered tool set is OK. The problem you get when you run the script within PeopleSoft is that you loose the synonym and security. Now if you are using alias that would resolve this problem. I tried to use ALIAS several years ago and had some problems. I do not remember what the problem was. It may have been in the audit reports used back then. I do know that I have implemented with synonyms and only had a [...]
19621 17 23_Graphical Relationships16_Brauweiler, Mike24_MBrauweiler@ELCAMINO.COM31_Wed, 14 Feb 2001 14:58:47 -0800380_- First, let me freely state that I am a died-in-the-wool heavy-iron bigot, and largely PC-illiterate. Having gotten that off my chest, I am looking for a means of graphically depicting all the relationships between all the tables in a UDB database on a laptop (Windows 98), in much the same way as MS Access ( gag! ). Can anyone out there point me in the right direction??? [...]
19639 19 29_Re: CORRUPTED DATA DICTIONARY15_Amy C Whitehead14_B06ACW@FDS.COM31_Wed, 14 Feb 2001 18:16:16 -0500474_us-ascii All, I have an application using both WITH UR and FOR FETCH ONLY. My understanding is they serve 2 totally different functions. Does anyone know of any problem with this?
Thanks, Amy Whitehead Federated Systems Group
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
19659 20 18_DPROPR/Data Joiner15_Amy C Whitehead14_B06ACW@FDS.COM31_Wed, 14 Feb 2001 18:16:49 -0500546_us-ascii All, I need to get some user feedback on DROPR/Datajoiner from OS390 DB2 users. We plan to apply deltas to Terradata. If you are willing to share your experiences with DPROPR/Datajoiner, please email your name and phone number directly to me.
Thanks, Amy Whitehead Federated Systems Group
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
19680 73 38_How to get the time stamp from DBRMLIB15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 14 Feb 2001 17:32:36 -0600621_us-ascii You can look in sysibm.syspackage and get the bindtime of the package and consistency token: select name,collid,bindtime,hex(contoken) from sysibm.syspackage where name = 'packageID' Note bind time and token. A -805 message will usually give the consistency token in the message used by the module. (Otherwise see finding token in module below) Compare the token from the message to what you see in DB2. - Compare the link timestamp in the module (n.f.3.1 -FileAid), and the bindtime found in DB2. The dates should match. - The DBRM consistency token should be the same as in the database, and can be found [...]
19754 38 24_Re: DB2 MANAGED BY DFSMS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 14 Feb 2001 18:43:23 -0500429_ISO-8859-1 You'll find much about this in the archives. To make it really short, unless you're on Shark, or other vendor equivalent using PAV, your performance will go right down the tubes. And... you can still kill the new systems using that have PAV if they do it poorly enough... If your installation is rather small, you might get away with it for a while... if you have a large DB2 system... you're dead. Regards, Joel [...]
19793 85 24_Re: DB2 MANAGED BY DFSMS0_19_csutfin@AMSOUTH.COM31_Wed, 14 Feb 2001 17:48:21 -0600371_us-ascii Jack, We use SMS to manage the application datsets only (tablespaces and indexes). The system datasets, catalog datasets, BSDS, logs, DSNDB07 are controlled by the System programmer and System DBA. We place these on our "system" packs by VOLSER.
Even for the application dataset we use multiple pools based on application and/or size of the dataset. [...]
19879 82 24_Re: DB2 MANAGED BY DFSMS14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 15 Feb 2001 11:47:47 +1100589_iso-8859-1 Jack,
Do you still use real 3390/3380's? If yes, imagine that a single disk contains both current active logs and a database cluster - not impossible if everything is in a single pool. Now imagine a single disk failure - that disk.
How are you going to recover?
There are two critical design decisions that need to be made: - how many disk failures are you going to plan for? The example above planned for zero failures. Putting active logs on one set of SLEDs (SLow and Expensive Disks aka real disks) and database clusters on a separate SLEDs allows [...]
19962 36 27_Re: Graphical Relationships15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 15 Feb 2001 10:53:01 +1100639_- Heavy-iron bigot, huh? Seems like you're pointing in the right direction already...
Raymond 'Big Blue Bigot' Bell
> -----Original Message----- > From: Brauweiler, Mike [SMTP:MBrauweiler@ELCAMINO.COM] > Sent: Thursday, 15 February 2001 9:59 am > To: DB2-L@RYCI.COM > Subject: Graphical Relationships > > First, let me freely state that I am a died-in-the-wool heavy-iron bigot, > and largely PC-illiterate. Having gotten that off my chest, I am looking > for a means of graphically depicting all the relationships between all the > tables in a UDB database on a laptop (Windows 98), in much the same way as > MS Access [...]
19999 61 28_Re: One Process - Many Users14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 15 Feb 2001 11:54:20 +1100345_iso-8859-1 I do not understand what you are trying to do. Can you supply an example and describe what happens and describe what you want to happen.
Is this in a batch process, CICS, IMS or something else?
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au [...]
20061 84 75_Re: Need Ideas/suggestions ... loading ASCII data into a DB2 for NT (v.6.1)14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Wed, 14 Feb 2001 19:43:18 -0500585_iso-8859-1 Joel, Of course there is also the LOAD utility....which allows you to load from ASC (non-delimited ASCII format), DEL (delimited ASCII format) and IXF (integrated exchange format, PC version) formats with either INSERT (similar to LOAD RESUME in the real world...) or REPLACE (yep, LOAD REPLACE). You should be able to build an ASC or DEL format file with little effort. Check the UDB docs (on the IBM website if you don't have installed) for details. Most UDB utilities are not as user friendly as the DB2 utilities you are used to, but they do the job. Good luck.... [...]
20146 59 18_Re: UDF / Resident14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 15 Feb 2001 12:08:10 +1100553_iso-8859-1 The two ways we found that worked were 1) bouncing the SP address space. 2) altering the UDF/SP to STAY RESIDENT NO, invoking the UDF/SP, and then altering it back to STAY RESIDENT YES.
In test environments, we use STAY RESIDENT NO.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Wolfgang Wolf [mailto:wolferl69@GMX.NET] Sent: Wednesday, February 14, 2001 10:30 PM To: DB2-L@RYCI.COM Subject: [DB2-L] UDF / Resident [...]
20206 91 18_DB2 & DW Questions11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Wed, 14 Feb 2001 20:47:51 +0700276_us-ascii Dear all,
I want to know on how to connect to other DB (non DB2) with Federated Database Objects. We have DB2 UDB 7.1 Workgroup Edition for NT on our box, I tried to connect to other DB with no success. Do we need to buy something else to get this done ? [...]
20298 48 27_Re: Graphical Relationships12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 15 Feb 2001 13:00:44 +1100503_- Mike, Although not exactly what you are after, you may find that a lot of modelling tools will reverse engineer DDL into a Data Model diagram.
Long live the dinosaurs. It's nice to work on a machine with a real "Byte" (apologies to those just having lunch).
Cheers, Nick Cianci DB2 DBA - CCRI Project 2nd Floor (West) 484 StKilda Rd MELBOURNE 3004 * (+613) 9865-8554 (+61) 0408-64 06 01 * Nick.Cianci@team.telstra.com _______________________________________________________________ [...]
20347 50 28_C/DB2 Precompiler & compiler12_Warren Homer17_whomer@CSC.COM.AU31_Thu, 15 Feb 2001 12:55:10 +1100294_us-ascii Hi all,
I have been trying to set up JCL to Precompile & Compile a 'C' (not C++) program to use DB2. I managed to get the C++ one running but the programmers require one for 'C'. I was wondering if anyone out there could offer any ideas.
We have DB2 V4, OS/390 V1.2 [...]
20398 96 32_Re: C/DB2 Precompiler & compiler14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 15 Feb 2001 14:43:45 +1100500_iso-8859-1 Warren
1) This line is generated by the precompiler. We use #include which brings in the declaration of NULL
2) Are you _quite quite_ certain that you don't include the extern "OS" long int dsntiar(void*, void*, void*); yourself. This is the C++ format (except that dsntiar should be a _short_ int - but due to the actual linkage mechanism used in OS/390, this discrepancy doesn't make a material difference). For C #pragma linkage (dsntiar,OS) would be used. [...]
20495 69 46_Help on Replication from OS/400 to DB2 UDB 7.114_Romeo B Titong26_rbtitong@ACISYSTEMS.COM.PH31_Thu, 15 Feb 2001 11:47:15 +0800524_us-ascii Hi All!
Here is a simple scenario that I have done successfully using DB2 UDB 5.2 and 6.1: Connect to AS/400 and perform replication. Now, I am trying the scenario using version 7.1. At first, I was having problems on "Connect" until I installed fixpak 2. My problem now is in the Control Center. Whenever I define a new subscription using the control center and run that definition, the Control Center closes. No error message was returned except a log in the DB2DIAG.LOG (see below). Can anyone help? [...]
20565 84 18_Re: UDF / Resident11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 14 Feb 2001 22:10:59 -0600306_- James & Wolf -
I was not sure of UDF's but resident SP's have always behaved this way - i.e. the entire address space must be bounced, STOP/START does not cut it. That is the main reason we have stayed away from resident SP's in spite of the potential gain. Is a fix/enhancement on the way?? [...]
20650 99 21_Re: peoplesoft tools-9_Gary Gray22_gary.gray@GRAYSHER.NET31_Wed, 14 Feb 2001 21:18:23 -0800368_iso-8859-1 Shery, My shop has had Peoplesoft HR and FS for over 2 years now. I very rarely use the scripts produced by application designer for serveral reasons.
1. Application Designer does not build scripts that save grants, views, alias's, etc. I see no reason to run a data mover replace_views script to rebuild every view for a 1 table drop/recreate. [...]