Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Santa Cruz, Bolivia
    Posts
    4

    Post Unanswered: Recover data problem with a normal user

    Hello:

    Please i need help. I am using the Microsoft oledb provider to connec to one database in a DB2 server from a VB client application . I connect with this connection string:

    ocon.Open "Provider=DB2OLEDB;Password=db2user;Persist Security Info=True;User ID=db2user;Initial Catalog=posystem;Data Source=Server1;Network Transport Library=TCPIP;Network Address=192.168.0.15;Network Port=50000;Package Collection=nullid;Default Schema=db2admin"

    The database POSystem in the DB2 server is created by the user "db2admin" and in the "db2admin" schema and the "db2user" user has the privileges "read only" in this database; but when I try to send this SQL statement:
    "Select * from Orders", I can not obtain a result , any data is returned to me. And then, when I send the sql statement "Select * from db2admin.orders" or when I connecto to the database with the user "db2admin"(the owner user), I obtain data.

    This means that ever I have to put in my sql statement the name of the owner user ? or I have to connect with the login of the owner user to obtain data?
    I have to change something? or what I have to do to obtain data connectint with other user different to the owner and without use the login name in the sql statement?

    Thanks for the help

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: Recover data problem with a normal user

    When you connect to database and wants to use tables created by another user you have to prefix them by the owner's name as:

    table_owner. table_name

    In common situation you would want to create synonyms/alias's in your own schema. Ask the DBA to do that. Below is an example of creating a synonym/alias:

    CREATE ALIAS employee FOR MIKE.employee;

    From this point on you can just make a direct call to employee table rather than prefixing it with owner name.

    Hope this helps!!!

    Paul

    Originally posted by Elicita
    Hello:

    Please i need help. I am using the Microsoft oledb provider to connec to one database in a DB2 server from a VB client application . I connect with this connection string:

    ocon.Open "Provider=DB2OLEDB;Password=db2user;Persist Security Info=True;User ID=db2user;Initial Catalog=posystem;Data Source=Server1;Network Transport Library=TCPIP;Network Address=192.168.0.15;Network Port=50000;Package Collection=nullid;Default Schema=db2admin"

    The database POSystem in the DB2 server is created by the user "db2admin" and in the "db2admin" schema and the "db2user" user has the privileges "read only" in this database; but when I try to send this SQL statement:
    "Select * from Orders", I can not obtain a result , any data is returned to me. And then, when I send the sql statement "Select * from db2admin.orders" or when I connecto to the database with the user "db2admin"(the owner user), I obtain data.

    This means that ever I have to put in my sql statement the name of the owner user ? or I have to connect with the login of the owner user to obtain data?
    I have to change something? or what I have to do to obtain data connectint with other user different to the owner and without use the login name in the sql statement?

    Thanks for the help

  3. #3
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    try set schema

    before issuing any select, execute SET SCHEMA=DB2ADMIN

    HTH,
    Oliver

  4. #4
    Join Date
    Nov 2002
    Location
    Santa Cruz, Bolivia
    Posts
    4

    Re: try set schema

    Originally posted by oliver
    before issuing any select, execute SET SCHEMA=DB2ADMIN

    HTH,
    Oliver
    Hello Oliver:

    Thanks for your help about this topic. I implement your solution with this sentences in my vb code:

    ocon.execute "set schema = db2admin"
    ocon.execute strsql

    where the strsql is my select , and this works.
    Now I have a new question ......... please!, I want to execute those two sentences in one, that means:
    ocon.execute strsql

    where strsql has the two sentences (the "set" and the "select"), but I need to know the syntax that I need to use to execute a compound sql (please take account that I execute this code from vb).

    I will appreciate very much your help
    Thanks a lot

    Regards,
    Elizabeth

  5. #5
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    sorry I am not a VB programmer.

    but in Java, the equivalent command would be the Batch Statement command, where you can have more than one commands in a statement and execute them in one batch... I suppose there is an equivalent command in VB.

    Let's see what other members suggest.
    Oliver

  6. #6
    Join Date
    Nov 2002
    Location
    Santa Cruz, Bolivia
    Posts
    4

    Red face Is not a VB Question, is related to DB2 SQL syntax

    Originally posted by oliver
    but in Java, the equivalent command would be the Batch Statement command, where you can have more than one commands in a statement and execute them in one batch... I suppose there is an equivalent command in VB.

    Let's see what other members suggest.
    Oliver
    Hello other time:

    I think that my question was formulated in wrong way. The question is in DB2 there are a way to execute two sql statements in one time? for example in SQL Server we can write

    Set ansi_nulls on
    go
    Select * from customers

    In DB2 how can I do that?
    Thanks a lot

    Haarlem

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    As with SQL server but terminate each individual statement with a semi-colon rather than the string "go".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •