1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 1999, week 4 2 38 46_Re: DSNTIAUL to access data at remote location12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Wed, 22 Dec 1999 10:49:35 +0200305_windows-1253 Thank you for all your answers. What do you mean by using the option to specify only the table name? Do you mean omitting the PARM('sql') keyword? If yes, I believe that then I cannot use the CONNECT TO statement before selecting, or is it something else that I am missing? Gina Fertaki [...] 41 30 0_10_ajay kumar19_db2v5r1@HOTMAIL.COM29_Wed, 22 Dec 1999 14:42:17 IST460_- Hi..

What is the limitation of mass insert..?

We are inserting 236000 records, Record length is 630. When we use DSNTIAUL mass insert, it is idle no movement for more than 5 hrs. Is any other systems variables to be set.

We cannot use LOAD utility. Why INSERT is using means, In source table the data is in float we want to convert to decimal,for that we used INSERT. For small table, it went thru but for biggrer table it is idle. [...] 72 101 39_Re: Questions about DB2V6 RECOVER INDEX14_Grainger, Phil21_Phil.Grainger@CAI.COM31_Wed, 22 Dec 1999 11:51:02 -0000391_- Of course, what Frank says is true.

But then, this is the same situation as backing up one tablespace of a tablespace set. DB2 allows this as well. The fact is, it's not when you take the backups that define whether you have a consistent recovery point or not, but rather it;s how you use them in a recovery scenario. It's during recovery that DB2 protects you from yourself!! [...] 174 46 0_24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM31_Wed, 22 Dec 1999 08:02:05 -0500376_iso-8859-1 Have you looked at the system console or JES log to see if DB2 logging is causing trouble ? Is there an index involved and how is it doing ?

-----Original Message----- From: ajay kumar [mailto:db2v5r1@HOTMAIL.COM] Sent: Wednesday, December 22, 1999 9:42 AM To: DB2-L@RYCI.COM Subject:



Hi..

What is the limitation of mass insert..? [...] 221 185 26_Re: Indus software and DB212_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 22 Dec 1999 05:31:27 -0800362_us-ascii When I installed the system and supported it, INDUS itself didn't have any recommendations. But one independent consultant who had a lot of influence with

INDUS and a number of INDUS customers did actively recommend against Type 2's. He was referred to the installation I was supporting by INDUS for additional performance and tech support. [...] 407 23 15_Re: LIKE CLAUSE0_20_ddlusk@HOUSEHOLD.COM31_Wed, 22 Dec 1999 07:31:31 -0600311_us-ascii Thanks to all whom responded, the solution was simple. fill the comparison field with '%' then string in the value that we're looking for . so if you wanted all variations of thomas then the value which will do the comparison is 'thomas%%%%%%%%%%' the qoutes are not present, but the query works. [...] 431 85 0_14_Grainger, Phil21_Phil.Grainger@CAI.COM31_Wed, 22 Dec 1999 13:48:37 -0000543_- Reading between the lines, I assume you are doing some sort of

INSERT INTO........ ..... SELECT ..... FROM

to insert your 236,000 records. This means all 236,000 inserts are being done under the same unit of work (there can be no intervening COMMITs). So, there is definately an issue with logging. Don't forget that all 236,000 lots of 630 bytes will have to be written out. Is it likely that DB2 has run out of active logs and is waiting for an archive to complete? IS any other work happening in DB2 at the same time?? [...] 517 79 46_Re: DSNTIAUL to access data at remote location0_18_damcon2@US.IBM.COM31_Wed, 22 Dec 1999 08:52:36 -0500508_us-ascii Gina,

Try using the three part name (location.creator.name) or setup an alias that points to the remote table. Then use the name/alias in the unload without the parm('sql').

HTH, Jay



Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 878-3525, Tie Line 8-427-3525 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - BMW ----------------------------------------------------- [...] 597 204 13_AD Hoc Access13_Brunner Don J24_Don.J.Brunner@M1.IRS.GOV31_Wed, 22 Dec 1999 09:06:37 -0500637_ISO-8859-1 The subject of allowing Ad Hoc access in production was discussed earlier this year. Unfortunately I discovered I only saved one post on the subject in my save box. I believe the responses were mixed, some allowed complete access, some allowed it with restrictions, others allowed no access.

We currently have developers, and IBM contractors who are assisting them, that have select authority in our production environment. (please don't turn this into a conversation about the type of work we do and the fact that contractors have access to our data. All the standard disclosure forms have been properly signed). [...] 802 100 0_24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM31_Wed, 22 Dec 1999 09:10:01 -0500679_iso-8859-1 Sounds good plus look for any bufferpool problems or any other data set contention on JES logs, etc.

-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CAI.COM] Sent: Wednesday, December 22, 1999 8:49 AM To: DB2-L@RYCI.COM Subject:



Reading between the lines, I assume you are doing some sort of

INSERT INTO........ ..... SELECT ..... FROM

to insert your 236,000 records. This means all 236,000 inserts are being done under the same unit of work (there can be no intervening COMMITs). So, there is definately an issue with logging. Don't forget that all 236,000 lots of 630 bytes will have to be written out. [...] 903 102 29_Re: Running utilities in REXX20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 22 Dec 1999 09:10:12 -0500608_- I always thought DSNUTILB does not need to e APF-authorized. I ran it couple of times thinking the steplib was not APF authorized.

I just want to confirm this - Are you sure DSNUTILB needs to be APF authorized module ? (in that case may be my libraries were APF authorized !!) > -----Original Message----- > From: Harvey Wachtel [SMTP:HWachtel@UAPC.CUNY.EDU] > Sent: Tuesday, December 21, 1999 7:24 PM > To: DB2-L@RYCI.COM > Subject: Re: Running utilities in REXX > > If you invoke an APF-authorized module through LINKMVS or ATTACHMVS, it > will be run in an ordinary unauthorized environment [...] 1006 107 29_Re: Running utilities in REXX13_Scott Goodell24_Scott.E.Goodell@WCOM.COM31_Wed, 22 Dec 1999 08:18:32 -0600463_us-ascii Thanks Harvey, I knew DSNUTILB needed to be authorized but I was not aware of the additional information you provided. I'll look into trying your suggestions.

Scott









Harvey Wachtel on 12/21/99 06:23:33 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Scott Goodell/CDR/BSM/MCI) Subject: Re: Running utilities in REXX [...] 1114 18 26_Limiting QMF Query Results18_McQuillan, Rory M.28_Rory.M.Mcquillan@STATE.ME.US31_Wed, 22 Dec 1999 10:10:11 -0500440_iso-8859-1 > > > A common user request is for the top "n" accounts of a particular type. > Is > there an SQL "trick" or some other technique to set the result set size to > a > fixed number of rows?

================================================ 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. 1133 274 29_Re: Running utilities in REXX12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 22 Dec 1999 07:32:09 -0800493_us-ascii



DSNUTILB must be APF authorized. It runs in key 7, and OS/390 ships a program properties table entry for it. If you look at the load module or at the JCLIN, you'll see the AC(1) attribute, and we only put that on where required.

DSNUTILB needs to have the protection key of 7 set in the TCBPKF field in order to run. A customer sent several code fragments. They are not tested or warranted, but might be useful to see what was needed, with assembler code. [...] 1408 58 30_Re: Limiting QMF Query Results0_19_Tim.Lowe@STPAUL.COM31_Wed, 22 Dec 1999 09:33:07 -0600721_us-ascii If you are using QMF version 3, release 3; then try: run query (rowlimit=10)













"McQuillan, Rory M." on 12/22/99 09:10:11 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Tim Lowe/sfm/spc)





Subject: Limiting QMF Query Results















> > > A common user request is for the top "n" accounts of a particular type. > Is > there an SQL "trick" or some other technique to set the result set size to > a > fixed number of rows? [...] 1467 271 17_Re: AD Hoc Access0_23_Mike_Levine@TEKHELP.NET31_Wed, 22 Dec 1999 10:38:16 -0500593_us-ascii Hi,

