1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2001, week 3 2 26 36_Re: RRSAF .....why JDBC wants it ???10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 15 Jan 2001 08:43:39 +0100472_us-ascii Hi Jodi, thanks for ypur reply.

Can you tell me where did you find the explanation in the manual ?

I read JAVA manuals, but I find that you can use RRSAF or CAF to access to DB2 data. The problem is that I configured

the profiles as suggested, but when I run the application program it try to connect to DB2 via RRSAF, it doesn't detect the connection type specified in ect/profile or in CLI profile. It seem a bug of the native driver. [...] 29 16 33_James Drewe is out of the office.11_James Drewe20_James.Drewe@AEXP.COM31_Mon, 15 Jan 2001 01:04:30 -0700470_us-ascii I will be out of the office from 01/12/2001 until 01/25/2001.

I will respond to your message when I return. For DBA assistance, please contact Debbie Russell at 602-766-5035 or Duane Foss at 602-766-5662. 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. 46 76 52_Re: Restoring 5.2 UDB backups using 6.1 UDB binaries11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Mon, 15 Jan 2001 13:22:02 -0800705_ISO-8859-1 Hi...

Just to add Gert's comments, Just in case the database doesn't automatically migrated, try 'migrate database <>' command after restore

*********** REPLY SEPARATOR ***********

On 1/11/2001 at 7:08 PM Gert van der Kooij wrote:

>Hi, >When you restore the database (with the restore command) it will >automatically be migrated to a 6.1 database. If it's a database with a lot >of entries in the catalog will take some time and logspace. > >Hope this helps. > >Gert > >----- Original Message ----- >From: >Newsgroups: bit.listserv.db2-l >To: >Sent: Thursday, January 11, 2001 2:43 PM >Subject: Restoring 5.2 UDB [...] 123 54 31_Re: Help needed DB2 UDB Restore11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Mon, 15 Jan 2001 13:28:20 -0800554_us-ascii Hi Jeff...

There's a bit irregularity on DB2 V.5 on restoring database, especially into the a new alias. Sometimes you may found error message, sometimes you don't... When you restore the database into the new name ( I use Create New From Backup), check if you specify the correct old database (from backup) name in the backup image tab with the date and time....You should also enter the new database name/alias on the Definition tab..I never found any error message after that... Btw, I am using DB2 7 on NT with Control Center... [...] 178 102 27_Re: DB2 PM Release Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 15 Jan 2001 09:02:31 -0000556_iso-8859-1 Tom

excellent, we have just ordered V7 .. would be nice to know of any new bugs ..

Les

-----Original Message----- From: Tom Taylor [mailto:ttaylor@CHUBB.COM] Sent: Thursday, January 11, 2001 1:55 PM To: DB2-L@RYCI.COM Subject: Re: DB2 PM Release Question



Les

I have installed DB2PM V7... it has changed slightly in the 'try and buy' arena. Otherwise the install was easy for a first timer. I'm playing withthe GUI piece and it looks good. There are a couple issues though. Will keep you informed. [...] 281 69 27_Re: DB2 PM Release Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 15 Jan 2001 09:03:32 -0000671_iso-8859-1 Bob

yes we have this problem aswell. We find changng the subsystem id in options helps sort it out (reset whatever is causingt he problem).

Les

-----Original Message----- From: BOB JEANDRON [mailto:BOB.JEANDRON@USDA.GOV] Sent: Thursday, January 11, 2001 3:34 PM To: DB2-L@RYCI.COM Subject: Re: DB2 PM Release Question Sensitivity: Personal



It is my understanding that PM must be upgraded to work correctly when you upgrade to DB2 V6.1. We just updated our DB2PM from V5.1 to V6.1 (Put 0011) in our test environment, and have a problem with both DB2 V6.1 and 5.1. There is an intermittent problem with displaying thread [...] 351 21 35_Re: Elapsed time problem; need help15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 15 Jan 2001 03:56:24 -0600365_- Well here are some items to further clarifa my problem

1. The explain is the most recent bind of a static statement.

2. The index in question is:

VORGANG ASC CHECKP_NR DESC

