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 版本 4.1.1(管理安装，使用 IBM Installation Manager 1.8.1 安装)
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)
数据库凭据是:db2admin/db2admin Windows 管理员用户名是:Nenad(密码保护)
Database credentials are: db2admin/db2admin Windows administrator username is: Nenad (password protected)
使用 DB2 命令行处理器(开始 -> IBM DB2 DB2COPY1(默认)-> DB2 命令行处理器)我可以使用以下方法连接到 SAMPLE 数据库:
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
查询人员表:
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.
所以我猜 DB/DBMS 工作正常.
So i guess DB/DBMS works fine.
然后我使用设置在 Data Studio 中创建连接:
Then I create connection in Data Studio using settings:
Database: SAMPLE
Host: localhost
Port number: 50000
Username: db2admin
Password: db2admin
测试连接:Ping成功！结果.
当我点击表格时，我可以看到所有表格.
When i click on Tables I can see all of them.
但是当我右键单击 (Schema: NENAD) (Table Name: STAFF) Data -> Browse Data 我得到这个错误:
But when I right-click on (Schema: NENAD) (Table Name: STAFF) Data -> Browse Data I got this error:
com.ibm.db2.jcc.am.SqlSyntaxErrorException:语句失败，因为授权标识没有执行操作所需的授权或特权.授权 ID:DB2ADMIN".操作:选择".对象:NENAD.STAFF".. SQLCODE=-551，SQLSTATE=42501，DRIVER=4.18.60语句失败，因为授权标识没有执行操作所需的授权或特权.授权 ID:DB2ADMIN".操作:选择".对象:NENAD.STAFF".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.18.60
我尝试了多次使用各种设置和用户名重新安装 DB2 和 Data Studio，我尝试右键单击表然后管理权限(将所有权限授予 DB2ADMIN 和 NENAD)但徒劳无功.
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:DB2NODE0000' DBPATH ON 'C:DB2NODE0000' ALIAS FTN WITH 'FTN database'执行命令失败.DAS 返回以下错误:sqlcode=-22201 output=null
我的问题是:
- 我应该如何解决这个错误?
- 有没有一种简单的方法(就像在 SQL 开发人员中一样)来创建用户，然后点击授予所有权限，就这样了吗?
使用 Oracle Express DB 和 SQL Studio，我以这种(非常简单)的方式创建新用户并授予权限:
With Oracle Express DB and SQL Studio I create new user and grant permissions very in this (very simple) way:
编辑 - 更新:我不得不使用:
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
之后我可以在 Data Studio 中浏览数据并执行查询，但我仍然无法从 Data Studio 创建新数据库?
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:
无法连接 IBM使用 DB2 Express-C 的数据工作室(错误:用户 ID 已撤销)
DB2 查询在 IBM Data Studio 中运行但不在代码中
我为什么不在 Data Studio 中查看我的所有 DB2 实例?
IBM Data Studio(管理员客户端)版本 3.1.1
如何创建使用 IBM Data Studio 的 db2 10.1 中的新实例
推荐答案
您的问题是您正在以 Nenad 身份登录命令行(您可以通过默认架构来判断)，并且您正在登录数据库通过 DataStudio 作为 db2admin.您需要以 Nenad 身份登录 Data Studio.或者作为 Nenad，您需要向用户 ID db2admin 授予适当的权限.
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.
为了更好地解释事情以及您在下面的评论中提出的问题...... DB2 不像其他人那样使用创建的用户 ID数据库可以.它查找底层操作系统或 LDAP验证.在 Unix/Linux 环境下，实例 ID 为管理上的最高 ID 是实例设置的 ID运行为.您的 db2admin 帐户就是这个帐户.那个存在说....Windows 引入了不同的安全方案.虽然实例 ID 存在，无论是谁安装了产品(他们的用户 ID)在 DB2 中往往被授予较高的管理权限.当你发出 CREATE DATABASE 语句，你正在用你的Windows 用户 ID.因此，它成为数据库的所有者，并且已授予其 DBADM、SECADM、DATAACCESS 和 ACCESSCTRL.db2admin 通常会有 DBADM WITHOUT DATAACCESS WITHOUTACCESSCTRL.因此，您需要以 Nenad 身份登录(ID 为SECADM) 并授予(为了安全起见)DBADM、SECADM、DATAACCESS、ACCESSCTRL 到用户 db2admin.现在该 ID 应该具有您的身份正在寻找.
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.
我不建议任何 ID 都具有这些权限.如果你只需要访问表的读/写权限，然后单独 DATAACCESS 应该这样做.
I don't recommend those privileges for just any ID. If all you need is
access to read/write to tables then DATAACCESS alone should do it.
这篇关于IBM Data Studio 无法浏览 SAMPLE (DB2 Express-C) 上的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