We were one of the shops that allows unrestricted read access to production data (if the user has the required authority). Consider implementing data sharing and running all of your adhoc from another LPAR/DB2 data sharing member with separate bufferpools, DSNDB07, etc. It seems to be a waste of resources to run a query, time out and lose all of that work. If implementing data sharing is not a short term option, consider tailoring the QMF governor and setting up RLF to permit higher limits during non-prime shifts. If your system is not CPU bound and you are not [...] 1739 36 29_Re: Running utilities in REXX20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 22 Dec 1999 10:55:03 -0500546_- Very useful. Thanks

> -----Original Message----- > From: Roger Miller [SMTP:millerrl@US.IBM.COM] > Sent: Wednesday, December 22, 1999 10:32 AM > To: DB2-L@RYCI.COM > Subject: Re: Running utilities in REXX > > > > DSNUTILB must be APF authorized. It runs in key 7, and OS/390 ships > a program properties table entry for it. If you look at the load module > or > at the JCLIN, you'll see the AC(1) attribute, and we only put that on > where > required. > > DSNUTILB needs to have the protection key of 7 set in the TCBPKF > field in [...] 1776 60 32_Re: Checkpoint Frequency to high12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Wed, 22 Dec 1999 08:09:51 PST563_- Chris:

Here in our shop we have Activity monitor (AM) and Opertune (OP) (BMC products). I think these products are amazing because some critical activities could be maiden easily count on these products as part of your shop. For example , the LOGLOAD parameter was setup 4000 , it seems to be short enough to kill your db2 but not because using AM and OP we turn on an exeption that can increase this parameter if the frecuency of checkpoint is too high and when the db2 activity become normal that value is reset automatically. If in your shop you [...] 1837 13 32_Chargeback for Stored Procedures11_Lori Bewley21_lori_bewley@SABRE.COM31_Wed, 22 Dec 1999 11:50:48 -0800468_- Our shop uses MICS for chargeback to customers. We recently implemented our first production stored procedures and are looking for a way to tie CPU utilization to individual stored procedures for chargeback. Any suggestions?

================================================ 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. 1851 53 8_Re: Load9_Wu, James13_jwu@KRAFT.COM31_Wed, 22 Dec 1999 12:32:32 -0600642_- You can convert the FLOAT to DECIMAL during the UNLOAD process (with PARM('SQL' and SELECT). Then load the target table. For that amount of data, you SHOULD use load instead of insert.

James Wu :-)

(847)646-5548 jwu@kraft.com

> -----Original Message----- > From: ajay kumar [SMTP:db2v5r1%HOTMAIL.COM@internet.kraft.com] > Sent: Wednesday, December 22, 1999 8:42 AM > To: DB2-L%ryci.com@internet.kraft.com > Subject: > > Hi.. > > What is the limitation of mass insert..? > > We are inserting 236000 records, Record length is 630. When we use > DSNTIAUL > mass insert, it is idle no movement for more than 5 hrs. Is [...] 1905 78 17_Re: AD Hoc Access0_23_Mike_Levine@TEKHELP.NET31_Wed, 22 Dec 1999 13:45:34 -0500593_us-ascii Hi Don,

I did not intend to infer that we give unrestricted read access of production data to developers, we don't. We periodically take 'snapshots' of production data and load into development and test tables. I was referring to the users/data owners.

I do not see a need for developers to be reading live, production data to do development work. You may want to consider creating a secure devl/test environment to load production data or unloading production data and scrambling sensitive information to load into your normal, less secure, devl/test environment. [...] 1984 56 51_Urgent - Probs with Db2 Update anywhere replication12_Adam Flinton16_adam@SOFTFAB.COM31_Wed, 22 Dec 1999 18:40:13 -0000376_iso-8859-1 Dear All,

I have an URGENT problem.

I am trying to set up an update anywhere replication system to keep 2 db2 db'es in synch between 2 NTS boxes using RAS.

I can get the source to target working fine. No problems the data from the source goes into the target OK (i.e. cold start & then apply foreign keys , views, triggers etc to target). [...] 2041 19 34_JCL for Platinum Command Processor9_Wu, James13_jwu@KRAFT.COM31_Wed, 22 Dec 1999 12:58:57 -0600403_- Can any one give me a sample JCL to issue a Platinum Command in batch mode, such as - TERM PLATINUM (dbname) SPACE(*) ?

TIA.

James Wu :-)



================================================ 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. 2061 22 35_Questions about DB2V6 RECOVER INDEX28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Wed, 22 Dec 1999 12:31:58 PST514_- Frank,

Copying or recovering one index has no affect on the pending state of another index. Similarly, if you recover one index, it does not affect the other indexes or related table space. If you want them recovered also then you must recover them separately.

Indexes must be created, or altered to have, the COPY YES attribute for COPY INDEX/RECOVER INDEX to be available. QUIESCE and REPORT were extended to establish recovery points and to provide information on related COPY YES indexes. [...] 2084 48 21_CASE statement oddity12_Jason Hughes20_Jason.Hughes@TRW.COM31_Wed, 22 Dec 1999 13:51:36 -0800318_US-ASCII I'm running the following SQL statements against DB2 V5 on an OS/390 machine. Forget what PUT level we are at, early 99 sounds right.

I would like to return a charater instead of a number because MS-Access has trouble creating a text file with no spaces between fields when one field is a number. [...] 2133 81 25_Re: CASE statement oddity14_Richard Yevich17_ryevich@YAHOO.COM31_Wed, 22 Dec 1999 14:08:18 -0800579_us-ascii Check the syntax but this would work. Use STRIP to remove leading 0's if necessary. No time at moment to see what's wrong with failure.

SELECT DIGITS(CASE WHEN MALE > 0 THEN 1 ELSE 2 END) AS S6_GENDER FROM table1



--- Jason Hughes wrote: > I'm running the following SQL statements against DB2 V5 on an > OS/390 machine. Forget what PUT level we are at, early 99 sounds > right. > > I would like to return a charater instead of a number because > MS-Access has trouble creating a text file with no spaces between > fields [...] 2215 13 12_QUERY STATUS12_KIM WHANG GI21_hkikim@LGEDS.LG.CO.KR29_Thu, 23 Dec 1999 11:11:12 KST254_euc-kr q DB2-L

===============================================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. 2229 62 30_Re: Limiting QMF Query Results0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 23 Dec 1999 03:56:55 GMT375_us-ascii This is the technique from the query which can give u the top n COL1 from the table.

SELECT COL1,COL2,......................... FROM T1 A WHERE N > (SELECT COUNT(*) FROM T1 B WHERE A.COL1 < B.COL1)



There is one more technique available in QMF i.e by QMF forms u can limit the no. of rows return and can get the top "n" or last "n" values. [...] 2292 54 30_Re: Limiting QMF Query Results0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 23 Dec 1999 03:57:18 GMT367_us-ascii This is the technique from the query which can give u the top n COL1 from the table.

SELECT COL1,COL2,......................... FROM T1 A WHERE N > (SELECT COUNT(*) FROM T1 B WHERE A.COL1 < B.COL1)

There is one more technique available in QMF i.e by QMF forms u can limit the no. of rows return and can get the top "n" or last "n" values. [...] 2347 79 25_Re: CASE statement oddity0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 23 Dec 1999 04:10:24 GMT297_us-ascii I don't know at what PUT level we are but it works in our installation.I have tried the same query and it is working. It seems from the SQLSTATE that there is some problem with SYSIBM.SYSSTRINGS as it says character conversion is not defined.

I think u should check this out. [...] 2427 22 21_Using CONCURRENT copy9_John Reid14_jreid@FBFS.COM31_Wed, 22 Dec 1999 14:52:02 -0600334_us-ascii Hi List, We are looking at using virtual concurrent copy for our backups, were are on DB2 V5.1 OS390 2.6. My questions are:

Is any one using virtual concurrent copy as their sole back up mechanism? If so any issues I should be looking out for? If not, had you considered it and why you did not go that direction. [...] 2450 146 29_Re: Running utilities in REXX14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Wed, 22 Dec 1999 22:25:36 -0500358_us-ascii Before I responded to Scott's original post, I looked at the directory entry for our copy and found that it is, indeed, marked as APF authorization. However, I don;t know which of its functions, if any, require authorization. (For example of what I mean, I do know that AMASPZAP can be run unauthorized as long as you don't try to zap a VTOC.) [...] 2597 25 30_Reclaiming space on DB2/AIX V211_Michael Lim24_TSMLIM@HKCLEARING.COM.HK31_Thu, 23 Dec 1999 15:56:11 +0800504_US-ASCII Hello,

I have a table which has accumulated 3 years' worth of data. Now I would like to keep the last 6 months worth and delete the rest. How do I shrink the tablespace and reclaim the unused space?

In addition, the file system containing the tablespace has run out of space. I seem to have trouble with the 'delete' command failing with an out of space error. How do I calculate how much more space to give to the file system to enable me to perform the delete successfully? [...] 2623 80 34_Re: Reclaiming space on DB2/AIX V213_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Thu, 23 Dec 1999 14:26:38 +0530552_us-ascii hi Mike , You seem to have run into a typical situation where database maintenance is required. Don't go for a delete. First you can unload the data into a flat file. Drop the tablespace. Calculate using the formula for space estimation where we have 4K data pages as the base. For this you will have to have an approx. fig. of the number of rows the database will contain after the delete. The row length will determine how much rows each page will handle. And then you can arrive at the number of pages reuired. Estimate the free space [...] 2704 114 17_Re: AD Hoc Access18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Thu, 23 Dec 1999 06:51:44 -0600582_iso-8859-1 Don, I might be barking up a tree you may not wish me to or be way off track, but I think two issues may be present in the situation as you described it. One, this tremendous demand for ad hoc query against production data by the developers and data base designers could be hiding some latent demand for actual production work and masquerading as test/devl work as a work-around to an applications backlog. Two, it appears there may be no, or a severe lack of a test and development subsystem/LPAR/machine where cleansed portions of production data could be stored. [...] 2819 37 31_Dynamic SQL from a CICS Program0_22_BILL_GALLAGHER@PHL.COM31_Thu, 23 Dec 1999 09:11:00 -0500587_us-ascii Hi. Here's a question that I'd like to throw out to the list for discussion, and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which includes an on-line query screen. I know few details about their requirements other than the fact that they will be selecting a fixed-list of columns from a particular table, but could have a varying set of where conditions (up to three columns predicates in any combination, based on what the user chooses), and the sort order of the result set can also be chosen by the [...] 2857 74 35_Re: Dynamic SQL from a CICS Program11_Jim Knisley27_Jim_Knisley@FORETHOUGHT.COM31_Thu, 23 Dec 1999 09:16:47 -0500626_us-ascii Bill,

We use a ton of dynamic SQL from CICS programs (DB2 V5.1, CICS V4). With current releases of DB2, dynamic SQL operates very efficiently. There are options for caching statements, however there are some DB2 considerations about whether or not the caching would buy you anything in terms of performance. The biggest consideration we have run across is the access path determination. Since the queries are not statically bound, a query that runs great today, may run poorly tomorrow if you run RUNSTATS and there are significant changes that cause DB2 to pick a different access path. Therefore, daily [...] 2932 17 8_Sign-off0_39_jayashree.sampathkumar@BELLATLANTIC.COM31_Thu, 23 Dec 1999 09:19:21 -0500509_us-ascii "SIGN-OFF DB2-L"

================================================ 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.

================================================ 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. 2950 73 35_Re: Dynamic SQL from a CICS Program12_craig patton21_prgpatton@HOTMAIL.COM29_Thu, 23 Dec 1999 06:41:45 PST618_- Bill, One thing to be careful of is LONG running dynamic SQL. You might want to incorporate RLF to prevent a run away query. From your description this might NOT be an issue, it depends on how tightly you are controlling that SQL.

HTH, Craig Patton DB2 DBA





>From: BILL_GALLAGHER@PHL.COM >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Dynamic SQL from a CICS Program >Date: Thu, 23 Dec 1999 09:11:00 -0500 > >Hi. Here's a question that I'd like to throw out to the list for >discussion, >and to get some opinions on how we should proceed. > [...] 3024 50 48_Passing values between columns and host variable14_Michael Kaplan22_michael.kp@SAPIENS.COM31_Thu, 23 Dec 1999 16:52:02 +0200368_iso-8859-1

Dear List,

I'd like to hear your opinion about the following scenario :

Program A (written in COBOL) has host variable V1 which fits DB2 table (T1) column C1.

Then the former table T1 is droped and new table (the same name T1) has created with enlarged column C1. F.e., former C1 was DECIMAL (5) and new one - DECIMAL (7). [...] 3075 87 35_Re: Dynamic SQL from a CICS Program22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 23 Dec 1999 15:02:00 +0000546_ISO-8859-1 Bill coming from a 99% dynamic DB2 environment I would suggest that utilising the Local and Global cache(edmpool) will aid performance .. this is important as you DO NOT want the system having to contunally re-preparing the statements everytime (i.e no cache) if the queries are the same .. for example say 100 people all use the same query exactly and they use it 100 times a day .. then with the right caching you can save yoursefl (100 * 100) - 1 number of prepares .. and the CPU saving is immense .. it may not matter to you [...] 3163 99 25_Re: CASE statement oddity15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 23 Dec 1999 11:13:35 -0500594_iso-8859-1 Our put level is 9809. The case statement works fine with our DB2 V5.1, OS/390 V2.6.

thanks Sibimon





-----Original Message----- From: ssethi@LOT.TATASTEEL.COM [mailto:ssethi@LOT.TATASTEEL.COM] Sent: Wednesday, December 22, 1999 10:10 PM To: DB2-L@RYCI.COM Subject: Re: CASE statement oddity



I don't know at what PUT level we are but it works in our installation.I have tried the same query and it is working. It seems from the SQLSTATE that there is some problem with SYSIBM.SYSSTRINGS as it says character conversion is not defined. [...] 3263 22 25_Re: CASE statement oddity12_Jason Hughes20_jason.hughes@TRW.COM31_Thu, 23 Dec 1999 10:52:24 -0800397_- Just a clarification. I was confused about our PUT level. We are at 9805. I confused our client's PUT level with our own. Does anybody know if there was a APAR/PTF on this problem?



On Thu, 23 Dec 1999 11:13:35 -0500, Philip, Sibimon wrote:

>Our put level is 9809. The case statement works fine with our DB2 V5.1, >OS/390 V2.6. > >thanks >Sibimon [...] 3286 36 12_Table Design19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Thu, 23 Dec 1999 09:21:50 PST478_- Hi DBAS There are 10 COBOL programs which insert file-names along with few other details into a table .There is one CONCAT Program which reads this table concatenates based on some flag and writes the output file-names into the same table . Some other PROCESS program will again read this table and processes the rows which were inserted by CONCAT program . All the above 10 Programs can run concurrently with CONCAT program What will be the best design for this table ? [...] 3323 54 25_Re: CASE statement oddity14_Richard Yevich17_ryevich@YAHOO.COM31_Thu, 23 Dec 1999 09:29:37 -0800566_us-ascii Get them much more current. You should at least be at 9903. The problem was fixed back in maintenance, along with some other quirks when using CASE. If you don't get closer to current with maintenance, you are going to get stung with other problems.

Richard Yevich

--- Jason Hughes wrote: > Just a clarification. I was confused about our PUT level. We are > at 9805. > I confused our client's PUT level with our own. Does anybody know > if there > was a APAR/PTF on this problem? > > > On Thu, 23 Dec 1999 11:13:35 [...] 3378 18 45_Number of rows in the resultant set of cursor19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Thu, 23 Dec 1999 09:32:12 PST497_- Hi , Is there any way to find out the number of rows returned by cursor without having Seperate Select using COUNT(*) function ? Thanks Kishore ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3397 18 35_Re: Dynamic SQL from a CICS Program12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 23 Dec 1999 09:36:01 -0800305_us-ascii One of the most common techniques for this kind of situation is to code one or two combinations in static SQL, so that common cases do not have the overhead of dynamic SQL. So you find a case or two that you expect to be common and has a good index, then use dynamic SQL for the exceptions. [...] 3416 14 29_Re: Running utilities in REXX12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 23 Dec 1999 09:43:18 -0800325_us-ascii For DSNUTILB, all of the function requires APF authorization.

