1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 2001, week 2 2 15 52_Cathy L Peck/ISG/CORP/Highmark is out of the office.10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Mon, 8 Oct 2001 01:00:48 -0400389_us-ascii I will be out of the office starting 10/05/2001 and will not return until 10/16/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. 18 21 31_Currentdata + workfile + cursor18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Mon, 8 Oct 2001 02:20:15 -0500505_- Hi all,

This refers to Currentdata info on pg 371 of appl prog & SQL guide where it says : "If the cursor is positioned on data in a work file, the data returned with the cursor is current only with the contents of the work file; it is not necessarily current with the contents of the underlying table or index." While I get the gist of the stmt, the term 'work file' and its relation to cursor has me in a fix. Could anyone help me in having a clear perspective and oblige with an example. [...] 40 30 38_ERROR DURING GETMAIN SYS CODE = 80A-1011_Daniel Adam16_dadam@GRATEX.COM30_Mon, 8 Oct 2001 03:36:10 -0500492_- Hi,

We experience DB2 related dumps on regular basis. When it happens, following error comes up :

IEA989I SLIP TRAP ID=X80A MATCHED. JOBNAME=DSN1DBM1, ASID=005E. IEA705I ERROR DURING GETMAIN SYS CODE = 80A-10 DSN1DBM1 DSN1DBM1 00 IEA705I 00F9D980 00AC5660 00AC5660 00E61200 00005000 IEA995I SYMPTOM DUMP OUTPUT 379 USER COMPLETION CODE=4093 REASON CODE=00000004

80A-10 should be "There is not enough virtual private area storage available to satisfy the request." [...] 71 80 24_Re: IMS to DB2 Migration16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Mon, 8 Oct 2001 09:30:30 +0100423_us-ascii You mean the oh-so-useful DL2?

Some of the guys from Circle subscribe to this list, I'm sure they'll jump on the chance.

BTW, I had used DL2 in the past, and found it a most useful tool.

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com [...] 152 55 30_VIEW MERGE AND MATERIALIZATION19_=?big5?B?tPa3VqS4?=21_A000896@MS.SKH.ORG.TW30_Mon, 8 Oct 2001 17:06:17 +0800131_big5 HI : Can someone tell me following meaning and how to use 1. view merge 2. view materialization thanks tang shenn yuan

208 104 26_Re: Oracle-DB2 differences9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Mon, 8 Oct 2001 10:01:10 +0100440_ISO-8859-1 Narayanan,

Below is a quick guide of what I have learned during my recent encroachment into the Oracle world.

I am sure I will be corrected if I am wrong.

Some terminology comparisons:

Tablespace scan - Table access full

Index scans - index range scan, index unique scan

Runstats - Analyze

Reorg index - Rebuild Index

Reorg table - Move table (v8); export/import (v7) [...] 313 201 26_Re: Oracle-DB2 differences18_Jean-Jacques Borie18_jjborie@TEKORA.COM30_Mon, 8 Oct 2001 12:15:36 +0200451_iso-8859-1 The best book about tunning Oracle is Oracle performance tunning tips & technique ISBN : 0-07-882434-6

All that stuff are available and more ...

Jean-Jacques Borie Directeur recherche et développement

Tekora, changez votre conception des sites web http://www.tekora.com 17, place de la Résistance - 92130 Issy-les-Moulineaux Tél. : +33 1 55 00 10 03 - Fax : +33 1 55 00 10 10 Por: +33 660199524 jjborie@tekora.com [...] 515 41 15_DSNDB curiosity14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Mon, 8 Oct 2001 12:15:29 +0100738_iso-8859-1 If DB2 for OS/390's directory is DSNDB01, default database DSNDB04, catalog DSNDB06 and temporary database DSNDB07, what happened to DSNDB02, 3 and 5?

Marcus Davage 'The views expressed are my own and do not necessarily represent the views or policy of my employer, Lloyds TSB Bank plc.'





----------------------------------------------------------------------------------------------------------------------- This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. [...] 557 86 46_Re: DB2 PM (no more) required for SAP 4.6C & D12_John Janssen19_john.janssen@ABP.NL30_Mon, 8 Oct 2001 14:05:35 +0200322_iso-8859-1 Hallo Tom,

DB2 PM is no more required. SAP has changed the requirements and given a bypass option.

Here is a mail I've received via BMC from IBM when we were engaged in connecting DB2PM. >>> Dear all, for those of you who did not receive this this note. Kind regards,

Jan de Jonge BMC [...] 644 154 34_Re: VIEW MERGE AND MATERIALIZATION13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 8 Oct 2001 07:24:31 -0500464_big5 Tang,

I would suggest you take a look the DB2 Administration Guide. It contains a good description of view merge vs materialization, including some examples and tables of when DB2 is required to materialize.

Merge: The query referencing the view or NTE (Nested Table Expression) is merge with the view or NTE with the result being a single, more restrictive query. This query is then optimized and executed to retrieve the required result. [...] 799 53 42_Re: ERROR DURING GETMAIN SYS CODE = 80A-1050_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM30_Mon, 8 Oct 2001 08:32:54 -0400401_iso-8859-1 You running stored procedures? A couple of years ago we had a problem with a stored procedure : had to modify the parameters : RUNOPTS to tune & adjust to correct the problem :







-----Original Message----- From: Daniel Adam [mailto:dadam@GRATEX.COM] Sent: Monday, October 08, 2001 4:36 AM To: DB2-L@RYCI.COM Subject: ERROR DURING GETMAIN SYS CODE = 80A-10 [...] 853 59 26_QMF DSNREXX CURRENT DEGREE9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Mon, 8 Oct 2001 14:16:57 +0100493_us-ascii Hi, QMF 3.3 DSNREXX V5 DB2 V5.1 Anybody know of a QMF global variable which is set to the CURRENT DEGREE value ?

Im trying to determine the CURRENT DEGREE setting from a REXX EXEC running in QMF(TSO) session.

Ive tried using the following code,

address dsnrexx "EXECSQL DECLARE C1 CURSOR FOR S1" sqlstmt = "SELECT CURRENT DEGREE FROM SYSIBM.SYSDUMMY1" "EXECSQL PREPARE S1 FROM :SQLSTMT" "EXECSQL OPEN C1" "EXECSQL FETCH C1 INTO :ICDEGREE" "EXECSQL CLOSE C1" [...] 913 17 32_varying commit freq dynamically.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Mon, 8 Oct 2001 08:28:44 -0500542_- Hi all,

Recently came across an interesting option called LOGLOAD which is used for dynamically changing the commit frequency!! Does this mean that the commit frequency of a Cobol/Db2 prog can be changed while the prog is running or that it needs to be changed prior to prog run.

TIA.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 931 49 36_Re: varying commit freq dynamically.16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 8 Oct 2001 09:51:54 -0400635_iso-8859-1 LOGLOAD does not change COMMIT frequency. LOGLOAD controls how frequently DB2 writes a CHECKPOINT record. LOGLOAD is an integer which says 'write a CHEKPOINT every this many log records'. LOGLOAD can be overridden by CHKTIME which is an integer which says 'write a CHECKPOINT every this many minutes'

If IBM (or anyone else, for that matter) comes up with a tool which will allow the beleagured DBAs and SYSPROGs to force errant applications (COBOL or other, especially the blankety-blank so-and-so things from blasted workstation platforms), this someone could sell this tool for $$$$$$$$$$$$$$$$$$$$$$$$$$$$$. [...] 981 77 26_QMF DSNREXX CURRENT DEGREE9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Mon, 8 Oct 2001 15:11:18 +0100382_us-ascii Ive found that the CURRENT DEGREE is '1' because it is a new connection to DB2 and is picking up the default DEGREE setting. Think Ill end up SAVE DATA the CURRENT DEGREE in QMF and read in from the REXX Exec. Kind Regards, Andy Hunt - Scottish And Southern Energy. ---------------------- Forwarded by Andy Hunt/HAV/SSE on 08/10/2001 14:56 --------------------------- [...] 1059 237 27_Re: DB2 (Dis)Connect Issues11_Jim Hardtke17_hardtkej@SLXI.COM30_Mon, 8 Oct 2001 09:03:09 -0500328_us-ascii The SYSPLEX enablement occurs on the DB2 connect gateway via the catalog DCS database parameters. There is nothing that needs to be set on the client. On the gateway, issue the command: db2 list dcs directory

If you have the keyword SYSPLEX in the DCS parameter string, you are enabled for SYSPLEX support: [...] 1297 52 36_Re: varying commit freq dynamically.9_Chris Tee23_chris.tee@UK.ZURICH.COM30_Mon, 8 Oct 2001 14:54:03 +0100461_us-ascii Rajendra,

LOGLOAD is used to determine the frequency of system checkpoints, the value represents the no. of log records written between checkpoints. The value should be set so that a checkpoint occurs every 10-15 minutes.

Chris





Rajendra Deshpande cc: Sent by: DB2 Data Base Subject: [DB2-L] varying commit freq Discussion List dynamically. [...] 1350 51 42_Re: ERROR DURING GETMAIN SYS CODE = 80A-1011_Robert Jans26_robert.jans@ALBERTSONS.COM30_Mon, 8 Oct 2001 07:36:50 -0700439_- Hi, quick question. Do you use SyncSort at this site?

Robert Jans Albertson's Inc < Standard Disclaimers Apply >



-----Original Message----- From: Daniel Adam [mailto:dadam@GRATEX.COM] Sent: Monday, October 08, 2001 2:36 AM To: DB2-L@RYCI.COM Subject: ERROR DURING GETMAIN SYS CODE = 80A-10



Hi,

We experience DB2 related dumps on regular basis. When it happens, following error comes up : [...] 1402 50 27_Re: Ranking in DB2 os390 V614_William Gannon33_wgannon@EMAIL.PALMBEACH.K12.FL.US30_Mon, 8 Oct 2001 10:30:24 -0400369_us-ascii Hi Anne !

Not to say that it is efficient or that it is a perfect fit but the following SQL could be adapted to your table and column names to return the desired result set :

SELECT NAME, CREATOR, TYPE, DBNAME, TSNAME, CARD FROM SYSIBM.SYSTABLES A WHERE 10 > (SELECT COUNT(*) FROM SYSIBM.SYSTABLES B WHERE A.CARD < B.CARD) ORDER BY 6 DESC ; [...] 1453 20 44_Index of Varchar column - Is it a good Idea?15_Neff, Stephen R16_NeffSR@STATE.GOV30_Mon, 8 Oct 2001 11:12:13 -0400515_ISO-8859-1 Hello,

We have a column that is a varchar. I was wondering if I indexed this column would the index be variable in length or would it be fixed. We are running DB2 V6 on OS/390. What are the pros and cons of indexing a varchar column?



Thanks

================================================ 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. 1474 54 23_RESTART = PHASE/ BYPASS36_KarthikeyanNair, Aravind (Cognizant)27_Karavind1@CHN.COGNIZANT.COM30_Mon, 8 Oct 2001 20:45:10 +0530467_iso-8859-1 Hi Listers,

I need some clarification on restarting a DB2 job if it abends. The scenario is like this :

If a DB2 job abends in any of the db2 utility, we can restart the job with RESTART = PHASE or RESTART = BYPASS based on the phase in which the job abended and get the job done without issuing DB2I command to kill the thread . For example if the job abended in INIT phase we can give the RESTART = BYPASS and get the job back to box. [...] 1529 97 49_Odp: Index of Varchar column - Is it a good Idea?15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL30_Mon, 8 Oct 2001 17:35:49 +0200334_iso-8859-2 Hallo Stephen,

a key value of index will be fixed - DB2 expands varchars to their full length before placing in index you will not be able to have INDEX-ONLY access if you would like to get varchar column value (see today's discussion on this subject)

Hope this helps :-)

Regards, Piotr Tarnowski [...] 1627 53 19_Re: DSNDB curiosity64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Mon, 8 Oct 2001 10:49:12 -0600336_ISO-8859-1 If memory serves me correclty, the first name of the DB2 catalog was DSNDB03. (IBMLink search confirmed this).

DSNDB05 was the original temporary database. (Again, found in IBMLink search)

I guess DSNDB02 is still waiting to be used... or maybe it was an incarnation of an object that never hit the field. [...] 1681 23 42_Re: ERROR DURING GETMAIN SYS CODE = 80A-1011_Mark Ediger22_mediger@TRANSUNION.COM30_Mon, 8 Oct 2001 10:58:44 -0500533_- Hi Daniel,

One easy thing to check is your JES2 parm for STC. Make sure that it has the parameter SWA=ABOVE. This is necessary to cut down the amount of storage below the line for each open dataset. If you have a lot of open datasets this might be the problem even though you usually get an abend106 instead of abend80a with this one. Also, make sure you have all of the ptfs listed in II04309 and II10817 applied. If your SWA parm is correct and you have all of those ptfs then you probably have to do some bufferpool [...] 1705 21 39_Long DB2 restart due to in-abort status15_Williams, Byron29_WilliamsByron@RUSSELLCORP.COM30_Mon, 8 Oct 2001 11:59:03 -0500531_iso-8859-1 What is the best way to reduce the DB2 restart time?

This happened in test so back out is not important. We had a CICS tran run for several hours. It performed many updates. It abended. While it was backing out, the box was IPL'ed - Test DB2 subsystem was cancelled. When Test db2 was restarted it took a long time for the back out of the CICS tran that was in an abort status. Has anyone used DEFER RESTART, page 4-104 in Admin Guide Vol 1, Version 5 - If so, did it help? What about the CREATE CONDITIONAL [...] 1727 38 43_Re: Long DB2 restart due to in-abort status18_Adrian P Challinor29_adrian.challinor@OSIRIS.CO.UK30_Mon, 8 Oct 2001 18:59:45 +0100445_iso-8859-1 Byron,

Not exactly the same, but we had a similar event with a UDB 7.1 transaction. We had been loading a data warehouse with a whole backlog of data. For reasons that are too complex to go in to we were re-loading 28 days worth of transaction data -- around 150M rows, and the database abended (well, the Unix equivalent). This was a EEE database on 16 instances - and it took the best part of 4 hours to recover itself. [...] 1766 31 18_Online performance13_Harry William27_harry_williamtk@HOTMAIL.COM30_Mon, 8 Oct 2001 18:52:38 +0000380_- Hi , We have noticed a couple of online screens returns data within 30 second for present date (say between 01.01.2001 to current date).

But when we try to see the data for dates in the past ie before 01.01.2000.It takes 2 to 3 minutes. I am not sure what is causing this delay.This happens in the case of single table index only access or multiple table join sqls. [...] 1798 58 22_Re: Online performance11_David Nance16_DWNance@FHSC.COM30_Mon, 8 Oct 2001 15:18:43 -0400374_US-ASCII Harry, Have you tried explains on the SQL with the literals that you are passing in? It could be that you're access path is changing, if SQL is dynamic. Could be that you're switching to sequential prefetch from list prefetch. What about the amounts of data being returned? Is it a large amount and the time is therefore proportionate with what takes 30 secs? [...] 1857 22 36_Re: varying commit freq dynamically.13_Judy Brownlow18_judysaxe@M3NET.NET30_Mon, 8 Oct 2001 16:25:59 -0500476_- Well...there IS a tool to vary commit frequency dynamically.....Database Rely, from SoftBase. (If I understand you correctly.)

It works with any language that can call an Assembler routine.

