Hi Andrew Because UniVerse dictionaries contain not only solid column definitions, but also synonyms and virtual fields, you have to choose which of these will be exposed for ODBC or similar relational protocols. A typical dictionary for a mature system might have a thousand entries which makes navigating these in something like MS query or the .NET data set designer very difficult!
So for this you need to do two things: First, create a phrase named @SELECT. This is a dictionary entry in the format: Key: @SELECT 1: PH 2: list of fields SPACE delimited. You can test this out by doing a SELECT * from file at TCL, which uses the same phrase. Then, you need to update the file information cache used by ODBC (prevents it having to scan every dictionary on connect). For this, run the HS.UPDATE.FILEINFO command after making your changes. However, you might also want to step back and consider whether ODBC is really your best choice. To use ODBC effectively you need to understand about dynamic normalizing and the differences between the UniVerse data model and the relational model expected by ODBC. Depending on what you wish to do, ODBC may tie you up in knots and you may be better off looking at one of the native protocols (the UniObjects family) or web services instead. Also, I don't know why you chose to copy your VOC across. That will contain pointers to all the files from the original system, which you don't have on yours, hence the large number of errors you are seeing. If you are brave, you can try a CLEAN.ACCOUNT command, but take a backup first! [AD] You might want to consider my latest book, 'IBM UniVerse Client Developer' (written when this was still an IBM product) which covers the various protocols in depth. You can find details on my website, www.brianleach.co.uk. [/AD] Regards Brian -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Andrew E. Tegenkamp Sent: 02 February 2010 4:17 PM To: 'U2 Users List' Subject: Re: [U2] Resize Question Thank you very much! That has helped a lot and I can now see the data in the UV command line :) If possible, I am now hoping to be able to expose this imported data to ODBC so I can work with it in PHP, etc. But I am again finding problems I cannot find an answer for in the help docs or searching the list archive. First, I created a PICK account using the U2UG guide mentioned. I then used HS.ADMIN option 3 to "Activate access to files in an account" which went well and showed it as an activated account. I copied over the TEMP and VOC (D_VOC, D_VOCLIB, VOC, VOCLIB, etc) files from the live UniVerse server to my account as well as my test table ZIP (ZIP and D_ZIP). I fnuxi'd and SET.INDEX'd them and could see the data on the command line using SELECT COUNT(*) FROM ZIP and the like. However, when I went to ODBC I could only see 3 tables. AE_DOC, HELP_FILE, and HS_FILE_ACCESS (ODBC reported no rows in HS_FILE_ACCESS). I ran HS.ADMIN option 3 again on the account and for most things it said: Could not open file FILENAME. STATUS() = -2 "FILENAME" was not written into File Information Cache. For the file I copied over, ZIP, it said: ZIP's dictionary has no @ phrase or @SELECT phrase. "ZIP" was written into File Information Cache but may not behave as desired. This is a little confusing as I thought D_ZIP was the dictionary file and SELECT works on the command line. After this, I checked ODBC and saw the 3 above and one more table called UVMD. I am not sure what this was so I got it but it appears to be a system table of some sort. Thanks again for any tips on how to properly setup/expose this file/table to ODBC! Andrew -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Monday, February 01, 2010 11:55 AM To: 'U2 Users List' Subject: Re: [U2] Resize Question Okay, a few things here... First the index issue. When UniVerse creates an index on a file, it puts the absolute pathname to the index into the file header. You will need to remove this using the SET.INDEX command thus: SET.INDEX filename TO NULL Next, the file size: The D_ZIP is the dictionary file. If you haven't fnuxi'd this, the modulus in the file header will be in the wrong byte order, so the reported modulus (16777216) is garbage. You will need to fnuxi D_ZIP as well. Incidentally - Download the free daily admin guide from the U2UG website (under the Learner Pack) for more details: here is a top level view - The modulus is one of the two components of sizing a file, and refers to the number of groups (pages) in the file. As it says, in personal edition there is a limit to this value. This number is independent of the actual data stored since a) it is the space reserved, so part may be empty, and b) you can make the size of each group (page) - also known as the separation - larger to store the data, it just makes the file less performant (and affects things like locking). To change the modulus and separation, look at the RESIZE command (HELP RESIZE). Brian -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Andrew E. Tegenkamp Sent: 01 February 2010 4:15 PM To: u2-users@listserver.u2ug.org Subject: [U2] Resize Question I'd like to preface this by saying I am a UniVerse newbie. Ideally, I am trying to import some data into a personal edition UniVerse database but I am having some trouble. One example is a ZIP code lookup called ZIP. First, I copied the files in from a UNIX system to a Windows install of Personal Edition. Not ideal but not sure if it's a deal-breaker as yet so I'm moving on... Then, I did fnuxi VOC and fnuxi ZIP. VOC was fine but on ZIP I got a warning the secondary indices are defined in a unix location and if necessary, use SET.INDEX to update file header to correct locations. I am not sure how to use SET.INDEX so I have not done anything with that. Now I go to UniVerse and get "Your VOC is out of date. Update to current release (Y/N)? If I select Yes, I get an error about &TEMP& being in my VOC file but unable to create it. I believe this is because I do not have all the data copied over from the live UniVerse server, so I select No and get "Unable to open the operating system file "OC.O/LOGON.TESTER" where TESTER is the account name logging on. That gives me a prompt so I can do ANALYZE.FILE ZIP and I see the file type is 18, modulo is 4507 and separation is 1. There are 44,098 records. Then I try a "SELECT ST FROM ZIP" to get the states and I get an error saying "File "D_ZIP" has modulo 16777216 which is > 10007. Please resize it to modulo <= 10007 in order to use it in Personal Edition." I have tried running RESIZE of various file types, modulo, and separation, but I still get the error on D_ZIP every time. I have no idea how to get D_ZIP down to <= 10007 or if it is even possible, or if the records being 44k+ means I cannot view it in personal edition or what exactly is going 44k+ on with this. Can anyone help me past this or tell me why I can't get past it? Thanks for any tips for a newbie, Andrew _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2660 - Release Date: 01/31/10 19:35:00 _______________________________________________ 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 No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2662 - Release Date: 02/01/10 12:37:00 _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
