Re: [U2] Slow READ/WRITE with indexes
I have found that deletes are the hardest on index updating, slower than inserts. If you can, I'd specifically disable the delete from the SO file to see what change that makes. Cheers Stuart -Original Message- Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes {Unclassified}
UD does indeed have NO.NULLS -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Friday, September 10, 2010 10:00 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Slow READ/WRITE with indexes {Unclassified} On 09/09/10 21:29, HENDERSON MIKE, MR wrote: > Ryan, > > You said you had indexes like "'ORDER.DATE', 'COUNTRY', 'MEMBER ID'". If > those are really the full index data items, you could have thousands, > tens of thousands, or even hundreds of thousands, of records with the > identical index data item value in the SOH file. If that's the case, > updates (inserts) may well be s-l-o-w as the system churns through many, > many, many linked buffers of identical valued keys looking for an insert > point. That was my thinking too. The other, related point. Does UD have NO.NULLS? And are you using it? If you haven't used it that's the most common performance-killer with indices out there ... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ### The information transmitted in this message and attachments (if any) is intended only for the person or entity to which it is addressed. The message may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. The intended recipient of this e-mail may only use, reproduce, disclose or distribute the information contained in this e-mail and any attached files with the permission of IMB. ### ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes {Unclassified}
On 09/09/10 21:29, HENDERSON MIKE, MR wrote: > Ryan, > > You said you had indexes like "'ORDER.DATE', 'COUNTRY', 'MEMBER ID'". If > those are really the full index data items, you could have thousands, > tens of thousands, or even hundreds of thousands, of records with the > identical index data item value in the SOH file. If that's the case, > updates (inserts) may well be s-l-o-w as the system churns through many, > many, many linked buffers of identical valued keys looking for an insert > point. That was my thinking too. The other, related point. Does UD have NO.NULLS? And are you using it? If you haven't used it that's the most common performance-killer with indices out there ... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
An interesting point about Windows 2008 R2 (it's only 64 bit) is the replacement backup utility cannot both save from a particular drive and store the backup to the same drive. So, if I were crazy enough to build a server with just a "C:" drive I would have to set the target for the backup file to a network share, as I can't store it on the "C:" drive (because I'm saving data from the "C:" drive. This pretty much indicates that one needs at least three drives for a Windows Server; one for the O/S, one for the backup file, and one to store all the data that needs saving. There didn't seem to be too much difference in backup speed using a network share as a target vs a local share, but one would think there would. Go figure... Bill Colin Alfke said the following on 9/9/2010 1:48 PM: Unfortunately, I haven't had a chance to play with it. It just looked like it treated C:\ like XP treats c:\windows\system and warns you away from it. I was wondering if it would prevent you from creating directories as well as files. I would have rather they fixed all of the tools to work with spaces - but then again - I still can't make myself create anything with a space in it. My current bear is Outlook keeping the .ost file in a hidden directory under Documents and Settings. My backup software seems to be smart enough to find the directory so that I can check it to be backed up, but not smart enough that I can find it when I want to restore!?!?!? Colin -Original Message- From: Bill Haskett Colin: I wonder if Windows 7 complains about any writes to the "C:\" drive. This would be a good thing, but still, some applications install into "C:\Program Files (x86)", so that directory must allow writes along with "C:\ProgramData" (notice how they seem to have removed their heads from that dark space by eliminating spaces in one of their primary directories in "C:"). I don't seem to have any problems writing to "D:" or beyond. Bill ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Unfortunately, I haven't had a chance to play with it. It just looked like it treated C:\ like XP treats c:\windows\system and warns you away from it. I was wondering if it would prevent you from creating directories as well as files. I would have rather they fixed all of the tools to work with spaces - but then again - I still can't make myself create anything with a space in it. My current bear is Outlook keeping the .ost file in a hidden directory under Documents and Settings. My backup software seems to be smart enough to find the directory so that I can check it to be backed up, but not smart enough that I can find it when I want to restore!?!?!? Colin -Original Message- From: Bill Haskett Colin: I wonder if Windows 7 complains about any writes to the "C:\" drive. This would be a good thing, but still, some applications install into "C:\Program Files (x86)", so that directory must allow writes along with "C:\ProgramData" (notice how they seem to have removed their heads from that dark space by eliminating spaces in one of their primary directories in "C:"). I don't seem to have any problems writing to "D:" or beyond. Bill ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Make sure you do some testing first. Under UniData I found that disabling the index made things much worse. It created a log file and put a copy of each record (appears to be the entire record - and not "optimized" for just the required index info) into it. The bad part was we were building a reporting cube so each record was written many times - which were all in the log file. This created a HUGE log file and was actually slower than leaving the index disabled. Plus it had to apply each of these records once we re-enabled/updated the index. It was actually much faster to remove the index, build the file, and then rebuild the index. Maybe it's better now - or maybe it just wasn't designed for what we were doing Hth Colin Alfke Calgary, Canada -Original Message- From: Ryan M I'm trying this now, thanks. Larry Hiscock wrote: > > Are any of your indices based on virtual fields? I haven't worked with UV > in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX > commands. If UV also has them, you could disable the indexes prior to the > archival and re-enable and update them at the end. I'm not sure if it > would > be any faster, but certainly worth an attempt. > > Larry Hiscock > Western Computer Services ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes {Unclassified}
Ryan, You said you had indexes like "'ORDER.DATE', 'COUNTRY', 'MEMBER ID'". If those are really the full index data items, you could have thousands, tens of thousands, or even hundreds of thousands, of records with the identical index data item value in the SOH file. If that's the case, updates (inserts) may well be s-l-o-w as the system churns through many, many, many linked buffers of identical valued keys looking for an insert point. I'd try to isolate if the problem is with deletes from the SO file or inserts to the SOH file, by disabling indexing first on one, then the other, then both, to see where the problem lies. If it's inserts to the SOH file that's the problem - which I suspect is the case - then you have two choices: make the disabling of indexes during this process a 'feature'; or restructure your SOH file and its indexes to avoid the clumping. The restructuring could involve re-defining your indexed fields to give fewer duplicated values (but that might mean considerable application program changes, depending how they're written) or you could consider turning SOH into a UV Distributed File set, maybe partitioning on values of ORDER.DATE. Good hunting Mike -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Friday, 10 September 2010 8:13 a.m. To: u2-users@listserver.u2ug.org Subject: Re: [U2] Slow READ/WRITE with indexes The indexes have been in place for some time now (1+ years) I'm guessing 10k to 15k new records per day is fairly high volume (this does not include changes to existing records). The information contained in this Internet Email message is intended for the addressee only and may contain privileged information, but not necessarily the official views or opinions of the New Zealand Defence Force. If you are not the intended recipient you must not use, disclose, copy or distribute this message or the information in it. If you have received this message in error, please Email or telephone the sender immediately. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
The indexes have been in place for some time now (1+ years) I'm guessing 10k to 15k new records per day is fairly high volume (this does not include changes to existing records). bradley.schrag wrote: > > How long have these indexes been in place? If they're new, that's one > thing. If they've been in place for a while and this is a change in > behavior we may need to look in different areas. FYI, on ud I've had > performance issues when going above five indexes on a given file when I > have a high volume of transactions. > > Brad. > U.S. BANCORP made the following annotations > - > Electronic Privacy Notice. This e-mail, and any attachments, contains > information that is, or may be, covered by electronic communications > privacy laws, and is also confidential and proprietary in nature. If you > are not the intended recipient, please be advised that you are legally > prohibited from retaining, using, copying, distributing, or otherwise > disclosing this information in any manner. Instead, please reply to the > sender that you have received this communication in error, and then > immediately delete it. Thank you in advance for your cooperation. > > > > - > > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29670926.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
I'm trying this now, thanks. Larry Hiscock wrote: > > Are any of your indices based on virtual fields? I haven't worked with UV > in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX > commands. If UV also has them, you could disable the indexes prior to the > archival and re-enable and update them at the end. I'm not sure if it > would > be any faster, but certainly worth an attempt. > > Larry Hiscock > Western Computer Services > > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M > Sent: Thursday, September 09, 2010 10:07 AM > To: u2-users@listserver.u2ug.org > Subject: [U2] Slow READ/WRITE with indexes > > > I am hoping I can find some help here. I am running into a serious > performance issue with indexes on our UV system (UV 10.2, on AIX). > > An example of this is our sales order files, SO (current/active) and SOH > (history) files. We archive sales orders from SO to SOH on a daily basis, > this is moving approx 15,000 records from one file to the other. If I > remove all indexes from both files, the process flies by, hundreds of > transactions per second. But, with indexes on, we are luck to get one per > second. > > Basically what happens with the code that does the archiving is it reads > the > SO record, does some quick checks to make sure we can move it, then writes > the record to SOH, then deletes the SO. > > There are 7 indexes on the SO file and 5 on the SOH. > > I've tried removing one index from a file and running the process, but see > no performance gain until all indexes are gone. > The SO file is approx 7GB with 280k records, and the SOH file is 11GB, > with > 8,900,000 records > > I have check the files sizes in UV and they are correct. > > Can anyone provide some pointers on ways to setup indexes so they will run > faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' > -- > View this message in context: > http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html > Sent from the U2 - Users mailing list archive at Nabble.com. > > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29670902.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Colin: I wonder if Windows 7 complains about any writes to the "C:\" drive. This would be a good thing, but still, some applications install into "C:\Program Files (x86)", so that directory must allow writes along with "C:\ProgramData" (notice how they seem to have removed their heads from that dark space by eliminating spaces in one of their primary directories in "C:"). I don't seem to have any problems writing to "D:" or beyond. Bill Colin Alfke said the following on 9/9/2010 12:02 PM: The pipe is different. I use it to send output as input to other commands: !LISTUSER | MORE or !LISTUSER | FIND "COLIN" /I But then I've only been really using DOS since 3.11 I've noticed Win 7 hides the root of C:, but I didn't realize that it wouldn't let you create a file. Colin -Original Message- From: Allen Elwood I've been using DOS since 1.0 - forgot all about pipe didn't it used to be | ? btw, even though I'm the admin on my laptop, it will not allow me to even use notepad to save to c:\buddy.txt Must be a new windows 7 thing. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
The pipe is different. I use it to send output as input to other commands: !LISTUSER | MORE or !LISTUSER | FIND "COLIN" /I But then I've only been really using DOS since 3.11 I've noticed Win 7 hides the root of C:, but I didn't realize that it wouldn't let you create a file. Colin -Original Message- From: Allen Elwood I've been using DOS since 1.0 - forgot all about pipe didn't it used to be | ? btw, even though I'm the admin on my laptop, it will not allow me to even use notepad to save to c:\buddy.txt Must be a new windows 7 thing. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
what sofware package are you running there at usbank ? --- On Thu, 9/9/10, bradley.sch...@usbank.com wrote: From: bradley.sch...@usbank.com Subject: Re: [U2] Slow READ/WRITE with indexes To: "U2 Users List" Date: Thursday, September 9, 2010, 2:39 PM How long have these indexes been in place? If they're new, that's one thing. If they've been in place for a while and this is a change in behavior we may need to look in different areas. FYI, on ud I've had performance issues when going above five indexes on a given file when I have a high volume of transactions. Brad. U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
How long have these indexes been in place? If they're new, that's one thing. If they've been in place for a while and this is a change in behavior we may need to look in different areas. FYI, on ud I've had performance issues when going above five indexes on a given file when I have a high volume of transactions. Brad. U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
I've been using DOS since 1.0 - forgot all about pipe didn't it used to be | ? btw, even though I'm the admin on my laptop, it will not allow me to even use notepad to save to c:\buddy.txt Must be a new windows 7 thing. On 9/9/2010 10:50 AM, Colin Alfke wrote: If you had the proper permissions you would have had a buddy.txt file with: ECHO is on. in the file. The '>' redirects the output to the file indicated (creates it if it doesn't exist or overwrites it if it does). I use it to create a log from a batch file: Date /t> batchfile.log Time /t>> batchfile.log The '>>' will append the output to the file. So, in the example he would likely want to do the WEOFSEQ SEQFILE to reset the record (although that will create the file as well so you might as well just do that...) Now that we're getting picky - the ABORTM statement doesn't exist in UniData either so you would need to use ABORT or STOP. Hth Colin Alfke Calgary Canada -Original Message- From: Allen Elwood RR How does this create a file? I've always code similar to the method David Green showed, since that works on all pick flavors instead of having to change my code every time I move to a different system. C:\Users\AllenElwood>help echo Displays messages, or turns command-echoing on or off. ECHO [ON | OFF] ECHO [message] Type ECHO without parameters to display the current echo setting. C:\>echo>c:\buddy.txt Access is denied. On 9/9/2010 9:47 AM, Rex Gozar wrote: OPENSEQ PATH TO SEQFILE THEN WEOFSEQ SEQFILE ON ERROR ABORTM "WEOFSEQ FAILED!" END END ELSE * instead of CREATE statement (on Windows) PCPERFORM "echo>":PATH OPENSEQ PATH TO SEQFILE ELSE ABORTM "OPENSEQ FAILED!" END END ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
If you had the proper permissions you would have had a buddy.txt file with: ECHO is on. in the file. The '>' redirects the output to the file indicated (creates it if it doesn't exist or overwrites it if it does). I use it to create a log from a batch file: Date /t > batchfile.log Time /t >> batchfile.log The '>>' will append the output to the file. So, in the example he would likely want to do the WEOFSEQ SEQFILE to reset the record (although that will create the file as well so you might as well just do that...) Now that we're getting picky - the ABORTM statement doesn't exist in UniData either so you would need to use ABORT or STOP. Hth Colin Alfke Calgary Canada -Original Message- From: Allen Elwood RR How does this create a file? I've always code similar to the method David Green showed, since that works on all pick flavors instead of having to change my code every time I move to a different system. C:\Users\AllenElwood>help echo Displays messages, or turns command-echoing on or off. ECHO [ON | OFF] ECHO [message] Type ECHO without parameters to display the current echo setting. C:\>echo>c:\buddy.txt Access is denied. On 9/9/2010 9:47 AM, Rex Gozar wrote: > OPENSEQ PATH TO SEQFILE THEN >WEOFSEQ SEQFILE ON ERROR > ABORTM "WEOFSEQ FAILED!" >END > END ELSE >* instead of CREATE statement (on Windows) >PCPERFORM "echo>":PATH >OPENSEQ PATH TO SEQFILE ELSE > ABORTM "OPENSEQ FAILED!" >END > END ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow READ/WRITE with indexes
Are any of your indices based on virtual fields? I haven't worked with UV in a while, but UD has the DISABLE.INDEX, ENABLE.INDEX and UPDATE.INDEX commands. If UV also has them, you could disable the indexes prior to the archival and re-enable and update them at the end. I'm not sure if it would be any faster, but certainly worth an attempt. Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ryan M Sent: Thursday, September 09, 2010 10:07 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow READ/WRITE with indexes I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Use echo . to get just a carriage return into the file. Note that you don't have permissions to write the file, therefore any attempt to create it from BASIC will also fail. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Allen Elwood RR Sent: Thursday, September 09, 2010 10:08 AM To: U2 Users List Subject: Re: [U2] Sequential Files Question How does this create a file? I've always code similar to the method David Green showed, since that works on all pick flavors instead of having to change my code every time I move to a different system. C:\Users\AllenElwood>help echo Displays messages, or turns command-echoing on or off. ECHO [ON | OFF] ECHO [message] Type ECHO without parameters to display the current echo setting. C:\>echo>c:\buddy.txt Access is denied. On 9/9/2010 9:47 AM, Rex Gozar wrote: > OPENSEQ PATH TO SEQFILE THEN >WEOFSEQ SEQFILE ON ERROR > ABORTM "WEOFSEQ FAILED!" >END > END ELSE >* instead of CREATE statement (on Windows) >PCPERFORM "echo>":PATH >OPENSEQ PATH TO SEQFILE ELSE > ABORTM "OPENSEQ FAILED!" >END > END > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
How does this create a file? I've always code similar to the method David Green showed, since that works on all pick flavors instead of having to change my code every time I move to a different system. C:\Users\AllenElwood>help echo Displays messages, or turns command-echoing on or off. ECHO [ON | OFF] ECHO [message] Type ECHO without parameters to display the current echo setting. C:\>echo>c:\buddy.txt Access is denied. On 9/9/2010 9:47 AM, Rex Gozar wrote: OPENSEQ PATH TO SEQFILE THEN WEOFSEQ SEQFILE ON ERROR ABORTM "WEOFSEQ FAILED!" END END ELSE * instead of CREATE statement (on Windows) PCPERFORM "echo>":PATH OPENSEQ PATH TO SEQFILE ELSE ABORTM "OPENSEQ FAILED!" END END ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Slow READ/WRITE with indexes
I am hoping I can find some help here. I am running into a serious performance issue with indexes on our UV system (UV 10.2, on AIX). An example of this is our sales order files, SO (current/active) and SOH (history) files. We archive sales orders from SO to SOH on a daily basis, this is moving approx 15,000 records from one file to the other. If I remove all indexes from both files, the process flies by, hundreds of transactions per second. But, with indexes on, we are luck to get one per second. Basically what happens with the code that does the archiving is it reads the SO record, does some quick checks to make sure we can move it, then writes the record to SOH, then deletes the SO. There are 7 indexes on the SO file and 5 on the SOH. I've tried removing one index from a file and running the process, but see no performance gain until all indexes are gone. The SO file is approx 7GB with 280k records, and the SOH file is 11GB, with 8,900,000 records I have check the files sizes in UV and they are correct. Can anyone provide some pointers on ways to setup indexes so they will run faster? Some of the indexes are 'ORDER.DATE', 'COUNTRY', 'MEMBER ID' -- View this message in context: http://old.nabble.com/Slow-READ-WRITE-with-indexes-tp29653705p29653705.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
OPENSEQ PATH TO SEQFILE THEN WEOFSEQ SEQFILE ON ERROR ABORTM "WEOFSEQ FAILED!" END END ELSE * instead of CREATE statement (on Windows) PCPERFORM "echo>":PATH OPENSEQ PATH TO SEQFILE ELSE ABORTM "OPENSEQ FAILED!" END END ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Al; If you don't want to create the extra VOC entry you can put the entire path in the OPENSEQ statement: OPENSEQ 'C:\OUTPUT\APCHECKS999' TO OUTFILE THEN Or FPATH = 'C:\OUTPUT\' FNAME = 'APCHECKS' OPENSEQ FPATH:FNAME TO OUTFILE THEN I just find the VOC method easier as I have a lot of programs and the path can be quite different Hth Colin Alfke Calgary, Canada -Original Message- From: Al DeWitt This is what finally worked. I was hoping to do it w/o having to create a VOC entry, but that was not to be. Thank you all for your help. Al DeWitt -Original Message- From: Colin Alfke Al; I use the following code: OPENSEQ 'OUTPUT.FILE', 'APCHECKS' TO OUTFILE THEN CRT 'APCHECKS ALREADY EXISTS' STOP END * code to build each line goes here MORE.DATA = 1 LOOP WHILE MORE.DATA DO LINE = 'data' 'L#9':MORE.DATA 'L#25':'still more data' 'L#20' WRITESEQ LINE APPEND ON OUTFILE ELSE CRT 'NOT AT END OF APCHECKS' MORE.DATA += 1 IF MORE.DATA >= 25 THEN MORE.DATA = 0 REPEAT CLOSESEQ OUTFILE Hth Colin Alfke Calgary, Canada -Original Message- From: Al DeWitt I GET THE FOLLOWING MESSAGE [FATAL ERROR: - Cannot open file C:\OUTPUT\APCHECKS1002] and I'm sent to the colon prompt. Al DeWitt -Original Message- From: Al DeWitt I forgot to mention that I'm on Windows. My output path is: C:\OUTPUT\ My file name isAPCHECKS where is a counter that gets incremented each time the program is run. FNAME would be C:\OUTPUT\APCHECKS so that my statement would read: OPENSEQ FNAME TO OUTFILE... However, since APCHECKS would be unique each time it doesn't exist. BTW when I try to display OUTFILE it gives me an Unitialized Variable, zero assumed error. Al DeWitt ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
because CREATE doesn't exist in Unidata, at least not at my release. On 9/9/2010 9:05 AM, Rex Gozar wrote: Al, Look at the CREATE statement in your documentation. Works for both Windows and *nix. Frankly, I'm amazed how complicated people are making this. OPENSEQ PATH TO SEQFILE THEN WEOFSEQ SEQFILE ON ERROR ABORTM "WEOFSEQ FAILED!" END END ELSE CREATE SEQFILE ELSE ABORTM "OPENSEQ AND CREATE FAILED!" END END ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Al, Look at the CREATE statement in your documentation. Works for both Windows and *nix. Frankly, I'm amazed how complicated people are making this. OPENSEQ PATH TO SEQFILE THEN WEOFSEQ SEQFILE ON ERROR ABORTM "WEOFSEQ FAILED!" END END ELSE CREATE SEQFILE ELSE ABORTM "OPENSEQ AND CREATE FAILED!" END END ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
This is what finally worked. I was hoping to do it w/o having to create a VOC entry, but that was not to be. Thank you all for your help. Al DeWitt -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Colin Alfke Sent: Wednesday, September 08, 2010 11:57 PM To: 'U2 Users List' Subject: Re: [U2] Sequential Files Question Al; I use the following code: OPENSEQ 'OUTPUT.FILE', 'APCHECKS' TO OUTFILE THEN CRT 'APCHECKS ALREADY EXISTS' STOP END * code to build each line goes here MORE.DATA = 1 LOOP WHILE MORE.DATA DO LINE = 'data' 'L#9':MORE.DATA 'L#25':'still more data' 'L#20' WRITESEQ LINE APPEND ON OUTFILE ELSE CRT 'NOT AT END OF APCHECKS' MORE.DATA += 1 IF MORE.DATA >= 25 THEN MORE.DATA = 0 REPEAT CLOSESEQ OUTFILE Where OUTPUT.FILE is a VOC item (which means I can change the location of the file without recompiling/changing code): DIR C:\OUTPUT D_VOC Using the "THEN" construct means the program will stop if that item already exists so you won't overwrite any existing data. Technically, the file won't actually exist until you do the WRITESEQ, but it won't matter that it won't already exist, it will create it. You can't really deal with OUTFILE as a variable - it's really a (type of) file handle. The WRITESEQ will fly through the file - you don't have to worry about creating a large array - it will just keep writing until it hits the limit (not sure if it's the OS limit or UniData's 2GB limit). Hth Colin Alfke Calgary, Canada -Original Message- From: Al DeWitt I GET THE FOLLOWING MESSAGE [FATAL ERROR: - Cannot open file C:\OUTPUT\APCHECKS1002] and I'm sent to the colon prompt. Al DeWitt -Original Message- From: Dan McGrath If you run this code: OPENSEQ FNAME TO OUTFILE ON ERROR STOP 'FATAL ERROR: - Cannot open file ':FNAME END THEN STOP 'FATAL ERROR: - Already exiting file called: ':FNAME END ELSE CRT "FILE OPEN SUCCEEDED!" WRITESEQ "Dummy data" ON OUTFILE THEN NULL END What happens? -Original Message- From: Al DeWitt I forgot to mention that I'm on Windows. My output path is: C:\OUTPUT\ My file name isAPCHECKS where is a counter that gets incremented each time the program is run. FNAME would be C:\OUTPUT\APCHECKS so that my statement would read: OPENSEQ FNAME TO OUTFILE... However, since APCHECKS would be unique each time it doesn't exist. BTW when I try to display OUTFILE it gives me an Unitialized Variable, zero assumed error. Al DeWitt ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Here is one approach. OPEN.DIR.FILE:* Open sequential output file DIR.FILE = "_PH_" OPEN DIR.FILE TO F.DIR.FILE THEN DELETE F.DIR.FILE, OUTPUT.NAME CLOSE F.DIR.FILE OPENSEQ DIR.FILE, OUTPUT.NAME TO F.DIR.FILE ELSE IF STATUS() THEN ABORT.FLAG = TRUE MSG = "Can't open ":DIR.FILE:", ":OUTPUT.NAME:" for output." END END END ELSE ABORT.FLAG = TRUE MSG = "Can't open ":DIR.FILE:"!" END RETURN David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Al DeWitt Sent: Wednesday, September 08, 2010 3:55 PM To: u2-users@listserver.u2ug.org Subject: [U2] Sequential Files Question Unidata 7.1.20 I've never dealt with sequential files, but I have a need to output records to a text file. Each record will be a series of fixed-length fields. Each file will need to be created at the time of opening. The destination will be a Windows folder on one of our servers. I will be using OPENSEQ to open the file. However, since each file needs to be created at time of opening what do I use to create the file? My reading of OPENSEQ doesn't indicate that it will create it automatically if it doesn't exist. Thanks. Al DeWitt ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Sequential Files Question
Al, Do the following: OPENSEQ FNAME TO OUTFILE ELSE NULL WEOFSEQ OUTFILE ON ERROR STOPM 'FATAL ERROR: - Cannot write to file ':FNAME Now, you should be able to perform the writes without a problem. By the way, the ON ERROR clause is optional. The WEOFSEQ will either create the file, or write an end of file mark at the end of an existing file, making the file null. If you want to keep the data, if the file exists, then add the WEOFSEQ in an OPENSEQ ELSE condition... but you need to position the pointer to the end of the file or you will be stepping on existing data. Tom RATEX Business Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Al DeWitt Sent: Wednesday, September 08, 2010 10:15 PM To: U2 Users List Subject: Re: [U2] Sequential Files Question I GET THE FOLLOWING MESSAGE [FATAL ERROR: - Cannot open file C:\OUTPUT\APCHECKS1002] and I'm sent to the colon prompt. Al DeWitt -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dan McGrath Sent: Wednesday, September 08, 2010 7:20 PM To: U2 Users List Subject: Re: [U2] Sequential Files Question If you run this code: OPENSEQ FNAME TO OUTFILE ON ERROR STOP 'FATAL ERROR: - Cannot open file ':FNAME END THEN STOP 'FATAL ERROR: - Already exiting file called: ':FNAME END ELSE CRT "FILE OPEN SUCCEEDED!" WRITESEQ "Dummy data" ON OUTFILE THEN NULL END What happens? -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Al DeWitt Sent: Thursday, September 09, 2010 10:03 AM To: U2 Users List Subject: Re: [U2] Sequential Files Question I forgot to mention that I'm on Windows. My output path is: C:\OUTPUT\ My file name isAPCHECKS where is a counter that gets incremented each time the program is run. FNAME would be C:\OUTPUT\APCHECKS so that my statement would read: OPENSEQ FNAME TO OUTFILE... However, since APCHECKS would be unique each time it doesn't exist. BTW when I try to display OUTFILE it gives me an Unitialized Variable, zero assumed error. Al DeWitt -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dan McGrath Sent: Wednesday, September 08, 2010 6:29 PM To: U2 Users List Subject: Re: [U2] Sequential Files Question Yes, it will. Note: It will drop into the 'ELSE' clause if the file didn't exist to start with. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Al DeWitt Sent: Thursday, September 09, 2010 8:55 AM To: u2-users@listserver.u2ug.org Subject: [U2] Sequential Files Question Unidata 7.1.20 I've never dealt with sequential files, but I have a need to output records to a text file. Each record will be a series of fixed-length fields. Each file will need to be created at the time of opening. The destination will be a Windows folder on one of our servers. I will be using OPENSEQ to open the file. However, since each file needs to be created at time of opening what do I use to create the file? My reading of OPENSEQ doesn't indicate that it will create it automatically if it doesn't exist. Thanks. Al DeWitt ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ### The information transmitted in this message and attachments (if any) is intended only for the person or entity to which it is addressed. The message may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. The intended recipient of this e-mail may only use, reproduce, disclose or distribute the information contained in this e-mail and any attached files with the permission of IMB. ### ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _
Re: [U2] Sequential Files Question
On 09/09/10 05:57, Colin Alfke wrote: > Al; > > I use the following code: > > OPENSEQ 'OUTPUT.FILE', 'APCHECKS' TO OUTFILE THEN > CRT 'APCHECKS ALREADY EXISTS' > STOP > END NOTE THE DIFFERENT SYNTAX! Looks like OPENSEQ has multiple syntaxes as everybody else has said OPENSEQ "os pathname" TO ... But I've only ever used the OPENSEQ "uv type 1 file", "record" TO syntax. What happens if you change syntax and try it that way? Cheers, Wol > * code to build each line goes here > MORE.DATA = 1 > LOOP WHILE MORE.DATA DO > LINE = 'data' 'L#9':MORE.DATA 'L#25':'still more data' 'L#20' > WRITESEQ LINE APPEND ON OUTFILE ELSE CRT 'NOT AT END OF APCHECKS' > MORE.DATA += 1 > IF MORE.DATA >= 25 THEN MORE.DATA = 0 > REPEAT > CLOSESEQ OUTFILE > > Where OUTPUT.FILE is a VOC item (which means I can change the location of > the file without recompiling/changing code): > DIR > C:\OUTPUT > D_VOC > > Using the "THEN" construct means the program will stop if that item already > exists so you won't overwrite any existing data. Technically, the file won't > actually exist until you do the WRITESEQ, but it won't matter that it won't > already exist, it will create it. You can't really deal with OUTFILE as a > variable - it's really a (type of) file handle. The WRITESEQ will fly > through the file - you don't have to worry about creating a large array - it > will just keep writing until it hits the limit (not sure if it's the OS > limit or UniData's 2GB limit). > > Hth > Colin Alfke > Calgary, Canada > > -Original Message- > From: Al DeWitt > > I GET THE FOLLOWING MESSAGE [FATAL ERROR: - Cannot open file > C:\OUTPUT\APCHECKS1002] and I'm sent to the colon prompt. > > Al DeWitt > > -Original Message- > From: Dan McGrath > > If you run this code: > > OPENSEQ FNAME TO OUTFILE ON ERROR >STOP 'FATAL ERROR: - Cannot open file ':FNAME END THEN >STOP 'FATAL ERROR: - Already exiting file called: ':FNAME END ELSE >CRT "FILE OPEN SUCCEEDED!" >WRITESEQ "Dummy data" ON OUTFILE THEN NULL END > > What happens? > > > -Original Message- > From: Al DeWitt > > I forgot to mention that I'm on Windows. > > My output path is: C:\OUTPUT\ > My file name isAPCHECKS where is a counter that gets incremented > each time the program is run. > > FNAME would be C:\OUTPUT\APCHECKS so that my statement would read: > OPENSEQ FNAME TO OUTFILE... > > However, since APCHECKS would be unique each time it doesn't exist. > > > BTW when I try to display OUTFILE it gives me an Unitialized Variable, > zero assumed error. > > Al DeWitt > > > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users