1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 2006, week 5 2 155 77_Baltimore/Washington DB2 Users' Group--Meeting Schedule for December 13, 200640_Henry Nalven-Baltimore-Washington UG Ldr25_henry.nalven@MARRIOTT.COM31_Sun, 29 Oct 2006 10:44:44 -0600513_ISO-8859-1 The Baltimore/Washington DB2 Users' Group Meeting Schedule for December 13, 2006 (Wednesday)

When? Wednesday, December 13, 2006, 9:00 AM-3:30 PM (Registration at 8:30)

Where? Sheraton Columbia Inn, 10207 Wincopin Circle Columbia, MD (410) 730-3900

Meeting Fees: Pre-registered members $20 Pre-registered non-members $30 At-the-door for members $30 At-the-door for non-members $40

To pre-register, please mail the appropriate fee with attendee's and employer's name to: [...] 158 280 58_db2 v8 NFM zOS - DSNL027I & DSNL028I - remote applications18_Maniar, Saquib TCS27_saquib.maniar@IMPDIRECT.COM31_Mon, 30 Oct 2006 11:04:05 +0100474_us-ascii Hi all, We've just migrated to NFM on one of our systems, and we're getting error messages in the MSTR when remote applications connect to DB2 on the mainframe.

DSNL027I =DCAP SERVER DISTRIBUTED AGENT WITH 462 LUWID=AC1E12AA.JC0D.061030073359=3330 THREAD-INFO=USSIEMM:NTS0ZZZZZ:ussieMM:pmdtm.exe RECEIVED ABEND=04E FOR REASON=00D31010 DSNL028I =DCAP AC1E12AA.JC0D.061030073359=3330 463 ACCESSING DATA FOR LOCATION 172.XXX.YYY.ZZZ IPADDR 172.XXX.YYY.ZZZ [...] 439 87 27_Re: Idle connection timeout18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 30 Oct 2006 06:27:04 -0500340_iso-8859-9 Did you try QueryTimeoutInterval=x setting in your db2cli.ini file?

We have querytimeoutinterval=0 for ours so that DB2 Connect does not time out any threads. We handle our timeouts on the z/OS side with our idle thread timeout zparm. I don't remember what the querytimeoutinterval default value is but its not much. [...] 527 127 14_Query for Copy14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Mon, 30 Oct 2006 13:35:59 +0200678_us-ascii Hi listers

Does anyone has a query from syibm.tablespacestats in order to get the tablespaces that really need to backup(Copy utility)

We are in DB2 V7 for z/OS



Disclaimer: This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system. EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation, offer or agreement or any information contained in this communication. EFG Eurobank Ergasias S.A. cannot accept any [...] 655 395 18_Re: Query for Copy12_HEPP SHERY C17_schepp@SRPNET.COM31_Mon, 30 Oct 2006 08:30:49 -0700638_us-ascii We have a query that we run against syscopy that tells us if we have any tablespaces that need an image copy? Is that what you're looking for? If so- here's the query:





SELECT TB.DBNAME,TB.NAME

FROM

SYSIBM.SYSTABLESPACE TB

WHERE TB.DBNAME NOT IN

('DSNDB06','DSNDB04','DSNDB07','VRADB001','VRADB002'

,'MCNTSN10','MCNTSN20','DSN8D71A','WORKDB2','DSNHMDB','DSNATPDB'

,'WCNTSU10','WCNTSU20','DSN8D71P','DSN8D71U')

AND TB.NAME <> 'SYSCOPY'

AND NOT EXISTS

