How to setup and Use ODBC For NON WORLDSHIP Use



How to setup and Use ODBC For NON WORLDSHIP Use

You will need to make sure you have Network connection from your RS6000 to the Target Computer that will be running ODBC, you cannot run this via Serial connection, it is a Network communications interface.

1. Have Prelude Load ODBC Account, Prelude will run the standard conversions on all base Prelude files at install time.

2. Find the following this will be required in later steps

a. IP or Name of Your Unidata Server (RS6000)

_____________________________

b. Unix Id of user you will login as for ODBC requests ______________________________

It is suggested you use a generic user or you will have to make sure you setup the proper permissions on all files/views using VSG Tool

Take special care when choosing a unix id make sure that there are no periods in the id this is a reserved

For Non System Administrators skip step 3-4

For Database Administrator or System Admins ONLY

3. download and install VSG Tools

you will need to Load the Visual Schema Generator (VSG) tool. This is only required to setup and create Views of your Data files and assign Permissions to those Views. It is NOT required on each workstation for normal use of ODBC.

4. On the System Admins PC run the Setup program located on the VSG CD you created in step 3d.

5. download and install IBM Unidata ODBC Driver & VSG Tools

a. ________________________________

b. Edit the UCI.CONFIG file located in the config Directory located using your favorite text editor.

c. Create the following entry at the bottom of this config file below All Other Entries

DBMSTYPE = UNIDATA

NETWORK = TCP/IP

SERVICE = udserver

HOST = IP/Name of UniData Server

d. Replace DataSourceName with a Name of Your Choosing, we suggest, LIVE or TEST to make things clear and Short. Do not use any spaces and remember case matters

e. Replace IP/NAME of UniData Server with the answer from 2a

f. Save changes and exit editor.

Note you can copy this UCI.CONFIG file to multiple machines providing you are all on the same network subnet and use the same routing.

6. setup ODBDC Data Sources for Windows Applications

a. Goto Windows Control Panel – Administrative Tools

b. Double Click on Data Sources (ODBC) Icon

You Should see this window.

[pic]

c. Click on System DSN, DO NOT Choose User DSN.

d. Click Add, You should see a window like this.

[pic]

e. Choose IBM Unidata ODBC Driver

f. Click Finish

You should see a window similar to

[pic]

g. Choose a Data source name that is meaningful and descriptive since most applications do not show the description. Example PRELUDELIVE,PRELUDETEST….

h. For Server enter the Name us used in step 5h, DatsourceName.

i. Database /ud/ODBC-XXX where XXX is your ODBC-XXX account that was loaded and setup by Prelude.

j. For User use the user you assigned at step 2b. If you choose to have different users then make sure your System Administrator knows to grant you privileges on files and views. NOT Unix Permission, but VSG Permssions.

The next Step is for System Administrator.

Skip to How To Access Files Section for Non-Administrators.

7. File Setup and Schema Maps.

a. Now that you have your basic environment setup you can start creating views and Schemas for your Prelude Files, then next several steps will explain how to do this, but before we start lets go over some terminology.

• Table – Same as A File in Prelude but one glaring difference, Tables are Flat, Prelude files are Not.

• Sub-Table – Sub set of data that is attached to the Main Table, This is how Multi-valued Associated Data is presented in SQL.

• VIEW – a layout that allows users to access fields/data values or subset of . You can have Multiple Views into a file where some fields are hidden in specific views, but Every file MUST have at least 1 view.

• Privilege- rights granted to you for a specific file & View, Select, Insert, Update, Delete

Privileges have nothing to do with Aix level permissions.

• Special User “PUBLIC” is a Generic term used for all users on your “Prelude Server”

•

b. Open up VSG tool you should see a popup similar to this

[pic]

c. Choose your servername from the dropdown box, it should be the same as the name you created in your uci.config file from 5h and 6h.

d. Database name enter /ud/ODBC-XXX same as you did in 6j, VSG does not use the “ODBC DataSources record”

e. User id from step 2b. You can create a “supervisor” aix user that owns all files but remember that user is the only one that can fix file if privileges are messed up. Suggestion is to use a common user like “odbc”.

f. Password, the password that your Prelude server operating system understands, you can NOT leave this blank. This is your aix login, NOT your SB+ login.

g. Click connect.

8. Now you can start working with files/tables.

[pic]

9. There are 4 tabs you should spend 99.9% of your time in the first tab, the other tabs do not need to be used since they are contained within the appropriate sub sections of the File/Table Tab.

10. Enter Your File name

you should see a window like this

[pic]

11. If this is the initial setup of a file or you are adding a New Field click Map Attributes, this is the “MASTER” list of attributes, if you delete an entry here it will remove it from all access Views. If you are adding something that did not exist in the original Prelude file you will have re-convert the entire file using File Conversion tool, or manually edit the dictionary item into your ODBC-XX Version of the file.

