Daily Archives: 2012 年 04 月 12 日

DB2中schema管理

Posted on by

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内部没有用户的概念,连接用户必须是操作系统用户.

Posted in DB2 |

DB2中产生唯一值三种方式

Posted on by

一.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.orasos.com'),(generate_unique(),'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_gu"

CUSTNO                        CUSTNAME        
----------------------------- ----------------
x'20120403054630527862000000' www.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.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.orasos.com
                  11 XIFENFEI        

  2 record(s) selected.

这个功能和sql server/mysql的自增长列很相似，给出了两种方式选择，使得比它们更加灵活

Posted in DB2 |

ORACLE 十进制与二进制互转函数

Posted on by

十进制转换二进制

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) 
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
  V_N1  NUMBER;
  V_N2  NUMBER;
BEGIN
V_N1 := V_NUM;
    LOOP
      V_N2  := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1 / 2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;
--返回二进制长度
 SELECT lpad(V_RTN,8,0) 
    INTO   V_RTN
    FROM dual;
return V_RTN;
end;

SQL> select NUMBER_TO_BIT(208) from dual;

NUMBER_TO_BIT(208)
-----------------------------
11010000

二进制转换十进制

CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
  V_SQL    VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
  V_RETURN NUMBER;
BEGIN
  IF LENGTH(P_BIN) >= 256 THEN
    RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
  END IF;
  IF LTRIM(P_BIN, '01') IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
  END IF;
  FOR I IN 1 .. LENGTH(P_BIN) LOOP
    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
  END LOOP;
  V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
  EXECUTE IMMEDIATE V_SQL
    INTO V_RETURN;
  RETURN V_RETURN;
END;

SQL> SELECT BIT_TO_NUMBER('11010000') FROM DUAL;

BIT_TO_NUMBER('11010000')
-------------------------
                      208

ORACLE ROWID 分析

Posted on by

ROWID知识补充
Rowid改为：OOOOOOFFFBBBBBBRRR，占用10个字节（32bit data_object_id +10bit rfile# +22bit block +16bit）。其中，O是对象ID，F是文件ID，B是块ID，R是行ID。

Rowid对应值对应10十进制值

A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

模拟环境

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t_xff (id number,name varchar2(100));

Table created.

SQL> insert into t_xff values(1,'www.orasos.com');

1 row created.

SQL> commit;

Commit complete.

SQL>  alter table t_xff move;

Table altered.

SQL> select rowid,a.* from t_xff a;

ROWID                      ID NAME
------------------ ---------- ------------------------------
AADye6AAEAAAtCcAAA          1 www.orasos.com

相关值计算

Data Object number = AADye6
File               = AAE
Block              = AAAtCc
ROW                = AAA

DATA_OBJECT_ID
AADye6
58*64^0+30*64^1+50*64^2+3*64^3+0*64^4+0*64^5
58+1920+204800+786432+0+0=993210

RFILE#
AAE
4*64^0+0*64^1+0*64^2
4+0+0=4

BLOCK
AAAtCc
28*64^0+2*64^1+45*64^2+0*64^3+0*64^4+0*64^5
28+128+184320+0+0+0=184476

验证结果

SQL> select object_id,data_object_id from dba_objects where object_name='T_XFF';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    993209         993210

SQL> select dbms_rowid.rowid_object('AADye6AAEAAAtCcAAA') data_object_id#,
  2         dbms_rowid.rowid_relative_fno('AADye6AAEAAAtCcAAA') rfile#,
  3         dbms_rowid.rowid_block_number('AADye6AAEAAAtCcAAA') block#,
  4     dbms_rowid.rowid_row_number('AADye6AAEAAAtCcAAA') row# from dual;  

DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
         993210          4     184476          0

dump方式分析

SQL> select rowid,dump(rowid) from t_xff;

ROWID              DUMP(ROWID)
------------------ ------------------------------------------
AADye6AAEAAAtCcAAA Typ=69 Len=10: 0,15,39,186,1,2,208,156,0,0

DATA_OBJECT_ID
0,15,39,186
15*256*256+39*256+186=993210

RFILE#
1,2(取前10位)
000000001 00 =4

BLOCK
2,208,156(取后22位)
000010 11010000 10011100
2^17+208*256+156=184476

DB2远程登录配置

Posted on by

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
Posted in DB2 |