(SELECT 1 FROM SYSIBM.SYSCOPY SC

WHERE ICTYPE= 'F'

AND SC.TSNAME = TB.NAME [...] 1051 157 31_DB2 V8 z/OS (ENFM) Broken Pages15_Moss, William R15_MossW@AETNA.COM31_Mon, 30 Oct 2006 10:38:53 -0500435_US-ASCII We had image copy of a partitioned tablespace fail on one of the partitions because of two broken pages in that partition. Some of the particulars of the tablespace are it has two hundred partitions containing a total of153 million pages and 1.8 billion rows and is defined with DSSIZE 64 G and COMPRESS YES. Someone else has the job of determining what broke the pages but my task is to figure out how to fix the pages. [...] 1209 207 21_Re: Access path in V813_Maulik, Tapan26_Tapan.Maulik@COURTS.WA.GOV31_Mon, 30 Oct 2006 08:12:51 -0800442_us-ascii This APAR PK31358 exactly matches our problem. When we are binding our packages using V8 libraries, in some cases the optimizer is picking up unwanted indexes, where multiple indexes are defined.

Thanks,

Tapan Maulik

-----Original Message----- From: Terry Purcell [mailto:tpurcel@US.IBM.COM] Sent: Saturday, October 28, 2006 6:28 AM To: DB2-L@WWW.IDUGDB2-L.ORG; Maulik, Tapan Subject: Re: Access path in V8 [...] 1417 126 35_Re: DB2 V8 z/OS (ENFM) Broken Pages9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 30 Oct 2006 10:31:42 -0600534_us-ascii BTDTGTTS - and it isn't much fun being the guy with the cape on.

1. open an incident with IBM - they have people who handle these kinds of problems more than once every couple of years. The next 3 steps, you will need the information for the review so keep the output as datasets. 2. I don't know how much update activity this table has but my starting point would be a report recovery to figure out how many archive logs the recover would have to process. 3. I might go ahead and put together a DSN1LOGP to pull [...] 1544 43 45_Determining partition number from page number13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM31_Mon, 30 Oct 2006 12:24:24 -0600588_- Hello,

What's the best way to determine what partition a given page that's involved in a deadlock belongs to?

We sometimes see messages like the following:

DSNT501I -DB3P DSNILMCL RESOURCE UNAVAILABLE CORRELATION-ID=CS070DIP CONNECTION-ID=DB2CALL LUW-ID=* REASON 00C9008E TYPE 00000302 NAME CIMSPRD1.CU02TS01.X'00C01816'

Since this is for a 32 partition tablespace, based on the DB2 Diagnosis Guide, I thought this page belonged to partition 1 (1st 5 bits 00000) but, when I run the following DSN1PRNT job, it's asking for the dataset for partition 25: [...] 1588 106 17_COBOL COPROCESSOR25_Miguel Del Valle Martinez24_mdelvalle@EVERTECINC.COM31_Mon, 30 Oct 2006 15:59:24 -0400822_us-ascii Hi folfs





I am trying to use the Cobol Enterprise Coprocessor for DB2 it woks but I don see the output from the

SQL/CA include statement





Doe any body have the options to make this works.









Regards





MIGUEL











----------------------------------------- CONFIDENTIALITY NOTE: This email communication and its attachments contain information that are proprietary and confidential to EVERTEC, INC., its affiliates or its clients. They may not be disclosed, distributed, used, copied or modified in any way without EVERTEC, Inc.s authorization. If you are not the intended recipient of this email, you are not an authorized person. Please delete it and notify [...] 1695 115 45_Determining partition number from page number17_McCormack, Mark A27_mamccormack@STATESTREET.COM31_Mon, 30 Oct 2006 15:52:41 -0500525_us-ascii Donna,

The formula I use is this: Take the first 3 hex digits of the page number (in your case 00C) Convert from hex to decimal (C --> 12) Add 1 to get partition number (13)

This works for us with 4k pagesize, EBCDIC, single byte character set, partitions up to 254. In your case, the calculated number is about half what DSN1PRNT seems to expect. I am puzzled. Are you using larger pagesize, unicode or ascii, or DBCS? I don't know if something like that could cause the symptoms you are seeing. [...] 1811 167 22_COBOL & GET DIAGNOSTIC53_Toppins, Smike --- Sr. Database Administrator --- CFS31_smike.toppins@FREIGHT.FEDEX.COM31_Mon, 30 Oct 2006 14:00:31 -0700711_us-ascii Does anyone have a COBOL example that does a GET DIAGNOSTICS they would be willing to share? I've been searching through the manuals but have been unable to decipher the correct WORKING STORAGE information.





TIA

SMike

_____





CONFIDENTIALITY NOTE: This email communication and its attachments contain information that are proprietary and confidential to EVERTEC, INC., its affiliates or its clients. They may not be disclosed, distributed, used, copied or modified in any way without EVERTEC, Inc.s authorization. If you are not the intended recipient of this email, you are not an authorized person. Please delete it and notify the sender [...] 1979 407 26_Re: COBOL & GET DIAGNOSTIC13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Mon, 30 Oct 2006 21:24:08 -0500317_us-ascii Hello SMike,

Here's some very old code I used in the early days of v8 to poke around with GET DIAGNOSTICS. It worrks. But it's probably not the details you are seeking. There are no details, because I depended on our Xpediter debugger to pause the program and examine the output from Get Diag... [...] 2387 48 16_DB2 for z/OS 9.110_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Tue, 31 Oct 2006 09:08:00 +0100739_US-ASCII Greetings

If interested, there are some new papers dealing with DB2 V9 for z/OS (and beyond) in DB2 FTP site. Among them a paper about CCSID and new z/OS 1.7 (UNICODE on demand)

You already knew it ? Sorry ......

Regards

Max Scarpa

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 2436 92 28_AW: [DB2-L] DB2 for z/OS 9.112_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 31 Oct 2006 09:14:55 +0100603_us-ascii Try http://www-306.ibm.com/software/data/db2/zos/

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r Vice Chair GSE Workinggroups DB2 Nord und Sued ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 2529 26 25_convert Timestamp to LRSN20_Streit Marc (KITD 2)31_marc.streit.2@CREDIT-SUISSE.COM31_Tue, 31 Oct 2006 11:34:59 +0100330_us-ascii Hello @ DB2L

I am looking for a procedure to convert a timestamp value to the LRSN format (Log Record Sequence Number).

The opposite way around from LRSN to Timestamp we are using a small REXX published at the list earlier. Does anyone have a little program for my requirements?

Thanks for help [...] 2556 63 26_DB2 for AIX, datasharing ?58_=?iso-8859-9?Q?Serdar_Sabri_=D6zkubulay_=28B.T.S.Y.G.=29?=27_Serdar.Ozkubulay@AKBANK.COM31_Tue, 31 Oct 2006 14:26:33 +0200322_iso-8859-9 Hi listers,

Thanks god, we have got data sharing on z/OS for DB2, is this feature available for DB2 for AIX in the new versions of it? Or, what is the common solution on the DB2 for AIX, if you need new engines to distribute the increasing workload?

Thanks in advance

Serdar OZKUBULAY [...] 2620 69 17_DB2 (LUW) and TSM0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Tue, 31 Oct 2006 08:31:51 -0500596_US-ASCII We're having a philosophical difference of opinion between the DBA's and the TSM administrators at our shop, so I'd like to get some feedback from other folks on the list.

We currently use TSM as our backup and archive log solution for our DB2 LUW environment. We're running DB2 v8.2 on both AIX and Solaris, and most of our scheduled database backups go directly to TSM, as do most of our log archives, using the integrated DB2/TSM api built into DB2. Our (the DBA's) recommendation for most of our application developers has also been to perform their load utilities using [...] 2690 97 21_Re: DB2 (LUW) and TSM11_Davies, Ian31_Ian.Davies@CGI.HEALTH.GOV.AB.CA31_Tue, 31 Oct 2006 07:17:19 -0700352_US-ASCII Hi Bill,

