1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2004, week 1 2 51 19_DB2 Index Question.11_Prasad Mani22_Prasad_Mani@SATYAM.COM30_Wed, 1 Sep 2004 15:00:26 +0530605_iso-8859-1 My shop runs DB2 V6.1 in OS390 V2.8 environment.

In one of the critical DB2/CICS application we are facing performance issues mainly with the increasing SYNC I/O response times for the non-terminal transactions. CA-Platinum detector analysis shows that the application INDB2 time has increased.

I have few questions pertaining to this. 1) We currently have 2 index per table standard in our shop.We will go for more than 2 indexes only if any new heavily used SQL accessing the table is added.Due to this we have maximum of 3 or 4 indexes per table and hance many of our SQL [...] 54 47 58_Cobol Stored Procedure Decimal Out parms and loss of scale11_SL DB2 DBAs32_listserver_idug@STANDARDLIFE.COM30_Wed, 1 Sep 2004 12:23:35 +0100443_US-ASCII We've just noticed quite a bizarre phenomenen with our DB2 V7.2 os/390 Cobol stored procedures. When using Windows based tools like SPB and WSAD to run Cobol SPs which have a final OUT parm of Decimal(x,y), we are losing the scale digits ie value 23.45 is returned as 23.00. Displays in the code show that the scalar digits are present at the conclusion of the Cobol program. They appear to be lost during return to the invoker. [...] 102 99 47_Re: Puzzling LOAD logging behavior - Any ideas?13_Pearson, Eric19_Eric.Pearson@53.COM30_Wed, 1 Sep 2004 07:47:31 -0400480_iso-8859-1 The number of records written correspons approximately to: (1 for table plus 1 per index) * a fudge factor = about 1.1.

That part I understand. What puzzles me is that the table with row len 170 averages 119 log rec size, but the table with row len 293 averages 527 bytes logged. It makes sense for the average log record size to be a bit smaller than the row len, but what about the one which has log records way bigger than row len? How is this possible? [...] 202 18 30_Re: visual explain v8 problem.13_Johnny Mossin24_johnny.mossin@NORDEA.COM30_Wed, 1 Sep 2004 06:51:36 -0500384_- Just a comment.

I have just tried to use the VE v 8.1 and got the same sqlcode=-206 error on some static sql from a package. The problem is that the plan_table is missing some of the new columns (here 'table_type'). We have actually removed the columns from our normal plan_tables, because the IBM EEE tool got an error when the new columns was added to the plan_table. [...] 221 35 32_DSN command processor hanging...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Wed, 1 Sep 2004 05:02:16 -0700513_us-ascii We have a strange situation here. All the commands I give through the DB2 command processor (DSN commands like -DISPLY, -STOP etc. issued in batch using IKJEFT01) or the same command issued interactively through DB2 Interactive are "hanging". The commands seem to be executing fine (the output is displayed as DISPLAY completed or STOP DATABASE normal completion etc.) but the control is not returned back. I have to cancel my TSO session (or cancel my job in case the command was issued in batch). [...] 257 329 38_Re: Rows Updated by a Stored Procedure12_Norma Mullin18_mullinn@US.IBM.COM30_Wed, 1 Sep 2004 08:08:26 -0400322_US-ASCII Ed, as Dave suggested, it is difficult to see why you are getting a -1 without knowing how you are invoking the Get Diagnostics statement. The information below is from the DB2 Information Center , it explains the statement and it gives a couple of examples of using the statement within a stored procedure. [...] 587 37 21_Dataset expansion....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Wed, 1 Sep 2004 05:06:34 -0700339_us-ascii Used to know this pretty well, but have since forgotten.

Are DB2 tablespace VSAMs 'multi-volume enabled' by default? Assuming a tablespace defined in a storage group SG with volumes V1 and V2. If volume V1 is 100% full, will a tablespace automatically occupy an extent on volume V2 (if V2 has enough space available). [...] 625 124 47_Re: Puzzling LOAD logging behavior - Any ideas?13_Vickers, Mark22_Mark.Vickers@ANICO.COM30_Wed, 1 Sep 2004 08:18:51 -0500366_iso-8859-1 What about index leaf page splits - half of the index entries could be moved to a new page. I would imagine that would all be logged too.

-----Original Message----- From: Pearson, Eric [mailto:Eric.Pearson@53.COM] Sent: Wednesday, September 01, 2004 6:48 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Puzzling LOAD logging behavior - Any ideas? [...] 750 75 25_Re: Dataset expansion....13_Michael Ebert18_mebert@AMADEUS.NET30_Wed, 1 Sep 2004 15:22:02 +0200409_US-ASCII Hi Raquel,

yes to both points. Re point 2, DB2 extension is completely independent of normal VSAM extension. The VSAM PRI/SECQTY have nothing to do with DB2 PRI/SECQTY used; also the extents DB2 requests may not be the same as the PRI/SECQTY specified by the user - I've described the rules once or twice some time back; it's a complicated set that depend on track- and pagesize as well. [...] 826 95 25_Re: Dataset expansion....12_michael bell21_mbell11a1@VERIZON.NET30_Wed, 1 Sep 2004 08:23:18 -0500360_Windows-1252 Depends VCAT defined datasets use different logic and I don't remember whether priqty/secqty on the second volume was part of it. I know a VCAT defined dataset has to have multi-volume manually added. (of course the reason I know this is syslgrnx ran out of extents on me and the catalog/directory is the only VCAT datasets anyone has left). [...] 922 55 30_Re: DB2 V Online REORG and NPI13_Mark Grundigz24_markgrundigz@HOTMAIL.COM30_Wed, 1 Sep 2004 09:08:41 -0500458_- Rama,

We faced similar a situation while performing online REORG of large partitions using the IBM REORG utility. In our experience, REORGing the NPIs prior to the tablespace partition REORG only made a small difference. I must admit that it can't hurt. One thing you might want to watch out is for DSNB319A messages in the DB2 Logs. During the BUILD2 phase, we experienced extremely long delays when there was a shortage of buffer pool space. [...] 978 53 47_Re: Puzzling LOAD logging behavior - Any ideas?9_Agus Kwee19_askwe@OPTONLINE.NET30_Wed, 1 Sep 2004 10:50:17 -0400545_iso-8859-1 Eric,

If you look in Chapter 37. DSN1LOGP of the DB2 Utility Guide and Referefence Manual, there is a sample of the output of the log records printout. In that example you will see that all log record will have a log record header of 38 bytes, and for update operation the undo and redo images are combined in a single log record. This should explain the larger log record sizes compared to the data row sizes of the table. As for log record size that is shorter than the data row size, I understand that on an update DB2 [...] 1032 102 25_Re: Dataset expansion....0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Wed, 1 Sep 2004 08:11:36 -0700371_- I don't know what the parameters are for moving extents to a second volume but, I had a case where the secqty was larger than the free space on the file's volume so instead of allocating the next extent on the stogroups second volume DB2 created a new/second vsam file on the second volume. That surprised me and now the tablespace is way over allocated. cliff:-) [...] 1135 77 47_Re: Puzzling LOAD logging behavior - Any ideas?13_Pearson, Eric19_Eric.Pearson@53.COM30_Wed, 1 Sep 2004 11:14:07 -0400363_iso-8859-1 Things are getting curiouser. The log records for the DBID/OBID for the tablespace and index objects have appropriate lengths. What appears to throw off the average length is a great number of UNDO/REDO records for DSNDB01.SYSUTILX created in behalf of the LUWID assiged to this LOAD. Many of these log records have a length of X'1006' (D'4102'). [...] 1213 61 18_LOB PGM Processing13_Robert Tilkes22_tilkesr@NATIONWIDE.COM30_Wed, 1 Sep 2004 10:12:14 -0500581_- Environment: Z/os DB2 V7

These are Operational tables not Warehouse tables

At our shop we are currently in the process of developing an application that is planning on using LOBs greater than 32K. We are aware that we can not use the LOAD, the UNLOAD, and the REORG unload external due to utility constraints. We are looking at design and how the program should do the transformation of the data, inserting of the data, and finally the extraction of the data. We have estimates that we will be appox. inserting 750,000 to 1M rows of data nightly and running a [...] 1275 61 30_Re: DB2 V Online REORG and NPI17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Wed, 1 Sep 2004 10:27:59 -0500470_iso-8859-1 Greetings Rama,

If you don't mind a blatant vendor plug, your environment/situation is exactly the type that we optimize BMC Reorg Plus for DB2 to run in. We excel in very large spaces and (I'm sure you are aware) have NO BUILD2 Phase.

I'll avoid the list of features part of this plug, but would be happy to discuss with you how Reorg Plus for DB2 can help. Please feel free to contact me directly or call our support line with questions. [...] 1337 62 47_Re: Puzzling LOAD logging behavior - Any ideas?13_Michael Ebert18_mebert@AMADEUS.NET30_Wed, 1 Sep 2004 17:24:46 +0200401_US-ASCII What is curious about that? The SYSUTILX rows have to contain the entire LOAD statement among other things, which can easily be some kB. There will also be SYSLGRNX and SYSCOPY log records. When I was mass-REORGing a SAP subsystem, I found out that every Online REORG of an empty, inactive TS created about 1MB of log records, so utilities are middlin' expensive in terms of overhead... [...] 1400 82 23_Re: DB2 Index Question.24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Wed, 1 Sep 2004 08:38:37 -0700310_us-ascii Having a large number of indexes on a table is usually only a problem for tables with a large amount of insert and delete activity, since each insert or delete needs to update all defined indexes. Update activity will only affect the index if one or more of the indexed columns is being updated. [...] 1483 34 30_Re: visual explain v8 problem.15_Patrick Bossman18_bossman@US.IBM.COM30_Wed, 1 Sep 2004 10:51:38 -0500549_- Hello, 1) The Visual Explain should catch the missing column and provide a pop-up which indicates the PLAN_TABLE should be upgraded. We're aware of this issue and are looking into why the pop-up doesn't occur.

2) Visual Explain V8 connected to V7 requires the 51 column V7 format. You should probably open a problem with IBM EEE on the issue regarding problems with 51 column PLAN_TABLE format. Otherwise that issue may not get resolved for you. In the meantime, you could build explain tables under another qualifier for use with VE. [...] 1518 73 30_Re: visual explain v8 problem.0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Wed, 1 Sep 2004 09:23:35 -0700398_- Patrick, thanks for the feedback.

You do get a pop-up if you go to "Enable Visual Explain" window first and enter a database tablespace created as older plan table.

In my case I had just create a plan table for my test table using a Zos v7 sample in batch. I then went directly to the "Tune SQL" window. I eventually stumbled into the "Enable.." window and figured things out. [...] 1592 71 30_Re: visual explain v8 problem.0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Wed, 1 Sep 2004 09:27:31 -0700398_- Patrick, thanks for the feedback.

You do get a pop-up if you go to "Enable Visual Explain" window first and enter a database tablespace created as older plan table.