DBR will intercept the COMMITs and prevent them from occuring until the value that you specify is reached (#of LUW's/COMMITs or # of seconds of elapsed time). See www.softbase.com for product details or I can email directly. (It doesn't cost quite that many $$$ !) [...] 1880 110 32_Re: Trying for index only access0_39_Patrick_D_Poziwilko@EMAIL.WHIRLPOOL.COM30_Mon, 8 Oct 2001 17:41:05 -0400464_US-ASCII Jeff, you are right on with your comment, I did not think about DB2 having to return the actual size of the VARCAR data along with the index data. It appears that SAP did not think about it either, as they use VARCAR to define all columns that have character data. The challenge of tuning/maintaining environments that are created by purchased application is having to deal with their short comings and opportunities. Again, thanks for your insight. [...] 1991 153 42_Re: ERROR DURING GETMAIN SYS CODE = 80A-1017_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Mon, 8 Oct 2001 19:35:43 -0300463_iso-8859-1 Daniel,

I have had this trouble in the past running with SWA=BELOW. This will cause all VSAM control blocks to be below the line, which can be a problem with lots of datasets open. To know what your setting is, issue $DSTCCLASS command. It´s a JES2 command and can be issued from SDSF. If you can´t do that ask your system programmer to check it and change it to SWA=ABOVE if possible. The other option is to limit number of datasets open. [...] 2145 28 10_Db2 Insert13_Abi Manoahran23_abi@PROTEOMESYSTEMS.COM30_Mon, 8 Oct 2001 17:32:14 -0500301_- Hi Folks, I want to dorp and recreate a table. In that table there is a constraint which does the auto increment. If I dump the data and re-loaded then the auto-incremental data will be changed. Is there any way I can do that without loosing the record-ids?

Here is the table structure: [...] 2174 199 32_Re: Trying for index only access12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Mon, 8 Oct 2001 17:58:19 -0500649_iso-8859-1 Patrick,

I think that changed in V6. From the Admin Guide:

Is the query satisfied using only the index?(INDEXONLY=Y) In this case,the method is called index-only access......For a SELECT operation,all the columns needed for the query can be found in the index and DB2 does not access the table.For an UPDATE or DELETE operation,only the index is required to read the selected row. | Index-only access to data is not possible for any step that uses list prefetch | (described under "What kind of prefetching is done?(PREFETCH =L,S,or blank)" | on page 944.Index-only access is not possible when returning varying-length [...] 2374 75 27_Re: db2 os/390 rexx support64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Mon, 8 Oct 2001 18:32:49 -0600314_ISO-8859-1 DB2 only allows one connection from a TCB at a time, and from any address space, you can only connect to one DB2 at a time (at least it used to be this way - I couldn't find in the latest (V7) documentation where it mentions anything to the contrary... Anyone? Please correct me if I am wrong...). [...] 2450 50 14_Re: Db2 Insert16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Mon, 8 Oct 2001 19:48:04 -0400561_us-ascii try dsn1copy







Abi Manoahran on 10/08/2001 06:32:14 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Db2 Insert





Hi Folks, I want to dorp and recreate a table. In that table there is a constraint which does the auto increment. If I dump the data and re-loaded then the auto-incremental data will be changed. Is there any way I can do that without loosing the record-ids? [...] 2501 50 44_Re: SQL full outer join replacing the union.9_john king24_john_king@REDIFFMAIL.COM30_Tue, 9 Oct 2001 01:54:33 -0000492_iso-8859-1 Hello Dash, thanks to you for your help but when I run the queries with your full outer join methods it gave me syntax errod regarding the selection. I feel the selection you are taking some 1 and 2 and making a join on that. I tried with two levels it is working but I dont know why the three level union conversion to outer join is giving any probelm. Is there any restriction from db2 side. Here also I request Sanjeev and Teryy to give their comments. Thanks in advanvce. [...] 2552 215 26_Re: Oracle-DB2 differences14_Agrawal, Navin21_navin.agrawal@EDS.COM30_Mon, 8 Oct 2001 20:57:58 -0500425_iso-8859-1 Hi all,

Back to the original issue of DB2 vs Oracle, there is a document of Tehcnical Comparison between IBM DB2 UDBv7.2 and Oracle 9i, published by PASS Consulting Group. The link is http://www-4.ibm.com/software/data/pubs/papers/orac91vsdb272/orac91vsdb272.p df

This paper does a technical examination of the database technologies so as to give the DBAs an feel of the efficiency of the DBMS. [...] 2768 21 14_SQLCODE = -33210_Zheng Miao15_zhm@ACCA.COM.CN30_Mon, 8 Oct 2001 21:15:23 -0500547_- Hi,all I want to connect two db2 for os/390 using db2 private protocol. One db2's location name is locdsn15, the other is locdsn16. I have defined ddf and vtam. When I execute SQL on locdsn16 as "select * from locdsn15.db.tab;", I got error code -332 which say "SYSSTRINGS DOES NOT DEFINE A TRANSLATION FROM CCSID 00935 TO 00000". The configuration on two db2 is the same , two system are both os/390 v2r10 and db2 are v5r1. The MCCSID on both db2s are 935. So I donnot know why db2 still need character conversion , and why convert 935 to [...] 2790 32 27_DB2 application programming16_Vajira Ginneliya18_vajira@CODEGEN.NET30_Tue, 9 Oct 2001 08:37:55 -0400526_iso-8859-1 Hi all , I am recently involve in DB2 application programming with C++. My problem ...........

Can I have multiple commit scopes ( multiple transactions ) using a single connection in DB2 ?

I 'm using embeded SQL with context API and CLI API . I have tried both but fails to do it. DB2 manual says it treats a connection as a single transactions. So if you want two commit scopes ( or two transaction ) in a program ,it requires to make two connection to the database. Isn't it costly operation? [...] 2823 49 28_Workfile Database Bufferpool13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU30_Tue, 9 Oct 2001 13:13:22 +1000835_iso-8859-1 Hi All,

Below are some stats from the excellent Buffer Pool Tool available from Responsive Systems. Could someone please explain what would cause such a high percentage of random access? Environment is DB2 OS/390 V6 and BP7 only contains DSNDB07 objects.

Statistics for Buffer Pool: BP7 Buffer size is ..............................................4K Number of VP Buffers is ..........................12,000 Primary Storage VP sequential threshold is .......................99% VP deferred write threshold is .................50% VP vertical write threshold is ...................10% Number of HP Buffers is ...........................0 HP sequential threshold is .......................80% Hiper Space Castout is ..............................Y Page Steal Algorithm ..................................LRU [...] 2873 158 44_Re: SQL full outer join replacing the union.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 8 Oct 2001 23:24:27 -0500676_iso-8859-1 John,

I wouldn't bother with a full join, since the first table (TESTC28) is being left-joined to the subsequent tables, and the inner tables (in a full join operation) may introduce nulls that would be discarded by the LEFT JOIN.

Try something like:

SELECT ip.CID AS IPCID ,COALESCE(kp.ZIPCODE, up.ZIPCODE, ep.POSTALCODE) AS POSTALCODE ,COALESCE(kp.CITYORKUN, up.CITY, ep.CITY ) AS CITY ,COALESCE(kp.PROVINCE, ' ' ) AS PROVINCE ,CASE WHEN kp.ZIPCODE IS NOT NULL THEN 100 WHEN up.ZIPCODE IS NOT NULL THEN up.COUNTRYCODE WHEN ep.POSTALCODE IS NOT NULL THEN ep.COUNTRYCODE ELSE ' ' END AS COUNTRYCODE FROM TESTC28 AS ip LEFT JOIN TESTC55 AS [...] 3032 80 27_Re: Ranking in DB2 os390 V613_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 8 Oct 2001 23:30:57 -0500395_us-ascii Bill,

If you read back through the archives, I have had many attempts to try to outlaw this query from any serious implementation for this type of problem.

Due to the exponential growth in rows counted by the correlated subquery, this query begins to suffer when the table size gets into the thousands, let alone the 25 million that Anne is wanting to query against. [...] 3113 55 35_Re: Currentdata + workfile + cursor13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 8 Oct 2001 23:37:21 -0500433_iso-8859-1 Rajendra,

There is a section entitled "The effect of sorts on OPEN CURSOR".

Do a search on "OPEN CURSOR" in the Admin guide at http://www-4.ibm.com/software/data/db2/os390/v7books.html

This should provide you a good guide. Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com [...] 3169 97 32_Re: Workfile Database Bufferpool22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 10:21:19 +0530627_- Shaun,

I am sure Joel will let you know the correct answer but some guess from myside :- Do you have non-correlated subquery (IN types) with large result set for the inner query?. I am not even sure if this can cause this.

Regards, Sanjeev

> -----Original Message----- > From: LOMBARD,Shaun [SMTP:Shaun.LOMBARD@DEWRSB.GOV.AU] > Sent: Tuesday, October 09, 2001 8:43 AM > To: DB2-L@RYCI.COM > Subject: Workfile Database Bufferpool > > Hi All, > > Below are some stats from the excellent Buffer Pool Tool available from > Responsive Systems. Could someone please explain what would cause such a > high [...] 3267 15 49_Martha Kijak/HRD/Prudential is out of the office.12_Martha Kijak27_martha.kijak@PRUDENTIAL.COM30_Tue, 9 Oct 2001 01:02:22 -0400389_us-ascii I will be out of the office starting 10/09/2001 and will not return until 10/15/2001.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3283 79 22_Re: Online performance22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 10:51:40 +0530430_- Harry,

It is quite possible and the reason can be identified. You need to first look at the explain with literals so that you can notice the change in accesspath (if any). If this is not giving you any clue, then look at the online monitor using this SQL, you can find out some reason. It might be the data clustering, table scan. Provide the SQL, explain, cardinality, indexes details for some better explaination. [...] 3363 18 36_Re: varying commit freq dynamically.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Tue, 9 Oct 2001 00:13:36 -0500472_- Hi Eric,

Thanks for clearing my misunderstanding. Btw, Judy mentions about a tool that dynamically changes commit freq. Can it be a panacea for the DBA woes you mentioned....:). Good day.



regards, Rajendra.

================================================ 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. 3382 149 48_Re: Index of Varchar column - Is it a good Idea?22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 10:57:01 +0530324_iso-8859-2 Other than what explained by Piotr, you can have it variable length by choosing the ZPARM RETVLCHK (please check the spelling). This allows us to have variable length index. This was introduced in V6 and i use it sometimes more than a year ago. So, i might be missing some details, so please cross-check it. [...] 3532 60 14_Re: Db2 Insert18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Tue, 9 Oct 2001 11:09:34 +0530396_- Hi Abi,

If I understand you right, you want to retain the old values for col 'id' when you load the data in the new table. In that case you will have to define the col as 'generated by default'. A 'generated always' clause would not allow user to insert any value in the col. Its the system that generates values for such cols. Hope that helps. Let me know if you have any problem. [...] 3593 112 14_Re: Db2 Insert22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 11:43:50 +0530267_- I strongly believe this may not give the correct result. The other column and the identity one has to be synchronized as it is right now and BY DEFAULT may not give as we never know in what sequence the data is loaded. Its better to use DSN1COPY in this case. [...] 3706 127 14_Re: Db2 Insert13_Abi Monoharan23_abi@PROTEOMESYSTEMS.COM30_Tue, 9 Oct 2001 15:45:27 +1000524_us-ascii Hi Sanjeev, What is DSN1COPY and how do I use it?

Thanks in advance Abi

S, Sanjeev (Cognizant) wrote:

> I strongly believe this may not give the correct result. The other column > and the identity one has to be synchronized as it is right now and BY > DEFAULT may not give as we never know in what sequence the data is loaded. > Its better to use DSN1COPY in this case. > > Other than this, i was unaware of not being able to ALTER a table and > redefine the column as IDENTITY with a new [...] 3834 189 14_Re: Db2 Insert22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 12:33:35 +0530498_iso-8859-1 It looks to me a very difficult question if asked as a whole and to be explained in this list. I would suggest you to read the manual for getting its concept and request you to ask your senior DBA to use it. Please look thru' Utility Guide Manual. In this forum, i can only tell you its an offline means of copying the data from one file to tablespace VSAM. The file can be tablespace itself(on other subsystem or may be on same, it depends), it can also be the image copy dataset. [...] 4024 121 14_Re: Db2 Insert18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM30_Tue, 9 Oct 2001 12:39:56 +0530381_- Hi Sanjeev,

I had tried moving data between 2 tables where the source table had had an identity_col defined as 'generated always' and dest table had it as 'generated by default'. Applying the same anology to current table- load wouldn't we achieve what is intended here. Or is it that load doesn't honor the 'by default' clause. I may be wrong but hope to know more. [...] 4146 212 44_Re: SQL full outer join replacing the union.22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 12:53:56 +0530326_iso-8859-1 This is what we call genius Terry. I would have missed looking at TEST55 being used in all the inner join and could have given outer join at the first place but Terry made it all the way simple. At first look i thought all the inner join tables inside UNIONs are different but TEST55 is used in all the cases. [...] 4359 171 14_Re: Db2 Insert22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 13:03:48 +0530614_iso-8859-1 Rajendra,

Yes, from always to default, it will work. This will work as the data which are there in the file will be loaded. Index need to be exists on that identity column to use insert or load.

Regards, Sanjeev

> -----Original Message----- > From: Rajendra Deshpande [SMTP:rajendra_deshpande@HOTMAIL.COM] > Sent: Tuesday, October 09, 2001 12:40 PM > To: DB2-L@RYCI.COM > Subject: Re: Db2 Insert > > Hi Sanjeev, > > I had tried moving data between 2 tables where the source table had had an > identity_col defined as 'generated always' and dest table had it as > 'generated by [...] 4531 116 24_Re: IMS to DB2 Migration10_Tony Monks22_monks_tony@HOTMAIL.COM30_Tue, 9 Oct 2001 08:22:50 +0100695_- If anyone needs more info on DL2, please contact me.

Regards,

Tony.

Tony Monks, Circle Computer Group Ltd, Queensberry House, Queens Road, Brighton, BN1 3XF, UK. Tel (+44) 1273 721123 Fax (+44) 1273 731345 email :tony@circle-group.com Net :www.circle-group.com





>From: Aurora Dell'Anno >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: IMS to DB2 Migration >Date: Mon, 8 Oct 2001 09:30:30 +0100 > >You mean the oh-so-useful DL2? > >Some of the guys from Circle subscribe to this list, I'm sure they'll jump >on the chance. > >BTW, I had used DL2 in the past, and found it [...] 4648 24 27_REXX for DB2 on linux/os3900_23_JENNY@VMA.TABNSW.COM.AU30_Tue, 9 Oct 2001 16:23:19 +1000163_- Hi , Is any special requirment to use rexx for db2 on linux/os390 ? It would be very help if there is sample rexx program to access db2 on linux/os390 .. thanks. 4673 67 25_00C90101 during DB2 reorg10_Steve Lamb25_steve.lamb@POWERGEN.CO.UK30_Tue, 9 Oct 2001 02:57:11 -0500411_- Dear List,

Has anybody come across a reason code of x'00C90101' during a tablespace reorg, or have any ideas what might have caused it? The offending tablespace is reorged on a weekly basis, but for some reason this week it fell in a heap during the BUILD phase. The control statements are: REORG TABLESPACE DWLKPRA.SCI5391 LOG NO SORTDATA SORTKEYS STATISTICS TABLE(ALL) INDEX(ALL) KEEPDICTIONARY [...] 4741 77 14_Re: Db2 Insert13_Mathew, Sithu30_Sithu.Mathew@BLR.HPSGLOBAL.COM30_Tue, 9 Oct 2001 13:34:35 +0500600_- Hi Abi,

Can you try with the option IDENTITYOVERRIDE along with your load script. Hope it will solve the problem.

A sample load script is as follows.

LOAD FROM "table_name.dat" OF DEL MODIFIED BY coldel~ IDENTITYOVERRIDE MESSAGES "table_name_msg.txt" REPLACE / INSERT INTO "table name" NONRECOVERABLE;

where the file table_name.dat will contain your unloaded data(text file). Error message will go to the file table_name_msg.txt and table_name refers to the table into which you are loading the data. Here ~ is the delimiter which is used while unloading the data. [...] 4819 41 31_Re: REXX for DB2 on linux/os39019_Briggs, N. - Neil -25_N.Briggs@CANON-EUROPA.COM30_Tue, 9 Oct 2001 10:07:53 +0200311_iso-8859-1 I have tried to get the rexx programming langauge to work on my linux suse 7.1 box. There is a tar ball available at the IBM site, but I have not been unable to get it to work.

The only requirement I can think of off the top of my head is that you must use the public domain shell pdksh. [...] 4861 102 29_Re: 00C90101 during DB2 reorg23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 9 Oct 2001 09:13:32 +0100422_- steve

from memory I think we had this problem a year or so ago.

Required maintenance to fix the reorg problem, however, I also remember doing a repair to the pageset .. :-((

We were version 6 .. r u?

Leslie

-----Original Message----- From: Steve Lamb [mailto:steve.lamb@POWERGEN.CO.UK] Sent: Tuesday, October 09, 2001 8:57 AM To: DB2-L@RYCI.COM Subject: 00C90101 during DB2 reorg [...] 4964 69 13_Fw: DDF query8_Tam Kyle25_tam_kyle@STANDARDLIFE.COM30_Tue, 9 Oct 2001 09:32:09 +0100626_iso-8859-1 Hi - I'm posting the attached for a colleague who doesn't have list access. Could any replies be directed to Lynn or myself.

Regards,

The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No. SZ4) and regulated by the Personal Investment Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without our consent and please return it to us. We virus scan and monitor all e-mails but are not responsible for any [...] 5034 104 29_Re: 00C90101 during DB2 reorg14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE30_Tue, 9 Oct 2001 10:50:29 +0200582_iso-8859-1 Steve,

we once came across this problem and in the end CHECKed the Indizes, eventually RECREATing them; what had caused this problem in the beginning I have no recollection of. CHECking the table and/or index might help.

Hth Ruediger

Steve Lamb schrieb: > > Dear List, > > Has anybody come across a reason code of x'00C90101' during a tablespace > reorg, or have any ideas what might have caused it? The offending > tablespace is reorged on a weekly basis, but for some reason this week it > fell in a heap during the BUILD phase. > The control [...] 5139 20 29_Re: 00C90101 during DB2 reorg10_Steve Lamb25_steve.lamb@POWERGEN.CO.UK30_Tue, 9 Oct 2001 04:31:04 -0500453_- Leslie/Ruediger

Thanks for the replies. Yes we are v6 - I had a very quick look on the IBM website and didn't find anything which looked applicable, but I'll have another look. We tried restarting the utility, but it restarted and fell over in the RELOAD phase with an S0C6, having already deleted the tablespace :-( I recovered the tablespace and rebuilt the index and everything seems to be OK at the moment (CHECK INDEX ended RC0) .... [...] 5160 152 44_Re: SQL full outer join replacing the union.12_sushant dash23_dash_dba@REDIFFMAIL.COM30_Tue, 9 Oct 2001 10:32:44 -0000556_iso-8859-1 Hello Terry , Sanjeev and John , here is a solution from my side and I would be waiting for JOHN to test it. Again case expression of terry gave me another idea to do that. If you guys get time please verify this and suggest me what are the flaws. John please test this and we are waiting for both the method to test. Is it going according to the original query result. I made your address table as addr in most cases(testc55). == the new method =========== SELECT ip.CID AS IPCID ,pa.POSTALCODE AS POSTALCODE ,pa.CITY AS CITY ,pa.PROVINCE [...] 5313 118 44_Re: SQL full outer join replacing the union.22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 16:42:07 +0530568_- Dash,

If i have interpreted the result of your query and the requirement from John correctly then this query may not work (I am sorry if i am wrong). The fundamental point which i can notice are:

i) The number of table joined (inner join) and the number of join conditions. ii) Table TEST55 need to be inner joined with all, not the other tables with each other. It is quite possible that records which are there in for e.g. TEST41 matching with TEST55(and left outer matching with TEST28) but not matching with TEST26, this will be missed out in [...] 5432 135 44_Re: SQL full outer join replacing the union.22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 9 Oct 2001 17:06:06 +0530574_- Dash,

I relooked at your query, the number of join condition is OK. I missed the conditon which is there with WHERE clause. But my point (ii) still applies. Let's see what Terry has to say.

Regards, Sanjeev



> -----Original Message----- > From: S, Sanjeev (Cognizant) > Sent: Tuesday, October 09, 2001 4:36 PM > To: DB2-L@RYCI.COM > Subject: RE: SQL full outer join replacing the union. > > Dash, > > If i have interpreted the result of your query and the requirement from > John correctly then this query may not work (I am sorry if i am [...] 5568 43 18_Re: SQLCODE = -33216_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Oct 2001 07:28:34 -0400668_iso-8859-1 We had this. We opened an ETR with IBM. No solution. We circumvented by using DRDA instead of PRIVATE.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Zheng Miao [mailto:zhm@ACCA.COM.CN] Sent: Monday, October 08, 2001 10:15 PM To: DB2-L@RYCI.COM Subject: SQLCODE = -332



Hi,all I want to connect two db2 for os/390 using db2 private protocol. One db2's location name is locdsn15, the other is locdsn16. I have defined ddf and vtam. When I execute SQL on locdsn16 as "select * from locdsn15.db.tab;", I got error code -332 which say "SYSSTRINGS DOES NOT DEFINE A TRANSLATION FROM CCSID [...] 5612 88 29_Re: 00C90101 during DB2 reorg16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Oct 2001 07:31:46 -0400685_iso-8859-1 We had this periodically when on V5. We have not had it since going to V7.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Steve Lamb [mailto:steve.lamb@POWERGEN.CO.UK] Sent: Tuesday, October 09, 2001 3:57 AM To: DB2-L@RYCI.COM Subject: 00C90101 during DB2 reorg



Dear List,

Has anybody come across a reason code of x'00C90101' during a tablespace reorg, or have any ideas what might have caused it? The offending tablespace is reorged on a weekly basis, but for some reason this week it fell in a heap during the BUILD phase. The control statements are: REORG TABLESPACE DWLKPRA.SCI5391 LOG NO [...] 5701 49 20_DB2 Connect question15_Steve McAuliffe27_Steve.Mcauliffe@NATWEST.COM30_Tue, 9 Oct 2001 12:36:54 +0100376_iso-8859-1 Hello there,

Possible stupid question, but being new to connect here it comes anyway:

I have DB2 connect enterprise edition installed on my PC, and am using TCP/IP to connect to two OS/390 V6 DB2 subsystems.

When I open Control Centre and connect to system A, the tree expands to have 15 branches including bufferpools, view, aliases etc. [...] 5751 81 24_Re: DB2 Connect question18_Gert van der Kooij15_geko@WANADOO.NL30_Tue, 9 Oct 2001 14:06:36 +0200655_iso-8859-1 Hi, I've seen this kind of differences when the remote OS was unknown within the System definitions.



----- Original Message ----- From: "Steve McAuliffe" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, October 09, 2001 1:36 PM Subject: DB2 Connect question



> Hello there, > > Possible stupid question, but being new to connect here it comes anyway: > > I have DB2 connect enterprise edition installed on my PC, and am using > TCP/IP to connect to two OS/390 V6 DB2 subsystems. > > When I open Control Centre and connect to system A, the tree expands to have > [...] 5833 19 14_Todays webcast0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Tue, 9 Oct 2001 07:18:07 -0500340_us-ascii I registered for the webcast (version 7) that will be presented this morning. I am a bit confused though about the time. The announcement says it will happen at 9:00 AM EASTERN STANDARD time. I thought that the nation was on DAYLIGHT SAVINGS time. So when will we hear the WEBCAST here in Omaha (using Central daylight time)? [...] 5853 39 27_Todays webcast - correction0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Tue, 9 Oct 2001 07:28:14 -0500699_us-ascii I meant to say "The announcement says it will happen at 11:00 AM EASTERN STANDARD time."

Sorry about the confusion.



----- Forwarded by L-IS Kirkpatrick/MutualOMA on 10/09/2001 07:23 AM -----





L-IS Kirkpatrick To: DB2-L@RYCI.COM cc: 10/09/2001 Subject: Todays webcast 07:18 AM









I registered for the webcast (version 7) that will be presented this morning. I am a bit confused though about the time. The announcement says it will happen at 9:00 AM EASTERN STANDARD time. I thought that the nation was on DAYLIGHT SAVINGS time. So when will we hear the WEBCAST here in Omaha (using Central daylight time)? [...] 5893 36 18_Re: Todays webcast14_Manish Kothari23_mkothari@DATAMIRROR.COM30_Tue, 9 Oct 2001 08:28:38 -0400652_iso-8859-1 I think we change our clocks back one hour on the last weekend in October...





-----Original Message----- From: L-IS.Kirkpatrick@MUTUALOFOMAHA.COM [mailto:L-IS.Kirkpatrick@MUTUALOFOMAHA.COM] Sent: Tuesday, October 09, 2001 8:18 AM To: DB2-L@RYCI.COM Subject: Todays webcast



I registered for the webcast (version 7) that will be presented this morning. I am a bit confused though about the time. The announcement says it will happen at 9:00 AM EASTERN STANDARD time. I thought that the nation was on DAYLIGHT SAVINGS time. So when will we hear the WEBCAST here in Omaha (using Central daylight time)? [...] 5930 58 18_Re: Todays webcast0_20_John_Lendman@FPL.COM30_Tue, 9 Oct 2001 08:33:36 -0400371_us-ascii Here is what I got. You have to determine how many hours behind you are. I would guess 10:00am for you.

You may view the broadcast by simply going back to this Web site on October 9th, 8:00 Pacific Standard Time, 11:00 Eastern Standard Time, 16:00 Continental Europe



John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413 [...] 5989 23 17_Re: Stop Database12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Tue, 9 Oct 2001 08:52:16 -0400568_us-ascii > Hi All. We have been experiencing alittle problem since going to Version 6. > The DBA's run their batch jobs to I/C and runstat DB2 objects on weekends > and at night. They issue a Stop DB(PTDB) space(*) then a Start DB(ptdb) > space(*) access(ro) in one step then the next step runs the DB2 utility. > After this finishes they issue Stop DB(PTDB) space(*) and then Start > DB(PTDB) Space(*) Access(rw). The problem is that we are starting to > experience commands finishing abnormally on the stop db or the start db > Access(ro). This is causing alot [...] 6013 12 17_Re: Stop Database15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA30_Tue, 9 Oct 2001 14:57:16 +0200299_us-ascii are you having problems where the sopts aren't c

================================================ 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. 6026 66 17_Re: Stop Database16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Oct 2001 09:03:23 -0400600_iso-8859-1 Jeff, What sort of messages accompany the 'commands finishing abnormally'? Are they indicative of timeouts?

If so, you probably have a lock/claim situation which needs attention. Try doing -DIS DB(dbname) SPACE(*) CLAIMERS ONLY LIMIT(*) and -DIS DB(dbname) SPACE(*) LOCKS ONLY LIMIT(*) before/after the STOP. Good chance you will find something still hanging on. If you have CMTSTAT=INACTIVE in your DSNZPARM, disconnected distributed threads can cause a problem like you describe. You can get rid of then by changing to CMTSTAT=ACTIVE or set POOLINAC to a low number or issue [...] 6093 99 14_Re: Db2 Insert13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Tue, 9 Oct 2001 08:05:37 -0500588_iso-8859-1 Another comment on this after "reading between the lines" -- I'm assuming the table is not being dropped and re-created just for the fun of it, which would imply that it will be created with a different structure than it was dropped with. If this is the case, I would think trying to use DSN1COPY would give you troubles since the structures will no longer match. You will also want to make sure you think through the implications of changing from generated always to generated by default (for example, with generated by default the generated values are not guaranteed to [...] 6193 37 17_Re: Stop Database12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Tue, 9 Oct 2001 09:09:01 -0400494_us-ascii You lost me. This is a Version 6 DB2 on the mainframe. Could you explain SOPTS and "C". Thanks







Regan Galbraith @RYCI.COM> on 10/09/2001 08:57:16 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: Stop Database



are you having problems where the sopts aren't c [...] 6231 104 58_Strange Results Using Round Function With Decimal Function11_Greg Helson26_Greg.Helson@TRICON-YUM.COM30_Tue, 9 Oct 2001 09:02:00 -0500379_- Hello SQL Gurus,

We are experiencing inconsistencies between dynamic executions and static executions of queries nesting the round and decimal functions. The queries were executed dynamically using SPUFI and DSNTEP2; they were executed staticly by embedding them into a COBOL program. The COBOL host variable is defined as S9(3)V(3) COMP-3.

The queries are: [...] 6336 39 17_Re: Stop Database11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Tue, 9 Oct 2001 08:18:01 -0500373_iso-8859-1 We had this problem, but in our case the stop caused an 0C4 abend in DB2 (the subsystem stayed up, but took a dump). The start always worked okay though. The PTF for this problem is UQ53953.

-----Original Message----- From: Jeff Frazier [mailto:Jeffrey_Frazier@WENDYS.COM] Sent: October 09, 2001 7:52 AM To: DB2-L@RYCI.COM Subject: Re: Stop Database [...] 6376 93 17_Re: Stop Database12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Tue, 9 Oct 2001 09:20:30 -0400479_us-ascii Eric, our CMTSTAT=ACTIVE. The message is DSN9023I and nothing else indicating a problem other than the step getting a rc12 and stopping the job..







"Pearson, Eric L," @RYCI.COM> on 10/09/2001 09:03:23 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: Stop Database [...] 6470 28 17_Re: Stop Database15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA30_Tue, 9 Oct 2001 15:29:32 +0200356_us-ascii Sorry - typo problems, and a mail-sending problem.

I meant to say -

Are you having problems where STOPs aren't completing because of other resources holding them? I have similar problems which cause BMC reorg's to fall out. Typically there are still some open threads etc. holding one of the resources you are trying to STOP... [...] 6499 217 24_Re: DB2 Connect question0_24_Bruce.Williamson@PSIR.IE30_Tue, 9 Oct 2001 14:37:39 +0100369_iso-8859-1 Howzit Steve?

Install 390 Enablement which is a set of DB2 stored procedures on 390 which enables the DB2 UDB Control Centre to administer DB2 for z/OS and OS/390. The FMID containing 390 Enablement for DB2 Universal Database for z/OS and OS/390 Version 5 is JDB551D. The FMID for DB2 Universal Database for z/OS and OS/390 Version 6 is JDB661D. [...] 6717 116 17_Re: Stop Database16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Oct 2001 09:37:12 -0400466_iso-8859-1 Jeff, Please look at the console (not job) log. Often messages like DSN9023I which may appear in the joblog or on the terminal are preceded by messages on the console. Those messages often tell you the root cause.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Jeff Frazier [mailto:Jeffrey_Frazier@WENDYS.COM] Sent: Tuesday, October 09, 2001 9:21 AM To: DB2-L@RYCI.COM Subject: Re: Stop Database [...] 6834 122 32_Thread parms on DB2 V6 OS390 2.912_Aedan Molony20_Aedan.Molony@PSIR.IE30_Tue, 9 Oct 2001 14:42:33 +0100532_windows-1252 Hi (ever-helpful)list! We are currently running DB2 UDB V6 under OS/390 2.9. We need to increase the number of remote connections into one of our DB2 environments from 10 to circa 150. This is to allow an application environment which uses up to 100 threads at anyone time, connect into the DB2 subsystem via DB2 Connect. We propose to change parameters 'MAXDBAT' & 'CONDBAT' within job DSNTIJUZ to 150. The values of other DDF thread related parameters, which we can see no good reason to change, are as follows: [...] 6957 157 36_Re: Thread parms on DB2 V6 OS390 2.916_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 9 Oct 2001 09:54:32 -0400398_windows-1252 The IDTHTOIN=0 may hurt you if the application has less than wonderful COMMIT logic. Approx 99.999999% ;=) of distibuted applications have less than wonderful COMMIT logic. IDTHTOIN=0 disables timeout for idle inactive threads and can result in their holding resources (especially locks :-( ) which they are not using.



Regards, eric pearson NS ITO Database Support [...] 7115 61 36_Re: Thread parms on DB2 V6 OS390 2.913_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM30_Tue, 9 Oct 2001 10:04:24 -0400375_US-ASCII A few minor points.

Check the Install Guide and other documentation regarding the POOLINAC parameter.

Also, increasing MAXDBAT will increase both the storage allocated for User Threads (by about 40k each) and also increases the "Storage Cushion" in the DBM1 Address Space. If you are already DBM1 storage-constrained, this may be a (minor) issue. [...] 7177 42 63_Dynamic rule Bind vs Dynamic Rule Run - impact on access path ?11_Eric Wilkin18_eric.wilkin@NBB.BE30_Tue, 9 Oct 2001 16:02:10 +0200365_iso-8859-1 Hi, We are runnind DB2 V5.1 on OS/390 .2.10

It seems that choosing "Dynamic rule RUN" or "Dynamic rule BIND" may have a impact on access-path (and so, on CPU consumption).

Is this possible ? And if so why ?



Thanks



Eric Wilkin National Bank of Belgium SYDS-DB tel +32 (0)2 221 45 99 fax +32 (0)2 221 30 92 [...] 7220 119 24_Re: DB2 Connect question15_Steve McAuliffe27_steve.mcauliffe@NATWEST.COM30_Tue, 9 Oct 2001 09:25:03 -0500638_- I don't think this applies in this case, both subsystems are running from a common set of runtime libraries...



On Tue, 9 Oct 2001 14:37:39 +0100, Bruce.Williamson@PSIR.IE wrote:

>Howzit Steve? > >Install 390 Enablement which is a set of DB2 stored procedures on 390 which >enables the DB2 UDB Control Centre to administer DB2 for z/OS and OS/390. >The FMID containing 390 Enablement for DB2 Universal Database for z/OS and >OS/390 Version 5 is JDB551D. The FMID for DB2 Universal Database for z/OS >and OS/390 Version 6 is JDB661D. > >HTH > >Cheers >Bruce > >-----Original Message----- >From: Steve McAuliffe [...] 7340 12 11_Unsubscribe8_DB2 Mail23_DB2.Mail.R000@SYSCO.COM30_Tue, 9 Oct 2001 10:33:00 -0400281_- Thanks, Evelyn J. Runyon 281-584-4028

================================================ 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. 7353 57 31_Re: DB2 application programming12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 9 Oct 2001 08:06:11 -0700644_us-ascii DB2 works differently than Oracle (Big Surprise :=) ) To have multiple transactions within one program, it requires multiple conections. If you use connection pooling, this isn't that expensive. I haven't actually measured the impact but haven't noticed any significant difference between the programs that have multiple connections and those that don't. I suppose there is some, but its not enough to be obvious to the eye. Not like a cartesian join between two multiple million row tables, for example. --- Vajira Ginneliya wrote: > Hi all , > I am recently involve in DB2 application programming with C++. [...] 7411 170 12_Commit Error14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM30_Tue, 9 Oct 2001 08:27:14 -0700480_iso-8859-1 Has anyone experienced the following error:

DSNE949E COMMIT PROCESSING ABNORMAL TERMINATION, REASON CODE X'00E30104'

DSN

END

READY

END

SARPAGE 6

CEE3250C The system or user abend S04E R=00C90101 was issued.

From compile unit JB401 at entry point JB401 at statement 3283 at comp 10A58EBA.

<> LEAID ENTERED (LEVEL 12/12/1997 AT 21.14)

* LEAID ABENDAID DD ALLOCATED BY CWBMAKDD DYNALLOC RC =00000 [...] 7582 75 31_Re: DB2 application programming13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Tue, 9 Oct 2001 16:31:55 +0100338_US-ASCII ,One thing you might investigate. DB2 V7 introduced "save points". I remember from an IDUG briefing that these were to "placate" Oracle developers who liked intermediate transactional support.

It didn't sound like a good idea to me - when is a transaction not a transaction ?

Is this what you're looking for ? [...] 7658 27 32_Version of UDB from Backup image15_Rajesh Udandrao17_urajes1@YAHOO.COM30_Tue, 9 Oct 2001 08:43:05 -0700602_us-ascii Hi Gurus Is there any way we can find the version of the database from the backup image in UDB under Unix environments



Thanks Rajesh





__________________________________________________ Do You Yahoo!? NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1

================================================ 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. 7686 58 30_Re: db2 certification resource13_Helen Johnson25_helen_johnson@RAC.RAY.COM30_Tue, 9 Oct 2001 10:48:07 -0500390_us-ascii Bill,

There is a free DB2 class for experienced DBAs to learn DB2. It can be downloaded at http://www-3.ibm.com/services/learning/news/freedb2cbt.html

Hope this helps, Helen

"Chen, Bill" wrote:

> > > Any good websites on IBM DB2 certification preparation? Any sample > exams? > Any good resource for an Oracle DBA trying to learn DB2? > > Thanks. > Bill 7745 49 36_Re: Version of UDB from Backup image18_Gert van der Kooij15_geko@WANADOO.NL30_Tue, 9 Oct 2001 17:59:24 +0200479_iso-8859-1 Hi, With DB2 V7 the db2ckbkp utility is available. With the -H option it only shows the media header information which also contains the the Release ID. I don't know if the tool can be used on backups from previous releases.

Hope this helps.

----- Original Message ----- From: "Rajesh Udandrao" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, October 09, 2001 5:43 PM Subject: Version of UDB from Backup image [...] 7795 65 24_Re: DB2 Connect question13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM30_Tue, 9 Oct 2001 10:54:13 -0500553_iso-8859-1 Steve,

Maybe double check the Node definition (instance name etc) through CCA for the data source?

Dave

-----Original Message----- From: Steve McAuliffe [mailto:Steve.Mcauliffe@NATWEST.COM] Sent: Tuesday, October 09, 2001 6:37 AM To: DB2-L@RYCI.COM Subject: DB2 Connect question



Hello there,

Possible stupid question, but being new to connect here it comes anyway:

I have DB2 connect enterprise edition installed on my PC, and am using TCP/IP to connect to two OS/390 V6 DB2 subsystems. [...] 7861 44 20_DB2 Connect Question14_Philip Gunning24_philip.gunning@QUEST.COM30_Tue, 9 Oct 2001 09:32:54 -0700159_- Steve, Do you have the Management tools FMID installed and enabled on both subsystems? Can you connect to the second subsystem via a command window? HTH Phil 7906 151 24_Re: DB2 Connect question0_23_Sheila_White@AMSINC.COM30_Tue, 9 Oct 2001 13:11:36 -0400586_us-ascii Were the packages bound on the second system?



|--------+-----------------------------> | | Steve McAuliffe | | | | | | | | | 10/09/2001 10:25 AM| | | Please respond to | | | DB2 Data Base | | | Discussion List | | | | |--------+-----------------------------> >----------------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Sheila White/AMS/AMSINC) | | Subject: Re: DB2 Connect question | >----------------------------------------------------------------------------| [...] 8058 25 43_Stopping Connections for Offline UDB Backup10_Rudy Betke15_rite2me@USA.COM30_Tue, 9 Oct 2001 12:48:34 -0500566_- We are running UDB v6.1 on Sun Solaris.

Some of our backups are offline, which gets executed through a cron script. Within the script we; 1). stop database manager force, 2). start database manager, then 3). backup database XYZ...

