1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2001, week 2 2 204 49_Re: Can't get rid of "Prefetch Queue Full" error!11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU30_Mon, 8 Jan 2001 17:06:23 +1100340_- Scott,

while I agree in theory with your first statement, in practive we found that by increasing NUM_IOSERVERS dramatically, not only did we get rid of thousands of those "Prefetch Queue Full" messages in the db2diag.log, we also noticed an improvement in response times, and we no longer had DB2 freezing on us (V6.1, fp4). [...] 207 82 15_Re: help please14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE30_Mon, 8 Jan 2001 08:46:22 +0100830_us-ascii Hi Larry,

try .....



BIND PLAN(DSNESPRR) OWNER(DBPRSYA1) PKLIST( DSNESPRR.DSNESM68 DEHUKDBWA.DSNESPRR.DSNESM68 DEHUKDBTS.DSNESPRR.DSNESM68) ACTION(REPLACE) RETAIN VALIDATE(RUN) ISOLATION(CS) FLAG(I) DYNAMICRULES(RUN) ACQUIRE(USE) RELEASE(COMMIT) EXPLAIN(NO) CURRENTDATA(YES) QUALIFIER(DBPRSYA1) CACHESIZE(1024) DEGREE(1) SQLRULES(DB2) DISCONNECT(EXPLICIT)

Regards Ruediger



elibs schrieb:

> Hello, > Since i am new to DD2,could you please kindly enough tell me how to bind dsnesprr? > Thank you in advance > Larry > > ----- Original Message ----- > From: Gerrit Groenendaal > Newsgroups: bit.listserv.db2-l > To: > Sent: Saturday, January 06, 2001 3:14 AM > Subject: Re: help please > > > HI, > > > > In our shop we bind the [...] 290 44 35_Archive DB2 system catalog (OS/390)14_Tibor Zubonyai19_zubonyait@YAHOO.COM30_Mon, 8 Jan 2001 00:31:00 -0800396_us-ascii

Hello dear DB2 fun,

we installed a DB2 system 3 months ago (OS/390) and I should backup the system catalog with a full image copy. Please send me how to create the backup job (controll card and JCL).

Thank you for your answer.

Tibor Zubonyai from Hungary













--------------------------------- Do You Yahoo!? 335 104 28_Re: How to clean-up DB2 Log?11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Mon, 8 Jan 2001 16:07:03 -0800433_us-ascii Dear Bill,

I am in the same team with Yosia. Thank you for your information. But we cannot find the db2uext2 command on our DB2, we're using DB2 5.2 on NT. We've tried to delete the contents of SQLOGDIR, and the database works fine now, but we still don't know whether there are "danger zones" that we haven't encountered or we should be aware of. FYI, on our db paramater configuration, Logretain=1 Userexit=0. [...] 440 27 37_Re: Creating a Copy of an Archive Log10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 8 Jan 2001 10:28:15 +0100294_us-ascii I agree with Mike Turner.

In general it's better to send to DR site a copy of archive log made offline with some utility , NOT the real archive log

tape. Of course it depends on 'recovery rate' at your site, but it's not so rare to 'burn' a tape cartridge with some [...] 468 100 49_Re: Can't get rid of "Prefetch Queue Full" error!14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Mon, 8 Jan 2001 03:50:11 -0600352_iso-8859-1 5 cents worth from a mainframe perspective.

DB2 for OS/390 has allowed 300 I-O engines in V4 and V5. An I-O engine can be used for any outstanding read, prefetch, write task. Due to this causing measurable constraint on I-O throughput, V6 allows 600 I-O engines. Large sites are hoping this number will go up in future releases. [...] 569 121 40_Re: Read-only table design advice wanted14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Mon, 8 Jan 2001 04:07:28 -0600597_iso-8859-1 My suggestions are along the lines of: keep it simple.

I would be very reluctant to have more than one table in a tablespace, unless forced by external reasons (e.g. buying a DBA-friendly ERP package). This is because table and tablespace maintenance is (I find) easier and less error-prone. If the production table/tablespace setup is mirrored in development, as it should be!, then multiple tablespaces in a tablespace is an accident waiting to happen, often soon after new staff are hired. Even production DBAs have been known to get their LOAD control statements wrong. [...] 691 47 53_Re: How to improve the operation of insert and update14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Mon, 8 Jan 2001 04:10:45 -0600370_gb2312 What version of DB2, what platform, and what version of operating system are you using?

As general guidance for most platforms, check that your space parameters for incremental growth are not too small, and check that your bufferpools are large enough. Anything more specific needs answers to the above questions.



From Edward Vassie... [...] 739 75 45_Re: IBM DB2 Administration Tool for OS/390 V215_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Mon, 8 Jan 2001 11:22:26 +0100320_iso-8859-1 Ken,

BMC Changemanager can do all this for you. We use it to migrate production environments and scale it down as well. We change names of the database objects. This can be done automatically on a scheduled regular basis as well as incidental. It helps us as to do changes in different databases. [...] 815 56 15_Re: Any site???14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Mon, 8 Jan 2001 04:27:03 -0600479_iso-8859-1 I wrote something like this in the past, but eventually it got replaced by a formal product.

The automation of reorgs is good, but is best done using a supported product from an established vendor. This allows the DBA to concentrate on database management, and not on fixing application code. Most vendor products will drive the reorganisation process harder than code you can develop, and provide you with an updated product to support a new DB2 release. [...] 872 21 25_Re: FW: BPXBATCH and JAVA10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 8 Jan 2001 12:24:51 +0100479_us-ascii Hi Shaun, take a look at redbook:

SG245619 Java programming guide for OS/390 (chapter 22).

I'll test (I hope; we are just now installing JDBC/SQLJ support in a test DB2) the jcl asap...

HTH

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 894 47 53_Re: How to improve the operation of insert and update10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Mon, 8 Jan 2001 05:45:50 -0600435_- Does your 'insert' process simply consist of 200,000 iterations of a random SQL Insert? If so, then why do you think that adding an extra index and partitioning your table will help? (I say an extra index as I assume that your 40 million row was already indexed!?).

If your process does consist simply of a series of random Inserts, then inserting the rows in clustering sequence often dramatically improves performance. [...] 942 35 53_Re: How to improve the operation of insert and update13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM30_Mon, 8 Jan 2001 07:19:02 -0500558_us-ascii Is your input dataset sorted in the order that the table is sequenced in??? If your table is clustered, your input dataset should be sorted in the clustering sequence. This will help!

> On Fri, 5 Jan 2001 08:50:45 -0500, elibs wrote: > >Hello,All: > > I have a serious problem to the performance of my large table which > has 40M rows and continues been enlarged. > > The case is that, I have a routine batch job devoloped by AP to insert > 200K rows into this table daily.I am very frustrated that this job > always [...] 978 31 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Mon, 8 Jan 2001 08:59:03 -0500518_us-ascii The Middleware here is manage by the DBA Staff. What we have done is the Workstation image that gets deployed by the PC support group contains the CAE + Fixpack. They do minimal support based of some documentation we (DBA's ) have provided them, after that the problems get routed to the DBA team. DB2 on OS/390 is were most of our applications live, so it is a natural progression to have the Distributed access and ODBC/JDBC support on our team. We also have PeopleSoft/Tuxedo is also part of the middleware 1010 48 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 8 Jan 2001 06:03:35 -0800567_us-ascii My current client has the DBA group support all of these. The help desk distributes the software and installs it on users PCs. But all server installs are done by the DBA group. And all problem resolution. --- BILL_GALLAGHER@PHL.COM wrote: > Hi, > > I'd like to take a very short survey about which > area in your shop supports > middleware products such as DB2 Connect, ODBC/MDAC, > JDBC, etc. It's been > an on-going issue here in my shop about where > support for these products > should belong. Right now, nobody has assumed > responsibility, so we [...] 1059 59 30_Re: DB2 Connect Userid Problem11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Mon, 8 Jan 2001 09:07:07 -0500371_us-ascii Hello Michael:

In looking at your e-mail the 446 port number is the DB2 Connect default port number when adding a new database/alias. Unless you are using the same port # on the MVS side ???

Protocol TCP/IP Target Operating System OS/390 Hostname XXXXXX.XXX.XXX.XX (our mainframe address) Port 446 Location Name MVS2DB2 Database Alias PEA0NPA [...] 1119 50 25_Re: Online Reorg with NPI16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 8 Jan 2001 09:12:01 -0500360_iso-8859-1 Bill, If you have NPI and do partition level reorg, Online Reorg will have a BUILD2 phase for the NPIs. During this BUILD2 phase you will no have application access to the table. If there are no NPI, partition-level Online Reorg is fine. The BUILD2 phase is *way* longer than the usual Drain, Last Log Apply, SWITCH sequence of unavailability. [...] 1170 31 53_DB2 V5 OS/390 - CPU consumption of DB2 address spaces11_Vijay Kumar25_vijay_kumar90@HOTMAIL.COM30_Mon, 8 Jan 2001 09:08:42 -0500411_- Greetings,

Could anyone let me know whether the Class 2 CPU accounted for the application is included in the CPU consumed by the address spaces (System Services, Database Services, DDF, Stored Proc. addr. spaces)?

I was looking at the CPU consumed by the address spaces (TCB+SRB) and found that the total CPU of the address spaces is about 60% more than the Class 2 CPU of the application. [...] 1202 127 40_Re: Read-only table design advice wanted16_Michael McMorrow25_michael.g.mcmorrow@AIB.IE30_Mon, 8 Jan 2001 14:20:47 +0000538_ISO-8859-1 Hi Raymond, Would you consider defining 2 partitions with a toggle strategy dynamically deeming one to be 'active' and one to be 'inactive'. Updates would be applied to the 'inactive' partition and, at a convenient time of your choice, this partition would be flipped over to 'active' by updating a control table. Application access would be via a view which referenced this control table for the key value of the currently active partition. Not a million miles away from the IMS approach to acblibs/mfslibs etc. Michael. [...] 1330 14 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?10_Tim Connor22_connor@OIR.STATE.SC.US30_Mon, 8 Jan 2001 09:17:10 -0500380_US-ASCII Our DB2 Connect support is done by a special group not DBA or PC support staff. This group handles web applications on NT and UNIX.

===============================================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. 1345 27 32_Re: how to empty table more fast11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Mon, 8 Jan 2001 09:33:30 -050091_us-ascii Hello:

You can also run a LOAD utility using load REPLACE, and use an empty 1373 142 40_Re: Read-only table design advice wanted13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Mon, 8 Jan 2001 08:37:56 -0600707_iso-8859-1 If this concept is an option, consider doing the switching via a table RENAME.



-----Original Message----- From: Michael McMorrow [mailto:michael.g.mcmorrow@AIB.IE] Sent: Monday, January 08, 2001 8:21 AM To: DB2-L@RYCI.COM Subject: Re: Read-only table design advice wanted



Hi Raymond, Would you consider defining 2 partitions with a toggle strategy dynamically deeming one to be 'active' and one to be 'inactive'. Updates would be applied to the 'inactive' partition and, at a convenient time of your choice, this partition would be flipped over to 'active' by updating a control table. Application access would be via a view which referenced this control table [...] 1516 47 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 8 Jan 2001 09:32:15 -0500428_iso-8859-1 In our shop all the client COMMS software lives in the PC/LAN group and all the non-MVS client software lives in the Client/Server group.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: BILL_GALLAGHER@PHL.COM [mailto:BILL_GALLAGHER@PHL.COM] Sent: Friday, January 05, 2001 2:06 PM To: DB2-L@RYCI.COM Subject: Who Supports DB2 Connect, ODBC, JDBC, etc.? [...] 1564 145 28_Re: How to clean-up DB2 Log?0_22_BILL_GALLAGHER@PHL.COM30_Mon, 8 Jan 2001 09:42:34 -0500404_us-ascii Endy,

db2uext2 is not a command to be executed . . . it is the name of the user exit that is invoked when you have the "userexit=1" db cfg parm set. There is documentation of setting up this exit in Appendix G of the "Administration Guide: Design and Implementation".

Information on using the exit under NT is lacking in the guide, so I'm not sure what your options would be. [...] 1710 14 32_Re: how to empty table more fast0_26_truman.g.brown@VERIZON.COM30_Mon, 8 Jan 2001 09:49:10 -0500325_us-ascii Dummy load the table - LOAD REPLACE with no input dataset...

George

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 1725 86 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Mon, 8 Jan 2001 08:49:14 -0600647_iso-8859-1 This is the same method we use, and for the same reasons.

-----Original Message----- From: Philip Gunning [mailto:Philip.Gunning@QUEST.COM] Sent: Friday, January 05, 2001 5:02 PM To: DB2-L@RYCI.COM Subject: Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?





We (DBAs) installed and tested new releases and then provided the doc to the pc people to mass distribute the CAE or Runtime client or fixpack. We tested all fixpacks with all applications on a DBA test server then rolled out to test and dev. The problem that occurs when just the pc people support DB2 Connect is that they are not familiar with [...] 1812 117 53_Re: How to improve the operation of insert and update16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 8 Jan 2001 09:43:38 -0500361_gb2312 Larry, Check the response time on your active logs. Insert and update are synchronous on the active logs. Poor log response can really wreck insert/update times. Also, see if much of your time is in lock wait. If so, add COMMITs to everything which uses the table.







regards,

eric pearson NS ITO Database Support [...] 1930 65 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?12_Martin, Paul22_Paul.Martin@ECOLAB.COM30_Mon, 8 Jan 2001 08:55:44 -0600591_iso-8859-1 DB2 Connect, UDB PE, Work Group and EE installs on all platforms are done by the DB2 systems personnel in the DBA group. The DB2 systems personnel are also responsible for creating the response files for the Runtime silent installs

When new workstations are deployed by the PC Services Group they are deployed without any UDB functions. If the user requests UDB functions the PC Services Group do a silent install of Version 7 Runtime client code. PC Services also makes the DBA recommended updates to the ODBC.INI, ODBCINST.INI & DB2CLI.INI files after the installs. [...] 1996 175 28_Re: How to clean-up DB2 Log?14_Scott Saunders20_ssaunders@SIEBEL.COM30_Mon, 8 Jan 2001 06:56:46 -0800475_iso-8859-1 Bill is correct. the db2uext2 is a C program (at least in Unix) usually found in the DB2 install directory (the location may be release dependent). The instructions for customizing the C program are included as comments within the code. There are usually three versions, one to copy the logs to disk, one to tape and one to ADSM. The instructions for the disk version, the only one I have used, are straight-forward. You will need a C compiler on the server. [...] 2172 148 40_Re: Read-only table design advice wanted10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Mon, 8 Jan 2001 09:04:58 -0600687_- Please correct me if I'm wrong but..

Raymond's requirement is to save DASD, and I think that you are advocating a strategy based upon data redundancy. Am I missing something?



>-----Original Message----- >From: Michael McMorrow [mailto:michael.g.mcmorrow@AIB.IE] >Sent: Monday, January 08, 2001 8:21 AM >To: DB2-L@RYCI.COM >Subject: Re: Read-only table design advice wanted > > >Hi Raymond, >Would you consider defining 2 partitions with a toggle strategy dynamically >deeming one to be 'active' and one to be 'inactive'. Updates would be >applied to the 'inactive' partition and, at a convenient time of your >choice, this partition would be flipped over to [...] 2321 26 32_Re: how to empty table more fast13_Aratos Sicyon25_aratos_sicyon@HOTMAIL.COM30_Mon, 8 Jan 2001 17:12:25 +0200532_- Please could everybody to try and pay attention. Now we all know, thanks to many many contributions, that you can empty a tablespace by loading empty file. Please nobody tell us this again. Thank you!

Dummy load the table - LOAD REPLACE with no input dataset...

George

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 2348 22 32_Re: how to empty table more fast36_Enarson, Mike (CC-Database Services)29_Mike.Enarson@CONAGRAFOODS.COM30_Mon, 8 Jan 2001 09:17:23 -0600332_iso-8859-1 Hi Larry. When I want a fast way to delete all of the rows in a table and it is not partitioned and has only 1 table in it then I make the tablespace segmented and just do a DELETE FROM owner.tablename.

This marks the tablespace as empty and takes only a few seconds no matter how many rows are in the table. [...] 2371 91 32_Re: how to empty table more fast11_Riyaz Momin15_RMomin@FHSC.COM30_Mon, 8 Jan 2001 10:23:42 -0500514_US-ASCII Hi,

Use REPLACE option on a LOAD utility after DUMMYing SYSREC file. This will erase entire TableSpace, so be careful. This works faster than DELETE FROM as this process doesn't create log records.

Riyaz. FHSC, Richmond.

>>> Mike.Enarson@CONAGRAFOODS.COM 01/08/01 10:17AM >>> Hi Larry. When I want a fast way to delete all of the rows in a table and it is not partitioned and has only 1 table in it then I make the tablespace segmented and just do a DELETE FROM owner.tablename. [...] 2463 56 32_Re: how to empty table more fast10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Mon, 8 Jan 2001 09:40:28 -0600549_- Larry,

Now that we're all agreed that the Dummy Load Replace is a good thing, assuming DB2 for OS/390 (which is what I infer from your recent series of questions) : What kind of Tablespace is your table on?

Segmented T/S: Your Mass Delete involves setting a flag on the Spacemap. This will not take 10 minutes! One big exception I can see: If you have an RI Relationship from another table dependant on columns in your table, then I am sure that this must result in the rows being deleted one at a time, even in a segmented T/S [...] 2520 91 115_=?gb2312?B?u9i4tDogICAgICBSZTogSG93IHRvIGltcHJvdmUgdGhlIG9wZXJhdA==?= =?gb2312?B?aW9uIG9mIGluc2VydCBhbmQgdXBkYXRl?=5_elibs17_elibs@NETEASE.COM30_Sun, 7 Jan 2001 11:06:29 -0500611_gb2312 Hi,John:

I am sorry that I leave out some information about my Q.In fact ,the table is for insert and read.one batch program insert about 200K rows into it every night,and one CICS programe randomly read the table every day.So I have to consider both the situations.Adding index and partition can see the positive result of CICS programe,but I have no idea of how to improve the batch program performance except for enlarging the bufferpools, assigning different BP and DASD to the index and table , assign enough cyls to the tablespace and routinely reorging the tablespace, and sorting the [...] 2612 153 62_Re: ??: Re: How to improve the operation of insert and updat e16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 8 Jan 2001 11:05:04 -0500521_gb2312 Unless you have *lots* (>10?) of duplicate key situations, you would probably improve things by *not* checking for duplicates on every insert but just handling the cases where the insert fails due to uniqueness constraints.

Also, consider adjusting PCTFREE so that you minimize page splits.

Do you have some sort of monitor which tells you how the time on the inserts is spent? How much time in I/O, how much CPU, how much lock wait, etc? This info may give you hints about what to tweak first. [...] 2766 81 73_=?gb2312?B?u9i4tDogICAgICBSZTogaG93IHRvIGVtcHR5IHRhYmxlIG1vcmUgZmFzdA==?=5_elibs17_elibs@NETEASE.COM30_Sun, 7 Jan 2001 11:22:55 -0500427_gb2312 hi,John, I agree with your advice. My table is Segmented T/S with RI.since I really do not want to chage the businese batch job,I will go and discuss with AP. thank you and all others. Sincerely Larry

-----Original Message----- ·¢¼þÈË: John Hardy ÐÂÎÅ×é: bit.listserv.db2-l ÊÕ¼þÈË: DB2-L@RYCI.COM ÈÕÆÚ: 2001Äê1ÔÂ8ÈÕ 10:48 Ö÷Ìâ: Re: how to empty table more fast [...] 2848 178 62_Re: ??: Re: How to improve the operation of insert and updat e15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Mon, 8 Jan 2001 10:22:09 -0600509_us-ascii





Set freepage 5 and Pctfree 10 or 15 and try ..... ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 01/08/2001 10:18 AM ---------------------------



"Pearson, Eric L," on 01/08/2001 10:05:04 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: ??: Re: How to improve the operation of insert and updat e [...] 3027 26 42_DB2 Stored Procedure migrations (UDB UNIX)23_Scarcella, Gary V (ING)23_ScarcellaGV@ING-AFS.COM30_Mon, 8 Jan 2001 11:29:03 -0500521_iso-8859-1 I'd like to know how people are migrating DB2 Stored Procedures from their development UDB UNIX environment to their production UDB UNIX environment.

Does the qualifier of the stored procedure change when you migrate? Are the table names qualified in the SQL in the Stored Procedure? (and if they are qualified do the qualifiers change during migration?) Any migration differences if using SQL vs Java stored procedures? Do you use DB2 Stored Procedure Builder for the migration or something else? [...] 3054 47 35_Re: CA Datacom/DB to DB2 Conversion12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Jan 2001 10:39:42 -0600359_- Most of the conversions seem to have services with some software, rather than just software. There are quite a few companies involved. Conversion to DB2 is common enough that we have a service offering and a Software Migration Project Office, or SMPO. We have a wide range of facilities and services for migration. ibm.com/Solutions/softwaremigration/ [...] 3102 43 36_Trigerring an email via db2/os390 v615_Pierre Dagenais19_pdagenai@UOTTAWA.CA30_Mon, 8 Jan 2001 11:49:59 -0500382_us-ascii Hi everyone,

I would like to facilitate and automate sending an email for all our db2/os390 applications through the use of a trigger. The concept is simple .

I will create a table called EMAIL ( FROM char(200), TO char(200), BODY varchar(32000)). I will create a trigger on insert into this table that will execute a stored procedure called SEND_EMAIL. [...] 3146 148 119_Re: =?gb2312?B?u9i4tDogICAgICBSZTogSG93IHRvIGltcHJvdmUgdGhlIG9wZXJhdA==?= =?gb2312?B?aW9uIG9mIGluc2VydCBhbmQgdXBkYXRl?=10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Mon, 8 Jan 2001 10:46:42 -0600460_ISO-8859-1 Hello Larry,

No need to apologise for asking questions. That's what the list is here for. You weren't to know, and it's easy to take some things for granted when posing a question. You also might not yet know exactly what information others need to help you. You'll soon find out. In the meanwhile, it's always a good idea to start your question by stating the platform and release of DB2 you are using, e.g. 'DB2 for OS/390 Version 5'. [...] 3295 214 32_Re: how to empty table more fast10_Greg Jonas29_greg.jonas.ay8i@STATEFARM.COM30_Mon, 8 Jan 2001 10:10:03 -0600578_- If you do a mass delete on a segmented tablespace, what gets logged? Just the spacemap pages, or does each deleted row still get logged?

-----Original Message----- From: john_j_hardy@CONSULTANT.COM [mailto:john_j_hardy@CONSULTANT.COM] Sent: Monday, January 08, 2001 9:40 AM To: DB2-L@RYCI.COM Subject: Re: how to empty table more fast



Larry,

Now that we're all agreed that the Dummy Load Replace is a good thing, assuming DB2 for OS/390 (which is what I infer from your recent series of questions) : What kind of Tablespace is your table on? [...] 3510 15 30_Re: CCSID - DB2v6.1 for OS/39012_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Jan 2001 10:59:35 -0600538_- If you have not looked at the appendix in the Installation Guide for character conversion, now would be a good time. Changing from zero to the correct one is the safest of the conversions, but changing the CCSID specification can mean loss of data integrity.

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. 3526 94 32_Re: how to empty table more fast15_Murley, Michael22_Michael_Murley@BMC.COM30_Mon, 8 Jan 2001 11:14:08 -0600535_iso-8859-1 Greg,

If the table is defined with DATA CAPTURE CHANGES, every deleted row is logged. Otherwise, only the spacemap updates are logged.

Michael Murley BMC

-----Original Message----- From: Greg Jonas [mailto:greg.jonas.ay8i@STATEFARM.COM] Sent: Monday, January 08, 2001 10:10 AM To: DB2-L@RYCI.COM Subject: Re: how to empty table more fast





If you do a mass delete on a segmented tablespace, what gets logged? Just the spacemap pages, or does each deleted row still get logged? [...] 3621 95 32_Re: how to empty table more fast10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Mon, 8 Jan 2001 11:07:45 -0600349_- The manual (Admin Guide) just says:

"For segmented table spaces, the volume of log data written for mass DELETE statements is much less than for nonsegmented table spaces.".

I'd be interested to know what the UNDO/REDO records look like for a Mass Delete in a Segmented T/S (but not interested enough to find out by doing it!). [...] 3717 204 40_Re: Read-only table design advice wanted16_Michael McMorrow25_michael.g.mcmorrow@AIB.IE30_Mon, 8 Jan 2001 17:15:38 +0000675_ISO-8859-1 ....Yup, that's true but only duplication (not triplication / quadruplication / whatever multiplication he has at the moment)....M.

---------- > From: John Hardy > To: DB2-L@RYCI.COM > Subject: Re: Read-only table design advice wanted > Date: Monday, January 08, 2001 3:04 PM > > Please correct me if I'm wrong but.. > > Raymond's requirement is to save DASD, and I think that you are advocating > a strategy based upon data redundancy. Am I missing something? > > > >-----Original Message----- > >From: Michael McMorrow [mailto:michael.g.mcmorrow@AIB.IE] > >Sent: Monday, January 08, 2001 8:21 AM > >To: DB2-L@RYCI.COM > [...] 3922 17 34_Re: _how_to_empty_table_more_fast?12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Jan 2001 11:13:38 -0600361_- Both drop table and delete all of the rows (delete with no where clause) are very fast for a segmented table, but there are restrictions. You should probably make sure that you know the organization of the table space, the referential constraints, ... The Administration Guide section on designing a database has a discussion of the table space options. [...] 3940 108 18_Re: need help asap13_Davis, Ronald17_DavisRo@CONED.COM30_Mon, 8 Jan 2001 12:25:11 -0500659_iso-8859-1



Edie, has this been resolved? I would be interested in knowing what hung your job up and how you guys finally resolved it.





-----Original Message----- From: edie.richardson@SRS.GOV [mailto:edie.richardson@SRS.GOV] Sent: Thursday, January 04, 2001 9:50 AM To: DB2-L@RYCI.COM Subject: need help asap





I have a db2 load job that has been hung up now for hours so I canceled the job and terminated the utility. The utility has been terminating now for over 30 minutes which is highly unusual. Why is it taking so long to terminate, what can I do to find out why it is taking so long and [...] 4049 25 40_Re: Read-only table design advice wanted12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Jan 2001 11:31:03 -0600507_- Here are a couple of thoughts.

You'll get better flexibility and concurrency with separate tables. If the tables have many non-partitioning indexes, then then need to separate into multiple tables increases and vice-versa.

The UNION processing is more expensive, and the key question is how much of the processing will need it. We did provide a couple of improvements in V7, with the ability to put the UNION in a view and the ability to LOAD with a technique that is more like INSERT. [...] 4075 22 54_UDB 6.1 on NT Performance Parameters For Mutiple Users11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Mon, 8 Jan 2001 11:42:03 -0600338_iso-8859-1 Hi,

Can someone from the list please help me about what all parameter in DB2 to look at make a application run faster. We have a application which runs good when used by single user but with multiple users using the application it becomes really slow. The application is in Microsoft ASP and DB2 6.1 on Windows NT. [...] 4098 27 31_CA Datacom/DB to DB2 Conversion13_Martin Spratt18_sprattm@US.IBM.COM30_Mon, 8 Jan 2001 11:36:14 -0600526_us-ascii Ken,

For IBM resources and access to comprehensive 3rd party resources and capabilities, contact me off the list.

Regards,

Martin Spratt DB2 Migration Team - IBM Americas SMPO Tools, Partners, Solutions, Technical Support and Experience to Migrate to DB2 TEL: (309) 797-7312 IBM Tieline: 674-7312 FAX: (309) 797-7485 EMAIL: sprattm@us.ibm.com PAGER: 1410268@skytel.com (250 character limit) Your first step toward switching to DB2 starts here: http://www.ibm.com/software/data/db2/migration [...] 4126 31 19_CA/Platinum upgrade18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Mon, 8 Jan 2001 13:04:57 -0500448_iso-8859-1 Afternoon all; We are currently using DB2 V5 and we use CA/Platinum tools to do various tasks. Our current CA/Platinum release is P97F (okay now no laughing 'cause we're so far behind). Here's my question: We are in the process of having them (CA) send us P99D to install. Since P99D is real new has anyone had any experience installing it yet and any 'gotchas' that might be out there for this release?? Would appreciate any info. [...] 4158 48 35_Re: how to improve the performance?12_tim malamphy20_timalamphy@YAHOO.COM30_Mon, 8 Jan 2001 10:17:43 -0800574_us-ascii You may want to review how you think an index is going to improve your writes to DB2. Generally, an index will help for reading data, but SLOWS down updating and inserts. There's an extra I/O that has to be done as each index gets updated. An old batch tuning trick is to drop all the indexes before beginning a massive update/insert job, then rebuild/recover the index(es) after that job has completed, and before the indexes are needed by the applications doing lots of reads. Since you're only updating about 200K records, I doubt if it would be worth your [...] 4207 62 58_Re: UDB 6.1 on NT Performance Parameters For Mutiple Users11_Paul Murphy22_Paul_Murphy@MAY-CO.COM30_Mon, 8 Jan 2001 12:08:25 -0600534_us-ascii The 'silver bullet' you're looking for is to increase the size of the bufferpool. It can be changed from the control center. Select the System, Instance, then Database you desire, then find the Bufferpool setting at the bottom of the list. The default is 250 4kb pages (1MB)! If you have many users, and can take the hit on physical memory, run it up to 2000. Once again, it all depends on everything else that's going on in your server. You don't want to raise it so high to cause your system to perform active paging. [...] 4270 48 23_Re: CA/Platinum upgrade9_Rob Crane22_racrane@CONCENTRIC.NET30_Mon, 8 Jan 2001 11:30:44 -0700319_us-ascii The SDM install is slightly different from the P97 tape. If you have questions about setting up the install and suffix processing send me a note and we can discuss. Also knew with the P99D tape is the use of LMP for licensing, etc. Make sure you have your LMP codes before you activate the new P99D code. [...] 4319 51 23_FW: CA/Platinum upgrade13_Gleason, Mike22_MGleason@UILLINOIS.EDU30_Mon, 8 Jan 2001 12:31:21 -0600504_iso-8859-1 Won't laugh because we also are at P97F in production. We have P97H and P99C in our DB2 V6 test subsystem. We have run into a couple of 'gotchas'. If you use INFOTRANSPORT/INFOREFINER, they are not included in P99C(and therefore probably not in P99D), which is why we have P97h installed. The biggest one, tho, is smpe install is now required. Don't get me wrong, I like SMPE, but the resources required to install our subset of tools is immense compared to the old IEBCOPY installation. [...] 4371 129 59_Re: UDB 6.1 on NT Performance Parameters For Mutiple User s14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Mon, 8 Jan 2001 10:44:38 -0800374_iso-8859-1 Amit, Is the application a heavy insert/update/replace app? If so, you may want to review your logging parms, such as log buffer size and the mincommit parm. Also, ensure that the active log files are not on the same device as your tablespaces. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 4501 32 35_Re: CA Datacom/DB to DB2 Conversion15_Wayne Arrington33_WAYNE.ARRINGTON@CUSTOMS.TREAS.GOV30_Mon, 8 Jan 2001 13:49:55 -0500563_US-ASCII Please call me at 703-921-6528 between the hours of 0600 to 1200 e.s.t. to discuss this subject....we are in the process of going this.

Wayne Arrington



______________________________ Reply Separator _________________________________ Subject: CA Datacom/DB to DB2 Conversion Author: "DB2 Data Base Discussion List" at smtplink Date: 1/5/01 11:19 AM



Is anybody aware of any software to help such a coversion? The programs accessing Datacom/DB are written in Cobol, CA Ideal, and CA Ideal for DB2. [...] 4534 39 34_Re: Automated DB2 Restart Question11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Mon, 8 Jan 2001 14:30:04 -0500443_iso-8859-1 Luke,

You should look into a feature of OS/390 called "AUTOMATIC RESTART MANAGER". There is some information in The DB2 Data Sharing documentation, and more in:

OS/390 MVS Programming: Sysplex Services Guide Document Number GC28-1771



-----Original Message----- From: McGee, Luke (ECS) [mailto:LuMcGee@EXCHANGE.ML.COM] Sent: Friday, January 05, 2001 10:42 AM Subject: Automated DB2 Restart Question [...] 4574 32 32_New tablespaces SYSSEQ & SYSSEQ216_Sharon Zitterman24_Sharon.Zitterman@TRW.COM30_Mon, 8 Jan 2001 11:25:51 -0800318_US-ASCII Hi all,

As a result of migration to DB2 V6 and IBM APAR PQ30652 / PTF UQ38405, we now have 2 additional tablespaces in our DB2 catalog.

We are currently using BMC copy for our catalog copy and added an additional copy statement (ahead of the DB2CATALOG wildcard) for the 2 new tablespaces. [...] 4607 32 58_Re: UDB 6.1 on NT Performance Parameters For Mutiple Users11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Mon, 8 Jan 2001 14:40:28 -0500445_us-ascii Hello Sinha:

I attended a presentation at the Central Ohio DB2 User Group (http://www.codug.org/) called Turbo Charge the Engine of e-Business: DB2 UDB by Scott Hayes of the Database-GUYS Inc. His presentation touched over a lot PARMS that can help DB2 UDB on the (non-OS/390 platform) to perform better. Perhaps, you can get the presentation from CODUG or if you attended IDUG last year, it is probably on the IDUG CD. It has is 4640 21 32_New tablespaces SYSSEQ & SYSSEQ212_Isaac Yassin23_yassin@NETVISION.NET.IL30_Mon, 8 Jan 2001 21:54:34 +0200341_iso-8859-1 Hi, Those tables are for the identity columns. You should take a backup of them together with the catalog backup and incorporate them in the recover catalog process. Since the catalog itself does not use (yet) identity columns you can safly recover them last.

Isaac Yassin DBMS & IT Consultant yassin@netvision.net.il [...] 4662 22 16_AS400 talk list?14_Susan Birgeles25_Susan.Birgeles@ALLTEL.COM30_Mon, 8 Jan 2001 15:01:00 -0600344_us-ascii One of my fellow managers has asked me if there are any reporting packages written for JD Edwards. I believe he wants a basic set of reports plus the ability to customize them,

Does anyone have the address of a JD Edwards talk list or an AS400 one.?

Thanks in advance

Sue Birgeles Alltel Information Services [...] 4685 28 9_IDUG 200116_Charles Stansell19_cfstansell@JUNO.COM30_Mon, 8 Jan 2001 14:21:44 -0600560_- All,

I was looking for the date of the IDUG conference for 2001. At http://www.idug.org/ it shows the date as May 13-17, 2001. Is this correct ? This is Sunday through Thursday ? Or does someone have a Y2K problem or something ?

Charles Stansell cfstansell@juno.com http://www.geocities.com/~cicsprog

________________________________________________________________ GET INTERNET ACCESS FROM JUNO! Juno offers FREE or PREMIUM Internet access for less! Join Juno today! For your FREE software, visit: http://dl.www.juno.com/get/tagj. [...] 4714 95 23_Re: CA/Platinum upgrade14_Jack O'Connell26_jack.oconnell@MCMASTER.COM30_Mon, 8 Jan 2001 14:26:05 -0600455_iso-8859-1 Watch out when you get to P97G, you will need a Zap to fix a Link-Listing problem ( link-listed libs do not work ). If you steplib to the Platinum P97G libraries your jobs will work.



PFU21023 - allow fast unload processing to work without steplibs ( if platinum P97G ) libraries are link-listed. This is a convoluted way of saying they dropped the ball ( why wouldn't they support link-listed libs the way they always do ). [...] 4810 70 13_Re: IDUG 20010_22_BILL_GALLAGHER@PHL.COM30_Mon, 8 Jan 2001 15:54:33 -0500523_us-ascii Charles,

This is correct. Sunday typically is conference registration, educational classes, and the evening "Welcome" reception. The actual conference itself runs Monday through Thursday.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083







Charles Stansell To: DB2-L@RYCI.COM Subject: IDUG 2001 Sent by: DB2 Data Base Discussion List



01/08/01 03:21 PM Please respond to DB2 Data Base Discussion List [...] 4881 31 9_DB2/Pearl14_jack fernicola20_bowling730@YAHOO.COM30_Mon, 8 Jan 2001 13:00:01 -0800386_us-ascii We have been requested by one of our customers, to inquire about running PEARL scripts aganist DB2 V5R1.

Is anyone willing to share some of their experiences using PEARL with DB2. Since we have no clue about Pearl, we are looking for any basic setup information that may be required for DB2. Such as, any ptfs, release level of DB2, ddf definitions, TCP/IP, etc... [...] 4913 61 13_Re: DB2/Pearl19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET30_Mon, 8 Jan 2001 15:08:43 -0600376_us-ascii Jack,

I've not used Perl (note : no a : it stands for "Practical Extraction and Reporting Language' or 'Pretty Eclectic Rubbish Lister') on DB2 for OS/390, but extensively on Unix (AIX, Solaris and Linux). In fact I'm preparing a presentation on this at present for IDUG.

I'd be glad if you could share your experiences of building Perl on OS/390. [...] 4975 58 40_Re: Trigerring an email via db2/os390 v619_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET30_Mon, 8 Jan 2001 15:12:14 -0600361_us-ascii I assume you've seen the article in the recent DB2 Magazine (www.db2mag.com) on doing this from DB2 UDB on Unix ?

On that platform you'd call Sendmail natively - but the article actually uses a Java interface to this.

I'm not sure OS/390 has a "standard" MTA (mail transport agent) similar to Sendmail (although you never know !!!). [...] 5034 170 32_Re: how to empty table more fast15_Murley, Michael22_Michael_Murley@BMC.COM30_Mon, 8 Jan 2001 15:42:48 -0600487_iso-8859-1 Greg,

If the data pages have not been re-used since the mass delete, RECOVER PLUS BACKOUT can still work because the rows are still in the data pages. If they have been reused, we can't back through the mass delete and we issue these messages:

BMC40265E NO DATA IN PAGE X'%X' BUT SPACE MAP PAGE X'%X' INDICATES DATA PAGE IS IN USE BMC40266E THIS PROBABLY INDICATES A BACKOUT OF A MASS DELETE OF A PAGE WHICH HAS BEEN REUSED. YOU MUST RUN A FORWARD RECOVERY [...] 5205 15 58_Should we use DB2 or Oracle for our very large database???12_Bob LaCerais28_Bob_LaCerais@TAX.STATE.NY.US30_Mon, 8 Jan 2001 16:54:50 -0500487_us-ascii We currently run DB2 for OS/390 along with DB2 UDB on our RISC boxes. There is a major project being discussed - rumor has it that it'll be done in Oracle. What are the pro's and con's of implementing a VLDB in Oracle as opposed to DB2?

================================================ 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. 5221 40 62_Re: Should we use DB2 or Oracle for our very large database???16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 8 Jan 2001 16:52:28 -0500574_iso-8859-1 Main pro: If you already have trained Oracle staff you can leverage their skills. Main cons: DB2 consistently rates a bit better for performance, scalablilty, integrity.... and *WAY* better for Total Cost of Ownership (includes hardware used, staffing, license fees, etc.)

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Bob LaCerais [mailto:Bob_LaCerais@TAX.STATE.NY.US] Sent: Monday, January 08, 2001 4:55 PM To: DB2-L@RYCI.COM Subject: Should we use DB2 or Oracle for our very large database??? [...] 5262 55 62_Re: Should we use DB2 or Oracle for our very large database???12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Tue, 9 Jan 2001 09:11:25 +1100406_- Assuming an OS/390 deployment is an option.

PARTITIONING ! ? - would be one. I don't believe that Oracle has it (I could be wrong though). This would help the maintainabillity of your tables, and give other advantages in performance when tackled carefully.

Being a self-confessed DB2 bigot (I still like ye olde Oracle as well), I'd also say performance, robustness, and availability! [...] 5318 32 15_Catalog entries17_Polley, Mike (M.)16_mpolley@FORD.COM31_Fri, 15 Dec 2000 12:20:45 -0500577_iso-8859-1 We're using 'Change Man' by Serena (a migration package - if you haven't heard of it, no problem.) You may know the answer to my question without being a Change Man expert. However, if you use it, you may better understand this dilemma. This is OS/390 DB2 Version 6. Using 'Change Man' (usually) when you move a DB2 program to prod, you would have bound it in test first. Wherein 'Change Man' just moves this to prod without recompiling. Thereby keeping the test and prod in sync. However, because of urgency (prod abends) an application group moved a bunch of [...] 5351 79 62_Re: Should we use DB2 or Oracle for our very large database???15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Mon, 8 Jan 2001 16:22:07 -0600550_us-ascii DB2 is well organised. DB2 possess system Catalogue information and I believe it does' nt exist in oracle.

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 01/08/2001 04:16 PM ---------------------------



"Cianci, Nick" on 01/08/2001 04:11:25 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Should we use DB2 or Oracle for our very large database??? [...] 5431 67 14_Ad Hoc EXPLAIN16_Brookman, Gerald32_gerald.brookman@US.ORIGIN-IT.COM30_Mon, 8 Jan 2001 17:29:26 -0500480_iso-8859-1 Envir: DB2 OS/390 V6.1

When we upgraded to DB2 V6.1, we deleted all the old PLAN_TABLE's and created a single new master 49 column PLAN_TABLE. All of our production plans and packages are currently bound with "EXPLAIN(NO)". What I would like to know is do I have to create an owner.PLAN_TABLE for each owner prior to a REBIND of all the production plans and packages with "EXPLAIN(YES)"? The V6.1 "Command Reference" manual on page 73-75 is not clear. TIA. [...] 5499 84 40_Re: Trigerring an email via db2/os390 v614_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 9 Jan 2001 09:29:59 +1100330_iso-8859-1 Pierre

OS/390 includes an SMTP program - see the IP Customization and Admin manual (eg SC31-8513 in the eNetwork Comms bookshelf for OS/390 V2.7; I don't know the V2.8 manual number). You can hook up to it by having a DD pointing to writer name SMTP (usually) and writing headers and data in RFC822 format. [...] 5584 41 19_Re: Catalog entries9_Rob Crane22_racrane@CONCENTRIC.NET30_Mon, 8 Jan 2001 15:38:09 -0700560_us-ascii You could use BIND PACKAGE COPY feature to assist you. Only get packages that have a timestamp equal to the timestamp of the programs they hit during their fixing activity.

"Polley, Mike (M.)" wrote: > > We're using 'Change Man' by Serena (a migration package - if you haven't > heard of it, no problem.) You may know the answer to my question without > being a Change Man expert. However, if you use it, you may better > understand this dilemma. This is OS/390 DB2 Version 6. > Using 'Change Man' (usually) when you move a DB2 program to [...] 5626 21 19_Re: Catalog entries0_17_JTonchick@AOL.COM28_Mon, 8 Jan 2001 17:36:05 EST560_US-ASCII My advice... NEVER manually update the DB2 Catalog tables except possiblly for Runstats information. Otherwise you'll be more likely to create more problems that what you are trying to fix.

Use the ChangeMan facilities and recompile in test. After all, it is just test.

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. 5648 57 46_Re: DB2 Stored Procedure migrations (UDB UNIX)14_Michael Finnis28_MFinnis@PRINCETONSOFTECH.COM30_Mon, 8 Jan 2001 17:56:34 -0500428_ISO-8859-1 Hello Gary,

I am a Product Specialist for Princeton Softech. We have a tool, Relational Tools for Servers and in particular the module Move for Servers, which will migrate Stored Procedures between DBMS subsystems.

You may care to look at: http://www.princetonsoftech.com/products and look at "Move for Servers" in particular.

HTH, Mike Finnis Product Specialist Princeton Softech Australia [...] 5706 52 39_Re: Archive DB2 system catalog (OS/390)36_Enarson, Mike (CC-Database Services)29_Mike.Enarson@CONAGRAFOODS.COM30_Mon, 8 Jan 2001 16:57:07 -0600630_iso-8859-1 Tibor, if you go back to the prefix.SDSNSAMP library that contains your install jobs, you will find a catalog backup in member DSNTIJIC.





Regards Mike Enarson





-----Original Message----- From: Tibor Zubonyai [mailto:zubonyait@YAHOO.COM] Sent: Monday, January 08, 2001 2:31 AM To: DB2-L@RYCI.COM Subject: Archive DB2 system catalog (OS/390)





Hello dear DB2 fun,

we installed a DB2 system 3 months ago (OS/390) and I should backup the system catalog with a full image copy. Please send me how to create the backup job (controll card and JCL). [...] 5759 12 47_DSNWZP STORED PROCEDURE WHICH READS AND RETURNS11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Mon, 8 Jan 2001 17:21:20 -0600306_us-ascii Does anyone have JCL and a program to execute "DSNWZP"?

================================================ 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. 5772 81 33_Re: V6 - Cursor names with dashes11_Suresh Sane21_data_arch@HOTMAIL.COM30_Mon, 8 Jan 2001 17:27:44 -0600463_- Walter & Tim - Thanks for the update.

I am still confused about IBM's long term direction on this. The SQL Ref says that only alpha/digits/underscore are allowed in a long identifier. We will be OK with the PTF but will IBM be disabling such support in the future? If so, I want to clean those we have and prevent any future programs containing cursors with dashes. If not, perhaps the manual should be changed? Will local binds be OK all the time? [...] 5854 31 51_Re: DSNWZP STORED PROCEDURE WHICH READS AND RETURNS11_Suresh Sane21_data_arch@HOTMAIL.COM30_Mon, 8 Jan 2001 17:30:01 -0600618_- Jeff - try running it from the Visual Explain. Works very well.

Suresh



>From: Jeff Faughn >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: DSNWZP STORED PROCEDURE WHICH READS AND RETURNS >Date: Mon, 8 Jan 2001 17:21:20 -0600 > >Does anyone have JCL and a program to execute "DSNWZP"? > >================================================ >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. [...] 5886 22 26_GUI development for DB2 ??13_Raghu V Singh15_rsingh1@LSU.EDU30_Mon, 8 Jan 2001 17:23:08 -0600512_us-ascii Hi all,

What do you need to develop GUI Interfaces for DB2 ???? ( kinda like Access Forms ? ). What are the IDE options available to someone developing for windows ?

Also does DB2 have ODBC drivers for Windows ??

regards to all

Raghu

================================================ 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. 5909 31 28_how to empty table more fast14_Mark McCormack27_mamccormack@STATESTREET.COM30_Mon, 8 Jan 2001 18:43:48 -0500479_us-ascii Whether you can quickly delete all rows from a table via an SQL stmt (DELETE FROM creator.table) depends on the table structure.

If the table is segmented, then only the space map pages will be reset and logged. The process will be quick and efficient. If the tablespace is not segmented (partitioned or simple), then a mass delete via SQL will cause each row to be deleted individually. This can take much time and involve lots of logging for large tables. [...] 5941 21 40_DB2 Print Log Map DSNJU004 Error message10_Lee Mandel16_DBMSUser@AOL.COM28_Mon, 8 Jan 2001 18:42:19 EST347_US-ASCII Hi All, We are not using data sharing members yet. We are on V5 of DB2. When we use the print log map utility DSNUJ004 if you do not have a sysin dd statement you get error message DSNJ237I It is only a info message. If we use a sysin dd statement and put member(*) or any keyword we still get the same message. Any way around this? [...] 5963 74 19_Re: Catalog entries14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 9 Jan 2001 10:57:31 +1100544_iso-8859-1 The reason why one _cannot_ do what you are suggesting is that there is two halves to DB2's internal picture, and the catalog is one half. The other half is the the directory - which you have no way of updating except through the standard methods (BIND, CREATE/ALTER etc SQL). In the case of programs, DB2 uses the stuff in the directory to actually do its work - not the contents of the catalog. So, yes, if you got really tricky you could copy stuff around in the catalog: but DB2 would happily continue to use the directory. [...] 6038 108 18_Re: Ad Hoc EXPLAIN11_James Szabo18_jim.szabo@CORE.COM30_Mon, 8 Jan 2001 19:10:16 -0500418_iso-8859-1 Ad Hoc EXPLAINYes - your REBINDs will fail otherwise.

Next time, don't delete the old PLAN_TABLES, but upgrade them via ALTER commands. If you have a change management product (BMC, CA, etc.), this is quite easy.

Jim Szabo ----- Original Message ----- From: Brookman, Gerald Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Monday, January 08, 2001 5:29 PM Subject: Ad Hoc EXPLAIN [...] 6147 34 18_DB2 and timestamps16_Dylan Vanderhoof20_DylanV@SEMAPHORE.COM30_Mon, 8 Jan 2001 16:26:40 -0800371_iso-8859-1 Hello all, This is probably something obvious and well documented, but I'm not seeing it anywhere. Is there a way to have the DBM convert a DB2 timestamp into a UNIX timestamp? (longint seconds since Jan 1, 1970)

I would use timestampdiff(), but that functions apparently makes some assumptions that will skew the resulting timestamp rather badly. [...] 6182 69 22_Re: DB2 and timestamps14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 9 Jan 2001 12:19:29 +1100633_iso-8859-1 (days(current date) - days('1970-01-01')) * 86400 + midnight_seconds(current time) ?

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Dylan Vanderhoof [mailto:DylanV@SEMAPHORE.COM] Sent: Tuesday, January 09, 2001 11:27 AM To: DB2-L@RYCI.COM Subject: [DB2-L] DB2 and timestamps



Hello all, This is probably something obvious and well documented, but I'm not seeing it anywhere. Is there a way to have the DBM convert a DB2 timestamp into a UNIX timestamp? (longint seconds since Jan 1, 1970) [...] 6252 59 40_Re: Read-only table design advice wanted15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Tue, 9 Jan 2001 13:14:49 +1100672_- Greetings Ed(ward), Michael, Glen, Marc, John, Roger, etc.,

Thanks for all your comments, although only Roger was really able to provide insight, albeit generically.

Ed, I was never going to have multi-table tablespaces; either partitioned (somehow) or separate tables with separate tablespaces.

Mike/Glen, I don't want to toggle partitions (or tables) as, although I like the idea, it would involve artificially creating a partitioning key which a) we're not really in a position to do, and b) wouldn't be able to be provided by the application. Actually, you've just made me start thinking about something I'll have to investigate... Anyway, [...] 6312 131 55_Re: Collections Plans Packages and software versioning?14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 9 Jan 2001 13:34:22 +1100387_iso-8859-1 Commiserations Lisa, it'll be a mess to control. All the following comments are dependant on fitting into whatever promotion schemes you currently use.

Start by considering load libraries - mainly because your clients can understand them and the way you manage packages is dependant on how you handle load libraries. (I am presuming promotion is DEVL->MODL->QUAL) [...] 6444 26 31_Restarting a failed Load Resume15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Tue, 9 Jan 2001 13:34:52 +1100570_- Colleagues, no pressure on this; it hasn't happened, I'm just wondering what would.

If I'm running a Load Resume Log No into a tablespace with existing data and the load fails with a B37 on the tablespace, where do I stand? Assume I have all necessary workfiles (sysut1, sortout, etc). Do I have to recover, after altering priqty and secqty? Would a stop-DF/DSS move-start job (to reduce extents) be enough to enable a successful restart(commit)? Could I even stop it with a utility pending? As you can tell, I haven't had a failed load resume for awhile. [...] 6471 67 63_Re: Should we use DB2 or Oracle for our very large databa se???14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 9 Jan 2001 13:38:55 +1100478_iso-8859-1 Some random jottings:

How large is "very"? What processing load will be on the data? How does this load compare to benchmarks (eg www.tpc.org).

I note that you refer to already using DB2, but didn't mention that you use Oracle. If you don't, have the costs associated with supporting Oracle been considered - training, maintaining yet another skill set, buying in experience? RYC used to have a white paper on the costs, but I can't find it today. [...] 6539 68 30_Re: GUI development for DB2 ??16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 09:32:33 +0530393_- Raghu,

I think anything which you can use with other database can be used with DB2 for GUI development. Anything means your own developed application in any language. However, DB2 Forms are available which is much easier to use and its a very good product because it offers web development very easily. You can download the trial copy from IBM website and use that for sometime. [...] 6608 60 44_Re: DB2 Print Log Map DSNJU004 Error message16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 09:44:22 +0530615_- Could you please send your JCL because i am sure you are aware of why DSN237I (informational message) is issued. Just check if you are specifying it correctly.

Is this something like this ?

//SYSIN DD* MEMBER(*)

HTH Regards Sanjeev







> -----Original Message----- > From: Lee Mandel [SMTP:DBMSUser@AOL.COM] > Sent: Tuesday, January 09, 2001 5:12 AM > To: DB2-L@RYCI.COM > Subject: DB2 Print Log Map DSNJU004 Error message > > Hi All, > We are not using data sharing members yet. We are on V5 of DB2. When we > use the print log map utility DSNUJ004 if you do [...] 6669 50 18_Re: Ad Hoc EXPLAIN16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 09:50:02 +0530607_- If you do not have the PLAN_TABLE for each owner, bind/rebind will definitely fail and probably with -204. In V6, all the newly added columns are NOT NULL WITH DEFAULT, so i think ALTER can be used.

HTH Regards, Sanjeev

> -----Original Message----- > From: Brookman, Gerald [SMTP:gerald.brookman@US.ORIGIN-IT.COM] > Sent: Tuesday, January 09, 2001 3:59 AM > To: DB2-L@RYCI.COM > Subject: Ad Hoc EXPLAIN > > Envir: DB2 OS/390 V6.1 > > When we upgraded to DB2 V6.1, we deleted all the old PLAN_TABLE's and > created a single new master 49 column PLAN_TABLE. All of our production > plans [...] 6720 73 19_Re: Catalog entries16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 09:56:38 +0530458_- First of all it is never advisable to update the catalog unless until we need to give some special hint to the optimizer which ofcourse are going to help us. Another thing which i feel in your case is, you can't get away with updating the catalog tables only. What about the directory where plans/packages are kept. Infact nothing more would have changed in the catalog tables other than timestamp. The executable is still not possible to be changed. [...] 6794 55 62_Re: ??: Re: How to improve the operation of insert and updat e16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 10:38:41 +0530591_- Everything is clearly explained by John.

Best thing is to have an online monitor, run the job and monitor for the reason of poor performance. Probably find that SELECT statement for checking the duplicate is the culprit and remove that, let the insert get -803 and remove the abend logic for this sqlcode. Also look at the Buffer Pool activity for Insert and Select. Probably you are not finding anything in the buffer for select. There is one more thing i.e. commit frequency which is important. Sometimes too many commits are causing problems. I would request the list to go [...] 6850 78 35_Re: Restarting a failed Load Resume16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 11:23:34 +0530575_- Hi Raymond,

I assume you are talking about full underlying VSAM dataset for the tablespace.

I think the load job will fail with Resource Unavailable and DB2 will put the tablespace in RECP status. If you need the data which was existing, you need to recover the tablespace with previous image copy + log ,after altering the priqty and secqty. After this recovery you will loose the data which was loaded because of LOG NO, so question of restrting the utility doesn't look logical. I did face this situation a lot in my previous work and if i am correct [...] 6929 125 63_AW: Should we use DB2 or Oracle for our very large databa se???12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Jan 2001 08:35:28 +0100717_iso-8859-1 James,

to "RYC used to have a white paper on the costs, but I can't find it today......"

Try http://www.ylassoc.com/DB2%20Facts/DB2vsOracle.htm

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 Software Development & Technology Center Knowlegde Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 7055 85 39_Re: Archive DB2 system catalog (OS/390)14_Tibor Zubonyai19_zubonyait@YAHOO.COM30_Mon, 8 Jan 2001 23:51:52 -0800572_us-ascii

Dear Mike,

Thank you for your answer.

Regards

Tibor



"Enarson, Mike (CC-Database Services)" wrote: Tibor, if you go back to the prefix.SDSNSAMP library that contains your install jobs, you will find a catalog backup in member DSNTIJIC.





Regards Mike Enarson





-----Original Message----- From: Tibor Zubonyai [mailto:zubonyait@YAHOO.COM] Sent: Monday, January 08, 2001 2:31 AM To: DB2-L@RYCI.COM Subject: Archive DB2 system catalog (OS/390) [...] 7141 26 62_Re: Should we use DB2 or Oracle for our very large database???10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 9 Jan 2001 09:28:15 +0100356_us-ascii If you want pain, a recover duration of months, some brand new features (????) like packages and partitioning (ORACLE 8i), no access path (as far as I know until ORACLE 8i), only 1 BP for ALL data and some other nice tuning features (???)

at less cost (hi hi hi ah ah ah uh uh uh..... yesss and I'm a Klingoon DB2 sysprog) buy Oracle. [...] 7168 93 44_Re: DB2 Print Log Map DSNJU004 Error message0_18_mebert@AMADEUS.NET30_Tue, 9 Jan 2001 09:55:45 +0100537_us-ascii Hi Lee,

we're on DB2 V5, no data sharing. This JCL works for us, RC=0, no error message, informational or otherwise:

//OSO031JU JOB (USERJOB,ME),'M.EBERT X3962',CLASS=Y,MSGCLASS=D, // MSGLEVEL=(1,1),REGION=0M,NOTIFY=&SYSUID /*JOBPARM SYSAFF=AMAC //*------- DSNJU004 - Print Log Map (Util. Guide p. 3-28) -------------- //DSNJU004 EXEC PGM=DSNJU004 //SYSUT1 DD DISP=SHR,DSN=DB2T.BSDS01 //SYSPRINT DD SYSOUT=*

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 7262 91 40_Re: REPAIR DBD complains about DB in use0_18_mebert@AMADEUS.NET30_Tue, 9 Jan 2001 10:22:19 +0100575_us-ascii Hello List,

thanks for all responses, on- and off-list. Unfortunately there was nothing that helped me. Here's a short summary:

1) REPAIR DBD requires the DB to be in UT mode. There's a new PTF (UQ48011) that can run REPAIR DBD DIAGNOSE/TEST in RW mode. We don't have it and it's no help for REPAIR DBD REBUILD anyway. It's definitely not a vital PTF to have. 2) The PTF about the DBD length display (UQ33487) fixes the display for DBD lengths>65536. We have it. Anyway the problem was not with the display; this is more likely just a symptom. 3) [...] 7354 111 62_Re: Should we use DB2 or Oracle for our very large database???15_Agarwal, Ashish28_Ashish.Agarwal@HPSGLOBAL.COM30_Tue, 9 Jan 2001 14:49:05 +0530314_- Dear Bob, Oracle will be costlier compared to db2 if buying Oracle's latest version..Can u please write in more details about the kind of transactions and size of databse involved for ur VLDB. Further, u have any legacy/mainframe systems to upgrade or integrate with, in your new project..? Regards Aashish [...] 7466 87 50_Re: Should we use DB2 or Oracle for our very large15_Agarwal, Ashish28_Ashish.Agarwal@HPSGLOBAL.COM30_Tue, 9 Jan 2001 15:00:41 +0530615_- Dear All, system catalogue information exist in oracle too.it has got a different name though.. Data Dictionary and views, further partitioning is there in Oracle since its 8.0.3 version (released in 1997). Further, it doesnt take months to recover ..use RecoverY manager, Multiplexing and Standby Databases and ur database will be Up in no time. Further, oracle i believe is more open ended system unlike DB2 about which runs best on IBM platforms.. Am new to DB2 and have worked on oracle previously..and am really not much impressed..Though i must say its Equally robust, if not more, compared to Oracle. [...] 7554 55 62_Re: Should we use DB2 or Oracle for our very large database???0_27_jim.leask@RS-COMPONENTS.COM30_Tue, 9 Jan 2001 09:47:46 -0000598_iso-8859-1 Much as I hate to defend Oracle but as from Oracle version 8.1.6 it has had 3 buffer pools (KEEP, DEFAULT and RECYCLE).

Jim.

-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: 09 January 2001 08:28 To: DB2-L@RYCI.COM Subject: Re: Should we use DB2 or Oracle for our very large database???



If you want pain, a recover duration of months, some brand new features (????) like packages and partitioning (ORACLE 8i), no access path (as far as I know until ORACLE 8i), only 1 BP for ALL data and some other nice tuning features (???) [...] 7610 89 40_Re: Trigerring an email via db2/os390 v60_27_jim.leask@RS-COMPONENTS.COM30_Tue, 9 Jan 2001 09:55:13 -0000612_iso-8859-1 Pierre,

This is some JCL I used to send an Email with a file (TXT1) as an attachment, thought it may help. The SYSEXEC is the library which holds the XMITIP REXX program at your site.

Jim.

//CTS03E2A JOB (1234),'JOB',CLASS=G,MSGCLASS=X,NOTIFY=&SYSUID //* //SENDMAIL EXEC PGM=IKJEFT01,DYNAMNBR=35 //SYSEXEC DD DSN=RS.PROD.RLIB,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //TXT1 DD DSN=CTS.DBA.JCL(EMAIL),DISP=SHR //SYSTSIN DD * %XMITIP (JIM.LEASK@RS-COMPONENTS.COM) - FILEDD (TXT1) - FILENAME (TEST.TXT) - FORMAT (.TXT) - NOMSG - SUBJECT "TEST TEXT FROM BATCH" //* [...] 7700 42 44_Re: DB2 Print Log Map DSNJU004 Error message20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM28_Tue, 9 Jan 2001 05:14:20 EST620_ISO-8859-1 Sanjeev, I am executing it foreground: PROC 0 /* RKIDBC55 CONTROL END(ENDO) SET &RESULT = &SYSDSN('&SYSUID..SYSPRINT') IF &RESULT ¬= OK THEN DO ALLOC REUSE F(SYSPRINT) DS('&SYSUID..SYSPRINT') NEW + SPACE(3,3) CYLINDERS RECFM(F B A) LRECL(121) ENDO ISPEXEC VGET (BSDSDSN1 BSDSDSN2 DSNLOAD HILEV) SHARED ALLOC REUSE F(SYSUT1) DS('&BSDSDSN1.') SHR ALLOC REUSE F(SYSUT2) DS('&BSDSDSN2.') SHR ALLOC REUSE F(SYSPRINT) DS('&SYSUID..SYSPRINT') SHR ALLOC REUSE F(SYSIN) DS('&HILEV..SKELS(MEMBER)') SHR CALL '&DSNLOAD.(DSNJU004)' ISPEXEC BROWSE DATASET('&SYSUID..SYSPRINT') EXIT The skels(member) below: MEMBER(*) [...] 7743 85 44_Re: DB2 Print Log Map DSNJU004 Error message16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Jan 2001 16:13:16 +0530402_iso-8859-1 From my level of knowledge in CLIST, i can not judge how the SYSIN parameter will be passed but it looks correct to me.Just had a quick look at my CLIST manual but could not find much. Please wait for replies from someone who knows CLIST better than me(anyone could be). I can only suggest you to look if the SYSIN parameters are resolved correctly or not. It should be MEMBER(*) only. [...] 7829 44 62_Re: Should we use DB2 or Oracle for our very large database???10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 9 Jan 2001 11:54:19 +0100397_us-ascii Oh YES, 3 Bp, great !!! 1 for ts, 1 for ix, 1 for workfiles. AND from version 8.1.6. !!! I suppose there's a 5 speed model too...

May be in version 9.69.69.69.69.69.69.69/HAL9000 you'll have a decent access path, a decent I/O error detection

(without loosing tons of data, I saw this with my own eyes) , a partitioning that work, may be parallellism (with 5 valves per [...] 7874 156 40_Re: Trigerring an email via db2/os390 v60_18_mebert@AMADEUS.NET30_Tue, 9 Jan 2001 12:05:34 +0100352_us-ascii Jim,

is this a standard OS/390 REXX? If so, how can I locate it in our system? I could not find it, a reference to it, or anybody who knows it anywhere. For info, I have attached the job I use for sending SMTP emails from OS/390.

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 8031 212 40_Re: Trigerring an email via db2/os390 v60_27_jim.leask@RS-COMPONENTS.COM30_Tue, 9 Jan 2001 11:15:17 -0000389_iso-8859-1 Oooops!! Talked to my MVS guys and they said it was downloaded from the NET. It can be found on many sites, here is one of them:- http://www.geocities.com/lbdyck/

All the best, Jim.

-----Original Message----- From: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Sent: 09 January 2001 11:06 To: DB2-L@RYCI.COM Subject: Re: Trigerring an email via db2/os390 v6 [...] 8244 21 27_when condition in a trigger15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 9 Jan 2001 05:17:08 -0600530_- Hello list

I want to come back to a question, I asked some weeks ago, because I didn't get any feedback. Maybe I should ask my question in a different way.

Did anybody use triggers with a WHEN-condition in a trigger body and checked the access path. I always get a tablespace scan on the table on which I defined the trigger, if I use a transition variable (even for the simplest condition for example WHEN N.column = constant). If this is really a ts, it could be horrible. I wonder what DB2 is looking for? [...] 8266 25 13_Re: DB2/Pearl10_Tim Miller21_tmiller@TELCORDIA.COM30_Tue, 9 Jan 2001 06:59:12 -0500569_us-ascii Jack,

I am using perl/cgi on a sun/solaris machine to sever up webpages from my DB2 for OS/390 database using DB2 Connect. What exactly are you looking for?



Please note I receive a digest form of these emails, please send any questions to tmiller@telcordia.com.



regards,

Tim

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8292 28 51_Re: DSNWZP STORED PROCEDURE WHICH READS AND RETURNS11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Tue, 9 Jan 2001 09:27:34 -0500482_iso-8859-1 Jeff,

You can use Visual Explain if you have it on your workstation. Also I posted Rexx and Cobol example on the DB2L Documents site. See:

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



-----Original Message----- From: Jeff Faughn [mailto:Jeff_Faughn@MAY-CO.COM] Sent: Monday, January 08, 2001 6:21 PM Subject: DSNWZP STORED PROCEDURE WHICH READS AND RETURNS



Does anyone have JCL and a program to execute "DSNWZP"? [...] 8321 67 44_Re: DB2 Print Log Map DSNJU004 Error message22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM30_Tue, 9 Jan 2001 10:15:47 -0500784_iso-8859-1 Remove the parenthesis (). The syntax is MEMBER * Also add the GROUP DDNAME.



Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749



-----Original Message----- From: Edward C. Benoit Jr. [mailto:DB2Automate@AOL.COM] Sent: Tuesday, January 09, 2001 5:14 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 Print Log Map DSNJU004 Error message



Sanjeev, I am executing it foreground: PROC 0 /* RKIDBC55 CONTROL END(ENDO) SET &RESULT = &SYSDSN('&SYSUID..SYSPRINT') IF &RESULT ¬= OK THEN DO ALLOC REUSE F(SYSPRINT) DS('&SYSUID..SYSPRINT') NEW + SPACE(3,3) CYLINDERS RECFM(F B A) LRECL(121) ENDO ISPEXEC VGET (BSDSDSN1 BSDSDSN2 DSNLOAD HILEV) SHARED ALLOC REUSE F(SYSUT1) DS('&BSDSDSN1.') SHR ALLOC REUSE [...] 8389 123 63_Re: Should we use DB2 or Oracle for our very large databa se???9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Tue, 9 Jan 2001 08:37:38 -0700627_iso-8859-1 Howdy Bob,

Having had some Oracle DBA background I'll make a few comments. As Jim mentioned Oracle does have some similar functions to DB2. In some cases it has some advanced functions that DB2 does not currently offer but will in Version 7.

A couple of things to think about: (I could write a thesis of reasons not to consider Oracle but I will keep it short). Oracle just recently offered something called Multithreaded Server (MTS). What this function allows is the ability to share Memory and Resources. Prior to this function Oracle was single threading these resources causing significantly [...] 8513 17 15_Re: help please12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 9 Jan 2001 10:01:09 -0600427_- One small change - from CURRENTDATA(YES) to CURRENTDATA(NO) can make a big difference in the performance. One situation I saw reduced the cpu time by more than 20%. I am a big fan of CURRENTDATA(NO), since you can have almost the same number of locks and cpu time as ISOLATION(UR), but avoid the inconsistencies of changes in progress. With just this dynamic SQL, it's nearly impossible to depend upon CURRENTDATA(YES). [...] 8531 32 27_DB2 Stored Procedure - JAVA13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 9 Jan 2001 10:08:20 -0600497_iso-8859-1 Hi,

I have a question about the environment required to implement JAVA stored procedures on the OS/390 environment.

From what I have read it states that the WLM-managed environment does not support the Java Virtual Machine (JVM). The Java bytecode has to be compiled which requires the purchase of extra software.

1. Is it true that we must purchase VisualAge for Java Enterprise Edition for OS390 to get the HPCJ to compile the Java. Is there an alternative? [...] 8564 74 51_Thread becomes indoubt in a distributed environment12_Fertaki Gina20_GFertaki@EUROBANK.GR30_Tue, 9 Jan 2001 17:57:50 +0200388_iso-8859-7 Hello everyone!

We have the following connection:

|------------| SNA |----------| TCP/IP |----------| | OS/390 |----------- | NT |---------- | UNIX | | CICS | /---------| Tuxedo | /-----------| TUXEDO | | DB2 | | | | INFORMIX | |------------| |----------| |----------|

The connection between OS/390 and NT is APPC (LU6.2). CICS and DB2 are Version 4. [...] 8639 47 39_Re: Archive DB2 system catalog (OS/390)11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Tue, 9 Jan 2001 11:15:47 -0500565_iso-8859-1 Tibor,

The Installation process created a sample job for this. Look in the samplib member. Should be something like dsn510.SDSNSAMP(DSNTIJIC) .

-----Original Message----- From: Tibor Zubonyai [mailto:zubonyait@YAHOO.COM] Sent: Monday, January 08, 2001 3:31 AM Subject: Archive DB2 system catalog (OS/390)





Hello dear DB2 fun,

we installed a DB2 system 3 months ago (OS/390) and I should backup the system catalog with a full image copy. Please send me how to create the backup job (controll card and JCL). [...] 8687 21 57_Re: DB2 V5 OS/390 - CPU consumption of DB2 address spaces12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 9 Jan 2001 10:37:50 -0600437_- At the 100,000 foot level, the DBM1, MSTR and IRLM address space cpu times are not included in the class 2 accounting. The DDF and stored procedure address spaces are the applications and are included in the class 2 accounting. I presume that you are using the red book, DB2 for OS/390 Capacity Planning, SG24-2244. It has information about the address spaces and the section on capture ratios seems close to what you are asking. [...] 8709 100 19_Re: Catalog entries19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Tue, 9 Jan 2001 08:57:38 -0800368_iso-8859-1 We have ChangeMan as well, but for implementation take an approach which is similar to what you need to do:

Each development project has it's own test database/owner. New and updated modules are only compiled and bound into the library and package collection associated with the assigned database/owner (managed via ChangeMan promotion levels). [...] 8810 16 36_Re: New tablespaces SYSSEQ & SYSSEQ212_Roger Miller19_millerrl@US.IBM.COM30_Tue, 9 Jan 2001 10:59:37 -0600329_- I did a quick check, and the DSNTIJIC job was updated to include SYSSEQ and SYSSEQ2 in APAR PQ30652. The order for recovery is specified in the updated Utilities Guide and Reference soft copy, dsnug0g2.pdf. SYSSEQ and SYSSEQ2 are there. There is also a note in the updated Administration Guide for point in time recovery. [...] 8827 40 14_Update Problem16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Tue, 9 Jan 2001 13:24:35 -0500696_iso-8859-1 Hey Folks... I have an application person trying to execute an update statement to replace a varchar column of 1800 bytes called c_text. Their sql is as follows and the error follows that... any help would be appreciated..

UPDATE tab1 SET c_text = 'oday because she had some hyperkeratosis^on her previous reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc. 3. Trial albuterol inhaler. If not improving in a few^weeks, will need to get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01 DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by [...] 8868 61 18_Re: Update Problem11_Eric Robida22_Eric_Robida@BCBSME.COM30_Tue, 9 Jan 2001 13:22:48 -0500592_us-ascii I do not use varchar much but don't you have to tell DB2 the length of the text?







"Dempsey, Michael" on 01/09/2001 01:24:35 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Eric Robida/BCBSME) Subject: Update Problem





Hey Folks... I have an application person trying to execute an update statement to replace a varchar column of 1800 bytes called c_text. Their sql is as follows and the error follows that... any help would be appreciated.. [...] 8930 57 18_Re: Update Problem17_Bruzdzinski, Mike24_MIKE.BRUZDZINSKI@SSA.GOV30_Tue, 9 Jan 2001 13:24:13 -0500383_iso-8859-1 I suspect you need to update the length of the varchar variable witin the program. You may even want to consider setting it to max of 1800 (by your definition) prior to issuing the UPDATE.



-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Tuesday, January 09, 2001 1:25 PM To: DB2-L@RYCI.COM Subject: Update Problem [...] 8988 65 18_Re: Update Problem16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Jan 2001 13:20:42 -0500576_iso-8859-1 varchar or long varchar

If the former, the message is telling you the truth.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Tuesday, January 09, 2001 1:25 PM To: DB2-L@RYCI.COM Subject: Update Problem



Hey Folks... I have an application person trying to execute an update statement to replace a varchar column of 1800 bytes called c_text. Their sql is as follows and the error follows that... any help would be appreciated.. [...] 9054 73 18_Re: Update Problem14_Scott Trometer22_scott.trometer@RCI.COM30_Tue, 9 Jan 2001 13:36:35 -0500381_iso-8859-1

I thought that the max string constant was 255. So wouldn't you have to do this update in 2 or more steps, concating the first 255 bytes with the next, and so on...Or does that only apply to inserts?

Update tab1 set c_text = 'first 255' where Key = 'xxxx'





Update tab1 set c_text = C_text || 'next 255 bytes' Where key = 'xxxx' [...] 9128 31 14_Update Problem19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Tue, 9 Jan 2001 13:42:49 EST556_us-ascii Michael,

My guess is that this is a static SQL where the string that you have typed/pasted in your post is actually a host variable. If it were a dynamic SQL built and executed in the program, you would not have a problem.

My suggestion would be either to define a group variable with S9(4) COMP as the first element in your group and your text variable as the second element in the group. Use the group variable name in your SQL instead of your text variable. DO NOT FORGET to populate the first element with the length of the [...] 9160 63 19_Re: Catalog entries16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Tue, 9 Jan 2001 11:10:09 -0800332_iso-8859-1 Is there any reason that you can't just BIND them in Test?

Some people have suggested recompiling, but that just creates another Test version. Then your Test and Prod timestamps don't match, so you've got to keep the Test Load Module around, even though it's identical to Prod except for the Precompile time. [...] 9224 21 21_Catalog Shadow Tables16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Tue, 9 Jan 2001 14:28:41 -0500428_iso-8859-1 Hey Folks... Is anyone out there using catalog shadow tables. Our apps folks are asking to query the catalog. Are there other reason's besides the locking contention on the catalog that would want me to create shadow tables. If so, would it be best to unload from the catalog tables and load into the shadow tables, or delete from the shadow tables and insert from the catalog tables on a nightly basis. Thanks! [...] 9246 48 25_Re: Catalog Shadow Tables16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Jan 2001 14:17:57 -0500410_iso-8859-1 We allow fairly open access to our catalog tables with the understanding that if anyone starts causing locking problems they may lose their access.

If you do the shadow routine (I have done this elsewhere), UNLOAD/LOAD is a very easy way. If you need, you can also propagate changes via a tool like Platinum/CA Log Analyzer (I suppose BMC and possibly CDB have similar tools available). [...] 9295 55 18_Re: Update Problem12_Taylor, John19_John.Taylor@SSA.GOV30_Tue, 9 Jan 2001 14:24:11 -0500550_iso-8859-1 I am not a DB2 expert but it looks like you have a premature ' embedded in your text string after get PFT - marked red below.

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Tuesday, January 09, 2001 1:25 PM To: DB2-L@RYCI.COM Subject: Update Problem



Hey Folks... I have an application person trying to execute an update statement to replace a varchar column of 1800 bytes called c_text. Their sql is as follows and the error follows that... any help would be appreciated.. [...] 9351 65 18_Re: Update Problem12_Kirk Hampton16_khampto1@TXU.COM30_Tue, 9 Jan 2001 13:23:40 -0600377_us-ascii The max length of a character literal is 255, and the literal you have posted here is 330+. Also, there is an apostrophe in the 5th line "get PFT's" which would cause you a premature termination of the literal and get you a syntax error even if the overall length was valid. So this statement is not executable as coded. What tool is being used to execute this ? [...] 9417 51 25_Re: Catalog Shadow Tables12_Michael Deer20_Michael.Deer@UAL.COM30_Tue, 9 Jan 2001 13:33:30 -0600549_- I worked at a shop a few years ago that used a shadow catalog tables. We used unload/load every night. I dont think they really did much to improve access (most dba's queried the actual catalog anyway). However, it did save the day when a new DBA (with SYSADM authority) ran a "FREE PLAN *". The user attentioned out of it after about 95% of the plans in the subsystem were FREE'd. I was able to use CA/Platinum's migrator to create a script that bound all the missing plans by pointing migrator at the alternate catalog (which still had the [...] 9469 83 18_Re: Update Problem16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Tue, 9 Jan 2001 14:42:20 -0500390_iso-8859-1 Hey Kirk.. Thanks.. The tool they're using is the command line processor on their windows workstation. There's a function that allows you to create update statemnets under the DB2 for Window's 98 Toolset....

-----Original Message----- From: Kirk Hampton [mailto:khampto1@TXU.COM] Sent: Tuesday, January 09, 2001 2:24 PM To: DB2-L@RYCI.COM Subject: Re: Update Problem [...] 9553 88 25_Re: Catalog Shadow Tables0_26_JCameron@MSI-INSURANCE.COM30_Tue, 9 Jan 2001 14:03:46 -0600633_us-ascii If locking is the issue, have your app people add "WITH UR" to their SELECT statements.

John Cameron JCameron@msi-insurance.com











"Pearson, Eric L," To: DB2-L@RYCI.COM Subject: Re: Catalog Shadow Tables Sent by: DB2 Data Base Discussion List



01/09/01 01:17 PM Please respond to DB2 Data Base Discussion List











We allow fairly open access to our catalog tables with the understanding that if anyone starts causing locking problems they may lose their access. [...] 9642 135 19_Re: Catalog entries17_Polley, Mike (M.)16_mpolley@FORD.COM30_Tue, 9 Jan 2001 15:08:39 -0500430_iso-8859-1 Actually, I don't know why my question took so long to reach the listserv, but nevertheless, I appreciate all the feed back, suggestions, and clarifications. The group has just been correcting the -805s as they happen.





-----Original Message----- From: Provenzola, Tony [mailto:Tony.Provenzola@NIKE.COM] Sent: Tuesday, January 09, 2001 2:10 PM To: DB2-L@RYCI.COM Subject: Re: Catalog entries [...] 9778 36 25_Re: Catalog Shadow Tables13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 9 Jan 2001 14:11:30 -0600658_iso-8859-1 I allow access to the catalog, and get the programmers etc. to use the "WITH UR" clause always to stop contention.

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Tuesday, January 09, 2001 1:29 PM To: DB2-L@RYCI.COM Subject: Catalog Shadow Tables



Hey Folks... Is anyone out there using catalog shadow tables. Our apps folks are asking to query the catalog. Are there other reason's besides the locking contention on the catalog that would want me to create shadow tables. If so, would it be best to unload from the catalog tables and load into the shadow tables, or delete from the [...] 9815 58 25_Re: Catalog Shadow Tables16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Jan 2001 15:11:20 -0500434_iso-8859-1 Does UR no longer leave CLAIMs against the objects? When we had the problem UR solved the locks but not the claims. Maybe maint has fixed that (it was quite a while ago).

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Mackey, Glenn [mailto:GMackey@GUIDEMAIL.COM] Sent: Tuesday, January 09, 2001 3:12 PM To: DB2-L@RYCI.COM Subject: Re: Catalog Shadow Tables [...] 9874 233 54_Re: SQL Procedures Processor (?) in WLM-managed SP JCL12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 9 Jan 2001 12:51:02 -0800596_us-ascii Lyon, I haven't answered this before because DB2 Data Sharing is not my speciality. But here's my gut feeling.

I would think that you'd need for one WLM JCL Proc. naming it for the DS group to match the WLM application environment. Since the WLM AE is shared over the Data sharing group over each processor, WLM can decide depending upon the workload on each processor where SP1 will actually run (ie which processor to start the WLM address space). --- Lockwood Lyon wrote: > Thanks, Myron. We don't plan on using the SP > Builder immediately, so I'll [...] 10108 84 25_Re: Catalog Shadow Tables12_Kirk Hampton16_khampto1@TXU.COM30_Tue, 9 Jan 2001 15:00:55 -0600553_us-ascii We also populate a shadow catalog every night using Data Refresher (formerly DXT) to unload to a flat file, then a LOAD REPLACE LOG NO into the shadow. Same as Michael, it has saved our behind more than once to be able to reconstruct something after an Oops. We do allow full catalog access in the Development subsystem, but not in Production. Also, in production we keep not only a 1-day-old shadow, but also one that is only refreshed every two weeks, in case a problem is not noticed the same day that it happened. I think a DELETE and [...] 10193 156 54_Re: SQL Procedures Processor (?) in WLM-managed SP JCL13_Lockwood Lyon19_Lockwool@MEIJER.COM30_Tue, 9 Jan 2001 16:16:50 -0500607_US-ASCII Dear List,

I don't think WLM works correctly this way ... not sure. help.

When defining the Application Environment (plex-wide) you get to specify a single ProcName and pass it parms.

So, for example, if you have DB2A and DB2B in a data sharing group (maybe on the same LPAR). A stored procedure SP1 gets invoked from an appl on DB2A (or from its DDF). DB2A checks in SYSIBM.SYSROUTINES for the WLM_ENVIRONMENT value, and invokes WLM. WLM notes the subsystem ID, finds the Proc (say, DB2AWLM1) in the ProcLib, (optionally) passes parameters, and starts the address space. [...] 10350 57 35_Re: Restarting a failed Load Resume15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 10 Jan 2001 09:44:35 +1100304_- Hi Sanjeev,

I guess I knew what you're saying; I just wanted someone to confirm it. Basically TSs involved in failed Load Resumes (when they fail 'cause the TS is full) have to be recovered to before the start of the load. Not what I wanted to hear, but, hey, it's a tough world out there. [...] 10408 40 19_Re: Catalog entries12_Dallas Focht21_dallas.focht@PMIC.COM30_Tue, 9 Jan 2001 17:38:51 -0600559_us-ascii Is there a reason you can not bind the test programs pointing to the appropriate DBRM member?

"Polley, Mike (M.)" wrote:

> We're using 'Change Man' by Serena (a migration package - if you haven't > heard of it, no problem.) You may know the answer to my question without > being a Change Man expert. However, if you use it, you may better > understand this dilemma. This is OS/390 DB2 Version 6. > Using 'Change Man' (usually) when you move a DB2 program to prod, > you would have bound it in test first. Wherein 'Change Man' just [...] 10449 34 46_Trying to access UDB on AIX from UDB on OS/39010_Fred Edgar22_fred_edgar@HOTMAIL.COM30_Tue, 9 Jan 2001 17:44:22 -0600479_- Greetings, We are trying to access data on AIX from an OS/390 application. Both are V6. When trying to bind a pkg. I get: SQLCODE = -30082 SQLSTATE = 08001 SQLERRMT = 17 UNSUPPORTED FUNCTION

When I try to do a select using a 3-part name I get: DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00D31057, TYPE OF RESOURCE 00001004, AND RESOURCE NAME SAMPLE.GRAVY.RBPAP97F DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE [...] 10484 18 35_Re: CA Datacom/DB to DB2 Conversion20_Rick (Dino) Belfanti17_dino@BELFANTI.COM30_Tue, 9 Jan 2001 17:46:56 -0600409_- Wayne, There is a successfully used software/services package available through ISI Australia, called 2BDB2. Contact Peter Richards(Peter_richards@attglobal.net) or visit the ISI website, www.isi.com.au, and click on products. Conversion requires no changes to application code except in exceptional circumstances. It has been successfully used by a number of clients. Ciao 4 Nao, Rick (Dino) Belfanti [...] 10503 127 28_Re: How to clean-up DB2 Log?11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU31_Wed, 10 Jan 2001 11:10:17 +1100416_- Endy,

as Bill and Scott have pointed out, db2uext2 is a program, not a command. However, if you decide to stick to the manual approach rather than using the user exit, yes, there is a danger zone: if you delete any log files from the "first active log file" onwards, you will be in serious trouble. You can find out the first active log file in the database configuration (do a "get db cfg for xxxx"). [...] 10631 125 132_=?iso-8859-1?Q?Re:______Re:_____________=BB=D8=B8=B4:______Re:_How_to_imp?= =?iso-8859-1?Q?rove_the_operation_of_insert_and_update?=5_elibs17_elibs@NETEASE.COM31_Wed, 10 Jan 2001 09:13:43 +0800589_iso-8859-1 Hi,John, Thank you for your kind instructions . As to my testing system , the volume of the data in the table is the same to the production system,since we always pour the production data to it.But I think I should have checked it for sure. On the other hand,the hardware is not the same.tesing system is R45 while production system is R35.Memory is less in testing system.And generally,the disks in production system are sperior to testing system.Well,I think I should monitor them all using CANDLE while testing. And I will check the table definition on the two systems. [...] 10757 53 31_Re: DB2 Stored Procedure - JAVA12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 9 Jan 2001 17:49:56 -0800571_us-ascii I don't believe you need JAVA Visual Age for OS/390. But you must currently have a JAVA Compiler for the OS/390 as all JAVA stored procedures must be compiled. --- "Mackey, Glenn" wrote: > Hi, > > I have a question about the environment required to > implement JAVA stored > procedures on the OS/390 environment. > > From what I have read it states that the WLM-managed > environment does not > support the Java Virtual Machine (JVM). The Java > bytecode has to be compiled > which requires the purchase of extra software. > > 1. Is [...] 10811 139 33_IDUG Orlando Conference -- Repost14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Tue, 9 Jan 2001 17:45:14 -0800454_- This is a repost for IDUG.

Dear DB2 Professional,

Mark your calendars for the premier user-run DB2 event in North America:

IDUG 2001 - North America DB2 Powered by You May 13-17, 2001 Orlando Marriott World Center Orlando, Florida, USA

With four full days of education and more than 140 in-depth sessions IDUG 2001 - North America will empower you with solutions to help you work smarter, faster, and more efficiently. [...] 10951 29 61_SEQPRES=SEQ or SEQPRES=BYPASS what should be the best option?12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM30_Tue, 9 Jan 2001 22:43:04 -0600517_- Hi Folks:



I'm wondering if setting the SEQPRES=SEQ in the DSNZPARM , I would gain better performance allowing Sequential prefetch read data from cache. Actually , all our devices are Shark (ESS) , and the ESS white papers said that you would gain better performance without change any db2 parameter but with this devices supporting more than 16 Gig of cache, some improvements must comes with it even sequential prefetch. I would like to hear your comments about this issue. Thanks and regards, [...] 10981 85 55_Partitioning a large tablespace - DB2 UDB V6 for OS/3907_Sri Sri20_sri_db2l@HOTMAIL.COM30_Tue, 9 Jan 2001 22:34:38 -0600597_- Dear List,

We are trying to re-partition an existing Tablespace which has the following statistics. We are on DB2- UDB V6 for OS/390

1) We have 256 million records in this table with 10 partitions. 2) Each partition containing around 25 million records. 3) Currently we do not have any NPI on it. 4) Data is stored from 1997 .... onwards. 5) Programs do not update records. 6) Deletes are possible on this table. 7) This is a Child table. 8) Currently TS occupies 25,000 cylinders, Cluster IX occupies 15,000 cylinders. (No NPIs) 9) Currently 100 million records are inserted [...] 11067 36 51_Interface Between Web Browser and OS/390 Using JDBC21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Wed, 10 Jan 2001 17:22:48 +0800421_us-ascii Dear all,

I am one of the DB2 DBA member in a bank. We are using the DB2 V5 in our OS/390 environment. I would like to study the way to access data from DB2 tables in OS/390 thru Web Browser using Java program. However, I have no idea about the appreciate softwares for the setup. Would you please let me have some input about the interface, the following are some of my queries for your referenece: [...] 11104 49 50_Re: Trying to access UDB on AIX from UDB on OS/39012_Travis, John27_John.Travis@CAPGEMINI.CO.UK31_Wed, 10 Jan 2001 09:08:25 -0000488_- Fred, 3-part names are only allowed when accessing other DB2 for OS/390 subsystems. John

-----Original Message----- From: Fred Edgar [mailto:fred_edgar@HOTMAIL.COM] Sent: 09 January 2001 23:44 To: DB2-L@RYCI.COM Subject: Trying to access UDB on AIX from UDB on OS/390



Greetings, We are trying to access data on AIX from an OS/390 application. Both are V6. When trying to bind a pkg. I get: SQLCODE = -30082 SQLSTATE = 08001 SQLERRMT = 17 UNSUPPORTED FUNCTION [...] 11154 252 136_Re: =?iso-8859-1?Q?Re:______Re:_____________=BB=D8=B8=B4:______Re:_How_to_imp?= =?iso-8859-1?Q?rove_the_operation_of_insert_and_update?=10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 10 Jan 2001 03:49:00 -0600421_- Larry,

Your mail software appears to put your Emails into some form that the list server software cannot interpret correctly (or something like that!). I have noticed that this occasionally happens to other contributors to the list.

Does anybody out there (or maybe the list administrators) know the cause/solution?



On Wed, 10 Jan 2001 09:13:43 +0800, elibs wrote: [...] 11407 60 55_Re: Interface Between Web Browser and OS/390 Using JDBC10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 10 Jan 2001 11:09:31 +0100538_us-ascii Hi, as i'm just now installing JDBC/SQLJ for DB2 for S/390......see replies below

HTH

Max Scarpa





I am one of the DB2 DBA member in a bank. We are using the DB2 V5 in our OS/390 environment. I would like to study the way to access data from DB2 tables in OS/390 thru Web Browser using Java program. However, I have no idea about the appreciate softwares for the setup. Would you please let me have some input about the interface, the following are some of my queries for your referenece: [...] 11468 32 65_Re: SEQPRES=SEQ or SEQPRES=BYPASS what should be the best option?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 10 Jan 2001 11:54:20 +0100341_us-ascii In my opinion if you have a disk cache > 1 Gb (it's common now) it's better to set SEQCACH=SEQ, this signals to disk

controller to use caching for seq. prefetch and dyn. prefetch, which is different from controller 'normal' logic:

Cache hit ratio < 10% inhibit cache " " 10 to 55% cache track " " > 55% cache cyl [...] 11501 15 52_Cathy L Peck/ISG/CORP/Highmark is out of the office.10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Wed, 10 Jan 2001 06:04:56 -0500389_us-ascii I will be out of the office starting 01/10/2001 and will not return until 01/15/2001.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 11517 40 35_Re: CA Datacom/DB to DB2 Conversion15_Wayne Arrington33_WAYNE.ARRINGTON@CUSTOMS.TREAS.GOV31_Wed, 10 Jan 2001 06:28:45 -0500787_US-ASCII Hey Rick, We tested the product 2BDB2 in '99', Peter and his guys from down under are sharp. thanks for the feed back.

Wayne U.S. Customs Service



______________________________ Reply Separator _________________________________ Subject: Re: CA Datacom/DB to DB2 Conversion Author: "DB2 Data Base Discussion List" at smtplink Date: 1/9/01 6:46 PM



Wayne, There is a successfully used software/services package available through ISI Australia, called 2BDB2. Contact Peter Richards(Peter_richards@attglobal.net) or visit the ISI website, www.isi.com.au, and click on products. Conversion requires no changes to application code except in exceptional circumstances. It has been successfully used by a number of clients. Ciao [...] 11558 13 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39015_Walter Janissen27_walter.janissen@VICTORIA.DE31_Wed, 10 Jan 2001 06:10:05 -0600416_- What do you think about that

Take the MOD-function from year, say 3 times the number of partitions that you need to store data for 1 year, then you don't need a NPI.

================================================ 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. 11572 96 19_Re: Catalog entries17_Polley, Mike (M.)16_mpolley@FORD.COM31_Wed, 10 Jan 2001 07:50:28 -0500338_iso-8859-1 Yes, In order to bind application programs, they must exists within what is called a package. Then the collection id would be named from that particular package, and still be out of sync with prod because of the collection id. So, the only way to get them in sync is to move the test package to prod. Thanks for the help. [...] 11669 62 34_Re: Automated DB2 Restart Question12_Sue Janowitz18_SJanowitz@NEFN.COM31_Wed, 10 Jan 2001 07:53:24 -0500408_us-ascii We use the automation product OPSMVS. We have a message rule that kicks off if we get a DSNV086E message (DB2 ABNORMAL TERMINATON). It triggers a REXX that does the following:

Checks to see if we've had more than 2 abends (for the same subsystem) in 10 minutes (an arbitrary number we decided on) - if so, don't try to start up again, but send message to console to contact DB2 support. [...] 11732 75 24_Need Help on Query!!!!!!20_Ravinder Rao Machadi22_machadi_rrao@YAHOO.COM31_Wed, 10 Jan 2001 05:13:45 -0800620_us-ascii Hi Studds,

We have the situation over here in one of our development team. we have an Organisation & Organisation_hierarchy tables. In Organisation_hierarchy table we are maintaing a hierarchy of 15 organisations with level no.s

The hirarchy table is populated as follows...

ORG_ODE Level H1 H2 H3 H4 H5 H6.. ------------------------------------------------------------ Mk-1 1 Mk-1 Mk-2 2 Mk-1 Mk-3 3 Mk-2 Mk-1 Mk-4 4 Mk-3 Mk-2 Mk-1 Mk-5 5 Mk-4 Mk-3 Mk-2 Mk-1 Mk-6 6 Mk-5 Mk-4 Mk-3 Mk-2 Mk-1 Mk-7 7 Mk-6 Mk-5 Mk-4 Mk-3 Mk-2 Mk-1 - - - For example in the above table the Mk-5 ‘s higher [...] 11808 177 19_Re: Catalog entries14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 10 Jan 2001 08:15:56 -0500374_iso-8859-1 I think what Dallas is saying is that once you move your programs to production you can bind them to your production DB2 subsystem(using your PROD bind parameters/collections) AND subsequently to your test DB2 subsystem(s) (using TEST bind parms/collections) all while using the same DBRM.

Example:

//DBRMLIB DD DSN=PROD.DBRMLIB //SYSTSIN DD * [...] 11986 217 19_Re: Catalog entries14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 10 Jan 2001 08:27:03 -0500556_iso-8859-1 Original example may have been misleading...see below for clarification

-----Original Message----- From: Trometer, Scott Sent: Wednesday, January 10, 2001 8:16 AM To: 'DB2 Data Base Discussion List' Subject: RE: Catalog entries



I think what Dallas is saying is that once you move your programs to production you can bind them to your production DB2 subsystem(using your PROD bind parameters/collections) AND subsequently to your test DB2 subsystem(s) (using TEST bind parms/collections) all while using the same DBRM. [...] 12204 56 27_Data Archive for DB2 OS/39012_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM31_Wed, 10 Jan 2001 08:32:38 -0500326_iso-8859-1 Hello,

We are running DB2 V6.1 for OS/390 and have a need to review Data Archiving processing as our data continues to grow and the application has no data archival procedures in place.

Through research of the archives and vendor website, I was wondering if anyone has made use of the following: [...] 12261 169 19_Re: Catalog entries17_Polley, Mike (M.)16_mpolley@FORD.COM31_Wed, 10 Jan 2001 08:38:15 -0500624_iso-8859-1 Ok thanks, however, no binds are run outside of Change Man in prod or test for applications. To bind in test the program must be in a package. Then the collection id is named from this package name. Change Man is set to use this package name. Once the package has moved to prod it's gone and cannot be recreated in test. We're long past this problem now, but thanks for your good suggestions, and clarifications. Hold the others for our next problem. thanks -----Original Message----- From: Scott Trometer [mailto:scott.trometer@RCI.COM] Sent: Wednesday, January 10, 2001 8:16 AM To: DB2-L@RYCI.COM Subject: [...] 12431 34 32_Can't update organization table.13_Henry J. Cobb14_hcobb@SLIP.NET31_Wed, 10 Jan 2001 08:11:27 -0600434_- > From: Ravinder Rao Machadi > Subject: Need Help on Query!!!!!!

> ORG_ODE Level H1 H2 H3 H4 H5 H6.. > ------------------------------------------------------------ > Mk-1 1 Mk-1 > Mk-2 2 Mk-1 > Mk-3 3 Mk-2 Mk-1

> this is the situation. We are not able to write a efficient > query to perform the entire updation. If any one of you > have the alternative solution, please send it accross. [...] 12466 87 54_Re: SQL Procedures Processor (?) in WLM-managed SP JCL11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Wed, 10 Jan 2001 09:27:20 -0500444_iso-8859-1 I admit that I have no experience here but it seems to me that to get this to work you would have "shared" and "not shared" Stored procedure procs. i.e. DB2GWLM1 would attach to Group DB2G which contains DB2A and DB2B. The "shared" procs should be connecting to the GROUP name, if it is possible for that stored procedure to run on any DB2 in that data sharing group. As I understand it CAF and RRS attach support Group attach. [...] 12554 100 35_Re: Restarting a failed Load Resume15_Jackson Reavill18_damcon2@US.IBM.COM31_Wed, 10 Jan 2001 09:33:49 -0500312_us-ascii Raymond,

I missed the initial note you sent out, but if it died in the build phase for the indexes then just reset the pending flags and rebuild your indexes. If it was in the load phase then recovery is basically it unless you have an unload to load back in and then rerun the load resume. [...] 12655 160 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39010_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 10 Jan 2001 08:43:33 -0600360_- Walter,

Please could you elicidate your proposal a little for the benefit of numbskulls like me please?

Whatever you are proposing, obviating the need to create the NPI is definitely a step in the right direction. Sri talks about a 'control table' as a maintenance overhead: the new NPI is going to be a real maintenance pain in the neck. [...] 12816 29 31_SQL for Self-Referencing Tables13_Glenn Andrews16_gandrew3@CSC.COM31_Wed, 10 Jan 2001 10:15:17 -0500566_us-ascii With all of the enhancements to SQL, is there any convenient and well-performing SQL to process self-referencing tables? For example, the DB2 Samples include the DEPT table:

DEPTNO CHAR(3) DEPTNAME VARCHAR(36) MGRNO CHAR(6) ADMRDEPT CHAR(3) LOCATION CHAR(16)

With one SQL query, I would like to return all subordinate departments for any given department. The use of multiple subqueries can do this if the number of levels is constant. I would like to know if there one SQL statement which will work without knowing the number of levels. [...] 12846 50 35_Re: SQL for Self-Referencing Tables14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Wed, 10 Jan 2001 09:33:50 -0600638_iso-8859-1 DB2 for AIX, NT, etc has recursive queries.

For a good discussion of their strengths and weaknesses, look at the SQL Cookbook, available for free from http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM

From Edward Vassie...



-----Original Message----- From: Glenn Andrews [mailto:gandrew3@CSC.COM] Sent: 10 January 2001 15:15 To: DB2-L@RYCI.COM Subject: SQL for Self-Referencing Tables



With all of the enhancements to SQL, is there any convenient and well-performing SQL to process self-referencing tables? For example, the DB2 Samples include the DEPT table: [...] 12897 24 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39015_Walter Janissen27_walter.janissen@VICTORIA.DE31_Wed, 10 Jan 2001 09:29:27 -0600317_- John

I think, I thought a little bit too quick. What I've meant was something like you proposed. With MOD I mean the MODULO-function in version 6.

MOD(1998, 4) = 2

But what was wrong was to multiply with number of partitions per year, because that will always yield in the same partition. [...] 12922 182 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39010_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 10 Jan 2001 09:38:39 -0600287_- Of course, the year modulus 4 is a more elegant solution than the highest decimal place of the result of division, so:

1996 / 4 // 0 1997 / 4 // 1 1998 / 4 // 2 1999 / 4 // 3 2000 / 4 // 0 etc.

which now I think about it, was maybe what Walter was talking about.... [...] 13105 41 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39010_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 10 Jan 2001 09:52:10 -0600359_- Walter,

This Email crossed in the post with my last Email!

Yes, although our solutions differ in detail, I think that in essence they are the same. I am sure that if Sri likes this approach, and decides to adopt it then he/she (sorry Sri, I can't deduce your gender from your name) will adapt the finer points according to his/her taste. [...] 13147 39 37_Priority of Cics-DB2 Thread Subtasks.32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Wed, 10 Jan 2001 18:12:55 +0200624_- Hello List,

Our CICS Sysprogs prepared the statement below. We could not find enough info for this topic.

Do you want to share your comments, ideas, anything about that ?

We need to specify different dispatch priorities (or Service Classes) for Cics-DB2 Thread Subtasks arising from the same Cics region. We have been investigating the PRIORITY parameter of the Cics DB2ENTRY definitons for a while. It enables us to specify a dispatching priority relative to the bearing Cics region's, HIGH, EQUAL or LOW. We have tried to find more detailed information about how HIGH or how LOW a priority can [...] 13187 57 41_Re: Priority of Cics-DB2 Thread Subtasks.14_Picou, Michael33_picou.michael@MAIL.DC.STATE.FL.US31_Wed, 10 Jan 2001 11:29:40 -0500376_iso-8859-1 I was told in a class I took, that when PRIORITY=HIGH is coded the priority of the request is at the CICS level and CICS priority is pushed down.

-----Original Message----- From: Cuneyt Goksu (Garanti Teknoloji) [mailto:CuneytG@GARANTI.COM.TR] Sent: Wednesday, January 10, 2001 11:13 AM To: DB2-L@RYCI.COM Subject: Priority of Cics-DB2 Thread Subtasks. [...] 13245 56 62_Re: Should we use DB2 or Oracle for our very large database???14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Wed, 10 Jan 2001 10:27:27 -0600461_iso-8859-1 Bob:

I see you have already gotten a bunch of answers to your question from us "DB2 bigots" on the DB2 list. Let me discuss a couple of issues I have not yet seen discussed here. First of all, I hope you posted this question to a least one Oracle newsgroup or mailing list, too. Although I agree that DB2 is probably your best choice (given the small amount of information given), I also think it is good to hear both sides of the issue. [...] 13302 67 50_Re: Trying to access UDB on AIX from UDB on OS/39013_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 10 Jan 2001 10:28:54 -0600472_US-ASCII John, I DB2 V6 you can access remote non OS/390 databases with 3 part names. I do this all the time to DB2 UDB on NT. Kurt

>>> John.Travis@CAPGEMINI.CO.UK 01/10/01 03:08AM >>> Fred, 3-part names are only allowed when accessing other DB2 for OS/390 subsystems. John

-----Original Message----- From: Fred Edgar [mailto:fred_edgar@HOTMAIL.COM] Sent: 09 January 2001 23:44 To: DB2-L@RYCI.COM Subject: Trying to access UDB on AIX from UDB on OS/390 [...] 13370 43 28_SQL question matching cols ?19_Briggs, N. - Neil -25_N.Briggs@CANON-EUROPA.COM31_Wed, 10 Jan 2001 17:38:15 +0100409_ISO-8859-1 Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no matching columns for the following SQL, I was expecting 1 matching column on M_CODE:-

SELECT M_CODE ,M_CODE_2 FROM SQLUSRA.AMAM24_CONV WHERE M_CODE = '2-80031-02 ' result should be 1 row returned.

SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM RECORD LENGTH : 75 [...] 13414 66 32_Re: SQL question matching cols ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 10 Jan 2001 11:40:15 -0500519_iso-8859-1 I think matchcols is based on the leading matching columns. If the pred was on REGISTRAR_COMPANY, matchcols would be 1. If it was on REGISTRAR_COMPANY and M_CODE, matchcols would be 2. Instead of a matchcols you will get 'index screening'.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Briggs, N. - Neil - [mailto:N.Briggs@CANON-EUROPA.COM] Sent: Wednesday, January 10, 2001 11:38 AM To: DB2-L@RYCI.COM Subject: SQL question matching cols ? [...] 13481 18 39_end of service dates - db2 connect v5.x11_David Ayers24_david.ayers@HIGHMARK.COM31_Wed, 10 Jan 2001 11:47:42 -0500521_us-ascii ? We are in the process of upgrading our DB2 connect gateways from v5.2 to v6.1. I was under the impression that DB2 connect v5.2 would no longer be a supported release after 1/31/2001. Is this date correct or has support been extended until 6/30/2001.

thank -dfa

================================================ 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. 13500 24 21_Data Warehouse Center15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Wed, 10 Jan 2001 11:47:12 -0500370_iso-8859-1 Are there any Data Warehouse Center users out there? My error is: DWC07356E An agent's processing of a command of type "importColumns" failed for edition "0" of step "?". RC = 7356 RC2 = 8615

Explanation: The agent encountered an error while it was processing. User Response: Check the secondary return code (RC2) and take the recommended action. [...] 13525 69 32_Re: SQL question matching cols ?13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 10 Jan 2001 08:46:32 -0800535_iso-8859-1 If M_CODE were the 'first' column of your index, it would have given you the result you are expecting.

Thanks.

Ashish.

-----Original Message----- From: Briggs, N. - Neil - [SMTP:N.Briggs@CANON-EUROPA.COM] Sent: Wednesday, January 10, 2001 8:38 AM To: DB2-L@RYCI.COM Subject: SQL question matching cols ?

Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no matching columns for the following SQL, I was expecting 1 matching column on M_CODE:- [...] 13595 20 32_Re: SQL question matching cols ?0_19_Tim.Lowe@STPAUL.COM31_Wed, 10 Jan 2001 10:51:36 -0600491_us-ascii Neil, Since your only index does not start with the only column that you have a predicate for, then DB2 will need to scan the entire index to satisfy your request. Therefore, matchcols must be 0.

I hope this helps.

Thanks, Tim

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 13616 66 32_Re: SQL question matching cols ?10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 10 Jan 2001 10:47:21 -0600281_- DB2 cannot carry out a matching index scan in this case, as you have not supplied the high order column of the key. As the value of REGISTRAR_COMPANY is unknown, the best that DB2 can do is scan the entire index searching for the supplied value of M-CODE. Your options are: [...] 13683 48 50_Re: Trying to access UDB on AIX from UDB on OS/39013_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 10 Jan 2001 11:00:04 -0600333_US-ASCII Fred, Location in sysibm.locations is the database name on AIX. HTH Kurt

>>> fred_edgar@hotmail.com 01/09/01 05:44PM >>> Greetings, We are trying to access data on AIX from an OS/390 application. Both are V6. When trying to bind a pkg. I get: SQLCODE = -30082 SQLSTATE = 08001 SQLERRMT = 17 UNSUPPORTED FUNCTION [...] 13732 113 43_Re: end of service dates - db2 connect v5.x14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 10 Jan 2001 09:02:00 -0800438_iso-8859-1 Dave, DB2 Connect 6.1 is withdrawn eff June 2001. I don't believe V5.2 support has been extended. Keep in mind that V8 is probably not that far off. Regards, Phil



Find announcement letters and withdrawals at www.ibm.com/news and search on your product of interest.

-----Original Message----- From: David Ayers To: DB2-L@RYCI.COM Sent: 1/10/01 8:47 AM Subject: end of service dates - db2 connect v5.x [...] 13846 73 32_Re: SQL question matching cols ?0_27_jim.leask@RS-COMPONENTS.COM31_Wed, 10 Jan 2001 16:47:27 -0000533_iso-8859-1 Neil,

For the second column to be matched, you must include a predicate for the first column e.g. REGISTRAR_COMPANY = '01'

Jim. -----Original Message----- From: Briggs, N. - Neil - [mailto:N.Briggs@CANON-EUROPA.COM] Sent: 10 January 2001 16:38 To: DB2-L@RYCI.COM Subject: SQL question matching cols ?



Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no matching columns for the following SQL, I was expecting 1 matching column on M_CODE:- [...] 13920 22 23_DB2 PM Release Question14_Richard Arnold24_Richard.Arnold@CHASE.COM31_Wed, 10 Jan 2001 11:04:07 -0600308_us-ascii Hello list,

We are running DB2 for OS/390 V5. In next month we will upgrade to V6.

We currently have DB2 PM V5.

Does DB2 PM V5 also work with DB2 for OS/390 V6? I am about to order a set of manuals, and want to get the current ones.

Thanks in advance for your help. [...] 13943 20 40_Encryption/Decryption Process for DB2 V67_A. Ward34_Angela_B_Ward@MAIL.DBF.STATE.FL.US31_Wed, 10 Jan 2001 12:21:32 -0500515_us-ascii We are running DB2 for OS/390 V6. We currently have a need to encrypt certain columns of data. Other than a fieldproc, is there a way to use triggers? We could call the encryption routine upon an update or insert but how could would decrypt the data upon a Select? I've read many negative things on this list regarding fieldprocs. Is it IBM's intent to phase these out? Is there something in the near future that will allow us to do encryp/decrypt without fieldprocs? Thanks for your input. A.B. Ward [...] 13964 44 31_JDBC/SQLJ error at runtime :-((10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 10 Jan 2001 18:27:24 +0100341_us-ascii Hi folks...I hate this, but Houston, we have a problem with JDBC/SQLJ drive.

We installed some days ago the JDBC driver type-1 and all worked fine.

We decided to install the SQLJ support and the new JDBC type-2 driver. We had many problems but all were solved

and (I think) the apply of the APAR ended ok. [...] 14009 25 62_Re: Should we use DB2 or Oracle for our very large database???10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 10 Jan 2001 18:37:49 +0100357_us-ascii Hi Craig, Well said.

I left a company 2 years ago and, after 3+ years (and after some millions of $$$$) they do not have

completed the migration from S390 to UNIX/ORACLE with the same performances and reliability.

And the cost of toolz to manage/admin 100+ oracle db in a distributed environment is becoming unacceptable. [...] 14035 48 27_Re: DB2 PM Release Question0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 10 Jan 2001 13:01:18 -0500528_us-ascii No, We found that we had to upgrade DB2PM to V6 when we went to DB2 V6.1

R/..Ed







Richard Arnold @RYCI.COM> on 01/10/2001 12:04:07 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DB2 PM Release Question



Hello list,

We are running DB2 for OS/390 V5. In next month we will upgrade to V6. [...] 14084 57 25_questionable results with17_John P. Schulting29_john.schulting@CUNAMUTUAL.COM31_Wed, 10 Jan 2001 12:53:41 -0600477_us-ascii I received the following question from one of our more experienced developers and was wondering if anyone else had come across something similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a column equal to a value acquired via a subselect. It didn't work quite the way I expected so I was wondering if there was any additional information or safety tips that could be provided in its use. [...] 14142 50 27_Re: DB2 PM Release Question0_28_charles.t.schopf@VERIZON.COM31_Wed, 10 Jan 2001 14:52:37 -0500475_us-ascii No, you'll need to upgrade PM to V6. If you use QMF that also needs to be upgraded.

Chuck Schopf Verizon Communications 215-466-2137







"Richard Arnold" @RYCI.COM> on 01/10/2001 12:04:07 PM

Please respond to "DB2 Data Base Discussion List"

Sent by: "DB2 Data Base Discussion List"



To: DB2-L@RYCI.COM cc: Subject: DB2 PM Release Question [...] 14193 82 29_Re: questionable results with11_David Nance16_DWNance@FHSC.COM31_Wed, 10 Jan 2001 14:54:15 -0500500_US-ASCII You are updating many More rows than you care to, due to not qualifying what rows in table a to update. I.E.: UPDATE TABLEA A SET A.COL1 = (SELECT B.COL1 FROM TABLEB B WHERE A.COL2 = B.COL2) WHERE A.C? =?????



Dave Nance First Health Services, Corp. (804)527-6841

>>> john.schulting@CUNAMUTUAL.COM 01/10/01 01:53PM >>> I received the following question from one of our more experienced developers and was wondering if anyone else had come across something similar: [...] 14276 86 29_Re: questionable results with16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Wed, 10 Jan 2001 15:08:22 -0500440_us-ascii John,

It would appear DB2 is functioning as requested by the UPDATE statement. In other words, there is no predicate coded on the first UPDATE statement (i.e., which would explicitly limit the update of COL1 for only qualified rows). Hence, mass update of COL1 for all rows.

Rick Molera











"John P. Schulting" on 01/10/2001 01:53:41 PM [...] 14363 83 27_Re: DB2 PM Release Question0_17_sjvagnier@AEP.COM31_Wed, 10 Jan 2001 15:03:42 -0500520_us-ascii Richard, we are running DB2 V6 and we did not have to upgrade our QMF environment which is presently at QMF V3.3.0.

Regards,

Steve Vagnier American Electric Power











charles.t.schopf@VERIZON.COM@RYCI.COM> on 01/10/2001 02:52:37 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: DB2 PM Release Question [...] 14447 72 55_Re: Interface Between Web Browser and OS/390 Using JDBC13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Wed, 10 Jan 2001 14:12:36 -0600649_iso-8859-1 See below.

-----Original Message----- From: Li, Chak Lung Dominic [ mailto:seddba@HK.SUPER.NET ] Sent: Wednesday, January 10, 2001 4:23 AM To: DB2-L@RYCI.COM Subject: Interface Between Web Browser and OS/390 Using JDBC



Dear all,

I am one of the DB2 DBA member in a bank. We are using the DB2 V5 in our OS/390 environment. I would like to study the way to access data from DB2 tables in OS/390 thru Web Browser using Java program. However, I have no idea about the appreciate softwares for the setup. Would you please let me have some input about the interface, the following [...] 14520 52 54_Pipeline Newsletter for DB2 and Oracle - January Issue9_Cam White20_cwhite@REVEALNET.COM31_Wed, 10 Jan 2001 15:21:09 -0500588_iso-8859-1 Click on the newsletter link below and register for a chance to win a FREE full conference registration to IDUG 2001.

The January, 2001 Issue of the Pipeline Newsletter is now available for DB2 and Oracle professionals.

http://www.revealnet.com/newsletter-v2/newsletter_0101.htm

Feature articles include:

- "Knowing When to Rebuild Oracle Indexes" - by Brian Peasland - "Tuning DB2 Data Sharing Performance" - by Robert Catteral - "Using Oracle 8i's ROLLUP Feature" by Vijays, Datacons - "Useful Java Methods for PL/SQL" - by Steven Feuerstein [...] 14573 84 55_Re: Interface Between Web Browser and OS/390 Using JDBC14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Wed, 10 Jan 2001 14:29:09 -0600490_iso-8859-1 -----Original Message----- From: McHugh, Gerry [mailto:MchughG@DIVINVEST.COM] Sent: Wednesday, January 10, 2001 2:13 PM To: DB2-L@RYCI.COM Subject: Re: Interface Between Web Browser and OS/390 Using JDBC



See below.

-----Original Message----- From: Li, Chak Lung Dominic [ mailto:seddba@HK.SUPER.NET ] Sent: Wednesday, January 10, 2001 4:23 AM To: DB2-L@RYCI.COM Subject: Interface Between Web Browser and OS/390 Using JDBC [...] 14658 112 55_Re: Interface Between Web Browser and OS/390 Using JDBC12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 10 Jan 2001 12:40:18 -0800591_us-ascii There are some new redbooks on WEBSPHERE that are very useful. See WWW.REDBOOKS.IBM.COM and search on WEBSPHERE. --- "McHugh, Gerry" wrote: > See below. > > -----Original Message----- > From: Li, Chak Lung Dominic [ > mailto:seddba@HK.SUPER.NET > ] > Sent: Wednesday, January 10, 2001 4:23 AM > To: DB2-L@RYCI.COM > Subject: Interface Between Web Browser and OS/390 > Using JDBC > > > Dear all, > > I am one of the DB2 DBA member in a bank. We are > using the DB2 V5 in > our OS/390 environment. I would like to study the > [...] 14771 71 50_Re: Trying to access UDB on AIX from UDB on OS/39012_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US31_Wed, 10 Jan 2001 12:33:51 -0600420_US-ASCII Fred, what are your Bind options on your BIND PACKAGE? some bind options are not valid on UDB AIX V6. For example, VALIDATE(RUN) is not a valid option. I think V7 documentation indicates that the VALIDATE(RUN) will be supported. Although, we should receive the elusive -4930 SQL Code. Nope, you won't find it in any SQL Code manual. I found it in an IBM Redbook "DB2 for MVS Connections with AIX and OS/2" [...] 14843 25 26_regarding stored procedure17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Wed, 10 Jan 2001 17:05:10 -0600540_- Hi Studds,

I need an help with stored procedure..... in our shop we are using DB2 V6 on OS390. i red about stored procedure in the manual but i am getting confused with that... we need to write a store procedure using cobol...... can anybody explain how write a stored procedure using cobol.... on os390 to execute a application. what is the advantage using this stored procedure???? and how would i call the stored procedure to execute the application???? if somebody has a example of stored procedure it will be helpful.... [...] 14869 126 29_Re: questionable results with17_John P. Schulting29_john.schulting@CUNAMUTUAL.COM31_Wed, 10 Jan 2001 15:53:35 -0600518_us-ascii I guess I should be a little clearer with my question. The fact that all rows on TableA are updated makes sense. What seems odd, however, is that for those rows where there is not a matching row on TableB, the column in TableA is not being set to null. The column is instead being set to the value that was assigned to the column for the previous row. For example: rows 1 and 2 have matching rows in TableB so their values are set properly. Row 3 does not have a matching row in TableB, its value is set [...] 14996 56 35_Re: Restarting a failed Load Resume15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 09:24:42 +1100528_- Jay,

Thanks for the depressing confirmation. I was specifically interested in failed Load Resume jobs that fail in the load phase. Looks like I'd be up for a recovery. Although someone out there suggested as an alternative to basically rebuild (gee, I'm SO getting the hang of the new V6 terminology) the indexes at the point of load phase failure and rerun the Load Resume, ignoring the resultant duplicates and SYSERR file. Depends on how far into the load you were, I guess, and whether or not you can go back. [...] 15053 87 32_Re: SQL question matching cols ?12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 09:31:12 +1100541_- Neil, in the case of the supplied Query, this would be because the M_CODE column is not the first Column in the index ( I would presume). For match on the index it needs to the first column (of course if you do that other queries may suffer)

Given that there is only the 1 unique index DB2 has 2 choices. Non-matching index scan ~ Scan the entire index find the matching RIDS and get the data from the TableSpace. OR . A TableSpace scan. Given AccessType=I was chosen you've got the non-matching Index scan. This is not as bad [...] 15141 43 57_Senior DB2 Database Administrator - Boston, Massachusetts0_18_jtulman@MAAINC.COM31_Wed, 10 Jan 2001 17:47:06 -0500712_us-ascii Members of the DB2-ListServer,

I am currently marketing for a Senior Database Administrator for a client of mine based in the Boston, Massachusetts area. The client is looking for the DBA to manage the database function utilizing the newly installed, state of the art DB2/Sun databases. This individual will design, develop, implement, and maintain databases, and advise member companies and junior administrators in these functions. He or she will also be responsible for multi-platform administration, security, database design, loading data, and maximizing system availability while minimizing response times. The DBA will also address the technical needs of team members. Qualifications [...] 15185 33 48_DB2-established Stored Procedures using MQSeries11_Lorenz, Don15_dlorenz@KCC.COM31_Wed, 10 Jan 2001 17:22:45 -0600597_iso-8859-1 Hi List,

Can DB2-established stored procedures via call attach (DSNALI) be used to implement asynchronous processing from (or to) a stored procedure using MQSeries? For example, we have a business user that would like to queue a message and pass some DB2 data for asynchronous delivery using the MQSeries product. I believe that this can be done via RRS with WLM-established stored procedures, but I can' t find anything in the V5 doc that shows this being done for DB2-established SP's...........Has anyone done something like this using DB2-established stored procedures? [...] 15219 28 13_QMF standards16_Alison Pelletier26_alison_pelletier@YAHOO.COM31_Wed, 10 Jan 2001 15:45:37 -0800362_us-ascii Hello everyone. I've been tasked with writing standards/procedures for administering QMF in our production environment. I have many ideas and past experience, but it would sure help if I had a head- start and I don't want to miss anything. It's been givin' out carte blanche before my reign and it's killing us! Anyone willing to share their doc.? [...] 15248 37 26_More design advice, please15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 11:06:10 +1100630_- Just simple (and brief) this time.

I'm changing some read-only tables to be partitioned and introducing a new partitioning key column. Sound familiar? Anyway, some of the tables have >1 index. Question: should I add the new partitioning key column to the front of these indexes too?

My feeling is no. It doesn't have/need it now, why add it? The partitioning key value (which has values 1-9) will be hidden from view (pun intended) via a view selecting all the other pre-existing columns 'where partitioning_column in 1,2,3...9' which gives matchcols=2 when searching on a specific value for the (now) second [...] 15286 69 17_Re: QMF standards15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 11:16:37 +1100552_- Hi Alison,

Stuff you might want to consider:

1) Set up fairly low QMF Governor limits for interactive use, but fairly high for QMF Batch. 2) Publish standard JCL for running QMF in batch and/or make sure the Batch QMF command works. 3) Create a RACF group which is the only ID that has the ability to run the QMF plan. 4) Establish a 'register for QMF' process that: a) adds the requester's ID to the RACF group above b) create a database and tablespace for their saved data c) update their QMF profile to use it! 5) Sweep through [...] 15356 30 47_Db2 Connect and UDB/OS390 version compatibility10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 11 Jan 2001 11:23:03 +1000423_ISO-8859-1 Hi everybody,

DOes anybody know where I can get information about the compatibility between DB2 Connect versions and UDB for OS/390 versions ?

We r currently on Connect v5 and DB2/OS390 v5, planning to migrate to DB2/OS390 v6. Wd this necessiate an upgrade to our Connect version also ? If yes, is it better to go to Connect v6 or v7 ? (I see that Connect v6 is available only till June 2001) [...] 15387 118 30_Re: regarding stored procedure13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 10 Jan 2001 20:23:07 -0500317_iso-8859-1 Hi Ravi, Who is Studds?

Below is a very very basic Stored procedure. All it does is accept a parm from the caller and insert a row into a table using the data from this parm. There are lots of things missing: debugging code, error handling, return communication to the caller.... But it works. [...] 15506 48 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/3907_Sri Sri20_sri_db2l@HOTMAIL.COM31_Wed, 10 Jan 2001 22:10:42 -0600588_- Dear John, Walter

Thanks for your excellent response on partition of the large tablespace.

I have few doubts :

It may happen with my client that , they may change their decision about how many years before archiving, and i may end-up in dropping the tablespace again

After realising the above fact, by further investigation, i found A create timestamp is available on the parent table, from which i can obtain the 'YEAR' part and can be included as the first column of the partitioned key, which is also the primary key of the table, In this way also , [...] 15555 52 41_INDEXED COLUMNS GOING FOR TABLESPACE SCAN0_24_ujjwal@LOT.TATASTEEL.COM29_Thu, 11 Jan 2001 04:14:05 GMT486_us-ascii Hi ALL, I have a typical case where all the columns of the WHERE clause goes for a Tablespace scan though there is an Index defined for that and the sequence of the index also matches the sequence of the WHERE PREDICATE. Actually it should give Matchcols = 5 and Accesstype should be N . Due to this the plan runs into hours .The plan has only two queries and the other query has an indexed access with matchcol = 1 .So the problem is only with this query. The QUERY IS : [...] 15608 28 33_Question regarding BMC's LoadPlus15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 15:20:14 +1100622_- Colleagues,

Just ran a LoadPlus load replace job against the first partition of an empty partitioned tablespace (freshly created), with each partition defined as priqty 48 secqty 97200. Worked OK, except that every partition took a secondary extent. Hmmm... Altered the tablespace so that all partitions had priqty 720 secqty 97200 and ran the job again. Much better; only the first partition took an extent, as I was originally expecting. Seems to be something to do with mixing my toasties (sorry, reference to a stupid joke re: rabbits and myxomatosis some of you may be unfortunate enough to have heard. [...] 15637 79 45_Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 15:26:17 +1100459_- Ujjal,

Do your host variables' definitions match the columns' definitions? If not, this might explain why this query is only matchcols=1. Actually, it's not clear to me that this is the case with the statement you've provided. You say this must be the problem query becaus the other one only has matchcols=1. Don't quite follow the logic. It may well be the other query that is the culprit. Have you checked the access path for the query below? [...] 15717 110 45_Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN0_24_ujjwal@LOT.TATASTEEL.COM29_Thu, 11 Jan 2001 04:29:49 GMT477_us-ascii Hi Raymond , The accesspath for the querybelow is Matchcols=0,AccessType= R . The other query I cannot do anything because the first column of that particular query is a range predicate .

Regards Ujjal







"Bell, Raymond W" @RYCI.COM> on 01/11/2001 09:56:17 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 15828 35 45_Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 15:40:12 +1100651_- OK, so back to plan A and check that your host variable declarations match your table column data types. Also check that runstats is current. Maybe DB2 thinks there's hardly any rows, so a TS scan is OK. If so, runstats and rebind might fix it.

Just some ideas.



Raymond



> -----Original Message----- > From: ujjwal@LOT.TATASTEEL.COM [SMTP:ujjwal@LOT.TATASTEEL.COM] > Sent: Thursday, 11 January 2001 3:30 pm > To: DB2-L@RYCI.COM > Subject: Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN > > Hi Raymond , > The accesspath for the querybelow is Matchcols=0,AccessType= R . The > other query I cannot do anything [...] 15864 61 37_Re: Question regarding BMC's LoadPlus14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 11 Jan 2001 15:46:23 +1100764_iso-8859-1 compress yes?

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: Thursday, January 11, 2001 3:20 PM To: DB2-L@RYCI.COM Subject: [DB2-L] Question regarding BMC's LoadPlus



Colleagues,

Just ran a LoadPlus load replace job against the first partition of an empty partitioned tablespace (freshly created), with each partition defined as priqty 48 secqty 97200. Worked OK, except that every partition took a secondary extent. Hmmm... Altered the tablespace so that all partitions had priqty 720 secqty 97200 and ran the job again. Much better; only the first [...] 15926 78 37_Re: Question regarding BMC's LoadPlus15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 15:49:28 +1100700_- Naturlich. Your point?



> -----Original Message----- > From: James Campbell [SMTP:James.Campbell@HANCORP.COM.AU] > Sent: Thursday, 11 January 2001 3:46 pm > To: DB2-L@RYCI.COM > Subject: Re: Question regarding BMC's LoadPlus > > compress yes? > > /* standard disclaimer */ > James Campbell > DBA > Hansen Corporation, Doncaster > +61 3 9843 8442 > James.Campbell@Hancorp.com.au > -----Original Message----- > From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] > Sent: Thursday, January 11, 2001 3:20 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Question regarding BMC's LoadPlus > > > Colleagues, > > Just ran a LoadPlus load replace job against the first partition of [...] 16005 104 37_Re: Question regarding BMC's LoadPlus14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 11 Jan 2001 16:06:34 +1100519_iso-8859-1 Compression requires a compression table; each partition has its own compression table; a load replace will rebuild the compression tables for all partitions (although you won't get a usefull compression table if there wasn't any data to build one from); the standard size of a compression table is 16 pages. Where do you think DB2 is going to put the compression tables for the non-first partitions? (Of course, only a print out of the actual pages in the non-first partitions will tell you for sure.) [...] 16110 42 45_Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Jan 2001 10:32:38 +0530565_- Hi Ujjal, I am sure you do have Omegamon and you can check which query is taking the time. Suppose its first presuming you have checked that and wrote over here. Just few ideas and guesses without looking at the complete explain statements:

1) Check the host variable because i know how the WS-DATE-PREV and other host variables are defined in your shop. As i have found about the year month variables at your place earlier, it causes some good problems. All the other things should be tried if this is not correct. 2) Check your Index Levels. I hope [...] 16153 45 37_Re: Question regarding BMC's LoadPlus15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 11 Jan 2001 16:27:39 +1100308_- Oh, James. How disappointed in me you must be. And you even gave me an opportunity to redeem myself. You're right, of course. I got thrown off track as two other tablespaces I've done the same processing to didn't have secondary extents on their other partitions. That's because their priqty was 720. [...] 16199 84 30_Re: More design advice, please16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Jan 2001 11:02:27 +0530546_- Raymond, It seems you hate NPIs without Partitioning key columns or you just want to see everything balanced. IT DEPENDS will be something the answer could be. I think this is a good idea to add this column in every index if we have the index without this column with less cluster ratio. It could be very dangerous if it gives the matchcols = 0 to some of your queries which are starting with the 2nd column of the index i.e the first column without the partitioning ket value. Are you always able to specify the partitioning key ranges ? [...] 16284 57 34_Mining the DB2 Catalog With SQL !!16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Jan 2001 11:53:51 +0530459_- Hi All,

I was going through the article titled above by Suresh Sane in the IDUG solutions journal. I hope everyone has seen that, otherwise please have a look at that in the IDUG. I found it a very nice concept of Mining/warehousing the catalog and doing some sort of Analytical Processing(APs) by the DBAs, for the DBAs to satisfy the customers with the best performance by the Database Management System after tuning applications and queries. [...] 16342 20 21_Archive log authority14_frankzhang200023_frankzhang2000@SINA.COM31_Thu, 11 Jan 2001 01:21:34 -0600459_- Hi all, I have a question about the authority for command:archive log,during the installation of db2,I have assigned ibmuser as sysadm,sysadm2,sysopr1 and sysopr2,but when I try to execute command:-archive log,db2 prompts me that due to lack of enough authority,the command failed.Only after I use spufi grant achive to public,can I execute the command,I want to know what's the function of sysadm,sysadm2,sysopr1 and sysopr2 in dsnzapxxx load module? [...] 16363 35 15_Re: Any site???12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Thu, 11 Jan 2001 08:34:01 +0100642_- Hi Nayeem! I read this a few days late, but anyway, look at www.xephon.com

/Seppo

> -----Original Message----- > From: Mohammed Nayeem [SMTP:Mohammed_Nayeem@MOMED.COM] > Sent: Saturday, January 06, 2001 12:08 AM > To: DB2-L@RYCI.COM > Subject: Any site??? > > Hi > > Is there any URL where I can have sample REXX routine that checks the DB2 > catalogs and generates reorg jobs ? > (If it is available) > > Thanks & Regards > Nayeem > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The owners [...] 16399 93 25_Re: Archive log authority16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Jan 2001 13:58:25 +0530361_iso-8859-1 Hi Victor, As per the command reference if i can recall correctly, You need one of the following authorities to execute the Archive log Command :

1) ARCHIVE privilege 2) Installation SYSOPR authority 3) SYSCTRL authority 4) SYSADM authority DB2 commands issued from an MVS console are not associated with any secondary authorization IDs. [...] 16493 49 43_Re: end of service dates - db2 connect v5.x10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 11 Jan 2001 18:41:25 +1000587_ISO-8859-1 David,

The ibm website (http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/enddates. d2w/report) mentions the following end-of-service dates :

Version 7.x : Dec 31, 2002 Version 6.x : Dec 31, 2001 Version 5.x : June 30, 2001

Version 6.x is being "withdrawn" from June 29, 2001. (See announcement letter 900-125)

Cheers, Kals



-----Original Message----- From: David Ayers [SMTP:david.ayers@HIGHMARK.COM] Sent: Thursday, January 11, 2001 3:48 AM To: DB2-L@RYCI.COM Subject: end of service dates - db2 connect v5.x [...] 16543 100 29_Re: questionable results with16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Jan 2001 14:25:52 +0530443_- John, It really looks something different. Are we sure about what correlated subquery returns(ofcourse true/false) in terms of value ?? But at the same time why it should return NULL ??. DB2 returns only Sqlcode 100 with no rows when it finds no rows with the matching criteria but it doen't return NULL as a row. What will DB2 do in this case ??. I think it is confused, So what should happen as a result to these types of queries ??. [...] 16644 145 29_Re: questionable results with14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 11 Jan 2001 20:11:13 +1100591_iso-8859-1 A.COL1 should be set to null (when there isn't a correlated B.COL1) because that's the behavoir described in the SQL Reference Manual for this situation. Which is what I got in my quick test.

Sounds like 'bug' to me (I just don't know whose bug).

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Sent: Thursday, January 11, 2001 7:56 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] questionable results with [...] 16790 85 44_Re: Encryption/Decryption Process for DB2 V614_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 11 Jan 2001 20:25:14 +1100504_iso-8859-1 Angela

You could write an en/decrypt UDF. To invoke on insert/update, use a trigger like:

CREATE TRIGGER xxx NO CASCADE BEFORE INSERT/UPDATE OF encrypted-column ON table REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL SET NEW.encrypted-column = CRYPTION(NEW.encrypted-column,'ENCRYPT');

I have never written a trigger quite like this. I would be _extremely_ careful because it would take only a minor mistake to encrypt already encrypted data. Ooops. Test carefully. [...] 16876 88 29_Re: questionable results with0_27_jim.leask@RS-COMPONENTS.COM31_Thu, 11 Jan 2001 09:32:23 -0000513_iso-8859-1 John,

I would raise this with IBM, it may be working as designed but I would have thought that returning a NULL would be more consistent.

Jim.

-----Original Message----- From: John P. Schulting [mailto:john.schulting@CUNAMUTUAL.COM] Sent: 10 January 2001 18:54 To: DB2-L@RYCI.COM Subject: questionable results with



I received the following question from one of our more experienced developers and was wondering if anyone else had come across something similar: [...] 16965 24 35_Re: JDBC/SQLJ error at runtime :-((10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 11 Jan 2001 10:39:44 +0100340_us-ascii Hi Mike thanks for your reply....but I checked the profile but it seems ok.

Can you send me offline your entire /etc/profile and/or your personal profile ?

Of course if someone in DB2-l can sand me a working profile i'll be very happy.

I'm afraid that there are some problems in setting UNIX directories. [...] 16990 137 30_Re: More design advice, please14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Thu, 11 Jan 2001 04:03:34 -0600310_iso-8859-1 I dislike having the partition number coded in the data. In most cases it adds nothing to the design and usability except the need for more disk space. It also means that if you want/need to re-partition at some stage in the future, you need to change the partition number in the affected row. [...] 17128 126 29_Re: questionable results with14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Thu, 11 Jan 2001 04:14:30 -0600295_iso-8859-1 I also think this should be raised with IBM. I can see no case for saying anything other than a NULL should be returned. If IBM say it is WAD, then the design is wrong.

If your query:

UPDATE TABLEA A SET A.COL1 = (SELECT B.COL1 FROM TABLEB B WHERE A.COL2 = B.COL2) [...] 17255 53 41_Re: Priority of Cics-DB2 Thread Subtasks.17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Thu, 11 Jan 2001 09:47:32 -0200388_iso-8859-1 Hi Cuneyt,

CICS region service class is only used if you don´t have transaction service classes defined in WLM. Otherwise it will only be used during CICS startup; so, you need to define service classes with 'response time' type goals in WLM and assign your CICS transactions to those service classes, based on CICS applid, transaction names or transaction groups. [...] 17309 28 17_DB2 UDB Version 711_John Curran26_John.Curran@DRESDNERKB.COM31_Thu, 11 Jan 2001 11:18:39 -0000743_iso-8859-1 Hi

Does any body know when DB2 UDB V7 for OS/390 is available?

thanks

John Curran Dresdner Kleinwort Wasserstein







---------------------------------------------------------------------- If you have received this e-mail in error or wish to read our e-mail disclaimer statement and monitoring policy, please refer to http://www.drkw.com/disc/email/ or contact the sender. ----------------------------------------------------------------------

================================================ 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. 17338 24 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39015_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 11 Jan 2001 05:19:08 -0600454_- Sri

To your recent questions:

to 1: we often define some dummy partitions (in one case for a large table about 10). Because these are not used, we habe no performance problems with them. Their size are only 1 track.

to 2: I think no, isn't DSSIZE 4 Gig the default ?

to 3: Inserts, deletes and updates must be considered As mentioned a few time in this list you loose concurrency in the BUILD2-phase of an Online-Reorg [...] 17363 39 12_(no subject)21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Thu, 11 Jan 2001 19:35:17 +0800324_us-ascii Dear All,

I am one of the DB2 DBA member in a bank. Our team plans to implement DB2 DRDA (DB2 V5) using TCP/IP support for the access of DB2 data in OS/390 thru ODBC and DB2 Connect (Personal Edition V 7).

Regarding the security of this setup, we would like to have your input for the following: [...] 17403 20 72_Queries on the Security of TCP/IP and DB2 Connect (Personal Edition V 7)21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Thu, 11 Jan 2001 19:39:11 +0800386_us-ascii Dear all,

This mail is re-sent with subject descriptions. Please let me have your reply soon.

Thanks and regards, A Cheng

================================================ 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. 17424 51 27_Re: DB2 PM Release Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 11 Jan 2001 11:31:29 -0000364_iso-8859-1 Richard

as already said you have to go to V6 PM . Beaware that V7 PM is on it's way though .. and PM V6 is limited in what it gives you over V5 ..

Les

-----Original Message----- From: Richard Arnold [mailto:Richard.Arnold@CHASE.COM] Sent: Wednesday, January 10, 2001 5:04 PM To: DB2-L@RYCI.COM Subject: DB2 PM Release Question [...] 17476 243 28_DB2 - RACF interface problem15_Ali OZTURK(942)27_ali.ozturk@PAMUKBANK.COM.TR31_Thu, 11 Jan 2001 13:47:46 +0200722_ISO-8859-9



We use DB2 V510 in OS/390 platform. We are implementing the DSNXÞXAC module for use external racf security. We used IRRÞXACS member as source code in SYS1.PARMLIB.

After the compile , db2 is starting normaly, and using external security module.

This is DBW1DBM1 log:

RACF/DB2 EXTERNAL SECURITY MODULE FOR DB2 SUBSYSTEM DBW1 HAS A MODULE VERSION OF OW38710 AND A MODULE LENGTH OF 00004ED0. RACF/DB2 EXTERNAL SECURITY MODULE FOR DB2 SUBSYSTEM DBW1 IS USING OPTIONS: &CLASSOPT=2 &CLASSNMT=DSN &CHAROPT=1 &PCELLCT=50 &SCELLCT=50 RACF/DB2 EXTERNAL SECURITY MODULE FOR DB2 SUBSYSTEM DBW1 INITIATED RACLIST FOR CLASSES: MDSNDB MDSNPK MDSNPN MDSNBP MDSNCL MDSNTS MDSNSG MDSNTB [...] 17720 72 21_AW: DB2 UDB Version 712_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 11 Jan 2001 12:41:34 +0100757_iso-8859-1 John,

please take a look at http://www.ibmlink.ibm.com/usalets&parms=H_200-087 and you will find some information on the planned availability date...

As far as I know Version 7 is in Beta........

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 Software Development & Technology Center Knowlegde Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 17793 76 39_Re: Archive DB2 system catalog (OS/390)23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 11 Jan 2001 11:33:41 -0000531_iso-8859-1 ....3 months before you back up your DB2 Catalog .. :-(

I would suggest you think about getting a DR strategy in place soon.

regards

Leslie

-----Original Message----- From: Hayden, Lee [mailto:Lee_Hayden@CSX.COM] Sent: Tuesday, January 09, 2001 4:16 PM To: DB2-L@RYCI.COM Subject: Re: Archive DB2 system catalog (OS/390)



Tibor,

The Installation process created a sample job for this. Look in the samplib member. Should be something like dsn510.SDSNSAMP(DSNTIJIC) . [...] 17870 118 32_Re: DB2 - RACF interface problem14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 11 Jan 2001 23:01:34 +1100481_iso-8859-1 It is (as I recall) a stated restriction of the external security exit that the exit is _not_ used for DB2 commands - the SYSIBM.SYSxxxxAUTH tables are still used.

Well, actually that's not quite correct. If you read the documentation there's a note that, for commands, the primary and secondary auth-ids are not correctly passed to the exit. Hence the UACC security will be used. This has to be set up so that the exit will tell DB2 to try the AUTH tables. [...] 17989 75 30_Re: More design advice, please16_Michael McMorrow25_michael.g.mcmorrow@AIB.IE31_Thu, 11 Jan 2001 12:04:43 +0000636_ISO-8859-1 Probably leave it out of the other indexes if the partitioning key value has absolutely no business meaning / sql use....If the partitioning key column has some meaning (eg. time cycle or business division) then you might consider it on that basis for sql interested in specific partitions....If NPI is currently on 'custno' with corresponding rows scattered randomly across partitions, then remember that range processing/non-matching-index-scans will be fundamentally impacted by sticking the partitition key column at the start of the index (do you want index on 'custno' or on 'custno within partition')....Michael. [...] 18065 66 51_Re: Db2 Connect and UDB/OS390 version compatibility10_Robert Ord21_robertord@HOTMAIL.COM31_Thu, 11 Jan 2001 13:15:58 -0000475_- We will have a similar problem when we upgrade to V6 on the mainframe, and to V7 on AIX.

We are currently running db2 connect 5.2 (NT), db2 v5.1 (OS/390) and udb v6.1 (AIX).

I made the mistake of upgrading to V7 admin client on my PC which meant I could no longer connect to the mainframe db2 directly or via db2 connect. When I had udb v6.1 admin client installed I couldn't connect to directly to mainframe db2 but I could connect through db2 connect. [...] 18132 96 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39010_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 11 Jan 2001 07:35:19 -0600580_- Hello Sri,

Sounds like you've found the correct solution to your problem.

To your questions:

1. The optimum number of parts which you should allocate? I can't answer that question. It sounds as if you are not certain yourself how long you might want to keep your data for. I guess if you are unable to extract a final and definitive answer from your clients (ha,ha), then you might have to guess it. You are obviously going to use a LARGE Tablespace (or one defined with DSSIZE). Because with V6 you can alter the partitioning keys, I would be happier [...] 18229 27 48_Restoring 5.2 UDB backups using 6.1 UDB binaries0_28_Roy_Bertossi@ABERCROMBIE.COM31_Thu, 11 Jan 2001 05:43:45 -0800464_us-ascii All,

We need to restore to a Solaris 6.1 UDB instance a backup taken from a Solaris 5.2 UDB instance. Will this work since 6.1 had column lengths change? If it works at all, will the database be corrupt because the catalog hasn't been converted? Since I wasn't involved in the upgrade process, I don't know exactly what all happened to the database, I just have to help get a failed upgrade recovered so that we can attempt the upgrade again. [...] 18257 20 15_Solaris upgrade13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET31_Thu, 11 Jan 2001 08:53:05 -0500348_us-ascii Well, I spent about a month getting windows nt, solaris, and db2/udb v6.1 working together. Everything was doing just great. then they upgraded the solaris to v8. Have not been able to get db2 working since... cannot even get tcp/ip up so that i can talk to it from windows nt... Anyone had this experience with solaris v8???? thanks [...] 18278 83 27_Re: DB2 PM Release Question10_Tom Taylor17_ttaylor@CHUBB.COM31_Thu, 11 Jan 2001 08:54:59 -0500476_us-ascii Les

I have installed DB2PM V7... it has changed slightly in the 'try and buy' arena. Otherwise the install was easy for a first timer. I'm playing withthe GUI piece and it looks good. There are a couple issues though. Will keep you informed.

Tom







"Pendlebury-Bowe, Leslie" @RYCI.COM> on 01/11/2001 06:31:29 AM

Please respond to DB2 Data Base Discussion List [...] 18362 135 32_Re: DB2 - RACF interface problem10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 11 Jan 2001 08:04:45 -0600408_- Right with your first paragraph.

Last I heard, you don't need to set up anything to 'try the AUTH tables'. The Exit first tries to satisfy the request using RACF External security. If that fails, then it falls through to DB2 security checking.

You will need to grant DB2 authorities to authorise the execution of DB2 commands. Install SYSADM/SYSOPR are the obvious and usual exceptions. [...] 18498 68 25_Re: Archive log authority16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 11 Jan 2001 09:08:04 -0500309_iso-8859-1 Victor, sysadm...sysopr2 should be RACF ids, not a literal like 'ibmuser'. These RACF ids will then have the desired access. Or is your RACF id in fact 'ibmuser'? If that is your RACF id and you cannot do the command, something has gone very wrong with the install and you should contact IBM. [...] 18567 90 38_Re: Mining the DB2 Catalog With SQL !!11_Suresh Sane21_data_arch@HOTMAIL.COM31_Thu, 11 Jan 2001 08:36:58 -0600330_- Sanjeev,

I have seen the "proofs" but am yet to get my copy of the magazine (the pony express takes it time to get to the heartland). In addition, I would welcome any feedback from the esteemed list members from whom I have learned a lot. I have several IDUG presentations to my credit but this is my first article. [...] 18658 104 35_Re: JDBC/SQLJ error at runtime :-((13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Thu, 11 Jan 2001 14:30:38 -0000524_iso-8859-1 Max,

Here's a couple of suggestions from somebody who knows very little about Java, but is trying hard :-)

1. Re-compile offending programs. 2. Make sure the JDBC driver's classfiles are in your CLASSPATH (the error message suggests it might be something to do with this).

Like you I'm struggling to understand enough about Java and JDBC to get by without becoming a fully-fledged Java programmer. There's not much info out there but you might want to check these links that I've found: [...] 18763 45 51_Re: Db2 Connect and UDB/OS390 version compatibility13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Thu, 11 Jan 2001 08:44:42 -0600512_iso-8859-1 We're using DB2 Connect V6 with DB2 OS/390 V5. Go to DB2 Connect V6 at a minimum so you can take advantage of connection pooling.

-----Original Message----- From: teldb2kals [mailto:teldb2kals@TELSTRA.COM] Sent: Wednesday, January 10, 2001 8:23 PM To: DB2-L@RYCI.COM Subject: Db2 Connect and UDB/OS390 version compatibility



Hi everybody,

DOes anybody know where I can get information about the compatibility between DB2 Connect versions and UDB for OS/390 versions ? [...] 18809 27 35_QMF for windows connection question14_Richard Arnold24_Richard.Arnold@CHASE.COM31_Thu, 11 Jan 2001 08:35:35 -0600353_us-ascii Hello list,

I am setting up QMF for windows to connect to DB2 for OS/390 via TCP/IP.

I can go into the QMF for windows administrator and connect to the DB2.

Then when I try to run a simple query in QMF for Windows (select 'test' from sysibm.sysdummy1;), it says "retrieving resource limits", and then later times out. [...] 18837 14 18_UDB Application ID13_Brian Charles20_bcharles@RITEAID.COM31_Thu, 11 Jan 2001 09:54:19 -0500353_iso-8859-1 Is the application id from the "list application" command stored anywhere in the UDB system catalog?

================================================ 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. 18852 54 25_Re: Archive log authority10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 11 Jan 2001 08:47:19 -0600379_ISO-8859-1 I have a question too:

- When I try to execute command.....

- Only after I use spufi grant archive.....

Which userid are you using in each of these cases?

Are you saying that 'ibmuser' (the install sysadm/sysopr!) was unable to issue the ARCHIVE LOG command, but could give itself the privilege to do so by GRANTing ARCHIVE to public? [...] 18907 68 16_Re: (no subject)13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Thu, 11 Jan 2001 09:16:29 -0600427_iso-8859-1 See below

-----Original Message----- From: Li, Chak Lung Dominic [mailto:seddba@HK.SUPER.NET] Sent: Thursday, January 11, 2001 6:35 AM To: DB2-L@RYCI.COM Subject: (no subject)



Dear All,

I am one of the DB2 DBA member in a bank. Our team plans to implement DB2 DRDA (DB2 V5) using TCP/IP support for the access of DB2 data in OS/390 thru ODBC and DB2 Connect (Personal Edition V 7). [...] 18976 23 28_Inserting Into Remote Tables16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Thu, 11 Jan 2001 10:31:40 -0500441_iso-8859-1 Hey Folks.. I'm able to select data remotely. From our development subsystem when I'm in production (different LPAR's). But when I try to insert into our development subsystem from our production subsystem, I get a -512, statement reference to remote object is invalid.. Here's the sql..

insert into mvs2db2.owner.tab1 select * from mvs1db2.owner.tab1 where cpk_mrno = 'xxxxx' Any help would be appreciated.. Thanks.. [...] 19000 54 32_Re: Inserting Into Remote Tables11_Suresh Sane21_data_arch@HOTMAIL.COM31_Thu, 11 Jan 2001 09:29:30 -0600421_- Michael -

The insert is fine - the problem is that you are referncing objects from 2 subsystems in 1 sql. This is (currently) not permitted. You will have the same problem with a select that joins 2 such tables.

If you have qmf, you can get around this by selecting from remote, saving it local and then insert to local with select from local (of course you can user export or any other means also). [...] 19055 58 21_Re: DB2 UDB Version 70_19_Tim.Lowe@STPAUL.COM31_Thu, 11 Jan 2001 09:37:51 -0600381_us-ascii This March was suggested as the DB2 UDB V7 OS/390 GA date at the DB2 tech conference, but this was not official.





John Curran cc: Sent by: DB2 Data Subject: DB2 UDB Version 7 Base Discussion List



01/11/2001 05:18 AM Please respond to DB2 Data Base Discussion List [...] 19114 12 9_DSSIZE V&15_Steven Morrison33_steven.morrison@FIRSTDATACORP.COM31_Thu, 11 Jan 2001 09:37:35 -0600405_- Does anyone know if the DSSIZE parameter will be alterable in V7 on OS/390? I thought that may be the case but can't find any documentation to confirm the rumor.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19127 42 32_Re: Inserting Into Remote Tables13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 11 Jan 2001 09:45:26 -0600615_US-ASCII Michael, DB2 does not support this function as of yet. You can select and delete using 3 part names but not insert from a remote table. I believe this function will be available in a future release of DB2. Maybe someone from IBM will comment on this. HTH Kurt

>>> MDempse@UNCH.UNC.EDU 01/11/01 09:31AM >>> Hey Folks.. I'm able to select data remotely. From our development subsystem when I'm in production (different LPAR's). But when I try to insert into our development subsystem from our production subsystem, I get a -512, statement reference to remote object is invalid.. Here's the sql.. [...] 19170 17 51_Re: Db2 Connect and UDB/OS390 version compatibility0_24_lightsey@ITS.STATE.MS.US31_Thu, 11 Jan 2001 09:43:44 -0600422_us-ascii My users' agency has UDB v5 on NT, v7 on HP-UX, and v5 &v6 on os/390 - they can successfully use db2 connect v5 and v7 to access any/all of the various db2's. As you can infer, they are in the middle of a transition to v6 on os/390 and v7 everywhere else - not the smoothest transition, but reasonable as long as everything is at current maint levels ( and I do mean EVERYTHING even remotely related to db2) [...] 19188 70 37_Re: Question regarding BMC's LoadPlus15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Thu, 11 Jan 2001 09:53:41 -0600355_iso-8859-1 Raymond,

First of all, BMC LOADPLUS will always build a 16 page dictionary for each partition, even if it is not full, thus each partition needs at least 18 pages. (Page ZERO, spacemap page, 16 dictionary pages, and that is the order they are in the object)

Next is a caution statement. If you did a load with the following, [...] 19259 28 37_Expression support in Group by clause14_Jessen Michael27_JessenMichael@JOHNDEERE.COM31_Thu, 11 Jan 2001 09:56:07 -0600482_us-ascii We are on DB2 V5 OS/390 (almost on V6!).

Does anyone know when expressions in a GROUP BY statement will be supported on OS/390? V7?

Thanks! Mike

SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE, OUTSTANDING_BALANCE FROM SIDBP01.RDXNOTEA WHERE UNIT_CODE = '01' GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE

Statement highlighted in RED is considered a complex object and isn't currently supported by DB2, but is other RDBMS' like Oracle. [...] 19288 70 39_Re: Archive DB2 system catalog (OS/390)12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Thu, 11 Jan 2001 17:05:39 +0100607_- Also Utility Reference Guide has a catalog backup scheme, but it tends not to agree with DSNTIJIC all the way. I don't know for sure which ist best, but I always do what is told in the manual :).

Regards Seppo

> -----Original Message----- > From: Enarson, Mike (CC-Database Services) > [SMTP:Mike.Enarson@CONAGRAFOODS.COM] > Sent: Monday, January 08, 2001 11:57 PM > To: DB2-L@RYCI.COM > Subject: Re: Archive DB2 system catalog (OS/390) > > Tibor, if you go back to the prefix.SDSNSAMP library that contains your > install jobs, you will find a catalog backup in member DSNTIJIC. > > > [...] 19359 107 59_Re: Partitioning a large tablespace - DB2 UDB V6 for OS/39010_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 11 Jan 2001 10:04:03 -0600589_- Correction to my comments about dssize. The RIDs are only 5 bytes for EA- enabled t/s over 4G.

On Thu, 11 Jan 2001 07:35:19 -0600, John Hardy wrote:

>Hello Sri, > >Sounds like you've found the correct solution to your problem. > >To your questions: > >1. The optimum number of parts which you should allocate? I can't answer >that question. It sounds as if you are not certain yourself how long you >might want to keep your data for. I guess if you are unable to extract a >final and definitive answer from your clients (ha,ha), then you [...] 19467 46 41_Re: Expression support in Group by clause9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 11 Jan 2001 09:38:08 -0700496_us-ascii Don't have an answer on support of concatenation in group by, maybe I am still half asleep but wouldn't the following group by statements get you the same result?

GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE

GROUP BY UNTI_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE

Also if this is really something that you need to treat as one chunk would it be worth adding a column to your table and indexing it, having this new column containing the concatenated information? [...] 19514 45 41_Re: Expression support in Group by clause10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 11 Jan 2001 10:28:06 -0600812_- You can already (on V5) do this with an NTE:

SELECT UNACSE, SUM(OUTSTANDING_BALANCE)

FROM (SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE AS UNACSE, OUTSTANDING_BALANCE FROM SIDBP01.RDXNOTEA WHERE UNIT_CODE = '01') AS UNACSE_NTE

GROUP BY UNACSE



On Thu, 11 Jan 2001 09:56:07 -0600, Jessen Michael wrote:

>We are on DB2 V5 OS/390 (almost on V6!). > >Does anyone know when expressions in a GROUP BY statement will be supported >on OS/390? V7? > >Thanks! >Mike > >SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE, > OUTSTANDING_BALANCE >FROM SIDBP01.RDXNOTEA >WHERE UNIT_CODE = '01' >GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE > >Statement highlighted in RED is considered a complex object and isn't >currently supported by DB2, [...] 19560 190 51_Re: Db2 Connect and UDB/OS390 version compatibility14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Thu, 11 Jan 2001 08:37:19 -0800379_iso-8859-1 Kals, You can use multiple versions of DB2 Connect against multiple versions of DB2 for OS/390. Of course, you need DB2 for OS/390 V5.1 to connect via TCP/IP with DB2 Connect. Each version of DB2 Connect uses different packages. To see prerequisites and compatibilities, view announcement letters at www.ibm.com/news (search on the product of interest). HTH Phil [...] 19751 71 41_Re: Expression support in Group by clause14_Toppins, Smike21_smike.toppins@GWL.COM31_Thu, 11 Jan 2001 09:38:39 -0700658_- No, there's a big difference. The first GROUP BY gives you one break point; the second one gives you three.



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Rob Crane[SMTP:racrane@concentric.net] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, January 11, 2001 9:38 AM > To: DB2-L@RYCI.COM > Subject: Re: Expression support in Group by clause > > Don't have an answer on support of concatenation in group by, maybe I am > still half asleep but wouldn't the following group by statements get you > the same result? > > GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE > [...] 19823 125 41_Re: Expression support in Group by clause12_Susan Lawson22_lawson_susan@YAHOO.COM31_Thu, 11 Jan 2001 08:52:38 -0800717_us-ascii Mike Try the following.....



SELECT NEWCOL, OB FROM (SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE AS NEWCOL, OUTSTANDING_BALANCE AS OB FROM SIDBP01.RDXNOTEA WHERE UNIT_CODE = '01') GROUP BY NEWCOL

Regards, Susan Lawson Yevich, Lawson and Associates, Inc (YL&A) www.ylassoc.com



--- "Toppins, Smike" wrote: > No, there's a big difference. The first GROUP BY > gives you one break point; > the second one gives you three. > > > SMike Toppins > Great-West Life > smike.toppins@gwl.com > (303) 737-5094 > > > > ---------- > > From: Rob > Crane[SMTP:racrane@concentric.net] > > Reply To: DB2 Data Base Discussion List > > Sent: Thursday, January [...] 19949 35 27_Re: DB2 PM Release Question12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 11 Jan 2001 10:34:00 -0500498_- It is my understanding that PM must be upgraded to work correctly when you upgrade to DB2 V6.1. We just updated our DB2PM from V5.1 to V6.1 (Put 0011) in our test environment, and have a problem with both DB2 V6.1 and 5.1. There is an intermittent problem with displaying thread information with the online monitor. You will get DGOM005 and DGOM106 (works fine in PM 5.1). IBM has reproduced this and has not yet resolved it as of this date. I will post the resolution when I get an answer. [...] 19985 164 41_Re: Expression support in Group by clause13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 11 Jan 2001 11:12:02 -0600600_iso-8859-1 Michael,

DB2 V7 for OS/390 does not allow expressions in the GROUP BY, although non-OS390 DB2 does...so I'm assuming that V8 should allow this.

Another alternative (although there was nothing wrong with the previous solutions): SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE, SUM(OUTSTANDING_BALANCE) FROM SIDBP01.RDXNOTEA WHERE UNIT_CODE = '01' GROUP BY UNIT_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE

As for the issue about breakpoints, that's true if you wish to run this thru QMF and set your breakpoints (or your own program). Otherwise it should not be an issue. [...] 20150 88 31_Re: Data Archive for DB2 OS/39013_McDonald, Ken20_Ken_McDonald@BMC.COM31_Thu, 11 Jan 2001 11:35:01 -0600391_iso-8859-1 Hi Susan,

Truthfully, I am not familiar with the functionality of the two archival tools you referenced... But, how about using a log processing tool for creation of archive files. You can create archive files from the logged deletes when removing aged rows. These files can be merged and stored on less expensive media and recalled/restored for use when need arises. [...] 20239 21 56_Catalog Utility abend causing package to be invalidated?13_Johnson, Bill18_bjo23@ALLSTATE.COM31_Thu, 11 Jan 2001 11:39:49 -0600474_- All, We had our DB2 (V6) catalog backup job abend the other night. Before it was restarted, DB2 was recycled. The skeleton cursor table was in UTRO upon DB2's return and there weren't any utilities outstanding and we had to perform an access(force) to make the SCT available. Later in the day, we had a situation where a package had been invalidated. Could the package have been invalidated due to the above situation? Thanks, Bill Johnson DBA Allstate Insurance Co. [...] 20261 22 40_Re: JDBC/SQLJ error at runtime - :-)))))10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 11 Jan 2001 18:37:36 +0100350_us-ascii Thanks a million to Michael Deer. With his help I resolved the problems due to a bad configured /etc/profile.

Now It'll be better to read some UNIX (for S390) manuals...sigh sob.

My old friend Max Biancucci will be laugh for a week.....

Thanks to the people who replied.

Max Scarpa DB2 for S390 (??) sysprog [...] 20284 149 41_Re: Expression support in Group by clause14_Jessen Michael27_JessenMichael@JOHNDEERE.COM31_Thu, 11 Jan 2001 11:46:56 -0600315_us-ascii Unfortunately, we are using a Business Objects front end and I don't have the luxury of being able to recode the SQL... well, I do, but this would kinda take the 'ad' out of ad hoc :-)

Unfortunately, Creating a column for the expression is the answer I was expecting, just not wanting to hear. [...] 20434 101 31_Re: Data Archive for DB2 OS/3909_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 11 Jan 2001 10:57:36 -0700316_us-ascii CA's RC/Extract also has the functionality you are looking for. Most of these types of tools allow you to use utilities or standard SQL processing. In my experience utilities were the way to go but not all application folks have the ability to run utilizes so sometimes the SQL option is nice to have. [...] 20536 94 17_sql help / advice20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM31_Thu, 11 Jan 2001 13:05:20 -0500555_iso-8859-1 DB2 V4.1 (don't laugh too much) OS390

I have the below query, I was trying to dynamically explain it via BMC Apptune but it doesn't. (But it does compile and run.) I get a SQLCODE = -206, TU4M2.TIMESTAMP_UPDATED IS not a column of the table identified in the from clause. This happens because of how the table alias is used and not used in the query. (TU4M2 is not an alias, it is a table name.) The table name is identified in the column/expression AND in the from clause. Quite frankly I thought it was a syntax problem at first. [...] 20631 51 52_Re: Restoring 5.2 UDB backups using 6.1 UDB binaries18_Gert van der Kooij15_geko@WANADOO.NL31_Thu, 11 Jan 2001 19:08:19 +0100480_iso-8859-1 Hi, When you restore the database (with the restore command) it will automatically be migrated to a 6.1 database. If it's a database with a lot of entries in the catalog will take some time and logspace.

Hope this helps.

Gert

----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Thursday, January 11, 2001 2:43 PM Subject: Restoring 5.2 UDB backups using 6.1 UDB binaries [...] 20683 90 25_Re: Archive log authority12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 11 Jan 2001 10:11:01 -0800467_us-ascii ibmuser used to be (an maybe still is) a default id that comes with MVS, that is not secure (no password needed). Most sysprogs would delete it as soon as they got their MVS system operational.

My guess is that you are trying to do the archive log from and MVS console (SDSF, OPER, etc). In this case, you will have to grant archive to that facility (SYSOPR). If you use the DB2I interface to submit the command, it should run under your tso id. [...] 20774 127 21_Re: sql help / advice13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 11 Jan 2001 12:29:28 -0600389_iso-8859-1 Lisa,

Are you asking whether it is good practice to qualify the column names in the SELECT list? If so, the answer is YES. Apart from the extra keystrokes, it does remove any ambiguity as to the origin of the columns for the reader; and the optimizer (who will complain if you have not qualified any ambiguous column references - except subqueries - another story). [...] 20902 21 18_online index build12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 11 Jan 2001 20:48:11 +0200440_iso-8859-1 Hi,

What I'm really missing is an "online index build" . Getting a window to do it can be very problematic.



Isaac Yassin DBMS & IT Consultant yassin@netvision.net.il

================================================ 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. 20924 20 31_Re: Help needed DB2 UDB Restore11_Jeff Kalman15_jkalman@CSC.COM31_Thu, 11 Jan 2001 13:57:44 -0500419_us-ascii I am trying to take a DB2 UDB database that was backed up and restore it as a new database on another box. I am getting an error message that states: The database alias "" parameter is not valid or not specified. The database that was backed up does not have any aliases. Has anybody taken a backup of an existing database and restored it on another server as a new database. I would appreciate any help. [...] 20945 39 31_Re: Help needed DB2 UDB Restore11_Jakobs, Jef18_JJakobs@HARTIC.COM31_Thu, 11 Jan 2001 13:54:15 -0600629_iso-8859-1 If you did not specify an alias, then by default you get the database name (as alias). Use that database name instead. Should work :)



Jef Jakobs

-----Original Message----- From: Jeff Kalman [mailto:jkalman@CSC.COM] Sent: Thursday, January 11, 2001 12:58 PM To: DB2-L@RYCI.COM Subject: Re: Help needed DB2 UDB Restore



I am trying to take a DB2 UDB database that was backed up and restore it as a new database on another box. I am getting an error message that states: The database alias "" parameter is not valid or not specified. The database that was backed up does not have [...] 20985 14 31_Re: Help needed DB2 UDB Restore11_Jeff Kalman15_jkalman@CSC.COM31_Thu, 11 Jan 2001 15:03:26 -0500285_us-ascii Thanks, I'll give it a try... Jeff

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 21000 42 22_Re: online index build15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Thu, 11 Jan 2001 14:06:38 -0600409_iso-8859-1 Hi Isaac,

Just out of curiosity, what prompts you to do an index rebuild? Is it because of a REORG, LOAD, RECOVERY, other? How would you envision this feature working?

Thanks, Donna Di Carlo BMC Software

-----Original Message----- From: Isaac Yassin [mailto:yassin@NETVISION.NET.IL] Sent: Thursday, January 11, 2001 12:48 PM To: DB2-L@RYCI.COM Subject: online index build [...] 21043 33 13_DSNDB07 locks16_Alison Pelletier26_alison_pelletier@YAHOO.COM31_Thu, 11 Jan 2001 12:32:21 -0800525_us-ascii We had a user "pig-out" on our sort space in DSNDB07. I called him. As we talked, I was doing display locks on DSNDB07 database. He says his query is complete and he is at QMF home screen, but I still see his ID holding locks big time on DSNDB07. I realize that application database locks can be held if QMF doesn't have to get all database data to say do a sort, which is what I suspected happened since he was gobbling DSNDB07. Is this to be expected that after all data is processed, locks to DSNDB07 do not [...] 21077 156 17_Re: DSNDB07 locks10_Bill Disch23_bill.disch@GENMILLS.COM31_Thu, 11 Jan 2001 14:40:27 -0600479_- All the data was not processed, therefore QMF still hold the locks. Rather than exiting QMF, have the user execute the QMF command "reset data" to free all locks and remove the report from QMF.

> Bill Disch > Technical Services > (763) 764-7055 > bill.disch@genmills.com >



-----Original Message----- From: Alison Pelletier [mailto:alison_pelletier@YAHOO.COM] Sent: Thursday, January 11, 2001 2:32 PM To: DB2-L@RYCI.COM Subject: [DB2-L] DSNDB07 locks [...] 21234 68 17_Re: DSNDB07 locks11_David Nance16_DWNance@FHSC.COM31_Thu, 11 Jan 2001 15:49:01 -0500402_US-ASCII The other way to release the locks is to max to the bottom of user's output.

Dave Nance First Health Services, Corp. (804)527-6841

>>> bill.disch@GENMILLS.COM 01/11/01 03:40PM >>> All the data was not processed, therefore QMF still hold the locks. Rather than exiting QMF, have the user execute the QMF command "reset data" to free all locks and remove the report from QMF. [...] 21303 43 30_Left Outer Join returns 0 rows11_David Nance16_DWNance@FHSC.COM31_Thu, 11 Jan 2001 16:16:29 -0500524_US-ASCII I'm looking up apars now, but sometimes you all are faster than that process. One of our developer's has a query select * from (select col1, col2, col3 from t1,t2,t3 where ....) as x left outer join (select col1, col2, col3, col4 from t4,t5 where ....) as y on x.col1 = y.col1 and x.col2 = y.col2 This returns zero rows. When I run the sub-queries separately I get 5 rows in X and a couple hundred in Y. So, with the outer join I should get returned the 5 rows from X and the corresponding rows from Y if they [...] 21347 48 34_Re: Left Outer Join returns 0 rows13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 11 Jan 2001 15:20:41 -0600634_iso-8859-1 This might shorten your search -- try looking at PQ41221.

Mike. -----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: Thursday, January 11, 2001 3:16 PM To: DB2-L@RYCI.COM Subject: Left Outer Join returns 0 rows



I'm looking up apars now, but sometimes you all are faster than that process. One of our developer's has a query select * from (select col1, col2, col3 from t1,t2,t3 where ....) as x left outer join (select col1, col2, col3, col4 from t4,t5 where ....) as y on x.col1 = y.col1 and x.col2 = y.col2 This returns zero rows. When I run the sub-queries separately I [...] 21396 79 34_Re: Left Outer Join returns 0 rows12_Susan Lawson22_lawson_susan@YAHOO.COM31_Thu, 11 Jan 2001 13:52:20 -0800591_us-ascii David,

When DB2 does query rewrite and changes this to an inner join this can happen. Could you show the entire query? The problem may lie in what is specified in the WHERE clause - DB2 may be doing some re-write

Regards, Susan Lawson Yevich, Lawson and Associates, Inc (YL&A) www.ylassoc.com



--- David Nance wrote: > I'm looking up apars now, but sometimes you all are > faster than that process. One of our developer's has > a query > select * from > (select col1, col2, col3 from t1,t2,t3 > where ....) as x > left outer join [...] 21476 44 44_Re: Encryption/Decryption Process for DB2 V612_Roger Miller19_millerrl@US.IBM.COM31_Thu, 11 Jan 2001 16:42:55 -0600323_- Hello Beth, nice to hear from you.

There are several possibilities for encryption with DB2. What you choose generally depends upon what you are trying to protect from and what side effects you can tolerate. Encryption and general relational function conflict. Here is how I would put out the primary options. [...] 21521 69 37_Re: Question regarding BMC's LoadPlus15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 12 Jan 2001 09:54:40 +1100346_- Hi Chris,

Thanks for your comments. As you and James have pointed out, the secondary extents on my 'other' partitions was because the compression dictionary was being buit for those partitions. All loads into this table will be partition-specific, saying Load Resume Into Table ... Part X Replace', so my dictionaries should be OK. [...] 21591 19 24_-725 sqlcode DB2 Connect13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 11 Jan 2001 16:55:01 -0600338_iso-8859-1 Hello List,

Has anyone else seen -725 sqlcode errors returned from DB2 OS/390 V6? We recently started getting bursts of these on our DB2 Connect servers (v6.1 fp5 and v7.1 fp1). We don't see any of them when connecting to DB2 OS/390 V5 subsystems.

Just thought I'd poll the list before opening up a problem. [...] 21611 14 49_Martha Kijak/HRD/Prudential is out of the office.12_Martha Kijak27_martha.kijak@PRUDENTIAL.COM31_Thu, 11 Jan 2001 18:20:49 -0500365_us-ascii I will be out of the office from 01/11/2001 until 01/17/2001.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 21626 20 45_moving data from one DB2 subsystem to another16_Drucker, Aurelia18_ADrucker@KMART.COM31_Thu, 11 Jan 2001 18:49:28 -0500304_iso-8859-1 Looking for advice regarding a good way to move data, from a quite big number of quite big tables (150) from one DB2 subsystem to another.

