1 IDUGDB2-L.ORG /home/listserv/home/db2-l August 2010, week 5
2 157 29_DB2mag Spring and Summer 201010_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 30 Aug 2010 04:30:06 -0400467_iso-8859-1 Hi DB2 user,
Here is a non-profit Blog about DB2 for z/OS (IBM mainframes):
http://db2usa.blogspot.com
Last update on Monday, August 30th 2010
Spring and Summer 2010 issues of DB2mag are available on line:
- DB2mag Summer 2010
- DB2mag Spring 2010
- DB2mag Archives (since Spring 2009)
- DB2mag Archives (Winter 1996-Spring 2009)
Note: DB2mag became IBMDatabasemag and then IBM Data Management magazine. [...]43_SNT118-W115810FBB8EB41F122D475AF890@phx.gbl
160 20 49_Re: Windows Job Scheduler Software Recommendation5_anand19_mahadea@LABCORP.COM31_Mon, 30 Aug 2010 08:59:21 -0400511_UTF-8 I use nnCron (the free lite version) to schedule my mySQL backup jobs.
_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]51_4993267972407027.WA.mahadealabcorp.com@www.idug.org
181 53 45_DBPROTOCOL(PRIVATE) OPTION IS NOT RECOMMENDED9_Dee Reins21_rcwingman@COMCAST.NET31_Mon, 30 Aug 2010 17:48:17 +0000463_utf-8 We are on DB2 version 9.1. All the packages have " DBPROTOCOL(PRIVATE)". The programming staff is in charge of how the binds are to be done. I think the bind parameters come from the vendor. I am the application DBA.
I understand that
Use DBPROTOCOL(DRDA) if you want DB2 to use DRDA access to access remote data that is specified with three-part names. You must bind a package at all locations whose names are specified in three-part names. [...]83_181374753.539337.1283190497230.JavaMail.root@sz0025a.emeryville.ca.mail.comcast.net
235 97 49_Re: DBPROTOCOL(PRIVATE) OPTION IS NOT RECOMMENDED13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Mon, 30 Aug 2010 14:27:21 -0400491_utf-8 I like easy questions
The intent is that DB2 10 will only support DRDA
Phil G
Sent from my iPod, so typing is even worse than usual
On 30 Aug 2010, at 19:20, Dee Reins > wrote:
We are on DB2 version 9.1. All the packages have "DBPROTOCOL(PRIVATE)". The programming staff is in charge of how the binds are to be done. I think the bind parameters come from the vendor. I am the application DBA. [...]49_68B45BFB-C01D-4E74-97AE-F5DD1BE65CB6@cogito.co.uk
333 36 16_REORG tablespace9_Subbu Rao24_subrahmanyarao@YAHOO.COM31_Mon, 30 Aug 2010 14:38:23 -0400476_UTF-8 Hi,
On Z/OS, DB2 V8.1, Do you see that REORGing the tablespace will influence the LEAFDIST value of the corresponding NPIs thereby making them a potential candidate for REORG? Here is the scenario:
On Sunday morning 2.00 AM we have a RUNSTATS scheduled to run. (Job takes 4 hours to complete).
On same day evening at 5:00 PM we have REORG of tablespaces job scheduled to run (OFFPOSLIMIT and INDREFLIMIT) at the partition level with inline STATISTICS. [...]56_9834740860093823.WA.subrahmanyaraoyahoo.com@www.idug.org
370 134 89_DB2 Connect Personal Edition backleveled (was: CALL statement and DB2 command processors)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 30 Aug 2010 14:52:17 -0400314_us-ascii David and Linda,
Thanks for your help. This was an issue with DB2 Connect Personal Edition 9.5FP0, which is fixed in more recent versions of the product. Now we just have to get the affected workstations upgraded -- the product is kind of "backleveled" across the enterprise, I am led to believe. [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF987@MAIL02.fisalan.nycnet
505 258 49_Re: DBPROTOCOL(PRIVATE) OPTION IS NOT RECOMMENDED14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 30 Aug 2010 14:53:32 -0400665_us-ascii Dee,
We have been told that DBPROTOCOL(PRIVATE) is not supported in future releases. I don't know whether this is to happen in V10 or Vnext, but it's definitely coming.
--Phil
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dee Reins
Sent: Monday, August 30, 2010 1:48 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] DBPROTOCOL(PRIVATE) OPTION IS NOT RECOMMENDED
We are on DB2 version 9.1. All the packages have "DBPROTOCOL(PRIVATE)". The programming staff is in charge of how the binds are to be done. I think the bind parameters come from the vendor. I am the application DBA. [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF988@MAIL02.fisalan.nycnet
764 95 49_Re: DBPROTOCOL(PRIVATE) OPTION IS NOT RECOMMENDED15_Cristian Molaro18_cristian@MOLARO.BE31_Mon, 30 Aug 2010 14:30:51 -0400572_- Hi Dee,
Private Protocol is deprecated in V9 and no longer supported in V10 (AFAIK, DB2 will not even load a package flagged as using PP). So yes, you need to migrate to DRDA before moving to the next version.
In any case, I would not wait till next migration in order to convert to DRDA: just consider that PP has not being updated since DB2 V5 (>10 years!) and many very useful features are available only using DRDA, like static SQL, thread pooling and stored procedures for mentioning some. And distributed workload using PP is not zIIP eligible. [...]50_2279076474409894.WA.cristianmolaro.be@www.idug.org
860 278 50_Compression Dictionary In Place? - DB2 V8 for z/OS12_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM31_Mon, 30 Aug 2010 14:13:21 -0500385_ISO-8859-1 Hello everyone,
We recently ran some dummy loads to clear out some data and we're not
sure that keepdictionary was specified so we've been snooping around,
but so far have not come up with a way (other than populating data back
in) to tell whether the dictionary is still in place. Runstats have
been run since the loads were done. Pagesave shows 0. [...]62_C90B7164E1511D499B3C479D8B7CEC9202363185@CMBFISLTC07.FNFIS.COM
1139 475 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 30 Aug 2010 15:31:13 -0400676_us-ascii DSN1PRNT should illustrate if you have a dictionary page after the
header and spacemap page ?
Steen Rasmussen
CA Technologies
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Reavill, Jay
Sent: Monday, August 30, 2010 2:13 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Compression Dictionary In Place? - DB2 V8 for z/OS
Hello everyone,
We recently ran some dummy loads to clear out some data and we're not
sure that keepdictionary was specified so we've been snooping around,
but so far have not come up with a way (other than populating data back
in) to tell whether the dictionary is still in place. [...]56_0A18F096E689AC43BB2F52DAE5674D60091C8A08@USILMS14.ca.com
1615 165 49_Re: DBPROTOCOL(PRIVATE) OPTION IS NOT RECOMMENDED13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Mon, 30 Aug 2010 14:33:51 -0500492_UTF-8 You cannot use private protocol in DB2 10...
If you specify DBPROTOCOL(PRIVATE) at BIND or REBIND, the BIND or REBIND
will fail.
A package bound with DBPROTOCOL(PRIVATE) will not execute in DB2 10...
IBM supplies tools to find, fix and avoid private protocol via APARs.
Write up on which APARs you should check out are detailed in my blog...
http://it.toolbox.com/blogs/db2zos/hopefully-this-is-the-last-time-we-hear-about-db2s-private-protocol-39780 [...]28_4C7C079F.20902@attglobal.net
1781 269 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Mon, 30 Aug 2010 14:36:19 -0500600_ISO-8859-1 Use a DSN1PRNT.... the dictionary should be pages 2-17 (header page is
page 0)... so you only need to print the first 20 pages...
Diagnostics Manual has more details
Willie
On 8/30/2010 2:13 PM, Reavill, Jay wrote:
>
> Hello everyone,
>
> We recently ran some dummy loads to clear out some data and we're not
> sure that keepdictionary was specified so we've been snooping around,
> but so far have not come up with a way (other than populating data
> back in) to tell whether the dictionary is still in place. Runstats
> have been run [...]30_4C7C0833.9000304@attglobal.net
2051 269 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Mon, 30 Aug 2010 14:38:56 -0500605_ISO-8859-1 Also...
If you specified KEEPDICTIONARY and LOAD did not find a dictionary, DB2
will issue an error informing you of the error..
Willie
On 8/30/2010 2:13 PM, Reavill, Jay wrote:
>
> Hello everyone,
>
> We recently ran some dummy loads to clear out some data and we're not
> sure that keepdictionary was specified so we've been snooping around,
> but so far have not come up with a way (other than populating data
> back in) to tell whether the dictionary is still in place. Runstats
> have been run since the loads were done. Pagesave shows [...]30_4C7C08D0.8000908@attglobal.net
2321 191 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS13_David Simpson22_dsimpson@THEMISINC.COM31_Mon, 30 Aug 2010 14:41:32 -0500605_utf-8 Do a DSN1PRNT on the underlying file. If you see dictionary pages then they are there.
Sent from my iPhone - Pardon the typing errors.
On Aug 30, 2010, at 2:37 PM, "Reavill, Jay" wrote:
> Hello everyone,
>
> We recently ran some dummy loads to clear out some data and we’re not sure that keepdictionary was specified so we’ve been snooping around, but so far have not come up with a way (other than populating data back in) to tell whether the dictionary is still in place. Runstats have been run since the loads were done. Pagesave [...]50_DA74AE3D-10FF-493F-B7B5-EE7298F9003C@themisinc.com
2513 49 56_Any negative experiences in DB2 9 with RELCURHL=NO gone?15_Tonmoy Dasgupta28_tonmoy.dasgupta@ARKANSAS.GOV31_Mon, 30 Aug 2010 15:46:55 -0400427_UTF-8 Currently on DB2 V8.1 NFM, z/OS v1.09 and researching DB2 9 migration. Have hundreds of programs with cursors declared " WITH HOLD" in production.
Am concerned at the Zparm RELCURHL=NO going away with IBM documentation stating “In Version 9.1, the option to hold a lock after commit is eliminated. DB2 always releases the page or row lock for cursor hold at commit, which allows for greater concurrency”. [...]60_8632077062246287.WA.tonmoy.dasguptaarkansas.gov@www.idug.org
2563 54 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS10_Joe Geller21_joerg6666@HOTMAIL.COM31_Mon, 30 Aug 2010 15:41:02 -0400573_UTF-8 An equally silly way to tell if the dictionary exists is to rerun the Load Replace with KeepDictionary.
You will get a warning message (DSNU233I) if the dictionary does not exist.
Joe
Hello everyone,
We recently ran some dummy loads to clear out some data and we’re not sure that keepdictionary was specified so we’ve been snooping around, but so far have not come up with a way (other than populating data back in) to tell whether the dictionary is still in place. Runstats have been run since the loads were done. Pagesave shows 0. [...]53_8084471930206245.WA.joerg6666hotmail.com@www.idug.org
2618 78 27_Datetime value is incorrect11_Ed Mullikin17_EMullikin@SCU.EDU31_Mon, 30 Aug 2010 14:32:47 -0700719_US-ASCII Has anyone gotten SQL0180N (The syntax of the string representation of a datetime value is incorrect) when trying to insert a null value to a date field that is nullable? DB2 9.1 fp8 on AIX 5.3.
Ed Mullikin
AIG Manager
Santa Clara University
emullikin@scu.edu
_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]38_4C7BC10F020000110002AB4C@GWIAC.scu.edu
2697 56 20_Re: REORG tablespace13_David Simpson22_dsimpson@THEMISINC.COM31_Mon, 30 Aug 2010 19:49:51 -0400529_UTF-8 Reorging at the partition level really does cause disorganization in NPIs in V8. It is very likely that they SHOULD be reorged as well following the part level reorgs. This behavior changes for online reorgs in DB2 9.
______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com
For more information about Themis, visit www.themisinc.com [...]67_7EC87535E7581C4C890F5BF1CF9A9374029FC84A@THEMISSRV1.themisinc.local
2754 22 65_Auto Reply: DB2-L Digest - 29 Aug 2010 to 31 Aug 2010 (#2010-236)0_27_scott.a.saunders@ORACLE.COM31_Mon, 30 Aug 2010 22:00:47 -0700627_utf-8 From August 30 - September 3, I will be visiting a customer or in transit. There will be a delay in responding to your email. For emergencies, contact my manager Pete Vammino at pete.vammino@oracle.com.
_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]44_390c68ab-618e-4ef7-8991-3b09c4122926@default
2777 299 41_Phils Viennese musings for IDUG EMEA 201013_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 31 Aug 2010 06:21:47 -0400769_us-ascii With IDUG EMEA 2010 being held in Vienna, I've been posting a weekly note to DB2-L (and elsewhere) on the various interesting things to do and the experiences not to be missed together with some suggestions on how to get the most benefit from your time at IDUG EMEA
But, as you may not have known whether you'd be attending IDUG EMEA at the time the original postings, I thought it would be useful to summarise them here for leisurely reading - hopefully, I'll be able to keep updating that page as a growing summary of my musings on Vienna [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902D194@MAILR004.mail.lan
3077 129 8_Re: MQTs10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 31 Aug 2010 07:31:41 -0400467_UTF-8 Hi Phil,
I give a sample example below :
CREATE TABLE SYSTAB1
(DBNAME
,TABLECOUNT
) AS
(SELECT DBNAME , COUNT ( * )
FROM SYSIBM.SYSTABLES
WHERE CREATEDTS > CURRENT TIMESTAMP - 100 DAYS
GROUP BY DBNAME )
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID UNICODE
IN DATABASE DSNDB04
AUDIT NONE;
If I code that CREATEDTS predicate there, I get an error : [...]54_1212133076033443.WA.teldb2kalstelstra.com@www.idug.org
3207 117 8_Re: MQTs13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 31 Aug 2010 07:43:35 -0400619_utf-8 Well, they do say you learn something new every day
The SQL Ref says
" When a materialized query table is defined with ENABLE QUERY OPTIMIZATION specified, the following additional restrictions apply:
- The fullselect must be a subselect.
- The subselect cannot contain – An ORDER BY clause – A FETCH FIRST clause – A nested table expression or view that requires temporary materialization – A join using the INNER JOIN syntax – An outer join – A special register – A scalar fullselect – A row change timestamp column – Any predicates that include subqueries. – A row [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902D19D@MAILR004.mail.lan
3325 109 43_Unload , reloading CLOB data in DB2 V8 z/os13_Mackey, Glenn20_GMackey@GUIDEONE.COM31_Tue, 31 Aug 2010 07:28:59 -0500594_us-ascii Hi,
I want to know any tricks to unload CLOB data via the standard unload
utility. Currently it seems to be very slow/inefficient. To unload
37,000 rows took 5hours with the machine at low utilization. I cancelled
another unload after about 20 hours - it was unloading 400k rows.
I need to unload, delete, redefine and reload a table which has two clob
columns.
I used the unload utility using the file reference option because the
CLOB data is over 32k.
That method creates a member in a pdse for each CLOB row and looking at
MSTR AS, I see [...]60_B9B0DB3A96C7104FA84DE2B54EDF73F709D47BF6@mail1.guidehome.com
3435 320 71_[AD] DB2 10 Is Coming- A First Look from Available Information (Part 2)13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK31_Tue, 31 Aug 2010 13:42:55 +0100397_US-ASCII DB2 10 is in beta, and thus subject to IBM non-disclosure agreements, but we
can continue to look at the publicly available information to give us a
really good idea of what IBM are planning for this new release.
Thursday September 2nd (at 12:00 PM EDT) is part 2 of a two part webinar and
will cover Security changes, Productivity enhancements and XML improvements. [...]43_003801cb490a$08dc3160$1a949420$@demon.co.uk
3756 269 47_Re: Unload , reloading CLOB data in DB2 V8 z/os10_Roger Hecq18_roger.hecq@UBS.COM31_Tue, 31 Aug 2010 09:43:07 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA616270A6D@NSTMC101PEX1.ubsw.net
4026 285 47_Re: Unload , reloading CLOB data in DB2 V8 z/os7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 31 Aug 2010 07:11:18 -0700481_utf-8 Specifying the HFS alternative to PDS helps somewhat with performance. It has limitations however on size.
The best way, that I have found, to unload and load LOB data is via user written programs.
The unload creates 2 output files, one for the table data and the other for the LOB data. The Lob data set is RECFM FB LRECL 4096. You basically chunk the lob.
The load step, really an insert, simply assembles the rows - dechunks if you will - and then inserts. [...]43_790318.64356.qm@web80204.mail.mud.yahoo.com
4312 641 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS12_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM31_Tue, 31 Aug 2010 09:45:55 -0500510_ISO-8859-1 As always, thanks to everyone that responded! The methods are
definitely a bit convoluted. You would think IBM would just make a
notation on systablepart as to whether a dictionary is in place or not,
but guess that would just be too easy.
Thanks again,
Jay
-------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716 [...]62_C90B7164E1511D499B3C479D8B7CEC92023632C5@CMBFISLTC07.FNFIS.COM
4954 617 54_Re: Compression Dictionary In Place? - DB2 V8 for z/OS13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Tue, 31 Aug 2010 10:07:26 -0500564_us-ascii One additional method that I didn't see mentioned -- if you just browse an imagecopy dataset you can generally tell if there's a compression dictionary (look for something along the lines of "\LZDICK" towards the top of the dataset). Not completely fool-proof, but it's an easy quick-check.
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Reavill, Jay
Sent: Tuesday, August 31, 2010 9:46 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Compression Dictionary In Place? - DB2 V8 for z/OS [...]87_18E3472326219848899E4980BFE573B303EAC52C30@PFGDSMMBX001.principalusa.corp.principal.com
5572 363 62_New for IDUG EMEA 2010 - IBM Hands-on Labs - And They're Free!13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 31 Aug 2010 11:12:05 -0400409_us-ascii Expand your technical proficiency by working alongside the experts at the IDUG EMEA 2010 Hands-on Labs. These instructor-led mini-courses are designed to get you up to speed quickly with hands-on interaction directly with IBM products.
Work through your questions with experts and learn tips, tricks and strategies to make your job easier and faster. Labs are typically three hours long. [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902D1D1@MAILR004.mail.lan
5936 55 11_z/OS db2 v937_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Tue, 31 Aug 2010 18:39:18 +0200715_iso-8859-2 Hi all
I have created a recursive select I and would like to insert it into a table. It seems to me that it isn't possible with Insert ... Select structure, or am I wrong?
Thanks in advance
dusan
_____________
Tato zprva a vechny pipojen soubory jsou dvrn a uren vlun adrestovi(-m). Jestlie nejste oprvnnm adrestem, je zakzno jakkoliv zveejovn, zprostedkovn nebo jin pouit tchto informac. Jestlie jste tento mail dostali neoprvnn, prosm, uvdomte odesilatele a smate zprvu i piloen soubory. Odesilatel nezodpovd za jakkoliv chyby nebo opomenut zpsoben tmto penosem.
Jste si jisti, e opravdu potebujete vytisknout tuto [...]58_0675446363E09A4EBE4CCF8D3AD7814F0454815B@MAIL1.cen.csin.cz
5992 153 47_Re: Unload , reloading CLOB data in DB2 V8 z/os12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 31 Aug 2010 09:48:47 -0700559_utf-8 You really don't want to do an unload/load utility on BLOBs. I have several PMRs open with IBM about this issue for well over 3 years now. They've got a new PTF UK59680 which helps V9 BLOB unloading a bit. But for all practical purposes, any table with a lot of BLOB/CLOB rows (and IBM defines this as more than a few thousand) is unloadable. But it really doesn't seem to be that important to IBM that people be able to unload BLOBs efficiently and quickly, in my own opinion. Theoretically V10 NFM mode provides some improvement. That remains to [...]44_574509.76061.qm@web112105.mail.gq1.yahoo.com
6146 85 15_Re: z/OS db2 v914_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 31 Aug 2010 12:55:01 -0400312_iso-8859-2 Dusan,
As far as I know, the only way to build recursive SQL in DB2V9 is through a Common Table Expression. I don't believe you can use CTE's with INSERT.
You should be able to create a cursor with your recursion, then FETCH the rows to host variables and individually INSERT the rows. [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF998@MAIL02.fisalan.nycnet
6232 51 31_Re: Datetime value is incorrect14_Larry Kintisch17_LKint@VERIZON.NET31_Tue, 31 Aug 2010 12:54:43 -0400733_us-ascii Hi Ed,
Try INSERT INTO DT,...VALUES((CAST NULL as DATE),...
or in a program use an "indicator variable" [half-word integer] set to -1:
Pseudo code: move -1 to DT_IND
INSERT INTO DT,... VALUES (:DT-HV:DT-IND,...)
Hope that helps.
Larry Kintisch, Pres. ABLE Information Services 845-353-0885
LKint at verizon.net
At 05:32 PM 8/30/2010, you wrote:
>Has anyone gotten SQL0180N (The syntax of the string representation
>of a datetime value is incorrect) when trying to insert a null value
>to a date field that is nullable? DB2 9.1 fp8 on AIX 5.3.
>
>Ed Mullikin
>AIG Manager
>Santa Clara University
>emullikin@scu.edu
>
>
>
[...]40_0L8100CV00BJ8A60@vms173019.mailsrvcs.net
6284 113 22_Re: [SPAM] z/OS db2 v916_Daniel Luksetich18_danl@DB2EXPERT.COM31_Tue, 31 Aug 2010 11:56:48 -0500657_iso-8859-2 I believe you are correct. You are going to have to unload (DSNTIAUL) from
your query and load into your target, or you can cross-load using the IBM
LOAD utility (not sure if the other vendors can do that or not).
Dan
Daniel L Luksetich
IBM Information Champion
IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS
IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database
Administration for UNIX, Windows, and OS/2
IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development
IBM Certified Advanced Technical Expert - DB2 [...]35_01ef01cb492d$820012f0$860038d0$@com
6398 110 15_Re: z/OS db2 v937_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Tue, 31 Aug 2010 19:26:19 +0200470_iso-8859-2 Thank you. The suggestion about QMF is very useful.
Regards,
dusan
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: Tuesday, August 31, 2010 6:55 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] z/OS db2 v9
Dusan,
As far as I know, the only way to build recursive SQL in DB2V9 is through a Common Table Expression. I don't believe you can use CTE's with INSERT. [...]58_0675446363E09A4EBE4CCF8D3AD7814F0454815D@MAIL1.cen.csin.cz
6509 184 15_Re: z/OS db2 v914_Peter Backlund21_BacklundDB2@TELIA.COM31_Tue, 31 Aug 2010 19:33:57 +02000_26_4C7D3D05.9020700@telia.com
6694 42 76_Combining Multiple MQTs to Improve SQL Optimization for DB2 Data Warehousing11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 31 Aug 2010 13:37:39 -04000_51_0141010396870102.WA.davedavebeulke.com@www.idug.org
6737 197 15_Re: z/OS db2 v914_Peter Backlund21_BacklundDB2@TELIA.COM31_Tue, 31 Aug 2010 20:06:44 +02000_26_4C7D44B4.7050404@telia.com
6935 487 15_Re: z/OS db2 v914_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 31 Aug 2010 14:19:57 -0400341_utf-8 Peter is correct. I was able to run his INSERT statement after constructing a compatible table for testing purposes, and the insert placed 100 rows in the table. (By the way, Peter, those are some pretty odd characters at the end of your alphabet :-) )
Dusan, can you provide an example of your code which isn’t working? [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF999@MAIL02.fisalan.nycnet
7423 356 47_Re: Unload , reloading CLOB data in DB2 V8 z/os13_Mackey, Glenn20_GMackey@GUIDEONE.COM31_Tue, 31 Aug 2010 13:26:08 -0500321_us-ascii Thanks to all who replied, at least I know I am in the same boat as
others and unloading lobs is a pain. For now I can get away with the
Insert- Select into/Rename solution and avoid writing programs.
I like Roger's suggestion to create a separate table for LOB columns. I
will follow suit. [...]60_B9B0DB3A96C7104FA84DE2B54EDF73F709D47C01@mail1.guidehome.com
7780 571 15_Re: z/OS db2 v914_Peter Backlund21_BacklundDB2@TELIA.COM31_Tue, 31 Aug 2010 21:02:22 +02000_26_4C7D51BE.1000508@telia.com
8352 124 22_Re: [SPAM] z/OS db2 v937_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Tue, 31 Aug 2010 21:23:42 +0200538_iso-8859-2 Thank you for suggestion; I will try cross-load
Regards,
dusan
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daniel Luksetich
Sent: Tuesday, August 31, 2010 6:57 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] [SPAM] z/OS db2 v9
I believe you are correct. You are going to have to unload (DSNTIAUL) from your query and load into your target, or you can cross-load using the IBM LOAD utility (not sure if the other vendors can do that or not).
Dan [...]58_0675446363E09A4EBE4CCF8D3AD7814F06433F8B@MAIL1.cen.csin.cz
8477 385 15_Re: z/OS db2 v936_=?utf-8?B?UG9zcMOtxaFpbCBEdcWhYW4=?=17_DPospisil@CSAS.CZ31_Tue, 31 Aug 2010 22:11:20 +0200512_utf-8 Thank a lot; I had to remove brackets. With your example easy task
Sorry for troubling so many people ...
INSERT INTO SPTOW --(
WITH P (NM, ID, STRYNG, LEV) AS
(SELECT 0,USERID,USERID,0 FROM DBZ0.CS07.SPTOQ WHERE AUTHORITY=0
UNION ALL
SELECT S.AUTHORITY, S.USERID, P.STRYNG || S.USERID, P.LEV+1
FROM P, DBZ0.CS07.SPTOQ S
WHERE NM+1=AUTHORITY AND P.LEV<10
)
SELECT *
FROM P
WHERE NM < 5 --) POM1 ;
(This is only simplified example, real sql works with syscolumn) [...]58_0675446363E09A4EBE4CCF8D3AD7814F06433F8E@MAIL1.cen.csin.cz
8863 73 80_Re: Combining Multiple MQTs to Improve SQL Optimization for DB2 Data Warehousing14_Larry Kintisch17_LKint@VERIZON.NET31_Tue, 31 Aug 2010 19:42:45 -0400588_us-ascii Hi Dave,
I remember the presentation you gave in Philadelphia about MQT's a
few years ago. I shared the program with you and I gave out a
handout that explained [from the course "Cost-Saving Database Index
Design"] how to calculate statistically when index splits caused
index leaf page scans to double in response time.
I don't recall if you gave a handout that day, or if you did,
whether or not it explained the SQL of the various views you were
proposing then and again today on your blog, and the various MQT's,
each built on each other. [...]40_0L81005RHJ9TXS20@vms173001.mailsrvcs.net
8937 242 15_Re: z/OS db2 v914_Larry Kintisch17_LKint@VERIZON.NET31_Tue, 31 Aug 2010 20:09:53 -0400351_iso-8859-1 Hi Dusan,
Please don't think of posting to the list as
"troubling so many people." We share ideas and
experiences. We learn something new each day. And
in this case, some of us missed the "vertical
change bar" on the left of an SQL manual page
about INSERT one of these past releases.
Keep posting! Larry Kintisch [...]40_0L8100LLUL3UZU70@vms173009.mailsrvcs.net