Most of the time the backup is successful. However, occasionally, it will fail. The reason being, is that before the backup command has a chance to kick in, some remote connection has snuck in and grabed a hold of the database. Therefore, the offline backup fails with "SQL1035N The database is currently in use." [...] 8084 40 27_Re: db2 os/390 rexx support17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Tue, 9 Oct 2001 16:20:47 -0300304_iso-8859-1 DSNREXX uses dynamic SQL, and CONNECT (type 1 or 2 ) are only allowed on static, precompiled SQL. The way to access a second subsystem is using private protocol with 3part names or alias. This is true for version 5, I don´t know if it has changed in newer versions, but I don´t think so. [...] 8125 107 32_Re: Workfile Database Bufferpool14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Tue, 9 Oct 2001 14:28:29 -0500505_iso-8859-1 In certain situations, there can be a large amount of random access in DSNDB07, due to several different things. The most common is sort merge phases, where I can seen DSNDB07 as high as 55% random during these actives. There are many others but they are small, like the sparse index page access, etc. This is not something to worry about in general, but could mean that your sort work area is perhaps too small. Answers are difficult here, without look at reports from performance tools. [...] 8233 77 36_Re: Version of UDB from Backup image15_Rajesh Udandrao17_urajes1@YAHOO.COM30_Tue, 9 Oct 2001 12:31:22 -0700537_us-ascii Hi Gert I used db2ckbkp utility, but I did not find any version of the database from the backup image. I am getting the same relid for both udb v6 and v7

Thanks Rajesh

--- Gert van der Kooij wrote: > Hi, > With DB2 V7 the db2ckbkp utility is available. With > the -H option it only > shows the media header information which also > contains the the Release ID. I > don't know if the tool can be used on backups from > previous releases. > > Hope this helps. > > ----- Original Message ----- [...] 8311 21 31_DFSMS Current copy image copies13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM30_Tue, 9 Oct 2001 14:52:26 -0500335_- Listers -

Is anyone using concurrent copy image copies? I am trying to help a large erp application "reset" their test environment back to a previous point-in-time. We cannot use volume level snaps because the data is not on isolated volumes. Will concurrent copy speed the back-up and recovery process? Any other ideas? [...] 8333 96 11_SQL Problem24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Tue, 9 Oct 2001 12:59:56 -0700368_- Hi Guys!

I'm trying to create a summary type of SQL statement to produce output like the following:

The output should look like: OMPO# Last Change By CHG Date Labor_Lines Labor_WA Labor_Class Parts_Lines Parts_FC Parts_Class 289892 LRG 08/16/01 3 1 2 4 2 2

The original SQL consists of several statements and produces the following output: [...] 8430 41 54_DB2 DBA wanted for perm. opportunity in South Florida!8_Jon Bass14_jbass@TSRC.NET30_Tue, 9 Oct 2001 16:07:55 -0500393_- Our client is has an excellent PERMANENT opportunity in SOUTH FLORIDA for a DB2 DBA with PEOPLESOFT experience. Our client is a stable, profitable, expanding international retail corporation. Below is a description of the position along with my contact information. If you are interested in this opportunity, please email me at jbass@tsrc.net, or call me at 1-800-229- 8324, ext. 2308. [...] 8472 131 15_Re: SQL Problem16_Pacocha, Michael24_Michael.Pacocha@MBNA.COM30_Tue, 9 Oct 2001 17:49:33 -0400580_iso-8859-1 Ran out of time to fit in the distinct but here's a start. You probably want to do the labor and the parts separate with a UNION. Hope this helps. Try something along these lines:

SELECT SUBSTR(J1.ORRONUM,2,6) AS "OMPO #" ,J1.ORAPRID AS "LAST CHANGED BY" ,J1.ORAPRDT AS "CHG DATE" ,J2.LABOR_LINES AS "LABOR LINES" ,J2.PART_LINES AS "PART LINES" FROM TB087 J1, (SELECT SUM(CASE WHEN IRTTYPE = 'W' THEN 1 END) AS LABOR_LINES ,SUM(CASE WHEN IRTTYPE = 'P' THEN 1 END) AS PART_LINES FROM TB088) AS J2 WHERE COMPANY = 'W' AND ORSTAT = 'P' AND ORRONUM = 'P289892' [...] 8604 163 35_Re: DFSMS Current copy image copies17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Tue, 9 Oct 2001 19:07:30 -0300306_iso-8859-1 John,

I´m at DFSMS 1.4 and I don´t know if there´s something new in the newer SMS versions, but all concurrent copy will do is to release the source dataset very early in DFDSS step, but COPY FULL will take exactly the same time to run. I don´t see how it can speed up RECOVER also. [...] 8768 124 14_Re: Db2 Insert13_Abi Monoharan23_abi@PROTEOMESYSTEMS.COM31_Wed, 10 Oct 2001 08:01:04 +1000366_us-ascii For my understanding of DB2, Alter doesn't work for changing a numeric field to char field or vice versa. I am using DB2 7.1 + fix pack 3 in AIX 5.1 environment. The auto increment field is very important because it is a primary key for that table and used in another tables to make relationships. I am not sure DSN1COPY is a tool in this environment? [...] 8893 194 25_SIRDUG Meeting 10/18/200113_BL.Tink Tysor16_tink@BLTYSOR.COM30_Tue, 9 Oct 2001 16:26:50 -0700723_us-ascii SIRDUG Southeastern Inter-Relational Database Users' Group P.O. BOX 34641 Charlotte, NC 28234 Email: SIRDUG@VNET.NET Information/Message Line: (704) 556-6621

SIRDUG meeting Announcement WHEN? Thursday, October 18 WHERE? IBM Harris Blvd facility (Charlotte, NC) See directions below COST? $25 cash or check, in advance or at the door. =========================== We are delighted to announce that the October 18 SIRDUG meeting will be a double-header, with presentations by Willie Favero of IBM and Mike Sniezek of BMC. In the morning, Willie will address mainframe DB2 performance issues in "DB2 Performance for Everyone." In the afternoon, Mike will offer two resentations, "Database Web Integration" [...] 9088 111 14_Re: Db2 Insert13_Abi Monoharan23_abi@PROTEOMESYSTEMS.COM31_Wed, 10 Oct 2001 09:01:16 +1000390_us-ascii Hi Mathew, It worked fine when I used your load command like this:

Input file : t.txt 3|axe 4|bxe 5|dxe 7|fxe

DB2=>load from "t.txt" of del modified by coldel| IDENTITYOVERRIDE NOHEADER MESSAGES "t.msg" insert into xx NONRECOVERABLEN

But after I insert this into that table I try to insert another row by using SQL : insert into xx (name) values ("abc" ) [...] 9200 66 47_Re: Stopping Connections for Offline UDB Backup14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Wed, 10 Oct 2001 10:26:09 +1000380_iso-8859-1 Wouldn't START ACCESS(UT) be nice - but in the absence of such useful commands, try:

1. REVOKE CONNECT FROM....whatever userids, groups & PUBLIC 2. stop database manager force, 3. start database manager, then 4. backup database XYZ... and finally 5. GRANT CONNECT TO....whatever groups & PUBLIC

If they can't connect, they can't get in the way... :) [...] 9267 104 19_Re: Extent Question18_Michael L McCawley20_mike_dba@HOTMAIL.COM31_Wed, 10 Oct 2001 01:23:48 +0000622_- Phil -

It is still possible to determine the number of primary and secondary extents allocated to a VSAM file. More experienced SysProgs out there can correct my limited understanding of the matter, but I'll try to explain the jist of allocations.

With current versions of IBM's Data Facility product, the primary allocation can be created in one to five individual data segments (extents, which have separate VTOC entries). DFP will try to allocate the primary allocation in a single contiguous extent, but if that much space is not available, it will attempt to split the allocation up into smaller [...] 9372 23 44_Pamela Harris/IT/SEARS is out of the office.0_17_pharri1@SEARS.COM31_Wed, 10 Oct 2001 01:19:45 -0500619_us-ascii I will be out of the office from 10/10/2001 until 10/11/2001.

I will not be checking voice-mail.

Please read carefully for who to page:

For DB2 page page, 1-800-759-8888, PIN # 7270526

For Informix/Sqlserver/Oracle page 1-800-759-8888, PIN # 7270525,

For Network Infrastructure, page 1-800-759-8352, PINs # 7270063, 7270065, 727-1038

================================================ 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. 9396 35 37_WHITE PAPERS ON BULL TO IBM MIGRATION17_Reddy, Sreenivasa34_sreenivasa.reddy@BLR.HPSGLOBAL.COM31_Wed, 10 Oct 2001 11:52:35 +0500358_- Hi Everybody,

Could anyone send me the white papers or related sites on BULL to IBM Migration.



Thanks & Regards,

Sreenivas P Reddi, Mobility Mainframe Support, HCL Perot Systems, Bangalore. Call me @ +91-80-6547198 ext 328/435

What lies ahead of u and what lies behind u is nothing compared to what lies within you [...] 9432 12 11_Unsubscribe6_rizwan29_rizwan@CLOVERTECHNOLOGIES.COM31_Wed, 10 Oct 2001 00:04:10 +0530253_iso-8859-1

================================================ 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. 9445 40 41_Re: WHITE PAPERS ON BULL TO IBM MIGRATION18_Adrian P Challinor29_adrian.challinor@OSIRIS.CO.UK31_Wed, 10 Oct 2001 08:09:33 +0100320_iso-8859-1 Sreenivas

You need to be a touch more specific. Are you talking about a GCOS Bull system on a DPS system, or an Escala system on PowerPC running AIX?

And which of IBM's systems are you migrating to:

Z/OS OS/390 AIX AS/400 Windows on Netfinity Linux - on Netfinity Linux - Under z/OS [...] 9486 53 18_Re: SQLCODE = -33229_=?ISO-8859-1?Q?=D6=A3=ED=B5?=15_zhm@ACCA.COM.CN31_Wed, 10 Oct 2001 15:13:18 +0800643_GB2312 Hi,eric

Yes, we also want to use DRDA instead of PRIVATE. But when I connect to by tcpip, it got sql code= "-30082, ERROR: CONNECTION FAILED FOR SECURITY REASON 17 (UNSUPPORTED FUNCTION)"



>We had this. We opened an ETR with IBM. No solution. >We circumvented by using DRDA instead of PRIVATE. > >Regards, >eric pearson >NS ITO Database Support > > >-----Original Message----- >From: Zheng Miao [mailto:zhm@ACCA.COM.CN] >Sent: Monday, October 08, 2001 10:15 PM >To: DB2-L@RYCI.COM >Subject: SQLCODE = -332 > > >Hi,all > I want to connect two db2 for os/390 using db2 private protocol. One >db2's location name [...] 9540 50 47_Re: Stopping Connections for Offline UDB Backup12_Robert Mills30_robert.mills@CENTRELINK.GOV.AU31_Wed, 10 Oct 2001 17:56:54 +1000449_us-ascii Hi Rudy,

We have exactly the same problem here with connections sneaking back in after we force them off with the Force Applications command. I have been working on a script which kills individual connections to the database to be backed up and then connects to the database with Mode Exclusive. The script loops around until it gets the exclusive connection and then we are free to run the offline backup without interruption. [...] 9591 34 27_Re: db2 os/390 rexx support14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 10 Oct 2001 05:04:44 -0500363_ISO-8859-1 Walter

Your first sentence is true, but OPEN is also only allowed in static SQL. How then can you OPEN your cursors? Think through the implications of the answer to that question on your second sentence.

Where I have a real question is "is V5 DSNREXX precompiled with connect type 2"? I don't have access to a V5 system to find out. [...] 9626 61 47_Re: Stopping Connections for Offline UDB Backup18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 10 Oct 2001 06:06:55 -0400338_- Our offline backup script performs a db2 list applications command twice, once to force initial connections and then again to make sure the connections have been forced off. If not, it reexecutes the force command. Then, it does the offline backup. The backup command usually exeuctes quick enough before new connections are made. [...] 9688 146 62_Re: Strange Results Using Round Function With Decimal Function22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 10 Oct 2001 15:29:51 +0530640_- Greg,

I tried all the queries in SPUFI only. I found that the results you have mentioned for SPUFI in queries 2 and 3 are incorrect. The result which i am getting in SPUFI is same as what you have mentioned in embedded cobol.

Regards, Sanjeev

> -----Original Message----- > From: Greg Helson [SMTP:Greg.Helson@TRICON-YUM.COM] > Sent: Tuesday, October 09, 2001 7:32 PM > To: DB2-L@RYCI.COM > Subject: Strange Results Using Round Function With Decimal Function > > Hello SQL Gurus, > > We are experiencing inconsistencies between dynamic executions and > static executions of queries nesting the round and decimal [...] 9835 16 28_contents of skeleton tables?18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Wed, 10 Oct 2001 05:13:23 -0500480_- Hi all,

Admin guide says skeleton cursor table space contains internal form of SQL stmt contained in an application. What exactly is this 'internal form' and how is it used by the application. Thanks in advance.

-rajendra.

================================================ 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. 9852 31 14_Re: Db2 Insert14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 10 Oct 2001 05:25:39 -0500598_- Abi

1) If you read the release notes for FP3, you'll find a new feature - SEQUENCEs. You might find them better than IDENTITY columns.

2) You are correct - DSN1COPY is for an OS/390 / z/OS environment.

James Campbell

On Wed, 10 Oct 2001 08:01:04 +1000, Abi Monoharan wrote:

>For my understanding of DB2, Alter doesn't work for changing a numeric >field to char field or vice versa. I am using DB2 7.1 + fix pack 3 in >AIX 5.1 environment. The auto increment field is very important because >it is a primary key for that table and [...] 9884 58 32_Re: contents of skeleton tables?22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 10 Oct 2001 16:06:52 +0530700_- As i understand, Internal form is executable.

Regards, Sanjeev

> -----Original Message----- > From: Rajendra Deshpande [SMTP:rajendra_deshpande@HOTMAIL.COM] > Sent: Wednesday, October 10, 2001 3:43 PM > To: DB2-L@RYCI.COM > Subject: contents of skeleton tables? > > Hi all, > > Admin guide says skeleton cursor table space contains internal form of SQL > stmt contained in an application. What exactly is this 'internal form' and > how is it used by the application. Thanks in advance. > > -rajendra. > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. [...] 9943 20 47_Re: Stopping Connections for Offline UDB Backup14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 10 Oct 2001 05:33:45 -0500559_- Personally, I'ld prefer START ID(sqlid) - only connections with the named id could access the database. (DBADM can still stop and start)

James Campbell

On Wed, 10 Oct 2001 10:26:09 +1000, Palgrave, Greg wrote:

>Wouldn't START ACCESS(UT) be nice -



================================================ 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. 9964 19 15_Re: Unsubscribe9_Ben Relle27_Ben.Relle@MORGANSTANLEY.COM31_Wed, 10 Oct 2001 10:12:27 +0100520_us-ascii No

rizwan wrote:

> ================================================ > 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. 9984 91 41_Re: WHITE PAPERS ON BULL TO IBM MIGRATION17_Reddy, Sreenivasa34_sreenivasa.reddy@BLR.HPSGLOBAL.COM31_Wed, 10 Oct 2001 16:34:42 +0500386_- Hi Adrian,

the details are as follows



Architecture BULL IBM O/S GCOS 4503O OS390 Release 2.8 H/W Titan 892 9672-R95 On-line processor TP8 CICS, IDMS Database IDS II VSAM, IDMS Language COBOL 74 COBOL OS/390 2.2 On-line transaction utility Gen-a-screen ADSO Job scheduling CA7 (resides on the IBM) CA7 Adhoc queries/reports LOUIS, BETTER On-line Query (OLQ) [...] 10076 88 19_Re: Extent Question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 10 Oct 2001 06:10:42 -0500382_- Michael,

"Most people seldom see 123 extents, only 119. That is because the first five extent entries are reserved solely for the primary extents." No. The reason that few people see more than 119 extents is that VSAM will/would not accept an allocation if there are/were 119, or more, extents. The number of extents required for the primary allocation is irrelevant. [...] 10165 32 41_Re: WHITE PAPERS ON BULL TO IBM MIGRATION18_Adrian P Challinor29_adrian.challinor@OSIRIS.CO.UK31_Wed, 10 Oct 2001 12:27:55 +0100340_iso-8859-1 Sreenivasa

Then, sorry, I cant help you. This is really a question for the OS/390 people on the list.

I would suggest, however, that this sounds like a fairly major exercise, especially if you are going from IDS to VSAM,IDMS; and from one TP to another. Someone may know of some automated translations system. [...] 10198 35 62_Re: Strange Results Using Round Function With Decimal Function18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Wed, 10 Oct 2001 06:51:32 -0500893_- Hi Greg,

Here are SPUFI results....matching your embedded SQL results! Wonder how your SPUFI results vary.

SELECT COALESCE(ROUND((((DEC(.37,11,3) - (.41)) / (.41))*100),2),0) FROM SYSIBM.SYSDUMMY1; ---------+---------+---------+---------+---------+---------+-------

---------+---------+---------+---------+---------+---------+------- -9.7500 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+------- SELECT COALESCE(ROUND((((DEC(.37,11,3) - DEC(.41,11,3)) / DEC(.41,11,3))*100),2),0) FROM SYSIBM.SYSDUMMY1; ---------+---------+---------+---------+---------+---------+-------

---------+---------+---------+---------+---------+---------+------- -9.700 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 [...] 10234 159 57_Re: Strange Results Using Round Function With Decimal Fun12_Helson, Greg26_Greg.Helson@TRICON-YUM.COM31_Wed, 10 Oct 2001 07:57:00 -0500402_ISO-8859-1 Sanjeev,

Thanks for running the queries and reporting your results. I re-ran the queries this morning in SPUFI, in DSNTEP2, and embedded in the COBOL program; the results are the same as reported yesterday. Since your results match the output from embedded COBOL, I'm going to start investigating environmental differences between our dynamic environment and static environment. [...] 10394 210 57_Re: Strange Results Using Round Function With Decimal Fun22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 10 Oct 2001 17:51:13 +0530629_iso-8859-1 Yes Greg, looks like some setting in the dynamic env. which causes this difference. I would request you to share the difference with the list so that we all can be aware of this.

Regards, Sanjeev

> -----Original Message----- > From: Helson, Greg [SMTP:Greg.Helson@Tricon-Yum.Com] > Sent: Wednesday, October 10, 2001 6:27 PM > To: "DB2-L@RYCI.COM" ; "S; Sanjeev (Cognizant)" > Subject: RE: Strange Results Using Round Function With Decimal Fun > > Sanjeev, > > Thanks for running the queries and reporting your results. I re-ran > the queries this morning in SPUFI, in DSNTEP2, and embedded in the > [...] 10605 11 62_Re: Strange Results Using Round Function With Decimal Function18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Wed, 10 Oct 2001 07:18:44 -0500335_- Btw,often wonder why a successful execution in SPUFI has SQLCODE 100. Intriguing, isn't it.

================================================ 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. 10617 84 62_Re: Strange Results Using Round Function With Decimal Function22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 10 Oct 2001 17:59:12 +0530304_- Running the query in SPUFI is dynamic and presumed as returning more than a row. So, SQLCODE of 100 reflects there is no more records. I am sure you are aware of SQLCODE 100 has 2 meaning :

i) No records found ii) No more records found.(Used in embedded language to write the cursor logic) [...] 10702 13 24_Re: DB2 Connect question15_Steve McAuliffe27_steve.mcauliffe@NATWEST.COM31_Wed, 10 Oct 2001 07:29:28 -0500516_- thanks all, solved it, within the enablement code which was already applied to our libraries there is a bind job referenced in the documentation at http://www-4.ibm.com/software/data/db2/os390/cc390/ which creates stored procedures to populate the various tree structures.

================================================ 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. 10716 49 62_Re: Strange Results Using Round Function With Decimal Function14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Wed, 10 Oct 2001 14:40:07 +0200644_iso-8859-1 Rajendra,

not necessarily intriguing, SQLCODE +100 simply indicates that there are no MORE rows to be returned; sure thing, it also means that there is no row to be returned, so be aware of both meanings of SQLCODE +100.

Hth Ruediger

Rajendra Deshpande schrieb: > > Btw,often wonder why a successful execution in SPUFI has SQLCODE 100. > Intriguing, isn't it. > > ================================================ > 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. 10766 68 20_GENERATE_UNIQUE PROC15_Chris Van Daele33_Chris.Van.Daele@TOYOTA-EUROPE.COM31_Wed, 10 Oct 2001 14:44:47 +0200406_us-ascii Hi list,

there exists something like an SQL cookbook which is actually a very good overview of the SQL possibilities.

Somewhere in this book the procedure GENERATE_UNIQUE() is used. When I try to execute the following

select generate_unique() from sysibm.sysdummy1 I get an -440 sql error. So It could be that the proc is not on our system, but I can't find it anywhere. [...] 10835 100 18_Re: SQLCODE = -33216_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 10 Oct 2001 09:04:18 -0400439_gb2312 All the possibilities listed in the manual seem addressable via configuration changes - either in the DB2 CDB or in the client. Do you get a DSNL030I message on the OS/390 console? Its reason code may help.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: ÖŁíľ [mailto:zhm@ACCA.COM.CN] Sent: Wednesday, October 10, 2001 3:13 AM To: DB2-L@RYCI.COM Subject: Re: SQLCODE = -332 [...] 10936 228 57_Re: Strange Results Using Round Function With Decimal Fun12_Helson, Greg26_Greg.Helson@TRICON-YUM.COM31_Wed, 10 Oct 2001 11:03:00 -0500367_iso-8859-1 We have found the difference to be in the DECARTH settings. The dynamic environment is running with a decimal precision of 31 and the static environment is running with a decimal precision of 15. The cause is a bad copy of DSNHDECP.

I set the db2 precompiler option DEC(31) and re-ran. The cobol results were consistent with the spufi results. [...] 11165 193 57_Re: Strange Results Using Round Function With Decimal Fun0_23_William_OBlack@FFIC.COM31_Wed, 10 Oct 2001 07:24:01 -0700446_us-ascii Greg, I ran your queries in spufi and my results were exactly the same as yours. We're running DB2 V6. We're also running PeopleSoft and have implemented several DB2 mods affecting division scale which were needed by PeopleSoft.

Bill









"Helson, Greg" @RYCI.COM> on 10/10/2001 05:57:00 AM

Please respond to DB2 Data Base Discussion List [...] 11359 62 57_DB2 O390 SMS management of space allocation on Shark DASD20_Bustamente, Gerald G30_Gerald.Bustamente@LANDSEND.COM31_Wed, 10 Oct 2001 09:47:46 -0500186_iso-8859-1 I'm just curious what the current thinking is on this. If you have time to share pros and cons very much appreciated.

Thanks,

Jerry Bustamente Lands' End

11422 50 61_Re: DB2 O390 SMS management of space allocation on Shark DASD0_19_csutfin@AMSOUTH.COM31_Wed, 10 Oct 2001 11:07:16 -0500530_us-ascii Jerry

We have been using SMS to manage our DB2 datasets (application databases) on Shark Dasd from the beginning. with no problems so far. If you would like details you may contact me off-list.



Carol Sutfin Corporate DBA AmSouth Bank csutfin@amsouth.com (205)326-5214





"Bustamente, Gerald G" cc: Sent by: DB2 Data Base Subject: DB2 O390 SMS management of space Discussion List allocation on Shark DASD [...] 11473 28 12_SQL Question22_Augustine, Jobi M B22524_Jobi.Augustine@CIGNA.COM31_Wed, 10 Oct 2001 08:49:46 -0400317_us-ascii Hi List,

I want to see whether any last name with a space is loaded in one of our DB2 tables (V6 OS390). Is there any command in DB2 to check a space within a string other than leading and trailing spaces.

Thanks for any comments that you can give !!

Thanks and best regards, Jobi. [...] 11502 54 86_Re: SPUFI SQLCODE 100; was: Strange Results Using Round Function With Decimal Function0_18_mebert@AMADEUS.NET31_Wed, 10 Oct 2001 16:13:24 +0200362_us-ascii That occurs if you do a SELECT where the limit of lines output is NOT exceeded. SPUFI fetches rows in a loop; the attempted FETCH after the last row gets SQLCODE +100 (row not found), and this is the code that SPUFI reports for the entire SELECT.

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 11557 28 47_Dist. Application with Stored Procs and DB2 v6?14_Paul A Walters23_Paul.A.Walters@SLMA.COM31_Wed, 10 Oct 2001 12:27:56 -0400411_us-ascii We are currently migrating from DB2 V5 to DB2 V6 and DB2 Connect V7. Our DEVL and ACPT environments will need to support multiple versions of the same stored procedures.