In my case I had just create a plan table for my test table using a Zos v7 sample in batch. I then went directly to the "Tune SQL" window. I eventually stumbled into the "Enable.." window and figured things out. [...] 1664 34 27_DEC15 and DEC31 implication15_Daniel Cremieux17_c300501@YAHOO.COM30_Wed, 1 Sep 2004 11:27:54 -0500534_- Dear all, I've just discovered that we use the ibm supplied dsnhdecp module at the pprecompiler step (thank you very much to message dsnh527i !) and didn't know that for years !!! fortunately the ccsid is the same as the ibm default (500) , i've checked the difference between our supplied value and the ibm default value , and the only difference is that we (want to ) use DEC31 instead of DEC15 (i don't really know who set it to this value, probably the first , gone, DBA) , the default , and the real parameter used in all [...] 1699 142 22_Tuning Content Manager11_April Wells17_AWells@CSEDGE.COM30_Wed, 1 Sep 2004 11:41:56 -0500525_iso-8859-1

Okay, I have read the Admin performance manual over... and it is conceptually very much like tuning any other database.

But how do I tune content manager when I have 10 links in a chain and everyone is pinning the blame on the last link or two?

We see extreme degradation of response after 300,000 documents are in the system. That should tell me indexes or statistics are at fault. But I have run runstats and it only provides minimal improvements and the indexes should be being used. [...] 1842 29 52_RTS table SYSIBM.TABLESPACESTATS columns not updated3_Joe16_tzs61x@YAHOO.COM30_Wed, 1 Sep 2004 10:21:25 -0700897_us-ascii Columns REORGLASTTIME and COPYLASTTIME in RTS table SYSIBM.TABLESPACESTATS did not get updated even though a REORG and a IMAGECOPY are performed against that tablespace. The STATSINT in DSNZPARM is set to 30.

Thanks













__________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1872 93 28_Launching a stored procedure18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Wed, 1 Sep 2004 14:15:27 -0400537_US-ASCII Wonder if anyone could make a suggestion here. We're running DB2 v7 OS/290 2.10 . Our application groups are starting to use Stored Procedures and they need a way to 'kick them off' if you will. Right now we (dba's) have DB2 Connect v7.2 Personal Edition on our desktops that has the Stored Procedure Builder in it and it allows us to RUN a stored procedure. The clients do not want this, they want a way to run the stored procedure when they want to and not have to call us. They do not want DB2 Connect PE either. Anyone [...] 1966 151 32_Re: Launching a stored procedure14_Allen, Susan A24_susan.a.allen@BOEING.COM30_Wed, 1 Sep 2004 11:23:30 -0700474_iso-8859-1 my users have no idea they are running Stored procedures; they interface their application from a web page my DBA uses REXX I tested my sps with a COBOL program

I am sure there are many other ways to invoked a stored procedure

- Susan

-----Original Message----- From: Whittaker, Stephen [mailto:stephen.whittaker@PGNMAIL.COM] Sent: Wednesday, September 01, 2004 11:15 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Launching a stored procedure [...] 2118 48 56_Re: RTS table SYSIBM.TABLESPACESTATS columns not updated17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Wed, 1 Sep 2004 14:47:16 -0500619_iso-8859-1 Hi Joe,

Regardless of your STATSINT value, if you are willing to STOP your application space then this will cause any cached Real-Time Statistics to be externalized to the DSNRTSDB.

STOP DB(DSNRTSDB) has the same affect but obviously has grander consequences.

That said, I don't think I've ever seen these values NOT get externalized by the utilities themselves. Keep in mind that IBM docs the fact that if the RTS function should fail for a utility then the utility will still end RC=0 (or whatever it would have been anyway). I'm not certain what events might make the RTS update [...] 2167 20 32_Re: Launching a stored procedure17_Chris Worthington20_cworthi@AGRIBANK.COM30_Wed, 1 Sep 2004 14:46:09 -0500458_- It's really "low-tech" but you can call a stored procedure through MS Access by means of a Pass-Through-Query. We found it useful since in our shop, just about everyone already had Access on their desktops. All you need to do is code the CALL statement along with any input parms as necessary. This worked reasonably well for us for several years until we developed our own Browser based interface to view sp documentation and execute procedures too. [...] 2188 140 29_Re: Changed Insert behaviour?15_Marcel Harleman25_marcel.harleman@HCCNET.NL30_Wed, 1 Sep 2004 22:22:30 +0200500_us-ascii Michael,

this is funny.

I don't know whether it has anything to do with it, but we have a problem with our DB2 performance monitor (Insight/DB2): the function MIN(eventtime) doesn't give back any results. CA Support told me this had something to do with the storeclock-value: after 22 august 2004 that value passed some boundaries if I understood well and as a result the functions in the performance monitor that did something with datetime-values gave peculiar results. [...] 2329 76 30_Re: DB2 V Online REORG and NPI12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 1 Sep 2004 17:12:30 -0500403_- Any formula would need to recognize the difference in V7, with improved BUILD2 performance and parallelism (ref. section 6.5.2 of SG24-6129, V7 Performance Topics). I checked in the hall quickly, and got an asnwer of "I don't think so." to the question about a formula.

Roger Miller

On Tue, 31 Aug 2004 17:00:58 -0400, Friedman, Avram (IT) wrote: [...] 2406 44 51_Re: Can you access IMS from a DB2 Stored Procedure?12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 1 Sep 2004 17:16:02 -0500708_ISO-8859-1 For DSNAIMS, a stored procedure to invoke IMS transactions and commands, try APAR PQ77702.

Roger Miller

On Fri, 27 Aug 2004 22:43:34 +0200, Schiradin,Roland HG-Dir itb-db/dc wrote:

>I believe there is an equivalent interface for IMS called DSNAIMS like DSNACICS for CICS > >Roland > >-----Ursprüngliche Nachricht----- >Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Tom Glaser >Gesendet: Freitag, 27. August 2004 21:38 >An: DB2-L@WWW.IDUGDB2-L.ORG >Betreff: Can you access IMS from a DB2 Stored Procedure? > > >Hi, > >I have an application that would like to access IMS data from a unix server. I know there [...] 2451 79 55_Re: Identify DB2 Subsystem Name In Application Programs12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 1 Sep 2004 17:35:13 -0500434_- As you noted, in other words, this technique is not an interface. So when it breaks, you get to keep the pieces and change your code. In V8, there is a lot of information that you can get with the new GET DIAGNOSTICS statement, and some that can be retrieved with the GETVARIABLE function, including: DATA_SHARING_GROUP_NAME, SYSTEM_NAME, PACKAGE_NAME, PACKAGE_SCHEMA, PACKAGE_VERSION, PLAN_NAME, SECLABEL, CCSIDs and VERSION. [...] 2531 123 25_What else uses SDSNEXIT ?0_16_khampto1@TXU.COM30_Wed, 1 Sep 2004 18:04:40 -0500649_us-ascii After reading Daniel's post, I checked our setup, and have discovered that we, too are using the IBM-supplied DSNHDECP in our pre-compiles, because we do not our SDSNEXIT library concatenated in, which contains our customized version of DSNHDECP. Our SDSNEXIT library also contains our Security and Signon exits, DSN3@SGN, DSN3@ATH, DSNX@XAC, DSNACICX, all created by the DSNTIJEX installation job, and some sample programs created by the DSNTEJ1 installation job. I know it is allocated by all the DB2 regions and the WLM stored procedures address space, but other than the precompiler step, what other user jobs need to allocate the [...] 2655 49 32_Re: Launching a stored procedure0_18_IWANT2BEME@AOL.COM28_Wed, 1 Sep 2004 20:16:44 EDT744_US-ASCII The Unix/Java side of our shop developed a GUI front end to test the stored procedures via DDF. Otherwise, COBOL programs were developed to call them and record their results.

I'm curious; why they do not want to use Stored Procedure Builder?

Jacquie

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 2705 116 31_Re: DB2 V7 Online REORG and NPI21_Naidoo, Rama [IBM GS]28_Rama.Naidoo@TEAM.TELSTRA.COM30_Thu, 2 Sep 2004 10:54:07 +1000556_US-ASCII Thanks to all for responding to my question.

We have tried all the following and non of them are satisfactory. REORG the NPI followed by REORG the partiton. This did not improve the elapsed time of the BUILD2 phase. It takes about 4 hours for the BUILD2 phase. We cannot drop the NPI, REORG and REBUILD the NPI. This will cause too much outage. We tried to REORG the whole tablespace to avoid the BUILD2 phase but this took too long (26hrs) and the LOGAPPLY could not catchup. We had to cancel the job. We tried to REORG the partition [...] 2822 80 23_Re: DB2 Index Question.12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 1 Sep 2004 19:12:28 -0700485_us-ascii Why have a fixed limit of number of indexes per table? The impact of indexes totally depends upon the UPDATE/DELETE/INSERT activity on the table. If only SELECTS, having more indexes is zero CPU impact, excluding extra reorg time, of course. But extra indexes can make for very significant CPU savings on the SQL processing. Most shops vary from one index per table to as many as 15 to twenty indexes, with an average of about two but a median of probably of about four. [...] 2903 182 29_Re: What else uses SDSNEXIT ?15_Marcel Harleman25_marcel.harleman@HCCNET.NL30_Thu, 2 Sep 2004 06:49:21 +0200441_us-ascii Kirk,

in some setups the installation procedure asks to specify a SDSNEXIT-library. So check your setups ...

Personnaly I don't believe there is a real need to separate the modules in SDSNEXIT from the ones in SDSNLOAD unless you create one by wanting different setups in security-exits and application programming variable-settings while using only one basesoftware-set (SDSNLOAD) for a group of DB2 subsystems. [...] 3086 189 29_Re: What else uses SDSNEXIT ?0_20_pmclaren2@CSC.COM.AU30_Thu, 2 Sep 2004 15:47:10 +1000617_us-ascii With the advent of PQ56697 it has created a few problems for some installations...one I support has multiple DB2's all running from the linklist, but the pre-compiles have no STEPLIB, so have always picked up the IBM supplied DSNHDECP.

After PQ56697, choices now are to add the subsystem specific DSNEXIT lib to the precompile (ideal solution but the Endevor people are not happy) or just copy 'any' local DSNHDECP into the linklisted SDSNLOAD lib to replace the IBM one. The DB2 subsystems will still refer to their own, but the only difference (fortunately) with all the DSNHDECP's in the same [...] 3276 34 13_Dataset sizes11_Mick Norman22_micksdb2@TISCALI.CO.UK30_Thu, 2 Sep 2004 09:20:40 +0100484_iso-8859-1 On OS/390 system running DB2 V6 I have a segmented tablespace that currently resides on 2 DB2-managed datasets (*.A001 and *.A002). A001 is full at 2 Gb. Am I right in thinking that DB2 will allocate further datasets (*.A003 etc.) as the table grows as long as the formula (Primary quantity + (Secondary quantity * maximum no. of extents - 1)) is greater than 2 Gb? The Stogroup is SMS-managed. The maximum no. of extents is dependent on the DFSMS version being used. [...] 3311 99 17_Re: Dataset sizes15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Thu, 2 Sep 2004 10:40:02 +0200403_iso-8859-1 Yes, you are right. DB2 will allocate up to 32 dataset for a total of 64 GB, wich is the maximum size of a non partitioned tablespace.





Mauro Moschelli SanPaoloIMI S.p.A.

Direzione Macchina Operativa Integrata F. M. - Sviluppo Sistemi - Database C.so Savona 58 - 10024 Moncalieri (TO)

