db2

[uniquejava]

If you choose to use DB2, assume the database name is cms, the user and schema both are db2inst1, type the following command to setup the database:

su db2inst1 #this line is only required on ubuntu
db2start
db2 "create database cms USING CODESET UTF-8 TERRITORY CN pagesize 32 K"
db2 connect to cms
db2 -t -f db2.sql
db2 -t -f data.sql

Sample SQL:

CREATE TABLE tbl_user (
  id  INTEGER NOT NULL PRIMARY KEY,
  username VARCHAR(20),
  password  VARCHAR(50)
);

CREATE TABLE msg (
  id          INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  content     VARCHAR(300)                             NOT NULL,
  name        VARCHAR(50)                              NOT NULL,
  phone       VARCHAR(50),
  email       VARCHAR(50),
  qq          VARCHAR(16),
  ip          VARCHAR(20),
  status      SMALLINT DEFAULT 0,
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP      NOT NULL
);

db2 connect reset和terminate的区别

The connection in DB2 are established by db2 connect, and that creates a Back-end process that is the agent that communicates with DB2. When you perform a db2 connect reset, the connection is dropped, but the back-end process (db2bp) still exists. You can also execute db2 terminate and that will drop the connection and terminates the back-end process.

see https://stackoverflow.com/questions/25657221/disconnect-from-the-database-not-using-the-syntax-connect-reset

[uniquejava]

db2 ssl

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/t0053518.html

db2常用命令

创建数据库：db2 create database 数据库名
查看数据库目录：db2 list database directory
连接到数据库：db2 connect to 数据库名 user xxxxx using passss
本地连数据库: db2 connect to 数据库名
断开数据库连接：db2 connect reset
断开同时关闭后台进程: db2 terminate
创建表：db2 “create table 表名 (id int,name varchar(30))”
查看表目录：db2 list tables
查看表结构信息：db2 describe table 表名
查看当前连接情况：db2 connect
查看当前连接的用户：db2 values user
查看当前实例：db2 get instance
关闭实例：db2stop    注明：如果数据库正在运行，必须强制关闭
强制关闭实例：db2stop force 
启动实例：db2start 
获取数据库的配置管理信息：db2 get dbm cfg
分页查看数据库的配置信息：db2 get dbm cfg | more
查看某一个数据库的配置信息：db2 get db cfg for 数据库名
分页查看某一个数据库的配置信息：db2 get db cfg for 数据库名 | more
查看数据库的表空间：db2 list tablespaces
查看表空的详细信息：db2 list tablespaces show detail
创建表空间：db2 create tablespace 表空间名字 managed by system using  /tablespace

远程连接数据库：
编目节点：db2 catalog tcpip  node 节点名 remote 对方 ip地址  server 50000
查看节点目录：db2 list node directory
编目数据库：db2 catalog database 数据库名 as 数据库别名 at node 节点名 
查看数据库目录状态：db2 list db directory
连接远程数据库：db2 connect to 数据库名 as 数据库别名 user  用户名 using  密码
反编目数据库：db2 uncatalog database 数据库名
反编目节点：db2 uncatalog node 节点名

删除表：db2 drop table 表名
删除数据库：db2 drop database 数据库名
删除表空间：db2 drop tablespace 表空间名
退出数据库：quit 

其它操作:
创建schema: create schema <schema_name> authorization <schmea-owner-name>
设置schema: set current schema SCHEMA名
创建用户 sudo useradd -u uid -g gid(dasadm1) -m -d /home/username username
修改密码 sudo passwd username

/etc/passwd格式

https://www.cyberciti.biz/faq/understanding-etcpasswd-file-format/

1. Username: It is used when user logs in. It should be between 1 and 32 characters in length.
2. Password: An x character indicates that encrypted password is stored in /etc/shadow file. Please note that you need to use the passwd command to computes the hash of a password typed at the CLI or to store/update the hash of the password in /etc/shadow file.
3. User ID (UID): Each user must be assigned a user ID (UID). UID 0 (zero) is reserved for root and UIDs 1-99 are reserved for other predefined accounts. Further UID 100-999 are reserved by system for administrative and system accounts/groups.
4. Group ID (GID): The primary group ID (stored in /etc/group file)
5. User ID Info: The comment field. It allow you to add extra information about the users such as user’s full name, phone number etc. This field use by finger command.
6. Home directory: The absolute path to the directory the user will be in when they log in. If this directory does not exists then users directory becomes /
7. Command/shell: The absolute path of a command or shell (/bin/bash). Typically, this is a shell. Please note that it does not have to be a shell.

