1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2001, week 3 2 65 25_db2dari dies out randomly11_Arvind Heda40_arvind_heda@INTERSOLUTIONS.STPN.SOFT.NET31_Tue, 15 May 2001 11:39:19 -0400467_iso-8859-1 hi list members,

wile repeatedly calling a stored procedure , the db2dari (which is keepalive) kept on increasing its memory size with each call and dies out randomly due to read access voilation error (ie reading the 00000004 x memory location) The observation regarding this is that if the calls to UDF are removed from theses stored procedures then this problem do not occurs. can somebody give any suggestion about this observation. thanx arvind 68 177 24_Re: Buffer pool question18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 15 May 2001 11:46:11 +0500420_iso-8859-1 Assuming that I have a BP which will not contain any updated pages , but only pages in which no rows are modified, I would be hitting DMTH for a pool of say 3000 buffers, if the number of free buffers is only 150 (say because all others are IN USE). 128 is less than 150, so does this mean that the migration to HP is not triggered till DMTH is hit in this case?. I think I am missing something obvious. [...] 246 23 29_Re: Example of REPAIR LEVELID10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 15 May 2001 08:43:37 +0200494_us-ascii Here a sample of REPAIR LEVELID:

//SYSIN DD * REPAIR LEVELID TABLESPACE dbname.tsname REPAIR LEVELID TABLESPACE dbname.tsname PART xx .......................................................... //

HTH

Max Scarpa DB2 sysprog

================================================ 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. 270 111 24_Re: Buffer pool question16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 15 May 2001 12:38:18 +0530552_iso-8859-1 Radha,

As per my understanding, what you are mentioning about 128 'free pages' are the pages kept in the separate queue.When HiperPools are used, DB2 creates and maintains an additional queue of 'free pages'. This queue improves performance because a target page does not have to be located from one of the normal LRU queues, and will not have to be migrated to the HP before a page is available for a new data page. DB2 attempts to maintain 128 or 256 pages in this free queue depending on the defined size of the virtual pool. [...] 382 32 32_RID failure - EXCEEDED RDS limit18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 15 May 2001 12:52:29 +0500574_- Hello listers

I am hitting 'exceeded RDS limit ' despite the fact that I have run RUNSTATS TABLESPACE tablespace-name TABLE(ALL) INDEX(ALL) on all the tablespaces containing the tables being accessed in my batch program. I rebound the package and plan after running RUNSTATS, but still I hit the limit. Between the time the program was rebound and the time I am running the program there are no inserts into any of the tables being accessed in my program. There is no one other than me using the test region at the moment. Can someone tell me where am I going [...] 415 23 27_DB2 os390 v6 with Unix-jdbc9_john king24_john_king@REDIFFMAIL.COM31_Tue, 15 May 2001 07:45:53 -0000598_- Hello Listers/DB2 Masrter’s .

Has any body experienced this situation. I have to run java programs with UNIX environment with JDBC connection to DB2 on OS390 V6. I m a purely os/390 professional with less experience on DBA side. The major area will be UNICODE problem. The DB2 on OS 390 supports the CCSID as EBCDIC/ASCII. On unix side it is ASCII and UNICODE. How I will solve this problem for setting up the same CHARCTER CONVERSION at both side. Does the Unicode charcters are converted to EBCDIC automatically once it goes through network(TCP/IP) and the same way the information [...] 439 38 36_Re: RID failure - EXCEEDED RDS limit16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 15 May 2001 14:04:28 +0530412_- Nagaraj,

Could you please send the complete message (wherever you have found this). You might have ran the runstats/rebind but what is the effect of that on the plan/package you are using is the things to look at. Find out the qualifying RIDs which is processed and then compare it with the size of your RID pool. The calculation should be easy as you mentioned no one else is using the subsystem. [...] 478 65 36_Re: RID failure - EXCEEDED RDS limit17_Miguel de Andrade29_miguel.andrade@BTINTERNET.COM31_Tue, 15 May 2001 03:24:36 -0500299_- You can get this message if : The number of times RID list processing was terminated because either the number of RID entries was greater than the maximum limit of 25% of the table size, or because the number of RID entries that can fit into the guaranteed number of RID blocks was exceeded. [...] 544 110 36_Re: RID failure - EXCEEDED RDS limit18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 15 May 2001 14:37:42 +0500541_iso-8859-1 Sanjeev I have read that RID pool size is of no consequence when you hit this limit. And hence increasing it is also of no consequence. Richard Yevich's book says that "RIDs over RDS limit is the number of times list prefetch is turned off because the RID list built for a single set of index entries is greater than 25% of the number of rows in the table. If this is the case DB2 determines that instead of using list prefetch to satisfy a query, it would be more efficient to perform a table space scan......" It also says [...] 655 99 36_Re: RID failure - EXCEEDED RDS limit23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 15 May 2001 10:17:39 +0100413_- Like the attached - TRUST NOTHING and check exactly what is happening by using the monitor.

From my own limited experience there is always something you are not seeing ...

regards

Leslie

-----Original Message----- From: Miguel de Andrade [mailto:miguel.andrade@BTINTERNET.COM] Sent: Tuesday, May 15, 2001 9:25 AM To: DB2-L@RYCI.COM Subject: Re: RID failure - EXCEEDED RDS limit [...] 755 112 36_Re: RID failure - EXCEEDED RDS limit18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 15 May 2001 15:00:51 +0500579_iso-8859-1 Thanks Miguel for your reply. Can you elaborate more on "...because the number of RID entries that can fit into the guaranteed number of RID blocks was exceeded.." By guaranteed number of RID blocks, do you mean the TOTAL number of blocks available in RID pool?. Also you mentioned that RID list processing was terminated because the number of RID entries was greater.....25% of table size. Is the RID list from one index entry or all index entries being used to access the specific table?. You asked me to look at EXPLAIN. Should I look at all access paths with [...] 868 72 38_: Re: RID failure - EXCEEDED RDS limit16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 15 May 2001 15:07:33 +0530543_- > Nagaraj, > Thanks for the info. We discussed this in detail with Venkat, Michael > Hannan and Richard sometimes 1 and half years ago. They explained this > phenomena quite nicely. However, i was thinking you do not have RID pool > size to back even 25% of the RIDs. Thanks for clarifying. > Coming back to the question, Could you identify the SQL which is executed > when this problem came ? You have mentioned the correct reason for running > the RUNSTAT but many times optimizer also can not find out the actual > number of records [...] 941 17 30_Re: Hybrid vs Nested Loop Join15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Tue, 15 May 2001 04:11:24 -0500518_- Aedan

Since you explained the query using SPUFI, the optimizer uses the current statistics. But when the package was bound, I guess, statistics were quite different. So rebind your package an look for the explain. I think it will be the same as with SPUFI.

HTH

================================================ 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. 959 110 36_Re: RID failure - EXCEEDED RDS limit18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 15 May 2001 15:22:22 +0500602_iso-8859-1 Sanjeev Thanks for the info. I will examine all the SQL statements with list prefetch in their access path

Regards

Nagaraj

> -----Original Message----- > From: S, Sanjeev (CTS) [SMTP:SSanjeev@CAL.COGNIZANT.COM] > Sent: Tuesday, May 15, 2001 3:08 PM > To: DB2-L@RYCI.COM > Subject: : Re: RID failure - EXCEEDED RDS limit > > > Nagaraj, > > Thanks for the info. We discussed this in detail with Venkat, Michael > > Hannan and Richard sometimes 1 and half years ago. They explained this > > phenomena quite nicely. However, i was thinking you do not have RID pool > > [...] 1070 134 27_Re: Omegamon/DB2 and DB2 V716_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 15 May 2001 10:48:49 +0100412_us-ascii Isac, Eric,

sorry I'm a bit late answering this one, I was busy casting my useless vote against the media-tycoon who's now my country's inept and dangerous number one, sigh sob. good job I live in the UK...

ANYWAY just to clarify here's my little product plug:

OMEGAMON II for DB2 v520 development is in progress, and should be available in 2H01. Planned enhancements include: [...] 1205 67 29_Re: Why is my index not used?13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Tue, 15 May 2001 10:56:36 +0100446_iso-8859-1 Dan,

A couple of thoughts....

1. Did you run runstats on the table AND all it's indexes, collecting all the index statistics, including KEYCARD? 2. What is the cardinality of the table, and what is the cardinality of the column on the index being used? I'm thinking that perhaps DB2 is using this one because the number of pages touched using this index might be smaller because the index is smaller?? Just a guess! [...] 1273 40 18_Re: Data Migration10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM31_Tue, 15 May 2001 06:05:11 -0400308_ISO-8859-1 I don't know if you want to look at third party tools to do this, but you might find the Move for Servers tool from Princeton Softech worth a look - it allows movement of logically and physically relationally linked data across multiple dbms's, including Sybase and DB2(both os/390 and UDB). [...] 1314 15 24_Re: Buffer pool question15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Tue, 15 May 2001 04:41:08 -0500309_- Radha

What should I conclude from this: Don't use hiperpools, because it is very common, that a GETPAGE-request needs a page which is not in the VBP nor in the HP. Or is it very unlikely, that ADMF has not finished transferring pages to the HP. I think, we need some more clarification on this. [...] 1330 29 27_Re: question on parallelism14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Tue, 15 May 2001 06:31:48 -0500473_iso-8859-1 It can be very important with parallelism to limit the number of tasks run by DB2. If too many tasks are run, then the query can chew up your CPU and run significantly slower.

One of the many ways DB2 decides on the planned degree at bind time is by dividing the total row count of the table by the number of rows in the largest partition. If you have a 100m row table equally spread over 100 partitions, then 100m / 1m = degree of 100 = CPU chewer. [...] 1360 17 23_-904 with Reason Code 015_Robin Woolyhand22_woolyhr@NATIONWIDE.COM31_Tue, 15 May 2001 05:59:27 -0500413_- Has anyone had the experience of an application getting a DB2 -904 with a reason code of 0? The application SQLCA is giving the return code of -904 with a reason code of 0 and the resource type and the resource description was low values.

We are executing a Stored Procedure in a WLM and data sharing environment and the Systems DBAs claim that there is nothing showing in the Master Address Space. [...] 1378 22 63_Re: Diff in Partitioning implementation between OS/390 and UNIX14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Tue, 15 May 2001 06:42:01 -0500443_iso-8859-1 DB2 for Unix works differently to DB2 for OS/390. Do not assume that because you perform a task in one way on OS/390, it is best to do it in the same way on DB2 for Unix.

I think you need to read the manuals a bit more to discover what a nodegroup means before you try to use them. Nodegroups can be of help for DB2 Enterprise Edition, but may not be. For DB2 EEE the use of nodegroups is mandatory for all tablespaces. [...] 1401 58 39_Re: question on parallelism (revisited)18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 15 May 2001 17:31:49 +0500319_- Hi Edward Thanks very much for your response. How does DB2 arrive at planned degree of parallelism for non-partitioned tablespaces. And also I see ACCESS_DEGREE of 0 and PARALLELISM_MODE of C (CPU) for many of my SQL statements. My table spaces are simple table spaces. What does ACCESS_DEGREEE of 0 mean here?. [...] 1460 33 39_Re: question on parallelism (revisited)14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Tue, 15 May 2001 07:39:42 -0500535_iso-8859-1 One reason for ACCESS_DEGREE of 0 is that your SQL statement contains a host variable (see DB2 Admin Guide). The PARALLELISM_MODE of C means that CPU parallelism only will be used.

This means that at run time DB2 will determine if parts of your query can be performed in parallel. e.g. If you have 2 sub-queries that must be complete before the main query can run, DB2 may get the data for both sub-queries at the same time. Also, if multiple indexes need to be accessed, this may be done by one task per index. [...] 1494 77 36_Re: Column Naming Standards - Thanks13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 15 May 2001 07:46:43 -0500568_iso-8859-1 Hi

Thanks to all who replied about the naming convention.

I will stick to the standard 2-character classifier on the end - never really in doubt that one.

I will use full words where space permits, if space is insufficient, I will use the standard abbreviation. There is an exception where even if space permits, I will continue to use the abbreviation. Example: I will always use Plcy_No for Policy Number, even though I have the space for the full spelling. This example is a standard abbreviation here and every one knows that. [...] 1572 70 34_Re: INDEXES IN TEMPORARY TABLES...34_=?iso-8859-1?Q?Mat=EDas_Salvador?=26_matias_salvador@UOL.COM.AR31_Tue, 15 May 2001 09:52:04 -0300418_iso-8859-1 Why does os/390 version 6.1 support indexes and version 7.1 for Solaris seem not to do it?

At least, that's what it says the SQL References of V7.1





*************************************************************

Yes,

If it is a declared temporary table and you are on os/390 version 6.1, then you can create indexes on the temporary tables.

Rick Creech [...] 1643 32 36_Re: Dynamic SQL and CPU usage report6_Chenny39_solaiyappan_chenniappan@MAIL.AMSINC.COM31_Tue, 15 May 2001 07:36:24 -0500532_- Hi Bill,

DB2PM has similar problems(it does not capture any of the CPU time charged for parallel tasks and enclaves).

We maintain a comprehensive performance database, Housing all the dynamic sql stmts in TABLEA and accounting information in TABLEB. The closest way to produce a single report containing sql stmt and accounting information(class1&2 elap_time and cpu_time) is by joining TIME(TABLEA.TIMESTAMP) and TIME(TABLE B.Class1_Beg_Time) as they always have a difference in terms of 1/100 th of a second. [...] 1676 19 49_Warning messages seleting from a 160 char column.12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Tue, 15 May 2001 07:36:33 -0500534_- We have a customer that has started to get warning messages when using DSNTEP2 and selecting from a table that has a column that is 160 characters. The only thing we have done is put on maintenance level 0012 about 3 weeks prior to them reporting this. From an ETR with IBM we found that DSNTEP2 has a default of 120 character columns. So, we can change the source to accept 160 character columns, which I have not tested yet, but we would like to know if anyone else has seen something simular. I can't tell if the source for [...] 1696 29 35_Re: INDEXES IN TEMPORARY TABLES....11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Tue, 15 May 2001 09:04:04 -0400508_us-ascii Matias, if you refer to global temp tables, I do not think you can't, they only live through the life of the application process. DB2 does not log or lock for efficiency. We support applications like PeopleSoft which make use of so called "temp tables", these are permanent tables that PeopleSoft loads and later empties them. What we have done is identify a lot of them and if they benefit by having an index we have created the index, and seeded the statistics to make sure DB2 Optimizer picks the 1726 41 52_Error connecting to DB2 for OS/390 using Visual Age.12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Tue, 15 May 2001 07:49:17 -0500562_- We have a customer that is trying to use Visual Age and Websphere Studio to select data from our DB2 for OS/390 using JDBC. He is getting an error saying: CLI0615E. ERROR RECEIVING FROM SOCKET SERVER IS NOT RESPONDING. SQLSTATE- 08S01.

They are trying to use JDBC to connect. We have other apps connecting to DB2 using JDBC but they go through a Websphere server running on the mainframe, this app is not. They say they are using the Universal Database driver. The only other driver to choose from is JDBC to ODBC. The URL that they are putting in [...] 1768 33 35_Re: INDEXES IN TEMPORARY TABLES....34_=?iso-8859-1?Q?Mat=EDas_Salvador?=26_matias_salvador@UOL.COM.AR31_Tue, 15 May 2001 10:41:14 -0300488_iso-8859-1 How many types of temporary tables does DB2 have? I'm a newbie to DB2 and don't understand very well the use of temp tables. I have version 7.1 for Solaris, but my SQL Reference says that DGTT cannot be referend in a CREATE INDEX, so... i can't create indexes on temporary tables? Then, someone at the list tells me that i can use indexes, but with os/390 v6.1...but this is an older version of 7.1.... am i missing anything? Sure I do... could anyone throw me a hint???? [...] 1802 57 35_Re: INDEXES IN TEMPORARY TABLES....11_David Nance16_DWNance@FHSC.COM31_Tue, 15 May 2001 10:00:39 -0400355_US-ASCII Matias, There are two types of Global Temporary tables. They are Dclared GTT's and Created GTT's. There was just a discussion about the use of these within the last couple of weeks, if you'd like to search the archives for more info or refer to the application programming guide.

Dave Nance First Health Services, Corp. (804)527-6841 [...] 1860 108 35_Re: INDEXES IN TEMPORARY TABLES....16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Tue, 15 May 2001 10:02:25 -0400520_iso-8859-1 It depends on the platform and the version. DB2 version 5.1 for OS/390 has only Create Global Temporary Tables - cannot use indexes. DB2 version 6.1 for OS/390 added Declared Global Temporary Tables - can use indexes.

You must find out the features of your flavor/version of DB2.

Rick Creech





Matías Salvador cc: Sent by: DB2 Data bcc: Base Discussion Subject: Re: INDEXES IN TEMPORARY TABLES.... List [...] 1969 95 35_Re: INDEXES IN TEMPORARY TABLES....15_Toine Michielse18_vndobtm@US.IBM.COM31_Mon, 14 May 2001 16:35:21 +0200414_iso-8859-1 Matthias,

You are talking about DB2 7.1 for Solaris. I can't comment on that but DB2 V6 for OS/390 does have 'Created Global Temporary Tables' and 'Declared Global temporary tables'. You CAN create indexes on DGTT not on CGTT. Don't assume that whatever is possible in on older version of DB2 for OS/390 is automatically available in the same or a newer version of DB2 for another platform. [...] 2065 24 12_UDB and LDAP8_bharat r20_bharat_321@YAHOO.COM31_Tue, 15 May 2001 07:57:18 -0700564_us-ascii Hi List We have database in HP Unix and I have to use LDAP. If anyone used it please let me know how I can start with.. Thanks in advance..





__________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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. 2090 41 17_Re: Cluster ratio15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Tue, 15 May 2001 10:06:19 -0500647_us-ascii Yes tried , but 2 of 5 NPIs on a table defined in a partitioned TS cluster ratios after reorg plus runstats are 66 and 70. If IX Space is too high also may cause ? am i right? ----- Forwarded by Mohammed Nayeem/MoMedicaid/US on 05/15/01 10:08 AM ----- |--------+---------------------------> | | Roger Ghose | | | | | | | | | 05/15/01 08:30 AM| | | | |--------+---------------------------> >--------------------------------------------------------| | | | To: Mohammed Nayeem/MoMedicaid/US@MoMedicaid | | cc: | | Subject: Re: Cluster ratio | >--------------------------------------------------------| [...] 2132 38 64_How can AVGCOLLEN > LENGTH in 7.1 for AIX; and whence RECLENGTH?0_26_Steve.Westfall@EQUIFAX.COM31_Tue, 15 May 2001 10:08:44 -0500467_us-ascii We use DB2 UDB EEE 7.1 on AIX. Coming from a DB2 for OS/390 background, I have noticed some differences in the way the system catalog is implemented in EEE.

I noticed in the SYSCAT.COLUMNS catalog view that there are some columns in our database where AVGCOLLEN is greater than LENGTH. Since the latter is defined as the maximum length of the data in a column, how can AVGCOLLEN, the average length of column values, be greater than the maximum? [...] 2171 166 30_Re: Hybrid vs Nested Loop Join12_Aedan Molony20_Aedan.Molony@PSIR.IE31_Tue, 15 May 2001 16:27:35 +0100561_windows-1252 Jeff, Walter, Venkat, Thanks for replies. As you say Walter the problem is one of significant table growth between infrequent BINDs. We think a solution is in sight based on your replies...

Venkat,

Both tables are well organised, both IXs have only 1 col LINK_ID. 1 IX (one on TDDMN) is clustered with 99% C.R. & Leafdist 0, other IX (one on TDDDA) is NOT clustered with 81% C.R. & Leafdist 0, but high FAROFFPOS. We run runstats fully every week, and COLCARDF is correct (same as no. of rows as both IXs are unique on LINK_ID). [...] 2338 111 42_FW: [DB2-L] INDEXES IN TEMPORARY TABLES...13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Tue, 15 May 2001 08:26:42 -0700332_iso-8859-1 declared temporary tables are not global. global temporary tables are not declared.

we could remove some of the confusion if we used the correct terms.

GTT: exit in catalog. created prior to program that calls them. exist for one LUW. Used in stored procedures to pass result sets back to calling job. [...] 2450 20 51_URGENT: Problem in DB2 Warehouse Manager for AS/40018_Yosia Widi Nugroho20_yosia@CENTRIN.NET.ID31_Tue, 15 May 2001 22:29:46 +0700541_- Guys,

We've experienced a problem with Warehouse Manager for AS/400 v7.1 (5697-G23). The source and target databases reside in OS/400 platform V4R3. Both in source and target servers have Warehouse Manager Agent installed.

When we try to promote the process to Test, we received an error message as follows: 'The warehouse server was unable to determine the message type of a message received while processing a command of type "prepareSelectStatement" for edition "0" of step "Test 4 Proses step".' RC: 7195 RC2: 9124. [...] 2471 170 46_Re: FW: [DB2-L] INDEXES IN TEMPORARY TABLES...16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Tue, 15 May 2001 11:44:25 -0400441_iso-8859-1 That depends upon your definition of "global". IBM calls them global. The syntax is:

DECLARE GLOBAL TEMPORARY TABLE SESSION.xxxxx

They are global for all sql in the unit of work.

Rick CReech







Daniel Turner cc: Sent by: DB2 Data bcc: Base Discussion List Subject: FW: [DB2-L] INDEXES IN TEMPORARY TABLES... [...] 2642 97 30_Re: Hybrid vs Nested Loop Join34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Tue, 15 May 2001 12:10:17 -0400414_us-ascii Make sense..., You have TDDDA accessed as the first table followed by TDDMN. Since TDDMN has badly clustered index (81% is not a good clustering ratio), optimizer decides to use List Prefetch & Hybrid join.

That is a good access path usually but if you have your data skewedly distributed, Clusterratio of 81 could be misleading. That may be reason, you may see nested loop join doing better. [...] 2740 32 44_CICS Transit Time vs. DB2 Accounting Class 111_Daryl Meyer24_daryl.meyer@CITICORP.COM31_Tue, 15 May 2001 11:40:19 -0500524_x-user-defined Greetings all,

We are doing some performance and tuning on a heavy hit CICS/DB2 transaction where occasionally the users report very slow response times.

We have run Omegamon accounting reports against both CICS and DB2. When we see the degradation in response times we also notice there is quite a large timespan (15+ seconds) difference in the CICS Transit time and the DB2 Class1 Accounting times. Since we are using RCT's for this transaction and have TWAIT=POOL, this seems odd to us. [...] 2773 93 17_job opportunities12_Ginger Mejia28_info@PALLADIANCONSULTING.COM31_Tue, 15 May 2001 10:21:23 -0700693_iso-8859-1 Dear Colleagues:





Palladian Consulting a leader in the RDBMS consulting industry is currently seeking DB2 DBA'S for a permanent position in Northern CA and contract position in New Mexico. The requirements for Northern CA are as follows: Installations, write-ups of maintenance scripts, do re-orgs, statistics monitoring and capacity planning, performance maintenance and support applications, trouble shooting and performance tuning.The contract position requires 2 years minimum experience with DB2 UDB running on a UNIX operating system.If these positions are of interest to you or if you would like more information please do not hesitate to call me. [...] 2867 61 48_Re: CICS Transit Time vs. DB2 Accounting Class 119_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 15 May 2001 12:38:11 -0500624_x-user-defined Daryl, A quick guess -- This tran set up on a protected thread? Enough of them?

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this [...] 2929 53 15_job opportunity11_Brian Lynch19_Brian.Lynch@TRW.COM31_Tue, 15 May 2001 14:00:24 -0400560_US-ASCII DB2 Application DBA in Montgomery, Alabama.

Everything you want for your career is here, at TRW Systems & Information Technology Group, a pacesetting leader in IT and engineering. You'll find hot technology, intelligently applied. Great challenges across markets, at all levels. The chance to make a real impact on how government and big business operate. Great people who know what it takes to create superior solutions. All in all an environment designed to bring out your best, and reward it. We call it Excellence @ Work. So will you. [...] 2983 112 48_Re: CICS Transit Time vs. DB2 Accounting Class 112_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 15 May 2001 14:05:24 -0400308_US-ASCII It's been a few years for me on this. But I would check and see if you have thread queuing. You may want to setup a few protected entry threads and then make sure you have enough pool threads to handle your peek transaction rates.

Good Luck,

Troy Coleman Coleman Consulting, Inc. [...] 3096 38 36_Having problem with Identity Columns14_Zemanek, Jerry25_Jerry.Zemanek@WEIRTON.COM31_Tue, 15 May 2001 14:15:17 -0400651_iso-8859-1 Recently created a V6 table with and identity column and have just ran into a problem.

Column definition is as follows: CREATE TABLE INVNTORY.OPINV ..... INVENTORY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20)

With a PRIMARY UNIQUE KEY on INVNTORY_ID.

Getting a -803 on insert using the KEYWORD value of DEFAULT in my insert values list. The duplicate row shows an INVENTORY_ID of 32. The MAXASSIGNEDVAL in SYSIBM.SYSSEQUENCES is 40. By definition 40 sounds right because I am CACHING 20 values and MAXASSIGNEDVAL contains the highest possible value Cached. The odd thing is [...] 3135 78 40_Re: Having problem with Identity Columns14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 15 May 2001 14:39:28 -0400393_iso-8859-1 Jerry,

Much has been discussed about the pros and cons of IDENTITY columns in the recent past. Check the archives.

To answer your question quick, unload your data, drop your table, change your DDL for the IDENTITY column as:

CREATE TABLE INVNTORY.OPINV ..... INVENTORY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 326, INCREMENT BY 1, CACHE 20) [...] 3214 130 44_Re: DB2 Connect - Large account installation14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Tue, 15 May 2001 12:16:01 -0700553_x-user-defined Jacob, This can be done with response files or a package, like SMS from Microsoft. Additionally, if you are using LDAP or Active Directory, you can define the ODBC DSNs, database aliases, user logons, etc in the Active directory and then DB2 Connect will get this info from LDAP at first connect. DB2 will cache the node and database directories on the clients as long as a connection exists or it will create them on the clients depending on your settings. The DB2 UDB Admin Guide V6.1 has a good discussion of using LDAP. HTH Phil [...] 3345 21 0_16_Cockerill, Steve30_SLCockerill@NATIONALGYPSUM.COM31_Tue, 15 May 2001 15:21:42 -0400543_iso-8859-1 I need to talk to anyone running Peoplesoft on UDB for 7.5 financials.

We are considering the RS6000 platform and like to find users to discuss tools and performance. You may e-mail me direct or phone.



Thanks Steve Cockerill slcockerill@nationalgypsum.com 704-365-7342

================================================ 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. 3367 132 34_Re: INDEXES IN TEMPORARY TABLES...15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 15 May 2001 16:25:58 -0400450_iso-8859-1 How many companies uses DTT in production? Can everybody send a e-mail directly to me if your company use it. Also is there any problem with it. I had posted a question regarding this, but could not get any reply.

Thanks..sibi

-----Original Message----- From: Daniel Turner [mailto:daniel_turner@ALBERTSONS.COM] Sent: Tuesday, May 15, 2001 10:27 AM To: DB2-L@RYCI.COM Subject: FW: [DB2-L] INDEXES IN TEMPORARY TABLES... [...] 3500 109 0_12_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 15 May 2001 16:41:41 -0400498_US-ASCII Hi Steve, I just noticed today when I was looking at www.db2mag.com the article "Another Round to the Contender" that an IBM benchmark site exists. It has good information on what your looking for. The URL is: http://www-4.ibm.com/software/data/db2/benchmarks/

I've been supporting PeopleSoft on OS/390 for the past 5 years. I know that the UDB on UNIX is growing at a record pace. I would like to keep in touch and learn more about this platform and any experiences you have. [...] 3610 40 44_Re: DB2 Connect - Large account installation17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Tue, 15 May 2001 17:44:36 -0300381_us-ascii It can be installed in a server machine and shared. All you need to do on the workstation is some registry updates and autoexec.bat changes, that can be easily done by any software delivery package.

I have registry updates and autoexec changes that works for version 5.2, let me know if you want a copy. I think it should work for later versions the same way. [...] 3651 41 46_Transaction Management using Database Triggers14_Randy Williams23_randy.williams@IINT.COM31_Tue, 15 May 2001 17:47:04 -0500487_- Hi,

Thanks in advance for whatever help you can provide...

If I have a unit of work that consists of multiple database updates that all trigger their own stored procedures, how can I relate the updates?

I would assume that there is a transaction ID/token/number that I could associate to each triggered event that would allow me to combine them as one unit of work within the stored procedure, but I haven't been able to find any examples of this on the web. [...] 3693 25 19_Eric the Half-a-Bee15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 16 May 2001 09:47:05 +1000569_- Glenn,

Could be my own sick twisted mind at work, but I think Herr. Seibert was quoting a song from Monty Python called 'Eric the Half-a-Bee'. Could be wrong...



Raymond

> -----Original Message----- > Thanks for the replies ... I appreciated them, not that I understood the > half-a-bee. > > Glenn

================================================ 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. 3719 18 23_Re: Eric the Half-a-Bee13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 15 May 2001 20:47:28 -0400470_iso-8859-1 Yes, Raymond, it was definitely your own twisted, sick, memoriable mind at work.

And of course I was referring (as I explained off-list to Glenn) to the Fish License sketch from M. Python.

Cyrol Connelly

================================================ 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. 3738 126 23_Global Temporary tables12_Steve Howell23_HOWELLS@STGEORGE.COM.AU31_Wed, 16 May 2001 12:27:46 +1000430_us-ascii Hi,

I am currently successfully using a Declared Global Tempary table and Indexes to produce a report using Spufi.

The process involves

1) creating two Decalred Golbal tables with each having an index

2) Inserting data into table 1

3) selecting data from table1 with a where clause and inserting it into table 2

4) selecting data from table1 and table2 to produce a report. [...] 3865 139 24_Re: Buffer pool question10_Radha Rani24_radha_rani@ALTAVISTA.COM31_Tue, 15 May 2001 21:05:38 -0700632_- Sanjeev,

You've said "There are no free buffers available. If that is the case then all the pages are unavailable. And hence the critical thresholds are already hit". I think there could be some confusion here between 'free' buffers and 'available' buffers. DMTH is hit when 'available' buffers are less than a particular percentage. The figure of 128, I mentioned is the number of 'free' buffers. Buffers become 'free' when 'available' buffers are transported to hiperpool. So, theoritically, even if inventory of free buffers comes down to zero, DMTH or any other thresholds will not be reached; buffer manager will [...] 4005 68 17_Re: Cluster ratio16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 16 May 2001 09:42:44 +0530418_- If the best option of REORG is not ensuring 100% of cluster ratio, then nothing is possible. As others mentioned these are NPI and will have 100% cluster ratio only if its design is almost same as PI.

After all if the business data all over the world were so nice from the point of view of storage and extraction, we would never have needed good DBMS's, only file system would have done the work for us. [...] 4074 62 30_Re: T-shirts T-shirts T-shirts25_Sinha, Ranjay Kumar (CTS)25_SRanjay@CAL.COGNIZANT.COM31_Wed, 16 May 2001 10:29:06 +0530346_- Hi, Hope things are fine with you. Just to remind you that still I am waiting for the T-SHIRT promised by CDB software few month back.

My full mailing address is :



RANJAY KR. SINHA PEKON BUILDING COGNIZANT TECH. SOLUTION INDIA LTD. SECTOR V SALT LAKE CALCUTTA - 91 WEST BENGAL - 700091 INDIA PH : 091333573301 X 1415 [...] 4137 13 34_Recall: T-shirts T-shirts T-shirts25_Sinha, Ranjay Kumar (CTS)25_SRanjay@CAL.COGNIZANT.COM31_Wed, 16 May 2001 10:32:04 +0530333_- Sinha, Ranjay Kumar (CTS) would like to recall the message, "T-shirts T-shirts T-shirts".

================================================ 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. 4151 114 24_Re: Buffer pool question16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 16 May 2001 11:10:00 +0530596_iso-8859-1 Radha, I realised that the whole confusion is because of the understanding of the "Free page". Please go thru' the first paragraph of my yesterday's mail below. "As per my understanding, what you are mentioning about 128 'free pages' are the pages kept in the separate queue.When HiperPools are used, DB2 creates and maintains an additional queue of 'free pages'. This queue improves performance because a target page does not have to be located from one of the normal LRU queues, and will not(neccessarily) have to be migrated to the HP before a page is available for a new data [...] 4266 59 33_Re: [DB2 os390 v6 with Unix-jdbc]8_duam lee16_duam_lee@USA.NET29_Wed, 16 May 2001 01:47:09 MDT613_US-ASCII Hi John I also need these informations. We are waiting here for experts comments.

Duam john king wrote: Hello Listers/DB2 Masrter’s .

Has any body experienced this situation. I have to run java programs with UNIX environment with JDBC connection to DB2 on OS390 V6. I m a purely os/390 professional with less experience on DBA side. The major area will be UNICODE problem. The DB2 on OS 390 supports the CCSID as EBCDIC/ASCII. On unix side it is ASCII and UNICODE. How I will solve this problem for setting up the same CHARCTER CONVERSION at both side. Does [...] 4326 47 53_Re: Warning messages seleting from a 160 char column.10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 16 May 2001 18:10:05 +1000407_us-ascii Richard,

We also get these messages. Initially I wondered what these were, then came to know that they were due to the data truncation. (We get an SQLstate of 01004).

Cheers, Kals

-----Original Message----- From: Richard Pack [SMTP:rpack1@MAIL.STATE.MO.US] Sent: Tuesday, May 15, 2001 10:37 PM To: DB2-L@RYCI.COM Subject: Warning messages seleting from a 160 char column. [...] 4374 38 48_Re: CICS Transit Time vs. DB2 Accounting Class 110_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 16 May 2001 10:06:20 +0200279_us-ascii Hi Daryl...

Take a look at statistic report if there are a tx queue and try to determine that tx is queued. As a rule of thumb in a hevy tx environment you may have AT MOST 1% of tx queued if CPU usage > 90%. But it may be unacceptable in some environments. [...] 4413 150 24_Re: Buffer pool question16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 16 May 2001 17:09:04 +0530494_iso-8859-1 Radha,

I have checked all the details i can say only thing we need to conclude this discussion is the exact description of "Free Page" in the bufferpool. What all kind of pages constitutes "Free Page" LRU queue.

This is related to one more question i.e. "Pages are to be brought back to BP to be used again but the reverse is true or not. whether the pages have to be neccessarily moved to HP or not (if at all HP is used), apart from the conditions given by you. [...] 4564 49 42_New Book on DB2 UDB for Windows/UNIX/Linux14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 16 May 2001 04:38:52 -0700326_- The DB2 UDB Developers Guide for Windows and UNIX/LINUX covering the V7.2 and the next release will be published by Prentice Hall and be available early 2002. Look for details and sample information at www.gunningtsi.com. The website will up and running in a few days. I will be looking for feedback. Thanks in Advance, Phil 4614 73 50_Re: Transaction Management using Database Triggers19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 16 May 2001 06:50:26 -0500589_iso-8859-1 Randy, You don't mention platform(s) so its tough to answer. And I can't address the Oracle issue(s). If the platform(s) are OS390/zOS look into MQSeries and/or RRS. If all the datastores can be handled by either of these you can achieve the UOW described. I'm not sure I fully understand the process you describe, but, my real concern is the final process (the XML piece). I don't think you desire to make your initial process wait until this completes, do you? Don't you simply want to have some process that will eventually ensure that it, the XML, is delivered/stored? [...] 4688 39 46_Re: New Book on DB2 UDB for Windows/UNIX/Linux23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 16 May 2001 12:43:37 +0100615_iso-8859-1 Phil do you need anybody to do a proof read? Leslie

-----Original Message----- From: Philip Gunning [mailto:Philip.Gunning@QUEST.COM] Sent: Wednesday, May 16, 2001 12:39 PM To: DB2-L@RYCI.COM Subject: New Book on DB2 UDB for Windows/UNIX/Linux





The DB2 UDB Developers Guide for Windows and UNIX/LINUX covering the V7.2 and the next release will be published by Prentice Hall and be available early 2002. Look for details and sample information at www.gunningtsi.com. The website will up and running in a few days. I will be looking for feedback. Thanks in Advance, Phil [...] 4728 25 3_CRS20_MANIKHANDAN Ramasamy21_RManikha@COVANSYS.COM31_Wed, 16 May 2001 08:47:11 -0400390_- Hi all, Does anyone using CRS(Cost Recovery System)? If u are using it, can u specify what will be the impact.

We are going to do enhancements in CRS at offshore from Next week, i don't have idea about CRS.

Can u please give me some ideas about it?

> Regards, > Manikhandan > Covansys India Ltd (Formerly CBSI) > Ph: 044-2623880, Ext:6384 > Pager No:9632-735538 [...] 4754 215 27_Re: Global Temporary tables17_Polley, Mike (M.)16_mpolley@FORD.COM31_Wed, 16 May 2001 09:04:43 -0400561_iso-8859-1

Try using:

//JSTEP003 EXEC PGM=IKJEFT01,TIME=1440, //..... //.....



//SYSTSIN DD * DSN SYSTEM(DB23) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL') - LIB('????.DB23.SSPGM')



That is: using the unload utility with parm 'sql' specified. This worked for me using Global Temporary Tables.







-----Original Message----- From: Steve Howell [mailto:HOWELLS@STGEORGE.COM.AU] Sent: Tuesday, May 15, 2001 10:28 PM To: DB2-L@RYCI.COM Subject: Global Temporary tables [...] 4970 36 11_DB2 Connect18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 16 May 2001 09:01:11 -0400550_iso-8859-1 Can someone help me out here.

We're using DB2 Connect and Datajoiner V2.1.1 to pull data from DB2 V5 OS/390. I have a column in our table that is defined as LONGVAR Lenght (3986). When using QMF to query the table the column, called TEXT, comes out with the text. No problem here. Problem: When using MS Access or using DB2 Connect Command window, when I pull the column TEXT in it comes in binary form! No text comes out. Guess I'm not sure what is going on here. Something in the translation? With MS Access all I'm doing is [...] 5007 21 8_CODEPAGE12_PAULO RACHID24_paulo_rachid@HOTMAIL.COM31_Wed, 16 May 2001 13:42:55 -0000580_iso-8859-1 HI GURUS,



COMO POSSO FAZER PARA MUDAR O CODEPAGE INTERNO DO DB2 DE 1252 PARA 850. A VERSÃO DO DB2UDB É A 5, A PLATAFORMA É WINDOWS NT 4.0 SP5





_________________________________________________________________________ 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. 5029 29 10_HiperPools13_Larry Findley21_LLFindley@BIGFOOT.COM31_Wed, 16 May 2001 08:14:15 -0500518_- I tried to send a reply to one of the messages in the current string on this topic, and it never showed up, so I will try this route.

On most modern mainframes the physical storage is configured as either central storage or ESTORE dynamically. If you are running on that type of machine you should NOT be using HiperPools. If you are doing so, the ESTORE currently being used for that purpose should be reconfigured as central storage, and the DB2 specifications should be set to use only virtual pools. [...] 5059 24 8_CODEPAGE12_PAULO RACHID24_paulo_rachid@HOTMAIL.COM31_Wed, 16 May 2001 13:46:29 -0000621_iso-8859-1 HI, GURUS



HOW CAN I CHANGE THE CODEPAGE OF DB2 FROM 1252 TO 850. BY THE WAY WE USE DB2UDB 5.0 AND WINDOWS NT 4.0 SP 5. SORRY MY PREVIOUS MESSAGE WAS SENT IN PORTUGUESE.





RACHID

_________________________________________________________________________ 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. 5084 17 30_DB2 Connect V7.1 and Dr.Watson13_Thomas Schulz20_thomas.schulz@SVI.DE31_Wed, 16 May 2001 08:34:42 -0500330_- If have installed DB2 Connect V7.1 PE on my Windows NT Workstation wiht Service pack 5. I go into the Client Configuration Menu to add a Database. Finally if I Try to save this Configuration I get the Dr. Watson coming up. Dr. Watson says 0C1 at DB2CAA.EXE. Does anybody had the some problem. Version 5.2 war working well. [...] 5102 35 43_Datajoiner access to Oracle from DB2 OS/39012_Kirk Hampton16_khampto1@TXU.COM31_Wed, 16 May 2001 09:43:22 -0500615_us-ascii I have been asked about the feasibility of deploying DataJoiner to enable an existing CICS / DB2 application running on OS/390 to access data in an Oracle / AIX server.

The operational data currently resides in DB2 V6.1 on OS/390 v2.8, maintained by a CICS application. Another application residing in Oracle on AIX maintains data related to the data in DB2, but the two applications are currently segregated. An "integration" project proposes to migrate the operational data from DB2 on OS/390 to Oracle on AIX. They would change the data layout, but they are willing to create and maintain a [...] 5138 29 10_Reorg stmt15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 16 May 2001 09:56:33 -0500536_us-ascii Hi

Is online reorg's stmt below is correct ?

REORG TABLESPACE Tspace_X LOG NO SORTDATA NOSYSREC SORTKEYS SHRLEVEL CHANGE MAPPINGTABLE map_table COPYDDN(COPYA,COPYB) STATISTICS TABLE(ALL) INDEX(ALL)

Any suggestions will be highly appreciated.



Regards

================================================ 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. 5168 31 38_Space reuse in DB2 UDB EEE tablespaces0_26_Steve.Westfall@EQUIFAX.COM31_Wed, 16 May 2001 10:06:17 -0500403_us-ascii In DB2 for OS/390, the space from rows deleted from a table residing in a segmented or partitioned tablespace can be reused immediately without need for a reorg. Is this also the case with the non-mainframe versions of DB2, in particular with DB2 UDB EEE 7.1 (on AIX), or do they only reclaim that space when a reorg is done? (Pointers to documentation of this would also be appreciated.) [...] 5200 23 28_ASP and DB2 Connect question13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Wed, 16 May 2001 10:22:34 -0500440_iso-8859-1 I want to use Active Server Pages to issue SQL commands against a DB2/OS390 V6 database via DB2 connect.

Can someone point me to some documentation on how to check for return codes (SQLCODE, SQLSTATE, etc) within the ASP?

Thanks,

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company Phone: (803) 936-4311 Fax: (803) 936-4629 shodgin@scfbins.com [...] 5224 60 14_Re: Reorg stmt34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Wed, 16 May 2001 11:22:59 -0400434_us-ascii There is no need to use LOG NO, SORTDATA NOSYSREC & SORTKEYS with SHRLEVEL CHANGE.

SHRLEVEL CHANGE is executed as if all these were specified, infact using the above options are not even allowed. Other than that, it should be workable statement.

Final statement would look like REORG TABLESPACE .Tspace_X SHRLEVEL CHANGE MAPPINGTABLE map_table COPYDDN(COPYA,COPYB) STATISTICS TABLE(ALL) INDEX(ALL) [...] 5285 58 42_Re: Space reuse in DB2 UDB EEE tablespaces15_Stacey L Waters23_WATERSS1@NATIONWIDE.COM31_Wed, 16 May 2001 10:43:04 -0500537_us-ascii Steve, In Administration Guide: Performance: Chapter 2 section Space Management. This should explain it for you.

Have a good day, Stacey







Steve.Westfall@EQUIFAX.COM@RYCI.COM> on 05/16/2001 10:06:17 AM

From: Steve.Westfall@EQUIFAX.COM@RYCI.COM on 05/16/2001 10:06 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Space reuse in DB2 UDB EEE tablespaces [...] 5344 60 32_Re: ASP and DB2 Connect question0_15_leon@CA.IBM.COM31_Wed, 16 May 2001 11:47:53 -0400413_us-ascii The way you would check return codes depends on the language that you use to build your ASPs. The two main languages are JScript and VBScript though in theory any WSH (Windows Scripting Host) compliant language can be used. If you a re using VBScript you will working with the ADO API and you will need to learn how to work with an Errors collection and individual Error objects in this collection. [...] 5405 27 32_Using CICS in a stored procedure14_Michele Saffer32_Michele_Saffer@CONSULTEC-INC.COM31_Wed, 16 May 2001 11:51:42 -0400507_us-ascii We are running DB2 V5/OS390 2.6. I do not know the version of CICS.

