2010-03-05 Fri
看到Kamus对SQLULDR2的留言后, 破有感触. 人们应当比较关注, 他们想要的功能用起来方便是否, 关键并不在于功能的多少. 而SQLULDR2的众多的命令行选项, 也确实有些让人发晕, 包括我自已.
为了方便大多数人使用, 简化了SQLULDR2的命令行帮助, 简化到如下所示.
SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
Usage: SQLULDR2 keyword=value [,keyword=value,...]
Valid Keywords:
user = username/password@tnsname
sql = SQL file name
query = select statement
field = separator string between fields
record = separator string between records
rows = print progress for every given rows (default, 1000000)
file = output file name(default: uldrdata.txt)
log = log file name, prefix with + to append mode
fast = auto tuning the session level parameters(YES)
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
parfile = read command option from parameter file
for field and record, you can use '0x' to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27
对于专家而言, 可以用如下方式得到以前全部的命令行选项.
sqluldr2 help=yes
通过引入一个TEXT选项, 来针对不同格式的导出进行相关选项的设置, 不仅方便了大家使用, 也可以对SQLULDR2的功能有一个很直接的了解, 例如SQLULDR2可以导出数据给MySQL用, 或导出成Excel可以打开的标准CSV文件, 或是生成MySQL和ORACLE上的INSERT语句, 也可以按列显示记录, 或为一些特殊的搜索程序生成数据源.
再次感谢Kamus的好建议, 今年是支付宝的用户体验年, 应当从用户角度进行反思.
Relative Posts:
2010-03-04 Thu
为了查询出保存在员工表(SCOTT.EMP)中, 每个部门工资最高的三个人, 如果是Oracle数据库, 大家可以使用Windows分组汇总函数来轻松地实现, 如下所示.
SELECT * FROM (
SELECT DEPTNO, EMPNO, ENAME, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK
FROM EMP ) WHERE RNK <= 3
但如果员工表存放在MySQL数据库, 或其他数据库, 如SQLLite中, 要实现同样的功能, 就比较复杂了, 至少我现在都还不会. 但利用DataReport以前开发的功能, 及刚增加的条件过滤功能, 就可以轻松实现这个需求.
webchart.query_1=select deptno, empno, ename, sal from emp
webchart.express_1=rank|x|rnk::sal|deptno
webchart.filter_1=3.5-x|rank
webchart.sort_1=deptno,rank
webchart.group_1=1
如果Filter中的公司算出来的值小于0, 那么这条记录就会被删除, 在这个例子中, 如果排名这一列的值大于3, 这个公式算出来的值就为负数, 所以只保留了前三名, 达到了我们的业务要求. 页面输出如下所示的表格:
deptno empno ename sal rank 10 7839 KING 5000.0 1 7782 CLARK 2450.0 2 7934 MILLER 1300.0 3 20 7788 SSCOTT 3000.0 1 7902 FORD 3000.0 2 7566 JONESS 2975.0 3 30 7698 BLAKE 2850.0 1 7499 ALLEN 1600.0 2 7844 TURNER 1500.0 3
将这些处理放在应用服务器端实现, 不仅让SQL变得通用, 如果访问频率极高, 还可以减轻数据库端的压力.
Relative Posts:
Votedisk can’t be add & remove while CRS is running, or else it would corrupt something.
OCR can be added & replaced while CRS is running.
ENV: Oracle Clusterware 10.2.0.4
Detailed logfile:
While CRS is still running…
# ./crsctl add css votedisk /dev/rdsk/ c4t60060E80056F160000006F1600000669d0s1
Cluster is not in a ready state for online disk addition
# ./crsctl add css votedisk /dev/rdsk/ c4t60060E80056F160000006F1600000669d0s1 -force
Now formatting voting disk: /dev/rdsk/ c4t60060E80056F160000006F1600000669d0s1
successful addition of votedisk /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1.
# ./crsctl query css votedisk
0. 0 /dev/rdsk/c4t60060E80056F160000006F1600000469d0s1
1. 0 /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1
located 2 votedisk(s).
Looks good, continue add another one.
# ./crsctl add css votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
Cluster is not in a ready state for online disk addition
# ./crsctl add css votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1 -force
Now formatting voting disk: /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
successful addition of votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1.
It’s corrupted. Entry 1 and entry 2 both point to “*869d0s1”.
# ./crsctl query css votedisk
0. 0 /dev/rdsk/c4t60060E80056F160000006F1600000469d0s1
1. 0 /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
2. 0 /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
located 3 votedisk(s).
Has to stop CRS on all nodes.
# ./crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Then I want to delete corrupted entry 1 and entry 2.
# ./crsctl delete css votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
Cluster is not in a ready state for online disk removal
# ./crsctl delete css votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1 -force
no votedisk found matching path specified /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1.
It shows “0” for entry 1 and entry 2.
# ./crsctl query css votedisk
0. 0 /dev/rdsk/c4t60060E80056F160000006F1600000469d0s1
1. 0 0
2. 0 0
Add another 2 votedisk while CRS is down.
# ./crsctl add css votedisk /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1 -force
Now formatting voting disk: /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1
successful addition of votedisk /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1.
# ./crsctl add css votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1 -force
Now formatting voting disk: /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
successful addition of votedisk /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1.
# ./crsctl query css votedisk
0. 0 /dev/rdsk/c4t60060E80056F160000006F1600000469d0s1
1. 0 0
2. 0 0
3. 0 /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1
4. 0 /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
located 5 votedisk(s).
Delete “0” votedisk. Looks good.
# ./crsctl delete css votedisk 0 -force
successful deletion of votedisk 0.
# ./crsctl query css votedisk
0. 0 /dev/rdsk/c4t60060E80056F160000006F1600000469d0s1
1. 0 0
2. 0 /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1
3. 0 /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
located 4 votedisk(s).
# ./crsctl delete css votedisk 0 -force
successful deletion of votedisk 0.
# ./crsctl query css votedisk
0. 0 /dev/rdsk/c4t60060E80056F160000006F1600000469d0s1
1. 0 /dev/rdsk/c4t60060E80056F160000006F1600000669d0s1
2. 0 /dev/rdsk/c4t60060E80056F160000006F1600000869d0s1
located 3 votedisk(s).
Want to add mirrored OCR while CRS is down. It reports error “PROT-1”.
# ./ocrconfig -replace ocrmirror /dev/rdsk/c4t60060E80056F160000006F1600000669d0s5
PROT-1: Failed to initialize ocrconfig
After start CRS on all nodes. Mirrored OCR can be added.
# ./ocrconfig -replace ocrmirror /dev/rdsk/c4t60060E80056F160000006F1600000669d0s5
# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1151724
Used space (kbytes) : 4608
Available space (kbytes) : 1147116
ID : 1986342521
Device/File Name : /dev/rdsk/c4t60060E80056F160000006F1600000469d0s5
Device/File integrity check succeeded
Device/File Name : /dev/rdsk/c4t60060E80056F160000006F1600000669d0s5
Device/File integrity check succeeded
Cluster registry integrity check succeeded
# cat /var/opt/oracle/ocr.loc
#Device/file getting replaced by device /dev/rdsk/c4t60060E80056F160000006F1600000669d0s5
ocrconfig_loc=/dev/rdsk/c4t60060E80056F160000006F1600000469d0s5
ocrmirrorconfig_loc=/dev/rdsk/c4t60060E80056F160000006F1600000669d0s5
local_only=false#
[detached]
2010-03-03 Wed
换手机号码了，原号码永久保留电话功能，不再支持短信功能；
新号码，要知道我新号码，可以继续打原来号码问我的，嘿嘿。
2010-03-01 Mon
我在"如何在oracle里使用java存储过程连接db2"这篇文章里已经提到过如何在oracle里用java存储过程连db2 v9 for z/OS，并且我在里面举了用java存储过程在db2 v9 for z/OS上执行一条sql的例子，这篇文章其实就是上述文章的延续，在这篇文章里，我们完整的做了一个调用db2 v9 for z/OS上存储过程的例子。
我们的目的是要调用存储过程XBAIPA.CAAUD，这个存储过程有一个输入参数和一个输出参数，其中输入参数是一个字符串，输出参数也是一个字符串。
注意这里安装完transparent gateway后虽然能连上db2 v9 for z/OS,但是不能执行其中的存储过程，如下所示：
SQL> select deptname from DSN8910.DEPT@PLHI3 where deptno='A00';
DEPTNAME
------------------------------------------------------------------------
SPIFFY COMPUTER SERVICE DIV.
SQL> var temp varchar2(32767);
SQL> set serveroutput on size 1000000;
SQL> begin
2 XBAIPA.CAAUD@PLHI3('A 9999992581970687034 NBEUR 1232542102008120220091222 LEENEN/WERNER MR 5BJ9B9/1A NON-RER REF FEE MAY APPLY BR0.114200 0000000019000000000001900000000000190000000000293000000000000Y 0 0N00000000000H',:temp);
3 end;
4 /
begin
XBAIPA.CAAUD@PLHI3('A 9999992581970687034 NBEUR 1232542102008120220091222 LEENEN/WERNER MR 5BJ9B9/1A NON-RER REF FEE MAY APPLY BR0.114200 0000000019000000000001900000000000190000000000293000000000000Y 0 0N00000000000H',:temp);
end;
ORA-06550: line 2, column 1:
PLS-00201: identifier 'XBAIPA.CAAUD@PLHI3' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
temp
---------
为什么不能执行上述存储过程的原因我已经在上述文章里说过了。
现在我们来看怎样才能成功调用上述存储过程：
在把JDBC for db2的驱动加载到oracle里后首先创建所需的java存储过程：
SQL> create or replace and compile java source named call_db2 as
2 import java.sql.*;
3 import oracle.jdbc.*;
4 import java.io.*;
5 import java.lang.*;
6
7 public class call_db2
8 {
9 public static String call_db2_procedure_type_1(String ProcedureName, String InputString)
10 {
11 Connection conn = null;
12 CallableStatement proc = null;
13 String callname = null;
14 String OutputString = null;
15
16 try
17 {
18 DriverManager.registerDriver(new com.ibm.db2.jcc.DB2Driver());
19 conn = DriverManager.getConnection("jdbc:db2://10.1.21.215:446/LOCDSN3", "xbalhu", "abcd");
20
21 //执行一个db2存储过程----------begin------------------------------
22 callname = "{CALL " + ProcedureName + "(?,?)}";
23 proc = conn.prepareCall(callname);
24 proc.setString(1, InputString);
25 proc.registerOutParameter(2, Types.VARCHAR);
26 proc.execute();
27 OutputString = proc.getString(2).substring(1,2000);
28 //执行一个db2存储过程----------End--------------------------------
29 }
30 catch(Exception e)
31 {
32 e.printStackTrace();
33 OutputString = "Error Occur: " + e;
34 }
35 finally
36 {
37 try
38 {
39 if(proc != null)
40 {
41 proc.close();
42 }
43
44 if(conn != null)
45 {
46 conn.close();
47 }
48 }
49 catch(Exception ex)
50 {
51 }
52 }
53
54 return OutputString;
55 }
56 }
57 /
Java created
接着创建wrapper function：
SQL> CREATE OR REPLACE Function F_SYS_CALL_DB2_PROC(ProcedureName varchar2, InputString varchar2)
2 return varchar2
3 as language java
4 name 'call_db2.call_db2_procedure_type_1(java.lang.String, java.lang.String) return java.lang.String';
5 /
Function created
好了，现在我们可以来测一下最后的效果了：
SQL> var temp varchar2(32767);
SQL> set serveroutput on size 1000000;
SQL> begin
2 :temp := f_sys_call_db2_proc('XBAIPA.CAAUD','A 9999992581970687034 NBEUR 1232542102008120220091222 LEENEN/WERNER MR 5BJ9B9/1A NON-RER REF FEE MAY APPLY BR0.114200 0000000019000000000001900000000000190000000000293000000000000Y 0 0N00000000000H');
3 end;
4 /
PL/SQL procedure successfully completed
temp
---------
0000000000000+0000000000000+0000000000000+0000000000000 Y
从结果里可以看到，我们已经在oracle里成功调用了db2 v9 for z/OS中的存储过程。
上述蓝色字体标注的部分格式不能错，否则oracle可能会报错：
com.ibm.db2.jcc.c.SqlException: [ibm][db2][jcc][10243][10940] 对 DB2 z/OS 版的存储过程调用不支持字符串文字
呵呵，很搞的错误提示。
2010-02-27 Sat
在邮件组里看到有人问能不能把多个hiphop-php编译后的程序跑在同一个端口上，想想也是合理的要求。如果一个服务器上跑了多个站点，那肯定都得用80端口，当大家共同租用服务器的时候，这个需求更为强烈。当时我所想到的解决办法是在前面搭个nginx之类的做代理，实际编译后的程序跑在别的端口，然后没过几天就看到了这份wiki - Using nginx as front server to HipHop。
简单的nginx配置示例
/etc/nginx/conf.d/ooso.conf:
-
server {
-
listen *:80;
-
server_name *.ooso.net ooso.net;
-
-
location / {
-
root __SERVER_ROOT__;
-
index index.html index.php index.htm;
-
}
-
-
location ~ \.php$ {
-
proxy_set_header X-Real-IP $remote_addr;
-
proxy_set_header Host www.ooso.net;
-
proxy_pass http://localhost:4247;
-
}
-
}
把hiphop-php编译后的代码跑在4247端口，然后通过nginx把所有对php的请求转发到这个端口，看上去就像我们平常配置的php fastcgi，不是吗？
这样做有什么好处
- 支持负载均衡
- 支持ssl
- 支持gzip压缩
- 用nginx来挡住DoS攻击
- 因为我们的代码需要经过编译才能上线，代码多起来这个时间还真不短，不能像之前单纯的php那样爽快覆盖就完事。把经过编译的最新代码部署在别的端口上，用nginx快速切换，应该是一个比较实际的用法。
额外的技巧
看wiki学到的额外技巧。以下配置段可以防止某些人把别的垃圾域名指向你的主机，结果被搜索引擎认为你用多个域名搞了一堆重复的内容建设，降低搜索权重。
-
server {
-
listen *:80;
-
server_name _;
-
-
location / {
-
deny all;
-
}
-
}
