1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2000, week 2 2 16 27_db2-sql servre connectivity12_Aditya Narra31_Aditya.Narra@NYCBOE.NYCENET.EDU30_Fri, 7 Jul 2000 13:36:00 -0400314_- Gurus, Right now I am working at a shop where we have both the databases Db2 and Sql Server.We use a midleware to transfer data between the databases.We are contemplating going in for a SNA gateway.So you think that is a good option or do we have any other options.Any suggestions are welcome. Thanks, Audi [...] 19 39 31_Re: db2-sql servre connectivity10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK30_Sat, 8 Jul 2000 10:58:48 +0100426_iso-8859-1 you could consider using the gartner site www.gartner.com to look at options. this will give an unbiased opinion of your options. We use neon shadow direct to get data from client server apps and db2. We also use sql server,oracle and informix! There are loads of data replication tools on the market by ca,ibm,inforatica etc. Middleware is a good option as it gives you the control and the meta data benefits [...] 59 26 5_Row X12_Alan C Smith26_alan@ALANSMITH.DEMON.CO.UK30_Sat, 8 Jul 2000 11:42:02 +0100455_us-ascii Not very neat, but you could unload with DSNTIAUL or REORG UNLOAD EXTERNAL and then use IDCAMS REPRO with SKIP(999999) COUNT(1) etc.

Alan Smith



-----Original Message----- From: Database Research [mailto:db2ibm@YAHOO.COM.AU] Sent: Thursday, July 06, 2000 8:37 AM To: DB2-L@RYCI.COM Subject: Row X



Does anyone have a way of retrieving the 1,000,000 th or 2,000,000 th or .... row in a DB2 OS/390 V5 table? [...] 86 56 23_Waiting on Dynamic Bind12_Alan C Smith26_alan@ALANSMITH.DEMON.CO.UK30_Sat, 8 Jul 2000 12:07:12 +0100391_us-ascii Don't know if this is the same problem - when we switched to goal mode, we found that DDF threads hung in the remote system. I think that WLM was unable to work out the priority it should give the threads as they belonged to another system, so they only got the CPU left over. When the machine filled up, they got no CPU at all. WLM params were modified and things are OK now. [...] 143 75 28_Re: Help needed with problem15_Tjader, Polly A20_patjader@EASTMAN.COM30_Sat, 8 Jul 2000 08:23:56 -0400408_- Just wanted to update you all on the answer to my problem. Turns out that this was not a TCP/IP problem at all. The stored procedure had gone awry. Not sure exactly what happened. It was probably something in our recent move to DB2 V6 (OS390) or changing the stored procedures to WLM or a combination of all. Once the stored procedure program was recompiled, linked, bound, etc., everything was fine. [...] 219 22 10_-910 Error12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Sat, 8 Jul 2000 07:43:20 -0500556_iso-8859-1 We are receiving a -910 error when attempting to insert into a table. There is no outstanding drop or alter to the table from our application or any other we can find. Insert is the only failure. We can update and delete without issue.

Any experience or thoughts?

Gerald Hodge 888-494-9019

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 242 120 37_Re: 00C90101 in index and data pages.14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Sat, 8 Jul 2000 06:45:37 -0700591_iso-8859-1 Most likely the tablespace (or partition) was copied via DSN1COPY. However, the source table originally had no variable length rows, and then a row was added via ALTER ADD COLUMN. Now the source DBD says the table may contain variable length rows. The DBD won't be reset until after the tablespace is reorged, Imaged Copied and all pre-reorg image copies are deleted. However, this alter never happened on the target side (or it has been reorged). In the source, there are one or more rows that are "short", but since the DBD says the table can contain variable length rows, [...] 363 51 15_IBM DB2 Webcast17_Shailendra Dhanoa19_ssdhanoa@US.IBM.COM30_Sat, 8 Jul 2000 18:33:57 -0600523_us-ascii -------------------------------------------------------------------------- High speed search technologies have evolved to make databases the indispensable tools of the net economy. Join developerWorks technology webcast, High Speed Data Search, on July 19th at 10:00 am Eastern,to learn more about the technology behind high speed databases and DB2 version 7.1. Join the IBM developers who created the DB2 databases system to pose your technical database questions and get a review of real world application. [...] 415 27 25_DB2 Connect thru APPC SNA13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Mon, 10 Jul 2000 12:11:00 +0530356_us-ascii Hi Leon !!

I am having problem connectting to DB2 on Mainframe thru DB2 Connect and APPC. We have a Microsoft SNA Server in which we have defined the Local and remote LU. Now at teh client side I have an NT with DB2 Connect installed and SNA Client for NT Installed. Now how do I configure the DB2 Connect to talk via the SNA server . [...] 443 36 19_DB2 + PHP4 on Linux13_Jari Kohvakka19_jakohvak@NETMILL.FI31_Mon, 10 Jul 2000 09:58:58 +0300508_US-ASCII We're just starting to use DB/2 with PHP4 and I'd like to have some links to good resources on this. If you go to ANY PHP-site, you'll probably find tons of text about PHP4 and MySQL, but finding good info about using DB/2 is difficult, if not possible.

IBM's documentation is a pain in the ***. I haven't found any simple 'quick start'-guide that would tell me how to connect and select the right database etc with ODBC/PHP4. So all I need is to know some of the very basics with DB/2. [...] 480 33 40_System R - The father of DB2 Optimizer ?14_Massimo Scarpa23_Massimo_Scarpa@DEBIS.IT31_Mon, 10 Jul 2000 09:25:59 +0100403_us-ascii Hi all folks......Another strange request for you.

I read a part of an old article about a 'System R' access path selection, with some interesting formulas about access path selection. I think that System R is the father of DB2 Optimizer so it'd be interesting to learn more about it. Unfortunately I have only a bad photocopy of that article so I couldn't read the entire article. [...] 514 71 44_Re: System R - The father of DB2 Optimizer ?7_Sanjeev19_Sanjeev@INTERRA.COM31_Mon, 10 Jul 2000 13:07:37 +0530545_iso-8859-1 Hi Max, A question similar to this one i wrote in the list sometimes about 8 months ago.Infact it was in context with some formula as well as difference with the optimizers of other DBMS.I didn't get any reply at that time. I do not know if you have seen an article in IDUG journal(do not remember the whole details) on what are the steps performed at the time of BIND.Till now i find that to be the most interesting and valuable information about optimizer.If you have not seen then u may have to search the IDUG site for that. [...] 586 34 44_Re: System R - The father of DB2 Optimizer ?14_Massimo Scarpa23_Massimo_Scarpa@DEBIS.IT31_Mon, 10 Jul 2000 09:56:02 +0100324_us-ascii Hi Sanjeev, nice to meet you again in the list.

In Italy a secret that many people know it's called 'the secret of Pulcinella'. I don't want to know the EXACT Optimizer code,

I only want to know some formulas that many gurus & consulting people said to be 'the well-know Optimizer cost formulas' [...] 621 41 9_Re: Row X10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Mon, 10 Jul 2000 10:04:15 +0100444_iso-8859-1 the question is a little ambiguous, would that be the 1000000th pyhsical row or is this based on a primary key order?

-----Original Message----- From: Alan C Smith [mailto:alan@ALANSMITH.DEMON.CO.UK] Sent: Saturday, July 08, 2000 10:42 AM To: DB2-L@RYCI.COM Subject: Row X



Not very neat, but you could unload with DSNTIAUL or REORG UNLOAD EXTERNAL and then use IDCAMS REPRO with SKIP(999999) COUNT(1) etc. [...] 663 37 21_test....please ignore14_Popy A. Dimoka24_dimokap@INTERAMERICAN.GR31_Mon, 10 Jul 2000 12:57:34 +030019_iso-8859-7

701 126 20_Capture Changed-Data5_Pearl17_panidak@SCB.CO.TH31_Mon, 10 Jul 2000 17:34:12 +0700643_windows-874 Hi List, I would like to know your thought about " What is the best method to capture changed-data?". Our objective is keep changed-data for audit purpose only. Assume we used DB2 V6 for OS/390 . We have four choices to capture changed-data 1. When changed-data committed, write that data to QSAM file. After that,load data into history table (history table structure like operational table structure). 2. Create history table like operational table and add 2 columns keep userid and time that update operation occured. After insert,update or delete data operate on operational table, we will insert changed-data into history [...] 828 47 24_Re: Capture Changed-Data10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Mon, 10 Jul 2000 12:36:59 +0100679_windows-874 use option 2. it costs more but is supported and uses logs, less invasive for production environments. you'll never get your developers to keep up ensuring their applications track the data.

-----Original Message----- From: Pearl [mailto:panidak@SCB.CO.TH] Sent: Monday, July 10, 2000 10:34 AM To: DB2-L@RYCI.COM Subject: Capture Changed-Data



Hi List, I would like to know your thought about " What is the best method to capture changed-data?". Our objective is keep changed-data for audit purpose only. Assume we used DB2 V6 for OS/390 . We have four choices to capture changed-data 1. When changed-data committed, write that data to QSAM [...] 876 141 9_DRDA Bind14_Popy A. Dimoka24_dimokap@INTERAMERICAN.GR31_Mon, 10 Jul 2000 13:42:25 +0300765_iso-8859-7 Hello List!

I am testing a DB2 application which is going to be migrated from the NT to the OS/390.The database has been succesfully transfered to the mainframe and now i am working on the application itself.I have changed the code of the cobol programs in order them to connect to the OS/390 database (subsystem).The problem arises when i am doing the (DRDA) bind.I issue the following command from the Command Center: ---------------------------------------------------------------------------------- bind d:\liabilt\test1\liabiln.bnd collection liab00 grant public qualifier tliab ; ---------------------------------------------------------------------------------- ,where tliab is the qualifier of the tables on the OS/390 DB2 subsystem. [...] 1018 59 24_Re: Capture Changed-Data19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 10 Jul 2000 07:29:26 -0500638_windows-874 Pearl, Have you considered AUDIT CHANGES on the subject tables? HTH, Rick Davis



"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 1078 102 3_SMS11_Mohamed Beg19_mohamedb@ABSA.CO.ZA31_Mon, 10 Jul 2000 15:16:14 +0200439_iso-8859-1 Hi.

IS SMS GOOD OR BAD FOR PERFORMANCE.

I've read an article in in the DB2 Magazine "Time To Reconsider SMS" it speaks about the advantages of using SMS in your environment and how the current DASD structures can compensate for having extents and so forth.

Then I go to a DB2 Performance Course and I'm told that extents are bad for performance even with all these new developments in DASD technology. [...] 1181 51 7_Re: SMS10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Mon, 10 Jul 2000 15:15:47 +0100284_iso-8859-1 you can tailor the sms routines to place datasets where you want based on the db2 dataset names. This is a bit heavy on maintainence but can be done. The disk technology these days is fast so I wouldn't gert too hung up on extents. As long as your under 10 you're ok. [...] 1233 56 7_Re: SMS0_19_mike.holmans@BT.COM31_Mon, 10 Jul 2000 14:18:10 +0100616_- The Redbook "Storage management with DB2 for OS/390", SG24-5462-00 is very good at explaining SMS to the DB2 specialist.

Mike Holmans BT ISE Technical Design mike.holmans@bt.com

This post represents the views of the author and does not necessarily accurately represent the views of BT

> -----Original Message----- > From: Mohamed Beg [SMTP:mohamedb@ABSA.CO.ZA] > Sent: Monday, July 10, 2000 2:16 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] SMS > > Hi. > > IS SMS GOOD OR BAD FOR PERFORMANCE. > > I've read an article in in the DB2 Magazine "Time To Reconsider SMS" it > speaks about > the [...] 1290 145 7_Re: SMS14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 10 Jul 2000 08:36:21 -0400510_iso-8859-1 Suggest that you get from the internet, or order, the new Redbook: Storage management with DB2 for OS/390 -- SG24-5462-00. Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@attglobal.net www.YLAssoc.com -- DB2 Performance Journal www.ryci.com -- Knowledge Base www.DB2-Portal.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Mohamed Beg Sent: Monday, July 10, 2000 9:16 AM To: DB2-L@RYCI.COM Subject: SMS [...] 1436 33 7_Re: SMS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 10 Jul 2000 10:10:35 -0400534_ISO-8859-1 While high numbers of extents still have some impact on performance, some number of extents is rarely a problem on todays devices. The primary problem with having SMS manage all your datasets is - dataset placement. When multiple datasets with high i/o rates are placed on the same volume, you get poor performance because of IO queing. You need to manage the placement of some small/reasonable number of important datasets, and let SMS deal with the rest. Total SMS management = total poor performance. Regards, Joel [...] 1470 62 7_Re: SMS0_27_jim.leask@RS-COMPONENTS.COM31_Mon, 10 Jul 2000 15:16:33 +0100341_iso-8859-1 Total SMS management only equals total poor performance if you (and your DASD manager) do not amend the ACS rules to manage the placement of your high I/O, contentious and critical datasets.

Total SMS (including good ACS rules) can = total good performance

If that is what you are saying Joel, I totally agree. [...] 1533 111 7_Re: SMS15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 10 Jul 2000 09:26:50 -0500359_iso-8859-1 Like everything, it depends. I agree with the assertion that important data sets might need to be hand placed. We hand-placed our bootstraps and logs outside of SMS control. Otherwise, the larger the pool the less likely you are to run into problems with disk contention. The smaller the pool the more likely you are run into disk contention. [...] 1645 38 20_Capture Changed-Data5_Riyaz15_RMomin@FHSC.COM31_Mon, 10 Jul 2000 10:29:53 -0400669_US-ASCII Pearl, CA (PLATINUM) Log Analyzer can do this. This product can track UPDATES/INSERTS to DB2 Tables, can prepare Audit Reports, and can even rollback the changes. Find out more by visiting http://www.cai.com/products/db2/loganalyzer/overview.htm Good Luck, Riyaz. Hi List, I would like to know your thought about " What is the best method to capture changed-data?". Our objective is keep changed-data for audit purpose only. Assume we used DB2 V6 for OS/390 . We have four choices to capture changed-data 1. When changed-data committed, write that data to QSAM file. After that,load data into history table (history table structure like operational table [...] 1684 34 7_Re: SMS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 10 Jul 2000 10:39:52 -0400334_ISO-8859-1 That's exactly what I said Jim. You can manage your critical datasets using ACS rules, distinct storage groups, whatever you choose - as long as you can manage the placement of those critical items. As most people here define it, "total sms management" means one big pool, and you don't manage anything. Regards, Joel [...] 1719 147 7_Re: SMS14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 10 Jul 2000 09:50:16 -0400736_iso-8859-1 On this issue, do not forget that EA (Extended Addressability) storage architecture requires SMS, and it would appear that the direction in DB2 is definitely towards SMS managed objects.

Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@attglobal.net http://www.YLAssoc.com -- DB2 Performance Journal http://www.ryci.com -- Knowledge Base http://www.DB2-Portal.com



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Billings, Linda > Sent: Monday, July 10, 2000 10:27 AM > To: DB2-L@RYCI.COM > Subject: Re: SMS > > > Like everything, it depends. I agree with the assertion that > important data > sets might need to be hand [...] 1867 36 45_DB2 NT locking for RI constraint enforcement?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 10 Jul 2000 11:00:59 -0400437_iso-8859-1 (cross-posted to DB2-l & DB2EUG)

I have a question from a colleague developing apps on DB2 UDB on Windows NT.

He is binding the application with Isolation level of Uncommitted Read. He is inserting rows into a dependent table and it is apparent to him that the DB2 RI check obtains a persistent exclusive lock on the parent table. (I hope I've translated accurately from the Java description he's given me) [...] 1904 114 7_Re: SMS14_John P Shipley24_jpshiple@DUKE-ENERGY.COM31_Mon, 10 Jul 2000 11:00:34 -0400570_us-ascii



I would suggest going with the notion of a general pool for most datasets and as has been suggested putting the heavy I/O datasets on placed volumes. What we have done in the past is to set up a general pool Storage group with a Storage group for each volume and also an overflow pool. Then you can spread out the I/O across multiple volumes and channels for heavy hitters. This is especially useful for partitioned datasets. You may need to turn a parameter called "garanteed space" for the storage groups "on". Good luck on your quest... [...] 2019 41 18_CA/PLT Inforefiner15_Michele Granito29_michele.granito@ISTRUZIONE.IT31_Mon, 10 Jul 2000 16:57:57 +0200621_iso-8859-1 After migration to Language Environment I have problem with Inforefiner VALIDATE process. The job give me the following messages:

IEW2322I 1220 1 LIBRARY STEPLIB(IGZEBST,IGZCBSO) IEW2456E 9207 SYMBOL CEESTART UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY. IEW2456E 9207 SYMBOL CEEBETBL UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY. IEW2456E 9207 SYMBOL CEESG005 UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY. IEW2456E 9207 SYMBOL IGZCBSO UNRESOLVED. MEMBER COULD NOT BE INCLUDED FROM THE DESIGNATED CALL LIBRARY. [...] 2061 27 37_FileAid for DB2 SQL Analysis function15_Michele Granito29_michele.granito@ISTRUZIONE.IT31_Mon, 10 Jul 2000 17:14:28 +0200381_iso-8859-1 People with DBADM authority can't execute SQL Analysis if the DB2 program use Views. How can I manage this problem without giving Grant SELECT,UPDATE, INSERT and DELETE on Views?

Thanks.

Michele Granito EDS Italy Delivery Via Maremmana Superiore, 29 Monteporzio Catone (RM) tel. +39 06 9402392 fax +39 06 9402237 e-mail: michele.granito@istruzione.it [...] 2089 25 24_DB2/OS390 DSN1CHKR usage11_Ron Johnson25_rjohnson@DATA-TRONICS.COM31_Mon, 10 Jul 2000 10:40:27 -0500483_- We currently run DSN1CHKR on catalog tablespaces during a weekly scheduled IPL and DB2 downtime. This has been done since before my time as a DBA and in over 9 years it has yet to find any problems in the catalog. This is falling into the catagory "Do we really need to do this??". The utility guide says "Use DSN1CHKR on a regular basis to detect any damage to the catalog and directory as soon as possible". I also run DSN1CHKR when migrating to a new release as suggested. [...] 2115 144 37_Re: 00C90101 in index and data pages.15_Claudio Sanchez20_clsanchez@BNL.COM.AR31_Mon, 10 Jul 2000 12:49:40 -0300722_us-ascii We had the same problem, and we solve them applying the PTF UQ08970. Pardon if my English is not very good Regards.











Wayne Driscoll con fecha 08/07/2000 10.45.37

Por favor, responda a DB2 Data Base Discussion List

Destinatarios: DB2-L@RYCI.COM CC: Asunto: Re: 00C90101 in index and data pages.





Most likely the tablespace (or partition) was copied via DSN1COPY. However, the source table originally had no variable length rows, and then a row was added via ALTER ADD COLUMN. Now the source DBD says the table may contain variable length rows. The DBD won't be reset until after the [...] 2260 135 53_Re: PeopleSoft: Programmer access to production data.4_IBM116_IBM1@UBIL.COM.TR31_Mon, 10 Jul 2000 18:50:00 +0300711_- A. NO B. Yes C. Yes D. Yes

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced.

*std disclaimer*

> -----Original Message----- > From: craig patton [SMTP:prgpatton@HOTMAIL.COM] > Sent: Wednesday, July 05, 2000 5:32 PM > To: DB2-L@RYCI.COM > Subject: Re: PeopleSoft: Programmer access to production data. > > A. NO > B. Yes > C. Yes > D. Yes > > Craig Patton > President/DBA Consultant > Patton Research Group, Inc. > "Specializing in Performance and [...] 2396 58 44_Re: System R - The father of DB2 Optimizer ?12_Kline, Wayne27_wayne.kline@INGRAMMICRO.COM31_Mon, 10 Jul 2000 08:58:57 -0700466_iso-8859-1 Massimo: Try to find some old-timer who has a copy of DB2 Access Path Selection by Prem Mehra. Mine has a blue cover on it. Don't know how many of the calculations are still valid, but Prem spent a lot of time and effort in assembling this information from developers. There is also an early article on System R* that may have been published in the Communications of the ACM. Good basic info but no access path calculations from what I can remember. [...] 2455 42 28_Re: DB2/OS390 DSN1CHKR usage10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Mon, 10 Jul 2000 18:00:29 +0100625_iso-8859-1 forget it, we used to do that but never got any probelms. we do it when we migrate to new releases

-----Original Message----- From: Ron Johnson [mailto:rjohnson@DATA-TRONICS.COM] Sent: Monday, July 10, 2000 3:40 PM To: DB2-L@RYCI.COM Subject: DB2/OS390 DSN1CHKR usage



We currently run DSN1CHKR on catalog tablespaces during a weekly scheduled IPL and DB2 downtime. This has been done since before my time as a DBA and in over 9 years it has yet to find any problems in the catalog. This is falling into the catagory "Do we really need to do this??". The utility guide says "Use DSN1CHKR [...] 2498 132 22_Archiving DB2 Database23_Donald W. Woodruff, Jr.30_dwoodruf@CI.NEWPORT-NEWS.VA.US31_Mon, 10 Jul 2000 11:58:39 -0400614_iso-8859-1 DB2L'ers:

I need some advice on the following issue:

We have a typical production database that is accessed via CICS and batch programs on a normal basis. At the end of the fiscal year, this database needs to be saved in its current state so end-users (auditors) can access the data for the prior fiscal year while the online/batch production work can continue for the new fiscal year. I suggested creating a "Archive" database, load the data into this database and allow the end-users to access this "archived data" via their batch programs. So, I will have duplicate databases, one [...] 2631 208 7_Re: SMS15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 10 Jul 2000 11:05:08 -0500394_iso-8859-1 Then we need to learn how to use SMS correctly to avoid performance problems on implementation and be wary of blanket statements such as SMS is always bad for DB2 performance.

Linda Billings Enterprise Systems Programmer Wisconsin Department of Administration Info-Tech Services

"Whom computers would destroy, they must first drive mad." -Anon. <../author/1.html> [...] 2840 63 28_Re: DB2/OS390 DSN1CHKR usage22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Mon, 10 Jul 2000 12:08:51 -0400698_- Think of it as preventative maintenance & keep executing it.



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: Pete Coles[SMTP:peter.coles@SOMERFIELD.CO.UK] > Sent: Monday, July 10, 2000 1:00 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] DB2/OS390 DSN1CHKR usage > > forget it, we used to do that but never got any probelms. we do it when we > migrate to new releases > > -----Original Message----- > From: Ron Johnson [mailto:rjohnson@DATA-TRONICS.COM] > Sent: Monday, July 10, 2000 3:40 PM > To: DB2-L@RYCI.COM > Subject: DB2/OS390 DSN1CHKR usage > > > We currently run DSN1CHKR on catalog tablespaces during a [...] 2904 63 24_Re: Capture Changed-Data13_Sawyer, Gregg20_Gregg_Sawyer@CSX.COM31_Mon, 10 Jul 2000 12:12:13 -0400471_windows-874 The number says "2", but the argument says "3"...

Gregg Sawyer

-----Original Message----- From: Pete Coles [mailto:peter.coles@SOMERFIELD.CO.UK] Sent: Monday, July 10, 2000 7:37 AM To: DB2-L@RYCI.COM Subject: Re: Capture Changed-Data



use option 2. it costs more but is supported and uses logs, less invasive for production environments. you'll never get your developers to keep up ensuring their applications track the data. [...] 2968 56 28_Re: DB2/OS390 DSN1CHKR usage13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Mon, 10 Jul 2000 11:28:28 -0500384_iso-8859-1 we execute on migrations, and would execute if we suspected a problem.

-----Original Message----- From: Pete Coles [mailto:peter.coles@SOMERFIELD.CO.UK] Sent: Monday, July 10, 2000 12:00 PM To: DB2-L@RYCI.COM Subject: Re: DB2/OS390 DSN1CHKR usage



forget it, we used to do that but never got any probelms. we do it when we migrate to new releases [...] 3025 20 18_Index on 2 tables?20_Baskaran Subramaniam27_subramaniam.baskaran@DB.COM31_Mon, 10 Jul 2000 22:08:23 +0530306_us-ascii Hello Listusers