以下转自: http://blog.163.com/myrudy@126/blog/static/171851536201122231932500/

etc/passwd 存放账户信息：

root:x:0:0:root:/root:/bin/bash
jianing:x:1011:100::/home/jianing:/bin/bash
username:password:uid:gid:allname:homedir:shell

用户名:密码:用户ID:组ID:用户全名:主目录:登录shell 密码：存在/etc/shadow中，存入后者的就用x表示,如果是“!”说明此用户不能用密码登录 UID：就是用户识别码（ID），当UID为0时说明其账号是管理员身份，1-499是保留给系统使用的主要是一些系统服务,500-65535是给一般用户的。 GID：与/etc/group文件有关，就是用户初始化组的ID。 用户全名：用户全名 家目录：就是该用户的主目录 Shell：SHELL脚本

/etc/shadow 存放用户密码

root:$2a$32$3lnW8AXwIZy.Ibh0T2k3huMil/ZGEOQwe3EvJJzfnIcnPzxTamjke:14916::::::
jianing:$2a$10$AmxqDaxMITQoEr4HGcM/zO6JzJWMVcHpxmXdWKVOX5Tb/WWQ/m8wK:14853:0:99999:7:::
aj:!:15006:0:99999:7:::
username:password:last_change:min_change:max_change:warm:failed_expire:expiration:reserved

账号名称:密码:最近更改密码的日期(from 1974-1-1):密码不可更改的天数:密码需要重新更改的天数:密码更改期限前的警告日期:密码过期的宽限时间:账号失效时间:保留

/etc/group 用户组信息

root:x:0:
test:!:1005:u01,u02,u03,cvsroot
groupname:password:gid:members

组名：密码：组ID：成员

[uniquejava]

Common Table Expression(CTE)

https://my.oschina.net/uniquejava/blog/317192

https://www.ibm.com/developerworks/ibmi/library/i-db2connectby/index.html

行列转换

http://www.cnblogs.com/NothingIsImpossible/archive/2010/10/13/1850250.html

单列变字符串

mysql是用的group_concat(..)

DB2 v9.7及以后可以用LISTAGG函数. https://stackoverflow.com/questions/3728010/create-a-delimitted-string-from-a-query-in-db2

with UR

本部分转自: http://blog.sina.com.cn/s/blog_7f311ef50101kkto.html

这几天查询DB2数据库，老遇到select * from XXX with ur, 好奇ur是什么作用，现在记录一下。

DB2中，共有四种隔离级：RS,RR,CS,UR，DB2提供了这4种不同的保护级别来隔离数据。隔离级是影响加锁策略的重要环节，它直接影响加锁的范围及锁的持续时间。两个应用程序即使执行的相同的操作，也可能由于选择的隔离级的不同而造成加锁的结果不同。

1、ur(Uncommited Read) 就是俗称“脏读“，在没有提交数据的时候能够读到更新的数据；是最低的隔离级别，并且提供最高的并行性。

2、cs(Cursor Stability) 在一个事务中进行查询时，允许读取提交前的数据，数据提交后，当前查询就可以读取到数据，update数据的时候并不锁表，在这一隔离级别中，游标的“当前”行是锁定的。如果该行只是被读，锁定会一直持续到一个新行被访问或者该工作单元终止。如果该行被修改，锁定会一直持续到该工作单元终止。

3、rs(Read Stability)读稳定性，在一个事务中进行查询时，不允许读取其他事务update的数据，允许读取到其他事务提交的新增数据，使用读稳定性，在同一个工作单元中的一个程序进程所检索的全部行都会被锁定。对于一个给定的游标，它要锁定所有与结果集匹配的行，例如，如果你有一个含1000行的表并且查询返回10行，那么只有那10行会被锁定。读稳定性使用中等级别的锁定。

