1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2001, week 1 2 56 0_18_Blumer Hans-Ulrich32_Hans-Ulrich.Blumer@WINTERTHUR.CH30_Thu, 1 Nov 2001 07:57:26 +0100389_iso-8859-1 Hi everybody



How can we avoid a sort in the following simplified example?

Let's assume there is a big table (20 million rows) and we have to select all of them. We want them to be sorted.

Table TAB1 Columns COL1, COL2, COL3, COL4 Clustering Index COL1, COL2

Select statement:

Select COL1, COL2, COL3 from TAB1 order by COL1, COL2 [...] 59 25 34_IMS/DC to CICS Migration - "ImstC"10_Tony Monks22_monks_tony@HOTMAIL.COM30_Thu, 1 Nov 2001 10:22:44 +0000694_- If you need to migrate your IMS/DC Application programs to run under CICS with minimal or no source modification, see our new product ImstC

For the fact sheet on ImstC please see: http://www.circle-group.com/software/softwareopeningpage.htm

Tony Monks

tony@circle-group.com



_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.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. 85 79 41_Re: new to stored procedures - need help.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Thu, 1 Nov 2001 16:22:43 +0530375_- Hi Susan /Arvind /others,

My current concern is to create/run a SQL SP, which would mainly contain SQLs and may be a few IF-THEN logic. This is how I plan to go about it : 1. Execute 'Create procedure' stmt under QMF or SPUFI to register its existence in the catalog tables...sysibm.sysparms, sysibm.sysroutines, sysibm.sysroutineauth (as I am on DB2 Ver 6.1). [...] 165 120 34_AW: RUNSTATS - to do or not to do?12_Herber, Eric22_Eric.Herber@DREGIS.COM30_Thu, 1 Nov 2001 09:12:26 +0100474_iso-8859-1 das geht so ein bisschen in unsere "update statistics"-loesung innerhalb der toolbox.

runstats sollte also gezielt angewandt werden und nicht global auf alle tabellen einer datenbank, sondern nur auf tabellen so wie die anzahl datensaetze oder evtl. die verteilung ueber einen bestimmten prozentsatz hinaus veraendert hat.

alles andere wuerde immer wieder die gleichen statistiken sammeln und erhoehte cpu-leistung und i/o-contention bedeuten. [...] 286 145 34_Re: RUNSTATS - to do or not to do?15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 1 Nov 2001 12:55:56 +0100466_iso-8859-1 Hello Eric,

Could you translate this into English for the people who arent familiar with the German language?

Thanx in advance,

Jaap Slot

-----Original Message----- From: Herber, Eric [mailto:Eric.Herber@DREGIS.COM] Sent: Thursday, November 01, 2001 9:12 AM To: DB2-L@RYCI.COM Subject: AW: RUNSTATS - to do or not to do?



das geht so ein bisschen in unsere "update statistics"-loesung innerhalb der toolbox. [...] 432 84 0_18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Thu, 1 Nov 2001 17:40:22 +0530637_- Hi Hans,

Considering the query and index(col1 + col2) there is no way you can avoid a sort. DB2 would definitely do a tablespace scan and employ sequential prefetch(32 pages at a time). The order by would force a db2 sort.

regards, Rajendra.



>From: Blumer Hans-Ulrich >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Date: Thu, 1 Nov 2001 07:57:26 +0100 > >Hi everybody > > >How can we avoid a sort in the following simplified example? > >Let's assume there is a big table (20 million rows) and we have to select >all of them. >We want [...] 517 205 34_AW: RUNSTATS - to do or not to do?12_Herber, Eric22_Eric.Herber@DREGIS.COM30_Thu, 1 Nov 2001 13:36:55 +0100480_iso-8859-1 Ooh,

this was a mistake. I didn't want to send that comment to this group.

However it was a comment to a collegue of me. We work with informix databaseservers and there is a command "update statistics..." which is comparable to the DB2 "runstats".

The comment was that it's the same with informix. If the number of rows or the distribution did not change much, a new update statics would not be useful, just a waste of cpu- and i/o-resources. [...] 723 98 17_Re: JDBC vs SQLJ?9_Mike Deer20_Michael.Deer@UAL.COM30_Thu, 1 Nov 2001 06:51:05 -0600314_- I also would prefer to see more SQLJ applications than I currently do. The Java development community does not seem to have fully embraced SQLJ. From my experience, most Java developers will show a strong bias towards JDBC. They tend to want to go with what they know best - and 9 times out of 10 its JDBC. [...] 822 52 33_Cluster ratio and avoiding a sort18_Blumer Hans-Ulrich32_Hans-Ulrich.Blumer@WINTERTHUR.CH30_Thu, 1 Nov 2001 13:52:46 +0100367_iso-8859-1 Hi everybody This text is identical to my message without subject. Excuse me please!

How can we avoid a sort in the following simplified example?

Let's assume there is a big table (20 million rows) and we have to select all of them. We want them to be sorted.

Table TAB1 Columns COL1, COL2, COL3, COL4 Clustering Index COL1, COL2 [...] 875 29 28_DB2 Connect vs Shadow Direct18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Thu, 1 Nov 2001 08:21:26 -0500423_iso-8859-1 Anyone have any thoughts or comments on the above subject? We are currently using DB2 Connect V6.2 and we're running DB2 V6 on OS/390. I received some literature on Shadow Direct and I was wondering if anyone out there has done any comparison shopping. Ie; anyone go from DB2 Connect to Shadow Direct and why or maybe the other way around? Just looking for any thoughts as to if this is worth looking into. [...] 905 45 27_Re: SAS layouts for DB2 SMF12_Alex Andrade22_alexdandrade@YAHOO.COM30_Thu, 1 Nov 2001 06:24:06 -0600604_- Compliments of a mate i have in SAS UK :

mxg 18.11 for DB2 v7 and 18.01 for DB2 v6



PS Recently out of work DB2 Consultant looking for work in UK, very user friendly







On Wed, 31 Oct 2001 10:04:10 -0500, Pearson, Eric L, wrote:

>Anyone have the SAS definitions for DB2 SMF records? > >All that is supplied with DB2 is the assembler macros >for the DSECTs. I would like to have something a bit >closer to SAS. If all I have available is the assembler >DSECTS, it might be quicker to write the reports in >assembler [...] 951 40 24_Re: Cost of DB2 and CICS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 1 Nov 2001 08:29:01 -0500350_ISO-8859-1 Maha, The level of information you gave does not even begin to provide enough information for a cost estimation. Cost estimations require DETAILED information about an application, the individual high volume transactions, how much work they will do and how, and peak transaction loads (per minute, per hour) - not a number per month. [...] 992 71 13_Trigger Error10_praveen_kj19_praveen_kj@INFY.COM30_Thu, 1 Nov 2001 19:42:24 +0530391_iso-8859-1 Hi All,

I am getting an error while executing trigger.

The trigger is operating on table a,b,c.

1.) Tables were created as follows.

create table a (a integer); create table b (b integer,e integer); create table c (c integer, d integer);



2.) In Tables Values were filled.

insert into a values (1); insert into c values (2,2); [...] 1064 42 34_Re: RUNSTATS - to do or not to do?14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM30_Thu, 1 Nov 2001 08:44:11 -0600519_iso-8859-1 I've never done this in my legecy environment. But for SAP tables that have no data they recommend the following:

UPDATE SYSIBM.SYSTABLES SET CARDF=-1, NPAGES=-1, PCTROWCOMP=-1 WHERE CREATOR='SAPR3' AND NAME IN ('tablea','tableb','tablec');

UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF=0, NACTIVE=0 WHERE CREATOR='SAPR3' AND NAME IN ('tblspa','tblspb','tblspc');

UPDATE SYSIBM.SYSTABSTATS SET CARD=-1, CARDF=-1, NPAGES=-1 WHERE OWNER='SAPR3' AND TSNAME IN ('tblspa','tblspb','tblspc'); [...] 1107 59 34_Remigration after fallback to v5.122_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM30_Thu, 1 Nov 2001 09:50:42 -0500464_iso-8859-1 We migrated to DB2 v6.1 successfully but had an error show up the next day that forced us to fallback to v5.1. The catalog remains at v6.1. We are now preparing to remigrate back to DB2 v6.1. Does anyone have any experiences they'd like to share or is there anything special we need to watch out for? A side note, no new updates have been made to SYSIBM.SYSPROCEDURES so we don't see a need to rerun the CATMAINT job. Thanks in advance for all responses 1167 39 31_Re: DB2 active log size effects16_Lemaire, Nigel B21_nigel.lemaire@EDS.COM30_Thu, 1 Nov 2001 09:13:20 -0600594_- Another consideration for Active log size is that whenever a "active log switch" happens the Subsystem takes a Checkpoint. This has the effect of quiescing all activity until the Checkpoint processing completes. While investigating why my Subsystem Checkpoint frequency was so high, I discovered this. Frankly, I don't know what the concern is regarding having a active log on a single tape. I have 12 full volume logs, 6/6 on a very active Subsystem. My greatest concern is NEVER have DB2 wait for a tape mount to archive an active log because all the current active logs are full (bad [...] 1207 160 24_Re: Cost of DB2 and CICS14_Philip Gunning24_philip.gunning@QUEST.COM30_Thu, 1 Nov 2001 07:52:16 -0800458_iso-8859-1 Maha, As Joel states, you really need to get these estimates down to trans per sec, peak times, etc. 1 million transactions per day is nothing for a CICS region to handle, many companies put much more than that through CICS per day, so given the resources, CICS can easily handle the load. Note, I said given the resources. If your system is resource constrained and paging, CICS response will degrade quickly. Same is true for DB2. HTH Phil [...] 1368 88 16_Re: 13_Mohammad Khan20_mkkhan88@HOTMAIL.COM30_Thu, 1 Nov 2001 10:06:08 -0600475_- Hi Rajendra I'd beg to differ. Either access path can be taken, via index when sort won't be required and tablespace scan when it will be. A high cluster ratio would favor access via index and so will the OPTIMIZE FOR ( small number ) ROWS. A point of caution, do not use this OPTIMIZE FOR trick on a unclustered table when you want a large number of rows, a tablespace scan + sort can easily outperform the large number of sync reads that the other path would incur. [...] 1457 22 10_SMS groups3_K T35_Kaushal.Trivedi@VERIZONWIRELESS.COM30_Thu, 1 Nov 2001 11:46:03 -0500408_iso-8859-1 Hi All, from reading some archives , I am seeing that some shops that have implemented SMS for their DB2 , have created groups based on sizes of the objects , can anyone explain the reasoning behind this, should'nt it be like , performance based, like the books read, or does it have to do with the kind of DASD we might be using , if so do let me know so I can gather info on those as well, [...] 1480 20 30_DB2 Developer Client and OS39012_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Thu, 1 Nov 2001 11:12:00 -0500323_- I have downloaded the DB2 Developer Client and am attempting to configure a connection to OS/390. The DB2 Connect Client Assistant included does not allow a direct connection to OS/390. Do I have to install DB2 Connect Personal Edition? Can I install it (DB2 Connect PE) over/after the current DB2 Developer Client? [...] 1501 83 38_Re: Remigration after fallback to v5.112_Chao Terry F20_Terry.F.Chao@IRS.GOV30_Thu, 1 Nov 2001 12:29:50 -0500709_iso-8859-1 Just follow the steps in the installation manual, we've recently done it, falled back to V5.1 and remigrated the next day. GLitchless.



Terry Chao (202)283-6359





-----Original Message----- From: Kennedy, Tom - Cendant [mailto:Tom.Kennedy@CENDANT.COM] Sent: Thursday, November 01, 2001 9:51 AM To: DB2-L@RYCI.COM Subject: Remigration after fallback to v5.1





We migrated to DB2 v6.1 successfully but had an error show up the next day that forced us to fallback to v5.1. The catalog remains at v6.1. We are now preparing to remigrate back to DB2 v6.1. Does anyone have any experiences they'd like to share or is there anything special we [...] 1585 42 14_Re: SMS groups16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 1 Nov 2001 12:29:39 -0500651_iso-8859-1 group by size is fairly common for Non-DB2. I've never heard of it for DB2.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: K T [mailto:Kaushal.Trivedi@VERIZONWIRELESS.COM] Sent: Thursday, November 01, 2001 11:46 AM To: DB2-L@RYCI.COM Subject: SMS groups



Hi All, from reading some archives , I am seeing that some shops that have implemented SMS for their DB2 , have created groups based on sizes of the objects , can anyone explain the reasoning behind this, should'nt it be like , performance based, like the books read, or does it have to do with the kind of DASD [...] 1628 119 34_Re: DB2 Developer Client and OS39014_Philip Gunning24_philip.gunning@QUEST.COM30_Thu, 1 Nov 2001 09:36:26 -0800573_iso-8859-1 Bob, If you don't want to use an EE gateway, then you need PE on your workstation. HTH Phil

-----Original Message----- From: BOB JEANDRON To: DB2-L@RYCI.COM Sent: 11/1/01 8:12 AM Subject: [DB2-L] DB2 Developer Client and OS390

I have downloaded the DB2 Developer Client and am attempting to configure a connection to OS/390. The DB2 Connect Client Assistant included does not allow a direct connection to OS/390. Do I have to install DB2 Connect Personal Edition? Can I install it (DB2 Connect PE) over/after the current DB2 Developer Client? [...] 1748 28 15_OS390 to Oracle11_Todd Martin15_tm9us@YAHOO.COM30_Thu, 1 Nov 2001 09:47:29 -0800379_us-ascii Hi All,

I have a question on OS390 to oracle .By submitting JCL job can we connect to Oracle ??? If we know the Oracle ip address and port number .If yes can anyone send me the sample JCL ??? I want to know any load libs shld be included in STEPLIB etc etc ...Any environment variables shld be setup in JCL ??? So many questions ..So help me out in this ... [...] 1777 40 34_Re: DB2 Developer Client and OS39017_Nicholson, Martin20_MNicholson@USSCO.COM30_Thu, 1 Nov 2001 12:14:25 -0600603_- I have always wondered why IBM insists on calling DB2 for OS/390 a member of the DB2 UDB family when you cannot connect to it with the CAE alone. It is almost as bad as Microsoft calling its NT failover capabilities clustering!

> -----Original Message----- > From: BOB JEANDRON [SMTP:BOB.JEANDRON@USDA.GOV] > Sent: Thursday, November 01, 2001 10:12 AM > To: DB2-L@RYCI.COM > Subject: DB2 Developer Client and OS390 > > I have downloaded the DB2 Developer Client and am attempting to configure > a > connection to OS/390. The DB2 Connect Client Assistant included does not > allow a direct [...] 1818 46 19_Re: OS390 to Oracle17_Nicholson, Martin20_MNicholson@USSCO.COM30_Thu, 1 Nov 2001 12:22:07 -0600568_- You must have SQLNet running on OS/390.

We run with the Oracle .CMDLOAD in the joblib, //TNSNAMES DD and //ORA$ENV DD in the step.

We are currently running an old version of SQLNet to access Oracle 8.1.5 and 8.1.6 on Windows NT.

> -----Original Message----- > From: Todd Martin [SMTP:tm9us@YAHOO.COM] > Sent: Thursday, November 01, 2001 11:47 AM > To: DB2-L@RYCI.COM > Subject: OS390 to Oracle > > Hi All, > > I have a question on OS390 to oracle .By submitting > JCL job can we connect to Oracle ??? If we know the > Oracle ip address and [...] 1865 60 40_Re: Online Reorg & 100% availability (Z/64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Thu, 1 Nov 2001 13:00:09 -0600487_ISO-8859-1 Hello Bruno,

CDB Software has the only truely 100% available Online Reorg product on the market. The triggering mechanism you mentioned is built into the product. I will put you in contact with some of our references off-list.





Todd Burch CDB Software.



-----Original Message----- From: bruno.vandeneeden@FORTISAG.BE Sent: Wednesday, October 31, 2001 5:58 AM To: DB2-L@RYCI.COM Subject: Online Reorg & 100% availability (Z/OS D [...] 1926 26 49_Question about OS/390 DB2 Recovery option LOGONLY0_20_John_Lendman@FPL.COM30_Thu, 1 Nov 2001 14:26:03 -0500427_us-ascii Does anyone know for sure if you use the LOGONLY option of recovery, does the indexes also get "recovered/updated" as the data is reapplied to the tables, or do you have to run a separate rebuild index job?

It seems to me that in a test I did with LOGONLY recovery,and after restoring back to a "flashcopy" and I did not rebuild the indexes and I did not receive any errors when accessing the data in DB2. [...] 1953 22 20_DDF HANGS - OS390 V615_Neff, Stephen R16_NeffSR@STATE.GOV30_Thu, 1 Nov 2001 14:38:19 -0500494_iso-8859-1 Hello, Most recently our system DBA did a -STOP DDF MODE (FORCE). Before doing so he checked for any distributed threads and found none. However the command has not stopped DDF. It has de-activated DB2 DDF functionality. Efforts to stop/start DDF have not worked. It appears the only way to get DDF re-started is to bounce the DB2 subsystem. This can only happen once a week in our environment. Does anyone know why this command does not work sometimes. We are on DB2 OS390 V6. [...] 1976 90 17_Re: Trigger Error10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV30_Thu, 1 Nov 2001 14:55:41 -0500477_iso-8859-1 The -551 is authority problems. See your DBA or security admin to grant you the privilege to do the function.



______________________________ Reply Separator _________________________________ Subject: Trigger Error Author: owner-db2-l@RYCI.COM at INTERNET Date: 11/1/01 10:03 AM



Hi All,

I am getting an error while executing trigger.

The trigger is operating on table a,b,c.

1.) Tables were created as follows. [...] 2067 90 40_DB2 DBA Career Opportunity at Lands' End20_Bustamente, Gerald G30_Gerald.Bustamente@LANDSEND.COM30_Thu, 1 Nov 2001 13:54:01 -0600617_iso-8859-1 Welcome to the Midwest.

This is an opportunity for a DB2 Database Administrator who is looking to make a real contribution to the bottom line and become a part of our continuing success. We are looking for someone interested in a career, not just a job.

Responsibilities include:



a. Working closely with the Data Administrator and other internal customers to physically implement logical data models to include: consultation, backup and recovery, security, optimization, maintenance, problem determination and resolution as well as exploitation of features and functions. [...] 2158 46 49_Problem with a Trigger calling a Stored Procedure10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Thu, 1 Nov 2001 15:06:16 -0500389_us-ascii I have to create a "simple" trigger which calls a Stored Procedure. The basic plan is that whenever a specific column gets updated, the trigger should fire so that the update can get propagated to another DB2 subsystem. I want to pass the key information, the date column which is being updated, the SSID where the change should be made and the qualifier on the table there. [...] 2205 29 75_Webcast: Plug into An Alternative to the IBM DB2 version 7 Utili ty Bundles13_Thomas, Janis19_Janis.Thomas@CA.COM30_Thu, 1 Nov 2001 15:23:34 -0500491_iso-8859-1 Find out how CA's Unicenter Database Management Enhanced Operational Utilities Suite, and Enhanced Diagnostic and Recovery Suite, replace IBM native tools with faster response, advanced technology, tighter integration, and decreased CPU usage.

Wednesday, November 21st, 11:00 AM ET

Presenter: David Schipper, Director of DB2 and IMS Product Management, Computer Associates

Audience: DB2 for z/OS and OS/390 Database Administrators and System Programmers [...] 2235 12 39_Help needed ASAP - DB2 Connect Question13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Thu, 1 Nov 2001 15:48:18 -0500 2248 63 53_Re: Problem with a Trigger calling a Stored Procedure10_Shery Hepp17_schepp@SRPNET.COM30_Thu, 1 Nov 2001 13:49:44 -0700395_iso-8859-1 Mark- what error are you getting and what do you mean by 'we keep getting caught up with cols not matching'? You need to define your stored proc with all IN parms.

Shery

-----Original Message----- From: Mark Labby [mailto:mlabby@AESSUCCESS.ORG] Sent: Thursday, November 01, 2001 1:06 PM To: DB2-L@RYCI.COM Subject: Problem with a Trigger calling a Stored Procedure [...] 2312 43 24_Re: DDF HANGS - OS390 V617_Nicholson, Martin20_MNicholson@USSCO.COM30_Thu, 1 Nov 2001 16:00:57 -0600601_- Are you running under SNA or TCP?

If SNA, try varying the nodes inactive and then active again.

If TCP, good luck.

Did you do -DIS THREAD(*) LOC(*) TYPE(*)?

> -----Original Message----- > From: Neff, Stephen R [SMTP:NeffSR@STATE.GOV] > Sent: Thursday, November 01, 2001 1:38 PM > To: DB2-L@RYCI.COM > Subject: DDF HANGS - OS390 V6 > > Hello, > Most recently our system DBA did a -STOP DDF MODE (FORCE). > Before > doing so he checked for any distributed threads and found none. However > the > command has not stopped DDF. It has de-activated DB2 DDF functionality. [...] 2356 42 53_Re: Question about OS/390 DB2 Recovery option LOGONLY17_Nicholson, Martin20_MNicholson@USSCO.COM30_Thu, 1 Nov 2001 16:02:59 -0600608_- If you recover to "current", why would you have to build indexes?

If you recover to "point in time", your indexes are more current than your data.

> -----Original Message----- > From: John_Lendman@FPL.COM [SMTP:John_Lendman@FPL.COM] > Sent: Thursday, November 01, 2001 1:26 PM > To: DB2-L@RYCI.COM > Subject: Question about OS/390 DB2 Recovery option LOGONLY > > Does anyone know for sure if you use the LOGONLY option of recovery, does > the indexes also get "recovered/updated" as the data is reapplied to the > tables, or do you have to run a separate rebuild index job? > > It seems [...] 2399 63 33_Re: DB2 Connect vs. Shadow Direct10_Dale Smock20_Dale.Smock@BMSUS.COM30_Thu, 1 Nov 2001 17:22:42 -0500548_iso-8859-1 We run both for two different businesses with different needs.

DB2 Connect is used by Sales & Marketing staff that wanted to get DB2 data downloaded to their desktops and laptops into Excel and Access, without a major expense for additional software on the mainframe or their PC's. We have Enterprise Edition with 10 concurrent user license on a NT server, to support access by 200+ users running the client on Windows. It was a relatively low cost solution for selectively downloading hundreds to thousands of rows to PC's. [...] 2463 23 23_DSNWZP No Data Returned11_Henry Boone16_hboone@GEICO.COM30_Thu, 1 Nov 2001 16:56:58 -0600568_- I usually get no data returned when I call the DSNWZP stored procedure. I've attempted this with both Visual Explain and the REXX procedure I got from this archive. The one exception is when I have an OMEGAMON performance trace running.

From searching this archive, it sounds like the IFCID 0106 records contain the data that DSNWZP needs. For the DB2 subsystem I'm trying this with, we have the 106's being cut to SMF for the STATISTICS CLASS 1 and ACCOUNTING CLASS 1 traces. They are also being cut for the MONITOR CLASS 1 traces but going to OP1. The [...] 2487 18 50_Help needed ASAP - DB2 Connect question - 2nd try!13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Thu, 1 Nov 2001 18:41:09 -0500 2506 61 54_Re: Help needed ASAP - DB2 Connect question - 2nd try!16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Thu, 1 Nov 2001 19:34:49 -0500376_us-ascii

http://www-4.ibm.com/software/data/db2/skills/offer1.html







Carlton Enuda on 11/01/2001 06:41:09 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Help needed ASAP - DB2 Connect question - 2nd try!

2568 39 14_DB2L-Documents20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM28_Thu, 1 Nov 2001 20:29:59 EST120_US-ASCII Hi All, I am looking for the DB2-L-Documents site. Can anyone help.

Regards, Ed.





2608 97 37_Re: Cluster ratio and avoiding a sort10_John Hardy25_john_hardy@ALLIANZ.COM.AU30_Thu, 1 Nov 2001 18:34:46 -0600414_- There are obviously a lot of 'it depends' in answering this question. Nevertheless...

Whatever you do, your data will be '100% sorted' by COLUMN1 and COLUMN2. This is guaranteed, as you have specified 'ORDER BY'.

This query will most likely be satisfied in one of two ways:

1. Via a non-matching index scan on the clustering index with Sequential Prefetch of both Index and Data pages. [...] 2706 60 40_Re: Online Reorg & 100% availability (Z/13_John Davidson21_jdadmin@IDATATECH.NET30_Thu, 1 Nov 2001 20:31:29 -0500 2767 18 31_RI in Data Warehouse DB2 Tables0_20_bjnigh@HOUSEHOLD.COM30_Thu, 1 Nov 2001 17:43:19 -0800353_us-ascii I heard that Richard Yevich spoke at the DB2 Technical Conference in Orlando on DB2 performance. We are considering adding RI to our data warehouse tables. Did Richard mention something regarding this topic during his performance session? I can't seem to find anything in the session handout that relates RI to the data warehousing world. [...] 2786 56 16_Access to tables11_Gus Butorac25_gbutorac@WORLDNET.ATT.NET30_Thu, 1 Nov 2001 20:19:21 -0600325_iso-8859-1 I know I've seen this before, but what I need is a query which will tell me who can access a specific table. The user wants to send out a message to all users who can access his table, to notify them of changes.

I believe it was a join of sysdbauth, systabauth, syscolauth, sysplanauth and sysplandep. [...] 2843 92 18_Re: DB2L-Documents14_Philip Gunning24_philip.gunning@QUEST.COM30_Thu, 1 Nov 2001 18:51:02 -0800368_iso-8859-1 Go to http://jupiter.ryci.com/archives/db2-l-documents.html.

Phil



Assoc List Owner

-----Original Message----- From: Edward C. Benoit Jr. To: DB2-L@RYCI.COM Sent: 11/1/01 5:29 PM Subject: [DB2-L] DB2L-Documents

Hi All, I am looking for the DB2-L-Documents site. Can anyone help.

Regards, Ed.





2936 129 37_Re: Cluster ratio and avoiding a sort13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Thu, 1 Nov 2001 21:24:24 -0600399_iso-8859-1 Ulrich,

In addition to John's excellent answer to your questions, the major point I would like to make is that the optimizer is extremely unlikely to use an Index + data access path if 100% of the data is going to be returned.

The rough figure thrown around is that if greater than 25% of the data is to be retrieved, then it is more efficient to do a tablespace scan. [...] 3066 57 41_Stored Procedure Calling Stored Procedure23_Bhuvaneswari Jaganmohan19_j_bhuvana@YAHOO.COM30_Thu, 1 Nov 2001 19:23:19 -0800409_us-ascii Hi,

I have a stored procedure A that returns result sets. There is a stored procedure B that returns one out parameter. These stored procedures independently work fine and give the expected results. i.e. The SQLCODE returned in the calling program after the call to Stored Procedure A is +466 . The Sqlcode returned in the calling program after the call to Stored procedure B is Zero(0). [...] 3124 39 35_Re: RI in Data Warehouse DB2 Tables12_Susan Lawson24_susan_lawson@YLASSOC.COM30_Thu, 1 Nov 2001 21:55:44 -0600423_us-ascii During our presentation at the Tech Conference we discussed pro/cons of RI. But no concerns were discussed in regards to a data warehouse environment.

Regards, Susan Lawson

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of bjnigh@HOUSEHOLD.COM Sent: Thursday, November 01, 2001 7:43 PM To: DB2-L@RYCI.COM Subject: RI in Data Warehouse DB2 Tables [...] 3164 19 311_Richard Gravel/Operations/ScotiabankGroup is out of the office. I am out of the office on vacation. For AC-HBP Project support, please contact Bruce Kenning @ 416.288-4805 For ISFE-MF Print Function Post-implementation issues, see Diana Chan. All other issues, please contact Joseph Lau at 416.288-3692. Thanks!14_Richard Gravel29_richard.gravel@SCOTIABANK.COM30_Fri, 2 Nov 2001 02:33:52 -0500365_us-ascii I will be out of the office from 10/26/2001 until 11/12/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. 3184 75 51_Trigger Error (SPUFI OK ,from command centre Error)10_praveen_kj19_praveen_kj@INFY.COM30_Fri, 2 Nov 2001 14:14:31 +0530391_iso-8859-1 Hi All,

I am getting an error while executing trigger.

The trigger is operating on table a,b,c.

1.) Tables were created as follows.

create table a (a integer); create table b (b integer,e integer); create table c (c integer, d integer);



2.) In Tables Values were filled.