We have a join query and the performance is very poor, so we want to improve it by creating an index. Is it possible to create an index on 2 tables on 2 different tablespaces??? If not is there any way of improving the performance. Pls suggest..

TIA Rgds Baskar [...] 3046 48 22_Re: Index on 2 tables?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 10 Jul 2000 11:44:22 -0500507_iso-8859-1 You would have to create an index on each table. There is no way to use one index for two.

Linda Billings Enterprise Systems Programmer Wisconsin Department of Administration Info-Tech Services

"Whom computers would destroy, they must first drive mad." -Anon. <../author/1.html>





-----Original Message----- From: Baskaran Subramaniam [mailto:subramaniam.baskaran@DB.COM] Sent: Monday, July 10, 2000 11:38 AM To: DB2-L@RYCI.COM Subject: Index on 2 tables? [...] 3095 29 41_Re: FileAid for DB2 SQL Analysis function13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 10 Jul 2000 12:46:33 -0400418_iso-8859-1 Hello Michele, File-AID for DB2 is invoking the Explain function as part of the SQL Analysis. Explain requires the user to have the privileges that the plan requires.

According to the SQL Reference: "The authorization rules are those defined for the SQL statement specified in the Explain statement ...."

I don't have any wonderful advice for you on beating this SQL Explain requirement. [...] 3125 21 34_Storage management using SAMS DISK11_Joe Bitetto31_joseph.bitetto@US.PWCGLOBAL.COM31_Mon, 10 Jul 2000 11:36:07 -0500502_ISO-8859-1 Our shop uses SAMS Disk (VAM) for storage management. Can anyone share any experiences they have using SAMS Disk to manage DB2 data sets?

How does SAMS compare to SMS? What are the similarities or differences? PROS? CONS? ECT………………..

TIA

===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3147 41 7_Re: SMS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 10 Jul 2000 12:52:57 -0400553_ISO-8859-1 Linda, Obviously the key is in your first line. SMS managed does not have to mean that SMS determines where to place objects because there is one huge SMS pool. Used with intelligence, SMS can do a good. Everything gets better over time. Based on current technology, "just giving it all to SMS", blindly, is a guarantee for performance problems - unless you have a really small system. This certainly may change in the future, but this is where things are today, and what I see at every "blindly" SMS managed installation. Regards, Joel [...] 3189 115 60_Re: DB2 for OS/390 Performance Monitor - which one? - thanks14_Ulrich, Deanna21_Deanna_Ulrich@BMC.COM31_Mon, 10 Jul 2000 12:10:49 -0500545_iso-8859-1 Georg,

We understand your concern in having to move from BMC's Activity Monitor to MAINVIEW for DB2. We are committed to making the process as simple possible. Our plan is to provide our customers with a gradual approach to MAINVIEW that will allow an easy migration.

Rest assured that we have our customers' interests and concerns in mind. It is neither our intention nor in our interest to pressure our customers into a rushed migration, and we will allow all the time needed to migrate in the best way possible. [...] 3305 34 28_Re: DB2/OS390 DSN1CHKR usage12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Mon, 10 Jul 2000 12:10:00 -0400572_- We use it on Version Migrations and after an offsite recovery from image copies.

>>> DB2-L@RYCI.COM@inter2 07/10/00 10:40AM >>> We currently run DSN1CHKR on catalog tablespaces during a weekly scheduled IPL and DB2 downtime. This has been done since before my time as a DBA and in over 9 years it has yet to find any problems in the catalog. This is falling into the catagory "Do we really need to do this??". The utility guide says "Use DSN1CHKR on a regular basis to detect any damage to the catalog and directory as soon as possible". I also run DSN1CHKR [...] 3340 62 29_Re: DB2 Connect thru APPC SNA0_15_leon@CA.IBM.COM31_Mon, 10 Jul 2000 13:11:00 -0400526_us-ascii Please look at the DB2 Connect Quick Beginnings and Connectivity Supplement manuals. There are instructions in these books. I can not type up instructions in an e-mail.

I think that what you are trying to do i.e. use MS SNA Server as an SNA gateway is not the right solution. You would be much farther ahead by fixing whatever TCP/IP problems you have. It is typically much easier to have TCP/IP working then to get SNA going. If you are having problems with TCP/IP check this informational APAR II11164. [...] 3403 45 41_Re: FileAid for DB2 SQL Analysis function12_Hayes, Chris19_CHRIS.HAYES@SSA.GOV31_Mon, 10 Jul 2000 13:20:47 -0400392_iso-8859-1 I am not sure if this will work with File-Aid or any other tool, but you could try using BINDAGENT to see if the EXPLAIN will complete without allowing direct access to the data.

-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Monday, July 10, 2000 12:47 PM To: DB2-L@RYCI.COM Subject: Re: FileAid for DB2 SQL Analysis function [...] 3449 51 22_Re: Index on 2 tables?14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 10 Jul 2000 12:42:56 -0400701_iso-8859-1 Post the query so anyone can make recommendations, based on the construct of the required result set.

Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@attglobal.net http://www.YLAssoc.com -- DB2 Performance Journal http://www.ryci.com -- Knowledge Base http://www.DB2-Portal.com



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Baskaran Subramaniam > Sent: Monday, July 10, 2000 12:38 PM > To: DB2-L@RYCI.COM > Subject: Index on 2 tables? > > > Hello Listusers > > We have a join query and the performance is very poor, so we want > to improve it by creating an index. > Is it possible [...] 3501 79 20_Re: Datajoiner & DB20_15_leon@CA.IBM.COM31_Mon, 10 Jul 2000 13:54:37 -0400419_us-ascii If all you are doing is connecting to DB2 databases then there is no reason to use DataJoiner. DB2 Connect V7.1 will do what you need using the latest technology.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Whittaker, Stephen" on 07/06/2000 03:23:50 PM

Please respond to DB2 Data Base Discussion List [...] 3581 53 28_Re: DB2/OS390 DSN1CHKR usage0_14_mtdage@ATT.NET31_Mon, 10 Jul 2000 18:08:41 +0000516_- We do it on a weekly basis. We copy a backup of tablespace to a temporary tablespace using DSN1COPY. For example we copy DB.DSBA.B1073DB1(+0) to DSST.DSNDBD.DSNDB01.DBD01.I0001.C001 then run DSN1CHKR against DSST.DSNDBD.DSNDB01.DBD01.I0001.C001 Then after running DSN1CHKR we turnaround and delete temporary tablespace so we free up DASD space.

We don't have to bring down DB2. When time to migrate we don't even have to run it again since we always do the migration after we have run weekly DSN1CHKR. [...] 3635 72 7_Re: SMS15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 10 Jul 2000 14:19:17 -0500315_iso-8859-1 Hi, Joel,

Except for the BSDS and logs we use SMS to manage our DB2 data. We have a rather large SMS pool and don't have any performance problems that can be attributed to that. I don't know much about the particulars of how it was implemented as this occurred before I became employed here. [...] 3708 48 40_R: FileAid for DB2 SQL Analysis function13_Carlo Petrone15_carpetro@TIN.IT31_Mon, 10 Jul 2000 21:32:02 +0200564_iso-8859-1 HEllo michele,

Give a SYSADM authorization to user is one of possible solution.

Ciao Carlo Petrone





-----Messaggio originale----- Da: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]Per conto di Michele Granito Inviato: lunedì 10 luglio 2000 16.14 A: DB2-L@RYCI.COM Oggetto: FileAid for DB2 SQL Analysis function



People with DBADM authority can't execute SQL Analysis if the DB2 program use Views. How can I manage this problem without giving Grant SELECT,UPDATE, INSERT and DELETE on Views? [...] 3757 109 22_FW: Index on 2 tables?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 10 Jul 2000 14:35:39 -0500392_iso-8859-1 Baskar sent me the email attached with the dratted SQL statement. Attached is also my response. I know that Baskar went home. If anyone wants to take a stab at it, go ahead.

Linda Billings Enterprise Systems Programmer Wisconsin Department of Administration Info-Tech Services

"Whom computers would destroy, they must first drive mad." -Anon. <../author/1.html> [...] 3867 75 47_Re: Current thoughts on table/plan/package auth12_Carol Sutfin19_csutfin@AMSOUTH.COM31_Mon, 10 Jul 2000 14:40:39 -0500541_- Cliff I don't remember seeing anyone reply to you, so I'll take a chance.

If the access to the data is using STATIC sql, i.e. bound as a plan/package you don't need to grant access to the tables to the user. The access comes from the ability to EXEC the plan/package. This allows the ID executing the package/plan to access the tables, views (etc) as defined by the DBRM. The rules in most of the shops I have worked in are that EXEC in production is never granted to PUBLIC. It is granted to individual ID's or groups of ID's [...] 3943 67 13_Re: DRDA Bind8_Rob Lake20_robert.lake@CONTI.DE31_Mon, 10 Jul 2000 14:55:05 -0500288_- For the warning message, it looks like the DB2 UDB for NT precompiler was invoked with the LANGLEVEL option when creating the bind file. This is an unsupported option under DB2 for OS/390 and so the option was ignored by the binder and the warning message that you see was issued. [...] 4011 51 29_Re: DB2 UDB V6 or V7 Monitors13_Jim Wankowski20_jwankowski@QUEST.COM31_Mon, 10 Jul 2000 14:57:58 -0500614_- On Fri, 7 Jul 2000 07:37:00 -0500, Baranowski.P@GRAINGER.COM wrote:

>Anyone recommendations on a monitor for UDB V6 or V7 running on UNIX that can >execute a predefined script when an event has occured? > >And here is the kicker: > >All this while I'm at home trying to get a good nights sleep. > > >I am aware of CA's ManageIT product but I need it NOW! No vaporware please. > > >TIA > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at [...] 4063 110 47_Re: Current thoughts on table/plan/package auth11_Suresh Sane21_data_arch@HOTMAIL.COM29_Mon, 10 Jul 2000 15:48:09 CDT331_- I agree, just a couple of additional comments to what Carol has stated.

1. With static sql, it may be advisable (sometimes) to grant to public for cics packages, enable them for cics only and control security via tran codes within cics. This is a perfectly viable option that I have successfully used for one client. [...] 4174 27 17_Stored Procedures15_Morris, Deborah20_DMorris@UNCH.UNC.EDU31_Mon, 10 Jul 2000 16:56:04 -0400386_iso-8859-1 We are looking at re-writing a CICS/VSAM based system using DB2 and were wondering whether stored procedures could be called from CICS.

Does anyone know whether this can be done. If it can, what have the experiences been, good and bad?

Deborah A. Morris Supervisor DataBase Administration UNC Health Care System 2nd Floor Carr Mill Mall Carrboro, NC 27510 [...] 4202 130 47_Re: Current thoughts on table/plan/package auth12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Mon, 10 Jul 2000 16:39:32 -0500449_iso-8859-1 I just logged on. Both the local and the remote. Let me get this other software and walk you through it.

GSH

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Suresh Sane Sent: Monday, July 10, 2000 3:48 PM To: DB2-L@RYCI.COM Subject: Re: Current thoughts on table/plan/package auth



I agree, just a couple of additional comments to what Carol has stated. [...] 4333 55 21_Re: Stored Procedures11_Suresh Sane21_data_arch@HOTMAIL.COM29_Mon, 10 Jul 2000 16:48:29 CDT510_- Deborah: yes, absolutely. One of my clients uses Stored Procedures almost exclusively. They are triggered by CICS as well as by Visual Basic client.

If you are thinking of replacing existing CICS programs with emebedded SQL by these, there will be a performance penalty but well worth the added functionality (especially with WLM) and the ability to re-use them when called by a clent application also. If you are a high transaction system running exclusively on OS/390, more reserach is needed. [...] 4389 28 26_Stored Procedure Execution12_Keister, Jim16_JKeister@QRS.COM31_Mon, 10 Jul 2000 15:16:23 -0700539_iso-8859-1 Is there a simple way to determine if a Stored Procedure has been CALLed by a Trigger or if a Trigger has executed??



Thanks,

Jim Keister Sr. DB2 DBA

QRS, Corp. 1400 Marina Way South Richmond, CA. 94804

(510)231-6654 (510)621-3873 Fax jkeister@qrs.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 4418 108 7_Re: SMS15_Richard Simpson25_rsimpson@MACQUARIE.COM.AU31_Tue, 11 Jul 2000 09:19:39 +1000409_iso-8859-1 Mohamed Beg wrote: >I've read an article in in the DB2 Magazine "Time To Reconsider SMS" it >speaks about the advantages of using SMS in your environment and how >the current DASD structures can compensate for having extents and so forth.

>Then I go to a DB2 Performance Course and I'm told that extents are >bad for performance even with all these new developments in DASD technology. [...] 4527 51 7_Re: SMS0_14_DPetro@AOL.COM29_Mon, 10 Jul 2000 20:18:56 EDT509_US-ASCII Do any of the comments regarding SMS management change in light of the new Shark DASD technology? Does the ability to do dual reads from the same device reduce the need to worry about dataset placement?



In a message dated 07/10/2000 12:56:25 PM Eastern Daylight Time, joel_goldstein@COMPUSERVE.COM writes:

<< Linda, Obviously the key is in your first line. SMS managed does not have to me= an that SMS determines where to place objects because there is one huge SMS pool.= [...] 4579 61 57_Performance and catalog managment tool selection criteria13_Henry Fogwill17_fogwill.h@GHC.ORG31_Mon, 10 Jul 2000 17:43:49 -0700323_- Hi, We are a 30 day old DB2 shop and got to the point where we need to get some tools. A performance tool and catalog management tool was voted as the primary requirements for now.

I am hoping someone kept some of there documentation / selection criteria for this type of products and could share it with us. [...] 4641 157 22_FW: Index on 2 tables?14_Purcell, Terry30_Terry.Purcell@TEAM.TELSTRA.COM31_Tue, 11 Jul 2000 12:05:09 +1000619_iso-8859-1 Baskar,

As Linda pointed out, the correlated subquery MAY be of concern if it is accessed by many outer rows (every outer row that meets other criteria), and requires either of the following: index + data access, tablespace scan, non-matching index scan, list-prefetch or a sort.

For this subquery, I would suggest an index on ILTFST table, columns ILTFS_MSGREF, ILTFS_MSGINFO DESCENDING. This would ensure one-fetch indexonly access from the outer query to the subquery. NOTE: The outer row comparison B.ILTFS_MSGINFO = (SELECT MAX(ILTFS_MSGINFO) is stage 2, and therefore not indexable. [...] 4799 48 7_Re: SMS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 10 Jul 2000 22:27:07 -0400592_ISO-8859-1 To a large extent, yes. Almost all sites so far have seen substantial reductions from the elimination of IO Queing.. and a few still see some problems. There are always considerations.. such as - how many device IDs are available versus the number of defined devices... may be a problem for large installations, but not for a small one. How many should be assigned to a volume, should they change for different workloads, at different times of the day... (just like changing pool thresholds for different workloads). Also, the reads are not merely dual, but could be 4, 8, or [...] 4848 53 7_Re: SMS14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 10 Jul 2000 22:34:14 -0400537_ISO-8859-1 Just as a caveat, it's not clear what you really mean when you say avg synch i/o across a week. If it's really for a week, this is a false metric since it factors in periods of low activity. You need to look at time slices for your peak workloads. It's also very dependant on your workload... how big/small. How large are the objects, and how random. is the access. With 8 ms elapsed, you are getting most of your hits from the cache, so there is little physical access. However, a cache miss on a raid device is usually [...] 4902 89 29_Re: DB2 Connect thru APPC SNA13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 11 Jul 2000 08:34:41 +0530469_us-ascii Thanks a lot Leon!! I was expecting just that !! Even we are trying to fix the TCPIP problem as fast as possible . Actually the production shops require a temporary alternative.



Vishy









leon@CA.IBM.COM on 10/07/2000 10:41:00 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Viswanathan N/LTITL)

Subject: Re: DB2 Connect thru APPC SNA [...] 4992 145 26_Re: Archiving DB2 Database11_K Mahadevan28_kmahadevan@CHENNAI.TCS.CO.IN31_Tue, 11 Jul 2000 20:52:22 +0530366_us-ascii



Hi Donald,

We are using a product called Neararchive supplied by Storagetek corporation for this. We use to archive old databases into tapes using this product . It keeps track of what goes into tape and will provide an efficient means of retrieval. But it needs a bit of application programming to be done to use this software. [...] 5138 105 7_Re: SMS12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Tue, 11 Jul 2000 13:16:25 +1000331_- My Opinions: =========

SMS is great for managing large amounts of DASD across a lot of volumes. It does however have a greater propensity to fragment the dataset into multiple extents. If your shop has RAID or as per Paragraph 3 in 2.2.3 of the red book mentioned below, the impacts of extending are greatly reduced. [...] 5244 225 7_Re: SMS15_Richard Simpson25_rsimpson@MACQUARIE.COM.AU31_Tue, 11 Jul 2000 13:35:30 +1000276_iso-8859-1 I mean what I said, while I know this does not represent actual peak load performance, it is the case that at our site, most Sync IO takes place during the day and it turns out that worst hour IO performance is no worse than 50% more than the average figures. [...] 5470 16 96_Per-Erik Svennberg/P001890/Nordbanken =?iso-8859-1?q?=E4r?= inte =?iso-8859-1?q?p=E5?= kontoret.15_Pelle Svennberg24_per-erik.svennberg@NB.SE29_Tue, 11 Jul 2000 07:20:19 GMT337_iso-8859-1 Jag är borta från kontoret från 2000-07-10 till 2000-07-30.

har semester (välförtjänt) ===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5487 61 101_=?iso-8859-1?Q?Re:_Per-Erik_Svennberg/P001890/Nordbanken_=E4r_int?= =?iso-8859-1?Q?e_p=E5_kontoret.?=0_18_gesquiere.l@PG.COM31_Tue, 11 Jul 2000 08:27:02 +0200652_iso-8859-1 Wat, waar, wanneer,

Ik begrijp Uw bericht niet goed, kan je dat herhalen ?

;-)









Internet Mail Message Received from host: [208.225.214.81] [208.225.214.81]





Envelope Sender: owner-db2-l@ryci.com

From: Pelle Svennberg on 07/11/2000 09:20 AM

Please respond to DB2 Data Base Discussion List

To: db2-l@ryci.com cc: (bcc: Luc Gesquiere-L/PGI) Subject: Per-Erik Svennberg/P001890/Nordbanken är inte på kontoret.







Jag är borta från kontoret från 2000-07-10 till 2000-07-30. [...] 5549 146 60_AW: DB2 for OS/390 Performance Monitor - which one? - thanks12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 11 Jul 2000 08:43:47 +0200573_iso-8859-1 Thanks, Deanna.

With kind regards - mit freundlichen Grüssen, G e o r g H . P e t e r DB2 Data Base Administrator c/o ------------------------------------------------------------------------ Datenzentrale Baden-Wuerttemberg Department Product Support Krailenshaldenstrasse 44, 70469 Stuttgart, Germany Phone 0049-711-8108-271 PC-Fax 004971189696071 ------------------------------------------------------------------------

No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 5696 40 40_Regarding DB2 UDB 6.1 on Sun Solaris 2.78_DVRAMESH16_DVRAMESH@INF.COM31_Tue, 11 Jul 2000 12:13:41 +0530550_iso-8859-1 Hi All,

We have installed UDB 6.1 on Sunsolaris 2.7, Model is E4500. Installation is through and when we try to create instance the installation menu gets hanged for hours. After installing we have applied UDB61_fixpak3.tar i.e. is PTF Number U469704.

******************************************************************** Product Name : DB2 for Solaris Version 6.1 PTF Number : U469704 Package Date : Wed Feb 23 18:21:18 EST 2000 Build Level : s000217 ******************************************************************** [...] 5737 29 7_Re: SMS14_Massimo Scarpa23_Massimo_Scarpa@DEBIS.IT31_Tue, 11 Jul 2000 09:29:03 +0100377_us-ascii I agree with Joel. In the new shop where I work I see that IOSQ decreased a lot (almost 0) with new SHARK DASDs (I had

some doubt about SHARK before) . In previous shops IOSQ (and dataset placement using SMS !!!) were the two main

contributors in I/O delay. It's not easy to convince a DASD manager to create a storage group for DB2 TS, one for DB2 [...] 5767 54 28_Re: DB2/OS390 DSN1CHKR usage20_Pav Kumar-Chatterjee17_pavkc@HOTMAIL.COM29_Tue, 11 Jul 2000 07:30:38 GMT350_- Ron

From past experience, you never know when the catalog becomes corrupt until it it too late! If you already have something in place, then continue running it. The suggestion that someone made of running it against image copies is a good one, and should reduce your down time.

Whichever way you go - keep checking the catalog. [...] 5822 67 7_Re: SMS12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Tue, 11 Jul 2000 09:48:58 +0200543_- Hi, Do I dare to say "it depends"? SMS is a bit more than a bare DASD management function. In my opinion it is more like a storage management application, and needs proper planning and software developement. It can be made work well but requires quite a customization effort. I earlier worked at a site where we used SMS and coded ACS routines specially for DB2 dataset placement. We had to, because we did not have enough DBA's to take care of all of the DB2 data placements manually, but we had the SMS expertice. Where I work now we [...] 5890 39 34_potential employment opportunities10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Tue, 11 Jul 2000 11:15:26 +0100347_iso-8859-1 Hi list members,

I am looking for DB2 administration and performance tuning work both at SQL and system level.

I have been working with DB2 on the OS/390 platform for 14 years.

My one requirement is that I would be home based.

If anyone is interested please get in touch and I can provide more details. [...] 5930 29 23_Problem in invoking QMF13_Mayank Sharma33_Mayank.Sharma@POWAIMAIL.LTITL.COM31_Tue, 11 Jul 2000 14:25:08 +0530387_us-ascii Hi all,

We have installed QMF in our DB2 Version 6 on Os390. When We are trying to invoke QMF thru TSO it gives error -

"DSQ14440 Internal error detected in Module DSQISRM"

This load module is in QMF610.ADSQOBJ.This is a distribution Library . we are using command to invoke QMF - tso call 'qmf610.sdsqload(dsqqmfe)' 'DSQSSUBS=ssnm,DSQSPLAN=DSQIN610'. [...] 5960 53 28_Re: DB2/OS390 DSN1CHKR usage0_27_jim.leask@RS-COMPONENTS.COM31_Tue, 11 Jul 2000 10:21:28 +0100627_iso-8859-1 I only run it at DB2 migrate time.

-----Original Message----- From: Ron Johnson [mailto:rjohnson@DATA-TRONICS.COM] Sent: 10 July 2000 16:40 To: DB2-L@RYCI.COM Subject: DB2/OS390 DSN1CHKR usage



We currently run DSN1CHKR on catalog tablespaces during a weekly scheduled IPL and DB2 downtime. This has been done since before my time as a DBA and in over 9 years it has yet to find any problems in the catalog. This is falling into the catagory "Do we really need to do this??". The utility guide says "Use DSN1CHKR on a regular basis to detect any damage to the catalog and directory as soon [...] 6014 44 27_Re: Problem in invoking QMF10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Tue, 11 Jul 2000 11:33:08 +0100438_iso-8859-1 nasty. I would contact ibm or check you have gone through the installation process correctly.

-----Original Message----- From: Mayank Sharma [mailto:Mayank.Sharma@POWAIMAIL.LTITL.COM] Sent: Tuesday, July 11, 2000 8:55 AM To: DB2-L@RYCI.COM Subject: Problem in invoking QMF



Hi all,

We have installed QMF in our DB2 Version 6 on Os390. When We are trying to invoke QMF thru TSO it gives error - [...] 6059 20 47_How to dtermine if HW-Compression supported ...15_Matthias Pohlig27_matthias.pohlig@T-ONLINE.DE31_Tue, 11 Jul 2000 04:26:30 -0500368_- Hello everyone...

I'm currently in a "foreign" shop and discussing DB2-Compression for a couple of tables ... Can't reach the SYSPROG and was wondering if any of you gurus could give me a hint how to dtermine if ESA Hardware-Compression is supported on the CECS I'm working on so DB2 compression will take the better benefits ? Thx for any comments .... [...] 6080 100 51_AW: How to dtermine if HW-Compression supported ...16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Tue, 11 Jul 2000 12:20:53 +0200401_iso-8859-1 Do the following to tell if the system contains the software or hardware to run a CSRCMPSC macro:

