1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2002, week 5 2 38 23_Delimiter change in DB213_Kaspar Joseph18_k_joseph@HMPPD.COM31_Sun, 29 Sep 2002 15:17:37 +0530569_us-ascii Dear DB2 Experts,

Well, I need a some help in database front. I want to insert a table of one column and the column type is character.

In the normal scenario we use

INSERT INTO COLA VALUES 'JOSEPH'

But I would like to Insert like 'St'Joseph'. In this example one more single quote is appearing after st. like this I have lot of info like B'lore etc... When I transfer data from Ingres to DB2 I'm facing this problem. I would like to know that temporarily is there any way to change the delimiter of ' to something [...] 41 73 27_Re: Delimiter change in DB214_Dash, Sushanta21_sushanta.dash@EDS.COM31_Sun, 29 Sep 2002 06:19:25 -0500389_- Hi Joseph, in DB2 OS/390 there is subsystem parameter which you use to change apost to any other you want. Though there may be other ways to insert into the table and wait for the experts suggestions also.

The Parameter is DSQLDELI and usually set to APOST. You can change it from DSNTIJUZ. I may be trying for a complex solution, but there would be simple solutions for it. [...] 115 54 27_Re: Delimiter change in DB214_James Campbell25_jacampbell@ACSLINK.NET.AU31_Mon, 30 Sep 2002 00:06:28 +1000603_US-ASCII How do you "transfer [the] data from Ingres"? Perhaps doing something like (DB2 V6)

SELECT '''' CONCAT replace(ingres_data,'''','''''') CONCAT '''' from ...

might be something to consider?

James Campbell



On 29 Sep 2002 at 15:17, Kaspar Joseph wrote:

> Dear DB2 Experts, > > Well, I need a some help in database front. I want to insert a table of > one column and the column type is character. > > In the normal scenario we use > > INSERT INTO
COLA VALUES 'JOSEPH' > > But I would like to Insert like 'St'Joseph'. In this example one [...] 170 24 37_Installation Fails with DB2 UDB on XP11_Gordon Clow18_gclow@NETSCAPE.NET31_Mon, 30 Sep 2002 18:14:31 -0400507_us-ascii Dear DB2 Experts,

I'm attempting to install DB2 UDB version 7.2 on Windows XP, and the installation fails with no error messages and no db2.log created. Installshield starts up and files are identified, but when the process goes into Setup, a text-less message box appears, stays visible for several minutes, and then disappears. I find no files installed on the system, nor do I find any new registry entries. Has anyone else seen this, and if so do you have any idea what's going on? [...] 195 34 47_Alex Andrade/GS/UK/Zurich is in sunny DUBAI !!!12_Alex Andrade26_alex.andrade@UK.ZURICH.COM31_Mon, 30 Sep 2002 01:00:41 +0100573_us-ascii I will be out of the office starting 27/09/2002 and will not return until 07/10/2002.

Please call Steve Jackson on 7901 1305 for any queries





___________________________________________________________________________

The information contained in this message is confidential and may be legally privileged. If you are not the intended recipient, please do not read, copy or otherwise use it and do not disclose it to anyone else. Please notify the sender of the delivery error and then delete the message from your system. [...] 230 42 63_row level locking vs page locking with MAXROWS =1 Lock overhead5_khkim15_khkim@LGCNS.COM31_Mon, 30 Sep 2002 11:50:57 +0900240_ks_c_5601-1987 Hello,

I heard page locking with MAXROWS =1 has less locking overhead than row level locking .

I want to know difference between row level locking and page locking with MAXROWS =1

Thank you for your help. 273 51 27_DB2 V7 LOAD sortkeys issues7_Eric Ng22_ngyh@PUBLICBANK.COM.MY31_Mon, 30 Sep 2002 04:27:19 -0500377_- Hi, we are now using DB2 V7. In the LOAD REPLACE job, in order to make use of parallel index build, we need to specify SORTKEYS n, where n is the total no. of keys to be sorted.