Phone : +39 011 555.5809 E-mail : mauro.moschelli@sanpaoloimi.com [...] 3411 47 32_Re: Launching a stored procedure18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Thu, 2 Sep 2004 06:52:37 -0400679_US-ASCII THANKS TO ALL who responded to my post. I got some really good feedback and I really appreciate it.

Thanks again: --Steve...

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Chris Worthington Sent: Wednesday, September 01, 2004 3:46 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Launching a stored procedure



It's really "low-tech" but you can call a stored procedure through MS Access by means of a Pass-Through-Query. We found it useful since in our shop, just about everyone already had Access on their desktops. All you need to do is code the CALL statement along with any input [...] 3459 24 32_Re: Launching a stored procedure12_Kalis, Frank23_Frank.Kalis@PROACTIV.DE30_Thu, 2 Sep 2004 13:21:00 +0200437_iso-8859-1 > The major down-side to using Access is the inability to view multiple > result-sets and output parms. Feel free to contact me off-line if you > want > more info. >

Well, I 'd say this story is only half true. It depends on the Access version you have. Access 2000 and above can take advantage of ADO and its NextRecordset method. The online manual has a nice example on how to process three separat SELECT statements. 3484 18 21_Batch Run CA Detector13_Steve Mallett23_smallett@OZEMAIL.COM.AU30_Thu, 2 Sep 2004 06:35:55 -0500705_- Esteemed list,

Is it possible to run Platinum detector in Batch mode to retrieve SQL Error information e.g. -805s, timeouts, deadlocks, etc.

Anyone doing this sort of thing on ~z/Os db2 v7 Detector v5

tia, Steve

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3503 21 84_Re: Why would you not use "Allow Reverse Scan" as the default when creating Indexes?13_Steve Mallett23_smallett@OZEMAIL.COM.AU30_Thu, 2 Sep 2004 06:50:49 -0500326_- That's odd - I was asking an IBM consultant similar questions just last month.

He suggested there was a slight overhead but not so much that most of us wouldn't benifit from bi-directional indexes. The reason it's not a default - because there is a slight overhead and those working on the edge may not want it. [...] 3525 47 39_Accessing UDB data from mainframe Cobol13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Thu, 2 Sep 2004 06:50:39 -0500426_iso-8859-1 Hi list,

We're UDB EE V8.1 and z/os with DB2 v7.1.

I have successfully accessed UDB data from a mainframe Cobol stored procedure, so I feel like the mainframe DB2 communication tables are set up properly.

Now, I'm trying to run a batch Cobol program which needs to get data from UDB. I have compiled the program on the mainframe and bound to both the mainframe and UDB with the following: [...] 3573 50 25_Re: Batch Run CA Detector16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 2 Sep 2004 07:06:07 -0500540_iso-8859-1 Hello Steve - sure it is. Chapter 10: "Batch Reporting Parameters and Unloaded Data Tables" in the Reference Guide has all the details as well as a pointer to the sample JCL to be executed from highlvl.SRCLIB(PDTBATCH).

Kind regards Steen Rasmussen Computer Associates Senior Consultant





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Steve Mallett Sent: 2. september 2004 13:36 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Batch Run CA Detector [...] 3624 140 43_Re: Accessing UDB data from mainframe Cobol0_25_LL581@DAIMLERCHRYSLER.COM30_Thu, 2 Sep 2004 08:13:27 -0400353_US-ASCII Scott,

You didn't say in your note how you bound the Cobol Plan. While you've probably checked this already, is the Plan bound with a Package List that includes both the local and the remote Packages?

Also, (again I assume you've checked this too) are you certain that the Bind of your remote Package completed successfully? [...] 3765 199 43_Re: Accessing UDB data from mainframe Cobol13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Thu, 2 Sep 2004 07:26:59 -0500575_iso-8859-1 Never assume... I'm new to this UDB stuff.

THANK YOU! THANK YOU! THANK YOU!

That was it.

-----Original Message----- From: LL581@DAIMLERCHRYSLER.COM [mailto:LL581@DAIMLERCHRYSLER.COM] Sent: Thursday, September 02, 2004 8:13 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Accessing UDB data from mainframe Cobol





Scott,

You didn't say in your note how you bound the Cobol Plan. While you've probably checked this already, is the Plan bound with a Package List that includes both the local and the remote Packages? [...] 3965 54 11_Time format11_Mayo Arthur19_Arthur.Mayo@IRS.GOV30_Thu, 2 Sep 2004 08:30:55 -0400756_- Good Morning all,

Is there any way to sum a column defined as time? To get the correct number of hours, minutes and seconds? I can sum the individual entities within time, but would then have to take those totals for minutes and seconds for further calculations.

Thanks

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4020 47 29_Re: Compress on mainframe....14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 2 Sep 2004 22:54:22 +1000630_US-ASCII PKZIP has the "disadvantage" that is a cost product; http://www.info-zip.org/pub/infozip/ is free and will do EBCDIC-ASCII conversions. Unfortunately I see that there is now no-one supporting the zOS/OS390 port: you get what you pay for.

James Campbell

On 31 Aug 2004 at 15:36, McKown, John wrote:

> > -----Original Message----- > > From: DB2 Data Base Discussion List > > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Graeme St. Clair > > Sent: Tuesday, August 31, 2004 3:34 PM > > To: DB2-L@WWW.IDUGDB2-L.ORG > > Subject: Re: Compress on mainframe.... > > > > > > Where would one get this from? I [...] 4068 162 42_Re: DB2-L Document information... (Update)16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Thu, 2 Sep 2004 09:39:18 -0400382_iso-8859-1 Just a note

As of this morning ALL code that was recoverable from the old L-documents listserve has been copied onto the new 'Code Place' site. As previously noted during the transition to the IDUG server, attached files from the old server (l-Documents) were not moved over. That being said People have started to contribute to the new 'code place' instead. [...] 4231 31 15_Re: Time format16_Ian D. Bjorhovde23_ianbjor@MOBILEAUDIO.COM30_Thu, 2 Sep 2004 07:07:18 -0700310_us-ascii Mayo Arthur wrote: > Good Morning all, > > Is there any way to sum a column defined as time? To get the correct > number of hours, minutes and seconds? I can sum the individual entities > within time, but would then have to take those totals for minutes and > seconds for further calculations. > [...] 4263 60 25_Re: Batch Run CA Detector49_Crane, Rob --- Sr. Database Administrator --- CFS27_rob.crane@FREIGHT.FEDEX.COM30_Thu, 2 Sep 2004 09:12:52 -0500564_iso-8859-1 Some favor running it as a batch job while migrating new versions of the code into production, to prevent having to have two started tasks while you test out the new version of code. Is there any reason you don not want to run it as a started task? If all you are interested in is the SQL error exceptions(SCA) you can throttle down or eliminate the other exception tracking if you are not interested in seeing that detail. Of course your batch job has to be running when the error condition occurs in order to capture that information, regardless [...] 4324 69 15_Re: Time format15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 2 Sep 2004 16:39:59 +0200668_iso-8859-1 It probably means you have to much time. At least to much time to try strange things...

Regards,

JP

-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]namens Ian D. Bjorhovde Verzonden: donderdag 2 september 2004 16:07 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: Re: Time format



Mayo Arthur wrote: > Good Morning all, > > Is there any way to sum a column defined as time? To get the correct > number of hours, minutes and seconds? I can sum the individual entities > within time, but would then have to take those totals for minutes and > seconds for further calculations. > [...] 4394 44 15_Re: Time format15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM30_Thu, 2 Sep 2004 17:47:42 +0300418_us-ascii On Thu, 2 Sep 2004 07:07:18 -0700 "Ian D. Bjorhovde" wrote:

:>Mayo Arthur wrote: :>> Good Morning all,

:>> Is there any way to sum a column defined as time? To get the correct :>> number of hours, minutes and seconds? I can sum the individual entities :>> within time, but would then have to take those totals for minutes and :>> seconds for further calculations. [...] 4439 96 15_Re: Time format17_Michaelis, Daniel25_dcmichaelis@EORIGINAL.COM30_Thu, 2 Sep 2004 10:42:40 -0400376_- For what it's worth, I'd think that it would mean 27:05:41; in other words, 27 hours, 5 minutes, and 41 seconds... A perfectly legitimate duration for an event. Perhaps the rub is thinking of a "time" as a Date/Time rather than a duration or interval.

Dan Michaelis

Senior Software Developer eOriginal 351 West Camden Street Suite 800 Baltimore, MD 21201 [...] 4536 25 15_Re: Time format16_Dennis H Werling18_DH-Werling@WIU.EDU30_Thu, 2 Sep 2004 09:50:32 -0500360_ISO-8859-1 Sum times? Sometimes

> > Mayo Arthur wrote: > > Good Morning all, > > > > Is there any way to sum a column defined as time? To get the correct > > number of hours, minutes and seconds? I can sum the individual entities > > within time, but would then have to take those totals for minutes and > > seconds for further calculations. > > > [...] 4562 185 29_Re: What else uses SDSNEXIT ?0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Thu, 2 Sep 2004 08:01:31 -0700320_- Kirk's right you could put everything in SDSNLOAD but on the other hand its easier to keep track of your modified modules it they're isolated on their own PDS. I keep my zparms, DSNHDECP, DSN3@ exits, and remaining field procs in SDSNEXIT.

Although I did move DB2 utilities out of RUNLIB.LOAD to SDSNLOAD. [...] 4748 305 34_Re: DB2 v7.1 triggers and MQSeries14_Schaeffer Dave25_Dave.Schaeffer@BENDIX.COM30_Thu, 2 Sep 2004 11:38:08 -0400641_iso-8859-1 I would like to thank everyone that responded to my query. This is a GREAT group!!!!

Dave

-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Tuesday, August 31, 2004 10:25 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DB2 v7.1 triggers and MQSeries



Our MQ series guy understood them perfectly. He had a minor issue with some of the settings in them because the documentation wasn't as clear as perhaps it should be but once our testing showed what was happening, he was able to quickly sort out the issues. We had it up and running in just over a day from the time [...] 5054 26 15_Re: Time format16_Ian D. Bjorhovde23_ianbjor@MOBILEAUDIO.COM30_Thu, 2 Sep 2004 08:45:13 -0700306_us-ascii Michaelis, Daniel wrote:

> For what it's worth, I'd think that it would mean 27:05:41; in other words, > 27 hours, 5 minutes, and 41 seconds... A perfectly legitimate duration for > an event. Perhaps the rub is thinking of a "time" as a Date/Time rather > than a duration or interval. [...] 5081 35 62_Re: Cobol Stored Procedure Decimal Out parms and loss of scale11_SL DB2 DBAs32_listserver_idug@STANDARDLIFE.COM30_Thu, 2 Sep 2004 17:09:25 +0100598_US-ASCII Peter