1. Determine if CSRCMPSC is available, by running the following:

L 15,16 Get CVT address USING CVT,15 Set up addressability to the C TM CVTFLAG2,CVTCMPSC Is CSRCMPSC available? BZ NO_CSRCMPSC Branch if not available * Compression feature is available . . NO_CSRCMPSC DS 0H [...] 6181 56 27_Re: Problem in invoking QMF0_16_dbussier@CSC.COM31_Tue, 11 Jul 2000 06:27:15 -0400492_us-ascii Hi Mayank.

I saw the same problem with QMF when migrating DB2 to V6-0003, IBM's solution was to rebind the QMF611 plan under DB2 V6. This worked fine. Also we were able to fall back without rebinding QMF611 under V5.

Hope this helps, Danny







Mayank.Sharma@powaimail.ltitl.com on 07/11/2000 04:55:08 AM

Please respond to DB2-L@ryci.com

To: DB2-L@ryci.com cc: (bcc: Danny Bussiere/GIS/CSC) Subject: Problem in invoking QMF [...] 6238 26 27_Renaming cf structure names11_Rolf Schwab28_rolf.schwab@CREDIT-SUISSE.CH31_Tue, 11 Jul 2000 12:14:27 +0200399_iso-8859-1 dear list-members

we renamed our coupling facility structure names. in order to restart the db2 we adjusted the zparm-module parameter group=... the problem now is: we can't adjust the group name in the bsds-file and therefore can't restart db2. it says mismatch between bsds and zparm-module (which is true infact). does anybody know how to change the groupname in the bsds? [...] 6265 59 31_Re: Renaming cf structure names23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 11 Jul 2000 11:37:51 +0100363_iso-8859-1 ....the answer to your question is NO if you wish to use the supplied tools(DSNJU003).

However, the answer is YES you can do it. I have had many discussions with the IBM labs of late about all this and I do beleive they have now accepted that it might be a good idea to be able to do this. So in time DSNJU003 will get fixed sorry changed. [...] 6325 18 18_Encryption of data12_Diwakar Goel32_Diwakar.Goel@VASHIMAIL.LTITL.COM31_Tue, 11 Jul 2000 17:15:28 +0530400_us-ascii Hi all ! We have a problem. How to encrypt the data while sending from web server ( Domino Go ) to DB2 UDB 5.2 on AIX.



Regards Diwakar

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 6344 270 7_Re: SMS12_Kent Collins15_kcollins@WF.NET31_Tue, 11 Jul 2000 07:06:32 -0500576_iso-8859-1 Between 2-3 years ago this discussion went on for days on the list. Since that time there have been some questions regarding SMS or No-SMS for mainframe Dataset management. Also was the discussion of extent management.

The tide has changed. It was clear to several of us that for most shops and most IO workloads that extent management on steroids was a bad idea and had no merit with regards to performance. Here I am talking about eliminating extents. Some management of extents is required if for no other reason than the extent limit for a dataset. [...] 6615 33 44_DB2 OS/390 Bind failure for DRDA application0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Tue, 11 Jul 2000 08:14:40 -0400663_us-ascii Hi,

I was hoping someone could identify the cause of the failure below. Documentation is non-existent on the error. DB2 OS/390 is performing the bind to a DB2 UDB server using location MBLDB

Thanks

Ed



BIND PACKAGE (MBLDB.UDBBAT) MEMBER(UDB100) LIBRARY('TEST.DB2.DBRMLIB') (I) ISOLATION(CS) RELEASE(COMMIT) DYNAMICRULES(RUN) DSNT270I - THE FOLLOWING SQLCA INFORMATION WAS RETURNED FROM SQLJPSEM SQLCODE = -4930 SQLSTATE = SQLERRMT = VERSION (DRDA Bind Option VRSNAM) SQLWARN 0= ,1= ,2= ,3= ,4= ,5= ,6= ,7= ,8= ,9= ,A= DSNT233I - UNSUCCESSFUL BIND FOR PACKAGE = MBLDB.UDBBAT.UDB100.(2000-07-11-11.38.47.775475) DSN [...] 6649 65 48_Re: DB2 OS/390 Bind failure for DRDA application0_23_sjtoeniskoetter@AEP.COM31_Tue, 11 Jul 2000 08:20:34 -0400414_us-ascii From the DB2/AIX Message Reference:

SQL4930N Bind or precompile option or option value "" is invalid. Explanation: Either "" is an invalid bind or precompile option or the value specified for this option is invalid. The bind or precompile cannot continue. User Response: Correct the bind or precompile option or option value and retry the bind or precompile command. [...] 6715 61 48_Re: DB2 OS/390 Bind failure for DRDA application16_Michael McCarthy18_mmccarthy@DTCC.COM31_Tue, 11 Jul 2000 08:40:04 -0400472_us-ascii Ed,

You cannot specify a VERSION during your precompile for a package to be bound on a UDB database. As of UDB V6.1, package versioning is not supported.

-Mike.









Ed_Vetock@NAVYFEDERAL.ORG on 07/11/2000 08:14:40 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Michael McCarthy/DTC) Subject: [DB2-L] DB2 OS/390 Bind failure for DRDA application [...] 6777 85 26_Re: Archiving DB2 Database7_Jim Lee25_JLee@PRINCETONSOFTECH.COM31_Tue, 11 Jul 2000 08:54:39 -0400482_- Donald

There are a number of things to consider when looking for an "archive" solution. Some items of importance include the complexity of the data model, referential integrity ( DB2 and/or application enforced), the need to do partial and full restoration of data for your auditors and the ability to quickly locate the necessary data. These factors are even more important since you are searching for general solution that can be used for more than one application. [...] 6863 34 11_Re: Extents14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 11 Jul 2000 08:55:51 -0400535_ISO-8859-1 Roger Miller answered this a couple of months ago, and his answer will be in the archives. There is a some small CPU impact for large numbers of extents.

Regards, Joel +++++++++++++++++++++++++++++++++++++++++++++++++++++++

Message text written by DB2 Data Base Discussion List >None of this, unfortunately, addresses the question I asked about extents. Does anyone know whether, in the absence of head movement concerns, extents are still something to worry about for large tables and if they are, why ? [...] 6898 22 22_Re: Index on 2 tables?20_Baskaran Subramaniam27_subramaniam.baskaran@DB.COM31_Tue, 11 Jul 2000 18:29:32 +0530560_us-ascii Hello Listusers, Linda & Terry Purcell

On creating an index on ILTFST table, columns ILTFS_MSGREF, ILTFS_MSGINFO DESCENDING, we were able to reduce the execution time by alomost 80 %.

Thanks for Linda & Terry for suggesting the same.

Thanks to all others who responded.

Rgds Baskar

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 6921 72 28_Re: DB2/OS390 DSN1CHKR usage14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Tue, 11 Jul 2000 11:42:39 +0100487_iso-8859-1 In a previous life (when I had control of such affairs), before SMS, we image copied to tape, and I did a DSN1CHKR against the image copies (to check the pages AND the validity of the copy). I also DSN1COPYed some catalog/directory tablespaces with PARM='CHECK'. I also ran a REPAIR TEST DBD against every database. Call it paranoia, but we were caught out on each one of those, so we built contingencies against them happening in the future. Oh, and we ran them weekly. [...] 6994 93 48_Re: DB2 OS/390 Bind failure for DRDA application0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Tue, 11 Jul 2000 09:39:14 -0400651_us-ascii To all,

Thanks for responding. Once we removed the precompile (version) option, we had success.

R/...Ed







Michael McCarthy @RYCI.COM> on 07/11/2000 08:40:04 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: DB2 OS/390 Bind failure for DRDA application



Ed,

You cannot specify a VERSION during your precompile for a package to be bound on a UDB database. As of UDB V6.1, package versioning is not supported. [...] 7088 29 55_Re: AW: How to dtermine if HW-Compression supported ...15_Matthias Pohlig27_matthias.pohlig@T-ONLINE.DE31_Tue, 11 Jul 2000 08:35:59 -0500676_- Thanks for your help !

I re-did this in rexx, if anyone needs it: <------------------------------------------------------------------> /* REXX - show HWcomp feature */ cvt = storage(10,4) cmpflag = x2b(storage(d2x(x2d(c2x(cvt))+x2d(0179)),1)) CVTCMPSC = substr(cmpflag,1,1) CVTCMPSH = substr(cmpflag,2,1) say "Compress Instruction supported ? <"CVTCMPSC">" say "Hardware assisted ? <"CVTCMPSH">"

return <------------------------------------------------------------------>

BTW: the shop does semm to have the compression instruction, but *not* hardware assisted ...anyone got a clue how much "overhead" that means (rough estimate, twice ? thrice ?) [...] 7118 32 22_How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Tue, 11 Jul 2000 10:13:14 -0400483_us-ascii This one is probably simple but it is driving me crazy.

The is a stored procedure that includes a VARCHAR(3500) as an OUT variable. The program calling it has the 'two-part' length and data fields in working storage and the stored procedure has the same in the linkage section. After calling and successfully returning from the stored procedure, the VARCHAR field has no length part and only contains 255 characters - it looks just like a CHAR(255) was defined. [...] 7151 85 26_AW: How big's the VARCHAR?12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 11 Jul 2000 16:20:07 +0200568_iso-8859-1 Martin,

how is the column defined ? VARCHAR(3500) or as a LONG VARCHAR(3500) ? May be in this area the error occurs....

HTH.

With kind regards - mit freundlichen Grüssen, G e o r g H . P e t e r DB2 Data Base Administrator c/o ------------------------------------------------------------------------ Datenzentrale Baden-Wuerttemberg Department Product Support Krailenshaldenstrasse 44, 70469 Stuttgart, Germany Phone 0049-711-8108-271 PC-Fax 004971189696071 ------------------------------------------------------------------------ [...] 7237 83 30_Re: AW: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Tue, 11 Jul 2000 10:33:54 -0400306_iso-8859-1 That was quick! The use of the word 'LONG' is not supported in the stored procedure definitions like it is in table definitions. It gives a DSNX902E when the SP is restarted.

As I said before, DB2 seems to support the VARCHAR(3500) definition but it is handling it like a CHAR(255). [...] 7321 83 21_Re: Stored Procedures15_Morris, Deborah20_DMorris@UNCH.UNC.EDU31_Tue, 11 Jul 2000 10:44:00 -0400409_iso-8859-1 Thanks for your input. That's what I was hoping for since the application will eventually be re-written as a client application. So, I was wanting to benefit from the code re-usability.

Deborah A. Morris Supervisor DataBase Administration UNC Health Care System 2nd Floor Carr Mill Mall Carrboro, NC 27510

(919) 966-2197 (voice) (919) 966-2110 (fax) mailto: dmorris@unch.unc.edu [...] 7405 34 19_DB2 RECOVER utility19_Peggs, Neal (ANFIS)22_neal.peggs@ANFIS.CO.UK31_Tue, 11 Jul 2000 15:39:50 +0100353_- Hi,

I recently ran a RECOVER to restore approx 180 tables in a database. I thought the job had worked OK but later found out that someone had altered 3 of the tables and then backed out those changes. This had caused the tables to be dropped and recreated with different PSID and OBID's after the image copy that I was using for recovery. [...] 7440 24 33_Re: SQLVAR length in DB2/OS390 V612_Roger Miller19_millerrl@US.IBM.COM31_Tue, 11 Jul 2000 09:55:21 -0500639_- I wanted to make sure this is in the books. This is a paste from the V6 Installation Guide, from the latest version on the web.

"Support for large objects:The following incompatibilities result from the expansion of the SQLDA to support large objects:

"Because of the additional data types supported in Version 6,the host language statements generated by the precompiler for each SQL statement include a larger parameter list. As a result,the size of the object code that results from compiling the output of the precompiler increases. This increase varies according to the number of host variables that are specified [...] 7465 122 51_Re: How to dtermine if HW-Compression supported ...0_18_mebert@AMADEUS.NET31_Tue, 11 Jul 2000 17:20:24 +0100641_us-ascii I think there is an error in the code: it should be

cmpflag = x2b(c2x(storage(d2x(c2d(cvt)+x2d(0179)),1)))

I added the c2x() function and I also replaced x2d(c2x(cvt)) by c2d(cvt). This new version agrees with the assembler one (test high bits). On our machines (both 9672, but different models) the STORAGE location is x'F8'=character "8" in one case, but x'E8'=character "Y" in the other, and here the x2b() fails, because "Y" is not a hex string representation - that's how I noticed. Using the original code we get the same result as you (Instruction supported, no HW compression) but the other version gives [...] 7588 183 51_Re: How to dtermine if HW-Compression supported ...15_Matthias Pohlig27_matthias.pohlig@T-ONLINE.DE31_Tue, 11 Jul 2000 10:22:47 -0500393_- Oh mea culpa ...mea maxima culpa...

Thank you very much for studying this quick piece so intense ! I saw the "F8" , too , was lucky that I found the bits - (had tested with a "say..") and then forgot that I had them in "displayable" format and must re-encode to hex before applying "x2b". (the other thing is functionally equivalent, but your version saves one function call :-) [...] 7772 26 44_Re: System R - The father of DB2 Optimizer ?12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 11 Jul 2000 10:35:47 -0500585_- The real formulas are extremely complicated and very proprietary. I don't think that the System/R formulas were ever published. What I see discussed are out of date, very high level overviews, and often reverse engineered. That is to say that you need to expect significant errors in any formulas that you read, unless they are stolen. The formulas change with every release, so you need to be sure that the information you are using matches the release you are running. Some of the PTFs make changes too. Some of the techniques were patented, so those can be examined, but the [...] 7799 15 51_AW: How to dtermine if HW-Compression supported ...16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Tue, 11 Jul 2000 17:56:32 +0200351_- The meaning of CVTFLAG2 is described in the "OS/390 V2R6.0 MVS Data Areas, Vol 1 (ABEP-DALT)"

Roland

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 7815 34 44_DB2 Connect License problems.. SQLSTATE 800132_Kumar, Hemant (CORP, Consultant)29_Hemant.Kumar@CORPORATE.GE.COM31_Tue, 11 Jul 2000 12:07:38 -0400302_- Hi Listusers,

I recently downloaded DB2 UDB 7.1 personal edition to my PC (Win 95) and am trying to use DB2 Connect to connect to DB2 on OS/390. I used CCA to configure my connection and it goes through fine but when I actually try to connect to host it gives me an error: SQLSTATE 8001: [...] 7850 25 28_DB2 running under OS390 2.8:14_jack fernicola20_bowling730@YAHOO.COM31_Tue, 11 Jul 2000 09:35:17 -0700592_us-ascii Hello list, We are planning to upgrade MVS to OS390 2.8. Is there any concerns (possible ptfs) with DB2 V4R1 or V5R1, that we should be aware of? Thanks, Jack

===== Jack Fernicola

__________________________________________________ Do You Yahoo!? Get Yahoo! Mail – Free email you can access from anywhere! http://mail.yahoo.com/

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 7876 63 48_Re: DB2 Connect License problems.. SQLSTATE 80010_15_leon@CA.IBM.COM31_Tue, 11 Jul 2000 12:36:00 -0400426_us-ascii DB2 UDB Personal Edition is not the same thing as DB2 Connect Personal Edition. These are 2 separate and unrelated products. DB2 UDB Personal Edition does not contain DB2 Connect. You need to purchase DB2 Connect separately.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Kumar, Hemant (CORP, Consultant)" on 07/11/2000 12:07:38 PM [...] 7940 142 48_Re: DB2 Connect License problems.. SQLSTATE 800113_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Tue, 11 Jul 2000 11:36:04 -0500445_US-ASCII Hemant, You need to add / update your license(s) via the nodelock administration tool. You should be able to find it in the path:\windows\start menu\programs\License Use Runtime directory. If you have a valid license(s) click on products then click new, Update the license(s) keys. If no products are shown, click on import and add the license(s) from the path:\sqllib\misc directory they should have an extension of .lic HTH Kurt [...] 8083 10 23_Re: DB2 RECOVER utility9_Jim Ruddy18_jaruddy@US.IBM.COM31_Tue, 11 Jul 2000 11:25:03 -0500303_- Neal you should open a problem so this can be investigated.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8094 159 24_Re: IMS to DB2 Migration0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 11 Jul 2000 11:29:43 -0500589_us-ascii Vijay: We finished our conversion from a DBMS that was like IMS (not actually IMS) to DB2 5 years ago.

Check the archives, this question has been asked before.

Jay's second point, don't change IMS DB calls directly to DB2 SELECTS (etc) is very important. To expand on that point: - BEFORE starting coding define DB2 coding standards. Don't rely on conversion effort to define them for you. If conversion is outsourced then their main concern will be to get the job done, not to create good DB2 code. - avoid falling in the trap where the converted code defines [...] 8254 66 34_RUNSTATS question - DB2 V5 / OS39010_Marc Fuqua19_marcfuqua@YAHOO.COM31_Tue, 11 Jul 2000 09:49:05 -0700572_us-ascii Hello list -

We are running DB2 V5 on OS390 & are looking for ways to improve the performance of one of our weekly RUNSTATS jobs.

The job updates the stats for a production tablespace (there is one table in the TS) with 64 partitions, of which partitions 1 - 46 are currently 'active' (contain rows). We 'fill' a partition every 4 - 5 weeks, depending on the application's workload. There are approximately 160 million rows total in the table, with an average of 3.5 million rows per partition. There are seven NPIs on the table & each NPI has [...] 8321 49 28_Re: DB2/OS390 DSN1CHKR usage12_Sue Janowitz18_SJanowitz@NEFN.COM31_Tue, 11 Jul 2000 13:20:26 -0400640_us-ascii We only run DSN1CHKR on the catalog at migration time.

Sue Janowitz DB2 Systems Administrator New England Financial Information Systems Boston, MA SJanowitz@nefn.com



> -----Original Message----- > From: Ron Johnson [SMTP:rjohnson@DATA-TRONICS.COM] > Sent: Monday, July 10, 2000 11:40 AM > To: DB2-L@RYCI.COM > Subject: DB2/OS390 DSN1CHKR usage > > We currently run DSN1CHKR on catalog tablespaces during a weekly scheduled > IPL and DB2 downtime. This has been done since before my time as a DBA > and > in over 9 years it has yet to find any problems in the catalog. This is > falling into the catagory [...] 8371 31 22_Point in time recovery14_Mike Lebermann18_Lebermm@BCBSKC.COM31_Tue, 11 Jul 2000 12:12:32 -0500590_US-ASCII Hello list,

I am testing archive logging and have been unable to restore a tablespace to a point in time. This test scenario is from a full database backup (offline) using ADSM. The environment is DB2 EE V5 fixpack 12, on AIX v 4.3.2.

I am trying this from the control center. When I specify a point in time to rollforward to, I am using the ending timestamp (local time) of the backup from the recovery history file. The student notebook from the Advanced Admin Workshop specifically says to use local time. The rollforward fails with an SQL1275N "The stoptime [...] 8403 27 26_Re: Archiving DB2 Database9_Colin Fay13_cfay2@CSC.COM31_Tue, 11 Jul 2000 13:33:00 -0400473_us-ascii Donald,

If your data is already keyed on fiscal year (or date), partitioning the data by fiscal year is a great solution as it separates the current data for regular processing but requires no new applicaitons to get at the old data. If you think that 'running out of space ' is a problem compare the cost of DASD to imlementing a 'data warehouse'. If you can implement this without any significant application development , you will look like a hero. [...] 8431 36 47_DB2 development/testing/production environments5_Riyaz15_RMomin@FHSC.COM31_Tue, 11 Jul 2000 13:54:45 -0400429_US-ASCII

First, thanks for the opportunity to pose my questions to the group for the purpose of generating ideas and issues.

I am interested in discovering how other companies have set up their DB2 environments to support multiple testing stages. By "multiple testing stages", I mean the different sets of tables to support a unit test stage, a systems test stage, and a quality assurance stage, for example. [...] 8468 112 48_Re: DB2 Connect License problems.. SQLSTATE 800132_Kumar, Hemant (CORP, Consultant)29_Hemant.Kumar@CORPORATE.GE.COM31_Tue, 11 Jul 2000 14:16:32 -0400273_- Hi Leon,

Thanks for the mail. Basically I was tryng to connect to DB2 5.1 running on OS/390 from my PC. I was using CCA on DB2 UDB (7.1) that I had installed on my PC to add a remote database. When I issued the connect command it gave me the following error: [...] 8581 64 51_Re: DB2 development/testing/production environments32_Kumar, Hemant (CORP, Consultant)29_Hemant.Kumar@CORPORATE.GE.COM31_Tue, 11 Jul 2000 14:31:01 -0400568_- Hi Riyaz,

Your assumption is right on. Wherever I have worked we had a production subsystem and multiple test subsystems (unit, integration etc). But then within each subsystem we also have multiple sets of tables with different creator IDs for different applications. So you could get away with just one susbsytem for test (with proper info on what creator ID to use for which 'stage') and save cost. The con would be probabilty of confusion on what set of tables to use for which stage. I would leave it to the DBAs to come up with thier point of view [...] 8646 142 48_Re: DB2 Connect License problems.. SQLSTATE 80010_15_leon@CA.IBM.COM31_Tue, 11 Jul 2000 14:34:32 -0400444_us-ascii As I said in my original post DB2 UDB Personal Edition does NOT provide capability to connect to DB2 fo OS/390 database servers. You need to purchase DB2 Connect product.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Kumar, Hemant (CORP, Consultant)" on 07/11/2000 02:16:32 PM

Please respond to DB2 Data Base Discussion List [...] 8789 17 13_BSDS Question10_Lee Mandel16_DBMSUser@AOL.COM29_Tue, 11 Jul 2000 14:34:55 EDT498_US-ASCII Hi All, Can anyone point me in the right direction to find how the BSDS is structured? Is there any documentation on the file Keys? Is there Access Method Services IDCAMS to print this information? Any help would be appreciated. Thanks In Advance.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8807 119 51_Re: DB2 development/testing/production environments12_craig patton21_prgpatton@HOTMAIL.COM29_Tue, 11 Jul 2000 11:45:51 PDT656_- HI Riyaz,

In most of the environments I have been involved with, separate subsystems were used for at least a TEST, ACCEPTANCE and PRODUCTION, where TEST normally had several occurrances of objects with differing CREATORs for the tables.

