How to execute a MySQL command from a shell script?

反正常遇到，不如寫下做個紀錄，以後直接複製貼上。

整個重點在於-p和密碼中間有沒有空格。當有空格時，mysql是使用互動式，要你填入密碼；如果沒有空格，mysql就直接使用-p後面的字串來登入。
指令：

$ mysql -h Server_name -u your_account -pPassword database_name < file.sql

若-p和密碼中間有空格，會出現下列情況：

$ mysql -h Server_name -u your_account -p Password database_name < file.sql
Enter password: 
ERROR 1049 (42000): Unknown database 'Password'

閱讀全文...

Characterset problem when inserting into mysql database from java

哈，又是編碼問題。本來用 WebSphere 和 DB2 的環境，資料新增至資料庫中文都沒問題，怎麼一換到 tomcat 和 MySQL 這組合就中文新增到資料庫時就變成了問號。

最後試驗的結果，需在 connection string 加上 useUnicode=true&characterEncoding=utf8 這段設定，這樣中文新增到資料庫就會正常了，不會再是?字元。

JDBC use like

String conString = "jdbc:mysql://host/database?useUnicode=true&characterEncoding=utf8";

DataSource resource url like

url="jdbc:mysql://host/database?useUnicode=true&characterEncoding=utf8"

閱讀全文...

[DB2]How to drop index in db2

use command:
drop index index_name
閱讀全文...

[DB2] How to adding columns to an existing table

To add columns to an existing table using the command line, enter:
ALTER TABLE table_name ADD column_name data_type null_attribute

EX:
ALTER TABLE MyTable
ADD COLUMN1 VARCHAR(5) NOT NULL WITH DEFAULT
ADD COLUMN2 CHAR(3)
閱讀全文...

[DB2] How to change the primary key for already existing table?

1. drop the existing primary key, use
ALTER TABLE Table_Name DROP PRIMARY KEY;

2. add new primary key, use
ALTER TABLE Table_Name ADD PRIMARY KEY (Column1, Column2, ...);
閱讀全文...

DB2: How to find your DB2 version

要如何得知你使用的db2版本，每次問，不會被回說去找cd的包裝，就是問不到答案。
本想google一下，卻意外發現手動用command line的方式去連db，上面就有顯示了。
db2 => connect to yourdb Database Connection Information Database server = DB2/LINUX 9.7.6 SQL authorization ID = yourdb Local database alias = yourdb db2 =>
Database Server那行後面 9.7.6 就是db2 version
另也可以透過下面的SQL語法來取得db2 version，但是用此sql語法前，得先連上db，否則會出現錯誤。
db2 => select * from SYSIBM.SYSVERSIONS VERSIONNUMBER VERSION_TIMESTAMP AUTHID VERSIONBUILDLEVEL ------------- -------------------------- -------- ------------------------------ 9070600 2013-02-21-16.21.51.489830 DB2INST1 s120516 1 record(s) selected.
VERSIONNUMBER 即db2 version.

未連上db時，會得到的錯誤訊息
db2 => select * from SYSIBM.SYSVERSIONS SQL1024N A database connection does not exist. SQLSTATE=08003 db2 =>
閱讀全文...

DB2: Get current date with format YYYYMMDD

How to get current date with format YYYYMMSS in DB2??

To get the current date, time, and timestamp using SQL in DB2, reference to use below sql statment
db2 => select current date from sysibm.sysdummy1 1 ---------- 08/29/2013 1 record(s) selected. db2 => select current time from sysibm.sysdummy1 1 -------- 16:59:01 1 record(s) selected. db2 => select current timestamp from sysibm.sysdummy1 1 -------------------------- 2013-08-29-16.37.35.960388 1 record(s) selected.

How to custom date/time formatting?
The easy way to do this is use VARCHAR_FORMATscalar function
db2 => select varchar_format(current timestamp, 'YYYYMMDD') from sysibm.sysdummy1 1 ----------------------------------- 20130829 1 record(s) selected.
閱讀全文...

DB2: Insert into with select, incrementing a column for each new row by one for each insert?

工作上要用到，新增記錄到資料庫時，其中一個欄位要從SEQUENCE取值，所以試了一下SQL，順便記錄一下，免得忘了
新增一筆記錄至XXX table，其中UNIQUEID要從UNIQUE_ID_SEQ SEQUENCE取值
INSERT INTO XXX (UNIQUEID, TYPE, ID) VALUES(NEXT VALUE FOR UNIQUE_ID_SEQ,'M','004123456789001');
閱讀全文...

db2 encrypt/decrypt value for column(表格欄位值加解密)