insert into a values (1); insert into c values (2,2); [...] 3260 26 25_Help with creating a view11_Judy Kibler21_judith.kibler@TRW.COM30_Fri, 2 Nov 2001 05:14:07 -0600368_us-ascii Hello all,

I am trying to create a view on a table that has appoximately 4 million rows. This view would need to limit the amount of data returned by selecting rows based on dates in the table and whether they are greater than the current date. Is there a function I can use to return current date in the view creation select clause? For example: [...] 3287 86 53_Re: Question about OS/390 DB2 Recovery option LOGONLY0_20_John_Lendman@FPL.COM30_Fri, 2 Nov 2001 07:10:17 -0500291_us-ascii The case I am talking about is at a DR site. So when we use LOGONLY, we recover to the last log that we have at the DR site. In most every case this will not be to current. So my question still stands>

John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413 [...] 3374 57 29_Re: Help with creating a view11_David Nance16_DWNance@FHSC.COM30_Fri, 2 Nov 2001 07:33:57 -0500337_US-ASCII Judith, Do you mean you would like to have a column that contains the current date in your view? If so, just add it to your select list.

db2 "create view ABC as select field1, field2, date1, current date from YYY.ZZZ where date1 > {current date}"



Dave Nance First Health Services, Corp. (804)527-6841 [...] 3432 194 29_Re: Help with creating a view0_24_Bruce.Williamson@PSIR.IE30_Fri, 2 Nov 2001 12:39:47 -0000450_windows-1252 Howzit Judy?

You're spot on,

create view ABC as select field1, field2, date1 from YYY.ZZZ where date1 > current date;

You could also specify:

where date1 > current date - 10 days /* or 1 month etc..

HTH

Cheers Bruce



-----Original Message----- From: Judy Kibler [mailto:judith.kibler@TRW.COM] Sent: 02 November 2001 11:14 To: DB2-L@RYCI.COM Subject: Help with creating a view [...] 3627 121 57_Online REORG Mapping Table using Declared Temporary Table0_24_Bruce.Williamson@PSIR.IE30_Fri, 2 Nov 2001 12:52:55 -0000427_iso-8859-1 Howzit Folks?

This is a bit of an off the wall question and may fall in the category of "not using product as intended". I've scoured the archives and can't find any topics related to it. So here goes:

Would it be possible to use a declared temporary table for the mapping table during an online REORG? If it's possible, would there be any benefit? Reduce locking, logging, catalog updates, etc.. [...] 3749 59 24_Re: DDF HANGS - OS390 V615_Neff, Stephen R16_NeffSR@STATE.GOV30_Fri, 2 Nov 2001 08:04:36 -0500422_iso-8859-1 We are running TCP/IP. The system DBA says he did a display thread prior to the stop. I do not know specifically what syntax he used. If there no threads shown from the -DIS THREAD(*) LOC(*) TYPE(*) is it safe to STOP DDF?

-----Original Message----- From: Nicholson, Martin [mailto:MNicholson@USSCO.COM] Sent: Thursday, November 01, 2001 4:51 PM To: DB2-L@RYCI.COM Subject: Re: DDF HANGS - OS390 V6 [...] 3809 23 21_-905 user block limit13_Walter Davies26_wdavies@CO.EL-DORADO.CA.US30_Fri, 2 Nov 2001 06:13:19 -0800531_iso-8859-1 Hi List: I have a programmer who is getting a -905 with resource 'user block ' limit '10'. She is programming in visual basic using the merant obdc DB2 driver to get to Db2 v5 on os390. By looking at display threads I see she fails when she hits ten open threads. She tries to close them in her program but is unsuccessful because she is using a visual control to access the data. Does any on know how to get a visual basic control to release it's thread or as another solution I can increase the number of threads [...] 3833 25 34_Regarding order of Column in DDL .10_praveen_kj19_praveen_kj@INFY.COM30_Fri, 2 Nov 2001 20:22:42 +0530334_iso-8859-1 Hi All,

Does the position of various columns in the DDL of Table matters ? Do some care has to be taken regarding positioning of frequently updated columns and varchar Columns? Does the above mentioned issues have some relevance in terms of DB2 logging.

Any pointers in this regard would be appreciated. [...] 3859 29 29_Thanks, and another question!11_Judy Kibler21_judith.kibler@TRW.COM30_Fri, 2 Nov 2001 09:05:54 -0600537_us-ascii Hello again,

Thanks to all who responded to my somewhat embarrassing question about how to return current date in an SQL statement! I'm going to chalk that one up to being up all night!

OK, one more question and then I will quit bothering you folks! Now I have a column in a table that is a character data type with a length of 1. I believe what is stored in there is possibly like a bitmap or something. Anyway, I can wrap the HEX function around the field and it returns the value as 1F, for instance. If I [...] 3889 140 37_Re: Cluster ratio and avoiding a sort13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Fri, 2 Nov 2001 09:27:09 -0600554_iso-8859-1 One other thing that I think is probably worth mentioning -- the original post mentioned this was a "big table" of 20 million rows. If this is the case (assuming a decent sized row length), sorting this in DB2 seems like it would be a good way to get a -904 sqlcode (00C900A5) on 4k sort space (which has an impact on everyone performing a DB2 sort in that subsystem). When I come across the situation where someone really wants to read the entire contents of a "big table" and sort the rows, I usually suggest unloading the table, using [...] 4030 37 38_Re: Regarding order of Column in DDL .11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM30_Fri, 2 Nov 2001 15:27:34 +0000393_us-ascii Praveen

As a general recommendation, you should consider placing all of your frequently updated columns together at the end of the table. Immediately prior to these should be any variable length fields. This will minimize both the amount of logging DB2 has to do, and the CPU overhead in calculating the start and end of columns following a variable length field. Regards [...] 4068 50 38_Re: Regarding order of Column in DDL .16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 2 Nov 2001 10:25:47 -0500427_iso-8859-1 DB2 logs from the first changed column to the last. Thus, position of columns is relevant. Frequently updated columns should be last in the table. I think that if the length of a varchar is changed, DB2 must log from that column to the end of the row. So maybe a better answer is: Place updated variable columns last. Place updated fixed columns next to last. Place together columns which are updated together. [...] 4119 125 53_Re: Question about OS/390 DB2 Recovery option LOGONLY15_Murley, Michael22_Michael_Murley@BMC.COM30_Fri, 2 Nov 2001 10:25:28 -0600571_iso-8859-1 John,

If you restore the table spaces but do not restore the indexes, there will be no index data sets at the DR site, so clearly you will have to rebuild them.

If you restore the table spaces and the indexes, but then apply log records to the table spaces, they will no longer be consistent with the indexes, so you will either have to rebuild the indexes or also recover them with LOGONLY. You can use LOGONLY on COPY YES indexes if you are limited to the standard DB2 Recovery utility. BMC RECOVER PLUS for DB2* supports LOGONLY recovery [...] 4245 29 69_Quick question about V7 AND supplied utilities (I know, another time)10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 2 Nov 2001 17:28:26 +0100545_us-ascii Estimeed list

Unfortunately today our web server is down so I cannot search this topic in DB2-L archives. I apologize for this, but I need this info, otherways my boss will fire me (that is he'll strike a match and he'll fire me as a Yule log).

Now the question:

I've ever heard that V7 will not provide ANY utility. But this means NO utility at all ? Or only some utilities are not provided but some basic utility like IC (for catalog tablespace) are still 'enclosed' ? No DSN1COPY ? No DSNJU003 ? DSN1PRNT ? [...] 4275 199 38_Re: Regarding order of Column in DDL .9_Mark Ruhe19_Mark.Ruhe@QUEST.COM30_Fri, 2 Nov 2001 08:44:06 -0800386_iso-8859-1 Eric,

I believe that the logging of updated columns was change in version 6. Prior to that release DB2 logged all changes between first and last columns. Since 6 only the columns updated are logged. I heard this at a technical conference but I have not verfied this in documentation. Can you point me to the documentation in version 6 or 7 that contradicts this? [...] 4475 29 37_Re: Cluster ratio and avoiding a sort13_Mohammad Khan20_mkkhan88@HOTMAIL.COM30_Fri, 2 Nov 2001 10:02:58 -0600362_- Hi Terry I've a question here.

On Thu, 1 Nov 2001 21:24:24 -0600, Terry Purcell wrote:

> >In addition to John's excellent answer to your questions, the major point I >would like to make is that the optimizer is extremely unlikely to use an >Index + data access path if 100% of the data is going to be returned. > [...] 4505 15 23_Re: ASCII on the OS/39012_Roger Miller19_millerrl@US.IBM.COM30_Fri, 2 Nov 2001 10:32:11 -0600539_- There are many different ASCII CCSIDs, just as there are many different EBCDIC CCSIDs. If the CCSIDs are not the same, then there is translation. There is an appendix in the Installation Guide titled Character Conversion that is required reading for this topic.

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. 4521 13 46_Re: Benefits of Z/OS and/or Zseries 900 Server12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 2 Nov 2001 10:35:47 -0600404_- This is an excellent match for chapter 10 of SG24-6129, Version 7 Performance Topics. The title is Synergy with host platform.

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. 4535 59 38_RLST - no rows but IO to RLST datasets10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Fri, 2 Nov 2001 10:42:36 -0600358_us-ascii I was checking on my dynamic SQL litter of mutt-puppies (our in-house fave - can you tell?) and watching the IO against the SYSDBASE and noticed also that there was too much IO against DSNARL01 which is the index for the RLST. There are no rows on the table/index, but runstats haven't been run (trying to get my catalogs reorged for months!). [...] 4595 94 24_Re: DDF HANGS - OS390 V60_16_khampto1@TXU.COM30_Fri, 2 Nov 2001 10:56:21 -0600532_us-ascii We have encountered this problem on an early maintenance level of DB2 v6. Do you know what maint level you are at ? An APAR/PTF fixed this for us, but I don't know the number. It may also require a corresponding fix to OS/390 TCP/IP. Our only way out was to bounce the DB2 subsystem, and more than once we had to kill IRLM F xxxxIRLM,ABEND,NODUMP to get the subsystem down because it seemed to be waiting on a ghost TCP/IP thread that could not be terminated. We are now at DB2 V6, PUT 0107, and OS/390 v2.10, and the [...] 4690 67 73_Re: Quick question about V7 AND supplied utilities (I know, another time)14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Fri, 2 Nov 2001 10:57:54 -0600749_us-ascii These are included with the DB2 product: CATMAINT DIAGNOSE LISTDEF OPTIONS QUIESCE REPAIR REPORT TEMPLATE All standalone utilities

All the other utilites are ALSO distributed but enabled only for the Catalog, unless you purchase the option.



Richard Yevich YL&A < http://www.YLAssoc.com > Richard_Yevich@YLAssoc.com

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Max Scarpa > Sent: Friday, November 02, 2001 10:28 AM > To: DB2-L@RYCI.COM > Subject: Quick question about V7 AND supplied utilities (I know, another > time) > > > Estimeed list > > Unfortunately today our web server is down [...] 4758 190 38_Re: Regarding order of Column in DDL .16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 2 Nov 2001 11:59:30 -0500388_iso-8859-1 Mark, You may be right. I've not visited the logging section of the manaul since our V5 to V7 upgrade. I will do that ASAP.



Regards, eric pearson NS ITO Database Support

-----Original Message----- From: Mark Ruhe [mailto:Mark.Ruhe@QUEST.COM] Sent: Friday, November 02, 2001 11:44 AM To: DB2-L@RYCI.COM Subject: Re: Regarding order of Column in DDL . [...] 4949 75 10_Re: QMF V70_16_khampto1@TXU.COM30_Fri, 2 Nov 2001 11:10:35 -0600561_us-ascii Hi Eric, What sort of locking problems are you referring to ? We have been running QMF against Production systems here sice the first day we had DB2, in 1987. There can be lockouts to CICS transactions, batch jobs, etc. caused by any end-user dynamic SQL tool in existence, not just QMF. In fact, after we set up DDF access, many Windows-based query tools that generate dynamic SQL are much worse than a QMF user coding his own SQL. In a perfect world, it would be nice to not allow users to query production databases that are also being updated [...] 5025 26 73_Re: Quick question about V7 AND supplied utilities (I know, another time)10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 2 Nov 2001 18:12:49 +0100458_us-ascii Hi Richard and thank you to be a fireman.

Your answer - and the answers I obtained from the other members of this newsgroup - are as usual very accurate. And so it must be, considering the quality of listers (I'm the exception who ruins the average,of course).

Now I've to buy a wagon load of Kleenex to dry my boss' tears (he doesn't like to pay more - including my wage :-(( ) as he must budget new expenses for the next years. [...] 5052 60 33_Re: Thanks, and another question!14_Judy Woodfield13_judyw@CAE.COM30_Fri, 2 Nov 2001 11:59:52 -0500328_iso-8859-1 I had the same problem, but only for a limited number of possible values (53). The only way we could resolve it was with a CASE statement: SELECT field1, field2, CASE HEX(T_PERI) WHEN '01' THEN '01' WHEN '02' THEN '02' WHEN '03' THEN '03' WHEN '04' THEN '04' ..... WHEN '35' THEN '53' END, field4 FROM table.... [...] 5113 49 46_Regarding change of sql to improve performance24_Hanumantharao Tatavarthi31_hanumantharao.tatavarthi@DB.COM30_Fri, 2 Nov 2001 18:12:12 +0100275_iso-8859-1 hi list, I have one sql as shown below SELECT COL_A , COL_B , COL_C FROM TAB_A WHERE COL_D NOT IN (SELECT COL_E FROM TAB_B WHERE COL_F = 'EZ748' ) ; which is taking lot of time to execute .... because my TAB_A is a large table and TAB_B is constant table.... [...] 5163 22 47_Copying a multi-volume ARCHLOG data set to DASD11_Joe Bitetto31_joseph.bitetto@US.PWCGLOBAL.COM30_Fri, 2 Nov 2001 10:58:12 -0600539_- At disaster recovery I copy ARCHLOG data sets from tape to DASD and then using DSNJU003 (Change Log Inventory) I will DELETE and add the NEWLOG information to the BSDS. My dilemma is when an ARCHLOG spans more than 1 tape volume the BSDS records the ARCHLOG data set multiple times with a different START RBA/TIME and END RBA/TIME for each tape volume. When I copy the a multi-volume ARCHLOG data set to DASD can I use the START RBA of the 1st volume and the END RBA of the last volume and register just 1 occurrence of the ARCHLOG [...] 5186 77 14_Re: SMS groups0_16_khampto1@TXU.COM30_Fri, 2 Nov 2001 11:19:27 -0600549_us-ascii We group our DB2 datasets into SMS pools based on size of primary allocation, the dividing line is about 50 Meg, the DASD folks' logic is (I think) that if a volume contains all large datasets, there will be less space fragmentation than if large and tiny are mixed together on the same pack. We do not force separation of tables and indexes, and for most of our applications performance is acceptable. For the small percentage where DASD placement is an issue, we use DB2 stogroups to override SMS and specify volumes for separation. [...] 5264 121 38_Re: Regarding order of Column in DDL .14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Fri, 2 Nov 2001 11:38:04 -0600554_iso-8859-1 RE: [DB2-L] Regarding order of Column in DDL .UPDATES;

Fixed length row: first column updated through last column updated Variable length row (Varchar or DB2 compressed); first column updated to end of row until V7 when only changed bytes are updated if the length of the row does not change

Richard Yevich -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Mark Ruhe Sent: Friday, November 02, 2001 10:44 AM To: DB2-L@RYCI.COM Subject: Re: Regarding order of Column in DDL . [...] 5386 78 50_Re: Regarding change of sql to improve performance11_David Nance16_DWNance@FHSC.COM30_Fri, 2 Nov 2001 12:44:53 -0500374_ISO-8859-1 Vart, How about using a correlated not exists? This seems like it would be a good candidate. Hopefully your col_f and col_d are indexed accordingly or you just shortened up the sql for simplicity sakes here on the list.

SELECT COL_A , COL_B , COL_C FROM TAB_A A WHERE NOT EXISTS (SELECT 1 FROM TAB_B B WHERE COL_F = 'EZ748' AND A.COL_D <> B.COL_E ) ; [...] 5465 99 50_Re: Regarding change of sql to improve performance12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Fri, 2 Nov 2001 11:50:30 -0600487_iso-8859-1 I'm not sure, but you could try moving the NOT as in

SELECT COL_A , COL_B , COL_C FROM TAB_A WHERE COL_D IN (SELECT COL_E FROM TAB_B WHERE COL_F NOT = 'EZ748' ) ;



Best regards, John P. Higgins Performance Specialist SAP BASIS Team * * * * * * *__________________________ * * * * * *____________________ * * * * * * *__________________________ * * * * * *_ * * * * * * *__________________________ * * * * * *_ * * * * * * *__________________________ [...] 5565 86 28_Re: Employment Opportunities13_Debra Roberts14_Debra@MYTA.COM30_Fri, 2 Nov 2001 13:03:59 -0500427_iso-8859-1 Hello Jeff- I have opportunities for UDB DBA's. Let me know if you're interested.

Thank You - Debra J. Roberts direct: 301-230-3256 fax: 301-770-6731

The MYTA Corporation 11400 Rockville Pike, Suite 210 Rockville, MD 20852

-----Original Message----- From: Jeff K. [mailto:JHK2218@PRODIGY.NET] Sent: Monday, October 22, 2001 10:52 AM To: DB2-L@RYCI.COM Subject: Re: Employment Opportunities [...] 5652 23 31_Re: DB2 active log size effects12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 2 Nov 2001 11:24:05 -0600434_- I have just one point to modify. It seems to be a fairly common misconception that a DB2 checkpoint quiesces activity. It does cause some logging to be done, but most of the activity is asynchronous. It does cause the changed pages to be written and those writes to be logged, along with status information. Some customers work so hard to avoid writes and checkpoints that they become disruptive with the amount of work to do. [...] 5676 109 50_Re: Regarding change of sql to improve performance16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Fri, 2 Nov 2001 11:14:26 -0700435_iso-8859-1 Vart,

You're not going to be able to avoid having TAB_A accessed first due to the fact that DB2 will need to check ALL rows of this table for existence in TAB_B based on your predicate. However, if you want to change this to a join, try this:

SELECT A.COL_A , A.COL_B , A.COL_C FROM TAB_A A LEFT OUTER JOIN (SELECT COL_E FROM TAB_B WHERE COL_F = 'EZ748' ) AS B ON A.COL_D = B.COL_E WHERE B.COL_E IS NULL; [...] 5786 98 10_Re: QMF V716_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 2 Nov 2001 13:16:55 -0500292_iso-8859-1 QMF locks not just the objects but also some catalog tables. DDL can be blocked. Most of our dynamic SQL is from workstations. These can be timed out after a few minutes. The QMF users can go to lunch and leave locks. The only thing which gets rid of them is the TSO timeout. [...] 5885 133 28_Re: Employment Opportunities7_Jeff K.19_JHK2218@PRODIGY.NET30_Fri, 2 Nov 2001 13:32:31 -0500432_iso-8859-1 Debra, Yes, I am interested. Please call me at (860) 487-4361.

Thank you. Best regards, Jeffrey Kalman -----Original Message----- From: Debra Roberts Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Friday, November 02, 2001 1:10 PM Subject: Re: Employment Opportunities



Hello Jeff- I have opportunities for UDB DBA's. Let me know if you're interested. [...] 6019 204 53_Re: Question about OS/390 DB2 Recovery option LOGONLY0_20_John_Lendman@FPL.COM30_Fri, 2 Nov 2001 13:51:13 -0500462_us-ascii Michael, I have copied your comments here, I will answer each of them

If you restore the table spaces but do not restore the indexes, there will be no index data sets at the DR site, so clearly you will have to rebuild them.

The type of restore I am doing is a "Flashcopy" of all the data, DB2 catalog and directory. Therefore I do have an index restored, but after I do my recovery, it is not a the same point in time as the tables. [...] 6224 21 24_online sreen performance17_Panicker, Lethika26_LPanicker@FSSA.STATE.IN.US30_Fri, 2 Nov 2001 14:27:11 -0500477_iso-8859-1 Hi All, We are using OS390/ DB2 V6 .Recently we moved one of our online program(Telon and CICS) to production. All the test region one of the SELECT query is taking the proper index and in the production it is not using the appropriate index. The plan table PREFETCH column is showing as 'L'( LIST PREFETCH ).Can anybody tell me is it possible to change the PREFETCH or possible to take the correct index. This screen is taking more than 5 minutes. TIA Lethika [...] 6246 65 37_Re: Cluster ratio and avoiding a sort13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 2 Nov 2001 13:37:36 -0600359_iso-8859-1 Mohammad,

I believe it is that cut and dry, but then again the only way to guarantee would be to look at the actual lines of DB2 code within the optimizer (not easy to do).

The optimizer does not cost the size of the DSNDB07 requirements, and therefore would not determine that it will obtain a -904 as Mike suggested may occur. [...] 6312 54 28_Re: online sreen performance13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 2 Nov 2001 13:37:39 -0600389_iso-8859-1 Lethika,

If you are only returning a subset of rows to the screen, such as 20 rows, then consider coding OPTIMIZE FOR 20 ROWS on the query.

If this doesn't work, then I suggest you may want to send the query, explain output, index information (including columns, clusterratios), number of rows in tables etc to the list for a better analysis of your problem. [...] 6367 224 53_Re: Question about OS/390 DB2 Recovery option LOGONLY15_Murley, Michael22_Michael_Murley@BMC.COM30_Fri, 2 Nov 2001 13:37:58 -0600437_iso-8859-1 John,

Are you going to use LOGONLY for any indexes ? Even if you have to alter them to COPY YES, it might be worth it to be able to avoid the key sorts on selected, very large indexes.

Michael

-----Original Message----- From: John_Lendman@FPL.COM [mailto:John_Lendman@FPL.COM] Sent: Friday, November 02, 2001 12:51 PM To: DB2-L@RYCI.COM Subject: Re: Question about OS/390 DB2 Recovery option LOGONLY [...] 6592 52 28_Re: online sreen performance16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Fri, 2 Nov 2001 13:09:09 -0700417_us-ascii Lethika,

In SOME cases, using the OPTIMIZE FOR 1 ROW clause can inhibit the list prefetch. In regards to the index chosen by the optimizer, could you provide the index definition, along with relevant statistics, and the problem SQL?

Regards, Jeff L'Italien American Express





From: "Panicker, Lethika" @RYCI.COM> on 11/02/2001 02:27 PM EST [...] 6645 65 33_Re: Thanks, and another question!14_Judy Woodfield13_judyw@CAE.COM30_Fri, 2 Nov 2001 15:13:10 -0500326_iso-8859-1 I had a similar challenge on a row that had only a limited number of valid values (53), so I was able to use a case statement as follows: SELECT SUBSTR(DIGITS(T_YEAR),2,4) AS T_YEAR, CASE HEX(T_PERI) WHEN '01' THEN '01' WHEN '02' THEN '02' ..... WHEN '35' THEN '53' END, T_DATE FROM Table_name ORDER BY 1,2,3; [...] 6711 43 37_Re: Cluster ratio and avoiding a sort64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Fri, 2 Nov 2001 14:47:37 -0600582_ISO-8859-1 I have a question in this area (not directly related to this thread, but close enough :-)

My understanding of this matter is that if you want your data returned in a particular order (any index order, not just clustering index order) then you MUST specify ORDER BY.

Didn't it used to be that you could "squeak" your way around having to specify ORDER BY for certain access paths and get your rows returned in clustering order anyway? (An example of this was the ability to avoid coding SELECT MIN(COL1) ... by coding SELECT COL1 ... and only having to [...] 6755 20 18_Record Identifiers17_Pickett, Mary Ann24_pickettm@DOT.STATE.AL.US30_Fri, 2 Nov 2001 16:02:18 -0600464_iso-8859-1 Hello all,

Does anyone know of a way to translate the RID of a record to value of the primary key for that record?

Thanks for any help you can offer.

Mary Ann Pickett Computer Services/ALDOT

================================================ 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. 6776 80 34_Test application and test database10_Grace Chen22_gchen@CUCENTRAL-AB.COM30_Fri, 2 Nov 2001 16:00:37 -0700508_iso-8859-1 Hi! We are new DB2 shop. Our application person start to develop DB2 application recently. They have a requirement that more than one group of application programmer need test their applications with the same tables/database and plans at the same time. Now they have different CICS region to fulfill this requirement(no database). In your shop, how do you fulfill this requirement ? If I want to avoid to give them separate DB2 subsystem, how can I solve duplicate table name, plan name..... [...] 6857 74 37_Re: Cluster ratio and avoiding a sort13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 2 Nov 2001 17:02:57 -0600359_iso-8859-1 Todd,

Yes, that is my understanding also.

The other consideration is a DELETE of the MIN value, and a subsequent INSERT of a higher value in it's place.....Also, data independence dictates that the DBA can change the clustering index, then REORG. And for larger result sets there is also parallelism that will ruin the sequence. [...] 6932 106 33_Re: Thanks, and another question!13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 2 Nov 2001 17:06:02 -0600414_iso-8859-1 Judy,

Whatever conversion you need to do with CASE, then to limit the CPU overhead, I would restrict the number of WHEN clauses as much as possible, and also put the most frequent at the top.

So if most are the same value then change it to: SELECT SUBSTR(DIGITS(T_YEAR),2,4) AS T_YEAR, CASE HEX(T_PERI) WHEN '35' THEN '53' ELSE HEX(T_PERI) END, T_DATE FROM Table_name ORDER BY 1,2,3; [...] 7039 56 22_Re: Record Identifiers15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Fri, 2 Nov 2001 17:10:03 -0600407_iso-8859-1 The RID has the row location, not any of the data in it. To build the key you would need to go to the record and extract the fields that make up the key. It is not a trivial task, especially if the table is compressed.

BMC and IBM have utilities that do key checking and more. (Working on the BMC product, I think it does a lot more than the IBM one and faster. end of advertisement) [...] 7096 79 37_RACF protect DB2 with generic profile10_Grace Chen22_gchen@CUCENTRAL-AB.COM30_Fri, 2 Nov 2001 16:11:38 -0700505_iso-8859-1 Hi! We now use RACF to protect DB2. We can't group DB2 resources as a generic profile so we have to permit them one by one. For example, if we specify: RDEF MDSNTB DBD1.USER1.*.SELECT UACC(NONE) AUDIT(ALL(READ)) OWNER(RACFAD) PERMIT DBD1.USER1.*.SELECT CLASS(MDSNTB) ACC(READ) ID(USER2) It won't protect all tables with owner USER1. It think DBD1.USER1.* is a table name and said there is no this table exists. We want to know if this is what RACF suppose to do or we got something wrong. [...] 7176 65 33_Can CICS call DB2 on another LPAR10_Grace Chen22_gchen@CUCENTRAL-AB.COM30_Fri, 2 Nov 2001 16:14:11 -0700416_iso-8859-1 Hi! If we don't want to install DB2 in the same LPAR as CICS and still want CICS be able to access DB2 on another LPAR. Is it possible?

Thanks! Grace Chen Technical Support Credit Union Central Alberta Ltd. Tel: (403) 258-5982 E-mail : gchen@cucentral-ab.com



_______________________________________________________ eSafe Protect Gateway has scanned this mail for viruses, vandals and 7242 58 22_Re: Record Identifiers0_19_Tim.Lowe@STPAUL.COM30_Fri, 2 Nov 2001 17:26:51 -0600474_us-ascii Mary Ann, You should take a look at the Repair utility, using the Locate syntax. For example: REPAIR LOCATE TABLESPACE dbname.tsname RID X'00362E16' DUMP

I hope this helps.

Thanks, Tim





"Pickett, Mary Ann" To: DB2-L@RYCI.COM Subject: Record Identifiers Sent by: DB2 Data Base Discussion List



11/02/2001 04:02 PM Please respond to DB2 Data Base Discussion List [...] 7301 27 29_Re: Help with creating a view12_sushant dash23_dash_dba@REDIFFMAIL.COM30_Sat, 3 Nov 2001 01:42:32 -0000558_iso-8859-1 Hi All, Just thought to add something which is use full. Also seek the comments . The idea may be limiting the no of records and access less number of records. This would work fine only if SELECTION is needed. Once the sql which call the view woould be merged with the view definitions. The queries to the view would work fine. But if your appliaction do some insertions,deltions and updation then it would suffer. For some moment it would work fine and no body can predict when it would make the system to a halt. Rather my suggestion would [...] 7329 21 24_db2 v4.1 migrate to v5.17_Eric Ng22_ngyh@PUBLICBANK.COM.MY30_Fri, 2 Nov 2001 21:17:42 -0600358_- We are going to migrate from V4.1 to V5.1 Before migration, I will shut down DB2 and perform a DFDSS backup of BSDS,CATALOG,DIRECTORY. I do not backup active logs.

After hitting problem in CATMAINT during migration, if I restore from the backup (which excludes active logs) and revert back to V4.1. Do you think I can bring up again DB2 V4.1 ? [...] 7351 15 49_Martha Kijak/HRD/Prudential is out of the office.12_Martha Kijak27_martha.kijak@PRUDENTIAL.COM30_Sat, 3 Nov 2001 01:01:49 -0500389_us-ascii I will be out of the office starting 11/02/2001 and will not return until 11/12/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. 7367 17 53_Matthias Inhelder/Australia/IBM is out of the office.17_Matthias Inhelder20_minhelde@AU1.IBM.COM30_Sat, 3 Nov 2001 18:40:36 +1100547_us-ascii I will be out of the office starting November 3, 2001 and will not return until November 14, 2001.

I am on leave until Wednesday, 14. November. Send a note to dbdcsup@au1.ibm.com, and another team member will respond or contact the acting TeamLeader Nick Price or my manager Sytze Verbeek.

================================================ 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. 7385 89 42_Re: RLST - no rows but IO to RLST datasets12_Isaac Yassin20_yassini@BEZEQINT.NET30_Sat, 3 Nov 2001 10:45:00 +0200384_ISO-8859-1 Hi, RUNSTATS SHRLEVEL CHANGE on the catalog won't hurt you. I see nearly no I/O - only getpages (against 06 and RLST) which is ok. unless you do un-needed access. RLST is usually not a big table (few rows of Resource Limiting info.) Do you cache your dynamic SQL ? - what's cache hit ratio? It does not look like you need to enlarge your BP from the statistics given. [...] 7475 122 33_Re: Thanks, and another question!14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 02:44:56 -0600597_- Terry,

I think you missed the line of dots in Judy W's case expression.

How about case substr(hex(),1,1) when '0' then 0 when '1' then 1 ... when 'F' then 15 end * 16 + case substr(hex(),2,1) when '0' then 0 when '1' then 1 ... when 'F' then 15 end

or case substr(hex(),1,1) > 'F' then integer(substr(hex(),1,1)) else case substr(hex(),1,1) when 'A' then 10 ... when 'F' then 15 end end * 16 + case substr(hex(),2,1) > 'F' then integer(substr(hex(),2,1)) else case substr(hex(),2,1) when 'A' then 10 ... when 'F' then 15 end end [...] 7598 38 22_Re: Record Identifiers14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 03:04:58 -0600300_- Mary Ann,

Not the "SQLCODE -803 and how do I know the row that already has those duplicated values" problem? Been around for, oh about, ... well ever since I've known DB2 at least. And, of course by the time you get to run a batch job to dump the data, the row has deleted. Been there. [...] 7637 19 27_load into a blob tablespace12_leila rahimi21_lshosaini@HOTMAIL.COM30_Sat, 3 Nov 2001 10:41:49 +0000360_- hi all i have made a tablespace with a blob datatype column,but i don't know which type of file can be input ,,,,when i load a jpg or gif i see an error ,that's reason is "00E40323", plz help me... thanks

_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp [...] 7657 34 25_Regarding db2cli.ini file10_praveen_kj19_praveen_kj@INFY.COM30_Sat, 3 Nov 2001 17:23:22 +0530417_iso-8859-1 Hi All,

In our programmes we do not want to access (DB2 database) tables without any high level qualifier. We are using DB2 connect(lying on HP-UX M/c) to connect to database (DB2 lying on mainframe) .

Basically our problem is tables have been created by some another id and they are accessed by another id.? How these type of things are generally sorted out in production environment.? [...] 7692 196 38_Re: Test application and test database14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 06:11:35 -0600338_- Grace

Typically there is no need for development teams to use the same tables, plans etc.

What would happen is each team has their own set of tables - the table names are common across teams, but the creator-ids are unique to the team. So team 1 uses tables T1.A, T1.B and T1.C while team 2 uses T2.A, T2.B and T2.C. [...] 7889 50 41_Re: RACF protect DB2 with generic profile14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 06:53:25 -0600423_- Are you using a Single Subsystem Class Scope (as distinct from a Multiple SCS)?. Is &CHAROPT not a blank? If either answer is "yes", your RACF Class is wrong. Read SecureWay Server RACF Sys Prog Guide for details. Possibly, it should be MDBD1TB1?

What "said there is no this table exists"? Perhaps you should re-phrase that - it is not clear what you mean. What evidence (eg messages, codes etc) do you have? [...] 7940 33 37_Re: Can CICS call DB2 on another LPAR14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 07:00:25 -0600328_- No, DB2 must be installed and active in the same LPAR as CICS.

It is possible to set things up so that the DB2 in the CICS LPAR uses DDF to use a remote DB2 for user data (BIND PLAN(...) CURRENTSERVER(remote-db2) PKLIST(remote-db2.package.*) ...), but there will still be some system logging on the CICS LPAR DB2. [...] 7974 32 28_Re: db2 v4.1 migrate to v5.114_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 07:03:46 -0600631_- If any activity has taken place on user-tablespaces or indexes you might get a down-level condition. This could be removed by using REPAIR LEVELID ... .



James Campbell

On Fri, 2 Nov 2001 21:17:42 -0600, Eric Ng wrote:

>We are going to migrate from V4.1 to V5.1 >Before migration, I will shut down DB2 and perform a DFDSS backup of >BSDS,CATALOG,DIRECTORY. I do not backup active logs. > >After hitting problem in CATMAINT during migration, if I restore from >the backup (which excludes active logs) and revert back to V4.1. >Do you think [...] 8007 29 31_Re: load into a blob tablespace14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 3 Nov 2001 07:09:50 -0600582_- Leila

Did you read the Messages and Codes for this code. Especially the bit where it said "[p]rior to this abend, error messages identifying the specific problem were issued."

My advice to you is to read those error messages.

James Campbell



On Sat, 3 Nov 2001 10:41:49 +0000, leila rahimi wrote:

>hi all >i have made a tablespace with a blob datatype column,but i don't know which >type of file can be input ,,,,when i load a jpg or gif i see an error >,that's reason is "00E40323", plz help me... >thanks > [...] 8037 30 23_SQL Procedure on OS/39011_David Nance16_DWNance@FHSC.COM30_Sat, 3 Nov 2001 08:45:29 -0500526_US-ASCII One of my co-workers is having a problem with trying to test out an SQL procedure. Our environment is DB2 V6 on OS/390 R2.10, no WLM. This is our first attempt of SQL procedures. When we call it from DSNREXX we get a -440 and when called from a cobol program we get +000, update stmt is not done during either call. I was wondering if maybe we aren't compiling with the correct compile options and was wondering if anyone could tell us which(if any compile options we should be using for the C compiler. Thanks. [...] 8068 59 37_Re: Can CICS call DB2 on another LPAR11_Suresh Sane21_data_arch@HOTMAIL.COM30_Sat, 3 Nov 2001 08:27:19 -0600588_- James,

What about MRO? Coun't we set it up so that a cics tran on lpar 1 invokes a cics tran on lpar 2 that does have db2? Are there restrictions within cics to not cross lpars (this becomes a cics issue rather than a db2 issue)?

Thanks, Suresh

>From: James Campbell >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Can CICS call DB2 on another LPAR >Date: Sat, 3 Nov 2001 07:00:25 -0600 > >No, DB2 must be installed and active in the same LPAR as CICS. > >It is possible to set things up [...] 8128 27 31_INTERSCT IN VERSION 7 ON OS 39017_jennifer jennifer22_jenni_jeni@HOTMAIL.COM30_Sun, 4 Nov 2001 02:27:07 +0000533_- Hi all , I have a doubt here for interset synatx with db2 on os/390. The code when shifted from udb db2 to os/390 certain queries are not working. Some queries are like this

SELECT KB_KEY FROM TGXXX21 INTERSECT SELECT JB_KEY FROM TGXXX27; This is working fine but on OS/390 it is not working. This is just one example but I have lot of queries with INTERSECT. I wonder when the same statement is working with db2 in one plat form then why not in OS/390. Here people says it can work on db2 v7 os/390. Can any body help [...] 8156 84 37_Re: Cluster ratio and avoiding a sort5_Larry19_larry1260@YAHOO.COM30_Sat, 3 Nov 2001 19:16:39 -0800527_us-ascii Todd,

I can tell you that since 1986 the only way to guarantee the correct ordering is to use ORDER BY. Now what you describe returned the correct row 90% of the time back when, I know for a fact that it did not always. The reason I know this is that I got a 2 am frantic call from an application programmer saying that DB2 was broken Christmas Eve 1986. It was the case if none of the qualifying pages were in the buffer pool prior to a query being executed you would get the desired results (without the [...] 8241 45 35_Re: INTERSCT IN VERSION 7 ON OS 39012_sushant dash23_dash_dba@REDIFFMAIL.COM30_Sun, 4 Nov 2001 03:56:15 -0000535_iso-8859-1 Hi Jennifer , it is very difficult to say why those are not at os/390. I m also facing the same probelm though not with INTERSECT. I m also working with V7 in both os/390 as well UDBdb2on unix. This INTERSECT syntax does not work on os/390. But we can not say why IBM has implemented same functions or statements for both.All the statement works with os/390 would work with db2 on nt or unx but vice versa is not true. The reason may be to provide the capability of processing huge amaont of data on os/390. But as far [...] 8287 51 28_Re: db2 v4.1 migrate to v5.17_Eric Ng22_ngyh@PUBLICBANK.COM.MY30_Sat, 3 Nov 2001 21:48:53 -0600653_- Hi, James. What if there is no activity on user-tablespaces/indexes ? I hit problems during migration at CATMAINT. The reason I tried to skip active logs is to save backup time.





Regards, Eric Ng



On Sat, 3 Nov 2001 07:03:46 -0600, James Campbell wrote:

>If any activity has taken place on user-tablespaces or indexes you might >get a down-level condition. This could be removed by using REPAIR >LEVELID ... . > > > >James Campbell > >On Fri, 2 Nov 2001 21:17:42 -0600, Eric Ng wrote: > >>We are going to migrate from [...] 8339 72 11_unload data33_=?iso-8859-9?Q?R.Teoman_KO=C7ER?=22_tkocer@HALKBANK.COM.TR30_Sun, 4 Nov 2001 12:33:28 +0200188_iso-8859-9 dear list members, I did a select from spufi.and my result is in dataset. is there any way to unload these results to Microsoft Access or Excel ?







8412 30 20_Regarding free space10_praveen_kj19_praveen_kj@INFY.COM30_Sun, 4 Nov 2001 17:20:27 +0530411_iso-8859-1 Hi All,

Can any body give me pointers regarding free space parameters (PCTFREE,FREEPAGE) and its relevance in terms of DDL. Basically i think PCTFREE is relevant in terms of varchar field only and FREEPAGE is relevant in clustered index.

Are there some other issues to which these parameters are linked (PCTFREE,FREEPAGE).





Regards,

Praveen Kumar Jain [...] 8443 30 27_Re: SQL Procedure on OS/39014_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 4 Nov 2001 05:48:33 -0600561_- Did you check each of the causes given for a -440? Some of them imply "found the routine, but you forgot to smile".

James Campbell



On Sat, 3 Nov 2001 08:45:29 -0500, David Nance wrote:

>One of my co-workers is having a problem with trying to test out an SQL procedure. Our environment is DB2 V6 on OS/390 R2.10, no WLM. This is our first attempt of SQL procedures. When we call it from DSNREXX we get a -440 and when called from a cobol program we get +000, update stmt is not done during either call. > I [...] 8474 36 37_Re: Can CICS call DB2 on another LPAR14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 4 Nov 2001 05:51:59 -0600615_- The CICS invoking DB2 is still on the same LPAR as DB2 - you've just moved to a different CICS.

James

On Sat, 3 Nov 2001 08:27:19 -0600, Suresh Sane wrote:

>James, > >What about MRO? Coun't we set it up so that a cics tran on lpar 1 invokes a >cics tran on lpar 2 that does have db2? Are there restrictions within cics >to not cross lpars (this becomes a cics issue rather than a db2 issue)? > >Thanks, >Suresh > >>From: James Campbell >>Reply-To: DB2 Data Base Discussion List >>To: DB2-L@RYCI.COM >>Subject: Re: Can [...] 8511 57 35_Re: INTERSCT IN VERSION 7 ON OS 39014_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 4 Nov 2001 06:09:52 -0600437_- Jennifer

Despite what IBM might imply, "DB2 UDB for z/OS and OS/390" is a different product from "DB2 UDB for Windows, OS/2, Unix and Linux" (and both are different from "DB2 UDB for iSeries"). Because something works at Version x of one product does not mean it will work at Version x of another (except insofar as the Windows, OS/2, HP-UX, Solaris, AIX and Linux implementations are implementations of the same product). [...] 8569 25 15_Re: unload data14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 4 Nov 2001 06:17:28 -0600413_- Edit the dataset to leave it with first row containing column names, and second and subsequent rows containing just data (ie edit out your sql, all page headings except the column names at the begining, and trailing stuff), FTP (or IND$FILE) to your PC as a .txt file, open the file under Excel - which will ask for how the data should be treated, and then specify appropriate a rows, columns and formats. [...] 8595 26 28_Re: online sreen performance8_Harish G24_hari_gangadhar@YAHOO.COM30_Sun, 4 Nov 2001 11:22:23 -0600543_- List prefetch could be due to a poor cluster ratio, or less number of contiguos rows returned or due to a hybrid join. Your test set-up could be different from the prod. set-up. One way to start exploring would be to start from the basics such as whether the exact indices are defined between test and prod, the cluster ratios and re-org dates. Things may or may not be complicated than these in your case. Also, unless your prod. and test data match exactly (or at least closely), the data in the DB2 catalogs in test will not be same [...] 8622 147 33_Re: Thanks, and another question!13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Sun, 4 Nov 2001 15:02:22 -0600518_iso-8859-1 James,

Actually, what I was trying to suggest is that every WHEN condition carries a CPU overhead, so if you can consolidate them then do so. I did see the dots, but it's difficult to know what they code they represent.

So if you had:

SELECT SUBSTR(DIGITS(T_YEAR),2,4) AS T_YEAR, CASE HEX(T_PERI) WHEN '01' THEN '01' WHEN '02' THEN '02' WHEN '03' THEN '03' WHEN '04' THEN '04' WHEN '05' THEN '05' WHEN '24' THEN '42' WHEN '35' THEN '53' END, T_DATE FROM Table_name ORDER BY 1,2,3; [...] 8770 85 35_Re: INTERSCT IN VERSION 7 ON OS 39017_jennifer jennifer22_jenni_jeni@HOTMAIL.COM30_Sun, 4 Nov 2001 23:46:46 +0000318_- Hello James/Dash, I got the reply from both of you. I gained a lot and had some idea about the diffrent versions of Db2 on diffrent plat form. I tried your and Dash's method and both are working fine. I would check its path through explain and implement the one. Again I give thanks to you as well all listers. [...] 8856 70 35_Re: INTERSCT IN VERSION 7 ON OS 39012_sushant dash23_dash_dba@REDIFFMAIL.COM30_Mon, 5 Nov 2001 01:22:41 -0000430_iso-8859-1 Hi Jennifer , I checked James method also it would be better for you to implement the James method. Becoz the query given by me would work well but it would take into a non corelated subquery. For better performances implement the Query by james and it would be co related sub query and it would perfomr better. Please check it with explain and it would be better. Hope this would agin help you a lot. Thanks Dash [...] 8927 184 59_Antwort: Re: Regarding change of sql to improve performance24_Hanumantharao Tatavarthi31_hanumantharao.tatavarthi@DB.COM30_Mon, 5 Nov 2001 11:45:59 +0100549_iso-8859-1 hi list thanks for your suggestions, But it seems to me most of the suggested ones won't work except outer join.... But the outer joint won't solve my problem.... because it has to search each row of both tables..... I need a join desperately here......Because i found when i have a querry like SELECT COL_A , COL_B , COL_C FROM TAB_A WHERE COL_D IN (SELECT COL_E FROM TAB_B WHERE COL_F = 'EZ748' ) ; In this case i found that TAB_A(big table) is accessed first and then condition checked with value in TAB_B, which is a small table [...] 9112 22 62_error X'00e40304' in Reorg Shrlevel Change in TS Part with NPI16_Edurne Murgiondo24_emurguiondo@IBERDROLA.ES30_Mon, 5 Nov 2001 06:16:31 -0600587_ISO-8859-1 I have a problen with a reorg in a Part of a tablespace with no partitioning index. The reorg Abended in Phase Build2 and the message is: DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E4030 When the reorg cancel, the logical partition of the non-partitioning indexes are in REBUILD-PENDING, I term the utility but the indexes are still in Rebuild-pending. I have to REBUILD indexes NPI'S for to resolve this problem, but it take a lot of time with unavilability. ¿Somebodi has had this problem? I hope that somebody can help me. Many thanks. [...] 9135 48 41_Re: RACF protect DB2 with generic profile10_Bruce, Mae26_BruceM@MAIL.OA.STATE.MO.US30_Mon, 5 Nov 2001 07:09:42 -0600453_ISO-8859-1 When you do the RDEF, don't you have to define it as a generic profile, otherwise it will think it is a discrete profile. Sounds like it is being treated as a discrete profile.

Mae Bruce State of Missouri OA/Division of Information Services

-----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Friday, November 02, 2001 5:12 PM To: DB2-L@RYCI.COM Subject: RACF protect DB2 with generic profile [...] 9184 55 66_Re: error X'00e40304' in Reorg Shrlevel Change in TS Part with NPI14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE30_Mon, 5 Nov 2001 14:22:12 +0100619_iso-8859-1 Edurne,

if I remember correctly I ran across the very same problem once upon a time. Increasing the amount of work-space helped.

HTH Ruediger Kurtz

Edurne Murgiondo schrieb: > > I have a problen with a reorg in a Part of a tablespace with no > partitioning index. The reorg Abended in Phase Build2 and the message is: > DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, > REASON=X'00E4030 > When the reorg cancel, the logical partition of the non-partitioning > indexes are in REBUILD-PENDING, I term the utility but the indexes are > still in Rebuild-pending. I have [...] 9240 17 66_Re: error X'00e40304' in Reorg Shrlevel Change in TS Part with NPI10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 5 Nov 2001 14:33:34 +0100411_us-ascii Are you sure that there's no 00D700xx (Insufficient space on DASD for instance) error before 00E40304 ?

Check DB2 MSTR to check if any.

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. 9258 60 37_Re: Can CICS call DB2 on another LPAR10_Grace Chen22_gchen@CUCENTRAL-AB.COM30_Mon, 5 Nov 2001 07:48:11 -0700447_iso-8859-1 James, Very appreciate your answer. It helps us to re-arrange softwares running in different machines.

Grace



-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Sunday, November 04, 2001 4:52 AM To: DB2-L@RYCI.COM Subject: Re: Can CICS call DB2 on another LPAR



The CICS invoking DB2 is still on the same LPAR as DB2 - you've just moved to a different CICS. [...] 9319 19 56_Shan Leatherman/MO/americancentury is out of the office.0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Mon, 5 Nov 2001 09:15:54 -0600547_us-ascii I will be out of the office starting 11/05/2001 and will not return until 11/07/2001.

If you need additional assistance, send a message to *DB or if you need immediate assistance page the primary DBA on call at (816) 292-5449 or contact the help desk (816) 340-4250.

Shan Leatherman

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9339 71 24_Re: DDF HANGS - OS390 V621_Tpg Sysdba department18_Sysdba.AHE@TNT.COM30_Mon, 5 Nov 2001 15:10:28 +0000316_us-ascii AFAIK this is exactly what the command does - it de-activates DDF functionality, but the DDF address space is not terminated. Likewise, the DDF address space is started at the same time as the rest of DB2, even if your DSNZPARM says DDF=COMMAND. To recycle the DDF address space, you must restart DB2. [...] 9411 102 24_Re: Regarding free space10_Mark Doyle19_mdoyle@JCPENNEY.COM30_Mon, 5 Nov 2001 10:12:36 -0600552_iso-8859-1 This is one of these simple questions that is much more involved that it appears. I really comes down to "knowing your data".

I find it helpful to think of these things in terms of rows and pages. A page contains 4074 usable (for data rows) bytes. SYSIBM.SYSTABLES will give you the row length of a data row with the row overhead (8 bytes per row) for non-variable rows & maximum row length for rows with varchars. Divide the row length by 4074 & discard the fractional remander to find out how many rows will fit on a page. The [...] 9514 78 29_Re: Regarding db2cli.ini file9_Chris Tee23_chris.tee@UK.ZURICH.COM30_Mon, 5 Nov 2001 16:00:29 +0000635_us-ascii Praveen,

I've used CURRENTSCHEMA in db2cli.ini which seems to work OK.

Chris ************************************************************************* Chris Tee, Technical Systems Services, Database Services.

Aldrin Place, Southwood, Farnborough, Hants, GU14-ONX. Phone: Ext: +44 (0) 1252 387655 Int: (7600 x3655). Email: chris.tee@uk.zurich.com *************************************************************************





praveen_kj cc: Sent by: DB2 Subject: [DB2-L] Regarding db2cli.ini file Data Base Discussion List [...] 9593 12 66_Re: error X'00e40304' in Reorg Shrlevel Change in TS Part with NPI16_Edurne Murgiondo24_emurguiondo@IBERDROLA.ES30_Mon, 5 Nov 2001 09:56:18 -0600369_- I have found an apar that can solve the problem. This apar is: pq41155 but Even not it has installed it. Many thanks for all.

================================================ 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. 9606 68 50_Re: Regarding change of sql to improve performance16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 09:19:02 -0700587_us-ascii Vart,

The sample you just provided indicates an existence condition unlike the original example you supplied to the group which tests for non-existence. Furthermore, as you have seen a join WILL outperform an in list due to the fact that in many cases (not all), the in list is resolved first and the result of it is stored within temporary work space in DB2, then each row of the outer table is then qualified against this table using a tablespace scan of the temporary work space. There are instances in which the optimizer will transform the subselect with an IN [...] 9675 45 41_Re: RACF protect DB2 with generic profile13_Juan Mautalen22_jmautalen@ANSES.GOV.AR30_Mon, 5 Nov 2001 13:21:53 -0300572_iso-8859-1 When Generics is turned on for a RACF class, then any profile created containing any wildcard in its name (* , % or **) will be created as a generic profile. There is no need in those cases to specify GENERIC at the profile´s creation time. However, it is a common RACF mistake to create a "generic" profile within a class before enabling generics for that class. In this case, turning on generics after the profile´s creation does not solve the problem and you will end up with a DISCRETE profile containing generic characters in its name. Of course, you [...] 9721 24 41_OS/390 DB2 Version 7 with QMF Version 3.39_Chu, Pius14_ChuP@CONED.COM30_Mon, 5 Nov 2001 11:26:15 -0500374_iso-8859-1 We are planning to migrate to DB2 V7 from V5 and we could not find any IBM writing stating QMF 3.3 works with DB2 V7. We check the V7 announcement letter and talked to IBM/QMF area without any luck. Does any one know the answer?

Thanks.



Pius Chu IR - Operating Systems Support Chup@coned.com (212) 460-4764 (phone) (212) 387-2157 (fax) [...] 9746 37 23_Cross Subsystem Queries11_Eric Wilkin18_eric.wilkin@NBB.BE30_Mon, 5 Nov 2001 17:49:24 +0100342_iso-8859-1 Hi,

We are Running DB2 5.1 on OS/390 v2.10

What are the differents solutions (and the easiest one) to be able to run queries (select only) over 2 DB2 subsystems under the same OS/390 ?

Thanks for your help.

Eric Wilkin National Bank of Belgium SYDS-DB tel +32 (0)2 221 45 99 fax +32 (0)2 221 30 92 [...] 9784 36 44_SQLCODE -497 : Immediate Assistance Required16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 10:00:10 -0700499_us-ascii Group,

We have encountered an SQL code of -497 which translates to the following:

THE LIMIT OF 32767 OBIDS HAS BEEN EXCEEDED FOR DATABASE database-name

The environment is adhoc QMF in which many of the PROCs are set up to create and drop tables on a regular basis. According to the messages and codes manual, the modify utility should be run in order to be able to reclaim some of the unused OBIDs. The utility guide recommends the following steps to action this: [...] 9821 41 48_Re: SQLCODE -497 : Immediate Assistance Required10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 5 Nov 2001 18:43:08 +0100406_us-ascii Hi Jeff

Why didn't you run Modify Utility for that DB ? I've never met this problem, but the procedure seems correct.

I'm not sure, but try to run a reorg SHRLEVEL REFERENCE.

Take a look at wasted space running the query:

SELECT TSNAME, DBNAME, PERCDROP FROM SYSIBM. SYSTABLEPART WHERE PERCDROP > 0

After the reorg/modify execute a CREATE TABLE/TABLESPACE. [...] 9863 166 48_Re: SQLCODE -497 : Immediate Assistance Required15_Tanya Haverchak36_Tanya.Haverchak@ANTARESSOLUTIONS.COM30_Mon, 5 Nov 2001 12:39:36 -0500432_us-ascii Yes it is correct. Reorg, I-copy, modify. Should take care of it. This kind of problem is quite common with QMF data bases.

Regards, Tanya Haverchak Antares Management Solutions.



>>> Jeff.A.L'Italien@AEXP.COM 11/05/01 12:00PM >>> Group,

We have encountered an SQL code of -497 which translates to the following:

THE LIMIT OF 32767 OBIDS HAS BEEN EXCEEDED FOR DATABASE database-name [...] 10030 21 13_Reorg pending0_21_BudGreenman@ONGOV.NET30_Mon, 5 Nov 2001 12:57:07 -0500518_us-ascii I seem to be in a catch 22 situation. I am working with a partitiioned ts in my test system and after changing the limit keys and reorging I wanted to return to the original state and try something different. I altered the index to reset the limits and then recovered the ts to the way it was prior to the first alter & reorg. This left the ts in reorg pending and the indexes in rebuild pending. Now I can't reorg because the ix's are in rebuild pend and I can't rebuild because the ts is in reorg pend [...] 10052 94 48_Re: SQLCODE -497 : Immediate Assistance Required14_Andy Lankester33_andy.lankester@FR.CDBSOFTWARE.COM30_Mon, 5 Nov 2001 18:06:34 -0000640_us-ascii A good standard is to give each QMF user his/her own database/tablespace to reduce catalog locking contention on DDL (eg SAVE DATA), but you may have this already. As answered already MODIFY is _essential_, not the least to reduce the size of SYSLGRNGX.

Andy Lankester CDB Software < ===================== New to those of you who know me!! Tel/Fax: +44 (0)20 8876 1370 Mobile: +44 (0)77 6825 7976 -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Tanya Haverchak Sent: 05 November 2001 17:40 To: DB2-L@RYCI.COM Subject: Re: SQLCODE -497 : Immediate Assistance Required [...] 10147 58 24_Re: Regarding free space14_Andy Lankester33_andy.lankester@FR.CDBSOFTWARE.COM30_Mon, 5 Nov 2001 18:17:34 -0000478_us-ascii On indexes there are some other considerations. If you are not adding at the end or if it not a clustering index then freespace is useful since it can be used to prevent LEAFDIST getting too big and triggering a REORG. Rule of thumb: FREEPAGE = 0, REORG when LEAFDIST/NLEAF > 1. If FREEPAGE is non zero then LEAFDIST = 200 is _very_ approximately when 50% of the freepages have been used. But this is ONLY a RoT - your results may vary and, as always, IT DEPENDS. [...] 10206 68 45_Re: OS/390 DB2 Version 7 with QMF Version 3.30_29_sflindsey@HIGHLIGHTS-CORP.COM30_Mon, 5 Nov 2001 13:18:29 -0500479_us-ascii You have to upgrade QMF. I am in the process of going to DB2 V6 and we had to move to QMF V6. HTH! Scott Lindsey Sr. DBA, Highlights for Children















"Chu, Pius" on 11/05/2001 11:26:15 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Scott F Lindsey/HFC/HFC_Corp) [...] 10275 65 48_Re: SQLCODE -497 : Immediate Assistance Required14_Andy Lankester33_andy.lankester@FR.CDBSOFTWARE.COM30_Mon, 5 Nov 2001 18:27:04 -0000618_us-ascii Forgot to add. REORG/MODIFY will also massively reduce the DBD size thereby reducing the pressure on your EDM pool.

Andy Lankester CDB Software UK



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] > On Behalf Of Jeff A L'Italien > Sent: 05 November 2001 17:00 > To: DB2-L@RYCI.COM > Subject: SQLCODE -497 : Immediate Assistance Required > > > Group, > > We have encountered an SQL code of -497 which translates to > the following: > > THE LIMIT OF 32767 OBIDS HAS BEEN EXCEEDED FOR DATABASE database-name > > The environment is adhoc QMF in [...] 10341 73 48_Re: SQLCODE -497 : Immediate Assistance Required16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 11:10:06 -0700391_us-ascii Max,

We did run the Modify utility this morning, but this alone did not fix the problem. At this point we're going to follow through based on Tanya's confirmation to see if this gets us through this situation. Since this database needs to be available throughout the day, I don't think we can tolerate a reorg with SHRLEVEL REFERENCE, but will try with an online reorg. [...] 10415 43 17_Re: Reorg pending16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 5 Nov 2001 13:37:05 -0500285_iso-8859-1 They are only index parts. Have you tried a -START .... FORCE on them to allow the REORG to run? Since they are useless anyway at this point, there is no risk to the -START ..... FORCE. I don't know for sure that this will allow the REORG to run but it is worth a try. [...] 10459 46 17_Re: Reorg pending17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Mon, 5 Nov 2001 13:17:53 -0600469_iso-8859-1 Hi Bud,

Do a Full Tablespace Reorg (NO part specification). That should work since it will rebuild the indexes anyway in that mode.

Hope this helps,

Ken Kornblum BMC Software Sr. Product Developer DB2 Utilities Austin (in the GREAT state of) Texas

-----Original Message----- From: BudGreenman@ONGOV.NET [mailto:BudGreenman@ONGOV.NET] Sent: Monday, November 05, 2001 11:57 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Reorg pending [...] 10506 24 16_V6 DBM1 High CPU14_Riedel, Marcia26_MRiedel@TUCSONELECTRIC.COM30_Mon, 5 Nov 2001 12:20:21 -0700492_iso-8859-1 Hello, List.

A second DB2 subsystem was just migrated to V6. Without any threads running, DBM1 is taking 55-60% of the CPU. ZPARMs compared favorably with the first subsystem. The first subsystem's DBM1 is taking negligible amounts of CPU with no workload.

We are at V6 maintenance level 0106 (June 1). One of the TCBs running DSNVEUS3 is using 45% of the DBM1 CPU. Other instances of DSNVEUS3 are using nothing. What does DSNVEUS3 do? I get no hits on IBMLINK. [...] 10531 14 21_Padding with 0 in UDB12_Lorraine Yan22_lorraine.yan@TELUS.COM30_Mon, 5 Nov 2001 12:30:04 -0600315_- I have an integer column that I need to pad out to 10 chars (left padded with 'zero') so that it can be read by a mainframe cobol program. If I were in Oracle I could use to_char and then lpad. I can find no UDB equivelant to lpad, and the char function has no formatting with it that I can find. Any ideas? [...] 10546 76 25_Merge Scan to Nested Loop11_David Nance16_DWNance@FHSC.COM30_Mon, 5 Nov 2001 14:25:30 -0500525_US-ASCII I'm having some problems with a particular query for a report, it gets quite a bit of data. I feel it would be much faster if I could get the access path to change to nested loop join on the first index instead of the current merge scan. I've tried most every trick I can think of but the only thing I've been able to do is get the path to change to a TS scan on the larger table. Any tips would be greatly appreciated. Thanks. DECLARE FN_CSR CURSOR FOR SELECT DISTINCT A.I_PAYEE , B.D_PYMT FROM FN_DISBMNT A , [...] 10623 23 17_Maintenance PTF's12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Mon, 5 Nov 2001 13:47:33 -0600360_us-ascii Hello,

We are about a year behind on maintenance here, (V6.1, OS/390) and now we've decided to "catch up". Would you suggest applying all available PTF's? Only closed PTF's? What criteria do others use?

We're having a few trigger issues but I didn't see the value of reporting them to IBM until we were more current on maintenance. [...] 10647 116 40_DB2 for OS/390 v7.1 Performance Problems11_Emery, Matt19_matt.emery@JNLI.COM30_Mon, 5 Nov 2001 14:56:30 -0500499_iso-8859-1 I am seeing a severe performance degradation going against a v7.1 table on OS390 going through a DB2 Connect Enterprise Gateway on an RS/6000. I have the same table in a DB2 for OS/390 v5.1 database and a DB2 for OS/390 v7.1 database. Going through the DB2 Connect Enterprise Gateway on the RS/6000 to the v5.1 database is noticeably faster. I have reorg'd and runstat'd the tables. I have tested using a version 5.2 gateway on AIX and on a version 7.2 gateway on AIX. Same results. [...] 10764 96 48_Re: SQLCODE -497 : Immediate Assistance Required16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 12:34:58 -0700430_us-ascii Andy,

We did as the manual suggested, following each step, and the result is that we are still unable to create tables in the existing database (still getting -497). The modify was run in a manner to remove all but the current image copy from SYSCOPY. I don't know if the SHRLEVEL parm of the reorg would make a difference, but at this point, we are going to opt at creating a new database for their objects. [...] 10861 66 51_DELAWARE VALLEY DB2 USERS GROUP, November 9th, 200118_Mulligan, Thomas J28_thomas.j.mulligan@BOEING.COM30_Mon, 5 Nov 2001 14:59:38 -05001082_iso-8859-1 DELAWARE VALLEY DB2 USERS GROUP

____________________________________________________________________________ _ MEETING DATE: Friday, November 9th, 2001 LOCATION: Dave & Buster's STARTING TIME: 9:00 AM REGISTRATION: 8:30 AM - 9:00 AM COST: ADVANCE REGISTRATION: MEMBERS $30, NON-MEMBERS $40 WALK IN REGISTRATION: MEMBERS $50, NON-MEMBERS $60 ____________________________________________________________________________ _ AGENDA

09:00 - 09:15 Opening Remarks Joe Carola, Chairperson DVDUG ____________________________________________________________________________ _ 09:15 -10:30 DB2 UDB for OS/390 V7 New Features Overview David Cohn - Themis Instructor ____________________________________________________________________________ _ 10:30 - 10:45 BREAK ____________________________________________________________________________ _ 10:45 - 12:00 Improve DB2 Performance through Bufferpool Tuning Joel Goldstein - Responsive Systems President ____________________________________________________________________________ _ 12:00 - 01:00 LUNCH: Sponsored [...] 10928 49 20_Re: V6 DBM1 High CPU12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Mon, 5 Nov 2001 15:07:01 -0500686_us-ascii Hello, the module is service task dispatcher. look in sdsnsamp(dsnwmods).







"Riedel, Marcia" @RYCI.COM> on 11/05/2001 02:20:21 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: V6 DBM1 High CPU



Hello, List.

A second DB2 subsystem was just migrated to V6. Without any threads running, DBM1 is taking 55-60% of the CPU. ZPARMs compared favorably with the first subsystem. The first subsystem's DBM1 is taking negligible amounts of CPU with no workload. [...] 10978 129 21_Re: Maintenance PTF's13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM30_Mon, 5 Nov 2001 15:32:01 -0500503_US-ASCII Stg,

The short answer is "no". In my mind there are few circumstances where you would consider applying all of that maintenance at once.

Example: We recently applied PTF UQ54198 as part of PUT0108 maintenance. That "enabled" access to Unicode System Services (FMID HUNI2A0). However, we don't have that FMID installed ... and one of our applications (Peoplesoft!) began failing as it tried to invoke the service. We should have seen this in the HoldData, and not applied it. [...] 11108 19 79_Setting the Correlation ID in DB2 Connect EE V7.2 in an IIS/ASP/V B Environment17_Nicholson, Martin20_MNicholson@USSCO.COM30_Mon, 5 Nov 2001 14:32:44 -0600393_- We are using DB2 Connect EE in a Microsoft web environment (IIS, ASP, VB, COM, & MTS). We are using AUTHID to differentiate our distributed applications in our performance reports. This technique is rapidly becoming inadequate.

Is there any way to set the DB2 Correlation-ID (CORR-ID accounting field) to something other than indeinfo.exe or MTX.EXE in an environment like ours? [...] 11128 79 17_Re: Reorg pending0_21_BudGreenman@ONGOV.NET30_Mon, 5 Nov 2001 15:52:32 -0500423_us-ascii I did try a full reorg (btw I am using catalog manager to generate the jcl), but db2 is still saying it can't run due to the rebuild pending status.





"Kornblum, Kenneth" To: DB2-L@RYCI.COM Subject: Re: Reorg pending Sent by: DB2 Data Base Discussion List



11/05/2001 02:17 PM Please respond to DB2 Data Base Discussion List [...] 11208 122 48_Re: SQLCODE -497 : Immediate Assistance Required11_Robert Jans26_robert.jans@ALBERTSONS.COM30_Mon, 5 Nov 2001 14:02:48 -0700535_- Okay, I'll jump in......... Have you checked the number of physical objects in this database? -497 states there are no more available identifiers for new objects implying there are 32k objects existing in the database..........

Robert Jans Albertson's, Inc.







-----Original Message----- From: Jeff A L'Italien [mailto:Jeff.A.L'Italien@AEXP.COM] Sent: Monday, November 05, 2001 12:35 PM To: DB2-L@RYCI.COM Subject: Re: SQLCODE -497 : Immediate Assistance Required [...] 11331 108 17_Re: Reorg pending15_Billings, Linda30_Linda.Billings@DEG.STATE.WI.US30_Mon, 5 Nov 2001 15:23:43 -0600525_iso-8859-1 OK, IMHO, you should unload your data from the image copy, drop and recreate the tablespace and indexes and reload your data.

Linda Billings Project Manager/Enterprise Systems Programmer State of Wisconsin Department of Electronic Government Your-Guess-Is-As-Good-As-Mine Division linda.billings@deg.state.wi.us (Note the new email address)

"Clear writers assume, with a pessimism born of experience, that whatever isn't plainly stated the reader will invariably misconstrue." - John R. Trimble [...] 11440 132 48_Re: SQLCODE -497 : Immediate Assistance Required15_Tanya Haverchak36_Tanya.Haverchak@ANTARESSOLUTIONS.COM30_Mon, 5 Nov 2001 16:27:11 -0500374_us-ascii Jeff, check out APAR PQ45758. I assume you are on V6. Also, have you ran REPAIR DBD DIAGNOSE on the data base in question? This will build a DBD from catalog(active objects) and compare it to a real DBD. There should be a difference.

Regards, Tanya Haverchak Antares Management Solutions.

>>> Jeff.A.L'Italien@AEXP.COM 11/05/01 02:34PM >>> Andy, [...] 11573 120 17_Re: Reorg pending0_19_Tim.Lowe@STPAUL.COM30_Mon, 5 Nov 2001 15:49:07 -0600454_us-ascii Bud, Try specifying SORTDATA. Otherwise, it uses the clustering index to unload. (I heard a rumor that they might change the default to SORTDATA in a future release since it gives such a dramatic improvement in performance in so many cases.)

I hope this helps.

Thanks, Tim





BudGreenman@ON GOV.NET To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: Reorg pending Discussion List [...] 11694 28 47_Stored Procs - multiple copies in one subsystem19_Natalie W. Faulkner27_nfaulknr@GWMAIL.STATE.WV.US30_Mon, 5 Nov 2001 15:56:46 -0600524_- Dear Listers:

We have an web app hitting DB2 for OS/390 V5 using stored procedures.

The problem:

1) We are new to stored Procs

2) We have multiple databases (dev, test, and training) in the same subsystem. How can we utilize the same stored procedures to hit the various databases? The only thing that has worked to access the stored proc is a primary authid (logon id) or spaces in the authid column of sysprocedures. Because of the number of potential users, we need to use racf groups. [...] 11723 90 17_Re: Reorg pending17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Mon, 5 Nov 2001 16:02:37 -0600337_iso-8859-1 Hmmm, not what I expected you to say! :-) Well if you have Catalog Manager perhaps you also have BMC Reorg Plus for DB2. I'm certain we'll run under the circumstances you describe.

I didn't mean to turn this into a vendor plug, but since you mention you have some of our tools I thought you might have the others. [...] 11814 153 48_Re: SQLCODE -497 : Immediate Assistance Required16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 15:17:07 -0700419_us-ascii Robert,

Actually we can see gaps in the OBIDs when we list all of the tables for the given database which leads me to believe that not all 32K OBIDs are currently in use.

Regards, Jeff L'Italien American Express





From: "Robert Jans" @RYCI.COM> on 11/05/2001 02:02 PM

Please respond to "DB2 Data Base Discussion List" [...] 11968 19 37_New to os/390 - looking for good book14_Michele Payton18_paytonm@US.IBM.COM30_Mon, 5 Nov 2001 15:32:39 -0700491_us-ascii Listers,

I am about to begin a new DB2 project that will be implemented on os/390. Can anyone recommend a good book or two? I am completely unfamiliar with mainframes. Thanks in advance.

Michele Payton Integration Services DBA

================================================ 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. 11988 50 51_Re: Stored Procs - multiple copies in one subsystem10_Shery Hepp17_schepp@SRPNET.COM30_Mon, 5 Nov 2001 15:50:08 -0700303_iso-8859-1 Natalie- It's been a while since we've been on V5- but I think you can only have 1 stored proc per subsystem. At least we never got multiples to work in one subsystem. In V6 you can have user defined schemas as stored proc owners- therefore having multiple copies of the same procedure. [...] 12039 188 48_Re: SQLCODE -497 : Immediate Assistance Required14_Andy Lankester33_andy.lankester@FR.CDBSOFTWARE.COM30_Mon, 5 Nov 2001 22:51:47 -0000278_us-ascii Wild guess - are there any objects that have never had an image copy? They may be still recoverable if they have never had a LOG(NO) syscopy event and may not have been removed by MODIFY. In any case list the objects in the DB and start your detective work there. [...] 12228 58 21_Re: Maintenance PTF's12_Sue Janowitz22_sjanowitz@MEDIAONE.NET30_Mon, 5 Nov 2001 17:41:30 -0500456_iso-8859-1 As a DB2 systems programmer, I liked to stay about 3 months back in maintenance. But I was only allowed to apply maintenance once a year, usually, occasionally more often. I would receive PTFs as our PUT tapes came in, which were 3 months in arrears, then Apply Check all of them. I did check the HoldData carefully, and tried not to put on any PTFs that were PE'd unless the problem they were fixing was more important than the PE reason. [...] 12287 56 51_Re: Stored Procs - multiple copies in one subsystem14_Allen, Susan A24_susan.a.allen@BOEING.COM30_Mon, 5 Nov 2001 14:53:38 -0800684_iso-8859-1 -----Original Message----- From: Natalie W. Faulkner [mailto:nfaulknr@GWMAIL.STATE.WV.US] Sent: Monday, November 05, 2001 1:57 PM To: DB2-L@RYCI.COM Subject: Stored Procs - multiple copies in one subsystem



Dear Listers:

We have an web app hitting DB2 for OS/390 V5 using stored procedures.

The problem:

1) We are new to stored Procs

2) We have multiple databases (dev, test, and training) in the same subsystem. How can we utilize the same stored procedures to hit the various databases? The only thing that has worked to access the stored proc is a primary authid (logon id) or spaces in the authid column of sysprocedures. [...] 12344 178 48_Re: SQLCODE -497 : Immediate Assistance Required11_Robert Jans26_robert.jans@ALBERTSONS.COM30_Mon, 5 Nov 2001 15:49:19 -0700336_- Jeff, please note the message text does not say the number 32767 has been exceeded. It says there are 32767 OBIDs and you're trying to create a new one. These do not have to be contiguous. Do a count on the various catalog tables and add them up.

We've had problems with functional ID's in our company like this.......... [...] 12523 46 51_Re: Stored Procs - multiple copies in one subsystem17_Nicholson, Martin20_MNicholson@USSCO.COM30_Mon, 5 Nov 2001 17:32:08 -0600582_- Migrate to V6 and use CURRENT FUNCTION PATH. But you cannot let dist threads go inactive.

We used the authid method until we migrated to V6.

> -----Original Message----- > From: Natalie W. Faulkner [SMTP:nfaulknr@GWMAIL.STATE.WV.US] > Sent: Monday, November 05, 2001 3:57 PM > To: DB2-L@RYCI.COM > Subject: Stored Procs - multiple copies in one subsystem > > Dear Listers: > > We have an web app hitting DB2 for OS/390 V5 using stored procedures. > > The problem: > > 1) We are new to stored Procs > > 2) We have multiple databases (dev, test, and training) in [...] 12570 210 48_Re: SQLCODE -497 : Immediate Assistance Required16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 16:44:46 -0700431_us-ascii Robert,

I concur with your statement, and performed a SELECT COUNT(DISTINCT OBID) FROM SYSIBM.SYSTABLES WHERE DBNAME = MYDBNAME. The result of the query was 436. So, this still doesn't explain why I am unable to use any of the OBIDs which do now not exist.

Regards, Jeff L'Italien American Express





From: "Robert Jans" @RYCI.COM> on 11/05/2001 03:49 PM [...] 12781 37 41_Re: New to os/390 - looking for good book15_Sniderman, Karl21_ksniderman@BCBSOK.COM30_Mon, 5 Nov 2001 17:34:06 -0600377_iso-8859-1 That's easy. The best book I've ever seen on any Computer subject is DB2 Developer's Guide, Fourth Edition (covers through V6) by one of the Listers: Craig S. Mullins. He probably won't respond, out of professional modesty, but I regard it as my bible. It is generally available at Borders Books. The only question is: when will the Fifth Edition on V7 be out? [...] 12819 124 29_Re: Merge Scan to Nested Loop16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Mon, 5 Nov 2001 16:56:21 -0700417_us-ascii David,

How about applying a nested table expression like so:

DECLARE FN_CSR CURSOR FOR SELECT DISTINCT A.I_PAYEE , B.D_PYMT FROM FN_DISBMNT A, (SELECT DISTINCT B.INUM, B.D_PYMT FROM FN_RA WHERE D_PYMT <= :H AND D_PYMT = (SELECT MAX(C.D_PYMT) FROM FN_DISBMNT D , FN_A C WHERE A.I_PAYEE = D.I_PAYEE AND C.I_NUM = D.I_NUM AND C.D_PYMT <= :H)) AS B WHERE A.I_NUM = B.I_NUM ORDER BY A.I_PAYEE; [...] 12944 115 41_Re: New to os/390 - looking for good book14_Philip Gunning24_philip.gunning@QUEST.COM30_Mon, 5 Nov 2001 16:20:08 -0800490_iso-8859-1 Michele, There are three good references that many DBAs I have known have used on a regular basis. They are: DB2 Developers Guide (Craig Mullins, SAMS), DB2 High Performance Design and Tuning (Richard Yevich, Susan Lawson, Prentice Hall), and DB2 for OS/390, Development for Performance (Wiorkowski). They should be available through most online book stores and most local book stores. There is a new cert guide for DB2 for OS/390, (Yevich, Lawson, Prentice Hall). HTH Phil [...] 13060 192 21_Re: Maintenance PTF's13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Mon, 5 Nov 2001 17:17:18 -0800557_iso-8859-1 I know software vendors don't like to listen to your problems unless you have every last PTF applied plus the ones they haven't written yet, but I call 'em anyway, no matter how far behind I am. I like to apply mass maintenance about every 6 months and stay 3 months behind (that is, in April apply PTF's through January), but in practice that comes out to once every 9-12 months. I'm meticulous about examining the SYSTEM HOLD ACTION's and DOC's, but I don't worry about the ones that say "to make this fix effective you have to rebind the [...] 13253 37 25_Re: Padding with 0 in UDB13_Larry Hubbard25_larry_hubbard@YLASSOC.COM30_Mon, 5 Nov 2001 19:47:31 -0600342_iso-8859-1 The DB2 DIGITS function will come close. It converts numeric to absolute value of number and adds left padding of zeros. Format is: digits(numeric-column). It may yield only 9 characters but believe it goes to 10. If it goes to 9 only just concatenate a character zero to the function expression e.g. '0'||digits(num-column). [...] 13291 47 21_Re: Maintenance PTF's25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU30_Mon, 5 Nov 2001 20:11:35 -0600181_iso-8859-1 Yep, if you have IBMLink can also sign up for ASAP tracking by FMID. So you'll get to see what's happening as they occur(before they close). Saved my bacon several times. 13339 33 58_CPU consumption while waiting for connection to BMC AR/CTL9_KLN, Babu24_Babu.K@BLR.HPSGLOBAL.COM30_Tue, 6 Nov 2001 08:33:26 +0500541_- Hi

Could you please explain why CPU consumption will be high while waiting for the connection to BMC AR/CTL.

The scenario is :

we have a batch job which runs a program under AR/CTL. The AR/CTL was not up by the time the job started. The job was waiting for more than 2 hours without giving any message and after two hours gave a message "*732 JOB XXXXX STEP CPU TIME EXCEEDED. REPLY 'WAIT' OR 'CANCEL'. " During the wait period, the CPU consumption was more than 30% and at one point in time it reached even 70%. [...] 13373 18 24_Dynamic SQL dependencies13_Robert Wright17_rwright@LIC.CO.NZ30_Mon, 5 Nov 2001 21:29:57 -0600530_- Hi

We have DB2 for OS/390 V6 with TMON for DB2 installed. Is there any way of finding the objects that a dynamically cached SQL statement is dependent on? ie what is the dynamic SQL CACHE equivalent of SYSIBM.SYSPACKDEP/SYSIBM.SYSPLANDEP and how do I get to it?

Cheers Rob

================================================ 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. 13392 245 48_Re: SQLCODE -497 : Immediate Assistance Required14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 5 Nov 2001 21:35:13 -0600356_- Jeff,

perhaps by now your problem has been solved. But one short term measure to avoid the problem would be to create a new database and tablespaces (use the same ts names), and change everyone's QMF profile (it's just a column in Q.PROFILES) to use the new database. After a suitable period of time you could just drop the current database. [...] 13638 39 27_Re: Cross Subsystem Queries14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 5 Nov 2001 21:39:59 -0600394_- The easiest (and most efficient) is to use the correct db2 subsystem-id in the DSN sub-command, CICS RCT entry etc.

More difficult, is to set up a DDF connection between the sub-systems (which you might want to do anyway), and either: - use three part names for tables - create local aliases on three part names - use CONNECT to go to the other sub-system and use two part names. [...] 13678 65 51_Re: Stored Procs - multiple copies in one subsystem14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 5 Nov 2001 22:03:20 -0600565_- Natalie,

Shery's advice is the best advice you can have.

If you cannot go to V6, then one other alternative is: - in your application code determine the table qualifier( http://jupiter.ryci.com/cgi/wa.exe?A2=ind0001D&L=DB2-L&P=R2626 ). This might have to be a sub-routine because of the dynamicrules(run) requirement. - from that determine a "qualified" sp-name (eg TPROC for a test SP, DPROC for development etc) appropriate to the environment - issue the SQL CALL using a host variable for the sp-name - which contains the qualified sp-name. [...] 13744 80 59_insertion of japanese characters:immediate assistance reqd.10_sweta jain24_sweta2780@REDIFFMAIL.COM30_Tue, 6 Nov 2001 04:47:21 -0000549_iso-8859-1

Hello List

I have a requirement of inserting japanese character to my database and to do the same I created a utf-8 databse with Japanese locale and changed the regional settings for my system to japanese ( I am working on udb 7.1 Windows2000) . so I was able to insert these characters . but now I am facing other problems like my program crashes if I change the locale back to English . so my doubt is that whether or not I m following the right procedure to insert these characters or is it that I cannot change my [...] 13825 20 32_insertion of japanese characters10_Sweta Jain24_sweta2780@REDIFFMAIL.COM30_Mon, 5 Nov 2001 23:10:01 -0600532_- Hello List

I have a requirement of inserting japanese character to my database and to do the same I created a utf-8 databse with Japanese locale and changed the regional settings for my system to japanese ( I am working on udb 7.1 Windows2000) . so I was able to insert these characters . but now I am facing other problems like my program crashes if I change the locale back to English . so my doubt is that whether or not I m following the right procedure to insert these characters or is it that I cannot change my [...] 13846 19 42_Database Migration of DB2 UDB V6.1 to V7.19_SrinivasG18_SRINIVASG@INFY.COM30_Tue, 6 Nov 2001 11:38:38 +0530411_iso-8859-1 Hi, Has anyone done a database migration of DB2 UDB V6.1 to V7.1 on Solaris or any Unix OS ? Any documents available on this ? Please help . Regards, Srinivas G

===============================================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. 13866 100 63_Re: insertion of japanese characters:immediate assistance reqd.12_sushant dash23_dash_dba@REDIFFMAIL.COM30_Tue, 6 Nov 2001 06:19:01 -0000613_iso-8859-1 Hi You have not mentioned what error you are getting. Is it like -332 or length mismatch. Write those things clearly. But here is a concept the code page of application and database should be same. Also while creating the tables have you considered the length factor ? Each CJK(Chinese,Japanese,Korean) Character/Varchar takes two to three bytes. Based on that say one of your field is COL1 CHAR(5) WHEN YOU GO FOR ENGLISH CHARCTER, should be MADE AS COL1 CHAR(10) OR CHAR(15) depending upon your appliaction and space availability. Unless this facility is not there it would expose the system to [...] 13967 67 41_AW: New to os/390 - looking for good book12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 6 Nov 2001 07:31:10 +0100918_iso-8859-1 Michele,

two recommendations: "DB2 DEVELOPERS GUIDE Fourth Edition" from Craig S. Mullins, ISBN 0-672-31828-8 and

"An Introduction to DB2 for OS/390 Version 7" from Susan Graziano Sloan und Ann Kilty Hernandez, ISBN 0-13-019848-X

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, EURurope e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- This e-mail is environment friendly and was made only from recycled electrons. And: The views expressed are my own and do not [...] 14035 20 14_ASCII Database11_Mina Gohari23_mgohari@DPI2.DPI.NET.IR30_Tue, 6 Nov 2001 11:35:54 +0330441_US-ASCII Hi to All,

I have an ASCII Database on OS/390. When I want to use of COALESCE function in Stored Procedure, it will damage the data , How can I solve this problem?



Mina

================================================ 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. 14056 132 48_Re: SQLCODE -497 : Immediate Assistance Required0_18_mebert@AMADEUS.NET30_Tue, 6 Nov 2001 09:24:47 +01001134_us-ascii Try running the following JCL. Substitute your values for sysuid, ssid, and dbname.

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

//sysuidRP JOB (USERJOB,ME),'DB2 UTILITY',CLASS=Y,MSGCLASS=D, // MSGLEVEL=(1,1),REGION=0M,NOTIFY=&SYSUID /*JOBPARM SYSAFF=lpar //*--------------------------------------------------------------------- //* REPAIR Utility: REBUILD DBD //*--------------------------------------------------------------------- //* //*------- Start DB in UT mode ----------------------------------------- //STARTUT EXEC PGM=IKJEFT1B //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(ssid) -TER UTIL(sysuid.REPAIR) -STO DB(dbname) -STA DB(dbname) ACCESS(UT) //*------- REPAIR ------------------------------------------------------ //TEST EXEC PGM=DSNUTILB,PARM='ssid,sysuid.REPAIR' //SYSPRINT DD SYSOUT=* //SYSIN DD * REPAIR DBD TEST DATABASE dbname //*------- REBUILD ----------------------------------------------------- //REBUILD EXEC PGM=DSNUTILB,COND=(8,LT),PARM='ssid,sysuid.REPAIR' //SYSPRINT DD SYSOUT=* //SYSIN DD * REPAIR DBD REBUILD DATABASE [...] 14189 10 29_Re: Merge Scan to Nested Loop15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 6 Nov 2001 01:49:11 -0600291_- And what's about a GROUP BY instead of DISTINCT

================================================ 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. 14200 13 20_Re: V6 DBM1 High CPU10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 6 Nov 2001 10:06:44 +0100346_us-ascii Try APARs Database, I found some entries searching the string "DB2 V6 CPU usage" (exact match).

================================================ 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. 14214 17 11_UNSUBSCRIBE17_Rabindra Senapati21_rsenapati@HOTMAIL.COM30_Tue, 6 Nov 2001 04:48:32 -0600 14232 146 29_Re: Merge Scan to Nested Loop13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Tue, 6 Nov 2001 04:58:33 -0600635_us-ascii David,

I wouldn't be focusing on the join method as the issue, but instead think that the correlated subquery accesses could be improved (list prefetch is required for each subquery probe), and also whether there is the possibility of index-only access on more tables.

Firstly though, is there any way to simplify the query? Although I do not know your data, the inclusion of FN_DISBMNT in the subquery appears redundant (this does depend on the relationship between I_PAYEE and I_NUM). Oddly enough too, the explain output contains FN_A table twice, but does not contain FN_RA which is in the query....and [...] 14379 123 50_Re: Regarding change of sql to improve performance13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Tue, 6 Nov 2001 04:58:37 -0600462_us-ascii Vart,

It is not as clear-cut to say that a join will always outperform a subquery. Although this is true in majority of cases, there are a small number of scenarios where the performance results are reversed.

In addition to Jeff's notes below, if a large table (T1 - 1 million rows) is doing a lookup where the key is in a small table (T2 - 10 rows), then you will benefit greatly from accessing the small table (T2) first in a join. [...] 14503 39 29_IBM Warehouse Manager Problem17_Tomy Widhartomo S28_tomyws@MITRAINFOSARANA.CO.ID30_Tue, 6 Nov 2001 18:07:40 +0700318_ISO-8859-1 Dear all,

We try to do datawarehousing by connecting and extracting data from DB2/400 and put it in the NT server. The Operating system that run on AS/400 is version 4.5 and the NT server is windows 2000. The warehouse manager we use are IBM Warehouse manager version 7.1 (the DB2 version 7.1). [...] 14543 48 33_Question - DSN1PRNT of Image Copy15_Jeffery A Price17_jprice@IPALCO.COM30_Tue, 6 Nov 2001 07:08:59 -0500484_us-ascii Hello List!

I am trying to use DSN1PRNT to obtain OBID to recover dropped TABLES. I am receiving strange error, as listed below. I searched IBMLINK, and found no bugs. Is there something that I am missing?

IEC020I 001-4,STUL1SPL,JS010,SYSUT1,0EF1,C08608, IEC020I INP.D2.IMG.DBXPRT56.SUMHIST.WL1000.G0048V00 IEC020I DCB EROPT=ABE OR AN INVALID CODE, AND/OR NO SYNAD EXIT SPECIFIED IEA995I SYMPTOM DUMP OUTPUT SYSTEM COMPLETION CODE=001 REASON CODE=00000004 [...] 14592 55 41_Re: New to os/390 - looking for good book12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM30_Tue, 6 Nov 2001 07:19:39 -0500679_iso-8859-1 I agree with Karl and also am awaiting announcement for the V7 edition ** excellent resource of information **.

Susan Loria Nielsen Media Research

-----Original Message----- From: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] Sent: Monday, November 05, 2001 6:34 PM To: DB2-L@RYCI.COM Subject: Re: New to os/390 - looking for good book



That's easy. The best book I've ever seen on any Computer subject is DB2 Developer's Guide, Fourth Edition (covers through V6) by one of the Listers: Craig S. Mullins. He probably won't respond, out of professional modesty, but I regard it as my bible. It is generally available at Borders Books. [...] 14648 94 41_Re: New to os/390 - looking for good book12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM30_Tue, 6 Nov 2001 07:33:49 -0500317_iso-8859-1 Michele,

You mention not only are you new to DB2 for OS/390, but also mainframes - and there are some good books for understanding more about mainframes to that can be used in your learning experience - -- go to this website and review the ABCs of System Programming Redbooks (by IBM authors): [...] 14743 237 48_Re: SQLCODE -497 : Immediate Assistance Required19_Michael Piontkowski34_michael_piontkowski@COMPUSERVE.COM30_Tue, 6 Nov 2001 07:34:51 -0500466_iso-8859-1 Don't forget SYSTABLESPACE & SYSINDEXES & maybe others. The next question is do you count OBID or PSID in SYSTABLESPACE and do you count OBID or ISOBID in SYSINDEXES?





Mike Piontkowski

-----Original Message----- From: owner-db2-l@RYCI.COM [mailto:owner-db2-l@RYCI.COM]On Behalf Of Jeff A L'Italien Sent: Monday, November 05, 2001 18:45 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] SQLCODE -497 : Immediate Assistance Required [...] 14981 17 15_Re: unload data12_David Harvey17_dmh@ONETEL.NET.UK30_Tue, 6 Nov 2001 06:37:07 -0600449_- You can indeed mess about with conversion of crude text into (say) a spreadsheet with all the pitfalls that go with it .....OR..... you could try QMF for Windows. See http://www-4.ibm.com/software/data/qmf/

There is a free 60 day trial but it comes with a superb interface to Excel and Lotus with a built in interface so all you do is click on your SQL and just watch the results drop into place. You will also find it on your UDB CD. [...] 14999 85 21_Re: Maintenance PTF's11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Tue, 6 Nov 2001 08:03:19 -0500352_iso-8859-1 We are using methods very similar. It is working OK for us on 9 LPARS and 11 DB2 subsystems. I think currency may be more critical if you are data sharing, which we are not.

-----Original Message----- From: Taddei, Cathy [mailto:Cathy.Taddei@PACIFICORP.COM] Sent: Monday, November 05, 2001 8:17 PM Subject: Re: Maintenance PTF's [...] 15085 17 45_Re: OS/390 DB2 Version 7 with QMF Version 3.313_Barb Milligan25_Barb_Milligan@CARGILL.COM30_Tue, 6 Nov 2001 06:57:41 -0600366_ISO-8859-1 We are in the process of upgrading from V5 to V7. QMF came with V7. It is no longer a separate product.

Barb

===============================================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. 15103 41 48_Re: SQLCODE -497 : Immediate Assistance Required11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Tue, 6 Nov 2001 08:36:49 -0500638_ISO-8859-1 Jeff

Don't forget the other object IDs that count towards the 32767 limit. These are: two per tablespace (OBID and PSID in SYSTABLESPACE) and two for each index (OBID and ISOBID in SYSINDEXES).

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

Message text written by DB2 Data Base Discussion List >Robert,

I concur with your statement, and performed a SELECT COUNT(DISTINCT OBID) FROM SYSIBM.SYSTABLES WHERE DBNAME = MYDBNAME. The result of the query was 436. So, this still doesn't explain why I am unable to use any of the OBIDs which do now not exist. [...] 15145 18 48_Re: SQLCODE -497 : Immediate Assistance Required17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Tue, 6 Nov 2001 15:01:21 +0100334_us-ascii If you don't care about being able to recover the tablespace back to a prior point in time (big IF there), the fastest way to shrink the DBD and free all unused xxIDs is to perform a REPAIR REBUILD DBD. In this case I definitely recommend taking an image copy immidiately after the REBUILD DBD to allow recovery at all. [...] 15164 87 45_Re: OS/390 DB2 Version 7 with QMF Version 3.311_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Tue, 6 Nov 2001 09:09:07 -0500532_iso-8859-1 QMF 3.3 worked fine with our DB2 V6 system. We upgraded QMF a few months after we upgraded DB2.

Tina Hilton Bertelsmann mediaSystems

-----Original Message----- From: sflindsey@HIGHLIGHTS-CORP.COM [mailto:sflindsey@HIGHLIGHTS-CORP.COM] Sent: November 05, 2001 1:18 PM To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Version 7 with QMF Version 3.3



You have to upgrade QMF. I am in the process of going to DB2 V6 and we had to move to QMF V6. HTH! Scott Lindsey Sr. DBA, Highlights for Children [...] 15252 115 45_Re: OS/390 DB2 Version 7 with QMF Version 3.39_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Tue, 6 Nov 2001 14:41:39 -0000583_iso-8859-1 Same here.

-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: 06 November 2001 14:09 To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Version 7 with QMF Version 3.3



QMF 3.3 worked fine with our DB2 V6 system. We upgraded QMF a few months after we upgraded DB2.

Tina Hilton Bertelsmann mediaSystems

-----Original Message----- From: sflindsey@HIGHLIGHTS-CORP.COM [mailto:sflindsey@HIGHLIGHTS-CORP.COM] Sent: November 05, 2001 1:18 PM To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Version 7 with QMF Version 3.3 [...] 15368 132 45_Re: OS/390 DB2 Version 7 with QMF Version 3.318_Gaston, Raymond J.17_GastonRay@ORU.COM30_Tue, 6 Nov 2001 10:09:52 -0500398_iso-8859-1 I think the initial question has still gone unanswered: Does "QMF 3.3 work with DB2 V7"? * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ** * * * * * * * * * * -Ray -----Original Message----- From: Jim Leask [mailto:jim.leask@RS-COMPONENTS.COM] Sent: Tuesday, November 06, 2001 9:42 AM To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Version 7 with QMF Version 3.3 [...] 15501 85 41_Re: New to os/390 - looking for good book23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 6 Nov 2001 15:07:03 -0000468_iso-8859-1 also check out Gabrielle Wiorkowski's book ..

http://www.gabrielledb2.com

I use this a lot....

Leslie

-----Original Message----- From: Loria, Susan [mailto:Susan_C_Loria@TVRATINGS.COM] Sent: Tuesday, November 06, 2001 12:20 PM To: DB2-L@RYCI.COM Subject: Re: New to os/390 - looking for good book



I agree with Karl and also am awaiting announcement for the V7 edition ** excellent resource of information **. [...] 15587 28 13_Cluster index10_praveen_kj19_praveen_kj@INFY.COM30_Tue, 6 Nov 2001 21:11:08 +0530387_iso-8859-1 Hi All,

Is it necessary to define at least one cluster index per table (or is it a good design practice to make at least one cluster index per table)

If you don't define cluster index explicitly then the first index defined on the table serves as cluster index?

Now what if a table which has no index defined on it? (In this case what willl happen?) [...] 15616 20 48_Re: SQLCODE -497 : Immediate Assistance Required16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Tue, 6 Nov 2001 09:07:37 -0700330_us-ascii Thanks to all who responded to this problem we were having. As it turns out, Tanya Haverchak was correct in that APAR PQ45758 for DB2 V6 addresses the problem. In a nutshell, the MODIFY utility is not reclaiming the space from the dropped objects within the database.

Regards, Jeff L'Italien American Express [...] 15637 63 45_FW: OS/390 DB2 Version 7 with QMF Version 3.313_Barb Milligan25_Barb_Milligan@CARGILL.COM30_Tue, 6 Nov 2001 10:07:17 -0600435_ISO-8859-1 DB2 V7 just costs more. So QMF is not free, its just included.

Barb

-----Original Message----- From: Paul.Martin@ecolab.com [mailto:Paul.Martin@ecolab.com] Sent: Tuesday, November 06, 2001 7:45 AM To: Paul.Martin@ecolab.com; Milligan, Barb /hdqt Subject: RE: OS/390 DB2 Version 7 with QMF Version 3.3



We are still on version 6 and I haven't started on justification to upgrade to version 7 [...] 15701 29 14_RRSAF and RACF0_19_mike.holmans@BT.COM30_Tue, 6 Nov 2001 16:11:18 -0000453_iso-8859-1 I know it's *somewhere* in the "friendly" manuals, but I've been searching for hours and I can't find it.

Once RRS has been set up, you need to give DB2 permission to use it, or something like that. And I know I saw a reference to what to do, but I cannot find it again, despite trying to go through the redbook on Getting Started With Stored Procedures and the MVS manuals on Setting Up a Sysplex and Resource Recovery Services. [...] 15731 48 18_Re: RRSAF and RACF13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Tue, 6 Nov 2001 10:26:45 -0600633_US-ASCII Hi Mike, Is this what your looking for ?

http://www-4.ibm.com/software/data/db2/os390/cc390/

Follow links Install Reference then Setup RRS HTH Kurt



>>> mike.holmans@BT.COM 11/06/01 10:11AM >>> I know it's *somewhere* in the "friendly" manuals, but I've been searching for hours and I can't find it.

Once RRS has been set up, you need to give DB2 permission to use it, or something like that. And I know I saw a reference to what to do, but I cannot find it again, despite trying to go through the redbook on Getting Started With Stored Procedures and the MVS manuals on Setting Up a [...] 15780 59 17_Re: Cluster index0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 6 Nov 2001 10:29:31 -0600394_us-ascii The short answer is "maybe".

The longer answer is if you don't DB2 will do so implicitly, and it may change if you happen to drop and recreate the index it picked (which can cause unexpected run time changes). So since you (or your applications developers) can decide which sort order will be most often used, it makes the most sense to explictly define the cluster index. [...] 15840 160 48_Re: SQLCODE -497 : Immediate Assistance Required9_Chris Tee23_chris.tee@UK.ZURICH.COM30_Tue, 6 Nov 2001 10:42:35 +0000396_us-ascii Jeff,

Was your MODIFY run with DELETE AGE(*)? I think all SYSCOPY entries have to be removed for the tablespace before the OBIDs are cleared from the DBD and also any reference to them in SYSLGRNX. Perhaps any listers from IBM/BMC/CA who work on utilities can clarify this. As other people have mentioned, the best long term solution is to give each user their own database. [...] 16001 71 18_Re: RRSAF and RACF17_Massimo Biancucci30_massimo.biancucci@T-SYSTEMS.IT30_Tue, 6 Nov 2001 18:03:48 +0100490_us-ascii If i'm not wrong you have to authorize the address space owner (for example the WLM managed AS for Stored Procedures) in READ to the DSNR.RRSAF class.

Best regards.











mike.holmans@BT.COM@RYCI.COM> on 06/11/2001 17.11.18

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: RRSAF and RACF [...] 16073 79 18_Re: RRSAF and RACF13_Ellen Richter19_e2richte@HEWITT.COM30_Tue, 6 Nov 2001 11:04:41 -0600619_us-ascii Mike.....this is what you're looking for:

the RRSAF needs to be defined to the RACF class DSNR.





|--------+-------------------------------> | | Kurt Sahlberg | | | | | | | | | 11/06/2001 10:26 AM | | | Please respond to DB2| | | Data Base Discussion | | | List | | | | |--------+-------------------------------> >------------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: | | Client: | | Subject: Re: RRSAF and RACF | >------------------------------------------------------------------------| [...] 16153 114 18_Re: RRSAF and RACF13_Andy Seuffert25_Andy.Seuffert@NEONSYS.COM30_Tue, 6 Nov 2001 11:16:55 -0600409_iso-8859-1 From the DB2 Administration Guide:

3.4.6.3.1 Step 1: Control access by using the attachment facilities (required)

The user ID that is associated with the DB2-established address space must be authorized to run the DB2 call attachment facility. It must be associated with the ssnm.BATCH profile, as described in "Define the names of protected access profiles" in topic 3.4.6.1.1. [...] 16268 187 37_Re: Question - DSN1PRNT of Image Copy13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Tue, 6 Nov 2001 09:21:11 -0800309_iso-8859-1 Could be an I/O error -- was there a message IEA000I as well? Any funny messages in the syslog? If C08608 is a tape volume, you may want to check your tape management system to see if it has been overwritten by another job. Also verify that it is SL and this file is file 1. hth, Cathy Taddei [...] 16456 123 21_Re: Maintenance PTF's12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Tue, 6 Nov 2001 12:42:00 -0500291_iso-8859-1 If I were you, I'd pick them off in small chunks, say maybe the PRPs first, then the HIPERS, then the PUTs until you are roughly 3 months behind, then try to maintain a schedule somewhere around a 3 month lag.

My strategy: Systems sandbox = All non-PE PTFs to current [...] 16580 108 45_Re: OS/390 DB2 Version 7 with QMF Version 3.312_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Tue, 6 Nov 2001 13:00:57 -0500444_iso-8859-1 Nope, V3.1 works fine in a V6 MIGRATION, so I'd imagine 3.3 does as well. If you are creating a new subsystem, thus performing an INSTALL of V6, you will fail when you attempt to bind the QMF plans. But as long as you are only migrating it and don't rebind them, then your old QMF will continue to work. We have a MIGRATEd V6 subsystem using QMF 3.1. We also have a V6 subsystem INSTALLed from scratch where QMF 3.1 won't work. [...] 16689 80 17_Re: Cluster index0_19_Tim.Lowe@STPAUL.COM30_Tue, 6 Nov 2001 12:08:26 -0600504_us-ascii Praveen, If you do not have an "explicit" clustering index, IBM's reorg will not change the clustering order of the data. (This also means that you cannot use SORTDATA, and you will need additional datasets in order to use online reorg.)

However, if you have an "implicit" clustering index, DB2 will attempt to use it to determine the "optimal place" to insert new rows. Over time, since the reorg will not use the "implicit" clustering index, the cluster ratio will likely go down. [...] 16770 50 8_SQL Help0_29_sflindsey@HIGHLIGHTS-CORP.COM30_Tue, 6 Nov 2001 13:08:39 -0500557_us-ascii The platform is DB2/OS390 V5 and MVS. Here is my SQL statement:

SELECT T1210.EFRT_KEY_NBR ,T1210.ACCT_NBR FROM PROD.T1500_EFFORT_KEY T1500 ,PROD.T1210_ORDER T1210 WHERE T1500.LIST_YR = 2001 AND T1500.CMPGN_CDE IN ('XDR','XNW','XPR') AND T1210.EFRT_KEY_NBR = T1500.EFRT_KEY_NBR GROUP BY T1210.EFRT_KEY_NBR Here are partial results from the query: EFRT KEY ACCT NBR NBR ------------ -------------- 248169 7120884 248169 380992701 248169 401501176 248169 402179998 262156 323568 262156 1552777 262156 2286490 262156 3269149 262156 3897089 [...] 16821 95 12_Re: SQL Help16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Tue, 6 Nov 2001 11:52:29 -0700378_us-ascii Scott, I guess my first question is, why the GROUP BY clause in your statement? Since you're not using any type of function like COUNT, AVG, MIN, etc., and since you don't include account number in the grouping, and there are 1 are more account numbers per EFRT_KEY_NBR, is it really required? Nonetheless, the modified SQL should provide what you're looking for: [...] 16917 61 53_Help for migration from Oracle to DB2 (LPAD Function)27_Alonso Verdugo/Colombia/IBM18_alonsov@CO.IBM.COM30_Tue, 6 Nov 2001 14:00:28 -0500683_iso-8859-1 Problem : We needed to implement a Oracle function in DB2 call LPAD. The format of the function is LPAD(string1, integer, string2). For example : lpad('1234', 8, 'x') and the results must be : 1 -------- xxxx1234 . however, we obtained : 1 --------------------etc. --------------------etc. ... xxx1234 The previous result generates problems, due to the amount of characters before result string. . At the moment we have implemented the function of the following form : CREATE FUNCTION LPAD(X varchar(4000), LEN int, S varchar(4000)) RETURNS varchar(1000) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN case when len-length(x)>0 then substr( concat [...] 16979 17 15_SYSCOPY cleanup0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 6 Nov 2001 13:42:48 -0600335_us-ascii DB2 V6 OS390 While doing some cleanup in SYSCOPY I've found that we have entries for some dropped tables (ie SYSLGRNG, table name changed for V6). Trying to MODIFY them out doesn't work, I get error saying that there is no such table (duh! ;-) ) and I haven't found any suggestions in the manuals how to get rid of them. [...] 16997 13 24_Striping DB2 active logs12_Doug Frankum26_doug_frankum@HOMEDEPOT.COM30_Tue, 6 Nov 2001 13:13:42 -0600352_- Has anybody tried striping their DB2 active logs. If so what kind of results have you seen?

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. 17011 169 17_Re: Cluster index14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM30_Tue, 6 Nov 2001 12:11:26 -0800471_iso-8859-1 I thought that IBM used the oldest index (lowest object ID) when there wasn't an explicit cluster index.

Is that not true?

Thanks,

Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719



-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Tuesday, November 06, 2001 10:08 AM To: DB2-L@RYCI.COM Subject: Re: Cluster index [...] 17181 19 21_Re: Maintenance PTF's12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Tue, 6 Nov 2001 14:24:23 -0600421_us-ascii Thank you for all of the excellent suggestions!

I'm going to try and synthesize your thoughts into a proposal to our systems group.

Thanks again.

Stg

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17201 40 28_Re: Striping DB2 active logs16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 6 Nov 2001 15:30:42 -0500318_iso-8859-1 HEY DOUG!!!

We have not done this yet but will soon with MQ logs. They have the same issues as DB2 logs. We sometimes experience log constraints when we need to do more than about 400 physical log writes per second for MQ. Striping is our next thing to try. When we do I will post results here. [...] 17242 46 20_RE : SYSCOPY cleanup21_Plourde Renaud (4320)29_renaud.plourde@RRQ.GOUV.QC.CA30_Tue, 6 Nov 2001 16:15:59 -0500660_windows-1252 I recently had the same problem. You can look at apar PQ48311.





Renaud Plourde Regie des rentes du Quebec



-----Message d'origine----- De : Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Envoyé : 6 novembre, 2001 14:43 À : DB2-L@RYCI.COM Objet : SYSCOPY cleanup



DB2 V6 OS390 While doing some cleanup in SYSCOPY I've found that we have entries for some dropped tables (ie SYSLGRNG, table name changed for V6). Trying to MODIFY them out doesn't work, I get error saying that there is no such table (duh! ;-) ) and I haven't found any suggestions in the manuals how to get rid of them. [...] 17289 22 21_Re: Maintenance PTF's25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU30_Tue, 6 Nov 2001 15:20:00 -0600426_iso-8859-1 Guess the thought I left out 'cause I do both OS/390 and DB/2(and assume everybody knows that the right and left hand know what they're doing) maint is a "coordinated effort" approach. We have a test lpar and do maint over there to see if we hurt anything or helped as the case may be. Then we try to run at least weekly and monthly closes(our bigger batch jobs) and see if everybody's happy and cross-footed. [...] 17312 61 17_Re: Cluster index12_Raymond Bell17_rbell@NZ1.IBM.COM30_Wed, 7 Nov 2001 10:09:56 +1300556_us-ascii Hi Daryl (Tim?),

Yeah, there's something like that. I think DB2 will try to keep clustering sequence according to the 'oldest' index but I'm not sure. What I am fairly sure about (gee, nothing like committing, is there?) is that dropping and recreating the indexes may effect a new default clustering index, depending on the order you create them and/or the OBIDs chosen by DB2. Also, as there's no actual clustering index, reorgs to re-establish clustering sequence won't do squat, although they will still honour %free and freepage. [...] 17374 52 19_Re: SYSCOPY cleanup10_Price, Ray18_Ray.Price@DRKW.COM30_Tue, 6 Nov 2001 21:24:38 -0000746_iso-8859-1 Hi.

Have a look at PQ48311.

PROBLEM DESCRIPTION: MSGDSNU054I ON MODIFY RECOVERY UTILITY ON THE OBSOLETE TABLE SPACE DSNDB01.SYSLGRNG





Regards,

Ray Price Database Manager Dresdner Kleinwort Wasserstein London

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: 06 November 2001 19:43 To: DB2-L@RYCI.COM Subject: SYSCOPY cleanup

DB2 V6 OS390 While doing some cleanup in SYSCOPY I've found that we have entries for some dropped tables (ie SYSLGRNG, table name changed for V6). Trying to MODIFY them out doesn't work, I get error saying that there is no such table (duh! ;-) ) and I haven't found any suggestions in the manuals how to [...] 17427 97 17_Re: Cluster index0_19_Tim.Lowe@STPAUL.COM30_Tue, 6 Nov 2001 15:34:11 -0600370_us-ascii Daryl, I had written a rather detailed message. Exactly what are you questioning?

Have you tried to reorg a DB2 table that does not have an explicit clustering index? Have you tried specifying SORTDATA on it? Have you tried an online reorg on it? Have you checked the clustering ratio after the reorg? Give it a try, it can't hurt.........much. (-: [...] 17525 112 17_Re: Cluster index12_Susan Lawson24_susan_lawson@YLASSOC.COM30_Tue, 6 Nov 2001 16:50:05 -0600583_iso-8859-1 RE: Cluster indexPraveen and Daryl,

If a clustering index has not been explicitly defined, DB2 will use the current oldest surviving index. Some call this the first index created, but contrary to popular belief, the default index chosen for insert processing is not the first index created on the table nor the index with the lowest OBID (object identifier). For example, if there were indexes A, B, C, and D on a table, and they had been defined in that order, and none was defined as the clustering index, index A would be chosen for inserts. If index A was [...] 17638 74 17_Re: Cluster index13_BL.Tink Tysor16_tink@BLTYSOR.COM30_Tue, 6 Nov 2001 16:03:46 -0800638_us-ascii Susan,

Are you sure that with no explicit index defined that reorg will choose the oldest as in inserts. I thought that with no explicit clustering index, reorg would not reorder the data, just honor pctfree and freepage, and rebuild compression dictionary (unless keep dictionary was specified).

Tink --- Susan Lawson wrote: > RE: Cluster indexPraveen and Daryl, > > If a clustering index has not been explicitly > defined, DB2 will use the > current oldest surviving index. Some call this the > first index created, but > contrary to popular belief, the default index chosen > for [...] 17713 121 17_Re: Cluster index0_19_Tim.Lowe@STPAUL.COM30_Tue, 6 Nov 2001 18:07:24 -0600613_iso-8859-1 Susan, I can see the same thing that you quote in the book "DB2 Answers!" that you helped to co-write. (on page 123) However, in the DB2 UDB V6 Utility Guide, subtopic 2.16.2.5.1, "Sorting data in clustering order" it says: If no explicit clustering index exists, SORTDATA is ignored. When SORTDATA is not specified: If an explicit clustering index exists, segmented table spaces are unloaded using that index If an explicit clustering index does not exist, the table space is unloaded by table. Multi-table simple table spaces are unloaded by table space scan, in which case rows are reloaded in [...] 17835 25 29_SQL activity at a table level13_Olson, Carlos14_COlson@QRS.COM30_Tue, 6 Nov 2001 17:52:21 -0800537_iso-8859-1 Hello all, How can I determine the amount of SQL activity (INSERT, UPDATE, DELETE, SELECT) for a given table over a given time period? What reporting tools are available?

OS/390 DB2V6.1

Thanks

Carlos Olson Database Administrator QRS Corporation http://www.qrs.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. 17861 21 34_An example DF/DSS move job, please12_Raymond Bell17_rbell@NZ1.IBM.COM30_Wed, 7 Nov 2001 15:37:14 +1300515_us-ascii Colleagues,

Back in the land of the Long Moisture-Laiden Cloud now and looking for a sample DF/DSS move job. Anyone feel like e-mailing me one? I've moved employers (no, really?) and am starting my JCL collection again.

Cheers,



Raymond

================================================ 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. 17883 37 33_Re: SQL activity at a table level14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 6 Nov 2001 20:34:47 -0600384_- Carlos

Have you looked at the Statistics traces eg -START TRACE(STAT) CLASS(1). You can roll your own product to extract the IFCID records (see the Appendix "Interpreting DB2 trace output" in the Admin Guide for the structure of IFCID records and for the macros that map their content) or use a commercial product (DB2 PM or an ISV products) to interpret their content. [...] 17921 63 38_Re: An example DF/DSS move job, please14_Vivoda, Robert30_Robert.Vivoda@TEAM.TELSTRA.COM30_Wed, 7 Nov 2001 14:22:39 +1100511_- //XXXXXXXM JOB (XXXXXXXX),'RAYMOND.BELL',CLASS=C,MSGCLASS=X, // NOTIFY=XXXXXXX,REGION=0M //* //* MOVE DATASET FROM QRN004 TO QRN070 //* //DFSMOV1 EXEC PGM=ADRDSSU //INVOL1 DD UNIT=3390,VOL=SER=QRN004,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSIN DD * COPY DATASET( - INC( - A.B.C.D.** - )) - LOGINDD(INVOL1) - OUTDYNAM(QRN081) - ALLMULTI - CATALOG - DELETE /* Regards

Robert Vivoda Technical Services, Environment Maintenance and Capacity Planning Group 3rd Floor, 484 St.Kilda Rd, Melbourne, Australia [...] 17985 72 57_Re: Help for migration from Oracle to DB2 (LPAD Function)14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 6 Nov 2001 20:56:54 -0600420_- Alonso

What did you actually receive and how are you displaying the result? If you are using SPUFI to display the result, then be aware that SPUFI looks at the data type (varchar(1000)) to be displayed and formats appropriately for the longest possible string - 1000 characters. If the this is longer than LRECL of the output dataset, SPUFI wraps the heading and data lines to provide the necessary space. [...] 18058 29 29_Foreign Key problem (RC -530)13_Othman Kidwai22_kidwaiom@ARAMCO.COM.SA30_Tue, 6 Nov 2001 23:52:44 -0600556_- Hi all,

I did ALTER TABLE to add 2 new foreign keys (table already had 2 foreign keys), then the analyst requested DROP of the newly added FKs and later reported that he's getting RC -530 (which means value inserted not in parent table) when he executes an online pgm to update this table (using one the columns that was previously defined as a FK). I checked the catalog to confirm the non-existence of the dropped FKs in both the table itself and also in the SYSFOREIGNKEYS table. I was able to do the update(same row) thru QMF without any [...] 18088 56 0_5_Priya17_deswalp@YAHOO.COM30_Tue, 7 Nov 2000 11:46:03 +0530210_iso-8859-1 Hi

Can anyone tell me how the new DB2 registry variable DB2_NEWLOGPATH2 can be used for multiplexing of log files.Where do you specify it in registry for a particular database.

Priyanka 18145 16 40_Sarah Ellis/UK/IBM is out of the office.11_Sarah Ellis17_sellis@UK.IBM.COM30_Wed, 7 Nov 2001 08:08:00 +0000474_us-ascii I will be out of the office starting November 6, 2001 and will not return until November 8, 2001.

I will respond to your message when I return. Alternatively please contact my colleague Ian Cook (ian_cook@uk.ibm.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. 18162 14 33_Re: SQL activity at a table level15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 7 Nov 2001 01:34:41 -0600413_- Hi James

I think, with the statistics class(1) trace, I cget only SQL activity for a time period, not on a table level. That's, I guess, nearly impossible, is it?

================================================ 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. 18177 242 31_BP7 and DSNDB07 / IMTH and DMTH10_Fritz Rapp21_fritzrapp@T-ONLINE.DE30_Wed, 7 Nov 2001 03:06:45 -0600599_- Hi bufferpool-ers, i have a question concerning BP7 und DSNDB07(non datasharing). All the information below belongs to a productive system(DBM1 size already about 1,7 GB, large EDMPool and therefore SRTPOOL-Zparm only 185K). Now I took a look at BP7 and DSNDB07 and found some things that I don't like, but I'm not quite sure what to do! All relevant data was obtained via Candle/Omegamon Monitor and are marked bold. The changeable thresholds for BP7 are VPSEQT=80%, DWQT=50% und VDWQT=10%. The value for IMTH is over 700 and fpr DMTH even over 20.000! In BP7 reside only the workfile TS no [...] 18420 32 8_IKJEFT0120_MANIKHANDAN Ramasamy21_RManikha@COVANSYS.COM30_Wed, 7 Nov 2001 05:17:52 -0500412_- Hi List,

We have a Requirement to Extract datas from a DB2 Table. We are now using IKJET01 to extract the datas.

Is it Possible to Display the Column names as Header, along with the extracted data using IKJEFT01? Or is there any other Batch Utility to achieve this task?

We know that, we can display the Column Names using AS Clause in Spufi. This job should run daily in Production. [...] 18453 31 34_No Foreign Key but getting rc -53013_Othman Kidwai22_kidwaiom@ARAMCO.COM.SA30_Wed, 7 Nov 2001 04:51:46 -0600566_- Hi all,

I did ALTER TABLE to add 2 new foreign keys each containing 2 columns (this table already had 3 Fkeys on other columns). I had to DROP these newly added FKs due to a request from an analyst. The analyst reported encountering RC -530 (which basically means value inserted not in parent table) when executing an online pgm to update this table (using one of the columns that was previously defined as a FK). I checked the Catalog to confirm the non-existence of the dropped FKs in both the table itself and also in the SYSFOREIGNKEYS and SYSRELS [...] 18485 86 21_Fw: DB2 V6 OBID query11_James Szabo18_jim.szabo@CORE.COM30_Wed, 7 Nov 2001 07:38:23 -0500384_iso-8859-1 Mike (and others),

"object IDs" are assigned in DB2 UDB for OS/390 V6 as follows:

tablespace obid tablespace psid table obid auxiliary table relobid trigger obid link obid check obid index obid index isobid relation obid1 (parent) relation obid2 (child)

I am posting a query to the DB2-L Documents list, to determine all the OBIDs for a database: [...] 18572 44 17_Re: Cluster index14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Wed, 7 Nov 2001 06:31:51 -0600322_iso-8859-1 Tim,

"DB2 Answers" was initially written 4 DB2 versions ago, although newer DB2 versions came out while it was being written. Therefore, it, as well as any book on technology, is probably out of date on "some" issues, the day it was released. Therefore the current versions of this topic are below: [...] 18617 59 26_Problems with CICS and DB222_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR30_Wed, 7 Nov 2001 10:32:58 -0200445_iso-8859-1 Our CICS personnel are saying that there is a CICS/DB2 application which is causing severe queueing in CICS, eventually leading it unoperable. However, the total application times for this CICS/DB2 program are not that high, about 5-10 seconds, and its DB2 times are quite small. The DSNC DISPLAY STATISTICS shows the following for SKPR transaction (most DB2 threads, including the suspect one, run under this CICS transaction): [...] 18677 114 17_Re: Cluster index14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Wed, 7 Nov 2001 06:40:04 -0600498_iso-8859-1 RE: Cluster indexDaryl,

Lowest ODIB is not necessarily the oldest index. Actually, think of it as a chain of indexes, on an object, in a list. When one is removed, the next one becomes current. From an understanding point, it is basically that simply.

Richard Yevich -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Johnson, Daryl Sent: Tuesday, November 06, 2001 2:11 PM To: DB2-L@RYCI.COM Subject: Re: Cluster index [...] 18792 65 35_Re: BP7 and DSNDB07 / IMTH and DMTH14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 7 Nov 2001 07:58:11 -0500433_ISO-8859-1 Fritz,

You also have counts of prefetch failing because of NO Buffers Available.

First your pool is too small to support the sort workload, but making it bigger may impact other things since there is already paging for the pool buffers and you said that DBM1 is at 1.7 Gig.

As a temporary measure, I recommend taking 5000 buffers away from BP0 and giving them to BP7. Increase the VPSEQT to 95%. [...] 18858 40 33_Re: SQL activity at a table level13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Wed, 7 Nov 2001 07:25:50 -0600354_iso-8859-1 Hi Walter,

There are log processing tools available which have auditing capabilities with which you can specify selection criteria as well as order information for reporting. These tools all have additional functionality in addition to audit reporting. BMC - Log Master for DB2, CA - DB2 Log Analyzer, IBM - DB2 Log Analysis Tool. [...] 18899 61 28_Re: Striping DB2 active logs12_Doug Frankum26_Doug_Frankum@HOMEDEPOT.COM30_Wed, 7 Nov 2001 08:31:27 -0500589_us-ascii Thanks Eric







DB2 Data Base Discussion List 11/06/2001 03:30 PM



Please respond to DB2 Data Base Discussion List

To: DB2-L@ryci.com cc: Subject: Re: Striping DB2 active logs



HEY DOUG!!!

We have not done this yet but will soon with MQ logs. They have the same issues as DB2 logs. We sometimes experience log constraints when we need to do more than about 400 physical log writes per second for MQ. Striping is our next thing to try. When we do I will post results here. [...] 18961 75 45_Re: OS/390 DB2 Version 7 with QMF Version 3.311_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Wed, 7 Nov 2001 08:42:41 -0500267_iso-8859-1 Are you sure? I just looked at the web, and it still looks to me like a separate feature that can be ordered or not ordered. Of course, I've always had trouble reading their price lists so I may be wrong, but I see QMF listed with a price next to it. [...] 19037 51 12_DSN Commands22_Augustine, Jobi M B22524_Jobi.Augustine@CIGNA.COM30_Wed, 7 Nov 2001 08:46:35 -0500730_iso-8859-1 Hi List,

Is there any document where I can get all the DB2 DSN Commands (Database Commands like -TERM UTIL) and the description.

Thanks in advance !! Jobi.



------------------------------------------------------------------------------ CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. © Copyright 2001 [...] 19089 136 18_Re: RRSAF and RACF0_19_mike.holmans@BT.COM30_Wed, 7 Nov 2001 13:50:10 -0000719_iso-8859-1 Andy and the others who replied,

Thanks very much.

Mike H

>>-----Original Message----- >>From: Andy Seuffert [mailto:Andy.Seuffert@NEONSYS.COM] >>Sent: Tuesday, November 06, 2001 5:17 PM >>To: DB2-L@RYCI.COM >>Subject: Re: [DB2-L] RRSAF and RACF >> >> >>From the DB2 Administration Guide: >> >>3.4.6.3.1 Step 1: Control access by using the attachment facilities >>(required) >> >>The user ID that is associated with the DB2-established address space >>must >>be authorized to run the DB2 call attachment facility. It must be >>associated with the ssnm.BATCH profile, >>as described in "Define the names of protected access >>profiles" in topic >>3.4.6.1.1. >> >>The user ID that is [...] 19226 13 17_Re: Reorg pending0_21_BudGreenman@ONGOV.NET30_Wed, 7 Nov 2001 09:01:27 -0500332_us-ascii Thanks to all who responded. I used the SORTDATA option and this took care of it.

================================================ 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. 19240 62 12_Re: IKJEFT0116_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Wed, 7 Nov 2001 07:10:04 -0700457_us-ascii Mani,

If your shop has QMF, then this can be invoked in batch, and should provide you with the type of extract/report that you require.

Regards, Jeff L'Italien American Express





From: "MANIKHANDAN Ramasamy" @RYCI.COM> on 11/07/2001 05:17 AM EST

Please respond to "DB2 Data Base Discussion List"

Sent by: "DB2 Data Base Discussion List" [...] 19303 26 18_Database of choice16_cristi radulescu15_depends@XNET.RO30_Wed, 7 Nov 2001 15:58:38 +0200383_iso-8859-1 i have to port an SQL Server 2000 based ERP to IBM DB2 7.2 on Linux or Oracle 9i on Win2000.

do you have any suggestion based on experience?

the ERP facts:

40+ users; 600k queries /day from which 3k > 1 sec (SQL Server statistics) hardware: Proliant 6000 dual Xeon 500Mhz, 512 L2 cache, 2 Gb RAM memory, 54 Gb SCSI 2 way RAID (Raid 1 and Raid 5) [...] 19330 97 45_Re: OS/390 DB2 Version 7 with QMF Version 3.327_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM30_Wed, 7 Nov 2001 09:13:54 -0500435_iso-8859-1 I looked more and found this on a marketing sheet on QMF V7.

"Available as a feature of DB2 for OS/390 or bundled with the DB2 Warehouse Manager for OS/390 feature."

That tells me it's a separate charge.

Tina

-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: November 07, 2001 8:43 AM To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Version 7 with QMF Version 3.3 [...] 19428 52 17_Stored Procedures20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM30_Wed, 7 Nov 2001 09:16:37 -0500151_ISO-8859-1 I'm looking for standards of use and coding stored procedures on OS/390. Can anyone help me locate this kind of info?

thank you, Lisa 19481 132 35_Re: BP7 and DSNDB07 / IMTH and DMTH15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 7 Nov 2001 07:48:43 -0600567_ISO-8859-1 Hello Fritz

Well, I wouldn'd say, that I'm a bufferpool expert, but some numbers are a little bit surprising to me. First of all, DMTH is greater than VDWTH. It seems, either your bufferpool is to small (but as you said DBM1-address- space is already about 1.7 GB) or there are to many datasets for DSNDB07. I don't know for sure if the number for succesful dataset open indicate taht you have 29 ts for DSNDB07 (or is it WORKFILE MAXIMUM 157!!). Then I would suggest to reduce this number and to make each one bigger. Also lowering your VDWTH [...] 19614 78 62_Re: SQLCODE -497 : Immediate Assistance Required (rebuild dbd)12_John Janssen19_john.janssen@ABP.NL30_Wed, 7 Nov 2001 15:39:40 +0100425_- we encountered a similar problem last week and were indead able to fix out problem by means of the rebuild dbd, but we also had to start access(UT) all the tablespaces explicit. The db only was not enough !!

-----Oorspronkelijk bericht----- Van: mebert@AMADEUS.NET [SMTP:mebert@AMADEUS.NET] Verzonden: dinsdag 6 november 2001 9:25 Aan: DB2-L@RYCI.COM Onderwerp: Re: SQLCODE -497 : Immediate Assistance Required [...] 19693 48 23_Unloading data question12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 7 Nov 2001 15:37:25 +0100360_iso-8859-1 Dear DB2 Gurus,

environment: DB2 for OS/390 V5

We have a segmented tablespace with seven tables and we need to unload all the rows from only one of this tables.

This table has a lot of columns defined as DECIMAL and for several reasons we need these values in a DECIMAL EXTERNAL (= unpacked) format in the output data set. [...] 19742 66 16_Re: DSN Commands14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Wed, 7 Nov 2001 14:39:14 -0000495_iso-8859-1 Yes. The "DB2 Command Reference Manual".

-----Original Message----- From: Augustine, Jobi M B225 [mailto:Jobi.Augustine@CIGNA.COM] Sent: 07 November 2001 13:47 To: DB2-L@RYCI.COM Subject: DSN Commands



Hi List,

Is there any document where I can get all the DB2 DSN Commands (Database Commands like -TERM UTIL) and the description.

Thanks in advance !! Jobi.



---------------------------------------------------------------------------- 19809 67 12_Re: IKJEFT0119_John H. Maenpaa Jr.24_john_maenpaa@YLASSOC.COM30_Wed, 7 Nov 2001 08:39:38 -0600445_iso-8859-1 IKJEFT01 is TSO Background. Look in the SYSTSIN DD to find the program that you are really running. From your description, you will probably find a statement like: RUN PROGRAM(DSNTIAUL) ... This means you are using the DB2 sample program DSNTIAUL to extract the data. This provides no formatting of the data, but you can but an ORDER BY clause on the select statement. See the comments in the hlq.SDSNSAMP(DSNTIAUL) source code. [...] 19877 19 27_Comments on backup scenario9_Don Cross20_Don.Cross@TC.FAA.GOV30_Wed, 7 Nov 2001 09:42:59 -0500352_us-ascii We are running v6 on OS390 2.9. We do not have a tape management system. We want to do a full backup once a week and incrementals every day using a 14 day cycle.

From the manuals the batch jobs we set up would call for a different tape and a different dataset name for each tablespace each day both for the fulls and incrementals. [...] 19897 151 57_Re: Help for migration from Oracle to DB2 (LPAD Function)27_Alonso Verdugo/Colombia/IBM18_alonsov@CO.IBM.COM30_Wed, 7 Nov 2001 09:57:55 -0500420_iso-8859-1 Thank Mr. Campbell.

We are using Db2 V7.2 in AIX and NT. What is SPUFI?

We going to tray your hint



Alonso Verdugo Medina, M.D. FTSS DM Andean e-Mail: alonsov@co.ibm.com Phone: 5716281651 T/L 835-1651 "Usted puede tener los números para fundamentar una idea, pero venderla requiere de su personalidad y su pasión y de la fe que tenga en ella" (Cynthia Cooper, General Motors) [...] 20049 58 33_RC00C9008E TYPE 903 DSNDB01.DBD019_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Wed, 7 Nov 2001 15:06:01 +0000426_us-ascii Hi, DB2 V5 at PUT0105, 2 member datasharing.

We hit a problem earlier this week where an online SPUFI held an X lock on a DBD01 hash anchor. Query was non-DDL. This caused transactions to timeout on the alternate member....

DSNT501I -DB3F DSNILMCL RESOURCE UNAVAILABLE CORRELATION-ID=POOLUEBA0029 CONNECTION-ID=SOEZP1A6 LUW-ID=* REASON 00C9008E TYPE 00000903 NAME DSNDB01 .DBD01 .X'000007' '.X'19' [...] 20108 10 16_Re: DSN Commands15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 7 Nov 2001 08:39:06 -0600261_- Command Reference

================================================ 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. 20119 65 31_Re: Comments on backup scenario0_20_John_Lendman@FPL.COM30_Wed, 7 Nov 2001 10:14:29 -0500590_us-ascii I believe the answer is GDG's.

John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413





"Don Cross" cc: Sent by: "DB2 Subject: Comments on backup scenario Data Base Discussion List"



11/07/01 09:42 AM Please respond to "DB2 Data Base Discussion List"











We are running v6 on OS390 2.9. We do not have a tape management system. We want to do a full backup once a week and incrementals every day using a 14 day cycle. [...] 20185 68 27_Re: Unloading data question17_Nicholson, Martin20_MNicholson@USSCO.COM30_Wed, 7 Nov 2001 09:20:48 -0600566_iso-8859-1 I would try to use DSNTIAUL with a select using the CHAR function.

> -----Original Message----- > From: Peter, Georg [SMTP:G.Peter@DZBW.DE] > Sent: Wednesday, November 07, 2001 8:37 AM > To: DB2-L@RYCI.COM > Subject: Unloading data question > > Dear DB2 Gurus, > > environment: DB2 for OS/390 V5 > > We have a segmented tablespace with seven tables and we need to unload all > the rows from only one of this tables. > > This table has a lot of columns defined as DECIMAL and for several reasons > we need these values in a DECIMAL EXTERNAL (= [...] 20254 88 21_BAD OPTIMIZER CHOICE?16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Wed, 7 Nov 2001 08:21:53 -0700492_us-ascii Group,

I'm stumped with the following problem and was wondering if someone might try and explain the unusual access path which the optimizer is selecting for the following query (DB2 V5):

SELECT * FROM TT3.TBC_DSPT_ACTNS TBC_DSPT_ACTN_RSLT WHERE TBC_DSPT_ACTN_RSLT . CODE_ACTN_STAT IN (?,?,?,?) AND TBC_DSPT_ACTN_RSLT . CODE_ACTN = ? AND TBC_DSPT_ACTN_RSLT . CODE_RSLT = ? AND TBC_DSPT_ACTN_RSLT . RFRN_REF_NBR = ? ORDER BY TBC_DSPT_ACTN_RSLT . NBR_DISPT_ACTN_SEQ [...] 20343 84 27_Re: Unloading data question16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Wed, 7 Nov 2001 08:31:35 -0700507_iso-8859-1 Georg,

If using DSNTIAUL, and selecting each column individually, you can wrap the DIGITS function around all decimal columns.

Regards, Jeff L'Italien American Express





From: "Peter, Georg" @RYCI.COM> on 11/07/2001 03:37 PM CET

Please respond to "DB2 Data Base Discussion List"

Sent by: "DB2 Data Base Discussion List"



To: DB2-L@RYCI.COM cc: Subject: Unloading data question [...] 20428 110 27_Re: Unloading data question13_Pranav Sampat31_Pranav.Sampat@FIRSTDATACORP.COM30_Wed, 7 Nov 2001 09:18:45 -0600468_iso-8859-1 Use DSNTIAUL with PARM('SQL') and use the DIGITS function for the DECIMAL fields in the sql.





"Peter, Georg" cc: Sent by: DB2 bcc: Data Base Subject: Unloading data question Discussion List



11/07/2001 08:37 AM Please respond to DB2 Data Base Discussion List











Dear DB2 Gurus,

environment: DB2 for OS/390 V5 [...] 20539 73 31_Re: Comments on backup scenario0_19_csutfin@AMSOUTH.COM30_Wed, 7 Nov 2001 09:35:01 -0600396_us-ascii Don

There is nothing in DB2 that requires you to catalog your Images Copies in the ICF(MVS) catalog . DB2 will keep track of the volser of the tapes (or dasd) in SYSCOPY.

Just use DISP=(,KEEP) and you don't need to worry about data set names being the same. The only thing that DB2 will check for is the same data set name on the same volume serial number is SYSCOPY. [...] 20613 99 79_What is SPUFI? (was AW: Help for migration from Oracle to DB2 (LP AD Function))12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 7 Nov 2001 16:39:50 +0100373_iso-8859-1 Alonso,

SPUFI (inside DB2I) is a TSO-based DB2 application in the DB2 for OS/390 world.

SPUFI reads SQL statement(s) contained as text in a sequential file, processes the statement(s) and delivers the results online to you.

Some spell it as SQL PROCESSOR USING FILE INPUT and some spell it as SQL PROCESSOR USER FRIENDLY INTERFACE ;-)) [...] 20713 12 31_Re: Comments on backup scenario10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Nov 2001 16:40:35 +0100273_us-ascii No way to use GDGs ???

================================================ 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. 20726 69 27_Re: Unloading data question17_Linda F. Claussen18_lindafc@NETINS.NET30_Wed, 7 Nov 2001 09:44:39 -0600577_iso-8859-1 Greg,

DSNTIAUL allows the user for SQL - PARM('SQL')

Use a SELECT to convert your decimal data to DIGITS .

SELECT DIGITS(DECIMAL_COLUMN) AS COL_NAME, NEXT_COL, .... FROM TABLEX;

This will convert the packed decimal data to external format for you,

Linda F. Claussen Claussen & Associates, Inc lindafc@netins.net

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Peter, Georg Sent: Wednesday, November 07, 2001 8:37 AM To: DB2-L@RYCI.COM Subject: Unloading data question [...] 20796 83 31_Re: Comments on backup scenario9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Wed, 7 Nov 2001 15:37:42 -0000995_iso-8859-1 Look into using GDG files:

Creating a GDG JCL which would keep 16 copies available:- //DEFGDG EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * DEFINE GDG(NAME(DB2IC.DB2P.FULL.ARPROD.ARBOIN) SCR NOEMPTY LIM(16))

Using a GDG JCL:- //STEP0001 EXEC PGM=DSNUTILB,REGION=4M, // PARM=DB2P,COND=(4,LT) //LOCCO1 DD DSN=DB2IC.DB2P.FULL.ARPROD.ARBOIN(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(1,SL,RETPD=15), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21) //REMCO1 DD DSN=DB2ICR.DB2P.FULL.ARPROD.ARBOIN(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(1,SL,RETPD=15), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21) //LOCCO2 DD DSN=DB2IC.DB2P.FULL.BKPROD.BKBADT(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(2,SL,RETPD=15), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21,REF=*.LOCCO1) //REMCO2 DD DSN=DB2ICR.DB2P.FULL.BKPROD.BKBADT(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(2,SL,RETPD=15), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21,REF=*.REMCO1) [...] 20880 43 31_Re: Comments on backup scenario10_Ali OZTURK27_Ali.OZTURK@PAMUKBANK.COM.TR30_Wed, 7 Nov 2001 18:02:56 +0200463_iso-8859-9 You can use GDG base datasets for backups entries. So you don't need to uncatalog.



-----Original Message----- From: Don Cross [mailto:Don.Cross@TC.FAA.GOV] Sent: Wednesday, November 07, 2001 4:43 PM To: DB2-L@RYCI.COM Subject: Comments on backup scenario



We are running v6 on OS390 2.9. We do not have a tape management system. We want to do a full backup once a week and incrementals every day using a 14 day cycle. [...] 20924 69 27_Re: Unloading data question13_Villa Horacio17_hvilla@TTI.COM.AR30_Wed, 7 Nov 2001 12:51:33 -0300421_iso-8859-1 I will answer this, though I'm no guru: use the char function. select char(colname),... Greetings - Saludos Horacio Villa





-----Mensaje original----- De: DB2 Data Base Discussion List En nombre de Peter, Georg Enviado el: Miércoles 7 de Noviembre de 2001 11:37 Para: DB2-L@RYCI.COM Asunto: Unloading data question



Dear DB2 Gurus,

environment: DB2 for OS/390 V5 [...] 20994 40 13_SQLCODE: -90412_Mathur, Anil29_AMathur@HARLEYSVILLEGROUP.COM30_Wed, 7 Nov 2001 11:01:31 -0500281_- Hi All - I am getting the following error message while trying to see the columns of a table defined in database DSNDB04, through the DB2 Admin tool. Can anyone tell me to what resource name is the message referring to and what I should be lookin for. Thanks for your help. [...] 21035 12 31_Unloading data question (Reply)0_28_Michael.D.Ockenfels@WCOM.COM30_Wed, 7 Nov 2001 10:03:42 -0600280_us-ascii Try using the DIGITS function

================================================ 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. 21048 93 27_Re: Unloading data question17_Vijay Subramanyam26_Vijay.Subramanyan@UBSW.COM30_Wed, 7 Nov 2001 15:56:28 +0000499_ISO-8859-1 Peter,

You can use DIGITS(col-name) for all the columns declared as DECIMAL in the select statement for unloading the data.

Regards, VIJAY



-----Original Message----- From: G.Peter Sent: 07 November 2001 14:37 To: DB2-L Cc: G.Peter Subject: Unloading data question



Dear DB2 Gurus,

environment: DB2 for OS/390 V5

We have a segmented tablespace with seven tables and we need to unload all the rows from only one of this tables. [...] 21142 163 57_Re: Help for migration from Oracle to DB2 (LPAD Function)17_Okronglis, Pete M22_pete.okronglis@NWA.COM30_Wed, 7 Nov 2001 10:17:17 -0600408_iso-8859-1 I've found something that may work for you. I used SQL functions to accomplish it (hopefully no need for a stored procedure). The statement is: SELECT SUBSTR(REPEAT('X',10-LENGTH('ABCD'))||'ABCD',1,10) FROM SYSIBM.SYSDUMMY1; where the outer substr is the length of the column you want, the inner length is the column you are selecting, and you then concatenate (||) with the original column. [...] 21306 143 38_Re: An example DF/DSS move job, please11_Andy Cliffe18_andy.cliffe@BT.COM30_Wed, 7 Nov 2001 16:07:28 -0000943_iso-8859-1 How about this - test it thoroughly first, I can't guarantee it:

//USER75W JOB ,'MOVE VSAM FILE ',MSGLEVEL=(1,1),MSGCLASS=Q, 00010000 // NOTIFY=&SYSUID,CLASS=A,TIME=1440 00020000 //* 00030000 //STOPUT EXEC PGM=IKJEFT01 00031000 //STEPLIB DD DSN=SYSD0.DB2.DSN1.SDSNLOAD,DISP=SHR 00032000 //SYSTSPRT DD SYSOUT=* 00033000 //SYSPRINT DD SYSOUT=* 00034000 //SYSUDUMP DD SYSOUT=* 00035000 //SYSTSIN DD * 00036000 DSN SYSTEM(DSN1) 00037000 -STOP DB(DB000001) SPACENAM(SP00001) 00038000 END 00039000 /* 00039100 //DFDSS1 EXEC PGM=ADRDSSU,REGION=8M 00040000 //SYSPRINT DD SYSOUT=* 00050000 //SYSIN DD * 00060000 COPY DATASET(INCLUDE(DSN1.DSNDBC.DB000001.SP00001.I0001.A001)) - 00070000 CANCELERROR CATALOG - 00080000 OUTDYNAM((NHAT60)) - 00090000 RENAMEUNCONDITIONAL( - 00100000 (DSN1.DSNDBC.DB000001.SP00001.I0001.A001, - 00110000 DSN1.DSNDBC.DB000001.SP00001.I0001.A001.N) ) 00120000 /* 00130000 // ENDIF 00131001 // IF (RC LE [...] 21450 74 27_Re: Unloading data question12_Dil Pratheek30_Dil.Pratheek@MORGANSTANLEY.COM30_Wed, 7 Nov 2001 11:49:07 -0500341_iso-8859-1 I agree with Villa....using DIGITS with decimal wont give the desired result. It can be used with integer values. If you use DIGITS with a value of 1234.56 it will return only 123456 which is not something you would want....! Digits will NOT display the deimal point..

Cheers. Dil Pratheek

Villa Horacio wrote: [...] 21525 15 17_Re: SQLCODE: -90410_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Nov 2001 17:44:39 +0100359_us-ascii Check MSTR log, you've a problen in DSNDB07 datasets.I'm afraid your 4k datasets exploded.

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. 21541 77 33_Re: SQL activity at a table level12_Moulder, Tom19_Tom_Moulder@BMC.COM30_Wed, 7 Nov 2001 10:59:54 -0600595_iso-8859-1 Carlos

There is a product from BMC that will provide you with this information, it is called APPTUNE. The information can be obtained by drilling down from DB2 to a plan to a package/DBRM to the table/indexes that are being accessed. The information can also be obtained by drilling down from BP to Database to Tablespace to table/index to SQL statements. It also provides a CRUD matrix to report on the different types of access. The information is ontained from DB2 control blocks at execution time and stored in VSAM data sets that work exactly like SMF data sets. The [...] 21619 86 17_Re: SQLCODE: -90415_Billings, Linda30_Linda.Billings@DEG.STATE.WI.US30_Wed, 7 Nov 2001 11:11:08 -0600342_iso-8859-1 Hi, Anil, If you look up DSNT416I in the messages and codes manual you will see that the -115 is a RDS return code. Then look up -115 and this is the answer for that error code.

-115 A PREDICATE IS INVALID BECAUSE THE COMPARISON OPERATOR operator IS FOLLOWED BY A PARENTHESIZED LIST OR BY ANY OR ALL WITHOUT A SUBQUERY [...] 21706 131 31_Re: Comments on backup scenario0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 7 Nov 2001 11:17:20 -0600433_us-ascii Expanding on Jim's JCL, rather than putting each tablespace on a separate tape as you stated, this JCL concatenates all TS datasets onto one tape (via LABEL number, RETAIN, and REF parameters). Get better utilitzation of tape, and fewer tapes to manage, it is faster because there are fewer tape mounts. Slight disadvantage if you are in a hurry when doing recovery and want to recover more than one table in parallel. [...] 21838 116 17_Re: Trigger Error0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Wed, 7 Nov 2001 11:20:34 -0600329_us-ascii With Triggers not only do you need authority to create triggers (which you or someone must have to be able to have it created) but you also need authority to use them. See the SQL reference manual on GRANT (table or view) and it will show you the syntax you need to allow you the authority to execute the triggers. [...] 21955 60 33_Re: SQL activity at a table level11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Wed, 7 Nov 2001 11:22:50 -0600365_us-ascii One way would be turn the Auditing ON for the table and run the Audit reports.... : )







"Olson, Carlos" To: DB2-L@RYCI.COM Subject: SQL activity at a table level Sent by: DB2 Data Base Discussion List



11/06/01 07:52 PM Please respond to DB2 Data Base Discussion List [...] 22016 72 17_Re: SQLCODE: -90417_Linda F. Claussen18_lindafc@NETINS.NET30_Wed, 7 Nov 2001 11:32:56 -0600405_iso-8859-1 In the messages and codes manual:

Resource 00000230 is Temporary file / Name 4K (your workfile database 4k tablespace - "DSNDB07" or equivalent)

Check your system log for additional messages.

Reason Code 00C90084 explains "An error was detected either during the opening of the page set or during the formatting of the header and the space map pages of the page set." [...] 22089 139 17_job opportunities17_Charlene Saunders33_csaunders@PALLADIANCONSULTING.COM30_Wed, 7 Nov 2001 09:13:29 -0800520_iso-8859-1

Dear Colleagues:





Palladian Consulting, a leader in the RDBMS consulting industry, is looking to hire 2 Programmer Analysts for one of our clients in Albany, NY.

Mandatory requirements for 1st position: 4 years hands-on COBOL programming experience on large IBM or compatible as well as JCL on MVS/XA, 2 years hands-on programming including database navigation and 2 years w/IBM's TSO/ISPF. Also, 2 years of Powerbuilder exp. w/in the last 4 yrs. using DB2 database. [...] 22229 48 17_Re: SQLCODE: -90415_Billings, Linda30_Linda.Billings@DEG.STATE.WI.US30_Wed, 7 Nov 2001 11:41:14 -0600448_iso-8859-1 Max is probably right. I looked up the first codes and saw that it was a temporary resource. I looked up the -115 out of curiosity and thought that might have something to do with it. After rereading the explanation that probably isn't the answer. The statement would not have executed at all if that was the case. However, I can find no other definition for -115 in the messages and codes manual. Where would these codes be found? [...] 22278 79 35_Re: BP7 and DSNDB07 / IMTH and DMTH13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Wed, 7 Nov 2001 09:58:13 -0800488_iso-8859-1 In addition, you might also consider setting VDWQT and DWQT to a very high value (75 and 90 respectively) as the default values of 10 and 50 are too less for sort work buffer pool.

However, do NOT consider hiperpools for the sort work buffer pool. Hiperpools are not effective because all pages used in the first phase of sort have their write pending flag set. Hence, they must be written to disk before before migrating to hiperpool. A disk write is unavoidable. [...] 22358 93 35_Re: BP7 and DSNDB07 / IMTH and DMTH13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Wed, 7 Nov 2001 10:24:20 -0800428_iso-8859-1 Oops forgot to mention that you should consider increasing VDWQT and DWQT only AFTER you have tuned the DASD system because otherwise, as suggested by Walter, it would actually increase DMTH and IWTH instances.

Ashish.



-----Original Message----- From: Mohan, Ashish Sent: Wednesday, November 07, 2001 9:58 AM To: 'DB2 Data Base Discussion List' Subject: RE: BP7 and DSNDB07 / IMTH and DMTH [...] 22452 122 17_Re: JDBC vs SQLJ?12_Myron Miller22_Myron.Miller@BMWNA.COM30_Wed, 7 Nov 2001 13:30:16 -0500388_iso-8859-1 Mike, If I understand how SQLJ works as far as security from what you're saying, then when I create the SQLJ package and bind it, I must use an authorization ID that has the appropriate authorities to the underlying tables. Then I just grant execute on that SQLJ package to the users (Java coders, etc). I don't have to grant access to the underlying tables to the users. [...] 22575 93 17_Re: SQLCODE: -90412_Mathur, Anil29_AMathur@HARLEYSVILLEGROUP.COM30_Wed, 7 Nov 2001 13:44:19 -0500639_- Thank you all for steering me in the right direction. I have resolved the problem by increasing the space for DSNDB07.

Best Regards,

Anil Mathur



> -----Original Message----- > From: Linda F. Claussen [SMTP:lindafc@NETINS.NET] > Sent: Wednesday, November 07, 2001 12:33 PM > To: DB2-L@RYCI.COM > Subject: Re: SQLCODE: -904 > > In the messages and codes manual: > > Resource 00000230 is Temporary file / Name 4K (your workfile database 4k > tablespace - "DSNDB07" or equivalent) > > Check your system log for additional messages. > > Reason Code 00C90084 explains "An error was detected either during the [...] 22669 174 17_Re: JDBC vs SQLJ?15_Jackson Reavill18_damcon2@US.IBM.COM30_Wed, 7 Nov 2001 13:57:48 -0500504_us-ascii BTW... There's an excellent article on this subject in the 4th Quarter, 2001 DB2 Magazine by Robert Catterall. It's written from a DBA's perspective.

Cheers, 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 ----------------------------------------------------- [...] 22844 51 51_Recovering TEMP DB at Recovery Site (DB2 v6 OS/390)14_Bill Gallagher28_BILL.GALLAGHER@PHOENIXWM.COM30_Wed, 7 Nov 2001 13:55:27 -0500587_us-ascii Dear List,

We've recently created some TEMP databases in our DB2 subsystems for use with declared global temporary tables. They work great. However, we're now looking into incorporating them in our disaster recovery procedures in order to recover them offsite in a DR situation. Normally, we would just recover all DB2 subsystem tablespaces (system catalog and application tablespaces) from image copies, and rebuild all indexes. However, when I tried to take an image copy of the tablespace that was created in our of our TEMP databases, I got the following error: [...] 22896 42 35_Re: BP7 and DSNDB07 / IMTH and DMTH14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 7 Nov 2001 14:35:44 -0500419_ISO-8859-1 Doing this (below) will usually slam you right into SPTH, DMTH, and IWTH since the i/o subsystem will never be fast enough.... remember you do not have to read a page into the pool first... you are creating new updated pages in the pool. The below values might save write i/o, but can only be effective if almost all sort activity can be completed in the pool - and this pool is far too small for that. [...] 22939 32 35_Re: BP7 and DSNDB07 / IMTH and DMTH14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 7 Nov 2001 14:39:02 -0500347_ISO-8859-1 This (below) is not completely correct. HPs have been shown to provide a good performance improvement at many installations. Yes, the pages still have to be written out, but you may save a lot on the read side if they are in the HP. It's a case that every installation is different, and you have to try it and measure the results. [...] 22972 194 17_Re: JDBC vs SQLJ?9_Mike Deer20_Michael.Deer@UAL.COM30_Wed, 7 Nov 2001 13:43:11 -0600609_- Hi Myron -

Yes, thats right. JDBC results in dynamic sql which requires the user have direct access to the underlying tables. There is no application plan/package (other than the generic plan/package in place that is required to enable dynamic sql.) The JDBC dynamic sql plan, DSNJDBC performs the same function as does DSNESPCS (spufi). In contract, SQLJ is static SQL. The program is first 'precompiled' and a db2 package is created that operates the same way any other traditional application plan/package works. The plan is bound by an ID that has access to the tables, and users get access [...] 23167 166 25_Re: BAD OPTIMIZER CHOICE?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 7 Nov 2001 14:11:49 -0600337_us-ascii Jeff,

In V5 I did see a number of access paths where a the optimizer would choose an ACCESSTYPE = I, when an ACCESSTYPE = N (IN list) would have achieved a higher number of matchcols, and more importantly - Index-only access.

This was generally when the index match (non-IN list) would choose a unique index. [...] 23334 51 35_Re: BP7 and DSNDB07 / IMTH and DMTH13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Wed, 7 Nov 2001 12:15:24 -0800383_iso-8859-1 Joel, disk write and reread, in case of sort work pages is a asynchronous operation. So, I am skeptical about the statement that having a HP will save a 'lot' of time. Disk cache and bufferpools are enough to take care of asynchronous operations. The very philosophy of Hiperpools is to aid synchronous operations finish faster (with Hardware move page instruction). [...] 23386 56 25_Re: BAD OPTIMIZER CHOICE?13_Harry William27_harry_williamtk@HOTMAIL.COM30_Wed, 7 Nov 2001 20:27:56 +0000703_- Jeff, We had a similar kind of problem with one of our sql.We modified the sql using union.

May be you can try rewriting the sql as

SELECT * FROM TT3.TBC_DSPT_ACTNS TBC_DSPT_ACTN_RSLT WHERE TBC_DSPT_ACTN_RSLT . CODE_ACTN_STAT IN (?,?) AND TBC_DSPT_ACTN_RSLT . CODE_ACTN = ? AND TBC_DSPT_ACTN_RSLT . CODE_RSLT = ? AND TBC_DSPT_ACTN_RSLT . RFRN_REF_NBR = ? ORDER BY TBC_DSPT_ACTN_RSLT . NBR_DISPT_ACTN_SEQ UNION SELECT * FROM TT3.TBC_DSPT_ACTNS TBC_DSPT_ACTN_RSLT WHERE TBC_DSPT_ACTN_RSLT . CODE_ACTN_STAT IN (?,?) AND TBC_DSPT_ACTN_RSLT . CODE_ACTN = ? AND TBC_DSPT_ACTN_RSLT . CODE_RSLT = ? AND TBC_DSPT_ACTN_RSLT . RFRN_REF_NBR = ? ORDER BY TBC_DSPT_ACTN_RSLT . NBR_DISPT_ACTN_SEQ [...] 23443 168 17_Re: Cluster index12_Veedu, Manoj18_MVeedu@STATE.NM.US30_Wed, 7 Nov 2001 13:40:48 -0700456_iso-8859-1 I created 4 indexes on a table

Index Name OBID TMOLMVT1 X'00C1' 193 TMOXMVT2 X'00C3' 195 TMOXMVT3 X'00C5' 197

None of them clustered. Then when I did the runstat, catalogs showed TMOLMVT1 AND TMOXMVT3 as clustered. Then I dropped TMOLMVT1 and recreated it. it still has the same OBID. Then I created TMOXMVT4 (OBID X'00C7' 199)and dropped TMOLMVT1 and TMOXMVT3. After the runstat its showing TMOXMVT4 as the clustering index. [...] 23612 20 35_Re: BP7 and DSNDB07 / IMTH and DMTH0_26_truman.g.brown@VERIZON.COM30_Wed, 7 Nov 2001 15:54:00 -0500375_us-ascii Ashish,

We have one subsystem that has a 25% HP read/write ratio for its dedicated DSNDB07 bufferpool/hiperpool. I think this is due to reclaims of stolen pages during very high dedicated sort bufferpool activity, e.g., stolen pages go out to the HP and don't require synchronous reads from their 4K work dataset's pageset when the next GETPAGE occurs.. [...] 23633 41 50_AIX upgrade from UDB EE V6.1 to V7.2 any problems?12_Martin, Paul22_Paul.Martin@ECOLAB.COM30_Wed, 7 Nov 2001 15:00:52 -0600141_iso-8859-1 We're running with EE V6.1 fixpak5 and would like to upgrade to version 7.2 -- anyone have any problems with this upgrade?

23675 162 26_Deadlock / Timeout Problem9_Dan Lamas22_DLamas@DUKE-ENERGY.COM30_Wed, 7 Nov 2001 15:06:24 -0600606_iso-8859-1 We are incurring a deadlock situation where the deadlock is not resolved until 8 minutes after the first detection message appeared in the log.

Monitoring through DB2PM showed that the two threads were locked (engaged in locking) but they were not showing any resources locked for either of the threads (i.e. Locked Resources screen was blank). However, displaying database locks in DB2I showed shared and exclusive locks for the resources in question. Additionally, DB2PM showed on the Thread Times screen that the majority of the elapsed time seemed to be Application and not DB2. [...] 23838 93 45_Re: OS/390 DB2 Version 7 with QMF Version 3.312_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Wed, 7 Nov 2001 16:22:30 -0500489_iso-8859-1 I can confirm for you that QMF, while now packaged as a function of DB2 rather than an entirely seperate product, is still an optional, chargeable feature,

as is the other pieces of the Warehouse Manager Suite, QMF HPO and Warehouse Manager.

HTH, tom



-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: Wednesday, November 07, 2001 8:43 AM To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Version 7 with QMF Version 3.3 [...] 23932 117 17_Re: JDBC vs SQLJ?12_Myron Miller22_Myron.Miller@BMWNA.COM30_Wed, 7 Nov 2001 16:23:53 -0500434_iso-8859-1 I follow this. The question was asked me about JAVA applications that are EJBs, especially Container Managed Beans. Does this change the issues as far as SQLJ and JDBC is concerned? Or can we still use SQLJ in these types of Container Managed Beans?

-----Original Message----- From: Mike Deer [mailto:Michael.Deer@UAL.COM] Sent: Thursday, November 01, 2001 7:51 AM To: DB2-L@RYCI.COM Subject: Re: JDBC vs SQLJ? [...] 24050 88 25_Re: BAD OPTIMIZER CHOICE?16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Wed, 7 Nov 2001 14:17:08 -0700326_us-ascii Harry,

We did think about this approach and also splitting up the in list to allow a multi index access, but I figured that these two approaches would probably incur more getpages than we would like. At this point, since V6 seems to correct this situation, we're going to live with it for the time being. [...] 24139 73 8_DSNISMKD14_Manish Kothari23_mkothari@DATAMIRROR.COM30_Wed, 7 Nov 2001 17:32:53 -0500431_iso-8859-1 Hi DB2-Geniuses

I am a newbie MVS/DB2 Admin person.

On my MVS (2.10) and DB2 7.1, I am planning to install ptf Uq53028.

It says I need to (re-)run job DB2 job DSNISMKD to make it effective. But as far as I can tell, the original person (Contractor) did not install it.

I am not even sure that OMVS is installed on this system. If I do a "XMITIP" and says it is says command not found... [...] 24213 110 17_Re: Cluster index10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Wed, 7 Nov 2001 16:49:55 -0600323_us-ascii Don't confuse 'clustered' with 'clustering'. Clustered is a Yes or No Flag that is reflective of the clusterratio percentage. When the clusterratio falls below 95%, the clusterED flag is changed to No when stats are run, the clusterING flag NEVER changes unless you drop & recreate the index as the opposite. [...] 24324 40 35_DB2 Connect /DB2 for OS390 versions0_19_csutfin@AMSOUTH.COM30_Wed, 7 Nov 2001 17:17:11 -0600335_us-ascii I know that this was discussed at one point but I cannot find the subject in the archives without having to search thousands of entries. I am running DB2 V6 on OS390 2.8. We have a connection to an NT server using DB2 Connect V5. The DB2/UDB version is also V5. We have done the upgrade to DB2 V6 in the last six months. [...] 24365 47 35_Re: BP7 and DSNDB07 / IMTH and DMTH14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 7 Nov 2001 18:39:56 -0500412_ISO-8859-1 Ashish, Not necessarily true at all. I see some systems with up to 50% random/synch read. Reading pages back from sort is not always SP, and you need to look at systems to realize this. Disk cache is still an I/O... costs time and far more cpu cycles than moving from HP. As a rough guess, I say at about 25% of the sites out there can or do get benefit from putting HP behind the sortwork pool. [...] 24413 46 17_Re: Cluster index12_sushant dash23_dash_dba@REDIFFMAIL.COM30_Wed, 7 Nov 2001 23:55:47 -0000499_iso-8859-1 Hello Manoj It seems odd. Could you check all those once again. It has been all ready told by Richard that the lowest OBID is not always the clustered index. You created 1,2,3. It seems 1 would be the clustered. Then when again you dropped 1 and three then 2 becomes the clustered index. Again you createed 4,1 and 3 so the 2 remains as clustered. Intially if you have created the index in the order then this should be the answer. Could you please check the order of your creation. [...] 24460 58 35_Re: BP7 and DSNDB07 / IMTH and DMTH13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Wed, 7 Nov 2001 16:14:50 -0800621_iso-8859-1 Thanks Joel...I am better informed now.

Ashish.

-----Original Message----- From: Joel Goldstein [mailto:joel_goldstein@COMPUSERVE.COM] Sent: Wednesday, November 07, 2001 3:40 PM To: DB2-L@RYCI.COM Subject: Re: BP7 and DSNDB07 / IMTH and DMTH

Ashish, Not necessarily true at all. I see some systems with up to 50% random/synch read. Reading pages back from sort is not always SP, and you need to look at systems to realize this. Disk cache is still an I/O... costs time and far more cpu cycles than moving from HP. As a rough guess, I say at about 25% of the sites out there can or do [...] 24519 146 17_Re: Cluster index0_19_Tim.Lowe@STPAUL.COM30_Wed, 7 Nov 2001 18:31:03 -0600530_us-ascii Manoj, Can I ask what you are trying to test? Can I assume that some reorgs were done somewhere to move data around? Or, are you deleting all of the rows and reinserting the data?

Just dropping and recreating an index does not effect the clustering sequence of the data in the tablespace. The clusterratio indicates how well the order of the data in the tablespace matches the index order. If you don't move the data, nothing really changes, and you don't demonstrate the use of explicit or implicit indexes. [...] 24666 37 43_Creating a Second Independent DB2 Subsystem12_Marc Matulis30_Marc.Matulis@OSFHEALTHCARE.ORG30_Wed, 7 Nov 2001 19:29:31 -0600374_us-ascii Anyone want to take a stab at supplying me with the necessary libraries to 'clone' to bring up a second independent DB2 subsystem? The plan is to only use one SMP/E environment, but separate 'load' libs across 3 independent DB2 subsystems (Development, Training, Production).

I searched the list, but couldn't find anywhere that this has been answered. [...] 24704 19 4_Nona11_Endy Lambey20_endyl@CENTRIN.NET.ID30_Thu, 8 Nov 2001 10:15:27 +0700389_- Welcome to the club buat Nona.

Btw, kemarin malam (Rabu 7-11-01), aku ketemu Marina di Pasaraya sedang menunggu jemputan. CUma aku kurang tau yang jemput dia itu siapa ya, hahahaha Tadi malam hampir terkesima aja aku melihatnya, untungnya pas dia *#$@!^* aku jadi tau deh kalo itu Marina, hahahaha

endy@lambey.net

'He who laughs, lasts' -- Mary Pettibone Poole [...] 24724 17 21_Ignore 'Nona' Subject11_Endy Lambey20_endyl@CENTRIN.NET.ID30_Thu, 8 Nov 2001 11:37:22 +0700355_- Dear all,

Sorry, I sent the email to the wrong list, just ignore that email.

So sorry

Endy

================================================ 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. 24742 40 8_Re: Nona12_Ranjan Birla26_Ranjan.Birla@HPSGLOBAL.COM30_Thu, 8 Nov 2001 10:22:15 +0530618_iso-8859-1 What is that??????? I never think such things also happened in this list................... Please avoid this. Thanks, Ranjan Birla.

-----Original Message----- From: Endy Lambey [mailto:endyl@CENTRIN.NET.ID] Sent: Thursday, November 08, 2001 8:45 AM To: DB2-L@RYCI.COM Subject: Nona

Welcome to the club buat Nona.

Btw, kemarin malam (Rabu 7-11-01), aku ketemu Marina di Pasaraya sedang menunggu jemputan. CUma aku kurang tau yang jemput dia itu siapa ya, hahahaha Tadi malam hampir terkesima aja aku melihatnya, untungnya pas dia *#$@!^* aku jadi tau deh kalo itu Marina, hahahaha [...]