The biggest issue in having all testing objects in the same Subsystem as Production will be the performance of THAT subsystem. If your application folks are continuously testing (compiling/binding and running test scenarios) they will burn a good many of the DB2 available service units. This can have a tremendously negative effect production performance (not to mention causing a ton of Catalog [...] 8927 55 51_Re: DB2 development/testing/production environments24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Tue, 11 Jul 2000 11:43:48 -0700635_- We're running most test databases in a single DB2 subsystem and using a different owner/qualifier for each set of test tables. We've not found any reason to go to multiple subsystems, except for our PeopleSoft databases where we have separate test and production.

> -----Original Message----- > From: Riyaz [SMTP:RMomin@FHSC.COM] > Sent: Tuesday, July 11, 2000 10:55 AM > To: DB2-L@RYCI.COM > Subject: DB2 development/testing/production environments > > > First, thanks for the opportunity to pose my questions to the group for > the > purpose of generating ideas and issues. > > I am interested in discovering how other [...] 8983 55 32_Re: DB2 running under OS390 2.8:10_WAYNE FISH22_WFISH@IDPH.STATE.IL.US31_Tue, 11 Jul 2000 12:48:52 -0500440_ISO-8859-1 unsubscribe

>>> jack fernicola 07/11/00 11:35AM >>> Hello list, We are planning to upgrade MVS to OS390 2.8. Is there any concerns (possible ptfs) with DB2 V4R1 or V5R1, that we should be aware of? Thanks, Jack

===== Jack Fernicola

__________________________________________________ Do You Yahoo!? Get Yahoo! Mail û Free email you can access from anywhere! http://mail.yahoo.com/ [...] 9039 192 42_QUERY GIVES DIFFERENT RESULTS IN V6 AND V523_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Tue, 11 Jul 2000 15:23:15 -0400501_iso-8859-1 We have a view definition as follows

CREATE VIEW CED_CLIENT_MASTER AS

SELECT VALUE(V3.RELM_ID,V3.FAM_NUMBER) CLIENT_ID, VALUE(V3.RELM_NME,V3.FAM_NAME) CLIENT_NME FROM (SELECT V1.RELM_ID,V1.RELM_NME,V2.FAM_NUMBER,V2.FAM_NAME FROM (SELECT A.RELM_ID,A.RELM_NME FROM RELM.RELMNOTB A, RELM.RELMSFTB B WHERE B.RELM_ID = A.RELM_ID AND B.SFAM_CD = 'CL' ) AS V1 FULL OUTER JOIN (SELECT C.FAM_NUMBER, C.FAM_NAME FROM SPEC.SPCFAMTB C ) AS V2 ON V1.RELM_ID = V2.FAM_NUMBER ) AS V3 ; [...] 9232 46 24_DB2/OS390 DSN1CHKR usage12_Alan C Smith26_alan@ALANSMITH.DEMON.CO.UK31_Tue, 11 Jul 2000 20:21:51 +0100552_us-ascii We run DSN1CHKR and DSN1COPY with CHECK regularly (I think weekly) against copies of our catalog tablespaces so they don't need to be stopped. This has thrown up corruption a couple of times - this was in a very volatile test system. I don't think these cover everything - I found some RI constraints which had been violated in SYSPKAGE the other week (DB2 blissfully unaware) - had to run CHECK DATA SCOPE ALL to clear it up. The DSNTESQ queries (from the install) are also a good idea, but take so long that we only attempt to run them [...] 9279 123 38_Re: RUNSTATS question - DB2 V5 / OS3900_19_Tim.Lowe@STPAUL.COM31_Tue, 11 Jul 2000 14:14:33 -0500603_us-ascii Marc, When you ran runstats on individual partitions, did you specify that you only wanted stats on the same part of the partitioning index? If this is not specified, then it would gather stats on all parts of the partitioning index even though you are only gathering stats on a single part of the tablespace, which is a lot of extra processing! For example, have you tried: RUNSTATS TABLESPACE dbname.tsname PART 003 TABLE INDEX (ixowner.ixname PART 003 KEYCARD FREQVAL NUMCOLS 1 COUNT 10) And, since it sounds as though you are primarily inserting rows into a single partition, would it [...] 9403 233 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V50_23_sjtoeniskoetter@AEP.COM31_Tue, 11 Jul 2000 15:34:44 -0400365_us-ascii You may want to look at page 2-50 in the V5 Application Programming and SQL Guide. The section SQL Rules for Statements Containing Join Operations has a discussion that looks very similar to your query. I was told they were going to allow the rules to be changed via a new zparm setting, but I didn't follow up on what actually was implemented in V6. [...] 9637 63 32_Application performance question11_Anshu Mohan20_anshu_garg@YAHOO.COM31_Tue, 11 Jul 2000 12:29:31 -0700362_us-ascii Hi !

Have a performance doubt.

A DB2 program accepts input from a flat file for column COL1. There are 125,000 different values of COL1 in the program. The logic of program is :

Do while EOF ACCEPT COL1 DECLARE CURSOR C1 FOR SELECT * FROM T1 WHERE COL1 = :COL1 END-EXEC OPEN CURSOR C1 FETCH UNTIL SQLCODE = 100 CLOSE C1 End-do [...] 9701 285 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V513_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Tue, 11 Jul 2000 12:45:31 -0700518_iso-8859-1 Hi !

Just for curiosity sake , did you try

SELECT * FROM ( SELECT VALUE(V3.RELM_ID,V3.FAM_NUMBER) CLIENT_ID, VALUE(V3.RELM_NME,V3.FAM_NAME) CLIENT_NME FROM (SELECT V1.RELM_ID,V1.RELM_NME,V2.FAM_NUMBER,V2.FAM_NAME FROM (SELECT A.RELM_ID,A.RELM_NME FROM RELM.RELMNOTB A, RELM.RELMSFTB B WHERE B.RELM_ID = A.RELM_ID AND B.SFAM_CD = 'CL' ) AS V1 FULL OUTER JOIN (SELECT C.FAM_NUMBER, C.FAM_NAME FROM SPEC.SPCFAMTB C ) AS V2 ON V1.RELM_ID = V2.FAM_NUMBER ) AS V3 ) V4 WHERE CLIENT_ID = 'AAD'; [...] 9987 97 51_Re: DB2 development/testing/production environments0_19_Tim.Lowe@STPAUL.COM31_Tue, 11 Jul 2000 14:40:17 -0500553_us-ascii Riyaz, I have done it both ways at different shops. In my opinion: If you have separate DB2 subsystems for each stage, there is a certain amount of additional work for the DB2 catalogs for each subsystem and the tables for any products that you use. If you put several "stages" in the same DB2 subsystem, then since DB2 PLANs must be unique, you need to be extremely careful that the "stages" are not corrupted by mixing tables from different stages together in the same unit of work or in the same transaction flow. If you go this way, I [...] 10085 40 51_Re: DB2 development/testing/production environments13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 11 Jul 2000 15:54:07 -0400373_iso-8859-1 Hi Riyaz, I have seen several flavors of test/QA/prod support in single and multiple subsystems. What has worked best for developers, users and testers is separate subsystems for each.

What has worked worst is putting them ALL in one subsystem. I think it is extremely ill-advised to combine production with any other work in the same DB2 subsystem. [...] 10126 329 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V523_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Tue, 11 Jul 2000 15:58:49 -0400658_iso-8859-1 Yes I did, It returns result in V5 and none in v6.

Sanjay

> -----Original Message----- > From: Mohan, Ashish [SMTP:Ashish.Mohan@NIKE.COM] > Sent: Tuesday, July 11, 2000 3:46 PM > To: DB2-L@RYCI.COM > Subject: Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V5 > > Hi ! > > Just for curiosity sake , did you try > > SELECT * FROM > ( > SELECT > VALUE(V3.RELM_ID,V3.FAM_NUMBER) CLIENT_ID, > VALUE(V3.RELM_NME,V3.FAM_NAME) CLIENT_NME > FROM > (SELECT V1.RELM_ID,V1.RELM_NME,V2.FAM_NUMBER,V2.FAM_NAME > FROM > (SELECT A.RELM_ID,A.RELM_NME > FROM RELM.RELMNOTB A, > RELM.RELMSFTB B > WHERE B.RELM_ID = A.RELM_ID AND > B.SFAM_CD = 'CL' ) [...] 10456 25 51_Re: DB2 development/testing/production environments15_Melvin Demmicks25_melvin_demmicks@YAHOO.COM31_Tue, 11 Jul 2000 13:00:11 -0700445_us-ascii Many years ago, I worked on a large project with many consultants(they were too snooty to be called developers or DBAs) from one of the major accounting firms here in the US.

The joke then was that those consultants each needed their OWN DB2 subsystem.





__________________________________________________ Do You Yahoo!? Get Yahoo! Mail – Free email you can access from anywhere! http://mail.yahoo.com/ [...] 10482 20 32_DB2 V7 for OS/390 - who's going?11_Burch, Todd18_Todd_Burch@BMC.COM31_Tue, 11 Jul 2000 14:52:26 -0500400_iso-8859-1 A lot of people are inquiring about the migration from V5 or V6 to V7, but who's really planning on migrating right away?

Or, maybe more specifically, what features in DB2 V7 for OS/390 are you needing right now (or will at GA) that V5 or V6 cannot satisfy that are inhibitors to your current use of DB2, or, will help you meet your business objectives that are not being met? [...] 10503 17 32_Re: DB2 running under OS390 2.8:0_17_JTonchick@AOL.COM29_Tue, 11 Jul 2000 16:04:27 EDT379_US-ASCII We've been running DB2 v5.1 (pdo9952) on OS/390 2.8 (servicepak 9907) since April, no problems.

Jim Tonchick Fiserv, Inc.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10521 76 51_Re: DB2 development/testing/production environments0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 11 Jul 2000 15:12:16 -0500584_us-ascii We have a Prod and a Devel DB2 Subsystem, all on 1 LPAR. Within the Devel subsystem we have 25 development databases defined. Each DB has a unique DB name as well as creator name (based on DB name) and collection id. We have separate CICS regions associated with each DB. Each DB has a full, or nearly full copy of all production table definitions (a very few tables are synonymed to other devel DB's). Each development project is usually assigned (by co-operation between the development teams, DBA stays out of assignment) 1 or 2 test DBs (devel and system/acceptance [...] 10598 24 31_Subsets of Large Tables with RI13_Leo Conchello18_lconche@USWEST.COM31_Tue, 11 Jul 2000 15:04:36 -0600362_us-ascii Can anyone suggest any software tools capable of creating subsets of large DB2 tables? (OS/390). I believe that Move for DB2 from Princeton Softech can do it. Are there any others?

An essential requirement is that the software must be able to read from Image Copy or Unload datasets. Also, it must preserve RI. Any ideas? Thanks in advance. [...] 10623 113 36_Re: Application performance question0_18_damcon2@US.IBM.COM31_Tue, 11 Jul 2000 16:47:23 -0400571_iso-8859-1 Anshu,

You're on the right track. However, instead of inserting the 125,000 rows into the scratch table, I would suggest using the load utility. As for the query, I would suggest using a join instead of an in list from a subselect.

Cheers, Jay



Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - BMW ----------------------------------------------------- [...] 10737 43 43_Creating/Stripping Data as Test DB2 tables.17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Tue, 11 Jul 2000 14:10:43 -0700436_US-ASCII Hello,

The problem were faced is to Create a Strip down version of the Production Tables into Test Table (Test Region).

Can someone give assistance how to go about this, the best way ,using DB2 utilities(Unload and Load), using SQL?



Given: 1. No. of Tables to be strip - seven (7) 2. Production and Test are separate LPAR. 3. Needed 11000 plus data out of 300,000. 4. OS/390, DB2 Release 5.2, [...] 10781 31 35_Re: Subsets of Large Tables with RI13_Leo Conchello18_lconche@USWEST.COM31_Tue, 11 Jul 2000 15:18:28 -0600609_us-ascii Please ignore my previous message, I found all the answers in the archives. My apologies!

Leo Conchello wrote:

> Can anyone suggest any software tools capable of creating subsets of > large DB2 tables? (OS/390). I believe that Move for DB2 from Princeton > Softech can do it. Are there any others? > > An essential requirement is that the software must be able to read from > Image Copy or Unload datasets. Also, it must preserve RI. Any ideas? > Thanks in advance. > > Regards, > > Leo > > ================================================ > To change your subscription options or [...] 10813 13 11_unsubscribe10_WAYNE FISH22_WFISH@IDPH.STATE.IL.US31_Tue, 11 Jul 2000 16:38:00 -0500320_- >>> INFO_SYSTEMS 06/30/00 11:52AM >>> signoff *

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10827 45 47_Re: Creating/Stripping Data as Test DB2 tables.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Wed, 12 Jul 2000 08:29:01 +1000562_us-ascii Armando, We use Princeton Softech's 'Move for DB2'. We have a 'driving' table of CUSTOMER from which we have RI defined to other tables etc... . For any relationships you don't have defined in DB2 you can define them within the product so that it knows about them. You then use the driving table with a seed of numerous CUSTOMER numbers (via SQL, so you could also give it a range) and it extracts those and traverses all relationships for each CUSTOMER so that a referentially intact set of data from all related tables is extracted out to a flat [...] 10873 19 26_Re: Archiving DB2 Database12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 11 Jul 2000 18:25:36 -0500384_- If you want to archive data sets and use something like DFSMShsm for archive and recall, then the support has been there since about 1987. There are a couple of zparms to set for handling the recalls (panel DSNTIPO, parameters for automatic recall or not and delay time). If there are large numbers of data sets to recall, you'll be better off to recall explicitly in the run. [...] 10893 15 17_Re: BSDS Question12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 11 Jul 2000 18:33:06 -0500505_- The Change log inventory and Print Log Map utilities are the only real interfaces. Just about everything else is reverse engineering and likely to break, since the usage changes from release to release and sometimes in a PTF.

Roger Miller, DB2 for OS/390

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10909 31 47_Re: Creating/Stripping Data as Test DB2 tables.12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 11 Jul 2000 18:48:43 -0500399_- What comes with DB2 is a couple of options, and I'll just outline the simplest choices:

1. A sample unload program called DSNTIAUL. See the documentation in the Installation Guide. You have the source to examine and modify if needed. If you want the optimizer to use indexes for efficient access and put out a few rows, then this works pretty well. You can use any SQL for selections. [...] 10941 114 36_Re: Application performance question0_29_Greg.Palgrave@BANKWEST.COM.AU31_Wed, 12 Jul 2000 08:35:11 +0800317_us-ascii Anshu,

If COL1 is the clustering key (or even if it isn't, but the data is reasonably clustered by COL1), make sure that the input flat file is SORTED, or your program will be dying the "death of 125,000 random I/Os". Sorting will allow prefetch to kick in and the job should go somewhat faster. [...] 11056 106 36_Re: Application performance question12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Wed, 12 Jul 2000 10:35:29 +1000321_- I Like Jay's approach, but ...

If the size of the table is NOT too much larger than the 125K file, then you could pre-sort the file (col1 sequence) and then >> SELECT * FROM T1 order by COL1 ; >> (depending on the table an index on COL1 may be desirable) processing the file & cursor as lists to be merged. [...] 11163 18 38_Re: RUNSTATS question - DB2 V5 / OS39012_Roger Miller19_millerrl@US.IBM.COM31_Tue, 11 Jul 2000 19:29:17 -0500311_- I would look to see if a specific set of columns could be chosen, rather than all columns. If the columns are not used much in WHERE clauses, then using the old values should be ok.

Have you considered using SAMPLE? When we ran the sampling in the lab, we cut the cpu and run time almost in half. [...] 11182 22 25_regarding tablespace scan17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Tue, 11 Jul 2000 20:27:25 PDT317_- Hi Folks,

We are using DB2 V5. and we are using a tool OMEGAMON FOR DB2 to see the activities. in OMEGAMON i am not able to find out wheather the sql query is using tablespace scan or index scan. is there any option in Omegamon to find out what type scan it is doing??. can anybody help in this matter. [...] 11205 46 29_Re: regarding tablespace scan7_Sanjeev19_Sanjeev@INTERRA.COM31_Wed, 12 Jul 2000 09:24:36 +0530359_iso-8859-1 Hi Ravi, Look at the object analysis (O) option(for the particular thread) in the omegamon to find out whether the query is using tablespace scan,index only scan or table + index scan.You will be shown tablespace or indexspace name with the other details like getpage,sync. I/O,Asynch. I/O ,List prefetch and Dynamic Prefetch. Hope this helps [...] 11252 83 44_failure in precomilation of C client program11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Wed, 12 Jul 2000 11:55:10 +0800360_us-ascii I have a probem with the precompilation of a C Client program (sample program modified from Application and Programming Guide of DB2 OS/390 v5 Appendix C X-57) using DB2 connect to access a stored procedure in OS/390.

The errors from the precompilation (db2 ==> prep d: \project\testclient\cprog.sqc bindfile owner db2t1) are as follows : [...] 11336 51 29_Re: regarding tablespace scan11_K Mahadevan28_kmahadevan@CHENNAI.TCS.CO.IN31_Wed, 12 Jul 2000 09:29:51 +0530705_us-ascii Hi Ravi,

I am not sure if you can use omegamon for this. But I suggest Explain is the best option.

Cheers

Maha







ravi kumar hassan on 07/12/2000 09:27:25

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: K Mahadevan/MLs/TCSCHENNAI)

Subject: regarding tablespace scan







Hi Folks,

We are using DB2 V5. and we are using a tool OMEGAMON FOR DB2 to see the activities. in OMEGAMON i am not able to find out wheather the sql query is using tablespace scan or index scan. is there any option in Omegamon to find out what type [...] 11388 51 29_Re: regarding tablespace scan11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Wed, 12 Jul 2000 12:02:06 +0800390_us-ascii Hi kumar, Thru Omegamon for DB2, you can set the application trace on. This will enable you to look at the performance trace of the sql query or alternatively zoom into the thread (make sure that it is a long running tasks) and look at "LOCK OWNED" panel.

Regard







ravi kumar hassan @RYCI.COM> on 12/07/2000 11:27:25 AM [...] 11440 72 29_Re: regarding tablespace scan12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Wed, 12 Jul 2000 14:34:23 +1000313_- Kumar, short of the Application Trace Facility, which I would not recommend in this case, nothing that would specifically indicate that. However if you are in the Thread Activity panel (Option T) and then drill into a particular thread, you can see what Locks that job owns under option "D". For Example:- [...] 11513 93 36_Re: Application performance question7_Sanjeev19_Sanjeev@INTERRA.COM31_Wed, 12 Jul 2000 10:06:57 +0530567_iso-8859-1 Hi Anshu, If you are going for using the option of temporary table then i think you cann't use global temporary table with load utility on that ,instead you have to create the DB2 base table and use load or insert on that or if you are going for the global temporary table then rows are to be inserted in the same unit of work thru the application. I would prefer to try the method of sorting the file on the basis of clustering key,considering clustering index on COL1 because sequential detection will definitely be triggered.Look at the article in [...] 11607 94 36_Re: Application performance question11_Suresh Sane21_data_arch@HOTMAIL.COM29_Tue, 11 Jul 2000 23:41:48 CDT493_- Anshu: The simplest and probably the fastest way would be to simply declare a cursor to fetch all rows of the table sorted by col1. Sort the input file in the same sequence. Then use a 2-file match logic. If COL1 is the first column of the clustering index, no sort will be needed. If it is not, you will incur some overhead up-front (ts scan + sort or a list prefetch if a random index exists on col1). But each fetch will be substantially cheaper leading to a far better performance. [...] 11702 72 37_Links about DB2 for OS/390 (07/12/00)6_DB2usa18_db2usa@HOTMAIL.COM31_Tue, 11 Jul 2000 23:27:06 -0500555_ISO-8859-1 Hi,

Here is a non-profit website about DB2 for OS/390 (IBM mainframes):

- English : http://www.multimania.com/db2usa/eliendb2.htm - Français: http://www.multimania.com/db2usa/liendb2.htm

Last update on Wednesday July, 12th 2000 ========================================

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliste.htm Without access to Internet, you can search the DB2-L newsgroup archives and the Web.

DB2 Performance Journal (YL&A, Inc) contains a new section: Hints and Tips. [...] 11775 70 31_Antwort: Point in time recovery13_Mark Nicholls20_Mark.Nicholls@DVG.DE31_Wed, 12 Jul 2000 08:46:10 +0200605_us-ascii Hallo Mike,

Good question because this aspect of rollforward recovery is somewhat (very :-) ) confusing. Your student notebook is certainly in conflict with the admin guide, which I quote below. First 2 points.

1. The "minimum recovery time" can be found by issuing

LIST TABLESPACES SHOW DETAIL

this value is updated, when DDL is executed aginst objects associated with the tablespace. You cannot specify a point in time to roll forward to prior to this. Did you run any DDL shortly before attempting to recover - that may explain running to the end of the logs. [...] 11846 182 38_Re: RUNSTATS question - DB2 V5 / OS3900_18_mebert@AMADEUS.NET31_Wed, 12 Jul 2000 10:00:45 +0100635_us-ascii I fell into that trap once also - specified RUNSTATS TABLESPACE xx.yy PART nn TABLE(ALL) INDEX(ALL) SHRLEVEL CHANGE, running several jobs in parallel. This updated the stats for each TS partition once, but for the partitioning index (no NPIs) n times - bad.

In your case, you should run: RUNSTATS TABLESPACE xx.yy PART nn TABLE(ALL) INDEX(clustering-index PART nn) SHRLEVEL CHANGE for the changed partition(s) only plus RUNSTATS INDEX(NPIm) CHRLEVEL CHANGE for each NPI (I don't know whether you can specify a PART clause for an NPI but I don't think so). These jobs can run in parallel without problems, reducing [...] 12029 40 44_Re: System R - The father of DB2 Optimizer ?14_Massimo Scarpa23_Massimo_Scarpa@DEBIS.IT31_Wed, 12 Jul 2000 10:20:05 +0100458_us-ascii Thank you Roger for the answer and the hint.

I'm well awared that optimizer is a proprietary code but I'm still surprised to read in many & many & many articles and to see

in many courses the phrases 'Optimizer calculate...' 'Optimizer decide to..' 'Optimizer calculate timerons (values) in this way

(nothing) '. And if many 3rd party vendors offer tools to calculate access paths and access costs and if there are so many [...] 12070 32 40_DB2 Connect Problem to Mainframe DB2 5.110_Robert Ord21_robertord@HOTMAIL.COM29_Wed, 12 Jul 2000 08:21:30 GMT423_- I seem to be having problems with db2 client connection setup. A specific sqlid is being set in the cli/odbc enterprise advanced settings which is supposed to achieve the following;

"Specify the ID used in a SET CURRENT SQLID statement sent to the DBMS upon a successful connect. This option is only relevant when connecting to those DB2 DBMSs where SET CURRENT SQLID is supported (such as DB2 for MVS/ESA)." [...] 12103 78 26_Re: How big's the VARCHAR?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 12 Jul 2000 18:22:35 +1000374_iso-8859-1 Hmmm, what is the actual code used in the calling program to define the host variable and call the SP? Is SQLWARN1 turned on? (I'm speculating that DB2 is thinking that your program's host variable is actually a CHAR(255) field.)

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au [...] 12182 114 38_Re: RUNSTATS question - DB2 V5 / OS3900_27_jim.leask@RS-COMPONENTS.COM31_Wed, 12 Jul 2000 09:43:41 +0100461_iso-8859-1 Marc,

I would consider running RUNSTATS only on the last partition to be filled, assuming that the previous 45 partitions have not changed significantly. Also, only running the runstats at then end of the 'fill' period i.e. 4-5 weeks rather than every week.

Jim.

-----Original Message----- From: Marc Fuqua [mailto:marcfuqua@YAHOO.COM] Sent: 11 July 2000 17:49 To: DB2-L@RYCI.COM Subject: RUNSTATS question - DB2 V5 / OS390 [...] 12297 57 44_Re: DB2 Connect Problem to Mainframe DB2 5.111_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Wed, 12 Jul 2000 17:06:11 +0800427_us-ascii Hi Robert, Have u try seting "current sqlid" in DB2 Command Line Processor environment? I had try setting current sqlid in DB2 command Line Processor environment and its work.







Robert Ord @RYCI.COM> on 12/07/2000 04:21:30 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 12355 16 21_Highest RBA offloaded10_Lee Mandel16_DBMSUser@AOL.COM29_Wed, 12 Jul 2000 06:06:11 EDT428_US-ASCII Hi all, When I run the Print Log Map Utility DSNJU004 - the message HIGHEST RBA OFFLOADED. Is this ALWAYS the ENDRBA of the last Archive Log? Maybe an IBMer can help me. TIA Lee

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 12372 27 26_Re: Point in Time Recovery13_Mark Nicholls20_Mark.Nicholls@DVG.DE31_Wed, 12 Jul 2000 12:57:18 +0200351_us-ascii Hallo Mike,

one further aspect of your problem occurs to me:

if you are trying to restore to a point in time that corresponds to your OFFLINE backup, then you should specify the 'WITHOUT ROLLING FORWARD' option on the restore command. The database should not then go into roll-forward pending state.

Hope this helps [...] 12400 27 20_Contract Opportunity12_Kent Collins15_kcollins@WF.NET31_Wed, 12 Jul 2000 06:38:24 -0500447_iso-8859-1 Location: Denver, Co. Contract Period: Open ended Contract to Hire Duties: Install tools and design Data Warehouse using DB2 EEE and Intelligent Miner on a large NT platform. Rates: Open Experience Level: Senior level knowledge in the following: DB2 EEE Designing a Data Warehouse NT Clusters

If you are interested or know someone who might be interested please contact me at Office/Cell 940.367.0514 Email: kcollins@wf.net [...] 12428 72 32_Re: DB2 running under OS390 2.8:8_Bob Byrd14_BBYRD@PCMH.COM31_Wed, 12 Jul 2000 07:41:06 -0400453_US-ASCII We are in the process of migrating all of our lpars to OS/390 2.8. In fact, the only one remaining is our production lpars. We use DB2 v5.1, service level 9909. To date, no problems encountered.



Bob Byrd Pitt County Memorial (252) 816-5154 (252) 816-0385 (fax) bbyrd@PCMH.com

>>> 07/11/00 04:04PM >>> We've been running DB2 v5.1 (pdo9952) on OS/390 2.8 (servicepak 9907) since April, no problems. [...] 12501 114 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Wed, 12 Jul 2000 08:04:10 -0400499_us-ascii I can see both the calling and called SQLCA's and both don't have any SQLWARN's on. This is the define for the calling prog:-

01 WS-PARM-RESULTS. 05 WS-PARM-RESULTS-LEN PIC 9(4) BINARY. 05 WS-PARM-RESULTS-DAT PIC X(3500). 01 WS-PARM-RESULTS-A REDEFINES WS-PARM-RESULTS PIC X(3502). 01 WS-PARM-SQLCA PIC X(136). 01 WS-PARM-ERROR-DESC PIC X(60). 01 WS-PARM-NULL-IND. 05 WS-PARM-SQLCA-IND PIC S9(4) COMP. 05 WS-PARM-ERROR-IND PIC S9(4) COMP. 05 WS-PARM-RESULTS-IND PIC S9(4) COMP. [...] 12616 13 11_unsubscribe12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Wed, 12 Jul 2000 05:56:38 PDT389_- ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 12630 161 26_Re: How big's the VARCHAR?15_Toine Michielse18_vndobtm@US.IBM.COM31_Wed, 12 Jul 2000 15:25:16 +0100608_us-ascii Hello Martin,

it seems to me you are not presenting DB2 with a varchar hostvariable.

Try

01 WS-PARM-RESULTS. 49 WS-PARM-RESULTS-LEN PIC s9(4) BINARY. 49 WS-PARM-RESULTS-DAT PIC X(3500).

Note level 49.....

Regards



Toine Michielse





MARTIN WOLFF @RYCI.COM> on 07/12/2000 01:04:10 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: How big's the VARCHAR? [...] 12792 182 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Wed, 12 Jul 2000 10:04:52 -0400333_us-ascii I see where you are going and I changed it to no effect. The problem is I have to use WS-PARM-RESULTS-A (the redefine of WS-PARM-RESULTS) as the V5 precompiler won't take the other. So, I am presenting a CHAR(3502) to DB2 in the call.

Just for completeness, this is the variables for the stored procedure side:- [...] 12975 42 25_Re: Highest RBA offloaded23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 12 Jul 2000 10:17:10 -0400619_- I am not an IBMer, but the answer is YES.

Sanjay

> -----Original Message----- > From: Lee Mandel [SMTP:DBMSUser@AOL.COM] > Sent: Wednesday, July 12, 2000 6:06 AM > To: DB2-L@RYCI.COM > Subject: Highest RBA offloaded > > Hi all, > When I run the Print Log Map Utility DSNJU004 - the message HIGHEST RBA > OFFLOADED. Is this ALWAYS the ENDRBA of the last Archive Log? Maybe > an > IBMer can help me. TIA Lee > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of [...] 13018 212 26_Re: How big's the VARCHAR?13_Kenski, David20_David.Kenski@SSA.GOV31_Wed, 12 Jul 2000 10:11:10 -0400509_iso-8859-1 Martin,

Shouldn't a true variable length item be represented by something like:

01 WS-PARM-RESULTS. 49 WS-PARM-RESULTS-LEN PIC 9(4) COMP. 49 WS-PARM-RESULTS-DAT PIC X OCCURS 3500 TIMES DEPENDING ON WS-PARM-RESULTS-LEN.

What does the SYSPROCEDURES entry look like?

Dave Kenski

-----Original Message----- From: MARTIN WOLFF [mailto:martin_wolff@GLOBALCROSSING.COM] Sent: Wednesday, July 12, 2000 10:05 AM To: DB2-L@RYCI.COM Subject: Re: How big's the VARCHAR? [...] 13231 230 26_Re: How big's the VARCHAR?15_Toine Michielse18_vndobtm@US.IBM.COM31_Wed, 12 Jul 2000 16:29:50 +0100369_us-ascii Martin,

Except that DB2 sees that as a CHAR not a VARCHAR and the max length of a char is 255...

Have you tried the 49 level? What error does the precompile give you? Seems strange to me that it would accept the declaration when precomping the stored proc and not when doing the caller.... Forgive me if I missed that part of the thread.... [...] 13462 33 50_Stored Procedures & DB2 Dev/Test/Prod Environments5_Riyaz15_RMomin@FHSC.COM31_Wed, 12 Jul 2000 10:49:09 -0400595_US-ASCII For those of you that have responded that your environments support multiple "test stages" within one DB2 subsystem, how do you manage stored procedures? The assumption is that stored procedures are commonly used modules yet are bound with one QUALIFIER only.

Here's an example. A stored procedure, SP1, is bound with qualifier of QA (quality assurance). I am developing a procedure and bind with qualifier of UT (unit test). Yet, my procedure makes a call to SP1. Upon execution, my unit test procedure now crosses over from the UT set of tables into the QA set of tables. [...] 13496 40 34_How Do I set up the maxcoordagents8_sncmonty18_sncmonty@YAHOO.COM31_Wed, 12 Jul 2000 08:06:30 -0700459_us-ascii Hi,

I have a problem with a shell script am running on AIX. It contains mini scripts to create tables, tablespace etc.

One of the steps I took to correct some errors I had been getting initially was to connect and disconnect at the end of the different steps (8). This now gives me an application error and sometimes, I have to reboot the system. It's kind of flaky. Sometimes the script will run perfectly and sometimes it won't. [...] 13537 39 21_DB2 Connect & Solaris11_Tim Kearney22_tkearney@ACCESS360.COM31_Wed, 12 Jul 2000 10:06:49 -0500549_- Did anyone ever install IBM DB2 Connect Enterprise Edition V6 on > Solaris and configure it successfully? > > We tried to use IBM DB2 Connect (Enterprise Edition V6) to manage > connections to IBM's DB2 V5 for OS/390, but couldn't make it work. > > 1. Install: > > We installed the DB2 Connect on the Solaris using the root account, and > chose to create a DB2 instance and administration server, after we input > all the necessary information, the installer started to create the DB2 > instance, but it seems > the process is endless, so we [...] 13577 24 31_AIV Extenders using DB2 ver 6.112_Gary Ashford31_gary_ashford@OMNIS-SOFTWARE.COM31_Wed, 12 Jul 2000 16:22:54 +0100663_iso-8859-1 We have an application which uses the DB2 AIV extenders via embedded SQL. This works fine with the version 5.2 Extenders. With 6.1 Clientware and 6.1 Extenders installed however, a crash always occurs inside EXEC SQL PREPARE statement1 FROM :sqlStr Specifically- inside the sqlacall() method The text of the sqlStr is "insert into imagetest values (1,mmdbsys.DB2Image(CURRENT SERVER, CAST(:my_bin1 as BLOB(4m)), 'jpg', CAST(NULL as LONG VARCHAR), 'aaa'))" (EXEC SQL DESCRIBE statement1 into :SQLDA also crashes inside the same call) Fix-Pak 4 did nothing to improve the situation. Has anyone been able to get the 6.1 extenders to work? If so what [...] 13602 35 25_Re: Highest RBA offloaded13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 12 Jul 2000 08:27:32 -0700447_iso-8859-1 My answer would be 'yes' unless someone corrects me

Thanks.

Ashish.

-----Original Message----- From: Lee Mandel [SMTP:DBMSUser@AOL.COM] Sent: Wednesday, July 12, 2000 3:06 AM To: DB2-L@RYCI.COM Subject: Highest RBA offloaded

Hi all, When I run the Print Log Map Utility DSNJU004 - the message HIGHEST RBA OFFLOADED. Is this ALWAYS the ENDRBA of the last Archive Log? Maybe an IBMer can help me. TIA Lee [...] 13638 114 48_Re: failure in precomilation of C client program16_Michael McCarthy18_mmccarthy@DTCC.COM31_Wed, 12 Jul 2000 11:42:24 -0400452_us-ascii Roland,

Unfortunately the DB2 UDB precompiler does not support result sets in static sql clients. This goes for sqlj programs as well. For Java you need to write a JDBC program and For C/C++, you need to write an ODBC (CLI) program.

-Mike.









Roland Chua on 07/11/2000 11:55:10 PM

Please respond to DB2 Data Base Discussion List [...] 13753 113 34_Distributed Data Facility Question24_Acri, Mark J. (CAP, CMC)25_Mark_Acri@MORTGAGE.GE.COM31_Wed, 12 Jul 2000 11:38:40 -0400427_iso-8859-1 I'm hoping somebody can provide some help with a question regarding the Distributed Data Facility (DDF). We are running on v5.2 and recently linked our production and development DB2 subsystems together using DDF. We were hoping to use this to allow the developers and easy way to copy rows of data from production down into test, by issuing an 'INSERT INTO TEST_TABLE SELECT * FROM PROD_LOC.OWNER.PROD_TABLE'. [...] 13867 341 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V514_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Fri, 12 Mar 1999 11:39:17 -0500536_us-ascii Hi Jain,

Have you verified the validity of the contents of the indexes for the table involved (run Check Index) ?

JAS Bill G.

Jain, Sanjay (Exchange) wrote:

> Yes I did, It returns result in V5 and none in v6. > > Sanjay > > > -----Original Message----- > > From: Mohan, Ashish [SMTP:Ashish.Mohan@NIKE.COM] > > Sent: Tuesday, July 11, 2000 3:46 PM > > To: DB2-L@RYCI.COM > > Subject: Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V5 > > > > Hi ! > > > > Just for curiosity sake , did you try > > [...] 14209 232 48_Re: failure in precomilation of C client program13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 12 Jul 2000 10:44:20 -0500493_US-ASCII Have you installed the SDK ?. The SDK has the .h header files. You can verify by checking that you have the "include" directory in the "sqllib" directory. HTH Kurt









>>> rolandchua@SINGAPOREEXCHANGE.COM 07/11/00 10:55PM >>> I have a probem with the precompilation of a C Client program (sample program modified from Application and Programming Guide of DB2 OS/390 v5 Appendix C X-57) using DB2 connect to access a stored procedure in OS/390. [...] 14442 22 24_Re: IMS to DB2 Migration0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 12 Jul 2000 10:50:57 -0500624_us-ascii Vijay:

For more info/reasons to do DB2 optimizing as part of your conversion process take a look at some of the articles linked to in the "Links about DB2 for OS/390 ..." posting. For example under Technical papers ( http://www.multimania.com/db2usa/earticle.htm ) try the "Powerful SQL: Beyond the Basics" article by Sheryl Larson. It starts with the following tag line "By using powerful SELECTs, you can yield CPU reductions near 70 percent! " and goes on to describe poor DB2 practices in applications converted from VSAM. Some of the other links to articles on that page have further related info. [...] 14465 30 40_Job opportunity in London,UK for UDB DBA20_Pav Kumar-Chatterjee17_pavkc@HOTMAIL.COM29_Wed, 12 Jul 2000 16:00:46 GMT386_- Hi List folks

We are a dotcom start up company in London, UK and are looking for a DB2 DBA with 6+ months experience. We are running UDB 7.1 on an RS/6000.

Salary is dependent on experience and there is an extremely generous share option scheme.

If you are interested, then please email me your CV (offline), together with a contact number and availability. [...] 14496 263 26_Re: How big's the VARCHAR?4_IBM116_IBM1@UBIL.COM.TR31_Wed, 12 Jul 2000 19:13:26 +0300523_ISO-8859-9 martin,

try coding it like this:

01 WS-PARM-RESULTS. 49 WS-PARM-RESULTS-LEN PIC S9(4). 49 WS-PARM-RESULTS-DAT OCCURS 1 TO 3500 TIMES DEPENDING ON WS-PARM-RESULTS-LEN PIC X(3500).

HTH.



ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 14760 140 38_Re: Distributed Data Facility Question0_28_dave.s.mukherjee@VERIZON.COM31_Wed, 12 Jul 2000 12:09:18 -0400650_us-ascii



Mark:

You can get around this problem using QMF...

Connect to remote_DB2_subsystem Select from the remote.table Connect back to the local_DB2_subsystem Save data as local.table

Thanks, -- Dave Mukherjee



















"Acri, Mark J. (CAP, CMC)" on 07/12/2000 11:38:40 AM

Please respond to "DB2 Data Base Discussion List"





To: DB2-L@RYCI.COM

cc: (bcc: DAVE S. MUKHERJEE/EMPL/VA/Bell-Atl)





Subject: Distributed Data Facility Question [...] 14901 301 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V523_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 12 Jul 2000 12:18:27 -0400489_iso-8859-1 Read page 2-50...This issue of where clause on a join has been discussed in great detail before in the list. And it seems to be related to my problem in the following way.

-- Select query on the view results in view merge. -- view merge process creates a result sql which is similar to problem described on page 2-50 of application guide. -- what compounds the problem is the VALUE clause in the view definition. To explain the problem, let me simplify the view DDL [...] 15203 67 38_Re: Distributed Data Facility Question23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 12 Jul 2000 12:28:04 -0400316_- Distributed SQL statements are not allowed YET. Don't know if this changes with ver 7 of DB2/OS390. What I used to do at another shop that had QMF

-- select from the remote DB2 -- save the result into a table at current DB2. Save would wipe out anything existing in the table, if I remember correctly. [...] 15271 70 38_Re: Distributed Data Facility Question14_David Williams30_David.Williams@DOL.STATE.GA.US31_Wed, 12 Jul 2000 12:31:24 -0400 15342 78 38_Re: How Do I set up the maxcoordagents25_Carlos Eduardo de Miranda25_carlos_e@CCUEC.UNICAMP.BR31_Wed, 12 Jul 2000 13:45:46 -0300532_iso-8859-1 Stella:

You can obtain the parameters regarding agents issuing the following command under your DB2 user Unix session:

db2 get dbm cfg | grep AGENTS Max number of existing agents (MAXAGENTS) = 80 Agent pool size (NUM_POOLAGENTS) = 10 Initial number of agents in pool (NUM_INITAGENTS) = 5 Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INIT AGENTS) Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS Max number of DARI processes (MAXDARI) = MAX_COORDAGENTS [...] 15421 53 54_Re: Stored Procedures & DB2 Dev/Test/Prod Environments14_McCombs, Terry20_tmccomb@SEDGWICK.GOV31_Wed, 12 Jul 2000 11:43:42 -0500561_us-ascii You can have one load module bound into different collections, each collection to bind the module with a different qualifier. Presumably the different environments would be using different collections. We don't happen to have different environments in the same sub-system, so I'm only speaking theoretically.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Riyaz Sent: Wednesday, July 12, 2000 9:49 AM To: DB2-L@RYCI.COM Subject: Stored Procedures & DB2 Dev/Test/Prod Environments [...] 15475 61 44_Re: Job opportunity in London,UK for UDB DBA17_Rabindra Senapati21_rsenapati@HOTMAIL.COM29_Wed, 12 Jul 2000 09:52:09 PDT482_- Yes, i am in USA for more than 3 years , 2 + on db2 mainframe and other platforms. Now i am working 6.2 with NT in a big application in one of the client. here i have totoal 3 client experience and some in india too. if you need my resume for initial interview then let me know. i will prepare my resume and send accross that to you. i have 10+ years of IT experience with NT, OS390,MVS AND UNIX. I have not work with DB2 on unix. but I am experienced on AIX on rs6000. I am [...] 15537 69 28_Re: DB2/OS390 DSN1CHKR usage11_Ron Johnson25_rjohnson@DATA-TRONICS.COM31_Wed, 12 Jul 2000 12:15:18 -0500514_us-ascii Thanks for all the responses on my question. I've decided to DSN1COPY the shrlevel reference catalog image copies to a VSAM dataset (using the CHECK option) and then run DSN1CHKR against those. That maintains the check but eliminates the extra downtime.

Ron Johnson Data-Tronics Corp

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Alan C Smith Sent: Tuesday, July 11, 2000 2:22 PM To: DB2-L@RYCI.COM Subject: DB2/OS390 DSN1CHKR usage [...] 15607 123 38_Re: How Do I set up the maxcoordagents8_sncmonty18_sncmonty@YAHOO.COM31_Wed, 12 Jul 2000 10:17:49 -0700610_us-ascii Hi Carlos,

Thanks for the response. This is very highly appreciated.

--- Carlos Eduardo de Miranda wrote: > Stella: > > You can obtain the parameters regarding agents > issuing the following command under your > DB2 user Unix session: > > db2 get dbm cfg | grep AGENTS > Max number of existing agents > (MAXAGENTS) = 80 > Agent pool size > (NUM_POOLAGENTS) = 10 > Initial number of agents in pool > (NUM_INITAGENTS) = 5 > Max number of coordinating agents > (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INIT > AGENTS) > Max no. of concurrent coordinating agents [...] 15731 56 38_Re: How Do I set up the maxcoordagents14_Mike Lebermann18_Lebermm@BCBSKC.COM31_Wed, 12 Jul 2000 12:21:07 -0500599_ISO-8859-1 Hi Stella,

What version of DB2, EE or EEE? Have you looked at the db2diag.log to see what kind of errors you are getting?

Mike

>>> sncmonty@YAHOO.COM 07/12/00 10:06AM >>> Hi,

I have a problem with a shell script am running on AIX. It contains mini scripts to create tables, tablespace etc.

One of the steps I took to correct some errors I had been getting initially was to connect and disconnect at the end of the different steps (8). This now gives me an application error and sometimes, I have to reboot the system. It's kind of flaky. Sometimes [...] 15788 324 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V515_Gingras, Martin30_Martin.Gingras@CCRA-ADRC.GC.CA31_Wed, 12 Jul 2000 13:26:38 -0400342_iso-8859-1 We just got a fix from IBM on a problem with outer joins that is very similar to your.

The problem did not only happen with the value clause. We were able to reproduce bad results without the value clause. It came through our software guys as a special fix so I don't know the apar #. Maybe somebody from IBM can help. [...] 16113 60 35_Re: Stored Procedures & DB2 Dev/Tes11_Robert Jans21_Jans.Robert@AMSTR.COM31_Wed, 12 Jul 2000 10:59:00 -0600434_- In V6 you can use the SCHEMA to define what tables get accessed. V5 uses the AUTHID column in SYSPROCEDURES.

A word of warning, if you're V5 and do use AUTHIDs, be aware that when you upgrade to V6, the stored proc migration program will not convert stored procs with an AUTHID. This has caused us some major consternation here as that means about 600 for our 800 stored procs need to be recreated in V6................ [...] 16174 385 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V523_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 12 Jul 2000 14:43:05 -0400579_iso-8859-1 Martin,

I did not find any hits on my problem on IBMLINK. Could you please find out from your sysprog guys what APAR # is that ? It may be what I am looking for.

Thank you

Sanjay Jain 8-226-7069

> -----Original Message----- > From: Gingras, Martin [SMTP:Martin.Gingras@CCRA-ADRC.GC.CA] > Sent: Wednesday, July 12, 2000 1:27 PM > To: DB2-L@RYCI.COM > Subject: Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V5 > > We just got a fix from IBM on a problem with outer joins that is very > similar to your. > > The problem did not only happen [...] 16560 52 24_CA's DB2 Tech Forum 200013_Thomas, Janis19_Janis.Thomas@CA.COM31_Wed, 12 Jul 2000 17:13:13 -0400518_iso-8859-1 Computer Associates announces the schedule of North American cities for the remainder of their DB2 Technology Forum World Tour 2000:

8/1 Cincinnati 8/3 Pittsburgh 8/9 Indianapolis 8/9 Raleigh/Durham 8/10 Memphis 8/10 Tallahassee 8/15 Milwaukee 8/17 Chicago 8/22 Albuquerque 8/22 Peoria 8/23 Detroit 8/24 Minneapolis 8/29 Columbia, South Carolina 8/30 Philadelphia 8/31 Edmonton 9/6 Sacramento 9/6 Dallas 9/7 Phoenix 9/7 Jackson, Mississippi 9/13 Atlanta 9/15 New York 10/3 San Juan, Puerto Rico [...] 16613 278 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Wed, 12 Jul 2000 16:07:25 -0400523_us-ascii I'll have to change my program to support the variables this way (I'm only using a VARCHAR because I have move than 255 byte to return and the front end where this is ultimately going doesn't support the cursor return approach).

While I do this, can I ask how I code the actual SP call command. It looks as if I will get the 'invalid or unuseable host variable' message if I try to pass the '01' or the '01' or the variable length '-DAT' variables. And I cannot do a redefine now and pass that either. [...] 16892 20 38_Re: Rejected posting to DB2-L@RYCI.COM19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Wed, 12 Jul 2000 18:42:30 -0300362_iso-8859-1 Where can I download Windows3.xx for free from?

Any tip is most welcome.

Regards,

Kleber

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16913 39 14_En: Re: REVIEW19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Wed, 12 Jul 2000 18:45:38 -030023_iso-8859-1 REVIEW DB2-L 16953 23 38_Re: Rejected posting to DB2-L@RYCI.COM19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Wed, 12 Jul 2000 18:47:56 -0300275_iso-8859-1 How can I limit the number of rows produced by my query?

For example, if I issue the query: "SELECT * FROM OWNER.TABLE", I know that tens of thousands rows will come up. What if I want that only the 10 or 20 first ones come up as a result of my query? [...] 16977 350 26_Re: How big's the VARCHAR?13_Kenski, David20_David.Kenski@SSA.GOV31_Wed, 12 Jul 2000 18:12:14 -0400506_iso-8859-1 Martin,

I am doing the same thing here (VARCHAR because of more that 255 characters) between a COBOL SP and VB. However, I am not using the Null Indicators. Using your syntax earlier syntax, I would say to code as follows:

This is the define for the calling prog:- > > > > > > 01 WS-PARM-RESULTS. > > > 05 WS-PARM-RESULTS-DAT PIC X(3500). > > > > > > 01 WS-PARM-SQLCA PIC X(136). > > > 01 WS-PARM-ERROR-DESC PIC X(60). > > > > > > And the actual call:- > > > > > > EXEC SQL > [...] 17328 46 43_Creating/Stripping Data as Test DB2 Tables.17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Wed, 12 Jul 2000 15:14:07 -0700524_US-ASCII Hello again,

I really appreciate the help and the quick response received. They were all great and feasible.

Unfortunately, we don't have Princeton Softech or Softbase Systems software.

I would like to hear again your opinion/suggestion/feedbacks to what we plan to do:

Unload the DB2 table using DB2 Utility into a Flat file, Depending on the size of the flat file, run a Cobol program or Syncsort against the Flat file or used ISPF edit function to delete/create the data needed. [...] 17375 79 47_Re: Creating/Stripping Data as Test DB2 Tables.24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Wed, 12 Jul 2000 15:30:39 -0700550_- Your approach will certainly work, but it has several limitations: * You will need a program to massage the data in each table. If you decide to load all of the data in small tables, you may run into problems with referential integrity. * You will need to handle referential integrity issues. If you are using DB2 RI, this may be a little simpler as long as you start at the top of a structure, and then run checks to remove the rows which fail. But this does not address related structures not in a direct parent-child relationship, nor does [...] 17455 21 27_enable DDF for Open Edition16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Wed, 12 Jul 2000 15:57:00 -0700423_- P. 476 on the DB2 V6 Install Guide documents steps to enable DDF for OpenEdition. Some text follows -

If you specify both a userid and a group in the RACF Started Procedures table, ...

ADDGROUP ddfgid OMVS(GID(x))...

where x is any valid unique identifier... Replace ddfgid withthe MVS user Id. Shouldn't that read 'Replace ddfgid with the MVS group id'? Also, what is 'x'supposed to represent? [...] 17477 66 28_Re: CA's DB2 Tech Forum 200011_Brian Lynch27_blynch@GRPWISE-EAST.TRW.COM31_Wed, 12 Jul 2000 18:59:08 -0400660_US-ASCII Janis, I tried to register for the New Mexico forum and the registration link had an error.

>>> Janis.Thomas@CA.COM 07/12/00 03:13PM >>> Computer Associates announces the schedule of North American cities for the remainder of their DB2 Technology Forum World Tour 2000:

8/1 Cincinnati 8/3 Pittsburgh 8/9 Indianapolis 8/9 Raleigh/Durham 8/10 Memphis 8/10 Tallahassee 8/15 Milwaukee 8/17 Chicago 8/22 Albuquerque 8/22 Peoria 8/23 Detroit 8/24 Minneapolis 8/29 Columbia, South Carolina 8/30 Philadelphia 8/31 Edmonton 9/6 Sacramento 9/6 Dallas 9/7 Phoenix 9/7 Jackson, Mississippi 9/13 Atlanta 9/15 New York 10/3 San Juan, Puerto Rico [...] 17544 149 48_Re: failure in precomilation of C client program11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Thu, 13 Jul 2000 08:59:16 +0800418_us-ascii Thanks Michael and Kurt for your fast responed. Very much appreciated.

Kurt, I had SDK installed and had checked and included the .h header files in the sqllib directory in my program. The precompile still having the same problem. I did not know whether I had missed out something. I even checked the sample programs that came with the SDK, there was no examples which were similar to my program. [...] 17694 14 14_NT Blue Screen12_Jojo Pereira27_jojo.pereira@SBS.SIEMENS.CA31_Wed, 12 Jul 2000 22:44:59 -0400452_- When running DB2BP.exe to backup the database to another set of disks, we intermittently get NT's blue screen. Does anyone have any idea why this happens. By the way this is intermittent. Thanks in advance...

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17709 65 38_Re: Distributed Data Facility Question7_Sanjeev19_Sanjeev@INTERRA.COM31_Thu, 13 Jul 2000 11:57:13 +0530559_iso-8859-1 Hi Mark, If you are using inserts for the large sized tables then i do not think it is an effective way ,rather unload and load should be prefered.But as far as the solutions to your problem is concerned, i will prefer write a small dynamic sql program which will be having FETCH the records from LOCATION.OWNER.TABLE and INSERT into the LOCAL DB2 table.In this case it is useful to use the frequent COMMITs with the use of control table which will keep the last key value which is inserted so that overheads at the rollbacks will be less and [...] 17775 27 24_Primary Authid for a BMP9_Stan Hoey24_stan_hoey@CIRCLE-DL2.COM31_Thu, 13 Jul 2000 08:44:57 +0100556_us-ascii Folks,

where can I find out the different factors that decide which primary authid is presented to DB2 by a BMP? I know the obvious answer is the RACF id of the job submitter - if only it were that simple.

When Secondary authids were introduced in DB2 V2.1, there was a DB2-IMS connect Redbook, which explained that the authid could be the submitter, the PSBNAME or even the LTERM depending on a variety of factors. My copy of the Redbook has long since perished. I know a -922 is simple to fix - GRANT EXECUTE to PUBLIC, but I [...] 17803 39 28_SQL/DS 3.5: Archive question15_Thomas Machovec26_thomas.machovec@AT.IBM.COM31_Thu, 13 Jul 2000 09:59:35 +0200569_iso-8859-1 Dear friends,

We are running SQL/DS 3.5 for VSE/ESA and are using LOGMODE=A.

Once a week we do a reorg with UNLOAD and RELOAD. To avoid so many logentries we do this with LOGMODE=N. SQL is requesting an archive, when switching the LOGMODE from A to N. So we do an SQLEND ARCHIVE before the reorganization. Then we start SQL with LOGMODE=A again, but no archive is requested now by SQL/DS. I am not sure, wether a restore of the DB would work properly in case of a failure, because the reorg changes are not contained in the log. Therefore [...] 17843 294 26_Re: How big's the VARCHAR?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 13 Jul 2000 18:32:04 +1000697_iso-8859-1 Martin,

Just what do you mean by "as the V5 precompiler won't take the other." What happens when you code:

01 WS-PARM-RESULTS. 49 WS-PARM-RESULTS-LEN PIC 9(4) BINARY. 49 WS-PARM-RESULTS-DAT PIC X(3500).

(note 49 levels as Toine pointed out)

EXEC SQL CALL D201B03 ( :WS-PARM-RESULTS :WS-PARM-RESULTS-IND , :WS-PARM-SQLCA :WS-PARM-SQLCA-IND , :WS-PARM-ERROR-DESC :WS-PARM-ERROR-IND ) END-EXEC

To get rather nitty-gritty about this - have a look at the output from the pre-compiler. You'll see that the EXEC SQL CALL... is replaced by "CALL 'DSNHLI' USING SQL-PLISTn", up to working storage to find "01 SQL-PLISTn" - it'll look something like this: [...] 18138 54 38_Re: Rejected posting to DB2-L@RYCI.COM14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 13 Jul 2000 18:49:08 +1000526_iso-8859-1 I didn't know that Microsoft had declared Windows3.anything free. Can you provide a link to the announcement?

If they haven't, you are asking us to conspire with you to commit a crime.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Kleber Faria Vianna [mailto:kfv@UOL.COM.BR] Sent: Thursday, July 13, 2000 7:42 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Rejected posting to DB2-L@RYCI.COM [...] 18193 29 18_Coding Standards !27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM31_Thu, 13 Jul 2000 14:19:05 +0530402_iso-8859-1 Hi All,

Our project is planning to prepare a document for Coding Standards and Coding guidelines in DB2 which cover best coding practices from DB2 shops.

It should also cover Stored Procedure and Database Objects Standards and naming conventions .

If any of you have such a generic document , could you please attach it and send it to me at the following email id: [...] 18223 128 35_Re: Stored Procedures & DB2 Dev/Tes14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 13 Jul 2000 19:21:48 +1000378_iso-8859-1 Robert, your V6 statement is not quite true. In V6, the bind time PATH option specifies the schemas which will be searched for the SP. A parameter on the CREATE PROCEDURE is COLLID - which, in turn, points to a particular package whose QUALIFIER is used.

Ahh, how I remember this particular problem. Eventually (when I was using V6) I found the solution: [...] 18352 40 28_SQL/DS 3.5: DUALLOG question15_Thomas Machovec26_thomas.machovec@AT.IBM.COM31_Thu, 13 Jul 2000 11:28:24 +0200401_iso-8859-1 Dear friends,

We are running SQL/DS 3.5 for VSE/ESA and switched a few days ago to DUALLOG=YES.

We did all the necessary steps mentioned in chapter 9.3.1 of the SQL/DS System Administration Manual.

When restarting the Application server for normal operation with DUALLOG=Y we get the following message:

ARI0012I SQL/DS ignores DUALLOG = Y when SYSMODE = M. [...] 18393 59 18_RELEASE DEALLOCATE0_25_Julian_Peacock@SWEB.CO.UK31_Thu, 13 Jul 2000 10:34:32 +0100739_ISO-8859-1 Fellow DB2'ers,

I had a batch job fall over on the weekend whilst attempting to stop a DB2 (ver 5, OS/390) tablespace. Prior to abending, this job produced the following output:



DSNT360I - *********************************** DSNT361I - * DISPLAY DATABASE SUMMARY * GLOBAL LOCKS DSNT360I - *********************************** DSNT362I - DATABASE = DBPMC001 STATUS = RW DBD LENGTH = 40370 DSNT397I - NAME TYPE PART STATUS CONNID CORRID LOCKINFO -------- ---- ---- ------------------ -------- ------------ --------- S01PMC02 TS STOPP CIMACLAE GT00$RWO H-IX,S,A S01PMC02 TS STOPP DSNP 020.STOPDB09 W-X,S,01 29 TB CIMACLAE GT00$RWO H-IX,T,A ******* DISPLAY OF DATABASE DBPMC001 ENDED ********************* [...] 18453 105 22_Re: RELEASE DEALLOCATE13_Adrian Savory24_adrian.savory@ZURICH.COM31_Thu, 13 Jul 2000 11:24:57 +0100571_us-ascii Julian,

I've seen a similar problem where the first CICS transaction of the day did a mass delete of the table to clear out the previous day's data. The transaction was defined for thread re-use and for years this was not a problem because the first transaction in the morning occurred in isolation and the locks released when the thread was not re-used within a short space of time (45 seconds rings a bell). A change in the business processes meant that suddenly this transaction was heavily used from the start of day, and the X lock on the table [...] 18559 33 0_10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Thu, 13 Jul 2000 12:54:38 +0100468_iso-8859-1 Hi DB2 people

we recently had a 00C90084 4K problem. I know this is because DSNDB07 tablespaces are exhausted. Can anyone tell me the best way to determine what connections are using this resource?

















> ------------------------------------------------------------------ > Pete Coles - Systems Integration > > Email: peter.coles@somerfield.co.uk > > Tel: +44 117 935 6726 > > > [...] 18593 1061 44_Re: Job opportunity in London,UK for UDB DBA19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Wed, 12 Jul 2000 19:28:59 -0300448_iso-8859-1

-----Mensagem original----- De: Pav Kumar-Chatterjee Grupos de notícias: bit.listserv.db2-l Para: DB2-L@RYCI.COM Data: Quarta-feira, 12 de Julho de 2000 12:58 Assunto: Job opportunity in London,UK for UDB DBA



Hi List folks

We are a dotcom start up company in London, UK and are looking for a DB2 DBA with 6+ months experience. We are running UDB 7.1 on an RS/6000. [...] 19655 50 10_easytrieve19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Thu, 13 Jul 2000 07:52:57 -0300122_iso-8859-1 Where can I download CA-easytrieve programming manuals from?

Thanks for the reply.

Kleber

19706 72 12_Re: 00C9008413_Adrian Savory24_adrian.savory@ZURICH.COM31_Thu, 13 Jul 2000 12:11:24 +0100457_us-ascii Pete,

Which DB2 monitor do you have? Most would be able to give you this information. For example, Omegamon has an object analysis option - you can drill down to database/tablespace level and see what threads are using most pages.

hth,

Adrian Savory ACSIS Ltd







Pete Coles cc: Sent by: DB2 Data Subject: Base Discussion List [...] 19779 128 22_Re: RELEASE DEALLOCATE11_rick creech18_ykcirc@HOTMAIL.COM29_Thu, 13 Jul 2000 11:32:37 GMT638_- You might want to try STOP AT COMMIT. HTH Rick Creech



>From: Adrian Savory >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: RELEASE DEALLOCATE >Date: Thu, 13 Jul 2000 11:24:57 +0100 > >Julian, > >I've seen a similar problem where the first CICS transaction of the day did >a mass delete of the table to clear out the previous day's data. The >transaction was defined for thread re-use and for years this was not a >problem because the first transaction in the morning occurred in isolation >and the locks released when the thread was not re-used [...] 19908 77 44_Re: DB2 Connect Problem to Mainframe DB2 5.110_Robert Ord21_robertord@HOTMAIL.COM29_Thu, 13 Jul 2000 11:40:26 GMT669_- I have tried setting the current sqlid in the command line processor and this works fine. The problem exists because the odbc application can't issue a set current sqlid command. It relies on DB2Connect setup to point it in the right direction.



>From: Roland Chua >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2 Connect Problem to Mainframe DB2 5.1 >Date: Wed, 12 Jul 2000 17:06:11 +0800 > >Hi Robert, >Have u try seting "current sqlid" in DB2 Command Line Processor >environment? I had try setting current sqlid in DB2 command Line Processor >environment and its [...] 19986 386 26_Re: How big's the VARCHAR?25_Aurora Emanuela Dell'Anno19_ibm1@EGEBANK.COM.TR31_Thu, 13 Jul 2000 06:37:59 -0500451_- dave,

which null indicators are we talking about?

as a matter of fact if the end-user can deal with the trailing blanks and receives the values passed in a field which is defined as large enough (in this case 3500 bytes) then you can do the call only passing the WS-PARM-RESULTS-DAT which will be as long as the result is (btw, the level 49 was the result of a cut-and-paste operation, it is not a prereq for the thing to work!). [...] 20373 20 14_repro for logs11_Rolf Schwab28_rolf.schwab@CREDIT-SUISSE.CH31_Thu, 13 Jul 2000 14:17:35 +0200438_iso-8859-1 dear list-members is there anybody out there .... who knows: how to convert a db2-log dataset (linear vsam) into a seq. dataset. any help is appreciated tia rolf a. schwab





===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20394 87 12_Re: 00C9008410_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Thu, 13 Jul 2000 14:27:08 +0100455_iso-8859-1 ta, i'll try that

-----Original Message----- From: Adrian Savory [mailto:adrian.savory@ZURICH.COM] Sent: Thursday, July 13, 2000 11:11 AM To: DB2-L@RYCI.COM Subject: Re: 00C90084



Pete,

Which DB2 monitor do you have? Most would be able to give you this information. For example, Omegamon has an object analysis option - you can drill down to database/tablespace level and see what threads are using most pages. [...] 20482 87 25_Re: DB2 Connect & Solaris11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Thu, 13 Jul 2000 08:17:21 -0400378_iso-8859-1 We are just now putting DB2 Connect up on Solaris so I can't reply in specific. We have other DB2 Connect working (NT) so here are some tips:

One of the first things you need to do to make sure this is going to work is to verify the OS/390 Host information. In the xxxMSTR task on the host there will be these messages if TCP/IP connections are possible: [...] 20570 101 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Thu, 13 Jul 2000 08:37:56 -0400506_us-ascii When I code it exactly as below, I get:-

DSNH312I E DSNHSMUD LINE 253 COL 22 UNDEFINED OR UNUSABLE HOST VARIABLE "WS-PARM-RESULTS"

Is there something in the pre-compiler I have to do to make it accept a group item?

I am only able to get through the pre-compile by doing a redefine on the group item but then that is making it a non-variable length variable. The value for SQL-PVAR-TYPE1 is always +453 which is probably fix length CHAR the SQL-PVAR-LEN1 is +3500 though. [...] 20672 49 18_Re: repro for logs15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Thu, 13 Jul 2000 07:49:15 -0500590_iso-8859-1 The ARCHIVE process turns an active log into a sequential file (the second on the cartridge after the BSDS).

However if you want to process it with a user program it is non-trivial (part rows, compressed data etc).

3rd party vendors do have products that will produce a 'logical log' that is much easier to process with user programs and can be filtered to contain only the data you require. One such is RESOLVE LOGMASTER for DB2 for MVS from BMC Software Inc - www.bmc.com. It does many other useful things with log data as well. [...] 20722 47 31_Re: enable DDF for Open Edition14_Johnson, Tracy22_TJohnson@GUIDEMAIL.COM31_Thu, 13 Jul 2000 07:55:12 -0500371_iso-8859-1 Yes, you're correct. It should read "Replace ddfgid with the MVS group id".

Above that example is the example for adding the DDF user-id.

The "x" in GID(x) can be any positive integer value, and it's recommended that it be a unique qualifier. Also, you must have SuperUser authority to define the OMVS Segment to a group or other user. HTH. [...] 20770 92 32_Re: SQL/DS 3.5: Archive question33_Luiz Cesar Vinhaes Da Costa Junor25_vinhaes@SASSECAIXA.COM.BR31_Thu, 13 Jul 2000 09:54:29 -0300434_us-ascii



Dear Thomas

Why you use logmode N? I always use logmode = L , with no problems.

If the fail occurs between the reorg and a Larchive, you will lose the changes occured in this interval of time, otherewisw there is no problems.

Luiz Cesar V da Costa Jr.















Thomas Machovec em 13/07/2000 04:59:35 [...] 20863 89 32_Re: SQL/DS 3.5: DUALLOG question33_Luiz Cesar Vinhaes Da Costa Junor25_vinhaes@SASSECAIXA.COM.BR31_Thu, 13 Jul 2000 10:13:53 -0300469_us-ascii



Dear Thomas

Why you change to duallog?

I always use one log, and never occured any problem with my log. You nedd dual log?

Luiz Cesar V da Costa Jr







Thomas Machovec em 13/07/2000 06:28:24

Favor responder a DB2 Data Base Discussion List

Para: DB2-L@RYCI.COM cc: (bcc: Vinhaes C Luiz/MATRIZ/SASSE) Assunto: SQL/DS 3.5: DUALLOG question 20953 33 38_Re: Rejected posting to DB2-L@RYCI.COM14_Mike Lebermann18_Lebermm@BCBSKC.COM31_Thu, 13 Jul 2000 08:35:50 -0500435_US-ASCII On UDB you can specify "select * from owner.table fetch first 10 rows only". Not sure if you can do that on OS390.

>>> kfv@UOL.COM.BR 07/12/00 04:47PM >>> How can I limit the number of rows produced by my query?

For example, if I issue the query: "SELECT * FROM OWNER.TABLE", I know that tens of thousands rows will come up. What if I want that only the 10 or 20 first ones come up as a result of my query? [...] 20987 97 22_Re: RELEASE DEALLOCATE15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM31_Thu, 13 Jul 2000 06:45:32 -0700401_iso-8859-1 Julian, I recently heard Bonnie Baker speak about Release(Deallocate) and am in the process of changing our batch jobs to deallocate to reduce internal locking time. Anyway here is some of what I think I understood her to say... The Release parameter tells DB2 when to release 'intent' locks acquired on the tablespace and table and when to release some other internal resources also. [...] 21085 409 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V515_Gingras, Martin30_Martin.Gingras@CCRA-ADRC.GC.CA31_Thu, 13 Jul 2000 09:49:19 -0400373_iso-8859-1 What we got is a test fix. The APAR # to track is PQ39421. This APAR is for full outer join only but the fix also works for the left outer join as well. I am not sure it is generally available.

We first got problems with left outer join returning nulls when we should have a value and we then ran into the FULL OUTER join problem (Similar to yours). [...] 21495 74 28_Re: CA's DB2 Tech Forum 200013_Horacio Villa17_hvilla@TTI.COM.AR31_Thu, 13 Jul 2000 11:17:48 -0300509_iso-8859-1 Janis,

do you know if/when the DB2 Tech Forum World Tour is comming to Argentina? Horacio Villa TTI S.A. Buenos Aires

-----Mensaje original----- De: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]En nombre de Thomas, Janis Enviado el: Miércoles 12 de Julio de 2000 18:13 Para: DB2-L@RYCI.COM Asunto: CA's DB2 Tech Forum 2000



Computer Associates announces the schedule of North American cities for the remainder of their DB2 Technology Forum World Tour 2000: [...] 21570 29 30_GRANTEELIST and DB2 Connect PE13_Tierney, Mark20_mtierney@FIRSTAM.COM31_Thu, 13 Jul 2000 09:18:39 -0500407_- Greetings All.....

I am running DB2 Connect PE Fixpack13 and am trying to set GRANTEELIST to limit the objects returned from DB2 but am having no luck. Has anyone experienced any problems doing this? Are there any relevant PATCH1/2 values for this?

Any help would be greatly appreciated.

Thanks, Mark

Mark Tierney Technical Specialist - DB Analysis First American Excelis [...] 21600 63 22_Re: Coding Standards !0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 13 Jul 2000 09:20:48 -0500520_us-ascii Take a look at the "multimania" site, http://www.multimania.com/db2usa/earticle.htm , at the very bottom of the list has these 3 links. They are the only standards docs I've seen online. They could provide a frame of reference for you.

http://www.das.state.ne.us/das_cdp/dba/dbmdoc01.html - DB2 APPLICATIONS DEVELOPMENT REFERENCE GUIDE, from Nebraska State.

http://www.das.state.ne.us/das_cdp/dba/dbmdoc02.html - DB2 SQL coding rules of thumb from data processing division of Nebraska State. [...] 21664 17 34_Access to DB2 OS/390 Data Movement13_Crowley, Paul21_CrowleP@DIVINVEST.COM31_Thu, 13 Jul 2000 09:11:53 -0500449_iso-8859-1 What's the best way to move 20,000 rows from an Access table to DB2v5? Establishing an Access ODBC connection (StarSQL), and using cut/paste seems to take 1 sec/row. I'm impatient.

TIA Paul

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 21682 72 12_Re: 00C9008413_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Thu, 13 Jul 2000 09:28:56 -0500298_us-ascii Pete,

If you check the DSNMSTR log, you can find which application was the first to have a problem with DSNDB07. This is usually the person (QMF) or the program that caused the problem. When it happens to us, it is usually a bad QMF query, but has occasionally been a program. [...] 21755 91 12_Re: 00C9008410_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Thu, 13 Jul 2000 16:32:49 +0100526_iso-8859-1 ta, i think it was qmf that was the issue.

-----Original Message----- From: Helen Johnson [mailto:helen_johnson@RAC.RAY.COM] Sent: Thursday, July 13, 2000 2:29 PM To: DB2-L@RYCI.COM Subject: Re: 00C90084



Pete,

If you check the DSNMSTR log, you can find which application was the first to have a problem with DSNDB07. This is usually the person (QMF) or the program that caused the problem. When it happens to us, it is usually a bad QMF query, but has occasionally been a program. [...] 21847 32 38_Re: Access to DB2 OS/390 Data Movement14_Arnold, Mark S20_Mark.Arnold@BNSF.COM31_Thu, 13 Jul 2000 09:38:01 -0500480_iso-8859-1 How about exporting to a text file, upload the text file and use load utility...?

-----Original Message----- From: Crowley, Paul [mailto:CrowleP@DIVINVEST.COM] Sent: Thursday, July 13, 2000 9:12 AM To: DB2-L@RYCI.COM Subject: Access to DB2 OS/390 Data Movement



What's the best way to move 20,000 rows from an Access table to DB2v5? Establishing an Access ODBC connection (StarSQL), and using cut/paste seems to take 1 sec/row. I'm impatient. [...] 21880 136 22_Re: RELEASE DEALLOCATE15_Toine Michielse18_vndobtm@US.IBM.COM31_Thu, 13 Jul 2000 16:29:56 +0100505_us-ascii Hello Sandi,

Just to make sure there's no misunderstanding....

Please be aware it's not only INTENT locks that are under control of the release bind parameter. It really should read the tablespace level locks which are USUALLY intent locks. However, when you application is not a 'healthy'committer and escalates locks or the lock is NOT an intent lock, it will also be held past commits in the case of release(deallocate). This is not what you want for good concurrency...... [...] 22017 40 38_Re: Access to DB2 OS/390 Data Movement41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR31_Thu, 13 Jul 2000 11:39:36 -0300332_iso-8859-1 We use a DB2 for Win NT for moving data from PC to mainframe. We create a table on NT and use IMPORT and EXPORT utilities in order to import from an ASCII file (several types are allowed). Using the same utilities we move the data into our OS/390 DB2. If you have DB2 for Win NT this might be an interesting option. [...] 22058 374 143_IBM ITSO Residency: SW-M101 Cross-Platform DB2 Stored Procedure s , exploiting: SQL Procedures Language, DB2 SPB, and the Distributed Deb ugger13_Morrill, John12_JohnM@VP.NET31_Thu, 13 Jul 2000 09:09:05 -0600486_iso-8859-1 Dear DB2 Interested Parties,

Please find below an announcement of a residency with the International Technical Support Organization (ITSO*, the "Redbook" writers), for a project to research and update IBM Redbook SG24-5485 with the latest information about DB2 cross-platform SQL Procedures language support, Stored Procedure Builder, and the Distributed Debugger for stored procedures. This San Jose, California residency begins 2 October and lasts for 6 weeks. [...] 22433 316 61_DB2 on Windows 2000: Certification and TPC-C Benchmark Record13_Morrill, John12_JohnM@VP.NET31_Thu, 13 Jul 2000 09:13:45 -0600722_iso-8859-1 Dear DB2 Interested Parties,

DB2 Universal Database Extended Edition (DB2 UDB EE) Version 7 has been Certified for Windows 2000 Server ( http://www.veritest.com/mslogos/windows2000/certification/ ). DB2 is the first IBM product to be Windows 2000 certified and the first (and so far only) database product to be certified.

Further, IBM, Microsoft, and Intel announced (see press release below) at Microsoft's Tech Ed Conference in Amsterdam this week that they collaborated recently on a joint project that resulted in a new record for the Transaction Processing Council's OLTP TPC-C benchmark - delivering 440,879 Transactions Per Minute using IBM DB2 Universal Database Enterprise-Extended [...] 22750 23 34_UDB version 7.1 media availability12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 13 Jul 2000 08:23:04 -0700264_us-ascii Has anyone gotten a GA copy of UDB 7.1 for AIX yet? I've been told that there was a problem manufacturing the CDs and that they're not available yet(and it's been a month since GA). All I can get off IBM's web site is a wimpy PE version. Thanks, Tim [...] 22774 71 32_Re: SQL/DS 3.5: DUALLOG question15_Dunton.Doug.MCI31_dunton.doug.mci@HARTE-HANKS.COM31_Thu, 13 Jul 2000 10:28:49 -0500466_ISO-8859-1 Thomas,

The DB2/VSE 5.1 Operation manual says to only use the duallog option for startup = L and C. The coldlog function sets this so you don't need it during warm start.

Doug Dunton Harte-Hanks Corp





______________________________ Reply Separator _________________________________ Subject: SQL/DS 3.5: DUALLOG question Author: DB2 Data Base Discussion List at ~INTERNET Date: 07/13/2000 11:28 AM [...] 22846 17 47_Re: Creating/Stripping Data as Test DB2 Tables.12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 13 Jul 2000 10:28:17 -0500403_- If the rows you want can be specified in SQL and they are a small percentage of the total, then you can save lots of work and time by doing the selection first. Table space scans and unloading every row are a good technique if you want most of the rows or probably even 20%, but running the first step much faster and eliminating the second one of processing with another program will be better. [...] 22864 462 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V523_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Thu, 13 Jul 2000 11:47:10 -0400563_iso-8859-1 Thanks Martin...I will look it up..

Sanjay > -----Original Message----- > From: Gingras, Martin [SMTP:Martin.Gingras@CCRA-ADRC.GC.CA] > Sent: Thursday, July 13, 2000 9:49 AM > To: DB2-L@RYCI.COM > Subject: Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V5 > > What we got is a test fix. The APAR # to track is PQ39421. > This APAR is for full outer join only but the fix also works > for the left outer join as well. I am not sure it is generally available. > > We first got problems with left outer join returning nulls when we should > have [...] 23327 45 38_Re: Access to DB2 OS/390 Data Movement13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 13 Jul 2000 10:49:22 -0500352_iso-8859-1 Is this a one off or a regular process ? - is something to take into account to see how much effort you want to spend in solving this problem.

We did a one off by unloading, then FTP to os390, then use load utility to load into DB2.

nb: FTP has no error checking. We had situations some bits were incorrectly transferred. [...] 23373 108 26_Re: How big's the VARCHAR?17_Alessandro Brezzi28_brezzi.isac@INTERBUSINESS.IT31_Thu, 13 Jul 2000 18:23:43 +0200533_us-ascii Hi Martin, I use a big VARCHAR in COBOL; my table is declared as follow (DB2 V5.1. OS/390, standard DCLGEN)

EXEC SQL DECLARE VTIFG.CODE_TEMPORANEE TABLE ( NUMERO_BLOCCO INTEGER NOT NULL, DT_ORA_INSER TIMESTAMP NOT NULL, RECORD VARCHAR(32666) NOT NULL, MODULO_CONTROLLO CHAR(8) NOT NULL, STATO_RECORD CHAR(1) NOT NULL, TIPO_INFO CHAR(4) NOT NULL, DT_ORA_VARIAZ TIMESTAMP NOT NULL ) END-EXEC. .... 10 ICOD-RECORD. * RECORD LENGTH 49 ICOD-RECORD-LEN PIC S9(4) USAGE COMP. * RECORD 49 ICOD-RECORD-TEXT PIC X(32666). [...] 23482 45 34_Distributed Data Facility Question13_Matheny, Bill25_bill.matheny@EXPERIAN.COM31_Thu, 13 Jul 2000 09:45:05 -0700449_- Mark,

I've faced this problem many times. To get around it, I do the following:

1) Log onto the test DB2 2) Go into QMF 3) CONNECT to the Production DB2 System (on the command line) 4) query the production table to get the needed rows 5) CONNECT back to the test DB2 6) SAVE data as temptable (QMF will create the table for you in your default ts) 7) Run a query like the one you indicated, using the temptable in the subselect [...] 23528 413 46_Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V523_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Thu, 13 Jul 2000 13:00:29 -0400600_iso-8859-1 No I had not, because I do know now what's causing the problem (as explained in one of my earlier e-mail). Anyway I ran the check index (after receiving your response), didn't see any problem there.