We are trying to execute a CICS command in a stored procedure. I know it's getting in without a problem because I have a DISPLAY in the stored procedure. But I keep getting a -965 when I try to execute the CICS command. (It is a CICS VERIFY command.) I'm getting ABENDAID information that tells me I'm getting a S0C1. When we try to execute the same code in a non-stored procedure program, we do not have any trouble. [...] 5433 105 47_Re: Datajoiner access to Oracle from DB2 OS/3900_23_sjtoeniskoetter@AEP.COM31_Wed, 16 May 2001 11:54:53 -0400416_us-ascii Kirk,

We currently do this, but we have one additional piece that you don't mention - DB2 on AIX. I was never able to get DB2 OS/390 to talk directly to Datajoiner. However, I cataloged the Datajoiner database in a DB2 AIX instance, and I am able to go from DB2 OS/390 to DB2 AIX to Datajoiner to Oracle. There may be a fix for this so that the DB2 AIX step is not needed but I did not find it. [...] 5539 90 49_Bufferpool threshold Prefetch disabled no engine?10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 16 May 2001 11:00:10 -0500392_us-ascii All,

OS/390 DB2 V6

I have a DB2 region that has been up since 4/29/01. I was strolling thru Omegamon screens checking my bufferpool thresholds when I saw some REALLY painful statistics. They look like this: VIRTUAL BUFFER POOL SIZE= 9000 VPOOL BUFFERS ALLOCATED = 9000 VPOOL BUFFERS IN USE = 7 VPOOL BUFFERS TO BE DEL = 0 USE COUNT = 2 HIPERSPACE NAMES = @221FDRP [...] 5630 84 14_Re: Reorg stmt0_18_mebert@AMADEUS.NET31_Wed, 16 May 2001 18:11:07 +0200579_us-ascii Some clarifications:

using SORTDATA and NOSYSREC is implied by OLR if an explicit clustering index exists. If there is none, then you do need to specify a SYSREC DD card, and OLR will give a message that SORTDATA and/or NOSYSREC were ignored if you did specify them. The same for SORTKEYS - OLR will ignore this keyword if no index or foreign key exists; otherwise it's implied. Also implied by the above is that you cannot OLR TSs with a record length approaching 32k (the "composite" record size - index keys+data - must be no more than 32760 bytes). This [...] 5715 43 38_Sequential vs. List Prefetch Questions21_Patrick M Darienzo Jr27_pdarienzo@KEYSPANENERGY.COM31_Wed, 16 May 2001 12:13:25 -0400454_us-ascii Hello Listers,

I have inherited a very large partitioned tablespace with two additional non-clustering indices. One is set to read forward through the data and one is for reading in reverse. The former is also used for singleton lookups.

Catalog statistics for these indices show a 43% clusterratio. An EXPLAIN on a query we're running shows an 'L' in the PREFETCH column. The performance is very slow using this access path. [...] 5759 111 30_Re: Hybrid vs Nested Loop Join12_Aedan Molony20_Aedan.Molony@PSIR.IE31_Wed, 16 May 2001 17:22:53 +0100722_iso-8859-1 Hi List, Our little problem has been fixed by the cat stats twiddling...ran in 2 mins last night as opposed to hours.

The permanent fix is to add OPTIMIZE FOR 1 ROW to the SQL. Developers can't do this till next week.

Thanks for all your assistance on this one!!

Regards,

Aedan











******************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify us immediately at MailManager@psir.ie and delete this E-mail from your system. [...] 5871 110 50_Re: Transaction Management using Database Triggers14_Randy Williams23_Randy.Williams@IINT.COM31_Wed, 16 May 2001 09:15:56 -0700497_us-ascii Hi Rick,

I need the solution to be platform independent and I don't need a transport utility because that is already in place. All I need is a way to link the individual triggered events together as one unit of work per transaction. The XML piece is kept separate from the initial process. Do you know of a field that I could associate to each of my triggered events that would allow my second process (the XML piece) to combine the information into transaction-level events? [...] 5982 19 53_Re: Bufferpool threshold Prefetch disabled no engine?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 16 May 2001 19:17:14 +0200450_us-ascii At a first look ...I'm guessing ...if VP SEQt thr. is too low....try 50% and with a lower DWQT ...say 30%

I'm doing some calculations, but I need I/O datas....:-))

HTH

MAx Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 6002 16 53_Re: Bufferpool threshold Prefetch disabled no engine?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 16 May 2001 19:20:22 +0200406_us-ascii Is there any parallellism ? If not (but it may be possible) use a lower VPPSEQT....SAY 10-20%...

Max Scarpa DB2 sysprog (not certified, be aware !!)

================================================ 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. 6019 216 14_Re: Reorg stmt34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Wed, 16 May 2001 13:23:51 -0400746_iso-8859-1 Herr Ebert

There is piece of information which is very vague in manual & in practicality.

Here is a piece of information from DB2 V6 Utility Guide (page 281-282 of hard copy) ---------------------------------------------------------------------------- ----------------------------------------------

SHRLEVEL Specifies the method of performing the reorganization. The.............. ... ... CHANGE .... .... If you specify CHANGE, you cannot specify the following parameters: . LOG. Reorganization with CHANGE always creates an image copy and always refrain from logging records during reloading. . SORTDATA, NOSYSREC, SORTKEYS. Reorganization with CHANGE always operates as if these parameters were specified. [...] 6236 18 14_Round function23_THOLKAPPIAN Chidambaram21_CTholkap@COVANSYS.COM31_Wed, 16 May 2001 13:28:56 -0400370_- Hi List,

Is there any ROUND function is available in DB2 like that in Oracle? we are DB2v6.

thanks --Tholkappian

================================================ 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. 6255 25 53_Re: Bufferpool threshold Prefetch disabled no engine?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 16 May 2001 19:31:40 +0200368_us-ascii It's the number of prefetch engines you can use for prefetch reads ie the max number of Media Manager Request blocks.

If you are in V5 you've 300 prefetch read engines, in V6 is 600. I think you can modify the number in member DSN6SPRC

in SDSNMACS library, variable SPRMRDU. But it's a risk, as every engine is a new SRB. NO ZPARM control. [...] 6281 15 18_Re: Round function0_26_truman.g.brown@VERIZON.COM31_Wed, 16 May 2001 13:59:21 -0400382_us-ascii Depends on what you want - there is a ZPARM option for rounding in decimal divide - DECDIV3 YES (for rounding) or NO.

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. 6297 66 18_Re: Round function0_20_John_Lendman@FPL.COM31_Wed, 16 May 2001 14:10:31 -0400396_us-ascii Yes it does.

If you have a DB2 V6 pub look in the SQL reference page 263.

It goes sometime like this "round(express1,expression2,n)" where the function returns expression1 rounded to expression2 n places to the right or left of the decimal point depending on the value of expression2.



John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413 [...] 6364 33 45_Repost: Online Survey About DB2 Documentation13_Morrill, John12_JohnM@VP.NET31_Wed, 16 May 2001 12:22:16 -0600364_- Dear DB2 Interested Parties,

The DB2 Universal Database (DB2 UDB) Information Development team is looking for feedback on DB2 UDB documentation.

Please complete this survey if your environment includes: o DB2 UDB for UNIX, Windows or OS/2; or o DB2 Connect for UNIX, Windows or OS/2; or o DB2 UDB for OS/390 and either DB2 UDB or DB2 Connect. [...] 6398 96 18_Re: Round function9_Mark Ruhe19_Mark.Ruhe@QUEST.COM31_Wed, 16 May 2001 11:20:26 -0700634_iso-8859-1 Yes, version 6 and 7 both have scalar functions ROUND.

-----Original Message----- From: THOLKAPPIAN Chidambaram [mailto:CTholkap@COVANSYS.COM] Sent: Wednesday, May 16, 2001 12:29 PM To: DB2-L@RYCI.COM Subject: [DB2-L] Round function



Hi List,

Is there any ROUND function is available in DB2 like that in Oracle? we are DB2v6.

thanks --Tholkappian

================================================ 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. 6495 49 32_Re: ASP and DB2 Connect question13_Morrill, John12_JohnM@VP.NET31_Wed, 16 May 2001 12:29:42 -0600651_iso-8859-1 Greetings Scott!

Check out:

http://support.microsoft.com/support/kb/articles/Q168/3/36.asp

and

http://support.microsoft.com/support/kb/articles/Q167/9/57.asp

Cheers!

John

-----Original Message----- From: Hodgin, Scott [mailto:shodgin@SCFBINS.COM] Sent: Wednesday, May 16, 2001 9:23 AM To: DB2-L@RYCI.COM Subject: ASP and DB2 Connect question



I want to use Active Server Pages to issue SQL commands against a DB2/OS390 V6 database via DB2 connect.

Can someone point me to some documentation on how to check for return codes (SQLCODE, SQLSTATE, etc) within the ASP? [...] 6545 22 28_DB2 Connect and Windows 200012_Kirk Hampton16_khampto1@TXU.COM31_Wed, 16 May 2001 13:26:50 -0500315_us-ascii Does anyone know what version of the DB2 Connect Client (CAE) would be "certified" to run on Windows 2000 ? This would be a Win2K server running an application that uses the client code to talk to a WinNT (or Win2K) server running the DB2 Connect EE 6.1 gateway, accessing DB2 UDB 6.1 on OS/390 2.8 ? [...] 6568 26 24_CA-Platinum Merge/Modify16_Michael McKinney16_mmckinne@CSC.COM31_Wed, 16 May 2001 15:18:45 -0400617_us-ascii All,

Anyone using CA's (Platinum) Merge/Modify product for or as part of your backup strategy ?

Any problems with the product ? especially on release P99C ?

Any problems using in conjunction with Fast Recover ?

Any thoughts or input would be appreciated.

Be good and have a great day.

Mike CSC Global Infrastrcuture Services

================================================ 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. 6595 67 36_Re: Using CICS in a stored procedure0_26_JCameron@MSI-INSURANCE.COM31_Wed, 16 May 2001 14:49:07 -0500420_us-ascii We are also DB2 V5/OS390 2.6, and ran into this. You need to look at the IBM redbook titled : "Getting Started with DB2 Stored Procedures: Give Them a Call through the Network" ... specifically, chapter 13. We have a stored procedure that executes a CICS LINK command using the EXCI method, following the example in this Redbook. I'm not familiar with the VERIFY command, but this may help ... Good Luck ! [...] 6663 146 50_Re: Transaction Management using Database Triggers19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 16 May 2001 14:53:52 -0500380_iso-8859-1 Hi Randy, Well, on second reading I don't think you have a problem now that the XML piece is out of the picture. And provided I really do understand it! First, the tran IS the LUW, given all work is completed prior to an implied or explicit commit and no commits are in any stored procedures (V7). All triggers fired, stored procedures, etc. are part of this LUW. [...] 6810 24 41_Implementing chargeback on Resource usage12_Brian Picard26_brian_picard@ALTAVISTA.COM31_Wed, 16 May 2001 15:02:01 -0500568_- Hello, We are exploring ways to implement chargeback on resource usage for DB2 access. We are interested in finding the right records (Accounting IFCIDs, or RMF recs) we should be using. We are currently interested in charging based on CPU time, dasd usage and tape usage. The rest will be treated as a overhead. A considerable part of our system also uses distributed DRDA apps. What trace records would be more appropriate to use in order to arrive at the CPU time.? Accounting Class2 - class3 equvates to cpu time resident in db2. But we have to scan thru a [...] 6835 24 28_Archive tools for DB2 on AIX21_Karthikeyan Vinayagam18_vkarthik@YAHOO.COM31_Wed, 16 May 2001 16:11:10 -0500399_- Greetings!

We are looking at solutions for selectively archiving data on UDB for AIX. (UDB v6.1 AIX v4.3)

One product that came up, was 'Archive for Servers' from Princeton Softech. We would like to share any experiences on using this product.

Are there any other tools which provide such a functionality?

Any inputs or suggestions on this are highly appreciated. [...] 6860 98 42_Re: Sequential vs. List Prefetch Questions13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 16 May 2001 17:10:05 -0500573_us-ascii Patrick,

I am not a big fan of updating the catalog statistics in this manner. The main reason is that you may have improved the performance of one query, but potentially to the detriment of other queries.

But to answer your questions: 1. Yes the 'S' indicates sequential prefetch. Sequential prefetch has been determined at bind time based on the clusterratio of 80% of the index. I do not believe that sequential detection will disable prefetch once the data is not found on the sequential pages, since sequential detection did not invoke it. [...] 6959 33 50_Re: Transaction Management using Database Triggers10_Rob Wright17_rwright@LIC.CO.NZ31_Thu, 17 May 2001 11:34:59 +1200525_us-ascii As you are using a stored procedure, you can take a look at

7.2.3.1.1 Passing environment information to the stored procedure

If you specify the DBINFO parameter when you define a stored procedure with PARAMETER STYLE DB2SQL, DB2 passes a structure to the stored procedure that contains environment information. Because the structure is also used for user-defined functions, some fields in the structure are not used for stored procedures. The DBINFO structure includes the following information: [...] 6993 56 12_Re: CODEPAGE18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Thu, 17 May 2001 09:54:32 +0930504_iso-8859-1 Que?









PAULO RACHID @RYCI.COM> on 16/05/2001 23:12:55

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: CODEPAGE





HI GURUS,



COMO POSSO FAZER PARA MUDAR O CODEPAGE INTERNO DO DB2 DE 1252 PARA 850. A VERSÃO DO DB2UDB É A 5, A PLATAFORMA É WINDOWS NT 4.0 SP5 [...] 7050 48 40_Re: Having problem with Identity Columns14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 16 May 2001 20:11:47 -0500611_- The problem with IDENTITY columns is not that DB2 "does not automatically update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_ mechanism, (apart from dropping and recreating the table with updated DDL) for modifying MAXASSIGNEDVAL.

Actually, I would not want DB2 to automatically update MAXASSIGNEDVAL. If you read the notes about using DPROPR to keep multiple databases in sync, you'll see a suggestion to use, say: - BY DEFAULT INITIAL 1 INCREMENT BY 2 on one system; and - BY DEFAULT INITIAL 2 INCREMENT BY 2 on the other. That way, each system uses a unique set of numbers. [...] 7099 57 27_Re: Global Temporary tables14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 16 May 2001 20:25:57 -0500396_- DSNTEP2 analysis the SQL string to determine how to process the statement. As DTT's came out after the initial release of V6, DSNTEP2 does not know that there is a DECLARE type statement that is executable.

You could modify the source - it's in SDSNSAMP.

James Campbell





On Wed, 16 May 2001 12:27:46 +1000, Steve Howell wrote: [...] 7157 148 27_Re: Global Temporary tables12_Steve Howell23_HOWELLS@STGEORGE.COM.AU31_Thu, 17 May 2001 12:03:10 +1000323_- My problem is now solved.

I had just found out we were using an old version of DSNTEP2 and that as of Version 6 IBM supplies DSNTEP2 in object format, therefore you no longer need the PL1 compiler.

Using the version 6 DSNTEP2 my query now executes correctly.

Many thanks to those who responded. [...] 7306 177 24_Re: Buffer pool question10_Radha Rani24_radha_rani@ALTAVISTA.COM31_Wed, 16 May 2001 20:44:37 -0700386_- Sanjeev,

There is no such thing as "Free page LRU queue". Buffer manager just maintains an "empty buffer queue". Least recently used has no meaning for free buffers. In fact one of the reasons why free pages are preferred by the buffer manager over other available pages is because buffer manager does not have to invoke the LRU logic and hence a shorter CPU path length. [...] 7484 49 24_Re: Buffer pool question16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 17 May 2001 10:38:34 +0530313_- Thanks for the correction of "Free Page" . It actually do not work on the LRU logic because pages actually contain nothing, so there is no need to have a logic of priority. For this reason, it seems to me NOW that the pages are called as FREE only when it is migrated to HP. Thanks for the clarifications. [...] 7534 25 21_I'm out of the office12_Kevin Arnold31_kevin.n.arnold@US.PWCGLOBAL.COM31_Thu, 17 May 2001 03:01:53 -0400292_us-ascii This is an automated response, please do not reply.

I've successfully received your email message RE: "DB2-L Digest - 16 May 2001 to 17 May 2001 (#2001-138)". I am currently out of the office and will return on May 24. I will respond to your message as soon as I return. [...] 7560 63 18_AW: Round function12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 17 May 2001 09:52:53 +0200807_iso-8859-1 Tholkappian,

in DB2 for OS/390 Version 6 and higher a round function is available. It rounds the first numeric argument to the number of places specified in the second argument.

HTH.

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 ---------------------------------------------------------------------- Linux is like a wigwam: no gates, no windows, and an apache inside..... [...] 7624 234 42_Re: inserting and retreiving images in db211_Alfred Moos21_moos@FH-HEIDELBERG.DE31_Thu, 17 May 2001 10:18:44 +0200637_iso-8859-1 Hello Hari Kishen,

when you use java you can do it like this example:

CREATE TABLE Bildtabelle (Schluessel VARCHAR (254) NOT NULL, Bild BLOB (64K) NOT NULL);



/**********************************************************/ /* Datenbankverbind herstellen */ /* */ /**********************************************************/ import java.sql.*;