You should see a screen similar to this

[pic]

12. add/remove fields from the master Map as needed, then click OK. You should see something similar to this

[pic]

13. Now that you have a master view, assign privileges by clicking the Privileges button under Manage

[pic]

14. There are 3 possible answers for each action, “G”rant rights, “X” allow right, “ “ not allowed. You are not required to list every user if you set “PUBLIC” to either “X” or “G”. I would suggest you give “G”rant rights to at least one user other the then owner of this file unless you “G”rant to Public. The reason is if the “owner” gets deleted then there is no easy way to fix the problem, unless there is another user who has Grant rights.

15. Ok this, and now you have a Master view which people can’t access thru ODBC. Until you create a “view”, Choose Views From Manage window and you should see something like this

[pic]

16. Click Create View and you will see a window like this

[pic]

17. Add and remove the desired fields from the view, note that Standard Single Valued fields should be populated into the “_NF” table and other Associated files will create sub-tables or alternate views.

Example of an alternate view , since CUSTOMER_ROUTE_NUM is a multi-valued field and it has an association defined VSG will offer to create the sub-view for me if I choose associated fields using its association name as the table key

NOTE you will have to grant privileges to these alternate tables and views accordingly.

[pic]

18. Do NOT go into Create View to add fields or update a view or sub-table, use modify under current view, otherwise you will be creating a brand new View.

19. If you wish to create “alternate limited Views” for specific subset of users then use the sub-tables and this will yield a “_SUB” extention to the original view name so in my example I would get A CUSTOMER_NF_SUB, It is possible to go straight to Sub-tables and not create a standard “MASTER VIEW”, but that introduces extra levels for single valued fields for no reason since unless you have a view or Sub-Table you can’t see field data.

how to access a Unidata file using ODBC and Excel

1. Open Excel to a blank worksheet

2. Choose Data – Import External Data – New Database Query You should see a window similar to this

[pic]

3. Choose your data source name that you should have setup in step 6f, you should get a window like this

[pic]

4. After you enter in your correct password you should see a window similar to this

[pic]

5. I am going to get data from Customer File , specifically Name, address, city so I choose the “NF” or Normal flat fields table and choose my fields.

6. Click next and Windows query wizard ask me how I want to filter and sort my data.

7. It also gives me the option of saving my query or viewing the output in Microsoft query viewer.

8. when I finish my query setup excel will ask me where I want to start putting my data, I choose my column & row and which worksheet. And after a few seconds I see

[pic]

Common Questions/issues.

• When I list my files in VSG I see different names for File vs SQL Table, whats the Deal?

[pic]

In the Above example see ORDER is mapped to ORDER_1 SQL Table, this is because someone Created the ORDER map and someone came in after them and Re-Mapped ORDER file to a new Schema Map, once you do this you can Not easily switch back to the original map without some manual cleanup on the ODBC-XXX Account.

• I get this error when I try and view a file/table in VSG

[pic]

This happened because whoever setup the Schema didn’t grant me privileges to the file ORDER which is mapped to SQL Table ORDER_1, either contact your System admin and have them grant permissions or you will have to contact Activant to have them steal permissions to fix the issue.

• Why cant I Create a Schema for ORDER file it keeps re-naming it ORDER_1 Why?

ORDER is a reserved word in SQL so VSG is creating a new name for it, but Prelude already Created a synonym called ORDER_FILE during the Standard Conversions

• The User that we always used was deleted from our system how do we allow access to odbc again.

There are several ways to do this,

1. re-establish that user with its original unix user id, this should be done by someone trained and proficient with aix security structures.

2. Activant can Steal the permissions for that user and assign every file to another id, this could take a while to run thru all the files and Views.

3. If you have Granted Permissions for Public and used the “G” vs the “X” option you are able to Grant or change permissions on that file/view.

4. you can login as root in ODBC and Grant Privileges to users a you need.

• I created my fields in Prelude account but they don’t show up in ODBC, what is wrong, what did I miss?

When you create a field in Prelude account it does not automatically update the ODBC Account since we have 2 separate dictionary files, the data will be there but you can’t see it. Here are the ways to fix this.

1. Run the File Conversion/setup option on odbc account, leave file name blank on main screen, F5 Add Dicts, and put filename and single dictionaries, F2 And its in the account, then add it to your Views and Sub-Tables in VSG tool.

2. Copy the dictionary Item from your Prelude account to the ODBC-XXX Account Dictionary file, this is easy to do if you know how to create pointers and making sure you copy to correct place and it must be valid SQL Name. then add it to your Views and Sub-Tables in VSG tool.

3. Create the Dictionary in your ODBC Account using /FD and make sure its ODBC Compliant Name, then add it to your Views and Sub-Tables in VSG tool.

•

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download