Sanjay

> -----Original Message----- > From: William Gannon [SMTP:wgannon@INET-SERVICES.PALMBEACH.K12.FL.US] > Sent: Friday, March 12, 1999 11:39 AM > To: DB2-L@RYCI.COM > Subject: Re: QUERY GIVES DIFFERENT RESULTS IN V6 AND V5 > > Hi Jain, > > Have you verified the validity of the contents of the indexes for the > table involved (run Check Index) ? > > JAS > Bill G. > [...] 23942 77 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Thu, 13 Jul 2000 13:24:35 -0400349_us-ascii It is a stored procedure definition not a table definition.

The fact about setting the length field before the data field is noted and a display of the full variable in the stored procedure itself shows that it is truely variable length but still, when it goes back, it looses the length definition and only get 255 bytes back. [...] 24020 27 47_How to store an Excel spreadsheet in SQL server23_AITHA, BHEEMSEN (SBCSI)22_BA2984@MSG.PACBELL.COM31_Thu, 13 Jul 2000 10:50:34 -070011_iso-8859-1 24048 30 6_Thanks10_Marc Fuqua19_marcfuqua@YAHOO.COM31_Thu, 13 Jul 2000 10:54:00 -0700362_us-ascii Thanks to Tim Lowe, Roger Miller, Dr. Michael Ebert, & Jim Leask for providing suggestions & comments about improving our RUNSTATS performance. We'll be looking at the various suggestions to see how we can improve the performance.