public class Datenbank { Statement sqlbefehl; Connection datenbankverbindung; boolean verbindungOK = true;

public Datenbank() { try { // JDBC-Treiber für den lokalen Datenbankzugriff laden: Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); [...] 7859 27 40_Trigger migration from Oracle to DB2 7.113_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM31_Thu, 17 May 2001 13:31:40 +0500361_- Hi,

I am currently doing a migration project from Oracle 8.1.6 to DB2 UDB 7.1 on AIX, and am facing a problem in migration of Triggers (especially BEFORE triggers). Since DB2 permits very few statements in BEFORE triggers, i do not know how to call stored procedures from triggers in DB2. Can anybody please suggest a resolution to this problem ? [...] 7887 80 44_AW: Trigger migration from Oracle to DB2 7.112_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 17 May 2001 11:04:04 +0200841_iso-8859-1 Ritesh,

you may find the information you need under http://www-4.ibm.com/software/data/db2/migration/

By the way: Congratulations that you are moving from Oracle 8.1.6 to DB2. May I know your reasons for the change ?

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 ---------------------------------------------------------------------- Linux is like a wigwam: no gates, no windows, and an apache inside..... [...] 7968 20 30_Stored Procedures and Commands11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Thu, 17 May 2001 10:27:48 +0100325_us-ascii Can anyone please tell me whether it is possible to run the UDB for Unix admin commands (e.g. List Applications, Get Snapshot etc.) from within a Stored Procedure? I've been using the SP Builder and have SQL Stored Procs working, but would find it useful to be able to issue commands as well. Thanks Dave Hayes [...] 7989 83 20_bind package problem8_Dan Zink18_zdan02@HOTMAIL.COM31_Thu, 17 May 2001 17:33:25 +0800694_- Hi,

Could you tell me what's wrong here?

In order add a user to use PM, I grant the auth first:

//SYSTSIN DD * DSN SYSTEM(DSN1) RUN PROGRAM(DSNTIAD) PLAN(DSNTIA61) - LIB('DSN610.RUNLIB.LOAD') END //SYSIN DD * GRANT BINDADD TO PMU01; GRANT PACKADM ON COLLECTION DGOOM610 TO PMU01; GRANT SELECT ON SYSIBM.SYSDATABASE TO PMU01; GRANT SELECT ON SYSIBM.SYSDBRM TO PMU01 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSINDEXES TO PMU01; GRANT SELECT ON SYSIBM.SYSPACKAGE TO PMU01 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSPACKSTMT TO PMU01; GRANT SELECT ON SYSIBM.SYSPLAN TO PMU01; GRANT SELECT ON SYSIBM.SYSTABLES TO PMU01; GRANT SELECT ON SYSIBM.SYSTABLESPACE TO PMU01; [...] 8073 85 18_Re: Round function23_THOLKAPPIAN Chidambaram21_CTholkap@COVANSYS.COM31_Thu, 17 May 2001 05:49:22 -0400672_iso-8859-1 Thank you all and I was able to use the function.

--Tholkappian



> -----Original Message----- > From: Peter, Georg [SMTP:G.Peter@DZBW.DE] > Sent: Thursday, May 17, 2001 1:23 PM > To: DB2-L@RYCI.COM > Subject: AW: Round function > > Tholkappian, > > in DB2 for OS/390 Version 6 and higher a round function is available. It > rounds the first numeric argument to the number of places specified in the > second argument. > > HTH. > > With kind regards - mit freundlichen Grüssen, > Georg H. Peter > DB2 Data Base Administrator c/o > ------------------------------------------------------------------- > Datenzentrale Baden-Wuerttemberg > [...] 8159 102 24_Re: bind package problem0_18_mebert@AMADEUS.NET31_Thu, 17 May 2001 11:52:37 +0200425_us-ascii Hi Dan,

I've grappled with this before. GRANT SELECT ON SYSIBM.SYSSTRINGS TO PUBLIC is what I did. One wonders how/whether they test these installation procedures at IBM...

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany



From: Dan Zink on 17/05/2001 09:33 GMT

Hi,

Could you tell me what's wrong here? [...] 8262 103 24_Re: bind package problem18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Thu, 17 May 2001 15:28:54 +0500593_- You have not granted select authority on sysibm.sysstrings to pmu01

> -----Original Message----- > From: Dan Zink [SMTP:zdan02@HOTMAIL.COM] > Sent: Thursday, May 17, 2001 3:03 PM > To: DB2-L@RYCI.COM > Subject: bind package problem > > Hi, > > Could you tell me what's wrong here? > > In order add a user to use PM, I grant the auth first: > > //SYSTSIN DD * > DSN SYSTEM(DSN1) > RUN PROGRAM(DSNTIAD) PLAN(DSNTIA61) - > LIB('DSN610.RUNLIB.LOAD') > END > //SYSIN DD * > GRANT BINDADD TO PMU01; > GRANT PACKADM ON COLLECTION DGOOM610 TO PMU01; > GRANT SELECT ON SYSIBM.SYSDATABASE [...] 8366 110 24_Re: bind package problem17_Miguel de Andrade18_ANDRADE@DE.IBM.COM31_Thu, 17 May 2001 10:53:03 +0100518_us-ascii Have you granted SYSIBM.SYSSTRINGS?

Best Regards, Miguel de Andrade

DB2 UDB for OS390/zOS & Siebel on OS/390 - Siebel 2000 Certified Consultant Email: andrade@de.ibm.com IBM Labor, Boblingen, Germany









Dan Zink on 05/17/2001 10:33:25 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: bind package problem



Hi,

Could you tell me what's wrong here? [...] 8477 115 24_Re: bind package problem14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Thu, 17 May 2001 12:10:39 +0200592_iso-8859-1 Hi Dan,

shouldn't you grant select-authority on sysibm.sysstrings to pmu01 as well, since this authority is required.

Dan Zink schrieb: > > Hi, > > Could you tell me what's wrong here? > > In order add a user to use PM, I grant the auth first: > > //SYSTSIN DD * > DSN SYSTEM(DSN1) > RUN PROGRAM(DSNTIAD) PLAN(DSNTIA61) - > LIB('DSN610.RUNLIB.LOAD') > END > //SYSIN DD * > GRANT BINDADD TO PMU01; > GRANT PACKADM ON COLLECTION DGOOM610 TO PMU01; > GRANT SELECT ON SYSIBM.SYSDATABASE TO PMU01; > GRANT SELECT ON SYSIBM.SYSDBRM TO PMU01 WITH GRANT OPTION; > GRANT [...] 8593 24 12_UDF problems13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM31_Thu, 17 May 2001 15:16:44 +0500630_- Hi,

I wanted to know if DB2 7.1 on AIX supports SQL statements in User Defined fuinctions. Please can somebody provide some information regarding the same.

thx and regds, RiteshWARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received [...] 8618 62 14_Re: HiperPools14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Thu, 17 May 2001 05:29:20 -0500400_iso-8859-1 There is a very important exception to this advice - if you are running in 31-bit mode and your machine has more than 2Gb storage and you think DB2 performance would improve if you could use more than 2Gb storage, then you desperately need to use hiperpools.

If you have only 2Gb or less of storage, then the case for using hiperpools is less, but can still be useful for BP0. [...] 8681 23 24_DB2 V6 maintenance level11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 17 May 2001 06:58:23 -0500336_iso-8859-1 I need to apply maintenance on DB2 (OS/390). What are the recommendations as to how close to the edge you should be? We don't do anything real fancy (no ERP, no datasharing) and our programmers are just getting ready to start with stored procedures and triggers. I don't want to be too current and end up with problems. [...] 8705 50 45_Re: Implementing chargeback on Resource usage11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Thu, 17 May 2001 08:49:09 -0400515_iso-8859-1 One of the best tools to get started on DB2 (or any other) chargeback project is MXG. This is a collection of SAS (you must have SAS for your system) that will extract and analyze all of the IBM SMF and RMF records (as well as a host of OEM ones as well). While (to my knowledge) there is not a complete chargeback system in MXG, it is a great "tool kit" to build one with, and I have used it several times for this purpose. MXG has lots of internal documentation about where and how to get CPU and [...] 8756 142 14_Re: HiperPools14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Thu, 17 May 2001 07:50:26 -0500565_iso-8859-1 No problem.

I have found significant performance improvements when using hiperpools, but there are a few 'gotchas'.

Amongst the many things, you need to look at the HP read-to-write ratio. 50% means that 1 page is read from HP for every 1 page written to HP, 25% means 1 page read for every two written, etc. Every page read from HP is potentially a I-O operation saved. If you get a ratio much below 25% then you are spending a lot of CPU writing to HP for very little benefit. A negative value for the r-w ratio means almost nothing [...] 8899 19 12_unit of work12_Victor Kumar28_victor_kumar@DCF.STATE.FL.US31_Thu, 17 May 2001 07:52:27 -0500456_- In our shop we are using db2 ver 6 with tux. We are getting lots of complaints that the resp time on db2 is slow and we are saying it is in tux or web server. I am kind of new to db2, my experience has been in MVS and CICS. I would like to mesaure the time the task spends the time in db2. I want the total time from the time it entered db2 and left db2. Where it spend the time and what wait and all. If some one can point to the right field I can [...] 8919 66 22_script center and adsm8_ian.long22_IAN.LONG@GRATTAN.CO.UK31_Thu, 17 May 2001 14:18:30 +0100338_iso-8859-1 HI We use ADSM to backup our databases but if we try and query or delete from script center using DB2AUDTL then we get "Error: Initialize session failed with ADSM return code 168" any help welcome. We are on aix and db2 5.2 Ian The Opinions expressed in this e-mail are strictly those of the author and not those of Grattan PLC 8986 192 14_Re: HiperPools12_Victor Kumar28_Victor_Kumar@DCF.STATE.FL.US31_Thu, 17 May 2001 09:46:49 -0400574_us-ascii Sir, How Can I get the report of HP read-to-write ratio. Can you please give me some advice.



Victor Kumar 487-7058







"Vassie, Edward" To: DB2-L@RYCI.COM Subject: Re: HiperPools Sent by: DB2 Data Base Discussion List



05/17/2001 08:50 AM Please respond to DB2 Data Base Discussion List











No problem.

I have found significant performance improvements when using hiperpools, but there are a few 'gotchas'. [...] 9179 218 14_Re: HiperPools16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 17 May 2001 19:40:47 +0530579_iso-8859-1 Read from HP to VP / Write to HP from VP. This should be good to get good utilization HP. Rest of the things are explained by Vassie.

Regards Sanjeev

> -----Original Message----- > From: Victor Kumar [SMTP:Victor_Kumar@DCF.STATE.FL.US] > Sent: Thursday, May 17, 2001 7:17 PM > To: DB2-L@RYCI.COM > Subject: Re: HiperPools > > Sir, > How Can I get the report of HP read-to-write ratio. Can you please give > me > some advice. > > > Victor Kumar > 487-7058 > > > > > "Vassie, > Edward" To: DB2-L@RYCI.COM > e@BMC.COM> Subject: Re: [...] 9398 33 16_Commit Overhead?15_Jackson Reavill18_damcon2@US.IBM.COM31_Thu, 17 May 2001 10:10:55 -0400605_us-ascii Hello all,

I know various factors come into play, but I was wondering if someone could please tell me a rule of thumb answer to the following question:

What is the typical wall-clock time overhead of a commit in a batch OS/390 DB2 V6 environment?

TIA, Jay



Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works ----------------------------------------------------- [...] 9432 23 46_DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY18_Ghose, Roger (MTO)25_Roger.Ghose@MTO.GOV.ON.CA31_Thu, 17 May 2001 10:11:32 -0400490_iso-8859-1 Hi. Everyone, Any ideas/suggestions will be appreciated on managing a single point of recovery with DB2/IMS co-existing under OS/390 in a 24x7 environment. The system is quite active as far as online is concerned, and running a DBR(IMS) and QUIESCE(DB2) has proven to hit a lot of online transaction, for which the users are not happy about. I would like to know is any way where the impact is minimal and yet we have a marker to recover to single point (both IMS and DB2). [...] 9456 17 18_OS390 DB2 Passport13_Daley, Norman18_NDaley@CINERGY.COM31_Thu, 17 May 2001 09:34:51 -0500491_iso-8859-1 Someone on the list had contacted me a while back about Indus' Passport release 8.0 CPU usage compared to earlier releases. I have a bit more info I could share now, if you want to contact me off-list.

Norm Daley NDaley@Cinergy.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. 9474 25 28_SAP BW 2.0b locking problems15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM31_Thu, 17 May 2001 09:31:28 -0500551_us-ascii We are in the process of testing SAP/BW Basis Release 4.6C, kernal 4.6D V531. We seem to be encountering locking problems when one of the functions of the system tries to create a database to create a temp table. This has been tested when the process is the only one running in the system on a single app server configuration. To solve this problem we have stopped and started the SAP system. The application then works for a period of time. What we are thinking is that there is a phontom process running that has a lock on the catalog [...] 9500 61 20_Re: Commit Overhead?34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Thu, 17 May 2001 10:41:56 -0400371_us-ascii If you are looking for just wall clock timing, it may be difficult to predict.

However, there are two extra I/O for commit, this might contribute to your wall clock time apart from CPU time.

Other cons could be resetting index look-aside & resetting sequential detection indicator etc. Of course, I am not mentioning various pros for commits. [...] 9562 26 29_European IDUG rquest for info10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 17 May 2001 16:49:18 +0200389_us-ascii Hi esteemed List

I'm guessing if someone knows where I can find some infos about IDUG subscriptions & payments. I checked IDUG web

site and I contacted via e-mail their office, but no one replied to my mail. Our administration office asked for more infos,

above all for payment procedures for subscriptions.

Can anyone give me some hint/contact ? [...] 9589 65 32_Re: SAP BW 2.0b locking problems0_20_John_Lendman@FPL.COM31_Thu, 17 May 2001 10:57:00 -0400359_us-ascii We do not run BW on DB2, but we do have Supply chain running on 4.5B of SAP. And we do have something very similar to this. We seem to be getting deadlocks on certain tables when it is the only thing running. I found an OS note that said you need to run RUNSTATS on that table that is getting the deadlock. I did this and it seems to work fine. [...] 9655 30 34_Re: European IDUG request for info14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 17 May 2001 10:20:29 -0500424_iso-8859-1 Hi Max,

what happens when you phone ?:

IDUG Headquarters Tel: +1 312 644 6610 FAX: +1 312 321 5158 E-mail: idugeucadministration@sba.com Web: http://www.idug.org

OR you can register online of print off the registration forms and fax them in.

N.B. there is a note on the site which states "Online registration capability will be available by Friday, 25 May", which is next Friday. [...] 9686 110 20_Re: Commit Overhead?16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Thu, 17 May 2001 11:24:17 -0400621_us-ascii Remember that writes to the log at commit time are synchronous. Most other DB2 writes are asynchronous. Therefore, for units of work that do massive updates with frequent commits, there will be added i/o wait time because of the synchronous log writes. How frequently should there be commits, one might ask. It depends: there is always a tradeoff between recoverability/restart issues, concurrency with other tasks, and the issue of synchronous i/o log writes at each commit. A long running batch update process that does millions of updates will easily see this kind of impact when there is a commit after [...] 9797 62 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY11_Bill Happel22_WRHappel@US.FORTIS.COM31_Thu, 17 May 2001 10:29:24 -0500443_US-ASCII Hi Roger. Thank God we do not have to worry about this - yet. But I could throw out some BS and see what sticks. I have also added the IMS List Server to the Distribution List to see if anyone has any additional comments.

The biggest problem is probably on the IMS side. I know billions of people have sent in requests to IBM to add QUIESCE functionality to IMS. I am not sure if and when IBM was going to look into this. [...] 9860 38 16_Re: unit of work10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Thu, 17 May 2001 11:33:27 -0400555_us-ascii In Tmon for DB2, from the Primary Menu select option 5, Online Anlysis. Then go to option 2, Thread Activity. Here you will see the history of threads that have executed. You can specify filters on this data if you want. Place your cursor an any thread you want to look at and hit enter. On the next screen, Online Analysis Thread Detail, position the cursor on the field TOTAL under the Elapsed Times header and hit enter. Here you will see the breakdown of your time. Subtract the DB2 Elapsed time from the Total Elapsed time and you will [...] 9899 86 26_Re: script center and adsm0_25_pierre.evans@AUTOZONE.COM31_Thu, 17 May 2001 10:49:09 -0500408_us-ascii Ian,

Although we don't use the Script Center here, we've found that messages like 'ADSM return code 168' are not exactly meaningful, and the best way to determine the actual problem is to query the adsm activity logs to see what error adsm is receiving when it attempts to query or delete your backup. Then check the adsm/tsm messages manual to see what are the likely causes/solutions. [...] 9986 90 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY0_19_Tim.Lowe@STPAUL.COM31_Thu, 17 May 2001 11:00:57 -0500512_us-ascii Roger, I agree with Bill that the IMS problem is more significant. You might want to look at a new "separately priced" feature in IMS version 7 called "IMS Online Recovery Service" that enables you to "recovery databases to any point in time".

You might also want to look into the new DB2 V7 commands, "SET LOG SUSPEND" and "SET LOG RESUME" to "suspend logging and update activity". This would enable a shop to use a capability with some dasd (like IBM's ESS/Shark) to "snapshot" your dasd. [...] 10077 53 80_Seeking several DB2 and/or UDB DBAs to work on a long term projec t in Phoenix!!10_West, Dawn16_dwest@AJILON.COM31_Thu, 17 May 2001 12:26:32 -0400413_- Dear Colleagues, Ajilon is a leading IT consulting firm with over 6,000 employees in North America. We are currently looking for UDB and DB2 DBAs to work in Phoenix on a multi-year contract. We have been awarded a project to provide DBA support to a major Phoenix client. We have 3-4 positions still open for DB2 and UDB DBAs with at least 2 years DBA experience in DB2/mainframe or UDB/Unix environments. [...] 10131 50 45_Re: Implementing chargeback on Resource usage17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Thu, 17 May 2001 13:37:01 -0300320_iso-8859-1 Brian,

RMF acts at system level, it doesn´t have detailed records about each task. If your threads are started tasks or jobs, the best way is to get from smf record 30. If it came from CICS, smf 110. For DRDA threads I think DB2 accounting records have all resource comsuption on the server side. [...] 10182 39 33_Re: European IDUG rquest for info11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Thu, 17 May 2001 18:51:05 +0100563_us-ascii Max,

I've just been on the IDUG web sote and the information is there. If you want to register on-line you will have to wait until 25 May, but you could print and fax the form now.

Jeremy



Max Scarpa wrote:

> Hi esteemed List > > I'm guessing if someone knows where I can find some infos about IDUG > subscriptions & payments. I checked IDUG web > > site and I contacted via e-mail their office, but no one replied to my > mail. Our administration office asked for more infos, > > above all for payment procedures [...] 10222 22 41_Poster Of Catalog Tables UDB 6.1 (NT/AIX)11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Thu, 17 May 2001 13:05:51 -0500491_iso-8859-1 Hi,

Just like Poster Of Catalog Tables Of Oracle, DB2 ....

Is anyone aware of any company Which gives out the Poster Of Catalog Tables of UDB 6.1 (NT/AIX). And, if yes How can I order one ?



Thanks.

Amit

================================================ 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. 10245 29 8_ims list13_Jimmy Willett13_JRLBW@AOL.COM29_Thu, 17 May 2001 14:14:58 EDT108_US-ASCII does anyone know the email address of the ims list...... the one much like this db2 list???? thanks 10275 51 14_Re: HiperPools12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Thu, 17 May 2001 13:34:02 -0500536_iso-8859-1 Larry

These comments are correct if you have 2GB of real storage. However, if your storage exceeds 2GB, then you would configure all storage above 2GB as estore.

Tom Moulder (BMC Software, Inc.)

-----Original Message----- From: Larry Findley [mailto:LLFindley@BIGFOOT.COM] Sent: Wednesday, May 16, 2001 8:14 AM To: DB2-L@RYCI.COM Subject: HiperPools



I tried to send a reply to one of the messages in the current string on this topic, and it never showed up, so I will try this route. [...] 10327 25 12_Re: ims list14_Dean Montevago18_DMonteva@VNSNY.ORG31_Thu, 17 May 2001 14:34:16 -0400423_- > IMS-L (IMS discussion group) > You can join this group by sending the message "subscribe IMS-L > your name" to listproc@lists.missouri.edu



> -----Original Message----- > From: Jimmy Willett [SMTP:JRLBW@AOL.COM] > Sent: Thursday, May 17, 2001 2:15 PM > To: DB2-L@RYCI.COM > Subject: ims list > > does anyone know the email address of the ims list...... > the one much like this db2 list???? > thanks [...] 10353 58 36_Re: Dynamic SQL and CPU usage report9_Wu, James13_jwu@KRAFT.COM31_Thu, 17 May 2001 13:41:42 -0500511_iso-8859-1 If you had IBM/Tivoli TDS (Tivoli Decision Support, formerly Performance Reporter), you would be able to report CPU usage on dynamic(and static) SQLs on different levels: plan, package, correlation id, userid, SQL, etc.



James Wu:-)

(847)646-5548 jwu@kraft.com





-----Original Message----- From: Chenny [mailto:solaiyappan_chenniappan@MAIL.AMSINC.COM] Sent: Tuesday, May 15, 2001 7:36 AM To: DB2-L@RYCI.COM Subject: Re: Dynamic SQL and CPU usage report [...] 10412 37 28_interpreting DSN1PRNT output10_Derek Lund22_derek.lund@UTORONTO.CA31_Thu, 17 May 2001 14:39:37 -0400400_US-ASCII I am seeing a DEADLOCK on a tablespace. The tablespace has row level locking (one table in TS). There are only 15 rows on the table (a control table). I am having difficulty finding which row the two applications are deadlocking on. I am unable to determine which applications are to blame, but was hoping to find the row that was deadlocked so as to help me find out more information. [...] 10450 42 56_Bit of a BSDS oversight in upgrading a test subsystem !!10_Robert Ord21_robertord@HOTMAIL.COM31_Thu, 17 May 2001 18:51:21 -0000329_- Upgrading a TEST subsystem from 5.1 to 6.1 and the catmaint update fails. Need to recover catalog, directory, etc.

I have all the relevant image copies but the bsds backup from the archive log is over a day old.

Looking for suggestions which might prevent me getting alot of hassle from developers tomorrow. [...] 10493 79 12_Re: ims list16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Thu, 17 May 2001 19:50:08 +0100513_us-ascii Jimmy,

To sign onto the list, email to listproc@lists.missouri.edu with the following request:

subscribe IMS-L

HTH.

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK



* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER [...] 10573 126 27_Re: Column Naming Standards13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Thu, 17 May 2001 15:05:12 -0400 10700 116 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Thu, 17 May 2001 20:06:10 +0100635_us-ascii ----- Forwarded by Aurora E DellAnno/CAM/Candle on 05/17/2001 08:05 PM ----- |--------+-----------------------> | | "Gary Jacek" | | | | | | | | | 05/17/2001 | | | 06:58 PM | | | Please | | | respond to | | | IMS-L | | | | |--------+-----------------------> >----------------------------------------------------------------------------| | | | To: IMS-L@lists.missouri.edu | | cc: Roger.Ghose@MTO.GOV.ON.CA, (bcc: Aurora E | | DellAnno/CAM/Candle) | | Subject: Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY | >----------------------------------------------------------------------------| [...] 10817 64 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Thu, 17 May 2001 20:06:52 +0100619_us-ascii ----- Forwarded by Aurora E DellAnno/CAM/Candle on 05/17/2001 08:06 PM ----- |--------+-------------------------> | | bo774@freenet.c| | | arleton.ca | | | (Kelly Bert | | | Manning) | | | | | | 05/17/2001 | | | 07:49 PM | | | Please respond | | | to bo774 | | | | |--------+-------------------------> >----------------------------------------------------------------------------| | | | To: IMS-L@lists.missouri.edu | | cc: (bcc: Aurora E DellAnno/CAM/Candle) | | Subject: Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY | >----------------------------------------------------------------------------| [...] 10882 15 45_Re: Poster Of Catalog Tables UDB 6.1 (NT/AIX)18_Gert van der Kooij15_geko@WANADOO.NL31_Thu, 17 May 2001 21:20:53 +0200389_iso-8859-1 When you download the Quest Central for DB2 trial you can also order the poster for free. It's at http://www.quest.com/solutions/db2.asp

================================================ 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. 10898 19 50_Sysindexpart and Platinum RAIX_STATS_0206 question13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Thu, 17 May 2001 15:25:04 -0400 10918 18 47_Returning SQLCA to Powerbuilder from a Cobol SP13_Brian Charles20_bcharles@RITEAID.COM31_Thu, 17 May 2001 15:33:38 -0400343_iso-8859-1 We are running DB2 V6, Connect V7.1 and PB version 7.0.2. Is there a way to return the SQLCA to a client like PB without defining an output parm and manually populating it when an sqlerror error is found? I would have thought that Powerbuilder could do SQLCODE checking following the CALL like it does any other SQL statement. [...] 10937 145 54_Re: Sysindexpart and Platinum RAIX_STATS_0206 question16_Herman, Philippe22_PHILIPPE.HERMAN@CA.COM31_Thu, 17 May 2001 21:18:37 +0100435_iso-8859-1 Hi Carlton,

Basicaly you will find the same information in the LEAFDIST and the RAIX-AVGLEAF columns.

The only difference is that we will save this information in an historic way so that you can analyze not only the situation from the latest runstats but the situation from anycollections that you have executed. This is a valuable information for the Trending and Forecasting feature of the PDA reports. [...] 11083 153 20_Re: Commit Overhead?15_Jackson Reavill18_damcon2@US.IBM.COM31_Thu, 17 May 2001 16:59:24 -0400377_us-ascii Rick and Venkat,

Thanks for the responses, but what I'm looking for is to be able to say... on average a commit generally takes x amount of time to complete. Such as the commit of an insertion of one row or the commit of a deletion of one row. I can find numbers for other forms of SQL, such as insert, but not for commit. I know it's out there somewhere. [...] 11237 200 60_Re: Bit of a BSDS oversight in upgrading a test subsystem !!16_Herman, Philippe22_PHILIPPE.HERMAN@CA.COM31_Thu, 17 May 2001 22:02:44 +0100318_iso-8859-1 Hi Robert,

Gone thru this as well....

If you use DSN1COPY RESET, you will have to backup all your datasets changing the name (new HLQ for example) with whatever backup product you have and then use this copy as input of your DSN1COPY RESET job. Don't forget to cold start your DB2 after. [...] 11438 124 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY10_Rob Wright17_rwright@LIC.CO.NZ31_Fri, 18 May 2001 09:19:57 +1200549_us-ascii Note that "SET LOG SUSPEND" and "SET LOG RESUME" are available with the DB2 OS/390 V6 technical update. We use it successfully with Amdahl's TDMF for taking a hot backup of our production DB2 subsystem. We then FDR COPY the subsystem datasets with a rename, run a REXX program to fix the BSDS, start a 'new' DB2 subsystem, run one more REXX to fix the CATALOG and work file database, and hey presto, we have an exact copy of prod for the users to SAS and BRIO away to their hearts content without impacting the production environment. [...] 11563 25 40_Upgrade from DB2 V6.1 to DB2 v7.1 OS/39011_Robert Jans26_robert_jans@ALBERTSONS.COM31_Thu, 17 May 2001 15:04:29 -0700327_iso-8859-1 Hi all. I was sure this has been covered in the archives but I can't seem to find it. We're DB2 V6.1 and looking to upgrade to V7.1 in the fall time frame. Those people who have gone to V7.1, what gotcha's, foibles or odd little quirks have you encountered? We are well aware of the utilities issue, of course. [...] 11589 17 47_Updating Statstime in SYSINDEXES & SYSINDEXPART13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Thu, 17 May 2001 18:23:08 -0400 11607 90 16_Re: unit of work11_Roland Chua18_rolandchua@SGX.COM31_Fri, 18 May 2001 08:27:55 +0800429_us-ascii You must set on the DB2 accounting trace class(1,2). Look into the In-DB2 elapsed time. This is the time spend in DB2. DB2 total elapsed time (class 1)) - IN-DB2 elapsed time (class 2) will be your application time spend outside DB2.









Bill Brown cc: Sent by: DB2 Subject: Re: unit of work Data Base Discussion List [...] 11698 191 20_Re: Commit Overhead?18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Fri, 18 May 2001 10:46:07 +0930379_us-ascii Howzit Jay?

If you're interetsted in finding out why not just run batch spufi sql that does 100,000 commits and determine the average elapsed time from there. You'll need to do it at different times of the day to get a representative cross section.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia [...] 11890 80 82_Re: Delete Explicit Vr.s Delete through cascade RI performance ~ Empirical results12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Fri, 18 May 2001 11:43:13 +1000336_- In case any one out there has CF>0? I ran a small test, over 3 tables A, B & C such that each table had 2 indices and both B(partitioned TS) & C(single table segmented TS) were RI children to A(single table segmented TS).

I loaded the following RI intact data into :- * A=30,358 ROW(S) * B=91,031 ROW(S) * C=75,904 ROW(S) [...] 11971 43 51_Re: Updating Statstime in SYSINDEXES & SYSINDEXPART16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 18 May 2001 09:33:34 +0530644_- Its very difficult to determine whether SYSINDEXPART is updated or not when SYSINDEXES is updated by the same RUNSTAT. However, it is possible that the column is updated manually.

Regards Sanjeev

> -----Original Message----- > From: Carlton Enuda [SMTP:carltonenuda@HOTMAIL.COM] > Sent: Friday, May 18, 2001 3:53 AM > To: DB2-L@RYCI.COM > Subject: Updating Statstime in SYSINDEXES & SYSINDEXPART > > Hi Listers, > Can someone explain when the STATSTIME column of either of > SYSINDEXES or SYSINDEXPART are updated after runstats is taken. I ask this > question because I'm looking at the STATSTIME for a given INDEX and it [...] 12015 54 32_Re: interpreting DSN1PRNT output16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 18 May 2001 09:47:53 +0530587_- RID is PAGENO+OFFSET . So is not that offset ? You have to look the key/data associated with this in DSN1PRNT.

Regards Sanjeev

> -----Original Message----- > From: Derek Lund [SMTP:derek.lund@UTORONTO.CA] > Sent: Friday, May 18, 2001 12:10 AM > To: DB2-L@RYCI.COM > Subject: interpreting DSN1PRNT output > > I am seeing a DEADLOCK on a tablespace. The tablespace has row level > locking (one table in TS). There are only 15 rows on the table (a > control table). > I am having difficulty finding which row the two applications are > deadlocking on. > I am unable to [...] 12070 10 32_Re: interpreting DSN1PRNT output15_Devendra Mahato24_devend@LOT.TATASTEEL.COM31_Fri, 18 May 2001 10:57:31 +0530244_-

================================================ 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. 12081 201 53_Re: Bufferpool threshold Prefetch disabled no engine?10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Thu, 17 May 2001 14:43:05 -0500572_us-ascii Max, Thanks for your responses. You're the only brave soul that wanted to answer. I have included my original question at the bottom.

Meanwhile, I'll give a bit more information in response to your suggestions. There is 1 segmented tablespace with a segsize of 64 in this bufferpool. We are suffering from the dynamic SQL that is built on the client and the host variables are populated when the SQL gets to DB2, so dynamic SQL caching is not really a viable option. There is no chance for parallelism, so I left VPPSEQT at the default of 50%. I have [...] 12283 89 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY13_de Jonge, Jan20_Jan_De_Jonge@BMC.COM31_Fri, 18 May 2001 02:24:33 -0500435_iso-8859-1 We've developed a process that allows you to obtain a Coordinated Recovery between IMS and DB2 with NO OUTAGE!! The process requires the use of 3 BMC Software products (RECOVERY MANAGER for IMS, RECOVERY MANAGER for DB2, and RECOVERY PLUS for IMS). A 10 page whitepaper will be published to ship with the product tapes beginning in June, but the process could be executed today using existing releases of the products. [...] 12373 27 53_Re: Warning messages seleting from a 160 char column.13_Thomas Schulz20_thomas.schulz@SVI.DE31_Fri, 18 May 2001 02:15:00 -0500481_- Richard,

I think you have three eventualities

1. Use the real Spufi in the DB2I Panels, there is the parameter -MAX CHAR FIELD- 2. Create your own DSNTEP2, but you have to change the logic of the programm. It's just a sample program. 3. If you select a field larger then 120 Char use substr Funktion e.G SELECT SUBSTR(TRANSTAB, 1, 100) FROM SYSIBM.SYSSTRINGS WHERE INCCSID = 850 ; SELECT SUBSTR(TRANSTAB, 101, 200) FROM SYSIBM.SYSSTRINGS WHERE INCCSID = 850 ; [...] 12401 43 33_Re: European IDUG rquest for info11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Fri, 18 May 2001 04:35:42 -0400479_ISO-8859-1 Max

You probably know this already, but the only ways to get IDUG membership are:

1. Attend a conference. or 2. Purchase a set of conference proceedings.

Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk

Message text written by DB2 Data Base Discussion List >Hi esteemed List

I'm guessing if someone knows where I can find some infos about IDUG subscriptions & payments. I checked IDUG web [...] 12445 17 45_Re: Implementing chargeback on Resource usage11_Fred Nijdam20_f.nijdam@SYNTEGRA.NL31_Fri, 18 May 2001 03:53:03 -0500527_- Right you are, remote users running DRDA are accounted by DB2 accounting records with PLAN=DISTSERV. Just use MXG (I don't work for MXG but are a heavy user) or any other tool. Or just extract it yourself, the record layout is documented in sdsnsamp(dsnw..) somewhere, and it's possible to write a simple (but ..) assembler program to do the job. In the later case you can not only format the records out of SMF but online and star your IFI online monitor (better buy a tool, there are several vendors of db2- monitors) [...] 12463 49 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 18 May 2001 11:16:50 +0100648_us-ascii ----- Forwarded by Aurora E DellAnno/CAM/Candle on 05/18/2001 11:16 AM ----- |--------+----------------------------> | | "Cameron, Dave" | | | | | | | | | 05/18/2001 02:34 | | | AM | | | Please respond to | | | IMS-L | | | | |--------+----------------------------> >----------------------------------------------------------------------------| | | | To: "'IMS-L@lists.missouri.edu'" | | cc: (bcc: Aurora E DellAnno/CAM/Candle) | | Subject: RE: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY | >----------------------------------------------------------------------------| [...] 12513 96 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 18 May 2001 11:18:01 +0100601_us-ascii ----- Forwarded by Aurora E DellAnno/CAM/Candle on 05/18/2001 11:17 AM ----- |--------+-----------------------> | | "Robert Hain"| | | | | | | | | 05/18/2001 | | | 12:05 AM | | | Please | | | respond to | | | IMS-L | | | | |--------+-----------------------> >----------------------------------------------------------------------------| | | | To: IMS-L@lists.missouri.edu | | cc: (bcc: Aurora E DellAnno/CAM/Candle) | | Subject: Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY | >----------------------------------------------------------------------------| [...] 12610 46 42_Urgent! DB2 Connect Unix AIX versus OS/39015_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Fri, 18 May 2001 12:37:10 +0200472_iso-8859-1 Hello all,

In our shop we're doing a Proof of Concept using Informatica's Powercenter on UNIX (RS6000) and DB2 Databases as sources / targets(OS/390). We have a response of 200 KB per second. We have a dedicated line using 100Mbit Ethernet. So we should be able to get a response of 6 to 8 Mb per second. We're using DB2 Connect EE 7.1 and DB2 UDB for OS/390 v 6.1. Please help us out getting a better response. What could be the problem? Solution? [...] 12657 119 50_AW: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 18 May 2001 12:38:43 +0200543_iso-8859-1 Aurora,

the DB2 for OS/390 Quiesce Utility is used to record a point of consistency for a tablespace, a list of tablespaces. This utility ensures that all tablespaces in the scope of the Quiesce are referentially okay. Quiesce handles this by externalizing all data modifications to DASD and recording log RBA's or LRSN's in SYSIBM.SYSCOPY, thereby indicating a consistency point for a(hopefully never neccessary ;_)) RECOVER in the future. In this manner Quiesce provides a probability of a successful RECOVER or COPY. [...] 12777 52 53_Re: Bufferpool threshold Prefetch disabled no engine?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 18 May 2001 15:57:46 +0530511_- Hi Missy,

I have gone thru' your both the mail. I have also seen the mail from Max. I have come out with some conclusions on the same, kindly let me know your views on the same.

You must be aware of the definition of both the prefetch failed :-

i) PREFETCH FAILED - NO BUFFER --> Sequential prefetch is inhibited.This shows how many times sequential prefetch is disabled because the sequential prefetch threshold (90%of the pages in the buffer pool are unavailable)has been reached. [...] 12830 22 12_set nodigest15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Fri, 18 May 2001 12:44:00 +0200634_iso-8859-1 ================================================ De informatie opgenomen in dit bericht kan vertrouwelijk zijn en is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en de afzender direct te informeren door het bericht te retourneren. ================================================ The information contained in this message may be confidential and is intended to be exclusively for the addressee. Should you receive this message unintentionally, please do not use the contents herein and notify the sender immediately by return e-mail. [...] 12853 33 14_Re: HiperPools12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 18 May 2001 13:56:12 +0200417_iso-8859-1 Hi, Just pay attention to the amount of HP available on the machine, if you have 3GB memory (2GB stor and 1GB estor) don't allocate 8GB HP ;-)

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "S, Sanjeev (CTS)" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, May 17, 2001 4:10 PM Subject: Re: HiperPools [...] 12887 16 42_Re: European IDUG rquest for info - Thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 18 May 2001 13:37:50 +0200344_us-ascii Thanks to all who replied to my request for infos !!