My question : is the value of n of paramout importance ?

Below is my JCL for loading. The tablespace (10 million)has 3 indexes. According to manual, I shd use SORTKEYS 30000000. [...] 325 30 31_Re: DB2 V7 LOAD sortkeys issues13_Martin Packer24_martin_packer@UK.IBM.COM31_Mon, 30 Sep 2002 11:30:17 +0100402_us-ascii The fact that the two runs went the same can be explained by the fact that DFSORT* (under the covers) chose to process the sort request the same way, based on the FILSZ estimate that the DB2 utility generated for it. However, that does not mean that sorting performance is totally insensitive to the FILSZ estimate. It might easily have chosen a different work file management technique. [...] 356 49 44_LOAD utility with packed decimal data on AIX14_Timothy Brocke30_Timothy_Brocke@NAVYFEDERAL.ORG31_Mon, 30 Sep 2002 09:50:48 -0400389_us-ascii All,

Can I LOAD a packed decimal column in DB2 on AIX ? The input file in the LOAD script contains a column with packed decimal value that was FTP'd from the mainframe to an AIX box. I've played with several options using the PACKEDDECIMAL parm of the LOAD utility with no success. Also the packed decimal data looks suspect after the file is FTP'd to the AIX server. [...] 406 90 48_Re: LOAD utility with packed decimal data on AIX12_McKown, John22_JMckown@UICIINSCTR.COM31_Mon, 30 Sep 2002 09:24:40 -0500544_iso-8859-1 Well, one question - when you did the FTP, did you do a BINARY ftp? If you did an ASCII ftp (the default), then your packed decimal is toast because ftp will totally destroy the values in its attempt to convert from EBCDIC to ASCII. Unfortunately, I cannot answer the question about loading S/390 packed decimal into DB2 on an AIX system. If you have DB2 on both systems, why not just connect the DB2 systems together and do a SELECT ... INTO type operation, bypassing ftp altogether. That I am sure will work. (federated data base?) 497 82 48_Re: LOAD utility with packed decimal data on AIX12_michael bell24_mbell11@WORLDNET.ATT.NET31_Mon, 30 Sep 2002 09:55:58 -0500583_iso-8859-1 I assume you converted from EBCIDIIC to ASCII in the FTP or your character fields would be scrambled. The result is that all of your internal numeric fields are not correct. Binary fields (INT,SMALLINT and FLOAT) will still load but they won't have the correct data. Decimal fields may or may not load. YES, you need to convert all numeric fields to display format with a recognizable sign. There are 2 starting utilities you can use on the main frame (IEBGENR and SORT). Both will let you unpack numeric fields to external format however they do not check for valid [...] 580 19 50_Fudging Datajoiner statistics for Redbrick server.13_Michalik, Ken19_kmichalik@KRAFT.COM31_Mon, 30 Sep 2002 09:55:10 -0500571_iso-8859-1 We understand that datajoiner can not obtain statistics from RedBrick servers because of using generic protocol. Has anyone attempted to fudge statistics in SYSCAT tables to enable the optimizer to make a better decision when accessing this server type?

Thanks in Advance. Ken Michalik Kraft Foods

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 600 30 38_how big should the "rid pool size" be?0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Mon, 30 Sep 2002 11:21:08 -0500547_us-ascii I have been attempting to tune some queries and found some that I thought would benefit from "List Prefetch" but "List Prefetch" is not selected by the optimizer. While I realize that the reason for this could be one of many things, I am questioning whether our "rid pool size" is as big as it should be. In the queries that I am looking at, the access is via an index with a low cluster ratio (<80), and several rows are retrieved from a single predicate. From what I can tell, the "rid pool size" affects the optimizers choice for [...] 631 19 42_Re: how big should the "rid pool size" be?0_26_truman.g.brown@VERIZON.COM31_Mon, 30 Sep 2002 12:44:40 -0400385_us-ascii Larry, a 4MB RID pool isn't very large; RID pool is a shared resource, whereas the SORT pool is allocated separately for each SORT user. If you have a monitor, check to see if you have any RID pool failures due to storage shortage. If your system is storage constrained, oh well... otherwise you might crank the RID pool up to 20 or 30 MB (we have some that are larger). [...] 651 32 51_Re: DB2 Version 7 Load Utility and Identity Columns13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 30 Sep 2002 13:04:22 -0400371_iso-8859-1 >V7 I believe (?) will allow you to alter the last seq number generated for a given identity column in a table directly in the catalog... Hello Bob.

Nope. V7 DB2 for z/OS does not.

But a little birdie at the Tech Conference in Anaheim a few weeks ago told us it might be in Version Next coming some time next year, perhaps, maybe, possibly. [...] 684 124 20_Re: Data question...16_Herbold, John W.26_JWHERBOLD@ARKBLUECROSS.COM31_Mon, 30 Sep 2002 12:18:38 -0500513_- Good idea on the ISPF. We did run several different routes to try and track down the problem. It appears that they were always at the end of the field in a CHAR column. And they did not occur that often only 6 records out of around one million.

I ended up writing a Perl program to go get a list of all of the CHAR columns from the system tables and hit each column with a SELECT DISTINCT and then go through the results and print out only the records that had something other than 0-9, a-z, A-Z or [...] 809 85 19_Dataspaces Question22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Mon, 30 Sep 2002 10:15:50 -0700302_- Hi Listers,

We have a question that I cannot find a definite answer to, so maybe a few of you have also ran into this. With dataspaces is it advisable to have any allocation in the HP's. I had thought that it was not advisable to have an HP allocation. We are V6, OS/390/ZOS-64 bit. TIA. [...] 895 80 55_Re: SQL - PL stored procedure not returning Result sets22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Mon, 30 Sep 2002 10:35:12 -0700337_iso-8859-1 Hello Gurus, I was finally able to determine that the problem was related to the compilation not suitable for WLM env. The code works with DB2 spa. I am now trying to see what I need to do to make it work in WLM region Can some body send me a JCL to compile a SQL stored procedure with WLM region please ? Thanks! Ramesh [...] 976 64 14_DB2 Connection12_Davari.Ahmad26_Davari.Ahmad@AAA-CALIF.COM31_Mon, 30 Sep 2002 10:36:16 -0700368_us-ascii We need to keep a DB2 connection to an application open to avoid the delay of repeated connections. The application is on a server that is connected to DB2 (V6) on OS/390 via DB2 CONNECT (V5.0) and DDF, and the application is referenced intermittently during the day. Any recommendations? Thanks in advance, Ahmad Davari Automobile Club of Southern California 1041 77 18_Re: DB2 Connection13_Jackson, Phil21_PJackson@TXFB-INS.COM31_Mon, 30 Sep 2002 12:56:22 -0500552_- That seems rather expensive, to keep that connection open all day. We mandate that all ASP applications must explicitly close their connections as soon as possible, since we have a limited number.

Phil Jackson









We need to keep a DB2 connection to an application open to avoid the delay of repeated connections. The application is on a server that is connected to DB2 (V6) on OS/390 via DB2 CONNECT (V5.0) and DDF, and the application is referenced intermittently during the day. Any recommendations? [...] 1119 47 18_Re: DB2 Connection12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 30 Sep 2002 10:59:17 -0700536_us-ascii Bear with me, but my first question is if the application is only referenced intermittently during the day, why the need to worry about the overhead of re-connecting. Reconnection overhead is relatively minimal. Certainly, if you're doing it hundred/thousands of time per day, minimal can add up to a very large number. But only a few times per day, I doubt if you can measure at the application user's tube, the difference in response times between having a reserved open connection and opening the connection each time. [...] 1167 27 85_Does FETCH for a result set by a Client cause Network traffic b etween Client and DB222_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Mon, 30 Sep 2002 11:12:24 -0700505_iso-8859-1 DB2 Gurus, I was asked this question by a Developer

When retrieving multiple result sets returned by a DB2 Stored procedure, a client allocates multiple cursors to 'Associate' the result sets with different cursors. The CURSORS (based on locator variables ) are fetched by the client to retrieve result sets.

Question: When the client does the "fetch" on the cursors, does that cause Network Traffic between the DB2 Client and DB2 (or DB2 Connect server). Please let us know [...] 1195 23 30_udf, ORACLE date format to UDB14_Linda Hagedorn30_Linda.Hagedorn@EMBARCADERO.COM31_Mon, 30 Sep 2002 11:17:18 -0700635_iso-8859-1 I have input data in this format, and need a UDF (user defined function) to translate data extracted from Oracle into a UDB date-recognizable format?

insert into customer values (48,'Emmitt','Smith','9725569900','Florida','Dallas','TX','75063',udf-to -translate('15-MAY-69'),...

Has anyone already written one they're willing to share?

Thanks, Linda

===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 1219 89 18_Dataspace Question22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Mon, 30 Sep 2002 11:15:54 -0700417_-

Hi listers,

Ok, I'm a moron and I admit it.. I found the answer in the admin guide. I swear I read the manual first, but just missed this paragraph.. Oh well, it is Monday..





Marty Killen Sr. DB2 DBA CNF Inc. 503-450-2681 killen.martin@cnf.com IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 DB2 V7.1 Database Administration for UNIX, Windows and OS/2 1309 37 23_Re: Dataspaces Question14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 30 Sep 2002 14:28:43 -0400415_ISO-8859-1 Marty, Can't use HP's with dataspaces.

Regards, Joel

Message text written by DB2 Data Base Discussion List > We have a question that I cannot find a definite answer to, so maybe a few of you have also ran into this. With dataspaces is it advisable to have any allocation in the HP's. I had thought that it was not advisable to have an HP allocation. We are V6, OS/390/ZOS-64 bit. TIA. [...] 1347 226 18_Re: DB2 Connection12_Davari.Ahmad26_Davari.Ahmad@AAA-CALIF.COM31_Mon, 30 Sep 2002 11:47:03 -0700380_us-ascii Myron,

I checked with the application team and the number of references to the application is in the range of 8000-20000 per day, so it is more than the 'intermittently' I had in my question.

Secondly, we are using a dummy select statement now, but we want to get rid of it and save its overhead of executing a dynamic sql and referencing the catalog. [...] 1574 117 18_Re: DB2 Connection12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 30 Sep 2002 12:07:28 -0700626_us-ascii Davari, In my opinion, this is another argument for going from V5 to V7 of DB2 Connect. V7 has connection pooling and connection concentration features. Then your app could connect and disconnect to the pool and the overhead for the connections would be miminized.

Plus I still don't understand the applications needs for keeping the connection open and "reducing the connection overhead". What type of application is actually running? Is it a VB app, web app, ASP or what? And what type of average response time is it currently experiencing? I just seriously doubt that the overhead for connections will [...] 1692 100 88_Re: Does FETCH for a result set by a Client cause Network traffic between Client and DB20_15_leon@CA.IBM.COM31_Mon, 30 Sep 2002 15:33:34 -0400343_us-ascii As usual, the answer is "it depends". The behavior is different for blocked and non-blocked cursors. If a cursor you are fetching from is not blocked then each fetch will result in a network flow. This will not be good for performance and that is why you will want to make sure that the cursor you are using is a blocked cursor. [...] 1793 201 55_Re: SQL - PL stored procedure not returning Result sets19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Mon, 30 Sep 2002 15:32:30 -0400478_US-ASCII Ramesh, DB2 requires that OS/390 resource recovery services (RRS) be active as WLM-established stored procedure address spaces use RRS attachment facility (RRSAF), not the call attachment facility (CAF) used for DB2-established stored procedure address space. Load modules for the Stored procedures must be link-edited with DSNRLI instead of DSNALI.

HTH



Murari Selvakesavan. Data Resource Management. First Health Services Corp. 804.965.7601 [...] 1995 100 20_CICS/DB2 transaction10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 30 Sep 2002 14:15:57 -0600586_iso-8859-1 Hi List, We are new CICS/DB2 shop. Our programmer developed a CICS program A which link to program B. Only program B has the DB2 call. Program A has transaction TRNA associate with it. We defined DB2TRAN entry with transaction id TRNA and point to the DB2ENTRY which execute plan DB2PLAN. Program B's DBRM bind into plan DB2PLAN. When we execute TRNA, we got error message : FMH**ERROR 00069SECURITY CHECK We couldn't find any other error message on CICS job log, DB2 job log and Console log. Does anybody there have this error message before? Do we have nay definition [...] 2096 24 14_Varchar insert12_Mark Anzmann24_manzmann@DHR.STATE.MD.US31_Mon, 30 Sep 2002 15:18:27 -0500439_- DB2 Listers-

Our environment is OS/390 2.10 DB2 V7

We have a Java application that is trying to insert data to a table with a varchar(2000) column. As long as the data to be inserted is less than 255, the insert works fine. When the data exceeds 255, the program gets -102. This program works when run against DB2/NT (with column > 255). I have tried passing a length in the varchar column data, but this didn't work. [...] 2121 30 17_Monitoring in UDB15_Sniderman, Karl21_ksniderman@BCBSOK.COM31_Mon, 30 Sep 2002 16:12:40 -0500323_iso-8859-1 I have been working with OS/390 DB2 for many years. I know how to use DB2I to "-Display database(name)" to see the status of the tablespaces. Now we are installing a UDB/AIX/Windows for PeopleSoft. How do I do the equivalent Display command in that environment - or, if you prefer, what manual explains it? [...] 2152 49 18_Re: Varchar insert16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 30 Sep 2002 17:05:27 -0400710_iso-8859-1 Are you perchance inserting from a literal (string constant) rather than from a host variable?



Regards, Eric Pearson NS ITO DB2 support



-----Original Message----- From: Mark Anzmann [mailto:manzmann@DHR.STATE.MD.US] Sent: Monday, September 30, 2002 4:18 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Varchar insert



DB2 Listers-

Our environment is OS/390 2.10 DB2 V7

We have a Java application that is trying to insert data to a table with a varchar(2000) column. As long as the data to be inserted is less than 255, the insert works fine. When the data exceeds 255, the program gets -102. This program works when run against DB2/NT (with [...] 2202 72 21_Re: Monitoring in UDB0_15_leon@CA.IBM.COM31_Mon, 30 Sep 2002 17:48:18 -0400708_us-ascii Have you tried Control Center?

Leon Katsnelson Manager, DB2 Product Planning mailto:leon@ca.ibm.com



|---------+----------------------------> | | "Sniderman, Karl"| | | | | | Sent by: DB2 Data| | | Base Discussion | | | List | | | | | | | | | | | | 09/30/2002 05:12 | | | PM | | | Please respond to| | | DB2 Data Base | | | Discussion List | | | | |---------+----------------------------> >------------------------------------------------------------------------------------------------------------------------------------| | | | To: DB2-L@LISTSERV.YLASSOC.COM | | cc: | | Subject: Monitoring in UDB | | | | [...] 2275 183 18_Re: Varchar insert13_Jackson, Phil21_PJackson@TXFB-INS.COM31_Mon, 30 Sep 2002 16:54:43 -0500632_iso-8859-1 Isn't 255 the max size of a varchar column???

Phil J.



-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Monday, September 30, 2002 4:05 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Varchar insert



Are you perchance inserting from a literal (string constant) rather than from a host variable?



Regards, Eric Pearson NS ITO DB2 support



-----Original Message----- From: Mark Anzmann [mailto:manzmann@DHR.STATE.MD.US] Sent: Monday, September 30, 2002 4:18 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Varchar insert [...] 2459 72 24_Re: CICS/DB2 transaction0_19_csutfin@AMSOUTH.COM31_Mon, 30 Sep 2002 17:04:41 -0500782_us-ascii Which CICS are you on?



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







Grace Chen cc: Sent by: DB2 Data Subject: CICS/DB2 transaction Base Discussion List



09/30/02 03:15 PM Please respond to DB2 Data Base Discussion List









Hi List, We are new CICS/DB2 shop. Our programmer developed a CICS program A which link to program B. Only program B has the DB2 call. Program A has transaction TRNA associate with it. We defined DB2TRAN entry with transaction id TRNA and point to the DB2ENTRY which execute plan DB2PLAN. Program B's DBRM bind into [...] 2532 51 18_Re: Varchar insert10_Shery Hepp17_schepp@SRPNET.COM31_Mon, 30 Sep 2002 15:36:46 -0700450_ISO-8859-1 Mark- we had this issue with a powerbuilder app trying to do the same thing. I think it had to do with the odbc driver not supporting columns > 255. However we also have peoplesoft and they have some columns that greatly exceed 255 bytes and they insert the data just fine. I think it has something to do with what module(?) is called to format the sql in the odbc driver? (I'm not a middleware person and am recalling from long ago) [...] 2584 96 50_Re: Best way to Deal with CICS/DB2 runaway tasks ?13_Dwiyono, Hend20_Hend.Dwiyono@TRW.COM31_Mon, 30 Sep 2002 16:58:44 -0600420_iso-8859-1 Thanks to Gregg, Walter, Dr. Ebert, Tina, and others who provided feedback in this matter.

I had my hopes up in using RLSTs but it looks like it only applies to dynamic SQLs. Our system staff also received feedback from IBM mailing list specifically on using CICS ICVR parameter. If there is a better way to handle these runaway tasks using CICS ICVR parameter, I will share it with the listserv. [...] 2681 80 18_Re: Varchar insert9_Timothy C26_timothy_curley@HOTMAIL.COM31_Mon, 30 Sep 2002 23:04:53 +0000633_- If you are going to insert a value longer than 255 bytes you have to use a variable...not a literal string. In the 390 world a literal cannot be > 255.

If I'm wrong someone correct me....

Tim.



>From: Shery Hepp >Reply-To: DB2 Data Base Discussion List >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Re: Varchar insert >Date: Mon, 30 Sep 2002 15:36:46 -0700 > >Mark- we had this issue with a powerbuilder app trying to do the same >thing. >I think it had to do with the odbc driver not supporting columns > 255. >However we also have peoplesoft and they [...] 2762 33 34_Fwd: auto-increment in EEE UDB 7.213_Abi Manoharan33_Abi.Manoharan@PROTEOMESYSTEMS.COM30_Tue, 1 Oct 2002 09:47:13 +1000329_US-ASCII ### *-------------------------------------* >"""""< !_ _! (o) (o) oOOO--(_)--OOOo-----------------------* * * * Abi Manoharan * * Snr Database Admin * * Proteome Systems Limited * * Phone: 02 - 8877 8970 * * EMail: abi@proteomesystems.com * * * * .oooO * *--( )--Oooo.-----------------------* * \ ( ( ) * * \_) ) / [...] 2796 77 31_Re: DB2 V7 LOAD sortkeys issues22_Naidoo, Rama [IBM GSA]28_Rama.Naidoo@TEAM.TELSTRA.COM30_Tue, 1 Oct 2002 10:00:58 +1000358_- Hi Eric,

As Martin stated in his reply the n in SORTKEYS is passed to DFSORT in FILEZ parameter. In your case you have three keys but you limit yourself to 4 parallel tasks ( by only providing two sets of SWnnWKnn datasets ). You may improve your SORTBLD phase by providing three sets of SWnnWKnn datasets and the associated UTPRINnn datasets. [...]