Marc

===== Marshall (Marc) R. Fuqua Database Administrator TRW Systems & I.T. Group - Des Moines, Iowa [...] 24079 43 51_Re: How to store an Excel spreadsheet in SQL server22_Lindsay, William (BIS)24_WLindsay@EXCHANGE.ML.COM31_Thu, 13 Jul 2000 13:58:21 -0400316_iso-8859-1 Lookup the BLOB datatype in MSDN for storing images, and complied code.



-----Original Message----- From: AITHA, BHEEMSEN (SBCSI) [mailto:BA2984@MSG.PACBELL.COM] Sent: Thursday, July 13, 2000 1:51 PM To: DB2-L@RYCI.COM Subject: How to store an Excel spreadsheet in SQL server



24123 23 29_Visual Basic -- DB2 API Calls13_Fletcher, Jim21_jim.fletcher@BSIS.COM31_Thu, 13 Jul 2000 13:59:40 -0400385_iso-8859-1 Hello,

I am looking for help from someone who has successfully issued the ATTACH api, or other DB2 UDB APIs (typically instance level calls)..such as node or database directory scans, from Microsoft Visual Basic. I have declared the functions(perhaps incorrectly) and get exception conditions at run-time. Any advice is appreciated.

Jim Fletcher Luminant [...] 24147 24 22_DB2 on OS/390 from WEB12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 13 Jul 2000 13:30:00 -0400379_- We are considering the following for WEB access to DB2: - DB2 connect Client Application Enabler on a WEB server - DB2 Connect Enterprise Edition on a UNIX server - DB2 V5.1 on OS/390 V2.6

