Re: [U2] Strange SQL query optimisation ...

2008-07-17 Thread Ray Wurlod 
They are not the same query.  The second one specifies LEFT OUTER JOIN.  The 
first does not.

A left outer join must capture rows that may not be in the right table.

An index on @ID in each of the tables may help.

> - Original Message -
> From: "Anthony Youngman" <[EMAIL PROTECTED]>
> To: "u2-users@listserver.u2ug.org" 
> Subject: [U2] Strange SQL query optimisation ...
> Date: Thu, 17 Jul 2008 14:34:32 +0100
> 
> 
> One of my colleagues brought a speed problem to me - he has two queries which
> produce the same result but one (the "proper" "modern" SQL) runs slowly, and
> the other (deprecated SQL) runs much faster. He was running them in a web
> engine, so I ran them at TCL with the EXPLAIN keyword and got an odd result.
> Here are the two queries:
> 
>  02 SELECT * FROM REGISTER, CLAIMS_CLAIMCOSTS,
> CLAIMS_COVERCODEFROMPOLICY WHERE R
> EGISTER.CLAIM.NUMBER=CLAIMS_CLAIMCOSTS.CLAIM.NUMBER AND
> REGISTER.CLAIM.NUMBER=CL
> AIMS_COVERCODEFROMPOLICY.CLAIM.NUMBER AND REGISTER.CLIENT.REF='W1323' AND
> CLAIMS
> _CLAIMCOSTS.CURRENCY.CLAIM.PAID.IN <>'' EXPLAIN
> 
>  01 SELECT * FROM REGISTER LEFT JOIN CLAIMS_CLAIMCOSTS ON
> REGISTER.CLAIM.NUMBER=C
> LAIMS_CLAIMCOSTS.CLAIM.NUMBER LEFT JOIN CLAIMS_COVERCODEFROMPOLICY ON
> REGISTER.C
> LAIM.NUMBER=CLAIMS_COVERCODEFROMPOLICY.CLAIM.NUMBER WHERE
> REGISTER.CLIENT.REF='W
> 1323' AND CLAIMS_CLAIMCOSTS.CURRENCY.CLAIM.PAID.IN <>'' EXPLAIN;
> 
> 
> Note that REGISTER and CLAIMS are the same UV file (don't ask why) and
> CLAIM.NUMBER is the @ID. When the first query is run, the optimiser says it's
> doing a hashed join on the primary key for both subtables. When the second
> query is run, it does an outer hashed join for the first subtable, but an
> "outer cartesian join using scan of secondary file" on the second. Why? It's
> the same query!
> 
> Any ideas how to make the modern syntax do the "right thing" and not run like
> a snail on tranquilisers?
> 
> Cheers,
> Wol
> ---
> u2-users mailing list
> u2-users@listserver.u2ug.org
> To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: [U2] Re: Going to U2 University 2008?

2008-07-17 Thread Ray Wurlod 
Unfortunately I'll be in the wrong part of the planet for all four sessions.  I 
note that the fourth session (in England) clashes directly with Information on 
Demand 2008 (which is where I will be that particular week).
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: [U2] UniData Dict Item Question

2008-07-17 Thread Colin Alfke 
Make sure there is an "I" in <1> and remove the V() from around the stmt -
which needs to be in <2>

Hth
Colin Alfke
Calgary, Canada

-Original Message-
From: Al DeWitt

I don't do these enough to get this right in the first 10 or so tries.
Can one of you more knowledgeable people tell me what's wrong with this
Derived Value?

V(IF BTC9000_MFGLOC1 = '' THEN 'CRB' ELSE IF BTC9000_MFGLOC1 = 'WAR' OR
BTC9000_MFGLOC1 = 'CFB' THEN BTC9000_MFGLOC2 ELSE BTC9000_MFGLOC1)

Thanks.
Albert DeWitt
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

[U2] UniData Dict Item Question

2008-07-17 Thread Al DeWitt 
I don't do these enough to get this right in the first 10 or so tries.
Can one of you more knowledgeable people tell me what's wrong with this
Derived Value?



V(IF BTC9000_MFGLOC1 = '' THEN 'CRB' ELSE IF BTC9000_MFGLOC1 = 'WAR' OR
BTC9000_MFGLOC1 = 'CFB' THEN BTC9000_MFGLOC2 ELSE BTC9000_MFGLOC1)



Thanks.



Albert DeWitt

Sr. Programmer Analyst

Stylmark, Inc.

763.574.8705 (V)

763-574-1052 (F)

[EMAIL PROTECTED] 
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

Re: [U2] UD - Unidata 7.2 Status

2008-07-17 Thread David Peters 
Hi Larry!   We expect it to go GA next month.



David Peters
U2 Servers Product Manager
IBM Information Management
Phone: 1-303-773-7783 
Internal: 656-7783
Email: [EMAIL PROTECTED]
http://www.ibm.com/software/data/u2
Mark your calendar!


 



Larry Chiang <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED]
07/17/2008 07:43 AM
Please respond to
u2-users@listserver.u2ug.org