Roger

================================================ 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. 3431 64 35_Re: Dynamic SQL from a CICS Program12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 23 Dec 1999 19:46:13 +0200466_x-user-defined Hi,

We do it a lot (about 70% of our activity is dynamic sql using cics) and it works fine. We have about 200+ plans to separate monitoring and catch the problematic ones. At the beginning there were some attempts at misuse (cpu hogs) but that disappeared fast enough with education and internal program limitations (like adding restrictive clauses to some "bad" queries). We started it on DB2 V2.3 !!! and never had complaints even then. [...] 3496 79 35_Re: Dynamic SQL from a CICS Program20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 23 Dec 1999 13:02:03 -0500507_us-ascii Bill

At the previous place I worked we used dynamic query from CICS a lot. The main reason was REOPT(VAR) was not available at earlier versions and the online screens had many optional fields. If user does not specify value for them, they used to be > LOW VALUE or < HIGH VALUE. We couldn't achieve good performance with static SQLs because of so many choices user had. We switched over to Dynamic SQL along with RLF and had tremendous improvement. User community was especially happy. [...] 3576 49 49_Re: Number of rows in the resultant set of cursor18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Thu, 23 Dec 1999 13:05:52 -0600299_iso-8859-1 Kishore, I don't know how to do it, if its even possible. But, if you are talking about trying to get the number immediately after the OPEN CURSOR statement, I wouldn't think so. DB2 may have been able to avoid materializing the entire result set, so it wouldn't know at that point. [...] 3626 27 24_UDB DBA POSITION POSTING14_Rebecca A Bond22_rebecca_a_bond@UHC.COM31_Thu, 23 Dec 1999 14:34:24 -0500521_us-ascii A large health care concern is seeking a UDB DBA for a contract or contract to perm position at its Columbus, Ohio location. The need is for a full service DBA who will support a small data mart and several databases for locally developed web applications. This individual will function as the database 'expert' and is expected to standardize and guide all database related activities. Products currently used include: UDB 5.2 EE, AIX 4.3, Websphere, Visual Age for Java, Crystal Reports, Seagate Analysis. [...] 3654 20 42_how to get the value of "identity columns"0_19_Tim.Lowe@STPAUL.COM31_Thu, 23 Dec 1999 14:07:36 -0600528_us-ascii I have been looking forward to "identity columns", but I am curious how we will use them.

After an insert into a table with an "identity column" that is "generated always", how do we determine what value was assigned to the row that was just inserted?

Thanks, Tim

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3675 76 35_Re: Dynamic SQL from a CICS Program13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 23 Dec 1999 14:55:11 -0600590_iso-8859-1 I worked with an application a few years ago that did this same thing for the same reasons. One thing I found was that keeping response times acceptable was a little challenging, mainly because of the end-user being able to choose the sort-order on the fly. In my case we had the potential for huge result sets, but of course only the first 'n'-rows could be displayed on a CICS screen. In this case it obviously made sense to try to build supporting indexes to eliminate the db2-sorts. With over a dozen different selection criteria available (including any combination of [...] 3752 47 46_Re: how to get the value of "identity columns"14_Richard Yevich17_ryevich@YAHOO.COM31_Thu, 23 Dec 1999 13:06:46 -0800623_us-ascii Use an AFTER INSERT TRIGGER for the table. Retrieve from the transition table.

Richard Yevich

--- Tim.Lowe@STPAUL.COM wrote: > I have been looking forward to "identity columns", but I am curious > how we will > use them. > > After an insert into a table with an "identity column" that is > "generated > always", how do we determine what value was assigned to the row > that was just > inserted? > > Thanks, > Tim > > ================================================ > To change your subscription options or to cancel your subscription > visit the DB2-L webpage at http://www.ryci.com/db2-l. The [...] 3800 80 35_Re: Dynamic SQL from a CICS Program14_Theisen, Craig22_CTheisen@GUIDEMAIL.COM31_Thu, 23 Dec 1999 16:02:30 -0600621_windows-1252 Recently we built our first CICS/Dynamic SQL application for similar reasons and it appears to be working quite well. It was a worth-while learning experience.

One thing we encountered and would like a better way to handle is "How to add table owner Qualifiers" so the source does not need to be changed when going between test and production environments.(the compile/bind will not qualify dynamic SQL tablenames) What we did was used the CICS assign APPLID instruction to identify what CICS region we are in, then based upon the result issued a set current sqlid statement to qualify the table [...] 3881 62 34_Re: Reclaiming space on DB2/AIX V213_Umair Hussain25_umair_hussain@HOTMAIL.COM29_Thu, 23 Dec 1999 17:10:00 CST529_- To shrink tablespace you basically have to export tables, drop tablespace, re-create tablespace, import tables

For your delete you must be running out of space in your log filesystem. Have to delete it programatically where a commit is issued after every n deletes.



Umair Hussain******************************** DBA Consultant * DB2 UDB, Oracle, Sybase, DataJoiner * IBM Certified Solution Expert - DB2 UDB DBA * IBM Certified AIX System Administrator * ********|All Disclaimers Apply|************** [...] 3944 109 35_Re: Dynamic SQL from a CICS Program15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 23 Dec 1999 18:46:37 -0500309_iso-8859-1 You may be able to use DYNAMICRULE=B in package bind in order to use qualifier of the package as the authid of the table in the dynamic SQL. I am doing this while executing a dynamic query using DRDA through ISQL. I do not specify authid in the dynamic SQL. Same thing may work for CICS also. [...] 4054 8 0_13_Sunil Venkata27_SVENKATA@GWMAIL.STATE.NV.US31_Thu, 23 Dec 1999 16:47:10 -0800244_-

================================================ 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. 4063 36 31_Y2K Bugs? Been There, Done That14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Thu, 23 Dec 1999 21:15:05 -0500660_us-ascii The dreaded pre-dawn phone call interrupts your all too brief post-New-Year's-party sleep: a critical system component has gone haywire trying to handle the date rollover and the shop is paralyzed....

Sounds like a prediction of a Y2K nightmare, but it's actually a reminiscence of the bug I had to cope with ten years ago. The phone call reported that almost all batch jobs were abending on S913s. The messages that the operators retrieved from the job log said things like "DATASET NOT ACESSIBLE - SYS90001.T174939.RA000.FARFEL.SYSUT2". Fortunately, the problem was fairly obvious -- our security system, Top Secret, was not recognizing [...] 4100 97 35_Re: Dynamic SQL from a CICS Program13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Fri, 24 Dec 1999 08:34:00 +0530511_us-ascii hi Bill

There is nothing wrong in giving a dynamic sql option from CICS. We too have it in our shops and use it for small administration work . It depends on what sort of query and how and when u fire it. See always we have to remember that CICS regions run at a higher priority and if you fire a dynamic sql we have to compromise on the response as this will take CPU. So u can build constraints within the program which does dynamic sql and then do it. We fire and save the output in TSQ [...] 4198 56 49_Re: Number of rows in the resultant set of cursor13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Fri, 24 Dec 1999 08:46:10 +0530592_us-ascii just check up SQLERRD(3) in the sqlca vishy







kishore erukulapati on 12/23/99 11:02:12 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Viswanathan N/LTITL)





Subject: Number of rows in the resultant set of cursor

