Nothing sexy I'm afraid - old fashioned TSO/ISPF - we started with Cobol but now we're onto SQL/PL so we can bring our baseball cap wearing Java developers into the fold writing data services. For SQL/PL we have started with Stored Procedure Builder but are crossing over to Websphere Studio now. These workstation tools are also useful for developing Cobol SPs in that they allow for quick and easy testing. We intend to use both languages. SQL/PL on os/390 platform is quite limited so it is useful where we require simple SPs and do not have Cobol resource available. [...] 5117 279 29_Re: What else uses SDSNEXIT ?11_Smock, Dale22_dsmock@RANDOMHOUSE.COM30_Thu, 2 Sep 2004 11:42:37 -0500589_us-ascii A 3rd option which we use is to include the SMPE target SDSNEXIT library in the Linklist (same HLQ as SDSNLOAD), which should have your modified version of DSNHDECP instead of the default module. This provides a safety net without having to modify SDSNLOAD, will have the same values as your multiple ssid.SDSNEXIT datasets, and will be maintained by any SMPE work that requires recompile/relink of DSNHDECP. It also keeps the Endevor people happy. It is still up to you to keep this module in sync with those in the ssid.SDSNEXIT, but allows for changes to be tested with a [...] 5397 96 34_Re: DB2 v7.1 triggers and MQSeries33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Thu, 2 Sep 2004 19:10:14 +0200558_iso-8859-1 Myron, we had problems and IBM raised APAR PQ83572.

Another issue was the binary upload of AMT files will append low-values at the end and if your download them bakc to the PC the AMITool complains about it. You have to change the AMT file before you could start AMItool.

Roland



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Myron Miller Sent: Wednesday, September 01, 2004 4:25 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DB2 v7.1 triggers and MQSeries [...] 5494 23 35_Searching for words in a text field4_Kals22_teldb2kals@BIGPOND.COM30_Thu, 2 Sep 2004 13:45:12 -0500275_- Hi,

What is the best way to search for occurrences of a lookup list of words in a text field ? I have a table containing a list of about 100 words. I would like to search for the existence of any of these words as a substring in a character field of length 20. [...] 5518 75 39_Re: Searching for words in a text field14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Thu, 2 Sep 2004 15:09:31 -0400331_- Not sure if this is what you want, but here is a crude solution.

Say you have a table: #db2 "select * from halu"

ID NAME ----------- ---------- 6 Jersey 4 Boston 1 nyc

3 record(s) selected.



Now you want to get all the rows from above table where NAME has substrings like 'ers' and 'ost'. [...] 5594 97 18_DB2 Connect V7 EOS12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Thu, 2 Sep 2004 16:36:15 -0400426_iso-8859-1 I've been looking at the product life cycle page at

http://www-1.ibm.com/support/docview.wss?rs=71&uid=swg21168270



I could have sworn that earlier this week it said DB2 V7.2 EOS was 09/01/2004 but that it was being EXTENDED.

And today it says EXTENDED and 09/30/2004, so I don't know whether that means support was extended until 09/30, or it is going to be extended beyond 09/30. [...] 5692 125 22_Re: DB2 Connect V7 EOS33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Thu, 2 Sep 2004 23:01:33 +0200381_iso-8859-1 I can only say it wasn't the first EOS has changed. We switch last year to V8 now FP6 but the clients are still V7.

Roland

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Hylton Tom P Gesendet: Donnerstag, 2. September 2004 22:36 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: DB2 Connect V7 EOS [...] 5818 50 22_Re: DB2 Connect V7 EOS12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 2 Sep 2004 14:54:03 -0700319_us-ascii I wish I knew. I have a critical problem with V8 Development center that prevents it from working at all. Consequently, we can't deploy V8 until that issue has been resolved. So I've been pressing IBM very severely about the EOS and this issue.

Myron --- Hylton Tom P wrote: [...] 5869 55 84_DB2-L-Documents migration - Suggestion archival date of Oct 1st - Feedback requested10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Thu, 2 Sep 2004 18:25:10 -0400944_us-ascii To: DB2-L-Document contributors since Dec 2002 DB2-L@www.idugdb2-l.org,mebert@amadeus.net,royr@uclink.berkeley.edu ,ron.root@CPA.STATE.TX.US ,yassini@BEZEQINT.NET,Aurora.DellAnno@BANKOFAMERICA.COM,db2_dba@BELLSOUTH.NET ,Johnny.Wilder@MOTION-IND.COM,ndt98@FREE.FR,mscarpa@CESVE.IT,Steve.Runtsch@US.FORTIS.COM ,Tina.Hilton@ARVATOSYSTEMS.COM,pgunning@gunningts.com



First I would like to send a huge Thank You to all the DB2-L-Documents contributors over the years!

As we all probably already recognize, listserv is a great place for conducting collaborating conversations. However, it's been less than desirable for long term storage of DB2 snippets. However, wait no longer! The IDUG Insider Code Place is now available! You can read about how to access / submit to the IDUG Insider 'The Code Place' via this month's IDUG Insider E-Bulletin which is located on the IDUG News: http://www.idug.org/html/home.asp [...] 5925 14 55_Re: Identify DB2 Subsystem Name In Application Programs14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 2 Sep 2004 19:09:22 -0500658_- In addition to other techniques, you can do an IFI READS for an IFCID. (I like 234 because it is always available.) The IFCID header contains the DB2 subsystem-id.

James Campbell

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 5940 71 20_Data Sharing problem12_Isaac Yassin20_yassini@BEZEQINT.NET30_Fri, 3 Sep 2004 08:36:49 +0200328_us-ascii Hi,

We have a strange situation while migrating to a D.S. (done on the past in 4 other subsystems without any problem) The jobs ran fine, but in the BSDS there is no mention of the group definition, (DSNJU004 at end of mail) even though we See the LRSN info in the checkpoint queue. Any idea is welcome :-) [...] 6012 46 15_Access path....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Thu, 2 Sep 2004 22:59:57 -0700331_us-ascii Completely taken by surprise on this one. A table T1 has an index X1 on COL1, COL2 and COL3. Following is the query:

SELECT COL2 FROM T1 WHERE COL2 = ?

The above goes for a tablespace scan. Thought it should have gone for a non-matching index scan.

Also another query:

SELECT COL1 FROM T1 [...] 6059 74 39_Re: Searching for words in a text field13_Michael Ebert18_mebert@AMADEUS.NET30_Fri, 3 Sep 2004 08:33:54 +0200560_US-ASCII The quick-and-dirty solution would be to code about 100 different OR...LIKE conditions. Ok if it is a rare query or the table with the text fields isn't too big. If it is expected that this query will take up a significant amount of resources, then the best way would be to treat the 100 search words as a kind of "regular expression" and build a finite-state machine from it. If the words are simple strings and do not contain patterns, then you'll never even need to backtrack and can check for matches in a single pass through the text field, [...] 6134 74 15_Re: Time format13_Michael Ebert18_mebert@AMADEUS.NET30_Fri, 3 Sep 2004 08:46:38 +0200603_US-ASCII Of course 27:05:41 is a valid time (as in "Est. time remaining 27:05:41"). Remember that there are two types of time, time duration (no problem there with any value) and time-of-day. On Earth, you cannot have a time-of-day that exceeds 24:00:00 (or is it 23:59:59.999..?); but how about Mars (let alone Venus)? Adding two time durations is perfectly ok, as is adding a time duration to a time-of-day (in which case you'd probably take the result modulo 24 hrs). Actually time-of-day is just a special case of a time duration, namely the duration since last midnight on a standard earth... [...] 6209 99 51_DELETE Performance - was: Changed Insert behaviour?13_Michael Ebert18_mebert@AMADEUS.NET30_Fri, 3 Sep 2004 09:43:16 +0200568_US-ASCII Hmm... I don't think that is involved. In any case, after the last REORG, the TS went below 2GB again, I deleted the extra datasets, and the growth behaviour is now what it was before. Maybe the change occurs only if a 2nd dataset is involved...

The critical issue is the elapsed time anyway. I could monitor the process while it was running and found that the Summarisation step took about 10 mins - more than I expected, but not critical. The costly statement is the DELETE FROM T1 WHERE DTPROCESSDATE=. This step took 1 hr 28' to delete 4M [...] 6309 108 24_Re: Data Sharing problem11_Mike Turner29_michael_turner@COMPUSERVE.COM30_Fri, 3 Sep 2004 10:01:50 +0100400_us-ascii Hi Isaac

Looking at the output, the DSNJ203I message says the GROUP operation failed. I think you get the LRSN values because the previous DATASHR ENABLE worked OK. The big question is why did the GROUP operation fail. The syntax looks OK. I suggest you check the Group Name, Member Name, and Member ID to make sure that you are not duplicating a member that is already defined. [...] 6418 73 19_Re: Access path....0_19_mike.holmans@BT.COM30_Fri, 3 Sep 2004 10:20:10 +0100450_iso-8859-1 Hi,

Are those the actual queries (at least in form), or are you selecting one or more non-indexed columns from the tables?

Mike Holmans Lead Database Administrator BTExact In-Life Services mike.holmans@bt.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Raquel Rodriguez Sent: 03 September 2004 07:00 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Access path.... [...] 6492 117 19_Re: Access path....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Fri, 3 Sep 2004 02:32:09 -0700350_us-ascii These are actual queries (only table/column/index names have been changed).

Was expecting an index only non-matching index scan in both the cases listed below...

Could the listers try this out and verify that this accesspath is at least consistent in all shops (if not fully reasonable). May be we are missing some PTF... [...] 6610 137 19_Re: Access path....12_Isaac Yassin20_yassini@BEZEQINT.NET30_Fri, 3 Sep 2004 12:37:13 +0200469_US-ASCII Hi

Any chance that the tables uses less pages then index nleaf ? (may happen with compressed tables)

Isaac Yassin



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raquel Rodriguez Sent: Friday, September 03, 2004 11:32 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Access path....



These are actual queries (only table/column/index names have been changed). [...] 6748 172 19_Re: Access path....13_Mertens, Bart18_bart.mertens@CZ.NL30_Fri, 3 Sep 2004 11:49:59 +0200512_- Both queries are using a index only non-matching index scan. I'm on V7 z/OS.

Access paths may vary due to card, clusterratio, etc...



met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl



-----Oorspronkelijk bericht----- Van: Raquel Rodriguez [mailto:raquel_rodriguezus@YAHOO.COM] Verzonden: Friday, September 03, 2004 11:32 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: Re: Access path.... [...] 6921 200 19_Re: Access path....15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Fri, 3 Sep 2004 11:53:15 +0200655_us-ascii Are the columns you read varchar? In this case, unless you set a ZPARM, the tablespace must be accessed to get the real lenght of the column.





Mauro Moschelli SanPaoloIMI S.p.A.



-> -----Original Message----- -> From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On -> Behalf Of Isaac Yassin -> Sent: Friday, September 03, 2004 12:37 PM -> To: DB2-L@WWW.IDUGDB2-L.ORG -> Subject: Re: Access path.... -> -> -> Hi -> -> Any chance that the tables uses less pages then index nleaf ? -> (may happen with compressed tables) -> -> Isaac Yassin -> -> -> -----Original Message----- -> From: DB2 Data [...] 7122 196 19_Re: Access path....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Fri, 3 Sep 2004 03:21:52 -0700296_us-ascii No compression; no Varchars defined in the table. Though I did notice that the combined length of the three columns in index is 50 bytes while the table has a reclength of 98 bytes. Probably somehow (how?) DB2 is estimating that index scan for such a large index would be costlier. [...] 7319 65 19_Re: Access path....16_Venkatesh Konnur21_vnkonnur@KCC.USDA.GOV30_Fri, 3 Sep 2004 07:18:44 -0500339_US-ASCII You have index created X on col1,2 and 3...ie in this order. For the query below where the where clasue col2 = ?, you would need to create another index sepearately on col2 or drop and recreate index X as in this order: col2,col1 and col3 and then run update statistics after creating the indexs... Hope this helps Venkatesh [...] 7385 105 22_Re: DB2 Connect V7 EOS13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Fri, 3 Sep 2004 07:40:40 -0500541_iso-8859-1 From everything I'm being told, 9/30/04 is the actual official end of support date for V7 of Connect. I would be very interested to hear what others approach is towards moving off of DB2 Connect V7 and onto V8. We had good intentions of being on V8 of connect by now and have been testing various fixpacks of V8 for the past several months, but we are also finding far too many critical defects to even consider using it in a production environment. We are also pressing IBM on the support date and I had a hunch we were not [...] 7491 116 22_Re: DB2 Connect V7 EOS12_Martin, Paul22_Paul.Martin@ECOLAB.COM30_Fri, 3 Sep 2004 07:52:42 -0500617_us-ascii Because of the support date we moved to linux DB2 Connect V8, fixpak5 in Jan. 2004 Paul

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Vaughan, Mike Sent: Friday, September 03, 2004 7:41 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DB2 Connect V7 EOS

From everything I'm being told, 9/30/04 is the actual official end of support date for V7 of Connect. I would be very interested to hear what others approach is towards moving off of DB2 Connect V7 and onto V8. We had good intentions of being on V8 of connect by now and have been [...] 7608 90 39_Philadelphia RUG welcomes Sheryl Larsen10_Anne Stout23_anne.stout@DOWJONES.COM30_Fri, 3 Sep 2004 07:52:27 -0500684_ISO-8859-1 DELAWARE VALLEY DB2 USERS GROUP



MEETING DATE: Friday, September 17th, 2004 LOCATION: Dave & Buster’s STARTING TIME: 9:00 AM REGISTRATION: 8:30 AM - 9:00 AM COST: ADVANCE REGISTRATION: MEMBERS $30, NON-MEMBERS $40 WALK IN REGISTRATION: MEMBERS $50, NON-MEMBERS $60

AGENDA

9:00 - 09:15 Opening Remarks: Joe Carola, Chairperson DVDUG 9:15-10:30 Migrating to DB2 Version 8 Don Fowler, Little Blue Software 10:30 - 10:45 BREAK 10:45 - 12:00 Powerful SQL: Past, Present & Future (Part I) Sheryl M. Larsen, Sheryl M. Larsen, Inc 12:00 - 01:00 LUNCH: Sponsored by Little Blue Software Consultants 01:00 - 01:30 Product Demo Migration Started Kits [...] 7699 68 56_Re: RTS table SYSIBM.TABLESPACESTATS columns not updated12_Bill Shipley28_william_shipley@VANGUARD.COM30_Fri, 3 Sep 2004 08:50:12 -0400389_US-ASCII We have experienced the problem with IMAGECOPY not showing up in RTS. It was because we are using BMC's CopyPlus and they do not update RTS. When I called BMC support I was told they had no plans to include updating RTS for imagecopies.

Bill Shipley IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for OS/390 The Vanguard Group, Inc. Malvern, PA [...] 7768 141 55_Re: DELETE Performance - was: Changed Insert behaviour?12_michael bell21_mbell11a1@VERIZON.NET30_Fri, 3 Sep 2004 08:09:11 -0500555_Windows-1252 Warning - guesstimates follow. There is a threshold for buffer pool size where the size of the buffer pool is just small enough in relation to the number of pages that each buffer read, frees a buffer that the process will have to reread to process a future row. IX2 is obviously the problem index. IX1 will track closely with the delete sequence so not much reread activity there. This will be dependent on other activity in that buffer pool and the number of pages in IX2. The other issue is how long each of the sync reads took. With [...] 7910 32 38_IBM Automation tool documentation ....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Fri, 3 Sep 2004 06:16:06 -0700325_us-ascii Could someone on the list send me a 'working' link to the IBM Automation tool.

I found the following link on google; the manual (through the above link) seems to 'open' up ok, but doesn't allow me to browse (go to any other page other than the front page). It just 'hangs' and has to be forcibly closed. [...] 7943 179 32_Re: SQLCODE -817 - PLEASE HELP!!13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Fri, 3 Sep 2004 08:20:14 -0500568_iso-8859-1 Might be worth a try, but I wouldn't be surprised if you got a -925 sqlcode on the commit in the stored procedure. If I recall correctly, the commit inside the stored procedure will appear to work correctly, but the sqlcode on the SP Call statement will give a -925. From past testing with something similar we got mixed results when issuing a commit inside a stored procedure being called from CICS - if only DB2 work was being done then the commit executed successfully, but as soon as another resource (in our case, MQ) was involved then the commit [...] 8123 56 42_Re: IBM Automation tool documentation ....13_Pearson, Eric19_Eric.Pearson@53.COM30_Fri, 3 Sep 2004 09:23:57 -0400398_iso-8859-1 Raquel,

I can use that link just fine. Have you by any chance recently 'upgraded' to Adobe 6.0? That hangs for me on many IBM PDF links. The older Adobe does not.

-----Original Message----- From: Raquel Rodriguez [mailto:raquel_rodriguezus@YAHOO.COM] Sent: Friday, September 03, 2004 9:16 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: IBM Automation tool documentation .... [...] 8180 27 57_Declared temp table not found on return from link to CICS13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Fri, 3 Sep 2004 08:31:19 -0500443_iso-8859-1 Hi list,

We're z/os and DB2 v7.1

I have a web application that is calling a Cobol stored procedure. The Cobol stored procedure 'links' to a CICS program which declares a global temp table and puts rows into it. On return from the CICS program, I get a -204 (table not found) when trying to access the temp table. I have even put ON COMMIT PRESERVE ROWS when declaring the temp table, but that didn't help either. [...] 8208 45 61_Re: Declared temp table not found on return from link to CICS11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US30_Fri, 3 Sep 2004 08:45:17 -0500412_- Yes it is possible to have the CICS transacton as a separate UOW

Check the value of the synch-opts in the DSNACICS call or the exci call parm synconreturn call if coding nativly.

-----Original Message----- From: Hodgin, Scott [mailto:shodgin@SCFBINS.COM] Sent: Friday, September 03, 2004 8:31 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Declared temp table not found on return from link to CICS [...] 8254 50 42_Re: IBM Automation tool documentation ....12_Ulf Heinrich17_u.heinrich@SEG.DE30_Fri, 3 Sep 2004 15:51:11 +0200758_us-ascii You can try the bookmanager file: http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/haaugb10

or even view it online: http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/haaugb10/CONTENTS?SHEL F=&DT=20040621180145&

http://publib.boulder.ibm.com/epubs/html/haaugb10/haaugb10tfrm.htm



I have also another link for the pdf file. Maybe this one works: http://publib.boulder.ibm.com/cgi-bin/bookmgr/download/haaugb10.pdf?ACTION =SAVE&DT=20040621180145



Have a nice week end Ulf

Ulf Heinrich SOFTWARE ENGINEERING GMBH -Technical Consultant- Robert-Stolz-Strasse 5 40470 Dusseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-32 E-mail U.Heinrich@seg.de Homepage www.seg.de ager for DB2 z/OS [...] 8305 72 61_Re: Declared temp table not found on return from link to CICS13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Fri, 3 Sep 2004 08:56:17 -0500363_iso-8859-1 Well, I did have SYNCONRETURN in the stored procedures 'link' to the CICS program, but I took it off and recompile, but I still cant get to the temp table.

Is there a way I can code around this so this is all happening in one UOW?

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company shodgin@scfbins.com [...] 8378 107 88_Re: DB2-L-Documents migration - Suggestion archival date of Oct 1st - Feedback requested14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM30_Fri, 3 Sep 2004 09:02:04 -0500623_UTF-8







In the IDUG Insider E-Bulletin that Cathy mentioned, I was surprised to find a reference to "the DB2 UDB for Linux, UNIX and Windows forum category". It also mentions "the DB2 UDB for OS/390 and z/OS category of the DB2 UDB Forums":

>Pick of the Forums >Here are some of the recent postings on the IDUG Insider discussion forums, complied >by Suneel Konidala, one of the IDUG Insider discussion forum managers: >* One Open Systems IDUG insider posts the question "How do I create a new user?”, and >Sathyaram Sannasi comes to the rescue once again with the answer. Check [...] 8486 87 61_Re: Declared temp table not found on return from link to CICS11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US30_Fri, 3 Sep 2004 09:06:21 -0500451_- Did you remember to refresh the stored procedure?

-----Original Message----- From: Hodgin, Scott [mailto:shodgin@SCFBINS.COM] Sent: Friday, September 03, 2004 8:56 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Declared temp table not found on return from link to CICS



Well, I did have SYNCONRETURN in the stored procedures 'link' to the CICS program, but I took it off and recompile, but I still cant get to the temp table. [...] 8574 99 56_Re: RTS table SYSIBM.TABLESPACESTATS columns not updated15_Murley, Michael22_Michael_Murley@BMC.COM30_Fri, 3 Sep 2004 09:00:38 -0500360_- Bill,

We are currently testing a change that will enable Realtime Statistics updates in the current COPY PLUS for DB2 release, V6.5.00. We will send out a Technical Bulletin in the next several days to make that function available.

We will also send out a Technical Bulletin to enable the feature in RECOVER PLUS for DB2 at the same time. [...] 8674 114 61_Re: Declared temp table not found on return from link to CICS13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Fri, 3 Sep 2004 09:11:07 -0500565_iso-8859-1 Yes, I have refreshed the stored procedure.

So, am I correct in thinking that there IS a way to create a temp table in the linked to CICS program and retrieve the results in the calling stored procedure?

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company shodgin@scfbins.com

-----Original Message----- From: Gugel, Rich [mailto:Rich.Gugel@DOA.STATE.WI.US] Sent: Friday, September 03, 2004 10:06 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Declared temp table not found on return from link to CICS [...] 8789 23 25_DB2 Path Checker for z/OS15_Moss, William R15_MossW@AETNA.COM30_Fri, 3 Sep 2004 10:20:09 -0400315_us-ascii Does anyone have any experiences or evaluations of DB2 Path Checker ? We've had some problems with rebinds taking bad access paths and are looking for a way to be proactive instead of trying to determine the old access path and then changing catalog statistics and rebinding to get back the old path. [...] 8813 136 25_Re: UDB Lock Wait Script?16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Fri, 3 Sep 2004 10:25:42 -0400485_iso-8859-1



IDUG has established a central location where people can share code with the community. The area is called Code Place and can be accessed at http://www.idug.org/user/userlogin.asp.

Just an idea, and thanks for the offer to share











Robert Galambos

Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 V7 Database Administration robert.galambos@compuware.com [...] 8950 132 61_Re: Declared temp table not found on return from link to CICS11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US30_Fri, 3 Sep 2004 09:44:00 -0500403_- Is the CICS region registered with RRS? There is a note in the appendex of DB2 UDB for OS/390 and z/OS V7 Application Programming and SQL Guide that addresses this.



-----Original Message----- From: Hodgin, Scott [mailto:shodgin@SCFBINS.COM] Sent: Friday, September 03, 2004 9:11 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Declared temp table not found on return from link to CICS [...] 9083 213 61_Re: Declared temp table not found on return from link to CICS15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Fri, 3 Sep 2004 16:48:01 +0200435_iso-8859-1 I'm not perfectly shure of what I'm saying, but I think that even tough all SQL you do in the stored procedure and in the CICS program are all either committed or rolled back, it is not a single UOW but a distributed UOW. I think you should see two threads allocated in the DB2, one for the SP (attachment CAF or RRSAF) and one for the CICS (attachment CICS). So I don't think there is a way of doing what you want to. [...] 9297 233 19_Re: Access path....15_Aniruddha Garud23_Aniruddha.Garud@MCI.COM30_Fri, 3 Sep 2004 11:14:22 -0400517_us-ascii This is of course basic - but by any chance you are not running Runstats with just Index all? Have seen problems with it when somebody was accidentally running it. The Syscolumns statistics does not get updated. And this might make a difference while choosing index vs table.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raquel Rodriguez Sent: Friday, September 03, 2004 6:22 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Access path.... [...] 9531 291 29_Re: What else uses SDSNEXIT ?0_16_khampto1@TXU.COM30_Fri, 3 Sep 2004 10:50:53 -0500353_us-ascii Thank you for sharing the various options you all have taken. Allow me to re-phrase my original question... 1) Other than the pre-compiler, does any other batch job need to see the subsystem-specific DSNHDECP module ? 2) Other than the DB2 subsystem regions themselves, does any other batch job need to see the DSN3@ exits or fieldprocs ? [...] 9823 40 27_Informatica and DB2 on z/OS12_Raman Murali19_rmurali28@YAHOO.COM30_Fri, 3 Sep 2004 09:53:36 -0700435_us-ascii

Is there anybody on the list who can comment on performance issues with Informatica (on AIX), accessing a target database (inserts/updates) on DB2 v7 (z/OS), without PowerConnect.

Thanks

Raman





--------------------------------- Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages!

--------------------------------------------------------------------------------- 9864 254 19_Re: Access path....20_Dash, Sushanta Kumar29_sushantakumar.dash@LEHMAN.COM30_Fri, 3 Sep 2004 13:08:26 -0400571_iso-8859-1 Hi Raquel, your estimate is in right direction. To choose an access path the optimizer goes for FF,I/O and Cpu costs. While estimating the I/O time needed the optimizer founds that the I/O estimate by non matching index scan is costlier than tablepsace scan. This can be sensed from your length. Most of the time while calculating i/o for the non matchi9ng index scan the leaf pages are considered. Not the no leaf pages and leaf pages. If we go for Frank's formual say 1.7 ms for the non match index scan using the sequential prefetch and same 1.7ms for [...] 10119 115 56_Re: RTS table SYSIBM.TABLESPACESTATS columns not updated3_Joe16_tzs61x@YAHOO.COM30_Fri, 3 Sep 2004 11:55:09 -0700573_us-ascii Bill: Thanks, Bill, You got the correct answer, I used IBM REORG and the RTS tables were updated correctly. Thanks for your help. I wonder when BMC will modify their utilities to include updating RTS ? Joe

--- Bill Shipley wrote:

> We have experienced the problem with IMAGECOPY not > showing up in RTS. It > was because we are using BMC's CopyPlus and they do > not update RTS. When I > called BMC support I was told they had no plans to > include updating RTS for > imagecopies. > > Bill Shipley > IBM Certified [...] 10235 74 56_Re: RTS table SYSIBM.TABLESPACESTATS columns not updated17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Fri, 3 Sep 2004 14:21:08 -0500472_iso-8859-1 Hello All,

Reorg Plus for DB2 V6.4.00 and DASD Manager V7.1.00 (both technically not GA until September 15th but are in marketing's hands) both support DB2 Real-Time-Statistics.

Michael Murley replied earlier that we are currently testing a change which will enable Real-Time-Statistics updates in the current COPY PLUS for DB2 release, V6.5.00. We will send out a Technical Bulletin in the next several days to make that function available. [...] 10310 103 39_Re: Searching for words in a text field4_Kals22_teldb2kals@BIGPOND.COM30_Fri, 3 Sep 2004 15:09:32 -0500559_- Thanks, Abhijit. That works. I had a feeling I was missing something simple. My search wordlist was in a table, so I had to use a STRIP function for the word. LOCATE(STRIP(colname1),colname2) > 0.

Regards, Kals.

On Thu, 2 Sep 2004 15:09:31 -0400, Sinha, Abhijit wrote:

>Not sure if this is what you want, but here is a crude solution. > >Say you have a table: > #db2 "select * from halu" > >ID NAME >----------- ---------- > 6 Jersey > 4 Boston > 1 nyc > > 3 record(s) selected. > > >Now you want to get all [...] 10414 29 24_Format Elapsed Timestamp15_Tillman, Paul A25_paul.a.tillman@BOEING.COM30_Fri, 3 Sep 2004 13:27:33 -0700800_us-ascii Hello Listeners, Does anyone have nice way to format this Elapsed Timestamp result nicely? I am trying to make it easier for the user to read.

ELAPSED TS ----------------------- 7070637.000000 7054912.000000 4090809.000000 4061012.000000 50207.000000 2070920.000000 509063132.000000 509063505.000000

Thanks

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 10444 143 22_Re: DB2 Connect V7 EOS13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Fri, 3 Sep 2004 15:50:51 -0500497_iso-8859-1 We did try V8fp5 and it seemed to be working well for awhile. Our issue with this one was it was not handing autocommit correctly for jdbc applications which caused the threads to stay active and eventually get cancelled by idle thread timeout. I should probably add that this only happened when an sql statement was delimited by a semi-colon which I didn't think was all that widespread, but apparently the IBM data access beans always add a semi-colon at the end of a statement. [...] 10588 112 59_DB2 z/OS v7 - to - DB2 V7 LUW on AIX "Performance" question0_25_LL581@DAIMLERCHRYSLER.COM30_Fri, 3 Sep 2004 17:00:24 -0400575_US-ASCII Esteemed List,

It's a three-day holiday weekend here in the US, and I honestly don't expect any quick replies to this. Also, don't laugh too hard . . . :)

One of my best internal customers is migrating an application from non-DB2 to DB2 on AIX. Most of the data will end up in tables on DB2 V7 on AIX; however, the application will access the data through Stored Procedures executing in a DB2 z/OS V7 environment. The question I was asked: "What is the connection overhead for this configuration? We are concerned about the CPU cost of making and [...] 10701 324 29_Re: What else uses SDSNEXIT ?0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Fri, 3 Sep 2004 15:06:42 -0700696_- Kirk, Some third party products will look in DSNHDECP for a subsystem ID. i.e. EzPlus, CA-db2 And others use the DSN3@s. i.e. NOMAD, FOCUS, CA-dbs.



cliff:-)

-----Original Message----- From: khampto1@TXU.COM [mailto:khampto1@TXU.COM] Sent: Friday, September 03, 2004 8:51 AM To: DB2-L@www.idugdb2-l.org Subject: Re: What else uses SDSNEXIT ?



Thank you for sharing the various options you all have taken. Allow me to re-phrase my original question... 1) Other than the pre-compiler, does any other batch job need to see the subsystem-specific DSNHDECP module ? 2) Other than the DB2 subsystem regions themselves, does any other batch job need to see the [...] 11026 123 15_Re: Time format16_Tink@BLTysor.com19_tinktysor@YAHOO.COM30_Fri, 3 Sep 2004 17:00:58 -0700593_us-ascii Hi Michael,