Max Scarpa

DB2 sysprog (?)

================================================ 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. 12904 105 60_Re: Bit of a BSDS oversight in upgrading a test subsystem !!0_27_jim.leask@RS-COMPONENTS.COM31_Fri, 18 May 2001 12:41:00 +0100587_iso-8859-1 I had to fallback a v6 DB2 system back to v4 without losing any user data. However, I had the luxury of printing off the current BSDS values.

We then recovered our catalog, directory and bsds from DFDSS back ups. Then amended the BSDS to perform a conditional restart, the RBA we chose was the our highrba rounded up e.g. //DSNTLOG EXEC PGM=DSNJU003 //SYSUT1 DD DISP=OLD,DSN=DB2D.BSDS01 //SYSUT2 DD DISP=OLD,DSN=DB2D.BSDS02 //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSIN DD * CRESTART CREATE,STARTRBA=0058B63B1000,ENDRBA=0058B63B1000, FORWARD=NO,BACKOUT=NO [...] 13010 82 28_Re: DB2 V6 maintenance level11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 18 May 2001 08:31:05 -0400517_iso-8859-1 As far as put level that depends on the depth of features you need. We are conservative here so we lag about 3-6 months behind current on the put level and use current hold data to be sure that no PTF's that have gone PE are applied. Note that we usually receive latest ESO maint every 2 months or so, but use SMP sourceids on apply to push out the basic level we desire. Note that we also apply HIPER and PRP in addition to PUT level, so those will be more current when we first roll out. We usually [...] 13093 234 53_Re: Bufferpool threshold Prefetch disabled no engine?14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US31_Fri, 18 May 2001 09:02:29 -0400330_US-ASCII Missy, given the single object in the pool ... some tuning aspects won't help, obviously ... but if the NO ENGINE LP failures are the biggest concern then you've got to take major surgery to either limit concurrent users (CTHREAD) and / or knock down the VPPSEQT, use PARADEG or some such to limit parallel streams. [...] 13328 85 32_Re: interpreting DSN1PRNT output11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Fri, 18 May 2001 09:15:13 -0400479_ISO-8859-1 Sanjeev

A minor correction. RID is page number + ID-Map entry number. ID-Map entry in data page contains actual offset to row in page.

Derek

Your interpretation is correct. X'000002' is the page number and corresponds to DSN1PRNT PGNUM. X'01' is ID-Map entry number and PGSBID is the pointer back to the ID-Map entry from the record header and therefore identifies the row you are looking for, the contents of which are displayed just below. [...] 13414 92 46_Re: Urgent! DB2 Connect Unix AIX versus OS/39012_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 18 May 2001 06:17:04 -0700614_us-ascii When I ran into this Informatica issue we were going from NT to AIX. The 3 major problems seemed to be:

1.An inordinate number of db2 indexes used by informatica at the target system which took a heck of a long time to update. Dropping them before a large data transfer job, and then recreating them afterwards gave throughput improvements. 2.There were significant problems with the SQL written to perform the source to target "mappings", as written by some "experts". A little tuning of the code provided more improvements. 3.It really used ODBC instead of DRDA connections. They were going [...] 13507 76 8_CLI0615E34_=?iso-8859-1?Q?Mat=EDas_Salvador?=26_matias_salvador@UOL.COM.AR31_Fri, 18 May 2001 10:19:00 -0300344_iso-8859-1 After installing FP3 7.1 for Solaris, when trying to connect to the DATABASE through JDBC, the CLI0615E Error appears.

Any idea what am i missing? I run the db2jstrt 6790, and before the patches, it worked fine. I updated all the instances after the installation and I can connect to the database from my Windows Client. [...] 13584 21 31_Oracle to DB2 UDB 7.1 Migration13_Mathew, Sithu30_sithu.mathew@BLR.HPSGLOBAL.COM31_Fri, 18 May 2001 19:13:22 +0500325_- Hi,

Currently we are doing a migration from Oracle 8.1.6 to DB2 UDB 7.1. We have used Man-tech migration tool provided by IBM. But the tool does not migrate Triggers. Can anyone let us know if there any other tool / Utility available to migrate Triggers from Oracle to DB2?.



Regards Sithu Mathew [...] 13606 211 20_DB2 Tip of the Month14_Campbell White20_cwhite@REVEALNET.COM31_Fri, 18 May 2001 10:29:47 -0400745_us-ascii Subscribe to the Pipeline Newsletter for monthly DB2 technical articles, tips and discussions about DB2 and Oracle technology: http://www.revealnet.com/newsletter-v2/newsletter_0501.htm ----------------------------------------------------------

DB2 TIP OF THE MONTH - MAY, 2001

How to Determine Data that is Losing Clustering

If less than 80 percent of the rows in a table are no longer in sequence according to the clustering index, it is advisable to consider reorganizing the table space. For data accessed sequentially, cluster ratio has a huge impact on performance; for randomly accessed data, cluster ratio is not as important. The CLUSTERRATIO in SYSINDEXES contains the percentage of rows that are in [...] 13818 47 63_DB2 V6 Stored Procedures, Schema names, SQL and Java Procedures0_19_Tim.Lowe@STPAUL.COM31_Fri, 18 May 2001 09:38:14 -0500467_us-ascii Dear List, We recently migrated to DB2 UDB V6 for OS/390, and have some questions about how people are dealing with DB2 Stored Procedures issues. Currently, our DBA group is creating all stored procedures in all of our test and production environments. But, as we move forward, we can see this moving to applications programmers with the use of the DB2 Stored Procedure Builder. Therefore, I have the following questions which I hope you can help with: [...] 13866 30 14_Reuse of Space16_Michael McKinney16_mmckinne@CSC.COM31_Fri, 18 May 2001 10:58:06 -0400433_us-ascii All,

I have a table whose primary key will a CHAR(17) audit number. This key will be clustered. The makeup of this key field ensures that records will always be inserted into this table sequentially. In addition, this table will only hold a rolling 90 days of data, with data older then 90 days being removed. So records are always being inserted on the front end and data always being purged from the back end. [...] 13897 16 33_Removing duplicates from an index13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Fri, 18 May 2001 11:13:02 -0400 13914 53 18_Re: Reuse of Space34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Fri, 18 May 2001 11:18:44 -0400635_us-ascii What kind of tablespace do you have? Partitioned , Segmented or simple ? How many tables per tablespace if not partitioned?

-Venkat Pillay Sun Certified Java 2 Programmer IBM Certified Solutions Expert - V7 family application development - DB2 UDB Database Administration for OS/390

also --- NJ DMV certified car driver

> -----Original Message----- > From: Michael McKinney [SMTP:mmckinne@CSC.COM] > Sent: Friday, May 18, 2001 10:58 AM > To: DB2-L@RYCI.COM > Subject: Reuse of Space > > All, > > I have a table whose primary key will a CHAR(17) audit number. This key > will be clustered. The makeup [...] 13968 81 37_Re: Removing duplicates from an index16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Fri, 18 May 2001 11:50:53 -0400553_us-ascii





Carlton,