4、rr(Repeatable Read)可重新性，可 重复读是最高的隔离级别，提供了最大程度的锁定和最少的并行。产生结果集的所有行都会被锁定，也就是说，即使不必出现在最终结果集中的行也会被锁定。在此 该工作单元结束前，任何其它程序都不能修改，删除或插入一个会影响结果集的行。重复读确保程序在一个工作单元中多次进行的同一项查询都返回结果。在一个事务中进行查询时，不允许任何对这个查询表的数据修改。

和ANSI isolation levels 对照

ANSI isolation level	DB2 isolation level	Dirty reads	Non-repeatable reads	Phantom reads
Serializable	RR (repeatable read)	No	No	No
Repeatable read	RS (read stability)	No	No	Yes
Read committed	CS (cursor stability)	No	Yes	Yes
Read uncommitted	UR (uncommitted read)	Yes	Yes	Yes

见Determine the effective isolation level in DB2 for Linux, UNIX, and Windows

在Liberty server.xml 中的设置

dashDB必须指定isolation level为TRANSACTION_READ_COMMITTED, 不然会报42858的错误, 见:Why does Bluemix dashDB operation throws a SqlSyntaxErrorException with SQLCODE=-1667?

修改server.xml的datasource如下:

    <!-- Data source -->
    <jdbcDriver id="DB2JCC" libraryRef="DB2JCCLib"/>
    <library filesetRef="DB2JCCFileset" id="DB2JCCLib"/>
    <fileset dir="${shared.resource.dir}/db2jars" id="DB2JCCFileset" includes="db2jcc4.jar,db2jcc_license_cu.jar"/>
    <dataSource id="jdbc/xxx" jdbcDriverRef="DB2JCC" jndiName="jdbc/xxx" transactional="true" isolationLevel="TRANSACTION_READ_COMMITTED">
        <connectionManager maxIdleTime="18m" maxPoolSize="20"/>
        <properties.db2.jcc databaseName="BLUDB" driverType="4" password="xxxxxx" portNumber="50000" serverName="1.2.3.4" user="dash5908" />
    </dataSource>

另一种解决方法是在dashDB中建表的时候指定参数ORGANIZE BY ROW.

[uniquejava]

db2 生成UUID

在db2中调用java代码: https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/generating_universally_unique_identifiers_uuid63?lang=en

DB2 provides several means to generate unique identifiers depending on ones need. For example:

1. IDENTITY column

That's a column of a table which is generated automatically, typically in an ever increasing fashion.

CREATE TABLE emp(
  empid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, 
  name VARCHAR(20), 
  salary DECIMAL(10, 2)
);
INSERT INTO emp VALUES(DEFAULT, 'Jones', 20000);
VALUES IDENTITY_VAL_LOCAL();
1
---------------------------------
                               1.

  1 record(s) selected.

An identity column is typically unique within the table unless you allow it to cycle, you reset it or allow overriding of the generation by LOAD or INSERT.

2. SEQUENCE object

A sequence is like an identity column, but without being attached to a table. Typically a sequence generates unique numbers within the database unless you allow it to cycle or reset it.

CREATE SEQUENCE seq1 AS INTEGER;
VALUES NEXT VALUE FOR seq1;
1
-----------
          1

  1 record(s) selected.

3. GENERATE_UNIQUE()

A function that generates a unique CHAR(13) FOR BIT DATA (binary) string based on the current time and information about nodes in a DB2 cluster. The result is unique across the database as long as the system clock is not reset.

VALUES GENERATE_UNIQUE();
1
-----------------------------
x'20101110052559155343000000'

1 record(s) selected.

None of these methods provide values which are unique across multiple DB2 databases or even across machines and geographies.

To produce universally unique identifiers (UUID) various well defined algorithms are available which use a combination of time or random number generation and machine unique information such as MAC addresses to produce binary strings that have a very, very low likelihood of colliding.

DB2 does not natively support UUID, but Java does. So here I provide a sample implementation of UUID based on Java.

Let's get started:

Ensure javac the java compiler is on your path. It's normally on sqllib\java\jdk\bin

Create a java file named UUIDUDF.java

import java.util.UUID;  // for UUID class

public class UUIDUDF {
    public static String randomUUID() {
        return UUID.randomUUID().toString();
    }
}

compile the program from your shell

javac UUIDUDF.java

Produce a jar file

 jar cvf UUIDUDF.jar UUIDUDF.class

Time to fire up DB2

db2 -t

Connect to the database

connect to <dbname>

Register the jar file with the database

call sqlj.install_jar('file:.\UUIDUDF.jar', 'UUIDUDFJAR')

CYPER笔记: 这里的路径windows如下:file:///c:/path/to/xx.jar, linux如下file:/home/db2inst1/path/to/xx.jar

You may want to move the JAR file to a safe place and adjust the path above accordingly. in a multi-member environment, make sure the file is accessible from all members.

Create the function:

CREATE OR REPLACE FUNCTION RANDOMUUID()
          RETURNS VARCHAR(36)
          LANGUAGE JAVA
          PARAMETER STYLE JAVA
          NOT DETERMINISTIC NO EXTERNAL ACTION NO SQL
          EXTERNAL NAME 'UUIDUDFJAR:UUIDUDF.randomUUID' ;

Test the function

VALUES RANDOMUUID();
1
------------------------------------
aa0058ed-bcca-4ead-a22f-b648ccb66787

1 record(s) selected.

What you see is the generally accepted pretty-printed form of UUIDs. db2 provides two functions VARCHAR_BIT_FORMAT() and VARCHAR_FORMAT_BIT() to convert the pretty printed version to a binary string and vice versa.