3. The statement was executed many times (I don't know it it was a million times), but the trace output (yes it is Omegamon output) shows one single SELECT. [...] 373 138 64_Re: Spanning More than One Volume When Copying the DB2 Directory0_27_jim.leask@RS-COMPONENTS.COM31_Mon, 15 Jan 2001 10:25:25 -0000784_iso-8859-1 I had this problem a couple of years ago and the following JCL corrected it.

Jim.



//LOCCO1 DD DSN=DB2IC.DB2P.FULL.SRPROD.SRBPCP(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(1,SL,RETPD=8), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21) //REMCO1 DD DSN=DB2ICR.DB2P.FULL.SRPROD.SRBPCP(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(1,SL,RETPD=8), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21) //LOCCO2 DD DSN=DB2IC.DB2P.FULL.SRPROD.SRBASL(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(2,SL,RETPD=8), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21,REF=*.LOCCO1) //REMCO2 DD DSN=DB2ICR.DB2P.FULL.SRPROD.SRBASL(+1), // DISP=(NEW,CATLG,CATLG),LABEL=(2,SL,RETPD=8), // UNIT=(CART,,DEFER),BUFNO=40, // VOL=(PRIVATE,RETAIN,,21,REF=*.REMCO1) [...] 512 51 30_Changing DB2 column attributes6_rajesh22_rajeshs@DOMINOMAIL.COM29_Mon, 15 Jan 2001 14:31:46 GMT558_iso-8859-1 Hi All,

A few basic quewtions on DB2 UDB 7.1 on NT/AIX

1.The Index name cannot be more than 18 chars,Application demands more than that,is there something that can be done.

2.Changing Attributes of a column, Column Name Data Type Length and Precision Not null to Null Changing the generated sequence I believe that the above cannot be changed, workaround suggested would be to extract the ddl stmts,make changes and run the new script,keeping in mind the RF & other constraints,As for the data use export/import utility. [...] 564 24 56_Re: Check index returns Index out of sequence.......why?7_Sri Sri20_sri_db2l@HOTMAIL.COM31_Mon, 15 Jan 2001 08:41:15 -0600318_- Hi Sanjeev,

I have RID , check index returned me only one index key is out of sync. This program runs every day, and it got problem on this saturday.

It is me the DBA, did not do any thing for this table from couple of months, one month back we did reorg. During reorg, i did not get any problem. [...] 589 13 6_DSSIZE15_Steven Morrison33_steven.morrison@FIRSTDATACORP.COM31_Mon, 15 Jan 2001 09:11:13 -0600480_- This may be a repeat of a previus messsage but I never seen it get posted. Does anyone know if the DSSIZE parameter will be alterable in V7 on OS/390? I thought that may be the case but can't find any documentation to confirm the rumor.

================================================ 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. 603 225 50_Re: DB2 stored procedure not returning result set.11_David Nance16_DWNance@FHSC.COM31_Mon, 15 Jan 2001 10:22:59 -0500573_US-ASCII Sibi, We just recently moved to 6.1 on OS/390 and were having the same type errors and in every case it was problems with our bind. We can now bind using a qualifier of say a person's userid and schema of test and all is working fine. I've found the best method is to step through all your process and ensure everything is done properly(i.e; check your procedure definition, how is it bound? then your individual packages, you say pkgs coming in ODBC are fine? what's the difference between those and your local pkgs? don't forget the new bind parms(possibly [...] 829 240 34_Re: Changing DB2 column attributes14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Mon, 15 Jan 2001 07:23:29 -0800437_iso-8859-1 Rajesh, Answers follow questions.

-----Original Message----- From: rajesh [mailto:rajeshs@DOMINOMAIL.COM] Sent: Monday, January 15, 2001 8:32 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Changing DB2 column attributes



Hi All,

A few basic quewtions on DB2 UDB 7.1 on NT/AIX

1.The Index name cannot be more than 18 chars,Application demands more than that,is there something that can be done. [...] 1070 52 34_Re: Changing DB2 column attributes13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 15 Jan 2001 10:32:12 -0500412_iso-8859-1 Hi Rajesh,

1. Why does your application care what the name of an index is? The index name is usually an internal identifier and its name is never (in my half-vast experience) of any interest to an application. You can't specify an index name anywhere in your application. Why do you care what its name is? I don't believe there is any way to increase the name beyond the documented limit. [...] 1123 79 60_CASE expression allowed to naviagate inside a WHERE clause ?12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 15 Jan 2001 16:52:49 +0100555_iso-8859-1 Dear Listers,

we're on DB2 for OS/390 Version 5 and one of our programmers has the following needs:

If a given host variable is "1" the where clause should be SELECT col1, col2, col3 where colx like 'a 1 byte comparisation field' if a given host variable is "5" the where clause should be SELECT col1, col2, col3 where colx like 'a 5 byte comparisation field' if a given host variable is "9" the where clause should be SELECT col1, col2, col3 where colx like 'a 9 byte comparisation field' and so on and so on and so on..... [...] 1203 81 53_Re: Conversion to DB2V6 with UTILITY in Stopped state16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Mon, 15 Jan 2001 10:59:01 -0500388_us-ascii Cheryl,

Try issuing a "-START DB(dbname) SPACE(tbspcnam) ACCESS(FORCE)" command on the tablespace which was being image copied when you terminated the utility.

This should clear up the utility that is hung on the given tablespace but not guaranteed since you attempted to recover SYSUTILX, and etc,,,,,

Hope this helps and good luck!

Rick Molera [...] 1285 19 47_Re: RRSAF .....why JDBC wants it ??? - Resolved10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 15 Jan 2001 17:14:38 +0100505_us-ascii Thanks to all who replied to my previous post. We resolved the problem.

It was due to a bad UNIX profile and variables referencing the wrong sqlj profile and the wrong CLI file. Now all is working.

Thanks again and Regardz

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. 1305 130 64_Re: CASE expression allowed to naviagate inside a WHERE clause ?14_Scott Trometer22_scott.trometer@RCI.COM31_Mon, 15 Jan 2001 11:36:07 -0500386_iso-8859-1 I'll take a shot. Is it cobol ? Couldn't you do something like this instead?

05 HV-wildcard char(??) value '%%%%%%%%%%%%%%%' <--same as column def

Evaluate HostVAR When '1' Move 1 byte comparison field to hv-wildcard(1:1) When '2' move 2 byte comparison field to hv-wildcard(1:2) When 'etc.' move x byte comparison field to hv-wildcard(1:?) when other End [...] 1436 31 10_Re: DSSIZE11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Mon, 15 Jan 2001 11:48:29 -0500542_ISO-8859-1 Steven

It is not alterable in V7 according to the draft documentation that I have seen.

Regards Mike Turner Email: michael_turner@compuserve.com Home Page: http://ourworld.compuserve.com/homepages/michael_turner

Message text written by DB2 Data Base Discussion List >This may be a repeat of a previus messsage but I never seen it get posted. Does anyone know if the DSSIZE parameter will be alterable in V7 on OS/390? I thought that may be the case but can't find any documentation to confirm the rumor.< [...] 1468 99 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 15 Jan 2001 16:53:07 -0000396_iso-8859-1 Not sure about other peoples opinions, but I have always been against using SQL to emulate what can be done in a host language.

Apart from only coding one (more complex) SQL statement instead of 16 (simple ones) where is the advantage.

In a host program, it's 100% clear what is going on, but when you start 'playing' with the SQL, it becomes a maintenance headache [...] 1568 117 64_Re: CASE expression allowed to naviagate inside a WHERE clause ?14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 15 Jan 2001 10:56:03 -0600341_iso-8859-1 Peter,

Very rough syntax follows but you will get the idea:

SELECT col1, col2, col3 from table where colx like (CASE :hv WHEN '1' THEN 1 byte comparisation field WHEN '5' THEN 5 byte comparisation field WHEN '9' THEN 9 byte comparisation field end) and hopefully some indexable columns or else tablespace scan. [...] 1686 143 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 15 Jan 2001 11:39:39 -0600316_iso-8859-1 In general I agree with Phil. Also, will the optimizer be able to handle this composite sql you desire as well as it could the separately coded SQL. My guess it that it could not. Maybe it could if the plan/package was reoptimized at run time (which is not a preferred option for an online program). [...] 1830 26 46_Lock Escalation, Lockmax and Row level locking12_Stephen Meek18_ismith@AMADEUS.NET31_Mon, 15 Jan 2001 18:33:11 +0100571_us-ascii Dear DB2 Community

We have a doubt that we could not clarify on the DB2 for OS/390 manuals (V5). On the DB2 developer's guide (Craig Mullins, 4th ed) it confirms our assumption, but we suspect that this might have been changed by a PTF... It simply refers that Lock Escalation is occurring only when LOCKSIZE is defined as ANY on TS level. So when we have LOCKSIZE ROW and LOCKMAX 1000 on the TS, what happens when the thread reaches 1000 locks (suppose NUMLKUS is 10000 and NUMLKTS is 1000)? Is the thread terminated with -904, or it simply ignores [...] 1857 129 53_Re: How to improve the operation of insert and update15_Hernandez, Hugo22_Hugo_Hernandez@BMC.COM31_Mon, 15 Jan 2001 13:00:25 -0600571_gb2312 Maybe you have to check the following :

Check the freespace and freepage, since your doing so many inserts, maybe DB2 have to split to arrange your data. For this, review the faroffpos and nearoffpos for your indexes after the stats. You have to check also the size of your bufferpools and how this table use the buffers, if you have it in a little bufferpool, maybe what is happening is that DB2 have to make synchronous writes too frequently. The idea to do a pre-sort of your data is excellent. By the way, are your indexes and tablespace partition [...] 1987 49 10_Re: DSSIZE14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 15 Jan 2001 13:32:19 -0600702_iso-8859-1 Steve,

It is not alterable in V7.

Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Steven Morrison > Sent: Monday, January 15, 2001 9:11 AM > To: DB2-L@RYCI.COM > Subject: DSSIZE > > > This may be a repeat of a previus messsage but I never seen it get posted. > Does anyone know if the DSSIZE parameter will be alterable in V7 > on OS/390? > I thought that may [...] 2037 158 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 15 Jan 2001 13:38:50 -0600329_iso-8859-1 Phil,

Is it okay if I disagree a little? First the word complex. Compound is better, as complex scares people away from achieving performance benefits in SQL rather than code.

Now in this case, it could go either way since we do not know all the information about the query or the business problem. [...] 2196 64 28_Security under RACF question13_Juan Mautalen22_jmautalen@ANSES.GOV.AR31_Mon, 15 Jan 2001 16:22:58 -0300450_us-ascii Hi,

We have implemented in our Test Lpar DB2 protection under RACF (RACF 2.6 and DB2 V5). Standard classes are being used and all are active (19 in total, counting the grouping classes). Up to the moment, we have only profiles defined in the member classes (MDSNxx). For some classes, protection is working as expected (MDSNTB or MDSNPN, for instance). However, i am having some problems with Database protection (MDSNDB class). [...] 2261 16 22_Where has DSTATS gone?18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM31_Mon, 15 Jan 2001 14:44:52 -0500 2278 67 50_Re: Lock Escalation, Lockmax and Row level locking14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 15 Jan 2001 13:49:41 -0600487_iso-8859-1 Ian,

When ROW level locking was introduced, a change was made to escalation. ROW, PAGE, and LOB locks are escalated to Table, Tablespace, or Partition locks, depending on the settings of LOCKSIZE and LOCKMAX.



Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums [...] 2346 58 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 Jan 2001 13:49:41 -0600498_us-ascii Horay for Phil ! I agree 200% !! I have spent hundreds of hours working on performance problems with SQL where people have gotten "creative" with SQL instead of spending a little extra time coding of few more lines of code. You have made my day.

Thanks, Tim





"Grainger, Phil" To: DB2-L@RYCI.COM Subject: Re: CASE expression allowed to Sent by: DB2 naviagate inside a WHERE c lause ? Data Base Discussion List [...] 2405 57 26_Re: Where has DSTATS gone?0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 15 Jan 2001 13:56:00 -0600426_iso-8859-1 here are a couple of places to try:

http://www.idug.org/member/journal/mar99/improving_db2.html The information is available in IDUG Solutions Journal March 1999 - Volume 6, Number 1 (may be under http://www.idug.org )

-Go to http://www.redbooks.ibm.com, Then Choose Additional Materials / Download FTP Site. DSSTATS Is there..

Does anyone know if it still is usefull for DB2 V6.1 OS/390? [...] 2463 103 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?18_Richard Mccutcheon32_Richard_McCutcheon@HCM.HONDA.COM31_Mon, 15 Jan 2001 15:17:21 -0500424_us-ascii Tim, you're issue may stem from a lack of SQL knowledge of the users/programmers.

We use CASE and a number of other 'new'er features of UDB/SQL with no more performance problems than can exist with any standard COBOL program that contains SQL. Old-style COBOL programs with straight SELECTs can be the poorest performers of all if they don't use properly designed SQL statments and good index analysis. [...] 2567 108 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 15 Jan 2001 14:18:52 -0600410_iso-8859-1 Tim,

Often times that is a case of the "creative" people not having been trained effectively in both the coding and performance aspects of SQL and DB2. Let's not get into the "if God wanted us to fly he would have given us wings" scenario.

Let's get management to understand that SQL is not just some little thing that requires no training or education. Rather just the opposite. [...] 2676 42 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Mon, 15 Jan 2001 14:25:46 -0600343_- My 2 cents ...

On Mon, 15 Jan 2001 13:38:50 -0600, Richard Yevich wrote:

>stay in C or Pascal. As SQL becomes more and more procedural, and makes it >easy to define business logic in the SQL Procedure language, why should we >only use SQL as a read/write engine, and keep the logic in the program? [...] 2719 173 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 15 Jan 2001 15:37:31 -0500508_iso-8859-1 Thanks Richard. I like this, I was always for doing this.





-----Original Message----- From: Richard Yevich [mailto:ryevich@ATTGLOBAL.NET] Sent: Monday, January 15, 2001 01:39 PM To: DB2-L@RYCI.COM Subject: Re: CASE expression allowed to naviagate inside a WHERE c lause ?



Phil,

Is it okay if I disagree a little? First the word complex. Compound is better, as complex scares people away from achieving performance benefits in SQL rather than code. [...] 2893 149 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Mon, 15 Jan 2001 14:51:51 -0600553_iso-8859-1 By the time it took for me to type this, 5 positive messages arrived on the list. But I typed it anyway, so I'm sending it.

On the inverse to Tim, I have saved hundreds of hours of development time by writing predominantly in SQL. And these applications FLY!!! I've got a 26 table SQL (that has comments on every line to allow the developers to understand it), utilizing CASE (in SELECT list), outer joins etc, that runs in 0.49 seconds elapsed when the data is buffered. It is 220 lines long and fits nicely into the cursor of a [...] 3043 37 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 Jan 2001 15:04:53 -0600311_us-ascii Richard, I do agree that it is an issue of education. However, it does seem that some people will spend hours "trying something new", rather than a few minutes doing something simple. People seem to want so hard to work with "new things" that they loose sight of the goal, "is this saving time?". [...] 3081 175 65_FW: CASE expression allowed to naviagate inside a WHERE c lause ?14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 15 Jan 2001 15:10:00 -0600579_iso-8859-1 Forward on behalf of Rick Creech...

> -----Original Message----- > From: rick creech [mailto:ykcirc@hotmail.com] > Sent: Monday, January 15, 2001 8:48 PM > To: Richard_Yevich@YLAssoc.com > Subject: Re: CASE expression allowed to naviagate inside a WHERE c lause > ? > > > Hi all, > > Sometimes Db2 performance and tuning is more an art than a > science. I do not > think that there are always hard-and-fast rules that apply to every > situation. But I like what Richard is saying. We need to get very > familiar > with new sql capabilites in order to have [...] 3257 14 28_Package Bind after Migration0_20_bjnigh@HOUSEHOLD.COM31_Mon, 15 Jan 2001 14:23:33 -0800418_us-ascii When a release of DB2 for MVS is upgraded, do existing packages need to be rebound or is that unnecessary? We are getting ready to migrate to DB2 Version 6 for OS/390.

================================================ 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. 3272 30 38_Advanced SQL class in Portland, Oregon24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 15 Jan 2001 14:46:40 -0800401_- To those in the US Pacific Northwest:

CNF, INC in Portland, Oregon will be hosting a 2-day Advanced SQL class from Yevich, Lawson and Associates on February 6-7, and again on February 8-9. The class is suitable for both DBAs and developers. The class is open to the Portland area community. Details on the class subject can be found at http://www.ylassoc.com/Education/education_06.htm. [...] 3303 39 32_Re: Package Bind after Migration9_Rob Crane22_racrane@CONCENTRIC.NET31_Mon, 15 Jan 2001 16:15:29 -0700578_iso-8859-1 If there are any show stoppers, the upgrade doc usually points them out and CATMAINT would generally find those and allow you take the corrective action prior to the upgrade. Like the host variables not using a colon, etc.

For the most part your V5 packages would not require a rebind to run under V6. However, your packages may benefit from the new features of the Version of DB2 that you are upgrading too. Most of these require code creation/change to make use of the feature not just a simple rebind. New REBIND options for packages and plans tied to [...] 3343 152 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Mon, 15 Jan 2001 18:26:41 -0600446_iso-8859-1 Steve,

By accident you have only sent this to me and not the list. I will answer it for you, but you may also receive some alternative answers since I have cc'd the list.

Unfortunately a ":hv = value" or "value = value" such as "0=1" are stage 2 and not indexable. Therefore, the optimizer waits until after it has retrieved the data (either via TS scan or non-matching index scan) before applying these predicates. [...] 3496 15 52_Cathy L Peck/ISG/CORP/Highmark is out of the office.10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Tue, 16 Jan 2001 01:00:28 -0500389_us-ascii I will be out of the office starting 01/15/2001 and will not return until 01/17/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. 3512 151 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Tue, 16 Jan 2001 13:58:05 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 3664 145 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Tue, 16 Jan 2001 01:15:03 -0600597_iso-8859-1 Steve,

I'm an optimist and a SQL advocate, so would rather use the ROT: "In the absence of an indexable predicate(s), this type of logic CAN cause you significant performance problems, or even with indexable predicates, MAY NOT deliver the performance you require". But it is not to be discarded.

Performance, however, is relative to the situation. Is this a batch program which runs once a night and you've added two minutes to the job's execution time, but cut out 500 lines of code (see the optimist side of me) ? :-) Or is this an online transaction executing 50 [...] 3810 12 64_Re: CASE expression allowed to naviagate inside a WHERE clause ?15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Tue, 16 Jan 2001 02:52:17 -0600335_- Richard

I think your proposal works only for version 6, but not for version 5 right?

================================================ 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. 3823 235 39_Re: Transactions deadlocking on inserts22_Kirkland, Andrew (FR1)18_kirkawp@RBOS.CO.UK31_Tue, 16 Jan 2001 09:06:23 -0000633_iso-8859-1 Terry, Tim et al,

We have resolved our deadlocking problems on Insert. And the breakthrough was on the information you provided on the Index Mgr locking strategy in the circumstance of duplicate, uncommitted key values encountered by Index Mgr when looking for a candidate page.

Our application created such a circumstance and because of an additional sequencing problem within each transaction created the classic deadlock scenario where Tran A tries to Insert key values 001, 002 and Tran B key values 002, 001. Here we have transactions starting at the same millisecond, A takes an X on 001, B takes [...] 4059 34 10_DDL parser10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 16 Jan 2001 10:41:15 +0100392_us-ascii A collegue asked me to post this request.

We finished the new daily procedure involving clenup, checks, reorgs generation etc etc.

Now we want to integrate this daily cycle with a procedure to pass the new DDL in the production DB2 and to store

this DDL in some file. The procedure is easy to create, but we want to know if there's a way to check/parse DDL [...] 4094 70 63_AW: CASE expression allowed to navigate inside a WHERE clause ?12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 16 Jan 2001 11:33:20 +0100399_iso-8859-1 Scott, Phil, Richard, Tim, Mohammad and all others who sent in an answer:

Thanks a lot for your thoughts. It seems some of you prefer using SQL as a procedural language too and some others prefer to keep the logic in the COBOL-program...

I talk to my programmer and he will test both variants, the 16 single sqls and the case variant with only one "big" sql statement. [...] 4165 94 32_Re: Security under RACF question10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Tue, 16 Jan 2001 05:22:43 -0600425_- I think you will find that you will encounter many anomolies when attempting to verify your external security against a Userid which has DB2 SYSADM authority.

I'm not sure what's going on with your Create Table. I have a few questions.

1. Is the ID issuing a 'SET CURRENT SQLID' before the CREATE? If so, then does the Current SQLID have either a DSNADM authority or MDSNDB class authorities for the DB? [...] 4260 73 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 16 Jan 2001 11:52:03 -0000586_iso-8859-1 Wow,

That started some discussion!!!

Just for some clarification, there are a couple of points I wanted to make which might explain where my stance comes from.

1. It's my experience visiting Db2 accounts and presenting at shows etc. that there is an almost 1 to 1 correlation between being 'allowed' to go on an SQL performance course and being a DBA. In other words, DBAs know EXACTLY how to code efficient SQL and developers do not. I know that this is a blanket statement and there will be exceptions (on both sides!) but this is the case in the [...] 4334 133 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Tue, 16 Jan 2001 12:22:28 -0000321_iso-8859-1 You old luddite Phil :-)

I can see both sides of this. I agree with your comment about developers not going on advanced SQL courses but the DBAs do. I must admit I get a bit bemused when a developer asks me for SQL advice though, it like 'hey, you should have done this 10 times more than I have'. [...] 4468 54 49_Re: moving data from one DB2 subsystem to another0_28_charles.t.schopf@VERIZON.COM31_Tue, 16 Jan 2001 08:20:59 -0500466_us-ascii I too have successfully used BMC's Change Manager product to migrate whole applications (size has never been a problem) to another DB2 subsystem in other OS/390 complexes without any problems. I have also used Change Manager to create copies of the application within the same DB2 subsystem.

Another option you may want to consider is using the DSNTIAUL program to unload the data along with the necessary control statements to reload the data. [...] 4523 86 67_Re: AW: CASE expression allowed to navigate inside a WHERE clause ?0_24_db46@DAIMLERCHRYSLER.COM31_Tue, 16 Jan 2001 08:22:12 -0500594_us-ascii I've used the following 'nested' case statements in a where clause to 'navigate' to the desired result. But, I also had some problems with this and reported such some years ago when our location got DB2 V5 for our OS/390 mainframe.

An example of this is as follows:

FROM PRDT.TBTORQUE TQ, PRDT.TBUSAGE4 U4 WHERE TQ.TORQUE_IND = U4.TORQUE_IND AND ( CASE WHEN TQ.UNIT_MEAS = 'NR' THEN CASE WHEN TQ.RSDL_MIN IS NOT NULL THEN 0 WHEN TQ.RSDL_TGT IS NOT NULL THEN 0 WHEN TQ.RSDL_MAX IS NOT NULL THEN 0 WHEN TQ.DYNM_MIN IS NOT NULL THEN 0 WHEN TQ.DYNM_TGT IS NOT NULL THEN [...] 4610 64 32_Re: Package Bind after Migration11_rick creech18_ykcirc@HOTMAIL.COM31_Tue, 16 Jan 2001 07:59:25 -0600694_- Actually, Catmaint will not find missing host variables. Upgrade will complete successfully, and binds/rebinds will fail for any plan or package using pre-2.3 dbrms without proper host variables. Any precompile without proper host variables will fail no matter what the level of the dbrm.

HTH,

Rick Creech

>From: Rob Crane >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Package Bind after Migration >Date: Mon, 15 Jan 2001 16:15:29 -0700 > >If there are any show stoppers, the upgrade doc usually points them out >and CATMAINT would generally find those and allow you take the >corrective action [...] 4675 191 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?14_Laredo, Andrew19_Andy_Laredo@BMC.COM31_Tue, 16 Jan 2001 08:58:43 -0600598_iso-8859-1 just another 2 cents...

the real problem/issue here is a language strong (the ultimate) on set processing, SQL, is combined with a language that is for the most part non-existent in its support of set processing, COBOL (and others). Therefore you end up with this mish-mash of code, some opting to load up on the SQL side, and some opting to plow ahead with 500 verbose lines of COBOL. If the procedural language could be better integrated with the results set and have better set processing features...then SQL would/could be used to extract data and COBOL could be used to [...] 4867 36 32_Re: Security under RACF question13_Juan Mautalen22_jmautalen@ANSES.GOV.AR31_Tue, 16 Jan 2001 12:31:11 -0300419_us-ascii Thanks John for your answer. In what concerns to your questions,



1. Is the ID issuing a 'SET CURRENT SQLID' before the CREATE? If so, then does the Current SQLID have either a DSNADM authority or MDSNDB class authorities for the DB?

Answer: No



2. Is the CREATOR (or 'prefix') of the table successfully created by User1 defined to DSNR as a Secondary Authid for User1? [...] 4904 24 31_DB2V6 (MVS), Buffer pool tuning20_Christoph Sassenrath36_christoph.sassenrath@D2MANNESMANN.DE31_Tue, 16 Jan 2001 16:43:46 +0100556_us-ascii Hallo all,

any idea where to find a comprehensive discussion about buffer pool tuning?

Thanks

Christoph Sassenrath Mannesmann Mobilfunk GmbH, Germany Abt. FIBD Systembetrieb, Raum 02C01 Telefon: +49 172 33099-3731 Telefax: +49 172 33099-1818 e-mail: christoph.sassenrath@d2vodafone.de

================================================ 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. 4929 33 35_Re: DB2V6 (MVS), Buffer pool tuning13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM31_Tue, 16 Jan 2001 10:33:52 -0500492_us-ascii Christoph, Try Joel Goldstein's website, http://www.responsivesystems.com. There are quite a few documents on this subject. Scott

Christoph Sassenrath wrote: > > Hallo all, > > any idea where to find a comprehensive discussion about > buffer pool tuning? > > Thanks > > Christoph Sassenrath > Mannesmann Mobilfunk GmbH, Germany > Abt. FIBD Systembetrieb, Raum 02C01 > Telefon: +49 172 33099-3731 > Telefax: +49 172 33099-1818 > e-mail: christoph.sassenrath@d2vodafone.de [...] 4963 41 35_Re: DB2V6 (MVS), Buffer pool tuning0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 16 Jan 2001 10:16:13 -0600690_us-ascii Also search the list archives. Buffer pool tuning has been part of many discussions.









Scott Lindsey @RYCI.COM> on 2001/01/16 09:33:52 AM Subject: Re: DB2V6 (MVS), Buffer pool tuning

Christoph, Try Joel Goldstein's website, http://www.responsivesystems.com. There are quite a few documents on this subject. Scott

Christoph Sassenrath wrote: > > Hallo all, > > any idea where to find a comprehensive discussion about > buffer pool tuning? > > Thanks > > Christoph Sassenrath > Mannesmann Mobilfunk GmbH, Germany > Abt. FIBD Systembetrieb, Raum 02C01 > e-mail: christoph.sassenrath@d2vodafone.de [...] 5005 16 26_Re: How do deadlocks work?12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jan 2001 10:19:48 -0600357_- I'm pretty sure this has never been documented. The general guidelines are clear, but there are exceptions, and interaction with all of the commit coordinators is very difficult to document. It's nearly impossible to document something for another product. Either it's incorrect now or will be when changes occur.

Roger Miller, DB2 for OS/390 [...] 5022 32 20_Re: Locking Question12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jan 2001 10:34:22 -0600410_- As noted, the isolation level is not useful for an update. The lock for updates should be a U lock in most cases. There is another concept called lock promotion. Here is a paste from the Administration Guide.

Lock promotion

Definition: Lock promotion is the action of exchanging one lock on a resource for a more restrictive lock on the same resource,held by the same application process. [...] 5055 16 33_Re: Case Sensitivity DB2 V6 vs V512_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jan 2001 10:39:24 -0600349_- DB2 V6 has new facilities, but they require changes in your code. In short, this is not an incompatible change. I suggest that you get the list of incompatible changes from the Installation Guide. Use the latest soft copy of the book, dsnig0g2.pdf, and look at the Migration Considerations section in chapter 2-7 Migrating the DB2 Subsystem. [...] 5072 87 32_Re: Package Bind after Migration0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 16 Jan 2001 11:16:01 -0600314_iso-8859-1 We went to V6 OS390 in December. Initially we didn't bother with a mass rebind, because it was not required. But after we got a few SQLCODE=-101 's management decided that it would be best if DBA did a mass rebind so that we, rather than users, could spot and correct any of these surprise errors. [...] 5160 37 32_Re: Security under RACF question12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jan 2001 11:15:36 -0600721_- Do you have the documentation from the red books or taken any of the classes at Share or the DB2 Technical Conference? Since the information crosses multiple products, the challenge is a bit more difficult. Here is the list I generally use:

OS/390 Security Server Release 4 OS/390 Security Server (RACF) Security Administrator's Guide, SC28-1915, chapter 11 OS/390 Release 4 Security Server (RACF) System Programmer's Guide, SC28-1913 DB2 Administration Guide, SC26-8957 Appendix B, Writing Exit Routines, Access Control Authorization Exit Web access: ibm.com/software/db2os390 then click Library Red Books: Web access: ibm.com/redbooks Version 5 Recent Enhancements, SG24-5421 Ready for e-business; OS/390 [...] 5198 22 47_Runstats on objects access by Dynamic SQL only?12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Tue, 16 Jan 2001 11:16:46 -0600479_- We have a application that is from the Webserver that accesses data using the plan DSNJDBC on subsystem DB1B. The same data is accessed from a DB2 plan running on another DB2 region on a differant LPAR using a alias with a three-part-name to get the data on DB1B. Question: does runstats need to be run on the objects on DB1B that the JDBC plan accesses? I believe the DDF access would by dynamic also because there is no plan bound on DB1B, so runstats would not help.?? [...] 5221 27 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?12_Jim Harrison12_jimh@QIS.NET31_Tue, 16 Jan 2001 12:26:18 -0500393_us-ascii You mean, given the chance, developers will opt out of the class? Sad to say, our site is another statistic to back you up. We had Bonnie Baker on site 4 days last year and I think only one developer bothered to show up. Being a DBA however, I wouldn't go so far as to say I know EXACTLY how to code efficient SQL. It still takes me some of time and effort to noodle it through. [...] 5249 37 0_15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM31_Tue, 16 Jan 2001 12:00:42 -0600370_us-ascii I have the following query:

SELECT * FROM "PAYR" WHERE "MANDT" = ? AND "ICHEC" = ? AND "ZBUKR" = ? AND "HBKID" = ? AND "HKTID" = ? AND "CHECF" <= ? AND "CHECT" >= ? FOR FETCH ONLY WITH UR

There are two indexes on this table:



PAYR~A : MANDT, ICHEC, ZBUKR, HBKID, HKTID, CHECT PAYR~A 2 : MANDT, ICHEC, ZBUKR, HBKID, HKTID, CHECF [...] 5287 62 0_16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 16 Jan 2001 13:09:45 -0500584_iso-8859-1 Try changing

AND "CHECF" <= ?

to AND ("CHECF" <= ? OR 0 = 1)

This will discourage the use of PAYR~A 2 over PAYR~A regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHUS.COM] Sent: Tuesday, January 16, 2001 1:01 PM To: DB2-L@RYCI.COM Subject:



I have the following query:

SELECT * FROM "PAYR" WHERE "MANDT" = ? AND "ICHEC" = ? AND "ZBUKR" = ? AND "HBKID" = ? AND "HKTID" = ? AND "CHECF" <= ? AND "CHECT" >= ? FOR FETCH ONLY WITH UR [...] 5350 41 32_DB2 DBAs and Systems Programmers13_Ginger Duncan20_gingerd@TGA-JOBS.COM31_Tue, 16 Jan 2001 13:34:26 -0500419_Windows-1252 Good afternoon. Software Vendor seeks experienced Database Administrators and Systems Programmers experienced in DB2 for Consulting and Customer Support. Must have minimum of 7 years experience in DB2 for mainframe (OS/390 environment). General information regarding both consulting and customer support opportunities listed below. Openings are in the following states: GA, TX, VA, IL, MA, MN, MI, NJ [...] 5392 33 29_Excel and DB2 ODBC Connection0_20_vpacheco@AMADEUS.NET31_Tue, 16 Jan 2001 19:40:56 +0100521_us-ascii Dear DB2 community

I have automated some reports in MS Excel using an ODBC connection to DB2 (V5) on OS/390 (thru DB2 Connect PE V7). Before I started distributing this reports, I noticed, fortunately, that Excel records all the ODBC information of the sheet - that is, the queries, the datasource, and, surprisingly, the userid and password. The worst of all is that if we edit the sheet using Notepad all this information can be easily read and the userid and password appear in plain text format. [...] 5426 57 0_12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Tue, 16 Jan 2001 13:32:26 -0500428_US-ASCII Dan-

If PAYR~A is the clustering index, it will probably also take precedence...

Mark Anzmann Database Administration DHRIS Program Team Accenture

>>> daniel.sullivan@ZURICHUS.COM 01/16 1:00 PM >>> I have the following query:

SELECT * FROM "PAYR" WHERE "MANDT" = ? AND "ICHEC" = ? AND "ZBUKR" = ? AND "HBKID" = ? AND "HKTID" = ? AND "CHECF" <= ? AND "CHECT" >= ? FOR FETCH ONLY WITH UR [...] 5484 13 0_15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM31_Tue, 16 Jan 2001 12:53:19 -0600400_us-ascii It's not a clustering index. I would like to try the hint or change the sql, but this is SAP and we can't change the code, only poosibly add an idex.

================================================ 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. 5498 104 35_Re: Change optimizer's index choice13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Tue, 16 Jan 2001 12:59:12 -0600541_iso-8859-1 Daniel,

Although Eric's answer is not incorrect, it does render the predicate stage 2.

There are a few methods (tricks) to encourage the usage of PAYR~A and keep all predicates stage 1: 1- ORDER BY CHECT (may be dangerous if index is dropped in future). 2- AND "CHECF <= ? CONCAT ''" (for CHAR) or "CHECF <= ? + 0" (for numeric) 3- AND "CHECF <= ? OR MANDT <> ?" <-- not possible predicate 4- AND "CHECF < ? OR CHECF = ?" 5- Change clustering index to PAYR~A (may not be possible due to other queries or insert [...] 5603 36 29_regarding repartioning.......17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Tue, 16 Jan 2001 12:59:13 -0600461_- Hi Folks,

In our shope we are using DB2 V6 on OS390, we have a partion tablespace in the production.... we need to do a repartion for that tablespace becasuse of ran out of room. we have on unique index and partioned index on the tablespace....

What we are planning to do is unload the data to a dataset by part wise.. then we are going to drop the tablespace for repartioning. somebody sugested that sort the unloaded data before load.... [...] 5640 37 0_16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 16 Jan 2001 14:00:02 -0500471_iso-8859-1 Maybe edit the catalog statistics so that the undesired index is very unattractive to the optimizer, rebind the package(s) with this SQL (useless effort if this is dynamic SQL), then restore the catalog stats to correctness?

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHUS.COM] Sent: Tuesday, January 16, 2001 1:53 PM To: DB2-L@RYCI.COM Subject: [...] 5678 44 35_Re: Change Optimizer's index choice13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Tue, 16 Jan 2001 13:06:08 -0600344_iso-8859-1 Daniel,

You could also change the PAYR~A index to add CHECF as an additional column. This would become an index screening predicate and encourage the optimizer to use PAYR~A. It would also have the side benefit of potentially reducing data page access. The downside is the additional column/space required for the index. [...] 5723 79 35_FW: Change Optimizer's index choice13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Tue, 16 Jan 2001 13:13:37 -0600436_iso-8859-1 On behalf of Eric Pearson...

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@nscorp.com] Sent: Tuesday, January 16, 2001 1:05 PM To: 'terry_purcell@ylassoc.com' Subject: RE: Change Optimizer's index choice



This is good if the PAYR~A index is not currently UNIQUE. If it is, the adding the additional column will result in uniqueness not being enforced as originally desired. [...] 5803 14 17_IBM DB2 utilities14_Cathy Puzzuoli24_cathy.puzzuoli@SABRE.COM31_Tue, 16 Jan 2001 13:28:40 -0600371_- What DB2 utilities will not be provided "FREE" with DB2? Will there be a base set of utilities, like COPY/REORG/LOAD that the user will have to pay for, and a base set of utilities, like REPAIR/MODIFY/QUIESCE that will be "FREE"? How about the IBM supplied applications, like DSN1COPY, etc.? Are these going to be "FREE" or is there going to be a charge for them? [...] 5818 41 21_Re: IBM DB2 utilities16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 16 Jan 2001 14:35:46 -0500457_iso-8859-1 Charge starts with V7. Don't know what is still free. I vaguely remember an IBMer saying that the entire pricing structure was changing but that the effects shouldn't be too bad for most customers.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Cathy Puzzuoli [mailto:cathy.puzzuoli@SABRE.COM] Sent: Tuesday, January 16, 2001 2:29 PM To: DB2-L@RYCI.COM Subject: IBM DB2 utilities [...] 5860 38 21_Re: IBM DB2 utilities10_Hill, Dave14_DHill@FSCO.COM31_Tue, 16 Jan 2001 14:48:53 -0500438_iso-8859-1 Cathy- This is what I've been told. Visual Explain is on the web for 60 day trial as is Stored Proceddure Builder. DB2 Control Center is free DB2 Estimator is also free DB2 performance Monitor is a chargabler piece DB2 installer I'm not sure Dave

-----Original Message----- From: Cathy Puzzuoli [mailto:cathy.puzzuoli@SABRE.COM] Sent: Tuesday, January 16, 2001 2:29 PM To: DB2-L@RYCI.COM Subject: IBM DB2 utilities [...] 5899 62 0_12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Tue, 16 Jan 2001 13:50:05 -0600356_us-ascii Well, I guess you could drop the A2 index. What function does it serve, anyway? I don't see it in our system.

Best regards, John P. Higgins Performance Specialist SAP BASIS Team

Deere & Company Voice: (309) 748-1625 Attn: GTTC East FAX: (309) 748-1605 One John Deere Place email: HigginsJohn@JohnDeere.com Moline, IL 61265-8098 [...] 5962 81 21_Re: IBM DB2 utilities18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 16 Jan 2001 14:58:43 -0500523_iso-8859-1 I would be interested in knowing about these new charges too but we may be better off waiting until we hear something from the horses-mouth themselves. (in this case IBM). I've heard alot of speculation on what is going to be 'costed out' and what is going to be 'free' (but nothing is ever free). I would hope at sometime IBM would either come out with a good list of what is and isn't chargeable or have a web page to visit. We're going from DB2 V5 to DB2 V6 this year and before we move to DB2 V7 (where [...] 6044 45 41_Online reorg abends 00E40345 and 00E4001A15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Tue, 16 Jan 2001 14:15:35 -0600662_iso-8859-1 Dear List,

DB2 V6.1/OS390 2.06.

Recall any experiences with abends 00E40345 and 00E4001A during online reorg?

The reorg stmt: REORG TABLESPACE x.y MAPPINGTABLE z.a KEEPDICTIONARY SHRLEVEL CHANGE COPYDDN(SYSCOPY)

The reorg ran fine in DB2 version 5.1. We recently moved to DB2 version 6.1 and it abended with REASON=X'00E40345'. Not implying that all online reorgs have problems with 6.1 because most of our online reorgs have run fine in 6.1 but not this one... Looking at APAR PQ41021 we thought that the workaround of specifying SWnnWKmm datasets (new Ver 6.1 feature) in the reorg step would get us through. We had [...] 6090 14 11_Index Usage15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM31_Tue, 16 Jan 2001 14:13:33 -0600392_us-ascii The a~2 index is used by another query in the same application. It improves a different query, making it run in about 2 hours rather than 10.

================================================ 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. 6105 29 22_DDF definition in BSDS14_Dale E. Miller15_dmiller@UGI.COM31_Tue, 16 Jan 2001 16:43:47 -0500546_us-ascii I have a question on DDF setup. I am trying to implement DDF in an existing subsystem. The subsystem is DB2 V6. I worked with my Network guy to define the TCPIP and VTAM info. I modified DSNZPARM to have DB2 start DDF. Then I ran the CHANGE LOG Utility (DSNJ0003) to define the LOCATION and LUNAME in the BSDS. The utility ended with RC=0. Everything looked good. When I ran DSNJ0004 to print the BSDS and verify the entries, the entries have NOT changed! I checked the JCL to ensure I used the correct datasets. Of course, the DDF [...] 6135 61 26_Re: DDF definition in BSDS0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Tue, 16 Jan 2001 17:18:37 -0500475_us-ascii Did you define a TCPORT in the BSDS? The DB2 address space needs a DD card to point to the TCP/IP system level datasets also.

HTH

Ed







"Dale E. Miller" @RYCI.COM> on 01/16/2001 04:43:47 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DDF definition in BSDS [...] 6197 74 51_Re: Runstats on objects access by Dynamic SQL only?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 17 Jan 2001 09:56:38 +1100364_iso-8859-1 Richard

DB2 statistics are used to determine the access path used by both dynamic (ie prepared) and static (ie bound) SQL. If you don't run RUNSTATS (or used explicit SQL to update the catalog), the only statistics available are the default statistics. It is unlikely that the default statistics will produce the most efficient access path. [...] 6272 84 33_DB2 V6 for OS/390 - Reorg Problem22_drdeath@ozemail.com.au22_drdeath@OZEMAIL.COM.AU31_Wed, 17 Jan 2001 01:32:16 "GMT"412_us-ascii We have a bit of a poser here with DB2 V6 for OS/390.

We are having a problem with Reorgs that allocate SWnnWKxx datasets dynamically. Under V6, DB2 is kicking of a pair of tasks per index when reorging a tablespace, and as such is dynamically allocating a set of SWnnWKxxs per task. We discovered that if there were four or more indexes the job would fail with a SO4E Reason Code 00E40005. [...] 6357 40 34_DB2 V6 - OS/390 Recovery Questions13_Moss, William15_MossW@AETNA.COM31_Tue, 16 Jan 2001 20:54:45 -0500552_iso-8859-1 We have just upgraded to DB2 V6 and it crashed after running for two days. After a successful restart of DB2, there were 75 DSNI023 messages like this one:

DSNI023I *DB3D DSNIERST PAGESET DSM1P000.XM1H0004 PART (n/a) IS AREST ON BEHALF OF UR 0266D734DD3F. BACKOUT TO LRSN B5411CD496D4 IS REQUIRED.

DB2 performed 74 successful backouts. The only one it didn't do was the one listed above. It is an indexspace for an NPI and when I display the index, it looks good and it is being used successfully by application programs. [...] 6398 90 38_Re: DB2 V6 - OS/390 Recovery Questions14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 17 Jan 2001 14:54:47 +1100642_iso-8859-1 William,

from a lack of experience with these problems, some items:

DSNI023I - when you "display[ed] the index", I presume did you mean "-DIS DB(DSM1P000) SP(XM1H0004)". Did you include "ADVISORY" or "AREST" on the command? We had these (although with RESTP) in 'normal' operations and had to rebuild the affected indexes.

J0001.A001 dataset - aren't these the clusters that on-line reorg creates to store the re-orged ts? After the reorg finishes, the I0001 cluster (pre-reorg) is deleted and the J0001 cluster is renamed to I0001. (BTW, I recall from AP IDUG (too lazy to check the foils), that, in V7, [...] 6489 29 20_DSN1COPY ALTERNATIVE12_John Piccoli16_jpiccoli@LOOK.CA31_Tue, 16 Jan 2001 23:00:45 -0500428_us-ascii Can anyone recommend a vendor product that can copy data across DB2 subsystems like the DSN1COPY utility and internally handle object id translation.

John Piccoli DBA CDS

================================================ 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. 6519 35 79_Extended Absence Response: DB2-L Digest - 16 Jan 2001 to 17 Jan 2001 (#2001-17)0_38_gerardo.t.garcia@US.ARTHURANDERSEN.COM31_Wed, 17 Jan 2001 01:19:08 -0600477_us-ascii Gerardo T. Garcia will be away from Friday January 12, 2001 to Thursday January 18, 2001.

During my extended absence I am receiving your mail. However, because I may not be able to respond immediately, you may want to leave me a voice message if the matter is urgent. Otherwise, I will respond to your e-mail as soon as I can once I return to the office on January 18. Thank You. *******************Internet Email Confidentiality Footer******************* [...] 6555 19 45_Re: Online reorg abends 00E40345 and 00E4001A16_Friemel, Norbert24_Friemel@RRZ-KARLSRUHE.DE31_Tue, 16 Jan 2001 23:48:17 +0100430_iso-8859-1 > > Recall any experiences with abends 00E40345 and 00E4001A during online > reorg? >

Yes. I saw 00E4001A when '//UTPRINT DD SYSOUT=*' was missing.

Norbert Friemel

================================================ 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. 6575 78 38_Re: DB2 V6 - OS/390 Recovery Questions10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 17 Jan 2001 08:45:23 +0100586_us-ascii Did you ever executed a COPY DFSMSdss with RENUNC of some DB2 tablespace ?

Regards Max Scarpa









"Moss, William" To: DB2-L@RYCI.COM Subject: DB2 V6 - OS/390 Recovery Questions Sent by: DB2 Data Base Discussion List



17/01/01 02.54 Please respond to DB2 Data Base Discussion List









We have just upgraded to DB2 V6 and it crashed after running for two days. After a successful restart of DB2, there were 75 DSNI023 messages like this one: [...] 6654 91 8_A2 Index12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Wed, 17 Jan 2001 10:05:22 +0200266_iso-8859-1 Hi,

SELECT * FROM "PAYR" WHERE "MANDT" = ? AND "ICHEC" = ? AND "ZBUKR" = ? AND "HBKID" = ? AND "HKTID" = ? AND "CHECT" >= ? AND "CHECF" <= ? FOR FETCH ONLY WITH UR



Since it's AND for all condition it'll by the order they are defined. 6746 39 40_Use of indicator variables in predicates11_Phil Button21_Phil.Button@BMW.CO.UK31_Wed, 17 Jan 2001 08:46:14 -0000543_iso-8859-1 Hi

We are in the process of upgrading from DB2 V5 to V6 on OS390, and one of our developers has 'discovered' that in V6 you can use SQL of the following form in precompiled COBOL:

SELECT xxx FROM TABLE WHERE COL1 = :hv1 AND COL2 = :hv2 :ind2



- if the indicator variable is set then rows will be returned where col2 is null. EXPLAIN shows that, if there is an index on COL2, that it will use a matching index scan for this access (which might not be the case if you used ORs to combine predicates). [...] 6786 61 24_Re: DSN1COPY ALTERNATIVE9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Wed, 17 Jan 2001 09:07:01 +0000630_us-ascii Have a look at BMC's Resource Maximiser http://www.bmc.com/rs-bin/RightSite/getcontent/bmcdoc.html?dmw_objectid=0900320180256c9d&dmw_format=html Kind Regards, Andy Hunt - Scottish And Southern Energy







John Piccoli on 17/01/2001 04:00:45

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Andy Hunt/HAV/SSE) Subject: DSN1COPY ALTERNATIVE





Can anyone recommend a vendor product that can copy data across DB2 subsystems like the DSN1COPY utility and internally handle object id translation. [...] 6848 93 44_Re: Use of indicator variables in predicates16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 17 Jan 2001 14:40:33 +0530267_- Hi Phil,

If i am correct and i can recall in which version i used this then it is available in V5 with no problems. I am still not sure about this. Infact it seems this helped us to do performance tuning of one of the SQL which was something like this: [...] 6942 103 32_Re: Package Bind after Migration0_27_jim.leask@RS-COMPONENTS.COM31_Wed, 17 Jan 2001 09:33:33 -0000474_iso-8859-1 This is probably obvious; I would rebind all plans and packages in your pre-production/development environments and this should highlight any potential -101's and -104's.

If you do not want to risk finding these problems in production for the first time.

Jim.

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: 16 January 2001 17:16 To: DB2-L@RYCI.COM Subject: Re: Package Bind after Migration [...] 7046 129 33_Re: regarding repartioning.......0_18_mebert@AMADEUS.NET31_Wed, 17 Jan 2001 10:55:21 +0100561_us-ascii Hi Kumar,

it is not necessary to sort the data to be loaded, but it is recommended. For one reason, the data will then be effectively reorged. Another reason, if the data is already sorted, LOAD can skip the SORT phase (it does not need to sort index keys) - if you've got more than just the partitioning index, it may be different. The Utility Guide has details on when LOAD can skip the SORT phase. If you're unloading with DSNTIAUL and an ORDER BY..., you don't need to sort the data. However, I wouldn't recommend using DSNTIAUL - it's [...] 7176 47 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 17 Jan 2001 10:53:51 -0000366_iso-8859-1 Actually,

I'd love to know whether developers are 'opting out' or just not being invited.

At CA (and at PLATINUM) we regularly run seminars (free mind you!) on performance issues and we always get 90% of the attendees from the DBA side of the fence. Part of this may be because we traditionally targeted DBAs and the like but I wonder. [...] 7224 51 32_Re: Security under RACF question10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 17 Jan 2001 04:53:38 -0600337_- Surely the puzzling thing here is that he was able to use RACF to prevent a SYSADM user from doing something? The fact that the success of the operation to Create a Table was dependant upon the Qualifier of the Table must constitute an anomaly.

On Tue, 16 Jan 2001 11:15:36 -0600, Roger Miller wrote: [...] 7276 30 41_DB2 Visual Explain - authorisation needed22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Wed, 17 Jan 2001 09:58:23 -0200592_iso-8859-1 We have been facing some difficulties regarding DB2 Visual Explain authorisation. Lately, we decided to distribute DB2 VE to our system analysts and programmers so that they could evaluate their own queries. However, nobody has authorisation to modify data on our production subsystem, except the DBA team. Even if analysts/programmers have an associated Plan_Table, they cannot explain their queries because for running DB2 VE the user must have all privileges to run the query. Does anybody has any hint about distributing DB2 VE to analysts/programmers in such conditions? [...] 7307 21 7_hi list33_=?iso-8859-1?Q?R.teoman_KO=C7ER?=22_tkocer@HALKBANK.COM.TR31_Wed, 17 Jan 2001 14:22:58 +0200493_iso-8859-1 are there any site or documents to find a beginning for system programming and db2 database programming? thanks in advance

R.Teoman KOÇER HALKBANK BIM System Programmer Phone:903122891116 Web: http://communities.msn.com/TheosCoolPage

================================================ 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. 7329 50 26_Re: DDF definition in BSDS14_Picou, Michael33_picou.michael@MAIL.DC.STATE.FL.US31_Wed, 17 Jan 2001 07:35:32 -0500629_iso-8859-1 When you ran the DSNJU003 utility did you specify the PORT and RESPORT parms? Is the VTAM APPLID active? Did you run DSNJU003 with NOPASSWD?



-----Original Message----- From: Dale E. Miller [mailto:dmiller@UGI.COM] Sent: Tuesday, January 16, 2001 4:44 PM To: DB2-L@RYCI.COM Subject: DDF definition in BSDS



I have a question on DDF setup. I am trying to implement DDF in an existing subsystem. The subsystem is DB2 V6. I worked with my Network guy to define the TCPIP and VTAM info. I modified DSNZPARM to have DB2 start DDF. Then I ran the CHANGE LOG Utility (DSNJ0003) to define the [...] 7380 76 26_Re: DDF definition in BSDS12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Wed, 17 Jan 2001 07:41:52 -0500554_us-ascii did you run it against both bsds's in your jcl?







"Picou, Michael" @RYCI.COM> on 01/17/2001 07:35:32 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: DDF definition in BSDS



When you ran the DSNJU003 utility did you specify the PORT and RESPORT parms? Is the VTAM APPLID active? Did you run DSNJU003 with NOPASSWD? [...] 7457 73 60_AW: documents to find a beginning for system programming....12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 17 Jan 2001 13:44:12 +0100393_iso-8859-1 Teoman,

I assume you're talking about DB2 for OS/390. In this case two books are very helpful:

1. Craig Mullins "DB2 Developers Guide". Take a look at http://www.craigsmullins.com/ and you'll find a lot information...

2. Susan Graziano Sloan und Ann Kilty's book "An Introduction to DB2 for OS/390 Version 7". You can order this book via the IBM webpages. [...] 7531 23 45_Re: DB2 Visual Explain - authorization needed11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Wed, 17 Jan 2001 08:05:06 -050083_us-ascii In order to Explain an SQL statement, I believe the Analyst/Programmer has 7555 117 32_Re: Package Bind after Migration13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 17 Jan 2001 07:12:21 -0600487_iso-8859-1 you could do a BIND COPY into a temp. collection and identify them that way.

-----Original Message----- From: jim.leask@RS-COMPONENTS.COM [mailto:jim.leask@RS-COMPONENTS.COM] Sent: Wednesday, January 17, 2001 3:34 AM To: DB2-L@RYCI.COM Subject: Re: Package Bind after Migration



This is probably obvious; I would rebind all plans and packages in your pre-production/development environments and this should highlight any potential -101's and -104's. [...] 7673 97 37_Re: DB2 V6 for OS/390 - Reorg Problem9_Bruce,Mae26_BruceM@MAIL.OA.STATE.MO.US31_Wed, 17 Jan 2001 07:15:12 -0600416_iso-8859-1 I can't remember if it is the exact same reason code we were getting, but the symptoms sure are the same. We had to upgrade our sort software (we use SYNCSORT) and then there was a DB2 fix to be applied also. (I work on the development end and it was our system programmers that applied the fix so I don't have the number for it.) Call your DB2 support; they should point you in the right direction. [...] 7771 46 26_Re: DDF definition in BSDS13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 17 Jan 2001 07:16:59 -0600623_iso-8859-1 What error are you getting.