Note: Some recipients have been dropped due to syntax errors. Please refer to the "$AdditionalHeaders" item for the complete headers. [...] 4255 90 49_Re: Number of rows in the resultant set of cursor0_24_ssethi@LOT.TATASTEEL.COM29_Fri, 24 Dec 1999 03:55:39 GMT559_us-ascii Hi Kishore, I gave a lot of thought but couldn't find out a way of doing this without COUNT(*).However the existence of a particular row or some rows can be checked without COUNT(*) . One more thing is that if the tables for which u want to find out the total no. of rows are not so frequently used then CARDF from the SYSIBM.SYSTABLES can be used(Risky)...if the catalog statistics are always correct.Say, the tables are used in the program which is running once in a week and u always run ur RUNSTAT for those tables weekly before running the [...] 4346 82 34_Re: Reclaiming space on DB2/AIX V211_Michael Lim24_TSMLIM@HKCLEARING.COM.HK31_Fri, 24 Dec 1999 12:07:37 +0800377_US-ASCII Thanks for the tip. I resolved my problem by exporting the data required to a flat file and then running a 'load replace'. This shrank the file system size.

regards, Mike

>>> Umair Hussain 24/12/99 7:10 AM >>> To shrink tablespace you basically have to export tables, drop tablespace, re-create tablespace, import tables [...] 4429 15 49_Re: Number of rows in the resultant set of cursor13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Fri, 24 Dec 1999 12:09:38 +0530414_us-ascii I think Kishore is asking for the number of rows returned in a query and not the total rows in a table. That will be given in the systables after a runstats. Vishy

================================================ 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. 4445 46 49_Re: Number of rows in the resultant set of cursor0_24_ssethi@LOT.TATASTEEL.COM29_Fri, 24 Dec 1999 06:58:00 GMT432_us-ascii Yes , i missed out this point. The number of rows for a particular condition in a query cann't be obtained by SYSTABLES . Thanks for correction Vishy. Any more answers will be helpful for me as well.



Sanjeev









Viswanathan N @RYCI.COM> on 12/24/99 06:39:38 AM

Please respond to DB2 Data Base Discussion List [...] 4492 16 49_Re: Number of rows in the resultant set of cursor13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Fri, 24 Dec 1999 12:24:44 +0530444_us-ascii I had mentioned something in my last mail about SQLERRD(3) of SQLCA. This gives information for updates / deletes etc. But I am not sure for the selects. You can check up that . !!

Vishy

================================================ 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. 4509 55 49_Re: Number of rows in the resultant set of cursor10_Anand Babu19_anandbabu.mp@DB.COM31_Fri, 24 Dec 1999 12:31:13 +0530551_us-ascii Hello Vishy

Can you please clarify this - Is the SQLERRD filled up just after the execution of the SQL. I had experienced otherwise. We had to code a call for DSNTIAR to fill up the values. Is there something that is missing ?



Regards

Anand





******************************************************************************** A Hammer May Miss it's Mark But A Compliment ... Never If you are not living on the edge you are occupying too much space **************************************** [...] 4565 19 49_Re: Number of rows in the resultant set of cursor13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Fri, 24 Dec 1999 13:23:01 +0530344_us-ascii no not at all . The SQLERRD field is in the SQLCA. You normally include the SQLCA in your program . This will help you to directly access the SQLCA variables. The DSNTIAR is a utility from IBM which will give the information regarding the errors . like say you give the SQLCODE and it will give you the meaningful error message . [...] 4585 102 49_Re: Number of rows in the resultant set of cursor0_24_ssethi@LOT.TATASTEEL.COM29_Fri, 24 Dec 1999 08:03:26 GMT468_us-ascii Kishore,

SQLERRD(3) doesn't give u the information about no. of rows selected ,it gives information about no. of rows modified (update,insert or delete) after the execution of sql statement , for no rows modification or in case of failure of statement it is 0 and -1 if mass delete is issued in the segmented tablespace. It also doesn't give u the information about no. of rows modified(on reference tables) due to the referential integrity rules [...] 4688 16 30_Wish you all a Happy Christmas22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Fri, 24 Dec 1999 08:21:39 +0000341_ISO-8859-1 Just like to wish you all a very happy and safe Christmas.

Leslie Pendlebury-Bowe

================================================ 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. 4705 24 42_DSNTIAUL to access data at remote location12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Fri, 24 Dec 1999 14:31:57 +0200553_iso-8859-7 Hi Jay, I tried the three part name when accessing DB2 at AS/400, but it did not work. It seems that System-directed access is available only to Version 2.3 and later versions of DB2 for MVS/ESA and not for other members of the DB2 family of IBM products as stated in the "DB2 for MVS/ESA Application Programming and SQL Guide". Since I don't know assembler it is difficult to write code for the semicolon as well, so I will probably give up on this solution. Anyway,thanks for all your answers and I wish Merry Christmas to all of you! [...] 4730 28 14_Holiday wishes24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 24 Dec 1999 08:03:32 -0800849_us-ascii Merry Christmas, Happy (belated) Channukah, Blessed Kwanzaa, Prayerful Ramadan, and a happy, healthy, and prosperous 2000 CE (the only thing that's going to happen at midnight one week from today). ;-)

Frank

+-------------------------------------+--------------------------------------+ | Frank C. Fillmore, Jr. | Voice/Fax: 410.465.6335 | | The Fillmore Group, Inc. (TFG) | Class registration: 800-TFG-RDBMs | | 3213-A Corporate Court | E-mail: fillmore@ws1.tfg-rdbms.com | | Ellicott City, Maryland 21042-2247 | URL: http://www.thefillmoregroup.com | | USA | | +-------------------------------------+--------------------------------------+ | DB2 Family, Oracle, Client/Server, Distributed Database | | "Relational Database Solutions"(sm) | +----------------------------------------------------------------------------+ [...] 4759 21 26_SQL Procedures (PSM) in V611_Joe Luthman22_jluthma@BGNET.BGSU.EDU31_Fri, 24 Dec 1999 10:13:13 -0500552_us-ascii I've consulted the redbook on SQL Procedures and have two questions.

1) How does one install SQL Procedures onto V6? The redbook addresses only V5.

2) When will a supported SQL Procedures for V6 be announced? What we seem to have now, for V5, is a use-at-your-own-risk product. I'd prefer to wait until an announced FMID(s) or PTF(s). Joe Luthman DBA Information Technology Services Phone: (419)372-7750 260 Hayes Hall FAX: (419)372-7723 Bowling Green State University E-Mail: jluthma@bgnet.bgsu.edu Bowling Green, OH 43403 [...] 4781 48 30_Re: SQL Procedures (PSM) in V614_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 24 Dec 1999 10:30:08 -0800340_us-ascii Joe, If you are talking about Stored Procedures, there is detailed informaiton in the V5 and V6 Application Programming and SQL Guide SC26-8958-00, and in the redbook. If you are talking about SQL Procedural Language, I do not know of any support in V5 for OS/390 but I know it is coming, not sure if in V6.1 or not. HTH Phil [...] 4830 34 29_Re: Running utilities in REXX14_Grainger, Phil21_Phil.Grainger@CAI.COM31_Fri, 24 Dec 1999 12:20:32 -0000705_- Of course, depending why you were wanting to run your utility from REXX and, depending on whether you are on V5 or earlier, you could always call the utilities stored procedure from REXX..........

Phil Grainger Director DB2 Operations, Europe Computer Associates International



> -----Original Message----- > From: Roger Miller [SMTP:millerrl@US.IBM.COM] > Sent: Thursday, December 23, 1999 5:43 PM > To: DB2-L@RYCI.COM > Subject: Re: Running utilities in REXX > > For DSNUTILB, all of the function requires APF authorization. > > Roger > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L [...] 4865 98 49_Re: Number of rows in the resultant set of cursor18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Mon, 27 Dec 1999 07:00:53 -0600312_iso-8859-1 Vishy, I think the original question asked how to determine the number of rows that would be returned by a CURSOR. In a previous reply I said I didn't think this was provided anywhere because the result set isn't always fully materialized at CURSOR OPEN. If there is a way, I'd like to know too! [...] 4964 106 35_Re: Dynamic SQL from a CICS Program13_Lynne Flatley17_LFlatley@NEFN.COM31_Mon, 27 Dec 1999 08:56:01 -0500518_us-ascii "How to add table owner qualifiers"