The DB2 load utility (and CA/Platinum's compatible load) will automatically discard all rows which fail to adhere to unique index constraints. You simply have to specify a DISCARD dataset to save all discarded rows.

So, you would:

1. Unload the data 2. Mass delete the table 3. Drop/Create the index as unique 4. Load the table specifying proper discard options 5. Uniquefy discarded rows 6. Load uniquefied rows back into table 7. Runstat tablespace/index 8. Rebind dependent objects [...] 14050 174 51_Re: Updating Statstime in SYSINDEXES & SYSINDEXPART10_Shukla, JR18_JR.Shukla@BNSF.COM31_Fri, 18 May 2001 10:51:10 -0500594_iso-8859-1 It depends on the UPDATE option used. The SYSINDEXPART will not be updated if the RUNSTATS was run with UPDATE ACCESSPATH.

JR Shukla jr.shukla@bnsf.com 817-333-7537

-----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Sent: Thursday, May 17, 2001 11:04 PM To: DB2-L@RYCI.COM Subject: Re: Updating Statstime in SYSINDEXES & SYSINDEXPART



Its very difficult to determine whether SYSINDEXPART is updated or not when SYSINDEXES is updated by the same RUNSTAT. However, it is possible that the column is updated manually. [...] 14225 58 37_Re: Removing duplicates from an index12_Gregg Sawyer26_gregg.a.sawyer@VERIZON.COM31_Fri, 18 May 2001 12:25:27 -0400429_us-ascii I haven't done this since DB2 v4, but for volume data it used to be a lot more efficient to use a batch sort to remove dups rather than letting DB2 LOAD do it. Please shout if this is "old tech."...

Gregg Sawyer









"RICHARD E MOLERA" @RYCI.COM> on 05/18/2001 11:50:53 AM

Please respond to "DB2 Data Base Discussion List" [...] 14284 198 14_FW: Reorg stmt16_Brookman, Gerald30_gerald.brookman@ATOSORIGIN.COM31_Fri, 18 May 2001 13:05:21 -04001238_windows-1252 Use this instead: (finalized after enormous amounts of grief!)

REORG TABLESPACE PDB00001.TSPACE01 COPYDDN(DB01TS01) RECOVERYDDN(DR01TS01) SHRLEVEL CHANGE MAPPINGTABLE PDB2.MAPPING_TABLE TIMEOUT TERM DRAIN ALL OFFPOSLIMIT INDREFLIMIT STATISTICS TABLE INDEX

sample for indexes:

REORG INDEX PDB2.IMASTCP1 SHRLEVEL CHANGE TIMEOUT TERM DRAIN ALL LEAFDISTLIMIT STATISTICS

Be sure to add DD statements for parallel index build:

//* //DATAWK01 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //DATAWK02 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //DATAWK03 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //DATAWK04 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //* //SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(1100),RLSE) //* //SW01WK01 DD UNIT=SYSDA,SPACE=(CYL,(550,110),RLSE) //SW01WK02 DD UNIT=SYSDA,SPACE=(CYL,(550,110),RLSE) //SW02WK01 DD UNIT=SYSDA,SPACE=(CYL,(550,110),RLSE) //SW02WK02 DD UNIT=SYSDA,SPACE=(CYL,(550,110),RLSE) //SW03WK01 DD UNIT=SYSDA,SPACE=(CYL,(550,110),RLSE) //SW03WK02 DD UNIT=SYSDA,SPACE=(CYL,(550,110),RLSE) //* //SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(275,55)) [...] 14483 39 16_Re: set nodigest10_Klaas Punt14_punt@XS4ALL.NL31_Fri, 18 May 2001 13:16:22 MET-2752_iso-8859-1 Jaap,

hierbij jouw bericht reour! Klaas.



Op 18-mei-01 schreef Slot, JP (Jaap):

>================================================ >De informatie opgenomen in dit bericht kan vertrouwelijk zijn en >is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht >onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en >de afzender direct te informeren door het bericht te retourneren. >================================================ >The information contained in this message may be confidential >and is intended to be exclusively for the addressee. Should you >receive this message unintentionally, please do not use the contents >herein and notify the sender immediately by return e-mail. [...] 14523 69 14_Re: Reorg stmt34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Fri, 18 May 2001 13:35:37 -0400725_us-ascii Just another additional info along with the previous email -

You could also use "SORTDEVT SYSDA SORTNUM 4" for dynamic sort allocation instead of coding all the DD for parallel index build-up in the JCL.

Regards, -Venkat Pillay



> -----Original Message----- > From: Brookman, Gerald [SMTP:gerald.brookman@ATOSORIGIN.COM] > Sent: Friday, May 18, 2001 1:05 PM > To: DB2-L@RYCI.COM > Subject: FW: Reorg stmt > > Use this instead: (finalized after enormous amounts of grief!) > > REORG TABLESPACE PDB00001.TSPACE01 > COPYDDN(DB01TS01) RECOVERYDDN(DR01TS01) > SHRLEVEL CHANGE MAPPINGTABLE PDB2.MAPPING_TABLE > TIMEOUT TERM DRAIN ALL > OFFPOSLIMIT INDREFLIMIT > STATISTICS TABLE INDEX > > [...] 14593 70 47_Re: DSN3@SGN setup for use of secondary authids9_Oliver Su14_ojsu@EMAIL.COM31_Fri, 18 May 2001 14:28:54 -0400523_- Linda, or Bill -

Would you mind sharing with us the sample codes of DSN3@ATH and DSN3@SGN? We are also modifying the exit routines to have RACF be an active part of DB2 security process. It is not very clear on what needs to be altered.

Appreciated.

Oliver Su OJ Consulting





------Original Message------ From: "Billings, Linda" To: DB2-L@RYCI.COM Sent: April 27, 2001 2:22:01 PM GMT Subject: Re: DSN3@SGN setup for use of secondary authids [...] 14664 124 50_Re: OSAM DB getting B37 with space still available16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 18 May 2001 19:50:43 +0100407_us-ascii sorry guys couldn't resist....

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK



* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER [...] 14789 40 30_Timestamp and table clustering22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Fri, 18 May 2001 16:14:52 -0300589_iso-8859-1 Hi list,

We are DB2 OS/390 V5.1. We had been facing problems related to an overactive area on a table which has a sequential number as primary key. As previously suggested by list members, we changed the free space parameter and since then it seems that concurrency has improved on that table. However, as a further improvement, we would like to use a timestamp as primary key, instead of the sequential number. Also, we would like to define a unique index composed of 5 columns as the cluster index, in substitution to the the primary key (the timestamp column) as [...] 14830 39 46_Database Design use of Codes/Reference Tables.13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 18 May 2001 14:22:32 -0500371_iso-8859-1 Hi,

I need to touch base with the outside world and get some feed back on the use of storing codes, instead of actual values. The values are decoded by accessing a code table. This is a pretty common procedure.

My question is; what guidelines do people use when determining when a code should be used as opposed to storing the actual value? [...] 14870 33 8_SYSLGRNX7_db user20_dbuser75@HOTMAIL.COM31_Fri, 18 May 2001 20:40:41 -0000438_- Hi !

I ran REPORT recovery on a tablespace to find out whether that tablespace was ever updated. I got the following results from SYSLGRNX:

UCDATE UCTIME START RBA STOP RBA 050501 21092217 0852143BEA70 0852143BEA70 050501 21092590 0852145235E9 0852145235E9

The start RBA and the stop RBA are exactly the same. I was wondering why it is so and what should I conclude from this? Was the tablespace ever updated? [...] 14904 230 14_Re: Reorg stmt16_Brookman, Gerald30_gerald.brookman@ATOSORIGIN.COM31_Fri, 18 May 2001 16:57:03 -0400494_windows-1252 Beware! The default dynamic sortwork DASD space allocations are usually set by the MVS Systems Programmers to be microscopically small. You can't sort 75 million application and mapping table index entries using 5 cylinders. If you have a biggie table, always hardcode the space allocations!

-----Original Message----- From: Pillay, Venkat (USPC.PCT.Hopewell) [mailto:venkat_pillay@ML.COM] Sent: Friday, May 18, 2001 12:36 PM To: DB2-L@RYCI.COM Subject: Re: Reorg stmt [...] 15135 156 24_FW: Error during Runstat12_Leslie Lewis20_icsles@BELLSOUTH.NET31_Fri, 18 May 2001 16:55:01 -0400584_iso-8859-1 We had a similar problem. Turned out we needed to apply additional maintenance to DB2. The problem originally presented itself when we applied PDO 0104 maintenance.

I don't have the APAR number handy, but the PTF for Version 6 is UQ52319. The PTF was not a direct hit on IBMLINK, but when IBM reviewed the dump this was the recommended fix. We received several variations of the error message (00E40210, 00E40206, etc) depending on the format of the RUNSTATS utility statement. After applying the PTF we have not received any further occurrences of this abend. [...] 15292 19 24_RTI Information - URGENT15_Kannan, Perumal22_perumal.kannan@EDS.COM31_Fri, 18 May 2001 10:30:41 -0500283_- Hello List, I need to know more about RTI (Run Time Improvement). I am with DB2 V5 plus running under S/390. What are all the areas we need to look in and we can improve? Please do throw your comments ASAP. Thank you very much indeed. (Even websites where I can look in) TIA. [...] 15312 133 28_Re: RTI Information - URGENT14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 18 May 2001 15:08:10 -0700663_iso-8859-1 Perumal, Check out www.software.ibm.com/data/db2 and look for white papers and manuals. Read the SQL Application Programming Guide, and the Administration Guide, for starters. Get some books on DB2, some good ones are by Yevich, Mullins, Wiorkowski and are available at most online book sellers. There are many Vendor tools out there to assist you in identifying and resolving performance problems. I would concentrate on analyzing your bufferpools and doing explains on your troublesome SQL. This will be where you get the biggest bang for your tuning efforts. After that, check everything, like DB2 STC dispatching priorities, find out how much [...] 15446 33 28_Re: RTI Information - URGENT12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 18 May 2001 17:14:00 -0400581_- You should start by finding what's wrong. What is taking the longest? View PM or other monitor's displays/reports and find if your problem is I/O, processor, buffer pool too small etc. There is a lot you can do to improve runtime. But, you have to find the problem before you can correct it. Once you zoom in on the problem, this list is an excellent resource. Information is in the Admin Guide, redbooks, etc. Books that I have are "DB2 Answers", "DB2 Developer's Guide" and "DB2 for OS390, Development for Performance". There is a lot of information available. Good Luck. [...] 15480 64 28_Re: RTI Information - URGENT0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 18 May 2001 17:20:16 -0500724_us-ascii Scan thru the list archive. Variations on this question has been asked many times, including just a few weeks ago.









BOB JEANDRON @RYCI.COM> on 2001/05/18 04:14:00 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: RTI Information - URGENT



You should start by finding what's wrong. What is taking the longest? View PM or other monitor's displays/reports and find if your problem is I/O, processor, buffer pool too small etc. There is a lot you can do to improve runtime. But, you have to find the [...] 15545 119 14_Re: Reorg stmt13_Olson, Carlos14_COlson@QRS.COM31_Fri, 18 May 2001 16:33:14 -0700619_windows-1252 I don't know if online reorg is different, but I ran the following REORG the other day without hardcoding SORTWORK datasets:

REORG TABLESPACE CCDBITMI.ITMUPCTS LOG NO SORTDATA NOSYSREC SORTKEYS SORTDEVT SYSDA SORTNUM 16 STATISTICS TABLE INDEX KEYCARD

The following Sort Control statements were in the UTPRINT output:

--- CONTROL STATEMENTS/MESSAGES ---- 5740-SM1 REL 13.0 ---- 12.49.31 M SORT FIELDS=(00006.0,00000.4,A,00008.0,00047.0,A,00001.0,00005.0,A),FO MAT=BI,FILSZ=U000000164640132,DYNALLOC=(SYSDA,16) RECORD TYPE=F,LENGTH=(0054,0054,0054) OPTION MSGPRT=ALL,MSGDDN=UTPRINT [...] 15665 158 18_Performance tuning23_Vivekananthan Ettiappan18_vivdb2@HOTMAIL.COM31_Sat, 19 May 2001 01:24:27 +0100654_- All, Following is the statistics from OMEGAMON for a program which started taking more time in last two months.



________________ ZTDTL VTM O2 V400./C DB2P 05/18/01 20:48:07 18 + DB2 Status = IN-SQL-CALL MVS Status = WAIT-MISC + Total Elapsed Time = 00:27:23.601 CPU Utilization = 02.3% + Total CPU Time = 00:03:59.765 Total Parallel Tasks = 0 + Total Stored Proc CPU = 00:00:00.000 Current Parallel Tasks = 0 + + In-DB2 Times Total Current + ------------------------------ ------------ ------------ + Elapsed Time 00:27:23.601 00:27:23.601 + CPU Time 00:03:59.765 00:03:59.765 + Stored Procedure CPU Time 00:00:00.000 N/A + + Waits [...] 15824 113 37_Re: Removing duplicates from an index13_Olson, Carlos14_COlson@QRS.COM31_Fri, 18 May 2001 17:35:19 -0700404_iso-8859-1 With your Platinum suite of products, you should be able to do a FAST UNLOAD of your data, then do a DROP and CREATE of your index with DEFER YES, followed by a FAST LOAD RESUME NO REPLACE with OUTPUT-CONTROL ALL. I would of course test this before doing it in production. Carlos Olson Database Administrator QRS Corporation email: colson@qrs.com http://www.qrs.com [...] 15938 173 22_Re: Performance tuning18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Sat, 19 May 2001 15:07:01 +0930368_us-ascii Howzit Vivekananthan?

What is the relationship between the to SQL statements ie what columns from the 1st statement are used in the predicate of the 2nd? What else besides PRODUCTION_DT?

Assuming you are getting the sum for the distinct result of the 1st statement the 1st thing I'd do is to join the 2 statements. Try something like this: [...] 16112 69 37_Re: Removing duplicates from an index18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Sat, 19 May 2001 15:09:38 +0930508_iso-8859-1 Howzit Carlton?

Use SORT SUM on the unloaded rows to eliminate duplicates. Make sure all binary columns are in external format.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









Carlton Enuda @RYCI.COM> on 19/05/2001 00:43:02

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 16182 16 8_DSNYASCP8_Rush Yan14_db2-l@21CN.COM31_Sat, 19 May 2001 15:17:00 +0800500_GB2312 Just curious: address space xxxxMSTR, xxxxDIST, xxxxDBM1 are all started by DSNYASCP, how DSNYASCP knows which space it should start? I mean does DSNYASCP retrieve the procedure name to analyse, then make the decision?

Thank you

Rush Yan

================================================ 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. 16199 213 22_Re: Performance tuning18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Sat, 19 May 2001 13:07:40 +0500637_iso-8859-1 Vivekananthan You might want to see on what specific objects is wait happening?. Which tablespaces/which index spaces?. What is the cost of each sync/async I/O?. HAs the unit cost increased compared to what it was before when the query performance was acceptable. When was the last time tablespaces and indexes were reorged (if the tables and indexes being accessed have insert/update/delete activity on them)?. I would be interested in analysing all probable factors which are causing so much async I/O wait?. Because so much time is being spent in I/O see if enabling parallelism (bind with degree(ANY))would be of any [...] 16413 79 50_Re: Database Design use of Codes/Reference Tables.12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 19 May 2001 13:13:03 +0200344_iso-8859-1 Hi,

Three main reasons: 1. If you need to change the decoded value it is easier to do it once on a code table and not "N" times in the big "fact" table. 2. If you save storage space - eg: code is 2 bytes , decode is 20 bytes (multiply that with "N" rows and you get the amount of storage you may save). 3. Mgmt decision. [...] 16493 18 34_parameter explanation of db2 macro8_Rush Yan14_db2-l@21CN.COM31_Sat, 19 May 2001 18:23:11 +0800417_GB2312 Hello,

Which book has the each parameter explanation of DB2 macro: DSN6ARVP,DSN6ENV,DSN6FAC,DSN6LOGP,DSN6SPRM,DSN6SYSP,DSN6GRP ?

Thank you

Rush Yan

================================================ 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. 16512 75 37_Re: Removing duplicates from an index13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Sat, 19 May 2001 08:12:05 -0400 16588 178 36_Re: RID failure - EXCEEDED RDS limit17_Miguel de Andrade18_andrade@DE.IBM.COM31_Sat, 19 May 2001 07:11:44 -0500555_- As it is known mostly of the things that go around problem determination in DB2 depend from environment to environment, I believe this can be called the 'TIMERON factor', probably... Anyway, I will try to answer with my limited knowledge.

What is a RID? For a table space defined as LARGE or with the DSSIZE a RID is a 5-byte field, containing a 4-byte page number followed by a 1-byte page ID map entry number. For any other table space type, a RID is a 4-byte field, con-taining a 3-byte page number followed by a 1-byte page ID map entry [...] 16767 18 38_DSN610.NEW.SDSNSAMP vs DSN610.SDSNSAMP8_Rush Yan14_db2-l@21CN.COM31_Sat, 19 May 2001 20:25:47 +0800524_GB2312 Hi,

I find both pds has job DSNTIJUZ which generates the system parm DSNZPARM, and there are other same jobs in both pds. My question is: do I always use the DSN610.NEW.SDSNSAMP first? or it's just an interim pds, used in installation?

Thank you

Rush Yan

================================================ 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. 16786 19 19_untility sample job8_Rush Yan14_db2-l@21CN.COM31_Sat, 19 May 2001 20:32:00 +0800426_GB2312 Hi,

I know I can find the sample control statements in <>, just wondering if these job samples hide in PDS ?

Thank you

Rush Yan

================================================ 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. 16806 111 39_Re: DB2 PM online monitor abend in ISPF8_Rush Yan14_db2-l@21CN.COM31_Sat, 19 May 2001 21:28:22 +0800782_GB2312 Kals,

I thought nobody answer my question until I searched the archive today.

We execute PM under ISPF, after going thru several panel, &ZSEL = TRANS( TRUNC (&ZCMD,'.') 1,'CMD(%DB2PM)'

the clist looks like: PROC 0 /*********************************************************************/ /* CLIST TO ALLOCATE DB2PM DATASETS /*********************************************************************/ CONTROL NOFLUSH NOMSG NOLIST NOCONLIST SET &DB2PM = DB2PM.V5R1M0

ISPEXEC CONTROL ERRORS RETURN ALTLIB ACTIVATE APPLICATION(CLIST) DATASET('&DB2PM..SDGOEXEC') /*********************************************************************/ ISPEXEC SELECT CMD(%DGOJINIT) NEWAPPL PASSLIB /*********************************************************************/ [...] 16918 38 45_RE : TRYING TO MIGRATE FROM V4.1 TO V5.1 !!!!12_Andre Cupido22_wcacu@WOOLWORTHS.CO.ZA31_Sat, 19 May 2001 18:29:42 +0200340_US-ASCII Hi

I am trying to upgrade out test DB2 v4.1 to v5.1 . The DB2 master crashes with a s04f and the DSNTIJTC job doing the CATMAINT UPDATE crashes with the following . I am told that if i need to apply fixes the smpe environment will have to recreated , the systems guys are not to keen on this . I have to migrate ASAP . [...] 16957 202 45_Re: Implementing chargeback on Resource usage11_Scott Barry19_sbarry@SBBWORKS.COM31_Sat, 19 May 2001 13:00:35 -0400442_iso-8859-1 You speak of chargeback and limit your focus to DB2 only. How curious, given that enterprises typically implement such a project across a larger slice of their I/T processing, for example, mainframe chargeback or enterprise-wide chargeback, even if only with the intention of supplying a resource usage feed to another business system such as General/Ledger, or possibly to generate an internal memo-only bill for management. [...] 17160 44 49_Re: RE : TRYING TO MIGRATE FROM V4.1 TO V5.1 !!!!12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Sat, 19 May 2001 12:27:00 -0400482_- Check console for messages. When I moved from 4 to 5, I had to increase the DSNDB07 sort DSNs.

>>> DB2-L@RYCI.COM@inter2 05/19/01 11:30AM >>> Hi

I am trying to upgrade out test DB2 v4.1 to v5.1 . The DB2 master crashes with a s04f and the DSNTIJTC job doing the CATMAINT UPDATE crashes with the following . I am told that if i need to apply fixes the smpe environment will have to recreated , the systems guys are not to keen on this . I have to migrate ASAP . [...] 17205 84 18_Use of Code Tables14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Sat, 19 May 2001 12:20:21 -0700550_- Gelnn, The use of codes tables is fairly common. As Issac has pointed out some uses. Most codes tables I worked with had a description associated with the code. If you stored the code and description in base tables you would have redundant data all over the place and have interesting maintenance challenges. On the other hand, you don't want 10 table joins all the time not withstanding the overhead but the complexity of the SQL required to be coded. Reading the codes into a table in memory would be the way to go in that case. Seems to me [...] 17290 73 49_Re: RE : TRYING TO MIGRATE FROM V4.1 TO V5.1 !!!!16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Sat, 19 May 2001 23:07:27 +0100414_us-ascii Andre,

One of the things to be careful about, is the DSNDB07 datasets used for SORT - they will have to be increased.

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK [...] 17364 184 22_Re: Performance tuning13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sat, 19 May 2001 18:38:45 -0500387_us-ascii Vivek,

Generally I'd say combine the two queries, but that will depend whether this is a batch or online query. Combining will require a GROUP BY sort, which may involve significantly more sort work space than the DISTINCT sort of the cursor. However, not combining the query will result in the additional overhead of an open/fetch/close cursor for each cursor row. [...] 17549 167 22_Re: Use of Code Tables13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sat, 19 May 2001 18:47:21 -0500345_iso-8859-1 Use of Code TablesPhil,

Isn't that what those data warehouser's call an oversized star schema?

You don't want to be reading the code tables into memory if they actually have filtering predicates applied to these. They are best left in the SQL. The SQL shouldn't be overly complex, just a little bigger than normal. [...] 17717 47 12_Re: DSNYASCP11_Bikash Paul19_bikash_db@YAHOO.COM31_Sat, 19 May 2001 17:49:46 -0700375_us-ascii Hi, DB2 subsystem name is defined in PARMLIB dataset member IEFSSNxx. So using the operator's command to bring up DB2, the system will locate xxxxMSTR and xxxxDBM1 (where xxxx is the subsystem name) in the cataloged procedure libraries and job will be started as started task.

xxxxDIST and xxxxSPAS will be started based on definition, made in DSNZPARM. [...] 17765 37 38_Re: parameter explanation of db2 macro11_Bikash Paul19_bikash_db@YAHOO.COM31_Sat, 19 May 2001 17:50:48 -0700556_us-ascii Hi, Look into the DB2 installation guide. It talks about all parameters.

Regards, Bikash --- Rush Yan wrote: > Hello, > > Which book has the each parameter explanation of DB2 macro: > DSN6ARVP,DSN6ENV,DSN6FAC,DSN6LOGP,DSN6SPRM,DSN6SYSP,DSN6GRP ? > > Thank you > > Rush Yan > > ================================================ > 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. [...] 17803 42 42_Re: DSN610.NEW.SDSNSAMP vs DSN610.SDSNSAMP11_Bikash Paul19_bikash_db@YAHOO.COM31_Sat, 19 May 2001 18:02:02 -0700637_us-ascii Hi rush, DSN610.NEW.SDSNSAMP is created and members in the dataset are generated during tailoring phase of installation. You should look into that. Whereas DSN610.SDSNSAMP is created as part of SMP process (Receive/apply/accept).

Regards, Bikash --- Rush Yan wrote: > Hi, > > I find both pds has job DSNTIJUZ which generates the system parm > DSNZPARM, and there are other same jobs in both pds. My question > is: do I always use the DSN610.NEW.SDSNSAMP first? or it's just an > interim pds, used in installation? > > Thank you > > Rush Yan > > ================================================ > To [...] 17846 15 12_Re: DSNYASCP26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Sun, 20 May 2001 00:06:19 -0500342_- It's all the the DSNZPARM. Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ 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. 17862 15 49_Martha Kijak/HRD/Prudential is out of the office.12_Martha Kijak27_martha.kijak@PRUDENTIAL.COM31_Sun, 20 May 2001 01:00:56 -0400389_us-ascii I will be out of the office starting 05/19/2001 and will not return until 05/29/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. 17878 44 12_Re: DSNYASCP8_Rush Yan14_db2-l@21CN.COM31_Sun, 20 May 2001 15:53:50 +0800466_GB2312 I rephrase my question:

I cut the procedures here,all 3 execute DSNYASCP, what's the indicator(decisive factor) that drives DSNYASCP to start this address space as DSN1DBM1,DSN1DIST,DSN1MSTR respectly?

I know DSNZPARM( the default) determines which procedures to be searched and started, but what will happen If I change DSN1DBM1 to ABCD1111 in the procedure first line,can procedure DSN1DBM1 start the address space DSN1DBM1 successfully? [...] 17923 145 22_Re: Use of Code Tables13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Sun, 20 May 2001 12:09:06 -0500520_iso-8859-1 Thanks for the comments.

As Phil said .. "Seems to me that if you are doing 10 table joins to get the meaning of a code, something else is wrong" - I guess that is what I am trying to work out - what is wrong. So far the codes seem reasonable as codes in the tables. As per Issac's points I think I can avoid the use of codes if I follow it as a recipe, but program-coding with codes and indexes on codes are better than against long descriptions. So, perhaps codes and descriptions is an option. [...] 18069 32 49_Re: RE : TRYING TO MIGRATE FROM V4.1 TO V5.1 !!!!12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 14:03:56 -0500477_- There is no way to migrate without CATMAINT, and very old service levels can be the problem. There are generally other messages before the abend with reason code 00E40601 that are more specific and look in the dump to see the problem. If you are just migrating to V5, then the V5 service level should be fairly current. I include a couple of notes below. I've forgotten most of the situations, so you should check in the Info APARs II10128 and its continuation II12123. [...] 18102 66 49_Re: RE : TRYING TO MIGRATE FROM V4.1 TO V5.1 !!!!12_Andre Cupido22_wcacu@WOOLWORTHS.CO.ZA31_Sun, 20 May 2001 21:38:10 +0200465_US-ASCII Hi

The problem is i cannot find any messages in the log pointing back to spaces problems .

Thanks



______________________________ Forward Header __________________________________ Subject: Re: RE : TRYING TO MIGRATE FROM V4.1 TO V5.1 !!!! Author: BOB.JEANDRON (BOB.JEANDRON@USDA.GOV) at SH Date: 5/19/101 6:27 PM



Check console for messages. When I moved from 4 to 5, I had to increase the DSNDB07 sort DSNs. [...] 18169 23 20_Re: Commit Overhead?12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 14:31:28 -0500517_- The variance for this type of rule of thumb exceeds the mean. There are generally four log writes for both log 1 and log 2 times phase 1 and phase 2 of commit. There are situations where log 1 and log 2 can be done in parallel, others that are serial. There are some situations where a single phase can be used. There are disks that respond ten times faster than others. The log writes can be done in a convoy, so that a single set of writes are done for ten transactions, reducing the overhead by a factor of [...] 18193 18 28_Re: DB2 V6 maintenance level12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 14:38:22 -0500470_- I would agree that this is a solid process. What we've been seeing is that about 70% of the PTFs in Error (PEs) and almost all of the severe ones show up in the first four months. I have customers who try to keep within one or two months and others that are more comfortable with six months back. The more you want to use the latest features, the more you need to be current. Getting the fixes for PEs and the hipers installed is important for avoiding problems. [...] 18212 18 50_Re: DB2/IMS COEXISTENCE - SINGLE POINT OF RECOVERY12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 14:47:45 -0500446_- This seems to match a tool we have here at IBM too. IBM DB2 Recovery Manager for OS/390 simplifies and coordinates the recovery of both DB2 and IMS data to a common point, cutting the time and cost of data recovery and availability, and eliminating the error-prone complexity of managing different logs, utilities and processes to do recovery from both databases. If that sounds useful, there is a fact sheet and a Users Guide on the web. [...] 18231 17 36_Re: Dynamic SQL and CPU usage report12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 15:03:53 -0500436_- The early work on parallel produced separate records for the parallel work and still has the option, but most customers are rolling up the cpu time for the parallel tasks. DB2 PM reporting includes the parallel processing. The key APARs for the rollup are PQ10864, PQ22451 & PQ22260. The parameter for this is called PTASKROL, so you can look in the updated V5 or V6 Installation Guide or in the V5 Recent Enhancements red book. [...] 18249 23 44_Re: Upgrade from DB2 V6.1 to DB2 v7.1 OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 15:23:32 -0500501_- V6 to V7 seems fairly smooth so far. The grades our customers are giving us look good. The incomplete table definitions (constraints without enforcing indexes), a few reserved word uses, changed default for the identity columns, ...

The Installation Guide has one chapter for V6 to V7 (the easier one), and a longer chapter for V5 to V7. I have a presentation or so on the web. The easy way to get there is probably www.db2usa.com Then click on Conferences and choose the item you want. [...] 18273 14 23_Re: untility sample job12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 20 May 2001 15:26:27 -0500465_- There are some samples of utilities in the jobs, such as DSNTEJ1, but if you want an example from the book, then the easiest technique might be to cut and paste from the PDF or HTML book.

Roger Miller, DB2 for z/OS

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 18288 152 37_Re: Removing duplicates from an index18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Mon, 21 May 2001 08:14:37 +0930567_iso-8859-1 Howzit Carlton?

//SYSIN DD * SORT CONTROL CARD DATA SET SORT FIELDS=(pos,length,format,sequence,...) SUM NONE

Sort the data in the same sequence as your clustering index (if one exists). 'NONE' deletes duplicate records without summarization.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









Carlton Enuda @RYCI.COM> on 19/05/2001 21:42:05

Please respond to DB2 Data Base Discussion List [...] 18441 99 32_Re: interpreting DSN1PRNT output16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 21 May 2001 08:54:28 +0530621_- Mike,

You are correct. I bypassed that one(the ID-Map).

Regards Sanjeev

> -----Original Message----- > From: Mike Turner [SMTP:Michael_Turner@COMPUSERVE.COM] > Sent: Friday, May 18, 2001 6:45 PM > To: DB2-L@RYCI.COM > Subject: Re: interpreting DSN1PRNT output > > Sanjeev > > A minor correction. RID is page number + ID-Map entry number. ID-Map entry > in data page contains actual offset to row in page. > > Derek > > Your interpretation is correct. X'000002' is the page number and > corresponds to DSN1PRNT PGNUM. X'01' is ID-Map entry number and PGSBID is > the pointer back to the ID-Map [...] 18541 52 12_Re: SYSLGRNX16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 21 May 2001 09:08:03 +0530622_- Isn't this i have written an Update statement but not updated anything. DB2 doesn't write the logs for those and infact doesn't not perform updates.

Regards Sanjeev

> -----Original Message----- > From: db user [SMTP:dbuser75@HOTMAIL.COM] > Sent: Saturday, May 19, 2001 2:11 AM > To: DB2-L@RYCI.COM > Subject: SYSLGRNX > > Hi ! > > I ran REPORT recovery on a tablespace to find out whether that > tablespace was ever updated. I got the following results from SYSLGRNX: > > UCDATE UCTIME START RBA STOP RBA > 050501 21092217 0852143BEA70 0852143BEA70 > 050501 21092590 0852145235E9 0852145235E9 > > The [...] 18594 36 25_Test only - please delete14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Mon, 21 May 2001 02:47:17 -050011_iso-8859-1 18631 100 33_One more : SQL Performace Problem13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Mon, 21 May 2001 02:48:16 -0500440_ISO-8859-1 Hi List !

We´ve got a SQL performance problem.

Two tables are involved in that kind of problem :

Table A which contains the following subset of columns important for the "problem SQL" :

CUST_NO CHAR(10) CUST_NAME CHAR(120) VALID_FROM TIMESTAMP VALID_UNTIL TIMESTAMP

Index (Unique, Clustered) :

CUST_NO ASC VALID_FROM ASC

Table A contains 750.000 rows, one for each customer. [...] 18732 151 37_Re: One more : SQL Performace Problem18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Mon, 21 May 2001 14:32:01 +0500440_iso-8859-1 Hi Patric

How about if you re-write your query as

SELECT TABLEA.CUST_NO ,TABLEA.CUST_NAME FROM TABLEA A, TABLEB B, TABLEB C WHERE TABLEA.CUST_NO = TABLEB.CUST_NO AND TABLEB.PHONETIC_CODE =:HVa AND TABLEA.CUST_NO=C.CUST_NO AND C.PHONETIC_CODE=:HVb

With this you should get 2 match cols on TABLEB and C in your EXPLAIN. Also people say that joins perform better than sub-queries. See if that would help?. [...] 18884 50 26_DB2 V6 & Oracle Gateway V49_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Mon, 21 May 2001 10:12:39 +0100508_us-ascii Hi, Is anybody out there using Oracle Gateway 4.0.1.1.0 (I think I've got the right number of maintenance levels there) to access OS390 DB2 V6 ?

I have raised the compatability issue with Oracle Support but they say that "...we cannot guarantee it will work successfully, but we only have one reported problem concerning v4 tg4db2 and db3 v6.1, with regard to stored procedures; db2 stored procedure definitions need to be present in sysibm.sysprocedures for the tg4db2 to access them". [...] 18935 203 37_Re: One more : SQL Performace Problem16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 21 May 2001 17:26:20 +0530611_iso-8859-1 Patric,

I think the EXISTS should work better....

SELECT CUST_NO ,CUST_NAME FROM TABLEA X WHERE EXISTS (SELECT 1 FROM TABLEB Y WHERE Y.CUST_NO = X.CUST_NO AND Y.PHONETIC_CODE = :HVa) AND EXISTS (SELECT 1 FROM TABLEB Z WHERE Z.CUST_NO = X.CUST_NO AND Z.PHONETIC_CODE = :HVb)



Try and see how it performs.

You can also try join as suggested by Nagaraj but if PHONETIC_CODE is taken as the local filter then how much filteration it offers will be an issue. If DB2 chooses Table A as the outer table(by size) then it "may" be a problem. Its better if you try both [...] 19139 36 37_Re: One more : SQL Performace Problem13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Mon, 21 May 2001 06:52:09 -0500608_- Hi Nagaraj !

Thanks, but local response time increased to 26 seconds using this SQL statement.

One of the reasons might be that we got a nested loop join.

Patric

> Hi Patric > > How about if you re-write your query as > > SELECT TABLEA.CUST_NO > ,TABLEA.CUST_NAME > FROM TABLEA A, TABLEB B, TABLEB C > WHERE TABLEA.CUST_NO = TABLEB.CUST_NO AND TABLEB.PHONETIC_CODE > =:HVa AND > TABLEA.CUST_NO=C.CUST_NO AND C.PHONETIC_CODE=:HVb > > With this you should get 2 match cols on TABLEB and C in your EXPLAIN. > Also people say that joins perform better than sub-queries. See if [...] 19176 77 30_Re: DB2 V6 & Oracle Gateway V412_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 21 May 2001 05:19:08 -0700600_us-ascii My client is using Oracle Transparent Gateway for DB2 on MVS: Release 4.0.1.1.1

to access DB2 V6 OS/390. Works ok usually. Occasional hangups. --- Andy Hunt wrote: > Hi, > Is anybody out there using Oracle Gateway 4.0.1.1.0 (I think I've got > the right > number of maintenance levels there) to access OS390 DB2 V6 ? > > I have raised the compatability issue with Oracle Support but they > say that > "...we cannot guarantee it will work > successfully, but we only have one reported problem concerning v4 > tg4db2 and > db3 v6.1, with regard [...] 19254 36 26_Error Handling in Triggers13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM31_Mon, 21 May 2001 17:26:59 +0500400_iso-8859-1 Hi

I wanted to know if there is anyway in which I can trap errors inside triggers and do some processing.

Eg. I have an INSERT inside an After Row trigger and if that Insert fails I want to record the error message in another table and continue.

I wanted to know if there is anyway using which I could introduce condition handlers and error handlers in triggers. [...] 19291 39 16_DSNR035I Message0_20_vpacheco@AMADEUS.NET31_Mon, 21 May 2001 14:21:50 +0200509_us-ascii Dear DB2 List

Last night I experienced the following message: DSNR035I -DSP1 DSNRPBCW WARNING - UNCOMMITTED UR AFTER 512 CHECKPOINTS -

I consider it a World Record ;-)

We had this message several times before, but never got so high.

Interestingly is that this morning the DB2 subsystem was frozen - did not process anything - and we had to purge IRLM in order to shut it down. When it come back the above thread was reported to be in abort and had to be rolled back. [...] 19331 223 37_Re: One more : SQL Performace Problem13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 21 May 2001 07:36:47 -0500420_iso-8859-1 Patric,

You may have got a poor response from Nagaraj's query due to a poor table join sequence, and it may actually introduce duplicates into the result set. This SQL will force the table join sequence so that TABLEA is accessed last. There is nothing actually wrong with the original access path, although you may wish to avoid the multi-index access, of which there are a number of techniques. [...] 19555 66 37_Re: One more : SQL Performace Problem18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Mon, 21 May 2001 18:12:51 +0500601_iso-8859-1 Hi Patric

CAn you provide your explain output and how about Sanjeev's recommendation?. Did you try that?. Also can you give your table and index stats?.

Thanks and regards

Nagaraj

> -----Original Message----- > From: Patric Becker [SMTP:Patric_Becker@SIS-WEST.DE] > Sent: Monday, May 21, 2001 5:22 PM > To: DB2-L@RYCI.COM > Subject: Re: One more : SQL Performace Problem > > Hi Nagaraj ! > > Thanks, but local response time increased to 26 seconds using this > SQL statement. > > One of the reasons might be that we got a nested loop join. > > Patric > > > [...] 19622 224 37_Re: One more : SQL Performace Problem13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 21 May 2001 07:59:45 -0500518_iso-8859-1 Patric/Sanjeev,

Converting both subqueries to EXISTS would be a bad idea since they are stage 2 predicates. Now all rows of TABLEA must be read since there are no indexable predicates on the outer table.

Converting only one of the subqueries would remove the multi-index access:

SELECT CUST_NO ,CUST_NAME FROM TABLEA X WHERE CUSTNO IN (SELECT CUSTNO FROM TABLEB WHERE PHONETIC_CODE = :HVa) AND EXISTS (SELECT 1 FROM TABLEB Z WHERE Z.CUST_NO = X.CUST_NO AND Z.PHONETIC_CODE = :HVb) [...] 19847 50 30_Fw: DB2 V6 & Oracle Gateway V417_Brigitte Philippi36_brigitte.philippi@ZID.UNI-LINZ.AC.AT31_Mon, 21 May 2001 15:15:04 +0200551_iso-8859-1 > > > > Andy Hunt schrieb in im Newsbeitrag: > > 80256A53.0031BCDC.00@mamntg01.scottish-southern.co.uk... > > > Hi, > > > Is anybody out there using Oracle Gateway 4.0.1.1.0 (I think I've got > the > > right > > > number of maintenance levels there) to access OS390 DB2 V6 ? > > > > > > I have raised the compatability issue with Oracle Support but they say > > that > > > "...we cannot guarantee it will work > > > successfully, but we only have one reported problem concerning v4 > > tg4db2 and > [...] 19898 42 54_ROUND function available in DB2 for OS/390 Version 5 ?12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 21 May 2001 15:36:41 +0200331_iso-8859-1 Hi Listers.

As far as I know the ROUND function, e.g. SET mycolumn = ROUND((DECIMAL(mycolum,31,5) / 1.95583),2) was introduced in DB2 for OS/390 Version 6.

Here is my question:

Is there an APAR or a PTF or somewhat else available to get the ROUND function working in DB2 for OS/390 version 5 ? [...] 19941 97 12_Re: DSNYASCP11_Bikash Paul19_bikash_db@YAHOO.COM31_Mon, 21 May 2001 06:45:42 -0700553_us-ascii Hi Rush, The procedure name is not derived from the DSNZPARM. Rather it is done by definition made in IEFSSNxx member of the a parmlib dataset (Like SYS1.PARMLIB, CPAC.PARMLIB etc). DB2 subsystem name is defined in IFFSSNxx member. Definition looks like ADD SUBSYS(DSN1) INITRTN(DSN3EPX) INITPARM(DSN3INI,@Y ... )

So While DB2 is started by the operator command as "@Y start db2".. The routine DSN3INI will look for DSN1MSTR, DSN1DBM1 and DSN1DIST in one of the cataloged procedure librarary. Upon finding them, it will start them. [...] 20039 13 45_Re: Implementing chargeback on Resource usage12_Brian Picard26_brian_picard@ALTAVISTA.COM31_Mon, 21 May 2001 08:37:03 -0500358_- Thanks Scott for an elaborate answer to this. We are considering normalizing the CPU Service Units. Regards, Brian

================================================ 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. 20053 36 22_-802 Zero divide error23_THOLKAPPIAN Chidambaram21_CTholkap@COVANSYS.COM31_Mon, 21 May 2001 10:15:11 -0400345_- Hi List,

I'm an application developer. I'm getting -802 error when I execute the following query. But it is successful when the last where condition is removed(DIFF <> 0). I could sense the problem is in the datatype of the DIFF but I'm unable to resolve. Can you please explain me what is the error. Any help will be appreciated. [...] 20090 89 30_Re: DB2 V6 & Oracle Gateway V40_27_jim.leask@RS-COMPONENTS.COM31_Mon, 21 May 2001 15:24:11 +0100450_iso-8859-1 We are using Oracle Transparent Gateway for DB2 on MVS: Release 3.1.0.1.0 on OS390 v1.3 and DB2 v6 in production.

We are using Oracle Transparent Gateway for DB2 on MVS: Release 4.0.1.1.1 on OS390 v2.9 and DB2 v6 in a development environment.

Jim.

-----Original Message----- From: Andy Hunt [mailto:Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK] Sent: 21 May 2001 10:13 To: DB2-L@RYCI.COM Subject: DB2 V6 & Oracle Gateway V4 [...] 20180 41 33_drop coulumn.... immediate reply.17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Mon, 21 May 2001 09:15:15 -0500304_- Hi Folks,

In our shop we are using DB2 V6, on os390. Actually we have a table in test region and there is data also in the table. now the user found that there is on unwanted column in the table so they want to take out that column from the table. but they want the same data to be loaded. [...] 20222 21 59_Visual basic calling DB2 Stored procedure on OS/390 example15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 21 May 2001 10:33:59 -0400398_iso-8859-1 Can somebody send me a VB6 code using ADO to call a DB2 stored procedure in version 6.0 on OS/390 2.9 to get output variables and result set. Can we access the output variables before fetching the result set. I have seen one message in the achieve saying we cannot. I saw the sample code in the VB4 using DB2 API and RDO. But our developers would like to have one with ADO and VB6. [...] 20244 72 37_Re: drop coulumn.... immediate reply.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 21 May 2001 20:11:44 +0530546_- Kumar,

While unloading, select the columns which you are left with after removing the 2 columns. Create the control card (Punch) from the same unload JCL. This way you should be able to load the data.

As per your message, it says that the column will be discarded, it will not load the data and fail with return code of 8 (If i remember correctly). Always, before loading check the data and control card with the clear concept of "How loading is done". This way, you can find out reasons for all the errors and rectify that. [...] 20317 84 37_Re: drop coulumn.... immediate reply.14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Mon, 21 May 2001 15:40:29 +0100410_iso-8859-1 Have you changed your LOAD card to reflect the new column layout?

Marcus

> 'The views expressed are my own and do not necessarily represent the > views or policy of my employer, Lloyds TSB Bank plc.'



-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: 21 May 2001 15:15 To: DB2-L@RYCI.COM Subject: drop coulumn.... immediate reply. [...] 20402 53 12_Re: SYSLGRNX15_Murley, Michael22_Michael_Murley@BMC.COM31_Mon, 21 May 2001 09:41:35 -0500453_iso-8859-1 Kumar,

I believe these start=stop SYSLGRNX records are used by DB2 during levelid checking.

Michael Murley BMC

-----Original Message----- From: db user [mailto:dbuser75@HOTMAIL.COM] Sent: Friday, May 18, 2001 3:41 PM To: DB2-L@RYCI.COM Subject: SYSLGRNX



Hi !

I ran REPORT recovery on a tablespace to find out whether that tablespace was ever updated. I got the following results from SYSLGRNX: [...] 20456 110 58_Re: ROUND function available in DB2 for OS/390 Version 5 ?12_Le BD Nguyen20_lnguyen@AHOLDUSA.COM31_Mon, 21 May 2001 10:45:30 -0400466_iso-8859-1 Peter,

I don't think in V5 there is a ROUND function or any PTF out there. However, you can simulate the ROUND function by manipulate the SQL a little. For example, DECIMAL(DECIMAL(mycolum,31,5) / 1.95583 + 0.005,31,2)

Hope this help.





"Peter, Georg" To: DB2-L@RYCI.COM Subject: ROUND function available in DB2 for OS/390 Version 5 ? Sent by: DB2 Data Base Discussion List [...] 20567 119 102_3 Jobs (Mainframe Developers in NC)- Please let me know if you have interest or know anyone who might.13_kathryn sears24_ksears@DPCONSULTANTS.COM31_Mon, 21 May 2001 10:49:15 -0400657_iso-8859-1 Hi, everyone- Thanks for your help on previous positions. Wanted to make you aware that I am currently working on: Three Mainframe Development positions, one of which I have described in the past. If you recommend someone to me, who is placed, then we do pay $1,000.00 referral bonuses! (Here are the 3 job descriptions:) 1. Assembler- North Carolina. Sr Applications Developer- Will lead projects, work in all stages of development process including "planning/ cost/benefit analysis). Great company; stability and good work environment. We need at least 3 years minimum of experience in Assembler programming, ALSO: COBOL, Lifecomm, and (a [...] 20687 143 63_Re: Visual basic calling DB2 Stored procedure on OS/390 example12_Grant Mackay28_Grant_Mackay@PROGRESSIVE.COM31_Mon, 21 May 2001 10:56:14 -04001008_us-ascii Here is some vb script code for an Active Server Page. Accessing the parameters first then the result set.



Set objConnection = Server.CreateObject("ADODB.Connection") set objConnection = OpenConnection()

Set objCmd = Server.CreateObject("ADODB.Command") Set objCmd.ActiveConnection = objConnection

Set ObjRS = Server.CreateObject("ADODB.Recordset") ObjRS.CursorType = adOpenStatic Set ObjRS.Source = objCmd





objCmd.CommandText = "CDWCY003" objCmd.CommandType=adCmdStoredProc

ObjCmd.Parameters.Append objCmd.CreateParameter("QUERYT", adChar, adParamInput,8,"AAA0001") ObjCmd.Parameters.Append objCmd.CreateParameter("APPRC", adChar, adParamOutput,2) ObjCmd.Parameters.Append objCmd.CreateParameter("FAILSTMT", adChar, adParamOutput,2) ObjCmd.Parameters.Append objCmd.CreateParameter("FAILCD", adInteger, adParamOutput) ObjCmd.Parameters.Append objCmd.CreateParameter("FAILMSG", adChar, adParamOutput,70) Response.Write("before execute") [...] 20831 46 21_DSNT212I BIND PACKAGE13_Thomas Schulz20_thomas.schulz@SVI.DE31_Mon, 21 May 2001 09:43:02 -0500452_- Hi Folks

I have some trouble with the following job

//USER1 JOB ,'Bind',MSGCLASS=X, // MSGLEVEL=(1,1),CLASS=C, // NOTIFY=USER //JOBLIB DD DSN=SDSNLOAD,DISP=SHR //BIND EXEC PGM=IKJEFT01,DYNAMNBR=100 //* //SYSTSPRT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSDBOUT DD SYSOUT=* //DBRMLIB DD DISP=SHR,DSN=DBRMLIB //SYSTSIN DD * DSN SYSTEM (DBT2) BIND PACKAGE(TLVB) MEMBER(LVG) - ACTION(REPLACE) OWNER(TLV) EXPLAIN(NO) [...] 20878 16 21_Re: Setting email off11_Jeff Kalman15_jkalman@CSC.COM31_Mon, 21 May 2001 11:10:52 -0400392_us-ascii Hi guys, I'm going on vacation for a week and do not want to receive emails from the list during that time. How can I do that?

Thanks.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20895 61 21_Re: Setting email off0_29_sflindsey@HIGHLIGHTS-CORP.COM31_Mon, 21 May 2001 11:28:04 -0400538_us-ascii Send an email to LISTSERV@RYCI.COM with SET DB2-L NOMAIL in the body of the email.

To turn it back on, send another email to LISTSERV@RYCI.COM with SET DB2-L MAIL in the body of the email.

















Jeff Kalman on 05/21/2001 11:10:52 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Scott F Lindsey/HFC/HFC_Corp) [...] 20957 166 63_Re: Visual basic calling DB2 Stored procedure on OS/390 example13_Andy Seuffert21_aseuffert@NEONSYS.COM31_Mon, 21 May 2001 10:30:40 -0500369_us-ascii







Andy Seuffert@NEON 05/21/2001 10:30 AM

Phillip, if you are using ADO and calling a DB2 stored procedure, you must first fetch the result set, then close and set your resultset object to NOTHING, before accessing the output variables. Following is a sample that I use and the MS Article that somewhat describes this: [...] 21124 57 26_Re: -802 Zero divide error12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 21 May 2001 18:44:45 +0200655_Windows-1252 Hi, You may have B.SCADIP=0 somewhere in your data.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "THOLKAPPIAN Chidambaram" Newsgroups: bit.listserv.db2-l To: Sent: Monday, May 21, 2001 4:15 PM Subject: -802 Zero divide error



> Hi List, > > I'm an application developer. I'm getting -802 error when I execute the > following query. But it is successful when the last where condition > is removed(DIFF <> 0). > I could sense the problem is in the datatype of the DIFF but I'm > unable to resolve. Can you please explain me what is [...] 21182 42 21_Re: Setting email off15_Robert lawrence21_rlawrence@BOSCOVS.COM31_Mon, 21 May 2001 11:57:53 -0400613_iso-8859-1 click on the webpage at bottom of reply and follow instructions hth Bob Lawrence DBA Boscov's Dept Stores LLc





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Jeff Kalman > Sent: Monday, May 21, 2001 11:11 AM > To: DB2-L@RYCI.COM > Subject: Re: Setting email off > > > Hi guys, > I'm going on vacation for a week and do not want to receive > emails from the > list during that time. How can I do that? > > Thanks. > > ================================================ > To change your subscription options or to cancel [...] 21225 188 22_Re: Use of Code Tables12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 21 May 2001 19:02:21 +0200524_iso-8859-1 Hi, If it's short term transaction (as opposed to long batch runs) don't read code tables into memory - it's a waste of time (in CICS days you may have used TS / CICS tables etc. for that). Another problem you may encounter with join is that you need left join and not inner join as you may have code in the fact data with no translation in the code table, this reduces the number of tables you can join in one SQL command (unless you want to tamper with DSN6* macros ...) You'll need to dig deeper into the [...] 21414 71 25_Re: DSNT212I BIND PACKAGE11_Bikash Paul19_bikash_db@YAHOO.COM31_Mon, 21 May 2001 09:15:19 -0700634_us-ascii Hi, I am not sure whether TLV is userid or group. If group, the user who is running the job shoule be connected with the group.

However, the TLV should have rebind access on the package if it is already bound.

Regards, Bikash --- Thomas Schulz wrote: > Hi Folks > > I have some trouble with the following job > > //USER1 JOB ,'Bind',MSGCLASS=X, > // MSGLEVEL=(1,1),CLASS=C, > // NOTIFY=USER > //JOBLIB DD DSN=SDSNLOAD,DISP=SHR > //BIND EXEC PGM=IKJEFT01,DYNAMNBR=100 > //* > //SYSTSPRT DD SYSOUT=* > //SYSOUT DD SYSOUT=* > //SYSUDUMP DD SYSOUT=* > //SYSDBOUT DD SYSOUT=* > //DBRMLIB [...] 21486 81 25_Re: DSNT212I BIND PACKAGE15_Toine Michielse18_vndobtm@US.IBM.COM31_Sun, 20 May 2001 18:24:32 +0200467_us-ascii Hello Thomas,

TLV should be the primary authid under which you run the job or one of the secondary id's. If you use the standard authorization exits that DB2 provides, these are the RACF groups the authid of the job is connected to.

Alternatively, the userid under which the bind runs must have either SYSADM or SYSCNTRL. BINDAGENT would also be sufficient authorisation provided the owner you specified was the grantor of that privilige. [...] 21568 31 30_QMF with multiple DB2 versions16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 21 May 2001 13:37:24 -0400277_iso-8859-1 Has anyone done this?

We have 4 DB2 subsystems on one LPAR. 2 subsys are at Db2 V7, 2 are at DB2 V5.

We would like to access all 4 DB2 via QMF in one LOGON proc. I.e. select Q1Q for QMF against one of the V5 subsys, Q3Q for QMF on a V7 subsystem. [...] 21600 19 0_8_K.Balaji19_K.Balaji@TARGET.COM31_Mon, 21 May 2001 12:44:17 -0500321_iso-8859-1 Hello, I am looking for some good books on "Designing a Batch system" - (Environment - Cobol-DB2 for batch & Front end is web-enabled with DB2 as backend database). And I am looking for good design principles and some case studies. Any help will be greatly appreciated. Regards Balaji Ph # (510) 727-3259 [...] 21620 44 37_Re: Removing duplicates from an index17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Mon, 21 May 2001 14:45:18 -0300236_us-ascii Carlton,

We do that using following procedure with IBM utilities:

1) REORG UNLOAD PAUSE 2) SORT using SUM FIELDS=NONE to eliminate duplicate rows (DFSORT) 3) LOAD FORMAT UNLOAD