-----Original Message----- From: Dale E. Miller [mailto:dmiller@UGI.COM] Sent: Tuesday, January 16, 2001 3:44 PM To: DB2-L@RYCI.COM Subject: DDF definition in BSDS



I have a question on DDF setup. I am trying to implement DDF in an existing subsystem. The subsystem is DB2 V6. I worked with my Network guy to define the TCPIP and VTAM info. I modified DSNZPARM to have DB2 start DDF. Then I ran the CHANGE LOG Utility (DSNJ0003) to define the LOCATION and LUNAME in the BSDS. The utility ended with RC=0. Everything looked good. When I ran DSNJ0004 to print [...] 7818 38 26_BMC-ChangeManager-Question14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Wed, 17 Jan 2001 14:47:46 +0100368_us-ascii Hi folks,

I have two questions concerning BMC's ChangeManager:

1) Is there any way to 'persuade' the product to run ImageCopies on partition-level rather than on tablespace-level ? 2) When does ChangeManager decide to create indices using "DEFER YES" ? And can this decision be influenced ?

I couldn't find any hints in the manuals. [...] 7857 28 10_Use of BP00_27_vinhaes@CAIXASEGUROS.COM.BR31_Wed, 17 Jan 2001 12:13:56 -0300332_us-ascii Dear List

I would like some help to configure BP0, some people say that it is better to put in BP0 only catalog and directory, and create others BP for tablespaces and indexes.

We have 3 BP

BP0 - catalog, directory and all tablespaces] BP1 - only indexes BP2 - sort

We use DB2 V 5.01/0S390 [...] 7886 56 14_Re: Use of BP012_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Wed, 17 Jan 2001 09:27:56 -0500310_us-ascii We use BP0 only for catalog and directory. We split indexes and tablespaces into 12 other pools based upon usage(seq or random). Makes tuning nicer. Also we stay clear of BP0 for other objects due to future DB2's possibily not allowing other objects other than catalog and directory to be there. [...] 7943 82 14_Re: Use of BP016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 17 Jan 2001 09:26:09 -0500342_iso-8859-1 Another consideration is Data Sharing - Catalog/Directory *must* be in BP0. For the 'application' tablespaces and indexes you may benefit by having one set of bufferpools (for data sharing) which are associated with Group Bufferpools, and another set (for data you do not wish to share) not associated with Group Bufferpools. [...] 8026 36 14_Re: Use of BP014_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 17 Jan 2001 09:55:08 -0500588_ISO-8859-1 BP0 should be reserved for the catalog and directory, and does not have to be large to provide good performance for those objects. The size of your environment and applications determines how many pools you should use. Most installations can optimize in 6-8 pools, some need a few more, and some small ones can get by with 4-5 pools. Objects should be grouped by access type and working set size (resident pages in a pool) for optimal performance. Too many pools waste memory and don't provide good performance, and too few very large pools will also waste memory and not [...] 8063 82 60_Re: documents to find a beginning for system programming....33_=?iso-8859-1?Q?R.teoman_KO=C7ER?=22_tkocer@HALKBANK.COM.TR31_Wed, 17 Jan 2001 17:10:34 +0200527_iso-8859-1 thanks for your reply

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Peter, Georg Sent: 17 Ocak 2001 Çarþamba 14:44 To: DB2-L@RYCI.COM Subject: AW: documents to find a beginning for system programming....



Teoman,

I assume you're talking about DB2 for OS/390. In this case two books are very helpful:

1. Craig Mullins "DB2 Developers Guide". Take a look at http://www.craigsmullins.com/ and you'll find a lot information... [...] 8146 61 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM31_Wed, 17 Jan 2001 10:21:48 -0500 8208 64 33_Re: Excel and DB2 ODBC Connection19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Wed, 17 Jan 2001 07:21:57 -0800390_iso-8859-1 How are the User ID and password being given to the spreadsheet?

If the macro process is fully automated, they have to be "hardcoded" there somewhere.

We do something similar, but have the VB macro coded to do a dialog box pop up and ask for the values each time the SQL is being executed, so the User ID and Password are not kept in the spreadsheet anywhere. [...] 8273 111 64_Re: CASE expression allowed to navigate inside a WHERE c lause ?18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM31_Wed, 17 Jan 2001 10:23:57 -0500 8385 56 45_Re: DB2 Visual Explain - authorisation needed0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 17 Jan 2001 09:35:09 -0600471_us-ascii Why are developers trying to explain, visual or not, in production? We copy runstats from prod to devel and developers each have their own plan_table so they can do explains before moving to production.









Claudio Meluzzi Mendes @RYCI.COM> on 2001/01/17 05:58:23 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 8442 29 32_DB2 : internal or RACF security?13_Juan Mautalen22_jmautalen@ANSES.GOV.AR31_Wed, 17 Jan 2001 12:40:53 -0300360_us-ascii Hi, We are testing security for DB2 under RACF in our non-production Lpar. I found some problems, like some things seems that cannot be protected properly under RACF (as DB2 commands, for instance). We are in the process of deciding about the convenience of migrating DB2 security to RACF in the Production Lpar Some questions come to my mind... [...] 8472 133 64_Was: CASE expression allowed to navigate inside a WHERE clause ?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 17 Jan 2001 16:08:45 -0000348_iso-8859-1 In the UK we have seminars on performance from an e- perspective scheduled for Feb 6/7/8. In Belgium a performance seminar on Mar 13, in The Netherlands on Mar 14 (none of these invitations have gone out yet).