I would have to agree with you. TSM should be available 24x7, not necessarily for backups but for restores. If your production archive logs are on TSM then you'd better be able to get them back quickly. Sounds like the two groups don't share the same view of how backup and recovery is/should be implemented at your shop. [...] 2788 93 30_Re: DB2 for AIX, datasharing ?0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Tue, 31 Oct 2006 09:28:01 -0500578_windows-1254 DPF (Data Partitioning Facility) is the solution for dis Serdar,

DPF (Data Partitioning Facility) is the solution for distributing workload across multiple servers for a DB2 database on AIX. This differs from data sharing on z/OS in that it uses a hash partitioning scheme to physically spread the data across multiple partitions (nodes, or servers), which each have their own DB2 engines, or instances, managing them. All of these multiple physical nodes are logically treated as a single database via a coordinator node, through which all work flows. [...] 2882 120 21_Re: DB2 (LUW) and TSM13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Tue, 31 Oct 2006 14:30:09 +0000641_- With respect to archive logs you can use a more flexible approach by archiving logs to disk and then periosdically backing up the logs to TSM and then deleting old logs from disk. Thus when TSM is down you still archive logs.

>From: "Davies, Ian" >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] DB2 (LUW) and TSM >Date: Tue, 31 Oct 2006 07:17:19 -0700 > >Hi Bill, > >I would have to agree with you. TSM should be available 24x7, not >necessarily for backups but for restores. If your production archive >logs are on [...] 3003 78 38_Indiana DB2 Users Group Meeting Nov 1412_Matthew Rhea24_matthew_rhea@CONSECO.COM31_Tue, 31 Oct 2006 08:26:20 -0600310_ISO-8859-1 The Indiana DB2 Users Group will have a meeting on Tuesday, November 14 at the Conseco Conference Center in Carmel, IN. IBM will provide speakers on DB2 V8 for z/OS features and on DB2 9 for LUW.

Please be sure to pass this information on to others who may not be IDUG or InDUG members. [...] 3082 28 36_Exit Cleanly from a Stored Procedure11_Matt Steele21_matt.steele@MEDRY.COM31_Tue, 31 Oct 2006 08:37:13 -0600538_- We are running into the case where we are experiencing an error in our Stored procedure that causes the Stored procedure to actually terminate. Is there any way we can cause the Stored procedure to error but not actually terminate?

We can cleanly exit the program for invalid SQL Codes but in this we are calling a function in the actual COBOL code with an invalid value. It is erroring out because the value is not valid but it was not trapped so it bombs the Stored Procedue which in turn makes the Stored Procedure Stop. [...] 3111 17 44_DB2 for z/OS 8.1 & Declared Temporary Tables17_Chris Worthington20_cworthi@AGRIBANK.COM31_Tue, 31 Oct 2006 08:51:32 -0600369_- Has anyone encountered performance issues with Declared Global Temporary tables in V8? We are in compatibility mode and think we may be seeing a degradation in query performance. It may be nothing more than different access paths chosen by the v8 optimizer but I seem to recall there were one or more APAR's for V7 that were necessary to address similar issues. [...] 3129 304 21_Re: DB2 (LUW) and TSM13_Michael Ebert18_mebert@AMADEUS.COM31_Tue, 31 Oct 2006 16:08:02 +0100591_US-ASCII Forcing both DB2 and TSM to be up (or down) at the same time creates an unnecessary dependency and greatly decreases your flexibility. With the good old mainframe, you archive to disk and HSM periodically comes along and moves files off to tape. Works smoothly.

With regards to having TSM continuously available, that would be nice, but any SW needs service. It is easier BY FAR if you can simply take down the server, apply a patch and start it up again without going through the *enormous* hassle of getting half a dozen departments to agree to a simultaneous outage. [...] 3434 415 21_Re: DB2 (LUW) and TSM11_Davies, Ian31_Ian.Davies@CGI.HEALTH.GOV.AB.CA31_Tue, 31 Oct 2006 08:58:39 -0700446_US-ASCII The key question is "is TSM available 24x7?", if it isn't then don't use it for log archiving, either directly or via HSM as you can't rely on it. Asynchronous movement of backups and logs to TSM is generally okay but restores/recalls better be synchronous.

On the mainframe HSM is generally available 24x7 plus it's much easier to archive directly to tape as DB2 can handle it itself, not usually the case in the LUW world. [...] 3850 58 29_Re: convert Timestamp to LRSN9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Tue, 31 Oct 2006 09:40:25 -0600362_us-ascii I use this spufi to convert the RBA to a hex LSRN.

SELECT DBNAME, TSNAME, HEX(START_RBA) , DSNAME, ICTIME FROM SYSIBM.SYSCOPY WHERE DBNAME = 'XXXXXXXX' AND TSNAME = 'XXXXXXXX' ORDER BY TIMESTAMP ;

Hope this helps

If this is not perspicuous please let me know. Dee Reins 360 905-7343 Business 360 905-7212 Fax 360 608-5262 Cell [...] 3909 27 6_Z to P13_Malik, Munwar31_munwar.malik@ASSOCIATES.DHS.GOV31_Tue, 31 Oct 2006 11:09:46 -0500345_US-ASCII Hi Folks:

Here is scenario: - I have connected z/db2 to p/db2. No problem. I can run spufi from z to access p database. - Base tables are on P. - I have created Aliases on Z. - A PLAN was bound on P and Z with proper collection ids. Now A COBOL program is getting -206 in Bind process for P/db2. Table is on P. Any answers. [...] 3937 123 47_Re: Example DDL for creating LOB objects needed23_BOLEY Maurice C * Cliff32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Tue, 31 Oct 2006 09:13:52 -0800675_us-ascii Georg, I used this a long time ago to create some test tables.

-- test table tablespace -- CREATE TABLESPACE SDOT6LOB IN TDOT6LOB BUFFERPOOL BP9 USING STOGROUP GTTDOT6 ; COMMIT; -- -- create table w/rowid and blob -- CREATE TABLE TDOT6LOB.TBLDOT6 (EMPNO CHAR(6) NOT NULL ,FIRSTNME VARCHAR(10) ,MI CHAR(1) ,LASTNAME VARCHAR(10) ,DEPT CHAR(3) ,DB2 CHAR(8) ,DATABASE CHAR(8) ,TABLE CHAR(8) ,INSERTDT TIMESTAMP ,WHO CHAR(8) ,LOBROW ROWID GENERATED ALWAYS NOT NULL ,EMPIMG BLOB (1M) ) IN TDOT6LOB.SDOT6LOB ; -- CREATE TYPE 2 UNIQUE INDEX TDOT6LOB.IDXDOT6 ON TDOT6LOB.TBLDOT6 (EMPNO) BUFFERPOOL BP9 ; -- -- create lob tablespace, aux table and index -- CREATE [...] 4061 77 10_Re: Z to P0_25_scott.taylor@AUTOZONE.COM31_Tue, 31 Oct 2006 11:15:19 -0600563_us-ascii Have you double checked to make sure you have the proper column names coded? Usually this SQL code means you are looking for a non-existant column name. ----------------------------------------------------------------------------------------------------

