1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2001, week 3
2 115 38_Re: copy archive log from dasd to tape10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Sun, 15 Jul 2001 03:02:43 -0600410_iso-8859-1 Eric, Nothing wrong with HSM migration. It is because we don't have HSM setup on DR site.
-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Friday, July 13, 2001 10:00 AM To: DB2-L@RYCI.COM Subject: Re: copy archive log from dasd to tape
What is wrong with MSM migration?
Regards, eric pearson NS ITO Database Support [...]
118 110 27_Re: verison 6 adn getpages.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sat, 14 Jul 2001 18:42:26 -0500572_us-ascii Victor,
As Kals says, there were (are?) problems with the Outer Join simplification choosing a poor access path. V6 will simplify a full outer join to a left, or left/right to an inner if there is a WHERE clause predicate which negates the NULLs introduced by the outer join. For a left, the subsequent inner join appears to choose the right table first in the table join sequence; even though it is the left table with the major filtering (hence the merge scan). This means that the SQL was originally coded incorrectly, but V5 executed it as coded. [...]
229 13 46_Demetris Kasheris/LT/CPB is out of the office.17_Demetris Kasheris19_DKasheris@LAIKI.COM31_Mon, 16 Jul 2001 01:01:34 +0300336_us-ascii I will be out of the office starting 07/13/2001 and will not return until 07/17/2001.
================================================ 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.
243 12 59_Re: Powerbuilder/DB2 Connect - Thread Resource Usage - ASAP11_Mark Harmon17_mhinnyc@YAHOO.COM31_Sun, 15 Jul 2001 21:37:20 -0500413_- There is a software product you can buy that will kill runaway threads according to rules you set up. It's called Thread/SENTRY from Relational Architects at relarc.com.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
256 129 28_Re: Grants on plans question15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 16 Jul 2001 11:08:34 +1000654_- I think the 'problem' is that the autocommit is for all SQL statements issued so far. It appears grants must be committed before they can be seen, even for the UOW that issued the grant.
Raymond
> -----Original Message----- > From: Billings, Linda [SMTP:linda.billings@DOA.STATE.WI.US] > Sent: Saturday, 14 July 2001 4:15 am > To: DB2-L@RYCI.COM > Subject: Re: Grants on plans question > > That's odd. I didn't know that either. > > Linda > > -----Original Message----- > From: Amit Agarwal [mailto:amit_agarwal@STANDARDANDPOORS.COM] > Sent: Friday, July 13, 2001 12:50 PM > To: DB2-L@RYCI.COM > Subject: Re: Grants on plans [...]
386 99 21_Re: DSNDB07 Sort Work13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Mon, 16 Jul 2001 15:02:53 +1000324_- Fritz, Is this true about the allocation of the data sets? I was under the impression that the logical work files were, well, logical and that they did not correspond to dataset numbers. In fact part of the admin guide shows how a sort using five logical work file would be allocated over three sortwork tablespaces. [...]
486 13 44_Marc Feit/GA/CheckFree is out of the office.12_Marc K. Feit19_MFeit@CHECKFREE.COM31_Mon, 16 Jul 2001 02:25:40 -0400336_us-ascii I will be out of the office starting 07/16/2001 and will not return until 07/30/2001.
================================================ 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.
500 40 36_Alter tablespace probelm for UNICODE8_duam lee16_duam_lee@USA.NET29_Mon, 16 Jul 2001 02:30:31 MDT640_US-ASCII Hi All here is a probelm for alter tablespaces which I m describing below.
Already CCSID value for tablespaces are EBCDIC. This definitions were written for V6 but as we have migrated from V6 to V7. The definitions of the source code should be Unicode to gain more flexibility with code conversion. So in tablespace definitions we need to get change like this below. ALTER TABLESPACE databasename.tablespacename CCSID UNICODE; while executing I got sysntax error with –104 sqlcode. I checked the manuals and other documents where the syntax says to give the numeric value for CCSID. Like alter tablespace dbname.tsname [...]
541 86 40_Re: Alter tablespace probelm for UNICODE15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Mon, 16 Jul 2001 11:09:28 +0200331_iso-8859-1 Duam,
When converting from EBCDIC to ASCII in V6 you have to quisce the activity against the table, unload the data (external).Drop the table, crate the ascii table. Reload the data and rebind plans / packages. I guess the same thing is used when changing to UNICODE in V7. So it's a tedious task after all. [...]
628 42 14_Re: DDF thread18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 16 Jul 2001 08:17:03 -0400423_iso-8859-1 It does show up in the -dis thread(active) and it does say that it was previously cancelled. By displaying the thread that is how I got the token number to use to try and cancel it... We're still working with IBM on a fix to this..
thanks.
-----Original Message----- From: Mark Harmon [mailto:mhinnyc@YAHOO.COM] Sent: Thursday, July 12, 2001 8:59 PM To: DB2-L@RYCI.COM Subject: Re: DDF thread [...]
671 174 38_Re: copy archive log from dasd to tape11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Mon, 16 Jul 2001 08:04:13 -0500531_iso-8859-1 I did this at my old job. If you use DFDSS to dump them to tape, then just use it to restore them back to disk at your recovery site. You don't have to tell DB2 anything since he'll just use the catalog. It's very simple. My method was the catalog/directory image copy job issued a -ARCHIVE LOG MODE(QUIESCE) as the last step. Then another job ran that dumped the archive logs to tape (I kept track of each archive log that had been created since the previous day, so I knew which ones needed to be dumped and sent [...]
846 22 20_db2 personal edition7_Ale Eba14_Ale_Eba@CPR.CA31_Mon, 16 Jul 2001 08:58:57 -0400700_us-ascii Hi Jimmy, I faced the same probelm with DB2 version 7.0 personal edition. To resolve the problem, in windows 98, I created a user id DB2ADMIN, signed on using this id and after that everything was smooth.
--------------------------------------------------------------------------------------------------------------------------------------------------------------
I have just downloaded and installed DB2 version 7.1 personal edition. I am using Windows 98. I have used Windows NT and am familiar with the admin authority of NT. When I tried to create the sample database, it said do not have authority. Where in Windows 98 do you set up the "admin" authority????? thanks [...]
869 43 24_DB2 UDB Personal Edition13_Jimmy Willett13_JRLBW@AOL.COM29_Mon, 16 Jul 2001 09:21:29 EDT584_US-ASCII I am trying to use the Personal Edition. It loaded successfully. I am using Windows 98. I have loaded the Enterprise edition on Solaris Unix and on Windows NT workstations with success. I know that on Windows NT about the administration group and the "Adminstrator" username...how it must be "Admin" for short. However, on Windows 98 there is no group like the one on NT. After successfully loading PE..I tried to create sample databases....But error saying "" does not have the authority to create database. Where can I enter user with name of "Sysadm" on the "98"???? [...]
913 233 38_Re: copy archive log from dasd to tape14_Crichton, Jean21_jcrichton@FEDDATA.COM31_Mon, 16 Jul 2001 09:55:06 -0400562_iso-8859-1 Tina,
That is definitely one of the easiest ways to do this. Out of curiosity, what was your methodology for selecting the tapes to send offsite? Did you have an automated method of doing this such as tape vaulting or did your operators pull tapes for DR vaulting after your jobs ran? At one place I worked, we used tape vaulting by DSN to select the DR tapes that were to go offsite, so they had to be cataloged and have unique names (we simplified this by using VAULT as the mandatory second qualifier for ALL vaulted tapes). This meant [...]
1147 28 17_performance issue12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 16 Jul 2001 09:04:56 -0500480_iso-8859-1 We have a small shop here, three lpars and five DB2 subsystems. A developer ran an update from one subsystem on one lpar to a different subsystem on another lpar, and experienced very slow response time, and very slow processing time. One of the staff here has stated....
I believe that the performance issue is most likely related to the remote issue because DB2 uses a different strategy for moving data in and out when access is from a remote requestor. [...]
1176 128 21_Re: DSNDB07 Sort Work24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 16 Jul 2001 07:07:52 -0700607_iso-8859-1 Hi Steve!
Given the size of the datasets in DSBDB07, it's highly unlikely that we ran out of space. I/O statistics on the DSNDB07 datasets also bear this out.
Fritz
> -----Original Message----- > From: Steve Tennant [SMTP:steve.tennant@CUSTOMS.GOV.AU] > Sent: Sunday, July 15, 2001 10:03 PM > To: DB2-L@RYCI.COM > Subject: Re: DSNDB07 Sort Work > > Fritz, > Is this true about the allocation of the data sets? I was under > the impression that the logical work files were, well, logical and that > they did not correspond to dataset numbers. In fact part of the admin > [...]
1305 30 53_Downloaded V7 presentation guide red book (prf-files)12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 16 Jul 2001 16:12:32 +0200423_iso-8859-1 Hello friends,
I have visited the IBM redbooks web page and downloaded some files from IBM about "DB2 UDB server for OS/390 and z/OS Version 7 Presentation Guide (additional material)".
What i received are some *.prz-files - seems these are Freelance foils and we do not have Freelance....
Anyone out there who can tell me how to convert these prz-files to ppt.(Powerpoint) files ? ... [...]
1336 74 17_Reorg On SYSDBASE31_Tekin Yavuz (Garanti Teknoloji)21_TekinY@GARANTI.COM.TR31_Mon, 16 Jul 2001 17:18:49 +0300529_iso-8859-9
Hi all,
I am trying to learn if a Reorg is necessary on SYSDBASE tablespace ,I am running reorg with reportonly option on it but I can not decide how much value I should give as offposlimit and indreflimit for it.Default is 10,but in utility Guide it says that "NEAROFFPOS and FAROFFPOS values in SYSINDEXPART can tolerate a higher value before reorganization needed if the tablespace is DSNDB06.SYSDBASE..." (Utility and Reference Guide Version 6) .Do you have any recommendation for my problem?? [...]
1411 52 21_Re: performance issue12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Mon, 16 Jul 2001 10:28:56 -0400583_US-ASCII have you looked at the performance group associated to DDF? In a prior shop, we had to bump the dispatching priority of DDF to get adequate performance...
HTH
Mark Anzmann Database Administration DHRIS Program Team Accenture
>>> John.Vernon@TRS.STATE.TX.US 07/16 10:04 AM >>> We have a small shop here, three lpars and five DB2 subsystems. A developer ran an update from one subsystem on one lpar to a different subsystem on another lpar, and experienced very slow response time, and very slow processing time. One of the staff here has stated.... [...]
1464 100 24_Re: BAD PLANS & PACKAGES18_Gaston, Raymond J.17_GastonRay@ORU.COM31_Mon, 16 Jul 2001 10:31:42 -0400410_iso-8859-1 Thanks Dave... I'm not sure that my "understanding the situation" was as clear as your detailed description! Much appreciated - Ray
-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Saturday, July 14, 2001 8:58 AM To: DB2-L@RYCI.COM Subject: Re: BAD PLANS & PACKAGES
Hello Ray, I suggest you might want to take a different approach. [...]
1565 63 21_Re: performance issue16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 16 Jul 2001 10:49:52 -0400452_iso-8859-1 John,
Was this one big 'UPDATE .... WHERE' statement or was it lots of individual updates? Was this done via 3-part name or via CONNECT? If lots of individual updates via 3-part-name the cost of the 'minibinds' for the remote 'dynamic' SQL could be significant.
Also: is MVS dispatching priority of (local .DIST address space or remote .DIST, .IRLM, .DBM1, .MSTR) address space lower than priority of local(.MSTR, .DBM1)? [...]
1629 56 57_AW: Downloaded V7 presentation guide red book (prf-files)12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Mon, 16 Jul 2001 17:12:35 +0200653_iso-8859-1 Hi Georg,
there is a filter for Freelance in Powerpoint (FILE-OPEN-ComboBox "Type"). We are at Office97.
HTH
Thomas
> -----Ursprüngliche Nachricht----- > Von: Peter, Georg [mailto:G.Peter@DZBW.DE] > Gesendet am: Montag, 16. Juli 2001 16:13 > An: DB2-L@RYCI.COM > Betreff: Downloaded V7 presentation guide red book (prf-files) > > Hello friends, > > I have visited the IBM redbooks web page and downloaded some > files from IBM > about "DB2 UDB server for OS/390 and z/OS Version 7 Presentation Guide > (additional material)". > > What i received are some *.prz-files - seems these are > Freelance foils and > [...]
1686 58 57_Re: Downloaded V7 presentation guide red book (prf-files)0_18_mebert@AMADEUS.NET31_Mon, 16 Jul 2001 17:15:43 +0200444_iso-8859-1 Hi Georg,
did you try simply opening them with Powerpoint (by specifying the filename, with extension, in the Open File dialog)? PPT might have an import filter already installed. If not, I'm sure there must be one for downloading somewhere. Check the Microsoft web site. My PPT version (PPT 97) can import Lotus Freelance 4.0 for DOS and Freelance Graphics for Windows 1.0-2.0. Check the PPT Help, search for Freelance. [...]
1745 37 16_personal edition13_Jimmy Willett13_JRLBW@AOL.COM29_Mon, 16 Jul 2001 11:45:42 EDT407_US-ASCII Thanks for the advice...I did get the userid set to DB2ADMIN in win 98.... In trying to create the sample database in First Starts, the system just starts, goes thru the creation process, the the wheels just sit and turn...after about 20 minutes I cancelled it...nothing was returned,,,just that it was in process... What is different about using the DB2 system to create its own sample database???
1783 53 57_Re: Downloaded V7 presentation guide red book (prf-files)14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Mon, 16 Jul 2001 10:55:18 -0500690_iso-8859-1 Requires Freelance, then save as *.pre (Previous Freelance version). PowerPoint will open those.
Richard Yevich YL&A - http://www.YLAssoc.com DB2 and Sysplex Gold Consultant Certified DB2 DBA for OS/390, z/OS Richard_Yevich@YLAssoc.com
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Peter, Georg > Sent: Monday, July 16, 2001 9:13 AM > To: DB2-L@RYCI.COM > Subject: Downloaded V7 presentation guide red book (prf-files) > > > Hello friends, > > I have visited the IBM redbooks web page and downloaded some > files from IBM > about "DB2 UDB server for OS/390 and z/OS Version 7 [...]
1837 105 21_Re: performance issue12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 16 Jul 2001 11:20:47 -0500576_iso-8859-1 Eric, The sql was as follows: DELETE FROM DB2PDIST.REPORT_SUMMARY_V1 WHERE RPT_STTS_CD = 'O' AND OID > '2001-07-11-00.00.00.000000' AND RPT_TY_CD = 'IN'
There should be 990 rows deleted.
The Performance Group Numbers are as follows: JOBNAME STEPNAME PROCSTEP JOBID OWNER C POS DP PGN DB2DMSTR DB2DMSTR IEFPROC STC25581 DB2DMSTR NS 76 702 DB2DIRLM DB2DIRLM STC25595 DB2DIRLM NS 81 701 DB2DDBM1 DB2DDBM1 IEFPROC STC25616 DB2DDBM1 NS 71 702 DB2DDIST DB2DDIST IEFPROC STC25620 OMVSKERN NS 71 702 DB2DSPAS DB2DSPAS IEFPROC STC25623 DB2DSPAS NS 71 702 [...]
1943 101 34_Re: DB2 for OS/390 V6 CPU increase16_Alison Pelletier17_ap84098@YAHOO.COM31_Mon, 16 Jul 2001 10:05:46 -0700507_us-ascii We experienced the same thing. Seemed to happen when we did a bind/rebind on a package with degree(any) after going V6. CPU shot thru the roof. Elapsed did too. Anyhow, we've cleaned all that up. Now all bound w/degree(1). I look at OMEGAMON historical reports...there are NO degree columns with values > 0. Does that mean I have no parallelism? Could DB2 be running parallelism that I don't know about? There are some "DSN*" packages that are bound w/degree(any). Would that have any affect? [...]
2045 153 21_Re: RACF/DB2 security10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 16 Jul 2001 11:09:10 -0600471_iso-8859-1 Hi! I got formal answer from IBM that the DB2 command prefix with a dash (-) are checked by DB2 catalog security. The RACF/DB2 external security module specifically passes the request to DB2 because current DB2 does not provide an ACEE when running task asynchronously.
Grace
-----Original Message----- From: Dallas Focht [mailto:dallas.focht@PMIC.COM] Sent: Thursday, July 12, 2001 1:44 PM To: DB2-L@RYCI.COM Subject: Re: RACF/DB2 security [...]
2199 126 21_Re: performance issue16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 16 Jul 2001 13:12:40 -0400499_iso-8859-1 What indexes do you have on this table? What is the total # of rows?
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Vernon, John [mailto:John.Vernon@TRS.STATE.TX.US] Sent: Monday, July 16, 2001 12:21 PM To: DB2-L@RYCI.COM Subject: Re: performance issue
Eric, The sql was as follows: DELETE FROM DB2PDIST.REPORT_SUMMARY_V1 WHERE RPT_STTS_CD = 'O' AND OID > '2001-07-11-00.00.00.000000' AND RPT_TY_CD = 'IN' [...]
2326 96 20_Re: personal edition18_Gert van der Kooij15_geko@WANADOO.NL31_Mon, 16 Jul 2001 19:18:58 +0200670_iso-8859-1 Hi, Sometimes it's also necessary to logon with the 'db2logon' command. My colleque needed it to get it running under W98.
Hope this helps. Regards, Gert ----- Original Message ----- From: Jimmy Willett Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Monday, July 16, 2001 5:45 PM Subject: personal edition
Thanks for the advice...I did get the userid set to DB2ADMIN in win 98.... In trying to create the sample database in First Starts, the system just starts, goes thru the creation process, the the wheels just sit and turn...after about 20 minutes I cancelled it...nothing was returned,,,just that it was in process... [...]
2423 112 34_Re: DB2 for OS/390 V6 CPU increase14_Crichton, Jean21_jcrichton@FEDDATA.COM31_Mon, 16 Jul 2001 13:37:15 -0400441_iso-8859-1 Alison,
If you look at the bufferpool statistics in OMEGAMON, do you see any parallel groups being executed? That's the easiest way I know to tell if you have what I call "stealth" parallelism going on.
Jean E. Crichton Logicon
-----Original Message----- From: Alison Pelletier [mailto:ap84098@YAHOO.COM] Sent: Monday, July 16, 2001 1:06 PM To: DB2-L@RYCI.COM Subject: Re: DB2 for OS/390 V6 CPU increase [...]
2536 176 28_Re: Grants on plans question16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Mon, 16 Jul 2001 19:03:03 +0100392_us-ascii Linda, Amit (and Eric),
sorry for being a bit late on this but I was away...
you actually have to commit explicitely, don't know if this was changed in more recent times.
I had the same problem at a customer's site and it took all of my skills of persuasion to get the application programmer to do this... (as well as swapping phone notes with Max Scarpa). [...]
2713 72 34_Re: DB2 for OS/390 V6 CPU increase15_Craig Nicholson27_Marion_C._Nicholson@HUD.GOV31_Mon, 16 Jul 2001 14:04:17 -0400367_iso-8859-1 We ran into the same issue at Amtrak when we converted to V6 with the CPU usage going through the roof when programs were bound with degree(any). We had to rebind with degree(1) and are still waiting for IBM to get back to us.
"Crichton, Jean" @RYCI.COM> on 07/16/2001 01:37:15 PM [...]
2786 217 28_Re: Grants on plans question12_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Mon, 16 Jul 2001 14:44:25 -0400346_us-ascii Aurora, As far as my problem was concerned, it was because there was a non sql statement after the grant, which cause the entire statement to rollback even though autocommit was on. As per your mail, I have also tested it without an explicit commit and autocommit on and the results are as expected, i.e the commit does take place. [...]
3004 144 21_Re: performance issue14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Mon, 16 Jul 2001 12:05:55 -0700404_iso-8859-1 John, If you can, run the query via QMF or SPUFI on the target system. Compare that to the run time via the distributed query. If it is much faster, then the network may be where the query is spending it's time. If they run the same, then it is the SQL. Either bad SQL or access path, etc. Also, Check the bind parameters of the remote package -- blocking, isolation level, etc. HTH Phil [...]
3149 39 81_Calling DB2 stored procedure from SQLserver stored procedure usin g DB2 connect!!15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 16 Jul 2001 15:24:14 -0400362_iso-8859-1 I want to call a DB2 stored procedure from SQLserver stored procedure. Please let me know if anybody has tried this. I did the following but did not work.
1. Created a link server with IBM DB2 connect.
2. Executed a query like this to access a mainframe table. select * from openquery (db2t_ibm,'select * from test.csvmtp with ur') [...]
3189 145 21_Re: performance issue12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 16 Jul 2001 14:45:17 -0500378_iso-8859-1 It turns out that it was a combination of bad SQL and a lack of runstats (for the last 8 months)....
Many thanks to one and all for the help, I really appreciate it.
john
-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Monday, July 16, 2001 12:13 PM To: DB2-L@RYCI.COM Subject: Re: performance issue [...]
3335 22 28_Re: Grants on plans question16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Mon, 16 Jul 2001 15:06:36 -0500400_- Amit,
thanks for your clearer explanation, which will teach me not to hit the "send" button before re-reading what I've written :-)
what I should have said, in more words, was that if you code a commit in between statements, then there would be no rollback in case of failure of a subsequent statement. but this has already been written by someone more articulate than myself... [...]
3358 76 14_Re: DDF thread14_Gary Bernhardt27_garybernhardt@EARTHLINK.NET31_Mon, 16 Jul 2001 15:39:15 -0500354_iso-8859-1 We had a similar problem to this beginning with our upgrade to DB2 V6. We started forcing DDF down and that seemed to work for a while, then, suddenly that quit working and left DDF in a bad state, meaning it would neither come down nor come back up. The only way to get out of this state was to bounce DB2, usually by abending the IRLM. [...]
3435 39 34_Re: DB2 for OS/390 V6 CPU increase12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 16 Jul 2001 16:14:14 -0500602_- This may be a bit generic for you, but here's how I've answered a number of questions. Did you know about APARs II12836, PQ45820 and PQ47914?
The first key for parallelism is having the resources (cpu, memory, IO) to provide better response. Do you have some extra cpu time, memory and IO bandwidth to use for improving response time? The design point for parallelism is large queries where the response times can be shortened. In general, plans that should be running with subsecond response time are not suited to parallel processing. Do you have the needed resources? Are you reducing [...]
3475 16 21_Re: RACF/DB2 security12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 16 Jul 2001 16:31:30 -0500348_- I checked just to be sure. The V6 Admin book has a sentence in the Access control authorization exit which indicates, "DB2 does not pass the ACEE address for DB2 commands or IMS transactions." That's at the bottom of page 1053 (book number) or 1065 (Acrobat number) in dsnag0g3.pdf. There are some other warnings here and a few pages above. [...]
3492 52 11_DB2 TRIGGER11_Wang, Jason29_jason.wang@CHECKSOLUTIONS.COM31_Mon, 16 Jul 2001 16:46:25 -0500626_iso-8859-1 Hi All,
I have the following script,
create db profile on c:; connect to profile;
create table nullid.prf ( prfid integer GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL, typid integer, prfname varchar(10), primary key (prfid) );
DROP TRIGGER NULLID.BI_Prf1 ; CREATE TRIGGER NULLID.BI_Prf1 NO CASCADE BEFORE INSERT ON NULLID.Prf REFERENCING NEW AS Newrow FOR EACH ROW MODE DB2SQL WHEN ( Newrow.PrfName IN (SELECT PrfName FROM NULLID.Prf WHERE ( TypID = newrow.TypID ) ) ) SIGNAL SQLSTATE '70001' ('Profile name already in use for this profile type') ; [...]
3545 24 60_Re: AW: Length of columns in DB2 OS/390 -Version 7, 8 , 9 ??12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 16 Jul 2001 16:51:21 -0500492_- Object Restore is the name of a current tool offering. You can look for it at http://www.ibm.com/software/data/db2/#Tools
Look under Data Management Tools for DB2 for OS/390 and z/OS
Click on IBM DB2 Object Restore Tool
IBM DB2 Object Restore Tool for OS/390, V1 automatically restores previously dropped objects and all related dependencies. This tool saves DASD space because it eliminates the need for a duplicate shadow copy of the catalog to recover objects. [...]
3570 13 33_Re: AW: same table Insert problem12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 16 Jul 2001 16:54:14 -0500375_- So if you were running V6, then this would work. Self-referencing updates and deletes come in V7.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3584 44 38_Northern Oregon DBMS Exchange Reminder24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 16 Jul 2001 15:06:24 -0700350_- Hi Everyone!
For those of you in the Pacific northwest, our regular August NODE meeting will be held a couple of weeks early. This is to take advantage of a trip to the area being made by Blair Adamache. Please note that the meeting will be on a Tuesday instead of the regular Monday. We hope to resume our regular schedule in October. [...]
3629 17 25_Having trouble connecting14_Michele Payton18_paytonm@US.IBM.COM31_Mon, 16 Jul 2001 16:23:21 -0600493_us-ascii I am having trouble connecting to a remote database through the Control Center. DCE is running on the remote system. Could DCE be causing the connection problem and if so does anyone know a way around that?
Michele Payton IT Specialist
================================================ 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.
3647 80 15_Re: DB2 TRIGGER13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Mon, 16 Jul 2001 15:58:28 -0700442_iso-8859-1 I think the problem here could be because in the statement: "INSERT into nullid.prf (typid, prfname) values(1, 'test1'),(1, 'test1');" , the first insert is uncommitted before the second insert. So, both the first and second inserts contribute to new transition variables and hence WHERE ( TypID = newrow.TypID ) does not match. How about skipping this where clause and adding WITH UR in your trigger (Hope this is allowed) : [...]
3728 161 29_Re: Having trouble connecting11_Rolf Kramer23_RKramer@POWERLAN.COM.AU31_Tue, 17 Jul 2001 09:13:43 +1000391_- Hello Michelle,
We run into a connection problem to Linux DB2 sometimes, although I don't think Linux is the cause. As yet have not found an explanation, but we have a workaround that works for our situation. Anyhow, since control center often causes problems, I mainly use CLP nowaday.
Will look into this further when I get spare time. Right now too many alligators ! [...]
3890 23 54_Paul Packham/ITS/POSG/POSTOFFICE is out of the office.12_Paul Packham26_paul.packham@CONSIGNIA.COM31_Tue, 17 Jul 2001 01:03:21 +0000534_us-ascii I will be out of the office from 17/07/2001 until 19/07/2001.
I will respond to your mail when I return, however please contact Tim Hayes or Mick Graley if urgent.
Regards Paul
This email and any attachments are confidential and intended for the addressee only. If you are not the named recipient, you must not use, disclose, reproduce, copy or distribute the contents of this communication. If you have received this in error, please contact the sender and then delete this email from your system. [...]
3914 24 23_colon in host variables25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Tue, 17 Jul 2001 09:22:51 +0800362_us-ascii Hi,
It has been mentioned that starting V6 the colon for host variables would be validated. If there was a colon missing there would be a bind error. Currently we are in V5. When I try to test this scenario by deliberately avoiding the colon, I still get a bind error. Is this colon verification for the "into clause" or the "where clause". [...]
3939 41 27_Re: colon in host variables16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 17 Jul 2001 09:52:48 +0530596_- It is mentioned colon in the host variable. What bind error you are getting ?
Regards Sanjeev
> -----Original Message----- > From: Srirengan Venkatesh Kumar > [SMTP:SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG] > Sent: Tuesday, July 17, 2001 6:53 AM > To: DB2-L@RYCI.COM > Subject: colon in host variables > > Hi, > > It has been mentioned that starting V6 the colon for host variables would > be validated. If there was a colon missing there would be a bind error. > Currently we are in V5. When I try to test this scenario by deliberately > avoiding the colon, I still get a [...]
3981 53 29_ASCII DB2 Database in OS/390?15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 17 Jul 2001 10:05:35 +0200617_iso-8859-1 Hello all,
In my shop we're thinking about a datawarehouse. This datawarehouse will reside in a DB2 v6 Database on OS/390. The Transformation will be done on an UNIX (RS6000) using Informatica's Powercenter tool. Connection is made using a dedicated line with DB2 Connect EE.
Normally the database on OS/390 is in EBCDIC, however the UNIX is ASCII. As data is retrieved from the OS/390, it is transformed by DB2 Connect form EBCDIC to ASCII. The tranformation is done on the UNIX and then again moved to the OS/390; again transformed by DB2 Connect form ASCII to EBCDIC. This conversion [...]
4035 105 21_FW: Reorg On SYSDBASE31_Tekin Yavuz (Garanti Teknoloji)21_TekinY@GARANTI.COM.TR31_Tue, 17 Jul 2001 13:31:33 +0300576_iso-8859-9 Has anyone have any comment about this topic? Thanks...
> -----Original Message----- > From: Tekin Yavuz (Garanti Teknoloji) > Sent: Monday, July 16, 2001 5:19 PM > To: 'DB2 Data Base Discussion List' > Subject: Reorg On SYSDBASE > > > Hi all, > > I am trying to learn if a Reorg is necessary on SYSDBASE tablespace > ,I am running reorg with reportonly option on it but I can not decide how > much value I should give as offposlimit and indreflimit for it.Default is > 10,but in utility Guide it says that "NEAROFFPOSF and FAROFFPOSF values in [...]
4141 148 25_Re: FW: Reorg On SYSDBASE14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 17 Jul 2001 13:38:52 +0100346_iso-8859-9 Someone once asked Roger Miller how often to reorg the catalog.
His answer went something along the lines of
"Q. How long have you had DB2?" "A. About 7 years"
"Q. How many times have you reorganised the catalog in those 7 years?" "A. Well, you've only been able to do it for the last couple, so once really" [...]
4290 108 54_Error occured during end-of-volume processing (OS/390)12_Peter Horner27_peter.horner@JULIUSBAER.COM31_Tue, 17 Jul 2001 14:45:52 +0200315_us-ascii Hi List
I got an error occurred during EOV (end-of-volume) processing for a VSAM data set.
A load was startet and then the EOV occured. It can't allocate another extend because it simply ran out of space on the volume. (It has used only 3 extends but has not enough space on the volume) [...]
4399 93 15_Re: DB2 TRIGGER11_Hamar, Bela22_Bela.Hamar@VARETIS.COM31_Tue, 17 Jul 2001 15:04:40 +0200483_us-ascii Hi Jason,
(I assume, you are using DB2 for Win*)
I don't know, whether you may change your trigger, but the following definition seems to do what you want:
1. it must be an "AFTER" trigger, so that it can reference the new contents of the table 2. the WHEN-predicate has been changed: it checks for each new row whether it occurs more than once 3. last and least, for convenience I put the information about the row in error into the error message [...]
4493 33 15_RRS management.17_Massimo Biancucci30_massimo.biancucci@T-SYSTEMS.IT31_Tue, 17 Jul 2001 15:11:26 +0100293_us-ascii We started, in a production environment, a set of Stored Procedures using WLM and RRS. Now some questions : What do I have to do to manage and control the RRS ? Anybody did experience any problem or failure ?
At the end, experienced folks, what do I have to be worry of ? [...]
4527 144 58_Re: Error occured during end-of-volume processing (OS/390)9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Tue, 17 Jul 2001 14:34:39 +0100443_ISO-8859-1 Hi,
Assuming this is a load replace, you need to terminate the load utility and then stop any tablespaces/indexspaces that you wish to add volumes to. Then start the tablespaces/indexspaces and rerun the load.
Jim.
-----Original Message----- From: Peter Horner [mailto:peter.horner@JULIUSBAER.COM] Sent: 17 July 2001 13:46 To: DB2-L@RYCI.COM Subject: Error occured during end-of-volume processing (OS/390) [...]
4672 51 27_Re: colon in host variables0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 17 Jul 2001 09:02:10 -0500451_us-ascii If I remember correctly:
colon has been required in new code since V3 (I think). But when rebinding using old dbrms (V1 or V2?) where colon was not required, was tolerated until V6 where it will cause an error.
Srirengan Venkatesh Kumar @RYCI.COM> on 2001/07/16 08:22:51 PM
Please respond to DB2 Data Base Discussion List [...]
4724 105 15_Re: DB2 TRIGGER11_Wang, Jason29_jason.wang@CHECKSOLUTIONS.COM31_Tue, 17 Jul 2001 09:11:01 -0500381_iso-8859-1 Hi Ashish,
1. in the trigger statement, there is clause "FOR EACH ROW MODE DB2SQL", which supposed to say that trigger apply on the row level. So if the first insert in uncommitted before the second insert, it will be a DB2 problem or bug.
2. I can not skipping the where clause because the prfname is not unique in the table but unique within typid. [...]
4830 38 21_Utility Compatibility13_Moss, William15_MossW@AETNA.COM31_Tue, 17 Jul 2001 10:11:29 -0400482_iso-8859-1 I was running a reorg in DB2 V6 OS/390 with these parameters:
REORG TABLESPACE DSM1Q000.RM1F000 UNLOAD ONLY SHRLEVEL NONE
I then tried to run a Copy utility on the same tablespace while the reorg was running. The copy failed with the following:
DSNU050I DSNUGUTC - COPY TABLESPACE DSM1Q000.RM1F000 DSNU180I *DB1A DSNUGUCC - UTILITY IS NOT COMPATIBLE WITH THE REORG TABLESPACE UTILITY UTILID = DHXKU001, OBJECT = DSM1Q000.RM1F000 [...]
4869 78 33_Re: ASCII DB2 Database in OS/390?17_Linda F. Claussen18_lindafc@NETINS.NET31_Tue, 17 Jul 2001 09:17:44 -0500490_iso-8859-1 Jaap,
The main problem I ran into while testing ASCII table on the OS/390 was I could not JOIN an EBCDIC table to an ASCII table.
Cheers, Linda F. Claussen Claussen & Associates, Inc. http://showcase.netins.net/web/lclaussen/
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Slot, JP (Jaap) Sent: Tuesday, July 17, 2001 3:06 AM To: DB2-L@RYCI.COM Subject: ASCII DB2 Database in OS/390? Importance: High [...]
4948 122 38_Re: copy archive log from dasd to tape11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Tue, 17 Jul 2001 09:16:35 -0500555_iso-8859-1 Nothing that easy. I used BMC's Paclog to compress the archive logs. I added a step at the end that read the list of archives that had been processed and added them to GDG. Then, when it was time to send the stuff off-site, I generated the control cards for the DFDSS dump with the list of archive logs. I also had Rexx that generated the jcl to do everything. It generated the recover job (specifying the volsers since our image copy tapes weren't cataloged), the job to restore the archives, a job to update the BSDS for the conditional [...]
5071 149 15_Re: DB2 TRIGGER11_Wang, Jason29_jason.wang@CHECKSOLUTIONS.COM31_Tue, 17 Jul 2001 09:26:14 -0500530_iso-8859-1 Hi Bela,
Thanks for your help.
I tested the trigger you modified and it works great. The following is a run of it:
C:\buffer>db2 insert into nullid.prf (typid, prfname) values(1, 'test1'),(1,'test1') DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "Profile name (test1) already in use for the profile type (1 )". SQLSTATE=70001 [...]
5221 88 34_Re: DB2 for OS/390 V6 CPU increase16_Alison Pelletier17_ap84098@YAHOO.COM31_Tue, 17 Jul 2001 07:36:52 -0700322_us-ascii Thanks for the tips, however, we have degree(1) on all our packages since the first week of V6. We have not turned parallelism off at the subsystem level. Could there be some parallelism going on that we don't know about? Would I see it in the historical stats in OMEGAMON? Because stats say NO parallelism. [...]
5310 59 34_Re: DB2 for OS/390 V6 CPU increase16_Alison Pelletier17_ap84098@YAHOO.COM31_Tue, 17 Jul 2001 07:55:59 -0700611_us-ascii On the SQL/RID POOL/I/O PARALLELISM STATISTICS DETAIL screen I see no parallelism going on. I ran query on h2acct_sum table and see none there either.
Thanks, Alison. --- "Crichton, Jean" wrote: > Alison, > > If you look at the bufferpool statistics in > OMEGAMON, do you see any > parallel groups being executed? That's the easiest > way I know to tell if > you have what I call "stealth" parallelism going on. > > Jean E. Crichton > Logicon > > -----Original Message----- > From: Alison Pelletier [mailto:ap84098@YAHOO.COM] > Sent: Monday, July 16, 2001 1:06 PM > [...]
5370 18 34_Re: DB2 for OS/390 V6 CPU increase12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 17 Jul 2001 10:53:07 -0500485_- With DEGREE(1), parallelism is not likely to be the problem. Second on the list is access path changes. Third is memory configuration. What I've done a couple of times is to compare the accounting reports for an application before and after the conversion. The accounting trace long usually has something that points to the problem - lots more getpages, locking, many more statements being executed, ... Then it's often time to compare the access paths from version to version. [...]
5389 23 27_Re: colon in host variables12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 17 Jul 2001 11:06:55 -0500411_- The situation is a little more complex. BIND and REBIND errors will not occur if your DBRMs are V2R3 or later.
The documentation has stated that colons are required since V2, but the code changed in V6. There is a two page description of the situation in the red book, SG24-6108 V6 Technical Update, pages 13 and 14 in the hard copy or search for 2.1.2 Host variables must be preceded by a colon. [...]
5413 28 20_Indus Passport & DB218_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 17 Jul 2001 12:27:47 -0400296_iso-8859-1 Is anyone out there using Indus/Passport Version 6 with DB2 Version 6 on OS/390?? We currently are supporting an Indus application that is running Passport V6 and we were wondering if we could upgrade the DB2 to DB2 V6 with as few problems as we could. Would appreciate any info. [...]
5442 151 34_Re: DB2 for OS/390 V6 CPU increase16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 17 Jul 2001 17:32:02 +0100442_us-ascii Alison,
if you're running OMEGAMON II v 500, there are some PTFs to be applied for full compatibility (rather than toleration) with DB2 v. 6.1, relating especially to the Historical reporting on parallelism.
Please contact me offline, alternatively contact your local friendly Candle rep, who'll be able to advise you.
HTH.
Ciao!
Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd. [...]
5594 32 45_regarding INCLUDE DCLGEN..... immediate reply17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Tue, 17 Jul 2001 11:36:52 -0500429_- Hi Folk's
In our shop we are using DB2V6 on os390. in one of the cobol-db2 programe there is no INCLUDE statement to include the DECLGEN member for the table. when we did a compilation it gave lotz of wornings... but when we ran the program it ran successfuly. my doubt it is ran successfuly... without using DECLGEN member for the table in INCLUDE STATEMENT. can someone explain this????. is it possible in DB2V6. [...]
5627 60 22_Re: DB2 Connect thread9_Phan, Kim25_CKPhan@NATIONALGYPSUM.COM31_Tue, 17 Jul 2001 13:57:32 -0400508_iso-8859-1 Check DB2 connect to make sure you do not have cursorhold parameter on.
Kim Phan ckphan@nationalgypsum.com 704-365-7431
National Gypsum company 2001 Rexford Road Charlotte, NC 28212
-----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Friday, July 13, 2001 10:31 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect thread
Hi Brian, I already set CMTSTAT to INACTIVE. However, it still always ACTIVE. [...]
5688 30 25_Maximum number of views ?12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 17 Jul 2001 11:11:05 -0700537_us-ascii I've checked the archives and Appendix A (SQL limits)of the SQL guide, and I can't find any reference to the number of views allowed per table, database, partition, or instance. Does anyone know of any limits, and where they might be documented? Since they're not in the "Sql Limits" appendix, I'm assuming I'm only limited by how silly we want to be in coding ,maintaining, and managing a view for every conceivable combination of columns, and the amount of space I want to dedicate to the catalog. I'm on 7.1 EEE on AIX. [...]
5719 72 49_Re: regarding INCLUDE DCLGEN..... immediate reply23_Campbell, James B - CNF22_Campbell.James@CNF.COM31_Tue, 17 Jul 2001 11:21:50 -0700383_- I am unable to find the exact sentence in the DB2 manuals but a DCLGEN is not required (but is recommended by IBM) to access DB2 in a program.
It does however include the necessary host variable working-storage entries so that you don't need to hardcode them. You are always assured of the correct column definition for the host variable if you use a generated DCLGEN. [...]
5792 63 25_Re: Utility Compatibility23_Campbell, James B - CNF22_Campbell.James@CNF.COM31_Tue, 17 Jul 2001 11:25:36 -0700490_- You can't run both utilities at the same time against the same table - the data is in a state of flux - hence the message you received. James (Jim) Campbell Sr. Analyst & Test Systems Coordinator CNF ADTECH Phone: (503) 450-4839 Fax: (503) 450-3629 Email: campbell.james@cnf.com
-----Original Message----- From: Moss, William [SMTP:MossW@AETNA.COM] Sent: Tuesday, July 17, 2001 7:11 AM To: DB2-L@RYCI.COM Subject: Utility Compatibility [...]
5856 23 18_Insert next seq_no11_David Nance16_DWNance@FHSC.COM31_Tue, 17 Jul 2001 16:14:37 -0400498_US-ASCII I realize most folks will suggest using an identity column, but it too late in the process for now. Would appreciate some suggestions for the easiest way to insert a row and have the seq_no for the row be 1 + max of previous row for the unique key? I.E.; tablea has unique key of colA(= A) and Seq_no(=2), now I want to insert the next row for value A and the seq_no to be a value of 3. Do I first select the previous row with a max on the seq_no or is there an easy way to increment [...]
5880 26 23_DB2 6.1 and 16 bit apps17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Tue, 17 Jul 2001 15:38:09 -0500317_us-ascii Hello list,
We have a 16bit application written with Visual Basic 4.0 that will not run with a DB2 6.1 runtime client. It keeps crashing with a GPF on VB40016.dll It works just fine with the 5.2 runtime client
Does anyone know if DB2 supports 16 bit apps? If not how to I get around this? [...]
5907 48 22_Re: Insert next seq_no11_Wang, Jason29_jason.wang@CHECKSOLUTIONS.COM31_Tue, 17 Jul 2001 15:49:54 -0500461_iso-8859-1 creating a trigger as follows will do the trick.
CREATE TRIGGER incrementKeyno NO CASCADE BEFORE INSERT ON yourtablename REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL set newrow.seq_no = 1 + (select max(seq_no) from yourtablename); ;
Jason
-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: Tuesday, July 17, 2001 3:15 PM To: DB2-L@RYCI.COM Subject: Insert next seq_no [...]
5956 231 39_OS/390 DB2v7 and Java Stored Procedures12_David Booher22_David.Booher@QUEST.COM31_Tue, 17 Jul 2001 14:40:07 -0700629_iso-8859-1 Hello Gurus!
I've been working with Java Stored Procedures from a redbook and had good luck with DB2v6. However, the following is appearing on my new DB2v7 subsystem. The java code is exactly the same and it compiles clean, however here's what happens when the SP runs on v7:
---------------------------------------------------- li408_o_jar version of DSNX9JIR classpath from getenv: .:/usr/lpp/hpj/lib:/usr/lpp/db2/db2710/vajlinks:/usr/lpp/db2/db2710/classes, length is 75 libpath from getenv: /usr/lpp/hpj/lib:/usr/lpp/db2/db2710/lib:/usr/lpp/db2/db2710/vajlinks Creating VAJ JVM Return parm is 0 [...]
6188 57 29_Re: Maximum number of views ?14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 17 Jul 2001 17:10:22 -0500451_us-ascii The maximum would be related to the number of table objects allowed. While I am not 100% sure, I believe that would be 51,000 in a DMS system, limited by table size in an SMS or something near that.
In other words, more allowed than you need it would appear.
Richard Yevich YL&A - http://www.YLAssoc.com DB2 and Sysplex Gold Consultant Certified DB2 DBA for OS/390, z/OS Richard_Yevich@YLAssoc.com [...]
6246 59 29_SELECT COUNT(*) with CS or RR21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Wed, 18 Jul 2001 09:31:17 +0800598_big5 Hi,
We have a problem in using the SELECT COUNT(*) statement and would like to seek for advices. The problem is described as follow:
There are 2 online transactions accessing a DB2 table TBOBGENT which resides in a partitioned tablespace named TPOBGENT. The 2 transactions are GEN and RTV.
GEN is a single-thread transaction which performs 2 main functions. The first function is to get the counts based on some criteria and then based on the difference between these counts to trigger the second function which INSERT or DELETE records into/from the table TBOBGENT. [...]
6306 102 33_Re: SELECT COUNT(*) with CS or RR12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Tue, 17 Jul 2001 22:00:44 -0500491_us-ascii I think the CS count all is correct.
I think some rows must be changing between the executions of the other 9 queries.
A possible scenario:
> Consider a particular row: >> At the time of query 2, the row is NOT counted because it currently corresponds to query 10 >> While queries 3, 4, 5, 6, 7, 8, 9 are executed, the row is updated to correspond to query 2. >> At the time of query 10, the row is again NOT counted because it now corresponds to query 2. [...]
6409 102 33_Re: SELECT COUNT(*) with CS or RR11_Grant Allen22_grant@TOWERSOFT.COM.AU31_Wed, 18 Jul 2001 13:42:53 +1000545_iso-8859-1 Li, Chak Lung Dominic[SMTP:seddba@HK.SUPER.NET] wrote: > > Hi, > > > > We have a problem in using the SELECT COUNT(*) statement and would like > > to seek for advices. The problem is described as follow: > > > > There are 2 online transactions accessing a DB2 table TBOBGENT which > > resides in a partitioned tablespace named TPOBGENT. The 2 transactions > > are GEN and RTV. > > > > GEN is a single-thread transaction which performs 2 main functions. The > > first function is to get the counts based on some criteria and then [...]
6512 64 49_Re: regarding INCLUDE DCLGEN..... immediate reply16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 18 Jul 2001 09:58:34 +0530549_- DCLGEN is not must but required for two main reasons:
i) Table declaration ii) Host variable definition in the W/S section of a cobol program
Using the host variable of dclgen have advantages i.e. the data type and length are generated by dclgen utility and it is exactly same as the column definition. This could give the program better readability and reduce the errors like different data type and length variable definition. The data type and length mismatch was stage 2 for most of the cases and it is now as well. [...]
6577 18 15_Identity column9_john king24_john_king@REDIFFMAIL.COM31_Wed, 18 Jul 2001 05:15:41 -0000493_- Good Moring lister's here is a trouble I m facing and seeking all your assistances. There is a big table which has one billion of records and the table is clustered key is on one column and the primary key is on another column. So I m not getting a faster acces. As you know the size of table is more so tried to make the access as faster as possible. The way I tried is to make one column as identity like generated always as identity , so that I will get each time a unique as well I [...]
6596 35 41_Dataset closure and the performance issue8_duam lee16_duam_lee@USA.NET29_Wed, 18 Jul 2001 00:16:39 MDT575_US-ASCII Hi all thanks to everybody for replying to my earlier queries regrading the alter tablespace with unicode context. Here is again another design issue I m facing which i describe below.
We have CLOSE YES and CLOSE NO option in tablespace definition and it is recommended that the most critical tables I mean referenced tables should be close no. As far I know the DSNZPARM for DSMAX is set to some value. Even when that threshold is reached the close no datasets also closed. Here is the doubt comes. As far I know the datasets takes 1.8 seconds to close [...]
6632 38 33_Re: SELECT COUNT(*) with CS or RR14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 18 Jul 2001 02:48:50 -0500418_- While RS prevents updates to rows that have been retrieved, it prevents neither insertion-of-rows nor update-of-rows where the inserted/updated rows satisfy the WHERE criteria (except, obviously, update of rows that have been retrieved).
Me? I'ld follow Grant and group by rec_stus.
James Campbell
On Tue, 17 Jul 2001 22:00:44 -0500, Higgins John wrote: [...]
6671 103 33_Re: ASCII DB2 Database in OS/390?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 18 Jul 2001 09:15:16 +0100470_ISO-8859-1 note - not that it relates (sorry), but the Catalog can be ASCII I think from V7 .. anybody confirm this?
Les
-----Original Message----- From: Linda F. Claussen [mailto:lindafc@NETINS.NET] Sent: Tuesday, July 17, 2001 3:18 PM To: DB2-L@RYCI.COM Subject: Re: ASCII DB2 Database in OS/390?
Jaap,
The main problem I ran into while testing ASCII table on the OS/390 was I could not JOIN an EBCDIC table to an ASCII table. [...]
6775 79 44_What are TCB and SRB times? - second try :-)15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL31_Wed, 18 Jul 2001 09:47:35 +0200304_us-ascii Halo Listers,
Two weeks ago I send a message with query about meaning of TCB/SRB times and got no answer. It would be very nice to find someone willing to share her/his knowledge on this subject with me or (if You think its obvious and boring) to point me the source of information. [...]
6855 169 15_Re: DB2 TRIGGER14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 18 Jul 2001 03:26:23 -0500451_- Jason
From SQL Ref Manual, CREATE TRIGGER: "NO CASCADE BEFORE Specifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database." (note "any changes") A pretty clear (well, clear to those fluent in IBM-ese and have the benefit of hind-sight) statement that a BEFORE trigger will be executed for each row before the table is actually modified. [...]
7025 203 22_Re: Insert next seq_no14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 18 Jul 2001 09:39:45 +0100538_iso-8859-1 Hmmm
On the basis triggers are NOT executed serially, how do you guard against duplicates???
My first thought was to ask "Why is it too late for Identity columns?"
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Wang, Jason [mailto:jason.wang@CHECKSOLUTIONS.COM] Sent: 17 July 2001 21:50 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Insert next seq_no [...]
7229 60 21_Re: Reorg On SYSDBASE14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 18 Jul 2001 03:57:56 -0500627_- Tekin
The rows in SYSDBASE are (or, at least they were in V6 one I had a good look at) placed somewhat differently from a normal tablespace.
Each page contains rows relating to a single tablespace. Rows are arranged in the sequence: SYSTABLESPACE row, SYSTABLEPART rows, first SYSTABLES row, SYSCOLUMNS rows for the table, first SYSINDEXES row for the table, SYSINDEXPART rows, SYSKEYS rows, second SYSINDEXES row, etc, second SYSTABLES row and so on. If necessary, this sequence will flow onto a second, or subsequent, page. Of course, as you add columns, indexes and foreign keys, this sequence will get [...]
7290 40 35_DBM1 CPU on SAP R/3, DB2 for OS/3900_18_mebert@AMADEUS.NET31_Wed, 18 Jul 2001 11:06:29 +0200449_us-ascii Hello List,
here is a rather fuzzy question concerning our SAP DB2. I have long since had the impression that something is not right there, performancewise. The thing I have to go on is often a very high wait time using the LPAR (doing edits, or jobs being IN but not using CPU). The most obvious difference between our production SAP DB2 and our production legacy DB2 are in the DBM1 resource consumption, as shown by SDSF.DA: [...]
7331 76 15_Re: DB2 TRIGGER11_Hamar, Bela22_Bela.Hamar@VARETIS.COM31_Wed, 18 Jul 2001 12:33:42 +0200443_us-ascii Hi Jason,
in your trigger, you have to check two cases: C1: whether any new row duplicates an exisiting row in the table C2: whether any two of the new rows are duplicates with each other
"after" (AT) vs. "before" (BT) triggers:
- in an insert BT you can reference only the new row(s) (NEW) - while in an insert AT, you can access the new row(s) (NEW) as well as all the inserted rows as a whole (NEW_TABLE) [...]
7408 36 39_Re: DBM1 CPU on SAP R/3, DB2 for OS/39010_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 18 Jul 2001 12:38:59 +0200515_us-ascii Hi Dr. Ebert
ALL TCBs + IRLM SRBs CPU times are usually very low relative to MSTR/DBM1 SRBs and as far as I can see from your small display this is your case so following Gopal Krishnan the ratio DBM1/MSTR in your system seems 'normal' .
More meaningful measurements are TCBs & SRBs times of DB2 address spaces using DB2 PM statistics report. You must have MSTR/DBM1 TCBs time << MSTR/DBM1 (say at least 5 times) and (in general) MSTR SRB time < DBM1 SRB time (DBM1 does a lot of work). [...]
7445 78 48_AW: What are TCB and SRB times? - second try :-)12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Wed, 18 Jul 2001 13:05:03 +0200529_iso-8859-1 Hi Piotr,
just from my rudimentary MVS (OS/390) knowledge:
A Task Control Block can have 3 different states: READY, ACTIVE or WAIT. As far as I know TCB TIME is the accumulated time the TCB was active, derived from TCBTTIME (8 bytes register) of TCB address space. So it should be the CPU time elapsed, without I/O (state=WAIT). Couldn't find such kind of time-register for SRBs, but the manual was for an older version of OS/390. Maybe some of the gurus will jump in. Hope this helps a little bit. [...]
7524 41 24_Query Parallelism DB2 V618_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 18 Jul 2001 07:09:02 -0400517_iso-8859-1 We are currently upgrading to DB2 v6 and I'm stumbling a little over understanding how to turn this new feature on. (or even if I want to) At this time all our plans and packages are bound with DEGREE = '1'. From what I can read in the manual for my Dynamic SQL stmts it says to set the CURRENT DEGREE special register to 'ANY'. I'm not sure how this is done and I can't find anything else in any of the manuals that I have that talks about a 'special register'. I've read here about pros and cons to [...]
7566 24 52_Re: AW: What are TCB and SRB times? - second try :-)10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 18 Jul 2001 13:31:47 +0200524_us-ascii Hi Piotr ...
Take a look at
http://www.mvsbook.fsnet.co.uk/contents.htm
Its pretty old but I think it's useful to answer your questions. Look at 'MVS internals' and choose 'Task management'
HTH
Max Scarpa Cheap,useless and bad DB2 sysprog
================================================ 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.
7591 113 48_Re: What are TCB and SRB times? - second try :-)16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 18 Jul 2001 17:04:42 +0530281_iso-8859-1 If i classify it different way, can i say that TCB is for all synchronous task and SRB is for asynchronous tasks. This is what i was thinking about till date. This should be pointed out by someone if it is wrong, however i do not deny the other details about this. [...]
7705 64 28_Re: Query Parallelism DB2 V610_Bruce, Mae26_BruceM@MAIL.OA.STATE.MO.US31_Wed, 18 Jul 2001 07:33:44 -0500337_iso-8859-1 There is a SET CURRENT DEGREE= statement to do this. You can set it to a value (either 'ANY' or '1') or set it to a host variable (that has the value of either 'ANY' or '1'.)
With the packages that come with DB2 Connect, etc you can set your DEGREE within the BIND statement by specfying DEGREE(ANY) or DEGREE(1). [...]
7770 23 19_Re: Identity column13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 18 Jul 2001 08:36:44 -0400388_iso-8859-1 Hello John, I have experimented lots with Identity column and had no problems creating them.
Why don't you post your Create (or alter) DDL syntax and post the error message you're getting. I'm confident someone on the list will be able to pinpoint what's going wrong.
David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.com [...]
7794 142 48_AW: What are TCB and SRB times? - second try :-)12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Wed, 18 Jul 2001 14:37:40 +0200614_iso-8859-1 Hi Sanjeev,
according to the manuals, to my collegues (and my protein-based hard disk drive :-)), I'd aggree with you. But I still hope, that some of the specialists will join the issue.
Best regards
Thomas
> -----Ursprüngliche Nachricht----- > Von: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] > Gesendet am: Mittwoch, 18. Juli 2001 13:35 > An: DB2-L@RYCI.COM > Betreff: Re: What are TCB and SRB times? - second try :-) > > If i classify it different way, can i say that TCB is for all > synchronous > task and SRB is for asynchronous tasks. This is what i was [...]
7937 62 39_Re: DBM1 CPU on SAP R/3, DB2 for OS/39014_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 18 Jul 2001 08:44:49 -0400432_ISO-8859-1 Because of the different types of of work each address spaces does. Usually, one of the largest contributors to higher CPU of DBM1 is Prefetch, and this is charged as SRB time. While it may or may not affect CPU, I would not rely too heavily on SAP reviews. Systems have been tuned differently from SAP recommendations several times, providing substantial performance improvements for reduced I/O and elapsed times. [...]
8000 51 45_Re: Dataset closure and the performance issue14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 18 Jul 2001 08:48:41 -0400572_ISO-8859-1 How can you doubt that open close impacts response time? You even provided your own number. The close itself won't hurt the application, but the open absolutely will. Also, both cause CPU cycles to perform... so then it becomes an issue of how frequently you close/open objects.
Regards, Joel
Message text written by DB2 Data Base Discussion List > Hi all thanks to everybody for replying to my earlier queries regrading the alter tablespace with unicode context. Here is again another design issue I m facing which i describe below. [...]
8052 107 39_If you are NOT in the UK, please ignore14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 18 Jul 2001 13:56:30 +0100450_iso-8859-1 OK,
Assuming you are only reading this if you ARE in the UK(!), I am finalising the agenda for the next DB2 GUIDE meeting and I am still looking for one (or maybe two) user speakers. All I would need is about 30 minutes of you talking about the way you are using DB2 and any of the pitfalls or advantages you have found. It does NOT have to be anything exotic or (b)leeding edge - just interesting to the rest of the audience. [...]
8160 94 22_Re: Insert next seq_no13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Wed, 18 Jul 2001 08:07:16 -0500517_iso-8859-1 To me it looks like you are not really looking for an identity column approach, but more of a sequence number approach? In other words, you don't want 1,2,3,4,5,... but instead A-1, A-2, A-3, B-1, B-2, C-1... If this is the case (and you are not concerned with concurrency on the inserts of values with the same high-order key), you can collapse the insert and the select(max) into a single statement. Would look something like this (where COL1 is the high-order key, and COL2 the sequence number)... [...]
8255 185 45_Re: Dataset closure and the performance issue14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 18 Jul 2001 13:59:31 +0100364_iso-8859-1 Also, and I have lost track of this a little in the transition from V5 -> V6 -> V7, it USED to be that CLOSE YES datasets were closed on a Least Recently Used basis, so DB2 would close the ones it wasn't actively using.
BUT
CLOSE NO datasets were closed on a FIFO basis (i.e. the one that had been open the LONGEST was closed first)! [...]
8441 90 71_Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2 V6 OS/39012_Troy Coleman19_Colematr@MEIJER.COM31_Wed, 18 Jul 2001 09:16:51 -0400542_US-ASCII Hello List, We have a problem with a program that is trying to update the last column of a partition key and receives SQLCODE -151. We noticed a lot of columns marked as updates = 'N' on partitioned tables. Our theory is that the table was created before this feature became available. The table in test is fine because it was just recently dropped and rebuilt. However, we do not really want to drop and recreate this in production since it is so large. Any ideas other than changing the application and/or dropping the table? [...]
8532 49 35_Triggers & Stored Procedures DB2 V618_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 18 Jul 2001 09:18:28 -0400567_iso-8859-1 HELP! (again).... I'm posting this on behalf of our system programmers and myself. We are currently upgrading to DB2 V6 from DB2 V5 and the question of triggers & stored procedures has come up. We have used neither.
For Stored Procedures: can anyone tell me how they are using them and how they create them? Ie; which language you use to create them, what product requirements are needed in order to create them. (if that makes sense). We were told by a developer here that we need a 'C' compiler etc. And he mentioned JAVA but we have no idea [...]
8582 175 14_Re: DDF thread18_Sandra Lakenburger34_sandra.lakenburger@MAIL.SPRINT.COM31_Wed, 18 Jul 2001 08:30:17 -0500602_- On Wed, 11 Jul 2001 09:00:01 -0700, Myron Miller wrote:
>Well, looks like it really spread. We have an open problem with IBM on >this as well (Sev 2 right now). Sometimes -STO DDF MODE(FORCE) works >and sometimes it doesn't. Sometimes, after the -STO DDF MODE(FORCE) >after a lengthy period (up to 15 minutes) the thread will go away and >sometimes the only way to get rid of the thread is to KILL DB2 which is >obviously not a good solution. > >And we're running DB2 OS/390 V6 and DB2 Connect V7.1 EE. >--- "Shapiro, Dave" wrote: >> [...]
8758 170 48_Re: What are TCB and SRB times? - second try :-)16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 18 Jul 2001 18:41:09 +0530600_iso-8859-1 Yes Thomas, i too expect some expert to give some more information something like you have mentioned about TCB. This is required for getting the idea from OS point of view.
Regards Sanjeev
> -----Original Message----- > From: Thomas Weber [SMTP:thomas.weber@LDS.NRW.DE] > Sent: Wednesday, July 18, 2001 6:08 PM > To: DB2-L@RYCI.COM > Subject: AW: What are TCB and SRB times? - second try :-) > > Hi Sanjeev, > > according to the manuals, to my collegues (and my protein-based hard disk > drive > :-)), I'd aggree with you. But I still hope, that some of the specialists [...]
8929 28 19_Re: CLOBS on Os/39014_Craig McKellar26_craig.mckellar@BIGPOND.COM31_Wed, 18 Jul 2001 23:51:16 +1000573_us-ascii We are currently reviewing the use of LOBs in the os/390 environment using DB2 v6.1. One of our clients are interested in using CLOBs in particular but we have delayed the implementation while we investigate all the issues involved with implementing them. I am particularly interested in any gotcha's (issues/probs) that others may have experienced and also any performance impacts. Our client plans to code their application with cobol at the os/390 level and JAVA for the web base applications. If anyone has any experiences I would love to hear from them. [...]
8958 77 39_Re: Triggers & Stored Procedures DB2 V611_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Wed, 18 Jul 2001 08:48:00 -0500467_iso-8859-1 We're not using stored procedures yet in our applications. You don't need a C compiler unless you want to use Stored Procedure Builder or write them in C. You can use other languages (Cobol, Rexx, Assembler, etc.). The IVP stored procedure programs are all in C (maybe PL1 too?) -- none in Cobol. For Java, make sure you've installed FMID JDB6612. You will need to work with your OS/390 systems programmer to set up WLM-established stored procedures. [...]
9036 28 31_Indirect reference to FREE page17_jennifer jennifer22_jenni_jeni@HOTMAIL.COM31_Wed, 18 Jul 2001 13:39:30 -0000492_- Hi All, I m a new lister seeking all your help. There is a design aspect where I do have doubt. The concept of indirect reference with varchar and free page is not clear to me. My coleagues say if there are varchar columns in table and updates are there for that table then we need to have enough free space for that table. For asynhornous I/O the page gets into buffer pool is 32 pages at a time if the page size is 4k. But if at all we need to give 30 to 35 freepage the masters are [...]
9065 120 74_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2 V6OS/39019_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Wed, 18 Jul 2001 09:54:28 -0400410_US-ASCII Troy,
How about temporarily saving the rows in question to an interim table (similar structure) and deleting from the original table. Once you make the necessary updates to the interim table rows, reinsert into the original table. This should work.
Murari Selvakesavan DBA Group First Health Services Corp. mselvakesavan@fhsc.com
>>> Colematr@MEIJER.COM 07/18/01 09:16AM >>> [...]
9186 81 39_Re: Triggers & Stored Procedures DB2 V612_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 18 Jul 2001 07:03:23 -0700384_us-ascii My client has several hundred stored procedures, most of which are in the SPL language built using the Stored Procedure Builder (requires C or C++ compiler on mainframe). Others are in COBOL.
We have a few COBOL triggers.
Both of which require WLM and RRS to be setup by your systems programmers. The Redbooks are a good place to start on how to use them. [...]
9268 201 28_Re: Query Parallelism DB2 V60_24_Bruce.Williamson@PSIR.IE31_Wed, 18 Jul 2001 15:07:21 +0100842_iso-8859-1 Howzit Stephen?
Have a look at 2.5.19 in the "Installation Guide"
6. CURRENT DEGREE _______________________ ________________________________________________
| Acceptable values: | 1, ANY |
|_______________________|________________________________________________| | Default: | 1 |
|_______________________|________________________________________________| | Update: | option 18 on panel DSNTIPB |
|_______________________|________________________________________________| | DSNZPxxx: | DSN6SPRM CDSSRDEF |
|_______________________|________________________________________________|
Specifies the default for the CURRENT DEGREE special register when no degree is explicitly set using the SQL statement SET CURRENT DEGREE. Accepting the default disables query parallelism. [...]
9470 55 54_Example of stored procedure with in and out parameters16_Proctor, William25_william.proctor@TGSLC.ORG31_Wed, 18 Jul 2001 09:24:35 -0500346_iso-8859-1 Hello, Can someone give me an example of a trigger that calls a stored procedure using a literal value on the input parameter and receives an integer value back from a table referenced in the procedure. How do you define the out parameter in the trigger and reference the data when its returned. Thankx for all replys in advance. [...]
9526 191 14_Re: DDF thread13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Wed, 18 Jul 2001 09:30:25 -0500457_iso-8859-1 Just an update on our issue: IBM has flagged a third party monitor product as the cause and they have concurred and offered a temporary workaround.
Dave
-----Original Message----- From: Sandra Lakenburger [mailto:sandra.lakenburger@mail.sprint.com] Sent: Wednesday, July 18, 2001 8:30 AM To: DB2-L@RYCI.COM Subject: Re: DDF thread
On Wed, 11 Jul 2001 09:00:01 -0700, Myron Miller wrote: [...]
9718 109 39_Re: Triggers & Stored Procedures DB2 V617_Okronglis, Pete M22_pete.okronglis@NWA.COM31_Wed, 18 Jul 2001 09:23:15 -0500651_iso-8859-1 You do need a C compiler for SQL stored procedures.
-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: Wednesday, July 18, 2001 08:48 To: DB2-L@RYCI.COM Subject: Re: Triggers & Stored Procedures DB2 V6
We're not using stored procedures yet in our applications. You don't need a C compiler unless you want to use Stored Procedure Builder or write them in C. You can use other languages (Cobol, Rexx, Assembler, etc.). The IVP stored procedure programs are all in C (maybe PL1 too?) -- none in Cobol. For Java, make sure you've installed FMID JDB6612. You will need to work with your [...]
9828 89 38_Incorrect Output from tuning SQL Query12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 18 Jul 2001 07:38:39 -07001102_us-ascii I ran into the following query being executed from COGNOS. It was taking over 35 CPU minutes to process. select count(*) from ( select T7."OPTION_CODE", T2."OPTION_CODE",T2."OPT_PACK_CODE", T1."CHASSIS_NUMBER",T3."TIME_STAMP", T5."BILL_TO_STATE", 'SER' || T6."SERIES_CODE",'NA' || T6."NA_MODEL_CODE", T5."RETAILER_CODE",T5."VEHICLE_REGION",T4."BEGIN_DATE", T2."OPT_COST_EUR" * T3."EXCH_RATE_EUR_DOL",T3."EXCH_RATE_EUR_DOL",
T2."OPT_COST_EUR",T1."INVOICE_DATE" from "UINTWHSL" T1,"UINTAGIV" T3,"UINTAGIO" T2,"USATRPDT" T4, "FDMTMAST" T5,"UINTAUTO" T8,"UMKTNMDY" T6,"UPRTAUOP" T7 where T3."AG_INVOICE_TYPE" in ('##', '10', 'F#') and T7."CHARGEABLE_OPT_IND" = 'Y' and T1."INVOICE_DATE" >= {d '2001-01-01'} and T1."INVOICE_TYPE" = 'DR' and T1."CHASSIS_NUMBER" = T7."CHASSIS_NUMBER" and T8."AG_MODEL_CODE" = T6."AG_MODEL_CODE" and T8."MODEL_YEAR" = T6."MODEL_YEAR" and T6."MODEL_SUB_GROUP" = T8."MODEL_SUB_GROUP" and T1."CHASSIS_NUMBER" = T8."CHASSIS_NUMBER" and T1."WHOLESALE_DEALER" = T5."RETAILER_CODE" and T1."INVOICE_DATE" <= T4."END_DATE" and T1."INVOICE_DATE" >= T4."BEGIN_DATE" [...]
9918 235 14_Re: DDF thread12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 18 Jul 2001 07:40:05 -0700628_us-ascii Which third party? We have the same issue. And in our test system, our monitor is turned off and we still get the problem. --- "Shapiro, Dave" wrote: > Just an update on our issue: IBM has flagged a third party monitor > product as the cause and they have concurred and offered a temporary > workaround. > > Dave > > -----Original Message----- > From: Sandra Lakenburger [mailto:sandra.lakenburger@mail.sprint.com] > Sent: Wednesday, July 18, 2001 8:30 AM > To: DB2-L@RYCI.COM > Subject: Re: DDF thread > > > On Wed, 11 Jul 2001 09:00:01 -0700, Myron Miller > [...]
10154 230 14_Re: DDF thread17_Sanfilippo, David34_David.Sanfilippo@GMACINSURANCE.COM31_Wed, 18 Jul 2001 10:46:16 -0400406_iso-8859-1 Is you third party monitor TMON for DB2? What version?
-----Original Message----- From: Shapiro, Dave [mailto:Shapiro.Dave@PRINCIPAL.COM] Sent: Wednesday, July 18, 2001 9:30 AM To: DB2-L@RYCI.COM Subject: Re: DDF thread
Just an update on our issue: IBM has flagged a third party monitor product as the cause and they have concurred and offered a temporary workaround. [...]
10385 87 58_Re: Example of stored procedure with in and out parameters14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Wed, 18 Jul 2001 09:46:42 -0500445_iso-8859-1 You need to define your stored procedure as a UDF instead, although that does require WLM. Then using the UDF, pass it the parm, and it returns the integer value which gets set into the row's column: using the line from your example...
SET RECORDID = SPCTR001('LOG');
Richard Yevich YL&A - http://www.YLAssoc.com DB2 and Sysplex Gold Consultant Certified DB2 DBA for OS/390, z/OS Richard_Yevich@YLAssoc.com [...]
10473 21 38_Stored Procedure Builder Build Utility12_Phil Jackson21_PJackson@TXFB-INS.COM31_Wed, 18 Jul 2001 09:56:50 -0500427_- We are trying to use the Stored Procedure Builder to build SQL stored procedures to run on the os/390. (DB2 UDB v 6.1)
This uses as it's build utility DSNTPSMP. We discovered that we have no c compiler on the mainframe.
So, the question is: Is there a way to direct DSNTPSMP to call a COBOL compiler, or is there another build utility other than DSNTPSMP that can compile the stored procedures into cobol? [...]
10495 48 42_Re: Stored Procedure Builder Build Utility18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 18 Jul 2001 11:15:52 -0400467_iso-8859-1 Phil: We ran into the same thing here and we're stuck? Hope we can get some good workarounds..
--Steve....
Steve Whittaker DB2/DBA Progress Energy, Inc. Mail stop:OHS 10- Raleigh Email: stephen.whittaker@pgnmail.com Phone: (919) 546-7267
-----Original Message----- From: Phil Jackson [mailto:PJackson@TXFB-INS.COM] Sent: Wednesday, July 18, 2001 10:57 AM To: DB2-L@RYCI.COM Subject: Stored Procedure Builder Build Utility [...]
10544 150 25_Re: FW: Reorg On SYSDBASE64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Wed, 18 Jul 2001 10:44:16 -0500578_ISO-8859-1 If memory serves me correctly, the whole reason that the subject of the need to REORG the catalog came about was because of a bug in DB2 (a bad APAR, PL38986 (circa March '89) that caused SYSDBASE, SYSPLAN and SYSVIEWS to become unclustered. (Clustering is not the same as a user tablespace clustering, but is still clustered nonetheless.) The bug was detected some time after the PTF was in the field for a while, and then subsequently fixed (PL48248, circa Aug. '89). However, data that had been placed poorly by the bug was still poorly placed and the fix to [...]
10695 100 42_Re: Stored Procedure Builder Build Utility13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 18 Jul 2001 10:44:18 -0500576_iso-8859-1 Hi,
I posted a question in April along similar lines which maybe of interest. Below is a reply from Leon Katsnelson.
There are no immediate plans that I am aware of. If this is important to you then I suggest that you submit a requirement through your IBM database specialist or sales rep. This by no means guarantees that your requirement will be addressed but it does enter it in to the system and if there are others who have submitted the same requirement the chances of it being worked on will increase. on the other hand, if you do [...]
10796 121 42_Re: Stored Procedure Builder Build Utility18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 18 Jul 2001 11:59:16 -0400555_iso-8859-1 All I can say to that is YUK!!!! We're STUCK!! I think...
Steve Whittaker DB2/DBA Progress Energy, Inc. Mail stop:OHS 10- Raleigh Email: stephen.whittaker@pgnmail.com Phone: (919) 546-7267
-----Original Message----- From: Mackey, Glenn [mailto:GMackey@GUIDEMAIL.COM] Sent: Wednesday, July 18, 2001 11:44 AM To: DB2-L@RYCI.COM Subject: Re: Stored Procedure Builder Build Utility
Hi,
I posted a question in April along similar lines which maybe of interest. Below is a reply from Leon Katsnelson. [...]
10918 19 22_DB2 Connect V7 via SNA10_Tom Taylor17_ttaylor@CHUBB.COM31_Wed, 18 Jul 2001 12:08:51 -0400480_us-ascii HI all Excuse me for my ignorance, I'm not a network person.
Can you configure db2 connect PE v7 using SNA from a laptop over ethernet connected to a router that is on a tokenring that connects to the mainframe.
Tom
================================================ 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.
10938 54 26_IMS RTT default plan name.12_Phil McLaren26_Philip.McLaren@BOIMAIL.COM31_Tue, 17 Jul 2001 17:09:40 +0100411_us-ascii Hi folks,
Would anyone know of a means to force a specific RTT module to always use the same DB2 plan name (similar in concept to specifying a PLAN name on the POOL entry in a CICS RCT)?
This is IMS/DC, DLI batch has this flexibility (via DDITV02) but online it appears we always have to specify a program to plan match. What I am looking for is a way of doing the equivalent of.... [...]
10993 83 39_Re: DBM1 CPU on SAP R/3, DB2 for OS/39012_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Wed, 18 Jul 2001 12:08:49 -0500379_us-ascii Joel,
Are you just going to drop it at that?
Or, can you give us some hints as to the inefficient SAP recommendations?
Best regards, John P. Higgins Performance Specialist SAP BASIS Team
Deere & Company Voice: (309) 748-1625 Attn: GTTC East FAX: (309) 748-1605 One John Deere Place email: HigginsJohn@JohnDeere.com Moline, IL 61265-8098 [...]
11077 30 33_Re: ASCII DB2 Database in OS/390?12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 18 Jul 2001 11:55:26 -0500506_- The DB2 catalog in V7 and before is EBCDIC, so if you join anything to the catalog, it must be EBCDIC. Is your conversion a single byte code page? If so, the translation is essentially a translate instruction on S/390 and zSeries, and the performance is not an issue (usually under 1% or not measurable). There are some cases where the ASCII cases do not perform as well as EBCDIC. Pulling down the data to UNIX, then this is a tight loop on the UNIX machine and I have not seen performance issues. [...]
11108 101 39_Re: Triggers & Stored Procedures DB2 V612_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 18 Jul 2001 12:17:22 -0500594_us-ascii Here's what we do... (Sorry for the length)
We don't have WLM (rats!) so we're limited to DB2 managed address space Stored Procedures.
Therefore, (without WLM) we cannot have SQL (or stored procedure builder) stored procedures. (For this, I believe we would be required to have a C compiler because the SQL in the SQL stored procedure is converted/wrapped in C code.) We have no C compiler. We cannot (without WLM) have Rexx stored procedures either. All of that is fine, I'm just giving you our situation. (I press for WLM daily -- at least in compatability mode!) [...]
11210 24 58_Re: Example of stored procedure with in and out parameters12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 18 Jul 2001 12:24:40 -0500397_us-ascii Hi.
Richard has already pegged this one, so I'll just add (again) that this is why I have a second set of stored procedure definitions with the parameters all defined as "IN".
The limitation of the UDF is that only a single value is returned. We'd like to use stored procedure output in our BEFORE triggers to enforce business rules and do surrogate key substitutions. [...]
11235 32 25_Re: FW: Reorg On SYSDBASE12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 18 Jul 2001 12:29:20 -0500555_- Maybe I should add in my real opinion.
There are some clear situations where reorganizing the catalog makes a difference. If you had plans and converted to packages and you have freed the plans, then reorganizing those catalog and directory table spaces can give back the free space. If you deleted the oldest 10,000 tables from that test subsystem, it is time to reorg. Other than in some DB2 bug situations, the DB2 catalog uses a couple of techniques that avoid the need to reorg. The DB2 catalog access does not use SQL or optimization. [...]
11268 15 25_Re: Utility Compatibility12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 18 Jul 2001 12:44:41 -0500283_- Here are a couple of thoughts, but utilities are not my my strength. If you're unloading, then SHRLEVEL REFERENCE might be enough. Can you use COPYDDN instead of another pass? I looked at the book, and it looks as though you can call the service group to see what's going on. [...]
11284 84 30_Re: IMS RTT default plan name.10_Tom Taylor17_ttaylor@CHUBB.COM31_Wed, 18 Jul 2001 13:46:40 -0400450_us-ascii Phil
The V5 installation guide details the use of a IMS RTT on page 2-360 thru 2-366 "defining DB2 to IMS" I don'tthink you can specify a '*'. Each program must be specified
Tom
Phil McLaren @RYCI.COM> on 07/17/2001 12:09:40 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List [...]
11369 30 44_Delphi calling DB2 stored procedures problem12_Sams, Debbie13_dsams@UFL.EDU31_Wed, 18 Jul 2001 14:25:01 -0400407_iso-8859-1 I have a user who is using Delphi V5. He is trying to call a DB2 (os/390 V5) stored procedure It ran the first time but each subsequent time returns a SQLCODE of -551. Any suggestins?
Debbie Sams Coordinator, Computer Applications University of Florida Information Systems (352) 392-1285, Fax: (352) 392-6635 33 Tigert Hall Email: dsams@ufl.edu P.O. Box 113275 Gainesville, FL. 32611 [...]
11400 31 30_DRDA Security -- OS/390 to AIX13_Harry Woelfer27_Harry_Woelfer@MCCORMICK.COM31_Wed, 18 Jul 2001 14:39:48 -0400334_us-ascii Hello, list...
We have an application that uses a DRDA connection between DB2 V6.1 on OS/390 and DB2 V7.1 on AIX.
The connection is established by a program running on the mainframe. The problem is that each cross-platform SQL call sends a user ID and password across the TCP/IP connection, in clear text. [...]
11432 27 45_Re: Dataset closure and the performance issue12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 18 Jul 2001 13:49:04 -0500310_- There is a section of the Administration Guide titled "Managing the Opening and Closing of Data Sets", which goes through the various tuning options, followed by Understanding the CLOSE YES and CLOSE NO Options. This section seems to need changes on every version. I recommend it as the starting point. [...]
11460 104 39_Re: DBM1 CPU on SAP R/3, DB2 for OS/39014_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 18 Jul 2001 15:11:36 -0400367_ISO-8859-1 Yes John, unfortunately I do have to drop it at that. Tuning changes are always specific and unique to every client site. The tuning improvements are related specifically to object grouping/placement into pools, number of pools, pool sizes, and pool thresholds.
Regards, Joel
Message text written by DB2 Data Base Discussion List >Joel, [...]
11565 190 48_Re: What are TCB and SRB times? - second try :-)13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 18 Jul 2001 12:31:23 -0700598_iso-8859-1 I'm sorry to say I'm not an expert, but maybe I can add a little bit. TCB time is the time your program was active. From the Authorized Assembler Services Guide:
-------------------- A program uses an SRB to initiate a process in another address space or in the same address space. The advantage of scheduling an SRB is that an SRB routine is asynchronous in nature and runs independently of the scheduling program. This advantage makes SRBs very useful in the following situations, where the scheduling program does not need to wait for the SRB routine to finish running: . [...]
11756 16 25_Re: FW: Reorg On SYSDBASE11_Joan Keemle25_KeemleJoanT@JOHNDEERE.COM31_Wed, 18 Jul 2001 14:22:42 -0500366_- Roger, please clarify - I thought it better to reorg after migrating to a new release, in part because of new columns added to catalog tables. I was under the impression that catalog rows could become fragmented just as user data when row lengths change and rows are relocated. We don't reorg very often, but my choice was post-migration. Which is preferred? [...]
11773 68 31_open position referral BONUS$$$12_Confidential26_confidential@ECFACTORY.NET31_Wed, 18 Jul 2001 15:28:05 -0400525_iso-8859-1 I am currently looking for a person with Database Architect experience for a position in Columbus Ohio. The opportunity offers a strong salary plus a nice sign on bonus plus full relo and a host of outstanding benefits. If you know of anyone who may be a good fit or if you want to know a little more detail yourself please reply or call and I would be happy to help in any way I can. We are not able to sponsor anyone with H1-b visa status. I can reward anyone with a referral bonus of $2000.00 if we secure [...]
11842 75 39_Re: Triggers & Stored Procedures DB2 V614_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Wed, 18 Jul 2001 14:50:20 -0500686_iso-8859-1 The SQL Procedure Language requires a C compiler for stored procedures today.
Richard Yevich YL&A - http://www.YLAssoc.com DB2 and Sysplex Gold Consultant Certified DB2 DBA for OS/390, z/OS Richard_Yevich@YLAssoc.com
> -----Original Message----- > From: Whittaker, Stephen [mailto:stephen.whittaker@PGNMAIL.COM] > Sent: July 18, 2001 8:18 AM > To: DB2-L@RYCI.COM > Subject: Triggers & Stored Procedures DB2 V6 > > > HELP! (again).... > I'm posting this on behalf of our system programmers and myself. > We are currently upgrading to DB2 V6 from DB2 V5 and the question of > triggers & > stored procedures has come up. We have used [...]
11918 58 14_Visual Explain14_Scott, Matthew18_MScott@ALFAINS.COM31_Wed, 18 Jul 2001 15:20:16 -0500405_iso-8859-1 Is it possible to monitor and capture SQL statements with Control Center on NT? I want to use Visual Explain to explain statements created dynamicly. If it's possible, would someone be willing to send a few instructions. If not, would someone be willing to suggest an application that does?
Thanks, Matthew R. Scott Database Administrator Alfa Insurance mscott@alfains.com (334) 613-4895
11977 73 50_Do I need backup indexspace of directory database?10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Wed, 18 Jul 2001 14:47:32 -0600409_iso-8859-1 Hi! I backup directory and catalog tablespaces already. Do I need to backup indexspaces of directory database too? I backup archive log already. Do I need backup active log? If not, I have to create BSDS and active log on disaster recovery site. Is that correct?
Grace Chen Technical Support Credit Union Central Alberta Ltd. Tel: (403) 258-5982 E-mail : gchen@cucentral-ab.com [...]
12051 51 34_Re: DRDA Security -- OS/390 to AIX14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 18 Jul 2001 16:52:40 -0400572_iso-8859-1 Harry:
Can you please post few more info on your problem:
1. How are you passing the user-id / password to AIX? 2. Are you using SYSIBM.USERNAMES?
Thanks, Manas.
============================================= Hello, list...
We have an application that uses a DRDA connection between DB2 V6.1 on OS/390 and DB2 V7.1 on AIX.
The connection is established by a program running on the mainframe. The problem is that each cross-platform SQL call sends a user ID and password across the TCP/IP connection, in clear text. [...]
12103 188 62_Re: What are TCB and SRB times? - second try :-) Part Number 112_Moulder, Tom19_Tom_Moulder@BMC.COM31_Wed, 18 Jul 2001 16:03:00 -0500596_iso-8859-1 To understand these timings requires some understand of the MVS,OS/390,z/OS dispatcher. Perhaps this will help clarify why there are two timers and how they are different. The purpose of the dispatcher is to schedule and execute as many tasks on MVS as possible based on the performance priorities that are defined by the systems programmers and the processing resources that it has at its disposal. MVS has evolved over the years with many enhancements concerning how this is done, but the main goal is to get as many things done as possible. Now, for the way that it works, MVS [...]
12292 193 62_Re: What are TCB and SRB times? - second try :-) Part number 212_Moulder, Tom19_Tom_Moulder@BMC.COM31_Wed, 18 Jul 2001 16:03:38 -0500567_iso-8859-1 Continued from Part 1
I hope that this will be enough background, because now I want to go on to DB2 and why these are important to DB2 and those who try to tune DB2. A DB2 subsystem is composed of multiple address spaces within MVS for different functions that it has to perform. Normally, all of these address spaces are marked non-swappable so that whenever a task (whether TCB or SRB) associated with DB2 work needs to be executed that the right environment will exists and MVS will not have to swap in an address space in order to [...]
12486 22 44_How do you calculate DASD space utilization?20_Alvarez, Maximiliano20_MAXIMILIANO@IADB.ORG31_Wed, 18 Jul 2001 17:11:51 -0400446_iso-8859-1 Hi All,
I am trying to estimate the total DASD space allocated for our DB2 tables. The values I get from the db2 catalog don't reflect the actual DASD space utilized. It seems that a 4K page is not really mapped to 4K in DASD. So far I see two options to solve this problem: 1. use a VTOC utility or 2. calculate a ratio and use it in my queries. How are you approaching this problem? Is there any recommendation from IBM? [...]
12509 77 63_Binding problem when using a trigger to call a stored procedure16_Proctor, William25_william.proctor@TGSLC.ORG31_Wed, 18 Jul 2001 16:17:35 -0500335_iso-8859-1 I am trying to call a stored procedure from a trigger. I did a create procedure that was successful and I can create my trigger but when I put the call to the procedure in the trigger it gets a binding error. I could use some suggestions on this. Below is a copy of the procedure and the tigger and the error listing.. [...]
12587 47 48_Re: How do you calculate DASD space utilization?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 18 Jul 2001 17:27:32 -0400319_iso-8859-1 Max, 4k page really *is* 4K on DASD. However, it is a 4k VSAM CI not a 4K record so you actually use ony 48k of the approx 56k track. So the first fudge factor is that DASD space = (56664/(4096*12)) * (pages). Which catalog values are you using? Did you run STOSPACE on everything before getting these? [...]
12635 93 24_Re: DB2 TRIGGER - thanks11_Wang, Jason29_jason.wang@CHECKSOLUTIONS.COM31_Wed, 18 Jul 2001 16:47:38 -0500486_iso-8859-1 Thanks all who responded, especially Bela and James for the great help.
Jason
-----Original Message----- From: Hamar, Bela [mailto:Bela.Hamar@VARETIS.COM] Sent: Wednesday, July 18, 2001 5:34 AM To: DB2-L@RYCI.COM Subject: Re: DB2 TRIGGER
Hi Jason,
in your trigger, you have to check two cases: C1: whether any new row duplicates an exisiting row in the table C2: whether any two of the new rows are duplicates with each other [...]
12729 52 29_Improving Trigger Performance11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Wed, 18 Jul 2001 17:06:59 -0500347_iso-8859-1 Hi All,
We are in the process of testing triggers to be used as a AUDIT Mechanism. The manual says Triggers offer an efficient means of maintaining an audit trail.
What we are doing is creating a insert,delete, update trigger and each time saving the old row with a timestamp in a audit table. The trigger looks like [...]
12782 31 45_Re: Dataset closure and the performance issue15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 19 Jul 2001 09:57:38 +1000404_- Just thought I'd chip in, for what it's worth. Many moons ago (DB2 V3?) recording the fact a tablespace had changed states from open and updateable to closed and non-updateable cost DB2 a large number of cycles. The initial recommendation was to specify Close No because of the (initially) high overhead of maintaining the list of open/closed datasets. Does anyone remember the term 'slow close'? [...]
12814 50 48_Re: How do you calculate DASD space utilisation?12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 19 Jul 2001 10:05:26 +1000488_- Max, a while ago I posted a REXX that processes listcat info. A copy is in the lists document site (see http://jupiter.ryci.com/cgi/wa.exe?S1=db2-l-documents and search on "Cianci" as author).
To me the Hi-Used and Hi-Alloc RBAs are what defines space utilisation. I may allocate in Cylinders, but I always measure space in M-bytes or G-bytes. Anyway if you try to divide this up to cylinders from G-bytes, remember DB2 only uses 48K per track, so don't go dividing by 56K. [...]
12865 132 62_Re: What are TCB and SRB times? - second try :-) Part number 213_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 18 Jul 2001 17:24:20 -0700421_iso-8859-1 Tom, I'm sorry but I had a hard time following your posts. I feel I need to make a few additional points about SRBs.
SRBs are not new - they are a fundamental component of MVS. They are not an alternative to TCBs -- for instance, they cannot "own" storage areas (storage must be owned by a TCB). I may be a bit behind, but I'm not currently aware of a new type of task that only runs under an SRB. [...]
12998 30 20_DB2 Connect EE setup10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Wed, 18 Jul 2001 17:29:04 -0700313_iso-8859-1 We have DB2 Connect personal edition working and are trying to install the enterprise edition. We have the DB2 subsystems defined on the enterprise edition and have successfully tested the connection. How is the DB2 connect client on the PC supposed to be defined to enable it to use the gateway? [...]
13029 145 24_Re: DB2 Connect EE setup12_Troy Coleman19_Colematr@MEIJER.COM31_Wed, 18 Jul 2001 21:43:37 -0400341_US-ASCII Hi Vince, I don't have my notes with me and it's been awhile since I've setup DB2 Connect. However, I'll give it a try. First, In order for your client to see the known system you must have the Administration SERVER (DAS) installed. As for the port that DB2 listens on I think it defaults to 50000 the DAS listens on port 523. [...]
13175 111 26_Re: DB2 Connect V7 via SNA14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 18 Jul 2001 19:33:06 -0700391_iso-8859-1 Tom, Yes you can. The SNA stack doesn't come with DB2 Connect V7 so you need to provide one. Take a look at the Quick Beginnings for PE. I would suggest one of the SNA products for which the Quick Beginnings book has configuration documentation. Also, all the VTAM work will need to be done. Now, TCPIP is much easier and the way to go, but if you can't, you can't. HTH Phil [...]
13287 76 33_Re: SELECT COUNT(*) with CS or RR23_Puddy, Andrew (CALBRIS)36_Andrew.PUDDY@COMALCO.RIOTINTO.COM.AU31_Thu, 19 Jul 2001 03:10:24 -0000413_big5 From where I sit i think that you will have an integrity problem if the two transactions are allowed to run concurrently and you are able to determine that results are different with rr & cs.
my two bobs worth.
-----Original Message----- From: Li, Chak Lung Dominic [mailto:seddba@HK.SUPER.NET] Sent: Wednesday, 18 July 2001 11:31 To: DB2-L@RYCI.COM Subject: SELECT COUNT(*) with CS or RR [...]
13364 155 54_Re: Do I need backup indexspace of directory database?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 19 Jul 2001 06:14:00 +0100406_ISO-8859-1 Grace
a large subject but in short no you do not need to backup the indexspace of the directory.
what you are talking about in this DR is called and conditional restart of DB2 at the DR site. This is where you do not have the actives or BSDS etc so you create new ones and you create also a conditional restart record in the BSDS using JU3. You are correct on this front too. [...]
13520 30 29_partitioned tablespace in UDB25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Thu, 19 Jul 2001 13:24:50 +0800731_us-ascii Hi,
May I know how DB2 UDB implements the partitioned tablespace concept on UNIX platform. Is it similar to OS390 environment. Is there alternative ways to handle large amount of data.
Thanks
======================================================================= "Privileged and/or confidential information may be contained in this email or its attachments. If you are not the intended recipient, you must not disclose, copy or distribute the contents therein or take any action in reliance of such contents. Communication of any information in this email or its attachments to an unauthorised person is prohibited and may constitute an offence under the Official Secrets Act (Cap 213). [...]
13551 15 42_Missy J Case/FDR/FDC is out of the office.10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Thu, 19 Jul 2001 00:28:00 -0500389_us-ascii I will be out of the office starting 07/19/2001 and will not return until 07/30/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.
13567 45 9_DB2 error13_Abi Manoahran23_abi@PROTEOMESYSTEMS.COM31_Thu, 19 Jul 2001 01:14:24 -0500421_- Hi, I am trying to create a table with the folloing sql in Linux/Db2 7.1eee and AIX/DB2 7.1 eee. In linux it works fine but in AIX box it gave a error message.
SQL ---
CREATE TABLE aa.collection ( COLLECTION_ID bigint NOT NULL generated always as identity (start with 0, increment by 1), TREATMENT_CLASS varchar(20), COMMENT long varchar, PRIMARY KEY (COLLECTION_ID) ) ---------------------- [...]
13613 84 45_Re: Dataset closure and the performance issue0_18_mebert@AMADEUS.NET31_Thu, 19 Jul 2001 09:56:42 +0200455_us-ascii I seem to remember a discussion years ago that concluded that CLOSE NO was preferable, and that is the default here (I don't recall any details, and I did not research it). But, it was recently mentioned that CLOSE YES DSs are closed on a LRU basis while CLOSE NO ones are closed on a FIFO basis. In that case, having CLOSE YES as the default would make much more sense, in my opinion. Is it definite that these closing strategies are used? [...]
13698 66 39_Re: DBM1 CPU on SAP R/3, DB2 for OS/3900_18_mebert@AMADEUS.NET31_Thu, 19 Jul 2001 10:26:40 +0200565_us-ascii Maybe I should ask this in a different manner. We have a job that does a QUIESCE every 15 minutes of a dummy TS created solely for that purpose, to get timestamp/RBA pairs for PIT recovery. Should be over in a second, right? But rather often, this job sits for 15 minutes without doing anything (and occasionally even getting a Utility timeout). SDSF.DA shows the job as IN, 0% CPU. The same happens for Image copies, they often take 30 seconds to copy a one-track TS (or, in the case of incremental ICs, not copy them because nothing's changed). The [...]
13765 19 47_Diffrence in Priqty and Sec qty than allocation9_john king24_john_king@REDIFFMAIL.COM31_Thu, 19 Jul 2001 11:17:13 -0000337_- Hi All I allocated PRIQTY and SEC qty as 96 and 48 for all the partitioned indexes. After loading data and running a rebuilding index I got a report with PRIQTY as 92 and SEC QTY as 12. Can any body tell why is the diffrence. When one partition is full it should at least take PRIQTY as 96. I m eagerly waiting for the responses. [...]
13785 31 38_DB2 Monitoring on Windows N/T and 200011_Mohamed Beg19_mohamedb@ABSA.CO.ZA31_Thu, 19 Jul 2001 13:17:50 +0200339_iso-8859-1 Hi
With regards to DB2 Monitoring on the Windows NT and 2000 environments
DB2 supports SNMP with agents and subagents. The DB2 Troubleshooting Manual suggests using the System View SNMP agent on the Server together with the DB2 SNMP subagent. The system view agent then talks with the TNG or Tivoli Software. [...]
13817 68 45_Re: Dataset closure and the performance issue16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 19 Jul 2001 17:34:52 +0530404_- If i can recall the discussion sometimes 1 and half years back, we were told that from V5, CLOSE YES had become LRU and CLOSE NO was FIFO. V6 have changed both to LRU (my interpretation from manuals).
CLOSE YES will be considered first.
As far as SYSLGRNX is considered, it is updated when dataset is pseudo-closed. This updates the SYSLGRNX and closes its entry for the tablespace. [...]
13886 75 39_Re: Triggers & Stored Procedures DB2 V611_Hayden, Lee18_Lee_Hayden@CSX.COM31_Thu, 19 Jul 2001 09:05:43 -0400537_iso-8859-1 There is lots of good information on triggers and stored procedures in the DB2 Application Programming and SQL Guide. I have also posted a simple COBOL program to call the IBM supplied stored procedure DSNWZP. You can see this out on the DB2L Documents site.
http://jupiter.ryci.com/archives/db2-l-documents.html
Search for DSNWZP. This is handy to test and make sure basic stored procedures are working in the DB2 Stored Procedure address space. Basically what it does is list your current ZPARM settings. [...]
13962 130 33_Re: Improving Trigger Performance14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 19 Jul 2001 14:12:49 +0100353_iso-8859-1
I must admit, in my presentations about triggers I also suggest auditing as one possible use for after triggers
BUT
There are other ways.
DB2 is already keeping an audit trail for you - it's called the DB2 log. Therefore, you can use the log as an audit source with NO overhead to your running applications. [...]
14093 87 45_Re: Dataset closure and the performance issue0_18_mebert@AMADEUS.NET31_Thu, 19 Jul 2001 15:23:22 +0200382_us-ascii Well, I finally looked it up in the Admin Guide (p. 5-81) for V5 and V6, our hardcopy edition as well as the IBM DB2 for OS390 Support site (http://www-4.ibm.com/software/data/db2/os390/support.html). And in all cases it states that "the least recently used datasets" get closed first; but CLOSE NO datasets are only considered if & when no open CLOSE YES DSs remain. [...]
14181 127 75_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2 V6 OS/39010_Mark Labby16_mlabby@PHEAA.ORG31_Thu, 19 Jul 2001 09:25:57 -0400539_iso-8859-1 Troy,
We hit this about a year and a half ago while still running DB2v5 with the APAR to allow Partition Keys to be updated. When we checked with IBM at that time, they told us that the only way to fix it was to drop and recreate the table structure. Like you, our table involved is too large to play those games in production. We had hoped that they would fix the problem in the CATMAINT part of upgrading to DB2v6 and set the updateble columns to yes as part of the upgrade. Unfortunately, they didn't do that and [...]
14309 162 54_Re: Do I need backup indexspace of directory database?50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM31_Thu, 19 Jul 2001 09:38:33 -0400416_iso-8859-1
If you are backing up the cat/dir then at the D/R site you restore the cat/dir down and then do a rebuild/recover (either one) to build the
indexes.
You should be dual logging, send the second copy offsite for D/R.
Keep Archive1 for onsite problems.
As for the active logs. When you take a backup (ADR/DFDSS) you are getting the active logs at that point in time. [...]
14472 30 24_Re: DB2 Connect EE setup11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Thu, 19 Jul 2001 09:50:21 -0400578_us-ascii I pretty much gave up using the Client Configuration Assistant. It is very buggy (I have tested releases, 5.0, 5.2, 6.1, 7.1 and 7.2 with and without patches on Win95, Win98, NT 4.0 and Win 2000 with and without patched and service packs, you name it....). I use a set of BAT files that I developed as a result of having 22-27 Database Aliases cataloged for our clients, gateways, Appservers. I use the CCA simply to test connectivity and ODBC settings, I distribute my changes via bat files on a File Server or via the Database Administration Website I developed [...]
14503 199 62_Re: What are TCB and SRB times? - second try :-) Part number 212_Moulder, Tom19_Tom_Moulder@BMC.COM31_Thu, 19 Jul 2001 09:07:17 -0500594_iso-8859-1 SRBs are not new, but I remember when they were new, so perspective has a lot to do with how we express things. Yes there are several restrictions associated with SRBs. My point about global SRBs was they can be scheduled immediately because they do not have to be associated with any particular address space. Parallelism can be achieved in many ways and reduces elapsed time for processing everytime it is tried. However, parallelism is not always the best way to solve a performance problem. If the SQL is written poorly and requires retrieval of large amounts of data, then [...]
14703 62 74_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2V6 OS/39012_Troy Coleman19_Colematr@MEIJER.COM31_Thu, 19 Jul 2001 10:27:06 -0400316_US-ASCII Thank you Mark.
Troy Coleman Coleman Consulting, Inc.
IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 DB2 UDB V5 Database Administration IBM Certified Advanced Technical Expert DB2 -DRDA
Voice: (847) 722-2698 Email: troycci@colemanconsulting.com
14766 50 24_Re: DB2 Connect EE setup13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 19 Jul 2001 09:23:30 -0500593_US-ASCII Vince, You can find the port numbers assigned at the EE server in the services file. winnt\system32\drivers\etc The default DB2 instance port# on NT is 50000. If you still need assistance, drop me a note offline. HTH Kurt
>>> vlee@SDCCD.CC.CA.US 07/18/01 07:29PM >>> We have DB2 Connect personal edition working and are trying to install the enterprise edition. We have the DB2 subsystems defined on the enterprise edition and have successfully tested the connection. How is the DB2 connect client on the PC supposed to be defined to enable it to use the gateway? [...]
14817 153 75_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2 V6 OS/39011_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 19 Jul 2001 09:50:36 -0500303_iso-8859-1 If you don't want them to do it (since it's against your standards), then you can always set the ZPARM (PARTKEYU=NONE) so they can't do it anywhere. I think you can update the partitioning key if it will stay in the same partition without recreating the table if you have PARTKEYU=SAME. [...]
14971 128 75_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2 V6 OS/39014_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 19 Jul 2001 15:56:00 +0100425_iso-8859-1 It would be nice if someone from IBM (hint hint) could tell us whether the UPDATES=N in SYSCOLUMNS is the ONLY thing stopping a partitioning key update. If it was, then all you would have to do is change that N to a Y and you'd be away.....................
(PS I'm not recommending anything here, but if someone has some spare time on their hands and a hankering to play with the REPAIR utility......) [...]
15100 23 20_CA-Platinum products12_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Thu, 19 Jul 2001 11:16:06 -0400532_us-ascii Is anyone else out there having problems getting CA-Platinum DB2 tools upgrade tapes?
We have been requesting an upgrade for 4 months
Stan Goodwin IBM Senior I/T Specialist DECC MBG DB2 Support
Stanley_goodwin@mech.disa.mil DSN: 430-4335 Outside:- 717-605-4335
================================================ 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.
15124 180 74_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2V6 OS/39012_Troy Coleman19_Colematr@MEIJER.COM31_Thu, 19 Jul 2001 11:34:34 -0400439_US-ASCII It's been a few years, but BMC gave a presentation at an IDUG on how to change some ZPARM marcro and start DB2 in MAINT mode. With this macro change you could use SQL to update the catalog. However, I don't think it updated DSNDB01 only DSNDB06. This would help when you have a catalog error which I did have at a previous client. However, In that case we used the REPAIR to make DSNDB06 have the same definition as DSNDB01. [...]
15305 106 24_Query Parallelism DB2 V611_John Clarke23_johnwclarke@HOTMAIL.COM31_Thu, 19 Jul 2001 15:51:09 +0000506_- Hi Steve, DB2 Parallelism is an awesome process. Sysplex parallelism just blows me away. The major concern though that you had eluded to was CPU problems. When your machine is short on CPU resources then I suggest not using parallelism. Its not that parallelism uses significantly more CPU than normal, its just that it tries to use it a smaller period of time. This is when it may hurt your system. Example: Batch reporting job for a DWhse. No Parallelism Job A - Elapsed Time 10 hours, CPU 1 hour [...]
15412 34 24_Re: DB2 Connect EE setup11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Thu, 19 Jul 2001 11:55:53 -0400206_us-ascii Sorry for venting on CCA. Do a search on the C: drive for a file called SERVICES and edit it.
I will look like this:
db2cDB2 50000/tcp # DB2 TCP/IP port db2iDB2 50001/tcp # DB2 TCP/IP
15447 49 62_Re: What are TCB and SRB times? - second try :-) Part number 211_Jim Keohane19_jimkeo@LOCKSTAR.COM31_Thu, 19 Jul 2001 12:02:00 -0400567_us-ascii Tom,
You might like one of my favorite email signatures:
"A little inaccuracy sometimes saves a ton of explanation." - H. H. Munro (Saki)
- Jim
Moulder, Tom wrote:
> . . . > > My point in the post was to try and take someone who was admittedly > new to OS/390 and try to explain somethings about the underlying > operating system and not necessarily to be technically correct in all > of my explanation. I left out a discussion of "enclaves" because the > more you talk about this the more you wonder how MVS gets any [...]
15497 36 24_Re: DB2 Connect EE setup12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 19 Jul 2001 09:07:05 -0700550_us-ascii Why don't you use the IMPORT/EXPORT facility of CCA? It works well in passing on the configuration to other users. --- Jose' Gomez wrote: > I pretty much gave up using the Client Configuration Assistant. It > is > very buggy (I have tested releases, 5.0, 5.2, 6.1, 7.1 and 7.2 with > and > without patches on Win95, Win98, NT 4.0 and Win 2000 with and without > patched and service packs, you name it....). I use a set of BAT > files > that I developed as a result of having 22-27 Database Aliases > cataloged > [...]
15534 17 24_Re: CA-Platinum products26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Thu, 19 Jul 2001 11:15:26 -0500490_- None at all. Having problems putting them on, but not getting them. Also having a problem removing old products still popping LMP violations everytime somebody goes into QMF. Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu
================================================ 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.
15552 113 24_Re: CA-Platinum products16_Brookman, Gerald30_gerald.brookman@ATOSORIGIN.COM31_Thu, 19 Jul 2001 12:19:10 -0400313_windows-1252 This is easy to handle. Simply send an email to your CA company contact asking why the delay. Mention you have 1,000,000 shares of CA stock in your family trusts. Send an email copy to Sam Wyly c/o Ranger Governance, Dallas, TX. You should have your upgrade tape the next day. It worked for me. [...]
15666 100 24_Query Parallelism DB2 V611_John Clarke23_johnwclarke@HOTMAIL.COM31_Thu, 19 Jul 2001 16:43:41 +0000506_- Hi Steve, DB2 Parallelism is an awesome process. Sysplex parallelism just blows me away. The major concern though that you had eluded to was CPU problems. When your machine is short on CPU resources then I suggest not using parallelism. Its not that parallelism uses significantly more CPU than normal, its just that it tries to use it a smaller period of time. This is when it may hurt your system. Example: Batch reporting job for a DWhse. No Parallelism Job A - Elapsed Time 10 hours, CPU 1 hour [...]
15767 24 30_Viewing transactions from Logs10_Azam Mirza18_azam00@HOTMAIL.COM31_Thu, 19 Jul 2001 11:38:19 -0500494_- HI, If you all find this question pretty silly, just excuse me as I am new with DB2. This is my second week with DB2. I have been asked to find out if DB2 keeps track of all the transactions that happen on the database. If it does then, what type of info is stored ? Where I can find it ? and How useful is it ? Can any reports be generated from this info ? how can I view these transaction? The client wants to monitor all the transactions. If database doesn't do it, then, how to keep [...]
15792 43 24_Re: CA-Platinum products16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 19 Jul 2001 13:05:54 -0400462_iso-8859-1 We get our tapes promptly. Making them work is another issue :=(
Regards, eric pearson
-----Original Message----- From: Stan Goodwin [mailto:STANLEY_GOODWIN@MECH.DISA.MIL] Sent: Thursday, July 19, 2001 11:16 AM To: DB2-L@RYCI.COM Subject: CA-Platinum products
Is anyone else out there having problems getting CA-Platinum DB2 tools upgrade tapes?
We have been requesting an upgrade for 4 months [...]
15836 41 24_Re: DB2 Connect EE setup11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Thu, 19 Jul 2001 13:18:14 -0400415_us-ascii The Import/Export is OK especially if you're at the same release, and for small number of PC's using CCA. I have well over 350 PC's my method works pretty nice. I had a issues using db2cfimp.exe, after running the export option, I noticed that it used the same DATABASE LOCATION for a number aliases when each alias should have had its unique DATABASE LOCATION. I had to edit the SPF files by hand... [...]
15878 19 33_Kevin Davis is out of the office.11_Kevin Davis18_kevin.davis@DB.COM31_Thu, 19 Jul 2001 12:28:13 -0600139_us-ascii I will be out of the office from 07/19/2001 until 07/23/2001.
I will respond to your message when I return.
15898 48 38_RRSAF contention and Stored Procedures11_Rosie Porco17_rporco@UOTTAWA.CA31_Thu, 19 Jul 2001 13:34:40 -0400401_us-ascii Hello to all,
We are an OS/390, DB2 V6. Db2 Connect v7 fixpack 1 I seek your expertise in solving this.. A while back one of our programs created a Stored procedure ( using Stored procedure Builder) He is now attempting to rebuild it but is unsuccessful the following error results . 14.04.43 STC01808 DSNT376I % PLAN=DSNRRSAF WITH CORRELATION-ID=CORRELATION0 CONNECTION-ID=RRSAF [...]
15947 107 42_FW: Incorrect Output from tuning SQL Query13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 19 Jul 2001 10:50:17 -0500602_iso-8859-1 This didn't get through (no reply from DB2-L)....so I am sending again
Myron,
Aside from the incorrect result, which I cannot comment on, or correct unless I was able to reproduce it myself.....
Why is it that T7 is the problem? - Join does not provide a matchcols = 1? - Different clustering keys with respect to other tables (and data is not in CHASSIS_NUMBER order when joined)? Hence Sync I/O problems (death by random I/O). - T7 introduces the most rows into the table join? - How many rows in T7 with chargable_opt_ind ='y'? Are there many rows unnecessarily [...]
16055 62 42_Re: RRSAF contention and Stored Procedures13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 19 Jul 2001 14:13:48 -0500686_iso-8859-1 Rosie,
It looks like a VisualAge Java developement (ide.exe) thread, via DB2 Connect, is using (and locking) your package. But I'm sure you already know this much......
Dave -----Original Message----- From: Rosie Porco [mailto:rporco@UOTTAWA.CA] Sent: Thursday, July 19, 2001 12:35 PM To: DB2-L@RYCI.COM Subject: RRSAF contention and Stored Procedures
Hello to all,
We are an OS/390, DB2 V6. Db2 Connect v7 fixpack 1 I seek your expertise in solving this.. A while back one of our programs created a Stored procedure ( using Stored procedure Builder) He is now attempting to rebuild it but is unsuccessful the following error results [...]
16118 71 35_Re: Indirect reference to FREE page13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 19 Jul 2001 11:16:24 -0500377_iso-8859-1 Jennifer,
To accomodate the "enlarging of a row due to an UPDATE", I would recommend using PCTFREE rather than FREEPAGE. FREEPAGE can minimize FARINDREF, but can still increase NEARINDREF. NO indirect references are better than either near or far, and only increasing available space on the current page (PCTFREE) will minimize the need to move the row. [...]
16190 17 16_One Plan_table ?10_Myles Reed21_myles.reed@NSCORP.COM31_Thu, 19 Jul 2001 14:37:58 -0500557_- Has anyone tried using one Plan_table per Subsystem?
I would like to convert our shop to one Plan_table but I'm concerned about the Developers locking each other out when running Binds or Explains.
As it stands now there are Plan_tables everywhere.
Any ideas on how best to consolidate them ?
================================================ 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.
16208 92 34_Re: Viewing transactions from Logs14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Thu, 19 Jul 2001 14:43:31 -0500520_iso-8859-1 > I have been asked to find out if DB2 keeps track of all the transactions > that happen on the database. If it does then, what type of info > is stored ?
No, it does not, not at a transaction level, or any process level. And neither does any other DBMS. What DB2 does track, is updates, inserts, and deletes, in the forward recovery log. Data is kept for both rollbacks and roll forward recoveries. Mass deletes are only indicated as logging every single deleted row in a table would be a waste. [...]
16301 78 24_Re: DB2 Connect EE setup10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Thu, 19 Jul 2001 13:21:05 -0700449_iso-8859-1 Kurt,
I've verified and defined the port number on the client but am still getting the same error:
[IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "*", "*". SQLSTATE=08001 [...]
16380 24 20_Performance and LOBs17_Haynes, Michael A22_michael.haynes@NWA.COM31_Thu, 19 Jul 2001 15:29:16 -0500511_iso-8859-1 I am starting to evaluate CLOBs versus VARCHAR versus XMLVARCHAR.
Has anyone seen any documents that benchmark space utilization or performance.
Michael Haynes Data Services Northwest Airlines (612) 726-3338 Michael.Haynes@NWA.COM
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16405 30 24_Re: DB2 Connect EE setup11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Thu, 19 Jul 2001 16:57:49 -0400225_us-ascii WSAECONNREFUSED (10061): The connection has been refused. If you are trying to connect to the database, check that the database manager and TCP/IP protocol support at the server has been started successfully.
16436 48 20_Re: One Plan_table ?10_Shery Hepp17_schepp@SRPNET.COM31_Thu, 19 Jul 2001 14:21:09 -0700354_iso-8859-1 Myles- In our shop we have it set up with schema id.plan table
The developers bind their programs using a generic id (schema id) as the owner/qualifier. Our schema ids are defined per CICS region. The schema ids are not the same as the owner id for the application tables. We create synonyms to application tables for each schema. [...]
16485 46 24_Re: DB2 Connect EE setup13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 19 Jul 2001 16:35:51 -0500311_iso-8859-1 Vinson,
The database name on the PE client needs to be the database alias you want to use on the EE. 100061 would indicate either DB2 is down, the DB2 Connect server is down, or you do not have things configured properly yet (and are asking to connect to a target that doesn't exist...). [...]
16532 26 24_Re: DB2 Connect EE setup10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Thu, 19 Jul 2001 14:37:42 -0700452_iso-8859-1 How do I check this on an NT machine?
-----Original Message----- From: Jose' Gomez [mailto:Jose_Gomez@WENDYS.COM] Sent: Thursday, July 19, 2001 1:58 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect EE setup
WSAECONNREFUSED (10061): The connection has been refused. If you are trying to connect to the database, check that the database manager and TCP/IP protocol support at the server has been started successfully. [...]
16559 64 27_New users of Index Advisor.11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Thu, 19 Jul 2001 16:29:55 -0600577_iso-8859-1 Has anyone out there got significant experience with the use of the Index Advisor?
Our project has relatively unseasoned DBAs and developers and I understand that the Index Advisor is also a fairly new product/feature.
We've had some success with the use of index advisor, but are finding that the results vary so significantly based on a variety of factors (the set of existing indexes, specified index space, etc.) that we don't know which result to trust. Any suggestions as to a strategy for getting a good starting point out of this product? [...]
16624 18 33_HELP !! -- Encrypting Column data10_Azam Mirza18_azam00@HOTMAIL.COM31_Thu, 19 Jul 2001 17:51:59 -0500483_- Hi, Is there any way to encrypt the column data in a table ? I am trying to store passwords in a table and want to encrypt the data. I want to know how to decrypt it to do a 'select'.
Any help would be appreciated.
thanks aam
================================================ 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.
16643 28 39_Re: DBM1 CPU on SAP R/3, DB2 for OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 18:57:25 -0500353_- When you see the long waits, I'd tend to use -dis db(xxx) locks to see if something shows up there. Trying IO and lock traces for that specific job would be better, if the waiting is repeatable, and you'll run DB2 PM. The cpu times depend upon the work being done, so there is not much consistency in the ratio between one work load and another. [...]
16672 17 75_Re: Update Partition Key Column when SYSCOLUMNS UPDATES = 'N' DB2 V6 OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 19:07:38 -0500377_- The change to allow updates of partitioning keys was complex and difficult. We needed a number of attempts to get it this far. We have had multiple requests to avoid the need to rebuild our foundation before building upon it. There is no easy solution. Trying to work around the limit is far too likely to result in data integrity issues. Please don't try this at home. [...]
16690 15 19_Re: CLOBS on Os/39012_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 19:15:07 -0500540_- I don't see other appends so far. Have you read the DB2 V6 Technical Update (SG24-6108) section on LOB performance considerations? It's section 2.4, about 9 pages. The important recommendations include using LOB locators, using them for large (over 32K objects).
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16706 17 42_Re: Incorrect Output from tuning SQL Query12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 19:34:34 -0500444_- I suspect that the person you are working with is frustrated and busy working on this problem and many others. Please do not confuse inability to recreate the problem or understand what is going wrong in a complex scenario with not caring. If someone you are working with is giving you the impression that they do not care, please call service again and ask for the duty manager. I think the duty manager can straighten out most issues. [...]
16724 63 25_Re: Utility Compatibility12_Doug Clifton16_dclifton@EV1.NET31_Thu, 19 Jul 2001 19:54:45 -0500401_us-ascii Hi, If you wish to copy the same tablespace that you are reorging, the best thing to do is have reorg copy the tablespace for you. Roger Miller was correct, code a Copyddn parm. This is one way of reducing the number and overhead of utility executions. As soon as the reorg is complete you have an image copy that you can recover to. Feel free to contact me if you have other questions. [...]
16788 20 25_Re: FW: Reorg On SYSDBASE12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 19:45:08 -0500528_- It's nice to hear from you Joan. Thanks for the question. I need the feedback. I think you're right, too.
If you did the reorg after the catalog change, it would fill out the rows and produce fewer updates that expand the row. We don't have many updates, though. When we add new fields to the end of a table, they stay short rows until there is an update or reorg. So the problem is relatively small. The special clustering techniques we use tend to leave more free space and result in less fragmentation as well. [...]
16809 14 34_Re: DRDA Security -- OS/390 to AIX12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 19:53:21 -0500483_- One possibility is APAR PQ21525 for V5 & V6 and the corresponding change in DB2 Connect with a fixpack. Does that match what you want? The bigger item is our Kerberos support in DB2 for z/OS and OS/390 V7.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16824 106 39_Fwd: Re: Viewing transactions from Logs11_Dick Hacker19_dick@HACKERHOME.COM31_Thu, 19 Jul 2001 18:22:45 -0700583_- It seems like the information the questioner is looking for might be in the accounting trace records which are not so difficult or expensive to collect. Every thread will create an accounting record and while it doesn't contain a record of which DBs and TSs were touched it does have the plan, userid, cpu time used, elapsed time, counts of getpages, selects, updates, inserts, fetches, etc and more other data than you can hope to use. You need a process to collect (SMF works) and analyze the records in order to build meaningfull reports/graphs. This could be most of what [...]
16931 17 48_Re: How do you calculate DASD space utilization?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 20:30:29 -0500387_- There is a section in the V5 Installation Guide (I know - bad place) titled DASD Storage for User Data. In my dog eared V5 paper copy it's page 2-27. In V6 the section is in the Administration Guide, chapter 2-12 in dsnag0g3.pdf (about 8 pages). There are lots of different methods, measurements and complexity in disk storage. DB2 Estimator can make part of the job fairly easy. [...]
16949 15 33_Re: Improving Trigger Performance12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 19 Jul 2001 20:44:33 -0500571_- There are some useful notes on trigger performance in the V6 Technical Update, SG24-6108, section 2.3 Trigger performance considerations, starting on page 27. Page 38 on Understanding trigger performance summarizes the options. Reading the log as the audit trail may be the cheapest technique.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16965 49 22_Rollfordward conundrum11_Grant Allen22_grant@TOWERSOFT.COM.AU31_Fri, 20 Jul 2001 11:58:03 +1000559_- Hi all,
For some strange reason (I call it "masochism"), I'm playing around with some disaster recovery scenarios, and I think I've got a conceptual problem with rollforward (or at least some of the commands).
I've got a db for which I took a backup at a given time (on an NT box, but it could be anywhere). Did some heavy work on the db (lots of updates, alter tables, dropping indexes etc), and then intentionally screwed the data. I did a whole-db restore, and then rolled forward to a point in time I thought was "safe" (i.e. when no [...]
17015 71 22_Re: Insert next seq_no15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Fri, 20 Jul 2001 12:19:16 +1000644_- Hi Jason,
Would this also introduce the possibility of duplicates if two Insert transactions ran concurrently - how could you guard against that?
regards, Steve
> -----Original Message----- > From: Wang, Jason [SMTP:jason.wang@CHECKSOLUTIONS.COM] > Sent: Wednesday, July 18, 2001 6:50 AM > To: DB2-L@RYCI.COM > Subject: Re: Insert next seq_no > > creating a trigger as follows will do the trick. > > > CREATE TRIGGER incrementKeyno > NO CASCADE BEFORE INSERT ON yourtablename > REFERENCING NEW AS newrow > FOR EACH ROW MODE DB2SQL > set newrow.seq_no = 1 + (select max(seq_no) from yourtablename); > ; > > > [...]
17087 47 26_Re: Rollfordward conundrum18_Gert van der Kooij15_geko@WANADOO.NL31_Fri, 20 Jul 2001 08:28:44 +0200605_iso-8859-1 ----- Original Message ----- From: "Grant Allen" Newsgroups: bit.listserv.db2-l To: Sent: Friday, July 20, 2001 3:58 AM Subject: Rollfordward conundrum
> Hi all, > > For some strange reason (I call it "masochism"), I'm playing around with > some disaster recovery scenarios, and I think I've got a conceptual problem > with rollforward (or at least some of the commands). > > I've got a db for which I took a backup at a given time (on an NT box, but > it could be anywhere). Did some heavy work on the db (lots of updates, > alter [...]
17135 49 34_AW: DRDA Security -- OS/390 to AIX12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 20 Jul 2001 08:40:57 +0200441_iso-8859-1 Roger,
never heard about Kerberos. What is it ?
With kind regards - mit freundlichen Grüssen, Georg H. Peter ---------------------------------------------------------------------- Twenty years from now you will be more disappointed by the things you didn't do than by the ones you did. So throw off the bowlines, sail away from the safe harbor, catch the trade winds in your sails. Explore. Dream. (Mark Twain) [...]
17185 15 63_Rick L. Sauerzopf/Household International is out of the office.0_25_rlsauerzopf@HOUSEHOLD.COM31_Fri, 20 Jul 2001 01:36:46 -0500389_us-ascii I will be out of the office starting 07/20/2001 and will not return until 07/23/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.
17201 56 24_AW: CA-Platinum products12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Fri, 20 Jul 2001 09:07:28 +0200337_iso-8859-1 Hi Stan,
we had no problem to get the tapes. But there has been a delay in delivering the 99E tapes, because it didn't pass internal quality control (it was announced for May 2001). Meanwhile it is shipped and 99F is announced. Talk to your CA/Platinum rep to see what's going on there or open an issue (StarTCC). [...]
17258 139 37_Re: HELP !! -- Encrypting Column data14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 20 Jul 2001 09:54:32 +0100503_iso-8859-1 On first glance, the answer seems obvious - use a FIELDPROC.
This will allow you to encrypt the data but of course ANY access (SELECT, UPDATE etc) will automatically decrypt it again, which is hardly what you want.
A better solution, assuming you are at least OS390 DB2 V6, would be a User Defined Function(s) to encrypt and decrypt the data. This way, unless you know what function to use and unless you have the authority to call it, the data will always look encrypted. [...]
17398 34 46_Is list prefetch dynamic, static or sth else ?15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL31_Fri, 20 Jul 2001 11:41:49 +0200560_us-ascii Halo Listers,
After sorting RID-s for list prefetch result can either form a sequence or not.
For example RID-s: 1,2,3,4,5,6,7,8,9 and 10000,10001,10002,10003,10004,10005 form two sequences which DB2 can handle in two I/Os
but RID-s: 1, 10000, 20000, 30000, 40000, 50000 even if sorted does not form sequence sutable for single I/O.
The question is what DB2 does in each of these cases? Does it switch from sync/random to async/prefetch mode depending on which is better or does it use prefetch even for single page ? [...]
17433 91 13_DB2 Vacancies12_Stuart Moore25_stuart.moore@TRITON.CO.UK31_Fri, 20 Jul 2001 12:19:13 +0100534_us-ascii > Triton Consulting currently has vacancies for experienced DBAs with a > minimum of two years DB2 experience on any platform (e.g. OS/390, > Windows, Unix, etc.) > > Rates are excellent. > > If you are interested in these positions with the UK's leading DB2 > Consultancy, please email a current CV and details of availability to > jobs@triton.co.uk, or register your interest via our website: > www.triton.co.uk > > You can also apply by post to: > > Triton Consulting > The Royal > 25 Bank Plain > Norwich NR2 4SF. > > > >
17525 20 50_Re: Is list prefetch dynamic, static or sth else ?15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 20 Jul 2001 06:30:49 -0500400_- Piotr
> Does it switch from sync/random to async/prefetch mode depending on which > is better or does it use prefetch even for single page ?
As far as I know, list prefetch will only be done for pages which have a distance not more than 150 pages. But what should it mean: prefetch for one page?. Do you think, which queue the page is put in: the random or the sequential queue. [...]
17546 28 19_Re: CLOBS on Os/39014_Craig McKellar26_craig.mckellar@BIGPOND.COM31_Fri, 20 Jul 2001 21:49:22 +1000356_us-ascii Thanks your reply Roger. I have looked at that chapter and also Richard and Susans recent book. The theory is very useful but I guess I am looking for some site experiences. I am interested in any numbers others may have like additional cpu required, wether dataspaces were used for buffer pools. Some feel good stuff. regards Craig McKellar [...]
17575 34 24_Re: Performance and LOBs14_Craig McKellar26_craig.mckellar@BIGPOND.COM31_Fri, 20 Jul 2001 21:54:47 +1000686_us-ascii Michael Sorry I cannot offer you anything at the moment but I believe I am in the same position as you and also would be interested in similar information.
regards Craig McKellar
"Haynes, Michael A" wrote:
> I am starting to evaluate CLOBs versus VARCHAR versus XMLVARCHAR. > > Has anyone seen any documents that benchmark space utilization or > performance. > > Michael Haynes > Data Services > Northwest Airlines > (612) 726-3338 > Michael.Haynes@NWA.COM > > ================================================ > To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the [...]
17610 72 50_Re: Is list prefetch dynamic, static or sth else ?13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Fri, 20 Jul 2001 04:52:47 -0700350_us-ascii Piotr,
There is a difference between sequential prefetch and list prefetch. Both get 32 pages per I/O (assuming that the bufferpool is defined with at least 1000 pages). Sequential prefech, whether dynamic or predetermined at bind time, will retrieve contiguous pages. With list prefetch, the pages do not have to be contiguous. [...]
17683 54 19_Re: CLOBS on Os/39017_Haynes, Michael A22_michael.haynes@NWA.COM31_Fri, 20 Jul 2001 07:40:43 -0500580_iso-8859-1 Thanks Roger, This was helpful!
-----Original Message----- From: Craig McKellar [mailto:craig.mckellar@BIGPOND.COM] Sent: Friday, July 20, 2001 6:49 AM To: DB2-L@RYCI.COM Subject: Re: CLOBS on Os/390
Thanks your reply Roger. I have looked at that chapter and also Richard and Susans recent book. The theory is very useful but I guess I am looking for some site experiences. I am interested in any numbers others may have like additional cpu required, wether dataspaces were used for buffer pools. Some feel good stuff. regards Craig McKellar [...]
17738 140 46_Re: FW: Incorrect Output from tuning SQL Query12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 20 Jul 2001 05:47:03 -0700397_us-ascii Terry, Thanks for the questions:
I'll try to answer your questions: 1) SELECT Counts are just for testing query 2)Why is T7 problem - My guesses: a) adds the most rows; b)count(chargable_opt_ind ='y') = 1.3million out of 1.8 million c) matchcol = 1 I took T7 out of join and made join result of rest in temp table and joined it there - result took only 10 minutes to process. [...]
17879 21 49_Looking for UDB DBA position on Unix or WindowsNT6_Ravi P22_ravidb2dba@HOTMAIL.COM31_Fri, 20 Jul 2001 08:03:02 -0500529_- Hi,
This is Ravi.I am looking for UDB DBA position on Unix or WindowsNT.Anyone Knows vacancies please help me.
Thanks, Ravi
_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
17901 49 26_Limiting values in Columns10_Joan Green22_Joan.Green@BOIMAIL.COM31_Fri, 20 Jul 2001 14:16:06 +0100569_us-ascii Hi Listers,
O/S 390 V6 is my flavour.
I'm pretty sure that this question has been asked before, but I'm probably looking for the wrong thing in the archives and simply can't find any references.
In Oracle (sorry to drag that one in) you can define what I think they call 'domains', which are lists or ranges of valid values for the column. Does anyone know of an easy way to implement this in DB2, I know referential constraints will do some of this for me, but I don't necessarily want to enter all possible values onto a data table [...]
17951 20 39_Upper case of DB2 table data in DB2 V5.0_24_ujjwal@LOT.TATASTEEL.COM31_Fri, 20 Jul 2001 18:59:04 +0530499_us-ascii ALL, I have a case where I got data from Lotus Notes into DB2 thru Lotus Pump .Now most of the data pumped into DB2 has lowercase .I have tried to Unload the data and convert all the data to uppercase in the sequential file and then Load it again.It worked for many tables but for some it did not work .It was giving a message Data Conversion error for a particular numeric field. Could anyone tell me any other way of doing this ? I am at present in DB2 V5R1. Thanks in Anticipation. [...]
17972 13 30_Re: Limiting values in Columns15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 20 Jul 2001 08:25:08 -0500405_- Joan
You can use column check constraints where you can define, which values are allowed for that column. I don't know if that is what you are asking for.
================================================ 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.
17986 14 29_Timeout or deadlock on insert15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 20 Jul 2001 08:27:26 -0500419_- Hi list
We are just discussing whether or not it is possible you get a deadlock or a timeout when I insert a row (table with page locking, no trigger or RI is involved)
================================================ 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.
18001 116 31_Re: New users of Index Advisor.12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US31_Fri, 20 Jul 2001 08:32:49 -0500530_US-ASCII We run UDB 6.1 and 7.1 EE on AIX. Programmers use Forte. A programmer helped us extract ALL the SQL within each Forte project and then we wrote a PERL program to execute each SQL statement with the necessary EXPLAIN info in front of it. Any SQL that had a total cost > a number we set has further processing performed on it. First, we run db2exfmt in graph mode to display the access path and 2nd we run db2advis to suggest indices. Say a Forte project has 900 SQL statements, we can run them thru and only the poor [...]
18118 70 30_Re: Limiting values in Columns16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 20 Jul 2001 09:45:15 -0400423_iso-8859-1 Joan, I think the DB2 V7 'check constraint' feature does what you want. I do not know if it is available in V6.
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Joan Green [mailto:Joan.Green@BOIMAIL.COM] Sent: Friday, July 20, 2001 9:16 AM To: DB2-L@RYCI.COM Subject: Limiting values in Columns
Hi Listers,
O/S 390 V6 is my flavour. [...]
18189 37 50_Re: Is list prefetch dynamic, static or sth else ?10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 20 Jul 2001 15:46:28 +0200368_us-ascii I'm not sure I understood your mail, but after RID sort you have PAGES sorted into ascending physical target page
number sequence, ie following ascendig disk physical addresses, not RIDs. The sequence of pages on disk which
may differ from an ascending RID sequence. In other words list prefetch is 'skip sequential' while the async engine [...]
18227 77 30_Re: Limiting values in Columns0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Fri, 20 Jul 2001 09:47:40 -0400666_us-ascii Joan,
Have you looked at check constraints on columns? Look at the CREATE TABLE syntax.
R/..Ed
Joan Green @RYCI.COM> on 07/20/2001 09:16:06 AM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc:
Subject: Limiting values in Columns
Hi Listers,
O/S 390 V6 is my flavour.
I'm pretty sure that this question has been asked before, but I'm probably looking for the wrong thing in the archives and simply can't find any references. [...]
18305 37 33_Re: Timeout or deadlock on insert16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 20 Jul 2001 09:47:01 -0400412_iso-8859-1 If you do not COMMIT soon enough, other tasks will be exposed to timeouts if they need to access (even read-only) that page.
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Walter Janissen [mailto:walter.janissen@VICTORIA.DE] Sent: Friday, July 20, 2001 9:27 AM To: DB2-L@RYCI.COM Subject: Timeout or deadlock on insert
Hi list [...]
18343 89 22_Re: Insert next seq_no11_Wang, Jason29_jason.wang@CHECKSOLUTIONS.COM31_Fri, 20 Jul 2001 09:06:27 -0500473_iso-8859-1 You are right. My oversight, Phil Grainger also pointed out the problem.
regards, Jason
-----Original Message----- From: Mallett, Steven [mailto:Steven.Mallett@TEAM.TELSTRA.COM] Sent: Thursday, July 19, 2001 9:19 PM To: DB2-L@RYCI.COM Subject: Re: Insert next seq_no
Hi Jason,
Would this also introduce the possibility of duplicates if two Insert transactions ran concurrently - how could you guard against that? [...]
18433 70 30_Re: Limiting values in Columns11_David Nance16_DWNance@FHSC.COM31_Fri, 20 Jul 2001 10:05:53 -0400514_US-ASCII Joan, Check the Administration Guide 2.3.2 Defining table check constraints
Example: CREATE TABLE EMPSAL (ID INTEGER NOT NULL, SALARY INTEGER CHECK (SALARY >= 15000));
Dave Nance First Health Services, Corp. (804)527-6841
>>> Joan.Green@BOIMAIL.COM 07/20/01 09:16AM >>> Hi Listers,
O/S 390 V6 is my flavour.
I'm pretty sure that this question has been asked before, but I'm probably looking for the wrong thing in the archives and simply can't find any references. [...]
18504 135 25_Re: Utility Compatibility0_19_Tim.Lowe@STPAUL.COM31_Fri, 20 Jul 2001 09:08:04 -0500641_us-ascii Bill, Your original question specified a reorg with unload only, and (per Roger's suggestion) I tried a reorg unload only with a copyddn parm, and it just ignores it. (which makes some sense considering that the copy would normally be made during the reload phase.)
And, when I attempt to use "shrlevel reference" with "unload only", I get the following error: DSNU070I DSNUGDFL - KEYWORD OR OPERAND 'UNLOAD ONLY' INVALID WITH 'SHRLEVEL REFERENCE' (I get a similar message when using "shrlevel change" and "unload only". And, using either shrlevel change or reference with "unload external".) And, it is documented that [...]
18640 101 22_Re: Insert next seq_no0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 20 Jul 2001 09:32:37 -0500389_us-ascii It seems likely that you would have a unique index on the sequence number, so the second insert would fail with an SQLCODE -803. You would just need code in place to retry the insert (and therefore refire the trigger) to get a new sequence number.
"Mallett, Steven" @RYCI.COM> on 2001/07/19 09:19:16 PM [...]
18742 15 33_Re: Timeout or deadlock on insert15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 20 Jul 2001 09:33:08 -0500498_- Eric
Thank you for your reply. But what I am interested in is, if the inserter can time out, not any other reading a page (That's completely clear to me, that no other can read a page, where a row was inserted into, until the insert is committed.)
================================================ 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.
18758 26 36_LOCKS ACQUIRED DURING MODIFY UTILITY16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Fri, 20 Jul 2001 07:33:42 -0700601_us-ascii Could anyone shed some light regarding the lock acquired against user tablespaces during the execution of the MODIFY utility? We had an instance recently of a MODIFY which ran for 19 minutes, and resulted in many online transactions timing out due to not being able to acquire a drain lock against the resource. Normally, the modify against the tablespace in question executes in a matter of seconds, but it appears as though it was elongated due to global lock waits within a 5 way datasharing environment. According to the utility manual the object of MODIFY is placed in UTRW status, [...]
18785 57 33_Re: Timeout or deadlock on insert0_19_Tim.Lowe@STPAUL.COM31_Fri, 20 Jul 2001 09:45:54 -0500372_us-ascii Walter, An insert can timeout(or deadlock) if it is inserting a duplicate key value that has not yet been committed. (it has to wait to see if the other transaction does a rollback, and therefore this insert could succeed)
We have discussed this on this list a few times in the past, if you want more information you might want to check the archives. [...]
18843 42 33_Re: Timeout or deadlock on insert16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 20 Jul 2001 10:45:03 -0400409_iso-8859-1 Walter, Inserter can time out if locks are held by others - even if others are read-only. Insert needs an X lock on the page. If any other task has an S or U lock on that page, the insert must wait. If it waits longer than the time specified in DSNZPARM value for IRLMRWT, the insert task times out. It is fairly common, since all too many programmers refuse to put COMMIT in read-only tasks. [...]
18886 13 22_DB2 Connect Encryption12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 20 Jul 2001 09:49:00 -0400350_- Is it correct that DB2 Connect V8.1, due out next year?, has builtin encryption?
Thanks in advance.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
18900 44 33_Re: Timeout or deadlock on insert12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Fri, 20 Jul 2001 09:54:45 -0500566_iso-8859-1 Walter
I have seen cases where a single thread in DB2 -- the ONLY thread executing in a DB2 subsystem and btw not in a data sharing sysplex group -- deadlocked. I was confused and did not think that it could occur. What actually happened was the thread did not commit, eventually enough locks were taken that the IRLM storage became full and there was not enough storage for the thread to obtain another lock. At that point the thread was waiting for another thread to release a lock and there was no other thread to comply. The single thread [...]
18945 209 30_Re: Limiting values in Columns12_Aedan Molony20_Aedan.Molony@PSIR.IE31_Fri, 20 Jul 2001 15:59:09 +0100427_windows-1252 TCC's came in w V4...
-----Original Message----- From: Joan Green [mailto:Joan.Green@BOIMAIL.COM] Sent: 20 July 2001 14:16 To: DB2-L@RYCI.COM Subject: Limiting values in Columns
Hi Listers,
O/S 390 V6 is my flavour.
I'm pretty sure that this question has been asked before, but I'm probably looking for the wrong thing in the archives and simply can't find any references. [...]
19155 54 33_Re: Timeout or deadlock on insert9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Fri, 20 Jul 2001 15:57:19 +0100460_ISO-8859-1 Hi,
You would think not, as DB2 just goes to another page to perform the insert.
However, if there is a unique index on the table , then I believe you can timeout when trying to ensure there are no duplicates.
Comments anyone.
Jim.
-----Original Message----- From: Walter Janissen [mailto:walter.janissen@VICTORIA.DE] Sent: 20 July 2001 14:27 To: DB2-L@RYCI.COM Subject: Timeout or deadlock on insert [...]
19210 112 30_Re: Limiting values in Columns14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 20 Jul 2001 10:02:02 -0500558_iso-8859-1 Joan,
Not in disagreement w/ any of the other responses, but someone will still have to 'enter all the valid values' somewhere.
One benefit of a Relational constraint is that most everyone can easily determine what those values are and if you have a new one or one that no longer applies, all you have to do is add a row or delete one. For the check constraint, I think the DBA would have to alter the table to drop the check constraint and alter the table again to add the constraint back w/ the diifferent set of values (I'll [...]
19323 37 60_DB2 UDB Windows/AIX Denial of Service vulnerability question14_Marshall Fuqua19_marcfuqua@YAHOO.COM31_Fri, 20 Jul 2001 08:09:37 -0700344_us-ascii A co-worker recently forwarded a link to an article that describes a Denial of Service vulnerability for DB2 UDB. In summary, the vulnerability exists when a Telnet session is established to the ports that db2ccs.exe & db2jds.exe run on (typically ports 6790 & 6789) & sending 1 byte of information. The result is a server crash. [...]
19361 28 33_Re: Timeout or deadlock on insert13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 20 Jul 2001 11:48:23 -0400429_iso-8859-1 Hi Eric, I may be wrong, but it sounds like in your response to Walter, you're forgetting that if the desired page for an insert is locked, DB2 will look for a nearby (ok, the algorithm is more complicated than that) page into which to insert.
That's why Walter's question is more complicated. I believe the cases cited by Tim and Tom provide real scenarios where you might see timeout/deadlock on insert. [...]
19390 77 42_Re: RRSAF contention and Stored Procedures10_Ben Reches33_Benjamin.Reches@MORGANSTANLEY.COM31_Fri, 20 Jul 2001 11:55:41 -0400514_us-ascii I have had the same problem, however sporadically. (i have sysadm)
In my case when I tried to REBUILD a sproc from SPB the GUI would just sit there spinning its gears until it got a deadlock timeout message from os390. (about 3 minutes time). In the sysprint of the WLM that ran the REBUILD I saw that the rebuild worked fine. In the MSTR log, I saw that the gui was deadlocking onto itself. A mainview trace showed that 2 connections were being initiated by the gui on behalf of the rebuild. [...]
19468 42 33_Re: Timeout or deadlock on insert10_Shery Hepp17_schepp@SRPNET.COM31_Fri, 20 Jul 2001 08:58:07 -0700331_iso-8859-1 Hi Walter- we have a process that does occasionally encounters a -911 on an insert. Activity against this table is insert only- the type of resource that's unavailable is the table space RID.
Here's a snip of the error message-
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED [...]
19511 16 37_Re: HELP !! -- Encrypting Column data12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 20 Jul 2001 10:46:00 -0500369_- If you have the crypto hardware and software, ICRF and ICSF, then that's probably what you want to use. If you don't mind changing the order of the fields and making range predicates (between, <, ...) useless, then the FIELDPROC is the one to use. Otherwise the choice is to use an EDITPROC and avoid indexing that column. I have an example EDITPROC using ICSF. [...]
19528 50 24_Catalog Database Problem14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 20 Jul 2001 09:02:31 -0700391_- Vince, The quickest way to get around this is to use the Quick beginnings guide for EE, it has many samples on how to catalog databases. On the client
you should be able to use the CCA, specify OS/390 type connection, and in the make sure you select TCPIP, host database, and enter the location name under the database tab. This is the location in the BSDS of the subsystem of [...]
19579 83 33_Re: Timeout or deadlock on insert13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 20 Jul 2001 10:30:26 -0500588_iso-8859-1 Walter,
There is another possibility which has not been discussed regarding deadlocks.
Consider the following example:
KEY A RID 201 pseudo-deleted (and committed) KEY B RID 401 pseudo-deleted (and committed)
Transaction 1 Insert KEY A into RID 401 will latch index key for pseudo-deleted A, X-lock on RID 401 (for insert) but also checks RID 201 to see it's commited status (since index does not indicate whether pseudo-delete is committed). Insert KEY B into RID 201 does the opposite (concurrently), and hence there is a deadlock situation. [...]
19663 148 22_Re: Insert next seq_no15_Jackson Reavill18_damcon2@US.IBM.COM31_Fri, 20 Jul 2001 13:08:01 -0400283_us-ascii I agree and in your case the unique index would be on colA asc and seq_no desc. In addition the trigger would look something like the following in order to get the next number within colA and to handle the first insert where there isn't an existing value for colA yet. [...]
19812 102 64_Re: DB2 UDB Windows/AIX Denial of Service vulnerability question14_MIKE FRIEDRICH27_RDBA002@REVENUE.STATE.IL.US31_Fri, 20 Jul 2001 12:03:00 -0500629_US-ASCII I received the same information from my Web security guru. I found the following on IBMLINK last Friday.
APAR: Item IY19086
APAR Identifier ...... IY19086 Last Changed..01/06/07 TELNET TO JDBC DAEMON (DB2JD(S)) TRAPS AFTER KEYING 10TH CHARAC TER
Symptom ...... AB ABEND Status ........... OPEN Severity ................... 1 Date Closed ......... Component .......... 5648B9700 Duplicate of ........ Reported Release ......... 610 Fixed Release ............ Component Name DB2 UDB AIX Special Notice Current Target Date ..01/08/06 Flags SCP ................... AIXRSC Platform ............ AIX [...]
19915 43 30_Viewing transactions from Logs7_Ale Eba14_Ale_Eba@CPR.CA31_Fri, 20 Jul 2001 13:32:21 -0400456_us-ascii Hello Azam,
Some information you can get from the transaction manager log such as IMS or CICS. For example, if you are using IMS transaction manager to access DB2, IMS log will have such information as input message, output message. These messages have transaction codes as well.
Ale CPR, Toronto
Date: Thu, 19 Jul 2001 11:38:19 -0500 From: Azam Mirza Subject: Viewing transactions from Logs [...]
19959 129 37_Re: HELP !! -- Encrypting Column data14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 20 Jul 2001 18:46:20 +0100473_iso-8859-1 Roger,
As I mentioned, the only problem with using one of the 'PROCs to encrypt data is that it is automatically decrypted whenever it's accessed by SQL. So any SELECT from SPUFI, QMF etc would see the decrypted row.
That's why I suggested a UDF to which access could be controlled
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...]
20089 201 22_Re: Insert next seq_no0_19_Tim.Lowe@STPAUL.COM31_Fri, 20 Jul 2001 13:29:11 -0500632_us-ascii Jay, I am just getting into triggers, and I thought I could use your example. But, when I try it (substituting my table and column names), I get an error: DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS VALUE, Can you tell me what I am doing wrong? And, how I go about debugging it? And, why is sysibm.sysdummy1 needed, why doesn't the following work either? CREATE TRIGGER incrementKeyno NO CASCADE BEFORE INSERT ON yourtablename REFERENCING NEW AS newrow FOR EACH [...]
20291 29 3_SPL0_24_db46@DAIMLERCHRYSLER.COM31_Fri, 20 Jul 2001 14:30:43 -0400726_us-ascii Can anyone tell me a good site for getting documentation and/or examples of using UDB's version 7 SPL (sequel procedural language).
Dean
****************************************************************************
The information contained in this transmission, which may be confidential and proprietary, is only for the intended recipients. Unauthorized use is strictly prohibited. If you receive this transmission in error, please notify me immediately by telephone or electronic mail and confirm that you deleted this transmission and the reply from your electronic mail system. **************************************************************************** [...]
20321 35 21_System & HP Hit ratio6_Chenny39_solaiyappan_chenniappan@MAIL.AMSINC.COM31_Fri, 20 Jul 2001 13:34:29 -0500557_- Please be patient with me if these question were already answered As we all know,widely used expressions for calculating the System and Hiperpool hit ratio are
System Hit Ratio = ((GP_req-(Async_page_read+Sync_io))/GP_req)*100 Hiperpool Hit Ratio = (HP_Read/(GP_req-(Async_page_read+Sync_io)))*100
Upon simplifying the above expression,
System Hit Ratio = (BP_reads/GP_request)*100 Hiperpool Hit Ratio = (HP_reads/BP_reads)*100
My first question, is it safe to assume that BP reads = pages found in BP + pages found in HP [...]
20357 74 37_Re: HELP !! -- Encrypting Column data10_Azam Mirza18_azam00@HOTMAIL.COM31_Fri, 20 Jul 2001 14:01:06 -0500285_- Phil, I am pretty new with DB2 ... so excuse me if you find these questions simple. How does FieldProc work ? At present the database is on NT and later we will move it to a os390. We are using version 7.1
Any help would be appreciated. Thanking you in anticipation Azam [...]
20432 44 37_Re: HELP !! -- Encrypting Column data10_Azam Mirza18_azam00@HOTMAIL.COM31_Fri, 20 Jul 2001 14:03:16 -0500286_- Roger, I am pretty new with DB2 ... so excuse me if you find these questions simple. How does FieldProc work ? At present the database is on NT and later we will move it to a os390. We are using version 7.1
Any help would be appreciated. Thanking you in anticipation Azam [...]
20477 44 37_Re: HELP !! -- Encrypting Column data10_Azam Mirza18_azam00@HOTMAIL.COM31_Fri, 20 Jul 2001 14:03:30 -0500285_- Roger, I am pretty new with DB2 ... so excuse me if you find these questions simple. How does EditProc work ? At present the database is on NT and later we will move it to a os390. We are using version 7.1
Any help would be appreciated. Thanking you in anticipation Azam [...]
20522 184 25_Re: Utility Compatibility15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 20 Jul 2001 14:53:58 -0500773_us-ascii try using sample online reorg utility card
REORG TABLESPACE DB.TS1 LOG NO SHRLEVEL CHANGE MAPPINGTABLE MAPPING_TABLE TIMEOUT TERM DRAIN ALL SORTDATA NOSYSREC SORTKEYS COPYDDN(SYSCOPA,SYSCOPB) UNLDDN(SYSPRT1) UNLOAD CONTINUE STATISTICS TABLE(ALL) INDEX(ALL)
Regards Nayeem
|--------+-----------------------> | | Tim.Lowe@STPA| | | UL.COM | | | | | | 07/20/01 | | | 09:08 AM | | | Please | | | respond to | | | DB2 Data Base| | | Discussion | | | List | | | | |--------+-----------------------> >--------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Mohammed Nayeem/MoMedicaid/US) | | Subject: Re: Utility Compatibility | >--------------------------------------------------------| [...]
20707 18 44_HELP !!! -- SQL stored procedures on ver 6.110_Azam Mirza18_azam00@HOTMAIL.COM31_Fri, 20 Jul 2001 14:56:05 -0500303_- Hi, I would like to know if SQL stored procedures can be created on DB2 UDB ver6.1 . I installed ver6.1 on NT. THe Stored procedure builder doesn't give me options to create stored procedures in SQL. THe only option I get is Java. Is there a way around it ? or I am doing doing something wrong ? [...]
20726 129 22_Re: Insert next seq_no15_Jackson Reavill18_damcon2@US.IBM.COM31_Fri, 20 Jul 2001 16:04:25 -0400522_us-ascii Tim,
Thank you for simplifying it... sysdummy1 is not needed. I thru it together quickly and made it more complicated than it needed to be. As for the syntax error, I believe what is needed is parenthesis wrapped around the select clause. Like the following:
CREATE TRIGGER incrementKeyno NO CASCADE BEFORE INSERT ON yourtablename REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL set newrow.seq_no = (select value(max(seq_no),0)+1 from yourtablename where yourtablename.colA = newrow.colA) ; [...]
20856 220 25_Re: Utility Compatibility0_19_Tim.Lowe@STPAUL.COM31_Fri, 20 Jul 2001 15:20:01 -0500413_us-ascii Nayeem, Since the sample online reorg is not an "unload only" reorg, then I don't see how it applies in this case.
Thanks, Tim
Mohammed Nayeem cc: Sent by: DB2 Data Subject: Re: Utility Compatibility Base Discussion List
07/20/2001 02:53 PM Please respond to DB2 Data Base Discussion List [...]
21077 36 48_Re: HELP !!! -- SQL stored procedures on ver 6.111_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Fri, 20 Jul 2001 15:22:58 -0500596_iso-8859-1 Azam SQL Stored Procedures is only supported on ver db2 7.1 (NT).
Thanks.
-----Original Message----- From: Azam Mirza [mailto:azam00@HOTMAIL.COM] Sent: Friday, July 20, 2001 2:56 PM To: DB2-L@RYCI.COM Subject: HELP !!! -- SQL stored procedures on ver 6.1
Hi, I would like to know if SQL stored procedures can be created on DB2 UDB ver6.1 . I installed ver6.1 on NT. THe Stored procedure builder doesn't give me options to create stored procedures in SQL. THe only option I get is Java. Is there a way around it ? or I am doing doing something wrong ? [...]
21114 35 51_Switching current database partition in DB2 UDB EEE0_26_Steve.Westfall@EQUIFAX.COM31_Fri, 20 Jul 2001 15:28:42 -0500563_us-ascii The environment: DB2 EEE 7.1 on AIX version 4.
Question 1: In a partitioned database with multiple logical partitions on the same physical machine, what DB2 command is used to change the logical partition to which subsequent DB2 commands will be directed?
Question 2: If all the partitions in a database are on the same physical machine, is use of the db2_all command still required in order to execute a DB2 command on all partitions in the database? Or is db2_all only used when the partitions reside on different physical machines? [...]
21150 172 22_Re: Insert next seq_no0_19_Tim.Lowe@STPAUL.COM31_Fri, 20 Jul 2001 15:36:03 -0500355_us-ascii Jay, Thank you. I tried adding the parens, and it looks great, but I get the same error (DB2 V6 for OS/390). I am looking for any PTFs that I might be missing, and Apar PQ40099 looks like a match, but I am told that PTF UQ46798 was already applied. Perhaps I am missing something else? (in DB2 or in my understanding of how this works!) (-: [...]
21323 44 48_Re: HELP !!! -- SQL stored procedures on ver 6.117_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Fri, 20 Jul 2001 16:15:26 -0500428_us-ascii I believe that PLSQL will only work with version 7.1. I could be wrong.
Jeremy
From: Azam Mirza @RYCI.COM on 07/20/2001 02:56 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Fax to: Subject: HELP !!! -- SQL stored procedures on ver 6.1 [...]
21368 15 65_Does DB2- UDB ver 6.1 on OS/390 support SQL stored procedures ???10_Azam Mirza18_azam00@HOTMAIL.COM31_Fri, 20 Jul 2001 16:21:49 -0500355_- Hi, Does Db2 UDB ver 6.1 on OS/390 support SQL stored procedures ?
Thanks for the assistance
Azam
================================================ 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.
21384 64 25_Re: System & HP Hit ratio14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Fri, 20 Jul 2001 18:01:36 -0400316_ISO-8859-1 Just to clarify the system hit ratio.... the Async_page_read you have below is the Total Number of Pages read by all asynch read activity. This hit ratio can be less than zero, caused either by thrashing, or reading in more pages than the applications reference - a common cause is dynamic prefetch. [...]
21449 124 33_Re: Timeout or deadlock on insert10_Shery Hepp17_schepp@SRPNET.COM31_Fri, 20 Jul 2001 15:47:16 -0700395_iso-8859-1 Esteemed list- now that we've established that one shouldn't get a deadlock on an insert- what is going on in this silly system that we are getting deadlocks on inserts? only access to this table is cursor processing w/ ur and inserts. No updates or deletes.
==> PROGRAM TYPE SQL_CALL STMT# S -------- ---- -------- ----- - P18497 PROC INSERT 1425 P18497 PROC INSERT 1485 [...]
21574 19 36_Setting DB2 Connection pooling parms22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Fri, 20 Jul 2001 16:24:29 -0700339_iso-8859-1 Gurus, Can anybody tell me how I should actually go about setting the following parameters NUM_POOLAGENTS, NUM_INITIALAGENTS? (DB2 CONNECT EE connection pooling params) I know the values I want to set them to , but the manual does not tell me the actual commands and where I need to issue them Thanks for your help! Ramesh [...]
21594 15 33_Re: Timeout or deadlock on insert26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Fri, 20 Jul 2001 18:28:21 -0500365_- -110 Invalid Hexidecimal Literal Beginning String Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu
================================================ 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.
21610 49 69_Re: Does DB2- UDB ver 6.1 on OS/390 support SQL stored procedures ???11_Joe Luthman22_jluthma@BGNET.BGSU.EDU31_Sat, 21 Jul 2001 06:51:26 -0500593_- Azam, Yes, it does. There's a redbook that describes how to setup. Cross-Platform DB2 Stored Procedures: Building and Debugging SG24-5485
You'll need to enable REXX exec DSNTPSMP, and a JCL procedure for compiling C language code. As you may have read, the SQL stored procedures get translated to C language. From there, an analogy to COBOL can be used (precompile, compile, bind, etc.) The fact that the code gets compiled also supports the idea that the SQL stored procedures will execute just as fast as COBOL or other languages. That is, this is NOT an interpreted language. [...]
21660 76 69_Re: Does DB2- UDB ver 6.1 on OS/390 support SQL stored procedures ???12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 21 Jul 2001 14:58:53 +0200555_Windows-1252 Hi, We used SPB on V7.1 on WIN2K to build SP for the V6.1 on OS/390. The generated C code worked fine. The only problem was debugging the generated C code.
Isaac Yassin DBMS & IT Consultant IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 yassini@bezeqint.net
----- Original Message ----- From: "Joe Luthman" Newsgroups: bit.listserv.db2-l To: Sent: Saturday, July 21, 2001 1:51 PM Subject: Re: Does DB2- UDB ver 6.1 on OS/390 support SQL stored procedures ??? [...]
21737 24 35_Data conversion error while loading8_duam lee16_duam_lee@USA.NET29_Sat, 21 Jul 2001 07:28:16 MDT426_US-ASCII Hi Masters, Here I have a probelm while loading the data to a table from a converted file. The conversion error occured and the record were discarded due to particular field's conversion. The field is defined as DEC(19,0) on OS 390 for making it compatible with BIGINT. The data converted on from legacy system. The PL/i supports only DEC(15,0). Is the probelm due to that. I m waiting for all your suggestions. [...]
21762 49 39_Re: Data conversion error while loading12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 21 Jul 2001 19:39:24 +0200344_iso-8859-1 Hi, PL1 supports dec(31,0) as well. If I understood correctly the program that prepared the file used dec(15,0) while the load command was defined for dec(19,0) so you got wrong data for that field. Check the fields in your input file (for the load) and fix the load command accordingly or build the file again using dec(31,0). [...]
21812 124 100_Central PA DB2 Users Group - Richard Yevich, YL&A, 10/30-31st - DB2 Version 7 Migration Class - $30010_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Sat, 21 Jul 2001 18:09:19 -0400328_us-ascii Hi all! I'm quite pleased to be having Richard Yevich of YL&A come to Central PA to teach a class for us! On Oct 30-31st, Richard Yevich of YL&A will be in town to teach a 2-day DB2 V7 Migration Class. So if you want to know what is involved w/migrating to DB2 V7, you might want to consider attending this class. [...]
21937 111 133_Central PA DB2 Users Group - Bonnie Baker, 12/5-6th - Things I Wish They'd Told Me 8 Years Ago Part IV & DB2 Locking - $150 per class10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Sat, 21 Jul 2001 18:09:29 -0400334_iso-8859-1 Hi all. On Dec 5-6th, Bonnie Baker will be in Central PA to teach 2 classes - Things I Wish They'd Told Me 8 Years Ago Part IV & DB2 Locking. Many of you have already participated in Bonnie's previous classes & know of her excellent DB2 teaching abilities! So I'm sure you won't want to miss her return to Central PA! [...]
22049 201 138_Pittsburgh DB2 Users Group - Bonnie Baker, 12/3-4th - Things I Wish They'd Told Me 8 Years Ago Part I & Explain Explained - $150 per class10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Sat, 21 Jul 2001 18:18:35 -0400405_iso-8859-1 Hi all. On Dec 3-4th, Bonnie Baker will be in town to teach a 2 classes - Things I Wish They'd Told Me 8 Years Ago Part I & Explain Explained.
For more details & directions, see our Central PA DB2 Users Group website: http://www.db2parug.org
If you would like to register, please contact me at 717-975-7396 or Cathy.Peck@Highmark.com. The price is $175 per person per class. [...]
22251 200 112_Central PA DB2 Users Group - Sheryl Larsen, 9/28th - DB2 for OS/390 V6/V7 Transition for Developers Class - $17510_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Sat, 21 Jul 2001 18:28:58 -0400372_iso-8859-1 Hi everyone! Back by popular demand Sheryl Larsen is returning to Central PA for a V6/V7 Transition for Developers Class. Many of you will recall that Sheryl has visited our RUG numerous times in the past. If you would like to know what's up/coming w/DB2 V6 & V7 from an application developer's standpoint, you might want to consider attending this class. [...]
22452 73 62_Re: Data conversion error while loading with doubt by jennifer17_jennifer jennifer22_jenni_jeni@HOTMAIL.COM31_Sat, 21 Jul 2001 23:32:21 +0000356_- Hi All, As far I know there is DEC(31,0) in PL/i. What ISSAc writen to define dec(31,0) in input field and and its corresponding field in db2 as dec(19,0). We have to give a try and see how it works. Also while loading if varchar fields are there in table column then how the load control statements are written. Can any body answetr this question. [...]