DB2 days are planned for Scandinavia, Austria/Eastern Europe, Israel. We just did a series in Germany in December. [...] 8606 22 35_Re: DDF definition in BSDS (answer)14_Dale E. Miller15_dmiller@UGI.COM31_Wed, 17 Jan 2001 11:14:07 -0500384_us-ascii Thanks for all the ideas. One of the replies gave me the answer. I forgot to specify the NOPASSWD/PASSWORD parameter in the DSNJU003 utility. It ran with RC=0 but apparently never updated the BSDS. When I specified the parameter and reran, the updates took. Thanks to all!



-------- //-- Dale Miller //-- Database Administrator //-- Email: dmiller@ugi.com [...] 8629 111 60_Re: documents to find a beginning for system programming....13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Wed, 17 Jan 2001 10:22:44 -0600444_iso-8859-1 Like to add one. For my money, other than the IBM manuals, Gabrielle Wiorkowski's DB2 for OS/390 is the best DB2 book around. It's 1000 pages, 80 bucks, and worth every penny. You can order from 972-412-8866.

-----Original Message----- From: R.teoman KOÇER [mailto:tkocer@HALKBANK.COM.TR] Sent: Wednesday, January 17, 2001 10:11 AM To: DB2-L@RYCI.COM Subject: Re: documents to find a beginning for system programming.... [...] 8741 57 12_DB2 and CICS15_Pelle Svennberg24_per-erik.svennberg@NB.SE29_Wed, 17 Jan 2001 16:25:05 GMT525_us-ascii Hello

I have a question regarding DB2 and CICS .

The basics are following :

- We run in one MVS environment (called MVS0 below) - We have 2 different CICS systems (CICSA and CICSB) running in MVS0 - We have 2 different DB2 systems (DB2A and DB2B) running in MVS0 - CICSA is connected to DB2A and CICSB is connected to DB2B - We have OAM running in MVS0 and all OAM tables are placed in DB2A and we can have only 1 OAM in the same MVS (that's by the book) - We have OnDemand running in MVS0 [...] 8799 25 32_Re: Security under RACF question12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 17 Jan 2001 10:22:12 -0600358_- It appears to me to be working just as designed. If RACF can identify the object, it can allow access or deny access. If it does not have a profile for the object, then DB2 access controls are used. I'm guessing that you thought that DB2 access controls would always be used, but that's not so. This is what a number of different customers requested. [...] 8825 139 32_Re: Security under RACF question12_David Booher22_David.Booher@QUEST.COM31_Wed, 17 Jan 2001 08:33:42 -0800671_iso-8859-1 Roger,

Thanks for your input. What are the best manuals to use when implementing these DB2 controls under RACF?

Thanks, Dave Booher



-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Wednesday, January 17, 2001 10:22 AM To: DB2-L@RYCI.COM Subject: Re: Security under RACF question



It appears to me to be working just as designed. If RACF can identify the object, it can allow access or deny access. If it does not have a profile for the object, then DB2 access controls are used. I'm guessing that you thought that DB2 access controls would always be used, but that's not so. This [...] 8965 19 35_Re: DB2V6 (MVS), Buffer pool tuning12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 17 Jan 2001 10:32:06 -0600603_- I still like the Chuck Hoover presentations and booklets. The ones on buffer pools and page sets, the ones on tuning and on external IO are all useful.

http://www.compuware.com/products/hoover/index.asp

Have you also looked over the Administration Guide chapter on Tuning DB2 Buffer, EDM, RID, and Sort Pools?

Roger Miller, DB2 for OS/390

================================================ 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. 8985 125 12_Re: A2 Index13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Wed, 17 Jan 2001 10:56:33 -0600403_iso-8859-1 Isaac,

Order of predicate coding within the query has no impact on the optimizer's index selection.

Predicate coding order does have an impact though. Within each stage (1 and 2), non-indexed predicates are applied in the sequence: - Equals predicates - Range predicates - All other predicates Further to that, the predicates are then executed in the order they are coded. [...] 9111 71 19_Systems Programming14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 17 Jan 2001 09:38:29 -0800546_- The first place I would start is to learn how the OS works and where it came from and where it is going. The best book I had was MVS Concepts and Facilities by Bob Johnson, J.Ranade IBM Series, pub McGraw Hill, 1989. A lot of the concepts still apply though, so if you can get a copy it would be worthwile. I don't believe it was ever updated though to cover OS/390. The OS/390 Initialization and Tuning Guide (IBM Manual) is also a good reference. For DB2 Systems Programming the closest reference on that is another old J.Randade series [...] 9183 19 16_BOOLEAN datatype11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Wed, 17 Jan 2001 17:40:17 -0000401_- Dear list,

What datatype is available for use within DB2 (OS/390 and non) to replicate the BIT datatype in SQLServer?

Many thanks,

Carl.

================================================ 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. 9203 80 38_Re: DB2 V6 - OS/390 Recovery Questions0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 17 Jan 2001 11:48:24 -0600624_us-ascii Here are a couple of earlier responses from the list about AREST status on a TS or IS

DB2 V6 Command Reference manual indicates the following:

AREST indicates that the object ( a table space , index space or a physical partition of a table space or index space ) is in a Advisory Restart Pending State. If backout activity against the object is not already underway, it should be initiated either by issuing the RECOVER POSTPONED command or by recycling the system with the system parameter LBACKOUT=AUTO ************ We also encountered this problem after an Abend involving parallelism. [...] 9284 112 35_Re: DB2V6 (MVS), Buffer pool tuning14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 17 Jan 2001 09:53:23 -0800363_iso-8859-1 Chucks latest one is "Everythng You Ever Wanted to Know About DB2 Buffering" 159 page presentation. It is awesome. Plus Joel Goldstein has excellent presentations available for download on his site. HTH Phil

-----Original Message----- From: Roger Miller To: DB2-L@RYCI.COM Sent: 1/17/01 8:32 AM Subject: Re: DB2V6 (MVS), Buffer pool tuning [...] 9397 54 11_Re: hi list12_Hoover, Gary18_GHoover@UNIPAC.COM31_Wed, 17 Jan 2001 10:45:16 -0700451_iso-8859-1 Check out the IBM Redbooks titled 'ABC's OF OS/390 System Programming' Volume 1-5 , SG24-5597 SG24-5652 SG24-5653 SG24-5654 SG24-5655

You can download them from the WEB at the IBM Redbooks site....

Gary Hoover System Programmer Unipac 303-696-3511



-----Original Message----- From: R.teoman KOÇER [mailto:tkocer@HALKBANK.COM.TR] Sent: Wednesday, January 17, 2001 5:23 AM To: DB2-L@RYCI.COM Subject: hi list [...] 9452 46 20_Re: BOOLEAN datatype11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Wed, 17 Jan 2001 09:59:19 -0800431_iso-8859-1 On db2 V6 OS/390 you can use the FOR BIT DATA column clause on the Create table........



Robert Jans Albertsons, Inc. Mail Drop 4427 155 North 400 West Salt Lake City, UT 84103

Phone (801) 961-3212





-----Original Message----- From: Carl Newton [mailto:newtonc@INNOVATION-GROUP.CO.UK] Sent: Wednesday, January 17, 2001 10:40 AM To: DB2-L@RYCI.COM Subject: BOOLEAN datatype [...] 9499 64 20_Re: BOOLEAN datatype11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Wed, 17 Jan 2001 17:57:03 -0000376_- Does that mean I should define the column as CHAR(1) FOR BIT DATA and it will perform the same......

-----Original Message----- From: Robert Jans [mailto:robert_jans@ALBERTSONS.COM] Sent: 17 January 2001 17:59 To: DB2-L@RYCI.COM Subject: Re: BOOLEAN datatype



On db2 V6 OS/390 you can use the FOR BIT DATA column clause on the Create table........ [...] 9564 17 19_Re: DB2 UDB Restore11_Jeff Kalman15_jkalman@CSC.COM31_Wed, 17 Jan 2001 13:18:36 -0500373_us-ascii Hi all, Can you restore a DB2 UDB back up file from version 6 and restore it as new in DB2 UDB version 7?

TIA. Jeff

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9582 35 19_Re: DB2 UDB Restore13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 17 Jan 2001 12:47:44 -0600327_US-ASCII Jeff, Yes as long as the target server has the same DB2 UDB drive mappings. The restore will do the V7 migration for you except for the V7 index semantics. You may want to run the db2uiddl command if your V6 database was migrated from V5.x. This will allow support of deferred index uniqueness checking. HTH Kurt [...] 9618 109 8_Triggers13_Brunner Don J21_Don.J.Brunner@IRS.GOV31_Wed, 17 Jan 2001 14:00:55 -0500625_iso-8859-1 We have developers asking the following about Triggers:

---------------------------------------------------------------------------- -------------------- We would like to initiate multiple jobs concurrently to access the trigger table and trigger multiple concurrent events. This will enable our batch program to process 30,000 to 40,000 records within the allocated processing window.

I have found a reference in an IBM that indicates Code reusability: "A trigger can be defined once on a table and used by any application that accesses the table". However, nothing was said about concurrency. [...] 9728 44 19_Re: DB2 UDB Restore16_Michael McCarthy18_mmccarthy@DTCC.COM31_Wed, 17 Jan 2001 14:00:42 -0500528_us-ascii Hi Jeff,

Yes you can. The restore will automatically migrate the database to the new version.

-Mike.









Jeff Kalman on 01/17/2001 01:18:36 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Michael McCarthy/DTC) Subject: Re: [DB2-L] DB2 UDB Restore







Hi all, Can you restore a DB2 UDB back up file from version 6 and restore it as new in DB2 UDB version 7? [...] 9773 15 19_Re: DB2 UDB Restore11_Jeff Kalman15_jkalman@CSC.COM31_Wed, 17 Jan 2001 14:15:43 -0500294_us-ascii Kurt, Thank you for your reply.

Jeff

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9789 30 8_A2 index12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Wed, 17 Jan 2001 21:31:10 +0200401_iso-8859-1 Hi Daniel,

I didn't see your comment before. So it looks like you'll have to add an index (unless it hurts the other insert/delete activity...) What about asking SAP for some changes in the code ???

>Daniel Sullivan > >It's not a clustering index. >I would like to try the hint or change the sql, but this is SAP and we >can't change the code, only poosibly add an idex. [...] 9820 95 20_Re: BOOLEAN datatype11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 17 Jan 2001 13:31:56 -0600468_- Carl,

On DB2 OS/390, while it is true that you can store boolean (char... as bit data), it realy will not behave the same way. It will be up to the application to process it as such. In fact you can do the same by storing it in a SMALLINT column or as a Y/N flag also. You could place a check constraint to ensure only "0" and "1" are possible but the application will need to make decisions based on the value. True boolean (True/False) does not exist. [...] 9916 14 20_Re: BOOLEAN datatype0_19_Tim.Lowe@STPAUL.COM31_Wed, 17 Jan 2001 13:38:57 -0600352_us-ascii Carl, Since you specified DB2 V6, could you use a user-defined data type and a user-defined 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. 9931 56 53_3rd DB2 Symposium in the Netherlands 14-16 March 200111_Klaas Brant14_KBrant@KBCE.NL31_Wed, 17 Jan 2001 21:31:43 +0100423_iso-8859-1 Dear DB2 professionals in Europe,

At the roots of DB2 Symposium: Veldhoven in The Netherlands we will have our 3rd DB2 Symposium in this Area. Dates: 14-16 March 2001.

Your teachers will be: Susan Lawson, Joyce Bischoff, Richard Yevich, Jan Henderyckx and Klaas Brant All teachers are IBM Gold Consultants. Besides teaching they are working on real projects using state of the art technology. [...] 9988 17 47_Current JCL to load the DB2PM Historical tables11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Wed, 17 Jan 2001 14:31:41 -0600472_us-ascii Does anyone have JCL to load the DB2PM performance tables talked about in chapter 39 of the DB2 Performance Monitor for OS/390 Report Reference Volume 1?

Our JCL is out of date for some of the fields loaded.... : (

================================================ 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. 10006 106 44_Re: Use of indicator variables in predicates13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Wed, 17 Jan 2001 14:39:14 -0600476_iso-8859-1 Phil,

Regardless of the syntax being considered valid for the precompiler, there is no concept of equality for NULL in relational theory.

I'm not aware of any change that would go against the SQL standards. And would therefore be very surprised if this worked.

You mentioned that "if the indicator variable is set then rows will be returned where col2 is null". Sorry to play on words, but you said, "will be"; were any actually returned? [...] 10113 78 33_Re: Excel and DB2 ODBC Connection18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM31_Wed, 17 Jan 2001 16:58:34 -0500 10192 122 12_Re: A2 Index14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Wed, 17 Jan 2001 16:00:58 -0600336_iso-8859-1 Daniel,

The following might work if you take CHECF out of the index seleciton process. Worth a try.

SELECT * FROM PAYR WHERE MANDT = ? AND ICHEC = ? AND ZBUKR = ? AND HBKID = ? AND HKTID = ? AND CHECT >= ? AND CHECF <= ? CONCAT '' -- if character or AND CHECF <= ? + 0 -- if numeric FOR FETCH ONLY WITH UR [...] 10315 78 16_Re: DB2 and CICS12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Wed, 17 Jan 2001 16:40:26 -0500368_US-ASCII have you thought about using Stored Procedures? With the EXCI option, you could have a SP call a CICS program from either subsystem to do a "local" call. This would also work across platforms. For an example of what I am talking about, see DB2 Magazine - Winter 99 (I think)- I co-authored an article on this topic with Phylis Donofrio and Mike Rettman. [...] 10394 110 33_Re: Excel and DB2 ODBC Connection13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Wed, 17 Jan 2001 16:11:02 -0600389_iso-8859-1 The id and password may be hardcoded in the CLI file for the database alias you're using. Look in C: (assuming DB2 is on your C drive), directory SQLLIB. The file name is DB2CLI.INI.

-----Original Message----- From: Anyanso Agwu-Okeke [mailto:pagwu@HOTMAIL.COM] Sent: Wednesday, January 17, 2001 4:59 PM To: DB2-L@RYCI.COM Subject: Re: Excel and DB2 ODBC Connection [...] 10505 95 28_DB2 Training on the Internet24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Wed, 17 Jan 2001 17:17:27 -0800321_us-ascii

Premier class: DB2 Server for VSE&VM System Administration - Basic (U4501) January 30 through February 1, 2001 from 9 a.m. to 5 p.m. US EST. The cost is US$990 (payable in advance). Soon to follow DB2 Universal Database on Linux, Unix, and Windows Administration, DB2 Java and XML coding, and more! [...] 10601 211 33_Re: regarding repartioning.......17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Wed, 17 Jan 2001 16:46:30 -0600605_- hi Michael,

Thanks for replying..... let me tell you how we are planning to do...

actually we have an indexes other than partion index. partion index is not an unique index but we do have untique index on the partion table.

what we are planning to do is... 1) DROP the indexes other than partion index.. then unload the data by part wise to different dataset. 2) sort the unloaded data .......suggested somebody because we drop the indexes other than partioned index. 3) recreate the tablespace with more partion, table and partionindex.... 4) load the sorted data back to the [...] 10813 208 12_Re: Triggers14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Wed, 17 Jan 2001 16:52:00 -0600440_iso-8859-1 TriggersTriggers are a continuation of a thread. So if you have several jobs running, and each is doing DML on the same table, then each will invoke the trigger in its execution path.

However, I am not sure that this is the question they asked. It is almost as if they want to issue events that cause a single trigger to then issue multiple events - spawn off multiple concurrent events. That is not going to happen. [...] 11022 236 33_Re: regarding repartioning.......15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 17 Jan 2001 16:59:54 -0600505_us-ascii Then I believe you'll spend whatever you saved time from unload/load for populating indexes when running REBUILD Index . Am I wrong?

Regards Nayeem ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 01/17/2001 04:54 PM ---------------------------



ravi kumar hassan on 01/17/2001 04:46:30 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US) [...] 11259 133 33_Re: Excel and DB2 ODBC Connection19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Wed, 17 Jan 2001 16:16:37 -0800462_iso-8859-1 The real question is where is this information coming from now, and then what do you have to do to make it a pop-up?

If it's in the spreadsheet VB macro, you can change the code to add a popup box. My total training on VB macros is from clicking the Help option, so that's where I'd recommend looking for info. If it's in DB2 Connect in text-readable form in DB2CLI.INI, then something needs to be done to protect the INI file. Elsewhere? [...] 11393 34 33_Stored procedures and auto-rebind8_LAM, Leo17_l.lam@IOOF.COM.AU31_Thu, 18 Jan 2001 12:26:12 +1000673_ISO-8859-1 It seems that if a stored procedure is dropped, all the dependant packages are marked as inoperative (instead of invalid). This stops the auto-rebind when the package is invoked next time.

Does anybody know the reasons of stopping auto rebinds for packages depending on stored procedures ?

Thanks

Leo



****************** IMPORTANT - PLEASE READ **************************************** Please confirm receipt of this email. This email is confidential - if you receive this email by mistake you must not use it or the information in any way. If there is a problem with this email or you have received it by mistake, please [...] 11428 83 20_Re: BOOLEAN datatype14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 18 Jan 2001 16:55:07 +1100576_iso-8859-1 Prior to V6, you could manually update the SYSIBM.SYSCOLUMNS.FOREIGNKEY (sic!) for the (character) column to be bit.

All this actually does is disable CCSID translation. The application has to use character host variables to manipulate the data.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Robert Jans [mailto:robert_jans@ALBERTSONS.COM] Sent: Thursday, January 18, 2001 4:59 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] BOOLEAN datatype [...] 11512 70 45_Re: DB2 Visual Explain - authorisation needed14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 18 Jan 2001 16:56:08 +1100572_iso-8859-1 (haven't used VE because I can read my own plan_table)

I know of installations that have written an "explain" program. This program reads in the user's sql and runs explain (into its own plan_table). With DYNAMICRULES(BIND) the end-user does not need to have any authority on the tables - it's all granted to the owner of the "explain" program. A bit of logic to enable the program to copy plan_table rows to a user's own plan_table (ie the user would have to grant the "explain" program's owner update on his/her own plan_table) and there you are. [...] 11583 26 19_distributed threads12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Thu, 18 Jan 2001 06:00:36 -0000497_- Hi All, In our environment (DB2 V5R1) we are facing a typical problem where we have controlled access of running QMF queries thru QMF Governor but that doesn't stop the users from accessing DB2 resources in prime time .They use DB2 for UDB and access DB2 data which we do not want. Can anyone tell me how to restrict those users accessing DB2 thru UDB.In our environment the DSNZPARM values we have are MAXDBAT=25,and CONDBAT=25,IDTHTOIN=180. ANy help will be appreciated.Thanx in Advance. [...] 11610 21 19_Distributed Threads12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Thu, 18 Jan 2001 06:31:10 -0000406_- Hi ALL, Can I also keep three or four userids explicitly for Distributed Threads access so that whenever they access, it should not be hampered by the limits of Remote Distributed Threads Max. Any help will be appreciated.

Regards Rakesh Kumar _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. [...] 11632 74 23_Re: distributed threads16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 18 Jan 2001 12:01:11 +0530430_- Hi Rakesh,

In your System Table for the governor(authid.DSNRLSTxx) specify PUBLIC in LUNAME column. This way you can limit the access for any remotely generated requests. The value PUBLIC represents all of the DBMS locations in the network,these locations do not need to be DB2 subsystems.PUBLIC is the only value for TCP/IP connections.Normally it is blank which means the governor is valid for only LOCAL threads. [...] 11707 79 45_Re: DB2 Visual Explain - authorisation needed16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 18 Jan 2001 12:16:10 +0530612_- This seems to be a major problem using the Visual Explain in production. At the same time i do not think rebinding everytime in production for the sake of looking at the explain(required for performance tuning) should be a genuine concept because if developers are going to use that then it may be a big overhead on the production subsystems. Best idea for getting the explain in test closer to the production enviornment is populating the catalog tables in test as it is in production. For exceptional cases DBAs/Team DBAs should be involved for Explain and performance tuning. Bind/Rebind authorization [...] 11787 92 33_Re: regarding repartioning.......16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 18 Jan 2001 12:27:51 +0530309_- Ravi, Indexes on the table doesn't degrade performance for UNLOAD which is simply select on the table. We can have number of indexes on the table but it will not degrade the select performance considering the fact that better index is used for accessing the data for SELECT(Non Generalized Statement). [...] 11880 113 33_Re: regarding repartioning.......16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 18 Jan 2001 12:35:08 +0530613_- I realized the fact that your existing index is partitioning/clusting index and hence sorting should be done or not is directly related to the present cluster ratio of the index.

Regards, Sanjeev