全球最嚴個資法，台灣說第二，不知道有沒有其他國敢跳出來說第一，所以一堆公司開始了所謂的控管，免不的，資料庫有些敏感性資料也要加密，只好找找資料，試一下db2怎麼對表格中的欄位來進行加解密。

db2 針對不同型態，提供了不同的加解密函式，根據版本不同，有些函式還未提供，或是只供內部使用，所以只把我試出來可以用的列出來。
資料型態，主要針對varchar
使用下列函式：

• encrypt(StringDataToEncrypt, PasswordOrPhrase, PasswordHint)
• decrypt_dhar(EncryptedData, PasswordOrPhrase)
• Set Encryption Password

對資料加密的演算法是一個 RC2 分組密碼(block cipher)，它帶有一個 128 位的密鑰。這個128位的密鑰是通過消息摘要從密碼得來的。加密密碼與DB2認證無關，僅用於資料的加解密。
另外提供一個可選的參數 PasswordHint，這是一個字串，可以幫助用戶記憶用於對 PasswordOrPhrase 提示。

db2 db2 => connect to your_database db2 => create table xxx(cardno varchar(33) for bit data) db2 => set encryption password = 'test1234'; db2 => insert into xxx values(encrypt('1234567890123456')) db2 => select decrypt_char(cardno) from xxx db2 => quit
NOTE:
密碼至少要 6byte
新增表格時欄位長度需設定為原本欄位的最大長度+9bytes，否則在新增欄位值，會出現欄位值太長的錯誤。例如cardno原本最大長度為24，在新增table時，將其設定成33 byte
閱讀全文...

DB2 sequence 簡單語法

剛好遇上要用db2 sequence, 將試過的語法做個備忘。

1. create
CREATE SEQUENCE XX_SEQ START 1 INCREMENT BY 1 MAXVALUE 999999 CYCLE NO CACHE
說明：
START WITH:起始值
INCREMENT BY:每次增加多少
MAXVALUE:設定最大值，不設定就設成 NO MAXVALUE
CYCLE:當到達最大值時，是否從頭開始，不循環設成NO CYCLE
CACHE:一次產生多個值於記憶體中，方便快速取用，如 CACHE 3 ，就會產生3個值於記憶體中，若不使用，則設成NO CACHE

2. alter (重新設定起始值) ALTER SEQUENCE XX_SEQ RESTART WITH 10
3. 使用 next value for seq_name 取值
SELECT NEXT VALUE FOR XX_SEQ FROM sysibm.sysdummy1
4. drop(刪除)
DROP SEQUENCE XX_SEQ RESTRICT
閱讀全文...

Oracle 計算時間差

突然來了個要計算每筆交易的時間，試著用Oracle提供的函式來解決，將最後的結果做個記錄。

計算兩日期的時間差：START_DATE, END_DATE
天：ROUND(TO_NUMBER(END_DATE - START_DATE))
小時：ROUND(TO_NUMBER(END_DATE - START_DATE) *24)
分：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60)
秒：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60*60)
毫秒：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60*60*1000)

上述的START_DATE, END_DATE為日期形態，如果遇到日期都用字串形態存入資料庫時，需在用TO_DATE轉換
TO_DATE(START_DATE, 'YYYYMMDDHH24MISS')
YYYYMMDDHH24MISS 這格式需帶入符合您的日期格式

閱讀全文...

[資料庫] SQL Server Expres(SQL Server免費版本)

最近需要用到，查了一下資料才發現 SQL Server 2012 Express 居然還分三個版本：
1. SQL Server Express
以 Microsoft SQL Server 為基礎的資料庫平台。SQL Server Express 可讓您輕鬆地開發功能豐富、提供強化儲存安全性而且部署快速的資料導向應用程式。

2. SQL Server Express with Tools
除了 Express 的功能，還多了圖形化管理工具(SQL Server Management Studio)。

3. SQL Server Express with Advanced Services
除了 Express 的功能，還多了圖形化管理工具(SQL Server Management Studio)、Reporting Services、BI Development Studio(提供整合式報表建立與設計環境來建立報表)、全文檢索搜尋，用於搜尋大量文字資料的強大搜尋引擎。

建議：
1. 若是用在開發資料庫程式，可以選用SQL Server Express with Tools版本。
2. 除了開發資料庫程式，還包含開發Reporting Services 報表時，請選用SQL Server Express with Advanced Services版本。
3. 若是要佈署資料庫程式到客戶電腦上，無需使用Reporting Services 報表，可以使用SQL Server Express版本，但建議使用SQL Server Express with Tools版本，畢竟有管理工具比較方便。

Express 版本的硬體限制
1. CPU：最多支援 1 顆實體 CPU。
2. 記憶體：最多支援到 1 GB。
3. 每個資料庫的最大大小為：10 GB(先前版本：SQL Server 2005 與 2008 的 Express 版本支援到 4 GB)，應該夠一堆資料量不大的軟體或網站使用了，真是佛來心的。