HTH,

Walter Trovijo Jr 21665 91 32_IDUG Conference (Migration Tool)11_Emery, Matt19_matt.emery@JNLI.COM31_Mon, 21 May 2001 14:20:21 -0400586_iso-8859-1 For those of you who attended the IDUG Conference last week in Orlando... There was mention of a "Beta IBM DB2 Migration Tool for Sybase T-SQL". Does anyone know where I can get more information about this tool (ie. website, download, how to access, etc.)?

Thanks in advance.

Matthew J. Emery Jackson National Life Insurance Company Technology Department - Database Administrator voice: 517-367-3715 fax: 517-706-5506 pager: 517-794-3504 email: Matt.Emery@jnli.com e-page: 5177943504@myairmail.com 21757 36 27_Tuning QMF FPARM (DSQSIROW)0_26_mmetcalf@NOTES.STATE.NE.US31_Mon, 21 May 2001 13:34:29 -0500550_us-ascii A statement was made in a training session to set the QMF FPARM (DSQSIROW) value high. In the QMF manuals it talks about the DSQSBSTG and DSQSRSTG parameters which need to be set in conjunction with the DSQSIROW. There is a statement that says if you use too large a value for DSQSIROW QMF might take a long time to display the first screen of data. If you set DSQSIROW higher than you set the DSQSBSTG parm, for example, QMF might display a message indicating that there is insufficient storage availale to satisfy the user's request. [...] 21794 104 37_Re: drop coulumn.... immediate reply.17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Mon, 21 May 2001 13:23:16 -0500673_- Hi Folks,