> -----Original Message----- > From: S, Sanjeev (CTS) > Sent: Thursday, January 18, 2001 12:26 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: regarding repartioning....... > > Ravi, > Indexes on the table doesn't degrade performance for UNLOAD which is > simply select on the table. We can have number of indexes on the table but > it will not degrade the select performance considering [...] 11994 123 44_Re: Use of indicator variables in predicates11_Phil Button21_Phil.Button@BMW.CO.UK31_Thu, 18 Jan 2001 08:58:36 -0000355_iso-8859-1 Terry

This syntax works does work - if the indicator is set the rows with NULL in the column are returned. And it uses the index if the column is indexed.

Interestingly, the Oracle manuals (8.1.6) explicitly state that you shouldn't use indicator variables in this way - I wonder if, in past release, Oracle did allow it...? [...] 12118 151 29_Re: regarding repartioning...0_18_mebert@AMADEUS.NET31_Thu, 18 Jan 2001 10:45:20 +0100431_us-ascii Hello Kumar,

your procedure is what I would recommend also. After creating the indexes with DEFER YES, you have to REBUILD them (can be done in parallel, one NPI per job). Don't run REBUILD INDEX(ALL) as this would also unnecessarily rebuild the PI. That way you'll save a lot of time as the BUILD phase of the NPIs (called BUILD2 with Online Reorg) is much slower and less efficient than the REBUILD utility. [...] 12270 38 32_Re: Security under RACF question10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 18 Jan 2001 04:55:57 -0600580_- Please don't think I was trying to contradict you, which would be a pretty stupid thing to do. I should have ended my last sentence with a question mark rather than a full stop.



On Wed, 17 Jan 2001 10:22:12 -0600, Roger Miller wrote:

>It appears to me to be working just as designed. If RACF can identify the >object, it can allow access or deny access. If it does not have a profile >for the object, then DB2 access controls are used. I'm guessing that you >thought that DB2 access controls would always be used, but that's not [...] 12309 222 58_SIRDUG (Charlotte, NC) Seminar with Sheryl Larsen, Feb. 2311_Paul Turpin22_pault704@EARTHLINK.NET31_Thu, 18 Jan 2001 05:31:30 -0600767_- --------------------------------------------------------------------- SIRDUG Southeastern Inter-Relational Database Users' Group P.O. BOX 34641 Charlotte, NC 28234

Website: http://users.vnet.net/sirdug Email: SIRDUG@VNET.NET Information/Message Line: (704) 556-6621



Educational Seminar Announcement =================================================================== WHEN? Friday February 23, 2001

WHERE? Royal & SunAlliance =======IMPORTANT============ 9300 Arrowpoint Blvd = Notice below that = Charlotte NC 28273-8135 = there is a change in the = (704) 522-2000 = directions for events = = at Royal & SunAlliance = ============================ COST? $195 cash or check, in advance or at the door. We do not accept credit cards. [...] 12532 22 29_regarding distributed threads12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Thu, 18 Jan 2001 11:55:00 -0000600_- Hi Sanjeev, It is still not working and I would like to know what should be the value for RLFFUNC in the DSNRLST table.

ANy help will be appreciated.Thanx in Advance.

Regards Rakesh Kumar

_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 12555 79 33_Re: regarding distributed threads16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 18 Jan 2001 17:38:57 +0530658_- I hope you do have the Admin Guide with you. If not, i am sending the realted documentation to you because you have asked for each and every function probably. =================== RLFFUNC Specifies how the row is used: --Blank means that the row reactively governs dynamic SELECT,INSERT,UPDATE,or DELETE statements by plan name. --'1 'means that the row governs bind operations. --'2 'means that the row reactively governs dynamic SELECT,INSERT,UPDATE,or DELETE statements by package or collection name. --'3 'means that the row disables query I/O parallelism. --'4 'means that the row disables query CP parallelism. --'5 'means that the row disables [...] 12635 89 33_Re: Excel and DB2 ODBC Connection27_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM31_Thu, 18 Jan 2001 07:07:52 -0500608_- Tim,

Could you share this macro with the rest of the list? I know I would like a copy of it, anyway.

Tina Hilton Database Analyst Bertelsmann mediaSystems Tina.Hilton@bmsus.com



> -----Original Message----- > From: Ohling, Tim R - CNF [SMTP:Ohling.Tim@CNF.COM] > Sent: January 17, 2001 10:22 AM > To: DB2-L@RYCI.COM > Subject: Re: Excel and DB2 ODBC Connection > > How are the User ID and password being given to the spreadsheet? > > If the macro process is fully automated, they have to be "hardcoded" there > somewhere. > > We do something similar, but have the VB macro [...] 12725 129 33_Re: Excel and DB2 ODBC Connection0_20_vpacheco@AMADEUS.NET31_Thu, 18 Jan 2001 13:40:44 +0100353_us-ascii Hello All

Thanks to the replies to my doubt.

I found the cause of the problem in the mean time - when returning the data from MS Query to MS Excel there is an option under properties ('External Data Range Properties') that, by default, saves the password in Excel. Once this option is deactivated there are no more problems. [...] 12855 24 22_Db2 system recovery !!13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Thu, 18 Jan 2001 18:36:26 +0530365_us-ascii hi !! At our shop there is a dB2 instance , whose hard disk crashed. The OS is windows NT. We take a folder backup of the disk every week . The harddisk was recovered but the DB2 does not start , we are forced to re-install DB2 . Now the folders in which the DB2 Data were there are intact . Is there any method to recatalog the databases manually ?? [...] 12880 123 33_Re: Excel and DB2 ODBC Connection11_rick creech18_ykcirc@HOTMAIL.COM31_Thu, 18 Jan 2001 07:07:10 -0600659_- Hi,

I have accomplished the same thing by editing the query and taking out the useris and password. This will also cause the dialog box to pop up.

Regards,

Rick Creech



>From: "Hilton, Tina, BmS - NMI -PM" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Excel and DB2 ODBC Connection >Date: Thu, 18 Jan 2001 07:07:52 -0500 > >Tim, > >Could you share this macro with the rest of the list? I know I would like >a >copy of it, anyway. > >Tina Hilton >Database Analyst >Bertelsmann mediaSystems >Tina.Hilton@bmsus.com > > > > -----Original Message----- [...] 13004 25 23_Re: distributed threads11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Thu, 18 Jan 2001 08:20:00 -0500227_us-ascii If you have a Thread Terminator or a similar tool from another vendor you can control the distributed threads and cancel them a particular time of the day or set a query time out. So after some many seconds of CPU time 13030 75 37_Re: Stored procedures and auto-rebind12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 18 Jan 2001 05:22:05 -0800320_us-ascii My interpretation of what IBM told me about this at the IBM DB2 Tech Conference is that IBM marked the packages as inoperative because the thinking was that one was making changes to the packages and dropping the SP, then the packages that referenced that dropped SP would not work. Therefore inoperative. [...] 13106 123 45_Re: DB2 Visual Explain - authorisation needed22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Thu, 18 Jan 2001 11:23:52 -0200473_iso-8859-1 Currently we do not copy catalog statistics from production to test. Usually analysts/programmers produce their code and before running it in production the DBA team explains all SQL statements. Then, developpers receive an authorisation to put the program effectively in production. For avoiding that overcharge to DBAs, the idea is providing a tool for developpers to analyse their own SQL statements. If they cannot tune it properly, they call the DBAs. [...] 13230 14 23_Re: distributed threads9_Colin Fay13_cfay2@CSC.COM31_Thu, 18 Jan 2001 08:41:54 -0500278_us-ascii It's been done

Colin

================================================ 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. 13245 32 27_migrating stored procedures10_John Lantz23_john.lantz@ZURICHUS.COM31_Thu, 18 Jan 2001 08:06:44 -0600564_- A quick question about migrating stored procedures in a UDB environment. We've used the stored procedure builder to create a stored procedure using the SQL Procedure Language. Now we want to migrate the code to production. Using the stored procedure builder from a test server, I'm able to copy the source code. But when I try to "paste and build" it on the production box, I'm getting errors. It says the NMAKE function is not found. The bottom line it seems that Visual C++ needs to be installed on the server that you want to build the procedure. We did [...] 13278 35 60_Re: documents to find a beginning for system programming....14_Michael Vanner19_mvanner@CANTIRE.COM31_Thu, 18 Jan 2001 09:38:07 -0500332_us-ascii Documents and books are a great start, but, here is what I would suggest:

If you are currently an application programmer and have never worked in computer operations, try to get some operations experience or talk to the operators. Learn the console commands, clists, rexx, and get an appreciation for hardware. [...] 13314 44 25_DB2 Connect V7.1 question13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Thu, 18 Jan 2001 09:05:32 -0600439_US-ASCII Hello, We are installing DB2 V6 OS390 and we are wanting to go ahead and upgrade DB2 Connect V5.1 to V7.1.. We have been told from our supplier of DB2 Connect that we should not install v7.1 without fixpack 2 especially if we are running Peoplesoft...which we are.

Can any of you tell me if you are running DB2 CONNECT v7.1 to DB2 V5 and V6 for OS390? And if you are running Peoplesoft if you have seen any problems?? [...] 13359 80 29_Re: DB2 Connect V7.1 question11_Steve Klotz19_Steve_Klotz@AUL.COM31_Thu, 18 Jan 2001 10:26:09 -0500407_us-ascii Mike, We are a new user of DB2 Connect V7.1 that communicates to DB2 V5 on OS390. We have not installed the fixpack as of yet, but we have not experienced any problems without it either. I believe the fixpack addresses functions that we are not using as part of our daily processes, (i.e. Microsoft Excel OBDC), so maybe by that alone we have be insulated from any fixpack addressed problems. [...] 13440 116 26_Re: Db2 system recovery !!14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Thu, 18 Jan 2001 07:36:58 -0800361_iso-8859-1 Vishy, You should be able to issue the CATALOG DATABASE olddb ON path

Where the "path" indicates the drive letter where the database resides. I have done this in the past on test systems when I had to do a re-install of DB2. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 13557 38 19_Re: DB2 UDB Restore10_John Lantz23_john.lantz@ZURICHUS.COM31_Thu, 18 Jan 2001 09:31:01 -0600626_- Also, I believe there is a requirement that the backup needs to be an offline backup. When we went from V5.2 to V7, it needed to be offline. So I'm assuming the same restriction applies from V6 as well.

On Wed, 17 Jan 2001 14:00:42 -0500, Michael McCarthy wrote:

>Hi Jeff, > > Yes you can. The restore will automatically migrate the database to the >new version. > > -Mike. >Jeff Kalman on 01/17/2001 01:18:36 PM > >Please respond to DB2 Data Base Discussion List > >To: DB2-L@RYCI.COM >cc: (bcc: Michael McCarthy/DTC) >Subject: Re: [DB2-L] DB2 UDB [...] 13596 55 31_Re: migrating stored procedures14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 18 Jan 2001 10:39:03 -0500696_iso-8859-1 John:

We do not use Stored Procedure Builder but we do use stored procedures written in C. And like yours our production machine does not have development tools. We copy the load modules to the sqllib/function directory of our production machine and bind the packages.

Thanks, Manas.



"John Lantz" wrote in message news:DB2-L%2001011808064484@RYCI.COM... > A quick question about migrating stored procedures in a UDB environment. > We've used the stored procedure builder to create a stored procedure using > the SQL Procedure Language. Now we want to migrate the code to > production. Using the stored procedure builder from [...] 13652 57 29_Re: DB2 Connect V7.1 question12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Thu, 18 Jan 2001 09:46:09 -0600382_iso-8859-1 I tried to pull down fixpak2 for DB2 Connect for NT and didn't have the authority to do a CD to the directory (note below). drwx------ 2 4975300 201 512 Jan 17 21:07 FP2_U474019

-----Original Message----- From: Mike Lawrence [mailto:mlawrence@ASCENSIONHEALTH.ORG] Sent: Thursday, January 18, 2001 9:06 AM To: DB2-L@RYCI.COM Subject: DB2 Connect V7.1 question [...] 13710 14 19_Re: DB2 UDB Restore11_Jeff Kalman15_jkalman@CSC.COM31_Thu, 18 Jan 2001 10:47:01 -0500288_us-ascii Thank you for your reply.

Jeff

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 13725 171 45_Re: DB2 Visual Explain - authorisation needed11_David Nance16_DWNance@FHSC.COM31_Thu, 18 Jan 2001 10:46:14 -0500571_US-ASCII Claudio, About your drawbacks. Yes if the catalog is changed in future releases of DB2, you may have to change the process you set up to copy the stats. But, that possibility exists for many things we do. As far as causing poor performance for your test system, I don't understand that. All test systems are to test a product prior to moving to production and you want to ensure that you do not degrade your performance in production. Using the real catalog statistics for your test subsystem the optimizer may choose an access path that works well for you [...] 13897 22 44_Re: Use of indicator variables in predicates12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 18 Jan 2001 10:06:45 -0600514_- What we do in DB2 books is to say what can be done and try to avoid the nots. It is impossible to list all of the "Don't put beans in your ears."

There is a section telling about using indicators for retrieving data and a little about using them for insert and update. There is nothing about using an indicator with a meaning in a predicate. I don't have the time to look it up, but I think you'll get an error if the indicator is negative. If it doesn't work that way now, then it will probably get [...] 13920 12 32_Re: Security under RACF question12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 18 Jan 2001 10:09:53 -0600340_- Go back about three appends in this thread, Tue, 16 Jan 2001 11:15:36 -0600.

Roger Miller

================================================ 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. 13933 190 29_FW: DB2 Connect V7.1 question14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Thu, 18 Jan 2001 08:37:14 -0800629_iso-8859-1



-----Original Message----- From: Philip Gunning To: 'Mike Lawrence ' Sent: 1/18/01 8:36 AM Subject: RE: DB2 Connect V7.1 question

Mike, As far as DB2 Connect V7.1 with Peoplesoft, you should check Peoplesoft customer connection for doc on it. They usually certify levels of DB2 and DB2 Connect for use with their products. If you go to a release they are not certified on that might cause you support problems. Peoplesoft in general is somewhat sensitive to some DB2 Connect settings and their doc usually adresses these to some extent. Of course, with DB2 Connect 7.1 and DB2 V6.1 you can [...] 14124 104 29_Re: DB2 Connect V7.1 question12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 18 Jan 2001 08:44:18 -0800388_us-ascii My client is running DB2 V6 OS/390 and DB2 Connect 5.2, V6 fixpack 4 and DB2 Connect V7.1 fixpack 1f.

I have migrated several applications to DB2 Connect V7.1 fixpack 1f. Fixpack 2 is not officially available yet (theoretically latest word is this friday, but who knows). I have downloaded the interim Fixpack 2 and in the process of doing preliminary testing on it. [...] 14229 22 48_DB2 keeps eating Paging Space till it freezes up8_lnsiebel20_lnsiebel@HOTMAIL.COM31_Thu, 18 Jan 2001 09:44:18 -0700488_iso-8859-1 > Subject: Paging Space leak > I am experiencing a problem with a potential memory leak within DB2. > > The AIX server with 1Gb RAM and 1.5GB Paging Spc runs out of paging spc = > so bad that even interactive logins are not possible on the unix box. = > Recycling DB2 clears it up and lsps -a shows the used Paging Spc drop = > from 99% to 3% !! > > Any clues on this ? The only application running here is Siebel. Both = > IBM and Siebel are deflectively mum about this. [...] 14252 36 36_Re: DB2 : internal or RACF security?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 18 Jan 2001 11:13:15 -0600619_- There are no real answers to the questions, but here are some thoughts.

What is the global tendency in what concerns DB2 security? Customer security practices are very diverse. The key questions are the customer needs, security policies and practices. Does IBM encourage migration to RACF security? I can tell you what this IBM employee thinks, but not much more. My opinion may not reflect that of the organization in general. Moving to RACF for access control is a choice that many customers asked for. There are some distinct advantages and some disadvantages. The migration is not a compatible change [...] 14289 23 15_Data Propagator13_Moss, William15_MossW@AETNA.COM31_Thu, 18 Jan 2001 12:50:12 -0500503_iso-8859-1 I'm a new user of Data Propagator with DB2 OS/390. I have ten tables to propagate. Does anyone have any opinions about creating ten subscription sets each with only a single member versus one subscription set with ten members ? Having only one member per subscription set would allow me to do a full refresh of a single table instead of having to refresh all ten tables at once. I don't know enough about Dprop to know if there is a downside to the one subscription set/one member idea. [...] 14313 20 21_copying Runstats info15_Kuashal Trivedi17_c_triveka@BAM.COM31_Thu, 18 Jan 2001 12:51:04 -0500284_iso-8859-1 Hi All, I remember of seeing a process ( a set of queries or a rexx) to copy the runstat info from one environment to another, on this forum, if anyone has a copy and would like to pass me I would appreciate that, could you please email it to me at c_triveka@bam.com. [...] 14334 95 25_Re: copying Runstats info15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 18 Jan 2001 12:59:03 -0500442_iso-8859-1 I some SQL which generate update statement and that be executed in other subsystem.



SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVE=' CONCAT DIGITS(NACTIVE) CONCAT ' WHERE NAME='''CONCAT TS.NAME CONCAT ''' AND CREATOR ='''CONCAT TS.CREATOR CONCAT''';' FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL WHERE TS.NAME = TSNAME AND TBL.NAME = 'CSTDBD' AND TBL.CREATOR = 'DB2PROD' AND NACTIVE >=0 WITH UR [...] 14430 54 19_Re: Data Propagator11_rick creech18_ykcirc@HOTMAIL.COM31_Thu, 18 Jan 2001 12:04:55 -0600569_- Hi,

We have data propagator on os/390. You have identified the biggest advantage to 1 table per sub-set as we see it. One disadvantage would be additional maintenance for the extra sub-sets. We have a couple of hundred tables that are propagated, but all are done with multiple tables per subset. Sometimes the question does come up here as you posed it, but I guess because we have started in this direction, it is where we will stay. Also, because we have always operated with multiple tables per sub-set, we do not really have experience with 1 for 1. [...] 14485 24 18_RDS Limit Exceeded11_Randy Scott19_Randy_Scott@VFC.COM31_Thu, 18 Jan 2001 13:41:42 -0500422_us-ascii DB2PM is reporting that our DB2 system is exceeding the 'RDS Limit' about 1,200 times each day. According to the DB2PM help, this count is the number of times RID list processing terminated because the number of RIDs that can fit into the guaranteed number of RID blocks was greater than the maximum limit (25% of table size). This looks like a RID pool size problem to me (controlled by MAXRBLK parameter). [...] 14510 70 22_Re: RDS Limit Exceeded12_Susan Lawson22_lawson_susan@YAHOO.COM31_Thu, 18 Jan 2001 10:57:08 -0800482_us-ascii Randy,

This is not a RID pool size problem. You are simply qualifying too many RIDS. DB2 has determined that instead of using List Prefetch for the query that a table space scan is more efficient.

If you are V5 be sure that you have the APAR on that enables index screeening (qualifies less rids). The number escapes me at this point I think it was PQ19045 ?. If you are V6, you need to possibly look at your query and your indexes for better filtering. [...] 14581 16 25_Re: copying Runstats info0_19_Tim.Lowe@STPAUL.COM31_Thu, 18 Jan 2001 13:03:02 -0600323_us-ascii Sibi, What do you do with SYSTABSTATS and SYSCOLDIST?

Thanks, Tim

================================================ 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. 14598 139 22_Re: RDS Limit Exceeded22_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM31_Thu, 18 Jan 2001 14:00:12 -0500347_iso-8859-1 When was the last time RUNSTATS was run? I believe DB2 bases the max limit (25%) on the statistics it keeps in the catalog. If your table has grown but the catalog statistics haven't been updated through a RUNSTATS then adding more memory to the RID pool probably won't help. Try running the RUNSTATS and compare before and after. [...] 14738 37 25_Re: copying Runstats info15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 18 Jan 2001 14:17:10 -0500455_- Tim,

I have not copied these tables to test the access path in development environment. May be we can generate some insert statement for these two tables too.



Thanks..Sibi

-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Thursday, January 18, 2001 01:03 PM To: DB2-L@RYCI.COM Subject: Re: copying Runstats info



