IBM Data Studio无法浏览SAMPLE（DB2 Express-C）上的数据 [英] IBM Data Studio can't browse data on SAMPLE (DB2 Express-C)
问题描述
我无法浏览SAMPLE DB上的数据。安装程序如下： - Windows 7 64位PRO，
- IBM DB2 Express-C，10.5.500.107（最新） - IBM Data Studio V4.1.1（管理安装，使用IBM Installation Manager 1.8.1安装）数据库凭据是：db2admin / db2admin Windows管理员用户名是：Nenad（密码保护）
使用DB2命令线路处理器（开始 - > IBM DB2 DB2COPY1（默认） - > DB2命令行处理器）我可以使用以下方式连接到SAMPLE数据库：
db2 =>连接到样本
数据库连接信息
数据库服务器= DB2 / NT64 10.5.5
SQL授权ID = NENAD
本地数据库别名= SAMPLE
并查询工作人员表：
db2 =>选择*从工作人员
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- - ----- --------- ---------
10 Sanders 20 Mgr 7 98357.50 -
20 Pernal 20销售8 78171.25 612.45
30 Marenghi 38 Mgr 5 77506.75 -
40 O'Brien 38销售6 78006.00 846.55
50 Hanes 15 Mgr 10 80659.80 -
60 Quigley 38销售 - 66808.30 650.25
70 Rothman 15销售7 76502.83 1152.00
80詹姆斯20文员 - 43504.60 128.20
90 Koonitz 42销售6 38001.75 1386.70
100 Plotz 42 Mgr 7 78352.80 -
110 Ngan 15文员5 42508.20 206.60
120 Naughton 38秘书 - 42954.75 180.00
130山口42职员6 40505.90 75.60
140 Fraye 51 Mgr 6 9115 0.00 -
150威廉姆斯51销售6 79456.50 637.65
160 Molinare 10 Mgr 7 82959.20 -
170 Kermisch 15秘书4 42258.50 110.10
180亚伯拉罕38秘书3 37009.75 236.50
190 Sneider 20职员8 34252.75 126.50
200 Scoutten 42职员 - 41508.60 84.20
210路10 Mgr 10 90010.00 -
220史密斯51销售7 87654.50 992.80
230 Lundquist 51职员3 83369.80 189.65
240 Daniels 10 Mgr 5 79260.25 -
250 Wheeler 51秘书6 74460.00 513.30
260 Jones 10 Mgr 12 81234.00 -
270 Lea 66 Mgr 9 88555.50 -
280 Wilson 66销售9 78674.50 811.50
290 Quill 84 Mgr 10 89818.00 -
300 Davis 84销售5 65454.50 806.10
310 Graham 66销售13 71000.00 200.30
320冈萨雷斯66销售4 76858.20 844.00
330 Burke 66职员1 49988.00 55.50
340爱德华兹84销售7 67844.00 1285.00
350 Gafney 84职员5 43030.50 188.00
选择35条记录。
所以我猜DB / DBMS工作正常。
然后我使用设置在Data Studio中创建连接：
数据库：SAMPLE
主机：localhost
端口号：50000
用户名：db2admin
密码：db2admin
strong>测试连接：与 Ping成功！结果
当我点击表格时，我可以看到所有的。 / p>
但是当我右键单击（模式：NENAD）（表名称：STAFF）数据 - >浏览数据我收到此错误：
com.ibm.db2.jcc.am.SqlSyntaxErrorException：该语句失败，因为授权ID没有执行操作所需的授权或权限。授权ID：DB2ADMIN。操作：SELECT。对象：NENAD.STAFF.. SQLCODE = -551，SQLSTATE = 42501，DRIVER = 4.18.60
语句失败，因为授权ID没有执行操作所需的授权或权限。授权ID：DB2ADMIN。操作：SELECT。对象：NENAD.STAFF.. SQLCODE = -551，SQLSTATE = 42501，DRIVER = 4.18.60
我尝试了几次重新安装DB2和Data Studio与各种设置和用户名，我试图右键单击表，然后管理权限（给予所有的权限，对于DB2ADMIN和NENAD），但徒劳无功。
我也可以创建新的数据库，我收到这个错误：
创建数据库FTN自动存储是在'C：\DB2\NODE0000'DBPATH ON'C ：\DB2\NODE0000'ALIAS FTN WITH'FTN数据库'
无法执行命令。 DAS返回以下错误：sqlcode = -22201 output = null
我的问题是：
- 我应该如何解决这个错误？
- 是否有一个简单的方法就像在SQL开发人员一样）创建
用户，然后点击授予所有权限就可以了。
使用Oracle Express DB和SQL Studio，我以非常简单的方式创建新的用户和授予权限：
编辑 - 更新：
我不得不使用： / p>
步骤1：
开始菜单 - > IBM DB2 DB2COPY1（默认） - > DB2命令行处理器
在DB2命令行处理器中：
db2 =>连接到样本
db2 => GRAT DBADM ON DATABASE to db2admin
步骤2：
db2admin stop
db2admin start
之后，我能够浏览Data Studio中的数据，并执行查询，但是我仍然无法从Data Studio创建新的数据库？
这些帖子不直接相关，或没有帮助我解决此问题：
无法连接IBM Data Studio与DB2 Express-C（错误：用户标识已被吊销）
DB2查询在IBM Data Studio中运行，但不在代码中
如何在db2 10.1中使用新建实例IBM Data Studio
您的问题是您以Nenad（登录到默认架构）并且您通过DataStudio作为db2admin登录到数据库。您需要以Nenad身份登录Data Studio。或者作为Nenad，您需要向用户ID db2admin授予适当的权限。
编辑：添加到更好在下面的意见中解释事情以及您的问题.... DB2不会像其他
数据库那样使用创建的用户ID。它查找基础操作系统或LDAP for
身份验证。在Unix / Linux环境中，实例ID是
，管理上最高的ID是实例设置为
的ID。您的db2admin帐户是此帐户。那就是
说.... Windows引入了不同的安全方案。即使
实例ID存在，安装产品（其用户ID）
的人往往在DB2中被授予高级管理权限。当
发出CREATE DATABASE语句时，您正在使用
Windows用户标识。因此，它成为数据库的所有者，
具有DBADM，SECADM，DATAACCESS和ACCESSCTRL授予它。
db2admin通常不需要
ACCESSCTRL 就可以使用$ code> DBADM。因此，您需要以Nenad（ID $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $） $ c>，SECADM，DATAACCESS，
ACCESSCTRL到用户db2admin。现在这个ID应该是你要
寻找的。
我不推荐任何ID的特权。如果你需要的是
访问表，那么DATAACCESS应该这样做。
I can't browse data on SAMPLE DB. Setup is as follows: - Windows 7 64 bit PRO, - IBM DB2 Express-C, 10.5.500.107 (latest) - IBM Data Studio Version 4.1.1 (Administrative installation, Installed using IBM Installation Manager 1.8.1)
Database credentials are: db2admin/db2admin Windows administrator username is: Nenad (password protected)
With DB2 command line processor (Start -> IBM DB2 DB2COPY1 (Default) -> DB2 Command Line Processor) I can connect to SAMPLE database using:
db2 => connect to sample
Database Connection Information
Database server = DB2/NT64 10.5.5
SQL authorization ID = NENAD
Local database alias = SAMPLE
And query staff table with:
db2 => select * from staff
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
10 Sanders 20 Mgr 7 98357.50 -
20 Pernal 20 Sales 8 78171.25 612.45
30 Marenghi 38 Mgr 5 77506.75 -
40 O'Brien 38 Sales 6 78006.00 846.55
50 Hanes 15 Mgr 10 80659.80 -
60 Quigley 38 Sales - 66808.30 650.25
70 Rothman 15 Sales 7 76502.83 1152.00
80 James 20 Clerk - 43504.60 128.20
90 Koonitz 42 Sales 6 38001.75 1386.70
100 Plotz 42 Mgr 7 78352.80 -
110 Ngan 15 Clerk 5 42508.20 206.60
120 Naughton 38 Clerk - 42954.75 180.00
130 Yamaguchi 42 Clerk 6 40505.90 75.60
140 Fraye 51 Mgr 6 91150.00 -
150 Williams 51 Sales 6 79456.50 637.65
160 Molinare 10 Mgr 7 82959.20 -
170 Kermisch 15 Clerk 4 42258.50 110.10
180 Abrahams 38 Clerk 3 37009.75 236.50
190 Sneider 20 Clerk 8 34252.75 126.50
200 Scoutten 42 Clerk - 41508.60 84.20
210 Lu 10 Mgr 10 90010.00 -
220 Smith 51 Sales 7 87654.50 992.80
230 Lundquist 51 Clerk 3 83369.80 189.65
240 Daniels 10 Mgr 5 79260.25 -
250 Wheeler 51 Clerk 6 74460.00 513.30
260 Jones 10 Mgr 12 81234.00 -
270 Lea 66 Mgr 9 88555.50 -
280 Wilson 66 Sales 9 78674.50 811.50
290 Quill 84 Mgr 10 89818.00 -
300 Davis 84 Sales 5 65454.50 806.10
310 Graham 66 Sales 13 71000.00 200.30
320 Gonzales 66 Sales 4 76858.20 844.00
330 Burke 66 Clerk 1 49988.00 55.50
340 Edwards 84 Sales 7 67844.00 1285.00
350 Gafney 84 Clerk 5 43030.50 188.00
35 record(s) selected.
So i guess DB/DBMS works fine.
Then I create connection in Data Studio using settings:
Database: SAMPLE
Host: localhost
Port number: 50000
Username: db2admin
Password: db2admin
And Test connection: with Ping succeeded! result.
When i click on Tables I can see all of them.
But when I right-click on (Schema: NENAD) (Table Name: STAFF) Data -> Browse Data I got this error:
com.ibm.db2.jcc.am.SqlSyntaxErrorException: The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "DB2ADMIN". Operation: "SELECT". Object: "NENAD.STAFF".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.18.60 The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "DB2ADMIN". Operation: "SELECT". Object: "NENAD.STAFF".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.18.60
I tried several reinstallations of both DB2 and Data Studio with various settings and usernames, I tried to right click on table then manage privileges (giving all privileges to both DB2ADMIN and NENAD) but in vain.
I also can't create new database, I got this error:
CREATE DATABASE FTN AUTOMATIC STORAGE YES ON 'C:\DB2\NODE0000' DBPATH ON 'C:\DB2\NODE0000' ALIAS FTN WITH 'FTN database' Failed to execute command. DAS returned the following error: sqlcode=-22201 output=null
My questions are:
- How should I resolve this error?
- Is there a simple way (just like in SQL developer) to just create user and then click on Grant All Privileges and that's it?
With Oracle Express DB and SQL Studio I create new user and grant permissions very in this (very simple) way:
EDIT - UPDATE: I had to use:
Step 1:
Start menu -> IBM DB2 DB2COPY1 (Default) -> DB2 Command Line Processor
In DB2 Command Line Processor:
db2 => connect to sample
db2 => GRANT DBADM ON DATABASE to db2admin
Step 2:
db2admin stop
db2admin start
and after that I was able to browse data in Data Studio, and to perform queries, but I am still not able to create new database from Data Studio?
Those posts are not directly related or haven't helped me to resolve this issue:
Cannot connect IBM data studio with DB2 Express-C (Error: User Id Revoked)
DB2 query runs in IBM Data Studio but not in code
IBM Data Studio Connection error SQL1035N
IBM data studio not connecting to local DB2 databases
Why don't I see all of my DB2 instances in Data Studio?
IBM DB2 created schema is not visible?
IBM Data Studio (Admin client) version 3.1.1
How to create New instance in db2 10.1 using IBM Data Studio
How to create a new schema on a database at IBM Data Studio?
Your issue is that you are logging in to the command line as Nenad (you can tell that by the default schema), and that you are logging into the DB through DataStudio as db2admin. You either need to log in to Data Studio as Nenad. Or as Nenad you need to grant the appropriate permissions to user ID db2admin.
EDIT: To add to better explain things as well as your question in the comments below.... DB2 doesn't use created user ids like other databases do. It looks to the underlying operating system or LDAP for authentication. In the Unix/Linux environment, the instance Id that is the highest ID administratively is what ID the instance is set up to run as. Your db2admin account is this account. That being said....Windows introduces a different security scheme. Even though the instance ID exists, whoever installed the product (their user ID) tends to be granted high administrative powers in DB2. And when you issue the
CREATE DATABASEstatement, you are doing it with your Windows user ID. Therefore, it becomes the owner of the database and hasDBADM,SECADM,DATAACCESS, andACCESSCTRLgranted to it. db2admin will generally haveDBADM WITHOUT DATAACCESS WITHOUT ACCESSCTRL. So you would need to log in as Nenad (the ID withSECADM) and grant (to be safe)DBADM,SECADM,DATAACCESS,ACCESSCTRLto user db2admin. Now that ID should have what you are looking for.I don't recommend those privileges for just any ID. If all you need is access to read/write to tables then
DATAACCESSalone should do it.
这篇关于IBM Data Studio无法浏览SAMPLE（DB2 Express-C）上的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