We have been unsuccessful in getting multiple versions of the same stored procedure to work.

We had thought that a remote call with "user id#5" would use a path of PATH = "SYSIBM","SYSFUN","SYSPROC","user id #5". [...] 11586 50 16_Re: SQL Question13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 10 Oct 2001 11:24:14 -0500453_ISO-8859-1 Hi Jobi, you can use the HEX column function to identify the spaces. ex. select hex(column) from xxxxxx.xxxxx where ..... the spaces will show up as X'40' s. HTH Kurt

>>> Jobi.Augustine@CIGNA.COM 10/10/01 07:49AM >>> Hi List,

I want to see whether any last name with a space is loaded in one of our DB2 tables (V6 OS390). Is there any command in DB2 to check a space within a string other than leading and trailing spaces. [...] 11637 54 16_Re: SQL Question13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 10 Oct 2001 09:49:38 -0700512_iso-8859-1 SELECT * FROM Tbl WHERE STRIP(LASTNAME) LIKE X'6C406C'

X'6C' == %

Ashish.



-----Original Message----- From: Augustine, Jobi M B225 [mailto:Jobi.Augustine@CIGNA.COM] Sent: Wednesday, October 10, 2001 5:50 AM To: DB2-L@RYCI.COM Subject: SQL Question

Hi List,

I want to see whether any last name with a space is loaded in one of our DB2 tables (V6 OS390). Is there any command in DB2 to check a space within a string other than leading and trailing spaces. [...] 11692 69 16_Re: SQL Question13_Renjith Davis30_Renjith.Davis@MAIL.STATE.AR.US31_Wed, 10 Oct 2001 12:15:03 -0500544_iso-8859-1 SELECT * FROM Table WHERE LOCATE(' ', STRIP(LASTNAME,BOTH)) > 0

-----Original Message----- From: Mohan, Ashish [mailto:Ashish.Mohan@NIKE.COM] Sent: Wednesday, October 10, 2001 11:50 AM To: DB2-L@RYCI.COM Subject: Re: SQL Question



SELECT * FROM Tbl WHERE STRIP(LASTNAME) LIKE X'6C406C'

X'6C' == %

Ashish.



-----Original Message----- From: Augustine, Jobi M B225 [mailto:Jobi.Augustine@CIGNA.COM] Sent: Wednesday, October 10, 2001 5:50 AM To: DB2-L@RYCI.COM Subject: SQL Question [...] 11762 54 51_Re: Dist. Application with Stored Procs and DB2 v6?13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 10 Oct 2001 12:21:13 -0500435_US-ASCII Paul, have you looked at the CURRENTFUNCIONPATH special register ?. "This keyword is used as part of the process for resolving unqualified function references that may have been defined in a schema name other than the current user's schema. The order of the schema names determines the order in which the function names will be resolved. For more information on function resolution, refer to the SQL Reference." HTH Kurt [...] 11817 53 51_Re: Dist. Application with Stored Procs and DB2 v6?12_Myron Miller22_Myron.Miller@BMWNA.COM31_Wed, 10 Oct 2001 14:25:00 -0400381_iso-8859-1 Paul, How are you trying to access the multiple versions?

We have multiple versions in test and production and access them via qualified calls, TESTX1.SP1 TESTX2.SP1, etc. The qualifier is set via control files for JAVA and thru the qualifier for packages and COBOL programs. We bind each different version in a different package with a different qualifier. [...] 11871 125 22_Re: Online Performance13_Harry William27_harry_williamtk@HOTMAIL.COM31_Wed, 10 Oct 2001 18:46:29 +0000760_- Hi, Thanks for the reply.I have put the SQL, Expalin output & keys on the table.

SELECT T2.OPPOSITE_FLOW_IND, SUM(T1.REQ_INPUT_ENRG), SUM(T1.SCHD_INPUT_ENRG) FROM CTPTPN T1, NOMACT T2, MEASTN T3 WHERE T1.PRODUCTION_DT = ? AND T1.CONTR_ID = T2.CONTR_ID AND T1.ACTIVITY_NBR = T2.ACTIVITY_NBR AND T2.GAS_TRADE_IND = 'N' AND T2.INPUT_OUTPUT_IND = 'I' AND T2.INPUT_POINT_NBR = T3.MEAS_STATN_NBR AND EXISTS (SELECT 'Y' FROM MSDTLS T4 WHERE T3.MEAS_STATN_NBR = T4.MEAS_STATN_NBR AND T4.ACTIVE_IND = 'Y' AND T4.EFF_DT <= ? AND T4.EFF_DT = (SELECT MAX(T5.EFF_DT) FROM MSDTLS T5 WHERE T3.MEAS_STATN_NBR = T5.MEAS_STATN_NBR AND T5.EFF_DT <= ?) ) GROUP BY T2.OPPOSITE_FLOW_IND; Explain output:- QUERYNO QBLOCKNO APPLNAME PROGNAME PLANNO METHOD CREATOR TNAME [...] 11997 101 51_Re: Dist. Application with Stored Procs and DB2 v6?14_Paul A Walters23_Paul.A.Walters@SLMA.COM31_Wed, 10 Oct 2001 14:56:29 -0400398_us-ascii We are not qualifying the calls. So the code is CALL SPX not CALL SCHEMA.SPX. I don't believe the application staff will be willing to change the code.

With V5 we were using the AUTHID to control which version/WLM environment was used. User ID#1 when it called SPX would run in the WLM environment X because the sysprocedure entry was: Procedure Authid WLM_ENV SPX User ID#1 X [...] 12099 32 30_Basic question regarding Views14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Wed, 10 Oct 2001 15:01:39 -0400562_iso-8859-1 Hi Listers,

I have a question regarding Views.

Is it true that after a View is created and then subsequently accessed, every time it is accessed, DB2 computes the result-set behind the view ?

Say, I have created a view with a complex SELECT sql. If I try to execute just the SELECT directly, it takes around 30 seconds. Now, I create the view using the SELECT sql. The view is based on a large table (about 500K rows). The table has a PK (say, doc_id), which is also part of the SELECT in the view definition. Now, after the [...] 12132 101 60_Re: DB2 O390 SMS management of space allocation on Shark DAS10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Wed, 10 Oct 2001 15:09:52 -0400577_iso-8859-1 Jerry,

There are numerous questions and replies in the archives. I have been using SMS to manage the placements of DB2 objects for years and have never had a problem. There is one point that I want to bring up and that is when talking to the SMS Admin group make sure they do NOT define your object DASD allocations as migratable. Currently in our testing environment some of the small reference tables were allocated as migratable and every so once in awhile I have to perform a HRECALL on all migrated tablespaces and indexes because DB2 will NOT wait [...] 12234 32 34_Re: Basic question regarding Views18_Adrian P Challinor29_adrian.challinor@OSIRIS.CO.UK31_Wed, 10 Oct 2001 20:19:52 +0100333_iso-8859-1 Why are views faster than SQL Query?

One part of the performance gain will come from the fact that DB2 will know the access path for the view - it will compute the executable form when the view is defined. This compares with the SQL query, where DB2 has to compile the query and then work out the access path. [...] 12267 82 34_Re: Basic question regarding Views0_19_Tim.Lowe@STPAUL.COM31_Wed, 10 Oct 2001 14:42:33 -0500379_us-ascii Abhijit, From what I understand, sometimes DB2 on OS/390 uses "view materialization", materializing the result of the view into the temporary database immediately before processing the query. At other times, it can use "view merge", merging the query and the view together before accessing the data. The simpler the view, the more likely that "view merge" is used. [...] 12350 75 40_Basic questions regarding DataPropogator14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Wed, 10 Oct 2001 13:47:25 -0700294_iso-8859-1 HI all,

I would like to talk with someone, offline about DataPropagator operation.

My phone #'s are listed below.

Thanks,

Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719



12426 62 36_Re: varying commit freq dynamically.0_16_admin@RELARC.COM31_Wed, 10 Oct 2001 17:20:07 -0400413_US-ASCII Hi Rajendra,

We have a product that allows you to change commit frequency dynamically.

The product is Smart/RESTART.

Smart/RESTART affords you to control commit frequency based on commit count or elapsed time.

Furthermore, if your program does not have any commits, Smart/RESTART can issue the SQL commit externally to your program based on various application criteria. [...] 12489 32 24_db2 connect/long varchar10_Shery Hepp17_schepp@SRPNET.COM31_Wed, 10 Oct 2001 16:09:29 -0700402_iso-8859-1 We have a powerbuilder app that connects to db2 v6 os/390 using db2 connect 6.1 fixpack 7. When the application tries to issue an update to a long varchar column and the update will cause the column length extend beyond 255 we get the following error -102 literal string is too long The update works if the column length is under 255 even though the column is defined to allow for 500. [...] 12522 54 51_Re: Dist. Application with Stored Procs and DB2 v6?10_Shery Hepp17_schepp@SRPNET.COM31_Wed, 10 Oct 2001 16:20:43 -0700409_iso-8859-1 Hi Paul- I worked on this for months- finally IBM helped me resolve the issues- I'm assuming that you have your stored procedures defined within different (user defined) schemas. Verify that the db2cli.ini file in db2 connect has the CURRENTFUNCTIONPATH set to the user id (stored proc schema) you're executing under. Also the current sqlid will need execute on the procedure not the package. [...] 12577 67 51_Re: Dist. Application with Stored Procs and DB2 v6?10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 11 Oct 2001 14:06:20 +1000372_us-ascii Paul,

We migrated to V6 a few months back, and still use Connect v5.2 (I think with FP14). We added the CURRENTFUNCTIONPATH parameter to the connection string in the application. We pick our connect string from an external source(like the Windows Registry settings), so we didnt actually need to change the code itself. (also tested under Connect v7) [...] 12645 49 24_Re: GENERATE_UNIQUE PROC10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 11 Oct 2001 14:08:25 +1000497_us-ascii Chris,

I suspect GENERATE_UNIQUE is available in UDB for Windows, but on OS/390. U cd check the SQL reference for UDB/Windows.

Cheers, Kals



-----Original Message----- From: Chris Van Daele [SMTP:Chris.Van.Daele@TOYOTA-EUROPE.COM] Sent: Wednesday, October 10, 2001 10:45 PM To: DB2-L@RYCI.COM Subject: GENERATE_UNIQUE PROC

Hi list,

there exists something like an SQL cookbook which is actually a very good overview of the SQL possibilities. [...] 12695 100 24_Re: GENERATE_UNIQUE PROC18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 11 Oct 2001 10:07:43 +0530280_- Hi Chris,

I haven't tried GENERATE_UNIQUE but there is another function called RANDOM, used as RAND() in SQL, which could be of some use to you. It is known to generate 32k distinct values between 0 & 1. More info on this can be found in SQL cook book for ver 7.1 . [...] 12796 16 67_Re: Dynamic rule Bind vs Dynamic Rule Run - impact on access path ?15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 11 Oct 2001 02:08:56 -0500501_- Hello Eric

As far as I know these parameters only affect the authorization checking for dynamic SQLs. RUN means authorization checking is done against the SQLID associated to the plan using this SQL and BIND means checking is done against the binder.

================================================ 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. 12813 25 19_SIGNOFF * (NETWIDE)6_Jim C.17_Jimluxpax@AOL.COM29_Thu, 11 Oct 2001 03:20:27 EDT28_US-ASCII SIGNOFF * (NETWIDE) 12839 19 36_William Poston is out of the office.14_William Poston21_william.poston@DB.COM31_Thu, 11 Oct 2001 02:48:40 -0600139_us-ascii I will be out of the office from 10/11/2001 until 11/12/2001.

I will respond to your message when I return.



12859 65 67_Re: Dynamic rule Bind vs Dynamic Rule Run - impact on access path ?22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Oct 2001 13:49:37 +0530633_- Eric,

In addition to what Walter has mentioned, DYNAMICRULES however is not related to accesspath determination but the same query may take more time because of additional authorization check work at the run time. But it may be very minimal.

Regards, Sanjeev

> -----Original Message----- > From: Walter Janissen [SMTP:walter.janissen@VICTORIA.DE] > Sent: Thursday, October 11, 2001 12:39 PM > To: DB2-L@RYCI.COM > Subject: Re: Dynamic rule Bind vs Dynamic Rule Run - impact on access > path ? > > Hello Eric > > As far as I know these parameters only affect the authorization checking > for dynamic SQLs. [...] 12925 184 22_Re: Online Performance22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Oct 2001 13:57:25 +0530563_iso-8859-1 Harry,

EXPLAIN with the parameter markers looks OK to me. The important thing as per your first mail which needs to be checked is, is the explain same for both the literals i.e. date before 01.01.2000 and after that. I presume the EXPLAIN for both will be same. So, the run time behaviour of list prefetch might be the cause of concern which needs to be looked at. If the number of qualifying RIDs cross a certain limit(25%), the list prefetch may be disabled and tablespace scan will be used. Can you use an online monitor to check that ? [...] 13110 70 47_Re: Stopping Connections for Offline UDB Backup15_Julie Lundrigan16_jlundri2@CSC.COM31_Thu, 11 Oct 2001 03:52:24 -0500607_- On Tue, 9 Oct 2001 12:48:34 -0500, Rudy Betke wrote:

>We are running UDB v6.1 on Sun Solaris. > >Some of our backups are offline, which gets executed through a cron >script. Within the script we; 1). stop database manager force, 2). start >database manager, then 3). backup database XYZ... > >Most of the time the backup is successful. However, occasionally, it will >fail. The reason being, is that before the backup command has a chance to >kick in, some remote connection has snuck in and grabed a hold of the >database. Therefore, the offline backup fails with "SQL1035N [...] 13181 63 43_!Candle Explain vs. SQLERRD4 timeron values14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Thu, 11 Oct 2001 11:40:55 +0100583_iso-8859-1 Hi, listers.

In the past, to get estimated statement cost timerons, I've used the hidden explain tables (although speaking to Roger at the DB2 Symposium in London in '98 put me off), and currently use !Candle DB/Explain (a wonderful tool which has saved us untold thousands of CPU pounds in performance improvements). I now have my grubby paws on DSNREXX, which, I am told, on an EXECSQL PREPARE, pops the timeron value for the statement into SQLERRD4. I've played about with it, but consistently get different values from !Candle DB/Explain's Statement Cost. [...] 13245 51 37_Stored procedure error while running.10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 11 Oct 2001 17:29:57 +0530608_iso-8859-1 Hi All,

When we compile stored procedures,after that then bind them then run them.

we get -991 sqlcode for the first query itself in the stored procedure.

The description for -991 is as follows :

-991 CALL ATTACH WAS UNABLE TO ESTABLISH AN IMPLICIT CONNECT OR OPEN TO DB2. RC1= rc1 RC2= rc2 Explanation: Call attach attempted to perform an implicit connect and open as the result of an SQL statement. The connect or open failed with the returned values. rc1 The value returned in FRBRC1 for the failed CONNECT or OPEN request. rc2 The value returned in FRBRC2 for [...] 13297 22 54_LEFT OUTER JOIN IMPACT WHEN NO DATA IN LEFT SIDE TABLE8_duam lee20_duam_lee@HOTMAIL.COM31_Thu, 11 Oct 2001 12:07:01 +0000481_- Hello All, I have a doubt. While executing a left outer join the sysetm hanged for i hour and I could not get any result. When I realised the situatioin I asked the programmer. They have deleted all the data from table which is at left side(outer table). The right table has 50000 rows. Could any body tell me the reason ? Is it due to left table being empty. I had the runstat ran recently. Also the query is a big query. The joins are on indexes. Could experts pitch in ? [...] 13320 50 36_Re: varying commit freq dynamically.11_Joe Luthman22_jluthma@BGNET.BGSU.EDU31_Thu, 11 Oct 2001 08:27:34 -0400464_us-ascii Hello Eric, Following an article written by Bonnie Baker in spring 199? of DB2 magazine, we implemented a subroutine that can dynamically change the commit frequency of any program using the subroutine. We created a table using jobname/pgmname as a key, and with columns for prime-time (when CICS is up) and non-prime-time commit frequency. The subroutine follows this strategy after each bump of a counter that reaches the current commit frequency. [...] 13371 74 58_Re: LEFT OUTER JOIN IMPACT WHEN NO DATA IN LEFT SIDE TABLE22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 11 Oct 2001 18:02:54 +0530462_- Duam,

Did your query failed with some SQLCODE/REASONCODE? Did you cancel it or it gave results after long time? If your RUNSTAT is current, does the CARD in SYSTABLES have value zero? I believe there is no issue that if the table is blank then it should take more time. I think it will take no time. There must be some complicacy in the query it self, it seems. It would be good if you can pass the query/EXPLAIN and relevant runstat informations. [...] 13446 70 36_Re: varying commit freq dynamically.16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 11 Oct 2001 08:28:42 -0400718_iso-8859-1 That is a great idea. I will pursue it. Of course, I anticipate that our anti-commit programmers will object if they hear of it.

Regards, eric pearson





-----Original Message----- From: Joe Luthman [mailto:jluthma@BGNET.BGSU.EDU] Sent: Thursday, October 11, 2001 8:28 AM To: DB2-L@RYCI.COM Subject: Re: varying commit freq dynamically.



Hello Eric, Following an article written by Bonnie Baker in spring 199? of DB2 magazine, we implemented a subroutine that can dynamically change the commit frequency of any program using the subroutine. We created a table using jobname/pgmname as a key, and with columns for prime-time (when CICS is up) and non-prime-time [...] 13517 24 39_Stored procedure calling a cics program16_Proctor, William25_william.proctor@TGSLC.ORG31_Thu, 11 Oct 2001 07:33:27 -0500465_iso-8859-1 Good Morning, Thanks for all reply's in advance. We have a cics program that updates a db2 table that has a trigger on insert that calls a stored procedure that updates another db2 table and then attempts to call a cics program that updates another db. I am having trouble getting the stored procedure to call the cics program. Does anyone have any examples of a stored procedure calling a cics program. Any suggestions will be greatly appreciated. [...] 13542 17 24_DB2 - IN SQL CALL Status13_Lourdes Kishi14_lkishi@BKB.COM31_Thu, 11 Oct 2001 07:45:04 -0500322_- Hi List,

In our production DB2 in each 10 minutes the threads is becoming in IN SQL CALL status, by about 10 seconds. Looking in the OmegamonDB2 reports the elapsed time is high and the cpu time is ok, but we couldn't identify where the thread is spending the time, I mean, there's nothing in wait time too. [...] 13560 18 23_Starting DB2 Admin Tool11_Daniel Adam16_dadam@GRATEX.COM31_Thu, 11 Oct 2001 07:47:31 -0500493_- Dear list, I'm trying to start freshly installed DB2 Admin Tool and it abends with: System abend code 0C1 Reason code 01

I tried to run clists ADB and ADBL, both end with same error. I'm sure it is something trivial, any idea ? Thanks, Daniel

================================================ 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. 13579 11 41_Re: Stored procedure error while running.15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 11 Oct 2001 07:55:40 -0500326_- Did you verify, that the stored procedure in question was link edited with DSNALI?

================================================ 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. 13591 66 41_Re: Stored procedure error while running.16_Proctor, William25_william.proctor@TGSLC.ORG31_Thu, 11 Oct 2001 08:06:35 -0500482_iso-8859-1 have you tried setting the package. ex. EXEC SQL SET CURRENT PACKAGESET = 'PSPSCOLL' END-EXEC.

-----Original Message----- From: praveen_kj [mailto:praveen_kj@INFY.COM] Sent: Thursday, October 11, 2001 7:00 AM To: DB2-L@RYCI.COM Subject: Stored procedure error while running.



