DB2 - ODBC connection with powerhouse on windows

[ken weiland]

Hi Brian,
I have migrated powerhouse applications to windows  pointing to an oracle data on a unix platform
And  using ODBC to connect to it.
 
I have also done this with MS SQL Server
 
Qshow looks at the database metadata area to display the table layouts
 
Quiz will create a SQL statement to send thru the ODBC connection to the data base
And will display the result back and this will read the database table
 
So setting up the tables in DB2 with the right user access is critical
User have to be granted access and user may have default access workspaces
Understanding how the DB2 works  and what is possible will let  determine what you can do
 
If you can create a sql statement in db2 that does not need the in workspace syntax to retrieve data
This may be a combination of what workspace or no workspace  setup and the user rights /defaults
 
On my reading of DB2 the tables are created in a workspace and users need to be granted access to this workspace in order to get at these tables
It seems that tables can be created without the workspace command
(you should try this as well – that may solve your problem)
 
There is also a workspace PUBLIC – which may allow easier access to
 
My suggestion:
 
Create a table in DB2 using SQL statements
Insert some data into it
Create a user or use a user that has access to this workspace
 
Create the ODBC connection in windows  -  with that user 
ODBC connection  can have default database or schema  setup in an advance tab area
 
Determine if the odbc connection can retrieve data by using it as a data source for excel
 
Add this odbc connection to the powerhouse dictionary using the user that has access to this area
Users may in DB2 have a default access to a workspace if one is not selected
 
You may want to setup a user with access to only 1 workspace and public may have lower security thend others
 
If this testing works, then look at how the powerhouse tables were setup in DB2 and what users
Were assigned and change this to the working solution
 
You may also want to try the following:
-set a data base in MS SQL Server with some tables
- create the database and tables using dbo
- the ODBC connection to SQL Server can be set to a default database and the user could be sa
- add this connection to the powerhouse dictionary
 
You can have many ODBC connections, name differently with a different default set for each one
 
DB2 has a way to connect to external non-DB2 data bases, and you may be able to do this
With the MS SQL Server
 
in windows, users can be given their startup commands, that could point to their own dictionary
that has the odbc connection for them - this is very easy to do
 
If you want to contact me directly, you can email  at ken_weiland at yahoo.com
Let me know how you make out
 
Ken
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130712/3bf8dde0/attachment.htm>