Scott Taylor Systems Programmer, Autozone 123 S Front St, Memphis, TN 38103 (901)/495-7797







"Malik, Munwar" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List [DB2-L] Z to P [...] 4139 37 42_DB2 z/OS: Unload delimited without Quotes?11_Mike Jessen22_jessenmike@HOTMAIL.COM31_Tue, 31 Oct 2006 11:48:27 -0600462_- Env: z/OS DB2 V8 (Compat mode).

Is there a way to unload data without having it default to putting quotes (or some other character) around every data item:

Instead of this: "00000001","10473", "00000007","10473", "00000009","10473",

I want this: 00000001,10473, 00000007,10473, 00000009,10473,

According to the manual: The UNLOAD utility adds the CHARDEL character before and after every character string. Can I override that? [...] 4177 64 46_Re: DB2 z/OS: Unload delimited without Quotes?12_McKown, John29_John.Mckown@HEALTHMARKETS.COM31_Tue, 31 Oct 2006 12:07:06 -0600591_US-ASCII > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Jessen > Sent: Tuesday, October 31, 2006 11:48 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] DB2 z/OS: Unload delimited without Quotes? > > > Env: z/OS DB2 V8 (Compat mode). > > Is there a way to unload data without having it default to > putting quotes > (or some other character) around every data item: > > Instead of this: > > > I want this: > 00000001,10473, > 00000007,10473, > 00000009,10473, > > According to the manual: > The UNLOAD utility adds [...] 4242 119 26_Re: COBOL & GET DIAGNOSTIC11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 31 Oct 2006 12:42:43 -0600758_- Smike,

I know for surethat the APG/SQL ref is NOT correct in a few places (I think it is 18 digits vs 31). Here is the declartion that does work. Contact me offline if questions -

01 WS-GD-STUFF. 05 WS-SQLCODE PIC S9(9) COMP VALUE 0. 05 WS-TOKEN-COUNT PIC S9(9) COMP VALUE 0. 05 WS-ORDINAL-TOKEN-1. 49 WS-ORDINAL-TOKEN-1-LEN PIC S9(4) COMP. 49 WS-ORDINAL-TOKEN-1-TXT PIC X(515). 05 WS-ORDINAL-TOKEN-2. 49 WS-ORDINAL-TOKEN-2-LEN PIC S9(4) COMP. 49 WS-ORDINAL-TOKEN-2-TXT PIC X(515). 05 WS-ORDINAL-TOKEN-3. 49 WS-ORDINAL-TOKEN-3-LEN PIC S9(4) COMP. 49 WS-ORDINAL-TOKEN-3-TXT PIC X(515). 05 WS-ORDINAL-TOKEN-4. 49 WS-ORDINAL-TOKEN-4-LEN PIC S9(4) COMP. 49 WS-ORDINAL-TOKEN-4-TXT PIC X(515). 05 WS-ORDINAL-TOKEN-5. 49 WS-ORDINAL-TOKEN-5-LEN [...] 4362 39 29_Re: convert Timestamp to LRSN9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Tue, 31 Oct 2006 13:02:12 -0600441_iso-8859-1 I use this spufi to convert the RBA to a hex LSRN.

SELECT DBNAME, TSNAME, HEX(START_RBA) , DSNAME, ICTIME FROM SYSIBM.SYSCOPY WHERE DBNAME = 'DATBASE1' AND TSNAME = 'TBLSPCE' ORDER BY TIMESTAMP ;



I find the last START_RBA to put in my recover list.

Recovery control cards.

