Professional Web Applications Themes

Help with statement and Schema - Newbie - IBM DB2

I have just done an install on Windows XP. Version 8.1 DB2 UDB (warehouse) I have a talbe called VT08_Year Column one is Char(3), Column Two is Char(6) YearCode and Year Desc A90, 1999 A95, 1999.5 B00, 2000 B10, 2001 B20, 2002 When I ran the script to create the table, I was logged onto the computer as LESB with full administrator rights. Now, this table resides in schema LESB - for the moment I don't know how or why. But I've created another user SA, given them admin rights, and rights to the LESB.VT08_Year Table. However, with an ADO ...

  1. #1

    Default Help with statement and Schema - Newbie

    I have just done an install on Windows XP.
    Version 8.1 DB2 UDB (warehouse)

    I have a talbe called VT08_Year
    Column one is Char(3), Column Two is Char(6)
    YearCode and Year Desc
    A90, 1999
    A95, 1999.5
    B00, 2000
    B10, 2001
    B20, 2002

    When I ran the script to create the table, I was logged onto the
    computer as LESB with full administrator rights.

    Now, this table resides in schema LESB - for the moment I don't know
    how or why. But I've created another user SA, given them admin
    rights, and rights to the LESB.VT08_Year Table. However, with an ADO
    connect I cannot see this table with this query "SELECT * FROM
    VT08_Year". I get an error stating that the server cannot find object
    "SA.VT08_Year".

    Can you help me understand what is happening here?
    I am familiar with Oracle, and believe IBM Aliases are similar to
    Oracle Synonyms but I don't want to have to create Aliases for all my
    tables for the SA user. How is this solved?

    Many thanks,
    Les Block
    Les Guest

  2. #2

    Default Re: Help with statement and Schema - Newbie

    Les Block wrote:
     

    You will need to qualify your query "select * from LESB.VT08_Year". If
    you don't want to do that, you can set the default schema to LESB by
    setting the "CURRENT SCHEMA" special register when you did the connect.

    No Guest

  3. #3

    Default Re: Help with statement and Schema - Newbie



    Where is this "CURRENT SCHEMA" specail register and how do I set it?

    If I were to use Aliases, does that mean that every user needs a
    schema, in order to make aliases to the original schema? How do you
    make many users have access to the same schema without having to make
    schemas for each user and then making aliases? Is there a way to make
    a group house the aliases, and then each user if part of the group. I
    have a database with many tables, and 200 users, it seems a bit
    peculiar that I would need to create 200 schemas for 200 users, and
    then create all the aliases to the original schema?

    Thanks for your response,
    The whole schema thing is really baffling coming from an MS SQL server
    background.

    Les
    Les Guest

  4. #4

    Default Re: Help with statement and Schema - Newbie

    Les Block wrote: 
    >
    >
    > Where is this "CURRENT SCHEMA" specail register and how do I set it?[/ref]

    The statement: "set current schema lesb" would do it.

    After executing this statement, any user executing the query
    "select * from vt08_year" would read
     

    Yes. DB2 does not provide the concept of a public synonym (alias). So,
    your options are:

    1) Use fully qualified table names (select * from lesb.vt08_year)

    2) Use "set current schema" immediately after your application connects
    Note: you can also set this in the db2cli.ini file on the client
    workstations.

    3) Create aliases for all users.


    Good luck,



    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Ian Guest

Similar Threads

  1. Replies: 3
    Last Post: September 22nd, 08:09 AM
  2. Need help with an SQL statement - Newbie
    By alanenglefield@gmail.com in forum MySQL
    Replies: 0
    Last Post: April 21st, 03:09 PM
  3. If then statement / mysql query: NEWBIE
    By phatnugs420@comcast.net in forum PHP Development
    Replies: 1
    Last Post: August 11th, 10:18 AM
  4. Registering WMI Schema
    By Ian Turner in forum ASP.NET General
    Replies: 0
    Last Post: July 29th, 12:41 PM
  5. SQL Query with Schema
    By Amardeep Verma in forum IBM DB2
    Replies: 1
    Last Post: July 29th, 09:00 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139