We are running DB2 V5; no sysplex We are having BMC utilities and I intend to use BMC UNLOAD+ /LOAD+

Any help will be appreciated, TIA [...] 21647 65 30_Re: More design advice, please15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 12 Jan 2001 10:54:09 +1100529_- Jeff, Michael Mc, etc.

Some good comments, guys. The partitioning key has no value whatsoever, other than carving up my loads. As such, it has no business value at all. However you both - I think - pointed out something I hadn't thought of. If range processing - or a now 2nd IN list process - is involved I could be in trouble. Checked around, and fortunately all existing processes look for specific values of the now 2nd column. So I should be OK with not adding the partitioning column to the front of my NPIs. [...] 21713 33 17_Loading DEL files10_Karl Bauer15_karl@VIATOR.COM31_Fri, 12 Jan 2001 11:26:35 +1100582_us-ascii I want to migrate data from the now-defunct Solid database to DB2 V.7.1 but I'm facing the following problem: The exported Solid data files are column-delimited with a comma, however ALL field data (both character and integer) is enclosed in single-quotes ('...'). The DB2 Load utility appears to accept data where only character strings are delimited. It does not recognise integer data which is enclosed in single-quotes and ignores it. Is there any way to coerce the Load utility into accepting delimited integer data as a valid integer? Thanks for your help, Karl [...] 21747 44 43_Re: end of service dates - db2 connect v5.x0_15_leon@CA.IBM.COM31_Thu, 11 Jan 2001 19:46:53 -0500535_us-ascii January 31, 2001 was the initial service end date for DB2 Connect V5.2. We have, however, extended service to June 30, 2001. For information on service end dates go to this web page: http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/enddates.d2w/report.



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



David Ayers @RYCI.COM> on 01/10/2001 11:47:42 AM

Please respond to DB2 Data Base Discussion List [...] 21792 61 49_Re: getting field names from DB2 stored procedure15_Benjamin Reches24_Benjamin.Reches@MSDW.COM31_Thu, 11 Jan 2001 20:01:52 -0500664_us-ascii IN JDBC:

rs=stmt.getresultSet() metaData=rs.getMetaData() cols=metaData.getColumnCount() for(i=1;i<=cols;i++) String colName=metaData.getColumnName(i) Object value=rs.getObject(i)

Ben

Peter Van Eeuwen wrote:

> It has been our experience that you WILL NOT get column names in the cursor(resultset). > > We had a similiar issue and the solution was for the application, your case VB, in ours EJB, to access the columns by displacement order not column name. > > If anyone has had success naming column names in a cursor; I too would be interested. > > P. Van Eeuwen > > On Wed, 03 January 2001, nag gan wrote: > > > > > Hi, [...] 21854 51 43_Re: end of service dates - db2 connect v5.x0_15_leon@CA.IBM.COM31_Thu, 11 Jan 2001 20:02:53 -0500601_us-ascii Since we are on the "end-of-service date for Db2 Connect V5.2" subject, I'd like to encourage people (OK this is a plug) to migrate to V7.1 as soon as possible. Besides providing a lot of new function, V7.1 will give you a very significant performance boost. Performance will be improved even with a straight migration even if advanced V.1 functions are not used. I am including a graph that illustrates performance improvements for a Java application that uses JDBC API. Improvements for ODBC and DB2 CLI applications are similar. As you can see in this graph, DB2 Connect V7.1 FixPack [...] 21906 76 43_Re: end of service dates - db2 connect v5.x10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 12 Jan 2001 11:14:43 +1000665_ISO-8859-1 Thanx for that clarification, Leon.

Btw, Cd u post the graph again ? I didnt get it. (Did anybody else on the get it??)

Cheers, Kals





-----Original Message----- From: leon@CA.IBM.COM [SMTP:leon@CA.IBM.COM] Sent: Friday, January 12, 2001 12:03 PM To: DB2-L@RYCI.COM Subject: Re: end of service dates - db2 connect v5.x

Since we are on the "end-of-service date for Db2 Connect V5.2" subject, I'd like to encourage people (OK this is a plug) to migrate to V7.1 as soon as possible. Besides providing a lot of new function, V7.1 will give you a very significant performance boost. Performance will be improved [...] 21983 126 18_Re: Update Problem15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Fri, 12 Jan 2001 12:17:09 +1100453_- This might look rather inelegant and cumbersome but we've got around similar problems in the past by segregating the update statement into a series of update statements along the lines of

update tab1 set c_text = ' first n characters ' where predicates . . . ; update tab1 set c_text = c_text CONCAT ' next n characters ' where predicates . . . ; update tab1 set c_text = c_text CONCAT ' next n characters ' where predicates . . . ; . . . [...] 22110 27 37_DB2 dasd extent hedge trivia question0_19_Tim.Lowe@STPAUL.COM31_Thu, 11 Jan 2001 19:25:58 -0600492_us-ascii Hello list, my memory needs some help with a DB2 trivia question that is bothering me, and I hope that with all of the experience out there, someone remembers better than I do.

If my memory serves me right, then many years ago, DB2 requested a new dasd extent when less than 50% of the current extent was available. (DB2's dasd extent "hedge") But, several years ago, this changed to requesting another dasd extent when less than 10% of the current extent was available. [...] 22138 63 51_Re: Db2 Connect and UDB/OS390 version compatibility0_15_leon@CA.IBM.COM31_Thu, 11 Jan 2001 20:27:00 -0500381_us-ascii DB2 Connect V5.2 will work with all versions of DB2 for OS/390 including V6.1. However, features that are new to V6.1 or V7.1 of the DB2 for OS/390 product may not be available to DB2 Connect V5.2 applications. To fully exploit new function you should upgrade to DB2 Connect V7.1. I also strongly recommend applying FixPack 2 when it becomes available in a few days. [...] 22202 97 41_Re: DB2 dasd extent hedge trivia question15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 12 Jan 2001 12:48:04 +1100598_- Hi Tim,

I remember the 50% rule, but not whether that was right or not and not at what release that is. Helpful, huh? Anyway, found this in the DB2 for OS/390 V5 manual:

5.5.6.1.1 Extending DB2-Managed Data Sets

When the data set is created, DB2 always allocates a primary allocation space on a volume that has space available and is specified in the DB2 storage group. Any new extension always gets a secondary allocation space. When the extensions reach the end of the volume, DB2 accesses all candidate volumes from the DB2 storage group and issues the access method [...] 22300 47 21_Unique where not null12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Fri, 12 Jan 2001 16:16:52 +1100519_- Dear Comrades (in DB2),

I find myself contemplating an index. This 20-byte field is initially blank/null, but after some other information gathering is populated for life. The field needs to be searchable (hence an index).

In a brief interlude I find myself wishing for IMS and sparse indexing. As I'll abhor the long RID chains on the NULL instance of this field, and the hassles that the pseudo delete will cause when going from a NULL to a valid value. (Definitely an index to REORG frequently) [...] 22348 69 28_AW: -725 sqlcode DB2 Connect12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 12 Jan 2001 08:41:01 +0100674_iso-8859-1 Dave,

SQLCODE -725 (SQLSTATE 42721) means that an invalid value is supplied to a specific special register.

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 Software Development & Technology Center Knowlegde Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 22418 78 21_Re: sql help / advice16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 12 Jan 2001 11:45:55 +0530591_- Hi Terry,

Could you please explain few things out of your comments ? My questions out of your answers lies in this line of yours :

"** and the optimizer (who will complain if you have not qualified any ambiguous column references - except subqueries - another story).**"

1) Why and what will optimizer complain ? Do you mean we get some warnings or it takes some slightly more time for optimizer to qualify the columns (mainly for dynamic queries) during the processing or it happens in the process of query rewriting, however, even then some time will be taken. [...] 22497 71 25_Re: Unique where not null14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Fri, 12 Jan 2001 03:26:23 -0600324_iso-8859-1 My understanding of 'Unique where not null' is that only rows that contain non-null values appear in the index. Rows with a Null in the column would not be indexed, and if did a SELECT ... WHERE COL IS NULL you would get a TS scan.

If this is right, then there would be no RID chain for Null entries. [...] 22569 123 43_Re: end of service dates - db2 connect v5.x16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Fri, 12 Jan 2001 10:00:23 +0100406_us-ascii no I don't think anybody got the image, since it was moved to an attachment and the list does not accept almost any attachment...

Aurora Emanuela Dell'Anno Systems Engineer Sistemi Informativi S.p.A. e-mail: aurora.dellanno@sistinf.it tel. +39 335 7429486

no trees were killed in the sending of this message. however - a large number of electrons were seriously inconvenienced. [...] 22693 20 43_Re: end of service dates - db2 connect v5.x10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Jan 2001 10:32:34 +0100366_iso-8859-1 ohè come va ???

Scusa se non mi hai trovato, ma sto preso con Java e sono fuori ufficio.....

BAci

Max ===============================================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. 22714 16 43_Re: end of service dates - db2 connect v5.x10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Jan 2001 10:33:30 +0100351_us-ascii Sorry list my previous message was for another person...delete it...

Cheers

MAx Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 22731 43 31_Elapsed time problem; need help15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 12 Jan 2001 03:49:29 -0600402_- Hello list

Currently a job is running since yesderday. Doing a quick shot, I have some problems in understanding certain numbers for a SQL in an application trace.

The SQL is as follows:

SELECT MAX(CHECKP_NR) FROM RWTB0512 WHERE VORGANG = :H

Explain shows a one fetch index only access (accesstype I1) with 1 matching index, i.e. the best accesspath DB2 is offering. [...] 22775 134 41_Re: DB2 dasd extent hedge trivia question13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Fri, 12 Jan 2001 09:56:57 -0000492_iso-8859-1 Wasn't the 50% rule for type 1 index extents, or is my memory also fading?

Adrian

-----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: 12 January 2001 01:48 To: DB2-L@RYCI.COM Subject: Re: DB2 dasd extent hedge trivia question



Hi Tim,

I remember the 50% rule, but not whether that was right or not and not at what release that is. Helpful, huh? Anyway, found this in the DB2 for OS/390 V5 manual: [...] 22910 26 13_see you along16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Fri, 12 Jan 2001 10:39:50 +0100348_us-ascii hello there,

just a quick note since I am changing companies and hope to be joining the lists again from my new post - in about a month's time.

thanks to all the people who have helped me in my current and previous assignments - see you soon!

Aurora Emanuela Dell'Anno Systems Engineer Sistemi Informativi S.p.A. [...] 22937 132 21_Re: sql help / advice10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Fri, 12 Jan 2001 04:08:15 -0600401_- Consider the following query:

SELECT NAME FROM SYSIBM.SYSTABLES, SYSIBM.SYSDATABASE WHERE DBNAME = NAME AND NAME = 'xxxxx' AND BPOOL = 'BP0';

The references to 'NAME' are ambiguous. The column 'NAME' exists on both tables. The 'complaint' which Terry refers to is a -203 SQL Error ('A REFERENCE TO COLUMN NAME IS AMBIGUOUS'). This can be resolved by the use of correlation names: [...] 23070 32 22_How do deadlocks work?0_19_mike.holmans@BT.COM31_Fri, 12 Jan 2001 11:11:17 -0000357_- I know that when it detects a deadlock between two or more processes, DB2 blows at least one of the processes out and rolls it back, allowing one or more of the others to continue, but I can't seem to find a detailed description of how it decides which one to terminate and which one to let carry on. Can anyone shed any light on the algorithm used? [...] 23103 19 26_Re: How do deadlocks work?15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 12 Jan 2001 05:27:01 -0600341_- Mike

I think this is one of the things, IBM hides, because it could be changed from version to version. I believe, I think it's in the administration guide, that the process, which causes the least costs rolling back, will be terminated.

Maybe somebody else in the list knows the details

Hope this helps a little [...] 23123 39 49_Re: moving data from one DB2 subsystem to another10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM31_Fri, 12 Jan 2001 07:02:54 -0500301_iso-8859-1 Hi Aurelia. If you would like to be able to move not just whole areas of data , but managable, RELATIONALLY INTACT subsets of data between subsystems, you might like to look at Move for DB2 from Princeton Softech. Don't worry, I'm a techie, not a salesman. The tool does work though!. [...] 23163 57 26_Re: How do deadlocks work?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 12 Jan 2001 07:19:45 -0500426_iso-8859-1 Hi Mike,

I believe in the good ol' days, this was documented. I think those days go back to DB2 1.2.

All I can find now, scanning the DB2 v6 bookshelf, is the following from the Admin guide:

"DB2 selects one of the transactions and rolls it back, releasing its locks. That allows the other transaction to proceed to completion and release its locks also." © Copyright IBM Corp. 1983, 1999 [...] 23221 22 21_Re: DB2 UDB Version 713_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 12 Jan 2001 07:35:01 -0500312_iso-8859-1 As Tim said, it was announced at the DB2 Tech conf in the US last fall that DB2 v7 for OS/390 would probably be GA toward the end of March 2001. It was also stated that in December 2000 it would be in Extended Availability status and if you are keen to get it sooner, talk to your local IBM rep. [...] 23244 70 26_Re: How do deadlocks work?10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Fri, 12 Jan 2001 06:38:15 -0600617_ISO-8859-1 "DB2 resolves a deadlock by rolling back any action already taken by the transaction in the deadlock that has the fewest log records or most current lock if IMS is the transaction manager."

Copyright© 2000, by Gabrielle & Associates,4



On Fri, 12 Jan 2001 07:19:45 -0500, Seibert, Dave wrote:

>Hi Mike, > > I believe in the good ol' days, this was documented. I think those days >go back to DB2 1.2. > >All I can find now, scanning the DB2 v6 bookshelf, is the following from the >Admin guide: > >"DB2 selects one of the transactions and rolls [...] 23315 42 49_Re: moving data from one DB2 subsystem to another15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Fri, 12 Jan 2001 08:12:22 -0500277_iso-8859-1 Move for DB2 from Princeton Softech works great for us. Not only can you move single tables, but you can also move many related tables all at the same time - and even apply SQL to any/all of the tables involved. It will move data only, structures only or both. [...] 23358 57 49_Re: moving data from one DB2 subsystem to another10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Fri, 12 Jan 2001 07:31:38 -0600424_- I'll ditto that. In one company I worked for, I evaluated the Princeton Tools, and compared them against those offered by their competitors. I recommended we purchase them, as they would be an invaluable aid for the use of developers in system testing. My ulterior motive was that (as a DBA) I found them (especially MOVE) extraordinarily useful. The only drawback is that they're quite pricey. Well worth it though. [...] 23416 27 45_moving data from one DB2 subsystem to another19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US29_Fri, 12 Jan 2001 08:44:42 EST365_us-ascii Hi,

I do this on a periodic basis and I do DSN1COPY from Image copies. After a one time exercise of checking the OBID/DBID/PSID, I need only to review those which have been dropped and defined in the interim. This way, I do not have to stop the source database explicitly for this purpose. I then rebuild the indexes and do runstats & rebinds. [...] 23444 56 49_Re: moving data from one DB2 subsystem to another10_Kathy Culp21_Kathy_Culp@BCBSTX.COM31_Fri, 12 Jan 2001 07:48:27 -0600484_us-ascii Used it lots during Y2K testing moving data between production and the Y2K LPAR used for testing. I would unload from most current image copy. Got DASD Manager? It will ease the JCL creation pain as you can use wild cards to select the tables to unload and load.







"Drucker, Aurelia" To: DB2-L@RYCI.COM Subject: moving data from one DB2 subsystem to another Sent by: DB2 Data Base Discussion List [...] 23501 162 21_Re: sql help / advice13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Fri, 12 Jan 2001 08:00:06 -0600368_iso-8859-1 John,

You know far too much about what is going on in my mind :-), but the "another story" was referring to the correlated version.

Sanjeev,

John was right about (what I meant by) the optimizer complaining. Now to bend John's example for the "another story" and make it correlated (for no other reason than to illustrate a point): [...] 23664 57 49_Re: moving data from one DB2 subsystem to another17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Fri, 12 Jan 2001 13:03:20 -0200398_iso-8859-1 Hi,

We are using some homegrown tools which based on naming standards can select a group of tables, build unload/reload jobs and submit all jobs using a job scheduling software. As far as we don´t have any faster utilities, we are using REORG with UNLOAD ONLY. We run it every weekend to move about 1500 tablespaces from production to QA subsystem, and it works fine for us. [...] 23722 81 49_Re: moving data from one DB2 subsystem to another15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 12 Jan 2001 08:25:20 -0600798_us-ascii Try with Platinum FASTLOAD/FASTUNLOAD utitlities.

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 01/12/2001 08:20 AM ---------------------------



John Hardy on 01/12/2001 07:31:38 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: moving data from one DB2 subsystem to another





I'll ditto that. In one company I worked for, I evaluated the Princeton Tools, and compared them against those offered by their competitors. I recommended we purchase them, as they would be an invaluable aid for the use of developers in system testing. My ulterior motive was that (as a DBA) I found [...] 23804 151 49_Re: moving data from one DB2 subsystem to another10_Bill Disch23_bill.disch@GENMILLS.COM31_Fri, 12 Jan 2001 08:27:47 -0600612_iso-8859-1 Aurelia, I have accomplished this two ways: 1) I have jobs set-up to do this bi-monthly fram an SAP R/2 application to duplicate the production data in two additional DB2 subsystems. The first subsystem uses DSN1COPY with the SYSXLAT option to copy from the weekly full IMAGECOPY (performed with BMC COPY+) then all indices are rebuilt. The second subsystem also uses DSN1COPY with the SYSZLAT option but uses the datasets of the first copied subsystem as input, much faster without the tape mounts, etc. 2) During an upgrade of the R/2 software, many table structures changed - minor changes. I [...] 23956 103 49_Re: moving data from one DB2 subsystem to another0_20_John_Lendman@FPL.COM31_Fri, 12 Jan 2001 09:45:07 -0500408_iso-8859-1 Looks like I missed the question, but from this response I can guess it.

We have a process called Homogenous Database System copy. This was created by IBM/SAP to copy one entire DB2 subsystem to another. We have modified it somewhat and we now use it to copy our SAP test system to each other. We have around 8,000 tablespace and 19,000 indexes that we copy. It runs about 8-10 hours. [...] 24060 36 32_RRSAF .....why JDBC wants it ???10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Jan 2001 15:43:08 +0100474_us-ascii We implemented Java in our test system, we installed the new driver and we run the samples pgms. We had some problems but all worked fine after a while.

Now we are installing JDBC in another subsystem. We made the same things we did for the test subsystem BUT NOW, and we don't know why, when we run a sample program, the same program want to connect to target DB2 using RRSAF, not CAF as specified in /etc/profile using DB2SQLJATTACHTYPE=CAF variable. [...] 24097 24 26_Re: How do deadlocks work?10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Jan 2001 15:50:24 +0100403_us-ascii DB2 destroys/kills/eliminates the application who did the lowest number of log records, i.e. the application with the faster rollback. LESS I/O is one of the most important factor in DB2's decisions. It doesn't matter if that application started

before the other one.



HTH

Max Scarpa

All disclaimer apply, long live Cindy Crawford, benedico vos, etc etc [...] 24122 90 49_Re: moving data from one DB2 subsystem to another15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Fri, 12 Jan 2001 09:16:05 -0600610_iso-8859-1 Aurelia,

Since you have said that you have BMC utilities, you can take Bill's suggestion one step further. Instead of using DSN1COPY, you could use R+/RESOURCE MAXIMIZER. It works much like DSN1COPY, but with a few advantages. First, R+/RM is much faster. Secondly, if you have shared DASD between your subsystems, you can migrate your data to a selected QUIESCE point. This is very important if your production image copies are SHRLEVEL CHANGE. It will also allow you to get a more recent image of your production subsystem if your image copies are old. If you have very large indexes, [...] 24213 90 25_Re: Unique where not null15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Fri, 12 Jan 2001 09:21:41 -0600391_iso-8859-1 This is not true. A UNIQUE WHERE NOT NULL index is structured like a non-unique index. It allows for a RID chain, but only chains the RIDs if the value is NULL.

Donna Di Carlo BMC Software

-----Original Message----- From: Vassie, Edward [mailto:Edward_Vassie@bmc.com] Sent: Friday, January 12, 2001 3:26 AM To: DB2-L@RYCI.COM Subject: Re: Unique where not null [...] 24304 24 60_Spanning More than One Volume When Copying the DB2 Directory13_Bernie Polley13_berns@JHU.EDU31_Fri, 12 Jan 2001 10:33:08 -0500425_US-ASCII Job DSNTIJIC in SDSNSAMP creates image copies of the db2 directory and catalog. My question concerns the following comment in the job stream: The Image Copy for Each Table Space Is Done in a Separate Step So That the Job Will Execute Successfully When an Image Copy Output Data Set Spans More than One Volume." Why should there be a problem spanning more than one volume if the copies are done in one job step? [...] 24329 138 45_Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN12_Rajiva Sinha29_Rajiva.Sinha@OCSE.STATE.AR.US31_Fri, 12 Jan 2001 09:34:02 -0600661_iso-8859-1 Hi Ujjal !

It was nostalgic to see the HRIA code and column names. 8 years back, I was working on the data modeling part of it. Seems like sanjeev has worked in that group too.



Rajiv Sinha







> -----Original Message----- > From: ujjwal@LOT.TATASTEEL.COM [SMTP:ujjwal@LOT.TATASTEEL.COM] > Sent: Wednesday, January 10, 2001 10:30 PM > To: DB2-L@RYCI.COM > Subject: Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN > > Hi Raymond , > The accesspath for the querybelow is Matchcols=0,AccessType= R . The > other query I cannot do anything because the first column of that > particular query is a range [...] 24468 24 25_Re: Unique where not null0_19_Tim.Lowe@STPAUL.COM31_Fri, 12 Jan 2001 09:46:00 -0600483_us-ascii Nick, Wouldn't use of an index index that is unique where not null depend on the index stats gathered by runstats, just as with other access paths? (I would expect that FREQVAL stats would show the percentage of rows that are null, so that the optimizer could make a "wise" decision. But, I am just guessing. Could the optimizer "understand" that if 90% of the values are null, that 10% of the values are not null, and then use that to choose the correct access path?) [...] 24493 84 49_Re: moving data from one DB2 subsystem to another11_rick creech18_ykcirc@HOTMAIL.COM31_Fri, 12 Jan 2001 09:44:46 -0600673_- Hi,

Since you have BMC utilities, maybe you also have BMC CHANGE MANAGER which allows you to migrate structures and data from 1 subsystem to another, and generates all the jcl to do it including the BMC utility JCL.

HTH,

Rick Creech



>From: John Hardy >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: moving data from one DB2 subsystem to another >Date: Fri, 12 Jan 2001 07:31:38 -0600 > >I'll ditto that. In one company I worked for, I evaluated the Princeton >Tools, and compared them against those offered by their competitors. I >recommended we purchase [...] 24578 42 17_Re: QMF standards11_Mohan Reddy28_Mohan.Reddy@OCSE.STATE.AR.US31_Fri, 12 Jan 2001 09:41:21 -0600592_iso-8859-1 please ignore - this is just a test

-----Original Message----- From: Alison Pelletier [mailto:alison_pelletier@YAHOO.COM] Sent: Wednesday, January 10, 2001 5:46 PM To: DB2-L@RYCI.COM Subject: QMF standards



Hello everyone. I've been tasked with writing standards/procedures for administering QMF in our production environment. I have many ideas and past experience, but it would sure help if I had a head- start and I don't want to miss anything. It's been givin' out carte blanche before my reign and it's killing us! Anyone willing to share their doc.? [...] 24621 112 49_Re: moving data from one DB2 subsystem to another13_Johnson, Bill18_bjo23@ALLSTATE.COM31_Fri, 12 Jan 2001 09:56:33 -0600294_- I agree with Rick regarding using BMC's Change Manager. I did this about a year ago. Create a migrate worklist. Run the migrate part on the source machine and the receive part on the target machine. It helps to have shared dasd but not necessary. Bill Johnson DBA Allstate Insurance Co. [...] 24734 174 21_Re: sql help / advice10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Fri, 12 Jan 2001 10:27:23 -0600613_- Sorry for trying to read your mind. Especially now I know that my interpretation of your remarks did not correlate (;)) with their meaning.



On Fri, 12 Jan 2001 08:00:06 -0600, Terry Purcell wrote:

>John, > >You know far too much about what is going on in my mind :-), but the >"another story" was referring to the correlated version. > >Sanjeev, > >John was right about (what I meant by) the optimizer complaining. Now to >bend John's example for the "another story" and make it correlated (for no >other reason than to illustrate a point): > >SELECT NAME [...] 24909 82 35_Re: Elapsed time problem; need help13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Fri, 12 Jan 2001 10:39:57 -0600425_iso-8859-1 Walter,

There are many questions to be asked regarding your problem:

Firstly, which explain shows an I1 fetch? Is it a dynamic explain or EXPLAIN(YES) on the most recent bind?

What access path does Omegamon (I'm assuming it's an Omegamon trace) show? I'm no Omegamon expert, but it looks like you have an ascending, rather than descending index. Has someone dropped the descending index? [...] 24992 34 49_Re: moving data from one DB2 subsystem to another16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Fri, 12 Jan 2001 09:45:25 -0700574_- I'd look at using DSN1COPY rather than unload/load.

Duane

> -----Original Message----- > From: Drucker, Aurelia [SMTP:ADrucker@KMART.COM] > Sent: Thursday, January 11, 2001 4:49 PM > To: DB2-L@RYCI.COM > Subject: moving data from one DB2 subsystem to another > > Looking for advice regarding a good way to move data, from a quite big > number of quite big tables (150) from one DB2 subsystem to another. > > We are running DB2 V5; no sysplex > We are having BMC utilities and I intend to use BMC UNLOAD+ /LOAD+ > > Any help will be appreciated, > TIA > [...] 25027 106 39_Re: FW: CA Datacom/DB to DB2 Conversion12_Dallas Focht21_dallas.focht@PMIC.COM31_Fri, 12 Jan 2001 10:48:52 -0600553_us-ascii We went through a similar conversion and used a company called P.O.V. Information technology out of Belgium. They converted DATACOM/IDEAL code to DB2/COBOL code for us. Was not perfect and there was still a far amount of additional work to do, mostly for error handling, but it definitely helped cut down the code conversion effort. Their e-mail address is POV.USA@pov-it.com. URL is http://www.pov-it.com. I have an 800 number of 800.285.8582. Not sure how accurate it is. Sacramento Office 916.646.3994, Belgium Office 011.32.15.287628. [...] 25134 23 36_Using DB2 PM for exception reporting10_Tom Glaser14_tg5444@SBC.COM31_Fri, 12 Jan 2001 10:35:02 -0600429_- Is there anyone out there using DB2 PM to perform "periodic exception processing?" That is, using DB2 PM to monitor your subsystem so that in the event that some threshold is reached (such as n number of deadlocks are reached, or a thread using up 20 minutes of CPU time), DB2 PM will notify you. I would be interested to know what you are monitoring. Are you monitoring EDM pool failures, buffer pool thresholds reached? [...] 25158 36 12_load utility12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Fri, 12 Jan 2001 10:59:47 -0600589_iso-8859-1 I have always used the load utility when my input data was either from an unload or a flat file that had packed data unpacked. I now have an input file with comp and comp-3 data. If anyone could tell me how to tell the load utility how to handle this data, I would appreciate it. The input "looks like" for instance bal_fiscal_year pic s9(4) usage comp bal_fiscal_mo pic s9(4) usage comp bal_dept pic x(4) bal_budget pic s9(10)v9(2) usage comp-3. This does not work - LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE RACADM2.BALANCES PART 1 REPLACE ( BAL_FISCAL_YEAR POSITION(1) [...] 25195 22 46_DB2 stored procedure not returning result set.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jan 2001 12:09:23 -0500414_iso-8859-1 Does anybody know in what situation a DB2 stored procedure does not return a result set back to calling program even if the cursor is open with return has some rows. The SQLCODE is coming back as 0 instead of +466. We are on DB2 V6.0 on OS/390 2.9. If I change the Schema to SYSPROC from TEST, everything works fine. We use WLM address space for stored procedure and I know it is getting executed. [...] 25218 62 16_Re: load utility10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Fri, 12 Jan 2001 11:15:55 -0600347_- bal_dept is 4 bytes. You think it is 3, so you are picking off 7 bytes for bal_budget from position 8 instead of position 9.

If you change your load card like so:

> BAL_FISCAL_YEAR POSITION(1) > SMALLINT , > BAL_FISCAL_MO POSITION(3) > SMALLINT , > BAL_DEPT POSITION(5) > CHAR( 4) , > BAL_LAST_YR_LTD POSITION(9: 15) >DECIMAL [...] 25281 109 16_Locking Question12_Dallas Focht21_dallas.focht@PMIC.COM31_Fri, 12 Jan 2001 11:25:08 -0600496_us-ascii We recently encountered an error were a FOCUS program selecting from a view caused an update program to error when trying to update one of the tables involved in the view. The FOCUS plan is bound with ISOLATION(CS) and CURRENTDATA(NO). The error messages indict the resource being held was the table. Can anyone explain why this woul be the case? Also, the tablespace id defined LOCKRULE(P) and LOCKMAX(0) so there should be no lock escalation. The messages and dynamic SQL follow. [...] 25391 51 50_Re: DB2 stored procedure not returning result set.0_24_db46@DAIMLERCHRYSLER.COM31_Fri, 12 Jan 2001 13:05:05 -0500492_us-ascii You may have to stop and start the procedure. Or do an LLA refresh on the addres space for the stored procedures.

Dean









"Philip, Sibimon" @RYCI.COM> on 01/12/2001 12:09:23 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DB2 stored procedure not returning result set. [...] 25443 71 29_Case Sensitivity DB2 V6 vs V50_21_kkane@PAINEWEBBER.COM31_Fri, 12 Jan 2001 13:15:49 -0500315_iso-8859-1 Is anyone aware of any difference in the way in which DB2 V6 handles combined upper/lower case character strings vis a vis DB2 V5 ? If so, is there an installation parm which can disable this so we can process with impact upon conversion?

Thanks much

Kenneth M. Kane





25515 64 20_Re: Locking Question13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Fri, 12 Jan 2001 10:23:43 -0800410_iso-8859-1 Even with isolation CS, DB2 will take a S lock over that 'page' and if your update program tries to update any record on that page, it will try to obtain an X lock. S and X are incompatible and hence the timeout.

Ashish.

-----Original Message----- From: Dallas Focht [SMTP:dallas.focht@PMIC.COM] Sent: Friday, January 12, 2001 9:25 AM To: DB2-L@RYCI.COM Subject: Locking Question [...] 25580 53 50_Re: DB2 stored procedure not returning result set.16_Michael McCarthy18_mmccarthy@DTCC.COM31_Fri, 12 Jan 2001 13:22:27 -0500447_us-ascii Sibi,

We have a PMR open with IBM on a similiar issue. When the stored procedure is defined with commit on return yes, the +0 SQLCODE from the commit is being returned instead of the +466. they had a fix for this problem with local apps calling a stored procedure, and said that we may have a variation on the same problem. We are connecting remotely via Websphere OS/390. If you would like to track the PMR, it is 33935,7td. [...] 25634 69 50_Re: DB2 stored procedure not returning result set.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jan 2001 13:27:57 -0500460_- When I call procedure SYSPROC.MQ0002SP and TEST.MQ0002SP, it is executing the same load module in the same WLM address space, but 1st one returning result set, but the other one not. The only difference is SCHEMA.

Thanks...Sibi

-----Original Message----- From: db46@DAIMLERCHRYSLER.COM [mailto:db46@DAIMLERCHRYSLER.COM] Sent: Friday, January 12, 2001 12:05 PM To: DB2-L@RYCI.COM Subject: Re: DB2 stored procedure not returning result set. [...] 25704 76 49_Re: moving data from one DB2 subsystem to another14_Johnston, Gary29_Gary.Johnston@CCRA-ADRC.GC.CA31_Fri, 12 Jan 2001 13:20:55 -0500451_iso-8859-1 It really depends on what you're doing at the receiving end. Here (Canada Customs), we have a requirement to 'refresh' about 120 DB2 tables from one of our online/update subsystem to a DS subsystem on a different LPAR on an overnight basis. We use DFDSS to copy the tables with the "CONCURRENT COPY" option. It's a fast data mover (our 8 jobs run in a maximum of 90 minutes at the moment, and that's copying about 100 3390's of data). [...] 25781 54 16_Re: load utility12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Fri, 12 Jan 2001 12:40:13 -0600559_iso-8859-1 Oh - my - i just figured it out - it wasn't the load that was wrong it was that there were duplicates per key. I'm adding a sort with a summing feature. I'm sorry I bothered you guys.



> -----Original Message----- > From: Reid, Louise [SMTP:ReidL@CI.FORT-WORTH.TX.US] > Sent: Friday, January 12, 2001 12:00 PM > To: DB2-L@RYCI.COM > Subject: load utility > > I have always used the load utility when my input data was either from an > unload or a flat file that had packed data unpacked. I now have an input > file with comp and [...] 25836 87 50_Re: DB2 stored procedure not returning result set.13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 12 Jan 2001 12:42:01 -0600462_iso-8859-1 Remember that the SP SQLCode=0 means that the SP worked, it does not indicate anything about the success or otherwise of the SQL within the program. I would try and get the SQLCode of the open cursor statement for the RETURN cursor.



-----Original Message----- From: Philip, Sibimon [mailto:SPhilip@CSXLINES.COM] Sent: Friday, January 12, 2001 12:28 PM To: DB2-L@RYCI.COM Subject: Re: DB2 stored procedure not returning result set. [...] 25924 73 20_Re: Locking Question12_Dallas Focht21_dallas.focht@PMIC.COM31_Fri, 12 Jan 2001 12:46:43 -0600602_us-ascii Yes, but the message says that the unavailable resource was the table and not the page. If it were the page I would have expected the message to look similar to

REASON 00C9008E TYPE 00000302 NAME DBNAME.TBSPACE .X'nnnnnn'



"Mohan, Ashish" wrote:

> Even with isolation CS, DB2 will take a S lock over that 'page' and if your > update program tries to update any record on that page, it will try to > obtain an X lock. S and X are incompatible and hence the timeout. > > Ashish. > > -----Original Message----- > From: Dallas Focht [SMTP:dallas.focht@PMIC.COM] > [...] 25998 91 50_Re: DB2 stored procedure not returning result set.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jan 2001 13:48:13 -0500530_- Mike,

Thanks. This is exactly the problem. I changed to COMMIT ON RETURN NO and it started working fine.

I have another problem too. I will explain it with a example.

Cobol program PGM1 call a stored procedure SP1 with SCHEMA = TEST and OWNER = ABCD. The PGM1's the owner is TEST and the PATH is also TEST. But when I bind the package of PGM1, if there is no DB2 stored procedure in Catalog with owner TEST and name SP1 it fails even though I have SP1 with SCHEMA TEST. Now I have to make the owner of [...] 26090 104 50_Re: DB2 stored procedure not returning result set.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jan 2001 13:59:25 -0500381_- One thing I forgot to mention is, if the SCHEMA is SYSPROC then COMMIT ON RETURN does not matter to get +466 SQLcode when there is a result set.

Thanks...Sibi

-----Original Message----- From: Philip, Sibimon Sent: Friday, January 12, 2001 12:48 PM To: 'DB2 Data Base Discussion List' Cc: Hayden, Lee Subject: RE: DB2 stored procedure not returning result set. [...] 26195 99 50_Re: DB2 stored procedure not returning result set.16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Fri, 12 Jan 2001 13:59:40 -0500324_us-ascii Sibi,

Check to see if you have "commit_on_return" set to "Y" on the stored procedure which is NOT returning the result set. If you do, and you have not specified the "with hold" option on the cursor, the result set will get clobbered upon successful return from the stored procedure.

Rick Molera [...] 26295 115 50_Re: DB2 stored procedure not returning result set.11_David Nance16_DWNance@FHSC.COM31_Fri, 12 Jan 2001 14:03:08 -0500355_US-ASCII Sibi, You can use the path(....) in your bind card for pgm1. That says to look for these schemas, when calling SP.

Dave Nance First Health Services, Corp. (804)527-6841

>>> SPhilip@CSXLINES.COM 01/12/01 01:48PM >>> Mike,

Thanks. This is exactly the problem. I changed to COMMIT ON RETURN NO and it started working fine. [...] 26411 99 20_Re: Locking Question11_Chin, Frank20_frank_chin@MERCK.COM31_Fri, 12 Jan 2001 13:58:15 -0500302_ISO-8859-1 When you look at the display: TYPE 00000D01 > NAME 00000259.00000407 the 249 is the PSID of the object that in contention, and from an open question we have with IBM it seems that it can be either a table or tablespace !! We questioned them why and have yet to get a reasonable answer. [...] 26511 33 33_overriding current date in cursor12_Grant Mackay28_Grant_Mackay@PROGRESSIVE.COM31_Fri, 12 Jan 2001 14:18:07 -0500345_us-ascii I have a cobol program that opens a cursor which does some date calculations...

select days(current date) - days(evt_dt) from tablea where days(current date) - days(evt_dt) > 0

For QA testing I want to be able to override the current date in the sql, so I don't have to wait a month for the test condition to be met. [...] 26545 22 14_LDAP Processes15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Fri, 12 Jan 2001 14:27:00 -0500394_iso-8859-1 We are considering an implementation of an LDAP client and wonder if someone has any experience with LDAP security issues - like controlling the users and groups that are authorized to read, modify or otherwise work with the information.

Can LDAP offer secure solutions while managing directory accesses?

Any information on LDAP processing would be helpful to me. [...] 26568 69 37_Re: overriding current date in cursor13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Fri, 12 Jan 2001 13:34:54 -0600456_iso-8859-1 Grant,

Try select days(DATE(:WS-CURRENT-DATE)) - days(evt_dt) from tablea where days(DATE(:WS-CURRENT-DATE)) - days(evt_dt) > 0

DB2 can cast the datatype of literal values, ie. there is no fixed datatype, so it assumes one. When you specify a host variable, DB2 picks up the datatype from your explicit definition. In this case, probably PIC X(10) or CHAR(10) in DB2 speak. Date arithmetic can only be done on date datatypes. [...] 26638 118 50_Re: DB2 stored procedure not returning result set.16_Michael McCarthy18_mmccarthy@DTCC.COM31_Fri, 12 Jan 2001 14:36:57 -0500534_us-ascii Sibi,

I'm only guessing, but you may want to check to make sure the plan that is executing PGM1 contains the collection id's of both the stored procedure and the client program. hope this helps,

-Mike.









"Philip, Sibimon" on 01/12/2001 01:48:13 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Michael McCarthy/DTC) Subject: Re: [DB2-L] DB2 stored procedure not returning result set. [...] 26757 75 64_Re: Spanning More than One Volume When Copying the DB2 Directory12_Kirk Hampton16_khampto1@TXU.COM31_Fri, 12 Jan 2001 13:54:04 -0600547_us-ascii I have hit this situation many times at our shop, here is the scenario. You have multiple COPY TABLESPACE commands in one job step, each with its own COPYDDN in the JCL, and each being directed via JCL to stack on a tape using LABEL and VOL=REF=the previous DDNAME. So the job goes running, writes the first image copy out to the tape, starts writing the second (or 15th or 30th) image copy, depending on the size of your tablespaces, and the tape volume fills up. So a second tape is mounted, and the remainder of the 2nd copy gets [...] 26833 31 20_sysadm_group dilemma11_Randy Perry14_rperry@ITI.COM31_Fri, 12 Jan 2001 15:48:43 -0400408_US-ASCII Hi folks,

I work for an educational institute, and we're in the middle of teaching a course in database administration with DB2. Some of our students have encountered what appears to be a catch-22 regarding the sysadm_group parameter, which they've accidentally set to an NT group (when they were supposed to be setting the sysctrl_group parameter). (Oh, we're using DB2 V7.1 on WinNT.) [...] 26865 123 64_Re: Spanning More than One Volume When Copying the DB2 Directory14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 12 Jan 2001 13:03:36 -0700650_iso-8859-1 We are stacking multiple datasets to multiple tapes for image copy jobs. Datasets span volumes without any problems. Are you using UNIT=AFF as well as the RETAIN subparm of the VOL=?



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Kirk Hampton[SMTP:khampto1@TXU.COM] > Reply To: DB2 Data Base Discussion List > Sent: Friday, January 12, 2001 12:54 PM > To: DB2-L@RYCI.COM > Subject: Re: Spanning More than One Volume When Copying the DB2 > Directory > > I have hit this situation many times at our shop, here is the > scenario. > You have multiple COPY TABLESPACE [...] 26989 152 64_Re: Spanning More than One Volume When Copying the DB2 Directory17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Fri, 12 Jan 2001 14:23:48 -0600307_iso-8859-1 It sounds like you may have this covered, BUT ...

Make sure that each VOL=REF refers to the COPYDDN which immediately preceeds it. A common error -which causes these exact symptoms- is to have them all refer back to the first DD statement which you cloned 'n' times to build the JCL. [...] 27142 195 64_Re: Spanning More than One Volume When Copying the DB2 Directory14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 12 Jan 2001 13:27:21 -0700633_iso-8859-1 Very true. The REF= *** MUST *** point back to the previous dataset on the tape and not the first one.



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Kornblum, Kenneth[SMTP:Kenneth_Kornblum@BMC.COM] > Reply To: DB2 Data Base Discussion List > Sent: Friday, January 12, 2001 1:23 PM > To: DB2-L@RYCI.COM > Subject: Re: Spanning More than One Volume When Copying the DB2 > Directory > > It sounds like you may have this covered, BUT ... > > Make sure that each VOL=REF refers to the COPYDDN which immediately > preceeds > it. A common error -which [...] 27338 65 24_Re: sysadm_group dilemma15_Stacey L Waters23_waterss1@NATIONWIDE.COM31_Fri, 12 Jan 2001 14:26:15 -0600487_us-ascii Randy, If you set the SYSADM_GROUP to a local NT group read on if not disregard....

Create a NT domain global group, name the same as the local NT group, and add it to the Administrators group on the Domain Controller. Drop the local NT group. Add the new NT to the local Administrators group. DB2 will reference the NT domain group for security. Then you will have the authority to change the parameter. If you still have issues check the parameter DB2_GRP_LOOKUP. [...] 27404 138 64_Re: Spanning More than One Volume When Copying the DB2 Directory0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 12 Jan 2001 14:31:06 -06001228_us-ascii We don't get multiple tape mounts either

JCL extract

//DB2U010 EXEC DSNUPROC,SYSTEM=DBP6,UID='G31SIC01',UTPROC='', // LIB='DB2.DBP6.SDSNLOAD' //COPY31A DD DSN=hlq.PBKP1.FULL.ts.PART01(+1), // DISP=(NEW,CATLG,CATLG), // UNIT=TAPE,LABEL=1,VOL=(,RETAIN,,20), // DCB=(MODEL.LABEL,TRTCH=COMP) //COPY32A DD DSN=hlq.PBKP1.FULL.ts.PART02(+1), // DISP=(NEW,CATLG,CATLG), // UNIT=TAPE,LABEL=2,VOL=(,RETAIN,,20,REF=*.COPY31A), // DCB=(MODEL.LABEL,TRTCH=COMP) . . . continues on for 10 partitions

extracted from a job run:

hlq.PBKP1.FULL.ts.PART01.G2045V00 CATALOGED VOL SER NOS= 005551,005550. hlq.PBKP1.FULL.ts.PART02.G2034V00 CATALOGED VOL SER NOS= 005550,005344. hlq.PBKP1.FULL.ts.PART03.G2031V00 CATALOGED VOL SER NOS= 005344,005340. hlq.PBKP1.FULL.ts.PART04.G2019V00 CATALOGED VOL SER NOS= 005340,005635. hlq.PBKP1.FULL.ts.PART05.G2011V00 CATALOGED VOL SER NOS= 005635,005816. hlq.PBKP1.FULL.ts.PART06.G2010V00 CATALOGED VOL SER NOS= 005816,005814. hlq.PBKP1.FULL.ts.PART07.G1999V00 CATALOGED VOL SER NOS= 005814,005713. hlq.PBKP1.FULL.ts.PART08.G1997V00 CATALOGED VOL SER NOS= 005713,005809. hlq.PBKP1.FULL.ts.PART09.G1990V00 CATALOGED VOL SER NOS= 005809. hlq.PBKP1.FULL.ts.PART10.G1988V00 [...] 27543 136 24_Re: sysadm_group dilemma14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 12 Jan 2001 12:32:34 -0800277_iso-8859-1 Randy, Make sure that the id that you are using is part of the sysadm group and that the group has Domain or local admin authority. You can look at this via user manager for domains. If that doesn't work, try using a domain admin id. That should work. HTH Phil [...] 27680 209 18_FW: LDAP Processes14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 12 Jan 2001 12:33:20 -0800619_iso-8859-1



-----Original Message----- From: Philip Gunning To: 'McClendon, Rick ' Sent: 1/12/01 12:28 PM Subject: RE: LDAP Processes

Rick, Access can be controlled by SIDs, ACLs,group policies, and through object ownership, and delegation. On Windows 2000, administration can be done from the Active Directoy Users option in the Administrative Tools group. Additionally, DPA, SSL, and Kerberos can be used. There is a good amount of information in, "Mastering Active Directory", author Robert King, Sybex. It has a chapter on "Ensuring Security in an Active Directory environment." HTH Phil [...] 27890 57 24_Re: sysadm_group dilemma16_Michael McCarthy18_mmccarthy@DTCC.COM31_Fri, 12 Jan 2001 15:35:12 -0500495_us-ascii Hi Randy,

That parameter change does not take effect until db2 is stopped and started via the db2stop/db2start commands. If this hasn't happened yet, the old sysadm_group should still be in effect.

-Mike.









Randy Perry on 01/12/2001 02:48:43 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Michael McCarthy/DTC) Subject: [DB2-L] sysadm_group dilemma [...] 27948 46 24_Re: sysadm_group dilemma12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Fri, 12 Jan 2001 14:41:41 -0600678_iso-8859-1 To change it back to null/spaces enter: Db2 update dbm cfg using sysadm_group ''





-----Original Message----- From: Randy Perry [mailto:rperry@ITI.COM] Sent: Friday, January 12, 2001 1:49 PM To: DB2-L@RYCI.COM Subject: sysadm_group dilemma

Hi folks,

I work for an educational institute, and we're in the middle of teaching a course in database administration with DB2. Some of our students have encountered what appears to be a catch-22 regarding the sysadm_group parameter, which they've accidentally set to an NT group (when they were supposed to be setting the sysctrl_group parameter). (Oh, we're using DB2 V7.1 on WinNT.) [...] 27995 30 32_Thoughts on Migration Strategies14_DAVID PETERSEN27_dpetersen@KEYSPANENERGY.COM31_Fri, 12 Jan 2001 15:50:50 -0500486_us-ascii Hi All:

We are planning to migrate from v5 to v6 on OS/390. In order to do this we must apply fallback PTFs. Our current maintenance level is 9912. We are going to roll the fallback PTFs through production. Once this is successful we will at a later date migrate to v6. Another approach would be to roll the fallback PTFs along with the migration to v6 at the same time. Any thoughts on either approach? Upsides? Downsides? What is your philosophy for this effort? [...] 28026 16 36_Re: Thoughts on Migration Strategies0_26_truman.g.brown@VERIZON.COM31_Fri, 12 Jan 2001 15:55:41 -0500456_us-ascii I haven't been responsible for migrations in a long time, but unless there's some reason to rush, I'd say make it a staged effort - apply PTF's, get current on maintenance, and then migrate.

Georgre

================================================ 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. 28043 147 36_Re: Thoughts on Migration Strategies14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 12 Jan 2001 12:58:04 -0800577_iso-8859-1 Dave, 1) I would recommend applying the latest maintenance and fallback ptfs. Many times once you get to the new release, you find out that it would have been better to have been current on maintenance as you may find certain new functions don't work right without maintenance that would have been there if you had applied it in the first place. This is even more important with going to V6.1 as a result of all the retrofits and new functionality. I've seen catmaints fail because we were not current. 2) Experience. You might be okay with the first approach, [...] 28191 58 36_Re: Thoughts on Migration Strategies12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Fri, 12 Jan 2001 16:20:09 -0500417_us-ascii i'm currently working on migrating to v6 of DB2. We brought it in as a Serverpac at a set level. I am then applying all the maint they sent with it and then ordering the PSP maint to put on before i migrate to v6. i also applied all the toleration and fallback maint that IBM suggested to my V5 DB2. With this release it sounds like it is better to stay current with maint and follow what IBM suggests. [...] 28250 67 49_Re: moving data from one DB2 subsystem to another12_Mckay, Wayne24_Wayne.Mckay@ATCOITEK.COM31_Fri, 12 Jan 2001 14:23:49 -0700555_iso-8859-1 We are using the following to do this: 1. BMC Recover Plus 2. Move for DB2

Our database is 700+ tables and very relational with over 1400 DB2 RI foreign keys defined. There are numerous cycles in the data paths.

If we want to move the entire database we use BMC Recover Plus with OBID translation. If we want a relational intact subset of the data to create a downsized test database we use Move for DB2. If we want to extract a single account with all it's related data to resolve a production problem we use the Move tool. [...] 28318 19 36_Re: RRSAF .....why JDBC wants it ???0_27_Jodi.Murawski@METAVANTE.COM31_Fri, 12 Jan 2001 15:25:11 -0600474_us-ascii We just recently had the same discovery. I found the explanation on RRSAF in the 'DB2 for OS/390 Applications and Programming Reference Guide for Java' at IBM's web site in books online.

Jodi Murawski Metavante Corp.

================================================ 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. 28338 93 28_Re: -725 sqlcode DB2 Connect13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 12 Jan 2001 16:03:36 -0600348_iso-8859-1 Thanks for responding Georg.

It turns out that inter-DB2 OS/390 subsystem applications (using DDF, but not distributed pc clients. Mainframe only.) are also taking this error. A problem has been opened with IBM on the OS/390 side. It is only occurring in DB2 OS/390 V6 subsystems, not V5.

Anyone else run across this? [...] 28432 24 18_online index build12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Sat, 13 Jan 2001 11:40:51 +0200383_iso-8859-1 Hi Donna, Let's start with simple "create index" when you need a new index, expand that to "rebuild index" when you don't have an IC of it (due to any reason of your choice), or a terminated "load" where you have to recover TS & its IXs...

The main point is that any index build from the data is keeping us from updating the data and that means "happy" users. [...] 28457 41 49_Conversion to DB2V6 with UTILITY in Stopped state17_Necessary, Cheryl31_Cheryl.Necessary@INGRAMBOOK.COM31_Sat, 13 Jan 2001 12:34:36 -0600601_iso-8859-1 DB2 Listers,

This weekend we attempted to migrate from DB2/OS390 V5 to DB2/OS390 V6. I made a series of missteps as follows:

1. Image copy of DB2V5 catalog and directory taken. 2. Image copy job for an application DB started. 3. Attempted to STOP DB2 4. DISPLAY THREAD showed application image copy job running. 5. I cancelled the job but forgot to terminate the utility. 6. Started DB2 under V6 7. Attempted to rerun the application image copy job. 8. Term Utility step received: DSNU115I *DBT2 DSNUGTER - COPY UTILITY WITH UTILID = IBT2ICFT CAN ONLY BE TERMINATED BY A [...] 28499 24 34_UDB on Windows Millennium Edition?14_Steve Westfall25_steve.westfall@AQUILA.NET31_Sat, 13 Jan 2001 16:06:16 -0600627_iso-8859-1 Has anyone successfully installed DB2 UDB Personal Edition (version 7) on Windows ME? The documentation refers only to Windows 95/98, but their successor, Windows Millennium Edition, didn't start shipping until last September.

If you have tried this, do you have any suggestions?

Thanks.

Steve Westfall Naperville, Illinois USA steve.westfall@aquila.net

================================================ 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. 28524 184 50_Re: DB2 stored procedure not returning result set.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Sat, 13 Jan 2001 16:02:09 -0500567_- This problem is not happening at execution. I am getting following error at the bind time of calling program

DSNX200I :DB2T BIND SQL ERROR USING TEST AUTHORITY PLAN=(NOT APPLICABLE) DBRM=MQCS02BP STATEMENT=105 SQLCODE=-440 SQLSTATE=42884 TOKENS=PROCEDURE MQ0002SP CSECT NAME=DSNXOSTP RDS CODE=-120 DSNT233I :DB2T UNSUCCESSFUL BIND FOR PACKAGE = SYSB.MQ.MQCS02BP.()

Here my stored procedure is MQ0002SP and calling program is MQCS02BP. In MQCS02BP I have specified the PATH (TEST). The package parameter for calling program MQCS02BP is as follows [...] 28709 39 22_Fw: UDB Application ID11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sat, 13 Jan 2001 21:53:03 -0500638_us-ascii Brian,

No, the application ID is a transient identifier that uniquely identifies each connection to the database. Application IDs can be found in application snapshots, the list application command, and DB2 UDB event instrumentation.

Why do you ask? What would you like to do?

Regards, Scott

Recently we helped a Siebel 2000 DB2 AIX user reduce the elapsed time of their batch EIM process by 65-75%. We did this in about four hours. If your current monitoring solution isn't helping you get results like this, get the GUYS and get your database really moving: http://www.database-guys.com/trial/ [...] 28749 45 38_Re: UDB on Windows Millennium Edition?12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Sun, 14 Jan 2001 20:04:41 +0200215_iso-8859-1 Hi, After struggling with ME per se - I gave up on it, didn't even recognize my DVD... I use only WIN2000. This has nothing to do with DB2 UDB, I didn't even reach the step of installing software.

28795 57 26_Re: How do deadlocks work?15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 15 Jan 2001 09:09:00 +1100608_- Mike,

Can't help you with the actual rules for this, but I've always believed DB2 kills the UOW that's done the least amount of work. How DB2 works out which one(s) that is (or if it's even true) is something one of the others will have to comment on.



Raymond



> -----Original Message----- > From: mike.holmans@BT.COM [SMTP:mike.holmans@BT.COM] > Sent: Friday, 12 January 2001 10:11 pm > To: DB2-L@RYCI.COM > Subject: How do deadlocks work? > > I know that when it detects a deadlock between two or more processes, DB2 > blows at least one of the processes out and [...] 28853 167 41_Re: DB2 dasd extent hedge trivia question14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 15 Jan 2001 09:38:28 +1100406_iso-8859-1 V3 had the 50% rule, V4 has the same text quoted by Raymond.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au -----Original Message----- From: Adrian Savory [mailto:Adrian.Savory@WORKTHING.COM] Sent: Friday, January 12, 2001 8:57 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 dasd extent hedge trivia question [...] 29021 28 52_Check index returns Index out of sequence.......why?7_Sri Sri20_sri_db2l@HOTMAIL.COM31_Sun, 14 Jan 2001 19:08:47 -0600452_- Dear List,

One of our daily production jobs that has been running happily for many years suddenly gave a sqlcode -532(referential constraint prevents delete) on a delete of the parent record, even though there was a check immediately before that to make sure that all children rows had been deleted. A check index on the child table's indexes gave a "Index out of sequence" message. After rebuild was done, the program ran happily again. [...] 29050 170 55_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT & Solaris14_Duncan Gardner23_dgardner@GESB.WA.GOV.AU31_Mon, 15 Jan 2001 09:19:45 +0800384_iso-8859-1 We raised an ETR for this (PMR 09003) and there is now an APAR (IY15592) for Solaris. We have been given a fix to FixPack6 which cures this problem and I believe it will also be incorporated in FixPack 7.

Duncan Gardner DBA Team, Infrastructure Support Group Western Australian Government Employees Superannuation Board (08) 9263 4164 All my own views etc .... [...] 29221 85 38_Re: UDB on Windows Millennium Edition?14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Sun, 14 Jan 2001 21:58:11 -0600490_iso-8859-1 Yes, I got it to work. But it is a mess since there is no autoexec.bat file etc., and everything that was in autoexec, is logged into the ME register, without you easily being able to modify it. There are a lot of nice features in ME, but the nicest feature is the advice to STAY AWAY FROM IT! Besides it pucks have the time when an install leaves stuff in a newly created autoexec.bat, and then on startup ME has to take that stuff and move it into the register and so on. [...] 29307 67 56_Re: Check index returns Index out of sequence.......why?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 15 Jan 2001 10:01:25 +0530312_- Hi Sri,

I am sure you must be having the RID which was causing the problem. Now i think everything is perfect but the particular row in problem could have been tracked for the reason. The complete SQLCA message could also have helped. I have not seen or heard of data inconsistency caused by SQLs. [...]