The WEB server will connect via DB2 Connect Client Application Enabler ===> EE on UNIX server ===> DB2 on OS/390. We are licensed on the UNIX box for EE not on the WEB server. [...] 24172 40 26_Re: DB2 on OS/390 from WEB12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Thu, 13 Jul 2000 14:47:02 -0400285_us-ascii It looks long but it is quite typical. In our case we have Enterprise Edition on an NT box but otherwise the same.

Comments? - Well it works more reliably than it looks like it will! The return times (from a browser) are about 3-5 secs. Not sure what else to say. [...] 24213 34 37_limits for dynamic insert statements?12_craig patton21_prgpatton@HOTMAIL.COM29_Thu, 13 Jul 2000 12:30:27 PDT576_- List: Working with a PeopleSoft Archive tool, (in Beta), we are getting an error on a dynamic insert statement:

-102 LITERAL STRING IS TOO LONG STRING BEGINS.....

Question: Does anyone know if V6 or V7 of DB2 for OS/390 eliminates this limit? I believe the limit is 255 bytes in current V5 @9911 maintenance. I have been informed by the PeopleSoft consultant that the code being used for this process, does not permit the use of a Host Variables in the VALUES clause of the INSERT statement, so I cannot use that solution. Any ideas would be appreciated. [...] 24248 63 41_Re: limits for dynamic insert statements?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 13 Jul 2000 15:44:16 -0400429_us-ascii Craig

The limit is not going to go away in near future. We have similar problem, we overcome this problem by inserting 255 bytes string followed by updates as many time as long the string should be.

Regards, Venkat

-----Original Message----- From: craig patton [SMTP:prgpatton@HOTMAIL.COM] Sent: Thursday, July 13, 2000 3:30 PM To: DB2-L@RYCI.COM Subject: limits for dynamic insert statements? [...] 24312 47 38_Re: Rejected posting to DB2-L@RYCI.COM19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Thu, 13 Jul 2000 19:02:33 -0300472_iso-8859-1 Sorry, it does not work in my MVS-ESA environment. Thanks anyway.

Kleber

-----Mensagem original----- De: Mike Lebermann Grupos de notícias: bit.listserv.db2-l Para: DB2-L@RYCI.COM Data: Quinta-feira, 13 de Julho de 2000 10:38 Assunto: Re: Rejected posting to DB2-L@RYCI.COM



On UDB you can specify "select * from owner.table fetch first 10 rows only". Not sure if you can do that on OS390. [...] 24360 86 37_limiting the number of returning rows19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Thu, 13 Jul 2000 19:08:47 -0300516_iso-8859-1 Thanks for replying. However, I am working in an OS/390 (MVS-ESA) environment. Is there anything that would do for it?



Regards,

Kleber

-----Mensagem original----- De: donny.ledbetter@autozone.com Para: kfv@UOL.COM.BR Data: Quinta-feira, 13 de Julho de 2000 10:01 Assunto: Re: Rejected posting to DB2-L@RYCI.COM





If you are using DB2 Version 6.1 on UNIX, Windows, or OS/2, you can using the following: [...] 24447 276 41_Re: limiting the number of returning rows13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 13 Jul 2000 17:36:47 -0500514_US-ASCII Kleber, Try this

SELECT * FROM owner.table X WHERE 20 >= (SELECT COUNT(*) FROM owner.table Y WHERE Y.colunm <= X.column) AND X.column < somevalue FOR FTECH ONLY

As long as you have an index on "column" and the "< somevalue" filters well it should perform ok. Kurt















>>> kfv@UOL.COM.BR 07/13/00 05:08PM >>> Thanks for replying. However, I am working in an OS/390 (MVS-ESA) environment. Is there anything that would do for it? [...] 24724 56 41_Re: limits for dynamic insert statements?11_Burch, Todd18_Todd_Burch@BMC.COM31_Thu, 13 Jul 2000 17:46:48 -0500386_iso-8859-1 There is a facility in dynamic SQL called parameter markers that can get around this restriction. It is an advanced coding technique, but I figured it out a few years ago, so I'm sure they (Peoplesoft) can too! :0)

They told you the "VALUES" clause won't support host variables, and that is true for dynamic SQL. That is why (one reason) parameter markers exist. [...] 24781 101 41_Re: limiting the number of returning rows11_Burch, Todd18_Todd_Burch@BMC.COM31_Thu, 13 Jul 2000 17:49:15 -0500460_iso-8859-1 There's a reason to migrate to DB2 V7!! DB2 V7 for OS/390 will support that just like you've coded it.

Todd Burch.

-----Original Message----- From: Kleber Faria Vianna [mailto:kfv@UOL.COM.BR] Sent: Thursday, July 13, 2000 5:09 PM To: DB2-L@RYCI.COM Subject: limiting the number of returning rows



Thanks for replying. However, I am working in an OS/390 (MVS-ESA) environment. Is there anything that would do for it? [...] 24883 78 26_Re: DB2 on OS/390 from WEB12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 13 Jul 2000 15:51:16 -0700532_us-ascii Bob- I'd get rid of the extra hop to AIX. You should be able to configure the UDB CAE on NT to access OS/390 directly via DRDA/TCPIP(if you're at least version 5.1) Having the extra hop to AIX will work (it's how we do it now), but it's pretty confusing to diagnose any problems. We're planning on getting rid of our AIX / DB2 Connect box and going NT to OS/390 directly, in the next couple of weeks. I've tested CAE (6.1) on my personal NT workstation and can get to OS/390 DB2 data with no problems. I'm assuming it [...] 24962 109 26_Re: DB2 on OS/390 from WEB22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 13 Jul 2000 19:32:17 -0400351_iso-8859-1 If DB2 Connect EE and CAE are on the NT box (Web Server), then you can go from the NT box (Web server) direct to DB2 for OS/390.

CAE does NOT provide DRDA Application Requester (AR) support to connect to DB2 for OS/390 & DB2 for OS/400.

DB2 Connect provides DRDA AR support to connect to DB2 for OS/390 & DB2 for OS/400. [...] 25072 135 26_Re: DB2 on OS/390 from WEB22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 13 Jul 2000 19:43:31 -0400626_iso-8859-1 Tim -

Hmmmm....

I suppose your personel NT workstation could have DB2 Connect EE installed instead of DB2 Connect PE... The point is some flavor of DB2 Connect is installed.



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: Piontkowski Michael ML > Sent: Thursday, July 13, 2000 7:32 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] DB2 on OS/390 from WEB > > If DB2 Connect EE and CAE are on the NT box (Web Server), then > you can go from the NT box (Web server) direct to DB2 for OS/390. > > CAE does NOT provide DRDA [...] 25208 72 41_Re: limits for dynamic insert statements?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 14 Jul 2000 09:52:01 +1000545_iso-8859-1 INSERT .... VALUES (..., 'first 255 bytes or so' CONCAT 'some more text' CONCAT ' YET MORE TEXT', ...)?

(provided that the total length of the insert is less than 32767 characters, you should be ok.)

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au

-----Original Message----- From: craig patton [mailto:prgpatton@HOTMAIL.COM] Sent: Friday, July 14, 2000 5:30 AM To: DB2-L@RYCI.COM Subject: [DB2-L] limits for dynamic insert statements? [...] 25281 96 52_Using a gateway to trigger insert to DB2 from Oracle9_JANA BONE25_JANA.BONE@TLC.STATE.TX.US31_Thu, 13 Jul 2000 19:15:38 -0500363_US-ASCII I am looking for feedback, comments, etc. on: Is any one out there using some sort of gateway or datajoiner solution to trigger an insert into DB2 as a result of an insert or update in Oracle?

We have a project that the developers want to put in Oracle (on an NT) that will have two tables that at some point will have 6-8 Million rows each. [...] 25378 138 22_Re: RELEASE DEALLOCATE12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Fri, 14 Jul 2000 11:08:56 +1000339_iso-8859-1 Julian, depending on your application RELEASE(DEALLOCATE) can be a useful tool. But it does too have it's disadvantages, one of which you have just discovered, it can also consume significantly more EDM pool. A good reference is the CICS DB2 Guide, although I'd still take some of the recommendations with a grain of salt. [...] 25517 60 30_Problem in loading LOB columns8_LAM, Leo17_l.lam@IOOF.COM.AU31_Fri, 14 Jul 2000 11:26:02 +1000331_iso-8859-1 I would like to know if anyone has experienced a similar problem when loading data into a CLOB column in DB2 OS390.

Steps to create the LOB columns : * Create a table with a single primary key column. * Alter the table to add a CLOB (1M) and a BLOB (1M) columns * Create the auxiliary TS, tables and indexes [...] 25578 260 26_Re: How big's the VARCHAR?15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 14 Jul 2000 04:01:05 +0100400_iso-8859-1 Hello Martin,

I understand your frustration. Going over your definitions with a magnifying glass I noticed that the length field does NOT have an S in the PIC string. According to the manual (see the end of my append...) a VARCHAR hostvar should be defined as:



01 WS-PARM-RESULTS. > 49 WS-PARM-RESULTS-LEN PIC S9(4) BINARY. > 49 WS-PARM-RESULTS-DAT PIC X(3500). [...] 25839 67 56_Re: Using a gateway to trigger insert to DB2 from Oracle12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Thu, 13 Jul 2000 20:55:35 -0500365_us-ascii Since you are starting from Oracle, I believe you cannot use Datajoiner.

Oracle sells a 'transparent' gateway to DRDA. We have used it for years.

The gateway uses Oracle 'database links' to make DB2 tables seem local to the Oracle instance.

A trigger on an Oracle table can insert a row into a DB2/OS390 database via the gateway. [...] 25907 151 26_Re: How big's the VARCHAR?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 14 Jul 2000 12:17:44 +1000499_iso-8859-1 A-MAZ-ING what one just assumes until one hits a brick wall! I just thought that your coding would be allright, but one of the possible causes of DSNH312I is: "The length attribute of a character or varying character host variable passed in a CALL statement exceeds 255 characters."

Then I looked in the SQL Reference under the CALL statement and found "If a host variable is used: It must be a character string variable with a length attribute that is not greater than 255." [...] 26059 193 26_Re: How big's the VARCHAR?15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 14 Jul 2000 05:10:17 +0100353_us-ascii Hello James

Good catch, however, this applies to the situation where you specify the name of the stored procedure in a hostvar.

So if you code CALL :hostvar1 (...)

then hostvar1 must follow the rules that you just quoted. These rules do not apply to hoatvariables that you pass as parameters to the stored procedure.. [...] 26253 18 14_Top Secret/DB218_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM31_Thu, 13 Jul 2000 23:13:03 -0400326_- We are planning for the implementation of Top Secret/DB2. Does anyone have any experience with it? Things to look out for? I'd also like to know if you have any recommendations for any DB2 training for our Security Administrators, which is a separate group at our shop.

Thank you for your input, Deborah Gresham [...] 26272 207 26_Re: DB2 on OS/390 from WEB12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 13 Jul 2000 20:30:17 -0700341_us-ascii I installed the CAE disk that came with a 6.1 EE AIX UDB system (packet of CDs). I think this effectively gives me a 6.1 EE on my workstation. My understanding is that DB2 Connect is part of the UDB code now. I'm not sure if it's a single user PE flavor, or an EE flavor. But I'm pretty sure I never even saw a DB2 Connect CD. [...] 26480 39 29_regarding OPTIMIZE FOR N ROWS17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Thu, 13 Jul 2000 21:01:23 PDT551_- Hi Folks,

I have doubt on optimize for n rows.

i have query like this declare cursor rampx select ramp_id, ramp_name, coorg_adress from ramp_table a, coorg_table b where a. ramp_id = b. ramp_id and a.client_id = 02226348 optimize for 1 row.

if i don't use optimizer for 1 row, then this query will bring like around 3000 rows. but if i use optimize for 1 row, my question is does it bring all the 3000 rows to buffer then does it fetch only one from buffer or it will only one row from about query to buffer and fetch that [...] 26520 60 33_Re: regarding OPTIMIZE FOR N ROWS19_Nagaraj Pudukotai S33_nagaraj.pudukotai@HPSBLR.SOFT.NET31_Fri, 14 Jul 2000 09:44:39 +0500633_- Hi Ravi

An article by Bonnie Baker in the recent DB2 magazine should help you. Check it out at http://www.db2mag.com/summer00/programmer.shtml.

Thanks

Nagaraj

> -----Original Message----- > From: ravi kumar hassan [SMTP:ravibh@HOTMAIL.COM] > Sent: Friday, July 14, 2000 9:31 AM > To: DB2-L@RYCI.COM > Subject: regarding OPTIMIZE FOR N ROWS > > Hi Folks, > > I have doubt on optimize for n rows. > > i have query like this > declare cursor rampx > select ramp_id, ramp_name, coorg_adress > from ramp_table a, coorg_table b > where a. ramp_id = b. ramp_id > and a.client_id = 02226348 > optimize for [...] 26581 148 33_Re: regarding OPTIMIZE FOR N ROWS0_29_Greg.Palgrave@BANKWEST.COM.AU31_Fri, 14 Jul 2000 12:30:27 +0800402_us-ascii Ravi,

OPTIMIZE FOR N ROWS does not change the number of rows potentially returned by a query. It is used so that you can influence the access path of a query.

From the manual (V5 Application Programming and SQL guide) section 6.3.5.2:

"What OPTIMIZE FOR n ROWS Does: The OPTIMIZE FOR n ROWS clause lets an application declare its intent to do either of these things: [...] 26730 99 33_Re: regarding OPTIMIZE FOR N ROWS7_Sanjeev19_Sanjeev@INTERRA.COM31_Fri, 14 Jul 2000 10:06:16 +0530548_iso-8859-1 Hi Ravi, First of all "Single" Row is never brought to the buffer.It is always the page which DB2 processes and brings to the buffer. As far as the optimize for 1 row /n row is concerned it is purely used for the performance reasons in some of the cases, mostly in the online application ,where you are almost sure that user is going to browse some 1st set of rows in the screen.Suppose your query satisfies 3000 rows and if you have coded "Optimize for" clause then DB2 will definitely return 3000 rows but the access path will be [...] 26830 197 26_Re: How big's the VARCHAR?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 14 Jul 2000 14:53:25 +1000457_iso-8859-1 Tear scales from the eyes, vow (again) to not leap to conclusions, return to drawing board, try code to reproduce error, correct code so that it works.

As someone else suggested, it's the missing 'S' from "PIC 9(4) BINARY" wot causes the error.

James -----Original Message----- From: Toine Michielse [mailto:vndobtm@US.IBM.COM] Sent: Friday, July 14, 2000 2:10 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] How big's the VARCHAR? [...] 27028 82 33_Re: regarding OPTIMIZE FOR N ROWS14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 14 Jul 2000 15:03:10 +1000491_iso-8859-1 Kumar,

Whether or not you have 'optimize for n rows' does not affect the number of rows that will be fetched - the entire result will be retrieved (assuming you keep fetching until you get sqlcode=+100) whatever you code.

What optimize does affect is _how_ db2 will retrieve those rows. The only way of determining what happens in any situation is to run an explain on the query and examine your PLAN_TABLE. Read the many postings on the topic in the archives. [...] 27111 61 30_Re: Stored Procedure Execution14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 14 Jul 2000 15:26:12 +1000502_iso-8859-1 Have a look at these ifcids: 124, 148, 242, 243, 325.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Keister, Jim [mailto:JKeister@QRS.COM] Sent: Tuesday, July 11, 2000 8:16 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Stored Procedure Execution



Is there a simple way to determine if a Stored Procedure has been CALLed by a Trigger or if a Trigger has executed?? [...] 27173 70 29_Re: DB2 UDB V6 or V7 Monitors11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Fri, 14 Jul 2000 01:36:51 -0400518_us-ascii Hi Paul,

We've had tools for DB2 UDB since 1998. Our Database-GUYS(TM) Performance Management Tools can easily launch your predefined scripts. In fact, our tools even go so far as to analyze performance data and provide tuning recommendations. They work while you sleep, every day and night, automatically keeping a watchful eye on your database. We provide simple, easy to use, HTML reports and telnet interfaces, so you can be successful with the tools on your LAN or dialed-up on a 28.8 modem. [...] 27244 36 17_UDB for NT script19_Peggs, Neal (ANFIS)22_neal.peggs@ANFIS.CO.UK31_Fri, 14 Jul 2000 09:05:53 +0100415_- Hi,

We are running UBD V5 on NT and are trying to develop a simple script to automate some data moves. All the script does is connect to a database on an NT server to export data from one table to a file in ixf format. It then imports that data to a table in another database on the same server. However, we then try to connect to an OS/390 subsystem to import the data into a table on the mainframe. [...] 27281 75 33_Re: regarding OPTIMIZE FOR N ROWS4_IBM119_IBM1@EGEBANK.COM.TR31_Fri, 14 Jul 2000 13:24:21 +0300555_- ravi,