Sibi, What do you do with SYSTABSTATS and SYSCOLDIST? [...] 14776 52 19_Re: Data Propagator21_Claypool, Alisa@HHSDC21_AClaypoo@HHSDC.CA.GOV31_Thu, 18 Jan 2001 11:06:14 -0800566_iso-8859-1 When determining whether to use one or multiple table(s) per sub-set, you should consider the unenforced referential integrity of your target tables. If you want to keep a group of tables 'in synch' with each other, then you should group them in a sub-set. The downside is that the row entered in the APPLYTRAIL table lists the results of the apply by the sub-set. So if you wanted to know how many rows were processed during an apply cycle for each target table, you would need to have one table per sub-set. The downside, you have more rows in the [...] 14829 39 52_Re: DB2 keeps eating Paging Space till it freezes up11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Thu, 18 Jan 2001 19:32:12 +0000332_us-ascii There are a fw different parameters / registry variables that can alter how much paging is happening. The two registry variable are DB2MEMDISCLAIM and DB2MEMMAXFREE. The first tells DB2 to free up the agents memory when it becomes idle. The second sets the amount to kepp - the IBM recommendation is 8Mb if it is set. [...] 14869 138 45_Re: DB2 Visual Explain - authorisation needed13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 18 Jan 2001 13:23:57 -0600566_iso-8859-1 I never have liked the security requirements on running an explain (that you need the authority to execute a statement in order to explain it). I also don't really like the approach of having a "hand-off" where the developers give the SQL to the DBA's just prior to moving to production, and the DBA's need to authorize the transfer. I've always felt that running explains is a function that needs to be in the developers hands, not the DBA's. In reality, both need to be able to run/interpret explains, but by the time the DBA gets ahold of the SQL [...] 15008 12 22_DB2 maintenance & CICS16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Thu, 18 Jan 2001 11:29:00 -0800419_- When I recycle DB2 for OS390(V6) after PTF maintenance, do I also have to recycle the connected CICS regions? Or only if specific modules are affected by the PTFs? CICS is V4.

================================================ 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. 15021 14 26_Re: DB2 maintenance & CICS0_26_truman.g.brown@VERIZON.COM31_Thu, 18 Jan 2001 14:40:24 -0500333_us-ascii You don't have to recycle CICS, but you will have to reconnect it...

George

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 15036 132 29_Re: DB2 Connect V7.1 question13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Thu, 18 Jan 2001 13:53:58 -0600450_US-ASCII Thanks to all for your responses... that was what I was looking for. Have a Great day!



Michael S. Lawrence Systems Programmer MLawrence@Ascensionhealth.org Ascension Health Information Services Division Evansville Indiana USA





>>> myronwmiller@YAHOO.COM 10:44:18 AM Thursday, January 18, 2001 >>> My client is running DB2 V6 OS/390 and DB2 Connect 5.2, V6 fixpack 4 and DB2 Connect V7.1 fixpack 1f. [...] 15169 30 69_Question about replication from DB2 OS/390 V5.1 to DB2 UDB Unix V 7.113_Lynne Flatley17_LFlatley@NEFN.COM31_Thu, 18 Jan 2001 15:45:50 -0500444_us-ascii Are many of you replicating from DB2 to UDB? We're thinking of replicating from V5.1 on the mainframe to V7.1 on Unix. Can we do that or is there a version compatibility issue?

This is for a Siebel application which is currently replicating from DB2 to MS SQL Server and the SQL Server data will be converted over to UDB. The daily volume of data being replicated I believe is in the neighborhood of about 300MB - 500MB... [...] 15200 69 31_Re: migrating stored procedures10_John Lantz23_john.lantz@ZURICHUS.COM31_Thu, 18 Jan 2001 14:48:52 -0600443_- I'm assuming my BIND statement would look something like this: BIND P4332590.BND COLLECTION COS QUALIFIER USMJL58 OWNER USMJL58 DATETIME DEF DEGREE 1 BLOCKING UNAMBIG SQLERROR NOPACKAGE SQLWARN YES DYNAMICRULES RUN EXPLSNAP NO EXPLAIN NO INSERT DEF QUERYOPT 5 ISOLATION CS FUNCPATH SYSIBM,SYSFUN,USMJL58;

But I can't find the *.bnd file anywhere. Is there an option when originally building the procedure to save the *.bnd file? [...] 15270 158 33_Re: Excel and DB2 ODBC Connection19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Thu, 18 Jan 2001 12:56:28 -0800345_iso-8859-1 Well - I like the option of doing it outside Excel a little better, but what we've got is this:

A popup form called UserID with fields for User ID and Password (defined as text and "password" types respectively), with an "OK" button.

In the associated UserID macro: Private Sub OKButton_Click() UserID.Hide End Sub [...] 15429 51 73_Re: Question about replication from DB2 OS/390 V5.1 to DB2 UDB Unix V 7.111_rick creech18_ykcirc@HOTMAIL.COM31_Thu, 18 Jan 2001 14:51:30 -0600626_- We are replicating from os/390 version 5.1 to UDB on NT.

hth,

Rick Creech



>From: Lynne Flatley >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Question about replication from DB2 OS/390 V5.1 to DB2 UDB Unix V > 7.1 >Date: Thu, 18 Jan 2001 15:45:50 -0500 > >Are many of you replicating from DB2 to UDB? We're thinking of replicating >from V5.1 on the mainframe to V7.1 on Unix. Can we do that or is there a >version compatibility issue? > >This is for a Siebel application which is currently replicating from DB2 to >MS SQL Server [...] 15481 89 31_Re: migrating stored procedures13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 18 Jan 2001 15:02:45 -0600448_US-ASCII John, The bind file is created when you "prep" you source. Regards, Kurt

>>> john.lantz@ZURICHUS.COM 01/18/01 02:48PM >>> I'm assuming my BIND statement would look something like this: BIND P4332590.BND COLLECTION COS QUALIFIER USMJL58 OWNER USMJL58 DATETIME DEF DEGREE 1 BLOCKING UNAMBIG SQLERROR NOPACKAGE SQLWARN YES DYNAMICRULES RUN EXPLSNAP NO EXPLAIN NO INSERT DEF QUERYOPT 5 ISOLATION CS FUNCPATH SYSIBM,SYSFUN,USMJL58; [...] 15571 67 74_Re: Question about replication from DB2 OS/390 V5.1 to DB2 UDB Un ix V 7.115_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Thu, 18 Jan 2001 15:59:31 -0500384_iso-8859-1 We are replicating from os/390 v 5.1 to UDB AIX. Rick McClendon - F.S.U.

-----Original Message----- From: rick creech [mailto:ykcirc@HOTMAIL.COM] Sent: Thursday, January 18, 2001 3:52 PM To: DB2-L@RYCI.COM Subject: Re: Question about replication from DB2 OS/390 V5.1 to DB2 UDB Unix V 7.1



We are replicating from os/390 version 5.1 to UDB on NT. [...] 15639 45 44_Re: Excel and DB2 ODBC Connection - MS QUERY18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM31_Thu, 18 Jan 2001 16:40:43 -0500 15685 46 52_Re: DB2 keeps eating Paging Space till it freezes up12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 18 Jan 2001 13:52:32 -0800551_us-ascii Are the combined sizes of all your bufferpools reasonable (1 gb or so on a machine your size)?

You might be able to find the miscreant by forcing off the application threads one a time until the problem clears, if buffers aren't the problem.

--- lnsiebel wrote: > > Subject: Paging Space leak > > I am experiencing a problem with a potential > memory leak within DB2. > > > > The AIX server with 1Gb RAM and 1.5GB Paging Spc > runs out of paging spc = > > so bad that even interactive logins are not [...] 15732 14 38_missing colon preceeding host variable16_Robert Blackwell31_robert.blackwell@PIEDMONTNG.COM31_Thu, 18 Jan 2001 16:47:49 -0500472_us-ascii When converting to DB2 OS/390 V6, will COBOL programs that are running under V5, but are missing a colon preceding a host variable, stop working when V6 is brought up or will the source code not compile and BIND under V6?

================================================ 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. 15747 73 44_Re: Excel and DB2 ODBC Connection - MS QUERY13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 18 Jan 2001 16:05:26 -0600625_US-ASCII You could create a view on OS/390 and select from that. Just an idea. Kurt



>>> pagwu@HOTMAIL.COM 01/18/01 03:40PM >>>

Hi all, If I may extend this thread just a tad longer, I will like to know this: I have long database column_names that I want to shorten in other to fit as many report items on one page as I can. I can shorten column_name on the OS/390 host via SPUFI using "Select Long_column_name AS Short_column_name" but cannot do the same in MS Query specification (e.g.SELECT TT01_NY001_STATS.DBNAMEAS DBNAME, TT01_NY001_STATS.TSNAME AS TSNAME.... When I tried this in MS Query, I [...] 15821 100 27_DSNX986I Message not in M&C13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Thu, 18 Jan 2001 17:05:40 -0500337_US-ASCII List,

We are DB2 V6 for OS/390. We have enabled WLM-managed stored procedures, and (after much travail) finally gotten the WLM stored procedures address space to come up. While our invocations of our test stored procedure seem to be successful, we're getting the following message in the JES output of the WLM Proc: [...] 15922 110 37_Re: Stored procedures and auto-rebind8_LAM, Leo17_l.lam@IOOF.COM.AU31_Fri, 19 Jan 2001 09:06:32 +1000406_ISO-8859-1 Myron, Thanks for the info. I found APAR PQ43444 but it's more related to SPB. I couldn't find the APAR you mentioned about invalidating packages when a stored procedure is dropped.

Regards Leo

-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Thursday, 18 January 2001 23:22 To: DB2-L@RYCI.COM Subject: Re: Stored procedures and auto-rebind [...] 16033 139 31_Re: DSNX986I Message not in M&C0_19_Tim.Lowe@STPAUL.COM31_Thu, 18 Jan 2001 16:22:24 -0600357_iso-8859-1 See apar II11895:

DSNX986I ATTEMPT TO func FAILED WITH RV = rv RC = rc RSN = rsn SSN = ssn PROC= jclproc

EXPLANATION: A WLM-established stored procedures address space was unable to register itself to OS/390 UNIX System Services. OS/390 UNIX System Services are not available to stored procedures running in the address space. [...] 16173 12 31_Re: DSNX986I Message not in M&C0_19_Tim.Lowe@STPAUL.COM31_Thu, 18 Jan 2001 16:27:20 -0600291_us-ascii You might also want to see Apar PQ42329.

================================================ 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. 16186 31 42_Re: missing colon preceeding host variable13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 18 Jan 2001 16:28:41 -0600336_us-ascii Verifying that all host variables are preceded with a colon in Version 6 is performed at precompile time. If you have applications with host variables missing colons you should be receiving warnings from the precompiler in Version 5. Those warnings will be errors in Version 6 and the precompiler step will not complete.. [...] 16218 67 36_DB2 v6.1 migration and CICS /TS v1.322_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM31_Thu, 18 Jan 2001 17:27:22 -0500338_iso-8859-1 Is there any special processing to be done on the CICS end if we migrate DB2 from v5.1 to v6.1 and if the CICS regions are at CICS TS v1.3? Library names will stay the same. We don't own the RCT anymore. Answers are appreciated.



Tom Kennedy - Cendant Database Tech Services (516) 222-6397 tom.kennedy@cendant.com 16286 64 42_Re: missing colon preceeding host variable0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 18 Jan 2001 16:40:31 -0600611_us-ascii Should run until you do a recompile, you will get an error in precompile step, or do a rebind.

Extract from "DB2 UDB Server for OS/390 Version 6 Technical Update", Redbook SG24-6108-00

2.1.2 Host variables must be preceded by a colon ":" DB2 Version 6 enforces the standard that requires all host variables preceeded by a colon ":". All host variable references must have the leading colon. If the colon is missing the precompiler will issue a DSNH104I message or interpret the variable as a column name. Converting to the standard requires a REBIND. If the DBRM was produced [...] 16351 73 31_Re: migrating stored procedures14_Michael Finnis28_MFinnis@PRINCETONSOFTECH.COM31_Thu, 18 Jan 2001 17:59:36 -0500579_iso-8859-1 John,

I am a Product Specialist for Princeton Softech and while this will not give you an immediate result, Move for Servers from Princeton Softech can migrate Stored Procedures, among many other features.

Take a look at: http://www.princetonsoftech.com/moveforservers

Cheers, Mike. ___________________________

Michael Finnis Product Specialist Princeton Softech Australia Dir tel: +61 8 8355 1269 Fax: +61 2 9369 4244 mfinnis@princetonsoftech.com supportau@princetonsoftech.com http://princetonsoftech.com ___________________________ [...] 16425 58 42_Re: missing colon preceeding host variable9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 18 Jan 2001 16:11:13 -0700550_us-ascii Really tied to what version of DB2 was in place when the program was precompiled:

If precompiled with V2.2 or prior and still a valid plan then it would fail when executed under V6.

If precompiled with V2.3 through V5 and the plan or package is still valid it would still run, but any future precompile of the DBRM under V6 would fail. If you look at your valid DBRMs precompiled in V5 you will see that the colon was added in front of the host variable if it was not coded this way. With V6 implementation of UDTs, UDFs, [...] 16484 166 44_Re: Use of indicator variables in predicates10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 19 Jan 2001 09:30:19 +1000394_ISO-8859-1 Hi,

Phil, when u say it works, r u referring to v5 or v6 ? I was dabbling with some code in v5, but it didnt work. Hence my question.

Interestingly, the DB2 v5 Application Programming and SQL Guide (Sec. 3.1.4.1.4) has this extra note in the section on indicator variables. (The '#' symbol indicates changes to the manual since the hardcopy edition was published) [...] 16651 54 45_Re: DB2 Visual Explain - authorisation needed14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 19 Jan 2001 11:09:19 +1100446_iso-8859-1 Perhaps we need: - BIND ... EXPLAIN(ONLY) - no catalog or directory tables changed, only OWNER's plan_table etc updated, no authority required on package or collection (or plan if you're doing a 'bind plan() member() ...') (?, perhaps this point needs to be thought through), the only authority required is sufficient authority to explain the sql - an EXPLAIN authority on tables. No access to data, but you can issue an explain. [...] 16706 45 40_Re: DB2 v6.1 migration and CICS /TS v1.312_Kirk Hampton16_khampto1@TXU.COM31_Thu, 18 Jan 2001 18:19:19 -0600600_us-ascii One thing to check, starting with DB2 Version 6.1, the DSNCLI and DSNCUEXT modules for the CICS/DB2 interface are shipped only with CICS, no longer with DB2. If you are running OAM applications under CICS, it requires the DSNCLI module to be in an APF-authorized library. When our migration process replaced the contents of our DB2 libraries with V6.1, and these modules were no longer there, there was no other copy of them in an APF-authorized library, and our OAM applications stopped working. There is a CICS APAR (PQ38659 I believe) that places a copy of the modules into SDFHAUTH [...] 16752 99 38_Re: DB2 V6 - OS/390 Recovery Questions14_Jack O'Connell26_jack.oconnell@MCMASTER.COM31_Thu, 18 Jan 2001 18:28:51 -0600517_- The 'J' at the start of the fifth node of the dataset name ( the 'instance node' ) is used for the switching of dataset names during online reorg. With DB2 Vers 7 the 1st char of the instance node can alternate with values of 'I' or 'J'. This was to be used for the 'fastswitch yes' option , but I thought that was for version 7 and not vers 6 ( although, sometimes they update the prior versions with features from later releases if demand is there ). As Max has suggested, DSS or IDCAMS could have also been [...] 16852 75 45_Re: DB2 Visual Explain - authorisation needed17_Belfield, Alma G.21_Alma.Belfield@TWA.COM31_Thu, 18 Jan 2001 20:09:07 -0600366_iso-8859-1 I think you'll find Apptune 'switches' to an install sysadm id in DOMEXIT1 user exit, connects to DB2, does a SET CURRENT SQLID = whatever, then EXPLAIN etc

the helpful support staff at BMC helped me sort this out (we have customised DSN3@xxx exits to cope with ACF2 but they couldn't cope with the 'switch' of userids in the control blocks). [...] 16928 95 45_Re: DB2 Visual Explain - authorisation needed14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 19 Jan 2001 13:42:36 +1100423_iso-8859-1 OK, so it's ... USERID=. My point is the use of PASSCHK=NO.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Belfield, Alma G. [mailto:Alma.Belfield@TWA.COM] Sent: Friday, January 19, 2001 1:09 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 Visual Explain - authorisation needed [...] 17024 185 65_Re: CASE expression allowed to naviagate inside a WHERE c lause ?14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Fri, 19 Jan 2001 13:54:37 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 17210 20 21_Sync of two Databases7_sri sri20_sri_db2l@HOTMAIL.COM31_Thu, 18 Jan 2001 21:58:35 -0600554_- Dear List,

We have two databases on two different subsystems. we need to sync them. These are user managed datasets.

I prepared the DSN1COPY jcls to do this.

Question :

1) Each database has 3000 VSAM clusters. The target database was copied with the source database data 1 year back

================================================ 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. 17231 33 21_Sync of two Databases7_sri sri20_sri_db2l@HOTMAIL.COM31_Thu, 18 Jan 2001 22:08:44 -0600464_- Dear List,

V6- DB2 UDB OS/390

We have two databases on two different subsystems. we need to sync them. These are user managed datasets.

I have prepared the DSN1COPY jcls to do this.

Question :

1) Each database has 3000 VSAM clusters. The target database was copied with the source database data 1 year back and since then the source database has grown much larger. All the TABLE Structures are same in the both databases. [...] 17265 103 31_Re: migrating stored procedures10_John Lantz23_john.lantz@ZURICHUS.COM31_Thu, 18 Jan 2001 22:26:25 -0600529_- I think I'm close, but must be missing something. I created the *.bnd file through the precompile and was then able to do the BIND on the package. I then issued the CREATE PROCEDURE statement. Didn't know if I had all of the right parms though. For example "language ..." - should it be "language SQL" which is what I see on the stored procedure in dev. Or should it be "language C", which is what the SQL procedure language gets converted to? I picked C. Finally when I tried to run it after needing to move the *.dll to [...] 17369 46 22_Re: RDS Limit Exceeded32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Fri, 19 Jan 2001 08:03:20 +0200719_- RAndy,

If you have V5 and don't have APAR PQ15670, PTF UQ35740 please apply it. It'll help a lot.

Regards,

Cuneyt GOKSU Garanti Teknoloji Tel : 0090-212-4783210 mailto:Cuneytg@garanti.com.tr





-----Original Message----- From: Randy Scott [mailto:Randy_Scott@VFC.COM] Sent: Thursday, January 18, 2001 8:42 PM To: DB2-L@RYCI.COM Subject: RDS Limit Exceeded



DB2PM is reporting that our DB2 system is exceeding the 'RDS Limit' about 1,200 times each day. According to the DB2PM help, this count is the number of times RID list processing terminated because the number of RIDs that can fit into the guaranteed number of RID blocks was greater than the maximum [...] 17416 76 45_Re: Online reorg abends 00E40345 and 00E4001A23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 19 Jan 2001 07:01:32 -0000407_iso-8859-1 Tonmoy

strange - we raised this one some months back and I thought it had been resolved with a PTF.

I'll dig and see if I can find anything in our PMR stash.

Les

-----Original Message----- From: Tonmoy Dasgupta [mailto:tonmoy.dasgupta@MAIL.STATE.AR.US] Sent: Tuesday, January 16, 2001 8:16 PM To: DB2-L@RYCI.COM Subject: Online reorg abends 00E40345 and 00E4001A [...] 17493 135 31_Re: migrating stored procedures10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 19 Jan 2001 18:06:25 +1000442_ISO-8859-1 John,

R u sure that was the SQLCODE u got ? Following is the message for -1109 that I got from the manual :

SQL1109N The specified DLL “ ” could not be loaded. Explanation: The DLL (dynamic link library) module specified could not be found. The command cannot be processed. User Response: Ensure that the specified file is in a subdirectory identified in the system LIBPATH. sqlcode: -1109 sqlstate: 42724 [...] 17629 49 44_Re: Use of indicator variables in predicates11_Phil Button21_Phil.Button@BMW.CO.UK31_Fri, 19 Jan 2001 08:56:00 -0000474_iso-8859-1 The problem is that developers seldom read manuals - they try something, and if it works they carry on using it; then complain like fury when we put in some DB2 maintenance that stops the unsupported feature working.

Thank you very much for your thoughts.

Henceforth our site standards will require any developer found writing SQL of the offending form to walk around with some of Mr.Heinz's finest produce clearly protruding from each ear.... [...] 17679 29 21_copying Runstats info12_Robert Brock28_brock@KRYPTON-CONSULTING.COM31_Fri, 19 Jan 2001 03:20:34 -0600570_iso-8859-1 Hi Kuashal,

I have a set of DB2 utility and performance products that I am marketing. One of the products does specifically what you are looking for. Not only will it copy existing statistics from one set of object to another. You can create a completely new set of values to represent a situation that may not exist yet. If you modify a value such as the number of rows in a table, the product will calculate the proper values for the associated columns in all the necessary catalog tables. I think you would find the pricing very reasonable. If [...] 17709 33 37_Connections (not threads) running out13_Tony Hurlston26_Tony.Hurlston@ZURICH.CO.UK31_Fri, 19 Jan 2001 09:39:29 -0000437_iso-8859-1 List,

I am currently experiencing connection problems (IDBACK) with a DB2 V5 System.

The problem centres around Omegamon reporting that 99% of all background connections are used (IDBACK=1200) yet the display of Active Connections totals around 100.



I also have users reporting they are unable to connect to DB2 via the third party product Shadow Server, as it is unable to get a connection. [...] 17743 57 53_Groupbufferpool Structure Size Increase By New Policy9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 19 Jan 2001 10:46:08 +0000467_us-ascii Hi, OS390 2.8 DB2 V5.1

We are trying to increase the size of a group bufferpool using the static method, i.e. by activating a new policy.