Hi All,

When we compile stored procedures,after that then bind them then run them.

we get -991 sqlcode for the first query itself in the stored procedure. [...] 13658 82 28_Re: db2 connect/long varchar0_19_Tim.Lowe@STPAUL.COM31_Thu, 11 Oct 2001 08:25:20 -0500477_us-ascii Shery, There has always been a limit to the size of literals. (it used to be 254 characters, increased to 255 characters with a ptf to V5)

As I understand it, there are only 3 ways around this, static sql, dynamic sql that concatenates multiple 255-character literals together, or dynamic sql with parameter markers. Generally, parameter markers is not only the easist solution to this problem, but it is also extremely beneficial for dynamic sql caching. [...] 13741 41 43_Re: Stored procedure calling a cics program12_Myron Miller22_Myron.Miller@BMWNA.COM31_Thu, 11 Oct 2001 09:36:42 -0400469_iso-8859-1 Go look at PQ47595. Its a new function introduced to DB2 by IBM that provides a stored procedure that calls CICS. Plus the documentation for the APAR points you to the various IBM documentation on how to call CICS transactions from a stored procedure.

-----Original Message----- From: Proctor, William [mailto:william.proctor@TGSLC.ORG] Sent: Thursday, October 11, 2001 8:33 AM To: DB2-L@RYCI.COM Subject: Stored procedure calling a cics program [...] 13783 11 43_Re: Stored procedure calling a cics program15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 11 Oct 2001 08:33:22 -0500328_- Have you taken a look at http://www.db2mag.com/db_area/archives/1999/q4/db2dba.shtml

================================================ 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. 13795 34 21_8K & 16K buffer pools16_WOLFENDEN, Kevin36_Kevin.WOLFENDEN@READING.SEMA.SLB.COM31_Thu, 11 Oct 2001 14:47:21 +0100688_iso-8859-1 Is there an automated/easy way to find good candidates for the 8K & 16K buffer pools ? Currently, we have everything in 4K pools & one 'token' 32K pool. TIA.

Kevin Wolfenden DB2 Database Group > SchlumbergerSema > *: KWolfenden@slb.com >



___________________________________________________________________________ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, [...] 13830 141 15_Re: SQL Problem13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 11 Oct 2001 09:01:11 -0500672_iso-8859-1 Fritz,

I'm not sure what your join column is to TB088, so I'll simply expand on Michael's solution a little:

SELECT SUBSTR(J1.ORRONUM,2,6) AS "OMPO #" ,J1.ORAPRID AS "LAST CHANGED BY" ,J1.ORAPRDT AS "CHG DATE" ,J3.* FROM TB087 J1, (SELECT SUM(LABOR_LINES) AS LABOR_LINES, COUNT(DISTINCT LABOR_CLASS) AS LABOR_CLASS ,COUNT(DISTINCT WA) AS WA ,SUM(PART_LINES) AS PART_LINES, COUNT(DISTINCT PART_CLASS) AS PART_CLASS ,COUNT(DISTINCT FC) AS FC FROM (SELECT CASE WHEN IRTTYPE = 'W' THEN 1 END) AS LABOR_LINES ,CASE WHEN IRTTYPE = 'W' THEN IRPRTNO END) AS LABOR_CLASS ,CASE WHEN IRTTYPE = 'W' THEN IRFCWA END) AS WA ,CASE WHEN IRTTYPE = 'P' THEN 1 [...] 13972 75 34_Re: Basic question regarding Views13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 11 Oct 2001 09:01:15 -0500516_iso-8859-1 Adrian,

I'm not sure what views you are referring to, unless they are automatic summary tables.

I don't believe the question was why a view quicker than the SQL query, but in fact the reverse.

The answer is because DB2 will merge the view definition with the query against it. If the resultant query is more restrictive than the original view definition then the SQL query will outperform the original view. That's how the query can run sub-second and the view takes 30 seconds. [...] 14048 38 33_Stored Procedures vis Datajoiner.9_Colin Fay13_cfay2@CSC.COM31_Thu, 11 Oct 2001 10:15:36 -0400397_us-ascii Hi,

I am trying to call a stored procedure via Datajoiner(v 2.1.1 on NT) .

Using DB2Conect (v5) and OS390/DB2 v 6 and MSAccess98 as a front-end tester , I can connect directly (TCPIP) to the mainframe and execute the SP, sucessfully returning result sets .

If I try to access the same SP through Datajoiner using a stored procedure Nickname , I get this message [...] 14087 22 47_Re: !Candle Explain vs. SQLERRD4 timeron values0_34_matthias.wehrens@AMB-INFORMATIK.DE31_Thu, 11 Oct 2001 09:13:34 -0500382_- You have to consider that field SQLERRD(4) is defined as an integer but it is containing a float value (very strange). Therefore you have to make a transformation, for example in COBOL:

01 intvalue PIC s9(9) comp-4. 01 floatvalue Redefines intvalue Comp-2. 01 displayvalue Pic -Z(3).Z(3).Z(3).Z(3),9(2). ... move sqlerrd(4) to intvalue move floatvalue to displayvalue [...] 14110 153 28_Re: db2 connect/long varchar13_Andy Seuffert25_Andy.Seuffert@NEONSYS.COM31_Thu, 11 Oct 2001 09:36:57 -0500590_iso-8859-1 Shery, below is a response I provided for a DB2 Connect user using Visual Basic and ADO who had the same problem back in Feburary. The problem that you are encountering is that Powerbuilder is using the ODBC SQLExecDirect function without binding the Long VarChar parameter via a SQLBindParameter call. What needs to occur is that the parameter is bound as a host variable. This can be done quite easily in Visual Basic, but if you are using Powerbuilder Data Windows, PB itself generates the ODBC calls. If you are using PBScript you have more control over this and should [...] 14264 130 47_Re: !Candle Explain vs. SQLERRD4 timeron values16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Thu, 11 Oct 2001 15:37:04 +0100376_us-ascii Hi Marcus,

If you use your SQL embedded in a program, do not forget that SQLERRD4 contains float values - although it is an INT. Does your application logic take this into account?

Is the comparison with our !DB/Explain values done with this in mind?

If so, please feel free to contact me offline, to see how to log what might be a problem. [...] 14395 71 27_Re: Starting DB2 Admin Tool11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Thu, 11 Oct 2001 09:41:15 -0500582_us-ascii Turn on the trace in the CLIST and see what statement is being executed. If it is a loadmod call make sure they are linked correctly!... : )