there is a very interesting article by bonnie baker in the programmers only section of db2 magazine (quarter 2,2000: Summer), available on the web at www.db2mag.com.

I think you'll find lots of answers in that article.

HTH.

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@egebank.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 27357 75 56_Re: Using a gateway to trigger insert to DB2 from Oracle0_23_sjtoeniskoetter@AEP.COM31_Fri, 14 Jul 2000 07:52:33 -0400483_iso-8859-1 Jana,

We have an application that does just what you're asking. MQ is used in the middle. The insert, update, or delete in Oracle triggers a CICS transaction on DB2 OS/390 through MQ. It works fine.

Steve









JANA BONE @RYCI.COM> on 07/13/2000 08:15:38 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 27433 207 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Fri, 14 Jul 2000 07:58:41 -0400590_iso-8859-1 No, signing the length field didn't help.

Toine Michielse wrote:

> Hello Martin, > > I understand your frustration. Going over your definitions with a > magnifying glass I noticed that the length field does NOT have an S in the > PIC string. According to the manual (see the end of my append...) a VARCHAR > hostvar should be defined as: > > 01 WS-PARM-RESULTS. > > 49 WS-PARM-RESULTS-LEN PIC S9(4) BINARY. > > 49 WS-PARM-RESULTS-DAT PIC X(3500). > > As Aurora suggest you CAN use a character hostvariable (just like you are > effectively doing) for a VARCHAR [...] 27641 34 14_Multiple VCATs0_19_mike.holmans@BT.COM31_Fri, 14 Jul 2000 13:16:55 +0100442_- We currently use the DB2 name (subsystem name in non-datasharing, group name in datasharing) as the VCATname for the stogroups in any given DB2. We have a system which is growing, and could grow to having 580,000 datasets. It has been suggested to us that perhaps 580,000 is rather a lot of datasets to put in one VCAT, and that we should really be considering having multiple VCATs with a target of no more than about 20,000 in each. [...] 27676 67 33_Re: regarding OPTIMIZE FOR N ROWS12_craig patton21_prgpatton@HOTMAIL.COM29_Fri, 14 Jul 2000 05:26:55 PDT462_- Kumar,

You have a misunderstanding of the function of the OPTIMIZE FOR n ROWS. This parameter is for the OPTIMIZER ONLY, while making it's decision as to which access path to choose. It does NOT effect the number of rows returned by the SQL Statement. This Parameter was added for those times when YOU (the developer) know the data well enough that YOU can tell the Optimizer how many rows will be returned; this can effect the access path chosen. [...] 27744 233 34_2ndary authids/DSN3@ATH/Top Secret12_Scott Bowden16_shbowden@GFB.ORG31_Fri, 14 Jul 2000 08:50:43 -0400562_iso-8859-1 Greetings - We are a new DB2 shop and I am trying to get the 2ndary authid security working and having some problems. As I read the manuals it seems straight foward - 1) assemble the sample exits from IBM 2) define the necessary Top Secret groups using the IBMGROUP facility and 3) permit the group to a user. But either it isn't working or I have a misconception about how it is supposed to work. As I understand it when using 2ndary authids DB2 will automatically check for the necessary permissions to do something in the following sequence - [...] 27978 39 19_Disable Datasharing9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 14 Jul 2000 13:20:13 +0100608_us-ascii I appreciate that disabling datasharing is going to be a very unlikely event and a last resort procedure (e.g. use one-way datasharing first)......but I decided to give the 'disable procedure' a test just to confirm it would work ok. I disabled datasharing in a sysprog datasharing DB2 sub-system using the procedure documented in the 'DS: Planning And Admin' manual. The procedure worked ok but it appears to have introduced a problem with an OEM product on the disabled system. Im just curious as to whether you can validly re-enable the system for datasharing and then disable it again at a [...] 28018 76 18_Re: Multiple VCATs11_rick creech18_ykcirc@HOTMAIL.COM29_Fri, 14 Jul 2000 13:40:38 GMT397_- Mike, In response to point 3: For application databases, there is no restriction from a naming standard point-of-view in datasharing or non-datasharing as far as the vcat name is concerned. At the company where I am currently: it's datasharing and we vary the vcat name based upon the application group. In other words we have many different vcat names: a different vcat name per stogroup. [...] 28095 49 26_Re: Point in Time Recovery14_Mike Lebermann18_Lebermm@BCBSKC.COM31_Fri, 14 Jul 2000 08:39:37 -0500516_US-ASCII Thanks for the info Mark.

I have been able to recover a tablespace to a point in time after the full backup and to the end of the logs. What I'm trying to accomplish is to restore the tablespace to the point in time right after the full database backup. I think the problem might reside in the db2uext2 user exit. It goes in a loop after retrieving the same 2 or 3 log files. This is happening both on the command line and from the control center. I think I'll go through the C code to see if I [...] 28145 72 23_Re: Disable Datasharing23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 14 Jul 2000 14:57:50 +0100380_iso-8859-1 Andy which OEM product - mail me direct and I will test here(we have many OEM products here) as I see no reason why you cannot just keep going through the process everytime ..

Les

-----Original Message----- From: Andy Hunt [mailto:Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK] Sent: Friday, July 14, 2000 1:20 PM To: DB2-L@RYCI.COM Subject: Disable Datasharing [...] 28218 264 26_Re: DB2 on OS/390 from WEB19_Michael Piontkowski34_michael_piontkowski@COMPUSERVE.COM31_Fri, 14 Jul 2000 09:58:14 -0400441_iso-8859-1 The IBM website that describes the DB2 UDB & DB2 Connect editions is http://www-4.ibm.com/software/data/db2/udb/devl_ed.html Keep in mind this is for DB2 UDB V7.1

Yes, UDB EE & EEE V6.1 provides connectivity to DRDA servers not CAE. Did you do a typical, minimal or custom install. What components did you select on the install. I'm guessing DB2 Connect is bundled with the product. I don't know whether its EE or PE. [...] 28483 129 38_Re: 2ndary authids/DSN3@ATH/Top Secret0_19_mike.holmans@BT.COM31_Fri, 14 Jul 2000 15:03:54 +0100589_iso-8859-1 Scott,

There's general concepts and there's specifics. How DB2 handles authorization checking with secondary authids available is quite often confusing.

Execution of a plan is allowed if *any* of the primary or secondary authids held by the user has the EXECUTE privilege. This is why you can run DSNTIAD.

However, DSNTIAD doesn't contain any SQL statements whose authorisation needs checking. It prepares and executes SQL statements which you supply to it at run time, which is a dynamic SQL process. Dynamic SQL authorisation is checked at run-time. [...] 28613 116 26_Re: DB2 on OS/390 from WEB0_15_leon@CA.IBM.COM31_Fri, 14 Jul 2000 10:17:53 -0400387_us-ascii Tim, I am sorry to disappoint you but there is absolutely NO WAY that DB2 CAE can connect to DB2 for OS/390 directly. You say you've tested it and it works. You either have a DB2 Connect installed on your Windows NT box and you don't know about it or you have cataloged your DB2 for OS/390 on your Windows NT workstation so that it still goes through a gateway some place. [...] 28730 163 26_Re: DB2 on OS/390 from WEB0_15_leon@CA.IBM.COM31_Fri, 14 Jul 2000 10:20:50 -0400353_us-ascii If the NT box is a server that does work on behaf of many users then DB2 Connect Personal Edition is NOT an option. Personal Edition is licensed for use by a single user and can not be used for enabling connectivity from midle-tier servers and DB2 for OS/390.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com [...] 28894 156 38_Re: 2ndary authids/DSN3@ATH/Top Secret12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Fri, 14 Jul 2000 10:26:48 -0400384_iso-8859-1 Hi Scott,

Just to summarize what Mike wrote....DML commands (select, insert, etc) can inherit privs from a secondary authid, but DDL commands (create, grant, revoke, etc.) require that the current sqlid have the appropriate authority. And I agree with Mike- at first it doesn't seem to make sense why certain commands only check the authority of current sqlid. [...] 29051 140 23_Re: Disable Datasharing9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 14 Jul 2000 15:27:57 +0100547_us-ascii Ken/Les, The product is BMC Logmaster V2.1.00 (I have raised problem 360069 with uksupport).

-------------

The disable procedure involves cold starting DB2 by updating the BSDS using the following cards with DSNJU003, DATASHR DISABLE * * WARNING!!! DO NOT CHANGE THE STARTRBA AND ENDRBA VALUES BELOW!!! CRESTART CREATE,STARTRBA=C4C9E2C1C2D3,ENDRBA=C4C9E2C1C2D3

The IBM Datasharing course notes state....."Do not change the hex values that appear in the CRESTART control statement. They are not real RBA values". [...] 29192 254 26_Re: DB2 on OS/390 from WEB0_15_leon@CA.IBM.COM31_Fri, 14 Jul 2000 10:35:46 -0400541_us-ascii OK, here is how things are licensed: - DB2 UDB Enterprise Edition does include DB2 Connect component but the license for DB2 Connect component allows for a single user to use it at any point in time. - DB2 UDB Enterprise Edition is licensed based on the number of CPUs of the machine you installed the SERVER on. The number of machines you install DB2 CAE on has no bearing on the license charges. In other words, DB2 UDB EE is priced based on database server capacity and not the number of people that will be using it. - DB2 [...] 29447 26 32_Reorging partitioned tablespaces12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM31_Fri, 14 Jul 2000 10:36:49 -0400514_iso-8859-1 Our shop has 10 table spaces that are partitioned. The largest of which is 180 partitions. I would like to reorg a group of partitions in parallel (ie. Job 1 reorgs partitions 1 thru 60, job 2 reorgs 61 thru 120, job 3 reorgs 121 thru 180, all running concurrently). Is there any way to defer index processing short of dropping the indexes? It would be nice to update (recover?) all indexes with one pass of the table after all reorgs are complete. Does anyone have a technique to accomplish this? [...] 29474 40 36_Re: Reorging partitioned tablespaces10_Pete Coles28_peter.coles@SOMERFIELD.CO.UK31_Fri, 14 Jul 2000 16:43:10 +0100647_iso-8859-1 you'll have to drop em

-----Original Message----- From: Andy Wheeler [mailto:Andy.Wheeler@GRIZZARD.COM] Sent: Friday, July 14, 2000 2:37 PM To: DB2-L@RYCI.COM Subject: Reorging partitioned tablespaces



Our shop has 10 table spaces that are partitioned. The largest of which is 180 partitions. I would like to reorg a group of partitions in parallel (ie. Job 1 reorgs partitions 1 thru 60, job 2 reorgs 61 thru 120, job 3 reorgs 121 thru 180, all running concurrently). Is there any way to defer index processing short of dropping the indexes? It would be nice to update (recover?) all indexes with one pass of [...] 29515 325 26_Re: How big's the VARCHAR?4_IBM119_IBM1@EGEBANK.COM.TR31_Fri, 14 Jul 2000 18:10:31 +0300453_ISO-8859-9 sorry martin,

signing the length did not help the precompiler error or the retrieving of the whole 3,500 bytes in the host variable?

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@egebank.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 29841 280 26_Re: DB2 on OS/390 from WEB22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Fri, 14 Jul 2000 11:10:23 -0400634_iso-8859-1 Leon - Thanks very much...



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: leon@CA.IBM.COM[SMTP:leon@CA.IBM.COM] > Sent: Friday, July 14, 2000 10:35 AM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] DB2 on OS/390 from WEB > > OK, here is how things are licensed: > - DB2 UDB Enterprise Edition does include DB2 Connect component but the > license for DB2 Connect component allows for a single user to use it at > any > point in time. > - DB2 UDB Enterprise Edition is licensed based on the number of CPUs of > the > machine you installed the [...] 30122 202 42_Fw: Re: 2ndary authids/DSN3@ATH/Top Secret12_Scott Bowden16_shbowden@GFB.ORG31_Fri, 14 Jul 2000 11:14:15 -0400406_iso-8859-1 Robert & Mike - thanks for the clarification. The fog begins to lift. The manuals sure make it sound a lot more automatic than it seems to be. Let me see if I have this now - Even with 2ndary id's in place we will have to do a lot of 'set current sqlid' stuff - right? And the fact that one of the secondary id's has sysadm is the reason that xxxx2 can issue a set current sqlid to ANY id? [...] 30325 355 26_Re: How big's the VARCHAR?22_Lindsay, William (BIS)24_WLindsay@EXCHANGE.ML.COM31_Fri, 14 Jul 2000 11:18:40 -0400376_iso-8859-1 Just a guess but would "PIC X occurs 1 to 3500 depending on WS-PARM-RESULTS-LEN" come into play here ?

I use to use COBOL with ORACLE and VARCHARS on SUN SOLARIS and from my memory this comes to mind.

Good Luck

Bill Lindsay Benefits and Investment Solutions 401(k) Distribution 732 764-3464 (voice) 732 764-5902 (fax) 888 MERRIL-0 (pager) [...] 30681 362 26_Re: How big's the VARCHAR?4_IBM119_IBM1@EGEBANK.COM.TR31_Fri, 14 Jul 2000 18:47:00 +0300429_ISO-8859-9 just to get things even straighter, which version of cobol are you using? LE applicable if I do not remember wrong?

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@egebank.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 31044 25 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Fri, 14 Jul 2000 11:49:19 -0400539_us-ascii Well, I've finally made it! What was the problem, well it was probably a combination of things.

At the heart of the problem, DB2 insisted that a full and properly defined COBOL variable is used as input and/or output for the varchar. The elementry variables must be level 49 and the length must be signed (has anyone ever had a negative length varchar?!). Only then can the call statement use the 01 group name. If any other character variable is used, DB2 will convert the output to a CHAR and truncate to 255 bytes. [...] 31070 58 26_Re: How big's the VARCHAR?15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 14 Jul 2000 17:56:21 +0100542_us-ascii Martin,

You're welcome. Glad it worked... I was getting desparate.....

Regards,





MARTIN WOLFF @RYCI.COM> on 07/14/2000 04:49:19 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: How big's the VARCHAR?





Well, I've finally made it! What was the problem, well it was probably a combination of things. [...] 31129 27 33_Repair level ID trivial question.14_Massimo Scarpa23_Massimo_Scarpa@DEBIS.IT31_Fri, 14 Jul 2000 18:17:05 +0100394_us-ascii Here someone says that a REPAIR LEVELID executed after a recover using a volume dump can 'delete' the

RBRBA so you cannot do a RECOVER LOGONLY, even after a RECOVER with DSN1COPY.

That is if you recover a tablespace with DSN1COPY (using an older image copy) + RECOVER/REBUILD indexes +

REPAIR LEVELID you cannot use RECOVER LOGONLY (to the current logpoint). [...] 31157 85 26_Re: How big's the VARCHAR?11_Culp, Kathy18_kculp@ALLSTATE.COM31_Fri, 14 Jul 2000 11:10:47 -0500525_iso-8859-1 Martin,

I've been following along. What was the final coding resolution? I saw too many variations.

Thanks, Kathy

-----Original Message----- From: Toine Michielse [SMTP:vndobtm@US.IBM.COM] Sent: Friday, July 14, 2000 11:56 AM To: DB2-L@RYCI.COM Subject: Re: How big's the VARCHAR?

Martin,

You're welcome. Glad it worked... I was getting desparate.....

Regards,





MARTIN WOLFF @RYCI.COM> on 07/14/2000 04:49:19 PM [...] 31243 162 23_Re: Disable Datasharing15_Murley, Michael22_Michael_Murley@BMC.COM31_Fri, 14 Jul 2000 11:17:44 -0500449_iso-8859-1 Andy,

For a clue to the magical internal meaning of the STARTRBA and ENDRBA values, try doing EBCDIC translation on them.

Michael

-----Original Message----- From: Andy Hunt [mailto:Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK] Sent: Friday, July 14, 2000 9:28 AM To: DB2-L@RYCI.COM Subject: Re: Disable Datasharing



Ken/Les, The product is BMC Logmaster V2.1.00 (I have raised problem 360069 with uksupport). [...] 31406 133 23_Re: Disable Datasharing13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Fri, 14 Jul 2000 11:45:12 -0500418_iso-8859-1 For the record... There is a Log Master zap available to allow for scanning logs after datasharing disablement.

I am curious to know if anyone has disabled, re-enabled, and then disabled again.

Ken BMC Software

-----Original Message----- From: Murley, Michael [mailto:Michael_Murley@bmc.com] Sent: Friday, July 14, 2000 11:18 AM To: DB2-L@RYCI.COM Subject: Re: Disable Datasharing [...] 31540 38 56_Re: Using a gateway to trigger insert to DB2 from Oracle12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 14 Jul 2000 12:18:00 -0400457_- We are using Oracle Procedural Gateway to do reads from DB2, But I believe that it can be used to insert data since it just calls a CICS program to access DB2. Two-phase commits may be an issue for you.

>>> DB2-L@RYCI.COM@inter2 07/13/00 07:15PM >>> I am looking for feedback, comments, etc. on: Is any one out there using some sort of gateway or datajoiner solution to trigger an insert into DB2 as a result of an insert or update in Oracle? [...] 31579 26 18_Re: Top Secret/DB212_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 14 Jul 2000 12:21:00 -0400543_- We attempted to implement it and our security group found it didn't work they wanted it to, sorry I don't have any details. There was also a problem with SOC6s when DB2 came up which I believe has been resolved.

>>> DB2-L@RYCI.COM@inter2 07/13/00 10:13PM >>> We are planning for the implementation of Top Secret/DB2. Does anyone have any experience with it? Things to look out for? I'd also like to know if you have any recommendations for any DB2 training for our Security Administrators, which is a separate group at our shop. [...] 31606 17 16_SEQ. CACHE ZPARM11_Robert Jans21_Jans.Robert@AMSTR.COM31_Fri, 14 Jul 2000 10:56:00 -0600428_- I have an odd question. We currently have have SEQCACH zparm set to BYPASS. The Storage Management with DB2 for OS/390 suggests this parm should (for us anyhow) be set to SEQ. It also indicates that most devices with large amounts of cache on them ignore this value anyway. My question is: Should we bother to set the parm to SEQ to at least have consistency in what the zparms show us? What do other sites do with this? [...] 31624 51 37_Re: Repair level ID trivial question.20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 14 Jul 2000 13:32:17 -0400392_us-ascii REPAIR LEVELID resets the header RBA to X'000000000000'. If the tablespace was never updated after REPAIR LEVELID then the header RBA will remain unchanged.

Using RECOVER LOGONLY will not work on such tablespace because the right RBA information is not available on the VSAM dataset. DB2 needs all the logs since RBA x'000000000000' till the current point and will fail. [...] 31676 59 18_Re: Top Secret/DB212_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 14 Jul 2000 10:41:25 -0700576_us-ascii My client has been using Top Secret and DB2 for some time with relatively minor problems.

We had to do some special stuff for Release 6 stored procedures and some different stuff for R5 DDF. Can provide details if needed.

They are not using secondary authids however due to other issues. --- BOB JEANDRON wrote: > We attempted to implement it and our security group > found it didn't work they > wanted it to, sorry I don't have any details. There > was also a problem with > SOC6s when DB2 came up which I believe has been [...] 31736 66 37_Re: Repair level ID trivial question.15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 14 Jul 2000 19:45:13 +0100474_us-ascii Hello Max,

I have never tried it but... If you REPAIR the levelid, you update the header page. Since this update is logged. The LOGONLY recover will start applying log records that were generated AFTER the REPAIR levelid.

So if you want to apply the log that starts at the point to where you recovered the space, you should not run REPAIR LEVELID. I think the RECOVER LOGONLY will bypass the levelid check. So you won't get the levelid mismatch. [...] 31803 58 63_Stored Proceedure Error SQL4032N from a VisualAge for Java App.15_Joel Zigelstein20_jzigel1@HALLMARK.COM31_Fri, 14 Jul 2000 12:53:51 -0500354_- Hello everyone,

Firstly we are "NEW" at VisualAge for JAVA and DB2 UDB on NT. The application that calls this stored procedure was not written by us. Under ceratyin circumstances we are getting an SQL4032N error - indicating bad data - we cannot however trace or discover what is wrong! This is taken from the DB2DIAG.log for the instance: [...] 31862 59 26_Re: How big's the VARCHAR?12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Fri, 14 Jul 2000 14:53:51 -0400443_us-ascii Yes, there were alot of variations! In the calling program, the storage was:-

01 WS-PARM-RESULTS. 49 WS-PARM-RESULTS-LEN PIC S9(4) BINARY. 49 WS-PARM-RESULTS-DAT PIC X(3500).

The levels must be 01,49&49 and the length field must be signed. The SP call was:-

EXEC SQL CALL D201B03 ( :WS-PARM-RESULTS :WS-PARM-RESULTS-IND , :WS-PARM-SQLCA :WS-PARM-SQLCA-IND , :WS-PARM-ERROR-DESC :WS-PARM-ERROR-IND ) END-EXEC [...] 31922 110 12_Re: 00C9008413_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 14 Jul 2000 14:13:31 -0500429_iso-8859-1 Pete,

You might try having your automation software flag the 00c90084 (and 00c900a5 while you are at it) and issue a -DIS DATABASE....USE command to see the mix at the time.

Dave -----Original Message----- From: Pete Coles [mailto:peter.coles@SOMERFIELD.CO.UK] Sent: Thursday, July 13, 2000 10:33 AM To: DB2-L@RYCI.COM Subject: Re: 00C90084



ta, i think it was qmf that was the issue. [...] 32033 20 7_Columns33_Luiz Cesar Vinhaes Da Costa Junor25_vinhaes@SASSECAIXA.COM.BR31_Fri, 14 Jul 2000 18:13:54 -0300444_us-ascii Hi all, hopefully this is a quick one.

I would like to know the meaning of the column minpdlength, I did not find it in the manuals. I am using UDB 5.02



Thanks, Luiz Cesar

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 32054 56 14_En: easytrieve19_Kleber Faria Vianna14_kfv@UOL.COM.BR31_Thu, 13 Jul 2000 19:21:20 -0300146_iso-8859-1





Where can I download CA-easytrieve programming manuals from?

Thanks for the reply.

Kleber

32111 35 14_Re: easytrieve19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Fri, 14 Jul 2000 14:34:49 -0700524_- You may find out more from CA looking at their web site. From what I am aware of the documentation is a cost item and not free download.

Try the following link: http://www.ca.com/documentation/doc.htm#mvs

HTH Tim Ohling

> -----Original Message----- > From: Kleber Faria Vianna [SMTP:kfv@UOL.COM.BR] > Sent: Thursday, July 13, 2000 3:21 PM > To: DB2-L@RYCI.COM > Subject: En: easytrieve > > > > > Where can I download CA-easytrieve programming manuals from? > > Thanks for the reply. > > Kleber > [...] 32147 248 30_Winter Corporation VLDB Survey13_Martin Spratt18_sprattm@US.IBM.COM31_Fri, 14 Jul 2000 17:32:53 -0400497_iso-8859-1 Dear DB2 Interested Parties,

In reference to the bulletin's below: Due to requests for more time to fill out surveys, Winter Corporation has pushed out the deadline for accepting surveys until August 31st. See below for information about this program and how to participate, thank you.

Regards, Mike Software is the soul of e-business Internet: dbswift@us.ibm.com IBM Silicon Valley Lab - 555 Bailey Avenue - San Jose, CA 95141 Phone: 408 463-4105, Fax: 408 463-4633 [...] 32396 191 122_Repost: Winter Corp. Survey Deadline Extended to 8/31/00) (was: W inter Corp Announces: Database Scalability Program 2000)13_Morrill, John12_JohnM@VP.NET31_Fri, 14 Jul 2000 16:20:16 -0600497_iso-8859-1 Dear DB2 Interested Parties,

In reference to the bulletin's below: Due to requests for more time to fill out surveys, Winter Corporation has pushed out the deadline for accepting surveys until August 31st. See below for information about this program and how to participate, thank you.

Regards, Mike Software is the soul of e-business Internet: dbswift@us.ibm.com IBM Silicon Valley Lab - 555 Bailey Avenue - San Jose, CA 95141 Phone: 408 463-4105, Fax: 408 463-4633 [...]