詳細資料請參考 SQL Server 2012 版本支援的功能
閱讀全文...

[資料庫]MySQL 授權協議

MySQL 是套開放原始碼的軟體，開放原始碼並不代表免費，雖然很多企業並不在意，總是抱著不會被抓的心態，但是工程師還是要懂得保護自己，不然工作久了，回首時，會發現背後的鍋子還真多....

MySQL 採用雙授權機制：商業授權和 GNU 通用公共許可證（GPL,GNU General Public License）。就因為這樣，常常讓我納悶，那到底什麼情況下才可以免費使用？？

根據MySQL官方的商業許可的相關說明，在下列情況下，可以免費使用MySQL：
1. 應用程式是在GPL許可下發佈的；(開放你的軟體原始碼？？)
2. 應用程式不用於分發。(關起門來自己用，不可以拿出去賣錢)
3. 非營利組織可以申請免費商業許可，但 MySQL 會carefully considered

也就是說，使用 MySQL 一定要有授權後才可以合法使用，不然就只能關起門在自家用。
閱讀全文...

[資料庫]PostgreSQL 授權協議

PostgreSQL 採用 BSD 版權協議發佈，允許您在商業或非商業應用的兩種環境下均享有自由取得且不受版權限制的自主使用權甚至延伸功能。

BSD授權協議是所有開源程式碼授權協議中最自由不受任何限制用途的版權宣告, 您永遠都不必擔心 PostgreSQL 被特定的公司所控制, 您不需要購買權權, 就如同當今的 GNU/Linux 一樣, 在您擁有開放源始碼的同時, 其高可用性的品質只有不斷提升而沒有下降過, 甚至您可以將 PostgreSQL 包在您的產品並出售, 更可以任意的加諸和修改功能

資料來源：PostgreSQL 中文
閱讀全文...

MS SQL Server 如何建立 Linked Server 連接 Oracle

因為專案的需求，所以研究了一下如何在 SQL Server 建立 Linked Server 連接 Oracle。
寫下此篇記錄，方便以後忘記時可以參考。

1. 首先要安裝 Oracle Client ，並設定好 tnsnames.ora ，其中的 NET_SERVICE_NAME 在建立 Linked Server 時會用到。
例：tnsnames.ora
NET_SERVICE_NAME =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = YourDbIP)(PORT = YourDbPort))
    )
    (CONTECT_DATA =
        (SERVICE_NAME = YourServiceName)
    )
)


2. 建立 Linked Server
開啟 Microsoft SQL Server Management Studio 來新增連結的伺服器。

在一般頁面中各欄位填入資料：
連結的伺服器： 填入這個連結的名稱，例： ORACLELNK
伺服器類型，請選擇其他資料來源。
提供者： 請選擇 Microsoft OLE DB Provider for Oracle
產品名稱： 填 oracle
產品來源： 請填入你在 tnsnames.ora 設定的網路服務名稱(NET_SERVICE_NAME)

切換至 安全性 頁面

請選擇 使用此安全性內容建立，並在遠端登入欄位填入帳號，指定密碼欄位填入登入密碼後，按 確定 ，即可以建立 Linked Server 。

3. 測試連線是否正常

成功就會顯示成功的訊息，如

4. 查詢測試
SELECT * FROM OPENQUERY(ORACLELNK, 'SELECT * FROM YourTable')

閱讀全文...

忘記PostgreSQL資料庫管理者密碼，要如何重新設定

當真的忘了PostgreSQL super user password的時候，可依照下列步驟來重新設定密碼：
1. 確認可以從本機免密碼登入資料庫。
   修改 pg_hba.conf，找到local這一行，將其改成trust
   local all all trust

2. 重啟PostgreSQL
   # su - postgres
   # pg_ctl reload

3. 重設密碼
   $ psql -U postgres
   SQL> ALTER USER postgres PASSWORD 'YourPassword';

4. 為了安全起見，修改pg_hba.conf回原設定，並重啟PostgreSQL
閱讀全文...

pg_ctl 啟動、停止和重啟 PostgreSQL

pg_ctl 是一個用於啟動、停止， 或重啟 PostgreSQL 後端伺服器，及顯示伺服器的狀態的工具。

Synopsis
pg_ctl start | stop | reload | status | restart [-D data_dir]

-D data_dir
聲明該資料庫文件的文件系統位置。 如果忽略這個選項，使用環境變量 PGDATA。

Note: 使用此命令前，請先將使用者切換至PostgreSQL super user(postgres)。

啟動伺服器：
$ pg_ctl start