Actually you can get into trouble adding durations. Since a duration is actually a packed decimal number, if you add 59 seconds to 59 seconds you will get 1 minute and 18 seconds!

Tink --- Michael Ebert wrote:

> Of course 27:05:41 is a valid time (as in "Est. time > remaining 27:05:41"). > Remember that there are two types of time, time > duration (no problem there > with any value) and time-of-day. On Earth, you > cannot have a time-of-day > that exceeds 24:00:00 (or is it 23:59:59.999..?); > but how about Mars (let > [...] 11150 172 15_Re: Time format13_Michael Ebert18_mebert@AMADEUS.NET30_Sat, 4 Sep 2004 08:28:30 +0200425_US-ASCII Really?? Well I'm sure IBM will say it's working as designed... I was talking about "durations" as an abstract data type anyway, not the odd DB2 version. I can't imagine anyone is seriously using that.

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





From: "Tink@BLTysor.com" @IDUGDB2-L.ORG on 03-09-2004 17:00 MST [...] 11323 139 31_Re: Informatica and DB2 on z/OS12_Martin, Paul22_Paul.Martin@ECOLAB.COM30_Sat, 4 Sep 2004 19:53:50 -0500553_us-ascii When we upgraded our AIX systems from UDB V7 to V8 the response time on Informatica (on AIX), was NOT expectable, the Informatica users wanted us to fall-back off Version 8 but we couldn't -- the problem was resolved by resetting a parm on AIX that we had to turn on when we went to UDB V7. When Informatica AIX applications connected to DB2 V7 on z/OS the transactions were taking about four times longer to run, they had to do something with the checkpoint parm on the Informatica programs, it was taking a ton of checkpoints, I think a [...] 11463 38 15_Re: Time format14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 5 Sep 2004 13:46:58 +1000573_US-ASCII Just to be picky, 23:59:60.999 can be a valid real time of day - although not in DB2 land. (But the last moment of every day is still 24:00:00.)

James Campbell

On 3 Sep 2004 at 8:46, Michael Ebert wrote:

> Of course 27:05:41 is a valid time (as in "Est. time remaining 27:05:41"). > Remember that there are two types of time, time duration (no problem there > with any value) and time-of-day. On Earth, you cannot have a time-of-day > that exceeds 24:00:00 (or is it 23:59:59.999..?); but how about Mars (let > alone Venus)? > Adding two [...] 11502 93 88_Re: DB2-L-Documents migration - Suggestion archival date of Oct 1st - Feedback requested14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 5 Sep 2004 13:46:58 +1000549_US-ASCII They are references to the Discussion Forums in the IDUG Insider part of the IDUG site.

James Campbell

On 3 Sep 2004 at 9:02, Steve Westfall wrote:

> > > > > In the IDUG Insider E-Bulletin that Cathy mentioned, I was surprised to find a > reference to "the DB2 UDB for Linux, UNIX and Windows forum category". It also > mentions "the DB2 UDB for OS/390 and z/OS category of the DB2 UDB Forums": > > > What are these forums that it is referring to? I don't think it's talking about > DB2-L, since the thread [...] 11596 51 62_Re: Cobol Stored Procedure Decimal Out parms and loss of scale11_SL DB2 DBAs32_listserver_idug@STANDARDLIFE.COM30_Mon, 6 Sep 2004 13:50:53 +0100602_US-ASCII Listers

It doesn't appear that our problems with decimal parameters are widely shared. I have a little bit more info to add now to my original definition of the problem.

My basic problem is that when Cobol Stored Procedures built on a DB2 os/390 v7 system are invoked from workstation tools like Stored Procedure Builder or Websphere Studio, we are losing scale detail for decimal out parameters at the end of the linkage ie a value of 12.34 is returned as 12.00 This doesn't happen to all such SPs; some work fine. When it doesn't function correctly, it consistently works [...] 11648 48 19_Re: Access path....33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Mon, 6 Sep 2004 08:11:13 -0500403_- On Thu, 2 Sep 2004 22:59:57 -0700, Raquel Rodriguez wrote:

I think, DB2 goes for a tablespace scan for the below queries, if one of the following is true:

1. the number of pages of the tablespace is less than the number of index leaf pages 2. the filter factor of col2 is bad and item 1 holds 3. the number of pages in the table is less than NPGTHRSH [...] 11697 85 58_IDUG 2004 Europe 11-14 October 2004 Prague, Czech Republic79_Marcel L=?ISO-8859-1?Q?=E9vy?= - IDUG Europe 2004 Conference Planning Committee24_marcel_idug_db2@YAHOO.FR30_Mon, 6 Sep 2004 08:22:52 -0500518_ISO-8859-1 Dear DB2-L member,

My name is Marcel Lévy and I'm a member of the IDUG 2004 Europe Conference Planning Committee.



Mark your calendars and make plans to attend... IDUG 2004 Europe 11-14 October 2004 Prague Hilton Prague, Czech Republic Click here for details: http://conferences.idug.org/europe/2004/index.cfm

This event promises to deliver a broad array of high-quality technical education, networking, and the practical information that you have come to expect from IDUG. [...] 11783 39 33_DB2 MAXTHREAD parameters question12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 7 Sep 2004 13:07:24 +0200920_Windows-1251 Fellow collegues,

examining the DSNZPARM’s (in DB2 for OS/390 V6) I wonder about the following two parameters: ”MAXIMUM REMOTE THREADS” and “MAXIMUM REMOTE CONCURRENT THREADS”.

What is the difference between these two parameters ?

Thanks in advance for any information you are willing to pass along.

With kind regards - mit freundlichen Gruessen, Georg H. Peter c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, ˆurope e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- In nature, nothing is ever right. Therefore, if everything is going right ... [...] 11823 347 63_Re: DB2 z/OS v7 - to - DB2 V7 LUW on AIX "Performance" question17_Michaelis, Daniel25_dcmichaelis@EORIGINAL.COM30_Tue, 7 Sep 2004 07:28:13 -0400399_- Lockwood,





I'm a fairly new DB2 DBA, so some of the comments that I'm about to make may not apply to the specific situation that you're in. I'm unfamiliar (past what the acronym stands for) with DRDA, so I don't know how it manages connections between disparate operating systems. Depending on its behavior, that might invalidate any/all of the discussion that follows. [...] 12171 112 37_Re: DB2 MAXTHREAD parameters question15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Tue, 7 Sep 2004 13:35:22 +0200602_Windows-1251 The two parameters are MAXDBAT and CONDBAT whose meaning are: MAXDBAT: Maximum number of active remote threads. Since you have a total maximum of 2000 active threads in DB2, you must leave space for CICS, BATCH, IMS etc. threads CONDBAT: Maximum number of connected remote threads. If you have the ZPARM CMTSTAT=INACTIVE, at commit time the remote thread is put in an inactive state, which convert back to active at the next request. So you can have up to 15.000 inactive threads allowing more remote client/server applications connected at the same time without reaching DB2 limits. [...] 12284 113 37_Re: DB2 MAXTHREAD parameters question15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Tue, 7 Sep 2004 14:50:04 +0200629_Windows-1251 Thanks to Bart Mertens for correcting me. The real maximum value for CONDBAT is:

150.000 inactive threads not 15.000



The two parameters are MAXDBAT and CONDBAT whose meaning are: MAXDBAT: Maximum number of active remote threads. Since you have a total maximum of 2000 active threads in DB2, you must leave space for CICS, BATCH, IMS etc. threads CONDBAT: Maximum number of connected remote threads. If you have the ZPARM CMTSTAT=INACTIVE, at commit time the remote thread is put in an inactive state, which convert back to active at the next request. So you can have up to 15.000 inactive [...] 12398 13 14_Pronounciation14_Henry L Nalven25_henry.nalven@MARRIOTT.COM30_Tue, 7 Sep 2004 08:53:17 -0500687_- Is anyone aware of any database (preferably DB2 in z/OS) that is maintained to assist in guiding the correct pronounciation of words. This would be mostly used for assistance with people's names. Thanks in advance.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 12412 242 22_Re: DB2 Connect V7 EOS12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Tue, 7 Sep 2004 10:02:28 -0400392_iso-8859-1 Because of a COTS product, I recently had to specifically make V8 FP1 available for one of my PSOFT environments, but had no intention of moving anything else yet. This is my first experience dealing with non-MF EOS, and in the back of mind figured that the DB2 Connect life cycles would mimic the DB2 MF support, but then discovered the EOS page letting me know I was wrong. [...] 12655 22 19_Mastercard & Oracle12_Isaac Yassin20_yassini@BEZEQINT.NET30_Tue, 7 Sep 2004 19:24:08 +0200705_US-ASCII Hi,

Can anyone (IBMers?) shed some light on that:

http://www.oracle.com/global/me/press/meo/ora_masterc_160504.html

Isaac Yassin DBMS & IT Consulting IBM Certified Solution Expert DB2 V7 for OS/390 & z/OS

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 12678 55 82_IDUG Insider Discussion Forums / IDUG DB2-L Listserv - They're different resources10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Tue, 7 Sep 2004 13:21:29 -0400500_us-ascii Greetings Steve / DB2-Lers!

Sorry for the delay responding to Steve's question. For some reason my DB2-L digest came across garbled & it took me a day or so to catch up w/Steve.

Anyway, I apologize for the confusion. I had assumed that an IDUG broadcast had already come across DB2-L describing 'IDUG Insider' but after looking thru the DB2-L archives I've discovered that's not true. So here's my quick explanation. Please let me know if anyone has additional questions. [...] 12734 59 23_Re: Mastercard & Oracle35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 7 Sep 2004 13:31:01 -0400437_iso-8859-1 Less cost..... Sounds like creative accounting to me.

Did you see the article in ComputerWorld where Ford cancelled a huge Oracle application after several years of effort, and wasting many millions...??

Regards, Joel



----- Original Message ----- From: "Isaac Yassin" To: Sent: Tuesday, September 07, 2004 1:24 PM Subject: Mastercard & Oracle [...] 12794 49 23_Re: Mastercard & Oracle0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Tue, 7 Sep 2004 11:32:59 -0700488_- 20 million from 11 million.

Can there really be that much disparity between any comparably tuned mainframe DBMS??

Maybe MC had an opportunity to clean up a poorly designed database when they migrated to Oracle.

Remember "numbers can lie and liars use numbers" cliff:-0



-----Original Message----- From: Isaac Yassin [mailto:yassini@BEZEQINT.NET] Sent: Tuesday, September 07, 2004 10:24 AM To: DB2-L@www.idugdb2-l.org Subject: Mastercard & Oracle [...] 12844 31 42_Full Image Copy & Percent Of Changed Pages13_Mark Grundigz24_markgrundigz@HOTMAIL.COM30_Tue, 7 Sep 2004 13:30:39 -0500421_- Hello Listers,

We are a DB2 V7 on z/OS shop and make full image copies of all production tablespaces on a nightly basis using DSNUTILB (IBM's image copy). TRACKMOD has been set to NO for all tablespaces. The size of production objects is increasing rapidly, and we are considering incremental image copies atleast for some of the large objects (in the order of 100 GB - 200 GB) to save both time and media. [...] 12876 47 68_Possible causes for bogus SQL6031N message [DB2 UDB EEE V7.1 on AIX]14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM30_Tue, 7 Sep 2004 13:39:26 -0500579_US-ASCII I`ve been administering DB2 UDB EEE systems for over three years now (version 7.1, currently on Fixpack 6) on an RS/6000 SP2. I`ve had MPP instances that operate on anything from two up to eighteen SP2 nodes. Over the years I have observed that one of the weaknesses of the EEE architecture is that it is only as robust as the network that is used for communication between the different nodes that comprise an instance, which is, of course, to be expected with this architecture. Looking back over that time, the majority of the outages that I recall were network [...] 12924 91 23_Re: Mastercard & Oracle17_Michaelis, Daniel25_dcmichaelis@EORIGINAL.COM30_Tue, 7 Sep 2004 14:57:07 -0400543_- For what it's worth, I would tend to believe Oracle's claims based on the information that was included in the article. As an Informix DBA, I'm convinced of the power of "partitioning", and I can unequivocally say that both Oracle and Informix are light years ahead of DB2 in their implementation of partitioning. The only hope that DB2 has of competing with Oracle's partitioning is to ensure that the customer is on EEE, and even there, the partitioning strategy with EEE isn't as robust or powerful as it is with Oracle or Informix. [...] 13016 22 13_Prefetch = D?0_23_Rich.Janni@WAKEFERN.COM30_Tue, 7 Sep 2004 15:04:03 -0400520_us-ascii I'm getting a 'D' in the prefetch column of the plan table. The latest version of the manual has the following description for the column: |-----------------------+------------------------------------------------------------------| | PREFETCH | Whether data pages are to be read in advance by prefetch. S = | | | pure sequential prefetch; L = prefetch through a page list; | | | blank = unknown or no prefetch. | |-----------------------+------------------------------------------------------------------| [...] 13039 16 26_RTS - Real Time Statistics0_23_Rich.Janni@WAKEFERN.COM30_Tue, 7 Sep 2004 15:07:01 -0400635_us-ascii Any reason why RTS does not reset the TOTALROWS column back to zero for a tablespace when a mass delete is performed?

Rich Janni Wakefern Food Corp.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 13056 21 36_Re: DSN command processor hanging...13_P K.Ganapathy21_pkganapathi@YAHOO.COM30_Tue, 7 Sep 2004 14:08:10 -0500742_- Raquel,

Looks like this is not a DB2 issue since your DB2 commands are completing successfully.

Have you checked with your MVS folks about this? You may have to look at an online monitor (Omegamon or similar) to see what is happening during the "wait" state.

Thanks, -PK.Ganapathy DB2 DBA at Verizon.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on [...] 13078 17 17_Re: Prefetch = D?13_P K.Ganapathy21_pkganapathi@YAHOO.COM30_Tue, 7 Sep 2004 14:14:02 -0500626_- Rich,

This was answered about 2 weeks ago.

D = optimizer expects dynamic prefetch: as per V8 guide.

-PK.Ganapathy DB2 DBA at Verizon

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 13096 47 13_Remote Buffer8_Bob dawe20_udbdba2004@YAHOO.COM30_Tue, 7 Sep 2004 12:25:43 -0700572_us-ascii

We've recently received the email from our security team warnning us...

System Vulnerability -IBM DB2 Universal Database Multiple Remote Buffer Overflow And Unspecified Vulnerabilities





Does anyone know what remote buffer is in IBM DB2UDB?











__________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--------------------------------------------------------------------------------- 13144 24 93_REORG Partition with statistics adversely affects overall cluster ratio....but only sometimes0_23_Rich.Janni@WAKEFERN.COM30_Tue, 7 Sep 2004 15:42:19 -0400436_us-ascii Reorg statements like the following:

REORG TABLESPACE(DATABASE.TBLSPACE ) PART 0184 LOG NO COPYDDN(CPYDD003) MAPPINGTABLE DB2.MAP1 TIMEOUT TERM SHRLEVEL CHANGE STATISTICS TABLE INDEX SORTNUM 16 SORTDEVT SYSDA

yielded a LOWER overall cluster ratio.............on occasion. The remedy, though hard to believe, was to run runstats separately. Other times, it worked as expected, and improved the cluster ratio. [...] 13169 158 12_SQL Question22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Tue, 7 Sep 2004 15:06:29 -0500355_iso-8859-1

I have the following SQL:

SELECT ( 'UPDATE TEST.TABLE SET COL1 = '||A.ANAME) ||' COL2 = @'||A.BNAME||SUBSTR(ANAME,4,2) FROM TEST.TABLE2 WHERE BNAME = 'ABC' ;

This is the result:

UPDATE TEST.TABLE SET COL1 = AAA COL2 = @B01 UPDATE TEST.TABLE SET COL1 = CCC COL2 = @D02

I need the result to be like this: [...] 13328 137 16_Re: SQL Question10_Venu Varma27_Venu.Varma@ATRS.STATE.AR.US30_Tue, 7 Sep 2004 15:34:37 -0500534_us-ascii Try this,





SELECT ( 'UPDATE TEST.TABLE SET COL1 = '|| '''' ||A.ANAME || '''') ||' COL2 = || '''' || @'||A.BNAME||SUBSTR(ANAME,4,2) || '''' FROM TEST.TABLE2 WHERE BNAME = 'ABC' ;





Thanks Venu







Venkatesh Mokshagundam To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SQL Question Sent by: DB2 Data Base Discussion List



09/07/2004 03:06 PM Please respond to DB2 Database Discussion list at IDUG [...] 13466 137 16_Re: SQL Question10_Venu Varma27_Venu.Varma@ATRS.STATE.AR.US30_Tue, 7 Sep 2004 15:36:11 -0500451_us-ascii Try this,





SELECT ( 'UPDATE TEST.TABLE SET COL1 = '|| '''' ||A.ANAME || '''') || ',' ||' COL2 = || '''' || @'||A.BNAME||SUBSTR(ANAME,4,2) || '''' FROM TEST.TABLE2 WHERE BNAME = 'ABC' ;





Thanks Venu







Venkatesh Mokshagundam To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SQL Question Sent by: DB2 Data Base Discussion List [...] 13604 358 16_Re: SQL Question12_PRASAD, SHIV17_SPRASAD@SCANA.COM30_Tue, 7 Sep 2004 16:28:59 -0400573_us-ascii Replace single quote with three consecutive quotes!





Your statement would become

SELECT ( '''UPDATE TEST.TABLE SET COL1 = ''' ||A.ANAME) ||''' COL2 = @'''||A.BNAME||SUBSTR(ANAME,4,2) FROM TEST.TABLE2 WHERE BNAME = 'ABC' ;

Hopefully this should work!!









________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Venkatesh Mokshagundam Sent: Tuesday, September 07, 2004 4:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SQL Question [...] 13963 48 17_Re: Remote Buffer12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 7 Sep 2004 13:48:23 -0700334_us-ascii Its the buffer pool for remote threads. I believe this issue was covered in the special fixpack 10a that came out about October/November of last year. If you do a search on IBMLINK on Security issues or buffer overflow and there will be an II infoapar covering the three security issues that IBM warned its users about. [...] 14012 282 23_Re: Mastercard & Oracle4_Smit24_robert.erwin.smit@WXS.NL30_Tue, 7 Sep 2004 22:55:53 +0200372_iso-8859-1

I can't help my self I have to respond to what I'm reading........ppppfff

1 it's an Oracle article....do I need to say more..... 2 it does not say anything about the OS (Zos/AS400/AIX/SUN/Linux/WinX/etc) 3 the bigger the OS the bigger the costs 4 db2 mainframe isn't the same as db2 midrange 5 There are tuned databases and there are....... [...] 14295 185 16_Re: SQL Question22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Tue, 7 Sep 2004 15:54:57 -0500328_iso-8859-1

Venu and Shiv, Thanks for your responses.

I'm running this in SPUFI and the result is actually more than 80 bytes.

I need the result to go to the second line as well, as shown below.

UPDATE TEST.TABLE SET COL1 = 'AAA', COL2 = '@B01' UPDATE TEST.TABLE SET COL1 = 'CCC', COL2 = '@D02' [...] 14481 225 16_Re: SQL Question15_Gaston, Raymond17_GastonRay@ORU.COM30_Tue, 7 Sep 2004 17:03:17 -0400478_iso-8859-1 I believe "spanning lines" was done before on this db2list using a UNION or UNION ALL.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Venkatesh Mokshagundam Sent: Tuesday, September 07, 2004 4:55 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: SQL Question





Venu and Shiv, Thanks for your responses.

I'm running this in SPUFI and the result is actually more than 80 bytes. [...] 14707 84 16_Re: SQL Question13_Solecki, Rohn19_Rohn.Solecki@GWL.CA30_Tue, 7 Sep 2004 16:09:43 -0500391_iso-8859-1 It depends on how you are using it. If it is feeding directly into something that uses it, I don't know. But if you are simply generating a bunch of SQL statements that you will have an opportunity to manipulate (edit) before executing the use the general form they showed with the concatenate to generate the 2 separate lines, in 2 separate SQL statements joined by a union [...] 14792 40 45_Help on storing a mainframe dataset as a BLOB14_Richard Atkins16_rratkp@YAHOO.COM30_Tue, 7 Sep 2004 14:11:32 -0700402_us-ascii Dear listers,

Anybody has had any luck in storing a MVS GDG dataset as a BLOB. We use DB2 7.X. Further, any one forsees any problem in retrieving these reports stored as BLOB on to a web screen which uses IBM's WSAD (Websphere) frontend.

Cheers,





--------------------------------- Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. [...] 14833 101 16_Re: SQL Question14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM30_Tue, 7 Sep 2004 16:22:33 -0500 14935 256 19_Re: Access path....20_Dash, Sushanta Kumar29_sushantakumar.dash@LEHMAN.COM30_Fri, 3 Sep 2004 12:08:26 -0500571_iso-8859-1 Hi Raquel, your estimate is in right direction. To choose an access path the optimizer goes for FF,I/O and Cpu costs. While estimating the I/O time needed the optimizer founds that the I/O estimate by non matching index scan is costlier than tablepsace scan. This can be sensed from your length. Most of the time while calculating i/o for the non matchi9ng index scan the leaf pages are considered. Not the no leaf pages and leaf pages. If we go for Frank's formual say 1.7 ms for the non match index scan using the sequential prefetch and same 1.7ms for [...] 15192 291 23_Re: Mastercard & Oracle10_PAUL CLARK23_DHS9005@DHS.STATE.IL.US30_Tue, 7 Sep 2004 16:53:21 -0500457_ISO-8859-1 Yes I can shed some light on that... It's a sales pitch.

Not really better - not really worse - just different - Besides a brand new shiney car almost always runs better than an old, rusty heap. If you maintain your old car, it continues to run okay - if you let it go, well, it turns into a rusty heap of crap and a new car becomes your only option and we are all drawn to the new shiney car because it's pretty and it runs so well. [...] 15484 154 16_Re: SQL Question10_Venu Varma27_Venu.Varma@ATRS.STATE.AR.US30_Tue, 7 Sep 2004 17:23:04 -0500525_us-ascii Venkatesh,

Try selecting the hexadecimal representation of new line character in EBCDIC (select col1, X'hex newline', col2). This works in PC. Did not try in mainframe. If that does not work try

SELECT first line from table Union

Select second line from table

order by key

Thanks Venu





Venkatesh Mokshagundam To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: SQL Question Sent by: DB2 Data Base Discussion List [...] 15639 160 26_Re: Tuning Content Manager0_16_khampto1@TXU.COM30_Tue, 7 Sep 2004 17:21:28 -0500413_us-ascii Hi April, you didn't state on which OS platform you are running DB2 Content Manager. Here, we are running the predecessor to Content Manager, OnDemand/390, on a z/OS 1.4 system with DB2 v6, and I find it helpful to periodically REORG the indexes on the OnDemand Directory tables (the table names are like DIRECTnn.CDDIR). Heavy loading of documents can really cause those indexes to get fragmented. [...] 15800 215 16_Re: SQL Question9_Agus Kwee19_askwe@OPTONLINE.NET30_Tue, 7 Sep 2004 19:41:02 -0400567_iso-8859-1 RE: SQL QuestionVenkat,

If the values 'AAA' and 'B01' came from the same row in table TEST.TABLE2, at the moment I do not see yet how we can produce those 2 values in separate result rows, unless the result of the original SELECT FROM TEST.TABLE2 WHERE BNAME = 'ABC' is only 1 row, in this case we can use union to produce 'AAA' and 'BBB' in different result rows. Looking at the 2 update statements that you want to produce from the SELECT FROM TEST.TABLE2 statement, I do not see WHERE clause in those update statements. Are they supposed to [...] 16016 41 97_Re: REORG Partition with statistics adversely affects overall cluster ratio....but only sometimes19_Jardine, Lawrence J19_JardineLJ@AETNA.COM30_Tue, 7 Sep 2004 20:05:56 -0400565_us-ascii I've also seen this. Sometimes adding FORCEROLLUP YES helps. IF not, I can usually get away with RUNSTATS on a single (small) partition of the partitioning index with FORCEROLLUP YES.

| Larry Jardine | Production DBA | Aetna |



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rich.Janni@WAKEFERN.COM Sent: Tuesday, September 07, 2004 3:42 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: REORG Partition with statistics adversely affects overall cluster ratio....but only sometimes [...] 16058 137 16_Re: SQL Question0_14_DPetro@AOL.COM28_Tue, 7 Sep 2004 20:17:13 EDT375_US-ASCII I typically do this sort of thing to generate SQL statements from the catalog. Try this:



Select aname, 1, 'UPDATE TEST.TABLE SET COL1 = ' || '''' || ANAME || '''' || ',' from TEST.table2 where BNAME = 'ABC' UNION ALL Select aname, 2, ' COL2 = @'|| '''' || BNAME||SUBSTR(ANAME,4,2) || '''' || ';' from TEST.table2 where BNAME = 'ABC' ORDER BY 1,2 [...] 16196 38 39_TSM backup failure leads to DB2 Crash ?10_Ravi Reddy19_dbadb2udb@YAHOO.COM30_Tue, 7 Sep 2004 19:48:41 -0700354_us-ascii Hi,

DB2 V7.2 FP6, AIX V5.1

We have had a situation where DB2 is marked bad and went into crash recovery mode due to System Limit being reached.

We found that multiple TSM backup failures prior to the crash.

Has anyone encountered such situation before, wherein failed TSM backup leads to DB2 Memory Contention ? [...]