RE: [U2] Strange SQL query optimisation ...
Thanks. But I don't understand ... In the second query I've asked for a LEFT JOIN both times. What's the difference between a LEFT JOIN and a LEFT OUTER JOIN? And why when I ask it twice for LEFT JOINs does it do a left join once, and a left outer join once? While I think I understand your comment about a left outer join must capture rows that may not be in the right table I don't see how any such rows could exist - the fact that we're matching @ID to @ID guarantees that, surely? Unfortunately, I don't think creating an index is an option Sorry for troubling you for more detail, but my SQL isn't superb, and I don't see why it should treat the two joins any differently - they're both joining tables on a key matches key basis. Cheers, Wol -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: 18 July 2008 02:00 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Strange SQL query optimisation ... 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 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/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] Copied an account, now indexes messed up..
I have a working running account... And needed to create a write-off account, so I simply used cp -Rp xxx to copy the one account to the other... Now I have index problems I tried to search U2Ug knowledgebase but all it ever returns is No records found... I know that indexes have path names embedded, and that my copy is messing up the original account's indexes... The thing is I need both accounts to have indexes, and I can't remember how I separate them... And its urgent... Oops! --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Copied an account, now indexes messed up..
Hi Dennis, I have a working running account... And needed to create a write-off account, so I simply used cp -Rp xxx to copy the one account to the other... Now I have index problems A UV file that has indices stores the pathname of the index directory in the header of the file. If you copy the file using an operating system tool, this pointer still points back to the indices of the original file. This can cause all sorts of fun with updates to the copied file altering the indices of the original file. Take a look at the SET.INDEX command. This allows you to display the index pathname, to change it, or to remove it completely, effectively deleting the indices. Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Copied an account, now indexes messed up..
SET.INDEX filename TO NULL On the copy. CREATE.INDEX filename INDEX etc on the copy will create copy. BUILD.INDEX filename ALL On copy. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Bartlett Sent: Friday, 18 July 2008 9:00 p.m. To: u2-users@listserver.u2ug.org Subject: [U2] Copied an account, now indexes messed up.. I have a working running account... And needed to create a write-off account, so I simply used cp -Rp xxx to copy the one account to the other... Now I have index problems I tried to search U2Ug knowledgebase but all it ever returns is No records found... I know that indexes have path names embedded, and that my copy is messing up the original account's indexes... The thing is I need both accounts to have indexes, and I can't remember how I separate them... And its urgent... Oops! --- 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] Copied an account, now indexes messed up..
Consider starting with UniAdmin... --Bill --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Copied an account, now indexes messed up..
Great point! I was thinking of doing that with my system. Is that pre 10.2 (we're at 10.1.8/hpux11)? Oh well, doesn't matter. I think we're moving to 10.2 on hp11.i in the next couple of weeks. BTW, someone shared a cool trick on this list at one time. COMO ON BLABLA SELECTF SET.INDEX INFORM COMO OFF This will show you all of the index paths for your current account. Then you can do a quick scan of the como for possible issues. One other thing... If you've got distributed files, you'll need to make sure you remember the REBUILD.DF. -- Original message -- From: Anthony Youngman [EMAIL PROTECTED] And if you've got to rebuild all the indices on the live server, you might want to look at recreating them. Not sure what the keyword is, but if you recreate them as relative indices, it will put the relative path in the file header and you won't have this problem again ... Cheers, Wol -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Bartlett Sent: 18 July 2008 14:28 To: u2-users@listserver.u2ug.org Subject: RE: [U2] Copied an account, now indexes messed up.. Thank you, thank you, thank you... Worked... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of phil walker Sent: 18 July 2008 11:03 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Copied an account, now indexes messed up.. SET.INDEX filename TO NULL On the copy. CREATE.INDEX filename INDEX etc on the copy will create copy. BUILD.INDEX filename ALL On copy. --- 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/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] UniData Dict Item Question
If that's an SB+ derived value (i.e. with the V(..)), try using I(..) instead for UV. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] Question about Uniobject and session encryption
Hi all. I am having trouble using session encryption in Uniobject and I hope someone can help me out on this issue. According to the UniObjects for .NET Developer#8217;s Guide, Uniboject allows encryption applied to its UniSession object. On page 3-34, I see two properties of UniSession explained: EncryptionEnabled and EncryptionType. If I create a simple VB.Net program such as the following Dim uvSession As UniSession Dim uvFile As UniFile uvSession = UniObjects.OpenSession(server, login, password, account, uvcs) uvFile = uvSession.CreateUniFile(some_file_on_server) I can successfully create a UniFile object to access a file on a remote server. However, if I added the following lines after the UniSession obejct is created: uvSession.EncryptionEnabled = True uvSession.EncryptionType = 1 I would get the following error message when creating a UniFile object. [IBM U2][UODOTNET - UNIRPC][ErrorCode=81004] An argument was requested from the RPC that was of an invalid type Does anyknow what I am doing wrong here? Any information would be greatly appreciated. Thank you. Jason --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