We created a table, kind of a "system" table, i.e. it didn't belong to any specific application. Its key was a 3-character applid and another column contained the value of the table creator ID. Any program that needed the table creator ID would do a look-up (SELECT) based on their applid. This had the added benefit of 'forcing' the program to contain static SQL so that we could bind it and therefore use the dynamic bind rule for granting execute authority. [...] 5071 89 23_Re: DB2 from EasyTrieve15_Mohammed Nayeem21_db2udbxpert@YAHOO.COM31_Mon, 27 Dec 1999 07:30:46 -0800586_us-ascii You have to use cursor in order to access DB2 tables in easytrieve plus. Cursor is required even if u want to process single record.

Nayeem

--- Madhu K Banavati wrote: > You were right !! Thanks a lot.......It worked....I > just had to put a /*Route > XEQ 'db2' statement..... > > lRegards and have a great holiday ! > > Madhu > > > > From: jimh%QIS.NET@Internet on 12/21/99 02:22 PM > To: DB2-L%RYCI.COM@Internet > cc: (bcc: Madhu K Banavati) > Subject: Re: DB2 from EasyTrieve > > I don't know a thing about Easytrieve, but are [...] 5161 135 30_Re: SQL Procedures (PSM) in V612_Roger Miller19_millerrl@US.IBM.COM31_Mon, 27 Dec 1999 08:50:28 -0800355_us-ascii 1) I think that the items that need APARs are out, but some probably do not have the PTFs available yet. Here is one of the better notes from Susan Malaika.



The Preview of SQL Procedures mainly documents the SQL Procedures

language on DB2 OS/390.

It does not describe the Stored Procedure Builder in any detail. [...] 5297 151 30_Re: SQL Procedures (PSM) in V610_Park, Stan17_SPark@STATE.NM.US31_Mon, 27 Dec 1999 10:01:18 -0700448_windows-1252 Roger, Could not resolve the hostname ( malaika4.stl.ibm.com ) -Stan



-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Monday, December 27, 1999 9:50 AM To: DB2-L@RYCI.COM Subject: Re: SQL Procedures (PSM) in V6



1) I think that the items that need APARs are out, but some probably do not have the PTFs available yet. Here is one of the better notes from Susan Malaika. [...] 5449 7825 30_Re: SQL Procedures (PSM) in V612_Roger Miller19_millerrl@US.IBM.COM31_Mon, 27 Dec 1999 09:23:57 -0800150_us-ascii



Sorry - that's inside our IBM firewall. Here is a pdf file.

(See attached file: psmv6_dec1999.pdf)

Roger Miller 13275 40 23_Ignore Duplicate Rows ?19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Mon, 27 Dec 1999 09:58:13 PST416_- Hi , Rows from a table have to be selected based on a specific criteria . If Duplicates are found we need to select any row randomly . How can we do this most efficiently . We thought of few alternatives : 1.

Select from a table based on specific criteria If -811 Open the cursor Fetch only once Close the cursor End-if For getting one row , We need to do open-fetch-close processing which is not good [...] 13316 129 49_Re: Number of rows in the resultant set of cursor16_Richard A Yevich21_ryevich@BELLSOUTH.NET31_Mon, 27 Dec 1999 12:25:47 -0600413_iso-8859-1 There is no way to know or be told ahead of time the number of rows that will be retrieved by a SELECT CURSOR, other than doing a COUNT(*) with the exact same predicates. HOWEVER, this will not necessarily be correct due to the time of the COUNT(*) and the time of the SELECT, during which data could have been inserted, updated, and deleted, unless of course you were holding a gross table lock. [...] 13446 59 27_Re: Ignore Duplicate Rows ?11_Steve Mazer17_smazer@FMTUSA.COM31_Mon, 27 Dec 1999 14:32:56 -0500539_us-ascii If you are using DB2 UDB for Unix/NT/OS2, then you can try using the 'fetch first 1rows only' clause on the the select statement. If not, then you may consider using option 1, since option 2 is certainly not random.

Regards, Steve At 09:58 AM 12/27/99 -0800, you wrote: >Hi , > Rows from a table have to be selected based on a specific criteria . If >Duplicates are found we need to select any row randomly . How can we do this >most efficiently . >We thought of few alternatives : >1. > >Select from a table based on [...] 13506 86 27_Re: Ignore Duplicate Rows ?0_18_damcon2@US.IBM.COM31_Mon, 27 Dec 1999 15:45:42 -0500334_us-ascii Kishore,

I seem to remember that when a -811 is received there are values returned for a given row to the application and that it is unpredictable as to which row will be returned at any given execution of the SQL. If this is the case then this row could be used as your random row with no need to open a cursor. [...] 13593 112 27_Re: Ignore Duplicate Rows ?16_Richard A Yevich21_ryevich@BELLSOUTH.NET31_Mon, 27 Dec 1999 19:47:17 -0600609_iso-8859-1 Jay,

The contents of the host-variables are unpredictable when an -811 occurs. They may or may not have valid data. This is documented in the IBM Manual now.

Richard Yevich

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > damcon2@US.IBM.COM > Sent: Monday, December 27, 1999 2:46 PM > To: DB2-L@RYCI.COM > Subject: Re: Ignore Duplicate Rows ? > > > Kishore, > > I seem to remember that when a -811 is received there are values returned > for a given row to the application and that it is unpredictable > as to which [...] 13706 26 69_Let's shoot for a whole year. Was: Re: Wish you all a Happy Christmas17_David Lloyd-Jones16_icomm5@NETCOM.CA31_Mon, 27 Dec 1999 22:13:44 -0500587_us-ascii Leslie Pendlebury-Bowe wrote:

> Just like to wish you all a very happy and safe Christmas. > > Leslie Pendlebury-Bowe > > ================================================ >



OK, those of you who survived your mother's cooking at Christmas, my wish is that you have a prosperous and loving year to come.

-dlj.

================================================ 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. 13733 70 27_Re: Ignore Duplicate Rows ?0_24_ssethi@LOT.TATASTEEL.COM29_Tue, 28 Dec 1999 03:26:54 GMT495_us-ascii I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode will be efficient if u want row to be returned randomly.

Thanks

Sanjeev









kishore erukulapati @RYCI.COM> on 12/27/99 05:58:13 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Ignore Duplicate Rows ? [...] 13804 87 27_Re: Ignore Duplicate Rows ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 28 Dec 1999 09:23:21 -0500607_- Sanjeev

Singleton select does not optimize for 1 row. One has to use cursor to use OPTIMIZE FOR 1 ROW.

> -----Original Message----- > From: ssethi@LOT.TATASTEEL.COM [SMTP:ssethi@LOT.TATASTEEL.COM] > Sent: Monday, December 27, 1999 10:27 PM > To: DB2-L@RYCI.COM > Subject: Re: Ignore Duplicate Rows ? > > I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode > will be efficient if u want row to be returned randomly. > > Thanks > > Sanjeev > > > > > > kishore erukulapati @RYCI.COM> on 12/27/99 05:58:13 > PM > > Please respond to DB2 Data Base [...] 13892 15 45_Robert Browning/HDQ/WSP is out of the office.12_Bob Browning29_robert.browning@WORLDSPAN.COM31_Tue, 28 Dec 1999 10:06:04 -0500389_us-ascii I will be out of the office starting 12/28/1999 and will not return until 12/29/1999.

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. 13908 121 27_Re: Ignore Duplicate Rows ?15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Tue, 28 Dec 1999 10:36:23 -0500543_iso-8859-1 In our shop we take value in the host variable for processing if -811 is returned. We do not open, fetch and close in order to get a row if there is a -811. So far no problem even though manual says the result is unpredictable.

I know that there was a discussion regarding -811 some time back and I expressed that -811 should be considered as warning not as an error. This will make the programming a lot easier in the situation like this. Since I was a programmer before becoming a DBA, I believe that DBMS should make [...] 14030 46 49_Re: Robert Browning/HDQ/WSP is out of the office.18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Tue, 28 Dec 1999 10:03:51 -0600607_iso-8859-1 Bob, please don't auto-reply to db2-l. To stop posts while you're gone send the following to listserv@ryci.com SET DB2-L NOMAIL to resume posts SET DB2-L MAIL see archives for anything you may have missed.

Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete [...] 14077 149 27_Re: Ignore Duplicate Rows ?17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Tue, 28 Dec 1999 11:09:04 -0500318_- I think you should believe the IBM manual and not rely on unpredictable results that just happen to be OK so far. Remember that even if results seem to be OK now that the unpredictable result could start happening after any DB2 upgrade/PTF and then your "easy programming" technique will turn into a nightmare. [...] 14227 38 42_how to get the value of "identity columns"0_18_cmunson@US.IBM.COM31_Tue, 28 Dec 1999 10:21:56 -0700354_us-ascii We recognize this as a requirement and have a couple of ideas on how to resolve it. As to how and when we really can't say at this point because we are not sure exactly how we are going to implement it. I realize that I am not telling you much but I wanted to at least let you know that we have something under development to address this. [...] 14266 55 11_Y2K NETWORK7_Gustavo26_lozano@BANDEIRANTES.COM.BR31_Tue, 28 Dec 1999 16:13:23 -0300200_iso-8859-1 Hi folks!

Is there any list of DB2 users who will be online around the world on Dec 31?

Gustavo Lozano (lozano@bandeirantes.com.br) - DBA Banco Bandeirantes Sao Paulo - Brazil 14322 112 27_Re: Ignore Duplicate Rows ?19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Tue, 28 Dec 1999 10:14:54 PST640_- But Optimize for 1 Row can be used only if the no. of rows returned is only one row . In this case there may be many duplicate rows. Optimize for 1 row degrades the performance .

Kishore



>From: "Pillay, Venkat (PCA)" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Ignore Duplicate Rows ? >Date: Tue, 28 Dec 1999 09:23:21 -0500 > >Sanjeev > > Singleton select does not optimize for 1 row. One has to use >cursor >to use OPTIMIZE FOR 1 ROW. > > > -----Original Message----- > > From: ssethi@LOT.TATASTEEL.COM [SMTP:ssethi@LOT.TATASTEEL.COM] > > [...] 14435 31 15_Re: Y2K NETWORK13_Decastro, Joe23_Joe.Decastro@DISNEY.COM31_Tue, 28 Dec 1999 10:19:07 -0800466_iso-8859-1 I will be online. I'm GMT - 5.

Joe

-----Original Message----- From: Gustavo [mailto:lozano@BANDEIRANTES.COM.BR] Sent: Tuesday, December 28, 1999 2:13 PM To: DB2-L@RYCI.COM Subject: Y2K NETWORK



Hi folks!

Is there any list of DB2 users who will be online around the world on Dec 31?

Gustavo Lozano ( lozano@bandeirantes.com.br ) - DBA Banco Bandeirantes Sao Paulo - Brazil [...] 14467 142 27_Re: Ignore Duplicate Rows ?15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Tue, 28 Dec 1999 13:37:42 -0500528_iso-8859-1 Kishore,

Optimize for 1 Row tells DB2 to optimize the cursor's access path to get only one row. There can be multiple rows satisfied in the cursor. But if you fetch all the rows, the access path selected may not be good and it may be costly.

But in some case I have seen in three large table join, DB2 was using merge scan and hybrid join which was taking lot of time and when we put optimize clause it used nested loop join and the total time DB2 took was 1/4th. You can test it and see which one [...] 14610 47 15_Re: Y2K NETWORK0_23_Mike_Levine@TEKHELP.NET31_Tue, 28 Dec 1999 13:43:26 -0500830_us-ascii Hi,

List members will be posting to DB2-L any problems that they encounter during roll-over and into the new year. There is no separate 'sub-list'.

Regards,

Michael Levine Premier Data Services, Inc.



>Hi folks! > >Is there any list of DB2 users who will be online around the world on Dec 31? > >Gustavo Lozano (lozano@bandeirantes.com.br) - DBA >Banco Bandeirantes >Sao Paulo - Brazil > > > > > > > > >
Hi folks!
>
 
>
Is there any list of DB2 users who will [...] 14658 83 15_Re: Y2K NETWORK17_Barbosa, Odimir J22_odimir.barbosa@EDS.COM31_Tue, 28 Dec 1999 12:49:25 -0600623_iso-8859-1

Dear collegues, the last day of 1999 i'll be working to check our DB2 and applications. I'm in São Paulo - Brazil (GMT -4)

Regards,

Odimir Jamas Barbosa EDS- DBA Team

> -----Original Message----- > From: Mike_Levine@TEKHELP.NET [SMTP:Mike_Levine@TEKHELP.NET] > Sent: Tuesday, December 28, 1999 4:43 PM > To: DB2-L@RYCI.COM > Subject: Re: Y2K NETWORK > > Hi, > > List members will be posting to DB2-L any problems that > they encounter during roll-over and into the new year. > There is no separate 'sub-list'. > > Regards, > > Michael Levine > Premier Data Services, Inc. > > > [...] 14742 89 15_FW: Y2K NETWORK17_Barbosa, Odimir J22_odimir.barbosa@EDS.COM31_Tue, 28 Dec 1999 12:52:18 -0600576_iso-8859-1 > -----Original Message----- > From: Barbosa, Odimir J > Sent: Tuesday, December 28, 1999 4:49 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: Y2K NETWORK > > > Dear collegues, the last day of 1999 i'll be working to check our > DB2 and applications. > I'm in São Paulo - Brazil (GMT -4) > > Regards, > > Odimir Jamas Barbosa > EDS- DBA Team > > -----Original Message----- > From: Mike_Levine@TEKHELP.NET [SMTP:Mike_Levine@TEKHELP.NET] > Sent: Tuesday, December 28, 1999 4:43 PM > To: DB2-L@RYCI.COM > Subject: Re: Y2K NETWORK > > Hi, > > List members [...] 14832 73 38_Re: JCL for Platinum Command Processor0_25_joseph.burns@HIGHMARK.COM31_Tue, 28 Dec 1999 14:02:28 -0500360_us-ascii James,

Have you gotten a response from anyone on this(anything from Platinum)?

I asked the same question of Platinum about 2 years ago, and the answer was that they did not have a batch interface to terminate a utility. (Things may have changed since then, and I would be interested in knowing if there is something out there now). [...] 14906 18 27_Re: Ignore Duplicate Rows ?10_Ken Koleto23_kkoleto@PAINEWEBBER.COM31_Tue, 28 Dec 1999 13:20:00 -0500389_us-ascii I agree with you, Gary. There's another 'easy technique' that's making most of us work this weekend!

Ken

usual disclaimer...

================================================ 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. 14925 33 34_Re: Wish you all a Happy Christmas17_Gerald Bustamente20_ggbusta@LANDSEND.COM31_Tue, 28 Dec 1999 13:10:46 -0600575_koi8-r Thanks. Happy Holidays to you also.

:-)

Still trying to call you about Data Sharing. My fault. Scheduling.

Take care,

Jerry Bustamente Lands' End





Leslie Pendlebury-Bowe wrote:

> Just like to wish you all a very happy and safe Christmas. > > Leslie Pendlebury-Bowe > > ================================================ > 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. [...] 14959 31 15_Re: Y2K NETWORK12_Jim Harrison12_jimh@QIS.NET31_Tue, 28 Dec 1999 14:11:21 -0500537_us-ascii Actually, at IBM-Main we created a sublist for off topic chatter over the weekend (and as a backup to the main list which had originally planned to shut down). DB2ers are more than welcome to join.

http://www.onelist.com/community/IBM-Main2





At 01:43 PM 12/28/99 -0500, you wrote: >Hi, > >List members will be posting to DB2-L any problems that >they encounter during roll-over and into the new year. >There is no separate 'sub-list'. > >Regards, > >Michael Levine >Premier Data Services, Inc. [...] 14991 63 32_On-line Y2K - will post problems17_Gerald Bustamente20_ggbusta@LANDSEND.COM31_Tue, 28 Dec 1999 13:12:46 -0600357_koi8-r Gustavo,

I will be. Central Time, United States. Any problems I will post.

Sincerely,

Gerald Bustamente Lands' End

Gustavo wrote:

> Hi folks! Is there any list of DB2 users who will be online around > the world on Dec 31? Gustavo Lozano (lozano@bandeirantes.com.br) - DBA > > Banco Bandeirantes > Sao Paulo - Brazil 15055 23 29_Insertion using host variable19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Tue, 28 Dec 1999 11:19:09 PST399_- Hi , We have a DB2 table column which is defined as time stamp data type .One application program does inserts into this table by using working storage host variable . This host variable value is set from current timestamp by using seperate SQL . Will there be any improvement in performance if we directly use current timestamp special register ? Which one of the above is a good practice ? [...] 15079 86 36_Re: On-line Y2K - will post problems16_Cockerill, Steve30_SLCockerill@NATIONALGYPSUM.COM31_Tue, 28 Dec 1999 14:19:07 -0500646_koi8-r I will be - Eastern USA

-----Original Message----- From: Gerald Bustamente [mailto:ggbusta@LANDSEND.COM] Sent: Tuesday, December 28, 1999 2:13 PM To: DB2-L@RYCI.COM Subject: On-line Y2K - will post problems



Gustavo,

I will be. Central Time, United States. Any problems I will post.



Sincerely,



Gerald Bustamente Lands' End



Gustavo wrote:



Hi folks! Is there any list of DB2 users who will be online around the world on Dec 31? Gustavo Lozano ( lozano@bandeirantes.com.br ) - DBA Banco Bandeirantes Sao Paulo - Brazil 15166 56 33_Re: Insertion using host variable16_Michael McCarthy24_Michael_McCarthy@DTC.ORG31_Tue, 28 Dec 1999 14:36:44 -0500430_us-ascii Kishore,

Using the CURRENT TIMESTAMP special register for each INSERT will cause DB2 to get the system time for each INSERT. This will not improve performance. However, if the TIMESTAMP must be unique, CURRENT TIMESTAMP must be used for every INSERT. If multiple rows (or multiple tables in the UOW) need the TIMESTAMP for future JOIN predicates or WHERE clauses, the SET of a host variable is the way to go. [...] 15223 36 15_Re: Y2K NETWORK15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Tue, 28 Dec 1999 14:42:19 -0600404_ISO-8859-1 BMC Software plans on having one support person and one manager (to supply the pizza) on sight for each of the major product groups. This will start at 5:00 AM CST December 31 and end 12:00 PM CST January 1. Our support will be monitoring this list during their shifts. We also have a fortified on-call contact list running through January 16. It even includes 3 levels of doc support!?! [...] 15260 112 40_DB2 Symposium: Atlanta, Boston, St.Louis16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Tue, 28 Dec 1999 15:11:51 -0600603_iso-8859-1 Dear DB2 Professional,

I would like to announce the 1st Quarter schedule for the DB2 Symposium Education Events in the United States. We are holding events in

Atlanta, GA February 16-18th Boston, MA March 8th-10th St. Louis, MO April 12th-14th

All in-depth sessions are 1 day each and are taught by some of the most famous DB2 instructors world-wide. Also, we are extending our special early bird discount of 20% until Jan 15th, 2000. The schedules are listed below and to find more details on each session and to register, go to our web site www.db2-symposium.com. [...] 15373 83 16_Re: Table Design0_25_joseph.burns@HIGHMARK.COM31_Tue, 28 Dec 1999 16:18:54 -0500397_us-ascii Kishore,

I am not sure I understand exactly what this table is desinged to do, but using row-locking to avoid -911 on inserts will probably not help.

On an insert, if DB2 sees that the page is in use, then it will not give a -911, instead it goes through an algorithm and picks another page. The biggest exception to this rule is in the case of a -803 (duplicate row). [...] 15457 94 33_Re: Insertion using host variable16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Tue, 28 Dec 1999 15:26:59 -0600492_iso-8859-1 In the first case, it WILL improve performance since there will now only be 1 SQL statement, and not 2.

Regarding UNIQUENESS - CURRENT TIMESTAMP is not guaranteed to be unique. Obviously, if there is only one process doing inserts, then you are not going to get duplicates. But when there is more than one concurrent job doing inserts, then you can. It is not stated anywhere in any IBM publication that I am aware of that CURRENT TIMESTAMP is guaranteed to be UNIQUE. [...] 15552 16 25_DB2/MVS connect processin16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Tue, 28 Dec 1999 17:14:00 -0800345_- I am using DB2 V4 for MVS, RACF secondary authids. I have UNIVERSAL ACCESS=NONE for my DB2P.BATCH profile. I am using the sample exit programs. I expected a SQL error for a user not authorized to this profile but anyone seems to be able to connect to DB2 from batch job. Our RACF person says everything seems to be OK from the RACF side. [...] 15569 17 38_Re: DB2 for MVS/ESA connect processing12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 28 Dec 1999 17:28:10 -0800511_us-ascii Did you add the entries to the RACF router table and issue the SETROPTS? If you did, then perhaps there is something else in the section titled, Defining DB2 Resources to RACF. It's page 3-81 in the Administration Guide in my old hard copy.

Roger Miller

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 15587 28 29_Re: DB2/MVS connect processin10_Leo Flores23_leoflores@EARTHLINK.NET31_Tue, 28 Dec 1999 17:41:09 -0800550_us-ascii Does User(*) have READ Access? This means anyone that has access to the mainframe has read access to the DB2P.BATCH file.

HTH - Leo Flores

JOHN G. MATTHEWS wrote:

> I am using DB2 V4 for MVS, RACF secondary authids. I have UNIVERSAL > ACCESS=NONE for my DB2P.BATCH profile. I am using the sample exit > programs. I expected a SQL error for a user not authorized to this > profile but anyone seems to be able to connect to DB2 from batch job. > Our RACF person says everything seems to be OK from the RACF side. > > [...] 15616 89 27_Re: Ignore Duplicate Rows ?14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU31_Wed, 29 Dec 1999 13:47:00 +1100604_- Kishore,

We have concensus that you should not rely on the results after -811 (as per IBM manual). Before answering your question, I am intrigued to know why you believe open-fetch-close processing is not good?

Assuming materialisation is not required (if it was then singleton select and cursor would give similar performance), then if a singleton select was used, you would need to cater for the use of a cursor for those -811 situations, or use column functions (MIN, MAX etc.). Use of the column function can range from OK (if index to support, and small number of rows qualify) [...] 15706 54 15_Re: Y2K NETWORK0_24_ssethi@LOT.TATASTEEL.COM29_Wed, 29 Dec 1999 03:23:03 GMT601_us-ascii I will be in the office for the whole day of 31st Dec. and 1st Jan.

Sanjeev







Jim Harrison @RYCI.COM> on 12/28/99 07:11:21 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: Y2K NETWORK



Actually, at IBM-Main we created a sublist for off topic chatter over the weekend (and as a backup to the main list which had originally planned to shut down). DB2ers are more than welcome to join. [...] 15761 177 27_Re: Ignore Duplicate Rows ?16_Richard A Yevich21_ryevich@BELLSOUTH.NET31_Tue, 28 Dec 1999 21:45:43 -0600347_iso-8859-1 Philip,

Almost correct but change a little wording. OPTIMIZE FOR 1 ROW tells DB2 to get the first row using the "fastest possible access path" for the first and perhaps only 1 row. This of course, can also be the fastest access path, for the rest of the rows. It truly depends on the physical design, SQL complexities, etc. [...] 15939 125 27_Re: Ignore Duplicate Rows ?16_Richard A Yevich21_ryevich@BELLSOUTH.NET31_Tue, 28 Dec 1999 21:45:39 -0600604_iso-8859-1 Terry/Kishore,

Terry great response but let's go one further. A single SQL SELECT statement IS A CURSOR with the two fetches possible. It is just that the OPEN CURSOR, FETCH, CLOSE CURSOR is handled logically inside of DB2 instead of the programmer issuing the CALL.

As Michael Hannon, and others, have proven through benchmarks over and over, an OPEN CURSOR with OPTIMIZE FOR 1 ROW, FETCH, CLOSE CURSOR, is difficult to beat. The details of this have been published on DB2-L at least 6 times over the last few years. They are in the archives if anyone is truly interested. [...]