LISTDEF TR01RCVA INCLUDE TABLESPACE DATABASE1.* RECOVER LIST TR01RCVA TOLOGPOINT (X'BCC047F8455A') ç HEX(START_RBA) [...] 4402 176 29_Re: convert Timestamp to LRSN14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Tue, 31 Oct 2006 13:39:53 -0600502_ISO-8859-1 Dee, do you use the MAX(HEX(START_RBA) from the resultset ?









Dee Reins Sent by: DB2 Data Base Discussion List 10/31/2006 01:02 PM Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: convert Timestamp to LRSN











I use this spufi to convert the RBA to a hex LSRN. [...] 4579 224 46_Re: DB2 z/OS: Unload delimited without Quotes?13_Horacio Villa17_hvilla@AR.IBM.COM31_Tue, 31 Oct 2006 17:25:39 -0300447_US-ASCII You can use (almost) any other character you define for CHARDEL. But it's not clear for me if you want to do an undelimited delimited Unload, or just change the quote.

Cheers,

Horacio Villa





"McKown, John" Sent by: DB2 Data Base Discussion List 31/10/2006 15:07 Please respond to DB2 Database Discussion list at IDUG [...] 4804 320 61_DB2 Informix Education December 8 & 9 for $80 - Register Now!11_Dave Beulke17_DaveBeulke@CS.COM29_Tue, 31 Oct 2006 15:39:41 EST631_UTF-8 A two-day IBM Informix and DB2 User Group Technical Conference - Friday and Saturday, December 8-9, 2006

Pre-Registration: $80 - must be received by November 1, 2006 Onsite Registration: $120 - all registrations after November 1, 2006

****************************************************************************** ******************** Early registration ends 11/1/2006! - Register now for the best value in Database User Conferences! ****************************************************************************** ******************** Web site at: http://www.iiug.org/waiug/present/Forum2006/Forum2006.html [...] 5125 137 36_DB2 Z/os connecting to MSSQL. ORACLE12_Rosina Porco17_rporco@UOTTAWA.CA31_Tue, 31 Oct 2006 15:49:54 -0500405_US-ASCII We are a DB2 /OS shop however we have SQL Server and Oracle, and the DBA(me) have to find a way to connect to MS SQL server(windows) and the Oracle(unix). We basically want to use DB2 StoredProcedure to select /update tables on the MSSQl db and Oracle db. I have updated DDF with the appropriate IPnames and Locations , but other than that I do not have a clue as to what needs to be done . [...] 5263 138 54_Fw: [DB2-L] DB2 z/OS: Unload delimited without Quotes?11_Mike Jessen22_jessenmike@HOTMAIL.COM31_Tue, 31 Oct 2006 15:05:34 -0600358_- I'm hoping to create a comma delimited file - one that separates the column values by a comma, but not a Quote comma quote... or any other character - just the comma.

Is this possible? Thanks! Mike

From Horacio Villa To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: [DB2-L] DB2 z/OS: Unload delimited without Quotes? [...] 5402 191 58_Re: Fw: [DB2-L] DB2 z/OS: Unload delimited without Quotes?21_Williams, Ray [WHQKO]23_Ray.Williams@UNITED.COM31_Tue, 31 Oct 2006 14:15:02 -0700419_US-ASCII I have a DRAW REXX command (IBM is original author) that I modified to add CSV (comma delimited) functionality. It works like the QMF draw command, but can be executed outside of QMF. This will do what you want.

Here is the syntax >>--DRmW-----tablename-----|---------------------------|------->< |-(-|-Ssid=subsystem-name-|-| | +-Select-+ | |-Type=-|-Insert-|----| |-Update-| |--Load--| +--CSV--+ [...] 5594 25 40_DB2 z/OS V.8 Declared Temp Tables and BP13_Robert Tilkes22_tilkesr@NATIONWIDE.COM31_Tue, 31 Oct 2006 15:20:15 -0600369_- I understand that I need to create a DB as temp. I also understand that I should create a TS for every page size. I also understand that the page size is controled by the BP selection in the TS definition. The questions I have are as follows:

1. In a Data sharing environment do I need to have a DB defined as temp for every member like I do for work DB? [...] 5620 235 29_Re: convert Timestamp to LRSN9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Tue, 31 Oct 2006 15:31:09 -0600355_us-ascii Not necessarily. Depending upon the DB2 system backup time there may be LSRN's that are after the time I want to recover. Our backups do a Quiesce, backup, and Quiesce. I use the LSRN from the second Quiesce. The DSNAME in the sql has the file containing the backup dataset that I am recovering from. This method uses fewest number of logs. [...] 5856 106 12_SQL question14_Sniatecki, Jim31_Jim.Sniatecki@ERIEINSURANCE.COM31_Tue, 31 Oct 2006 17:06:06 -0500455_us-ascii I know IN Oracle you have an INSTR column function. I cannot find the equivalent in DB2 z/OS V8.





Can you help?





I need to do a WHERE clause and if a certain character is in like position 4 of the column string then I want to

Select that row.





For example , if the column were veh_id_num and the 4 position in that column string had the character 'S' select that row. [...] 5963 426 58_Re: Fw: [DB2-L] DB2 z/OS: Unload delimited without Quotes?13_Horacio Villa17_hvilla@AR.IBM.COM31_Tue, 31 Oct 2006 19:19:48 -0300372_US-ASCII Hi Mike,

I copy this from V8 Utility Guide & Reference:

Figure 171 shows an example of a delimited file with non-delimited character strings. In this example, the column delimiter is a semicolon (;). Because the character strings do not contain the column delimiter character, they do not need to be delimited with character string delimiters. [...] 6390 230 16_Re: SQL question18_Leblanc, Francis C27_Leblanc.Francis@CON-WAY.COM31_Tue, 31 Oct 2006 14:26:15 -0800591_us-ascii Try

where substr(veh_id_num,4,1) = 'S'

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sniatecki, Jim Sent: Tuesday, October 31, 2006 2:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL question





I know IN Oracle you have an INSTR column function. I cannot find the equivalent in DB2 z/OS V8.





Can you help?





I need to do a WHERE clause and if a certain character is in like position 4 of the column string then I want to [...] 6621 39 21_Re: DB2 (LUW) and TSM9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Tue, 31 Oct 2006 16:31:56 -0600514_us-ascii I worked with Oracle and TSM for 5 years as an oracle DBA, and the last 2 years back on the mainframe as a DB2 dba and I also work as a TSM Administrator. First of all, My view is that all systems should be loosely coupled, ie if one system goes down, the others can fail. Work towards that goal. What we did with Oracle. For Oracle logs(like DB2) the logs went to their own file system on the oracle server. Every 15, or 20 minutes the file system was checked. If the file system was over 15% full, [...] 6661 424 82_MichaelM@TOWERSOFT.COM.AU - Email found in subject - Re: [DB2-L] DB2 (LUW) and TSM11_Davies, Ian31_Ian.Davies@CGI.HEALTH.GOV.AB.CA31_Tue, 31 Oct 2006 08:58:39 -0700446_US-ASCII The key question is "is TSM available 24x7?", if it isn't then don't use it for log archiving, either directly or via HSM as you can't rely on it. Asynchronous movement of backups and logs to TSM is generally okay but restores/recalls better be synchronous.

On the mainframe HSM is generally available 24x7 plus it's much easier to archive directly to tape as DB2 can handle it itself, not usually the case in the LUW world. [...] 7086 240 29_Re: convert Timestamp to LRSN9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Tue, 31 Oct 2006 15:31:09 -0600355_us-ascii Not necessarily. Depending upon the DB2 system backup time there may be LSRN's that are after the time I want to recover. Our backups do a Quiesce, backup, and Quiesce. I use the LSRN from the second Quiesce. The DSNAME in the sql has the file containing the backup dataset that I am recovering from. This method uses fewest number of logs. [...] 7327 315 82_MichaelM@TOWERSOFT.COM.AU - Email found in subject - Re: [DB2-L] DB2 (LUW) and TSM13_Michael Ebert18_mebert@AMADEUS.COM31_Tue, 31 Oct 2006 16:08:02 +0100591_US-ASCII Forcing both DB2 and TSM to be up (or down) at the same time creates an unnecessary dependency and greatly decreases your flexibility. With the good old mainframe, you archive to disk and HSM periodically comes along and moves files off to tape. Works smoothly.

With regards to having TSM continuously available, that would be nice, but any SW needs service. It is easier BY FAR if you can simply take down the server, apply a patch and start it up again without going through the *enormous* hassle of getting half a dozen departments to agree to a simultaneous outage. [...] 7643 82 71_MichaelM@TOWERSOFT.COM.AU - Email found in subject - Re: [DB2-L] Z to P0_25_scott.taylor@AUTOZONE.COM31_Tue, 31 Oct 2006 11:15:19 -0600563_us-ascii Have you double checked to make sure you have the proper column names coded? Usually this SQL code means you are looking for a non-existant column name. ----------------------------------------------------------------------------------------------------

Scott Taylor Systems Programmer, Autozone 123 S Front St, Memphis, TN 38103 (901)/495-7797







"Malik, Munwar" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List [DB2-L] Z to P [...] 7726 125 82_MichaelM@TOWERSOFT.COM.AU - Email found in subject - Re: [DB2-L] DB2 (LUW) and TSM13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Tue, 31 Oct 2006 14:30:09 +0000650_iso-8859-1 With respect to archive logs you can use a more flexible approach by archiving logs to disk and then periosdically backing up the logs to TSM and then deleting old logs from disk. Thus when TSM is down you still archive logs.

>From: "Davies, Ian" >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] DB2 (LUW) and TSM >Date: Tue, 31 Oct 2006 07:17:19 -0700 > >Hi Bill, > >I would have to agree with you. TSM should be available 24x7, not >necessarily for backups but for restores. If your production archive >logs are on [...] 7852 46 40_Re: DB2 Z/os connecting to MSSQL. ORACLE14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 1 Nov 2006 11:46:01 +1100302_US-ASCII DB2 uses a protocol known as DRDA to talk to other database systems. IBM managed to get DRDA accepted as a standard; but the other database vendors don't really like it - they want everyone to use their protocols. Sometimes they'll generate requests using DRDA - but not accept requests. [...] 7899 46 40_Re: Exit Cleanly from a Stored Procedure14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 1 Nov 2006 11:46:02 +1100339_US-ASCII Hmm, yes, wander over to the Language Environment manuals and look at CEEHDLR. This will enable you to intercept that abend and do your own thing. As you don't reveal the actual error, or what generates it, this might not fix your problem.

It will probably be easier to fix up the SP code to validate the user's text. [...] 7946 47 44_Re: DB2 z/OS V.8 Declared Temp Tables and BP14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 1 Nov 2006 11:46:02 +1100548_US-ASCII You don't actually need a TS with "every" page size. Try just 4K and 32K. Anything with rows too long for 4K will end up in 32K. Then if you find inordinate numbers of 5K rows you might try an 8K TS.

1) Yes. Unlike DSNDB07 every member's temp db needs a different name. 2) no more than for DSNDB07 3) Ideally your pool should be large enough to accommodate rows for the duration of the transaction, so when the table is implicitly dropped there has been no actual write activity. Putting them into the same pools as "every day [...] 7994 31 25_Archiving Historical Data15_Barbara Jo Nigh26_barbara.j.nigh@US.HSBC.COM31_Tue, 31 Oct 2006 18:01:27 -0800306_US-ASCII In our Data Warehouse we have partitions containing historical data that is rarely used. Our DASD team has rejected my request to use HSM to migrate these files to tape until needed. Are there any other automated options for moving rarely used DB2 files to tape and back to disk when needed? [...] 8026 40 35_Expats (Brazil/Costa Rica/Columbia)27_DB2 Database Solutions Port26_db2dba_certified@YAHOO.COM31_Tue, 31 Oct 2006 19:02:48 -0800434_iso-8859-1 Hello, I am an MBA student from George Washington University. I am currently doing a project for International HR Management on "Brazil, Costa Rica and Columbia" countries. This project needs collecting experiences from Expats (US citizens worked or working in any one of the countries).I know many of you have/had such international work experience. If you have/had such international work experience, kindly assist. [...] 8067 73 29_Re: Archiving Historical Data9_Tony Saul30_generalemaillists@YAHOO.COM.AU30_Wed, 1 Nov 2006 14:10:18 +1100633_iso-8859-1 I was just being asked about DB2 Data Archive Expert yesterday, so I still had this page up (http://www-306.ibm.com/software/data/db2imstools/db2tools/db2archiveexpert.html ). I don't know what it is like, but it sounds interesting.