Thanks for your hlep. As Sanjeev said, i unloaded the data with the selected column to the dataset, i drop the table and recreated the table with selected column. then i reload the data using selected column SYSPUNCH. it works good.

Thnks kumar



>From: "S, Sanjeev (CTS)" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: drop coulumn.... immediate reply. >Date: Mon, 21 May 2001 20:11:44 +0530 > >Kumar, > >While unloading, select the columns which you are left with after removing >the 2 columns. Create the control card (Punch) from the same unload JCL. [...] 21899 77 21_Re: Design Guidelines16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Mon, 21 May 2001 14:39:37 -0400804_iso-8859-1 On Compuware's web site, there is a link, for downloading, a series of Chuck Hoover presentations concerning DB2. One of those presentations deal directly in designing a DB2 application within a batch environment. The downloads are free.

hope this helps







http://www.compuware.com/products/db2.htm

Robert Galambos Compuware Corp. Of Canada

1-800-348-8299 1-800-447-1662 (quebec) 1-905-803-8603 (fax)

******************************************************************** This message and any attachments are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee you may not copy, forward, disclose or use any part of the message or its attachments [...] 21977 107 43_Announcing BMC's SQL-Programmer for DB2 UDB12_Cook, Nicole19_Nicole_Cook@BMC.COM31_Mon, 21 May 2001 13:53:36 -0500807_iso-8859-1 BMC Software's Award-Winning SQL-Programmer* Now Available For IBM's DB2* Universal Database



SQL-Programmer Boosts Productivity for Database Developers and Administrators



HOUSTON - (May 14, 2001) - BMC Software, Inc., [NYSE: BMC], the leading provider of enterprise management, today announced that its award-winning SQL programming tool is now available for IBM's DB2 Universal Database (UDB) for UNIX, Windows and Linux platforms. SQL-Programmer 2001 for DB2 Universal Database will boost database developers' productivity and make their SQL programming jobs easier. This new tool further rounds out BMC Software's database management offerings and supports BMC Software's infrastructure management initiatives to assure business availability. SQL-Programmer [...] 22085 50 34_Re: QMF with multiple DB2 versions12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 21 May 2001 14:17:33 -0500397_iso-8859-1 Eric, I will send you the code off list. DB2L only allows 250 lines.

john

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Monday, May 21, 2001 12:37 PM To: DB2-L@RYCI.COM Subject: QMF with multiple DB2 versions

Has anyone done this?

We have 4 DB2 subsystems on one LPAR. 2 subsys are at Db2 V7, 2 are at DB2 V5. [...] 22136 18 28_data lost - last update time16_Cockerill, Steve30_SLCockerill@NATIONALGYPSUM.COM31_Mon, 21 May 2001 15:27:27 -0400411_iso-8859-1 Anyone know how I can find the last date and time a table was updated?



Thanks Steve Cockerill Sr. Peoplesoft DBA slcockerill@nationalgypsum.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. 22155 36 32_Re: data lost - last update time13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Mon, 21 May 2001 12:34:15 -0700443_us-ascii REPORT RECOVERY TABLESPACE will give you this info.

Thanks. Ashish.

-----Original Message----- From: Cockerill, Steve [SMTP:SLCockerill@NATIONALGYPSUM.COM] Sent: Monday, May 21, 2001 12:27 PM To: DB2-L@RYCI.COM Subject: data lost - last update time

Anyone know how I can find the last date and time a table was updated?



Thanks Steve Cockerill Sr. Peoplesoft DBA slcockerill@nationalgypsum.com [...] 22192 70 34_Re: QMF with multiple DB2 versions16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 21 May 2001 15:34:19 -0400563_iso-8859-1 THANKS!!!

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Vernon, John [mailto:John.Vernon@TRS.STATE.TX.US] Sent: Monday, May 21, 2001 3:18 PM To: DB2-L@RYCI.COM Subject: Re: QMF with multiple DB2 versions



Eric, I will send you the code off list. DB2L only allows 250 lines.

john

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Monday, May 21, 2001 12:37 PM To: DB2-L@RYCI.COM Subject: QMF with multiple DB2 versions [...] 22263 65 34_Re: QMF with multiple DB2 versions11_Mark Turner29_mark.turner@UCDMC.UCDAVIS.EDU31_Mon, 21 May 2001 12:43:38 -0700477_us-ascii To overcome LIBDEF limitations, consider TSOLIB or, better yet, a product that provides dynamic STEPLIB allocation.







"Pearson, Eric L," To: DB2-L@RYCI.COM Subject: QMF with multiple DB2 versions Sent by: DB2 Data Base Discussion List



05/21/01 10:37 AM Please respond to DB2 Data Base Discussion List











Has anyone done this? [...] 22329 73 30_Re: Error Handling in Triggers14_Richard Yevich17_ryevich@YAHOO.COM31_Mon, 21 May 2001 12:57:19 -0700537_us-ascii Ritesh,

There is no way to do that at this time.

--- "Swamy, Ritesh" wrote: > Hi > > I wanted to know if there is anyway in which I can trap errors > inside > triggers and do some processing. > > Eg. I have an INSERT inside an After Row trigger and if that Insert > fails I > want to record the error message in another table and continue. > > I wanted to know if there is anyway using which I could introduce > condition > handlers and error handlers in triggers. > > regds, [...] 22403 96 34_Re: QMF with multiple DB2 versions16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 21 May 2001 16:07:43 -0400520_iso-8859-1 We are looking at TLIB from Xephon. TSOLIB is not an option since it must happen before the user goes into ISPF. The need is for a user who is in ISPF to be able to 'jump between' QMF/DB2 sessions. Not a problem with SPUFI and with CA/Platinum. They can handle all the libs being allocated with LIBDEF. QMF is the problem since it uses MVS LOAD etc. We may be forced to upgrade from QMF 3.3 to 7.1 to handle what we need. We were hoping to upgrade DB2 without doing a QMF upgrade, but it is looking more [...] 22500 43 34_Re: QMF with multiple DB2 versions12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Mon, 21 May 2001 16:06:00 -0400426_- If you are using QMF v6: /* QMF V6.1 ALLOWS LIBDEF ALLOCATION */ ALLOC FI(DSQLLIB) SHR REUSE + DA('DB2.QMF&DBID..SDSQLOAD', + 'DB2.&DBID..DSNEXIT', + 'DB2.&DBID..DSNLOAD') ISPEXEC LIBDEF ISPLLIB LIBRARY ID(DSQLLIB) I believe this is documented in the QMF install. >>> DB2-L@RYCI.COM@inter2 05/21/01 12:37PM >>> Has anyone done this?

We have 4 DB2 subsystems on one LPAR. 2 subsys are at Db2 V7, 2 are at DB2 V5. [...] 22544 76 32_Re: data lost - last update time15_Toine Michielse18_vndobtm@US.IBM.COM31_Sun, 20 May 2001 22:43:32 +0200598_us-ascii Hello,

REPORT RECOVERY will give you the logranges during which the tablespace containing the table was opened for update. You are interested in the last logrange you find here. However, depending on your PCLOSET/ (PCLOSEN + #of checkpoints) this logrange may be open ended and will not give you an exect time for the last update. So use the startrba (end if the logrange is closed also the endrba) as input to a DSN1LOGP utility with SUMMARY(ONLY). This will give you an overview of all the UR's that modified the tablespace. Then do a find (starting at the bottom, searching [...] 22621 87 12_Re: DSNYASCP36_Enarson, Mike (CC-ETS Database Svcs)29_Mike.Enarson@CONAGRAFOODS.COM31_Mon, 21 May 2001 16:46:55 -0500376_gb2312 In the MVS parmlib is defined the subsystem id as a registered MVS subsystem, which is also contained in the DSNHDECP. The first characters of the started task names need to be the SSID which is referenced in IEFSSN00 with: the SSID, the name of the ERLY code module, and the subsystem recognition character which is used to start DB2, in your case DSN1 for ssid. [...] 22709 82 37_Re: Removing duplicates from an index13_Olson, Carlos14_COlson@QRS.COM31_Mon, 21 May 2001 14:48:28 -0700586_iso-8859-1 Carlton, FAST UNLOAD is a batch utility - not an option from the CA Main menu. It's just a substitute for using IBM's DSNTIAUL or REORG PAUSE/UNLOAD ONLY. For what you are trying to accomplish strictly from a functionality standpoint (not performance), there's nothing to gain by using CA's unload and load. Whether you use IBM's or CA's LOAD, they both throw away all duplicates violating the new unique contraints. If you want to keep at least one of each row where there are duplicates based on the new index, you need to edit duplicates from the discard dataset and [...] 22792 41 47_Datatype problem with Access 2000 & DB2 UDB 7.10_26_Steve.Westfall@EQUIFAX.COM31_Mon, 21 May 2001 17:42:07 -0500575_us-ascii I searched the archives but didn't find the answer to this, so . . .

I am using Microsoft Access 2000 to create reports using content from the DB2 system catalog views. (We have DB2 UDB EEE 7.1 in AIX 4.3.) I find that when I link an Access database to one of the DB2 catalog views, say SYSCAT.TABLES, columns that are defined in DB2 as INTEGER usually translate into the Access datatype of Number with a fieldsize of Long Integer. However, in at least one case, SYSCAT.TABLES.CARD, the DB2 INTEGER gets translated by Access as its BINARY datatype, with [...] 22834 15 46_Demetris Kasheris/LT/CPB is out of the office.17_Demetris Kasheris19_DKasheris@LAIKI.COM31_Tue, 22 May 2001 01:00:33 +0300379_us-ascii I will be out of the office starting 05/21/2001 and will not return until 05/28/2001.

I will be in London for BRS testing

================================================ 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. 22850 191 26_SQL Statement Optimization15_Bergeson, Loren24_LBergeson@IDAHOPOWER.COM31_Mon, 21 May 2001 17:01:23 -0600975_iso-8859-1 We have an on-line query which is giving very poor performance (response time is 50-55 seconds). The SQL statement is as follows (question marks are host variable markers):

SELECT LOG_COUNTER, ADD_USER_ID, ADD_PROGRAM, ADD_TIME, UPDATE_USER_ID, UPDATE_PGM, UPDATE_TIME, FROZEN_USER_ID, FROZEN_PGM, FROZEN_TIME , PAY_HDR_ID, PAY_STATUS, AMOUNT, ACCOUNT_ID, SHAREHOLDER_ID, BILL_HDR_ID, MAY_BE_LNK_TO_BL, SORT_NAME, NAME_BUSINESS, CASH_ID , PAY_SOURCE_CODE, COY, AREA, PAYOR, CHECK_ID, RECEIPT_NBR, CASH_DRAWER, BUNDLE, CREATED_DTM, RP_FROZEN, CANCELED_DTM, CANCELED_USER_ID, PAY_DT, PAY_DT9C, PAY_TOT, UTIL_TOT, NUT_TOT, DEP_TOT, STOCK_TOT, MISC_TOT, NOT_FROZEN, NOT_BALANCED, TRF_PAY_HDR_ID, PAY_METHOD, SG_STAT_PAYID, ERROR_MSG, ERROR_INFO , WSC_RECEIPT, CREDIT_CARD_NUMBER, CRED_CARD_EXP_DATE, CRED_AUTH_NUMBER, REM_AGENCY_ID, PAY_DIST_CODE, REM_AGENCY_BRANCH, EASYWAY_ID, CONCESSIONER_NBR FROM PY_PAY_HEADER WHERE COY =? AND ( PAY_STATUS = 'E' OR ( [...] 23042 29 41_Steen Hansen/DMDATA is out of the office.12_Steen Hansen13_sth@DMDATA.DK31_Tue, 22 May 2001 01:00:53 +0200634_us-ascii I will be out of the office from 21-05-2001 until 28-05-2001.



I will respond, to your mail when I return.

Med venlig hilsen / Best Regards

Steen Hansen Systems Programmer Database & Middleware ---------------------------- DMdata a/s Bytoften DK-8240 Risskov Tlf.: +45 8673 5425 Fax: +45 8673 5301 E-mail: mailto:sth@dmdata.dk ----------------------------

================================================ 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. 23072 19 22_REPORT RECOVERY output14_Toppins, Smike21_smike.toppins@GWL.COM31_Mon, 21 May 2001 17:28:16 -0600450_- Is the output from the REPORT utility guaranteed to be in TIMESTAMP sequence. Couldn't find the answer in the Utility Guide.



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. 23092 226 30_Re: SQL Statement Optimization18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Tue, 22 May 2001 09:23:51 +0930703_us-ascii Howzit Loren?

You could also use a CASE statement.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









"Bergeson, Loren" @RYCI.COM> on 22/05/2001 08:31:23

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: SQL Statement Optimization





We have an on-line query which is giving very poor performance (response time is 50-55 seconds). The SQL statement is as follows (question marks are host variable markers): [...] 23319 91 21_Re: Design Guidelines8_K.Balaji19_K.Balaji@TARGET.COM31_Mon, 21 May 2001 18:59:20 -0500504_iso-8859-1 Thanks Galambos. I down loaded it and looking at it.

-----Original Message----- From: Galambos, Robert [mailto:Robert_Galambos@COMPUWARE.COM] Sent: Monday, May 21, 2001 11:40 AM To: DB2-L@RYCI.COM Subject: Re: Design Guidelines



On Compuware's web site, there is a link, for downloading, a series of Chuck Hoover presentations concerning DB2. One of those presentations deal directly in designing a DB2 application within a batch environment. The downloads are free. [...] 23411 31 21_DSN1COPY on DB2 v6/v716_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Mon, 21 May 2001 17:43:09 -0700538_iso-8859-1 In one of the sessions at IDUG last week a comment was made that there were problems taking an imagecopy from a DB2 v5 system and using DSN1COPY to put it onto a DB2 v6 system. The person said the problems occurred early in the release of DB2 v6 and the problems may not be there now. Has anyone experienced any problems like this lately? If so, what types of problems did you encounter and if you solved the problem what was the solution? Has anyone tried this with a DB2 v7 system? If so, did you encounter any problems [...] 23443 229 30_Re: SQL Statement Optimization12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Tue, 22 May 2001 10:57:02 +1000325_iso-8859-1 Loren, I think that what has made the difference to your query is the New Index. I believe that the union has forced the issue and encouraged DB2 to use the new index. However, with the New Index in place and COLDIST stats collected you MAY even find that, under the old SQL, DB2 MIGHT look at the new index. [...] 23673 47 21_Re: Design Guidelines12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Tue, 22 May 2001 11:01:08 +1000397_- Thanks Robert!

... and above all thanks Chuck, for your contribution to DB2 over the years. If there was a DB2 Hall of Fame, you'd be one of the people I'd vote to have in there!

Cheers, Nick Cianci DB2 DBA - CCRI Project 5th Floor (South) 484 StKilda Rd MELBOURNE 3004 * (+613) 9865-8354 (+61) 0408-64 06 01 * MailTo:Nick.Cianci@team.telstra.com MailTo:NCianci@CPTglobal.com [...] 23721 15 27_Re: Global Temporary tables12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 21 May 2001 19:35:16 -0500518_- Back in about Version 3, the code was fixed to remove command validation or VALCMD. If you are using the old versions of code for DSNTEP2 and DSNTIAUL, you can make a significant performance improvement as well by running the current level.

Roger Miller, DB2 for z/OS

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 23737 74 53_Re: Warning messages seleting from a 160 char column.12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 21 May 2001 20:23:12 -0500299_- DSNTEP2 has quite a few variables that are meant to be changed as needed. The last big change to DSNTEP2 I see was about a year ago in PQ36800. To increase the maximum column width MAXCOLWD, you probably want to increase the PAGEWIDTH and MAXPAGWD as well. Here are a couple of the comments. [...] 23812 19 16_Migration to DB212_Roger Miller19_millerrl@US.IBM.COM31_Mon, 21 May 2001 21:01:02 -0500308_- Are you interested in a migration toolkit? For a free download?

The IBM DB2 V7.2 Sybase Migration Toolkit was released in beta last week, and we already have a few downloads, new customers and interesting questions in the DB2 newsgroup. This is a beta and covers DB2 for UNIX and Windows V7.2. [...] 23832 36 26_Re: REPORT RECOVERY output16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 22 May 2001 09:46:40 +0530669_- YES, it is. But i get a feeling this is true provided SYSCOPY is not played around with manually(not checked this one).

Regards Sanjeev

> -----Original Message----- > From: Toppins, Smike [SMTP:smike.toppins@GWL.COM] > Sent: Tuesday, May 22, 2001 4:58 AM > To: DB2-L@RYCI.COM > Subject: REPORT RECOVERY output > > Is the output from the REPORT utility guaranteed to be in TIMESTAMP > sequence. Couldn't find the answer in the Utility Guide. > > > SMike Toppins > Great-West Life > smike.toppins@gwl.com > (303) 737-5094 > > ================================================ > To change your subscription options or to cancel your subscription visit [...]