1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2001, week 5 2 24 53_prepareStatements with viriable no of bind parameters11_arvind_heda40_arvind_heda@INTERSOLUTIONS.STPN.SOFT.NET31_Sat, 29 Sep 2001 15:34:33 +0530347_iso-8859-1 hi members,

I am trying to use prepareStatements to prepare a set of queries to be fired on the database, it requires the exact no of arguments to be set using '?' which can be instatiated at runtime. but waht to do if queries are using IN clause which can have different no of arguments thru different calls too the query. [...] 27 50 38_DB2-L Gathering at the Tech Conference14_Philip Gunning24_philip.gunning@QUEST.COM31_Sat, 29 Sep 2001 10:45:09 -0700145_- Monday, 10/1/01, 10:00 PM, Location: Champions, in the hotel. See all of you there. Regards, Phil





Assoc List Owner DB2-L 78 21 11_Re: db2 log12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 15:13:14 -0500338_- This is a technique that we call putting a hole in the log. It was not too uncommon during Y2K testing, and some customers got the opportunity to reinstall a new DB2. If you are very lucky, then you have a number of checks because the data is back level. Then you get to understand conditional restart and recovery at a new level. [...] 100 39 27_Re: XML Extender for DB2 V712_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 15:18:56 -0500348_- I'm only a developer. For me, the terms and conditions and prices are mostly something I get from the web, and they are complex. So here is an attempt from a non-expert.

XML extenders are part of V7. The new separate charge pieces are Net Search Extender and Warehouse Manager.

Here are some pastes from the web announcements. [...] 140 20 21_Re: -904 with NUMLKUS12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 15:24:34 -0500280_- So the questions seem to be whether the maximum is too low or the commit frequency and other parameters for the program are keeping too many locks. We saw a program with millions of locks the other day. The customer turned off the limits and did not like to see DB2 crash. [...] 161 15 57_Re: prepareStatements with viriable no of bind parameters12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 15:30:25 -0500332_- You will need to use USING DESCRIPTOR and set up the SQLDA structure to handle the variables. Like the variable list SELECT statement discussed in the Application Programming and SQL Guide chapter on dynamic SQL, you can use the same construct for OPEN USING DESCRIPTOR for variable inputs.

Roger Miller, DB2 for z/OS [...] 177 22 23_Re: Capturirng SMF data12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 15:53:59 -0500488_- You will need to explain a bit more of what you want before someone can help. The DB2 instrumentation includes a few hundred types of information. If you just want accounting for a user, that's pretty easy. If you want to see a lot more, then the questions are in auditing or in performance traces. DB2 sends the data to SMF, GTF or to a program. Then those programs often format it, provide reports, and can have function to put the data into LOAD format, so you can work in SQL. [...] 200 16 48_Re: Falling back to V4.1 after migrating to V5.112_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 16:21:12 -0500357_- One of the quick checks for problems is looking for release dependency marks to see if anyone made changes so that something is dependent upon the new version. Check the catalog tables for IBMREQD values of H or higher. I'd check SYSPACKAGE, SYSDATABASE, SYSPLAN, SYSTABLES, SYSTABLESPACE, SYSINDEXES, and SYSVIEWS.

Roger Miller, DB2 for z/OS [...] 217 21 15_Re: DB2 and USS12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 16:38:50 -0500293_- With USS, the environment is pretty much the same as batch, except that you can only use Call Attach or RRSAF. So there is not much to say, except where a REXX exec or batch is needed for functions like BIND. I saw a couple of notes in the Administration Guide for authorization setup. [...] 239 23 26_Re: DB2, ESS, XRC and PPRC12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 29 Sep 2001 16:49:42 -0500622_- We've been using the combinations for quite a while. I think many of the best worked examples are using Geographically Dispersed Parallel Sysplex. There is a services offereing, and Data Sharing is not necessary.

http://www.ibm.com/servers/eserver/zseries/pso/ http://www.ibm.com/servers/eserver/zseries/library/whitepapers/gf225114.html http://www.as.ibm.com/asww/offerings/mww62b1.htm http://www.ibm.com/services/learning/global/itprod/msseduc.html http://www.ibm.com/storage

XRC and PPRC are fairly unique, so using search from the ibm.com pages tends to work fairly well for many more references. [...] 263 54 57_Re: prepareStatements with viriable no of bind parameters11_Suresh Sane21_data_arch@HOTMAIL.COM31_Sun, 30 Sep 2001 01:01:57 -0500441_- Arvind,