VALUES VARCHAR_BIT_FORMAT(RANDOMUUID(), 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');
1
-------------------------------------------------------------------------------------
x'57E71F89F093428DB696A9FEB6785853'

1 record(s) selected.

This is a 16 byte long VARCHAR FOR BIT DATA.

To make things nice and tight let's produce another function that gets us the binary string right away as a CHAR(16) FOR BIT DATA. We pick SYS_GUID() as a name to please the Oracle crowd.

CREATE OR REPLACE FUNCTION SYS_GUID()
          RETURNS CHAR(16) FOR BIT DATA
          CONTAINS SQL NOT DETERMINISTIC NO EXTERNAL ACTION
          RETURN VARCHAR_BIT_FORMAT(RANDOMUUID(), 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');

        VALUES SYS_GUID();

        1
        -----------------------------------
        x'A6F0F1FFF93B4CF8A9C4E990774D9B70'

        1 record(s) selected.

Now let's tie this all up with an example usage.

CREATE TABLE license(
key CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY, 
customername VARCHAR(20), 
product VARCHAR(20)
);

CREATE OR REPLACE TRIGGER license_bi BEFORE INSERT ON license
        REFERENCING NEW AS n FOR EACH ROW
        SET n.key = SYS_GUID();

        SELECT key FROM NEW TABLE(INSERT INTO license(customername, product) VALUES('Oracle', 'DB2'));
        KEY
        -----------------------------------
        x'A944FBAA5611459EBB9C7FCEC7F65FDE'

        1 record(s) selected.

That was easy :-)

QA

Q: If this was to be implemented in a data warehouse, will it have implications on the database considering we would be invoking the function thousands of times on a hourly basis ?

A: Considering that there are 3600 seconds in an hour, "thousands of times" per hour doesn't sound like a lot, but to avoid speculation you should plan to conduct some performance tests on with your particular hardware and workload. If the Java-based solution doesn't perform according to your requirements, you can consider implementing it as a C UDF, which should perform somewhat better, with a smaller memory footprint.

linux生成uuid: https://serverfault.com/questions/103359/how-to-create-a-uuid-in-bash

[uniquejava]

db2 授权

见: http://db2commerce.com/2013/03/01/db2-basics-users-authentication-and-authorization/

先查看谁是超管:

select ACCESSCTRLAUTH, SECURITYADMAUTH, granteetype, grantee 
from syscat.dbauth
where 'Y' in (ACCESSCTRLAUTH, SECURITYADMAUTH);

用超管登录后执行下面的命令:

db2 -td;
db2 connect to DBNAME user xxx;
grant dbadm on database to user xxx;
revoke dbadm on database from user xxx;

db2 调优

db2top

db2pd

db2 backup and restore

db2 force applications all
db2 backup database <old_dbname> [online] to /home/db2inst1/dbbackup
db2 restore db <old_dbname> from /home/db2inst1/dbbackup taken at <timestamp> redirect generate script <filename>
vi <filename>

db2 terminate
db2 -tvf <filename>
db2 rollforward db <new_dbname> to end of backup and complete
db2 connect to <new_dbname>

备份

db2 restore db $olddbname from $imagepath taken at $timestamp redirect generate script $scriptname
--to: new db path
--on: if automatic storage is enable
--DBPATH ON: the target database directory
--into: new db alias
--logtarget: temp log path
--newlogpath: new active log path
--container path
--RESTORE DATABASE $olddbname CONTINUE;
db2 terminate;
db2 -tvf $scriptname;
db2 "rollforward db $newdbname to end of logs and stop overflow log path ($tmplogpath) noretrieve"

前两天执行redirect restore，这里分享一下它的过程和遇到的一些困难。

1. 首先从生产环境得到image， online 备份的
2. 传送到测试环境后，先用db2 restore db $dbname from $imagepath taken at $timestamp redirect generate script $scriptname产生出脚本。
3. 修改脚本。里面的信息全是在生产环境中的数据库的信息，表空间信息。这里需要改为测试环境的表空间，container。里面注释了db path，LOGTARGET等信息，如果测试环境没有这个数据库，就要自己设定db path，newlogtarget等信息，如果测试环境原本就有这个数据库，那么不要用DBPATH ON， NEWLOGPATH，需要使用原来（或者重新指定的目录）的值，这时要用LOGTARGET指定log的path，ON后面指定数据库路径 。

4.执行脚本的时候，因为生产环境的表空间特别大，造成在测试环境中原本的表空间container不能容纳那么多的表空间，这时候需要用一些额外的硬盘来容纳剩余的表空间。如果restore出错想重新设置参数，可以db2 restore db $dbname abort，然后再restore

5.restore后需要rollforward， 检查image中是否包含log，db2ckbkp -h 。如果发现INCLUDE LOGS 为 "1 "，那么说明image中有log，这时候可以用db2 restore db $dbname logs from $imagepath logtarget $logpath

6.最后rollforward成功后就可以使用database了。db2 rollforward db $dbname to end of logs and complete

更多: Redirected Database Restore Operations , CSDN: DB2 redirect restore Db2 Roll-Forward Pending SQLSTATE=57019 Solution. How to Restore Database from online backup

[uniquejava]

db2统计分析函数

https://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/

https://chartio.com/resources/tutorials/how-to-use-row_number-in-db2/

db2 export data

db2 "export to /home/db2inst1/output_file.del of del 
select * from schema_name.table_name where condition"

db2 import data

格式: LOAD FROM <FNM> OF DEL INSERT INTO <TBL_NM> NONRECOVERABLE 实例 import from "/home/db2inst1/t_xxx.csv" of del modified by COLDEL,TIMESTAMPFORMAT="YYYY-MM-DD HH:MM:SS.UUU" SKIPCOUNT 1 insert into t_xxxx NONRECOVERABLE

NONRECOVERABLE的作用见: https://bytes.com/topic/db2/answers/184498-insert-bulk-data-into-table-temporarily-disable-log

未加NONRECOVERABLE导入的过程出现错误:

SQL3306N  An SQL error "-964" occurred while inserting a row into the table.

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

解决办法:

db2 update db cfg for dbname using logfilsiz 81920 logprimary 15 logsecond 10
db2 list applications for DB sample
db2stop force
db2start

见: Fixing “The transaction log for the database is full” problem in DB2

实例

export to /home/db2inst1/20171101/TABLE_1.del of del lobs to /home/db2inst1/20171101 lobfile lob_doc modified by lobsinfile messages msg.out select * from TABLE_1  
import from /home/esadmin/aaa/TABLE_1.del of del lobs from /home/esadmin/aaa modified by lobsinfile replace into TABLE_1  

如何列出全部的表名

禁用logging的另一种办法是: (未试验)

db2 list tables for schema xxxx | grep "^T_" |awk '{print "ALTER TABLE "$1" ACTIVATE NOT LOGGED INITIALLY"}'

如上, 学会了怎么批量生成操作TABLE的命令, 见: https://stackoverflow.com/questions/16913284/turning-off-logging-in-ibm-db2-9-7-luw

关于length的计算

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/char/src/tpc/db2z_specifylengthcalcutation.html

select length('你好',CODEUNITS16) from sysibm.sysdummy1

[uniquejava]

db2 监控

db2 get snapshot for locks on DB_NAME | less
db2 get snapshot for application agentid 48878

查询表空间利用率情况

SELECT
    SUBSTR(tbsp_name,1,20) AS tablespace_name,
    SUBSTR(tbsp_content_type,1,10) AS tablespace_type,
    SUM(tbsp_total_size_kb)/1024 AS total_mb,
    SUM(tbsp_used_size_kb)/1024 AS used_mb,
    SUM(tbsp_free_size_kb)/1024 AS free_mb,
    tbsp_page_size AS page_size,
    tbsp_utilization_percent AS percent
FROM
    sysibmadm.tbsp_utilization
GROUP BY
    tbsp_name,
    tbsp_content_type,
    tbsp_page_size,
    tbsp_utilization_percent
ORDER BY
    tbsp_utilization_percent

查询连接数

select db_name, appl_name, authid, appl_id, appl_status,client_pid,
client_nname, client_platform from sysibmadm.applications order by CLIENT_NNAME

查询db2锁信息

select DB_NAME,AGENT_ID,APPL_NAME,AUTHID,TBSP_NAME,TABSCHEMA,TABNAME,
LOCK_OBJECT_TYPE,LOCK_NAME,LOCK_MODE,LOCK_STATUS from sysibmadm.LOCKS_HELD

查询db2缓冲池信息

select db_name, bp_name,data_hit_ratio_percent,index_hit_ratio_percent,
total_hit_ratio_percent from sysibmadm.bp_hitratio

见: https://jingyan.baidu.com/article/d7130635c6a76213fdf4753f.html

UOW Waiting means that the application is currently not running any SQL. However, it does not mean that the application has finished its transaction. May be, it finished its transaction (by committing or rolling back). Or may be, it's still in the middle of a transaction and is just waiting for user/appl to enter another SQL. So, an appl in UOW Waiting status can hold locks/active log space. You can use db2pd to see if it's holding locks, what it was executing last (in db2pd output, looks for Locks/Applications/Transactions sections)

Most java application servers have connection pooling software which will permanently maintain a certain number of connections per app server based on the connection pooling configuration. This is usually not a problem.

If you have transactions that have not committed or not closed the transaction (a frequent problem with today's untrained programmers), then do a snapshot for locks to determine where the problems are. You will need to turn on the lock and statement monitors.

TODO

http://www.dbforums.com/showthread.php?1645407-Applications-Hanging-around-Is-this-normal

https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0803liyue/ | 1:44:49 PM

[uniquejava]

db2start 报错

很久之前在macOS上装过db2, 然后几百年不用, 然后macOS不知道升级了多少个版本. 然后db2 service还在. 今天用db2start启动报错了

➜  ~ su - db2inst1
Password:
mbp13:~ db2inst1$ db2start
SQL5043N  Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.

然后用客户端连接报08001的错误. Error: Error: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "127.0.0.1". Communication function detecting the error: "selectForConnectTimeout". Protocol specific error code(s): "36", "", "". SQLSTATE=08001

解决办法 先查一下service name db2 get dbm cfg | grep SVCENAME

然后 sudo vi /etc/services

在最后加上 db2c_db2inst1 50000/tcp, 问题解决.

[uniquejava]

db2 docker

镜像地址: https://hub.docker.com/r/ibmcom/db2express-c/

# 装好后开放50000端口
$ sudo firewall-cmd --list-ports
To open these ports run the following commands:
$ sudo firewall-cmd --zone=public –-permanent --add-port=50000/tcp

# docker装好后
$ docker info
WARNING: IPv4 forwarding is disabled. Networking will not work.
#需要解决以上警告, 不然外部将无法访问docker版的db2.
$ vim /etc/sysctl.conf
net.ipv4.ip_forward=1
$ systemctl restart network
$ sysctl net.ipv4.ip_forward
#交互式方式启动以便管理数据(password是启动时动态指定的!)
$ docker run -it --name db2express -p 50000:50000 -e DB2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept  ibmcom/db2express-c:latest bash
$  db2 sampl
$ db2 create database <db>
# 后台启动
$ docker run --name db2express -d -p 50000:50000 -e DB2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept  ibmcom/db2express-c:latest db2start
# 测试
$ docker ps
$ docker exec -it a62c1583a756 bash -c 'su - db2inst1'

How to install IBM Db2 Developer Edition on Centos 7 using Docker

db2 docker macOS

$ docker pull ibmcom/db2express-c
$ docker run --name db2express -d -p 50000:50000 -e DB2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept  ibmcom/db2express-c:latest db2start
$ su - db2inst1
$ db2start
$ db2 create database CMS

db2 docker 2020

镜像地址变了： https://hub.docker.com/r/ibmcom/db2

$ docker run -itd --name db2inst1 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=db2inst1-pwd ibmcom/db2

$ docker cp dashdb.sql db2inst1:/database/config/db2inst1/

$ docker exec -it db2inst1 bash

> su - db2inst1
> db2 create db dashdb
> db2 connect to dashdb
> db2 -tvf dashdb.sql

[uniquejava]

db2 timezone

如何知道db2 server 用的timezone是多少！

Method 1: Using db2diag.log

In db2diag.log, the timestamp indicates the timezone:

docker exec -it my_awesome_container_name bash
su - db2inst1
find . -name db2diag.log
=> /database/config/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log

2006-05-30-14.11.02.407777+600 I4153330G543 LEVEL: Severe

The +600 value represents the timezone in minutes, +600 / 60 = +10 hours.

Method 2: Using CURRENT TIMEZONE special register

db2 "select current timestamp as local,              
current timezone as timezone,             
 current timestamp - current timezone as UTC from sysibm.sysdummy1"

You get the following output:

LOCAL                      TIMEZONE UTC
-------------------------- -------- --------------------------
2006-05-30-16.32.26.945121 100000.  2006-05-30-06.32.26.945121

  1 record(s) selected.

The 10000 value is a decimal number in which the first two digits are the number of hours, the next two digits are the number of minutes, and the last two digits are the number of seconds.

来自: Determining the timezone used by the DB2 server

已知为utc时间如何转成local time

AS-IS： 2020-04-26 03:21:37 => TO-BE： 2020-04-26 11:21:37

在现有的时间戳后面拼上 UTC。 即 new Date(current_time + ' UTC') 仅针对chrome有效！， FF不认识

然后🈶了这个Date对象之后就好办了

 formatDate(utcDate) {
      if (!utcDate) return '';

      // let date = new Date(`${utcDate} UTC`);
      // return date.normalize();
      return utcDate ? new Date(utcDate).normalize() : '';
    },

给原生的Date对象定义一个 normalize方法

Object.defineProperty(Date.prototype, 'normalize', {
  value: function() {
    function pad2(n) {
      // always returns a string
      return (n < 10 ? '0' : '') + n;
    }
    let yyyy = this.getFullYear();
    let MM = pad2(this.getMonth() + 1);
    let dd = pad2(this.getDate());
    let HH = pad2(this.getHours());
    let mm = pad2(this.getMinutes());
    let ss = pad2(this.getSeconds());
    return `${yyyy}-${MM}-${dd} ${HH}:${mm}:${ss}`;
  },
});

参考：

1. Convert UTC date time to local date time

[uniquejava]

ISO 8601

试了半天

select replace(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'),' ','T')||'Z' from sysibm.sysdummy1

怎么要这么麻烦。