[U2] select query..
Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] select query..
Hi Do you have wildcard characters like [] into KEYVALUE ? If not, why do you SELECT ; it's the key of file.name ; you can read directly ! Elsewhere, For performance, set a index on the @id Why do you want to use save-list/get-list ? You can work on LIST1 directly ! manu -Message d'origine- De : u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] De la part de Sathya Envoyé : mercredi 15 mai 2013 11:45 À : u2-users@listserver.u2ug.org Objet : [U2] select query.. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ 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] select query..
Manu Fernandes efv at infodata.lu writes: Hi Do you have wildcard characters like [] into KEYVALUE ? If not, why do you SELECT ; it's the key of file.name ; you can read directly ! Elsewhere, For performance, set a index on the at id Why do you want to use save-list/get-list ? You can work on LIST1 directly ! manu Hi Fernandes, Thanks for your comments. But as Im new to this technology Im not sure how to proceed with your suggestions. Can u please be more specific on this approach? There is no [] symbols in the KEYVALUE. How do I check whether @ID is indexed? I will modify it with using LIST1 directly. Thanks Regards, Sathya V. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH at ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ ://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users at 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] select query..
Hi, You are better off executing everything in one EXECUTE statement, it is cleaner. EXECLINE='SELECT FILE.NAME WITH @ID = ':KEYVALUE:'' EXECLINE-1='SAVE-LIST SOME.LIST' EXECLINE-1='GET-LIST SOME.LIST' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Tom Whitmore RATEX Business Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Sathya Sent: Wednesday, May 15, 2013 7:19 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] select query.. Manu Fernandes efv at infodata.lu writes: Hi Do you have wildcard characters like [] into KEYVALUE ? If not, why do you SELECT ; it's the key of file.name ; you can read directly ! Elsewhere, For performance, set a index on the at id Why do you want to use save-list/get-list ? You can work on LIST1 directly ! manu Hi Fernandes, Thanks for your comments. But as Im new to this technology Im not sure how to proceed with your suggestions. Can u please be more specific on this approach? There is no [] symbols in the KEYVALUE. How do I check whether @ID is indexed? I will modify it with using LIST1 directly. Thanks Regards, Sathya V. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH at ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ ://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users at 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] select query..
What do you actually try to achieve? On 15/05/2013 12:18, Sathya wrote: Manu Fernandes efv at infodata.lu writes: Hi Do you have wildcard characters like [] into KEYVALUE ? If not, why do you SELECT ; it's the key of file.name ; you can read directly ! Elsewhere, For performance, set a index on the at id Why do you want to use save-list/get-list ? You can work on LIST1 directly ! manu Hi Fernandes, Thanks for your comments. But as Im new to this technology Im not sure how to proceed with your suggestions. Can u please be more specific on this approach? There is no [] symbols in the KEYVALUE. How do I check whether @ID is indexed? I will modify it with using LIST1 directly. Thanks Regards, Sathya V. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH at ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ ://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users at 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] select query..
Mecki Foerthmann meckif at gmx.net writes: ___ U2-Users mailing list U2-Users at listserver.u2ug.org Actually Im fetching the KEYVALUE data from a flat file and passing the values one by one to the select command and creating the list. Then I will write the data into another log file. 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] select query..
So you don't need to select at all. Just use READ record FROM FILE.NAME,KEYVALUE ELSE record = '' in your loop. On 15/05/2013 12:52, Sathya wrote: Mecki Foerthmann meckif at gmx.net writes: ___ U2-Users mailing list U2-Users at listserver.u2ug.org Actually Im fetching the KEYVALUE data from a flat file and passing the values one by one to the select command and creating the list. Then I will write the data into another log file. 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] select query..
You should not need to do the SAVE-LIST and GET-LIST unless you wish to do a 2nd pass on the exact same results. The main purpose of a SAVEDLIST is to prevent you from having to do the same select multiple times (or to preserve the list of keys for some other purpose). Note that a SAVEDLIST is just a list of KEYs and no other data. I do not understand what is in KEYVALUE. I am assuming you are running in native UniData/UniVerse mode. If you are always wanting the same records, just build a permanent SAVEDLIST, get it and never bother with a SELECT again. A little more info about what you are ultimately trying to accomplish will help us give you a more specific solution. How many values are in KEYVALUE? -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Sathya Sent: Wednesday, May 15, 2013 5:45 AM To: u2-users@listserver.u2ug.org Subject: [U2] select query.. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ 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] select query..
Unlike SQL (Snail Query Language), in Multi Value you do not need to SELECT to return the row of the key itself. MultiValue provides the READ command READ theRow FROM theFile,KeyValue THEN do something And you do not need an EXECUTE wrapping a READ as you would for a SELECT At TCL type HELP BASIC READ -Original Message- From: Israel, John R. johnisr...@daytonsuperior.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, May 15, 2013 5:12 am Subject: Re: [U2] select query.. You should not need to do the SAVE-LIST and GET-LIST unless you wish to do a 2nd pass on the exact same results. The main purpose of a SAVEDLIST is to prevent you from having to do the same select multiple times (or to preserve the list of keys for some other purpose). Note that a SAVEDLIST is just a list of KEYs and no other data. I do not understand what is in KEYVALUE. I am assuming you are running in native UniData/UniVerse mode. If you are always wanting the same records, just build a permanent SAVEDLIST, get it and never bother with a SELECT again. A little more info about what you are ultimately trying to accomplish will help us give you a more specific solution. How many values are in KEYVALUE? -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Sathya Sent: Wednesday, May 15, 2013 5:45 AM To: u2-users@listserver.u2ug.org Subject: [U2] select query.. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ 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] select query..
I'm confused.. If your doing a select with @ID = you will only get 1 response each time, @ID is a primary key field. So as suggested just open the file, then do a read from That file with the given @ID. An alternative to a SAVE.LIST and GET.LIST could be to open SAVEDLISTS as file Then READ and WRITE your dynamic list to that file, and just use a FOR/NEXT loop to Traverse it. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Sathya Sent: Wednesday, May 15, 2013 5:45 AM To: u2-users@listserver.u2ug.org Subject: [U2] select query.. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ 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] select query..
George: In UniData, all SAVEDLISTS items have 000 (or 001, etc) appended to the end of it. Although one can normally do a SAVE.LIST MY.LIST and expect that the actual item ID in the SAVEDLISTS file to be MY.LIST000 this isn't always the case if the list is large. This makes managing a list directly in UD problematic. :-( HTH, Bill - Original Message - *From:* ggal...@wyanokegroup.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 5/15/2013 6:43 AM *Subject:* Re: [U2] select query.. I'm confused.. If your doing a select with @ID = you will only get 1 response each time, @ID is a primary key field. So as suggested just open the file, then do a read from That file with the given @ID. An alternative to a SAVE.LIST and GET.LIST could be to open SAVEDLISTS as file Then READ and WRITE your dynamic list to that file, and just use a FOR/NEXT loop to Traverse it. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Sathya Sent: Wednesday, May 15, 2013 5:45 AM To: u2-users@listserver.u2ug.org Subject: [U2] select query.. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Convert data from UniData to Microsoft SQl server
Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert data from UniData to Microsoft SQl server
I think you multiply by a thousand and round up -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kathleené M Hunter Sent: Wednesday, May 15, 2013 12:26 PM To: u2-users@listserver.u2ug.org Subject: [U2] Convert data from UniData to Microsoft SQl server Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ 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] select query..
That's what READLIST and WRITELIST are for. I've found writing the list as MY.LIST000 to be relatively safe, when you read you would have to check for MY.LIST000, then MY.LIST001, etc. Sathya - as Mecki said, if you're using the @ID of the file you can do a straight read to check if it's there. However, the select shouldn't be taking that long. Hth Colin -Original Message- From: Bill Haskett Sent: Wednesday, May 15, 2013 9:41 AM To: U2 Users List Subject: Re: [U2] select query.. George: In UniData, all SAVEDLISTS items have 000 (or 001, etc) appended to the end of it. Although one can normally do a SAVE.LIST MY.LIST and expect that the actual item ID in the SAVEDLISTS file to be MY.LIST000 this isn't always the case if the list is large. This makes managing a list directly in UD problematic. :-( HTH, Bill - Original Message - *From:* ggal...@wyanokegroup.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 5/15/2013 6:43 AM *Subject:* Re: [U2] select query.. I'm confused.. If your doing a select with @ID = you will only get 1 response each time, @ID is a primary key field. So as suggested just open the file, then do a read from That file with the given @ID. An alternative to a SAVE.LIST and GET.LIST could be to open SAVEDLISTS as file Then READ and WRITE your dynamic list to that file, and just use a FOR/NEXT loop to Traverse it. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Sathya Sent: Wednesday, May 15, 2013 5:45 AM To: u2-users@listserver.u2ug.org Subject: [U2] select query.. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert data from UniData to Microsoft SQl server
Multiple what by a thousand and round up ? -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dan Ell Sent: Wednesday, May 15, 2013 9:32 AM To: U2 Users List Subject: Re: [U2] Convert data from UniData to Microsoft SQl server I think you multiply by a thousand and round up -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kathleené M Hunter Sent: Wednesday, May 15, 2013 12:26 PM To: u2-users@listserver.u2ug.org Subject: [U2] Convert data from UniData to Microsoft SQl server Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ 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] Convert data from UniData to Microsoft SQl server
On 15/05/13 18:43, Kathleené M Hunter wrote: Multiple what by a thousand and round up ? Measure the disk space used by your Unidata account, and convert Kb to Mb. And Dan Ell is only *half* joking. Really. I did an account dump and transfer to SQL-Server, and SQL-Server really did take on the order of one order of magnitude more disk space! Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dan Ell Sent: Wednesday, May 15, 2013 9:32 AM To: U2 Users List Subject: Re: [U2] Convert data from UniData to Microsoft SQl server I think you multiply by a thousand and round up -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kathleené M Hunter Sent: Wednesday, May 15, 2013 12:26 PM To: u2-users@listserver.u2ug.org Subject: [U2] Convert data from UniData to Microsoft SQl server Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] select query..
On 15/05/13 18:43, Colin Alfke wrote: That's what READLIST and WRITELIST are for. I've found writing the list as MY.LIST000 to be relatively safe, when you read you would have to check for MY.LIST000, then MY.LIST001, etc. Sathya - as Mecki said, if you're using the @ID of the file you can do a straight read to check if it's there. However, the select shouldn't be taking that long. The other little trick you can use (I hope, it's fine in UV) is EXECUTE SELECT FILE.NAME :KEYVALUE However, I agree with the other posters that you probably shouldn't be using SELECT at all. But you can pass a (list of) keys directly to SELECT without having to specify equals or anything like that. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert data from UniData to Microsoft SQl server
The amount of disk space the Unidata files are using -Original Message- From: Kathleené M Hunter kmhun...@resolutionprovider.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Wed, May 15, 2013 10:44 am Subject: Re: [U2] Convert data from UniData to Microsoft SQl server Multiple what by a thousand and round up ? -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dan Ell Sent: Wednesday, May 15, 2013 9:32 AM To: U2 Users List Subject: Re: [U2] Convert data from UniData to Microsoft SQl server I think you multiply by a thousand and round up -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kathleené M Hunter Sent: Wednesday, May 15, 2013 12:26 PM To: u2-users@listserver.u2ug.org Subject: [U2] Convert data from UniData to Microsoft SQl server Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert data from UniData to Microsoft SQl server
There are a lot of factors that will determine this. The main thing is the data types used on the SQL side. See the following link for size of each data type http://msdn.microsoft.com/en-us/library/ms172424(v=sql.100).aspx As a rule of thumb using the fixed length data types will take up more space than the variable length data types. Another piece that will contribute to your size is the fact you will need to create cross-reference tables, where you can do multivalues in UniData. As a rough estimate we have seen a 2:1 ratio in the amount of disk space used. HTH -Dan Dan Goble | Senior Systems Engineer Interline Brands, Inc. 804 East Gate Drive Suite 100, Mount Laurel, NJ 08054 Office: 856.533.3110 | Mobile: 609.792.6855 E-mail: dan.go...@interlinebrands.com | Website: www.interlinebrands.com This e-mail and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. Please notify the sender immediately by e-mail if you have received this e-mail in error and delete all copies of this message. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kathleené M Hunter Sent: Wednesday, May 15, 2013 12:26 PM To: u2-users@listserver.u2ug.org Subject: [U2] Convert data from UniData to Microsoft SQl server Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ 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] select query..
Hi Sathya, Clearly, in basic, if you know the unique-key of a record, you don't need to select it to access its data. Do it like this : OPEN FILE.NAME TO F.FILE.NAME ELSE ... error ... KEYVALUE = READ V.RECORD FROM F.FILE.NAME, KEYVALUE THEN * OK RECORD EXIST ; V.RECORD IS LOADED WITH DATA END ELSE * KO RECORD DOESN'T EXIST ! V.RECORD IS NOT MODIFIED V.RECORD = '' END Do what you need with V.RECORD -- Now if the value you have is not the unique-key ; you need to execute a select. Do like this : OPEN FILE.NAME TO F.FILE.NAME ELSE ... error ... DATAVALUE =. .. EXECUTE 'SELECT FILE.NAME WITH FIELD.NAME = ':DATAVALUE:'' RTNLIST LIST1 LOOP WHILE READNEXT KEYVALUE DO READ V.RECORD FROM F.FILE.NAME,KEYVALUE THEN * OK RECORD EXIST END ELSE * KO RECORD DOEN'T EXIST CONTINUE ;* GOTO NEXT READNEXT END Do what you need with V.RECORD REPEAT By extension, You can create index à secondary-key declaration like this : At TCL,do like this (with users logged out) CREATE.INDEX FILE.NAME FIELD.NAME NO.NULLS BUILD.INDEX FILE.NAME FIELD.NAME If exist, Universe SELECT use automatically the index system - I hope this help. Manu -Message d'origine- De : u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] De la part de Sathya Envoyé : mercredi 15 mai 2013 13:19 À : u2-users@listserver.u2ug.org Objet : Re: [U2] select query.. Manu Fernandes efv at infodata.lu writes: Hi Do you have wildcard characters like [] into KEYVALUE ? If not, why do you SELECT ; it's the key of file.name ; you can read directly ! Elsewhere, For performance, set a index on the at id Why do you want to use save-list/get-list ? You can work on LIST1 directly ! manu Hi Fernandes, Thanks for your comments. But as Im new to this technology Im not sure how to proceed with your suggestions. Can u please be more specific on this approach? There is no [] symbols in the KEYVALUE. How do I check whether @ID is indexed? I will modify it with using LIST1 directly. Thanks Regards, Sathya V. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH at ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ ://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users at 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] select query..
Erratum, LOOP WHILE READNEXT KEYVALUE FROM LIST1 DO -Message d'origine- De : u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] De la part de Manu Fernandes Envoyé : mercredi 15 mai 2013 21:10 À : U2 Users List Objet : Re: [U2] select query.. Hi Sathya, Clearly, in basic, if you know the unique-key of a record, you don't need to select it to access its data. Do it like this : OPEN FILE.NAME TO F.FILE.NAME ELSE ... error ... KEYVALUE = READ V.RECORD FROM F.FILE.NAME, KEYVALUE THEN * OK RECORD EXIST ; V.RECORD IS LOADED WITH DATA END ELSE * KO RECORD DOESN'T EXIST ! V.RECORD IS NOT MODIFIED V.RECORD = '' END Do what you need with V.RECORD -- Now if the value you have is not the unique-key ; you need to execute a select. Do like this : OPEN FILE.NAME TO F.FILE.NAME ELSE ... error ... DATAVALUE =. .. EXECUTE 'SELECT FILE.NAME WITH FIELD.NAME = ':DATAVALUE:'' RTNLIST LIST1 LOOP WHILE READNEXT KEYVALUE DO READ V.RECORD FROM F.FILE.NAME,KEYVALUE THEN * OK RECORD EXIST END ELSE * KO RECORD DOEN'T EXIST CONTINUE ;* GOTO NEXT READNEXT END Do what you need with V.RECORD REPEAT By extension, You can create index à secondary-key declaration like this : At TCL,do like this (with users logged out) CREATE.INDEX FILE.NAME FIELD.NAME NO.NULLS BUILD.INDEX FILE.NAME FIELD.NAME If exist, Universe SELECT use automatically the index system - I hope this help. Manu -Message d'origine- De : u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] De la part de Sathya Envoyé : mercredi 15 mai 2013 13:19 À : u2-users@listserver.u2ug.org Objet : Re: [U2] select query.. Manu Fernandes efv at infodata.lu writes: Hi Do you have wildcard characters like [] into KEYVALUE ? If not, why do you SELECT ; it's the key of file.name ; you can read directly ! Elsewhere, For performance, set a index on the at id Why do you want to use save-list/get-list ? You can work on LIST1 directly ! manu Hi Fernandes, Thanks for your comments. But as Im new to this technology Im not sure how to proceed with your suggestions. Can u please be more specific on this approach? There is no [] symbols in the KEYVALUE. How do I check whether @ID is indexed? I will modify it with using LIST1 directly. Thanks Regards, Sathya V. Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH at ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ ://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users at 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Convert data from UniData to Microsoft SQl server
The last one I saw was a 20MB UniData file took up 200MB in SQL Server. Having said that, it is *highly* dependent on the nature of your data, how many multivalued/sub-multivalued attributes there are (and how deep), as well as what data types you select for your SQL schema. This means there is generic way to calculate how much your data will expand when stored in SQL Server - other than actually trying. For what purpose are you moving data into SQL Server? Cheers, Dan -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kathleené M Hunter Sent: Wednesday, May 15, 2013 10:26 AM To: u2-users@listserver.u2ug.org Subject: [U2] Convert data from UniData to Microsoft SQl server Does anyone have a way to calculate the disk space required to move data from a UniData Server to a Microsoft SQL server. ___ 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] select query..
To speed it up, try resizing the file. On 15 May 2013 21:44, Sathya sathya8...@gmail.com wrote: Hi Experts.. I have one issue here with fetching data from the file. EXECLINE = SELECT FILE.NAME WITH @ID = \:KEYVALUE:\ DISPLAY EXECLINE EXECUTE EXECLINE RTNLIST LIST1 EXECUTE 'SAVE-LIST SOME.LIST' PASSLIST LIST1 LIST.NAME = GET-LIST SOME.LIST sellist = '' EXECUTE LIST.NAME RTNLIST sellist CAPTURING junk Here Im running a loop for a set of KEYVALUEs and for each run the SSELECT statement is taking around 30 seconds. When I want to run 1000 loops, ideally the run time will be much. Is there any easier way to do this with less time consumption. Thanks Regards, Sathya V. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Kate Stanton Walstan Systems Ltd 4 Kelmarna Ave, Herne Bay, Auckland 1011, New Zealand Phone: + 64 9 360 5310 Mobile: + 64 21 400 486 Email: k...@walstan.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Relative Path for SET.INDEX in Universe
Rick, Don't know if you got your question about relative index answered, but this was from a post I made back in 2007: IBM tech support just made me aware of an apparently undocumented feature that was added to SET.INDEX in release 10.1 allowing a relative path for the index file. SET.INDEX filename TO RELATIVE.PATH This command will set the index pathname to ./I_filename eliminating the need to reset the index pathname when the files are copied, as long as the data file and index file are copied as a unit. Similarly: CREATE.INDEX filename indexname AT RELATIVE.PATH Wanted to share this *very* nice enhancement. Jeff Fitzgerald Fitzgerald Long, Inc. --- Not too long after my post, Steve O'Neal made this post about a problem. Haven't tested lately to see if it's still a problem or not --- There are, of course, caveats with everything. You cannot add a second index after you make the first one, AT RELATIVE.PATH, in a typical fashion. This would be necessary if you wanted indices with different characteristics, for example, with and without NO.NULLS. This behavior occurs on AIX 5.3 and Windows XP at UniVerse 10.2 Case 422234*USA has been created. C:\Accounts\bugs\IndexRelative2C:\ibm\uv\bin\uv This directory is not set up for uniVerse. Would you like to set it up (Y/N)?Y 0. Ideal UniVerse compatibility 1. IN2 compatibility 2. Prime Information compatibility 3. PICK compatibility 4. PI/open compatibility 5. Microdata Reality compatibility Which way do you wish to configure your VOC ? 0 Your VOC is configured for Ideal UniVerse compatibility Creating file VOC as Type 3, Modulo 23, Separation 4. Creating file D_VOC as Type 3, Modulo 2, Separation 1. Loading your VOC file. (Each * = 10 records.) Loading your D_VOC file. (Each * = 10 records.) * Creating file SAVEDLISTS as Type 1. Creating file D_SAVEDLISTS as Type 3, Modulo 1, Separation 2. Added @ID, the default record for RetrieVe, to D_SAVEDLISTS. Creating file VOCLIB as Type 2, Modulo 7, Separation 4. Creating file D_VOCLIB as Type 3, Modulo 1, Separation 2. Added @ID, the default record for RetrieVe, to D_VOCLIB. UniVerse Command Language 10.2 (c) Copyright IBM Corporation 2006. All rights reserved. IndexRelative2 logged on: Thu May 17 10:16:49 2007 CREATE.INDEX VOC F2 AT RELATIVE.PATH CREATE.INDEX VOC F1 AT RELATIVE.PATH Unable to open index map C:\Accounts\bugs\IndexRelative2/VOC/INDEX.MAP for rea d/write,CREATE.INDEX VOC F1 Unable to open index map C:\Accounts\bugs\IndexRelative2/VOC/INDEX.MAP for rea d/write, --- Hope this helps! Jeff Fitzgerald Fitzgerald Long, Inc. www.fitzlong.com On Tuesday, May 7, 2013, Rick Nuckolls wrote: Has anyone else used relative paths for Universe indexes? Perry, if you remember what problems you had, please get back to me! Thanks, Rick On May 7, 2013, at 6:38 AM, Perry Taylor perry.tay...@zirmed.comjavascript:; wrote: Tried it at 10.3.9 and there were some problems. I don't recall exactly what it was but I had to revert back to absolute pathing. Perry -Original Message- From: u2-users-boun...@listserver.u2ug.org javascript:; [mailto: u2-users-boun...@listserver.u2ug.org javascript:;] On Behalf Of Rick Nuckolls Sent: Monday, May 06, 2013 1:59 PM To: 'U2 Users List' Subject: [U2] Relative Path for SET.INDEX in Universe I was told, at one point, that it was possible to use a relative path in the SET.INDEX command for UV. If anyone knows that syntax, please advise! Thanks, Rick Nuckolls Lynden Inc ___ U2-Users mailing list U2-Users@listserver.u2ug.org javascript:; http://listserver.u2ug.org/mailman/listinfo/u2-users CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. ZirMed, Inc. has strict policies regarding the content of e-mail communications, specifically Protected Health Information, any communications containing such material will be returned to the originating party with such advisement noted. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org javascript:; http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org javascript:; http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org
Re: [U2] Relative Path for SET.INDEX in Universe
Thanks, Jeff. Someone else did respond, but with caveats that he remembered non-specific issues. One definitely seems to be that INDICES() returns the partial path rather than the full one, so utilities that use that information will have an issue. -Rick On May 15, 2013, at 8:56 PM, Jeff Fitzgerald j...@fitzlong.com wrote: Rick, Don't know if you got your question about relative index answered, but this was from a post I made back in 2007: IBM tech support just made me aware of an apparently undocumented feature that was added to SET.INDEX in release 10.1 allowing a relative path for the index file. SET.INDEX filename TO RELATIVE.PATH This command will set the index pathname to ./I_filename eliminating the need to reset the index pathname when the files are copied, as long as the data file and index file are copied as a unit. Similarly: CREATE.INDEX filename indexname AT RELATIVE.PATH Wanted to share this *very* nice enhancement. Jeff Fitzgerald Fitzgerald Long, Inc. --- Not too long after my post, Steve O'Neal made this post about a problem. Haven't tested lately to see if it's still a problem or not --- There are, of course, caveats with everything. You cannot add a second index after you make the first one, AT RELATIVE.PATH, in a typical fashion. This would be necessary if you wanted indices with different characteristics, for example, with and without NO.NULLS. This behavior occurs on AIX 5.3 and Windows XP at UniVerse 10.2 Case 422234*USA has been created. C:\Accounts\bugs\IndexRelative2C:\ibm\uv\bin\uv This directory is not set up for uniVerse. Would you like to set it up (Y/N)?Y 0. Ideal UniVerse compatibility 1. IN2 compatibility 2. Prime Information compatibility 3. PICK compatibility 4. PI/open compatibility 5. Microdata Reality compatibility Which way do you wish to configure your VOC ? 0 Your VOC is configured for Ideal UniVerse compatibility Creating file VOC as Type 3, Modulo 23, Separation 4. Creating file D_VOC as Type 3, Modulo 2, Separation 1. Loading your VOC file. (Each * = 10 records.) Loading your D_VOC file. (Each * = 10 records.) * Creating file SAVEDLISTS as Type 1. Creating file D_SAVEDLISTS as Type 3, Modulo 1, Separation 2. Added @ID, the default record for RetrieVe, to D_SAVEDLISTS. Creating file VOCLIB as Type 2, Modulo 7, Separation 4. Creating file D_VOCLIB as Type 3, Modulo 1, Separation 2. Added @ID, the default record for RetrieVe, to D_VOCLIB. UniVerse Command Language 10.2 (c) Copyright IBM Corporation 2006. All rights reserved. IndexRelative2 logged on: Thu May 17 10:16:49 2007 CREATE.INDEX VOC F2 AT RELATIVE.PATH CREATE.INDEX VOC F1 AT RELATIVE.PATH Unable to open index map C:\Accounts\bugs\IndexRelative2/VOC/INDEX.MAP for rea d/write,CREATE.INDEX VOC F1 Unable to open index map C:\Accounts\bugs\IndexRelative2/VOC/INDEX.MAP for rea d/write, --- Hope this helps! Jeff Fitzgerald Fitzgerald Long, Inc. www.fitzlong.com On Tuesday, May 7, 2013, Rick Nuckolls wrote: Has anyone else used relative paths for Universe indexes? Perry, if you remember what problems you had, please get back to me! Thanks, Rick On May 7, 2013, at 6:38 AM, Perry Taylor perry.tay...@zirmed.comjavascript:; wrote: Tried it at 10.3.9 and there were some problems. I don't recall exactly what it was but I had to revert back to absolute pathing. Perry -Original Message- From: u2-users-boun...@listserver.u2ug.org javascript:; [mailto: u2-users-boun...@listserver.u2ug.org javascript:;] On Behalf Of Rick Nuckolls Sent: Monday, May 06, 2013 1:59 PM To: 'U2 Users List' Subject: [U2] Relative Path for SET.INDEX in Universe I was told, at one point, that it was possible to use a relative path in the SET.INDEX command for UV. If anyone knows that syntax, please advise! Thanks, Rick Nuckolls Lynden Inc ___ U2-Users mailing list U2-Users@listserver.u2ug.org javascript:; http://listserver.u2ug.org/mailman/listinfo/u2-users CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. ZirMed, Inc. has strict policies regarding the content of e-mail communications, specifically Protected Health Information, any communications containing such material will be returned to the originating party with such advisement noted. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. ___ U2-Users mailing list
