-
加我微信
加我QQ
标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,688)
- DB2 (22)
- MySQL (74)
- Oracle (1,550)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (566)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (80)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- Oracle read only用户—23ai新特性:只读用户
- 迁移awr快照数据到自定义表空间
- .hmallox加密mariadb/mysql数据库恢复
- 2025年首个故障恢复—ORA-600 kcbzib_kcrsds_1
- 第一例Oracle 21c恢复咨询
- ORA-15411: Failure groups in disk group DATA have different number of disks.
- 断电引起的ORA-08102: 未找到索引关键字, 对象号 39故障处理
- ORA-00227: corrupt block detected in control file
- 手工删除19c rac
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障（文件offline）
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁（RUs+OJVM）
分类目录归档：DB2
DB2中schema管理
0.DB2版本信息
[db2inst1@xifenfei ~]$ db2level DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09050" with level identifier "03010107". Informational tokens are "DB2 v9.5.0.0", "s071001", "LINUXIA3295", and Fix Pack "0". Product is installed at "/opt/db2/V9.5".
1.显示syscat.schemata视图结构
[db2inst1@xifenfei ~]$ db2 "describe table syscat.schemata"
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMANAME SYSIBM VARCHAR 128 0 No
OWNER SYSIBM VARCHAR 128 0 No
OWNERTYPE SYSIBM CHARACTER 1 0 No
DEFINER SYSIBM VARCHAR 128 0 No
DEFINERTYPE SYSIBM CHARACTER 1 0 No
CREATE_TIME SYSIBM TIMESTAMP 10 0 No
REMARKS SYSIBM VARCHAR 254 0 Yes
2.查询当前存在schema
[db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata" SCHEMANAME OWNER CREATE_TIME ------------ ------------ ---------------------------- SYSIBM SYSIBM 2012-03-25-15.07.07.196612 SYSCAT SYSIBM 2012-03-25-15.07.07.196612 SYSFUN SYSIBM 2012-03-25-15.07.07.196612 SYSSTAT SYSIBM 2012-03-25-15.07.07.196612 SYSPROC SYSIBM 2012-03-25-15.07.07.196612 SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612 SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612 SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612 NULLID SYSIBM 2012-03-25-15.07.23.011671 SQLJ SYSIBM 2012-03-25-15.07.54.575637 SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744 11 record(s) selected.
3.显示创建schema
[db2inst1@xifenfei ~]$ db2 "create schema xifenfei" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata" SCHEMANAME OWNER CREATE_TIME ------------ ------------ ---------------------------- SYSIBM SYSIBM 2012-03-25-15.07.07.196612 SYSCAT SYSIBM 2012-03-25-15.07.07.196612 SYSFUN SYSIBM 2012-03-25-15.07.07.196612 SYSSTAT SYSIBM 2012-03-25-15.07.07.196612 SYSPROC SYSIBM 2012-03-25-15.07.07.196612 SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612 SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612 SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612 NULLID SYSIBM 2012-03-25-15.07.23.011671 SQLJ SYSIBM 2012-03-25-15.07.54.575637 SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744 XIFENFEI DB2INST1 2012-04-03-12.01.12.724932 12 record(s) selected.
4.隐式创建schema
[db2inst1@xifenfei ~]$ db2 "create table xff.t_xifenfei(id int,name varchar(100))" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata" SCHEMANAME OWNER CREATE_TIME ------------ ------------ ---------------------------- SYSIBM SYSIBM 2012-03-25-15.07.07.196612 SYSCAT SYSIBM 2012-03-25-15.07.07.196612 SYSFUN SYSIBM 2012-03-25-15.07.07.196612 SYSSTAT SYSIBM 2012-03-25-15.07.07.196612 SYSPROC SYSIBM 2012-03-25-15.07.07.196612 SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612 SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612 SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612 NULLID SYSIBM 2012-03-25-15.07.23.011671 SQLJ SYSIBM 2012-03-25-15.07.54.575637 SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744 XIFENFEI DB2INST1 2012-04-03-12.01.12.724932 XFF SYSIBM 2012-04-03-12.03.12.581260 13 record(s) selected.
隐式创建schema的所属用户会是SYSIBM(存放系统数据字典表SCHEMA)
5.删除schema
[db2inst1@xifenfei ~]$ db2 "drop schema xff" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "drop schema xff". Expected tokens may include: "RESTRICT". SQLSTATE=42601 [db2inst1@xifenfei ~]$ db2 drop schema xff restrict DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA" cannot be processed because there is an object "XFF.T_XIFENFEI", of type "TABLE", which depends on it. SQLSTATE=42893 [db2inst1@xifenfei ~]$ db2 "drop table xff.t_xifenfei" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 drop schema xff restrict DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata" SCHEMANAME OWNER CREATE_TIME ------------ ------------ ---------------------------- SYSIBM SYSIBM 2012-03-25-15.07.07.196612 SYSCAT SYSIBM 2012-03-25-15.07.07.196612 SYSFUN SYSIBM 2012-03-25-15.07.07.196612 SYSSTAT SYSIBM 2012-03-25-15.07.07.196612 SYSPROC SYSIBM 2012-03-25-15.07.07.196612 SYSIBMADM SYSIBM 2012-03-25-15.07.07.196612 SYSIBMINTERNAL SYSIBM 2012-03-25-15.07.07.196612 SYSIBMTS SYSIBM 2012-03-25-15.07.07.196612 NULLID SYSIBM 2012-03-25-15.07.23.011671 SQLJ SYSIBM 2012-03-25-15.07.54.575637 SYSTOOLS DB2INST1 2012-03-25-15.09.01.964744 XIFENFEI DB2INST1 2012-04-03-12.01.12.724932 12 record(s) selected.
删除schema需要使用restrict关键字,而且该schema中无对象存在.
在DB2中的schema的概念和ORACLE中的概念有着本质的区别：在ORACLE中schema和用户是同一个;在DB2中schema不一定是用户,因为db2内部没有用户的概念,连接用户必须是操作系统用户.
发表在 DB2
评论关闭
DB2中产生唯一值三种方式
一.Genearate_unique函数
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_gu(custno char(13) for bit data, > custname varchar(16))" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_gu values > (generate_unique(),'www.xifenfei.com'),(generate_unique(),'xifenfei')" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_gu" CUSTNO CUSTNAME ----------------------------- ---------------- x'20120403054630527862000000' www.xifenfei.com x'20120403054630527940000000' xifenfei 2 record(s) selected.
generate_unique是按照国际标准时间(UTC)生成的当前时间戳加上当前数据库分区号，包含13个字节的字符串。如果调整了系统时间可能会出现重复
二.序列(Sequence)
[db2inst1@xifenfei ~]$ db2 "create sequence xifenfei.xff_seq
> as bigint
> start with 1
> increment by 1
> no maxvalue
> cycle
> cache 10"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_seq(xff_id bigint,
> custname varchar(16))"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_seq values(nextval
> for xifenfei.xff_seq,'www.xifenfei.com'), (nextval for xifenfei.xff_seq,'xifenfei')"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_seq"
XFF_ID CUSTNAME
-------------------- ----------------
1 www.xifenfei.com
2 xifenfei
2 record(s) selected.
和ORACLE的sequence基本相同，只是在oracle中是sequence.nextval这里改为了nextnvl for seqence
三.自增字段
--1.generated always as identity方式(不能人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_a(xff_id bigint not null generated always
> as identity(start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(custname) values('www.xifenfei.com')"
DB20000I The SQL command completed successfully.
--指定值插入失败
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(xff_id,custname) values(11,'XIFENFEI')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0798N A value cannot be specified for column "XFF_ID" which is defined as
GENERATED ALWAYS. SQLSTATE=428C9
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_a"
XFF_ID CUSTNAME
-------------------- ----------------
10 www.xifenfei.com
1 record(s) selected.
--1.generated by default as identity方式(可以人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_d(xff_id bigint not null generated by default
> as identity (start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(custname) values('www.xifenfei.com')"
DB20000I The SQL command completed successfully.
--指定值插入成功
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(xff_id,custname) values(11,'XIFENFEI')"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_d"
XFF_ID CUSTNAME
-------------------- ----------------
10 www.xifenfei.com
11 XIFENFEI
2 record(s) selected.
这个功能和sql server/mysql的自增长列很相似，给出了两种方式选择，使得比它们更加灵活
发表在 DB2
评论关闭
DB2远程登录配置
1.服务器端操作
[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:55554 0.0.0.0:* LISTEN 8072/db2sysc 0 [db2inst2@xifenfei ~]$ db2 list db directory System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = XIFENFEI Database name = XIFENFEI Local database directory = /home/db2inst2/xifenfei Database release level = c.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = --配置监听协议 [db2inst2@xifenfei ~]$ db2set db2comm=tcpip --配置实例端口 [db2inst2@xifenfei ~]$ db2 update dbm cfg using svcename 5000 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. --重启生效 [db2inst2@xifenfei ~]$ db2stop 04/02/2012 12:33:36 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst2@xifenfei ~]$ db2start 04/02/2012 12:33:41 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:50000 0.0.0.0:* LISTEN 8462/db2sysc 0
2.客户端配置
C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000 DB21061E 未初始化命令行环境。 --DB21061E错误解决 C:\Windows\system32>db2cmd --编目节点 C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000 DB20000I CATALOG TCPIP NODE命令成功完成。 DB21056W 直到刷新目录高速缓存之后，目录更改才生效。 --编目数据库 C:\Windows\system32>db2 catalog db xifenfei as xifenfei at node vm_xff DB20000I CATALOG DATABASE命令成功完成。 DB21056W 直到刷新目录高速缓存之后，目录更改才生效。 C:\Windows\system32> db2 list node directory 节点目录 目录中的条目数 = 1 节点 1 条目： 节点名 = VM_XFF 注释 = 目录条目类型 = LOCAL 协议 = TCPIP 主机名 = 192.168.1.90 服务名称 = 50000 --terminate使操作生效 C:\Windows\system32>db2 terminate DB20000I TERMINATE命令成功完成。
3.验证登陆
[db2inst2@xifenfei sqldbdir]$ db2 connect to xifenfei user db2inst2 using xifenfei
Database Connection Information
Database server = DB2/LINUX 9.5.0
SQL authorization ID = DB2INST1
Local database alias = XIFENFEI
[db2inst1@xifenfei ~]$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST2 db2bp.exe 45 192.168.1.1.38849.120411160214 XIFENFEI 1
DB2INST2 db2bp 50 *LOCAL.db2inst1.120402072313 XIFENFEI 1
发表在 DB2
评论关闭