--- Barbara Jo Nigh wrote:

> In our Data Warehouse we have partitions containing > historical data that is > rarely used. Our DASD team has rejected my request > to use HSM to migrate > these files to tape until needed. Are there any > other automated options for > moving rarely used DB2 files to tape and back to > disk when needed? > > > [...] 8141 31 40_Re: DB2 Z/os connecting to MSSQL. ORACLE28_=?UTF-8?B?UGhpbCBndW5uaW5n?=22_pgunning@GUNNINGTS.COM30_Wed, 1 Nov 2006 03:46:21 +0000404_Windows-1252 You need DB2 II, oracle gateway, and MS Integration Services. PG poly Oracle gateway if coming from oracle. They may have changed the name of this recently. Sent via BlackBerry from Cingular Wireless

-----Original Message----- From: Rosina Porco Date: Tue, 31 Oct 2006 15:49:54 To:DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Z/os connecting to MSSQL. ORACLE [...] 8173 93 44_Re: DB2 z/OS V.8 Declared Temp Tables and BP12_Isaac Yassin20_yassini@BEZEQINT.NET30_Wed, 1 Nov 2006 06:22:28 +0200459_US-ASCII Hi

For 5K row I would use 16K pages, not 8K (this gives 1K waste compared to 3K). Compression ratio may change that. I'm referring to "final" length.

Isaac Yassin



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: Wednesday, November 01, 2006 2:46 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/OS V.8 Declared Temp Tables and BP [...]