The technically correct way to handle this is to use SQLDA. An alternative you may want to consider is using a fixed number for the IN list (whatever the likely max) and then set the "unused" values to something that will not match (e.g. zeros, blanks, high-values etc). This is not a good performance alternative but will lead to simpler (somwhat sloppy) code. I said consider ... I am NOT recommending this approach. [...] 318 52 19_Re: Extent Question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 30 Sep 2001 04:02:38 -0500322_- Todd,

The number of extents in a VSAM cluster (of which a DB2 tablespace is one kind) cannot be deterministally (ie a way which always works) determined from its space allocation values (which for a tablespace come from PRIQTY and SECQTY) and total space usage. Some of the factors which get in the way are: [...] 371 50 27_Re: CERTIFICATION QUESTIONS14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 30 Sep 2001 04:49:13 -0500551_- Well, the answer to the first question can be found by reading the syntax diagram for UNLOAD. The second question is garbled.

I would also ask "where did you get these questions from?" If they came from a sample/preparatory test, I doubt that you will find them in the actual test. The purpose of these tests (sample/preparatory) is to give you an indication of the bredth and depth of knowledge expected to be able to handle the real test. Hence you should not need to look up the manual - you should know, or be able to figure out for [...] 422 37 57_Re: prepareStatements with viriable no of bind parameters14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 30 Sep 2001 04:57:58 -0500628_- Avind,

I presume that the IN list is not a "IN (SELECT ...)".

Would it be possible to pass constants in the IN list, rather than host variables?

James Campbell



On Sat, 29 Sep 2001 15:34:33 +0530, arvind_heda wrote:

>hi members, > >I am trying to use prepareStatements to prepare a set of queries to be fired >on the database, it requires the exact no of arguments to be set using '?' >which can be instatiated at runtime. but waht to do if queries are using IN >clause which can have different no of arguments thru different calls too [...] 460 34 21_Re: -904 with NUMLKUS12_sushant dash23_dash_dba@REDIFFMAIL.COM31_Sun, 30 Sep 2001 10:46:47 -0000473_iso-8859-1 Hello John, please try out what other say but if you have time just see that any of your indexes are not in rebuild pending status.Or there may be chance for the complex query involved with so many unions and nestings.Once I had a same kind of experince. I was executing a query and there sould be index acess. It thrwn me out with same reason code but luckily here was a experienced person who found out the probelm. Is it a same probelm. JUSt give a try. [...] 495 35 21_Re: -904 with NUMLKUS10_Alan Smith25_alancsmith@BTINTERNET.COM31_Sun, 30 Sep 2001 17:42:03 +0100569_us-ascii Check your SPUFI defaults to make sure you're not using RR isolation.

Alan Smith

> ------------------------------ > > Date: Fri, 28 Sep 2001 00:16:02 -0000 > From: john king > Subject: Re: -904 with NUMLKUS > > Hi Kirk/Sanjev, it is dynamic sql query. It is from application but it > goes= > to db2 on os 390 from message queue. This query is not implemented or > call= > ed from the apllication it is simply executed from the spufi. This is > the q= > uery has to be called from application side. But I m testing [...] 531 81 81_Re: Optimizations for BLOBs (how to increase BLOB inserts with C CLI application)21_Christopher I Schmidt18_cschmid@US.IBM.COM31_Sun, 30 Sep 2001 20:40:50 -0500421_us-ascii Hello Bela,

Thanks for the response. The database in question is behind a firewall on an AIX machine (I do not have physical access to it), so I haven't been able to do performance monitoring through the Control Center. I did the tests on a less powerful machine, but I didn't see the 'direct write... ' monitor elements. I noticed Total Direct Access time. Is this the element you are referring to? [...] 613 71 42_Re: Looking for quick help on create index8_K.Balaji19_K.Balaji@TARGET.COM31_Sun, 30 Sep 2001 21:52:25 -0500459_iso-8859-1 Hi, Is there any way to know what is the plan that is holding this dynamic SQL ? Thanks Balaji

-----Original Message----- From: Isaac Yassin [mailto:yassini@BEZEQINT.NET] Sent: Wednesday, September 19, 2001 5:52 AM To: DB2-L@RYCI.COM Subject: Re: Looking for quick help on create index



Hi, According to Msgs&Codes (V7) - you have a dynamic SQL in the cache that references this object. Get rid of it and it should be ok. [...] 685 67 19_Re: Extent Question12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Mon, 1 Oct 2001 13:16:45 +1000183_- Todd, if you are after a quick and dirty way of getting extents then "3.4" under ISPF will show you! eg: ----------------------------------------------------------------------------