| Author |
Message
|
| harristm |
 Posted: Thu Aug 02, 2012 6:32 am Post subject: building large SQL statements in esql |
 |
|
Apprentice
Joined: 02 Aug 2012
Posts: 29
|
I have a complex query that was 400 lines long and ran fine the next version is 533 lines and it does not return a result nor does it throw a db2 exception.
I have traced it out and the trace has issues as well but what I can see are the substitutions work down to a point, and after that the trace stops.
I am using the following form to build and run the query:
| Code: |
DECLARE QRY CHARACTER ;
SET QRY = ' SELECT stuff ....
WHERE DATE BETWEEN ''' || START_DATE || ''' AND ''' || END_DATE || ''' AND ....
COUNTRY_CODE IN (' || COUNTRY_CODES || ') ' ;
SET OutputRoot.MRM.MyRecords[] = PASSTHRU (QRY) ;
|
I have tested the query from a db2 command line with values for the variables in the SQL file and it works fine.
From that, I assert that the query is not too big for db2 itself.
The previous version of the query uses the same pattern and works fine, but it is 130 lines shorter.
I broke the substitution parts into four chunks, did the substitutions and concatenated it into a single statement
and it still fails the same way, no error, no data.
My first question is: is there a limit to the size of a CHARACTER variable.
My second question: how do I sort this one out?
This is on WMB 6.1.0.5 running on zlinux.
Last edited by harristm on Thu Aug 02, 2012 8:11 am; edited 1 time in total |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Aug 02, 2012 6:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
There is a limit to the length of a character variable, but it's *big*.
You should replace your literal substitution values with "?" and use the VALUES clause of the PASSTHRU clause to provide the substitution values, first.
This is bog standard database best practices, aside from whether you use Broker or not.
It will also very likely speed up the execution of the statement, as it can be prepared once rather than every time you call it.
You should strongly consider putting this select statement into a stored procedure and calling it rather than using passthru.
You can then consider running an ODBC trace - whether you've put the statement into a stored procedure or not - and determine if the time being spent is inside the database or while the broker processes the command and it's result.
Oh, and if you're feeling especially special, you can instead replace this call with a SOAPRequest node that calls an entirely separate webService that abstracts all of the database work and returns you an XML message that has your results. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Aug 02, 2012 6:56 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
6.1.0.5 is very out of date. Can you upgrade to 6.1.0.10 or later?
Look for a null value in one of your variables. There is no practical limit on CHARACTER datatype in ESQL as it relates to any real SQL statement length.
For example, in the debugger, do you see valid values for START_DATE and END_DATE?
Next time you post, please use [c o d e ] tags.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Aug 02, 2012 7:52 am Post subject: Re: building large SQL statements in esql |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
|
| Back to top |
|
|
| harristm |
| Posted: Thu Aug 02, 2012 8:10 am Post subject: |
|
|
Apprentice
Joined: 02 Aug 2012
Posts: 29
|
guys, thanks for your immediate response:
mqjeff:
In most of my queries I use "?" and the VALUES clause for PASSTHRU.
That broke when I needed to pass a list for country_codes so that the resulting db2 statement fragment looked like
| Code: |
COUNTRY_CODE IN ( 'US','FR','RU')
|
I pass the list into the node as a single variable but I was never able to get it into the SQL using the "?" and VALUES() clause. If you have any suggestions on how to make that happen, I would really appreciate it.
I'm open to the stored procedure idea particularly if I can avoid refactoring the query, is there a handy example somewhere, particularly that would let me supply a variable for the "IN" clause above.
Lancelotlinc:
I agree 6.1.0.5 is old, upgrade is on the todo list but has to coordinate a lot of other people besides just me.
I'll crawl through again looking for the nulls but the same variable gets substituted earlier in the statement just fine, but I understand your point.
Thanks for the nudge on the [c o d e ] tags.
mqsiuser:
Thanks I would like to use '?' and Values() |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 02, 2012 8:29 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| harristm wrote: |
In most of my queries I use "?" and the VALUES clause for PASSTHRU.
That broke when I needed to pass a list for country_codes so that the resulting db2 statement fragment looked like
| Code: |
COUNTRY_CODE IN ( 'US','FR','RU')
|
I pass the list into the node as a single variable but I was never able to get it into the SQL using the "?" and VALUES() clause. If you have any suggestions on how to make that happen, I would really appreciate it. |
I'd have to do some experiments. But I'd hope you could pass in a LIST variable in the VALUES clause, i.e. something like
| Code: |
| VALUES(START_DATE, {'US',"FR','RU'}, STOP_DATE) |
Then your SQL statement would be
| Code: |
| WHERE DATE BETWEEN ? AND ? AND COUNTRY_CODE IN (?) |
Where {} is an LIST constructor that would be equivalent to using a LIST variable directly.
| harristm wrote: |
| I'm open to the stored procedure idea particularly if I can avoid refactoring the query, is there a handy example somewhere, particularly that would let me supply a variable for the "IN" clause above. |
Another advantage of the stored procedure here is that you could pass in a flat STRING "US,FR,RU" to the stored procedure and then use some basic manipulation inside the stored procedure to turn it into a list that can be used in the IN clause.
Talk to your DBAs about this. Again, I'd have to do some experiments.
EDIT: And if none of this works, just because you can't use ? for one parameter, doesn't mean you shouldn't use it for the rest of them... |
|
| Back to top |
|
|
| harristm |
| Posted: Sat Aug 04, 2012 9:02 am Post subject: |
|
|
Apprentice
Joined: 02 Aug 2012
Posts: 29
|
Thank you all for your immediate response. I did find the problem, it was in how I was quoting one parameter, to get a single quote into the SQL, I have to double the quotes, to get an actual value into the SQL I need a single quote.
I will be working to figure out how to get this into a stored procedure for my next release, and upgrading the broker is on our list of things to do.
If there is a reference on upgrading from broker 6.1.0.5 to version 8 I would love to see it. Also any input on going from version 6 to 7 or just skip 7 and go straight to 8.
Thank you all very much |
|
| Back to top |
|
|
| Vitor |
| Posted: Sat Aug 04, 2012 2:14 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| harristm wrote: |
| If there is a reference on upgrading from broker 6.1.0.5 to version 8 I would love to see it. |
Weirdly enough, there's this in the WMBv8 InfoCenter. I don't know what IBM were thinking, putting information about WMBv8 in the WMBv8 documentation; who'd look there? 
| harristm wrote: |
Also any input on going from version 6 to 7 or just skip 7 and go straight to 8.
|
Oddly, it talks about that as well
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| harristm |
| Posted: Sat Aug 04, 2012 3:36 pm Post subject: |
|
|
Apprentice
Joined: 02 Aug 2012
Posts: 29
|
| I guess, just Read The Field Manual eh? |
|
| Back to top |
|
|
| Vitor |
| Posted: Sun Aug 05, 2012 5:33 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| harristm wrote: |
| I guess, just Read The Field Manual eh? |
You asked for a reference on migrating. That's the reference, containing all you need to know for migrating to v8 from both v6.1 & v7, and technical reasons for going via v7 (such as they are). Not sure what else you were expecting.....?
Talks less on the imponderables of going via v7, but that's a site based decision.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| harristm |
| Posted: Sun Aug 05, 2012 10:46 am Post subject: |
|
|
Apprentice
Joined: 02 Aug 2012
Posts: 29
|
Vitor:
I appreciate the pointer to the documentation. It was an appropriate answer to my question. No offense intended nor taken.
I was curious about there being a thread around what people have done in the migration before.
I really appreciate the responsiveness of this board.
Please PM if we need to chat. |
|
| Back to top |
|
|
| Vitor |
| Posted: Sun Aug 05, 2012 11:22 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| harristm wrote: |
| Please PM if we need to chat. |
a) We don't need to chat
b) PM is disabled on this forum
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