To
u2-users@listserver.u2ug.org
cc

Subject
[U2] UD - Unidata 7.2 Status






Does anyone have the latest status of Unidata 7.2?
Last thing I heard was it's in Beta back in March.
Thanks.
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

[demime 1.01d removed an attachment of type image/jpeg]
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: [U2] UD - Unidata 7.2 Status

2008-07-17 Thread John Jenkins 
Imminent - due very soon indeed...
*almost* at breath-holding stage

Regards

JayJay

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Larry Chiang
Sent: 17 July 2008 14:44
To: u2-users@listserver.u2ug.org
Subject: [U2] UD - Unidata 7.2 Status

Does anyone have the latest status of Unidata 7.2?
Last thing I heard was it's in Beta back in March.
Thanks.
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: [U2] Re: Going to U2 University 2008?

2008-07-17 Thread Stevenson, Charles 
One of Janet Oswald's email ad broadcasts for U2U indicated that there
would be pre-conference "chargeable tutorials" the day before each of
the 4 editions of the conference.  The list of tutorials and their
charges are not yet on the web-site, but she tells me they should be
there tomorrow.  These tutorials have been worthwhile in years past.
I'm holding off on registering for San Diego until I can see those.

I think the regional conference idea is a good one.  I attended the San
Francisco one & 2 prior pan-IBM ones, as well as others off and on
dating back to Infocus/VMark days.

Pluses:
+ Cheaper.
+ Shorter.  2 nights lodging only.
+ More targeted. (That 2006 Anaheim "Information on Demand" event for
all IBM software users was ghastly.  Sure, we got to see Gladys Knight
and the Pips and got in free one night to part of Disneyland where the
music blared that you couldn't network (the primary purpose for the
social events), provided you could even find any fellow U2 users amid
the AIX, Tivoli, DB2, etc. users, almost half of whom seemed to be IBM
employees.)
+ If you attend the later venues, the presenters have honed their show a
bit.
+ The audience seemed a bit more technical last year.  I'm guessing the
lower price & shorter time span allowed companies to send technical
people other than the mucky-mucks who see these conferences like
junkets.
+ More content, less junket.

Minuses:
- You don't see some users from other parts of the world.  I miss that.
- Almost all sessions are presented by IBM staff, since not many
independent presenters want to commit to 4 such events.  I have always
appreciated the presentations by users & independent consultant groups,
myself, so I miss that.
- More content, less junket. (Some people will see that as a minus,
anyway.)

cds

P.S. Pardon me if some of this has already been discussed.  I've been
unsubscribed for a month long vacation.


-Original Message-
From: Louie Bergsagel
Ah, found the U2 University session abstracts:
http://www-306.ibm.com/software/info/u2/university/index3.jsp
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

[U2] Strange SQL query optimisation ...

2008-07-17 Thread Anthony Youngman 
One of my colleagues brought a speed problem to me - he has two queries which
produce the same result but one (the "proper" "modern" SQL) runs slowly, and
the other (deprecated SQL) runs much faster. He was running them in a web
engine, so I ran them at TCL with the EXPLAIN keyword and got an odd result.
Here are the two queries:

02 SELECT * FROM REGISTER, CLAIMS_CLAIMCOSTS,
CLAIMS_COVERCODEFROMPOLICY WHERE R
EGISTER.CLAIM.NUMBER=CLAIMS_CLAIMCOSTS.CLAIM.NUMBER AND
REGISTER.CLAIM.NUMBER=CL
AIMS_COVERCODEFROMPOLICY.CLAIM.NUMBER AND REGISTER.CLIENT.REF='W1323' AND
CLAIMS
_CLAIMCOSTS.CURRENCY.CLAIM.PAID.IN <>'' EXPLAIN

01 SELECT * FROM REGISTER LEFT JOIN CLAIMS_CLAIMCOSTS ON
REGISTER.CLAIM.NUMBER=C
LAIMS_CLAIMCOSTS.CLAIM.NUMBER LEFT JOIN CLAIMS_COVERCODEFROMPOLICY ON
REGISTER.C
LAIM.NUMBER=CLAIMS_COVERCODEFROMPOLICY.CLAIM.NUMBER WHERE
REGISTER.CLIENT.REF='W
1323' AND CLAIMS_CLAIMCOSTS.CURRENCY.CLAIM.PAID.IN <>'' EXPLAIN;


Note that REGISTER and CLAIMS are the same UV file (don't ask why) and
CLAIM.NUMBER is the @ID. When the first query is run, the optimiser says it's
doing a hashed join on the primary key for both subtables. When the second
query is run, it does an outer hashed join for the first subtable, but an
"outer cartesian join using scan of secondary file" on the second. Why? It's
the same query!

Any ideas how to make the modern syntax do the "right thing" and not run like
a snail on tranquilisers?

Cheers,
Wol
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

[U2] UD - Unidata 7.2 Status

2008-07-17 Thread Larry Chiang 
Does anyone have the latest status of Unidata 7.2?
Last thing I heard was it's in Beta back in March.
Thanks.
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

RE: [U2] Multiple columns from a LIST statement?

2008-07-17 Thread Edward Brown 
Keith,

Thank you - this is a very clever solution. Admittedly not as
straightforward as it could be - wouldn't it be nice if there was a
built in keyword such as ENABLE-COLS that did it automatically!

Edward

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Keith Johnson
(DSLWN)
Sent: 16 July 2008 22:25
To: u2-users@listserver.u2ug.org
Subject: RE: [U2] Multiple columns from a LIST statement?

Using LIST.LABEL (on a Universe system) EVAL can create a dummy column
mergeing
two columns and you can replicate column headings using the page heading
as below.

LIST.LABEL TRANSACTION EVAL
"FMT(@ID,'L#15'):FMT(OCONV(TRAN.AMOUNT,'MD2$,'),'R#15')" ID-SUPP
HEADING "ID  Amount  ID
Amount"

Answer the prompt like this,

COUNT, ROWS, SKIP, INDENT, SIZE, SPACE {,C} ?2,1,0,0,30,2

and here is the output

ID  Amount  ID  Amount

183046|11645|19  $2.49  28282|14248|147 $-0.34
99305|12650|116$201.58  16308|13753|169 $84.77
11495|14099|141$-23.14  2143|13275|16  $162.17
21782|14617|506$164.09  21782|14617|749$213.35
33661|12995|103 $23.21  92635|13095|635  $2.49


Note that TRAN.AMOUNT already had an output conversion, but I had to put
one inside the EVAL statement.

Regards, Keith
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/

---
Please remember to recycle wherever possible. 
Reduce, reuse, recycle, think do you need to print this e-mail?
---
This e-mail and any attachment(s), is confidential and may be legally 
privileged. It is intended solely for the addressee. If you are not the 
addressee, dissemination, copying or use of this e-mail or any of its content 
is prohibited and may be unlawful. If you are not the intended recipient please 
inform the sender immediately and destroy the e-mail, any attachment(s) and any 
copies. All liability for viruses is excluded to the fullest extent permitted 
by law. It is your responsibility to scan or otherwise check this email and any 
attachment(s). Unless otherwise stated (i) views expressed in this message are 
those of the individual sender (ii) no contract may be construed by this 
e-mail. Emails may be monitored and you are taken to consent to this 
monitoring.  

Civica Services Limited, Company No. 02374268; Civica UK Limited, Company No. 
01628868
Both companies are registered in England and Wales and each has its registered 
office at 2 Burston Road, Putney, London, SW15 6AR.
---
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/