停止伺服器：
$ pg_ctl stop

重啟伺服器：
$ pg_ctl restart

顯示伺服器狀態：
$ pg_ctl status
pg_ctl: postmaster is running (pid: 15718)
Command line was:
/usr/bin/postmaster '-D' '/var/lib/pgsql/data' '-p' '5433' '-B' '128'
閱讀全文...

在CentOS安裝PostgreSQL

在CentOS安裝PostgreSQL最簡單的方式就是在安裝CentOS時，勾選安裝PostgreSQL。如果在安裝過程並沒有安裝PostgreSQL，可以透過下列步驟來將PostgreSQL安裝設定完成。

1. 事先準備
2. 安裝 PostgreSQL
3. 第一次啟動
4. 設定成開機啟動PostgreSQL
5. 修改設定檔(pg_hba.conf)
6. 重啟PostgreSQL

1. 事先準備

在CentOS安裝光碟可以找到底下三個rpm檔，將其放置於同一個目錄中。
postgresql-8.1.22-1.el5_5.1.i386.rpm (版本序號可能不同，請找類似postgresql開頭的檔案)
postgresql-libs-8.1.22-1.el5_5.1.i386.rpm
postgresql-server-8.1.22-1.el5_5.1.i386.rpm

2. 安裝PostgreSQL

執行下列命令來安裝PostgreSQL：
rpm -Uvh postgresql-*.rpm
或
yum install postgresql postgresql-server

3. 第一次啟動

執行下列命令來啟動PostgreSQL：
service postgresql start
或
/etc/init.d/postgresql restart

4. 設定成開機啟動PostgreSQL

啟動後沒任何問題時，再將PostgreSQL設定成開機時啟動。
chkconfig postgresql on

5. 修改設定檔(pg_hba.conf)

修改pg_hba.conf(預設路徑為/var/lib/pgsql/data)
#local all all ident sameuser
local all all trust
# host all all 127.0.0.1/32 ident sameuser
host all all 127.0.0.1/32 md5

Note: md5和trust差別在於trust允許在本機不用輸入密碼來登入資料庫，安全性較弱。

6. 重啟PostgreSQL

由於設定檔改變了，需通知 postmaster 重新載入這些新的設定。
執行以下命令：
su - postgres
pg_ctl reload

閱讀全文...

Perl connect/access PostgreSQL

底下說明了Perl如何連接PostgreSQL和操作sql。

事前準備
範例：
1. load module
2. 初始 database handle
3. connect to database
4. 執行SQL
5. 取得結果列數
6. 處理每一row資料

1. load module

use DBI;

2. 初始 database handle

my $db_driver = 'Pg';
my $db_name = 'yourDB';
my $db_host = 'localhost';
my $db_user = 'yourDBUser';
my $db_pass = 'yourDBPass';
my $db_port = '5432';
my $db_url = "dbi:${db_driver}:dbname=${db_name};host=${db_host};port=${db_port};"

3. connect to database

my $dbh = DBI->connect($db_url, $db_user, $db_pass) or die "Can't connecting to the database: $DBI::errstr\n";

4. 執行SQL

my $sql = "SELECT * FROM yourTable';
my $sth = $dbh->prepare($sql);
$sth->execute;

5. 取得結果列數

my $rows = $sth->rows;

6. 處理每一row資料

while ($row = $sth->fetchrow_hasherf()) {
    my ($field1, $field2, ...) = ($row->{field1_name}, $row->{field2_name}, ...);
    ...
}

閱讀全文...

如何查詢PostgreSQL資料庫使用空間大小及回收垃圾儲存空間

一般資料庫在使用一段時間後，隨著資料庫操作和資料越來越多，資料庫的儲存空間就會慢慢的增加，如果不適當的管理，最後會演變成一隻超吃空間的怪獸。
而一般的PostgreSQL SQL操作，如update或delete，這些資料的位元組，並沒有真正的被刪除，如不適當的回收，整個資料庫會虛胖到一個讓人無法接受的程度。

要如何知道資料庫的儲存的空間大小，PostgreSQL透過下列語法可以查詢資料庫所佔用的位元組：
postgres=> SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database\g 
datname | size
--------------+--------- 
postgres | 3537 kB
template1 | 3480 kB
template0 | 3480 kB

(4 行)

PostgreSQL提供 VACUUM 指令來回收垃圾儲存空間。
1. 建議平常定時使用 VACUUM 不帶任何參數，來進行簡單的回收空間令其可再次使用。
2. 有特殊大量資料新增或長時間定期維護時，使用 VACUUM FULL來做完全清理的動作，會耗費較長的時間來回收垃圾空間。
Note：VACUUM期間會lock table，會導致無法存取。

閱讀全文...