0C1 System ABEND: S0C1 Description: An attempt was made to execute an invalid machine instruction operation code. The operation code is either invalid or is for an instruction that is not available on this CPU. This failure is usually due to a branch to an invalid storage location, as might occur in a load module with unresolved external references, or when a branch to an address outside of a program occurs. (Reason [...] 14467 41 28_DB2 System Code Page Change.17_Nicholson, Martin20_MNicholson@USSCO.COM31_Thu, 11 Oct 2001 09:48:58 -0500629_- We have OS/390 running with codepage 37 and DB2 running with codepage 500 (the default). Because the codepages are similar, this has not presented a problem. Now that our DB2 subsystem is being used as a database server more and more, this issue is becoming a problem.

There is an unsupported/non-recommended method for changing DB2 from codepage 500 to codepage 37. This change is accomplished by changing DSNZPARM variable SCCSID from 500 to 37, and then dropping and recreating/reloading any database/tablespace where SBCS_CCSID is not equal to 0 (zero). We have only a few new databases where SBCS_CCSID is not [...] 14509 61 47_Re: !Candle Explain vs. SQLERRD4 timeron values14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Thu, 11 Oct 2001 16:05:17 +0100514_iso-8859-1 Of course! Thanks.

Anyone know how to do it in Rexx?

Marcus

-----Original Message----- From: matthias.wehrens@AMB-INFORMATIK.DE [mailto:matthias.wehrens@AMB-INFORMATIK.DE] Sent: 11 October 2001 15:14 To: DB2-L@RYCI.COM Subject: Re: !Candle Explain vs. SQLERRD4 timeron values



You have to consider that field SQLERRD(4) is defined as an integer but it is containing a float value (very strange). Therefore you have to make a transformation, for example in COBOL: [...] 14571 76 67_Re: Dynamic rule Bind vs Dynamic Rule Run - impact on access path ?11_Eric Wilkin18_eric.wilkin@NBB.BE31_Thu, 11 Oct 2001 17:32:31 +0200303_iso-8859-1 Thanks for your answers. But what we see is a difference of 30 till 50% cpu less with dynamic rule run !! I agree with your position - and we dot understand such a large difference



Eric Wilkin National Bank of Belgium SYDS-DB tel +32 (0)2 221 45 99 fax +32 (0)2 221 30 92 [...] 14648 206 47_Re: !Candle Explain vs. SQLERRD4 timeron values16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Thu, 11 Oct 2001 16:46:54 +0100268_us-ascii Couldn't get less offended if I tried - I am grateful to mamma for my name ;-)

I think you'd have to use the C2D or D2C or whatever it is function, if you give me a moment I'll go and bury my head in the manuals and try to get back to you ASAP... [...] 14855 90 27_Re: Starting DB2 Admin Tool11_Daniel Adam16_dadam@GRATEX.COM31_Thu, 11 Oct 2001 11:02:24 -0500402_- In debug mode I can see that abend is caused by this: ISPEXEC SELECT PGM(ADB2SYS) PARM(/,DEBUG,EXITPAN,NONE)

Module adb2sys comes with the product - we didn't have to link it and I don't think we can. This is fresh installation, packages are bound, ispf libraries are set as described in DB2 Admin Tool User's Guide. There must be a rediculous mistake on my part, how could it not work ? [...] 14946 23 77_InDUG (Indiana DB2 Users Group) Fall Conference changed to Thursd ay, Nov 1st11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 11 Oct 2001 11:26:02 -0500575_iso-8859-1 For anyone interested in attending the InDUG fall conference, please note that it has been rescheduled for Thursday, November 1st. All other details remain the same.

Please see our web site (http://www.in-dug.org/) for more information on the conference.

Thanks,

Tina Hilton InDUG Membership Committee

================================================ 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. 14970 131 27_Re: Starting DB2 Admin Tool11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Thu, 11 Oct 2001 11:26:14 -0500414_us-ascii Can you compare this loadmod link parms from ISPF 3.4 on the library to the previous version and see how they stack up? Ours is as follows:

Name Prompt Alias-of Size TTR AC AM RM ADB2SYS 00004E28 01101E 00 31 ANY







Daniel Adam cc: Sent by: DB2 Subject: Re: Starting DB2 Admin Tool Data Base Discussion List [...] 15102 30 41_How does Oracle communicate with DB2/390?0_19_mike.holmans@BT.COM31_Thu, 11 Oct 2001 17:50:13 +0100400_iso-8859-1 I've got OS/390 2.10, and DB2 V5. I've enabled TCP/IP connections to my subsystem.

What does an Oracle user who hasn't got DB2 Connect do if he wants data from my subsystem? I haven't yet been able to locate an Oracle expert in my company who can say what Oracle has available, so I wondered if any of you who have to cope with Oracle trying to talk to you knew what they did. [...] 15133 156 27_Re: Starting DB2 Admin Tool12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 11 Oct 2001 13:10:35 -0400586_us-ascii Have you checked the lib where the loadmod comes out is APF auth or linklisted? Just a thought.







Jeff Faughn @RYCI.COM> on 10/11/2001 12:26:14 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: Starting DB2 Admin Tool



Can you compare this loadmod link parms from ISPF 3.4 on the library to the previous version and see how they stack up? Ours is as follows: [...] 15290 56 45_Re: How does Oracle communicate with DB2/390?17_Nicholson, Martin20_MNicholson@USSCO.COM31_Thu, 11 Oct 2001 12:11:01 -0500296_- I believe Oracle has a gateway (both DRDA and non-DRDA flavors). This would be required if you wish to join Oracle to DB2 data. However if you simply wish to access DB2 data from Unix, then I believe DB2 Connect would be a less costly solution.

Also consider the reverse direction: [...] 15347 65 45_Re: How does Oracle communicate with DB2/390?12_Chris Hoover28_ChrisHoover@SAFETY-KLEEN.COM31_Thu, 11 Oct 2001 13:18:03 -0400410_us-ascii I'm not sure how to do it on the DB2 side, but as far as Oracle is concerned you will most likely need to use the Oracle to DB2 gateway from Oracle. I believe that this is the preferred way from the Oracle side.

HTH,

Chris







mike.holmans@BT.COM@RYCI.COM> on 10/11/2001 12:50:13 PM

Please respond to DB2 Data Base Discussion List [...] 15413 20 45_Re: How does Oracle communicate with DB2/390?12_Jim Harrison17_jharrison@GMX.NET31_Thu, 11 Oct 2001 13:45:14 -0400418_us-ascii I believe Oracle Transparent Gateway will run on either the Unix box or OS/390. Can't attest to it's viability, but I know we've used it on both.

At 05:50 PM 10/11/2001 +0100, mike.holmans@BT.COM said:

>Answers involving installing DataJoiner or any other close relation don't >count. Your mission, should you choose to accept it, is to achieve it using >Oracle tools only on the Unix box. [...] 15434 169 22_Re: Online Performance16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Thu, 11 Oct 2001 11:38:56 -0700435_us-ascii Harry,

If I understand your SQL correctly, within your correlated subquery, you are trying to find a row from MEASTN whose MEAS_STATN_NBR matches that of table MEASTN, AND it is an active row, AND the EFF_DT value is <= some host variable, AND that particular row contains a maximum EFF_DT value of all qualifying rows regardless of the ACTIVE_IND value???? If so, portion of the query might be rewritten as such: [...] 15604 189 44_Re: Online Performance - Analysis correction16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Thu, 11 Oct 2001 11:43:43 -0700410_us-ascii Harry,

The description in my last memo was not quite correct, it should have read as:

you are trying to find a row from MSDTLS whose MEAS_STATN_NBR matches that of table MEASTN

Regards, Jeff L'Italien American Express ---------------------- Forwarded by Jeff A L'Italien on 10/11/2001 11:37 AM ---------------------------

From: Jeff A L'Italien on 10/11/2001 11:38 AM [...] 15794 86 25_Re: 8K & 16K buffer pools13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Thu, 11 Oct 2001 11:41:32 -0700455_us-ascii Kevin,

Consider a table with a row length of 2038 bytes. If you used a 4K page (which has a useful size of 4074 bytes, one could only save 1 row per page (lets leave out compression for this discussion).

If you had that in an 8k page (which I believe is actually 2 4k pages managed by DB2 to look like an 8k page) you could get 3 rows in the 2 4K pages. This could give you a 50% increase in through put (or buffer pool hits). [...] 15881 48 18_Online Performance13_Harry William27_harry_williamtk@HOTMAIL.COM31_Thu, 11 Oct 2001 19:09:00 +0000303_- Hi jeff, Thanks for the reply. as you said there can be only one row for a MEAS_STATN_NBR for a date. As requested I have provided the firstkey card & full keycard information. I am not that good in interpreting these values. I will try the sql given by you tomorrow and let you know the result. [...] 15930 69 46_Temporary Tables and UDB SQL Stored Procedures0_24_db46@DAIMLERCHRYSLER.COM31_Thu, 11 Oct 2001 15:16:11 -0400369_us-ascii Hello List; I would appreciate some help on the following:

For the project I am presently on we have a UDB version 7.1 database on a UNIX server.

I want to write a SQL stored procedure that will return a result set.

But this result set cannot be returned in one cursor.

I would like to use a Global Temporary Table as follows: [...] 16000 34 22_DB2 Connect V5 support11_Brown.James24_James.Brown@SUNTRUST.COM31_Thu, 11 Oct 2001 15:22:00 -0400813_- Hi all,

Does anyone know when support for DB2 Connect v5 will expire?

Thanks!



Jim Brown Database Management Services Data Base Administrator (804) 553-9024 STnet(643-9024) james.brown@suntrust.com ***************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. **************************************************************** [...] 16035 58 45_Re: How does Oracle communicate with DB2/390?12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 11 Oct 2001 12:33:07 -0700397_us-ascii I still have scars from using OTG 5 years ago... DB2 can probably get to oracle data via federated databases...I don't think it needs Datajoiner.

As far as only using UNIX and Oracle tools on the Unix box, how about...

1.telnet to the db2 host and use it's services to export or unload the data 2.ftp the data back to the Unix box 3.import or load the data into Oracle [...] 16094 109 22_Re: Online Performance16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Thu, 11 Oct 2001 12:48:11 -0700419_us-ascii Harry,

Based on the number of rows in the CTPTPN table, and low cardinality of PRODUCTION_DT to the total number of rows in the table, you might want to consider one of the two following options:

1) Create an additional index on NOMACT comprised of GAS_TRADE_IND and INPUT_OUTPUT_IND

OR

2) Rewrite the outer portion of your SQL as follows to allow NOMACT to be accessed first: [...] 16204 22 59_Selectively discarding rows using standard DB2 Load utility11_John Cimbel19_signibm@HOTMAIL.COM31_Thu, 11 Oct 2001 19:36:29 +0000286_- Can anyone tell if there is a way to selectively discard records from an input file while using the standard DB2 Load Utility (DB2 for MVS V6) ??

For example, is it possible to load all records in an input file but for those which has a specific character in position 1 ? [...] 16227 146 45_Re: How does Oracle communicate with DB2/390?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 11 Oct 2001 12:51:26 -0700371_iso-8859-1 We installed the Oracle Transparent Gateway on the mainframe to accomplish this. One of its major benefits, from my perspective, is that the gateway software will perform password checking against your mainframe security package. Without that, I'm not sure how you would authenticate a user.

Cathy Taddei DB2 & OS/390 Systems Programmer PacifiCorp [...] 16374 87 50_Re: Temporary Tables and UDB SQL Stored Procedures12_Myron Miller22_Myron.Miller@BMWNA.COM31_Thu, 11 Oct 2001 16:07:18 -0400343_iso-8859-1 I haven't done this on the UDB AIX version, but have done the exact things you're trying to do on OS/390 and it works just fine, including the access from JAVA. I would have to assume that in the UDB version the temp table will stick around until the JAVA program issues a "COMMIT" (or close cursor) and closes the connection. [...] 16462 98 63_Re: Selectively discarding rows using standard DB2 Load utility12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Thu, 11 Oct 2001 16:20:08 -0400394_US-ASCII Look in DB2 Utility Guide and Reference - Chapter 2-9

WHEN The WHEN clause tells which records in the input data set are to be loaded. If there is no WHEN clause (and if FORMAT UNLOAD was not used in the LOAD statement), all records in the input data set are loaded into the specified tables or partitions. (Data beyond the range of the partition specified is not loaded.) [...] 16561 37 63_Re: Selectively discarding rows using standard DB2 Load utility11_Mike Waters18_MWaters@BCBSMS.COM31_Thu, 11 Oct 2001 15:26:32 -0500412_US-ASCII John,

There is a WHEN clause for the load utility that allows you to specify conditional loading criteria. Only those rows that evaluate to "TRUE" for the when clause are loaded.

>>> John Cimbel 10/11/01 02:36PM >>> Can anyone tell if there is a way to selectively discard records from an input file while using the standard DB2 Load Utility (DB2 for MVS V6) ?? [...] 16599 203 15_Re: SQL Problem24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Thu, 11 Oct 2001 13:27:14 -0700702_iso-8859-1 Thanks, Guys!

I think we're getting closer. The statement as adjusted by Terry and including the necessary join columns is below. However, in DB2 version 6 on OS/390, DISTINCT is only allowed to occur once per subselect. Does anyone have any suggestions on how to get past this?

SELECT SUBSTR(J1.ORRONUM,2,6) AS "OMPO #" ,J1.ORAPRID AS "LAST CHANGED BY" ,J1.ORAPRDT AS "CHG DATE" ,J3.* FROM CTSDB2.MCMTB087 J1, (SELECT SUM(LABOR_LINES) AS LABOR_LINES ,COUNT(DISTINCT LABOR_CLASS) AS LABOR_CLASS ,COUNT(DISTINCT WA) AS WA ,SUM(PART_LINES) AS PART_LINES ,COUNT(DISTINCT PARTS_CLASS) AS PART_CLASS ,COUNT(DISTINCT FC) AS FC FROM (SELECT CASE WHEN IRTTYPE = 'W' THEN 1 END AS [...] 16803 58 63_Re: Selectively discarding rows using standard DB2 Load utility11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Thu, 11 Oct 2001 15:25:59 -0500485_us-ascii Take a look at the "Yourhighlevel.SDGOSAMP(DGOALFBU)" or "Yourhighlevel.PM.SDGOSAMP(DGOALFDF)" and search for the character string NULLIF and you will see how to do it... : )







John Cimbel cc: Sent by: DB2 Subject: Selectively discarding rows using Data Base standard DB2 Load utility Discussion List



10/11/01 02:36 PM Please respond to DB2 Data Base Discussion List [...] 16862 49 26_Re: DB2 Connect V5 support13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 11 Oct 2001 15:43:31 -0500799_US-ASCII Hello James, End of Service Date for 5.2 DB2 Universal Database and DB2 Connect was June 30, 2001 Regards, Kurt

>>> James.Brown@SUNTRUST.COM 10/11/01 02:22PM >>> Hi all,

Does anyone know when support for DB2 Connect v5 will expire?

Thanks!



Jim Brown Database Management Services Data Base Administrator (804) 553-9024 STnet(643-9024) james.brown@suntrust.com ***************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient [...] 16912 24 19_DNA Database Design11_John Kruger23_krugerj@ISP.STATE.IL.US31_Thu, 11 Oct 2001 15:57:27 -0500646_us-ascii Greetings all, Is anyone out there designing or an administrator of a DNA database? I would like to know how DNA is typed or stored and other intriguing methods in this latest and greatest technology.



************************************ John P. Kruger Illinois State Police Information Services Bureau 217-558-6442 Springfield, Il 62794-9461 *************************************

================================================ 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. 16937 154 41_Re: WHITE PAPERS ON BULL TO IBM MIGRATION25_Robinson, Peter [IBM GSA]33_Peter.J.Robinson@TEAM.TELSTRA.COM31_Fri, 12 Oct 2001 07:42:26 +1000635_iso-8859-1 Hi Sreenivas

Many years ago I worked on a conversion from BULL IDS-II to IBM/CICS/DB2. It was interesting :-)

A third party company here in Australia specialised in these type of conversion projects and built a parser that converted the IDS verbs into calls to common hand-written modules that implemented the functions of GET, FETCH, etc as some tricky SQL. They also converted the screen layouts info BMS macros etc. All of the business logic remained largely untouched (except for some small COBOL syntax changes) and the system is still running today - being maintained and enhanced regularly I hear. [...] 17092 34 22_DB2 Connect encryption10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Thu, 11 Oct 2001 17:27:05 -0700597_iso-8859-1 Hello,

We're running DB2 Version 5 on OS/390 Version 2.5 with DB2 Connect Personal Edition (PE) and the Enterprise Edition (EE) Version 7.2 on a Windows NT platform. When using PE, we noticed that the TCP/IP packets between the DB2 Connect PE client and DB2 on the mainframe seems to be encrypted. However, when using the EE gateway, we can see plain text in the packets between the DB2 Connect client on the PC and the DB2 Connect EE gateway. This is distressing because we have social security numbers and other sensitive information being selected from DB2 tables on the [...] 17127 25 26_Store Procedures in OS/39013_Nguyen, Cindy22_Cindy.Nguyen@OCGOV.COM31_Thu, 11 Oct 2001 17:41:47 -0700448_iso-8859-1

Hi All, My shop just installed the DB2 on OS/390 version 6 . I would like to create store procedures of DB2 OS/390 . I would like to have some advices about the materials to read on and procedures that some of you have experience in your shop. I would like to code in SQL. Should I use the Store Procedure Builder software of IBM for OS/390 or I can manually code it. I appreciate for your recommendation. Thank in advance. [...] 17153 53 30_Re: Store Procedures in OS/39011_Suresh Sane21_data_arch@HOTMAIL.COM31_Thu, 11 Oct 2001 23:10:24 -0500634_- Cindy -

2 Redbooks I consider a must-read (don't have the numbers): 1. Getting started with Stored Procedures (give them a call..) 2. Cross System Stored Procedures

HTH

Suresh

>From: "Nguyen, Cindy" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Store Procedures in OS/390 >Date: Thu, 11 Oct 2001 17:41:47 -0700 > > > Hi All, > My shop just installed the DB2 on OS/390 version 6 . I would >like >to create store procedures of DB2 OS/390 . > I would like to have some advices about the materials to read on >and >procedures that some [...] 17207 221 30_Re: Store Procedures in OS/3900_24_Bruce.Williamson@PSIR.IE31_Fri, 12 Oct 2001 07:47:56 +0100733_windows-1252 Howzit Cindy?

Try these sites for starters.

http://www.ibm.com/software/data/db2/os390/spb/

http://www7b.boulder.ibm.com/dmdd/

http://www.developer.ibm.com/devcon/

Redbooks:

Accessing DB2 for OS390 Data from the World Wide Web Cross-Platform DB2 Stored Procedures - Building and Debugging

HTH Cheers Bruce



-----Original Message----- From: Suresh Sane [ mailto:data_arch@HOTMAIL.COM ] Sent: 12 October 2001 05:10 To: DB2-L@RYCI.COM Subject: Re: Store Procedures in OS/390



Cindy - [...] 17429 78 41_Store Procedures Error Handling in OS/39012_Dil Pratheek30_Dil.Pratheek@MORGANSTANLEY.COM31_Fri, 12 Oct 2001 03:40:24 -0400417_us-ascii Hi to all the SP wiz out there....

I have a user who is needing to code the following scenario, and would appreciate any guidance you could give on the best way to achieve this :

Within the procedure there is one piece of update SQL where it is legitimate to have a row not found SQLCODE. If this is the case we need to retry the piece of SQL before the row not found, and the update SQL. [...] 17508 56 47_Re: !Candle Explain vs. SQLERRD4 timeron values14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 12 Oct 2001 09:24:35 +0100541_iso-8859-1 Listers,

I have found Jim Connelley's Rexx from CBTTAPE file 493.

Based on my original values:

Statement 1 !Candle=3,711,183.0 DSNREXX=1178116303 Statement 2 !Candle= 10.9 DSNREXX=1101918706

Jim's Rexx returns, Input Value: 1178116303 Resulting floating value: 484638A0CF000000 Floating value converted back: 1178116303 and Input Value: 1101918706 Resulting floating value: 4841ADF1F2000000 Floating value converted back: 1101918706

Neither look anything like Candle's 3,711,183.0 or 10.9! [...] 17565 18 32_Re: DB2 System Code Page Change.15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 12 Oct 2001 04:05:19 -0500352_- Hello Martin

We faced a similar problem a few years ago, when we installed DB2 V5 with the default CCSID 500 (erranously) and switched to 271 (the german code page). In the time in between we created a crop of databases. So we inserted two rows in SYSSTRINGS with the appropriate transformation and since then we experienced no problems. [...] 17584 125 22_Re: Online Performance22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 12 Oct 2001 15:03:19 +0530600_- Harry,

Just a clarification, you mentioned "and for past date,I can only see list prefect and sequential prefetch is 0.". List prefetch and sequential prefetch both are zero or just sequential prefetch is zero.

In any case, as CTPTPN is accessed first, i feel the local predicate " T1.PRODUCTION_DT = ?" is applied with the list prefetch and so we should not have any difference in that because list prefetch is on that table only and increasing the effective date span should not cause any problems with that. I mentioned this yesterday but now i think list prefetch should not [...] 17710 15 67_Re: Dynamic rule Bind vs Dynamic Rule Run - impact on access path ?15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 12 Oct 2001 04:37:12 -0500433_- Hi Eric

Just a quick guess: May be the SQLIDs have a lot more secondary authids than the binder which has to be checked in order to determine if the user has the necessary authority.

================================================ 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. 17726 45 31_Antwort: Re: Online Performance14_Sukendu Mishra21_sukendu.mishra@DB.COM31_Fri, 12 Oct 2001 11:53:38 +0100321_iso-8859-1 hi i have one query suppose i have two coloumn in one table like coloumn A, and coloumn B : when i want to execute query on this table in my output report i will only get two coloumn but is it possible to only display a new coloumn in my output report.If yes can you please let me know about that query.. [...] 17772 43 41_Antwort: Re: DB2 System Code Page Change.14_Sukendu Mishra21_sukendu.mishra@DB.COM31_Fri, 12 Oct 2001 11:59:40 +0100349_iso-8859-1 hi

can somebody help me in giving syntax of rounding of any float variable in SQL

Sukhendu Mishra Deutsche Bank AG Global Technology & Services GCBS/Electronic Financial Services Frankfurter Str. 84, 65760 Eschborn Ph :- 0049-69-91062713 mobil: 0049-01795070828 Email :- sukendu.mishra@db.com







17816 161 45_Re: Antwort: Re: DB2 System Code Page Change.22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 12 Oct 2001 15:53:33 +0530789_iso-8859-1 Read the SQL Reference content below and look at the examples(2 in numbers). It should be clear to you. You can try out the examples for getting more clear concepts.

============================================================================ ============================ - -ROUND(expression1,expression2) -------------------------------------------------------------------- The schema is SYSIBM. The ROUND function returns expression1 rounded to expression2 places to the right or left of the decimal point depending on the value of expression2. expression1 An expression that returns a value of any built-in numeric data type. expression2 An expression that returns a small or large integer. The value of integer specifies the number of places to the right of the [...] 17978 110 35_Re: Antwort: Re: Online Performance22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 12 Oct 2001 15:59:44 +0530323_iso-8859-1 Sukhendu,

I am not sure if i have understood your question correctly but if you want to achieve different column name to be appear in the report then you have to use AS clause something like this:

SELECT COL1 as EMPNO, COL2 as EMPNAME FROM TABLE

Let us know if you need something else. [...] 18089 128 47_Re: !Candle Explain vs. SQLERRD4 timeron values0_18_mebert@AMADEUS.NET31_Fri, 12 Oct 2001 12:23:01 +0200434_us-ascii C2D and D2C convert the ASCII or EBCDIC value of a character to/from the numerical representation. Unless REXX has changed dramatically when I wasn't looking, it only supports "string type" numbers, i.e. the character string "123.4" can be manipulated like the numeral 123.4. But there is no binary integer datatype, let alone a float. You'll have to write your own conversion routine. Interesting challenge, in REXX... [...] 18218 51 63_Re: Selectively discarding rows using standard DB2 Load utility18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 12 Oct 2001 16:03:46 +0530686_- Hi John,

Yes, there certainly is a way to meet your requirement. Use 'WHEN' clause in the load stmt in sysin. Records meeting the 'when' criterion get loaded to table and the others find their way to the discard dataset that has ddname as DISCARDDN.

thanks, Rajendra.









>From: John Cimbel >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Selectively discarding rows using standard DB2 Load utility >Date: Thu, 11 Oct 2001 19:36:29 +0000 > >Can anyone tell if there is a way to selectively discard records from an >input file while using the standard DB2 Load Utility [...] 18270 115 62_Re: !Candle Explain vs. SQLERRD4 timeron values (INT to FLOAT)14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 12 Oct 2001 11:44:12 +0100424_iso-8859-1 Hi listers.

I attach the Rexx I've knocked up, but it doesn't quite give the answers I'm expecting. Please feel free to copy/amend/correct, so long as you tell me what you've done!

Here are the results:

Stmt SQLERRD4 My func Candle ---- ---------- --------- ----------- S1 1178116303 3711183.0 3,711,183.0 S4 1166271272 864336.8 540,210.5 S2 1101918706 11.4 10.9 S5 1101221559 10.7 10.2 [...] 18386 170 39_Examples of ROUND function - Corrected.22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 12 Oct 2001 16:39:38 +0530464_iso-8859-1 Sukendu,

Probably there was some problem in copy/paste, so i am repeating the query example. You can also look in Sql Reference....

Example 1: Calculate the number 873.726 rounded to 2, 1, 0, -1, and -2 decimal places respectively. SELECT ROUND(873.726,2), ROUND(873.726,1), ROUND(873.726,0), ROUND(873.726,-1), ROUND(873.726,-2) FROM SYSIBM.SYSDUMMY1; This example returns the values 873.730, 873.700, 874.000, 870.000, and 900.000. [...] 18557 29 33_Temporary Tables on UDB version 70_24_db46@DAIMLERCHRYSLER.COM31_Fri, 12 Oct 2001 08:09:45 -0400671_us-ascii Uh, does UDB version 7 have temporary tables and if so how are they created?

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. **************************************************************************** [...] 18587 58 37_AW: Temporary Tables on UDB version 712_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Fri, 12 Oct 2001 14:37:52 +0200721_iso-8859-1 Hi Dean,

a look at SC09-2944-00 (search for "temporary table") should lead you to the desired example.



HTH

Thomas

> -----Ursprüngliche Nachricht----- > Von: db46@DAIMLERCHRYSLER.COM [mailto:db46@DAIMLERCHRYSLER.COM] > Gesendet am: Freitag, 12. Oktober 2001 14:10 > An: DB2-L@RYCI.COM > Betreff: Temporary Tables on UDB version 7 > > Uh, does UDB version 7 have temporary tables and if so how are they > created? > > Dean > > > > > > ************************************************************** > ************** > > The information contained in this transmission, which may be > confidential and proprietary, is only for the intended recipients. > Unauthorized use is [...] 18646 25 52_Connection the Control Center to a OS/390 DB2 SYSTEM0_20_John_Lendman@FPL.COM31_Fri, 12 Oct 2001 08:46:09 -0400445_us-ascii Our DB2 systems programmer has told us that he has completed the install of the control center for the mainframe. He said he has activated the store procedures. We would like to define the OS/390 system DB2 V6.1 in our UDB control center but we are not sure of the parms we need. One thing we think we need is the TCP/IP address of the MVS mainframe. Does any one know what else we need to provide in order to make the connection? [...] 18672 125 56_Re: Connection the Control Center to a OS/390 DB2 SYSTEM12_Chao Terry F20_Terry.F.Chao@IRS.GOV31_Fri, 12 Oct 2001 08:52:00 -0400661_iso-8859-1 You'll need the host IP address and the port number for the DB2 subsystem.

Terry Chao





-----Original Message----- From: John_Lendman@FPL.COM [mailto:John_Lendman@FPL.COM] Sent: Friday, October 12, 2001 8:46 AM To: DB2-L@RYCI.COM Subject: Connection the Control Center to a OS/390 DB2 SYSTEM



Our DB2 systems programmer has told us that he has completed the install of the control center for the mainframe. He said he has activated the store procedures. We would like to define the OS/390 system DB2 V6.1 in our UDB control center but we are not sure of the parms we need. One thing we think we need is [...] 18798 124 56_Re: Connection the Control Center to a OS/390 DB2 SYSTEM12_Chao Terry F20_Terry.F.Chao@IRS.GOV31_Fri, 12 Oct 2001 08:53:36 -0400663_iso-8859-1 And the Location Name for the subsystem.

Terry Chao (202)283-6359



-----Original Message----- From: John_Lendman@FPL.COM [mailto:John_Lendman@FPL.COM] Sent: Friday, October 12, 2001 8:46 AM To: DB2-L@RYCI.COM Subject: Connection the Control Center to a OS/390 DB2 SYSTEM



Our DB2 systems programmer has told us that he has completed the install of the control center for the mainframe. He said he has activated the store procedures. We would like to define the OS/390 system DB2 V6.1 in our UDB control center but we are not sure of the parms we need. One thing we think we need is the TCP/IP address of the MVS [...] 18923 151 56_Re: Connection the Control Centre to a OS/390 DB2 SYSTEM0_24_Bruce.Williamson@PSIR.IE31_Fri, 12 Oct 2001 14:17:31 +0100654_windows-1252 Howzit John?

Have a look at the following redbook: The Universal Connectivity Guide to DB2 (SG24-4894-00).

Cheers Bruce



__________________________________________________

Bruce Williamson DB2 Database Administrator

perotsystems information resource(tm)

TSG DBA Computer Centre Cabinteely Dublin 18 Ireland

Bruce.Williamson@psir.ie





-----Original Message----- From: John_Lendman@FPL.COM [ mailto:John_Lendman@FPL.COM ] Sent: 12 October 2001 13:46 To: DB2-L@RYCI.COM Subject: Connection the Control Center to a OS/390 DB2 SYSTEM [...] 19075 54 47_Re: !Candle Explain vs. SQLERRD4 timeron values14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Oct 2001 08:19:48 -0500440_- Marcus

1178116303 (integer) = x'4638A0CF' (using, eg, D2X(1178116303,8), although I used Windows Calculator ) = 0.38a0cf (base16) * 16**(70-64) = 0.221203744411468505859375 * 16**6 = 3711183

I seem to recall sending you a piece of rexx code that converted floating point numbers in this format to displayable characters - when you were trying to use DSNTIAUL to get a DB2 to REXX interface. I no longer have that code. [...] 19130 95 47_Re: !Candle Explain vs. SQLERRD4 timeron values14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 12 Oct 2001 14:33:10 +0100499_iso-8859-1 I've just remembered it and found it! I'm tweaking a function now... Marcus

-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: 12 October 2001 14:20 To: DB2-L@RYCI.COM Subject: Re: !Candle Explain vs. SQLERRD4 timeron values



Marcus

1178116303 (integer) = x'4638A0CF' (using, eg, D2X(1178116303,8), although I used Windows Calculator ) = 0.38a0cf (base16) * 16**(70-64) = 0.221203744411468505859375 * 16**6 = 3711183 [...] 19226 35 22_Failed load in DB2 AIX14_Curran, John F29_john.f.curran@UK.FID-INTL.COM31_Fri, 12 Oct 2001 14:35:19 +0100484_iso-8859-1 Hi

I wonder if any one has any helpful information

Environment AIX 4.3.3 Database DB2 Enterprise Edition V7.1

One of our users ran a load job which failed this left the entire table space in a load pending state.

The manual seemed to only have two options to reset the load pending flag

1 Re run the exact same load job replacing insert/replace with terminate 2. Recover the tablespace to a point in time before the load took place. [...] 19262 208 62_Re: !Candle Explain vs. SQLERRD4 timeron values (INT to FLOAT)16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 12 Oct 2001 14:43:07 +0100365_iso-8859-1 Hi Marcus,

I don't have the chance of Rexx-ing now, but I used the scientific calculator from this pseudo-OS to do the conversions and this is what I got (slow cut-and-paste follows):

1178116303 (INT) = x'4638A0CF' (here in Rexx I would use D2X(1178116303,8) = 0.38a0cf (b) * 16**(70-64) = 0.221203744411468505859375 * 16**6 = 3711183 [...] 19471 57 62_Re: !Candle Explain vs. SQLERRD4 timeron values (INT to FLOAT)14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 12 Oct 2001 14:48:38 +0100612_iso-8859-1 Many thanks to Aurora and James... Here is my final cut...

Marcus

/* REXX which may convert an INTEGER representation of a FLOAT value into DECIMAL. But then again, it may not. */ I2F: Numeric Digits 80 setval1=d2x(Arg(1)) setval1_b1 = x2b(substr(setval1,1,2)) sign = substr(setval1_b1,1,1) exponent = x2d(b2x(substr(setval1_b1,2,7))) - 64 mantissa = 0 hex_mul = 1/256 do i = 3 to length(setval1) by 2 mantissa = mantissa + x2d(substr(setval1,i,2)) * hex_mul hex_mul = hex_mul/256 end setval = mantissa * (16**exponent) if sign = 1 then setval = 0 - setval Return Format(setval,,1) [...] 19529 58 26_Re: Failed load in DB2 AIX18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Fri, 12 Oct 2001 09:58:57 -0400340_iso-8859-1 I'm not sure what you are expecting to do. If a load fails, you can't just "change a flag". You either have to terminate the load or recover the tablespace.

Why aren't the options practical?

Peter J. Krawetzky, DBA IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2 [...] 19588 35 26_Db2 and W2000 Single Logon11_Mark Scheel18_mscheel@AUXILIO.DE31_Fri, 12 Oct 2001 15:56:04 +0200535_iso-8859-1 Dear Listeners,

we need to access Db2 by the following way.

In Db2 we should have 4 different UserGroups with different access rights for our DWH.

The users should access Db2 by Single Logon (automatic logon by getting UserName/Rights from OS=W2000)

The (db2) access rights for the users should be given by our W2000-Admins; what we would like is, that the Admin only has to add a W2000-User to a special W2000-Group. By this the access right to one of the Db2-UserGroups should be managed. [...] 19624 62 38_Re: Datajoiner 2nd instance connection15_Neff, Stephen R16_NeffSR@STATE.GOV31_Fri, 12 Oct 2001 09:52:51 -0400444_ISO-8859-1 Hello Adrian,

Were in the process of replicating ORACLE DATA to OS390 mainframe. We are interested in replicating changes (only) to an ORACLE datbase. I understand from my readings that the changes to an ORACLE table can be captured (update, delete, & inserts) via triggers and stored in a DJ/ORACLE CCD table. When the Apply runs this DJ/ORACLE CCD table is read and replicated. Were you trying to capture only changes. [...] 19687 152 22_Re: Online Performance16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Fri, 12 Oct 2001 07:39:06 -0700637_us-ascii Harry,

One other thing that I thought of to further limit the number of qualifying rows from the NOMACT table is to incorporate the MEASTN table within the nested table expression as such:

SELECT T2.OPPOSITE_FLOW_IND, SUM(T1.REQ_INPUT_ENRG), SUM(T1.SCHD_INPUT_ENRG) FROM CTPTPN T1, (SELECT DISTINCT A.CONTR_ID, A.ACTIVITY_NBR, A.OPPOSITE_FLOW_IND, A.INPUT_POINT_NBR, B.MEAS_STATN_NBR FROM NOMACT A, MEASTN B WHERE A.GAS_TRADE_IND = 'N' AND A.INPUT_OUTPUT_IND = 'I' AND A.INPUT_POINT_NBR = B.MEAS_STATN_NBR ) AS T2 WHERE T1.PRODUCTION_DT = ? AND T1.CONTR_ID = T2.CONTR_ID AND T1.ACTIVITY_NBR = T2.ACTIVITY_NBR [...] 19840 40 22_Re: -DISPLAY THREAD(*)15_Neff, Stephen R16_NeffSR@STATE.GOV31_Fri, 12 Oct 2001 10:18:32 -0400384_ISO-8859-1 Hello, We are running OS390 V6. I looked up DSNV404I in the DB2 UDB for OS/390 V6 Messages and Codes manual and that status is not defined. Do you have a DB2 UDB for OS/390 V7 Messages and Codes manual?

-----Original Message----- From: Vince Crose [mailto:vcrose@US.IBM.COM] Sent: Friday, October 05, 2001 7:59 AM To: DB2-L@RYCI.COM Subject: -DISPLAY THREAD(*) [...] 19881 65 26_Re: Failed load in DB2 AIX15_Stacey L Waters23_WATERSS1@NATIONWIDE.COM31_Fri, 12 Oct 2001 09:41:44 -0500500_us-ascii Have you looked at load terminate?





"Curran, John F" To: DB2-L@RYCI.COM cc: Sent by: DB2 Data Base Discussion bcc: List Subject: Failed load in DB2 AIX





10/12/01 08:35 AM Please respond to DB2 Data Base Discussion List











Hi

I wonder if any one has any helpful information

Environment AIX 4.3.3 Database DB2 Enterprise Edition V7.1 [...] 19947 64 22_Re: -DISPLAY THREAD(*)16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 12 Oct 2001 10:44:29 -0400666_iso-8859-1 Try: http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/Shelves/DSNSHHA0

I would post the message, but this is one of the longer ones and would probably exceed the maximum lines in a post.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Neff, Stephen R [mailto:NeffSR@STATE.GOV] Sent: Friday, October 12, 2001 10:19 AM To: DB2-L@RYCI.COM Subject: Re: -DISPLAY THREAD(*)



Hello, We are running OS390 V6. I looked up DSNV404I in the DB2 UDB for OS/390 V6 Messages and Codes manual and that status is not defined. Do you have a DB2 UDB for OS/390 V7 Messages and Codes manual? [...] 20012 64 22_Re: -DISPLAY THREAD(*)16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 12 Oct 2001 10:47:56 -0400418_iso-8859-1 OOOPS.... Never mind. That status is not in the manual on the server. Maybe a new APAR? I can't look on IBMLINK right now due to temporarily (I hope!) neutered access.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Neff, Stephen R [mailto:NeffSR@STATE.GOV] Sent: Friday, October 12, 2001 10:19 AM To: DB2-L@RYCI.COM Subject: Re: -DISPLAY THREAD(*) [...] 20077 72 22_Re: -DISPLAY THREAD(*)12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Fri, 12 Oct 2001 10:14:14 -0500486_iso-8859-1 The status field is a one to two character field. I did not find the characters TD defined together as one unit, but I did find T and D defined separately:

T An allied, nondistributed thread was established (plan allocated). D The thread is in the process of termination as a result of the termination of the associated allied task. If this thread is also the last (or only) DB2 thread for the address space, the associated allied task is placed in a wait state. [...] 20150 24 27_ROWID and DIRECT ROW ACCESS11_Moore, Tony15_TMoore@IKON.COM31_Fri, 12 Oct 2001 11:25:04 -0400572_iso-8859-1 Yo Listers, Can anyone confirm this suspicion for me?? I'm reading that in DB2 (Big Iron) Version 6, accessing a row via a ROWID column allows for direct row access. It does this by storing the location of the row within the ROWID column. Sounds pretty straight forward so far. However, say a ROWID column is defined as GENERATED BY DEFAULT and the value of the column is populated by some other ROWID specified by the user / application. I'm thinking that if DB2 was to use direct row access using that value, it would get unpredictable results. So, does [...] 20175 175 56_Re: Connection the Control Center to a OS/390 DB2 SYSTEM14_Philip Gunning24_philip.gunning@QUEST.COM31_Fri, 12 Oct 2001 09:27:52 -0700429_iso-8859-1 Although it is a little old the best overall documentation on seeting up TCPIP and DDF and RACF and cataloging databses is the Redbook, WOW! DRDA Supports TCPIP: DB2 Server for OS/390 and DB2 UDB, SG24-2212-00. HTH Phil







-----Original Message----- From: Chao Terry F To: DB2-L@RYCI.COM Sent: 10/12/01 5:52 AM Subject: Re: [DB2-L] Connection the Control Center to a OS/390 DB2 SYSTEM [...] 20351 16 53_Debra Jordan/Riverwoods/BT/DFSI is out of the office.15_Debra D. Jordan33_debrajordan@DISCOVERFINANCIAL.COM31_Fri, 12 Oct 2001 12:07:29 -0500447_us-ascii I will be out of the office starting 10/12/2001 and will not return until 10/15/2001.

I will be out of the office for the remainder of the day. 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. 20368 43 31_Re: ROWID and DIRECT ROW ACCESS9_Rob Crane22_racrane@CONCENTRIC.NET31_Fri, 12 Oct 2001 11:33:30 -0600473_us-ascii Assuming the row is in the same page/rid as when the initial insert occurred then DIRECT ROW access would still be feasible.

The Explain output will show you what access path you are getting as well.

When using generated by default, you also need a unique index on the at column, using generated always, you do not need the index. Regardless DIRECT ACCESS will not use the index unless the page/rid is different from its initial inserted value. [...] 20412 22 32_DB2 Connect and the Concentrator17_Miguel de Andrade29_miguel.andrade@BTINTERNET.COM31_Fri, 12 Oct 2001 12:32:29 -0500357_- Hello Dear Listers,

I was wondering if anyone has already experienced the DB2 Connect Concentrator feature, particularly with large number of users. The main areas I am looking is performance and setup problems.

If you are aware of any documentation that you found particularly useful please let me know.

Thank you for your help [...] 20435 50 16_Trigger Problems8_Ken Kane24_kkane@UBSPAINEWEBBER.COM31_Fri, 12 Oct 2001 15:12:09 -0400471_iso-8859-1 Hello Troopers,

We're a DB2 V6 OS-390 shop having growing pains with triggers. An application (CICS-DB2) has triggers on several tables to produce an audit trial. The triggers in this case are on the TABLEA and TABLEB , each has 3 triggers one for update, one for insert, one for delete. All triggers will insert a concatenated before-after image with a timestamp key into a target table. The logic and data flow produces the following situation.. [...] 20486 156 18_Online Performance13_Harry William27_harry_williamtk@HOTMAIL.COM31_Fri, 12 Oct 2001 19:20:28 +0000671_- Sanjeev, Regarding the list & sequential prefetch,What I mentioned was, for present date I can see value in Sequential and list prefetch(say 182). For past date seqential prefetch is 0 and list prefetch has some numbers(say 400). I guess for past date it is going for index scan on CTPTPN. Jef, I tried both the sql given by you. It is taking more time. I have pasted the explain output.I guess table space scan is causing the delay. SELECT T2.OPPOSITE_FLOW_IND, SUM(T1.REQ_INPUT_ENRG), SUM(T1.SCHD_INPUT_ENRG) FROM CTPTPN T1, (SELECT DISTINCT CONTR_ID, ACTIVITY_NBR, OPPOSITE_FLOW_IND, INPUT_POINT_NBR FROM NOMACT WHERE GAS_TRADE_IND = 'N' AND INPUT_OUTPUT_IND = [...] 20643 86 20_Re: Trigger Problems11_Todd Martin15_tm9us@YAHOO.COM31_Fri, 12 Oct 2001 12:25:03 -0700555_us-ascii Hi Ken,

I dont know why you guys following that logic .Update Trigger fails with 302 code bcos ur trying to update a value which is too long than the column defined value .I can help you more if you can show me the triggers .

Thx, TM.

--- Ken Kane wrote: > Hello Troopers, > > We're a DB2 V6 OS-390 shop having growing pains > with triggers. > An application (CICS-DB2) has triggers on > several tables to produce an > audit trial. The triggers in this case are on the > TABLEA and TABLEB , > [...] 20730 100 41_Table Scan Problems - using LIKE operator20_Sitaram Vijay kartik21_vijaykartik@YAHOO.COM31_Fri, 12 Oct 2001 13:17:01 -0700504_us-ascii Hi DB2 Guru's!

We got this SQL doing a fuzzy search. Please analyze the details given below and suggest possible improvements.

SQL to Tune: select last_name from mem where last_name LIKE '%FRETE'

Anytime we run this SQL, we get a TABLESCAN. The expected index to use has columns: LAST_NAME+BU_ID+FIRST_NAME+MIDDLE_NAME

How can we force the optimizer to use the existing set of indexes. We tried with the DFT_QUERYOPT DB CFG set to 9 but it always table scanned. [...] 20831 116 22_Re: -DISPLAY THREAD(*)12_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 12 Oct 2001 13:19:57 -0700552_us-ascii Isn't RRSAF a FOCUS/EDA plan name? In my last shop I noticed it usually seemed to be connected, even when it wasn't doing anything. And it sometimes had to be cancelled/forced off when I wanted to do some maint to the database. I don't remember seeing it in TD though. Tim --- "Vernon, John" wrote: > The status field is a one to two character field. I > did not find the > characters TD defined together as one unit, but I > did find T and D defined > separately: > > T An allied, nondistributed thread was [...] 20948 114 45_Re: Table Scan Problems - using LIKE operator11_Mike Waters18_MWaters@BCBSMS.COM31_Fri, 12 Oct 2001 15:35:33 -0500284_US-ASCII I'm sorry to say, but this is a non-indexable predicate. The "LIKE" statement will use an index but only if the % appears last.

One possibility is to add a column populated with the last 5 bytes of the last name. Then you sould use col = 'FRETE' vs LIKE '%FRETE' [...] 21063 110 15_Re: SQL Problem24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Fri, 12 Oct 2001 13:39:17 -0700584_- Hi Terry!

That worked great! But, you ever have one of those days where the developer presents you with a problem, your return with a solution, and then the developer finally gives you the rest of the problem information? You ever want to shoot them for that?

The additional information is that the value of IRRONUM and ORRONUM are not initially know, and need to be obtained from the database. I was working on the assumption that these columns (which are equal) were to be supplied to the query, and that the query would return a single row. Unfortunately, the [...] 21174 69 26_Re: Failed load in DB2 AIX20_Pav Kumar-Chatterjee17_pavkc@HOTMAIL.COM31_Fri, 12 Oct 2001 21:05:35 +0000489_- Hi John

The 2 things you mention are the only 2 things you can do if a load fails. That is why the manual recommends taking an image copy before starting the load. If it is feasible in your shop, it would be better to load into an empty(temporary) table/tablespace, and then insert into your main table. Of course this may prove unacceptable to you if you have to insert many millions of rows. But as far as I am aware there is no way of resetting the load pending flag off. [...] 21244 38 56_Re: Connection the Control Center to a OS/390 DB2 SYSTEM13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 12 Oct 2001 18:55:48 -0400676_iso-8859-1 Hi John,

Others have told you what parms you need. The redbook is, as Phil says, the best reference.

Meanwhile, you can go to your z/OS DB2 master address space log for the subsystem (using SDSF or equivalent) to find the following messages which tell you those values.

00.47.13 STC20444 DSNL519I * DSNLILNR TCP/IP SERVICES AVAILABLE FOR DOMAIN xxxx.compuware.com AND PORT 646 00.47.13 STC20444 DSNL519I * DSNLIRSY TCP/IP SERVICES AVAILABLE FOR DOMAIN xxxx.compuware.com AND PORT 6020 00.47.13 STC20444 DSNL004I * DDF START COMPLETE LOCATION D610CWFH LU USCWXN01.D610CWFH GENERICLU -NONE DOMAIN xxxx.compuware.com TCPPORT 646 RESPORT 6020 [...] 21283 15 54_Barbara Kilburn/Santa Teresa/IBM is out of the office.15_Barbara Kilburn19_bkilburn@US.IBM.COM31_Sat, 13 Oct 2001 08:31:45 -0600425_us-ascii I will be out of the office starting October 12, 2001 and will not return until October 16, 2001.

I will respond to your message when I return on Tuesday October 16th.

================================================ 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. 21299 28 34_Re: Basic question regarding Views12_sushant dash23_dash_dba@REDIFFMAIL.COM31_Sat, 13 Oct 2001 07:56:38 -0000552_iso-8859-1 Hello Terry/All, I have gone through this and aslo was in discussion list for view materializatin as well merge. Sanjeev also participated and we had better knowledge. BUt here I have a doubt and feel you all could be in position to answer it. In one OLTP there are milions of customer and the sqls would be from application to db2 through MQ. The designer says we have to make views for all sql so that it would be materialized and as the queries would be dynamic so it would be compiled or its accesspath woould be known earlier than [...] 21328 24 11_SQL Problem10_praveen_kj19_praveen_kj@INFY.COM31_Sat, 13 Oct 2001 20:36:22 +0530540_iso-8859-1 Hi All,

I am firing an SQL in which a field of type varchar(500), I am giving the value of variable 400 characters long.

Now the query fails saying variable too long..........as it canot take values greater than 255 char.?

Any pointers to this??

regards Praveen

===============================================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. 21353 31 31_COBOL string literal assignment10_praveen_kj19_praveen_kj@INFY.COM31_Sat, 13 Oct 2001 20:43:26 +0530365_iso-8859-1 Hi All,

In cobol assigning the a string value to a variable in the VALUE clause has a limitation of 160 characters.

One way is to have a group level

01 VAR. 02 VAR1 PIC X(160) VALUE 'ABCD......'. 02 VAR2 PIC X(160) VALUE 'XYX........'.

Is there any other way of assigning a string of about 1000 characters to a variable. [...] 21385 55 42_Re: ERROR DURING GETMAIN SYS CODE = 80A-1010_Alan Smith25_alancsmith@BTINTERNET.COM31_Sun, 14 Oct 2001 00:09:28 +0100566_us-ascii Apologies if this has been answered already. I think that the 00E61200 means that the request is for storage below the line. A lot of the storage for allocating datasets goes below the line. The amount can be reduced by changing the JES parameters to SWA=ABOVE, which causes more of the storage to be above the line. This can also be relieved by reducing the number of datasets which DB2 will allocate - i.e., reduce DSMAX in your ZPARM. If the requested storage is above the line, this can be relieved by reducing the sizes of buffer pools, EDM pool, [...] 21441 41 22_Java exercise examplea17_Esmaiel Nokhodian15_dbaen@TEXAS.NET31_Sat, 13 Oct 2001 20:18:19 -050076_iso-8859-1 http://www.intelinfo.com/newly_researched_free_training/Java.html 21483 30 62_Re: !Candle Explain vs. SQLERRD4 timeron values (INT to FLOAT)14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 14 Oct 2001 00:16:02 -0500379_- Marcus,

Three changes I'ld make:

1) Add a "procedure" keyword to I2F.

2) "setval1=d2x(Arg(1))"

I'm somewhat averse to this, because if arg(1) was less than 258435458, setval1 will not have the leading '0' - which is necessary for the rest of the algoritm to work (correctly). I'ld use "setval1=d2x(Arg(1),8)" to ensure I got the leading zeros. [...] 21514 18 31_Re: ROWID and DIRECT ROW ACCESS14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 14 Oct 2001 00:34:32 -0500500_- Tony

read http://jupiter.ryci.com/cgi/wa.exe?A2=ind0102A&L=DB2-L&P=R26180

In terms of that message, I would think that only the "unique value" (actually 15 bytes) part of the supplied rowid is stored in the inserted row.

James Campbell

================================================ 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. 21533 203 15_Re: SQL Problem13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 12 Oct 2001 17:51:18 -0500411_iso-8859-1 Fritz,

The best solution for this would be correlated nested table expressions, although in V6 we are still waiting on a resolution on whether the correlation predicates can be indexable. Using standard nested table expressions, the following may solve the issue of a scan each for each inner join. There is an intermediate sort for each step, but at least the joins should be indexable: [...] 21737 92 22_Re: Online Performance13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sat, 13 Oct 2001 21:21:12 -0500413_iso-8859-1 Harry,

Let me address your original question, as to why the query takes 2-3 min for dates earlier than '2000-01-01', compared to 30 seconds for '2001-01-01' to today: How many rows qualify as earlier than '2000-01-01' compared to after '2001-01-01'? The query is running 4-6 times slower. What you must ask is whether this is indicative of increased data volumes or due to the access path. [...] 21830 28 22_Re: Online Performance13_Harry William27_harry_williamtk@HOTMAIL.COM31_Sun, 14 Oct 2001 17:47:47 +0000492_- Jeff, Total number of pages for NOMACT is 1928.I got it from the SYSTABLES (NPAGE). Hope I am right. No other table is present in this tablespace. Regarding the qualifying row ,it ranges from 2500 to 3000 from CTPTPN.

Terry, The data is actually increasing over period of time.The qualifying row for CTPTPN was 20% less in 2000 and further less in 1999. I even tried altering the index in NOMACT.But I was able to get only 5 secs gain.I will also try the solution given by you. [...]