The policy is activated and a /D XCF,STR,STRNAME=structure shows 'POLICY CHANGE PENDING'.

If I carry out a /D START,REBUILD,STRNAME=structure I get...

IXC367I THE SETXCF START REBUILD REQUEST FOR STRUCTURE 628 structure WAS REJECTED: REBUILD HAS ALREADY BEEN INITIATED FOR THE STRUCTURE [...] 17801 64 31_Re: migrating stored procedures23_Scarcella, Gary V (ING)23_ScarcellaGV@ING-AFS.COM31_Fri, 19 Jan 2001 07:15:58 -0500346_iso-8859-1 I recently heard from IBM, that in DB2 UDB v7.1 fixpak2, new functionality is being added to 'move' compiled SQL procedures from one server to another. Note that this is only available for SQL procedures. Also, note that SQL SPs can only be 'moved' in this manner when the platforms of the source and target servers are the same. [...] 17866 91 7_DB2 CLI0_18_dsi.listsrv@DB.COM31_Fri, 19 Jan 2001 17:53:08 +0530400_us-ascii Hi all, This is a question about CLI. Here is a sample query - "SELECT FIRSTCOL, SECONDCOL FROM CW.XTABLE WHERE THIRDCOL = ?".

When the above query is exceuted by passing value "AAA" to coulmn "THIRDCOL" assuming that desclaration of THIRDCOL is CHAR(1), CLI function SQLGetDiagRec() gives error message "[IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001". [...] 17958 91 53_Groupbufferpool Structure Size Increase By New Policy9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 19 Jan 2001 12:20:48 +0000552_us-ascii Further tests have shown that the structure has to be deallocated before the 'pending' is removed for the policy change.

Im a bit disappointed about this since the 'Datasharing And Planning Guide - Section, Changing the Size of the Group Buffer Pool (p248)' indicates that you only need to do a 'SETXCF START,REBUILD,STRNAME=structure' after the new policy is activated . I guess this is true if the Group Bufferpool is not allocated yet but not true if it is allocated. I think there should be a warning about this in the manual. [...] 18050 34 18_DB2 V6.1 migration12_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Fri, 19 Jan 2001 07:36:12 -0500397_us-ascii I have 2 questions for the forum. All responses appreciated.

1.Did you dump your DB2 cats & dirs via some dump utility (DFDSS, FDR etc). Before migrating to DB2 V6.1



2. After you migrated to V6 did you have to increase the size of BSDS's

All right there is a 3rd.

3. Did you have any problems with the Catmaint and if so what and how did you fix? [...] 18085 15 19_Global transactions15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 19 Jan 2001 06:59:27 -0600497_- Hi

Sometimes I read something about the new term "global transaction". Unfortunately I still don't know what exactly this term means. I think it has something to do with Recovery Ressource Manager. Can anybody out there enligthen me a little bit?

================================================ 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. 18101 90 42_Re: missing colon preceeding host variable11_rick creech18_ykcirc@HOTMAIL.COM31_Fri, 19 Jan 2001 07:11:57 -0600340_- Hi,

Concerning the following statement: 'If precompiled with V2.2 or prior and still a valid plan then it would fail when executed under V6.'

It is my understanding that these will execute under v6, but once a bind or rebind takes place, the bind or rebind will fail and then at that point they will no longer execute. [...] 18192 123 57_Re: Groupbufferpool Structure Size Increase By New Policy11_rick creech18_ykcirc@HOTMAIL.COM31_Fri, 19 Jan 2001 07:16:09 -0600714_- Just curious,

What is your CF level?



Regards, Rick Creech

>From: Andy Hunt >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Groupbufferpool Structure Size Increase By New Policy >Date: Fri, 19 Jan 2001 12:20:48 +0000 > >Further tests have shown that the structure has to be deallocated before >the >'pending' is removed for the policy change. > >Im a bit disappointed about this since the 'Datasharing And Planning Guide >- >Section, Changing the Size of the Group Buffer Pool (p248)' indicates that >you >only need to do a 'SETXCF START,REBUILD,STRNAME=structure' after the new >policy >is activated . [...] 18316 65 22_Re: DB2 V6.1 migration12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Fri, 19 Jan 2001 14:22:50 +0100510_- Maybe you can learn something of my mistakes (I am good in that part :).

I did a V6 migration test in a system that is a complete copy of our production system. 1) dump not necessary for the test, 2) did not need to increase BSDS, 3) Catmaint abendend, due to one type 1 index I had missed to convert. In that situation it turned out impossible to fix the index. I was bound to do fall back. After fallback DB2 V5 did not start. I had missed a couple of fallback PTFs. I apply them, and DB2 goes [...] 18382 165 57_Re: Groupbufferpool Structure Size Increase By New Policy9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 19 Jan 2001 13:22:35 +0000830_us-ascii CFLEVEL 9 Kind Regards, Andy Hunt - Scottish And Southern Energy







rick creech on 19/01/2001 13:16:09

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Andy Hunt/HAV/SSE) Subject: Re: Groupbufferpool Structure Size Increase By New Policy





Just curious,

What is your CF level?



Regards, Rick Creech

>From: Andy Hunt >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Groupbufferpool Structure Size Increase By New Policy >Date: Fri, 19 Jan 2001 12:20:48 +0000 > >Further tests have shown that the structure has to be deallocated before >the >'pending' is removed for the policy change. [...] 18548 69 22_Re: DB2 V6.1 migration13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Fri, 19 Jan 2001 07:31:36 -0600309_US-ASCII We just completed migrating one of our subsystems from v5.1 to v6.1 on the OS/390 and had no issues... 1. we did a DFDSS backup and also took image copies of the catalog prior to the migration.

2. We increased our BSDS's prior to the migration.

3. We had no problem with Catmaint. [...] 18618 45 42_Re: missing colon preceeding host variable20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 19 Jan 2001 08:39:38 -0500379_us-ascii If you use the REXX specified in the URL http://www.ibm.com/software/db2os390/downloads.html to identify missing colon, it will most often comes up for SQLDA area missing the colon, this is of least concern. All other cases of missing colon wouldn't cause the program to stop working in V6 instead it will fail only during REBIND or a fresh bind from the old DBRM. [...] 18664 28 60_Re: documents to find a beginning for system programming....11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 19 Jan 2001 09:19:46 -0500525_iso-8859-1 I think a good start would be to join NASPA the "Network and Systems Professionals Association" This has been very helpful to me. They publish a magazine with lots of good information and also have a web site

http://www.naspa.com/

See also

http://www.cbttape.org/

for lots of MVS freeware. This source code can teach you a lot, and there is lots of documentation. You may also want to track the IBM-MAIN newsgroup. There is info here. There are also many links on these two sites. [...] 18693 195 57_Re: Groupbufferpool Structure Size Increase By New Policy11_rick creech18_ykcirc@HOTMAIL.COM31_Fri, 19 Jan 2001 08:19:50 -0600748_- Andy,

Did you enter: SETXCF START,ALTER,STRNAME=DSNDB0G_GBPn,SIZE=newsize

hth,

Rick Creech

>From: Andy Hunt >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Groupbufferpool Structure Size Increase By New Policy >Date: Fri, 19 Jan 2001 13:22:35 +0000 > >CFLEVEL 9 >Kind Regards, >Andy Hunt - Scottish And Southern Energy > > > > >rick creech on 19/01/2001 13:16:09 > >Please respond to DB2 Data Base Discussion List > >To: DB2-L@RYCI.COM >cc: (bcc: Andy Hunt/HAV/SSE) >Subject: Re: Groupbufferpool Structure Size Increase By New Policy > > > >Just curious, > >What is your CF level? > > >Regards, [...] 18889 76 42_Re: missing colon preceeding host variable11_rick creech18_ykcirc@HOTMAIL.COM31_Fri, 19 Jan 2001 08:24:00 -0600327_- Venkat,

Another thing that we have done (the DBA staff) to make sure our exposure was limited:

1. Identified 1410 plan/pacakages in our environment with pre-2.3 dbrms. 2. Educated application groups on the issues. 3. So far we have identified almost 1000 of these that are obsolete and we have freed them. [...] 18966 15 41_Re: missing colon preceding host variable11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 19 Jan 2001 09:35:16 -0500384_iso-8859-1 There are several helpful programs regarding this in the archives, See

http://jupiter.ryci.com/archives/db2-l-documents.html

================================================ 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. 18982 55 29_Insert & Update using VARCHAR11_John Puglia16_jpuglia@IWON.COM31_Fri, 19 Jan 2001 09:27:28 -0500413_ISO-8859-1 We have a Cobol program in our shop that has a dclgen with 4 columns that have attributes of char.

The same columns in the table that the dclgen refers to have attributes of varchar.

DCLGEN TABLE

CHAR1 (55) VARCHAR(55) CHAR2 (15) VARCHAR(15) CHAR3 (60) VARCHAR(60) CHAR4 (60) VARCHAR(60)

Nowhere in the program is there a field that holds the length for those columns. [...] 19038 57 57_Re: Groupbufferpool Structure Size Increase By New Policy9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 19 Jan 2001 14:41:13 +0000416_us-ascii Rick, No, I wanted to use the 'static' method as described in the 'Datasharing And Planning' Guide.

That is (from the manual)... "1. Increase the storage for the group buffer pool in the CFRM policy.

2. Use the following MVS command to start the updated policy:

SETXCF,START,POLICY,TYPE=CFRM,POLNAME=policyname

3. Use the following command to rebuild the group buffer pool: [...] 19096 65 25_Re: Sync of two Databases19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 19 Jan 2001 09:51:49 -0600584_iso-8859-1 sri, if you have RC/Migrator, turn on the VSAM defines. It uses the source DB space allocations. Be careful though, some of the source VSAM files my be in extents and you'll have to hand fix those.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender [...] 19162 120 7_DB2 CLI14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 19 Jan 2001 07:57:59 -0800487_- Ramesh, The SQLGetDiagfield() function will return the SQL_DIAG_COLUMN_NUMBER field which will contain either the column number or parameter number. HTH Phil

Philip K. Gunning Sr. Systems Consultant Quest Software



-----Original Message----- From: dsi.listsrv@DB.COM To: DB2-L@RYCI.COM Sent: 1/19/01 4:23 AM Subject: DB2 CLI

Hi all, This is a question about CLI. Here is a sample query - "SELECT FIRSTCOL, SECONDCOL FROM CW.XTABLE WHERE THIRDCOL = ?". [...] 19283 19 32_Re: Security under RACF question12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 19 Jan 2001 09:52:25 -0600367_- I did not have any problem with your query. The topic is complex and the design crosses multiple products. I wish we could document it better, and don't know how. I've been wrong and been corrected many times. The last time I checked, I was very human and expect that's still the case. I try to indicate what I know, what I think is so, and what I'm guessing. [...] 19303 13 31_Dataload for DB2 on VM question10_Wes Wright23_wesleyw@RENEWAL-IIS.COM31_Fri, 19 Jan 2001 09:43:40 -0600480_- Is it necessary to use the commitcount parameter with the Dataload utility when loading large amounts of data? Also, is there a way to put invalid records into an exceptions table when these records are rejected by the Dataload utility?

================================================ 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. 19317 14 33_Re: regarding repartioning.......9_Jim Ruddy18_jaruddy@US.IBM.COM31_Fri, 19 Jan 2001 10:00:00 -0600395_- Are you doing all this work becuase you need more partitions rather than just rebalancing the existing partitions?

Jim Ruddy IBM DB2 Developemnt

================================================ 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. 19332 44 51_Re: Current JCL to load the DB2PM Historical tables23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 19 Jan 2001 16:00:45 -0000472_iso-8859-1 yes .. mail me Monday and I will send you a copy

Leslie DB2 DBA DB2 Certified

-----Original Message----- From: Jeff Faughn [mailto:Jeff_Faughn@MAY-CO.COM] Sent: Wednesday, January 17, 2001 8:32 PM To: DB2-L@RYCI.COM Subject: Current JCL to load the DB2PM Historical tables



Does anyone have JCL to load the DB2PM performance tables talked about in chapter 39 of the DB2 Performance Monitor for OS/390 Report Reference Volume 1? [...] 19377 40 14_Update a table18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Fri, 19 Jan 2001 11:10:14 -0500288_iso-8859-1 I've got an application programmer that wants to update a DB2 table using a Temporary table that he's created. Ie; he's got data in his temp table and he wants to match those rows to certain rows in the DB2 table and then update from there. Here's the QMF query he wrote: [...] 19418 83 18_Re: Update a table11_rick creech18_ykcirc@HOTMAIL.COM31_Fri, 19 Jan 2001 10:14:31 -0600363_- Hi,

I think you need to be at version 6 to do a correlated update of that kind, although I think you would have to use a sub-select.

What I do at version 5 to accomplish this kind of thing is to create update statements with SELECT of a literal string that results in the updates and then run the output through dsntiad or tep2. For example: [...] 19502 53 26_Decimal division results ?15_Jackson Reavill18_damcon2@US.IBM.COM31_Fri, 19 Jan 2001 11:22:15 -0500506_us-ascii Hi all,

I'm getting what appear to be strange decimal division results. Can someone please explain to me why the following occurs?

When executing the SQL below, I get different results for what are basically the same calculations. The column BILLING_RATE is defined as decimal 15,5 not null. The actual value in the column when selected thru spufi is 50.00000. The expected result is 3.63364. When done on a calculator the result is 3.633641708393. The SQL gives the following: [...] 19556 67 35_Re: Dataload for DB2 on VM question0_27_vinhaes@CAIXASEGUROS.COM.BR31_Fri, 19 Jan 2001 14:27:36 -0300305_us-ascii No it is not necessary to use the commicount clause parameter, but if you are load a lager amount of data, it is a good idea to use it, if you have a problem you can restart the dataload, from the point the error occurs, and also you will not have problems with log and with the checkpoint. [...] 19624 152 42_Re: missing colon preceeding host variable9_Rob Crane22_racrane@CONCENTRIC.NET31_Fri, 19 Jan 2001 09:33:22 -0700517_us-ascii Sounds like my memory is failing..... I thought I recieved a -204 on a 2.2 DBRM in a V6 subsystem but I could be mistaken. I will try to recreate. Either way I would still correct any SQL that is not using a colon preceeding the host variables. Here are the typical warnings(V5 - return code 0) and error (V6 -return code 8) that the precompiler will generate. Having the output of current precompiles searched for these warnings could be another way of finding these on your current V5 subystems, etc. [...] 19777 75 22_Re: DB2 V6.1 migration0_24_lightsey@ITS.STATE.MS.US31_Fri, 19 Jan 2001 10:34:01 -0600623_us-ascii Just converted production to v6 last Friday.

1) yes - fdr dumped all of the packs associated with the subsystem > catalog and dir packs for backup, userdata for paranoia

2) no - they were already plenty large enough

3) yes - the database with the demo/verification tables still had dataset passwords on it (&%$@***) used the fdr dump to restore the packs to the v5 level, restarted db2, dropped the demo database (didn't ever need it anyway), and started the process over - no problems that time. Still difficulties for remote clients changing racf passwords with the encryption routines [...] 19853 98 22_Re: DB2 V6.1 migration13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 19 Jan 2001 10:51:24 -0600378_iso-8859-1 In the process of doing the same.

1. DFDSS dump the catalog. 2. No, did not increase space. 3. In testing on test LPAR, got error relating to a view while executing CATMAINT. The incompatible was reported by DSNTIJPM, I did not see it though. CATMAINT detected the error in the first phase. I deleted offending view and re-executed CATMAINT. No problems. [...] 19952 59 45_Re: DB2 Visual Explain - authorisation needed22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Fri, 19 Jan 2001 14:57:10 -0200401_iso-8859-1 > I never have liked the security requirements on running an explain (that you need the authority to execute a statement in order to explain it).

I share this feeling with you...

> I also don't really like the approach of having a "hand-off" where the developers give the SQL to the DBA's just prior to moving to production, and the DBA's need to authorize the transfer. [...] 20012 153 37_Re: Stored procedures and auto-rebind12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 19 Jan 2001 08:58:05 -0800558_us-ascii The APAR for invalidating packages is combined with another apar. Unfortunately my client is having problems with INFOLINK and IBM this week so I'm unable to see the open PMR and get you the correct number. I will do so as soon as IBM gets everything resolved. (Have you ever heard that one before :=) ) --- "LAM, Leo" wrote: > Myron, > Thanks for the info. > I found APAR PQ43444 but it's more related to SPB. > I couldn't find the APAR > you mentioned about invalidating packages when a > stored procedure is > dropped. > [...] 20166 108 42_Re: missing colon preceeding host variable20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 19 Jan 2001 11:59:26 -0500269_us-ascii We freed a lot of them but we have a vendor package and vendor doesn't exist. No source, corrupted DBRM library but we need to rebind them on & off. We converted them using the Rexx (which I have put in DB2-L doc) and haven't seen any side effect so far. [...] 20275 41 33_Re: regarding repartioning.......17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Fri, 19 Jan 2001 10:54:11 -0600302_- Hi Jim,

I can not rebalance existing partition now because inflow of data is too much... and we don't have the room to fill the data also we are facing the space problem.... with all these we are planning to do repartion so that alteast for till next year we should not face any problem. [...] 20317 119 45_Re: DB2 Visual Explain - authorisation needed17_Belfield, Alma G.21_Alma.Belfield@TWA.COM31_Fri, 19 Jan 2001 11:19:08 -0600559_iso-8859-1 Your correct in that no password checking is being done..... but my point is that they are NOT using any security product calls - no RACROUTE or SAF call at all. They simply overwrite the instorage control block where the userid value is stored, before they connect to DB2. DB2 picks up the userid out of that instorage control block. The install sysadm value doesn't even have to be a real userid in RACF (or ACF2 etc), so if they did do a RACROUTE it would fail. I just thought you might be interested in knowing how it actually worked..... [...] 20437 91 30_Re: Decimal division results ?13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Fri, 19 Jan 2001 11:53:10 -0600411_iso-8859-1 Jay,

It has to do with the precision and scale of each decimal value during the division (or multiplication). The formula for the scale is (15 - (scale of 1st arg - scale of 1st arg + scale of 2nd arg)): Eg. 50.00000 (DEC(7,5)) / 13.7603 (DEC(6,4)) = 15 - (7 - 5 + 4) = 9 hence result = 3.633641708 BILLING_RATE (DEC(15,5)) / 13.7603 (DEC(6,4)) = 15 - (15 - 5 + 4) = 1 hence result = 3.6 [...] 20529 213 33_Attempt to Call SP from CLP fails13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Fri, 19 Jan 2001 13:20:05 -0500705_US-ASCII List,

An acquaintance of mine is having a problem invoking a DB2-managed stored procedure from the DB2 Connect command line processor. The error message is a bit odd. Any help would be appreciated.

Thanis!

- - LL

-=-=-=-=-=---=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



We have installed DB2 for OS/390 V5.1 and DB2 Connect Personal Edition 7.1 on a Window 2000 workstation. The Stored Procedures reside on OS/390 and have been called successfully from a COBOL program on OS/390. We have successfully executed SQL queries using the DB2 Connect Command Line Processor (CLP) so the connection between DB2 Connect and DB2 for OS/390 is there. We [...] 20743 26 16_QUIESCE QUESTION16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Fri, 19 Jan 2001 10:25:14 -0700593_us-ascii We have recently had problems with a fairly large tablespace which we just partitioned. During a system wide quiesce which we take daily, the quiesce took around 25 minutes to complete for this structure. This caused many of our CICS transactions to fail due to timeouts. The number of partitions for the tablespace is 34, there are 5 datasharing groups for the subsystem, and our CICS transactions run across two of the LPARs. What we ended up doing to minimize the impact of the quiesce is to perform the operation at the partition level AND specify WRITE(NO). My question is, [...] 20770 69 37_Re: Attempt to Call SP from CLP fails12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Fri, 19 Jan 2001 12:36:13 -0600505_iso-8859-1 The CLI0150E message pulls up the following information:

********************* Text Below Copyright (c) 2001, NCR ********************* CLI0150 Session is busy.





Explanation: The Wait_If_Session_Busy option has been

set to 'N' (no implicit waits) and CLI2 can not process

the requested function because the session is busy.

Notes: This is an informational return code that can

only be received if the Wait_If_Session_Busy option is [...] 20840 114 37_Re: Attempt to Call SP from CLP fails0_24_db46@DAIMLERCHRYSLER.COM31_Fri, 19 Jan 2001 15:21:55 -0500524_iso-8859-1 If you are using datajoiner for 'ushmutds', it only allows stored procedures to be invoked with input parameters. It cannot return any parameters or result sets.

Dean









Lockwood Lyon @RYCI.COM> on 01/19/2001 01:20:05 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Attempt to Call SP from CLP fails [...] 20955 27 20_Re: QUIESCE QUESTION0_17_JTonchick@AOL.COM29_Fri, 19 Jan 2001 15:43:39 EST438_US-ASCII In a message dated 01/19/2001 12:26:37 PM Central Standard Time, Jeff.A.L'Italien@AEXP.COM writes:

<< My question is, does anyone know what the downside would be of using WRITE(NO) during the quiesce, other than not externalizing updates from the bufferpools to DASD? >>

Isn't that why you are doing the QUIESCE in the first place? To force externalization of changes buffers for some backup/recovery purpose? [...] 20983 21 39_ADD CONSTRAINT on DB2 UDB V6 for OS/39015_Larry Berlinski26_Larry_Berlinski@TIVOLI.COM31_Fri, 19 Jan 2001 15:55:54 -0500513_us-ascii How would I convert the following constraint coded for NT based system for an OS/390 based system?

ALTER TABLE atable ADD CONSTRAINT athisone CHECK( ( (aname like '[A-z]%') and aname not like '%[-% ~`!@#$^&*()_+=\|{}''";:,./<>?]%'));

Regards, Larry

================================================ 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. 21005 33 37_Re: Attempt to Call SP from CLP fails13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 19 Jan 2001 16:20:34 -0500394_iso-8859-1 Hey Lockwood.

I had this error and several others and a number of problems open with IBM.

They were all resolved by Fixpack 1.

By the way, you can get around the problem by using the Stored Procedure Builder. It's nice in that it brings up a list of Stored Procedures on the server, lets you right click on one, and it prompts you for the Input parameters. [...] 21039 99 22_FW: RDS Limit Exceeded11_Lorenz, Don15_dlorenz@KCC.COM31_Fri, 19 Jan 2001 15:26:15 -0600496_iso-8859-1 Susan,

We looked at PQ19045, but that is not the correct APAR. We may have missed it, but we did not find a hit related to index screening. Can someone in the list provide the APAR?

We are DB2 V5, PUT level 0003.....going to 0008 or 0011 shortly....on our legacy systems.

Regards,

-----Original Message----- From: Susan Lawson [mailto:lawson_susan@YAHOO.COM] Sent: Thursday, January 18, 2001 12:57 PM To: DB2-L@RYCI.COM Subject: Re: RDS Limit Exceeded [...] 21139 22 19_QUERYNO in V6 OS3900_22_Rohn.Solecki@MTS.MB.CA31_Fri, 19 Jan 2001 15:33:09 -0600539_us-ascii I just found that you can specify QUERYNO in V6 (I thought it wasn't coming until V7) for EXPLAINS.

As far as I can tell from my reading QUERYNO is only used in the EXPLAIN process.

Is there any way of retrieving the QUERYNO during normal error processing. i.e. If I specify QUERYNO on all SQL in my program. Then when I get a non zero SQLCODE I would like to be able to report (or include in our error logging table entry) the QUERYNO so that programmer can go directly to the problem code. It would be a real [...] 21162 19 45_Using Perl to Access DB2 for Linux - Tutorial19_Shailendra S Dhanoa19_ssdhanoa@US.IBM.COM31_Fri, 19 Jan 2001 13:35:21 -0800696_us-ascii =========================================== In this free, dW-exclusive tutorial you'll learn how to install and use a Perl interface to the IBM DB2 Universal Database, Personal Developer's Edition. You'll also learn by example how to query the sample database provided with the DB2 Personal Developer's Edition.

http://www6.software.ibm.com/reg/devworks/dw-linuxperl-i?open&l=327,t=gr,p=perldb2 ===========================================

================================================ 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. 21182 126 22_Re: RDS Limit Exceeded12_Susan Lawson24_susan_lawson@YLASSOC.COM31_Thu, 18 Jan 2001 15:51:36 -0600512_iso-8859-1 DOn,

Boy was I off just a few numbers!! It is PQ15670.

Regards, Susan

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Lorenz, Don Sent: Friday, January 19, 2001 3:26 PM To: DB2-L@RYCI.COM Subject: FW: RDS Limit Exceeded



Susan,

We looked at PQ19045, but that is not the correct APAR. We may have missed it, but we did not find a hit related to index screening. Can someone in the list provide the APAR? [...] 21309 68 98_Repost: Participants for Usability Studies Needed: Monday Night, 1/22/01 at IBM Silicon Valley Lab13_Morrill, John12_JohnM@VP.NET31_Fri, 19 Jan 2001 14:51:50 -0700461_- Dear DB2 Interested Parties,

I know that this is (very) last minute, and realistically limited to DB2 DBA's in Northern California, but I thought that I'd send it out anyway...

One additional participant is needed to participate in a usability study at the IBM Silicon Valley Lab this Monday night, January 22nd (see below). Please contact Dana Marsh today (or Monday morning) if you think that you might be able to help us out. Thank you. [...] 21378 117 43_Re: ADD CONSTRAINT on DB2 UDB V6 for OS/3900_22_Rohn.Solecki@MTS.MB.CA31_Fri, 19 Jan 2001 15:52:32 -0600534_iso-8859-1 Funny thing, one of my developers asked basically the same question an hour ago ...

Here is a snip from the V6 "Application and Programming SQL Guide" that I think answers your question 2.2.1.3 Creating tables with check constraints

A check constraint allows you to specify what values of a column in the table are valid. For example, you can use a check constraint to make sure that no salary in the table is below $15,000, instead of writing a routine in your application to constrain the data. [...] 21496 52 23_Re: QUERYNO in V6 OS39011_Bill Happel22_WRHappel@US.FORTIS.COM31_Fri, 19 Jan 2001 16:13:42 -0600425_US-ASCII Your programs already know what the QUERYNO is as it is hardcoded in the program itself.

Are you saying you have programs which produce an error message, but does NOT supply any information where the error occurred at? Boy does that sound familiar.

>>> Rohn.Solecki@MTS.MB.CA 01/19/01 03:33PM >>> I just found that you can specify QUERYNO in V6 (I thought it wasn't coming until V7) for EXPLAINS. [...] 21549 51 20_Re: QUIESCE QUESTION11_Bill Happel22_WRHappel@US.FORTIS.COM31_Fri, 19 Jan 2001 16:21:15 -0600359_US-ASCII I agree with Jim. Our QUIESCE steps are executed prior to all DB2 Update Job Steps and only those tablespaces which will have to be recovered in case the job step fails are listed on the QUIESCE.

>>> JTonchick@AOL.COM 01/19/01 02:43PM >>> In a message dated 01/19/2001 12:26:37 PM Central Standard Time, Jeff.A.L'Italien@AEXP.COM writes: [...] 21601 24 17_Using REXX in QMF15_Schaeffer, Dave28_dave.schaeffer@HONEYWELL.COM31_Fri, 19 Jan 2001 15:22:45 -0700352_- We are a QMF v3.3 shop running on a OS/390 system.

What we are trying to do is execute a QMF PROC that contains REXX code. The REXX code calls a CLIST that sets ISPF variables. I have tried everything I can think of to issue a VGET and retrieve the variable back to QMF. Is this something that can be done? Any help would be appreciated. [...] 21626 45 37_Different results in program vs SPUFI13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Fri, 19 Jan 2001 16:39:49 -0600342_- I'm getting sqlcode +100 from this sql when executed in a COBOL program while getting two rows in SPUFI. The SQL is same except for INTO clause ( I'm only interested in sqlcode not in the value ), no qualifier for the tables and host variable in place of hard coded value. Based on the data result from SPUFI is correct. Any ideas ... [...] 21672 39 43_Re: ADD CONSTRAINT on DB2 UDB V6 for OS/39015_Larry Berlinski26_Larry_Berlinski@TIVOLI.COM31_Fri, 19 Jan 2001 17:53:57 -0500528_us-ascii Rohn, Thanks for your response. I do have the manuals, but they're not clear on how to specify ranges of characters or how to specify wildcards as in the example I presented. None of the examples in the manuals deal with alphabetic characters or strings of characters.

For instance, the first portion of the constraint in my example is: ... CHECK( ( (aname like '[A-z]%') ... which reads "Allow alphabetics A through Z and a through z in the first postion, and allow anything there after" (% is a wildcard) [...] 21712 41 11_set options13_Laura Hendrix16_lhendrix@BCO.COM31_Fri, 19 Jan 2001 14:44:32 -080035_ISO-8859-1 SET DB2-L DIGEST

21754 74 22_Re: DB2 V6.1 migration12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Fri, 19 Jan 2001 16:50:33 -0600561_- Stan:





If you can take a full dump to the db2 subsystem before migrating using dfdss or flash copy or snapshot that would be good and safe for you. On my test environment before I started the migration I did a dump (DFDSS) and the when I was running the catmaint Job it failed because I had forgotten remove one DSETPASS on one tablespace. We just restore the entire subsystem because if you have problem with the catmaint job I think the only option that we have is come back to version 5. Then I remove DSETPASS for the remaining [...] 21829 65 41_Re: Different results in program vs SPUFI11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET31_Fri, 19 Jan 2001 14:53:14 -0800643_US-ASCII it sounds like your host variable is getting overlaid in your program. check the value of the host variable at time of program execution.

Kathy Jones Central Information Services Clark County School District Senior Database Analyst

jonesks@GroupWise.ccsd.net

>>> mkkhan88@HOTMAIL.COM 01/19/01 02:39PM >>> I'm getting sqlcode +100 from this sql when executed in a COBOL program while getting two rows in SPUFI. The SQL is same except for INTO clause ( I'm only interested in sqlcode not in the value ), no qualifier for the tables and host variable in place of hard coded value. Based on the data result from [...] 21895 33 41_Re: Different results in program vs SPUFI13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Fri, 19 Jan 2001 17:08:21 -0600596_- Thanks but that doesn't seem to be the case. Here is the display from the proram, immediately before and after the sql:

SHARES QUERY 0004980761 LK-AUTO-REGISTER = SQLCODE 0000000100

Thanks Mohammad



On Fri, 19 Jan 2001 14:53:14 -0800, KATHY JONES wrote:

>it sounds like your host variable is getting overlaid in your program. check the value of the host variable at time of program execution. > >Kathy Jones >Central Information Services >Clark County School District >Senior Database Analyst > >jonesks@GroupWise.ccsd.net > [...] 21929 21 25_DB2 Connect V7.1 question0_39_brian_holroyd.consultant@PEOPLESOFT.COM31_Fri, 19 Jan 2001 15:24:25 -0800446_us-ascii From our DB2 platform support manager.....

"We are waiting for IBM to release FP 2 for general availability until we can certify it. The Certification team is ready and waiting to go.

We were running into error messages like "Function Sequence Error" with FP1. This has been fixed with FP2. As soon as it goes GA, I am anticipating a certification turnaround of 2 weeks. It will be certified, and as soon as possible" [...] 21951 36 64_DB2 Connect Personal Edition CALL OS/390 Stored Procedure - Help12_Shepard, Ken27_KShepard@HASTINGSMUTUAL.COM31_Fri, 19 Jan 2001 18:27:37 -0500619_iso-8859-1 If anyone of you has experience with DB2 Connect Personal Edition (V7 or any) processing Client CALLs to OS/390 Stored Procedures (DB2 V5.1) and are willing to share a coded sample of one of those CALLs, we would be very grateful.

Perhaps more than you want to know: Using the DB2 Connect Command Line Processor (CLP) or Command Window - - we can CONNECT TO the OS/390 Database - successfully execute SQL SELECT statements - when we attempt to execute a CALL storedprocedure (parm, parm, etc.); we get CLI0150E Driver not capable. SQLSTATE=HYC00DB21018E A system error occurred. The command line [...] 21988 156 31_Re: migrating stored procedures13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 19 Jan 2001 17:56:45 -0600564_US-ASCII John, Try putting the dll in the sqllib/function directory. HTH Kurt

>>> teldb2kals@TELSTRA.COM 01/19/01 02:06AM >>> John,

R u sure that was the SQLCODE u got ? Following is the message for -1109 that I got from the manual :

SQL1109N The specified DLL " " could not be loaded. Explanation: The DLL (dynamic link library) module specified could not be found. The command cannot be processed. User Response: Ensure that the specified file is in a subdirectory identified in the system LIBPATH. sqlcode: -1109 sqlstate: 42724 [...] 22145 18 18_DB2 Books for PL/110_Philip Lee14_plee@LACSD.ORG31_Fri, 19 Jan 2001 15:46:51 -0800384_us-ascii Hi All,

I'm newbie to DB2 and looking for the DB2 books for PLI. We're using IBM PL/I for VSE/ESA and CICS/VS 1.6.

TIA.

================================================ 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. 22164 68 33_Re: Insert & Update using VARCHAR13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 19 Jan 2001 18:21:41 -0600363_US-ASCII If you do not tell DB2 the length of the varchar field it will pad the inserts and updates with blanks to the maximum char size. You can verify this by selecting you varchar field with the "hex" column function. You will see your data followed by hex'40' s. This defeats the purpose of using varchars as you will get no DASD space savings. HTH Kurt [...] 22233 15 23_Re: Distributed Threads11_Mark Harmon17_mhinnyc@YAHOO.COM31_Fri, 19 Jan 2001 18:20:49 -0600506_- There is a product you can buy called Thread/SENTRY that should give you total control over your DB2 threads. You can cancel threads that exceed limits you define and I think you can also slow them down. The vendor site is: www.relarc.com/Threads.htm

Mark

================================================ 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. 22249 63 16_STORED PROCDURED19_=?big5?B?tPa90qr4?=21_A000896@MS.SKH.ORG.TW31_Sat, 20 Jan 2001 23:50:27 +0800273_big5 HI : ALL I WANT TEST OS/390 DB2 STORES PROCEDURE, ENVITONMET CLIENT : OS/390 DB2 V5 SERVER : OS/390 DB2 V6 CPU : ONE OS/390 AND BUILD TWO DB2 (V5, V6) SOMEONE CAN TELL ME ,I HOW ABOUT SET UP STORED PROCDURE ENVIRONMET AND PROCEDURE STEP ......



THANK YOU 22313 69 41_Re: Different results in program vs SPUFI13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Fri, 19 Jan 2001 21:31:31 -0600444_iso-8859-1 Mohammad,

The standard question to this is: Did you populate the host variable before the OPEN CURSOR or before the FETCH?

But the fact that you mention the INTO implies a singleton select.

Is that the host variable used in the equals predicate that you are displaying? Is that the SQLCODE of that SQL statement that you are checking (often the SQLCODE is moved to a host structure for display/comparison)? [...] 22383 57 23_Re: QUERYNO in V6 OS39014_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Sat, 20 Jan 2001 02:22:40 -0600480_iso-8859-1 QUERYNO was introduced in V6 as a way to specify a particular QUERYNO for a SQL statment and match it to a HINT, which reference the QUERYNO column in the plan table. It is used at BIND time by DB2, and nothing else.

Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums [...] 22441 63 20_Re: QUIESCE QUESTION11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Sat, 20 Jan 2001 09:18:12 -0500574_ISO-8859-1 Jim, Jeff

I would have thought that the primary purpose of Quiesce was to establish a point of consistency on the Log. Forcing updated pages from the buffer pool is only required for non-DB2 recovery (see below).

In response to Jeff's original question: WRITE(YES) causes the Recovery Log RBA in the header page to be updated. This is necessary if you want a fast recovery using RECOVER LOGONLY after restoring the tablespace from a non-DB2 backup (DFDSS or DSN1COPY for example). WRITE(YES) is not necessary for a RECOVER TOLOGPOINT recovery. [...] 22505 31 42_Re: missing colon preceeding host variable12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 20 Jan 2001 14:20:06 -0600346_- It is hard to predict exactly what error you will get. If the colon is missing, then we might assume that the object is a column, and thus SQLCODE -204 is a possible consequence.

During the process of working with a couple of customers, we compared the code to the red book SG24-6108. We found a number of corrections for the book. [...] 22537 103 18_Re: Update a table14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Sat, 20 Jan 2001 16:01:25 -0600418_iso-8859-1 Stephen,

Yes, obviously that does not work as written since that would be trying to update the result of a join.

However, theoretically (since I cannot test it at this time)...depending on version, and coded 2 ways below...as long as the subquery returns one and only one row. The top one will not work on OS/390 until next release but bottom should as long as DB2 UDB OS/390 V6 refresh. [...] 22641 47 33_Re: Insert & Update using VARCHAR13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Sat, 20 Jan 2001 22:20:53 -0500332_iso-8859-1 >I thought in the program you must move the proper value to a length field >before issuing an Insert or Update.

You thought correctly John. I have had lots of painful experience explaining and proving this to programmers. You may have valid data in the tables, but I suspect you're missing the first 2 bytes. [...] 22689 25 18_db2 dba job search0_18_Usregion10@AOL.COM29_Sun, 21 Jan 2001 02:28:29 EST409_ISO-8859-1 Hi all!

It has been a while since I have explored new job search sites. So far, I have had enough luck with dice.com and monster.com. However, now I would like to expand my job search (esp. for contract assignments) beyond these sites. Therefore, I would like to hear from you folks about whict sites are considered as the prime locations to get new contracts, contract firms, etc etc. [...] 22715 80 23_Re: QUERYNO in V6 OS39011_Suresh Sane21_data_arch@HOTMAIL.COM31_Sun, 21 Jan 2001 03:10:48 -0600401_- Just to elaborate on Richrard's comment "and nothing else" - What this means is that it is NOT stored in SYSPACKSTMT. This presumably will happen in V7 (I can hardly wait!). So if you use it in V6, you can specify HINTs (without worrying about changing queryno) but to find the text that corresponds to it - you are on your own since SYSPACKSTMT will still contain the old and changing number. [...] 22796 45 45_PQ37203: Need to precompile existing programs15_Hans Schuermann18_hschuermann@DIS.CH31_Sun, 21 Jan 2001 10:39:21 -0600462_- Dear list,

I'm just preparing some maintenance to our DB2 V6 problem and it seems we are running into a problem.

PQ37203 (UQ45132) requires an action for DBRMs containing 'SET :hvar = special register' where the special register is either CURRENT SERVER or CURRENT PACKAGESET.

If the package is created before migration to V6 we have to do bind them again. No problem. But for packages created on V6 a new precompilation is required. [...] 22842 57 43_Re: ADD CONSTRAINT on DB2 UDB V6 for OS/39014_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Mon, 22 Jan 2001 08:27:53 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 22900 53 23_Re: Global transactions15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 22 Jan 2001 09:18:56 +1100404_- Hi Walter,

Your timing is excellent. I've just finished reading the section on global transaction support in the exellent PDF 'DB2 UDB Server for OS/390 Technical Update' which you can download from IBM (somewhere; I forget where. Oh yeah, that's right; www.redbooks.com). Global transaction support is about allowing 2 separate UOWs to see and access each other's locked table rows/pages. [...] 22954 50 41_Re: Connections (not threads) running out13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Mon, 22 Jan 2001 10:41:43 +1100574_iso-8859-1 Tony,

What is the value for CTHREAD (Maximum concurrent threads) ?

Shaun

-----Original Message----- From: Tony Hurlston [mailto:Tony.Hurlston@ZURICH.CO.UK] Sent: Friday, 19 January 2001 20:39 To: DB2-L@RYCI.COM Subject: Connections (not threads) running out



List,

I am currently experiencing connection problems (IDBACK) with a DB2 V5 System.

The problem centres around Omegamon reporting that 99% of all background connections are used (IDBACK=1200) yet the display of Active Connections totals around 100. [...] 23005 59 21_Re: Using REXX in QMF14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 22 Jan 2001 10:46:04 +1100438_iso-8859-1 I think you have to use QMF global variables.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Schaeffer, Dave [mailto:dave.schaeffer@HONEYWELL.COM] Sent: Saturday, January 20, 2001 9:23 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Using REXX in QMF



We are a QMF v3.3 shop running on a OS/390 system. [...] 23065 148 18_Re: Update a table14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 22 Jan 2001 10:56:52 +1100440_iso-8859-1 it might be safer to add a WHERE EXISTS (SELECT 1 FROM STOCK_PARTS S WHERE P.FACILITY = S.FACILITY AND P.CATALOG_ID = S.CATALOG_ID AND P.Q_LEVEL = S.PRE_Q_LEVEL )

to the update statement - to cater for situations where there isn't a STOCK_PARTS row.

Also, my tests indicate that method 1 (... SET ((P.REORDER_POINT, P.TARGET_MAXIMUM) = (SELECT S.STOCK_MIN, S.STOCK_MAX ...) seems to work with the May refresh. [...] 23214 78 43_Re: ADD CONSTRAINT on DB2 UDB V6 for OS/39014_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 22 Jan 2001 11:03:52 +1100292_iso-8859-1 Regular expressions are not available on DB2 for OS/390 up to V6. Who (well, I can guess some names) knows what V7 and beyond might bring? Even if they were, ranges might work a little differently: in EBCDIC 'z' occurs before 'A'. But, being unable to test, I could be wrong. [...] 23293 16 25_Re: Sync of two Databases7_Sri Sri20_sri_db2l@HOTMAIL.COM31_Sun, 21 Jan 2001 22:51:23 -0600345_- Rick, the rc/migrator does use the source allocation but is there a way by which only the used dasd space can be considered instead of the allocated space. This is to avoid wasting space because the target is going to be readonly. Can adrdssu dataset copy facility be used to copy the vsam clusters from the source to target. Thanks, Sri [...]